Функция ВПР в Excel

Posted on: 2020年10月22日 2020年11月12日
Tags: lookup-formulas

Всем привет! Представьте, что у вас есть тысячи строк данных о сотрудниках в Excel, и ваш начальник в случайном порядке спрашивает у вас зарплату и должность Рамеша. Или, что еще хуже, он дает вам список из сотен сотрудников и просит прислать данные об этих сотрудниках. Что бы вы сделали? Вы не можете найти его вручную. Это просто неосуществимо и совсем не умно.

Но ты умный, поэтому ты здесь. Позвольте познакомить вас с функцией ВПР в Excel.

Зачем нам ВПР? Что такое ВПР? VLOOKUP означает вертикальный поиск.

ВПР просто ищет строку с заданным значением в первом столбце таблицы и возвращает запрашиваемое значение столбца в этой строке.

Синтаксис ВПР:

=VLOOKUP(lookup_value, table_array, col_index_number, [range_lookup])

Lookup_value: значение, по которому вы хотите выполнить поиск в первом столбце массива таблиц.

Table_array: Таблица, в которой вы хотите искать / искать col_index_number: Номер столбца в Table Array, из которого вы хотите получить результаты.

[range_lookup]: FALSE, если вы хотите найти точное значение, TRUE, если вы хотите приблизительное совпадение.

Хорошо, хватит теории. Давайте перейдем к примеру Excel VLOOKUP.

VLOOKUP Пример 1. В таблице Excel у вас есть эти данные от сотрудников. В первом столбце (Столбец A) у вас есть имена сотрудников. Далее у вас есть их обозначения и так далее, как показано на изображении ниже.

Чтобы было легче, я взял небольшой столик.

Теперь ваш начальник отправил вам этот список и запросил подробности в пустых столбцах.

Вы должны сообщить Рамешу и Дивье зарплаты и должности. Как бы Вы это сделали?

Для этого вы можете искать вручную или… Или вы можете использовать функцию Excel VLOOKUP.

Просто напишите эту формулу ВПР в ячейке H2 и скопируйте ее в ячейку ниже:

=VLOOKUP(G3,$A$3:$D$11,4,FALSE)

Вы получите результаты, как показано ниже.

Пояснение:

Здесь мы сказали excel искать Рамеша (G3) в первом столбце (A) таблицы $ A $ 3: $ D $ 11.

Если данное значение найдено, вернуть значение в 4-м (4)

столбец в этой таблице.

Давая ему FALSE в range_lookup, вы говорите VLOOKUP найти точное совпадение (Нет, если и но).

Здесь Рамеш находится в первом столбце A, а его зарплата возвращается как 105347.

Точно так же функция VLOOKUP ищет Дивию в столбце и возвращает ошибку # Н / Д, так как записи Дивьи нет в вашей таблице.

Вы видите здесь другое использование ВПР? Вы можете использовать функцию VLOOKUP, чтобы узнать, существует ли значение в списке или нет. Вы также можете сопоставить два списка и узнать, какие значения в них общие.

Совет от профессионалов: всегда блокируйте ссылку на Table_array при использовании функции ВПР в Excel. В противном случае ваш table_array изменится, когда вы скопируете формулу в другие ячейки.

По умолчанию, когда вы выполняете ВПР с другого листа или книги, но на том же листе, вам нужно делать это вручную.

Можете ли вы написать формулу ВПР, чтобы получить обозначение Рамеша? Раздел комментариев — все ваше.

В приведенном выше примере мы видели пример ВПР, в котором нам нужно точное совпадение. Но что, если мы хотим узнать приблизительное совпадение?

Когда вы используете приблизительное совпадение в VLOOKUP как Range_lookup?

Давайте разберемся с этим на другом примере ВПР.

Пример 2: Использование приблизительного совпадения в ВПР. Ранее вы правильно ответили на вопрос начальника, используя ВПР. Теперь ваш «дорогой» начальник хочет знать ближайшую к 150000 зарплату (только меньше).

Чтобы ответить на этот вопрос, мы снова будем использовать ВПР, но на этот раз с приблизительным соответствием.

Важное примечание: чтобы найти примерное совпадение, вам необходимо отсортировать данные. Здесь я отсортировал данные в порядке возрастания зарплаты.

В ячейке H2 напишите эту формулу и перетащите ее в ячейку ниже:

=VLOOKUP(G9,$D$3:$D$11,1,1)

Примечание. 1 интерпретируется как ИСТИНА, а 0 как ЛОЖЬ в Excel.

В конечном итоге вы получите следующий результат:

Объяснение.

Наше значение поиска составляет 150000 (G9), и мы хотим получить ближайшую к нему зарплату.

Наш диапазон составляет $ D $ 3: $ D $ 11, поскольку мы хотим вернуть значение из того же столбца. Следовательно, col_index_num также равен 1. И поскольку мы хотим приблизительного совпадения, мы предоставили range_lookup как 1 (ИСТИНА). Для приблизительного совпадения ваши данные необходимо отсортировать. В противном случае Excel VLOOKUP будет давать неверный результат.

Важные примечания:

ВПР всегда работает слева направо. Вы не можете получить значение справа от первого столбца. Нумерация столбцов начинается с выбранного диапазона. Например, если вы предоставите массив таблиц D4: F10. Тогда счет столбца будет начинаться с D, а не с фактического начала таблицы. *

Всегда фиксируйте ссылку на таблицу с помощью знака $, чтобы избежать ошибки при копировании формулы поиска V в Microsoft Excel 2016. Вы можете использовать подстановочный знак () в значении поиска, если вы помните только некоторую часть значения поиска. *

* Установите для Range_Lookup значение FALSE (0), чтобы получить точное соответствие вашему значению.

Установите Range_Lookup в TRUE (1), чтобы получить ближайшее к меньшему, чем заданное значение. *

* Всегда сортируйте данные в порядке возрастания для приблизительного совпадения.

Теперь вы можете ответить на вопрос начальника за секунды. Вы можете легко сравнить два списка. Вы можете выполнить массовый поиск с помощью ВПР в Excel 2016, 2013, 2010 и более старых версиях Excel за секунды.

Так было ли это полезно? Если нет, дайте мне знать ваш точный запрос в разделе комментариев.