ЕСЛИОШИБКА используется с функцией ВПР для устранения ошибок #Н/Д

При использовании формул ВПР в Excel иногда могут возникать неприятные ошибки #Н/Д.Это происходит, когда ваша формула не может найти искомое значение.

В этом руководстве я покажу вам различные способы использования ЕСЛИОШИБКА и ВПР для обработки этих ошибок #Н/Д на листах.

Используйте ЕСЛИОШИБКА в сочетании с ВПР, чтобы отобразить что-то значимое вместо ошибки #Н/Д (или любой другой ошибки).

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

Описание функции ЕСЛИОШИБКА

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

Это синтаксис функции ЕСЛИОШИБКА.

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

• стоимость - Это параметр для проверки ошибок.В большинстве случаев это либо формула, либо ссылка на ячейку.При использовании ВПР с ЕСЛИОШИБКА этим параметром будет формула ВПР.
• значение_если_ошибка – это значение, возвращаемое при возникновении ошибки.Были оценены следующие типы ошибок: #N/A, #REF!, #DIV/0!, #VALUE!, #NUM!, #NAME? и #NULL!.

Возможные причины, по которым функция ВПР возвращает ошибки #Н/Д

Функция ВПР может возвращать ошибку #Н/Д по любой из следующих причин:

1. Значение поиска не было найдено в массиве поиска.
2. В искомом значении (или массиве таблиц) есть начальные, конечные или двойные пробелы.
3. Опечатка в искомом значении или искомом значении в массиве.

Вы можете использовать ЕСЛИОШИБКА в сочетании с ВПР для обработки всех этих причин ошибок.Однако вы должны помнить о причинах № 2 и № 3 и исправлять их в исходных данных, а не позволять IFERROR обрабатывать их.

Примечание. ЕСЛИОШИБКА обработает ошибку независимо от того, что ее вызвало.Если вы хотите обрабатывать только ошибки, вызванные тем, что функция ВПР не может найти искомое значение, используйте вместо этого IFNA.Это гарантирует, что ошибки, отличные от #N/A, не будут обработаны, и вы сможете исследовать эти другие ошибки.

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

Замените ошибки ВПР #Н/Д осмысленным текстом

Предположим, у вас есть набор данных, который выглядит следующим образом:

Как видите, формула ВПР возвращает ошибку, поскольку искомое значение отсутствует в списке.Мы ищем счет Глена, его нет в таблице очков.

Хотя это очень маленький набор данных, вы можете получить огромный набор данных, в котором вам придется проверять наличие множества элементов.Вы получите ошибку #N/A для каждого случая, когда значение не найдено.

Это формула, которую вы можете использовать, чтобы получить что-то значимое, а не #N/A неправильное.

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

Приведенная выше формула возвращает текст «Не найдено» вместо ошибки #Н/Д.Вы также можете использовать ту же формулу для возврата пробелов, нулей или любого другого осмысленного текста.

Вложенная функция ВПР с функцией ЕСЛИОШИБКА

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

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

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

Вот вложенная формула ЕСЛИОШИБКА, которую я могу использовать для поиска значения:

=IFERROR(VLOOKUP(G3,$A$2:$B$5,2,0),IFERROR(VLOOKUP(G3,$D$2:$E$5,2,0),"Not Found"))

Использование ВПР с ЕСЛИ и ЕОШИБКА (до Excel 2007)

Функция ЕСЛИОШИБКА появилась в Excel 2007 для Windows и Excel 2016 для Mac.

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

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

Позвольте мне быстро показать вам, как использовать комбинацию ЕСЛИ и ЕОШИБКА вместо ЕСЛИОШИБКА.

В приведенном выше примере вместо использования ЕСЛИОШИБКА вы также можете использовать формулу, показанную в ячейке B3:

=ЕСЛИ(ЕОШИБКА(A3),"Не найдено",A3)

Часть формулы IОШИБКА проверяет наличие ошибок (включая ошибки #Н/Д) и возвращает ИСТИНА, если обнаружена ошибка, и ЛОЖЬ в противном случае.

• Если TRUE (указывает на ошибку), функция ЕСЛИ возвращает указанное значение (в данном случае «Не найдено»).
• Если FALSE (что означает отсутствие ошибки), функция ЕСЛИ вернет это значение (A3 в приведенном выше примере).

IFERROR и IFNA

IFERROR обрабатывает все типы ошибок, тогда как IFNA обрабатывает только ошибки #N/A.

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

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

Используйте IFNA, если вы хотите обрабатывать только ошибки #N/A, что, скорее всего, вызвано тем, что формула ВПР не может найти искомое значение.