Excel тип ошибки функция

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

Как определить тип ошибки в формуле Excel

Пример 1. Вывести числовые обозначения восьми распространенных ошибок в Excel.

Вид таблицы данных:

Для решения выделим диапазон ячеек B2:B9 и запишем следующую формулу:

• A2:A9 – диапазон ячеек, содержащих коды ошибок, для которых будут найдены их числовые представления.

Как видно выше на рисунке функция возвращает для каждой ошибки ее код в Excel.



Пример определения и обработки ошибок в Excel

Пример 2. Вычислить значение квадратно корня для каждого числа из диапазона значений, хранящихся в таблице. Если число отрицательное, может быть возвращен код ошибки #ЧИСЛО!. В этом случае следует рассчитать корень квадратный для модуля числа. Некоторые ячейки могут содержать текст. В этом случае необходимо вернуть значение 0.

Вид таблицы данных:

Для решения используем следующую формулу массива:

Выражение состоит из формулы ЕСНД, проверяющей результат выполнения функции ЕСЛИ, и нескольких функций ТИП.ОШИБКИ, вычисляющих коды возможных ошибок (3 – ошибка типа данных, 6 – введено некорректное число). В результате проверки нескольких условий функциями ЕСЛИ может быть сгенерирован код для типа #Н/Д, возвращаемый данной функцией, если ошибка не возникла. Перехват данной ситуации выполняет функция ЕСНД.

В результате получим следующие значения:

Данный пример приведен для наглядной демонстрации возможностей функции. В данном случае рациональнее использовать Е-функции (функции проверки условий, например, ЕОШИБКА).

Описание параметров и аргументов функции ТИП.ОШИБКИ в Excel

Функция имеет следующую синтаксическую запись:

=ТИП.ОШИБКИ(значение_ошибки)

• значение_ошибки – единственный аргумент (обязательный для заполнения), принимающий данные кода (например, #ССЫЛ) или ссылку на ячейку, содержащую результат выполнения выражения или функции.

Данную функцию зачастую используют в качестве аргумента функции ЕСЛИ для вывода поясняющей текстовой строки в случаях, если был сгенерирован код.

Интерпретация результатов выполнения функцией:

• 1 – для #ПУСТО! (возникает в случае, если оператор пересечения диапазонов не обнаружил пересечений);
• 2 – для #ДЕЛ/0! (возникает при делении на число 0);
• 3 – для #ЗНАЧ! (возникает при вводе данных неверного типа);
• 4 – для #ССЫЛКА! (при отсутствии ячейки или диапазона ячеек, на которые ссылается формула);
• 5 – для #ИМЯ? (при использовании нераспознанных имен);
• 6 – для #ЧИСЛО! (возникает при вводе недопустимых чисел);
• 7 – для #Н/Д (возникает в случае выхода за диапазон допустимых значений);
• 8 – для #ОЖИДАНИЕ_ДАННЫХ (возникает при выполнении функций, когда найдены не все данные).

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

Вчера в марафоне 30 функций Excel за 30 дней мы искали значения с помощью функции LOOKUP (ПРОСМОТР). Сегодня мы вновь воспользуемся этой функцией для работы над ошибками.

17-й день марафона мы посвятим исследованиям функции ERROR.TYPE (ТИП.ОШИБКИ). Она способна распознавать типы ошибок, а Вы, в свою очередь, можете использовать эту информацию, чтобы устранить их.

Итак, рассмотрим информацию и примеры использования функции ERROR.TYPE (ТИП.ОШИБКИ) в Excel. Если у Вас есть дополнительная информация или примеры, пожалуйста, делитесь ими в комментариях.

Функция 17: ERROR.TYPE (ТИП.ОШИБКИ)

Функция ERROR.TYPE (ТИП.ОШИБКИ) определяет тип ошибки по номеру или возвращает #N/A (#Н/Д), если ошибка не найдена.

Как можно использовать функцию ERROR.TYPE (ТИП.ОШИБКИ)?

При помощи ERROR.TYPE (ТИП.ОШИБКИ) Вы можете:

• идентифицировать тип ошибки.
• помочь пользователям исправить возникающие ошибки.

Синтаксис ERROR.TYPE (ТИП.ОШИБКИ)

Функция ERROR.TYPE (ТИП.ОШИБКИ) имеет вот такой синтаксис:

ERROR.TYPE(error_val)
ТИП.ОШИБКИ(значение_ошибки)

• error_val (значение_ошибки) – та самая ошибка, которую надо идентифицировать.
• коды, которые возвращает функция ERROR.TYPE (ТИП.ОШИБКИ):
  • 1 … #NULL! (#ПУСТО!)
  • 2 … #DIV/0! (#ДЕЛ/0!)
  • 3 … #VALUE! (#ЗНАЧ!)
  • 4 … #REF! (#ССЫЛ!)
  • 5 … #NAME? (#ИМЯ?)
  • 6 … #NUM! (#ЧИСЛО!)
  • 7 … #N/A (#Н/Д)
  • #N/A (#Н/Д) … любое другое значение

Ловушки ERROR.TYPE (ТИП.ОШИБКИ)

Если значение аргумента error_val (значение_ошибки) не является ошибкой, результатом функции ERROR.TYPE (ТИП.ОШИБКИ) будет сообщение об ошибке #N/A (#Н/Д). Вы сможете избежать этого, если воспользуетесь функцией ISERROR (ЕОШИБКА), чтобы проверить наличие ошибки, как это показано в примере 2.

Пример 1: Определяем тип ошибки

При помощи функции ERROR.TYPE (ТИП.ОШИБКИ) Вы можете проверить ячейку, чтобы определить, какая в ней содержится ошибка. Если в ячейке нет ошибки, то вместо числового кода ошибки будет возвращено значение #N/A (#Н/Д).

=ERROR.TYPE(B3)
=ТИП.ОШИБКИ(B3)

В этом примере ячейка B3 содержит #VALUE! (#ЗНАЧ!), поэтому тип ошибки равен 3.

Пример 2: Помогаем пользователям разобраться с ошибками

Сочетая ERROR.TYPE (ТИП.ОШИБКИ) с другими функциями, Вы можете помочь пользователям исправить ошибки, появляющиеся в ячейке. В этом примере в ячейках B3 и C3 должны быть введены числа. Если введён текст, результатом в D3 будет сообщение об ошибке #VALUE! (#ЗНАЧ!). Если в ячейке C3 введён ноль, результатом будет сообщение об ошибке #DIV/0 (#ДЕЛ/0).

В ячейке D4 функция ISERROR (ЕОШИБКА) проверяет наличие ошибки, а ERROR.TYPE (ТИП.ОШИБКИ) возвращает номер этой ошибки. Функция LOOKUP (ПРОСМОТР) находит в таблице кодов ошибок подходящее к данной ситуации сообщение с подсказкой, как исправить ошибку, и показывает его пользователю.

=IF(ISERROR(D3),LOOKUP(ERROR.TYPE(D3),$B$9:$B$15,$D$9:$D$15),"")
=ЕСЛИ(ЕОШИБКА(D3);ПРОСМОТР(ТИП.ОШИБКИ(D3);$B$9:$B$15;$D$9:$D$15);"")

Вот таблица соответствия числовых кодов ошибки и выводимых сообщений:

The ERROR.TYPE function is an Information formula that returns an index number corresponding to a specific error value in Microsoft Excel. If there are no errors in the target cell(s), the function returns another error value, #N/A. This function can be especially useful in troubleshooting and error handling in your Excel models, as it can provide a quick overview of the existing issues. In this guide, we’re going to show you how to use the ERROR.TYPE function and also go over some tips and error handling methods.

Supported versions

• All Excel versions

ERROR.TYPE Function Syntax

Arguments

Examples for Identifying Excel Errors

Use the this function to get a number value that indicates a specific error value. The return values and types are given in the table below:

To use this formula, all you need to to is to set a formula containing cell reference to the error_val argument to test its result. The result will give you the index number for the error type in the target cell.

Download Workbook

Tips

• You can combine the ERROR.TYPE function with IF or IFNA functions for even better error handling in your Excel models.
• Other error related functions of Excel include:
  • ISERROR function tests the error values and returns a Boolean (TRUE / FALSE) value based on the outcome
  • ISERR function tests error values and returns a Boolean (TRUE / FALSE) value based on the outcome
• Please see Reducing Formula Errors in Excel to learn more about error-handling methods and workarounds.

Issues

• If the error_val argument doesn’t contain any errors, the function returns the #N/A error value.

Функция ТИП.ОШИБКИ возвращает номер, соответствующий одному из возможных значений ошибок в Microsoft Excel, или значение ошибки #Н/Д, если ошибка отсутствует.

Описание функции

Возвращает номер, соответствующий одному из возможных значений ошибок в Microsoft Excel, или значение ошибки #Н/Д, если ошибка отсутствует. Функцию ТИП.ОШИБКИ можно использовать в функции ЕСЛИ для проверки значения ошибки и возврата строки текста вместо соответствующего значения ошибки.

Синтаксис

=ТИП.ОШИБКИ(значение_ошибки)

Аргументы

Пример

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

Итак, начнём!

Какие бывают ошибки?

Давайте рассмотрим каждый тип ошибки в Excel и из-за чего они возникают.

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

Эта ошибка возникает тогда, когда Excel не может «подгрузить» значение. Например, когда его в ячейке нет.

На картинке ниже, я пытаюсь посчитать значение, которого в табличке не существует (пустая ячейка):

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

Собственно, из названия функции все понятно. Ошибка возникает, когда вы пытаетесь разделить что-либо на 0.

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

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

Пример ниже:

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

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

Я удалил столбец из нашей таблички и получилось это:

Ошибка #ИМЯ? (#NAME?)

Данная ошибка является ошибкой имени (понятно из названия), имени функции, чаще всего. Например вы хотите вызвать функцию ВПР, а вызываете ВП.

Пример на картинке ниже:

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

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

А еще эта ошибка может возникать, когда вы пытаетесь рассчитать то, что невозможно рассчитать. Например, вычислить корень из отрицательного числа.

Мы рассмотрели лишь самые простые примеры, но я вставил их в эту статью для того, чтобы вы поняли сам тип ошибок. Конечно у вас могут быть другие ситуации, при которых вы получаете вышеуказанные ошибки. Итак, что же с ними делать?

Функция ЕСЛИОШИБКА хороший метод фильтрации этих ошибок.

Функция ЕСЛИОШИБКА

Итак, как понятно из названия, функция обрабатывает ошибку и возвращает указанное значение, если в результате выполнения какой-либо функции возникла ошибка.

Синтаксис

=ЕСЛИОШИБКА(функция; значение_если_ошибка)

Входные аргументы

• функция — это часть, которая в случае возникновения ошибки будет обработана;

• значение_если_ошибка — значение которое будет результатом выполнения функции ЕСЛИОШИБКА при наличии ошибки.

Важная информация:

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

• Если второй аргумент функции — значение массива, то Excel вернет результат функции с каждым значением массива.

Варианты использования

Вернем пустую ячейку вместо ошибки

Итак, мы рассмотрим пример с делением на 0.

На картинке ниже, мы пытаемся делить число 10 на 0, из-за этого возникает ошибка #ДЕЛ/0!.

Давайте попробуем обработать её!

Используем функцию:

=ЕСЛИОШИБКА(A1/A2;"")

Так как в результате выполнения деления, мы получаем ошибку, то функция ЕСЛИОШИБКА вернет нам пустое место, как мы и указали во втором аргументе функции.

В случае ошибки, вы можете вывести что угодно. На ваш выбор.

Давайте сделаем тоже самое, но чтобы вернулось не пустое место, а слово «Ошибка».

=ЕСЛИОШИБКА(A1/A2; "Ошибка")

Фильтрация ошибки #Н/Д

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

На примере ниже, вы можете увидеть, как появилась наша ошибка:

Функция ВПР не может найти имя последнего студента в списке и из-за этого появляется ошибка #Н/Д.

Давайте отфильтруем её!

Итак, эта функция, в результате выполнения отдаст нам “Не найдено” при возникновении ошибки.

=ЕСЛИОШИБКА(ВПР(D2;$A$2:$B$12;2;0); "Не найдено")

Также вы можете использовать функцию ЕСНД для обработки ошибок типа #Н/Д, но она работает только для этого типа ошибок.

Возвращаем 0 в случае возникновения ошибки

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

Например, та же ошибка #ДЕЛ/0!:

Используем нашу функцию ЕСЛИОШИБКА, но намеренно не будем указывать второй аргумент функции. Посмотрим, что будет:

Используем ЕСЛИОШИБКА совместно с ВПР

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

Чтобы сделать это, мы можем использовать функцию ЕСЛИОШИБКА совместно с ВПР.

Допустим, мы имеем такую табличку:

Давайте найдем оценку для Грейс, для этого используем:

=ЕСЛИОШИБКА(ВПР(G3;$A$2:$B$5;2;0);ЕСЛИОШИБКА(ВПР(G3;$D$2:$E$5;2;0);"Не найдено"))  

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