Обзор ошибок, возникающих в формулах Excel

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

Несоответствие открывающих и закрывающих скобок

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

Например, на рисунке выше мы намеренно опустили закрывающую скобку при вводе формулы. Если вы нажмете клавишу Enter, Excel выдаст следующее предупреждение:

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

Ячейка заполнена знаками решетки

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

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

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

В этом случае увеличение ширины столбца уже не поможет.

Ошибка #ДЕЛ/0!

Ошибка #DIV/0! Происходит, когда Excel делит на ноль. Это может быть явное деление на ноль или деление на ячейку, содержащую ноль или пустую.

Ошибка #Н/Д

Ошибка #Н/Д возникает, когда значение для формулы или функции недоступно. Вот несколько случаев, когда возникает ошибка #Н/Д:

Функция поиска не находит соответствия. Например, функция ВПР для точного поиска вернет ошибку #Н/Д, если совпадений не найдено.
Формула прямо или косвенно ссылается на ячейку, в которой отображается значение #Н/Д.
При работе с массивами в Excel, когда аргументы массива меньше результирующего массива. В этом случае в неиспользуемых ячейках результирующего массива будут отображаться значения #Н/Д. Например, на рисунке ниже видно, что результирующий массив C4:C11 больше, чем аргументы массива A4:A8 и B4:B8.

Нажав комбинацию клавиш Ctrl+Shift+Enter, получим следующий результат:

Ошибка #ИМЯ?

Ошибка #ИМЯ? возникает, когда формула содержит имя, которое Excel не понимает.

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

В этом примере имя диапазона не определено.

Адрес указывается без двоеточия:
В названии функции опечатка:

Ошибка #ПУСТО!

Ошибка #ПУСТО! возникает при пересечении двух диапазонов, не имеющих общих точек.

Например, =A1:A10 C5:E5 — это формула, в которой используется оператор пересечения и которая должна возвращать значение ячейки на пересечении двух диапазонов. Поскольку диапазоны не имеют точек пересечения, формула вернет #NULL!.
Также эта ошибка возникнет, если вы случайно пропустите один из операторов в формуле. Например, формула =A1*A2*A3 записывается как =A1*A2 A3.

Ошибка #ЧИСЛО!

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

Например, вместо положительного значения указывается отрицательное значение. Хорошим примером является квадратный корень из отрицательного числа.
Кроме того, #ЧИСЛО! возникает, когда возвращается слишком большое или слишком маленькое значение. Например, формула =1000^1000 вернет именно эту ошибку.

Не забывайте, что Excel поддерживает числовые значения от -1E-307 до 1E+307.

Еще один экземпляр #ЧИСЛО! это использование функции, которая использует метод итерации при вычислении и не может вычислить результат. Хорошим примером таких функций в Excel являются RATE и IRR.

Ошибка #ССЫЛКА!

Ошибка #ССЫЛКИ! возникает в Excel, когда формула ссылается на ячейку, которая не существует или была удалена.

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

Если вы удалите столбец B, формула вернет ошибку #ССЫЛКА!.
Другой пример. Формула в ячейке B2 относится к ячейке B1, то есть к ячейке 1.

Если мы скопируем эту формулу в любую ячейку 1-й строки (например, в ячейку D1), формула вернет ошибку #ССЫЛКА!, поскольку содержит ссылку на несуществующую ячейку.

Ошибка #ЗНАЧ!

#ЗНАЧ ошибка! одна из самых распространенных ошибок в Excel. Происходит, когда значение одного из аргументов формулы или функции содержит недопустимые значения. Самая распространенная #ЦЕННОСТЬ! Они есть!:

Формула пытается применить к тексту стандартные математические операторы.
В качестве аргументов функции используются данные неправильного типа. Например, номер столбца в функции ВПР установлен на число меньше 1.
Аргумент функции должен иметь одно значение, но вместо этого ему назначается весь диапазон. На следующем рисунке диапазон A6:A8 используется в качестве требуемого значения функции ВПР.

Вот и все! Мы разобрали типичные ситуации, когда возникают ошибки в Excel. Зная причину ошибки, исправить ее гораздо проще. Удачи в изучении Excel!