Skip to content
В статье описано, как использовать функцию ЕСЛИОШИБКА в Excel для обнаружения ошибок и замены их пустой ячейкой, другим значением или определённым сообщением. Покажем примеры, как использовать функцию ЕСЛИОШИБКА с функциями визуального просмотра и сопоставления индексов, а также как она сравнивается с ЕСЛИ ОШИБКА и ЕСНД.
«Дайте мне точку опоры, и я переверну землю», — сказал однажды Архимед. «Дайте мне формулу, и я заставлю ее вернуть ошибку», — сказал бы пользователь Excel. Здесь мы не будем рассматривать, как получить ошибки в Excel. Мы узнаем, как предотвратить их, чтобы ваши таблицы были чистыми, а формулы — понятными и точными.
Итак, вот о чем мы поговорим:
Что означает функция Excel ЕСЛИОШИБКА
Функция ЕСЛИОШИБКА (IFERROR по-английски) предназначена для обнаружения и устранения ошибок в формулах и вычислениях. Это значит, что функция ЕСЛИОШИБКА должна выполнить определенные действия, если видит какую-либо ошибку. Более конкретно, она проверяет формулу и, если вычисление дает ошибку, то она возвращает какое-то другое значение, которое вы ей укажете. Если же всё хорошо, то просто возвращает результат формулы.
Синтаксис функции Excel ЕСЛИОШИБКА следующий:
ЕСЛИОШИБКА(значение; значение_если_ошибка)
Где:
- Значение (обязательно) — что проверять на наличие ошибок. Это может быть формула, выражение или ссылка на ячейку.
- Значение_если_ошибка (обязательно) — что возвращать при обнаружении ошибки. Это может быть пустая строка (получится пустая ячейка), текстовое сообщение, числовое значение, другая формула или вычисление.
Например, при делении двух столбцов чисел можно получить кучу разных ошибок, если в одном из столбцов есть пустые ячейки, нули или текст.
Рассмотрим простой пример:
Чтобы этого не произошло, используйте формулу ЕСЛИОШИБКА, чтобы перехватывать и обрабатывать их нужным вам образом.
Если ошибка, то пусто
Укажите пустую строку (“”) в аргументе значение_если_ошибка, чтобы вернуть пустую ячейку, если обнаружена ошибка:
=ЕСЛИОШИБКА(A4/B4; «»)
Вернемся к нашему примеру и используем ЕСЛИОШИБКА:
Как видите по сравнению с первым скриншотом, вместо стандартных сообщений мы видим просто пустые ячейки.
Если ошибка, то показать сообщение
Вы также можете отобразить собственное сообщение вместо стандартного обозначения ошибок Excel:
=ЕСЛИОШИБКА(A4/B4; «Ошибка в вычислениях»)
Перед вами – третий вариант нашей небольшой таблицы.
5 фактов, которые нужно знать о функции ЕСЛИОШИБКА в Excel
- ЕСЛИОШИБКА в Excel обрабатывает все типы ошибок, включая #ДЕЛ/0!, #Н/Д, #ИМЯ?, #NULL!, #ЧИСЛО!, #ССЫЛКА! и #ЗНАЧ!.
- В зависимости от содержимого аргумента значение_если_ошибка функция может заменить ошибки вашим текстовым сообщением, числом, датой или логическим значением, результатом другой формулы или пустой строкой (пустой ячейкой).
- Если аргумент значение является пустой ячейкой, он обрабатывается как пустая строка (»’), но не как ошибка.
- ЕСЛИОШИБКА появилась в Excel 2007 и доступна во всех последующих версиях Excel 2010, Excel 2013, Excel 2016, Excel 2019, Excel 2021 и Excel 365.
- Чтобы перехватывать ошибки в Excel 2003 и более ранних версиях, используйте функцию ЕОШИБКА в сочетании с функцией ЕСЛИ, например как показано ниже:
=ЕСЛИ(ЕОШИБКА(A4/B4);»Ошибка в вычислениях»;A4/B4)
Далее вы увидите, как можно использовать ЕСЛИОШИБКА в Excel в сочетании с другими функциями для выполнения более сложных задач.
ЕСЛИОШИБКА с функцией ВПР
Часто встречающаяся задача в Excel – поиск нужного значения в таблице в соответствии с определёнными критериями. И не всегда этот поиск бывает успешным. Одним из наиболее распространенных применений функции ЕСЛИОШИБКА является сообщение пользователям, что искомое значение не найдено в базе данных. Для этого вы заключаете формулу ВПР в функцию ЕСЛИОШИБКА примерно следующим образом:
ЕСЛИОШИБКА(ВПР( … );»Не найдено»)
Если искомое значение отсутствует в таблице, которую вы просматриваете, обычная формула ВПР вернет ошибку #Н/Д:
Для лучшего понимания таблицы и улучшения ее внешнего вида, заключите функцию ВПР в ЕСЛИОШИБКА и покажите более понятное для пользователя сообщение:
=ЕСЛИОШИБКА(ВПР(D3; $A$3:$B$5; 2;ЛОЖЬ); «Не найдено»)
На скриншоте ниже показан пример ЕСЛИОШИБКА вместе с ВПР в Excel:
Если вы хотите перехватывать только #Н/Д, но не все подряд ошибки, используйте функцию ЕНД вместо ЕСЛИОШИБКА. Она просто возвращает ИСТИНА или ЛОЖЬ в зависимости от появления ошибки #Н/Д. Поэтому нам здесь еще понадобится функция ЕСЛИ, чтобы обработать эти логические значения:
=ЕСЛИ(ЕНД(ВПР(D3; $A$3:$B$5; 2;ЛОЖЬ)); «Не найдено»;ВПР(D3; $A$3:$B$5; 2;ЛОЖЬ))
Дополнительные примеры формул Excel ЕСЛИОШИБКА ВПР можно также найти в нашей статье Как убрать сообщение #Н/Д в ВПР?
Вложенные функции ЕСЛИОШИБКА для выполнения последовательных ВПР
В ситуациях, когда вам нужно выполнить несколько операций ВПР в зависимости от того, была ли предыдущая ВПР успешной или неудачной, вы можете вложить две или более функции ЕСЛИОШИБКА одну в другую.
Предположим, у вас есть несколько отчетов о продажах из региональных отделений вашей компании, и вы хотите получить сумму по определенному идентификатору заказа. С ячейкой В9 в качестве критерия поиска (номер заказа) и тремя небольшими таблицами поиска (таблица 1, 2 и 3), формула выглядит следующим образом:
=ЕСЛИОШИБКА(ВПР(B9;A3:B6;2;0);ЕСЛИОШИБКА(ВПР(B9;D3:E6;2;0);ЕСЛИОШИБКА(ВПР(B9;G3:H6;2;0);»Не найден»)))
Результат будет выглядеть примерно так, как на рисунке ниже:
То есть, если поиск завершился неудачей (то есть, ошибкой) первой таблице, начинаем искать во второй, и так далее. Если нигде ничего не нашли, получим сообщение «Не найден».
ЕСЛИОШИБКА в формулах массива
Как вы, наверное, знаете, формулы массива в Excel предназначены для выполнения нескольких вычислений внутри одной формулы. Если вы в аргументе значение функции ЕСЛИОШИБКА укажете формулу или выражение, которое возвращает массив, она также обработает и вернет массив значений для каждой ячейки в указанном диапазоне. Пример ниже поможет пояснить это.
Допустим, у вас есть Сумма в столбце B и Цена в столбце C, и вы хотите вычислить Количество. Это можно сделать с помощью следующей формулы массива, которая делит каждую ячейку в диапазоне B2:B4 на соответствующую ячейку в диапазоне C2:C4, а затем суммирует результаты:
=СУММ(($B$2:$B$4/$C$2:$C$4))
Формула работает нормально, пока в диапазоне делителей нет нулей или пустых ячеек. Если есть хотя бы одно значение 0 или пустая строка, то возвращается ошибка: #ДЕЛ/0! Из-за одной некорректной позиции мы не можем получить итоговый результат.
Чтобы исправить эту ситуацию, просто вложите деление внутрь формулы ЕСЛИОШИБКА:
=СУММ(ЕСЛИОШИБКА($B$2:$B$4/$C$2:$C$4;0))
Что делает эта формула? Делит значение в столбце B на значение в столбце C в каждой строке (3500/100, 2000/50 и 0/0) и возвращает массив результатов {35; 40; #ДЕЛ/0!}. Функция ЕСЛИОШИБКА перехватывает все ошибки #ДЕЛ/0! и заменяет их нулями. Затем функция СУММ суммирует значения в итоговом массиве {35; 40; 0} и выводит окончательный результат (35+40=75).
Примечание. Помните, что ввод формулы массива должен быть завершен нажатием комбинации Ctrl + Shift + Enter (если у вас не Office365 или Excel2021 – они понимают формулы массива без дополнительных телодвижений).
ЕСЛИОШИБКА или ЕСЛИ + ЕОШИБКА?
Теперь, когда вы знаете, как использовать функцию ЕСЛИОШИБКА в Excel, вы можете удивиться, почему некоторые люди все еще склоняются к использованию комбинации ЕСЛИ + ЕОШИБКА. Есть ли у этого старого метода преимущества по сравнению с ЕСЛИОШИБКА?
В старые недобрые времена Excel 2003 и более ранних версий, когда ЕСЛИОШИБКА не существовало, совместное использование ЕСЛИ и ЕОШИБКА было единственным возможным способом перехвата ошибок. Это просто немного более сложный способ достижения того же результата.
Например, чтобы отловить ошибки ВПР, вы можете использовать любую из приведенных ниже формул.
В Excel 2007 — Excel 2016:
ЕСЛИОШИБКА(ВПР( … ); «Не найдено»)
Во всех версиях Excel:
ЕСЛИ(ЕОШИБКА(ВПР(…)); «Не найдено»; ВПР(…))
Обратите внимание, что в формуле ЕСЛИ ЕОШИБКА ВПР вам нужно дважды выполнить ВПР. Чтобы лучше понять, расшифруем: если ВПР приводит к ошибке, вернуть «Не найдено», в противном случае вывести результат ВПР.
А вот простой пример формулы Excel ЕСЛИ ЕОШИБКА ВПР:
=ЕСЛИ(ЕОШИБКА(ВПР(D2; A2:B5;2;ЛОЖЬ)); «Не найдено»; ВПР(D2; A2:B5;2;ЛОЖЬ ))
ЕСЛИОШИБКА против ЕСНД
Представленная в Excel 2013, ЕСНД (IFNA в английской версии) — это еще одна функция для проверки формулы на наличие ошибок. Его синтаксис похож на синтаксис ЕСЛИОШИБКА:
ЕСНД(значение; значение_если_НД)
Чем ЕСНД отличается от ЕСЛИОШИБКА? Функция ЕСНД перехватывает только ошибки #Н/Д, тогда как ЕСЛИОШИБКА обрабатывает все типы ошибок.
В каких ситуациях вы можете использовать ЕСНД? Когда нецелесообразно скрывать все ошибки. Например, при работе с важными данными вы можете захотеть получать предупреждения о возможных ошибках в вашем наборе данных (случайном делении на ноль и т.п.), а стандартные сообщения об ошибках Excel с символом «#» могут быть яркими визуальными индикаторами проблем.
Давайте посмотрим, как можно создать формулу, отображающую сообщение «Не найдено» вместо ошибки «Н/Д», которая появляется, когда искомое значение отсутствует в наборе данных, но при этом вы будете видеть все другие ошибки Excel.
Предположим, вы хотите получить Количество из таблицы поиска в таблицу с результатами, как показано на рисунке ниже. Проще всего было бы использовать ЕСЛИОШИБКА с ВПР. Таблица приобрела бы красивый вид, но при этом за надписью «Не найдено» были бы скрыты не только ошибки поиска, но и все другие ошибки. И мы не заметили бы, что в исходной таблице поиска у нас есть ошибка деления на ноль, так как не заполнена цена персиков. Поэтому более разумно использовать ЕСНД, чтобы с ее помощью обработать только ошибки поиска:
=ЕСНД(ВПР(F3; $A$3:$D$6; 4;ЛОЖЬ); «Не найдено»)
Или подойдет комбинация ЕСЛИ ЕНД для старых версий Excel:
=ЕСЛИ(ЕНД(ВПР(F3; $A$3:$D$6; 4;ЛОЖЬ));»Не найдено»; ВПР(F3; $A$3:$D$6; 4;ЛОЖЬ))
Как видите, формула ЕСНД с ВПР возвращает «Не найдено» только для товара, которого нет в таблице поиска (Сливы). Для персиков она показывает #ДЕЛ/0! что указывает на то, что наша таблица поиска содержит ошибку деления на ноль.
Рекомендации по использованию ЕСЛИОШИБКА в Excel
Итак, вы уже знаете, что функция ЕСЛИОШИБКА — это самый простой способ отлавливать ошибки в Excel и маскировать их пустыми ячейками, нулевыми значениями или собственными сообщениями. Однако это не означает, что вы должны обернуть каждую формулу в функцию обработки ошибок.
Эти простые рекомендации могут помочь вам сохранить баланс.
- Не ловите ошибки без весомой на то причины.
- Оберните в ЕСЛИОШИБКА только ту часть формулы, где по вашему мнению могут возникнуть проблемы.
- Чтобы обрабатывать только определенные ошибки, используйте другую функцию обработки ошибок с меньшей областью действия:
- ЕСНД или ЕСЛИ ЕНД для обнаружения только ошибок #H/Д.
- ЕОШ для обнаружения всех ошибок, кроме #Н/Д.
Мы постарались рассказать, как можно использовать функцию ЕСЛИОШИБКА в Excel. Примеры перехвата и обработки ошибок могут быть полезны и для «чайников», и для более опытных пользователей.
Также рекомендуем:
- Что делает ЕСЛИОШИБКА?
- Синтаксис
- Пример 1: ЕСЛИОШИБКА + ВПР
- Пример 2: ЕСЛИОШИБКА + деление на ноль
- Пример 3: ЕСЛИОШИБКА в формулах массива
- Другие логические функции
| Раздел функций | Логические |
| Название на английском | IFERROR |
| Волатильность | Не волатильная |
| Похожие функции | ЕСЛИ, ЕОШ |
Что делает ЕСЛИОШИБКА?
Часто при использовании формул, если результат возвращает ошибку, нужно обрабатывать ее, а если нет – возвращать результат вычисления.
Именно эту задачу и решает функция ЕСЛИОШИБКА.
Функция проверяет входящее значение/вычисление на ошибочность, если ошибки нет, возвращает его само.
Оцениваются следующие типы ошибок: #Н/Д, #ЗНАЧ!, #ССЫЛКА!, #ДЕЛ/0!, #ЧИСЛО!, #ИМЯ? и #ПУСТО!
Эквивалентным сочетанием была бы формула на основе комбинации функции ЕСЛИ и ЕОШ.
Такая комбинация дважды использует вычислительные ресурсы – один раз чтобы проверить результат на наличие ошибки, а второй уже для произведения вычисления, если ошибки нет.
ЕСЛИ(ЕОШ(вычисление);"результат-если-ошибка";вычисление)
Отличие ЕСЛИОШИБКА в том, что она производит вычисление однократно и экономит ресурсы.
Также использование функции упрощает синтаксис формул.
Синтаксис
Синтаксис функции ЕСЛИОШИБКА предполагает всего два аргумента, оба – обязательные:
=ЕСЛИОШИБКА(Значение-Или-Вычисление;Значение-если-ошибка)
Пример 1: ЕСЛИОШИБКА + ВПР
Наиболее характерный пример использования – в паре с функцией ВПР при поиске данных в больших таблицах.
=ЕСЛИОШИБКА(ВПР(ячейка-или-значение;диапазон;номер-столбца;0);"ошибка") =ЕСЛИОШИБКА(ВПР(ячейка-или-значение;диапазон;номер-столбца;0);"")
Первый вариант вернет текстовое значение ошибки, второй оставит строку пустой.
Поскольку ВПР может изрядно загрузить процессор, функция ЕСЛИОШИБКА здесь весьма кстати.
Пример 2: ЕСЛИОШИБКА + деление на ноль
Задача маркетолога – произвести оценку эффективности рекламных кампаний. Один из ключевых показателей – стоимость привлечения клиента. Рассчитывается он довольно просто – расходы по рекламным кампаниям делятся на количество приведенных ими клиентов.
Что делать, когда кампания не привела ни одного? Вычисление выдаст ошибку
#ДЕЛ/0!
Оставлять ее не стоит, ведь деньги на рекламу были потрачены, и нужно это учитывать.
Лучший вариант – представить, что один клиент был приведен, т.к. рано или поздно это произойдет, а эффективность нужно смотреть уже сейчас. Функция ЕСЛИОШИБКА дает возможность вернуть весь расход на кампанию, если возникает ошибка деления на ноль.
Наиболее наглядна польза от такой формулы – если использовать условное форматирование с цветовой шкалой. Сразу бросаются в глаза эффективные и неэффективные кампании.
Пример 3: ЕСЛИОШИБКА в формулах массива
Проблема функций МИН и МАКС при работе с массивами в том, что, если в массиве присутствует хотя бы одна ошибка, они также возвращают ошибку.
Поэтому, если есть вероятность такого события, функцию ЕСЛИОШИБКА нужно использовать как обработчик, чтобы избежать результирующей ошибки.
Формула для поиска позиции первого символа латиницы:
{=МИН(ЕСЛИОШИБКА(ПОИСК(СИМВОЛ(СТРОКА(65:90));A1);""))}
Механика ее работы такова:
- Функция СИМВОЛ создает массив букв английского алфавита;
- Функция ПОИСК ищет позицию каждой буквы в строке;
- Если буква не найдена, функция вернет ошибку Н/Д;
- Функция ЕСЛИОШИБКА возвращает пустую строку в таких случаях, а числа оставляет как они есть;
- Функция МИН пропускает пустые строки и возвращает минимальное число;
- Если весь массив будет состоять из пустых строк, функция МИН вернет 0.
А такая формула использует массив констант и ищет позицию первой цифры:
{=МИН(ЕСЛИОШИБКА(ПОИСК({1:2:3:4:5:6:7:8:9:0};A1);""))}Читайте подробнее в статье про формулы массива.
Другие логические функции
ЕСЛИ, И, ИЛИ, НЕ
Понравилась статья? Поддержите ее автора!
Набор инструментов !SEMTools поможет решить множество задач без ввода сложных формул и сэкономит ваше время. И вам полезно, и автору приятно!
В поисковых функциях Excel: ВПР, ГПР, ПОИСКПОЗ чаще всего в третьем аргументе используется значение ЛОЖЬ или 0. Так пользователь заставляет искать в исходной таблице только точные совпадения значений при поиске. Если в поисковой функции будет в третьем аргументе определено точное совпадение, а искомое значение не будет найдено в таблице, тогда функция возвращает ошибку с кодом #Н/Д!
Формула ЕСЛИОШИБКА обработки ошибок функции ВПР в Excel
Ошибка #Н/Д! пригодится в анализе моделей данных Excel, так как информирует пользователя и программу о том, что не было найдено соответственное значение. Однако если большая часть такой модели данных будет использована в отчетах, то код ошибки #Н/Д! будет смотреться некорректно. Для этого Excel предлагает функции, которые проверяют результаты вычислений на ошибки и позволяют возвращать другие альтернативные значения.
Ниже на рисунке представлена таблица фирм с фамилиями их руководителей. Вторая таблица содержит те же фамилии и соответствующие им оклады. Функция ВПР используется для соединения двух таблиц в одну. Но не по всем руководителям имеются данные об их окладах, поэтому часто встречается код ошибки #Н/Д! в результатах вычисления функции ВПР.
Формула, изображенная на следующем рисунке уже изменена. Она использует функцию ЕСЛИОШИБКА и возвращает пустую строку в том случае если искомое значение не найдено в исходной таблице:
Пользователи часто называют эту функцию «скрывающая ошибки». Так как она позволяет определить и укрыть любые ошибки, которые можно после этого воспринимать по-другому. А не сметить этими некрасивыми кодами в отчетах для презентации.
Первый аргумент функции ЕСЛИОШИБКА – это выражение или формула, а во втором аргументе следует указать альтернативное значение, которое должно отображаться при возникновении ошибки. Если в первом аргументе выражение или формула вернет ошибку, тогда функция вместо его значения возвратит второй аргумент. В противные случаи будет возвращено значение первого аргумента.
В данном примере альтернативным значением является пустая строка (двойные кавычки без каких-либо символов между ними). Благодаря этому отчет более читабельный и имеет презентабельный вид. Данная функция может возвращать любое значение, например, «Нет данных» или число 0.
Функции для работы с кодами ошибок в Excel
Функция ЕСЛИОШИБКА проверяет каждую ошибку, которую способна вернуть формула в Excel. Но следует использовать ее с определенной осторожностью. Ведь она способна скрывать все ошибки без разбора и даже такие важные как: #ДЕЛ/0! или #ЧИСЛО! и т.п.
Чтобы скрывать только определенную группу ошибок Excel предлагает еще 3 других функций:
- ЕОШИБКА – возвращает логическое значение ИСТИНА если ее аргумент содержит ошибку: #Н/Д, #ЗНАЧ!, #ССЫЛКА!, #ДЕЛ/0!, #ЧИСЛО!, #ИМЯ? или #ПУСТО.
- ЕОШ – функция возвращает ИСТИНА если ее аргумент содержит любую ошибку, кроме #Н/Д!
- ЕНД – возвращает значение ИСТИНА если ее аргумент содержит ошибку с кодом #Н/Д! или ЛОЖЬ если аргумент содержит любое значение или любую другую ошибку.
Три выше описанные функции для обработки ошибок в Excel возвращают логические значения ИСТИНА или ЛОЖЬ наиболее часто используются вместе с функцией ЕСЛИ.
Формула ЕСЛИ и ЕДН для ошибок ВПР без функции ЕСЛИОШИБКА в Excel
Бескомпромиссная функция обработки ошибок ЕСЛИОШИБКА появилась в программе Excel начиная с 2010-й версии. Для проверки ошибок в старших версиях Excel наиболее часто использовалась функция ЕНД:
Функция ЕНД возвращает логическое значение ИСТИНА если в ее аргументе находится только один тип ошибок – #Н/Д! Или же значение ЛОЖЬ при любых других значениях. В данной формуле функция ЕСЛИ помогает функции ЕНД. Если была получена ошибка #Н/Д! формула возвращает пустую строку – указано во втором аргументе функции ЕСЛИ. В противные случаи возвращается результат вычисления функции ВПР – указано в третьем аргументе ЕСЛИ.
Главным недостатком такой формулы является необходимость дублировать функцию ВПР:
- первый разу внутри функции ЕНД;
- второй раз в третьем аргументе ЕСЛИ.
Это значит, что Excel должен два раза выполнять функцию ВПР для одной и той же ячейки. Если на листе содержится множество таких формул, тогда их пересчет требует много времени и системных ресурсов. Очень неудобно будет работать с такими файлами. Возникнет необходимость отключения автоматического пересчета формул: «ФОРМУЛЫ»-«Вычисления»-«Параметры вычислений»-«Вручную».
Этот урок объясняет, как быстро справиться с ситуацией, когда функция ВПР (VLOOKUP) не хочет работать в Excel 2013, 2010, 2007 и 2003, а также, как выявить и исправить распространённые ошибки и преодолеть ограничения ВПР.
В нескольких предыдущих статьях мы изучили различные грани функции ВПР в Excel. Если Вы читали их внимательно, то сейчас должны быть экспертом в этой области. Однако не без причины многие специалисты по Excel считают ВПР одной из наиболее сложных функций. Она имеет кучу ограничений и особенностей, которые становятся источником многих проблем и ошибок.
В этой статье Вы найдёте простые объяснения ошибок #N/A (#Н/Д), #NAME? (#ИМЯ?) и #VALUE! (#ЗНАЧ!), появляющихся при работе с функцией ВПР, а также приёмы и способы борьбы с ними. Мы начнём с наиболее частых случаев и наиболее очевидных причин, почему ВПР не работает, поэтому лучше изучать примеры в том порядке, в каком они приведены в статье.
- Исправляем ошибку #Н/Д
- Исправляем ошибку #ЗНАЧ! в формулах с ВПР
- Ошибка #ИМЯ? в ВПР
- ВПР не работает (проблемы, ограничения и решения)
- ВПР – работа с функциями ЕСЛИОШИБКА и ЕОШИБКА
Содержание
- Исправляем ошибку #Н/Д функции ВПР в Excel
- 1. Искомое значение написано с опечаткой
- 2. Ошибка #Н/Д при поиске приближённого совпадения с ВПР
- 3. Ошибка #Н/Д при поиске точного совпадения с ВПР
- 4. Столбец поиска не является крайним левым
- 5. Числа форматированы как текст
- 6. В начале или в конце стоит пробел
- Ошибка #ЗНАЧ! в формулах с ВПР
- 1. Искомое значение длиннее 255 символов
- 2. Не указан полный путь к рабочей книге для поиска
- 3. Аргумент Номер_столбца меньше 1
- Ошибка #ИМЯ? в ВПР
- ВПР не работает (ограничения, оговорки и решения)
- 1. ВПР не чувствительна к регистру
- 2. ВПР возвращает первое найденное значение
- 3. В таблицу был добавлен или удалён столбец
- 4. Ссылки на ячейки исказились при копировании формулы
- ВПР – работа с функциями ЕСЛИОШИБКА и ЕОШИБКА
- ВПР: работа с функцией ЕСЛИОШИБКА
- ВПР: работа с функцией ЕОШИБКА
Исправляем ошибку #Н/Д функции ВПР в Excel
В формулах с ВПР сообщение об ошибке #N/A (#Н/Д) – означает not available (нет данных) – появляется, когда Excel не может найти искомое значение. Это может произойти по нескольким причинам.
1. Искомое значение написано с опечаткой
Хорошая мысль проверить этот пункт в первую очередь! Опечатки часто возникают, когда Вы работаете с очень большими объёмами данных, состоящих из тысяч строк, или когда искомое значение вписано в формулу.
2. Ошибка #Н/Д при поиске приближённого совпадения с ВПР
Если Вы используете формулу с условием поиска приближённого совпадения, т.е. аргумент range_lookup (интервальный_просмотр) равен TRUE (ИСТИНА) или не указан, Ваша формула может сообщить об ошибке #Н/Д в двух случаях:
- Искомое значение меньше наименьшего значения в просматриваемом массиве.
- Столбец поиска не упорядочен по возрастанию.
3. Ошибка #Н/Д при поиске точного совпадения с ВПР
Если Вы ищете точное совпадение, т.е. аргумент range_lookup (интервальный_просмотр) равен FALSE (ЛОЖЬ) и точное значение не найдено, формула также сообщит об ошибке #Н/Д. Более подробно о том, как искать точное и приближенное совпадение с функцией ВПР.
4. Столбец поиска не является крайним левым
Как Вы, вероятно, знаете, одно из самых значительных ограничений ВПР это то, что она не может смотреть влево, следовательно, столбец поиска в Вашей таблице должен быть крайним левым. На практике мы часто забываем об этом, что приводит к не работающей формуле и появлению ошибки #Н/Д.
Решение: Если нет возможности изменить структуру данных так, чтобы столбец поиска был крайним левым, Вы можете использовать комбинацию функций ИНДЕКС (INDEX) и ПОИСКПОЗ (MATCH), как более гибкую альтернативу для ВПР.
5. Числа форматированы как текст
Другой источник ошибки #Н/Д в формулах с ВПР – это числа в текстовом формате в основной таблице или в таблице поиска.
Это обычно случается, когда Вы импортируете информацию из внешних баз данных или когда ввели апостроф перед числом, чтобы сохранить стоящий в начале ноль.
Наиболее очевидные признаки числа в текстовом формате показаны на рисунке ниже:
Кроме этого, числа могут быть сохранены в формате General (Общий). В таком случае есть только один заметный признак – числа выровнены по левому краю ячейки, в то время как стандартно они выравниваются по правому краю.
Решение: Если это одиночное значение, просто кликните по иконке ошибки и выберите Convert to Number (Конвертировать в число) из контекстного меню.
Если такая ситуация со многими числами, выделите их и щелкните по выделенной области правой кнопкой мыши. В появившемся контекстном меню выберите Format Cells (Формат ячеек) > вкладка Number (Число) > формат Number (Числовой) и нажмите ОК.
6. В начале или в конце стоит пробел
Это наименее очевидная причина ошибки #Н/Д в работе функции ВПР, поскольку зрительно трудно увидеть эти лишние пробелы, особенно при работе с большими таблицами, когда большая часть данных находится за пределами экрана.
Решение 1: Лишние пробелы в основной таблице (там, где функция ВПР)
Если лишние пробелы оказались в основной таблице, Вы можете обеспечить правильную работу формул, заключив аргумент lookup_value (искомое_значение) в функцию TRIM (СЖПРОБЕЛЫ):
=VLOOKUP(TRIM($F2),$A$2:$C$10,3,FALSE)
=ВПР(СЖПРОБЕЛЫ($F2);$A$2:$C$10;3;ЛОЖЬ)
Решение 2: Лишние пробелы в таблице поиска (в столбце поиска)
Если лишние пробелы оказались в столбце поиска – простыми путями ошибку #Н/Д в формуле с ВПР не избежать. Вместо ВПР Вы можете использовать формулу массива с комбинацией функций ИНДЕКС (INDEX), ПОИСКПОЗ (MATCH) и СЖПРОБЕЛЫ (TRIM):
=INDEX($C$2:$C$10,MATCH(TRUE,TRIM($A$2:$A$10)=TRIM($F$2),0))
=ИНДЕКС($C$2:$C$10;ПОИСКПОЗ(ИСТИНА;СЖПРОБЕЛЫ($A$2:$A$10)=СЖПРОБЕЛЫ($F$2);0))
Так как это формула массива, не забудьте нажать Ctrl+Shift+Enter вместо привычного Enter, чтобы правильно ввести формулу.
Ошибка #ЗНАЧ! в формулах с ВПР
В большинстве случаев, Microsoft Excel сообщает об ошибке #VALUE! (#ЗНАЧ!), когда значение, использованное в формуле, не подходит по типу данных. Что касается ВПР, то обычно выделяют две причины ошибки #ЗНАЧ!.
1. Искомое значение длиннее 255 символов
Будьте внимательны: функция ВПР не может искать значения, содержащие более 255 символов. Если искомое значение превышает этот предел, то Вы получите сообщение об ошибке #ЗНАЧ!.
Решение: Используйте связку функций ИНДЕКС+ПОИСКПОЗ (INDEX+MATCH). Ниже представлена формула, которая отлично справится с этой задачей:
=INDEX(C2:C7,MATCH(TRUE,INDEX(B2:B7=F$2,0),0))
=ИНДЕКС(C2:C7;ПОИСКПОЗ(ИСТИНА;ИНДЕКС(B2:B7=F$2;0);0))
2. Не указан полный путь к рабочей книге для поиска
Если Вы извлекаете данные из другой рабочей книги, то должны указать полный путь к этому файлу. Если говорить точнее, Вы должны указать имя рабочей книги (включая расширение) в квадратных скобках [ ], далее указать имя листа, а затем – восклицательный знак. Всю эту конструкцию нужно заключить в апострофы, на случай если имя книги или листа содержит пробелы.
Вот полная структура функции ВПР для поиска в другой книге:
=VLOOKUP(lookup_value,'[workbook name]sheet name'!table_array, col_index_num,FALSE)
=ВПР(искомое_значение;'[имя_книги]имя_листа'!таблица;номер_столбца;ЛОЖЬ)
Настоящая формула может выглядеть так:
=VLOOKUP($A$2,'[New Prices.xls]Sheet1'!$B:$D,3,FALSE)
=ВПР($A$2;'[New Prices.xls]Sheet1'!$B:$D;3;ЛОЖЬ)
Эта формула будет искать значение ячейки A2 в столбце B на листе Sheet1 в рабочей книге New Prices и извлекать соответствующее значение из столбца D.
Если любая часть пути к таблице пропущена, Ваша функция ВПР не будет работать и сообщит об ошибке #ЗНАЧ! (даже если рабочая книга с таблицей поиска в данный момент открыта).
Для получения дополнительной информации о функции ВПР, ссылающейся на другой файл Excel, обратитесь к уроку: Поиск в другой рабочей книге с помощью ВПР.
3. Аргумент Номер_столбца меньше 1
Трудно представить ситуацию, когда кто-то вводит значение меньше 1, чтобы обозначить столбец, из которого нужно извлечь значение. Хотя это возможно, если значение этого аргумента вычисляется другой функцией Excel, вложенной в ВПР.
Итак, если случилось, что аргумент col_index_num (номер_столбца) меньше 1, функция ВПР также сообщит об ошибке #ЗНАЧ!.
Если же аргумент col_index_num (номер_столбца) больше количества столбцов в заданном массиве, ВПР сообщит об ошибке #REF! (#ССЫЛ!).
Ошибка #ИМЯ? в ВПР
Простейший случай – ошибка #NAME? (#ИМЯ?) – появится, если Вы случайно напишите с ошибкой имя функции.
Решение очевидно – проверьте правописание!
ВПР не работает (ограничения, оговорки и решения)
Помимо достаточно сложного синтаксиса, ВПР имеет больше ограничений, чем любая другая функция Excel. Из-за этих ограничений, простые на первый взгляд формулы с ВПР часто приводят к неожиданным результатам. Ниже Вы найдёте решения для нескольких распространённых сценариев, когда ВПР ошибается.
1. ВПР не чувствительна к регистру
Функция ВПР не различает регистр и принимает символы нижнего и ВЕРХНЕГО регистра как одинаковые. Поэтому, если в таблице есть несколько элементов, которые различаются только регистром символов, функция ВПР возвратит первый попавшийся элемент, не взирая на регистр.
Решение: Используйте другую функцию Excel, которая может выполнить вертикальный поиск (ПРОСМОТР, СУММПРОИЗВ, ИНДЕКС и ПОИСКПОЗ) в сочетании с СОВПАД, которая различает регистр. Более подробно Вы можете узнать из урока — 4 способа сделать ВПР с учетом регистра в Excel.
2. ВПР возвращает первое найденное значение
Как Вы уже знаете, ВПР возвращает из заданного столбца значение, соответствующее первому найденному совпадению с искомым. Однако, Вы можете заставить ее извлечь 2-е, 3-е, 4-е или любое другое повторение значения, которое Вам нужно. Если нужно извлечь все повторяющиеся значения, Вам потребуется комбинация из функций ИНДЕКС (INDEX), НАИМЕНЬШИЙ (SMALL) и СТРОКА (ROW).
3. В таблицу был добавлен или удалён столбец
К сожалению, формулы с ВПР перестают работать каждый раз, когда в таблицу поиска добавляется или удаляется новый столбец. Это происходит, потому что синтаксис ВПР требует указывать полностью весь диапазон поиска и конкретный номер столбца для извлечения данных. Естественно, и заданный диапазон, и номер столбца меняются, когда Вы удаляете столбец или вставляете новый.
Решение: И снова на помощь спешат функции ИНДЕКС (INDEX) и ПОИСКПОЗ (MATCH). В формуле ИНДЕКС+ПОИСКПОЗ Вы раздельно задаёте столбцы для поиска и для извлечения данных, и в результате можете удалять или вставлять сколько угодно столбцов, не беспокоясь о том, что придётся обновлять все связанные формулы поиска.
4. Ссылки на ячейки исказились при копировании формулы
Этот заголовок исчерпывающе объясняет суть проблемы, правда?
Решение: Всегда используйте абсолютные ссылки на ячейки (с символом $) при записи диапазона, например $A$2:$C$100 или $A:$C. В строке формул Вы можете быстро переключать тип ссылки, нажимая F4.
ВПР – работа с функциями ЕСЛИОШИБКА и ЕОШИБКА
Если Вы не хотите пугать пользователей сообщениями об ошибках #Н/Д, #ЗНАЧ! или #ИМЯ?, можете показывать пустую ячейку или собственное сообщение. Вы можете сделать это, поместив ВПР в функцию ЕСЛИОШИБКА (IFERROR) в Excel 2013, 2010 и 2007 или использовать связку функций ЕСЛИ+ЕОШИБКА (IF+ISERROR) в более ранних версиях.
ВПР: работа с функцией ЕСЛИОШИБКА
Синтаксис функции ЕСЛИОШИБКА (IFERROR) прост и говорит сам за себя:
IFERROR(value,value_if_error)
ЕСЛИОШИБКА(значение;значение_если_ошибка)
То есть, для первого аргумента Вы вставляете значение, которое нужно проверить на предмет ошибки, а для второго аргумента указываете, что нужно возвратить, если ошибка найдётся.
Например, вот такая формула возвращает пустую ячейку, если искомое значение не найдено:
=IFERROR(VLOOKUP($F$2,$B$2:$C$10,2,FALSE),"")
=ЕСЛИОШИБКА(ВПР($F$2;$B$2:$C$10;2;ЛОЖЬ);"")
Если Вы хотите показать собственное сообщение вместо стандартного сообщения об ошибке функции ВПР, впишите его в кавычках, например, так:
=IFERROR(VLOOKUP($F$2,$B$2:$C$10,2,FALSE),"Ничего не найдено. Попробуйте еще раз!")
=ЕСЛИОШИБКА(ВПР($F$2;$B$2:$C$10;2;ЛОЖЬ);"Ничего не найдено. Попробуйте еще раз!")
ВПР: работа с функцией ЕОШИБКА
Так как функция ЕСЛИОШИБКА появилась в Excel 2007, при работе в более ранних версиях Вам придётся использовать комбинацию ЕСЛИ (IF) и ЕОШИБКА (ISERROR) вот так:
=IF(ISERROR(VLOOKUP формула),"Ваше сообщение при ошибке",VLOOKUP формула)
=ЕСЛИ(ЕОШИБКА(ВПР формула);"Ваше сообщение при ошибке";ВПР формула)
Например, формула ЕСЛИ+ЕОШИБКА+ВПР, аналогична формуле ЕСЛИОШИБКА+ВПР, показанной выше:
=IF(ISERROR(VLOOKUP($F$2,$B$2:$C$10,2,FALSE)),"",VLOOKUP($F$2,$B$2:$C$10,2,FALSE))
=ЕСЛИ(ЕОШИБКА(ВПР($F$2;$B$2:$C$10;2;ЛОЖЬ));"";ВПР($F$2;$B$2:$C$10;2;ЛОЖЬ))
На сегодня всё. Надеюсь, этот короткий учебник поможет Вам справиться со всеми возможными ошибками ВПР и заставит Ваши формулы работать правильно.
Оцените качество статьи. Нам важно ваше мнение:
В этом руководстве мы рассмотрим, как использовать ЕОШИБКУ с ВПР в Excel для продуктивной обработки всех видов ошибок.
ВПР — одна из самых запутанных функций Excel, из-за которой возникает множество проблем. В какой бы таблице вы ни искали, ошибки #N/A являются обычным явлением, а #NAME и #VALUE также появляются время от времени. Использование функции ВПР с ЕОШИБКА может помочь вам обнаружить все возможные ошибки и обработать их наиболее подходящим для вашей ситуации способом.
Почему ВПР выдает ошибку?
Наиболее распространенной ошибкой в формулах ВПР является ошибка #Н/Д, возникающая, когда искомое значение не найдено. Это может происходить по разным причинам:
- Значение поиска не существует в массиве поиска.
- Искомое значение написано с ошибкой.
- В искомом значении или столбце поиска есть начальные или конечные пробелы.
- Столбец подстановки не является самым левым столбцом массива таблиц.
Кроме того, вы можете нарваться на #ЗНАЧ! ошибка, например, когда искомое значение содержит более 255 символов. Если в имени функции есть орфографическая ошибка, #NAME? появится ошибка.
Полную информацию см. в нашем предыдущем сообщении о том, почему функция ВПР в Excel не работает.
ЕСЛИ ОШИБКА Формула VLOOKUP для замены ошибок пользовательским текстом
Чтобы скрыть все возможные ошибки, которые могут быть вызваны функцией ВПР, вы можете поместить ее в формулу ЕСЛИ ОШИБКА следующим образом:
ЕСЛИ(ОШИБКА(ВПР(…)), «text_if_error«, ВПР(…))
В качестве примера вытянем названия предметов, по которым ученики группы А провалили тесты:
=ВПР(A3, $D$3:$E$9, 2, ЛОЖЬ)
В результате вы получаете кучу ошибок #Н/Д, что может создать впечатление, что формула повреждена.
На самом деле эти ошибки просто указывают на то, что некоторые значения поиска (A3:A14) не найдены в списке поиска (D3:D9). Чтобы четко передать эту мысль, вложите формулу ВПР в конструкцию ЕСЛИ ОШИБКА:
=ЕСЛИ(ЕОШИБКА(ВПР(A3, $D$3:$E$9, 2, ЛОЖЬ)), «Нет», ВПР(A3, $D$3:$E$9, 2, ЛОЖЬ))
Это уловит ошибки и вернет ваше собственное текстовое сообщение:
Советы и примечания:
- Основное преимущество этой формулы в том, что она прекрасно работает во всех версиях Excel 2000 по Excel 365. В современных версиях проще и компактнее альтернативы доступны.
- Функция ЕОШИБКА перехватывает абсолютно все ошибки, такие как #Н/Д, #ИМЯ, #ЗНАЧ и т. д. ЕСЛИ ВПР ЕСЛИ ЕСТЬ (во всех версиях) или ЕСЛИ ВПР (в Excel 2013 и более поздних версиях).
ISERROR VLOOKUP для возврата пустой ячейки в случае ошибки
Чтобы иметь пустую ячейку при возникновении ошибки, заставьте формулу возвращать пустую строку («») вместо пользовательского текста:
ЕСЛИ(ЕОШИБКА(ВПР(…)), «», ВПР(…))
В нашем случае формула принимает такой вид:
=ЕСЛИ(ЕОШИБКА(ВПР(A3, $D$3:$E$9, 2, ЛОЖЬ)), «», ВПР(A3, $D$3:$E$9, 2, ЛОЖЬ))
Результат именно такой, как и ожидалось — пустая ячейка, если имя учащегося не найдено в таблице поиска.
Кончик. Аналогичным образом вы можете заменить ошибки ВПР нулями, тире или любым другим символом, который вам нравится. Просто используйте нужный символ вместо пустой строки.
ЕСЛИ ОШИБКА ВПР Формула Да/Нет
В какой-то ситуации вы можете что-то искать, но вместо того, чтобы тянуть спички, просто хотите вернуться Да (или другой текст, если искомое значение найдено) и Нет (если искомое значение не найдено). Чтобы это сделать, вы можете использовать эту общую формулу:
ЕСЛИ(ОШИБКА(ВПР(…)), «text_if_not_found«, «text_if_found«)
Предположим, что в нашем образце данных вы хотите узнать, какие учащиеся провалили тест, а какие нет. Для этого подайте уже знакомую формулу ВПР ЕОШИБКА для логической проверки ЕСЛИ и скажите ей выводить «Нет», если значение не найдено (ВПР ЕОШИБКА возвращает ИСТИНА), и «Да», если найдено (ВПР ЕОШИБКА возвращает ЛОЖЬ):
=ЕСЛИ(ЕОШИБКА(ВПР(A3, $D$3:$E$9, 2, ЛОЖЬ)), «Нет», «Да»)
IERROR VLOOKUP альтернативы
Комбинация ЕСЛИ ОШИБКА — это старейший проверенный временем метод ВПР без ошибок в Excel. Со временем появились новые функции, обеспечивающие более простые способы выполнения той же задачи. Ниже мы обсудим другие возможные решения и когда каждое из них лучше всего применять.
ЕСЛИ ОШИБКА ВПР
Доступно в Excel 2007 и выше
Начиная с версии 2007, в Excel есть специальная функция ЕСЛИОШИБКА, которая проверяет формулу на наличие ошибок и возвращает собственный текст (или запускает альтернативную формулу) при обнаружении какой-либо ошибки.
ЕСЛИОШИБКА(ВПР(…), «text_if_error«)
Реальная формула выглядит следующим образом:
=ЕСЛИОШИБКА(ВПР(A3, $D$3:$E$9, 2, ЛОЖЬ), «Нет»)
На первый взгляд это выглядит как укороченный аналог формулы ЕСЛИ ОШИБКА ВПР. Однако есть существенное отличие:
- ЕСЛИОШИБКА ВПР предполагает, что вам всегда нужен результат ВПР, если это не ошибка.
- ЕСЛИ ОШИБКА VLOOKUP позволяет указать, что возвращать, если ошибка и что, если ошибки нет.
Дополнительные сведения см. в разделе Использование ЕСЛИОШИБКА с функцией ВПР в Excel.
ЕСЛИ ISNA ВПР
Работает в Excel 2000 и более поздних версиях
В ситуации, когда вы хотите перехватывать только #N/A, не перехватывая никаких других ошибок, вам пригодится функция ISNA. Синтаксис такой же, как у IF IERROR VLOOKUP:
ЕСЛИ(ИСНА(ВПР(…)), «text_if_error«, ВПР(…))
Но при определенных обстоятельствах эта, казалось бы, идентичная формула может давать разные результаты:
=ЕСЛИ(ИСНА(ВПР(A3, $D$3:$E$9, 2, ЛОЖЬ)), «Нет», ВПР(A3, $D$3:$E$9, 2, ЛОЖЬ))
На изображении ниже ячейка A13 содержит много пробелов в конце, из-за чего общая длина искомого значения превышает 255 символов. В результате формула вызывает ошибку #ЗНАЧ! ошибка, привлекая ваше внимание к этой ячейке и призывая разобраться в причинах. IERROR VLOOKUP в этом случае вернет «Нет», что только скроет проблему и даст абсолютно неверный результат.
Когда использовать:
Эта формула прекрасно работает в ситуации, когда вы хотите отобразить некоторый текст только тогда, когда значение поиска не найдено, и не хотите маскировать основные проблемы с самой формулой ВПР, например, когда имя функции введено с ошибкой (#ИМЯ?) или не указан полный путь к книге поиска (#ЗНАЧ!).
Для получения дополнительной информации см. функцию ISNA в Excel с примерами формул.
IFNA VLOOKUP
Доступно в Excel 2013 и выше
Это современная замена комбинации IF ISNA, которая упрощает обработку ошибок #N/A.
ЕСЛИНА(ВПР(…), «text_if_error«)
Вот сокращенный эквивалент нашей формулы IF ISNA VLOOKUP:
=ЕСЛИНА(ВПР(A3, $D$3:$E$9, 2, ЛОЖЬ), «Нет»)
Когда использовать:
Это идеальное решение для перехвата и обработки ошибок #N/A в современных версиях Excel (2013–365).
Для получения полной информации см. Функция Excel IFNA.
XLOOKUP
Поддерживается в Excel 2021 и Excel 365.
Благодаря встроенной функции «если ошибка» функция XLOOKUP — это самый простой способ поиска без ошибок #N/A в Excel. Просто введите удобный для пользователя текст в необязательный 4-й аргумент с именем если_не_найдено.
Например:
=XLOOKUP(A3, $D$3:$D$9, $E$3:$E$9, «Для»)
Ограничение: он перехватывает только ошибки #N/A, игнорируя другие типы.
Для получения дополнительной информации ознакомьтесь с функцией XLOOKUP в Excel.
Как видите, Excel предоставляет довольно много различных возможностей для обработки ошибок ВПР. Надеюсь, этот урок пролил свет на то, как их эффективно использовать. Я благодарю вас за чтение и надеюсь увидеть вас в нашем блоге на следующей неделе!
Доступные загрузки
IERROR с примерами VLOOKUP (файл .xlsx)