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

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

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

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

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

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

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

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

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

       

      

       

      ...или изменить числовой формат ячейки.

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

    

   

    

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

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

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

Ошибка #Н/Д

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

1. Функция поиска не находит совпадений. Например, функция ПОИСК ВВЕРХ для точного поиска вернет ошибку #Н/Д, если совпадений не найдено.

    

   

    

2. Формула прямо или косвенно ссылается на ячейку, в которой отображается значение #Н/Д.

    

   

    

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

    

   

    

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

    

   

    

Ошибка #ИМЯ?

Неправильное имя? возникает, когда формула содержит имя, которое Excel не понимает.

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

    

   

    

2. Функция ссылается на доменное имя, которое не существует или написано с ошибкой:

    

   

    

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

1. Адрес указывается без разделителя:

    

   

    

2. В названии функции опечатка:

    

   

    

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

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

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

    

   

    

2. Эта ошибка также возникает, если вы случайно пропустите один из операторов в формуле. Например, формула =A1*A2*A3 записывается как =A1*A2 A3.

    

   

    

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

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

1. Например, вместо положительного значения вводится отрицательное значение. Хорошим примером является квадратный корень из отрицательного числа.

    

   

    

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

    

   

    

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

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

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

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

1. Например, на рисунке ниже показана формула, которая суммирует значения двух ячеек.

    

   

    

   Если вы удалите столбец B, формула вернет ошибку #ССЫЛКА!.

    

   

    

2. Другой пример. Формула в ячейке B2 относится к ячейке B1, т е к строке ячейки 1 выше.

    

   

    

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

    

   

    

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

Ошибка № ЗНАЧ! одна из самых распространенных ошибок, возникающих в Excel. Это происходит, когда значение одного из аргументов формулы или функции содержит недопустимые значения. Наиболее распространенные ошибки #VALUE!:

1. Формула пытается применить к тексту стандартные математические операторы.

    

   

    

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

    

   

    

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

    

   

    

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