Как использовать функцию IFNA в Excel с примерами
Получаете много ошибок #N/A на ваших листах и вам интересно узнать, есть ли способ вместо этого отобразить пользовательский текст? Формула IFNA – это решение, которое вам нужно.
Когда формула Excel не может идентифицировать или найти что-то, она выдает ошибку #Н/Д. Чтобы поймать такую ошибку и заменить ее удобным для пользователя сообщением, вы можете использовать функцию IFNA. Другими словами, #N/A — это способ Excel сказать, что искомое значение отсутствует в указанном наборе данных. IFNA — это ваш способ поймать и обработать эту ошибку.
Функция ИФНА в Excel
Функция Excel IFNA предназначена для обнаружения и обработки ошибок #N/A. Если формула оценивается как #Н/Д, IFNA перехватывает эту ошибку и заменяет ее указанным пользователем значением; в противном случае возвращает нормальный результат формулы.
Синтаксис ИФНА
Синтаксис функции IFNA следующий:
IFNA(значение, значение_если_на)
Где:
Значение (обязательно) — формула, значение или ссылка для проверки наличия ошибки #Н/Д.
Value_if_na (обязательно) — возвращаемое значение при обнаружении ошибки #Н/Д.
Примечания по использованию
- Функция IFNA обрабатывает только #N/A без подавления каких-либо других ошибок.
- Если ценность аргумент представляет собой формулу массива, IFNA возвращает массив результатов, по одному на ячейку, как показано на этот пример.
Доступность IFNA
Функция IFNA была представлена в Excel 2013 и доступна во всех последующих версиях, включая Excel 2016, Excel 2019, Excel 2021 и Microsoft 365.
В более ранних версиях вы могли перехватывать ошибки #N/A, используя вместе функции IF и ISNA.
Как использовать функцию IFNA в Excel
Чтобы эффективно использовать IFNA в Excel, следуйте этому общему подходу:
- В первом аргументе (ценность), укажите формулу, на которую повлияла ошибка #Н/Д.
- Во втором аргументе (значение_если_на), введите текст, который вы хотите вернуть, вместо стандартного обозначения ошибки. Чтобы вернуть пустую ячейку, когда ничего не найдено, введите пустую строку (“””).
Для возврата пользовательского текста используется общая формула:
ИФНА(формула(), “пользовательский текст“)
Чтобы вернуть пустую ячейку, используйте общую формулу:
ИФНА(формула(), “”)
Давайте посмотрим, как это работает на простом примере. В приведенной ниже таблице предположим, что вы хотите узнать, какое место занимает оценка данного учащегося среди других. Поскольку данные сортируются по Счет колонке от высшего к низшему, ранг будет соответствовать относительному положению учащегося в таблице. А чтобы получить позицию, вы можете использовать функцию ПОИСКПОЗ в ее простейшей форме:
=ПОИСКПОЗ(E1, A2:A10, 0)
Поскольку искомое значение (Нил) недоступно в массиве поиска (A2:A10), возникает ошибка #Н/Д.
Столкнувшись с этой ошибкой, неопытные пользователи могут подумать, что с формулой что-то не так, и вы, как создатель рабочей книги, получите много вопросов. Чтобы этого избежать, можно явно указать, что формула верна, она просто не может найти то значение, которое ее просят искать. Итак, вы вставляете формулу ПОИСКПОЗ в первый аргумент IFNA, а во второй аргумент вводите свой собственный текст, в нашем случае «Не найдено»:
=ЕСЛИНА(ПОИСКПОЗ(E1, A2:A10, 0), “Не найдено”)
Теперь вместо стандартного обозначения ошибок в ячейке отображается ваш собственный текст, информирующий пользователей о том, что искомое значение отсутствует в наборе данных:
Как использовать IFNA с функцией ВПР
Чаще всего ошибка #Н/Д возникает в функциях, которые ищут что-то вроде ВПР, ГПР, ПРОСМОТР и ПОИСКПОЗ. Приведенные ниже примеры охватывают несколько типичных случаев использования.
Пример 1. Базовая формула IFNA VLOOKUP
Чтобы перехватывать ошибки #Н/Д, возникающие, когда ВПР не может найти совпадение, проверьте его результат с помощью IFNA и укажите значение, которое будет отображаться вместо ошибки. Обычная практика заключается в том, чтобы обернуть функцию IFNA вокруг существующей формулы VLOOKUP, используя следующий синтаксис:
ЕСЛИ(ВПР()), “твой текст“)
Предположим, что в нашем образце таблицы вы хотите получить оценку определенного учащегося (E1). Для этого вы используете эту классическую формулу ВПР:
=ВПР(E1, A2:B10, 2, ЛОЖЬ)
Проблема в том, что Нил не сдавал экзамен, поэтому его имени нет в списке, и, очевидно, ВПР не может найти совпадение.
Чтобы скрыть ошибку, оборачиваем ВПР в IFNA вот так:
=ЕСЛИНА(ВПР(E1, A2:B10, 2, ЛОЖЬ), “Не сдал экзамен”)
Теперь результат не выглядит таким пугающим для пользователя и намного информативнее:
Пример 2. IFNA VLOOKUP для поиска на нескольких листах
Функция IFNA также удобна для выполнения так называемого последовательный или же прикованный поиск по нескольким листам или разным книгам. Идея состоит в том, что вы вкладываете несколько разных формул IFNA(VLOOKUP(…)) одна в другую следующим образом:
IFNA(ВПР(…), IFNA(ВПР(…), IFNA(ВПР(…), «Не найдено»)))
Если первичная ВПР ничего не находит, ее функция IFNA запускает следующую ВПР до тех пор, пока не будет найдено нужное значение. Если все поиски завершатся неудачно, формула вернет указанный текст.
Предположим, у вас есть баллы по разным классам, перечисленные на разных листах (названных Класс А, Класс Ба также Класс С). Ваша цель — получить оценку конкретного учащегося, чье имя введено в ячейку B1 вашего текущего рабочего листа. Для выполнения задачи используйте эту формулу:
= ЕСЛИНА(ВПР(B1, ‘Класс A’!A2:B5, 2, ЛОЖЬ), ЕСЛИНА(ВПР(B1, ‘Класс B’!A2:B5, 2, ЛОЖЬ), ЕСЛИНА(ВПР(B1, ‘Класс C) ‘!A2:B5, 2, FALSE), “Не найдено”)))
Формула последовательно ищет указанное имя в трех разных листах в порядке вложения ВПР и выводит первое найденное совпадение:
Пример 3. IFNA с INDEX MATCH
Аналогичным образом IFNA может обнаруживать ошибки #N/A, генерируемые другими функциями поиска. В качестве примера воспользуемся ею вместе с формулой ИНДЕКС ПОИСКПОЗ:
=ЕСЛИНА(ИНДЕКС(B2:B10, СОВПАДЕНИЕ(E1, A2:A10, 0)), “Не найдено”)
Суть формулы такая же, как и во всех предыдущих примерах: ПОИСКПОЗ ИНДЕКС выполняет поиск, а IFNA оценивает результат и выявляет ошибку #Н/Д, если указанное значение не найдено.
IFNA для возврата нескольких результатов
В случае, если внутренняя функция (т.е. формула, помещенная в ценность аргумент) возвращает несколько значений, IFNA проверит каждое возвращаемое значение по отдельности и выведет массив результатов. Например:
=ЕСЛИНА(ВПР(D2:D4, A2:B10, 2, ЛОЖЬ), “Не найдено”)
В Dynamic Array Excel (Microsoft 365 и Excel 2021) обычная формула в самой верхней ячейке (E2) автоматически распределяет все результаты в соседних ячейках (в Excel это называется диапазоном переноса).
В додинамических версиях (Excel 2019 и ниже) аналогичного эффекта можно добиться, используя формулу массива из нескольких ячеек, которая завершается сочетанием клавиш Ctrl+Shift+Enter.
В чем разница между IFNA и IFERROR?
В зависимости от основной причины проблемы формула Excel может вызывать различные ошибки, такие как #Н/Д, #ИМЯ, #ЗНАЧ, #ССЫЛКА, #ДЕЛ/0, #ЧИСЛО и другие. Функция ЕСЛИОШИБКА перехватывает все эти ошибки, в то время как функция IFNA ограничена только #N/A. Какой лучше выбрать? Это зависит от ситуации.
Если вы хотите подавить какую-либо ошибку, используйте функцию ЕСЛИОШИБКА. Это особенно полезно при сложных вычислениях, когда формула включает несколько функций, которые могут генерировать разные ошибки.
С функциями поиска вам лучше использовать IFNA, так как он отображает пользовательский результат только тогда, когда значение поиска не найдено, и не скрывает основные проблемы с самой формулой.
Чтобы проиллюстрировать разницу, давайте вернемся к нашему базовому Формула IFNA VLOOKUP и «случайно» неправильно написал имя функции (ВПР вместо ВПР).
=IFNA(ВЛОКУП(E1, A2:B10, 2, FALSE), “Экзамен не сдавал”)
IFNA не подавляет эту ошибку, поэтому хорошо видно, что с одним из названий функций что-то не так:
Теперь давайте посмотрим, что произойдет, если вы используете ЕСЛИОШИБКА:
=ЕСЛИОШИБКА(ВЛОКУП(E1, A2:B10, 2, ЛОЖЬ), “Не сдал экзамен”)
Хм… там написано, что Оливия не сдавала экзамен, что неправда! Это связано с тем, что функция ЕСЛИОШИБКА перехватывает #NAME? ошибка и вместо этого возвращает пользовательский текст. В этой ситуации он не только возвращает неверную информацию, но и скрывает проблему с формулой.
Вот как использовать формулу IFNA в Excel. Я благодарю вас за чтение и с нетерпением жду встречи с вами в нашем блоге на следующей неделе!
Доступные загрузки
Примеры формул Excel IFNA (файл .xlsx)
