Функция ВПР() в EXCEL. Примеры и описание

Пример как использовать функцию ВПР по нескольким столбцам для одной и той же таблицы с данными. Формула комбинации функций ВПР и ЕСЛИ.

ВПР по нескольким условиям с использованием дополнительного столбца.

Задачу, рассмотренную в предыдущем примере, можно решить и другим способом – без использования формулы массива. Ведь работа с массивами многим представляется сложной и недоступной для понимания. Дополнительный столбец для поиска по нескольким условиям будет в определенном отношении более простым вариантом.

Итак, необходимо выбрать значение выручки за определенный месяц, год и по нужному магазину. В итоге имеем 3 условия отбора.

Сразу по трем столбцам функция ВПР искать не может. Поэтому нам нужно объединить их в один. И, поскольку поиск производится всегда в крайнем левом (первом) столбце, то нужно добавить его в нашу таблицу тоже слева.

Вставляем перед таблицей с данными дополнительный столбец A. Затем при помощи оператора & объединяем в нем содержимое B,C и D. Записываем в А7

=B7&C7&D7

и копируем в находящиеся ниже ячейки.

Формула поиска в D4 будет выглядеть:

=ВПР(D1&D2&D3;A7:E20;5;0)

В диапазон поиска включаем и наш дополнительный столбец. Критерий поиска – также объединение 3 значений. И извлекаем результат из 5 колонки.

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

Вид станет приятнее, а на результаты это никак не повлияет.

Синтаксис

=ВПР(элемент_который_нужно_найти;адрес_таблицы_где_находится_искомое_значение;порядковый_номер_столбца;интервальный_просмотр)

Элемент – может быть числовым (адрес ячейки) или текстовым («текст»).

Адрес таблицы – диапазон ячеек, где примерно находится значение.

Номер столбца – принимает целое число из диапазона от 1 до n, из него будет извлечен результат.

Интервальный просмотр – приблизительное (ближайшее) соответствие критерию обозначается как 1 (истина), а точное соответствие – 0 (ложь). Данный логический аргумент указывать необязательно, если таблица отсортирована от минимального к максимальному значению. Если таблица не отсортирована и аргумент опущен, это равносильно истине.

Важно! Искомое значение должно находиться слева (в первом столбце) от возвращаемого элемента.

В русифицированной версии Excel аргументы вводятся через знак «;», в англоязычной – через запятую.

Синтаксис

Функция ВПР имеет четыре параметра:

=ВПР( <ЧТО> ; <ГДЕ> ; <НОМЕР_СТОЛБЦА> [;<ОТСОРТИРОВАНО>] ), тут:

<ЧТО> — искомое значение (редко) или ссылка на ячейку, содержащую искомое значение (подавляющее большинство случаев);

<ГДЕ> — ссылка на диапазон ячеек (двумерный массив), в ПЕРВОМ (!) столбце которого будет осуществляться поиск значения параметра <ЧТО>;

<НОМЕР_СТОЛБЦА> — номер столбца в диапазоне, из которого будет возвращено значение;

<ОТСОРТИРОВАНО> — это очень важный параметр, который отвечает на вопрос, а отсортирован ли по возрастанию первый столбец диапазона <ГДЕ>. В случае, если массив отсортирован, мы указываем значение ИСТИНА (TRUE) или 1, в противном случае — ЛОЖЬ (FALSE) или 0. В случае, если данный параметр опущен, он по умолчанию становится равным 1.

Держу пари, что многие из тех, кто знает функцию ВПР как облупленную, прочитав описание четвёртого параметра, могут почувствовать себя неуютно, так как они привыкли видеть его в несколько ином виде: обычно там идёт речь о точном соответствии при поиске (ЛОЖЬ или 0) либо же о диапазонном просмотре (ИСТИНА или 1).

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

ВПР по двум условиям при помощи формулы массива.

У нас есть таблица, в которой записана выручка по каждому магазину за день. Мы хотим быстро найти сумму продаж по конкретному магазину за определенный день.

Для этого в верхней части нашего листа запишем критерии поиска: дата и магазин. В ячейке B3 будем выводить сумму выручки.

Формула в B3 выглядит следующим образом:

{=ВПР(B1;ЕСЛИ(B6:B19=B2;A6:C19;””);3;0)}

Обратите внимание на фигурные скобки, которые означают, что это формула массива. То есть наша функция ВПР работает не с отдельными значениями, а разу с массивами данных.

Разберем процесс подробно.

Мы ищем дату, записанную в ячейке B1. Но вот только разыскивать мы ее будем не в нашем исходном диапазоне данных, а в немного видоизмененном. Для этого используем условие

ЕСЛИ(B6:B19=B2;A6:C19;””)

То есть, в том случае, если наименование магазина совпадает с критерием в ячейке B2, мы оставляем исходные значения из нашего диапазона. А если нет – заменяем их на пробелы. И так по каждой строке.

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

Как видите, строки, в которых ранее был «Магазин 1», заменены на пустые. И теперь искать нужную дату мы будем только среди информации по «Магазин 2». И извлекать значения выручки из третьей колонки.

С такой работой функция ВПР вполне справится.

Такой ход стал возможен путем применения формулы массива. Поэтому обратите особое внимание: круглые скобки в формуле писать руками не нужно! В ячейке B3 вы записываете  формулу

=ВПР(B1;ЕСЛИ(B6:B19=B2;A6:C19;””);3;0)

И затем нажимаете комбинацию клавиш CTRL+Shift+Enter.  При этом Excel поймет, что вы хотите ввести формулу массива и сам подставит скобки.

Таким образом, функция ВПР поиск по двум столбцам производит в 2 этапа: сначала мы очищаем диапазон данных от строк, не соответствующих одному из условий, при помощи функции ЕСЛИ и формулы массива. А затем уже в этой откорректированной информации производим обычный поиск по одному только второму критерию при помощи ВПР.

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

Вот как это будет выглядеть.

Видео — «Быстрый перенос данных с помощью функции ВПР в Экселе»

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

Это очень актуально для тех кто работает в закупках и отправляет заказы поставщику.

Обычно происходит следующая ситуация. Вы отправляете заказ поставщику, через некоторое время получаете ответ в виде счёта и сверяете заказ с счётом.

Всё ли есть в счёте, в нужном ли количестве, по правильным ли ценам и т.д.

Примеры

Вот несколько примеров использования функции ВПР.

Пример 1

Пример 2

Пример 3

Пример 4

Пример 5

Ошибки

Когда пользователь ошибается при вводе данных или выборе диапазона, вместо результата отображаются различные ошибки: #Н/Д, #ЗНАЧ, #ССЫЛКА.

Ошибка #Н/Д появляется, если:

1. Указанный диапазон не содержит искомый элемент.
2. Искомый элемент меньше, чем минимальный в массиве.
3. Задан точный поиск (аргумент «ЛОЖЬ» или 0), а искомого нет в диапазоне.
4. Задан приблизительный поиск (аргумент «ИСТИНА» или 1), но данные не отсортированы по возрастанию.
5. Разный формат (числовой и текстовый) у ячейки, откуда берется искомое, и ячейки с данными первого столбца.
6. В коде есть пробелы или невидимые непечатаемые знаки.
7. Используются значения времени или большие десятичные числа.

Во избежание ошибки #Н/Д, когда ВПР не находит значение, рекомендуется использовать следующую формулу: =ЕСЛИОШИБКА(ВПР(C2;A1:B12;2;ЛОЖЬ);0) – вместо 0 можно написать «не найдено».

Ошибка #ЗНАЧ появляется, если:

1. В качестве номера столбца указано число 0.
2. Длина первого аргумента превышает 255 знаков.

Ошибка #ССЫЛКА появляется, если третий аргумент больше количества столбцов в таблице.

Двойной ВПР при помощи ИНДЕКС + ПОИСКПОЗ

Далее речь у нас пойдет уже не о функции ВПР, но задачу мы будем решать ту же самую. В качестве критерия поиска нам опять нужно использовать несколько условий.

Существуют, пожалуй, даже более гибкие решения, нежели функция ВПР. Это комбинация функций ИНДЕКС + ПОИСКПОЗ.

Область их применения очень велика, о чем бы также будем рассказывать на сайте mister-office.ru.

А пока вернемся вновь к нашей задаче.

Формула в С4 теперь выглядит так:

=ИНДЕКС(D7:D20;ПОИСКПОЗ(1;(A7:A20=C1)*(B7:B20=C2)*(C7:C20=C3);0))

И не забываем при вводе нажать CTRL+Shift+Enter! Это формула массива.

Теперь давайте разбираться, как это работает.

Функция ИНДЕКС в нашем случае позволяет извлечь элемент из списка по его порядковому номеру. Список – это диапазон D7:D20, где записаны суммы выручки. А вот порядковый номер, который нужно извлечь, мы определяем при помощи ПОИСКПОЗ.

Синтаксис здесь следующий:

ПОИСКПОЗ(что_ищем; где_ищем; тип_поиска)

Тип поиска ставим 0, то есть точное совпадение. В нашем случае мы будем искать 1. Далее мы определим массив, в котором будем работать.

Выражение  (A7:A20=C1)*(B7:B20=C2)*(C7:C20=C3) позволит создать виртуальную таблицу примерно такого вида:

Как видите, первоначально мы последовательно сравниваем каждое значение с нашим критерием отбора. В столбце А у нас записаны месяцы – сравниваем их с месяцем-критерием из ячейки C1. В случае совпадения получаем ИСТИНА, иначе – ЛОЖЬ. Аналогично последовательно проверяем год и название магазина. А затем просто перемножаем значения. Поскольку логические переменные для Excel – это либо 0, либо 1, то произведение их может быть равно 1 только в том случае, если мы имеем по каждой колонке ИСТИНА (то есть,1). Во всех остальных случаях получаем 0.

Убеждаемся, что цифра 1 встречается только единожды.

При помощи ПОИСКПОЗ определяем, на какой позиции она находится. На какой позиции находится 1, на той же позиции находится в массиве и искомая сумма выручки. В нашем случае это 10-я.

Далее при помощи ИНДЕКС извлекаем 10-ю по счету выручку.

Таким образом мы выбрали значение по нескольким условиям без использования функции ВПР.

Используем несколько ВПР в одной формуле

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

Давайте разберем следующий пример. У нас есть основная таблица (Main table) со столбцом SKU (new), куда необходимо добавить столбец с соответствующими ценами из другой таблицы. Кроме этого, у нас есть 2 таблицы поиска. Первая (Lookup table 1) содержит обновленные номера SKU (new) и названия товаров, а вторая (Lookup table 2) – названия товаров и старые номера SKU (old).

Чтобы добавить цены из второй таблицы поиска в основную таблицу, необходимо выполнить действие, известное как двойной ВПР или вложенный ВПР.

1. Запишите функцию ВПР, которая находит имя товара в таблице Lookup table 1, используя SKU, как искомое значение:

   =VLOOKUP(A2,New_SKU,2,FALSE)
   =ВПР(A2;New_SKU;2;ЛОЖЬ)

   Здесь New_SKU – именованный диапазон $A:$B в таблице Lookup table 1, а 2 – это столбец B, который содержит названия товаров (смотрите на рисунке выше)

2. Запишите формулу для вставки цен из таблицы Lookup table 2 на основе известных названий товаров. Для этого вставьте созданную ранее формулу в качестве искомого значения для новой функции ВПР:

   =VLOOKUP(VLOOKUP(A2,New_SKU,2,FALSE),Price,3,FALSE)
   =ВПР(ВПР(A2;New_SKU;2;ЛОЖЬ);Price;3;ЛОЖЬ)

   Здесь Price – именованный диапазон $A:$C в таблице Lookup table 2, а 3 – это столбец C, содержащий цены.

На рисунке ниже виден результат, возвращаемый созданной нами формулой:

Недостатки формулы

Недостатки ВПР очевидны: во-первых, она ищет только в первом столбце указанного массива, а во-вторых, только справа от данного столбца. А как вы понимаете, вполне может случиться так, что столбец, содержащий необходимую информацию, окажется слева от столбца, в котором мы будем искать. Этого недостатка лишена уже упомянутая связка формул ИНДЕКС+ПОИСКПОЗ (INDEX+MATCH), что делает её наиболее гибким решением по извлечению данных из таблиц в сравнении с ВПР (VLOOKUP).

Видео — «Сравнение двух таблиц с помощью функции ВПР в Экселе»

Эпилог

Коллеги, поздравляю, с этого момента ваша работа с данными значительно упростится и ускорится, ведь теперь вы «почтигуру» по применению функции ВПР в Экселе.

Достойная замена – функция СУММПРОИЗВ.

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

Поясним расчеты.

Выражение

=СУММПРОИЗВ((A2:A27=$G$2)*(B2:B27=$G$3)*($C$2:$C$27=G4)*(D2:D27))

Работает как формула массива, хотя по факту таковой не является. В этом заключается замечательное свойство функции СУММПРОИЗВ, о которой мы еще много будем говорить в других статьях.

Последовательно по каждой строке диапазона от 2-й до 27-й она проверяет совпадение каждого соответствующего значения с критерием поиска. Эти результаты перемножаются между собой и в итоге еще умножаются на сумму выручки. Если среди трех условий будет хотя бы одно несовпадение, то итогом будет 0. В случае совпадения сумма выручки трижды умножится на 1.

Затем все эти 27 произведений складываются, и результатом будет выручка нужного менеджера в каком-то регионе за определенную дату.

В качестве бонуса можно продолжить этот пример и рассчитать общую сумму продаж менеджера в определенном регионе.

Для этого из формулы просто уберем сравнение по дате.

=СУММПРОИЗВ((A2:A27=$G$2)*(B2:B27=$G$3)*(D2:D27))

Кстати, возможен и другой вариант расчета с этой же функцией:

=СУММПРОИЗВ(–(A2:A27=$G$2);–(B2:B27=$G$3);(D2:D27))

Итак, мы рассмотрели примеры использования функции ВПР с двумя и с несколькими условиями. А также обнаружили, что этой ценной функции есть замечательная альтернатива.

Примеры использования функции ВПР:

6. Как использовать ВПР с несколькими критериями

VLOOKUP — мощная функция, без сомнения, но в случае поиска на основе двух или более критериев ВПР не справится.

Однако, если у Вас есть доступ и права на изменение данных, то можно воспользоваться вспомогательным столбцом, как в примере 2. Мы объединим несколько полей с помощью амперсанда и используем их в функции. Проще говоря, мы добавим новый столбец, в котором объединятся все критерии в один родительский критерий. Теперь мы сможем применить к данным функцию ВПР.

Давайте рассмотрим пример:

Несмотря на то, что все проектировщики баз данных как Отче наш должны знать три основные нормальные формы баз данных, частенько мы сталкиваемся с таблицами, как представлена в примере.

Она не подчиняется никаким правилам, поэтому с ней трудно проводить различные манипуляции.

Попробуем привести таблицу в более понятный вид.

Предположим, нам нужно найти количество баллов ученицы Ольги по физике. Сначала добавим новый столбец, склеив первый и второй:

Чтобы сделать Вспомогательный столбец более читабельным, мы добавили пробел после имени, используя “ ”. 

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

Посмотрим, что у нас получилось:

Важно, чтобы функция была настроена на точное совпадение.