Pozrite si video - Ako používať funkciu VLOOKUP s viacerými kritériami
Funkcia Excel VLOOKUP vo svojej základnej forme dokáže vyhľadať jednu vyhľadávaciu hodnotu a vrátiť zodpovedajúcu hodnotu zo zadaného riadka.
Často je však potrebné použiť VLOOKUP programu Excel s viacerými kritériami.
Ako používať VLOOKUP s viacerými kritériami
Predpokladajme, že máte údaje s menom študentov, typom skúšky a skóre z matematiky (ako je uvedené nižšie):
Použitie funkcie VLOOKUP na získanie matematického skóre pre každého študenta pre príslušné úrovne skúšok môže byť výzvou.
Niekto môže tvrdiť, že lepšou možnosťou by bola reštrukturalizácia súboru údajov alebo použitie kontingenčnej tabuľky. Ak vám to funguje, nič také. V mnohých prípadoch však uviaznete na údajoch, ktoré máte, a kontingenčná tabuľka nemusí byť možnosťou.
V takýchto prípadoch je tento návod pre vás.
Teraz existujú dva spôsoby, ako môžete získať hodnotu vyhľadávania pomocou funkcie VLOOKUP s viacerými kritériami.
- Použitie pomocného stĺpca.
- Použitie funkcie CHOOSE.
VLOOKUP s viacerými kritériami - pomocou stĺpca pomocníka
Som fanúšikom pomocných stĺpcov v programe Excel.
Zistil som dve významné výhody použitia pomocných stĺpcov oproti vzorcom poľa:
- Umožňuje ľahko porozumieť tomu, čo sa deje v pracovnom hárku.
- Je to rýchlejšie v porovnaní s funkciami poľa (viditeľné vo veľkých množinách údajov).
Teraz ma nechápte zle. Nie som proti vzorcom poľa. Milujem úžasné veci, ktoré sa dajú robiť pomocou vzorcov poľa. Jednoducho ich ukladám na špeciálne príležitosti, keď všetky ostatné možnosti nepomáhajú.
Ak sa vrátime k položenej otázke, pomocný stĺpec je potrebný na vytvorenie jedinečného kvalifikátora. Tento jedinečný kvalifikátor potom možno použiť na vyhľadanie správnej hodnoty. V údajoch sú napríklad traja Matt, ale existuje iba jedna kombinácia Matt a Unit Test alebo Matt a Mid-Term.
Tu sú kroky:
- Vložiť pomocný stĺpček medzi stĺpce B a C.
- V stĺpci pomocníka použite nasledujúci vzorec: = A2 & ”|” & B2
- To by vytvorilo jedinečné kvalifikátory pre každú inštanciu, ako je uvedené nižšie.
- To by vytvorilo jedinečné kvalifikátory pre každú inštanciu, ako je uvedené nižšie.
- V G3 = VLOOKUP použite nasledujúci vzorec ($ F3 & ”|” & G $ 2, $ C $ 2: $ D $ 19,2,0)
- Kopírovať pre všetky bunky.
Ako to funguje?
Vytvárame jedinečné kvalifikátory pre každú inštanciu mena a skúšky. Vo funkcii VLOOKUP, ktorá sa tu používa, bola hodnota vyhľadávania upravená na $ F3 & ”|” & G $ 2, aby boli obe kritériá vyhľadávania kombinované a boli použité ako jedna hodnota vyhľadávania. Napríklad vyhľadávacia hodnota pre funkciu VLOOKUP v G2 je Matt | Unit Test. Teraz táto vyhľadávacia hodnota slúži na získanie skóre z C2: D19.
Objasnenie:
Pravdepodobne vám napadne niekoľko otázok, a tak som si povedal, že sa pokúsim na ne odpovedať tu:
- Prečo som použil | symbol pri spájaní týchto dvoch kritérií? - V niektorých výnimočne zriedkavých (ale možných) podmienkach môžete mať dve kritériá, ktoré sú odlišné, ale v konečnom dôsledku poskytujú pri kombinácii rovnaký výsledok. Tu je veľmi jednoduchý príklad (odpustite mi tu moju kreativitu):
Všimnite si toho, že zatiaľ čo A2 a A3 sú odlišné a B2 a B3 sú odlišné, kombinácie sú nakoniec rovnaké. Ak však použijete oddeľovač, potom aj kombinácia bude odlišná (D2 a D3).
- Prečo som vložil pomocný stĺpec medzi stĺpce B a C a nie úplne doľava? - Vložiť pomocný stĺpik úplne doľava nie je na škodu. V skutočnosti, ak nechcete temperovať pôvodné údaje, mala by to byť správna cesta. Urobil som to, pretože ma núti používať menší počet buniek vo funkcii VLOOKUP. Namiesto toho, aby som mal v poli tabuľky 4 stĺpce, dokázal som si vystačiť iba s 2 stĺpcami. Ale to som len ja.
Teraz neexistuje žiadna univerzálna veľkosť, ktorá by vyhovovala všetkým. Niektorí ľudia môžu pri použití funkcie VLOOKUP s viacerými kritériami radšej nepoužívať žiadny pomocný stĺpec.
Tu je teda pre vás metóda stĺpca, ktorá nie je pomocníkom.
Stiahnite si ukážkový súbor
VLOOKUP s viacerými kritériami - pomocou funkcie CHOOSE
Použitím vzorcov poľa namiesto pomocných stĺpcov ušetríte nehnuteľnosť v hárku a výkon môže byť rovnako dobrý, ak sa v zošite použije menej krát.
Vzhľadom na rovnaký súbor údajov, ako bol použitý vyššie, tu je vzorec, ktorý vám poskytne výsledok:
= VLOOKUP ($ E3 & ”|” & F $ 2, VYBERTE ({1,2}, $ A $ 2: $ A $ 19 & ”|” & $ B $ 2: $ B $ 19, $ C $ 2: $ C $ 19), 2, 0)
Pretože toto je maticový vzorec, použite ho namiesto Ctrl iba pomocou klávesov Ctrl + Shift + Enter.
Ako to funguje?
Vzorec tiež používa koncept pomocného stĺpca. Rozdiel je v tom, že namiesto vloženia pomocného stĺpca do pracovného hárka ho považujte za údaje virtuálneho pomocníka, ktoré sú súčasťou vzorca.
Ukážem vám, čo myslím údajmi virtuálnych pomocníkov.
Na obrázku vyššie, keď vyberiem časť ZVOLIŤ vzorec a stlačím kláves F9, zobrazí sa výsledok, ktorý by poskytol vzorec ZVOLIŤ.
Výsledkom je {„Matt | Unit Test“, 91; „Bob | Unit Test“, 52; …}
Je to pole, kde čiarka predstavuje ďalšiu bunku v rovnakom riadku a bodkočiarka znamená, že nasledujúce údaje sú v nasledujúcom stĺpci. Tento vzorec teda vytvára 2 stĺpce údajov - jeden stĺpec má jedinečný identifikátor a jeden má skóre.
Keď teraz použijete funkciu VLOOKUP, jednoducho vyhľadá hodnotu v prvom stĺpci (týchto virtuálnych 2 stĺpcových údajov) a vráti zodpovedajúce skóre.
Stiahnite si ukážkový súbor
Na vyhľadanie podľa viacerých kritérií môžete použiť aj iné vzorce (napríklad INDEX/MATCH alebo SUMPRODUCT).
Viete o tom aj iným spôsobom? Ak áno, podeľte sa so mnou v sekcii komentárov.
Tiež by sa vám mohli páčiť nasledujúce návody na LOOKUP:
- VLOOKUP vs. INDEX/ZHODA
- Získajte viac hodnôt vyhľadávania bez opakovania v jednej bunke.
- Ako zabezpečiť, aby VLOOKUP rozlišoval malé a veľké písmená.
- Použite IFERROR s VLOOKUP a zbavte sa #N/A chýb.
