Расширенные примеры VLOOKUP: многокритериальный поиск

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

• Поиск в Excel по нескольким критериям
• Извлекаем 2-е, 3-е и т. д. значения с помощью ВПР.
• Получить все повторения желаемого значения
• XNUMXD поиск по известной строке и столбцу
• Использование нескольких ВПР в одной формуле
• Динамическая подмена данных из разных таблиц

Поиск в Excel по нескольким критериям

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

Пример 1. Поиск по двум разным критериям

Предположим, у нас есть список заказов, и мы хотим найти Количество товаров (кол-во), на основе двух критериев – Имя клиента (Клиент) и авление продуктов (Продукт). Дело осложняется тем, что каждый из покупателей заказал несколько видов товара, как видно из таблицы ниже:

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

=VLOOKUP(B1,$A$5:$C$14,3,FALSE)

=ВПР(B1;$A$5:$C$14;3;ЛОЖЬ)

– эта формула вернет результат 15соответствующий продукту Яблоки, потому что это первое совпадающее значение.

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

Итак, вы добавляете в таблицу вспомогательный столбец и копируете по всем ее ячейкам следующую формулу: =B2&C2. Если вы хотите, чтобы строка была более читабельной, вы можете разделить объединенные значения пробелом: =B2&» «&C2. После этого вы можете использовать следующую формулу:

=VLOOKUP("Jeremy Hill Sweets",$A$7:$D$18,4,FALSE)

=ВПР("Jeremy Hill Sweets";$A$7:$D$18;4;ЛОЖЬ)

or

=VLOOKUP(B1,$A$7:$D$18,4,FALSE)

=ВПР(B1;$A$7:$D$18;4;ЛОЖЬ)

Где находится ячейка B1 содержит объединенное значение аргумента искомое_значение (искомое_значение) и 4 – Аргумент номер_столбца (column_number), т. е. номер столбца, содержащего данные, которые необходимо получить.

Пример 2: ВПР по двум критериям с просмотром таблицы на другом листе

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

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

Итак, формула с ВПР могло быть так:

=VLOOKUP(B2&" "&C2,Orders!$A&$2:$D$2,4,FALSE)

=ВПР(B2&" "&C2;Orders!$A&$2:$D$2;4;ЛОЖЬ)

Здесь столбцы B и C содержат имена клиентов и названия продуктов соответственно, а ссылку Приказы!$A&$2:$D$2 определяет таблицу для поиска на другом листе.

Чтобы сделать формулу более читабельной, можно дать диапазону представления имя, и тогда формула будет выглядеть намного проще:

=VLOOKUP(B2&" "&C2,Orders,4,FALSE)

=ВПР(B2&" "&C2;Orders;4;ЛОЖЬ)

Чтобы формула работала, значения в крайнем левом столбце просматриваемой таблицы должны быть объединены точно так же, как и в критериях поиска. На рисунке выше мы объединили значения u2bu2band и поместили между ними пробел, точно так же, как нужно сделать в первом аргументе функции (BXNUMX& «» & CXNUMX).

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

Я согласен, что добавление вспомогательного столбца – не самое изящное и не всегда приемлемое решение. Вы можете сделать то же самое без вспомогательного столбца, но для этого потребуется гораздо более сложная формула с комбинацией функций. ИНДЕКС (ИНДЕКС) и МАТЧ (Более разоблачено).

Извлекаем 2-е, 3-е и т. д. значения с помощью ВПР.

Вы уже знаете, что ВПР может возвращать только одно совпадающее значение, точнее, первое найденное. Но что, если это значение повторяется в просматриваемом массиве несколько раз, и вы хотите извлечь 2-й или 3-й из них? Что, если все значения? Проблема кажется сложной, но решение существует!

Предположим, что в одном столбце таблицы указаны имена клиентов (Имя клиента), а в другом — товары (Продукт), которые они купили. Попробуем найти 2-й, 3-й и 4-й товары, купленные данным покупателем.

Самый простой способ — добавить вспомогательный столбец перед столбцом Имя Клиента и заполните его именами клиентов с количеством повторений каждого имени, например, John Doe1, John Doe2 и т. д. С нумерацией проделаем работу с помощью функции COUNTIF (COUNTIF), учитывая, что имена клиентов находятся в столбце B:

=B2&COUNTIF($B$2:B2,B2)

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

После этого вы можете использовать обычную функцию ВПРнайти нужный заказ. Например:

• Найдите 2-й товар, заказанный клиентом Дэн Браун:

  =VLOOKUP("Dan Brown2",$A$2:$C$16,3,FALSE)

  =ВПР("Dan Brown2";$A$2:$C$16;3;ЛОЖЬ)

• Найдите 3-й товар, заказанный клиентом Дэн Браун:

  =VLOOKUP("Dan Brown3",$A$2:$C$16,3,FALSE)

  =ВПР("Dan Brown3";$A$2:$C$16;3;ЛОЖЬ)

Фактически, вы можете ввести ссылку на ячейку в качестве искомого значения вместо текста, как показано на следующем рисунке:

Если ты только ищешь 2-е повторение, можно обойтись и без вспомогательного столбца, создав более сложную формулу:

=IFERROR(VLOOKUP($F$2,INDIRECT("$B$"&(MATCH($F$2,Table4[Customer Name],0)+2)&":$C16"),2,FALSE),"")

=ЕСЛИОШИБКА(ВПР($F$2;ДВССЫЛ("$B$"&(ПОИСКПОЗ($F$2;Table4[Customer Name];0)+2)&":$C16");2;ИСТИНА);"")

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

• $ F $ 2 – ячейка, содержащая имя покупателя (оно неизменное, обратите внимание – ссылка абсолютная);
• $ Б $ - столбец Имя Клиента;
• Table4 — Ваш стол (это место также может быть обычным ассортиментом);
• $ C16 – конечная ячейка вашей таблицы или диапазона.

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

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

Получить все повторения желаемого значения

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

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

{=IFERROR(INDEX($C$2:$C$16,SMALL(IF($F$2=B2:B16,ROW(C2:C16)-1,""),ROW()-3)),"")}

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

Введите эту формулу массива в несколько соседних ячеек, например в ячейки Ф4: Ф8как показано на рисунке ниже. Количество ячеек должно быть равно или больше максимально возможного количества повторений искомого значения. Не забудьте нажать Shift + Ctrl + Enterправильно ввести формулу массива.

Если вам интересно понять, как это работает, давайте немного углубимся в детали формулы:

Часть 1:

IF($F$2=B2:B16,ROW(C2:C16)-1,"")

ЕСЛИ($F$2=B2:B16;СТРОКА(C2:C16)-1;"")

$F$2=B2:B16 – сравните значение в ячейке F2 с каждым из значений в диапазоне B2:B16. Если совпадение найдено, то выражение СТРОКА(C2:C16)-1 возвращает номер соответствующей строки (значение -1 позволяет не включать строку заголовка). Если совпадений нет, функция IF (IF) возвращает пустую строку.

Результат функции IF (ЕСЛИ) будет такой горизонтальный массив: {1,"",3,"",5,"","","","","","",12,"","",""}

Часть 2:

ROW()-3

СТРОКА()-3

Здесь функция РЯД (LINE) действует как дополнительный счетчик. Так как формула копируется в ячейки F4:F9, то вычитаем число 3 из результата функции, чтобы получить значение 1 в камере F4 (строка 4, вычесть 3), чтобы получить 2 в камере F5 (строка 5, вычесть 3) и так далее.

Часть 3:

SMALL(IF($F$2=B2:B16,ROW(C2:C16)-1,""),ROW()-3))

НАИМЕНЬШИЙ(ЕСЛИ($F$2=B2:B16;СТРОКА(C2:C16)-1;"");СТРОКА()-3))

Функция МАЛЕНЬКИЙ (МАЛЕНЬКИЙ) возврат н-ох наименьшее значение в массиве данных. В нашем случае, какую позицию (из самой маленькой) возвращать, определяет функция РЯД (ЛИНИЯ) (см. Часть 2). Итак, для клетки F4 функция МАЛЕНЬКИЙ({массив},1) Возвращает 1-й (наименьший) элемент массива, т.е. 1. Для ячейки F5 Возвращает 2-й наименьший элемент массива, т.е. 3, и т.д.

Часть 4:

INDEX($C$2:$C$16,SMALL(IF($F$2=B2:B16,ROW(C2:C16)-1,""),ROW()-3))

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

Функция ИНДЕКС (ИНДЕКС) просто возвращает значение определенной ячейки массива. С2: С16. Для ячейки F4 функция ИНДЕКС($C$2:$C$16) вернется ЯблокиЧто касается F5 функция ИНДЕКС($C$2:$C$16) вернется Сладости' и так далее.

Часть 5:

IFERROR()

ЕСЛИОШИБКА()

Наконец, мы помещаем формулу внутрь функции IFERROR (ЕСЛИОШИБКА), поскольку сообщение об ошибке вас вряд ли обрадует. #В (#Н/Д), если количество ячеек, в которые копируется формула, меньше количества повторяющихся значений в просматриваемом диапазоне.

XNUMXD поиск по известной строке и столбцу

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

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

Существует несколько способов выполнить поиск XNUMXD. Ознакомьтесь с вариантами и выберите тот, который подходит вам лучше всего.

Функции ВПР и ПОИСКПОЗ

Вы можете использовать кучу функций ВПР (ВПР) и БОЛЬШЕ ОТКРЫТОГО (MATCH), чтобы найти значение на пересечении полей авление продуктов (строка) и Месяц (столбец) рассматриваемого массива:

=VLOOKUP("Lemons",$A$2:$I$9,MATCH("Mar",$A$1:$I$1,0),FALSE)

=ВПР("Lemons";$A$2:$I$9;ПОИСКПОЗ("Mar";$A$1:$I$1;0);ЛОЖЬ)

Формула выше представляет собой регулярную функцию ВПР, который ищет точное совпадение значения «Лимоны» в ячейках с A2 по A9. Но поскольку вы не знаете, в каком столбце находятся продажи за март, вы не сможете задать номер столбца для третьего аргумента функции. ВПР. Вместо этого используется функция БОЛЬШЕ ОТКРЫТОГОчтобы определить этот столбец.

MATCH("Mar",$A$1:$I$1,0)

ПОИСКПОЗ("Mar";$A$1:$I$1;0)

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

• Ищем персонажей «Мар» — аргумент искомое_значение (искомое_значение);
• Просмотр ячеек от A1 до I1 – аргумент искомый_массив (просмотр_массива);
• Возврат точного совпадения – аргумент тип_соответствия (тип_соответствия).

. 0 в третьем аргументе вы говорите функции БОЛЬШЕ ОТКРЫТОГО найдите первое значение, которое точно соответствует искомому значению. Это эквивалентно значению НЕПРАВДА (ЛОЖЬ) для четвертого аргумента ВПР.

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

Функция СУММПРОИЗВ

Функция SUMPRODUCT (СУММПРОИЗВ) возвращает сумму произведений выбранных массивов:

=SUMPRODUCT(($A$2:$A$9="Lemons")*($A$1:$I$1="Mar"),$A$2:$I$9)

=СУММПРОИЗВ(($A$2:$A$9="Lemons")*($A$1:$I$1="Mar");$A$2:$I$9)

Функции ИНДЕКС и ПОИСКПОЗ

В следующей статье я подробно объясню эти функции, а пока можете просто скопировать эту формулу:

=INDEX($A$2:$I$9,MATCH("Lemons",$A$2:$A$9,0),MATCH("Mar",$A$1:$I$1,0))

=ИНДЕКС($A$2:$I$9;ПОИСКПОЗ("Lemons";$A$2:$A$9;0);ПОИСКПОЗ("Mar";$A$1:$I$1;0))

Именованные диапазоны и оператор пересечения

Если вы не разбираетесь во всех этих сложных формулах Excel, вам может понравиться этот наглядный и запоминающийся способ:

1. Выберите таблицу, откройте вкладку Формулы (Формулы) и нажмите Создать из выбранного (Создать из выделения).
2. Проверьте коробки Верхний ряд (строкой выше) и Левая колонка (в столбце слева). Microsoft Excel присвоит имена диапазонам на основе значений в верхней строке и левом столбце вашей электронной таблицы. Теперь вы можете выполнять поиск по этим именам напрямую, не создавая формул.
3. В любую пустую ячейку напишите =имя_строки имя_столбца, например так:

   = Лимоны Мар

   … или наоборот:

   = Мар Лимонс

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

Когда вы вводите имя, Microsoft Excel отображает всплывающую подсказку со списком совпадающих имен, как и при вводе формулы.

4. Нажмите Enter и проверить результат

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

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

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

Давайте посмотрим на следующий пример. У нас есть основная таблица со столбцом Артикул (новый), куда вы хотите добавить столбец с соответствующими ценами из другой таблицы. Кроме того, у нас есть 2 таблицы поиска. Первый (таблица поиска 1) содержит обновленные номера. Артикул (новый) и названия продуктов, а второй (Справочная таблица 2) – названия продуктов и старые номера. Артикул (старый).

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

1. Напишите функцию ВПР, который находит название продукта в таблице Таблица поиска 1через SKU, как желаемое значение:

   =VLOOKUP(A2,New_SKU,2,FALSE)

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

   Здесь Новый_SKU – именованный диапазон $А:$Б в таблице Таблица поиска 1, 2 – это столбец Б, в котором указаны названия товаров (см. картинку выше)

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

   =VLOOKUP(VLOOKUP(A2,New_SKU,2,FALSE),Price,3,FALSE)

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

   Здесь Цена – именованный диапазон $ A: $ C в таблице Таблица поиска 2, 3 столбец C содержит цены.

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

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

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

Бывают ситуации, когда имеется несколько листов с данными одного формата, и необходимо извлечь необходимую информацию из определенного листа в зависимости от значения, которое введено в данную ячейку. Думаю, проще это объяснить на примере.

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

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

=VLOOKUP($D$2,IF($D3="FL",FL_Sales,CA_Sales),2,FALSE)

=ВПР($D$2;ЕСЛИ($D3="FL";FL_Sales;CA_Sales);2;ЛОЖЬ)

Где:

• 2 доллара США — ячейка, содержащая название продукта. Обратите внимание: здесь мы используем абсолютные ссылки, чтобы избежать изменения искомого значения при копировании формулы в другие ячейки.
• $ D3 — это ячейка с названием региона. Мы используем абсолютную ссылку на столбец и относительную ссылку на строку, поскольку планируем скопировать формулу в другие ячейки того же столбца.
• FL_Sales и CA_Sales – названия таблиц (или именованных диапазонов), содержащих соответствующие отчеты о продажах. Можно, конечно, использовать обычные имена листов и ссылки на диапазон ячеек, например «Лист FL»!$A$3:$B$10, но именованные диапазоны гораздо удобнее.

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

Как вы, наверное, знаете, функция КОСВЕННЫЕ используется для возврата ссылки, заданной текстовой строкой, и это именно то, что нам сейчас нужно. Итак, смело замените в приведенной выше формуле выражение функцией IF связать с функцией КОСВЕННЫЕ. Вот комбинация ВПР и КОСВЕННЫЕ отлично работает с:

=VLOOKUP($D$2,INDIRECT($D3&"_Sales"),2,FALSE)

=ВПР($D$2;ДВССЫЛ($D3&"_Sales");2;ЛОЖЬ)

Где:

• 2 доллара США – это ячейка с названием товара, она не меняется за счет абсолютной ссылки.
• $ D3 — ячейка, содержащая первую часть названия региона. В нашем примере это FL.
• _Продажи – общая часть имени всех именованных диапазонов или таблиц. В сочетании со значением в ячейке D3 оно формирует полное имя требуемого диапазона. Ниже приведены некоторые подробности для тех, кто впервые знаком с этой функцией. КОСВЕННЫЕ.

Как работают INDIRECT и VLOOKUP

Для начала напомню синтаксис функции КОСВЕННЫЕ (КОСВЕННО):

INDIRECT(ref_text,[a1])

ДВССЫЛ(ссылка_на_текст;[a1])

Первым аргументом может быть ссылка на ячейку (стиль A1 или R1C1), имя диапазона или текстовая строка. Второй аргумент определяет, какой стиль ссылки содержится в первом аргументе:

• A1если аргумент ИСТИННЫЙ КОД (ИСТИНА) или не указано;
• R1C1, Если FКАК Е (ЛОЖЬ).

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

Итак, вернемся к нашим отчетам о продажах. Если вы помните, то каждый отчет представляет собой отдельную таблицу, расположенную на отдельном листе. Чтобы формула работала корректно, вы должны дать своим таблицам (или диапазонам) имена, причем все имена должны иметь общую часть. Например, вот так: CA_Sales, FL_Продажи, TX_Sales и так далее. Как видите, «_Sales» присутствует во всех именах.

Функция КОСВЕННЫЕ соединяет значение в столбце D и текстовую строку «_Sales», тем самым сообщая ВПР в какой таблице искать. Если ячейка D3 содержит значение «FL», формула выполнит поиск по таблице. FL_Продажи, если «CA» – в таблице CA_Sales и так далее.

Результат функций ВПР и КОСВЕННЫЕ будет следующее:

Если данные находятся в разных книгах Excel, то перед именованным диапазоном необходимо добавить название книги, например:

=VLOOKUP($D$2,INDIRECT($D3&"Workbook1!_Sales"),2,FALSE)

=ВПР($D$2;ДВССЫЛ($D3&"Workbook1!_Sales");2;ЛОЖЬ)

Если функция КОСВЕННЫЕ ссылается на другую книгу, эта книга должна быть открыта. Если он закрыт, функция сообщит об ошибке. #REF! (#ССИЛ!).