Формулы Excel без угадывания. 16 разборов и 64 упражнения

Число в ячейке ещё не ответ на вопрос

Представьте список покупок для небольшой мастерской. В нём две строки с чаем: в одной четыре упаковки, в другой одна. Сколько чая в списке? Можно получить и два, и пять. Оба результата вычислены правильно, но относятся к разным вопросам: сколько строк и сколько упаковок. Первое действие при работе с формулой — договориться, что именно нужно узнать.

Эта книга учит переходить от вопроса к расчёту и обратно. В каждом разборе сначала указана задача. Затем мы разбираем формулу на части, получаем ответ другим способом и проверяем, что изменится на неудобном примере. Такой порядок помогает заметить ошибку, которая не выдаёт красного предупреждения, а тихо возвращает правдоподобное число.

Здесь нет обещания освоить весь Excel. Вы научитесь разбирать несколько распространённых видов задач: построчный расчёт, итог, условную сумму, отметку по правилу, поиск по коду и очистку текста. Небольшая общая таблица позволяет тратить внимание на различия между задачами, а не разбираться каждый раз в новой предметной области.

Подготовьте отдельный лист

Создайте пустую учебную книгу. Не экспериментируйте в рабочем отчёте, где формулы влияют на чужие решения. Переименуйте один лист в Inputs — латинскими буквами, без кавычек. В примерах используются ссылки на этот лист. Имя выбрано только для того, чтобы формулы можно было воспроизвести одинаково.

Адрес B5 означает столбец B и строку 5. Диапазон B5:B10 включает обе границы и все ячейки между ними. Строку заголовков поместите в четвёртую строку, а записи — начиная с пятой. Нужные значения повторены в условиях разборов. Если указано, что E5 содержит формулу, введите именно формулу, а не показанный рядом результат.

Пустая ячейка в условиях означает ячейку без содержимого: не пробел, не ноль и не слово «пусто». Для текстового числа 12 используйте ввод с ведущим апострофом: '12. Апостроф указывает способ ввода и обычно не показывается внутри ячейки. Формула ="" — ещё один отдельный случай: она существует, хотя её результат выглядит пустым.

Для нового расчёта, если в разборе не задана выходная ячейка, используйте Q5. Следующую пробу можно выполнить в Q6. Не записывайте итог внутрь диапазона, который этот итог суммирует. Изменения данных в упражнениях локальны: перед следующим упражнением восстановите исходные значения, если явно не сказано продолжить изменение.

Две записи одной формулы

Основная запись в книге использует русские имена функций и точку с запятой между аргументами. Рядом приведён английский вариант с запятыми. Это два образца записи для распространённых настроек, а не утверждение, что язык интерфейса однозначно определяет все разделители. Настройки языка и региона могут отличаться.

Например, русская запись суммы — =СУММ( B5:B10 ), английская — =SUM( B5:B10 ). В формуле с несколькими аргументами разделитель уже заметен: =ЕСЛИ( B5=0; "Нет"; "Есть" ) и =IF( B5=0, "Нет", "Есть" ). Если Excel отвергает ввод, сначала посмотрите подсказку самой функции и разделители в формуле, которая уже работает в вашей учебной книге.

В формулах нужны прямые двойные кавычки ". Типографские «ёлочки» используются только в обычном русском тексте. Начальный знак равенства обязателен. При переносе из электронной книги проверьте, не попал ли в формулу разрыв строки внутри имени функции или адреса. Пробелы, которыми для удобства разделены аргументы, не являются частью текстового критерия; пробелы внутри двойных кавычек являются.

Если вместо ответа видна вся строка с формулой, проверьте, не включён ли показ формул. Если так ведёт себя лишь одна ячейка, возможны текстовый формат или ведущий апостроф перед знаком равенства. Исправление формата само по себе не всегда переосмысливает старую запись: проверьте её повторный ввод в отдельной ячейке.

Как извлечь пользу из упражнений

Сначала решите, каким должен быть ответ, не вводя формулу. Для небольшой таблицы можно выписать подходящие строки или сложить числа вручную. После расчёта сравните не только число, но и его смысл: строки, штуки, сумма, текстовая отметка. Разные единицы — частая причина уверенного неверного вывода.

В заданиях намеренно встречаются неправильные формулы. Их результат не считается правильным решением исходной задачи. Например, 129 вместо 500 показывает, что сложение выполнено, но стоимость четырёх предметов не найдена. Разбор должен назвать обе части: что формула фактически посчитала и что требовалось посчитать.

Ответы расположены отдельно. Ссылка «Проверить задание» ведёт к объяснению, обратная — к разбору. Если ваша читалка не поддерживает внутренние ссылки, используйте оглавление и номер задания. Не торопитесь открывать ответ после первого несовпадения: назовите возможную причину и измените один параметр, который отличает вашу гипотезу от другой.

Границы книги

Примеры опираются на базовые функции Excel и на официальную справку Microsoft. Расчётная основа проверена в Microsoft Excel для Mac версии 16.112.3. Это не означает, что автор проверил все версии Excel, Windows, веб-приложение и сторонние табличные редакторы. Названия команд и вид окон могут отличаться; в книге важнее адреса, значения и логика формулы.

Денежные числа в учебной таблице служат удобным примером умножения и суммирования. Это не бухгалтерская, налоговая или финансовая модель. В частности, глава об округлении показывает арифметическое расхождение, но не устанавливает правила расчётов для договоров или отчётности.

Все исходные записи, вопросы и объяснения созданы для этого практикума. Книга не является официальным руководством Microsoft. В конце приведены ссылки на справку для проверки определений и дальнейшего чтения. Для выполнения упражнений обращаться к внешним файлам или регистрироваться на дополнительных сайтах не требуется.

Разбор 1. Посчитать строку и скопировать правило

Задача: Превратить вопрос о стоимости в умножение двух ячеек и проверить, как правило меняется при копировании.

Создайте пустой лист и назовите его Inputs. В A4:D4 введите заголовки: Предмет, Количество, Цена, Группа. В E4 напишите Сумма. Цены — условные денежные единицы за один предмет; количество и цена вводятся числами, без слов и знаков валюты.

Заполните A5:D10 по строкам: 5 — Чай | 4 | 125 | Напитки; 6 — Кофе | 3 | 210 | Напитки; 7 — Кружка | 2 | 350 | Посуда; 8 — Тарелка | 5 | 180 | Посуда; 9 — Чай | 1 | 140 | Напитки; 10 — Блокнот | 0 | 95 | Канцелярия. Пока оставьте E5:E10 пустыми.

Основную формулу введите в E5 на этом же листе. После проверки скопируйте E5, выделите E6:E10 и вставьте обычным способом, не выбирая вставку только значений. Это учебная копия данных: не выполняйте опыт поверх своей рабочей таблицы.

До обращения к Excel сформулируйте вопрос целиком: сколько стоят все предметы в одной строке, если известны их количество и цена одного? Здесь количество измеряется в штуках, а цена — в денежных единицах за штуку. При умножении получается стоимость всей строки. Заголовок Сумма сам по себе не говорит программе, какое действие нужно выполнить: выбор действия остаётся за автором формулы.

Адрес B5 означает столбец B, строку 5; там лежит количество чая. Адрес C5 ведёт к цене того же чая. Формула начинается с =, а знак * между адресами означает умножение. Вводите латинские буквы адресов. На бумаге удобно писать знак ×, но в копируемой формуле используется звёздочка. В этом примере нет имени функции, поэтому русская и английская записи совпадают.

Можно было бы вписать готовое число 500. Однако такое число не хранит способ его получения. Ссылка на B5 и C5 оставляет связь с исходными данными: читатель формулы видит, откуда взят результат. После ввода выделите E5 и посмотрите строку формул. В самой ячейке вы видите ответ, а в строке формул — правило. Это два представления одной ячейки, и для проверки нужны оба.

Теперь требуется то же действие для следующих предметов. При копировании из E5 в E6 формула перемещается на одну строку вниз. Ссылки без знаков $ перемещаются вместе с ней: B5 становится B6, C5 становится C6. Цена кофе берётся из строки кофе. Мы переносим не стоимость чая, а правило: взять два значения из той же строки и перемножить. Такое поведение ссылок называют относительным.

Не ограничивайтесь проверкой первой вставленной ячейки. В E10 должны оказаться ссылки именно на B10 и C10. Посередине диапазона отдельно проверьте E8. У нескольких соседних строк ответы могут случайно выглядеть убедительно даже при ошибке адреса; просмотр начала, середины и конца помогает заметить сдвиг. Здесь строки не сортируются и не вставляются: разбирается только обычное копирование вниз.

Ручная проверка должна отличаться от повторного чтения формулы. Для чая сложите цену четырёх одинаковых предметов; для тарелок рассуждайте как о пяти одинаковых покупках. Затем сопоставьте эти ответы с E5 и E8. Ноль у блокнота тоже объясним: строка и цена существуют, но количество равно нулю. Нулевой результат не означает отсутствия записи и не требует удаления строки.

Какую формулу ввести в E5, чтобы получить стоимость четырёх единиц чая по 125?

Русская запись: =B5*C5

Английская запись: =B5*C5

Результат: 500 денежных единиц.

B5 передаёт число 4, C5 — число 125. Получается 4 × 125 = 500. Независимый путь: 125 + 125 + 125 + 125 тоже даёт 500.

После копирования E5 вниз ожидаются E6 = 630, E7 = 700, E8 = 900, E9 = 140 и E10 = 0. Эти числа — контрольные ответы, а не значения, которые следует вручную подставить вместо формул.

Excel принял формулу, а ответ неверен для задачи. Что произошло?

Русская запись: =B5+C5

Английская запись: =B5+C5

Что получится: 129 — вычислено правильно, но это не стоимость строки.

Программа честно складывает 4 и 125. Ошибка не в вычислителе и не в распознавании формулы: выбрано неподходящее действие. Сумма количества предметов и цены одного предмета не отвечает на исходный вопрос. Исправьте + на *, сохранив ссылки, и объясните себе, почему теперь используются совместимые величины.

Не глядя на контрольные ответы, найдите стоимость строки 5 двумя способами: через формулу и сложением одинаковых цен.

Проверить задание 1.1

Вы копируете E5 на одну строку вниз. Назовите будущую формулу E6 и результат до вставки.

Проверить задание 1.2

В E8 показано 900. Подтвердите ответ без просмотра E5 и объясните, какие исходные ячейки надо проверить.

Проверить задание 1.3

В E5 ввели =B5+C5 и получили 129. Нужно ли искать сбой Excel? Назовите исправление и его смысл.

Проверить задание 1.4

Проверяйте не только ответ, но и правило: операция должна соответствовать вопросу, а ссылки после копирования — нужной строке.

К списку задач

Разбор 2. Применить один коэффициент ко всем строкам

Задача: Разделить меняющуюся цену и общий коэффициент, чтобы при копировании каждый множитель брался из правильного места.

На листе Inputs введите числовые цены: C5 = 125, C6 = 210, C7 = 350, C8 = 180, C9 = 140, C10 = 95. Им соответствуют чай, кофе, кружка, тарелка, чай и блокнот. В C4 напишите Цена, в N4 — После коэффициента; N5:N10 пока пусты.

В M4 напишите Коэффициент, а в M5 введите 90%. Смысл значения — девять десятых, то есть число 0,9 в записи с десятичной запятой или 0.9 с точкой. M6:M10 оставьте действительно пустыми: не вводите нули, пробелы и формулы.

Все формулы этого разбора вводятся на листе Inputs. Основная формула предназначена для N5; затем её нужно скопировать в N6:N10. Это пересчёт цены одного предмета, а не умножение на количество из столбца B.

Здесь у каждого результата два источника с разными обязанностями. Цена относится к конкретному предмету и должна меняться от строки к строке. Коэффициент относится ко всему расчёту и находится в одной ячейке M5. Перед записью формулы назовите эти обязанности вслух: цена текущей строки; общий коэффициент. Так легче выбрать ссылки, чем запоминать знаки $ как украшение адреса.

В N5 цена берётся из C5. При копировании в N6 нам понадобится C6, поэтому первую ссылку оставляем относительной. Вторую записываем как $M$5. Знак перед M закрепляет столбец, а знак перед 5 — строку. Для рассматриваемого копирования вниз решающим является закрепление строки, но полная абсолютная ссылка ясно выражает замысел: коэффициент хранится в одной определённой ячейке.

Знаки $ не превращают коэффициент в неизменяемое число и не защищают ячейку от редактирования. Они определяют, как адрес ведёт себя при копировании формулы. Если значение M5 изменится, формулы по-прежнему будут обращаться к M5 и использовать уже новое содержимое. Обсуждаемое правило относится к копированию; это не обещание о любых перемещениях, удалениях или перестройках листа.

Запись 90% означает, что берётся девяносто сотых исходной цены. Она не означает уменьшить цену на девяносто процентов. В нашей задаче остаётся девяносто процентов, то есть цена снижается на десять процентов. Полезный контроль направления: положительная цена после умножения на коэффициент между нулём и единицей должна стать меньше исходной. Если ответ заметно больше, прежде всего проверьте ввод M5.

Вычисление первой строки ещё не проверяет закрепление: и C5*M5, и C5*$M$5 обращаются к одним и тем же ячейкам, пока находятся в N5. Различие обнаруживается после копирования. Поэтому для такого правила обязательна хотя бы одна проверка другой строки. Откройте N6 в строке формул и убедитесь, что рядом с C6 всё ещё указан $M$5, а не M6.

Нулевой результат ошибочной формулы может показаться законной ценой, особенно в таблице, где встречаются настоящие нули. Здесь он подозрителен по смыслу: ненулевая цена кофе умножается на ненулевой коэффициент. Сначала проследите обе ссылки. Не заменяйте неправильный ноль готовым числом 189: так вы скроете поломку правила и потеряете связь с коэффициентом. Исправлять нужно формулу, из которой будут получены остальные строки.

Как в N5 получить девяносто процентов цены C5 и подготовить формулу к копированию вниз?

Русская запись: =C5*$M$5

Английская запись: =C5*$M$5

Результат: 112,5 денежных единицы за один предмет.

Для проверки вне таблицы: 125 × 0,9 = 112,5. Можно сначала найти десятую часть цены — 12,5 — и вычесть её из 125; получится то же значение.

После копирования в N6 запись будет =C6*$M$5: цена изменится на 210, а коэффициент останется 0,9. Поэтому результат следующей строки равен 189. Количество кофе в этом расчёте не участвует.

Из N5 скопировали незакреплённую формулу =C5*M5. Что окажется в N6 при пустой M6?

Русская запись: =C6*M6

Английская запись: =C6*M6

Что получится: 0 вместо ожидаемых 189.

Обе ссылки сдвинулись на строку вниз. В этой операции ссылка на действительно пустую M6 даёт нулевой множитель, поэтому получается 0. Excel не знает, что вы намеревались использовать общий коэффициент. Верните в исходную формулу $M$5 и заново скопируйте её вниз; не заполняйте весь столбец M копиями коэффициента ради маскировки ошибки.

Найдите N5 при цене 125 и коэффициенте 90%. Затем проверьте ответ через уменьшение исходной цены на её десятую часть.

Проверить задание 2.1

Назовите формулу и значение N6 после копирования правильной формулы из N5. Какая часть адреса должна измениться?

Проверить задание 2.2

Для кружки в C7 записано 350. Найдите N7 и объясните, почему не нужно переносить коэффициент в M7.

Проверить задание 2.3

В N5 написано =C5*M5. Предскажите формулу N6 после копирования на одну строку вниз, её результат при пустой M6 и исправление.

Проверить задание 2.4

У общего параметра закрепляйте адрес, у построчного значения оставляйте нужное движение; проверяйте правило за пределами первой строки.

К списку задач

Разбор 3. Проверить общий итог, а не только красивую сумму

Задача: Задать полную область суммирования и проверить её состав независимо от арифметики Excel.

На листе Inputs введите заголовки A4:E4: Предмет, Количество, Цена, Группа, Сумма. Записи A5:D10: 5 — Чай | 4 | 125 | Напитки; 6 — Кофе | 3 | 210 | Напитки; 7 — Кружка | 2 | 350 | Посуда; 8 — Тарелка | 5 | 180 | Посуда; 9 — Чай | 1 | 140 | Напитки; 10 — Блокнот | 0 | 95 | Канцелярия.

В E5 введите =B5*C5 и скопируйте вниз до E10. Контрольные значения E5:E10: 500; 630; 700; 900; 140; 0. В отличие от просто напечатанных сумм, формулы позволят выполнить упражнение с изменением количества блокнотов.

Для общего итога используйте свободную ячейку P5. Она не входит в E5:E10 и не заменяет ни одну исходную запись. После упражнения с изменением B10 восстановите исходное число 0.

У общего итога есть две отдельные проверки: правильно ли сложены числа и все ли нужные числа попали в расчёт. Программа умеет выполнять первое, но не читает ваше намерение. Если выбрать только часть таблицы, она без возражений выдаст сумму этой части. Поэтому перед вводом формулы определите первую и последнюю строки данных. Здесь их шесть: от строки 5 до строки 10 включительно.

СУММ, или SUM в английской записи, позволяет обозначить весь последовательный участок одним диапазоном. Двоеточие в E5:E10 читается как «от E5 до E10 включительно». Это не только две крайние ячейки. Имя листа Inputs и восклицательный знак показывают, где расположен диапазон. Такой адрес удобно читать даже тогда, когда итог вынесен в другую часть книги.

Для этой задачи нужен столбец E, где уже рассчитана стоимость каждой строки. Столбец C содержит цены отдельных предметов, а B — количество. Все три столбца числовые, поэтому технически из каждого можно получить сумму. Но только один отвечает на заданный вопрос об общей стоимости. Подпись рядом с итогом должна напоминать, что измеряет результат: не число строк и не количество предметов.

Шестая запись даёт ноль, но её всё равно нужно включить в область расчёта. Нулевое значение сегодня не является разрешением обрезать диапазон. Если завтра количество в этой же записи изменится, правильно заданный итог должен его учесть. Это различие между описанием области задачи и подгонкой формулы под текущие цифры. Мы не добавляем строку: меняем значение внутри уже существующей записи.

Один способ независимой проверки — разложить данные на непересекающиеся части. Напитки занимают строки 5, 6 и 9; посуда — 7 и 8; канцелярия — 10. Каждая исходная строка должна оказаться ровно в одной части. Такое разбиение заставляет осмотреть всю таблицу и заметить вторую запись чая, которую легко пропустить при беглом чтении сверху вниз.

Другой полезный контроль — заранее предсказать изменение итога при одной правке. В упражнении меняется только B10: число блокнотов увеличивается с нуля до одного. Изменение общей стоимости должно равняться цене одного блокнота. Если сумма осталась прежней, проверяйте связь E10 с B10 и присутствие E10 в диапазоне. Если сдвиг иной, выясните, не изменились ли дополнительные ячейки.

Как получить в P5 стоимость всех шести записей, включая строку с нулевым количеством?

Русская запись: =СУММ( Inputs!E5:E10 )

Английская запись: =SUM( Inputs!E5:E10 )

Результат: 2870 денежных единиц.

Прямой контроль: 500 + 630 + 700 + 900 + 140 + 0 = 2870. Диапазон содержит каждое из этих шести значений ровно один раз.

Проверка по группам: напитки дают 1270, посуда — 1600, канцелярия — 0. Сумма 1270 + 1600 + 0 равна 2870. Здесь группировка используется для ручной проверки, а не требует знания новой функции.

Почему формула не выдаёт сообщение об ошибке, хотя итог неполный?

Русская запись: =СУММ( Inputs!E5:E8 )

Английская запись: =SUM( Inputs!E5:E8 )

Что получится: 2730 — сумма только строк 5–8.

Диапазон заканчивается слишком рано: строки 9 и 10 исключены. Сейчас видимая разница равна 140, потому что E9 содержит 140, а E10 — 0. По одному расхождению нельзя заключить, что пропущена только одна строка: нулевая запись тоже находится за границей формулы. Исправляется конец диапазона на E10, а не прибавляется вручную 140.

Запишите полную формулу общей стоимости в свободной P5. Укажите, сколько ячеек входит в её диапазон.

Проверить задание 3.1

Проверьте итог через три непересекающиеся группы. Для каждой перечислите строки и найдите сумму.

Проверить задание 3.2

Получено 2730 по формуле =СУММ( Inputs!E5:E8 ). Назовите все пропущенные строки и объясните разницу с полным итогом.

Проверить задание 3.3

Временно замените B10 с 0 на 1, оставив в E10 формулу =B10*C10. Как изменятся E10 и общий итог? Затем восстановите B10 = 0.

Проверить задание 3.4

Полнота диапазона проверяется составом записей и контролируемым изменением данных, а не убедительным видом итогового числа.

К списку задач

Разбор 4. Разобраться, почему 5 и «12» дают разные суммы

Задача: Отличать число от его текстовой записи и не принимать успешное преобразование одного значения за исправность всего столбца.

На листе Inputs подготовьте семь ячеек. F5 — число 5; F6 — текстовое число 12, для его ввода наберите ведущий апостроф и цифры: '12; F7 — действительно пустая ячейка; F8 — число 0; F9 — формула =""; F10 — слово текст; F11 — число 7. В F4 можно написать Смешанные данные.

Апостроф при вводе F6 служит указанием хранить запись как текст; не добавляйте второй апостроф в конце. В F9 находятся знак равенства и две прямые двойные кавычки подряд, без пробела между ними. F7 оставьте нетронутой: пустая ячейка, пустая строка формулы и пробел — разные исходные данные.

Сравниваемые формулы вводите вне F5:F11, например в P5 и P6. В упражнении с заменой F6 числом установите для неё формат Общий и заново введите 12 без апострофа. После опыта верните исходное текстовое значение вводом '12.