Check-moscow.ru

Финансы и учет
0 просмотров
Рейтинг статьи
1 звезда2 звезды3 звезды4 звезды5 звезд
Загрузка...

Как выполнить 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?

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

Описание формулы ПОИСКПОЗ

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

формула индекс и поискпоз

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

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

Искомого значения не существует. Ячейка E2 содержит формулу =ВПР(D2;$D$6:$E$8;2;ЛОЖЬ). Значение "Банан" не найдено, поэтому формула возвращает ошибку #Н/Д.

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

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

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

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

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

Ссылка на форум сообщества Excel

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

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

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

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

Диалоговое окно "Формат ячеек" с открытой вкладкой "Число" и выделенным элементом "Текстовый"

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

Читайте так же:
Как вставить таблицу Excel в слово и сделать содержимое таблицы подходящим для страницы?

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

Использование функции ВЛП с функцией СРЕЗ в формуле массива для удаления пробелов с ведущими и ведущими данными. Ячейка E3 содержит формулу <=ВLOOKUP(D2;СРФ(A2:B7);2;ЛОЖЬ) data-lazy-src=

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

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

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

Читайте так же:
Как добавить процент столбца общего / промежуточного итога в сводную таблицу Excel?

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

Пример формулы массива со ссылками на неоховеченные диапазоны, вызывающими #N/A. Ячейка E2 содержит формулу <=СУММ(ЕСЛИ(A2:A11=D2;B2:B5)) data-lazy-src=

В данном случае #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:

Читайте так же:
Как выделить строки на основе раскрывающегося списка в Excel?

ЕСЛИ и ЕДН для ошибок ВПР.

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

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

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

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

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

документ-vlookup-возврат-ноль-1

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

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

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

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

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

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

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

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

Читайте так же:
Как добавить пробел между символами или каждой цифрой в Excel?

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

1. Нажмите Кутулс > Супер ПОСМОТРЕТЬ > ПОСМОТРЕТЬ справа налево.
документ-vlookup-возврат-0-2

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

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

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

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

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

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

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

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

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

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

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

Округлять

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

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

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

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

голоса
Рейтинг статьи
Ссылка на основную публикацию
Adblock
detector