Формулы Excel по описанию словами

Главная причина, почему чужая формула не работает

Прежде чем менять функцию, проверьте разделитель. Это причина примерно половины случаев «скопировал — не работает».

В русской локали Excel аргументы функции разделяются точкой с запятой. В англоязычной — запятой. Формулы в интернете чаще всего написаны с запятыми, и при вставке в русский Excel они либо дают ошибку, либо остаются текстом в ячейке.

Вторая по частоте причина — десятичный разделитель. В русской локали дробная часть отделяется запятой (3,5), и если данные пришли из выгрузки с точкой (3.5), Excel считает их текстом. Внешне это видно по выравниванию: числа прижимаются вправо, текст — влево. Быстрая проверка — обернуть ячейку в =ЕЧИСЛО(A2).

Третья — русские и английские имена функций. В русском Excel это ВПР, СУММЕСЛИ, ЕСЛИ; в английском — VLOOKUP, SUMIF, IF. Файл, созданный в другой локали, обычно переводит имена сам, а вот вставленная руками формула — нет.

Пять задач, которые закрывают 90% случаев

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

Сложить по условию

«Сколько всего оплачено» — суммируем столбец B там, где в столбце A стоит нужное слово.

Если условий несколько — например, оплачено И в августе, — берётся СУММЕСЛИМН, и порядок аргументов у неё другой: сначала диапазон суммирования, потом пары «где искать — что искать».

Подтянуть значение из другой таблицы

Классическая ВПР: по артикулу найти цену в прайсе на другом листе.

Два места, где ошибаются почти все. Последний аргумент ЛОЖЬ обязателен — он означает «точное совпадение»; без него функция ищет приблизительное и молча возвращает не то. И искомое значение должно быть в первом столбце диапазона: ВПР смотрит только вправо. Если артикул лежит правее цены, ВПР не поможет — нужна связка ИНДЕКС и ПОИСКПОЗ или функция ПРОСМОТРX в новых версиях.

Посчитать процент

Доля от суммы и изменение к прошлому периоду — две разные формулы, которые постоянно путают.

Результат форматируйте как процент, а не умножайте на 100 в формуле, — иначе при смене формата получите 15000%.

Работа с датами

Разница в днях считается простым вычитанием — даты в Excel это числа. Рабочие дни считает отдельная функция, и она умеет исключать праздники.

Условие «если»

«Поставить „просрочено“, если срок прошёл».

Если условий много, не вкладывайте пять ЕСЛИ друг в друга — читать это потом невозможно. Лучше ЕСЛИМН в новых версиях или отдельная таблица соответствий с ВПР.

Как описать задачу, чтобы получить рабочую формулу

Если формулу для вас собирает нейросеть, качество ответа зависит от четырёх вещей в описании. Все четыре — про вашу таблицу, а не про Excel.

Скажите, где что лежит. «В столбце A статус, в B сумма» — этого достаточно. Без этого получите формулу с абстрактными диапазонами, которую придётся переписывать.

Скажите, что должно получиться. Число, текст, дата, признак «да/нет». Одна и та же задача решается по-разному в зависимости от типа результата.

Предупредите про листы. Если данные на другом листе — назовите его. Ссылка на лист в формуле выглядит иначе, и добавить её задним числом сложнее, чем сразу попросить.

Скажите про версию, если она старая. ПРОСМОТРX, ЕСЛИМН и функции динамических массивов есть не везде. Если Excel 2016 — так и напишите, получите совместимый вариант.

Бот «Готово» принимает такое описание обычными словами и возвращает готовую формулу с русскими именами функций и точкой с запятой. Команда — /formula, первые две формулы бесплатно.

Что означают ошибки и как их чинить

Excel сообщает о проблеме довольно точно, если знать код.

• #Н/Д — не найдено. В 90% случаев виноваты данные, а не формула: лишний пробел в конце значения, число сохранено как текст, разный регистр. Лечится функцией СЖПРОБЕЛЫ и проверкой типа.
• #ЗНАЧ! — неподходящий тип. Пытаетесь сложить текст с числом или подставили диапазон туда, где ждут одно значение.
• #ДЕЛ/0! — деление на ноль или на пустую ячейку. Оборачивается в ЕСЛИОШИБКА, но сначала подумайте, не прячете ли вы этим реальную проблему в данных.
• #ССЫЛКА! — формула ссылается на удалённые ячейки. Обычно появляется после удаления столбца.
• #ИМЯ? — Excel не узнал имя функции. Либо опечатка, либо функция из другой локали, либо её нет в вашей версии.
• Формула показывается текстом — ячейка имеет текстовый формат. Смените формат на «Общий» и перевведите формулу.

Отдельно про ЕСЛИОШИБКА: она прячет ошибку, а не исправляет. Ставить её стоит только после того, как вы поняли, почему ошибка возникала, — иначе рискуете спрятать неверный расчёт и отчитаться по нему.

Как проверить формулу до того, как поверить

Формула, которая вернула число, ещё не формула, которая вернула правильное число. Три проверки занимают минуту и ловят почти всё.

1. Посчитайте одну строку руками. Возьмите первую строку и проверьте результат на калькуляторе. Если сходится — логика верна.
2. Проверьте край. Пустая ячейка, ноль, отрицательное число, строка без пары в справочнике. Ошибки живут именно в краях, а не в середине.
3. Сверьте итог с независимой величиной. Сумма по категориям должна совпасть с общей суммой. Расхождение сразу покажет, что часть строк не попала в условие.

И зафиксируйте диапазоны знаком доллара ($A$2:$B$500) до того, как протянете формулу вниз. Съехавший при копировании диапазон — самая незаметная и самая дорогая ошибка в таблицах: результат выглядит правдоподобно и не подсвечивается никак.

Excel и Google Таблицы: где расходятся

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

В Google Таблицах имена функций всегда английские, независимо от языка интерфейса. Разделитель аргументов зависит от настроек локали документа, а не от вашей системы. И у Google есть свои функции, которых нет в Excel, — например QUERY и ARRAYFORMULA; обратно они не переносятся.

Если формула нужна для Google Таблиц — скажите об этом сразу, вариант будет другим.

Чек-лист

• Разделитель аргументов — точка с запятой (для русской локали).
• Числа выровнены вправо, то есть Excel видит их числами, а не текстом.
• В ВПР последний аргумент — ЛОЖЬ, искомое значение в первом столбце диапазона.
• Диапазоны закреплены через $ до протягивания формулы.
• Первая строка проверена вручную.
• Проверены край и пустые значения.
• Итог сверен с независимой суммой.
• ЕСЛИОШИБКА поставлена осознанно, а не чтобы спрятать проблему.

Смежные задачи: если исходные данные приходят в PDF и их нужно сначала превратить в таблицу — см. разбор конвертера. Если по этим расчётам потом делается отчёт — как собрать презентацию и как оформить документ. Как всё это работает прямо в мессенджере — на странице «Нейросеть в MAX».