При использовании формулы ВПР в Excel иногда вы можете получить неприятную ошибку # Н / Д. Это происходит, когда ваша формула не может найти искомое значение.
В этом руководстве я покажу вам различные способы использования ЕСЛИОШИБКА с ВПР для обработки этих ошибок # Н / Д, возникающих на вашем листе.
Использование комбинации IFERROR с VLOOKUP позволяет вам показать что-то значимое вместо ошибки # N / A (или любой другой ошибки, если на то пошло).
Прежде чем углубляться в подробности использования этой комбинации, давайте сначала быстро рассмотрим функцию ЕСЛИОШИБКА и посмотрим, как она работает.
Объяснение функции ЕСЛИОШИБКА
С помощью функции ЕСЛИОШИБКА вы можете указать, что должно произойти, если формула или ссылка на ячейку вернут ошибку.
Вот синтаксис функции ЕСЛИОШИБКА.
= ЕСЛИОШИБКА (значение; значение_если_ошибка)
- стоимость - это аргумент, который проверяется на наличие ошибки. В большинстве случаев это либо формула, либо ссылка на ячейку. При использовании ВПР с ЕСЛИОШИБКА формула ВПР будет этим аргументом.
- value_if_error - это значение, которое возвращается в случае ошибки. Оценивались следующие типы ошибок: # N / A, #REF !, # DIV / 0 !, #VALUE !, #NUM !, #NAME? И #NULL !.
Возможные причины того, что функция VLOOKUP возвращает ошибку # N / A
Функция ВПР может возвращать ошибку # Н / Д по любой из следующих причин:
- Значение поиска не найдено в массиве поиска.
- В поисковом значении (или в массиве таблицы) есть начальный, конечный или двойной пробел.
- Ошибка правописания в поисковом значении или значениях в поисковом массиве.
Вы можете справиться со всеми этими причинами ошибок с помощью комбинации IFERROR и VLOOKUP. Однако вы должны следить за причинами №2 и №3 и исправлять их в исходных данных, а не позволять ЕСЛИОШИБКА обрабатывать их.
Примечание. ЕСЛИОШИБКА обработает ошибку независимо от ее причины. Если вы хотите обработать только ошибки, вызванные тем, что функция VLOOKUP не может найти значение подстановки, используйте вместо этого IFNA. Это гарантирует, что ошибки, отличные от # N / A, не будут обрабатываться, и вы сможете исследовать эти другие ошибки.
Вы можете обрабатывать начальные, конечные и двойные пробелы с помощью функции TRIM.
Замена ошибки VLOOKUP # N / A значимым текстом
Предположим, у вас есть набор данных, как показано ниже:
Как видите, формула ВПР возвращает ошибку, поскольку искомого значения нет в списке. Мы хотим получить оценку Глена, которой нет в таблице результатов.
Хотя это очень маленький набор данных, вы можете получить огромные наборы данных, в которых вам нужно будет проверять наличие многих элементов. В каждом случае, когда значение не найдено, вы получите ошибку # N / A.
Вот формула, которую вы можете использовать, чтобы получить что-то значимое вместо ошибки # N / A.
= ЕСЛИОШИБКА (ВПР (D2; $ A $ 2: $ B $ 10,2,0); «Не найдено»)
Приведенная выше формула возвращает текст «Not Found» вместо ошибки # N / A. Вы также можете использовать ту же формулу для возврата пустого, нулевого или любого другого значимого текста.
Вложение ВПР с функцией ЕСЛИОШИБКА
Если вы используете ВПР и ваша таблица поиска фрагментирована на одном или разных листах, вам необходимо проверить значение ВПР во всех этих таблицах.
Например, в наборе данных, показанном ниже, есть две отдельные таблицы с именами учащихся и оценками.
Если мне нужно найти оценку Grace в этом наборе данных, мне нужно использовать функцию VLOOKUP для проверки первой таблицы, а если значение в ней не найдено, проверить вторую таблицу.
Вот вложенная формула ЕСЛИОШИБКА, которую я могу использовать для поиска значения:
= ЕСЛИОШИБКА (ВПР (G3, $ A $ 2: $ B $ 5,2,0), ЕСЛИОШИБКА (ВПР (G3, $ D $ 2: $ E $ 5,2,0), «Не найдено»))
Использование ВПР с IF и ISERROR (версии до Excel 2007)
Функция ЕСЛИОШИБКА была представлена в Excel 2007 для Windows и Excel 2016 для Mac.
Если вы используете предыдущие версии, функция ЕСЛИОШИБКА не будет работать в вашей системе.
Функциональность функции ЕСЛИОШИБКА можно воспроизвести, используя комбинацию функции ЕСЛИ и функции ЕСЛИОШИБКА.
Позвольте мне быстро показать вам, как использовать комбинацию IF и ISERROR вместо IFERROR.
В приведенном выше примере вместо использования ЕСЛИОШИБКА можно также использовать формулу, показанную в ячейке B3:
= ЕСЛИ (ЕСТЬ ОШИБКА (A3); «Не найдено»; A3)
Часть формулы ISERROR проверяет наличие ошибок (включая ошибку # N / A) и возвращает TRUE, если ошибка обнаружена, и FALSE, если нет.
- Если он имеет значение ИСТИНА (что означает наличие ошибки), функция ЕСЛИ возвращает указанное значение (в данном случае «Не найдено»).
- Если это ЛОЖЬ (что означает, что ошибки нет), функция ЕСЛИ возвращает это значение (A3 в приведенном выше примере).
ЕСЛИ ОШИБКА против IFNA
ЕСЛИОШИБКА обрабатывает все виды ошибок, тогда как IFNA обрабатывает только ошибку # Н / Д.
При обработке ошибок, вызванных ВПР, необходимо убедиться, что вы используете правильную формулу.
Используйте ЕСЛИОШИБКА когда вы хотите лечить всевозможные ошибки. Теперь ошибка может быть вызвана различными факторами (например, неправильной формулой, неправильным написанием именованного диапазона, отсутствием поиска значения и возвратом значения ошибки из таблицы поиска). Это не имеет значения для IFERROR, и все эти ошибки заменяются указанным значением.
Используйте IFNA если вы хотите обработать только ошибки # Н / Д, которые с большей вероятностью будут вызваны тем, что формула ВПР не может найти значение подстановки.
Вы также можете найти следующие полезные руководства по Excel:
- Как сделать ВПР чувствительным к регистру.
- VLOOKUP Vs. ИНДЕКС / МАТЧ - Дебаты заканчиваются!
- Используйте VLookup, чтобы получить последний номер в списке в Excel.
- Как использовать ВПР с несколькими критериями
- Ошибка #NAME в Excel - что ее вызывает и как ее исправить!
