✅ Как да използвате VLOOKUP с множество критерии в Excel

Съдържание

Как да използвате VLOOKUP с множество критерии

Гледайте видео - Как да използвате функцията VLOOKUP с множество критерии

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

Но често има нужда да използвате Excel VLOOKUP с множество критерии.

Как да използвате VLOOKUP с множество критерии

Да предположим, че имате данни с име на ученици, вид на изпита и оценка по математика (както е показано по -долу):

Използването на функцията VLOOKUP за получаване на оценка по математика за всеки ученик за съответните нива на изпит може да бъде предизвикателство.

Може да се твърди, че по -добър вариант би бил преструктурирането на набора от данни или използването на обобщена таблица. Ако това работи за вас, нищо подобно. Но в много случаи сте заседнали с данните, които имате, и осевата таблица може да не е опция.

В такива случаи този урок е за вас.

Сега има два начина, по които можете да получите стойността за търсене с помощта на VLOOKUP с множество критерии.

Използване на помощна колона.
Използване на функцията ИЗБОР.

VLOOKUP с множество критерии - Използване на помощна колона

Аз съм фен на помощните колони в Excel.

Намирам две значителни предимства от използването на помощни колони пред формулите на масива:

Това улеснява разбирането на случващото се в работния лист.
Това го прави по -бърз в сравнение с функциите на масива (забележимо в големи масиви от данни).

Сега, не ме разбирайте погрешно. Не съм против формулите за масиви. Обичам невероятните неща, които могат да бъдат направени с формули за масиви. Просто ги запазвам за специални случаи, когато всички други опции не са от полза.

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

Ето стъпките:

Поставете помощна колона между колони B и C.
Използвайте следната формула в помощната колона: = A2 & ”|” & B2
- Това ще създаде уникални квалификатори за всеки екземпляр, както е показано по -долу.
Използвайте следната формула в G3 = VLOOKUP ($ F3 & ”|” & G $ 2, $ C $ 2: $ D $ 19,2,0)
Копиране за всички клетки.

Как работи това?

Ние създаваме уникални квалификатори за всеки екземпляр от име и изпит. В използваната тук функция VLOOKUP стойността за търсене е променена на $ F3 & ”|” & G $ 2, така че и двата критерия за търсене се комбинират и се използват като единична стойност за търсене. Например стойността за търсене за функцията VLOOKUP в G2 е Matt | Unit Test. Сега тази стойност за търсене се използва за получаване на резултата от C2: D19.

Пояснения:

Има няколко въпроса, които вероятно ще ви дойдат на ум, затова реших да опитам да отговоря тук:

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

Обърнете внимание, че докато A2 и A3 са различни и B2 и B3 са различни, комбинациите в крайна сметка са еднакви. Но ако използвате разделител, дори комбинацията ще бъде различна (D2 и D3).

Защо вмъкнах помощната колона между колони B и C, а не в крайния ляв ъгъл? - Няма вреда при вмъкването на помощната колона крайно вляво. Всъщност, ако не искате да се сдържате с оригиналните данни, това трябва да е начинът. Направих го, тъй като ме кара да използвам по -малък брой клетки във функцията VLOOKUP. Вместо да имам 4 колони в масива на таблици, бих могъл да управлявам само с 2 колони. Но това съм само аз.

Сега няма един размер, който да пасва на всички. Някои хора може да предпочетат да не използват помощна колона, докато използват VLOOKUP с множество критерии.

Така че тук е методът на непомощната колона за вас.

Изтеглете примерния файл

VLOOKUP с множество критерии - Използване на функцията CHOOSE

Използването на формули за масиви вместо помощни колони ви спестява недвижими имоти на работен лист, а производителността може да бъде еднакво добра, ако се използва по -малко пъти в работна книга.

Като се има предвид същият набор от данни, както е използван по -горе, ето формулата, която ще ви даде резултат:

= VLOOKUP ($ E3 & ”|” & F $ 2, ИЗБЕРЕТЕ ({1,2}, $ A $ 2: $ A $ 19 & ”|” & $ B $ 2: $ B $ 19, $ C $ 2: $ C $ 19), 2, 0)

Тъй като това е формула за масив, използвайте я с Control + Shift + Enter, вместо само с Enter.

Как работи това?

Формулата също използва концепцията за помощна колона. Разликата е, че вместо да поставяте помощната колона в работния лист, считайте я за виртуални помощни данни, които са част от формулата.

Нека ви покажа какво имам предвид под виртуални помощни данни.

В горната илюстрация, докато избирам частта CHOOSE от формулата и натискам F9, тя показва резултата, който формулата CHOOSE би дала.

Резултатът е {“Matt | Unit Test”, 91; “Bob | Unit Test”, 52;…}

Това е масив, където запетая представлява следващата клетка в същия ред, а точка и запетая представлява, че следните данни са в следващата колона. Следователно, тази формула създава 2 колони с данни - една колона има уникален идентификатор, а една има резултат.

Сега, когато използвате функцията VLOOKUP, тя просто търси стойността в първата колона (от тези виртуални данни от 2 колони) и връща съответния резултат.

Изтеглете примерния файл

Можете също да използвате други формули, за да извършите търсене с множество критерии (като INDEX/MATCH или SUMPRODUCT).

Има ли друг начин да знаете това? Ако да, споделете с мен в секцията за коментари.

Може да харесате и следните уроци за ПОГЛЕД: