Отладка формул и ошибки

Книга: Магия таблиц. 100+ приемов ускорения работы в Excel (и немного в Google Таблицах)

Назад: Функция LAMBDA и вспомогательные функции

Какие бывают ошибки в формулах

Начнем с ошибки ИМЯ и на ее примере обсудим, как ошибка может передаваться дальше по цепочке формул.

#ИМЯ! / #NAME! — ошибка в имени функции, именованном диапазоне, ссылке на диапазон. Пробегитесь по всем этим пунктам в вашей формуле. Кроме того, не забывайте, что текстовые значения указываются внутри формул в кавычках.

В следующем примере — ошибка в названии функции, не хватает второй буквы «м».

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

Помните, что ошибки в формулах могут быть и в том случае, если эти ошибки есть в ячейках, на которые формулы ссылаются. Надо разматывать всю цепочку. На следующем скриншоте в формуле нет ничего криминального: к ячейке A4 прибавляем число 10. Но в ячейке A4 ошибка #ИМЯ? — она и отображается в результате расчета новой формулы.

Отследить, на какие ячейки ссылается формула, можно следующими способами.

1. Войти в режим редактирования формулы (двойной щелчок по ячейке или F2 или щелчок по строке формул).

Ссылки выделяются цветом — в формуле и собственно на листе

Клавиша F2 при работе с формулой переключает два режима: либо клавиши со стрелками перемещают курсор в самой формуле, либо позволяют ссылаться на ячейки, двигаясь от одной к другой с помощью стрелок и сочетаний (Ctrl + стрелки — перемещение до конца диапазона, Ctrl + Shift + стрелки — выделение до конца диапазона).

2. Включить режим «Показать формулы», который мы вкратце упоминали ранее, — на вкладке ленты «Формулы». Будут видны все формулы, значения будут видны «как есть» (без форматирования). Ссылки будут выделяться цветом для формулы в активной ячейке.

3. Стрелки трассировки: там же на вкладке «Формулы» — «Влияющие ячейки» (Trace Precedents).

Нажимаем один раз и видим, куда ссылается формула (на ячейку B4).

После второго нажатия увидим, куда ссылается формула в B4.

Чтобы убрать стрелки, нажмите соответствующую кнопку на той же вкладке: Убрать стрелки (Remove Arrows).

Рассмотрим другие ошибки в формулах.

#ЗНАЧ! / #VALUE! — арифметические операции с разными типами данных — сложение текста и чисел.

#ССЫЛ! / #REF — ссылка на несуществующий диапазон. Появляется, когда ячейка, на которую ссылалась формула, была удалена (вместе со строкой или столбцом, например) или когда вы пытаетесь, протягивая формулу, сослаться на ячейку A0, например (то есть на ячейку за пределами листа). Также возникает, если в функции СМЕЩ / OFFSET высота или ширина диапазона задана как ноль, что невозможно.

В ячейке B4 ссылка на A1. При попытке протянуть такую формулу вверх появляется ошибка #ССЫЛ! так как выше уже ничего нет

Циклическая ссылка (в Google Таблицах выглядит как #ССЫЛ! / #REF!). Возникает, если формула ссылается на собственное значение. При возникновении смотрите на диапазоны в формуле (бывает, что вы ссылаетесь на весь столбец, а формула стоит в нем же под таблицей, и т. д.)

В Excel при попытке ввести формулу с циклической ссылкой появится сообщение об ошибке (оно же будет появляться при открытии книги, в которой есть хотя бы одна циклическая ссылка):

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

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

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

Циклические ссылки могут иметь смысл, если у вас включены итеративные вычисления: Параметры — Формулы — Включить итеративные вычисления (Formulas — Enable iterative calculation).

С включенными итеративными вычислениями функция СУММ возвращает 600, так как к вычисленному на предыдущем шаге результату прибавляется новый. В параметрах по умолчанию задано 100 итераций (вычислений).

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

Если вы не уверены, что вам нужны итеративные вычисления для определенной задачи, то включать их не нужно. В большинстве случаев циклическая ссылка — это ошибка, на которую нужно реагировать (убирать ссылку на формулу из нее самой).

#ЧИСЛО! / #NUM! — недопустимое число. Например, при попытке возвести 10 в степень 30 000 получится слишком большое число.

#Н/Д / #N/A — значение не найдено. Эта ошибка характерна для функций поиска: ВПР / VLOOKUP и ГПР / HLOOKUP, ПОИСКПОЗ / MATCH, ПОИСКПОЗX / XMATCH, ПРОСМОТРX / XLOOKUP. Либо значения действительно нет в таблице и тогда это «нормальная» ошибка, либо оно введено по-разному в исходной таблице и в таблице, откуда оно берется для поиска.

#ДЕЛ/0! / #DIV/0! — деление на ноль.

И это случается не только прямолинейно, в случае деления на ноль, но и, например, с функциями для расчета среднего СРЗНАЧЕСЛИ(МН) / COUNTIF(S). Допустим, по вашим критериям не найдено ни одного условия, а в логике расчета среднего арифметического заложено деление, и в такой ситуации деление будет именно на ноль (найденных по критериям значений).

Ошибка #ДЕЛ/0! / #DIV/0! при расчете среднего, потому что под заданный критерий не попадает ни одно значение

НЕКОТОРЫЕ ТИПОВЫЕ ПРОБЛЕМЫ С ФОРМУЛАМИ

Системная ошибка (из перечисленных выше) — это еще ладно, во всяком случае Excel и Google Таблицы вам о ней сигнализируют. Бывают в сложных формулах и не такие явные ошибки. В результате расчета нет ошибок, но есть ноль или неправдоподобное / явно ошибочное число / текст / синтаксическая ошибка. Как искать причины, на что обращать внимание?

Функции ВПР / VLOOKUP, ПОИСКПОЗ / MATCH: если есть сомнения, что функция «тянет» все корректно, проверяйте, точно ли вы указали последний аргумент как ЛОЖЬ (ноль, 0)? Если нет, будет по умолчанию 1, ИСТИНА (интервальный, а не точный поиск). И в случае с поиском текстовых значений возникнут ошибки.

Сравнение данных, поиск данных (те же ВПР и прочие): если у вас ошибка Н/Д, может быть проблема в разных форматах данных. Например, в исходнике, который вы импортируете, артикулы текстового формата, а у вас в вашей таблице — числового. Проверить можно с помощью функций ЕТЕКСТ / ISTEXT, ЕЧИСЛО / ISNUMBER.

Кроме того, всегда есть риск банальных ошибок ввода данных — лишние пробелы, перепутанные кириллица/латиница, сокращения. Для предотвращения таких ошибок используйте проверку данных. А для отлавливания — функцию УНИК / UNIQUE (выводите список уникальных значений и смотрите, есть ли там разные варианты написания одного и того же значения).

Если вдруг ВПР или другая функция в упор не находит значение, а визуально они кажутся одинаковыми, не забывайте, что всегда можно сравнить две ячейки формулой (=A1=A2) или посмотреть, одинаковой ли они длины по количеству символов (с помощью функции ДЛСТР / LEN).

Убрать лишние пробелы (до и после текстовой строки и все, что свыше одного пробела между слов) помогает функция СЖПРОБЕЛЫ / TRIM.

Забыли протянуть формулу / изменить диапазон: по возможности используйте таблицы в Excel и ссылки на них в формулах.

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

=HYPERLINK("mailto: "&E2&"?subject=Тема письма";"Письмо")

Вместо такой формулы сделайте ее по открытому диапазону E2:E, и она будет работать бесконечно для любого количества строк. Чтобы в пустых строках ничего не отображалось, добавьте проверку на пустоту ячейки с имейлом (с помощью ЕСЛИ / IF).

Чтобы обрабатывать весь массив, нужно всю формулу поместить внутрь функции ArrayFormula (также можно нажать Ctrl + Shift + Enter, как для ввода формулы массива в Excel, и эта функция появится автоматически):

=ArrayFormula(IF(F2:F="";""; HYPERLINK("mailto: "&E2:E&"?subject=Подключение тарифа";"Письмо")))

Формула в одной ячейке, а дальше во всем столбце ссылки появляются автоматом — только если есть имейл.

ЧТО ПОМОЖЕТ НАПИСАТЬ/РАЗОБРАТЬ/ПОЧИНИТЬ СЛОЖНУЮ ФОРМУЛУ

Принцип луковицы

Так этот подход назвал эксперт по Google Таблицам и автор книги Spice Up Your Sheet Life Бен Коллинз. Идея в следующем: вместо того чтобы с ходу прописывать сложную формулу, сначала прописываем часть, смотрим, как она работает и правильно ли вычисляет промежуточное значение, затем ссылаемся на эту формулу из другой формулы, в которой она будет выступать одним из аргументов, и, если все заработает, удаляем столбец с промежуточной формулой, а ее переносим в итоговую формулу (на то место, где мы на нее ссылались).

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

В приведенном выше примере с гиперссылкой можно сначала сформировать текстовую формулу, которая будет соединять mailto и адрес электронной почты из ячейки, и сослаться на ячейку с этой формулой из ГИПЕРССЫЛКИ. И если все заработает, уже потом скопировать первую формулу и вставить вместо ссылки на ячейку с ней.

Переход на следующую строку в формуле

В строке формул можно переходить на следующую строку с помощью Alt + Enter. Это позволяет визуально разделить отдельные фрагменты/функции, тогда формулу будет проще воспринимать (вашим коллегам и вам самим в будущем, когда вы уже забудете ее логику).

Это может помочь, если у вас уже «многоэтажная» формула, а в ней возникает синтаксическая ошибка. Обратите внимание, что высоту строки формул можно менять: достаточно потянуть за нижнюю границу, удерживая нажатой левую кнопку мыши.

Также можно пробелами ставить отступы, если это поможет вам с восприятием формулы.

Клавиша F9

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

F9 можно использовать, чтобы посмотреть, какой результат возвращает один из промежуточных этапов формулы, что мы получаем в ней на входе.

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

Можно вычислить значение отдельной ячейки.

Можно вычислять и значение отдельной функции.

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

В Google Таблицах это реализовано в виде всплывающей подсказки, которую можно отключить той же клавишей F9 (или нажатием на крестик справа сверху).

В Excel всплывающие подсказки, показывающие, чему равен фрагмент формулы, появились только в 2023 году (то есть их нет в версиях вплоть до Excel 2021, только в Microsoft 365).

После нажатия F9 вычисленный фрагмент становится значением, и нужно выйти через Esc, чтобы он так и не остался фиксированным значением, а подсказка просто отображает значение выделенного фрагмента, как в Google Таблицах.

Можно посмотреть, чему равны:

• одна из функций в формуле;

• сочетание функций, выражение внутри формулы;

• что хранится в той или иной ячейке;

• какой массив данных хранится в диапазоне или вычисляется функцией/частью формулы.

Отключаются и включаются подсказки сочетанием Ctrl + Alt + P.

Но иногда превратить диапазон в значения — именно то, что нужно! F9 можно применять и так. Если выделить диапазон и нажать F9, то вместо ссылки в формуле будет массив из значений, то есть тот же самый диапазон, но уже «внутри формулы». И тогда ваша формула будет работать без вспомогательной таблицы. Если вы хотите избавиться от такой таблицы, то этот способ вам подойдет.

Файл с примером: F9 и ВПР.xlsx

Так окажется выглядеть формула после нажатия F9 — таблица в строках 11–14 уже будет не нужна.

Пошаговое вычисление

Когда в формуле применяется несколько шагов (как правило, несколько разных функций), можно использовать инструмент «Вычислить формулу» (Evaluate Formula), чтобы проследить ход вычисления по порядку.

На втором шаге эта формула будет выглядеть так. Вычислено название («Номенклатура») искомого товара.

А через несколько шагов формула будет выглядеть так (найдены строка и столбец с нужным товаром и заголовком, которые выступают аргументами функции ИНДЕКС).

Окно контрольного значения

Если вам нужно всегда видеть, чему равно значение в какой-нибудь ячейке, даже если вы работаете на другом листе, — добавьте эту ячейку в окно контрольного значения.

Лента инструментов: Формулы — Окно контрольного значения (Formulas — Watch Window).

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

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

Назад: Функция LAMBDA и вспомогательные функции

Дальше: Анализ данных