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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

Ниже приводится пошаговое руководство по применению функции ВПР в Excel:

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

В приведенном выше примере объясняется точные совпадения в 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:

ЛИСТ 2:

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

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

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

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

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

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

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

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

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

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

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

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

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

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

Заключение

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

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