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

Содержание

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

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

Функция Excel VLOOKUP в своей базовой форме может искать одно значение поиска и возвращать соответствующее значение из указанной строки.

Но часто возникает необходимость использовать ВПР в Excel с несколькими критериями.

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

Предположим, у вас есть данные с именами учащихся, типом экзамена и оценкой по математике (как показано ниже):

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

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

В таких случаях это руководство для вас.

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

Использование вспомогательного столбца.
Используя функцию ВЫБРАТЬ.

ВПР с несколькими критериями - Использование вспомогательного столбца

Я поклонник вспомогательных столбцов в Excel.

Я обнаружил два существенных преимущества использования вспомогательных столбцов по сравнению с формулами массива:

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

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

Возвращаясь к рассматриваемому вопросу, вспомогательный столбец необходим для создания уникального квалификатора. Затем этот уникальный квалификатор можно использовать для поиска правильного значения. Например, в данных есть три Matt, но есть только одна комбинация Matt и Unit Test или Matt и Mid-Term.

Вот шаги:

Вставьте вспомогательный столбец между столбцами B и C.
Используйте следующую формулу во вспомогательном столбце: = A2 & ”|” & B2
- Это создаст уникальные квалификаторы для каждого экземпляра, как показано ниже.
Используйте следующую формулу в G3 = ВПР ($ F3 & ”|” & G $ 2, $ C $ 2: $ D $ 19,2,0)
Скопируйте для всех ячеек.

Как это работает?

Мы создаем уникальные квалификаторы для каждого экземпляра имени и экзамена. В используемой здесь функции VLOOKUP значение поиска было изменено на $ F3 & ”|” & G $ 2, так что оба критерия поиска объединены и используются как одно значение поиска. Например, значение поиска для функции ВПР в G2 - Matt | Unit Test. Теперь это значение поиска используется для получения оценки от C2: D19.

Разъяснения:

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

Почему я использовал | символ при объединении двух критериев? - В некоторых исключительно редких (но возможных) условиях у вас могут быть два разных критерия, но в конечном итоге они дают одинаковый результат при объединении. Вот очень простой пример (простите меня за недостаток творчества):

Обратите внимание, что хотя A2 и A3 разные, а B2 и B3 разные, комбинации в конечном итоге остаются одинаковыми. Но если использовать разделитель, то даже комбинация будет другой (D2 и D3).

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

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

Итак, вот вам метод не вспомогательного столбца.

Загрузите файл примера

ВПР с несколькими критериями - Использование функции ВЫБРАТЬ

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

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

= ВПР ($ E3 & ”|” & F $ 2, ВЫБРАТЬ ({1,2}, $ A $ 2: $ A $ 19 & ”|” & $ B $ 2: $ B $ 19, $ C $ 2: $ C $ 19), 2, 0)

Поскольку это формула массива, используйте ее с Control + Shift + Enter, а не просто Enter.

Как это работает?

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

Позвольте мне показать вам, что я имею в виду под виртуальными вспомогательными данными.

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

Результат: {"Matt | Unit Test", 91; "Bob | Unit Test", 52;…}

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

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

Загрузите файл примера

Вы также можете использовать другие формулы для поиска по нескольким критериям (например, INDEX / MATCH или SUMPRODUCT).

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

Вам также могут понравиться следующие руководства LOOKUP: