Учебное пособие по Excel VLOOKUP для начинающих: пошаговые примеры

Что такое ВПР?

Vlookup (V означает «Вертикальный») — это встроенная функция Excel, которая позволяет устанавливать связи между различными столбцами Excel. Другими словами, он позволяет вам найти (поиск) значение в одном столбце данных и возвращает соответствующее или соответствующее значение из другого столбца.

Использование ВПР

Если вам нужно найти некоторую информацию в большой электронной таблице данных или вам нужно найти одну и ту же информацию по всей электронной таблице, используйте функцию Vlookup.

Давайте возьмем пример Vlookup:

Таблица зарплат компании которым управляет финансовая команда Компании – В Таблица зарплат компаниивы начинаете с части информации, которая уже известна (или легко извлекается). Информация, которая служит индексом.

Итак, пример:

Вы начинаете с уже имеющейся информации:

(В данном случае имя сотрудника)

Использование ВПР

Чтобы найти информацию, которую вы не знаете:

(В данном случае мы хотим найти зарплату сотрудника)

Использование ВПР

Таблица Excel для приведенного выше примера:

Использование ВПР

Загрузите вышеуказанный файл Excel

В приведенной выше таблице, чтобы узнать зарплату сотрудника, которую мы не знаем:

Мы введем код сотрудника, который уже доступен.

Использование ВПР

Более того, Применяя ВПР, Будет отображено значение (зарплата сотрудника) соответствующего кода сотрудника.

Использование ВПР

Как использовать функцию ВПР в Excel

Фоллоwing представляет собой пошаговое руководство по применению функции ВПР в Excel:

Шаг 1) Перейдите к ячейке, которую вы хотите просмотреть.

Нам нужно перейти к ячейке, в которой вы хотите просмотреть зарплату конкретного сотрудника. - (в данном случае щелкните ячейку с индексом «H3»).

Используйте функцию ВПР в Excel

Шаг 2) Введите функцию ВПР =ВПР().

Введите функцию ВПР в ячейке выше: начните с знак равенства, который означает, что функция введена, 'Ключевое слово ВПР используется после знака равенства, обозначающего функцию ВПР. =ВПР ()

Используйте функцию ВПР в Excel

Круглые скобки будут содержать набор аргументов (аргументы — это фрагмент данных, который необходим функции для выполнения).

ВПР использует четыре аргумента или фрагмента данных:

Шаг 3) Первый аргумент — введите искомое значение, которое вы хотите найти или выполнить поиск.

Первым аргументом будет ссылка на ячейку (в качестве заполнителя) для значения, которое необходимо найти, или искомого значения. Значение поиска относится к данным, которые уже доступны, или к данным, которые вам известны. (В этом случае код сотрудника рассматривается как искомое значение, поэтому первым аргументом будет H2, т. е. значение, которое необходимо найти или найти, будет присутствовать в ссылке на ячейку «H2»).

Используйте функцию ВПР в Excel

Шаг 4) Второй аргумент – массив таблицы.

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

ПРИМЕЧАНИЕ. Значения поиска или известные вам данные должны находиться в левом столбце вашей таблицы поиска, то есть в диапазоне ячеек.

Используйте функцию ВПР в Excel

Шаг 5) Третий аргумент. Синтаксис VLOOKUP — это columns_index_no.

Это относится к ссылке на столбец. Другими словами, он уведомляет VLOOKUP, где вы ожидаете найти данные, которые хотите просмотреть. (Ссылка на столбец — это индекс столбца в таблице поиска столбца, в котором должно быть найдено соответствующее значение.) В этом случае ссылка на столбец будет равна 4, поскольку столбец «Зарплата сотрудника» имеет индекс 4 в соответствии с таблицей поиска.

Используйте функцию ВПР в Excel

Шаг 6) Четвертый аргумент – точное или приблизительное совпадение.

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

  1. FALSE: Относится к точному совпадению.
  2. TRUE: Относится к приблизительному совпадению.

Используйте функцию ВПР в Excel

Шаг 7) Нажмите Enter!

Нажмите «Ввод», чтобы уведомить ячейку о завершении функции. Однако вы получаете сообщение об ошибке, как показано ниже, поскольку в ячейку H2т.е. не было введено значение. В поле «Код сотрудника» не введен код сотрудника, который позволит найти значение.

Используйте функцию ВПР в Excel

Однако, когда вы вводите любой код сотрудника в H2, он вернет соответствующее значение, то есть зарплату сотрудника.

Используйте функцию ВПР в Excel

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

В приведенном выше примере объясняется точные совпадения в VLOOKUP, т. е. ключевое слово FALSE в качестве последнего параметра.

ВПР для приблизительных совпадений (ключевое слово TRUE в качестве последнего параметра)

Рассмотрим сценарий, в котором таблица рассчитывает скидки для клиентов, которые не хотят покупать ровно десятки или сотни товаров.

Как показано ниже, некоторые компании установили скидки на количество товаров от 1 до 10,000 XNUMX:

ВПР для приблизительных совпадений

Загрузите вышеуказанный файл Excel

Теперь нет уверенности в том, что покупатель покупает именно сотни или тысячи товаров. В этом случае скидка будет применяться в соответствии с приблизительными совпадениями VLOOKUP. Другими словами, мы не хотим ограничивать их поиск совпадений только значениями, присутствующими в столбце: 1, 10, 100, 1000, 10000. Вот шаги:

Шаг 1) Нажмите на ячейку в котором необходимо применить функцию ВПР, т.е. ссылку на ячейку «I2»..

ВПР для приблизительных совпадений

Шаг 2) Введите в ячейку «=VLOOKUP()». В скобках введите набор аргументов для вышеуказанного экземпляра.

ВПР для приблизительных совпадений

Шаг 3) Введите аргументы:

Аргумент 1: Введите ссылку на ячейку, в которой присутствующее значение будет искать соответствующее значение в справочной таблице.

ВПР для приблизительных совпадений

Шаг 4) Аргумент 2: Выберите таблицу поиска или массив таблиц, в котором вы хотите, чтобы VLOOKUP выполнял поиск соответствующего значения. (В этом случае выберите столбцы «Количество» и «Скидка»).

ВПР для приблизительных совпадений

Шаг 5) Аргумент 3: Третьим аргументом будет индекс столбца в справочной таблице, в которой вы хотите найти соответствующее значение.

ВПР для приблизительных совпадений

Шаг 5) Аргумент4: Последним аргументом будет условие Приблизительные совпадения или точные совпадения. В этом случае мы особенно ищем приблизительные совпадения. (ИСТИНА ключевое слово).

ВПР для приблизительных совпадений

Шаг 6) Нажмите Ввод.' Формула Vlookup будет применена к указанной ссылке на ячейку, и когда вы вводите любое число в поле количества, она покажет вам скидку, налагаемую на основе Приблизительные совпадения в VLOOKUP.

ВПР для приблизительных совпадений

ПРИМЕЧАНИЕ: Если вы хотите использовать TRUE в качестве последнего параметра, вы можете оставить его пустым, и по умолчанию для Приблизительных совпадений будет выбрано TRUE.

Функция Vlookup применяется между двумя разными листами, помещенными в одну книгу.

Давайте рассмотрим пример, аналогичный приведенному выше сценарию. Нам предоставлена ​​одна рабочая тетрадь, содержащая два разных листа. Тот, где код сотрудника вместе с именем и должностью сотрудника указан, другой лист содержит код сотрудника и соответствующую зарплату сотрудника (как показано ниже).

ЛИСТ 1:

Функция Vlookup применяется между двумя разными листами

ЛИСТ 2:

Функция Vlookup применяется между двумя разными листами

Загрузите вышеуказанный файл Excel

Теперь цель состоит в том, чтобы просмотреть все данные на одной странице, т. е. на листе 1, как показано ниже:

Функция Vlookup применяется между двумя разными листами

VLOOKUP может помочь нам объединить все данные, чтобы мы могли видеть код, имя и зарплату сотрудника в одном месте или на листе.

Мы начнем работу над Листом 2, поскольку этот лист предоставляет нам два аргумента функции ВПР, а именно: Заработная плата сотрудника указана на Листе 2, поиск по которому осуществляется с помощью ВПР, и ссылка индекса столбца равна 2 (согласно справочной таблице).

Функция Vlookup применяется между двумя разными листами

Кроме того, мы знаем, что хотим найти зарплату сотрудника, соответствующую Кодексу работника.

Функция Vlookup применяется между двумя разными листами

Более того, эти данные начинаются с A2 и заканчиваются в B25. Так что это будет наш таблица поиска или аргумент массива таблицы.

Шаг 1) Перейдите к листу 1 и введите соответствующие заголовки, как показано.

Функция Vlookup применяется между двумя разными листами

Шаг 2) Нажмите на ячейку, к которой вы хотите применить функцию ВПР. В данном случае это будет ячейка рядом с зарплатой сотрудника со ссылкой на ячейку «F3».

Функция Vlookup применяется между двумя разными листами

Введите функцию ВПР: =ВПР().

Шаг 3) Аргумент 1: Введите ссылку на ячейку, содержащую значение для поиска в справочной таблице. В этом случае «F2» — это ссылочный индекс, который будет содержать код сотрудника, соответствующий зарплате соответствующего сотрудника в справочной таблице.

Функция Vlookup применяется между двумя разными листами

Шаг 4) Аргумент 2: Во втором аргументе мы вводим справочную таблицу или массив таблиц. Однако в этом случае у нас есть таблица поиска, расположенная на другом листе той же книги. Следовательно, для построения связи нам нужно ввести адрес таблицы поиска как Лист2!A2:B25 – (A2:B25 относится к таблице поиска на листе 2)

Функция Vlookup применяется между двумя разными листами

Шаг 5) Аргумент 3: Третий аргумент относится к индексу столбца столбца, присутствующего в таблице поиска, где должны присутствовать значения.

Функция Vlookup применяется между двумя разными листами

Функция Vlookup применяется между двумя разными листами

Шаг 6) Аргумент 4: Последний аргумент относится к Точные совпадения (ЛОЖЬ) or Приблизительные совпадения (ИСТИНА). В этом случае мы хотим получить точные совпадения с зарплатой сотрудника.

Функция Vlookup применяется между двумя разными листами

Шаг 7) Нажмите Enter, и когда вы введете код сотрудника в ячейку, вы получите соответствующую зарплату сотрудника для этого кода сотрудника.

Функция Vlookup применяется между двумя разными листами

Заключение

Приведенные выше 3 сценария объясняют работу функций ВПР. Вы можете поиграть, используя больше экземпляров. ВПР — важная функция, присутствующая в MS Эксель что позволяет более эффективно управлять данными.

Также ознакомьтесь с нашим руководством по Excel в формате PDF: — Кликните сюда