3 метода, которые приводят к цели
Каждый активный пользователь VLOOKUP знаком с сообщением об ошибке «#NV». Это означает «нет значения» или указывает, что искомое значение «недоступно». Такое сообщение об ошибке совершенно нормально, поскольку оно предназначено для обозначения проблемы для пользователя Excel. Но как избежать появления этого сообщения об ошибке? Для этого доступны три варианта:
- Оригинальный метод
- Упрощенный метод из Excel 2013
- Новейший метод с XLOOKUP
Внимание: Если, с другой стороны, ВПР дает неверное значение 0, то это может быть другая ошибка. Эту проблему можно решить в настройках Excel.
1. Подавление ошибок ВПР: оригинальный метод
Если вы хотите отображать альтернативный текст вместо стандартной информации, вы можете интегрировать две дополнительные функции. Для этого функция ISERROR связана с функцией IF и строится на основе ВПР. Чтобы распутать эту, казалось бы, сложную конкатенацию, мы начнем с существующей ВПР. В примере это выглядит так:
= ВПР (C1; A1: B10; 2)
Две дополнительные функции должны теперь определить, есть ли ошибка, а затем действовать автоматически в соответствии с указаниями пользователя. Поэтому, если возникает ошибка, должно отображаться само выбранное сообщение. Полная формула для этого выглядит так:
= ЕСЛИ (ЕСТЬ ОШИБКА (ВПР (C1; A1: B10,2)); ""; ВПР (C1; A1: B10,2))
Между двумя кавычками есть пробел, чтобы указать, какой текст ошибки следует печатать вместо «#NV». Если здесь ничего не введено, сообщение об ошибке остается пустым.
Информация: Прочтите эту статью, чтобы узнать, как избежать ошибок с помощью ВПР: перехват сообщения об ошибке Excel в запросе ВПР
2. Выявление сообщений об ошибках из Excel 2013 стало еще проще.
Начиная с версии Excel 2013, эту проблему можно решить с помощью более элегантной формулы. Для этого вместо функции ЕСЛИ используется новая функция IFNV. Как только появляется сообщение об ошибке «#NV», начинается тот же процесс, что и для функции if. Однако функция ISERROR здесь не требуется. Однако, прежде чем вы сможете начать, необходимо ненадолго настроить ВПР, чтобы она выглядела следующим образом:
ВПР ($ C $ 5; $ B $ 10: $ E $ 14; 2; FALSE)
Теперь вокруг него установлена функция IFNV, чтобы завершить формулу
= IFNV (ВПР ($ C $ 5; $ B $ 10: $ E $ 14; 2; ЛОЖЬ); "")
Две кавычки в конце формулы продолжают выполнять ту же работу, что и раньше: они позволяют пользователю указать имя выдаваемого сообщения об ошибке.
3. Новейший метод с XLOOKUP
С 2022-2023 года у пользователей Microsoft 365 появилась еще более быстрая возможность самостоятельно решить проблему с сообщением об ошибке. Вместо предыдущей VLOOKUP используется новый XLOOKUP. Это устраняет необходимость в функциях IF и IFNV, а также в функции ISERROR.
Информация: Если вам нужна дополнительная информация о ВПР, прочтите следующую статью: Подавление сообщения # N / A с помощью VLOOKUP с ISNV
