ВПР по столбцам с несколькими критериями с использованием вспомогательного метода и функции ВЫБОР

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

Во-первых, давайте посмотрим на общую формулу:

Формула:

=VLOOKUP(v1&v2,dataset_part,column,0)

Здесь,

• Здесь v1 и v2 обозначают те две ячейки, которые будут использоваться для поиска в комбинации.
• Dataset_part обозначает начальное значение ячейки и конечное значение ячейки, по которым должен выполняться поиск. Это может также состоять из всего набора данных.
• Столбец указывает, из какого столбца должно быть взято значение.
• Здесь 0 означает аппроксимацию поиска в vlookup. Его можно установить равным 1, чтобы включить аппроксимацию.

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

1. Вспомогательная колонка.
2. ВЫБЕРИТЕ функцию.

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

Теперь давайте посмотрим на пример в обоих направлениях.

Использование вспомогательного столбца:

Теперь предположим, что у нас есть этот набор данных. С левой стороны записывается основной набор данных, а с правой стороны запись должна быть извлечена из основной базы данных. Теперь вы можете либо использовать функцию CONCATENATE, либо вручную записать объединенные значения ваших столбцов критериев во вспомогательный столбец. Здесь мы хотим узнать область изучения РИЗВАНА АХМАДА. Но мы не знаем никакого другого метода, кроме ВПР, для выполнения этого. Итак, для этого мы будем использовать функцию ВПР. Во-первых, давайте посмотрим на формулу и разберем ее аргумент за аргументом.

=VLOOKUP(H1&H2,A2:D5,4,0)

Результатом этого будет:

Итак, мы добились правильного результата, но как? Давайте посмотрим на это.

• H1 и H2: Имя разделено на 2 ячейки, которые были взяты из ячеек критериев.
• A2:D5: Данные берутся из этого диапазона.
• 4: Это номер столбца, из которого будут извлекаться данные, т.е. из столбца AREA.
• 0: Это означает, что аппроксимация поиска отключена.

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

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

Использование функции ВЫБОР:

Теперь предположим, что данный набор данных большой, и мы хотим узнать область изучения РИЗВАНА АХМАДА, поэтому для этого мы будем использовать функцию ВЫБОР, которая поможет нам создать 2D-массив, в котором значения критериев поиска и другие аргументы будут храниться и использоваться.

Учитывая этот набор данных, формула будет:

=VLOOKUP(H1&&H2,CHOOSE({1,2},B2:B5&&C2:C5,D2:D5),2,0)

Вставив эту формулу в H3, мы получим тот же результат, что и выше. Теперь, как эта формула работала? Давайте рассмотрим аргумент за аргументом.

• H1&&H2: Это будет необходимое значение для поиска. Поскольку мы не использовали вспомогательный столбец, мы должны использовать конкатенированный результат. Итак, мы использовали «&», чтобы соединить или объединить имя.
• {1,2}: это создаст 2D-массив, в котором будут храниться конкатенированные имена, и столбец AREA, из которого будет извлекаться значение.
• B2:B5&&C2:C5: Это объединит два столбца имени и сохранит их в первом столбце 2D-массива, созданного функцией ВЫБОР.
• D2:D5: Это поместит значения столбца AREA во второй столбец 2D-массива, созданного функцией ВЫБОР.
• 2: это число означает, какой столбец 2D-массива должен использоваться для получения значения, поэтому в нашем случае это второй столбец (ОБЛАСТЬ).
• 0: Это означает, что аппроксимация поиска отключена.

Примечание. Если вы хотите разделить конкатенацию, вы можете использовать любой символ, пробел внутри «» (двойные кавычки). Обратите внимание, что он должен быть помещен между значениями &&, которые используются для слияния. Так, например, при объединении H1 и H2, но с некоторым разделением пробелом, мы можем записать это как->H1&», «&H2.

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