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

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

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

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

Циклические ссылки могут иметь смысл, если у вас включены итеративные вычисления: Параметры — Формулы — Включить итеративные вычисления (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 и ссылки на них в формулах.