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

данными; lemur — накопленный итог; revenue — очередное значение из массива (выручка за конкретную дату); СМЕЩ(…) — ссылка на ячейку слева от ячейки с выручкой:

=SCAN(0;Выручка2[Выручка];

LAMBDA(lemur;revenue;

ЕСЛИ(СМЕЩ(revenue;0;-1;1;1)="Лемур";revenue + lemur;lemur)))

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

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

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

#ИМЯ! / #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: если есть сомнения, что функция «тянет»