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

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

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

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

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

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

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

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

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

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

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

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

Ошибка #Н/Д

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

1. Функция поиска не подходит. Например, функция ВПР для точного поиска вернет ошибку # Н / Д, если совпадений не найдено.
2. Формула прямо или косвенно ссылается на ячейку, в которой отображается значение # Н / Д.
3. При работе с массивами в Excel, когда аргументы массива меньше результирующего массива. В этом случае неиспользуемые ячейки результирующего массива будут отображать значения # N / D. Например, на следующем рисунке вы можете видеть, что результирующий массив 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. Возникает еще один экземпляр ошибки # ЧИСЛО !. — это использование функции, которая использует метод итераций при вычислении и не может вычислить результат. Яркий пример таких функций в Excel — RATE и IRR.

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

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

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

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

   

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

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

   

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

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

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

это все! Мы проанализировали типичные ошибочные ситуации в Excel. Знание причины ошибки значительно упрощает ее устранение. Успехов в изучении Excel!