Учебное пособие по Excel VLOOKUP для начинающих
Шарлотта Миллер
⚡ Умное резюме
В этом руководстве по функции VLOOKUP в Excel объясняется, как функция вертикального поиска ищет значение в первом столбце таблицы и возвращает соответствующее значение из другого столбца. В руководстве рассматриваются синтаксис, точное и приблизительное совпадение, поиск между листами, распространенные ошибки и современная альтернатива функции XLOOKUP.
Что такое ВПР?
Функция VLOOKUP (буква V означает «вертикальная») — это встроенная функция Excel, которая устанавливает связь между столбцами в электронной таблице. Она позволяет найти значение в одном столбце и получить соответствующее значение из другого столбца в той же строке.
Синтаксис и аргументы функции VLOOKUP
Перед использованием функции VLOOKUP полезно понять структуру формулы. Функция принимает четыре аргумента и следует единому шаблону во всех версиях Excel.
- искомое_значение — значение, которое вы хотите найти (ссылка на ячейку или литерал).
- таблица_массив — диапазон ячеек, содержащий столбец поиска и столбец возврата.
- номер_столбца — номер столбца в массиве table_array, из которого нужно вернуть значение (1 — самый левый столбец).
- диапазон_поиска — FALSE означает точное совпадение, TRUE (или опускается) — приблизительное совпадение по отсортированным данным.
Важно: Искомое значение должно находиться в самом левом столбце таблицы table_array, а функция VLOOKUP выполняет поиск только слева направо.
Использование ВПР
Когда вам нужно найти конкретную информацию в большой электронной таблице или получить одно и то же значение несколько раз, функция VLOOKUP значительно экономит время по сравнению с ручной фильтрацией.
Рассмотрим Таблица зарплат компании Поддерживается финансовым отделом. Вы начинаете с известной информации — индекса — и используете функцию VLOOKUP для получения неизвестного значения.
Например, вам уже известно имя сотрудника:
А вы хотите узнать зарплату сотрудника:
Электронная таблица Excel для вышеуказанного случая:
Загрузите вышеуказанный файл Excel
Чтобы узнать неизвестную зарплату сотрудника, мы вводим данные о сотруднике. Code это уже доступно.
С помощью функции VLOOKUP можно получить значение заработной платы, соответствующее данному сотруднику. Code появляется автоматически.
Как использовать функцию ВПР в Excel
Следуйте этой пошаговой инструкции, чтобы применить функцию VLOOKUP в Excel:
Шаг 1) Перейдите к целевой ячейке
Щелкните ячейку, в которой вы хотите отобразить зарплату выбранного сотрудника — в этом примере это ячейка H3.
Шаг 2) Введите функцию VLOOKUP =VLOOKUP()
Введите функцию в ячейку. Начните со знака равенства (который указывает Excel, что далее следует формула), а затем используйте ключевое слово VLOOKUP: =ВПР().
В скобках указан набор аргументов (данные, необходимые функции).
Функция VLOOKUP принимает четыре аргумента:
Шаг 3) Первый аргумент — искомое значение
Первый аргумент — это ссылка на ячейку, в которой находится искомое значение. В данном случае это значение поля "Сотрудник". Code Это искомое значение, поэтому первым аргументом является H2 — ячейка, содержимое которой Excel должен сопоставить.
Шаг 4) Второй аргумент — массив таблиц
Это относится к блоку значений, подлежащему поиску, известному в Excel как массив таблиц или справочной таблицей. В нашем примере справочная таблица работает следующим образом: от B2 до E25.
ПРИМЕЧАНИЕ: Столбец поиска должен быть самым левым столбцом массива вашей таблицы.
Шаг 5) Третий аргумент — col_index_num
Это указывает функции VLOOKUP, в каком столбце внутри массива таблицы находится возвращаемое значение. Зарплата сотрудника находится в четвертом столбце, поэтому индекс столбца равен 4.
Шаг 6) Четвертый аргумент — точное или приблизительное совпадение
Последний аргумент — это флаг поиска в диапазоне. Он определяет, возвращает ли функция VLOOKUP точное или приблизительное совпадение. В данном случае нам нужно точное совпадение (FALSE).
- НЕПРАВДА — точное совпадение.
- ИСТИНА — приблизительное совпадение.
Шаг 7) Нажмите Enter.
Нажмите Enter, чтобы завершить формулу. Сначала вы увидите ошибку, потому что не указан сотрудник. Code В H2 еще не внесены данные.
После ввода данных о действующем сотруднике Code В ячейке H2 отображается соответствующая заработная плата сотрудника.
Вкратце, формула сообщает Excel, что известные значения находятся в самом левом столбце данных (сотрудник). CodeФункция VLOOKUP затем сканирует таблицу и возвращает значение из четвертого столбца соответствующей строки — зарплату сотрудника.
В этом примере рассматривались точные совпадения (ключевое слово FALSE). В следующем разделе объясняются приблизительные совпадения.
ВПР для приблизительных совпадений (ключевое слово TRUE в качестве последнего параметра)
Рассмотрим ситуацию, когда таблица рассчитывает скидки для покупателей, которые приобретают не ровно десятки или сотни товаров.
Как показано ниже, компания предоставляет скидки на заказы от 1 до 10 000 единиц:
Загрузите вышеуказанный файл Excel
Покупатель редко приобретает ровно 100 или 1,000 единиц. Режим приблизительного соответствия позволяет функции VLOOKUP найти ближайшее меньшее значение, вместо того чтобы настаивать на точном значении. Шаги:
Шаг 1) Щелкните ячейку, в которую будет встроена функция VLOOKUP — ссылка на ячейку I2.
Шаг 2) Введите в ячейку формулу =VLOOKUP() и укажите аргументы внутри скобок.
Шаг 3) Аргумент 1: Введите ссылку на ячейку, значение которой должно быть сопоставлено с таблицей соответствия.
Шаг 4) Аргумент 2: Выберите таблицу поиска — в данном случае столбцы «Количество» и «Скидка».
Шаг 5) Аргумент 3: Введите индекс столбца в справочной таблице, из которого нужно получить соответствующее значение.
Шаг 6) Аргумент 4: Установите последний аргумент на ИСТИНА для приблизительного совпадения.
Шаг 7) Нажмите Enter. Формула теперь применяется к ячейке. При вводе любого количества Excel вернет диапазон скидки, основанный на приблизительном совпадении.
ПРИМЕЧАНИЕ: Если оставить четвертый аргумент пустым, Excel по умолчанию выберет значение TRUE (приблизительное совпадение). Для приблизительного совпадения столбец поиска должен быть отсортирован в порядке возрастания.
Функция Vlookup применяется между двумя разными листами, помещенными в одну книгу.
Теперь рассмотрим рабочую книгу с двумя листами. На листе 1 указан сотрудник. CodeИмя и должность; на листе 2 указан сотрудник. Code и заработная плата сотрудника.
ЛИСТ 1:
ЛИСТ 2:
Загрузите вышеуказанный файл Excel
Цель состоит в том, чтобы объединить все данные на Листе 1, как показано ниже:
Функция VLOOKUP может агрегировать данные, например, данные о сотрудниках. CodeИмя и зарплата указаны вместе на одном листе.
Мы начинаем с Листа 2, поскольку он предоставляет два аргумента — здесь находится столбец «Заработная плата сотрудника», и Индекс столбца равен 2..
Мы хотим подобрать зарплату, которая соответствовала бы уровню каждого сотрудника. Code.
Данные расположены в диапазоне от A2 до B25 — это наш табличный массив.
Шаг 1) Перейдите на Лист 1 и введите указанные заголовки.
Шаг 2) Щелкните ячейку рядом с «Заработная плата сотрудника» — ячейку F3 — где будет размещена формула VLOOKUP.
Введите функцию VLOOKUP: =VLOOKUP().
Шаг 3) Аргумент 1: Введите F2 — ячейку, содержащую имя сотрудника. Code для сопоставления в таблице поиска.
Шаг 4) Аргумент 2: Таблица поиска находится на другом листе, поэтому ссылайтесь на нее, используя имя листа: Лист 2!A2:B25.
Шаг 5) Аргумент 3: Введите индекс столбца в справочной таблице, который содержит возвращаемое значение.
Шаг 6) Аргумент 4: Используйте FALSE для точного соответствия, поскольку нам нужна точная зарплата для каждого сотрудника. Code.
Шаг 7) Нажмите Enter. При вводе данных сотрудника CodeВ ячейке отображается соответствующая заработная плата, взятая из листа 2.
Распространенные ошибки функции VLOOKUP и способы их исправления
Даже опытные пользователи сталкиваются с ошибками функции VLOOKUP. Наиболее распространенные из них и способы их быстрого устранения:
- # N / A — Функция VLOOKUP не может найти искомое значение. Проверьте наличие лишних пробелов, несоответствия типов данных (числа хранятся как текст) или убедитесь, что значение действительно существует в первом столбце таблицы table_array.
- #REF! — Значение col_index_num больше, чем количество столбцов в table_array. Уменьшите индекс столбца или расширьте диапазон.
- #СТОИМОСТЬ! — Значение col_index_num меньше 1 или аргумент недопустим. Проверьте синтаксис формулы.
- Получен неверный результат. — Четвертый аргумент имеет значение TRUE или опущен, но столбец поиска не отсортирован. Переключитесь на FALSE или отсортируйте столбец по возрастанию.
- Заблокированные ссылки — При копировании формулы вниз используйте абсолютные ссылки (например, $B$2:$E$25), чтобы массив таблицы не смещался.
VLOOKUP против XLOOKUP: что использовать?
Microsoft Функция XLOOKUP была введена в Microsoft В Office 365 и Excel 2021 она используется как современная замена функции VLOOKUP. Она устраняет ряд ограничений функции VLOOKUP и теперь является рекомендуемым выбором в поддерживаемых версиях.
| Характеристика | ВПР | XLOOKUP |
|---|---|---|
| Направление поиска | Только слева направо | В любом направлении (влево, вправо, вверх, вниз) |
| Тип совпадения по умолчанию | Приблизительно (истина) | точная |
| Обработка случаев, когда не найдено | Возвраты #N/A | Встроенный аргумент if_not_found |
| Указатель столбцов | Заданное число | Укажите диапазон столбцов возврата |
| Доступность | Все версии Excel | Microsoft 365, Excel 2021, Excel для веб-версии |
Когда следует использовать функцию VLOOKUP: Рабочая книга должна работать в Excel 2019 или более ранних версиях, либо вы используете устаревшие формулы. Когда следует использовать функцию XLOOKUP: Вы создаёте новые рабочие книги в современном Excel и хотите использовать поиск слева, более удобную обработку ошибок и точное совпадение по умолчанию. Подробнее о функциях поиска можно узнать в разделе... Учебники Excel серии.
Заключение
Три описанных выше сценария объясняют, как работает функция VLOOKUP для точных совпадений, приблизительных совпадений и межстраничных ссылок. Попрактикуйтесь на собственных наборах данных, чтобы развить навыки работы с функцией VLOOKUP. Функция VLOOKUP остается важной функцией в MS Эксель для эффективного управления данными, а функция XLOOKUP расширяет этот набор инструментов в современных версиях Excel.
