Обработка отсутствующих результатов поиска с помощью IFNA

IFERROR — мощный инструмент, но она скрывает все ошибки. Иногда это слишком много. Если поиск завершается ошибкой из-за некорректных данных, а не из-за отсутствия совпадения, Вы должны увидеть эту проблему, а не спрятать её.

Функция IFNA решает эту задачу. Она обрабатывает только ошибку #N/A, а все остальные ошибки отображаются как обычно.

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

IFNA выглядит почти так же, как IFERROR, и принимает два аргумента:

• значение формула, которую нужно выполнить
• значение_при_NA что показать, только если результатом является #N/A

Шаблон выглядит так: =IFNA(your_formula, fallback). Если формула возвращает #N/A, Вы видите запасной вариант. Если она возвращает любую другую ошибку, эта ошибка остаётся видимой.

Разница имеет значение. Рассмотрим поиск с неверным индексом столбца, из-за которого возникает ошибка #REF!.

• IFERROR заменила бы эту ошибку #REF! запасным вариантом и скрыла бы настоящую проблему.
• IFNA оставила бы #REF! видимой, поэтому Вы поняли бы, что формулу нужно исправить.

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

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

Если клиент действительно отсутствует, Вы получите "Unknown customer". Но если Вы случайно указали не ту таблицу или нарушили индекс столбца, появится исходная ошибка, и Вы сможете её исправить.

Это помогает не доверять незаметно появившимся неверным числам.

XLOOKUP также возвращает #N/A, если не находит совпадение, поэтому IFNA хорошо с ней сочетается.

Хотя у XLOOKUP есть собственный встроенный аргумент если_не_найдено, IFNA удобна при редактировании старых формул или когда Вы хотите использовать единый подход для множества поисковых формул.

Оба подхода дают аккуратный результат при отсутствии совпадения и при этом оставляют настоящие ошибки видимыми.

Запасным вариантом также может быть число. Если отсутствующее совпадение должно учитываться как ноль в дальнейших вычислениях, возвращайте 0, а не текст.

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

Возврат текста вроде "None" в числовом столбце вызовет ошибки #VALUE! в последующих вычислениях, поэтому тип запасного варианта должен соответствовать назначению ячейки.

Во время проверки полезно использовать IFNA вместо IFERROR, пока Вы создаёте формулу.

Поскольку IFNA скрывает только ожидаемую ошибку #N/A, любая неожиданная ошибка, например #VALUE! или #REF!, сразу станет заметной.

Позже можно перейти на IFERROR, если Вы действительно хотите подавлять все ошибки, но начало работы с IFNA помогает раньше обнаружить ошибки.

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

Если цена найдена, Вы получаете настоящую сумму по строке. Если товара нет в таблице цен, цена становится равной 0, и сумма по строке также равна 0, а любая структурная ошибка по-прежнему отображается.

Так лист продаж остаётся одновременно аккуратным и надёжным.

IFNA доступна в современных версиях Excel и Google Таблицах, поэтому работает в большинстве электронных таблиц, которыми Вы будете пользоваться сегодня.

В очень старых версиях Excel её может не быть. В таких случаях её обычно имитируют, объединяя IF с ISNA, которая проверяет именно ошибку #N/A.

В следующем уроке Вы познакомитесь с проверками ошибок семейства IS, которые дают ещё более точный контроль.

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

При протягивании этой формулы вниз для известных магазинов отображается настоящий регион, а для магазинов, которых ещё нет в главном списке, — "Region TBD".

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

Сделать выбор помогает простое правило:

• Используйте IFNA для поиска, если хотите обрабатывать отсутствующие совпадения, но при этом видеть настоящие ошибки.
• Используйте IFERROR, когда любая ошибка ожидаема и не представляет опасности, например при вычислении отношений деления.

IFNA — более осторожный и точный вариант. Она скрывает только один конкретный случай, а остальные ошибки оставляет Вам для исправления.

Вы освоили специализированную функцию для обработки результатов поиска:

• =IFNA(value, value_if_na) обрабатывает только ошибку #N/A.
• Все остальные ошибки остаются видимыми, поэтому настоящие ошибки в формулах не скрываются.
• Это безопасный выбор для отсутствующих совпадений при использовании VLOOKUP и XLOOKUP.
• Согласуйте тип резервного значения — текст или число — с тем, как ячейка используется дальше.

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