Функция ISNA в Excel с примерами формул
В этом руководстве рассматриваются различные способы использования функции ISNA в Excel для обработки ошибок #N/A.
Когда Excel не может найти запрошенное, в ячейке появляется ошибка #Н/Д. Для перехвата и обработки таких ошибок можно использовать функцию ISNA. Какая от этого практическая польза? По сути, это помогает сделать ваши формулы более удобными для пользователя, а ваши рабочие листы – более привлекательными.
Функция ИСНА в Excel
Функция Excel ISNA используется для проверки ячеек или формул на наличие ошибок #Н/Д. Результатом является логическое значение: TRUE, если обнаружена ошибка #N/A, FALSE в противном случае.
Эта функция доступна во всех версиях Excel 2000–2021 и Excel 365.
Синтаксис функции ISNA максимально прост:
ИСНА(значение)
Где ценность — это значение ячейки или формула, которую вы хотите проверить на наличие ошибок #Н/Д.
Чтобы создать формулу ISNA в ее базовой форме, укажите ссылку на ячейку в качестве ее единственного аргумента:
=ИСНА(A2)
Если указанная ячейка содержит ошибку #Н/Д, вы получите значение ИСТИНА. В случае любой другой ошибки, значения или пустой ячейки вы получите ЛОЖЬ:
Как использовать ISNA в Excel
Использование функции ISNA в чистом виде имеет мало практического смысла. Чаще она используется вместе с другими функциями для оценки результата определенной формулы. Для этого просто поместите эту другую формулу в ценность аргумент ИСНА:
ИСНА(ваша_формула())
В приведенном ниже наборе данных предположим, что вы хотите сравнить два списка (столбцы A и D) и определить имена, которые присутствуют в обоих списках, и те, которые появляются только в списке 1.
Чтобы сравнить имя в A3 с каждым именем в столбце D, используйте следующую формулу:
=ПОИСКПОЗ(A3, $D$2:$D$9, 0)
Если значение поиска найдено, функция ПОИСКПОЗ возвращает его относительное положение в массиве поиска, в противном случае возникает ошибка #Н/Д. Чтобы проверить результат MATCH, мы вкладываем его в ISNA:
=ISNA(ПОИСКПОЗ(A3, $D$2:$D$9, 0))
Эта формула идет в ячейку B3, а затем копируется в ячейку B14.
Теперь вы можете четко видеть, какие учащиеся прошли все тесты (имя недоступно в столбце D > ПОИСКПОЗ возвращает #Н/Д > ISNA возвращает ИСТИНА) и у кого есть хотя бы один непройденный тест (имя появляется в столбце D > нет ошибки > ISNA возвращает ЛОЖЬ).
Кончик. В Excel 365 и Excel 2021 вы можете использовать более современную функцию XMATCH. вместо ПОИСКПОЗ.
Формула ЕСЛИ ISNA в Excel
По замыслу функция ISNA может возвращать только два логических значения. Чтобы отобразить ваши пользовательские сообщения, используйте его в сочетании с функцией ЕСЛИ:
ЕСЛИ(ИСНА(…), “text_if_error“, “text_if_no_error“)
Немного уточнив наш пример, давайте выясним, какие ученики из группы А не провалили ни одного теста, и вернём для них «Нет проваленных тестов». Для оставшихся студентов мы вернем «Failed». Для этого в логическую проверку ЕСЛИ вставьте формулу ПОИСКПОЗ ISNA, чтобы ЕСЛИ стала самой внешней функцией:
=ЕСЛИ(ИСНА(ПОИСКПОЗ(A3,$D$2:$D$9,0)), “Нет неудачных тестов”, “Ошибка”)
Теперь результаты выглядят намного лучше и интуитивно понятны, согласны?
Как использовать ISNA в Excel с функцией ВПР
Комбинация IF ISNA — это универсальное решение, которое можно использовать с любой функцией, которая ищет что-то в наборе данных и возвращает ошибку #Н/Д, когда искомое значение не найдено.
Синтаксис функции ISNA с функцией ВПР следующий:
ЕСЛИ(ИСНА(ВПР(…), “custom_text“, ВПР(…))
В переводе на человеческий язык это говорит: если функция ВПР приводит к ошибке #Н/Д, вернуть пользовательский текст, в противном случае вернуть результат ВПР.
В нашей примерной таблице предположим, что вы хотите вернуть предметы, по которым учащиеся не прошли тесты. Для тех, кто успешно прошел все тесты, будет отображаться «Нет неудачных тестов».
Чтобы искать предметы, мы создаем эту классическую формулу ВПР:
=ВПР(A3, $D$3:$E$9, 2, ЛОЖЬ)
А затем вложите его в общую формулу IF ISNA, описанную выше:
=ЕСЛИ(ISNA(ВПР(A3, $D$3:$E$9, 2, ЛОЖЬ)), “Нет неудачных тестов”, ВПР(A3, $D$3:$E$9, 2, ЛОЖЬ))
В Excel 2013 и более поздних версиях вы можете использовать функцию IFNA для обнаружения и обработки ошибок #N/A. Это делает вашу формулу короче и легче для чтения.
Например, мы заменяем ошибки #N/A на тире (“-“) и получаем такое изящное решение:
=ЕСЛИНА(ВПР(A3, $D$3:$E$9, 2, ЛОЖЬ), “-“)
Пользователям Excel 365 и 2021 вообще не нужны никакие функции-оболочки, поскольку современный преемник функции ВПР, функция КСПР, может изначально обрабатывать ошибки #Н/Д:
=XLOOKUP(A3, $D$3:$D$9, $E$3:$E$9, “-“)
Результат будет точно таким же, как показано на скриншоте выше.
Формула SUMPRODUCT ISNA для подсчета ошибок #N/A
Чтобы подсчитать ошибки #Н/Д в определенном диапазоне, используйте функцию ЕСНА вместе с СУММПРОИЗВ следующим образом:
СУММПРОИЗВ(–ИСНА(диапазон))
Здесь ISNA возвращает массив значений TRUE и FALSE, двойное отрицание (–) преобразует логические значения в 1 и 0, а СУММПРОИЗВ суммирует результат.
Например, чтобы узнать, сколько учащихся успешно прошли все тесты, измените формулу ПОИСКПОЗ для диапазона значений поиска (A3:A14) и вложите ее в ISNA:
=СУММПРОИЗВ(–ИСНА(ПОИСКПОЗ(A3:A14, D2:D9, 0)))
Формула определяет, что у 9 учащихся нет непройденных тестов, т.е. функция ПОИСКПОЗ возвращает 9 ошибок #Н/Д:
Вот как создавать и использовать формулы ISNA в Excel. Я благодарю вас за чтение и с нетерпением жду встречи с вами в нашем блоге на следующей неделе!
Доступные загрузки
Примеры формул ISNA (файл .xlsx)