10 ошибок в Excel и Google Таблицах

Ошибки в Excel и Google Таблицах встречаются даже в простых формулах. Причины могут быть разными: неверные ссылки, опечатки, неправильный формат данных. Из-за этого много времени уходит на решение проблемы, а не на саму работу. 

Разобрали 9 самых частых ошибок и показали, как быстро их обнаружить и исправить, чтобы формулы работали правильно.

Ошибка 1: #ДЕЛ/0! или #DIV/0!

Ошибка появляется, когда формула пытается разделить число на ноль. Например, в таблице анализа рекламных кампаний есть формула, которая делит сумму расходов на количество кликов. Поскольку рекламная кампания только началась, она еще не набрала ни одного клика. Формула пытается поделить на ноль и выводит #ДЕЛ/0!.

Чтобы избежать ошибки, нужно проверить, не равен ли делитель нулю. Можно использовать формулу:

=ЕСЛИ(C2=0;0;B2/C2)

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

Ошибка 2: #Н/Д (#N/A)

Ошибка #Н/Д появляется, когда функция не может найти нужное значение в указанном диапазоне. Это бывает при работе с ВПР, ИНДЕКС и ПОИСКПОЗ или любыми формулами поиска. 

Например, функция ищет название рекламной кампании, которой нет в таблице. Возможно, кампанию переименовали или удалили. Если формула не находит совпадения, она возвращает #Н/Д.

Чтобы не допустить ошибку, нужно убедиться, что искомое значение есть в диапазоне поиска и написано так же, как в формуле. Если строка в столбце A совпадает с тем, что указано в формуле, ВПР находит нужное значение и подставляет результат без ошибки.

Ошибка 3: #ЗНАЧ! или #VALUE!

Ошибка #ЗНАЧ! появляется, когда формула не может выполнить действие из-за некорректного типа данных. Такое происходит, если в ячейке вместо числа стоит текст, лишний символ или дата в неправильном формате. Из-за этого формула не может выполнить вычисление и возвращает #ЗНАЧ!.

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

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

Иногда числа копируют из других отчетов в виде текста, и это тоже может привести к ошибке. Тогда нужно зайти в раздел «Формат» и поменять формат ячейки на «Числовой». 

Ошибка 4: #ССЫЛКА! или #REF!

Ошибка #ССЫЛКА! появляется, когда формула ссылается на ячейку, которая больше не существует. Это происходит, если удалить столбец или строку, на которую указывала формула, или случайно перезаписать диапазон в расчетах. 

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

Затем из таблицы удалили столбец с расходами, но формула в соседнем столбце по-прежнему пытается умножить значение из этого столбца на коэффициент. Поскольку исходной ячейки больше нет, формула не может выполнить вычисление и выводит #ССЫЛКА!.

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

Ошибка 5: #ИМЯ? или #NAME?

Ошибка #ИМЯ? появляется, когда таблица не распознает название функции или текст внутри формулы. Это бывает из-за опечатки в названии функции, неправильных кавычек или лишнего символа.

Например, пользователь вместо функции «СУММ» указал «СУМ», или использовал в формуле кавычки-елочки «» вместо так кавычек-лапок “”. Таблица не может распознать такую запись и выводит #ИМЯ?.

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

Ошибка 6: ПУСТО! или #NULL!

Ошибка #ПУСТО! появляется, когда формула не может найти пересечение между диапазонами. Например, в формуле расчета суммы между диапазонами B2:С6 и С5:С6 стоит пробел, который является оператором пересечения. Он пытается найти общие ячейки для указанных диапазонов:

=СУММ(B2:C6 C5:C6)

Но если диапазоны указаны неправильно, и между ними формула не находит пересечений, то появляется ошибка #ПУСТО!.

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

Ошибка 7: #ЧИСЛО! или #NUM!

Ошибка #ЧИСЛО! появляется, когда формула не может выполнить расчет из-за некорректного результата: отрицательного числа, даты.

Например, такое бывает в отчетах, где дата начала рекламной кампании указана позже даты завершения. Формула РАЗНДАТ пытается посчитать разницу, но получает отрицательное значение и выводит ошибку #ЧИСЛО!.

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

Ошибка 8: #####

Такая ошибка появляется, когда таблица не может отобразить значение в ячейке. Чаще всего это происходит из-за того, что столбец слишком узкий. Число, дата или результат формулы не помещается по ширине, и вместо значения появляется «#####».

Например, в отчете указаны даты начала и завершения рекламных кампаний. Значения корректные, но столбец слишком узкий. Поэтому таблица не может отобразить результат и заменяет его символами «#####».

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

Ошибка 9: #ERROR!

Ошибка #ERROR! появляется только в Google Таблицах. Обычно она возникает из-за лишнего символа, неправильных кавычек, пропущенной скобки или ошибки в выражении.

Например, пользователь случайно поставил в формуле лишний знак перед числом или забыл закрыть скобку. Формула получается неполной, и Google Таблицы не могут понять, что именно нужно вычислить, и появляется #ERROR!.

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

=FILTER(A2:A6;C2:C6>0)

После исправления таблица возвращает отфильтрованные значения.

Ошибка 10: #ПЕРЕНОС! или #SPILL!

Ошибка #ПЕРЕНОС! появляется в Excel, когда формула пытается вернуть несколько значений, но рядом нет свободного места для вывода результата. Такое бывает при работе с функциями FILTER (ФИЛЬТР), SORT (СОРТ), ВПР (VLOOKUP). Например, в одной из ячеек стоит текст, число или пробел, и формула не может вставить результат расчета.

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

Иногда ошибка #ПЕРЕНОС! появляется из-за того, что формула пытается вывести намного больше значений, чем помещается на листе. 

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

=ВПР(A:A; A:C; 2; ЛОЖЬ)

Из-за ссылки на весь столбец A формула пытается вернуть результат для всех номеров сразу, а это более миллиона строк. Такой массив не помещается на лист, поэтому появляется ошибка #ПЕРЕНОС!.

Чтобы решить проблему, нужно ограничить диапазон и указать только те строки, которые действительно используются в расчетах. Например:

=ВПР(A2:A7; A:C; 2; ЛОЖЬ)

В этом случае Excel вернет массив только из шести строк, и формула не вызовет ошибку.

Как решить проблему: формула ЕСЛИОШИБКА или IFERROR

Формула ЕСЛИОШИБКА помогает заменить любое сообщение об ошибке — #ДЕЛ/0!, #Н/Д, #ЧИСЛО!, #ССЫЛКА!, #ИМЯ?, #ERROR! — на понятное значение. Это помогает избежать ошибок в формулах в случаях, когда в данных есть пустые значения или временно стоят некорректные числа. Формат функции:

=ЕСЛИОШИБКА(выражение; значение_если_ошибка)

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

Например, в таблице есть расчет показателя CPC. Формула делит затраты на рекламную кампанию на количество кликов. Если кликов нет, возникает ошибка #ДЕЛ/0!.

Чтобы избежать этого, можно обернуть формулу в ЕСЛИОШИБКА. Она проверяет вычисление и возвращает указанное значение, если произошла ошибка. Например, вместо ошибки формула может отобразить ноль:

=ЕСЛИОШИБКА(B2/C2; 0)

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