Videó megtekintése - A VLOOKUP funkció használata több kritériummal
Az Excel VLOOKUP függvény alapvető formájában megkereshet egy keresési értéket, és visszaadhatja a megfelelő értéket a megadott sorból.
De gyakran szükség van az Excel VLOOKUP használatára több feltétellel.
A VLOOKUP használata több kritériummal
Tegyük fel, hogy rendelkezik adatokkal a diákok nevével, vizsgatípusával és matematikai pontszámával (az alábbiak szerint):
A VLOOKUP funkció használata kihívást jelenthet az egyes diákok matematikai pontszámának megszerzéséhez a megfelelő vizsgaszinteken.
Lehet vitatkozni azzal, hogy jobb megoldás lenne az adathalmaz átstrukturálása vagy a Pivot tábla használata. Ha ez neked működik, semmi ilyesmi. De sok esetben elakad a rendelkezésre álló adatokkal, és előfordulhat, hogy a pivot tábla nem választható.
Ilyen esetekben ez az oktatóanyag az Ön számára.
Most kétféle módon szerezheti be a keresési értéket a VLOOKUP használatával, több feltétellel.
- Segítő oszlop használata.
- A CHOOSE funkció használata.
VLOOKUP több kritériummal - Segítő oszlop használata
Rajongója vagyok az Excel segédoszlopainak.
A segítő oszlopok használatának két jelentős előnyét találom a tömbképletekkel szemben:
- Könnyen megérthető, hogy mi történik a munkalapon.
- Gyorsabbá teszi a tömbfunkciókhoz képest (nagy adathalmazokban észrevehető).
Most ne érts félre. Nem vagyok a tömbképletek ellen. Szeretem azt a csodálatos dolgot, amit tömbképletekkel lehet csinálni. Csak különleges alkalmakra mentem őket, amikor minden más lehetőség nem segít.
Visszatérve a kérdésre, a segítő oszlopra szükség van egy egyedi minősítő létrehozásához. Ez az egyedi minősítő használható a megfelelő érték megkeresésére. Például három Matt van az adatokban, de csak egy kombinációja van Matt és Unit Test vagy Matt és Mid-Term.
Íme a lépések:
- Helyezzen be egy segítő oszlopot a B és C oszlop közé.
- Használja a következő képletet a segítő oszlopban: = A2 & ”|” és B2
- Ez egyedi minősítéseket hozna létre minden példányhoz, amint az alább látható.
- Ez egyedi minősítéseket hozna létre minden példányhoz, amint az alább látható.
- Használja a következő képletet G3 = VLOOKUP ($ F3 & ”|” & G $ 2, $ C $ 2: $ D $ 19,2,0)
- Másolás az összes cellához.
Hogy működik ez?
A név és a vizsga minden egyes példányához egyedi minősítőket hozunk létre. Az itt használt VLOOKUP függvényben a keresési érték $ F3 & ”|” & G $ 2 értékre módosult, így mindkét keresési feltétel kombinálva van, és egyetlen keresési értékként használható. Például a G2 VLOOKUP függvényének keresési értéke Matt | Unit Test. Most ezt a keresési értéket használjuk a C2: D19 pontszámának lekérésére.
Tisztázások:
Valószínűleg néhány kérdés jut eszedbe, ezért gondoltam megpróbálok válaszolni itt:
- Miért használtam | szimbólum a két kritérium összekapcsolásakor? - Bizonyos kivételesen ritka (de lehetséges) körülmények között két különböző kritériuma lehet, de kombinálva ugyanazt az eredményt adja. Íme egy nagyon egyszerű példa (bocsásson meg nekem a kreativitás hiánya miatt):
Vegye figyelembe, hogy míg az A2 és A3 különböző, a B2 és a B3 pedig különböző, a kombinációk végül ugyanazok. De ha elválasztót használ, akkor még a kombináció is más lenne (D2 és D3).
- Miért illesztettem be a segítő oszlopot a B és C oszlop közé, és nem a szélsőbalba? - Nem árt behelyezni a segítő oszlopot a szélső balra. Valójában, ha nem akarja az indulatokat az eredeti adatokkal, akkor ennek kell lennie. Megtettem, mivel kevesebb cellát használok a VLOOKUP funkcióban. Ahelyett, hogy a táblázat tömbjében 4 oszlop lenne, csak 2 oszloppal tudnám kezelni. De ez csak én vagyok.
Most nincs egy méret, amely mindenkinek megfelelne. Vannak, akik inkább nem kívánnak segítő oszlopot használni a VLOOKUP több feltétellel történő használata közben.
Tehát itt van a nem segítő oszlopos módszer az Ön számára.
Töltse le a példa fájlt
VLOOKUP több kritériummal - A CHOOSE funkció használata
Ha a segédoszlopok helyett tömbképleteket használ, akkor munkalap -ingatlanokat takarít meg, és a teljesítmény ugyanolyan jó lehet, ha kevesebb alkalommal használják a munkafüzetben.
Tekintettel a fentiekben használt adatkészletre, itt van a képlet, amely megadja az eredményt:
= VLOOKUP ($ E3 & ”|” & F $ 2, CHOOSE ({1,2}, $ A $ 2: $ A $ 19 & ”|” & $ B $ 2: $ B $ 19, $ C $ 2: $ C $ 19), 2, 0)
Mivel ez egy tömbképlet, használja a Control + Shift + Enter billentyűkombinációt az Enter helyett.
Hogy működik ez?
A képlet a segítő oszlop fogalmát is használja. A különbség az, hogy ahelyett, hogy a segítő oszlopot a munkalapra tenné, tekintse azt a képlet részét képező virtuális segítő adatnak.
Hadd mutassam meg, mit értek virtuális segítő adatok alatt.
A fenti illusztrációban, amikor kiválasztom a képlet CHOOSE részét, és megnyomom az F9 billentyűt, az azt az eredményt mutatja, amelyet a CHOOSE képlet adna.
Az eredmény: {„Matt | Unit Test”, 91; „Bob | Unit Test”, 52;…}
Ez egy tömb, ahol a vessző ugyanazon sor következő celláját, a pontosvessző pedig azt jelenti, hogy a következő adatok a következő oszlopban vannak. Ezért ez a képlet 2 adatoszlopot hoz létre - az egyik oszlop egyedi azonosítóval, a másik pedig a pontszámmal rendelkezik.
Most, amikor a VLOOKUP függvényt használja, egyszerűen megkeresi az értéket (ennek a virtuális 2 oszlopos adatnak) az első oszlopában, és visszaadja a megfelelő pontszámot.
Töltse le a példa fájlt
Más képletek segítségével is végezhet több feltételt tartalmazó keresést (például INDEX/MATCH vagy SUMPRODUCT).
Van más módszer, amellyel ezt tudja? Ha igen, ossza meg velem a megjegyzések részben.
A következő LOOKUP oktatóanyagok is tetszhetnek:
- VLOOKUP vs. INDEX/MATCH
- Több keresési értéket kaphat ismétlés nélkül egyetlen cellában.
- Hogyan lehet a VLOOKUP kis- és nagybetűket érzékenyíteni?
- Használja az IFERROR -t a VLOOKUP funkcióval, hogy megszabaduljon a #N/A hibáktól.
