Как отследить ошибки Excel —

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

Типы ошибок

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

# DIV / 0 — The # DIV / 0 ошибка произойдет, если операция деления в вашей формуле ссылается на аргумент, который содержит 0 или является пустым.

# N / A — The # N / A ошибка на самом деле не является ошибкой. Это тем более указывает на недоступность необходимого значения. # N / A Ошибка может быть выдана вручную с помощью = NA (). Некоторые формулы также выдают сообщение об ошибке.

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

#НОЛЬ! — Пробелы в Excel указывают на пересечения. Из-за этого возникает ошибка, если вы используете пробел вместо запятой (оператор объединения) между диапазонами, используемыми в аргументах функции. В большинстве случаев вы увидите, что это происходит, когда вы указываете пересечение двух диапазонов ячеек, но пересечение фактически никогда не происходит.

#NUM! — Хотя есть много обстоятельств, #NUM! может появиться ошибка, обычно она вызывается неверным аргументом в функции или формуле Excel. Обычно тот, который производит число, которое является или слишком большим или слишком маленьким и не может быть представлено в рабочем листе.

#REF! — Обычно упоминается как «ссылка», #REF! ошибки могут быть отнесены к любым формулам или функциям, которые ссылаются на другие ячейки. Формулы типа VLOOKUP () могут бросить #REF! ошибка, если вы удаляете ячейку, на которую ссылается формула, или, возможно, вставляете ячейки, на которые ссылаетесь тоже.

#ЗНАЧЕНИЕ! — всякий раз, когда вы видите #ЗНАЧЕНИЕ! ошибка, обычно есть неверный аргумент или используется неправильный оператор. Это обычно видно, когда «текст» передается функции или формуле в качестве аргумента, когда ожидается число.

Отслеживание ошибок

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

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

Предупреждение об ошибке

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

При наведении курсора на кнопку появляется всплывающая подсказка с кратким описанием значения ошибки. В этом же районе будет представлен раскрывающийся список с доступными параметрами:

1. Справка по этой ошибке: откроется окно справки Excel, в котором будет представлена ​​информация о значении ошибки, а также приведены предложения и примеры того, как решить проблему.
2. Показать этапы расчета. Откроется диалоговое окно «Оценка формулы», также находящееся на вкладке «Формулы». Это проведет вас через каждый шаг вычисления, показывая вам результат каждого вычисления.
3. Игнорировать ошибку: Обходит все проверки ошибок для активированной ячейки и удаляет треугольник уведомления об ошибке.
4. Редактировать на панели формул: активируется «Режим редактирования», перемещающий точку вставки в конец формулы на «Панели формул».
5. Параметры проверки ошибок: откроются параметры Excels по умолчанию для проверки и обработки ошибок. Здесь вы можете изменить способ, которым Excel обрабатывает различные ошибки.

Дополнительная обработка ошибок

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

Есть несколько разных способов обработки ошибок. IFERROR () — это полезная формула, обеспечивающая два разных процесса в зависимости от того, присутствует ошибка или нет. Другие варианты используют комбинации формул, такие как IF (ISNUMBER ()). Большую часть времени эта комбинация формул используется с SEARCH (). Мы знаем, что когда Excel возвращает что-то TRUE, оно может быть представлено как 1. Итак, когда вы пишете = IF (ISNUMBER (SEARCH («Hello», A2)) », TRUE, FALSE), вы говорите:« Если вы найдете Hello в A2 возвращает 1, в противном случае возвращает 0. »Еще одна формула, которая пригодится для более поздних версий Excel, — это функция AGGREGATE (). Мы рассмотрим несколько кратких примеров ниже.

Генерация ошибки без использования IFERROR ()

1. В приведенном ниже примере вы увидите, что мы пытаемся вычесть «текст» из суммы другого диапазона. Это могло произойти по разным причинам, но вычитание «текста» из числа, очевидно, не будет работать слишком хорошо.
2. В этом примере он генерирует # ЗНАЧЕНИЕ! ошибка, потому что формула ищет число, но вместо этого получает текст
   
3. В том же примере, давайте использовать IFERROR () и отобразить функцию «Была проблема»
4. Ниже вы можете видеть, что мы обернули формулу в функцию IFERROR () и предоставили значение для отображения. Это базовый пример, но вы можете проявить изобретательность и придумать множество способов справиться с ошибкой, начиная с этого момента, в зависимости от объема формулы и ее сложности.

   

Использование функции AGGREGATE () может быть немного сложным, если вы никогда не использовали ее раньше.

1. Однако эта формула является универсальной и гибкой и является отличным вариантом для обработки ошибок в зависимости от того, что делают ваши формулы.
2. Первый аргумент в функции AGGREGATE () — это формула, которую вы хотите использовать, например SUM (), COUNT () и некоторые другие, как вы видите в списке.
   
3. Вторая часть аргумента — это параметры, которые вы можете включить в формулу в первом аргументе. Ради этого примера и этого урока вы должны выбрать опцию 6 «Игнорировать значения ошибок».
   
4. Наконец, вы просто должны указать диапазон или ссылку на ячейку, для которой будет использоваться формула. Функция AGGREGATE () будет выглядеть примерно так:
   = СОВОКУПНАЯ (2,6, А2: А6)
5. В этом примере мы хотим, чтобы COUNT A2 до A6 и игнорировать любые ошибки, которые могут произойти
6. Чтобы написать пример формулы как другой метод, он будет выглядеть так:
   = ЕСЛИОШИБКА (СЧЕТ (A2: A6),»»)

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

Я призываю вас поиграть с формулами и посмотреть, что лучше всего подходит для вашей ситуации и вашего стиля.