Формулы для таблиц с объяснением каждого шага
Выбирайте формулу по вопросу к данным, а не по длине записи. На небольших примерах видно, что делает каждый аргумент и как проверить ответ до переноса в большую таблицу. Учебные таблицы и файл XLSX показывают сумму, условия и скидку; формулы из руководства можно воспроизвести в своей книге Excel.
Попробовать
Как выбрать функцию по формулировке задачи
Выбор функции начинается с ожидаемого ответа. Для количества записей по условию нужен подсчёт, для денежных сумм по нескольким признакам — суммирование, для адресного значения по ID — поиск. На учебном листе доставок A2:A5 содержит номера Д-11, Д-12, Д-13, Д-14; B — статусы «Готово», «В пути», «Готово», «Готово»; C — зоны «Центр», «Центр», «Север», «Центр»; D — стоимости 500, 700, 600, 800 ₽.
Таблица показывает формулы для русской локали Microsoft 365 с разделителем «;». ПРОСМОТРX доступна в Microsoft 365 и Excel 2021/2024, но отсутствует в Excel 2016/2019. Поиск использует точный уникальный ключ; при дублях он вернёт первое соответствие. Перед использованием перенесите учебные данные в указанные столбцы Excel и проверьте результаты на каждой строке.
| Вопрос | Формула для учебных диапазонов | Ответ |
|---|---|---|
| Сколько готовых доставок? | =СЧЁТЕСЛИ(B2:B5;"Готово") | 3 |
| Сумма готовых доставок в Центре? | =СУММЕСЛИМН(D2:D5;B2:B5;"Готово";C2:C5;"Центр") | 1 300 ₽ |
| Стоимость Д-13? | =ПРОСМОТРX("Д-13";A2:A5;D2:D5;"Не найдено") | 600 ₽ |
Сначала назовите тип ответа: количество, сумма или значение по ключу.
Как посчитать сумму по условию
В учебной таблице курсов столбец C отвечает на вопрос «Оплачено?», а D содержит стоимость. Чтобы сложить стоимость только оплаченных заказов, используется =СУММЕСЛИ(C2:C5;"Да";D2:D5). Сначала функция проверяет признак в C, затем суммирует соответствующие числа из D. Ожидаемый ответ — 14 000 ₽.
Важно, чтобы диапазоны начинались и заканчивались на одинаковых строках. Иначе условие одной записи может сопоставляться сумме другой. Проверьте также сами значения: «Да» с лишним пробелом отличается от принятого справочника. Сумма цен оплаченных заказов не равна сумме реальных поступлений, если в вашей задаче бывают частичные оплаты; для них нужен отдельный числовой столбец.
Как записать проверку условия
Для пояснения состояния заказа запишите =ЕСЛИ(C2="Да";"Доступ открыт";"Ожидаем оплату"). Формула выбирает один из двух результатов на основании проверяемого условия. В этом простом варианте пустая C2 также приводит к ожиданию оплаты. Решите заранее, приемлемо ли такое поведение или отсутствие данных нужно показывать отдельно.
Если пустое значение означает незаполненную карточку, используйте дополнительную проверку: =ЕСЛИ(C2="";"Нет данных";ЕСЛИ(C2="Да";"Доступ открыт";"Ожидаем оплату")). Начинайте с простой версии и добавляйте условия только для реальных различий. Текст внутри кавычек — часть результата, а названия функций и ссылки описывают вычисление.
Когда закреплять ссылку на ячейку
При копировании относительная ссылка меняется вместе с положением формулы. Если стоимость находится в D2, запись =D2*(1-E2) при переносе вниз будет использовать D3 и E3. Это удобно для скидки каждой отдельной строки. В базовом примере скидки равны нулю, поэтому стоимость не изменяется.
Для отдельного опыта запишите общий процент 10% в F1 и используйте =D2*(1-$F$1). Ссылка на стоимость должна двигаться, а ссылка на общий процент — оставаться F1. Знаки доллара закрепляют столбец и строку. У первого курса получится 2 700 ₽. Не вводите 10 вместо 10%, иначе формула получит совсем другое число.
Как найти и проверить ошибку
Проверяйте формулу по частям: исходные значения, условие, диапазоны и арифметику. Для маленькой таблицы посчитайте результат вручную: 3 000 + 5 000 + 6 000 = 14 000. Затем измените один признак оплаты и убедитесь, что сумма меняется на стоимость именно этой строки. Это проверяет смысл, а не только отсутствие сообщения об ошибке.
Примеры записаны для русской локали Excel с разделителем аргументов «;». В другой конфигурации могут использоваться английские имена SUMIF и IF и иной разделитель. Базовые функции есть в Excel 2016–2024 и Microsoft 365; новые функции поиска и массивов нужно проверять по версии отдельно. Число, сохранённое текстом, и включённый режим показа формул — разные причины неожиданного вида ячейки.
Почему нельзя скрывать любую ошибку через ЕСЛИОШИБКА
ЕСЛИОШИБКА заменяет ошибочный результат, но не исправляет его причину. Деление на ноль, отсутствие ключа и повреждённая ссылка требуют разных действий. Если всем трём случаям присвоить ноль, отчёт станет внешне аккуратнее, однако потеряет важные признаки неполноты данных.
Например, известная стоимость двух отправлений равна 400 и 650 ₽, а тариф третьего отсутствует. Подстановка нуля выдаст сумму 1 050 ₽ как будто она полная, хотя это только подтверждённая часть. Отмечайте неполный итог и ищите тариф. Ожидаемое отсутствие ключа можно описать текстом «Не найдено» непосредственно в поиске; сломанную ссылку нужно восстановить. В русской локали Microsoft 365 используйте осмысленное сообщение вместо универсального нуля. После устранения причины проверьте итог повторно — отсутствие видимого кода ошибки само по себе не подтверждает правильность.
| Код или ситуация | Возможная причина | Следующее действие |
|---|---|---|
| #ДЕЛ/0! | Нулевая или пустая база деления | Проверить знаменатель |
| #Н/Д | Ключ не найден | Проверить ключ и полноту справочника |
| #ССЫЛКА! | Удалена используемая ячейка | Восстановить ссылку |
| 400 + 650 + неизвестный тариф | Известна только часть стоимости | 1 050 ₽ — неполный итог |
Обрабатывайте известное отсутствие результата, сохраняя видимость ошибок данных.
Оплаченные заказы онлайн-курсов
Разместите заголовки в A1:E1 и четыре записи в строках 2–5. Суммируется стоимость заказов со статусом «Да»; скидка в базовом наборе равна 0%.
| Курс | Группа | Оплачено | Стоимость | Скидка |
|---|---|---|---|---|
| Основы | А | Да | 3 000 ₽ | 0% |
| Графики | Б | Нет | 4 500 ₽ | 0% |
| Формулы | А | Да | 5 000 ₽ | 0% |
| Анализ | Б | Да | 6 000 ₽ | 0% |
=СУММЕСЛИ(C2:C5;"Да";D2:D5) даёт 14 000. Для контрпримера очистите C2: сумма уменьшится до 11 000. В отдельном опыте поместите 10% в F1; =D2*(1-$F$1) даст 2 700.
Пример с вымышленными данными. Формулы доступны для проверки в Excel.
Как проверить формулу на граничных значениях
Для нового правила проверьте обычное значение, ноль, пропуск, точную границу и текст. В учебной классификации заказ от десяти единиц относится к партии, меньше десяти — к штучному. Пустая ячейка означает отсутствие данных, текст требует исправления типа. Правило предполагает неотрицательное количество; отрицательные значения отсекаются при подготовке данных.
В первой версии после проверок пустоты и типа использовано A2>10. Четыре случая проходят, но ровно десять ошибочно попадает в штучный заказ. Замена на A2>=10 исправляет границу. Для русской локали Microsoft 365 итоговое выражение приведено ниже; ожидаемые результаты задаются до проверки. Матрица демонстрирует логику на конкретных входах и помогает повторить проверку в рабочей книге. При переносе в книгу проверьте адреса и разделитель аргументов.
=ЕСЛИ(A2="";"Нет данных";ЕСЛИ(НЕ(ЕЧИСЛО(A2));"Проверьте тип";ЕСЛИ(A2>=10;"Партия";"Штучно")))
| Ввод A2 | Ожидается | С условием >10 | С условием >=10 |
|---|---|---|---|
| 12 | Партия | Партия | Партия |
| 0 | Штучно | Штучно | Штучно |
| Пустая ячейка | Нет данных | Нет данных | Нет данных |
| 10 | Партия | Штучно — ошибка | Партия |
| Текст «десять» | Проверьте тип | Проверьте тип | Проверьте тип |
Точный порог проверяется отдельной строкой, даже если обычные значения совпали.
Вопросы о выборе и проверке формул
То, что полезно знать перед началом работы.
Почему формула отображается текстом?
Проверьте, начинается ли запись со знака равенства, нет ли перед ним апострофа и не был ли у ячейки текстовый формат во время ввода. После исправления формата иногда требуется заново подтвердить содержимое. Если формулы видны сразу во всём листе, проверьте режим их отображения. Эти причины различаются, поэтому не заменяйте исходные данные наугад.
Когда нужен знак доллара в ссылке?
Когда часть адреса должна оставаться неизменной при копировании. В $F$1 закреплены и столбец F, и строка 1; в F$1 закреплена только строка, а в $F1 — только столбец. Выбирайте фиксацию по смыслу задачи. Для единого процента скидки удобно полностью закрепить ячейку, а адрес цены каждой строки оставить относительным.
Почему используется точка с запятой?
В приведённых примерах она разделяет аргументы функции в русской конфигурации Excel. Конкретный разделитель зависит от региональных настроек среды. Если скопированная формула не принимается, проверьте локаль и подсказку синтаксиса своей версии. Не путайте разделитель аргументов с десятичным разделителем внутри числа: они выполняют разные задачи и не всегда заменяются одним общим поиском.
Как искать ошибку по частям?
Сначала проверьте несколько исходных ячеек и результат простого условия. Затем отдельно проверьте диапазон суммирования и только после этого объединяйте части. Подготовьте ожидаемый результат для двух-трёх строк вручную. Меняйте по одному входному значению: так видно, что именно повлияло на итог. Отсутствие сообщения об ошибке ещё не доказывает, что формула отвечает на нужный вопрос.
Почему функция работает в одной версии и недоступна в другой?
Набор функций зависит от выпуска и обновлений Excel, а названия — также от языка интерфейса. Новая функция может отсутствовать в старой версии даже при корректном синтаксисе. Проверьте раздел совместимости в официальной справке и условия работы получателя файла. Для передачи книги полезно выбрать поддерживаемый вариант формулы и испытать его в целевой среде.