Профессиональные примеры формулы ВПР в Excel

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

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

ВПР(lookup_value, table_array, col_index_num, [range_lookup])

А дальше мы рассмотрим, каким образом можно сделать ее применение более гибким.

Как использовать несколько критериев в формуле

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

Пример 1: использование двух условий поиска

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

То есть, у нас есть два критерия:

1. Имя покупателя.
2. Название продукта.

Для наглядности, приводим таблицу.

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

Таким образом, нам необходимо вставить новый столбец в правую часть и скопировать формулу =B2&C2 по всей колонке. Для увеличения простоты чтения строки можно использовать пробел для разделения соединенных значений =B2&” “&C2.

Далее используется формула ВПР для этих значений, как показано на примере:

=ВПР(«Jeremy Sweets», A2:D11, 4, FALSE)

Или же можно сократить эту формулу до:

=ВПР(G1&» «&G2, A2:D11, 4, ЛОЖЬ)

Цифра 4 в формуле означает номер колонки, данные из которой извлекаются.

Чтобы формула работала правильно, значения во вспомогательной колонке должны быть объединены точно таким же способом, как и в аргументе «Искомое значение». В описанном нами примере для разделения критериев использовался пробел как во вспомогательной колонке, так и в формуле ВПР().

Пример 2: использование множества условий формулы

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

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

К счастью, Excel предусматривает несколько вариантов решения этой проблемы. Один из них – использование комбинации формул ИНДЕКС() и ПОИСКПОЗ(). Такая формула будет довольно длинной, но можно обойтись без добавления дополнительного столбца.

Например, формула может быть следующей:

=ИНДЕКС(D2:D11, ПОИСКПОЗ(1, (G1=A2:A11) * (G2=B2:B11) * (G3=C2:C11), 0))

В Microsoft Office 365 недавно была добавлена функция ПРОСМОТРХ().

Пример использования формулы следующий:
=XLOOKUP(1, (G1=A2:A11) * (G2=B2:B11) * (G3=C2:C11), D2:D11)

Расшифровка обеих формул в описываемом нами случае:

1. G1 – первое условие (дата).
2. G2 – условие номер 2 (имя клиента).
3. G3 – поисковый критерий 3 – (наименование товара).
4. A2:A11 – искомый диапазон 1.
5. B2:B11 – диапазон для поиска, в котором приводятся имена клиентов.
6. C2:C11 – искомый диапазон 3, где перечисляются наименования товаров.
7. D2:D11 – диапазон возвращаемых значений (количество товара).

Использование функции ВПР для получения значений, следующих за первым

Насколько опытные пользователи Excel знают, формула ВПР() возвращает только первое попавшееся значение. Но что делать, если совпадений больше, и требуется получить второй или третий элемент? Эта задача является сложной на первый взгляд, но решить ее значительно проще, чем может сперва показаться.

Формула 1: Совпадение n по функции ВПР

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

Проще всего это сделать путем добавления вспомогательной колонки в левую часть таблицы так, как было описано с самого начала. В каждую ячейку прописываются имена клиентов с цифрой, например, John Doe1, John Doe2. Для этого нужно применить формулу СЧЁТЕСЛИ, учитывая нахождение клиентов в столбце B: 

=B2&СЧЁТЕСЛИ($B$2:B2;B2)

Данная формула направляется в ячейку А2, и после этого ее можно скопировать в такое количество ячеек, которое необходимо.

Затем нужно ввести целевое имя и искомый номер в отдельные ячейки (F1 и F2) и использовать эту формулу:

=ВПР(F1&F2, A2:C11, 3, ЛОЖЬ)

Формула 2: Поиск второго вхождения

Если вам требуется найти второе вхождение, но по каким-то причинам невозможно добавление вспомогательной колонки, это можно сделать с помощью функции ДВССЫЛ() вместе с ПОИСКПОЗ():

=ВПР(E1, ДВССЫЛ(«A»&(ПОИСКПОЗ(E1, A2:A11, 0)+2)&»:B11″), 2, ЛОЖЬ)

В этой формуле:

1. E1 – искомое значение.
2. A2:A11 – диапазон, в котором осуществляется поиск.
3. B11 – последняя ячейка в таблице, где осуществляется поиск.

Значительно проще увидеть, как работает эта функция, на реальном примере.

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

Принцип действия этой формулы

В этой формуле есть ключевая часть, которая создает динамический поисковый диапазон. Она являет собой объединение функций ДВССЫЛ() и ПОИСКПОЗ():

ДВССЫЛ(«A»&(ПОИСКПОЗ(E1, A2:A11, 0)+2)&»:B11″)

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

Расчет необходимого увеличения исходя из позиции заглавной строки (в нашем случае это строка A1) можно рассчитать и с помощью формулы. Для этого нужно использовать формулу 1+СТРОКА(А1). Естественно, на место A1 необходимо вставлять ту ячейку, которая больше всего подходит в конкретной ситуации.

В результате, мы получаем следующую строку, которая конвертируется в диапазон функцией ДВССЫЛ().

Этот диапазон вставляется на место аргумента «просматриваемый массив», и формула начинает искать необходимое значение в строке 5, а первое значение пропускается.

Как с помощью функции ВПР возвращать несколько значений

Функция Excel ВПР() не предусматривает возможности извлекать больше одного значения. Но есть ли способ обойти это ограничение? Конечно, да. Правда, данный метод нельзя назвать простым. Он требует использования сразу нескольких функций, таких как ИНДЕКС(), НАИМЕНЬШИЙ(), СТРОКА().

Например, следующая формула способна найти все значения, содержащихся в ячейке F2 среди ячеек, находящихся в рамках диапазона B2:B16. Результат вычислений по этой формуле в виде нескольких значений возвращается из таких же строк в колонке C:

{=ЕСЛИОШИБКА(ИНДЕКС($C$2:$C$16;НАИМЕНЬШИЙ(ЕСЛИ($F$2=B2:B16;СТРОКА(C2:C16)-1;»»);СТРОКА()-3));»»)}

Ввод этой формулы возможен двумя способами:

1. Непосредственный ее ввод в первую ячейку. После этого нужно нажать на комбинацию клавиш Ctrl + Shift + Enter, после чего скопировать ее вниз на необходимое количество ячеек.
2. Выбрать диапазон значений в рамках одного столбика, как в скриншоте ниже, ввести формулу, после чего нажать на вышеуказанную комбинацию клавиш.

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

Поиск по известному столбцу и строке

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

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

В нашей формуле названия товаров указаны в диапазоне A2:A9, месяцы – B1:F1, а целевой месяц указан в ячейке I2.

В таком случае формула обретет следующий вид:

=ВПР(I1, A2:F9, ПОИСКПОЗ(I2, A1:F1, 0), ЛОЖЬ)

Как эта формула работает

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

ПОИСКПОЗ(I2, A1:F1, 0)

Выражаясь простыми словами, эта формула говорит: ищите значение, находящееся в ячейке I2 в диапазоне значений A1:F1, после чего пропишите позицию найденного значения в массиве. Передавая в третий аргумент значение 0, пользователь говорит программе, чтобы она искала такое же значение, как прописывается в поиске. Это работает таким же образом, как и использование параметра ЛОЖЬ в формуле ВПР().

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

VLOOKUP(I1, A2:F9, 4, FALSE)

При этом нужно обращать внимание на то, что, хотя названия месяцев описаны в колонке B, поиск все же нужно осуществлять по всей таблице. Это необходимо для полного соответствия значения, возвращаемого функцией ПОИСКПОЗ(), позиции столбца в аргументе table_array функции ВПР().

Возможно ли использование нескольких ВПР в одной формуле

Иногда случается, что у основной таблицы и таблицы, в которой осуществляется поиск, нет общего столбца. Это не дает использовать стандартную функцию VPR. Несмотря на это, есть другая таблица, которая не содержит искомой информации, но зато у нее есть по общей колонке с главной таблицей и таблицей поиска.

Значительно проще это изобразить с помощью скриншота.

Задача – копировать цены в главную таблицу, основываясь на идентификаторе. Сложность заключается в том, что в таблице содержатся цены на товары, в которых нет своего ID. Поэтому приходится использовать несколько формул ВПР() вместе.

Чтобы продемонстрировать это, можно создать пару диапазонов.

1. Искомый диапазон 1 назовем «Товары».
2. Второй искомый диапазон пусть называется «Цены».

Они имеют адреса D3:E3 и G3:H3 соответственно.

Таблицы могут быть одинаковыми или различными.

После этого нужно прописать действие, называемое двойным ВПР или вложенным ВПР.

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

=ВПР(A3, Products, 2, ЛОЖЬ)

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

В результате, получится такая нехитрая формула:

=ВПР(ВПР(A3, Products, 2, ЛОЖЬ), Prices, 2, ЛОЖЬ)

И вот скриншот, чтобы было более понятно.

Динамическая подстановка данных из разных таблиц

Для начала нужно более детально объяснить значение термина «Динамическая подстановка данных». Иногда может случиться так, что есть два листа с одинаковым форматом данных. И перед человеком стоит задача извлечь требуемые данные исходя из того, какое значение указано в определенной ячейке. Значительно проще это объяснить на примере.

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

Точно так же, как в прошлом примере, нам необходимо сначала определить названия нескольких диапазонов A2:B5 в разных регионах.

1. CA – CA_Sales.
2. FL – FL_Sales.
3. KS – KS_Sales.

Как вы можете увидеть, у всех названных диапазонов есть общая часть – Sales, а также есть уникальные части, обозначающие регион. Перед тем, как осуществлять дальнейшие действия, нужно убедиться, что диапазоны с разными названиями имеют одинаковую форму, иначе формула не будет работать.

Формула 1: использование комбинации формул ДВССЫЛ и ВПР

Если необходимо получить данные сразу с нескольких таблиц, лучшим решением будет использовать одновременно формулы ДВССЫЛ() и ВПР(). Это наиболее простое для понимания решение, которое, кроме всего прочего, невероятно компактное и поможет сэкономить много времени.

Для примера, мы организуем общую таблицу таким способом:

1. Ввести необходимые продукты в ячейки A2 и A3. Они и будут нашими искомыми значениями.
2. Ввести уникальные части названий последовательно в ячейки B1, C1, D1.

Далее необходимо объединить уникальную часть с общей и получившееся значение использовать в качестве параметра функции ДВССЫЛ().

ДВССЫЛ(B$1&»_Sales»)

Эта формула превращает строчное значение в понятный для экселя вид, после чего результат этой функции необходимо разместить в аргументе table_array:

=ВПР($A2, ДВССЫЛ(B$1&»_Sales»), 2, ЛОЖЬ)

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

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

Если главная таблица организована другим способом, необходимо зафиксировать адрес с помощью значка $:

=VLOOKUP(B$1, INDIRECT($A2&»_Sales»), 2, FALSE)

Формула 2: ВПР и вложенное ЕСЛИ

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

Пример формулы следующий:

=ВПР($A2, IF(B$1=»CA», CA_Sales, ЕСЛИ(B$1=»FL», FL_Sales, ЕСЛИ(B$1=»KS», KS_Sales,»»))), 2, ЛОЖЬ)

В этой формуле $A2 – это значение, которое необходимо обнаружить (название элемента) и B$1 – значение, указывающее на регион.

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

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

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