Как игнорировать ошибки при использовании функции ВПР в Excel?
Обычно, если в справочной таблице есть ячейки с ошибками, функция ВПР также возвращает эти ошибки (см. снимок экрана ниже). Однако некоторым пользователям может быть не нужно отображать ошибки в новой таблице. Как игнорировать ошибки из исходной справочной таблицы при использовании функции ВПР в Excel? Ниже мы предлагаем два быстрых способа решения этой задачи.
- Игнорирование ошибок при использовании функции ВПР путём изменения исходной справочной таблицы
- Игнорирование ошибок при использовании функции ВПР путём комбинирования функций ВПР и ЕСЛИ
- Игнорирование ошибок при использовании функции ВПР с помощью замечательного инструмента
Игнорирование ошибок при использовании функции ВПР путём изменения исходной справочной таблицы
Этот метод позволит вам изменить исходную справочную таблицу, создать новый столбец или таблицу без ошибок, а затем применить к ним функцию ВПР в Excel.
1. Помимо исходной справочной таблицы вставьте пустой столбец и задайте ему имя.
В нашем случае мы вставили пустой столбец сразу после столбца «Возраст» и указали имя «Возраст (игнорировать ошибки)» в ячейке D1.
2. Введите приведённую ниже формулу в ячейку D2, а затем протяните маркер заполнения до нужного диапазона.
=IF(ISERROR(C2),«»,C2)
Теперь в новом столбце «Возраст (игнорировать ошибки)» все ячейки, содержащие ошибки, заменены пустыми значениями.
3. Перейдите в ячейку (в нашем случае — G2), куда будут выводиться результаты функции ВПР, введите приведённую ниже формулу и протяните маркер заполнения до нужного диапазона.
=VLOOKUP(F2,$A$2:$D$9,4,FALSE)
Теперь вы увидите, что если в исходной справочной таблице есть ошибка, функция ВПР вернёт пустое значение.
Игнорирование ошибок при использовании функции ВПР путём комбинирования функций ВПР и ЕСЛИ
Иногда вам может не захотеться изменять исходную справочную таблицу. В таком случае можно объединить функции ВПР, ЕСЛИ и ЕОШИБКА, чтобы проверять, возвращает ли ВПР ошибку, и легко игнорировать такие ошибки.
Перейдите в пустую ячейку (в нашем случае — G2), введите приведённую ниже формулу и протяните маркер заполнения до нужного диапазона.
=IF(ISERROR(VLOOKUP(F2,$A$2:$C $9,3,FALSE)),«»,VLOOKUP(F2,$A$2:$C $9,3,FALSE))
В приведённой выше формуле:
- F2 — это ячейка, содержащая значение, которое необходимо найти в исходной справочной таблице
- $A$2:$C $9 — это исходная справочная таблица
- Эта формула вернёт пустое значение, если найденное в исходной справочной таблице значение окажется ошибкой.
 | Слишком сложная формула, чтобы её запомнить? Сохраните формулу как элемент автотекста и используйте её в будущем всего одним щелчком! Подробнее… Бесплатная пробная версия |
Игнорирование ошибок при использовании функции ВПР с помощью замечательного инструмента
Если у вас установлен Kutools для Excel, воспользуйтесь функцией Заменить 0 или #N/A пустыми/указанным значением, чтобы игнорировать ошибки при использовании функции ВПР в Excel. Выполните следующие действия:
Kutools для Excel — это более 300 удобных инструментов для Excel! Полнофункциональная бесплатная пробная версия на 30 дней без привязки банковской карты.Получить сейчас
1. Нажмите Kutools > Супер ПОИСК > Заменить 0 или #N/A пустыми/указанным значением, чтобы активировать эту функцию.
2. В появившемся диалоговом окне выполните следующие действия:
(1) В поле Диапазон значений поиска укажите диапазон, содержащий значения для поиска.
(2) В поле Область размещения списка укажите диапазон назначения, куда будут помещены возвращаемые значения.
(3) Выберите способ обработки ошибок в результатах. Если вы не хотите отображать ошибки, установите флажок напротив опции Заменить ошибки 0 или #Н/Д пустыми значениями; если же вы хотите пометить ошибки текстом, установите флажок напротив опции Заменить 0 или N/A указанным значением и введите нужный текст в поле ниже;
(4) В поле Диапазон данных выберите таблицу для поиска;
(5) В поле Ключевой столбец укажите номер столбца, содержащего значения для поиска;
(6) В поле Столбец для возврата укажите номер столбца, содержащего значения для сопоставления.
3. Нажмите кнопку ОК.
Теперь вы увидите, что значения, соответствующие диапазону поиска, найдены и помещены в указанный диапазон назначения, а ошибки заменены либо пустыми ячейками, либо заданным текстом.
Связанные статьи:
Лучшие инструменты повышения продуктивности в Office
Раскройте весь потенциал Excel с помощью Kutools для Excel и ощутите эффективность как никогда раньше.Kutools для Excel предлагает более 300 расширенных функций для повышения продуктивности и Экономия времени.Нажмите здесь, чтобы получить нужную Вам функцию…
Office Tab добавляет в Office вкладки и значительно упрощает Вашу работу
- Включите редактирование и чтение во вкладках в Word, Excel, PowerPoint, Publisher, Access, Visio и Project.
- Открывайте и создавайте несколько документов во вкладках одного окна — вместо того чтобы использовать отдельные окна.
- Повышает вашу продуктивность на 50 % и экономит сотни кликов мышью каждый день!
Все надстройки Kutools — один установщик
Kutools for Office — набор надстроек для Excel, Word, Outlook и PowerPoint, а также Office Tab Pro, идеально подходящий командам, работающим с разными приложениями Office.
- Единый комплект— надстройки для Excel, Word, Outlook и PowerPoint + Office Tab Pro
- Один установщик, одна лицензия— настройка занимает считанные минуты (готово для MSI)
- Лучше работать вместе— оптимизированная продуктивность во всех приложениях Office
- 30-дневная полнофункциональная пробная версия— без регистрации и кредитной карты
- Лучшее соотношение цены и качества— экономия по сравнению с покупкой отдельных надстроек