В работе часто встречаются задачи, которые без Excel заняли бы очень много времени. Одна из них — поиск информации в больших наборах данных и ее подстановка из одной таблицы в другую. Например, когда хочется узнать, сколько покупок сделал каждый клиент. Обычно для этого нужно найти его имя в таблице клиентов и затем использовать это значение для поиска соответствующей записи в таблице продаж.
Для подобных ситуаций существует функция ВПР (VLOOKUP, «вертикальный просмотр»). Как она работает, когда ее стоит использовать и с какими ошибками можно столкнуться — рассказываем в статье.
Когда использовать ВПР
- Чтобы найти информацию по заданному критерию. Владелец магазина хочет узнать стоимость товара из своего ассортимента. Он может использовать функцию ВПР, чтобы найти его цену в таблице.
- Чтобы соединить данные из нескольких разных таблиц или листов. Компании нужно соотнести данные о продажах с данными о товарах, чтобы определить, какие позиции продаются лучше всего. ВПР поможет сопоставить наименования товаров с данными о продажах.
- Если нужно обновить большой пласт данных в таблице. Отдел закупок в компании регулярно обновляет цены на товары различных поставщиков. Они используют функцию ВПР для обновления цен в таблице товаров на основе актуальных данных.
- Чтобы сгенерировать отчет на основе данных. Магазин электроники хочет сгенерировать отчет о продажах нескольких моделей смартфонов за последний квартал. Функция ВПР поможет извлечь из базы данные о продажах каждой конкретной модели.
- Когда нужно определить максимальное или минимальное значение. Отдел маркетинга хочет определить, какой товар приносит наибольшую прибыль. Они используют функцию ВПР, чтобы найти максимальную сумму продаж в своей таблице данных о продажах и выяснить, какой товар ей соответствует.
Из чего состоит функция ВПР
ВПР состоит из нескольких аргументов, которые определяют условия поиска и искомого результата. Синтаксис функции выглядит так:
Fx = ВПР(искомое_значение; таблица_диапазон; номер_столбца; [интервальный_просмотр])
Где:
Таблица (диапазон) — диапазон ячеек, в котором будет проведен поиск искомого значения. В этом параметре можно указать:
— диапазон ячеек в одном листе (например, A1:D10);
— диапазон ячеек в другом листе (например, если ячейки находятся в листе «Лист2», нужно указать так: Лист2!A1:D10);
— массивы ячеек;
— динамический диапазон (не имеет жестко заданных координат, создается с помощью функций СМЕЩ и СЧЕТ);
— именованный диапазон (как на картинке ниже).
![Именованный диапазон ВПР](https://blog.skillfactory.ru/wp-content/uploads/2024/04/vpr-tablitsa-diapazon.png)
Номер столбца — столбец в таблице (диапазоне), из которого будет возвращен результат. Столбцы нумеруются по порядку, начиная с первого столбца диапазона.
![Столбец диапазона для ВПР](https://blog.skillfactory.ru/wp-content/uploads/2024/04/vpr-nomer-stolbtca.png)
Искомое значение — значение, которое функция ищет в первом столбце диапазона. Это может быть число, текст или ссылка на ячейку с данными.
Возвращаемым результатом будет содержимое той ячейки, которая находится на пересечении строки с искомым значением и указанного в функции столбца.
![Искомое значение для ВПР](https://blog.skillfactory.ru/wp-content/uploads/2024/04/vpr-iskomoe-znachenie.png)
Например, на картинке значению 9 в столбце 7 соответствует значение 63, которое и будет результатом.
Интервальный просмотр (необязательный аргумент) — определяет, нужно ли искать точное совпадение. По умолчанию значение аргумента — ЛОЖЬ.
Если значение ЛОЖЬ или 0 (или этот аргумент опущен), функция ВПР будет искать только точные совпадения.
Если значение аргумента равно ИСТИНА или 1, то функция ВПР будет использовать приближенный поиск. Это работает с отсортированными по возрастанию списками.
Логика функции на примере:
Fx = ВПР(A1; B1:D10; 3; ЛОЖЬ)
Функция ВПР будет искать значение из ячейки A1 в диапазоне B1:D10 и если найдет совпадение, то вернет значение из третьего столбца этого диапазона. При этом алгоритм будет искать только точные совпадения, так как в последнем аргументе указано ЛОЖЬ.
Ограничения:
— Искомое значение всегда должно находиться в первом столбце диапазона.
— Получить можно только один результат на одно искомое значение. Это значит, что если в первом столбце находится несколько ячеек с искомым значением, то функция выдаст результат лишь для первой найденной ячейки в списке.
— Для поиска значений, которые не полностью совпадают, а начинаются с определенного текста, ВПР не подойдет. Здесь поможет функция совпадения СОВПАД() или фильтрация данных с помощью фильтров Excel.
Как пользоваться ВПР
Если первый столбец диапазона (столбец искомого значения) не отсортирован в порядке возрастания, то лучше это сделать до начала работы. Сортировка нужна, если планируется указать значение «ИСТИНА» в интервальном просмотре.
Для этого выделите столбец без заголовка, перейдите во вкладку «Данные», а затем нажмите «Сортировка от А до Я». При появлении предупреждения о данных рядом с выделенным диапазоном нажмите «автоматически расширить выделенный диапазон».
![Как пользоваться ВПР](https://blog.skillfactory.ru/wp-content/uploads/2024/04/kak-polzovatsya-vpr-1.gif)
Где находится функция ВПР
Вставить функцию можно либо через вкладку «Формулы» — «Вставить функцию», либо нажатием кнопки «fx». Ее также можно ввести вручную: «=ВПР» без кавычек.
![Где находится ВПР](https://blog.skillfactory.ru/wp-content/uploads/2024/04/gde-nahoditsya-vpr-1.png)
После нажатия кнопки функции и выбора «ВПР» откроется интерфейс для заполнения данных.
![Интерфейс ВПР](https://blog.skillfactory.ru/wp-content/uploads/2024/04/gde-nahoditsya-vpr-2.png)
В Google Таблицах такой возможности нет и доступен только ручной ввод.
Рассмотрим использование ВПР на примере: нужно посчитать сумму заказа для каждой позиции канцтоваров. Но в таблице «Канцтовары» отсутствует количество заказываемых товаров. Его нужно будет взять из листа «Заказ».
Порядок решения
- Выберите ячейку для расчета ВПР.
Это будет первая ячейка в столбце «Кол-во» (ячейка H4).
- Примените функцию или введите ее вручную.
- Определите искомое значение.
В столбце «Товары» выбираем первое наименование — блок для записи (ячейка C4).
![Товары](https://blog.skillfactory.ru/wp-content/uploads/2024/04/kak-polzovatsya-vpr-poryadok-resheniya-3.gif)
- Выделите диапазон ячеек для поиска.
Его возьмем из листа «Заказ», захватывая информацию о наименовании товара и его количестве.
Выбираем диапазон (ячейки C5:D23) и закрепляем его нажатием F4 (Windows) или Cmd + T (MacOS).
![Заказ](https://blog.skillfactory.ru/wp-content/uploads/2024/04/kak-polzovatsya-vpr-poryadok-resheniya-4.gif)
- Укажите целевой столбец.
Количество товаров находится во втором столбце диапазона. Указываем четвертый аргумент функции — 2.
- Введите значение интервального просмотра.
Для точного совпадения — ЛОЖЬ, для приближенного — ИСТИНА. Используем приближенный поиск для сортированного по возрастанию списка, указываем 1 (ИСТИНА).
- Примените формулу для всего столбца.
Получилась формула:
=ВПР(C4; заказ!$C$5:$D$23; 2; 1)
Примените это значение ко всем ячейкам столбца, растянув диапазон.
![Растягивание значений](https://blog.skillfactory.ru/wp-content/uploads/2024/04/kak-polzovatsya-vpr-poryadok-resheniya-7.gif)
Как осуществить поиск по нескольким критериям
Несмотря на то что функция ВПР может выполнять поиск только по одному искомому значению, есть способы вручную расширить ее возможности.
Способ 1 — с использованием нового столбца объединенных значений
Создание нового столбца, содержащего объединенные значения по нескольким критериям, значительно упрощает процесс поиска. Новый столбец с объединенными значениями можно повторно использовать в различных запросах и аналитических операциях. К нему можно применять стандартные функции фильтрации и сортировки. Также объединение в один столбец позволяет легко сравнивать результаты поиска и анализировать их на предмет соответствия всем заданным критериям.
Задача
В таблице канцтоваров может значиться несколько одинаковых товаров с разными артикулами. Представим, что нам нужно найти товар и по наименованию, и по артикулу. Для этого нужно будет вручную объединить два столбца — «Товары» и «Артикул».
Лист «Канцтовары»
![Канцтовары](https://blog.skillfactory.ru/wp-content/uploads/2024/04/sposob-1-list-kanctovari.png)
Лист «Заказ»
![Заказы](https://blog.skillfactory.ru/wp-content/uploads/2024/04/sposob-1-list-zakaz.png)
Порядок решения
- В листе «Заказ» вставьте слева от столбца артикула новый пустой столбец.
![Вставить новый столбик](https://blog.skillfactory.ru/wp-content/uploads/2024/04/sposob-1-poryadok-resheniya-1-1.png)
- Назовите столбец «Артикул и товар».
- Соедините значения ячеек.
Введите в его первую ячейку формулу =C5&»-«&D5, которая соединит первую ячейку из столбца «Артикул» и первую ячейку из столбца «Товары».
- Растяните значение ячейки с формулой на весь столбец.
- Отсортируйте полученный столбец по возрастанию.
- Определите искомое значение.
Теперь вместо одного искомого значения можно выбрать два. Выберите на листе «Канцтовары» первую ячейку из «Артикула» (B4) и первую ячейку из «Товары» (C4). Искомое значение будет выглядеть так: B4&»-«&C4.
- Установите диапазон значений.
Диапазоном будет таблица на листе «Заказ», начиная от первой ячейки столбца «Артикул и товар» и заканчивая последней ячейкой столбца «Кол-во» — заказ!$B$5:$E$25.
- Выберите столбец для поиска и интервальный просмотр.
Ищем в четвертом столбце (4) методом точного поиска (0).
- Составьте формулу.
В листе «Канцтовары» в ячейке с результатом составьте формулу ВПР:
=ВПР(B4&»-«&C4; заказ!$B$5:$E$25; 4; 0)
![Столбец с результатом формулы](https://blog.skillfactory.ru/wp-content/uploads/2024/04/sposob-1-poryadok-resheniya-9.png)
Способ 2 — с применением массивов и функции ВЫБОР
Если у вас есть набор критериев, которые должны быть выполнены одновременно, с помощью массивов и функции ВЫБОР можно создать структуру, которая проверяет каждый критерий и возвращает соответствующее значение.
Кроме этого, если у вас есть набор данных, который нужно классифицировать по разным категориям в зависимости от нескольких условий, массивы и функция ВЫБОР помогут в этом. Можно создать набор критериев, определяющих, в какую категорию должна быть помещена каждая сущность, и затем использовать функцию ВЫБОР, чтобы вернуть соответствующую категорию.
Задача
Есть таблица с информацией о продажах канцтоваров. Нужно узнать, сколько товаров продано, используя в качестве критериев поиска наименование товара и его стоимость. В этом поможет функция ВЫБОР.
![Товары](https://blog.skillfactory.ru/wp-content/uploads/2024/04/sposob-2-zadacha.png)
Что такое функция ВЫБОР
Эта функция использует номер индекса, чтобы выбрать и вернуть данные из массивов значений. Синтаксис функции выглядит так:
ВЫБОР(номер_индекса;значение1; значение2;…)
Номер индекса — номер выбираемого аргумента-значения. Это должно быть число от 1 до 254, формула или ссылка на ячейку, содержащая число в этом диапазоне.
Когда номер индекса равен 1, функция ВЫБОР вернет значение1; если он равен 2, вернется значение2, и т. д.
Если номер индекса меньше 1 или больше числа последнего значения в списке, функция ВЫБОР вернет ошибку #ЗНАЧ! (описание распространенных ошибок — в конце статьи).
Если номер индекса представляет собой дробь, то он округляется до меньшего целого.
Значение1, значение2, … — аргумент значение1 является обязательным, следующие за ним — нет. Аргументов может быть от 1 до 254. Из них функция ВЫБОР, используя номер индекса, выбирает значение. Аргументы могут быть числами, ссылками на ячейки, формулами, функциями или текстом.
Порядок решения
- Определите критерии поиска и цель.
Нужно найти количество проданных единиц G5, исходя из наименования товара G3 и его стоимости G4. Объединенный результат двух ячеек можно получить так: G3&G4.
![Пример](https://blog.skillfactory.ru/wp-content/uploads/2024/04/sposob-2-poryadok-resheniya-1.png)
- Воспользуйтесь функцией ВЫБОР в формуле ВПР.
Выглядеть это будет так:
=ВПР(G3&G4;ВЫБОР({1;2}; B3:B23&C3:C23;D3:D23);2;1)
Где:
{1;2} — создает двумерный массив, в котором будут храниться объединенные значения двух критериев.
B3:B23&C3:C23 — объединение двух столбцов с наименованиями и стоимостью, которое будет храниться в первом столбце двумерного массива.
D3:D23 — диапазон количества проданных единиц, который будет храниться во втором столбце двумерного массива.
2 — столбец двумерного массива, который будет использоваться для извлечения результата (столбец с количества проданных единиц).
1 — будет использоваться приближенный поиск.
![Функция ВЫБОР](https://blog.skillfactory.ru/wp-content/uploads/2024/04/sposob-2-poryadok-resheniya-2.png)
При работе с массивами после написания формулы нужно обязательно нажать Ctrl + Shift + Enter.
После применения формулы получаем результат:
![Результат](https://blog.skillfactory.ru/wp-content/uploads/2024/04/sposob-2-poryadok-resheniya-resultat.png)
Способ 3 — с применением массивов и функции ЕСЛИ
Использование массивов и функции ЕСЛИ позволяет автоматизировать процесс поиска и выбора данных в таблице, что особенно полезно при работе с большим объемом данных.
Задача
Возьмем ту же таблицу с данными о продаже канцтоваров. Критерии поиска останутся неизменными (наименование и стоимость товара). Но искать количество проданных единиц будем с использованием функции ЕСЛИ.
Что такое функция ЕСЛИ
Эта функция позволяет проводить логические сравнения значений с ожидаемыми результатами. Синтаксис функции выглядит так:
ЕСЛИ(лог_выражение; значение_если_истина; [значение_если_ложь])
Логическое выражение — условие, которое нужно проверить.
Значение, если истина — то значение, которое вернется, если логическое выражение имеет значение ИСТИНА.
Значение, если ложь (необязательно) — значение, которое вернется, если логическое выражение — ЛОЖЬ.
Порядок решения
Используйте формулу (не забывайте нажать Ctrl + Shift + Enter):
=ВПР(G3;ЕСЛИ(C3:C23=G4;B3:D23;»»);3;0)
Где:
G3 — первый критерий для поиска по наименованию товара.
C3:C23=G4 — логическое выражение, которое проверяет, есть ли совпадение в указанном диапазоне (столбце стоимости) с ячейкой G4 (стоимость товара).
B3:D23 — диапазон, который вернется в случае, если логическое выражение истинно. Если оно ложно, то вернется пустая строка.
3 — столбец для поиска результата.
0 — используем точный поиск.
![Функция ЕСЛИ](https://blog.skillfactory.ru/wp-content/uploads/2024/04/sposob-3-1.png)
Распространенные ошибки в функции ВПР
#Н/Д — означает, что функция ВП не смогла найти то, что вы искали.
#ССЫЛКА! — появляется, когда формула ссылается на ячейки, которых нет, или когда пытается использовать столбец, который слишком велик.
#ЗНАЧ! — возникает, если номер столбца не является числом или если значение «интервальный просмотр» не является ни ИСТИНА, ни ЛОЖЬ.
Кратко о том, что такое функция ВПР в Excel
ВПР или VLOOKUP — это базовая функция Excel и Google Sheets, которая позволяет искать информацию в больших наборах данных, соединять данные из разных таблиц или листов, генерировать отчеты и обновлять данные в таблице. Синтаксис функции выглядит так:
Fx = ВПР(искомое_значение; таблица_диапазон; номер_столбца; [интервальный_просмотр])
Таблица (диапазон) — диапазон ячеек, в котором будет проведен поиск искомого значения.
Номер столбца — столбец в таблице (диапазоне), из которого будет возвращен результат.
Искомое значение — значение, которое функция ищет в первом столбце диапазона.
Интервальный просмотр (необязательный аргумент) — определяет, нужно ли искать точное совпадение. По умолчанию значение аргумента — ЛОЖЬ.
Полезные ссылки
Как сделать сводную таблицу Excel: пошаговая инструкция
5 причин, почему пора перестать пользоваться Excel и перейти на Python