Как выполнить VLOOKUP и вернуть ноль вместо # N / A в Excel?

Exceltip

Если поискать функции подстановки, Google покажет, что VRP гораздо популярнее функции INDEX. Это понятно, поскольку для того, чтобы функция ИНДЕКС работала так же, как и ВПР, необходимо использовать другую формулу — SEARCHPOPE. Для меня попытка освоить две новые функции одновременно всегда была сложной задачей. Однако они дают больше возможностей и гибкости при создании электронных таблиц. Но больше всего.

Функция ВПР()

Предположим, у вас есть таблица, содержащая данные о сотрудниках. Первый столбец содержит номер должности сотрудника, а остальные столбцы — другие данные (имя, отдел и т.д.). Если у вас есть номер табеля учета рабочего времени, вы можете использовать функцию FFT для возврата конкретной информации о сотруднике. Синтаксис формулы =FRP(look_value; table; number_of_columns; [range_view]). Он сообщает Excel: «Найдите в таблице строку, первая ячейка которой идентична значению look_value, и верните значение ячейки с последовательностью column_number.

Но бывают ситуации, когда у вас есть имя сотрудника и нужно вернуть табельный номер. Как видно на рисунке, имя сотрудника находится в ячейке A10, а табельный номер — в ячейке B10.

Если ключевое поле находится справа от извлекаемых данных, VPR не поможет. Конечно, если бы вы могли установить column_number в -1, то проблемы бы не было. Обычным решением является добавление нового столбца A, копирование имен сотрудников в этот столбец, заполнение номеров табелей учета рабочего времени с помощью VPR, сохранение их как значений и удаление временного столбца A.

Функция ИНДЕКС()

Чтобы решить нашу проблему за один шаг, используйте формулы ИНДЕКС и ПОИСК. Проблема с этим подходом заключается в том, что он требует от вас использования двух функций, которые вы, возможно, никогда не использовали раньше. Чтобы облегчить понимание этой задачи, мы будем решать ее в два этапа.

Начнем с функции INDEX. Это кошмарное название. Когда кто-то говорит «индекс», у меня в голове не возникает ни одной ассоциации о том, что делает эта функция. И она требует целых три аргумента: =INDEX(array; number_row; [column_number]).

Excel просто вводит массив данных и возвращает значение, которое находится на пересечении определенной строки и столбца. Как бы просто это ни звучало. Поэтому формула =INDEX($A$2:$C$6;4;2) возвращает значение, найденное в ячейке B5.

В качестве примера, чтобы вернуть номер табеля сотрудника, мы используем =INDEX($A$2:$A$6;? ;1). Мы можем решить эту проблему, заменив знак вопроса на формулу ПОИСКПОЗ.

Функция ПОИСКПОЗ()

Синтаксис этой функции следующий: =Search_value; browsable_array; [match_type]).

Эта команда сообщает Excel: «Найдите значение_поиска в наборе данных и верните номер строки в массиве, в которой встречается это значение». Чтобы узнать, в какой строке в ячейке A10 содержится фамилия сотрудника, введите формулу =Найти_значение(A10; $B$2:$B$6; 0). Если ячейка A10 содержит имя «Colin Farrell», функция ПОИСКПОЗ возвращает пятую строку в массиве B2:B6.

Вот, в принципе, и все. Функция ПОИСК указывает функции ИНДЕКС, в какой строке искать заданное значение. Замените знак вопроса в формуле INDEX на формулу SEARCH, и вы получите эквивалент БПФ с данными для поиска слева от ключевого столбца. Вы можете сделать это следующим образом:

Сначала я не привык к этому, соблазном было вставить еще один столбец и работать так, как это всегда получалось. Но со временем использование функции INDEX вошло в привычку. Оказалось, что это быстрее и требует меньше манипуляций. Так что в следующий раз, когда вам понадобится поместить в RVP отрицательный номер столбца, используйте комбинацию двух странных функций INDEX и SEARCH для решения вашей проблемы.

Если формула не может найти значение, к которому она обращается, ошибка #N/D чаще всего вызывается функциями ПРОСМОТР, VLI ПРОСМОТР, G ПРОСМОТР, ПРОСМОТР или ПОИСКПОЗ. Например, искомое значение отсутствует в исходных данных.

В этом случае в таблице подстановок нет элемента Banana, поэтому функция VRP возвращает ошибку №N/D.

Решение: убедитесь, что искомые значения находятся в исходных данных, или используйте обработчик ошибок в формуле, например, IFERROR. Например, =ESLERROR(FORMULA();0), что говорит:

=Если (при вычислении формулы произошла ошибка, то выведите 0, иначе выведите результат формулы)

Вы можете указать «», чтобы ничего не отображать, или подставить свой собственный текст: =ELIOSURE(FORMULA(), «Error message»)

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

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

Искомое значение и исходные данные относятся к разным типам. Например, вы пытаетесь использовать ссылку на функцию БПФ в виде числа, но исходные данные хранятся как текст.

Решение:убедитесь, что типы данных одинаковы. Чтобы проверить формат ячеек, вы можете выбрать ячейку или диапазон ячеек, щелкнуть правой кнопкой мыши и выбрать формат ячеек >число (или нажмите CTRL+1) и при необходимости изменить числовом формате.

Если вам нужно изменить форматирование всего столбца, сначала примените нужный формат, а затем выберите Данные > Текст столбца > Готово.

Начальные и конечные пробелы можно удалить с помощью функции ПРОБЕЛ. В следующем примере функция VPR использует подфункцию SPACE, чтобы удалить ведущие пробелы из имен в ячейках A2:A7 и вернуть имя отдела.

Этот пример возвращает не только ошибку #N/D для «Банана», но и неверную цену для «Вишни». Это результат аргумента TRUE, который указывает функции БПФ искать не точное, а приблизительное совпадение. Для «Банана» нет точного соответствия, а «Вишня» идет перед «Персиком». В данном случае использование БПФ с аргументом FALSE отобразит правильную цену для товара «Вишня», но товар «Банан» все равно отобразит ошибку # N/A, потому что его нет в списке замены.

Если вы используете функцию FIND, попробуйте изменить значение аргумента match_type, чтобы указать порядок таблиц. Чтобы найти точное совпадение, установите аргумент match_type в 0 (ноль).

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

В этом примере ячейка E2 содержит ссылку на несовпадающие диапазоны:

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

Чтобы исправить ошибку, проверьте синтаксис используемой функции и введите необходимые аргументы, которые возвращают ошибку. Для проверки функции вам, вероятно, придется использовать редактор Visual Basic. Вы можете открыть этот редактор из вкладки «Разработчик» или нажав ALT+F11.

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

Чтобы исправить эту ошибку, убедитесь, что аргументы функции верны и их расположение правильно.

C TRL+ALT+F9 пересчитывает рабочий лист при нажатии этой комбинации клавиш.

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

Excel автоматически запускает мастера.

Нажмите на любой аргумент, и Excel покажет вам подробности.

Значение #H/D может быть полезным. Часто значения #H/D используются в диаграммах, на которых также представлены данные, как в примере ниже, поскольку эти значения не появляются на самой диаграмме. Ниже приведены примеры значений 0 и #H/D на диаграмме.

Значения 0 изображаются в виде прямой линии вдоль нижнего края диаграммы, а затем линия резко поднимается, чтобы отобразить общую сумму. В следующем примере вместо нулевых значений используются значения #H/D.

Формула ЕСЛИ и ЕДН для ошибок ВПР без функции ЕСЛИОШИБКА в Excel

В Microsoft Excel 2010 включена бескомпромиссная функция обработки ошибок ELIOS ERROR. В более старых версиях Excel для выявления ошибок часто использовалась функция END:

Функция END возвращает TRUE, если в ее аргументе есть только один тип ошибки: N/A! Или возвращает FALSE для всех остальных значений. В этой формуле функция IF помогает функции END. Если получена ошибка #N/D!, формула возвращает пустую строку — она указывается во втором аргументе функции IF. В противном случае возвращается результат вычисления функции FIR — указанной в третьем аргументе функции IF.

Основной недостаток этой формулы — необходимость дублирования функции БПФ:

• Первый раз в функции END;
• Второй раз в третьем аргументе функции IF.

Поэтому Excel должен дважды выполнить функцию БПФ для одной и той же ячейки. Большое количество подобных формул потребует много времени и системных ресурсов для пересчета. Работать с такими файлами будет очень неудобно. Если включен автоматический пересчет формул, его необходимо отключить: «ФОРМУЛЫ»-«Вычисления»-«Параметры вычислений»-«Вручную».

Как выполнить VLOOKUP и вернуть ноль вместо # N / A в Excel?

В Excel отображается # N / A, если не удается найти относительно правильный результат с помощью формулы ВПР. Но иногда вы хотите вернуть ноль вместо # N / A при использовании функции VLOOKUP, что может сделать таблицу намного лучше. В этом руководстве говорится о возврате нуля вместо # N / A при использовании VLOOKUP.
При использовании ВПР возвращает ноль вместо # Н / Д
Вернуть ноль или другой конкретный текст вместо # Н / Д с помощью расширенной функции ВПР.
Преобразование всех значений ошибки # Н / Д в ноль или другой текст

При использовании ВПР возвращает ноль вместо # Н / Д

Если вы хотите вернуть ноль вместо # H / D, когда функция VRP не может найти допустимый относительный результат, просто измените обычную формулу на другую формулу в Excel.

Выберите ячейку, в которой вы хотите использовать функцию ВПР, и введите эту формулу = ЕСЛИОШИБКА (ВПР (A13; $ A $ 2: $ C $ 10,3,0); 0) перетащите маркер автозаполнения в нужный диапазон. Смотрите скриншот:

Советы:
(1) В приведенной выше формуле A13 — это значение поиска, A2: C10 — это диапазон массива таблицы, а 3 — номер столбца индекса. Последний 0 — это значение, которое вы хотите показать, когда ВПР не может найти относительное значение.
(2) Этот метод заменяет все виды ошибок числом 0, включая # DIV / 0, #REF !, # N / A и т. Д.

Мастер условий ошибки

Вернуть ноль или другой конкретный текст вместо # Н / Д с помощью расширенной функции ВПР.

Работает с группой утилит SuperView «Us Kutools for Excels», вы можете искать значения справа налево (или слева направо), искать значения на нескольких листах, искать значения снизу вверх (или сверху вниз), искать значение и сумму, а также искать между двумя значениями, все они поддерживают замену ошибочного значения # N/A на другой текст. В качестве примера, я бы взял SURVEY справа налево.

После установки Kutools for Excel выполните следующие действия: (Скачайте Kutools for Excel бесплатно прямо сейчас!)

1. Нажмите Кутулс > Супер ПОСМОТРЕТЬ > ПОСМОТРЕТЬ справа налево.

2. В диалоговом окне LOOK right to left выполните следующие действия:

1) Введите диапазон значений поиска и диапазон значений вывода в поле поиска, установите флажок Заменить значение ошибки #N/A, а затем введите ноль или любой текст, который вы хотите отобразить в текстовом поле.

2) Затем выберите диапазон данных, который включает или исключает заголовки, укажите ключевой столбец (столбец подстановки) и столбец возврата.

3. Нажмите OK. Значения были возвращены на основе значения поиска, и ошибки # N / A также заменены нулем или новым текстом.

Преобразование всех значений ошибки # Н / Д в ноль или другой текст

Если вы хотите изменить все значения ошибок # N / A на ноль, а не только в формуле VLOOKUP, вы можете применить Мастер условий ошибок Kutools for ExcelAuthor.

После установки Kutools for Excel выполните следующие действия: (Теперь загрузите Kutools for Excel бесплатно!)

1. Выберите диапазон, в котором вы хотите заменить ошибки # N / A на 0, и нажмите Кутулс > Еще > Мастер условий ошибки. Смотрите скриншот:

2. Затем в появившемся диалоговом окне выберите Только значение ошибки # Н / Д из Типы ошибок раскрывающийся список и проверьте Сообщение (текст), затем введите 0 в следующее текстовое поле. Смотрите скриншот:

3. Нажмите Ok. После этого все ошибки # N / A будут заменены на 0.

Примечание: Это значение полезности будут иметь все ошибки #H/D, равные 0, а не только #H/D в формуле IDP.

Округлять

XLOOKUP — это долгожданный преемник VLOOKUP и HLOOKUP.

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

Поскольку в Excel скоро появятся динамические таблицы, он также сможет возвращать диапазон значений. Это, безусловно, заслуживает дальнейшего изучения.

Дни VLOOKUP сочтены. Поиск станет намного проще с появлением XLOOKUP.