Како користити ВЛООКУП са више критеријума у ​​Екцелу

Погледајте видео - Како користити функцију ВЛООКУП са више критеријума

Екцел ВЛООКУП функција, у свом основном облику, може тражити једну вредност претраживања и вратити одговарајућу вредност из наведеног реда.

Али често постоји потреба да се Екцел ВЛООКУП користи са више критеријума.

Како користити ВЛООКУП са више критеријума

Претпоставимо да имате податке са именом ученика, врстом испита и резултатом из математике (као што је приказано испод):

Коришћење функције ВЛООКУП за добијање оцене из математике за сваког ученика за одговарајуће испитне нивое могло би бити изазов.

Може се тврдити да би боља опција била реструктурирање скупа података или употреба изведене табеле. Ако вам то одговара, ништа слично. Али у многим случајевима сте заглављени са подацима које имате и изведена табела можда није опција.

У таквим случајевима овај водич је за вас.

Сада постоје два начина на које можете добити вредност претраживања помоћу ВЛООКУП -а са више критеријума.

• Коришћење колоне помоћника.
• Коришћење функције ИЗАБЕРИ.

ВЛООКУП са више критеријума - коришћење колоне помоћника

Љубитељ сам помоћних колона у Екцелу.

Налазим две значајне предности коришћења помоћних колона у односу на формуле низа:

• Олакшава разумевање шта се дешава на радном листу.
• То га чини бржим у поређењу са функцијама низа (приметно у великим скуповима података).

Немојте ме погрешно схватити. Нисам против формула низа. Волим невероватне ствари које се могу урадити помоћу формула низа. Само их чувам за посебне прилике када све остале опције нису од помоћи.

Враћајући се на постављено питање, помоћна колона је потребна за креирање јединственог квалификатора. Овај јединствени квалификатор се затим може користити за тражење тачне вредности. На пример, у подацима постоје три Матта, али постоји само једна комбинација Матт и Унит Тест или Матт и Мид-Терм.

Ево корака:

• Уметните помоћну колону између колона Б и Ц.
• Користите следећу формулу у помоћној колони: = А2 & ”|” & Б2
  • Ово би створило јединствене квалификаторе за сваку инстанцу као што је приказано у наставку.
• Користите следећу формулу у Г3 = ВЛООКУП ($ Ф3 & ”|” & Г $ 2, $ Ц $ 2: $ Д $ 19,2,0)
• Копија за све ћелије.

Како ово ради?

Креирамо јединствене квалификаторе за сваку инстанцу имена и испита. У функцији ВЛООКУП која се овде користи, вредност претраживања је промењена у $ Ф3 & ”|” & Г $ 2, тако да су оба критеријума претраживања комбинована и користе се као једна вредност тражења. На пример, вредност претраживања за функцију ВЛООКУП у Г2 је Матт | Унит Тест. Сада се ова вредност претраживања користи за добијање резултата из Ц2: Д19.

Појашњења:

Постоји неколико питања која ће вам вероватно пасти на памет, па сам мислио да ћу покушати да одговорим овде:

• Зашто сам користио | симбол при придруживању два критеријума? - У неким изузетно ретким (али могућим) условима, можда имате два различита критеријума, али на крају дају исти резултат када се комбинују. Ево врло једноставног примера (опростите ми на недостатку креативности овде):

Имајте на уму да, иако су А2 и А3 различити, а Б2 и Б3 различити, комбинације на крају остају исте. Али ако користите сепаратор, чак би и комбинација била другачија (Д2 и Д3).

• Зашто сам помоћну колону уметнуо између колона Б и Ц, а не крајње лево? - Нема штете ако уметнете помоћну колону крајње лево. У ствари, ако не желите да се помирите са оригиналним подацима, то би требао бити начин. Урадио сам то јер ме тера да користим мањи број ћелија у функцији ВЛООКУП. Уместо да имам 4 колоне у низу табела, могао сам да управљам са само 2 колоне. Али то сам само ја.

Сада не постоји једна величина која одговара свима. Неки људи могу радије да не користе помоћну колону док користе ВЛООКУП са више критеријума.

Дакле, ево методе колоне која није помоћна за вас.

Преузмите датотеку примера

ВЛООКУП са више критеријума - Коришћење функције ИЗАБЕРИ

Употреба формула поља уместо помоћних колона штеди некретнине на радном листу, а перформансе могу бити једнако добре ако се користе мање пута у радној свесци.

Узимајући у обзир исти скуп података који је коришћен горе, ево формуле која ће вам дати резултат:

= ВЛООКУП ($ Е3 & ”|” & Ф $ 2, ИЗАБЕРИТЕ ({1,2}, $ А $ 2: $ А $ 19 & ”|” & $ Б $ 2: $ Б $ 19, $ Ц $ 2: $ Ц $ 19), 2, 0)

Пошто је ово формула низа, користите је са Цонтрол + Схифт + Ентер, уместо само Ентер.

Како ово ради?

Формула такође користи концепт помоћне колоне. Разлика је у томе што уместо да уносите помоћну колону у радни лист, сматрајте је виртуелним помоћним подацима који је део формуле.

Дозволите ми да вам покажем шта мислим под виртуелним помоћним подацима.

На горњој илустрацији, док изаберем део ИЗБОР формуле и притиснем Ф9, она приказује резултат који би дала формула ИЗАБЕРИ.

Резултат је {“Матт | Унит Тест”, 91; “Боб | Унит Тест”, 52;…}

То је низ у коме зарез представља следећу ћелију у истом реду, а тачка -зарез представља да су следећи подаци у следећој колони. Дакле, ова формула ствара 2 колоне података - једна колона има јединствени идентификатор, а једна резултат.

Сада, када користите функцију ВЛООКУП, она једноставно тражи вредност у првој колони (ових података из виртуелне 2 колоне) и враћа одговарајућу оцену.

Преузмите датотеку примера

Такође можете користити друге формуле за претраживање са више критеријума (попут ИНДЕКС/МАТЦХ или СУМПРОДУЦТ).

Постоји ли неки други начин да то учините? Ако да, поделите са мном у одељку за коментаре.

Можда ће вам се допасти и следећи ЛООКУП водичи:

• ВЛООКУП Вс. ИНДЕКС/МАТЦХ
• Добијте више вредности за тражење без понављања у једној ћелији.
• Како ВЛООКУП учинити осетљивим на велика и мала слова
• Користите ИФЕРРОР са ВЛООКУП -ом да бисте се решили #Н/А грешака.