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