Videó megtekintése – A VLOOKUP funkció használata több kritériummal

Az Excel VLOOKUP függvény alap formájában egy keresési értéket kereshet, és visszaadhatja a megfelelő értéket a megadott sorból.

De gyakran szükség van az Excel VLOOKUP használatára több kritériummal.

A VLOOKUP használata több kritériummal

Tegyük fel, hogy vannak olyan adatai, amelyek tartalmazzák a hallgatók nevét, vizsga típusát és a matematikai pontszámot (az alábbiak szerint):

Kihívást jelenthet a VLOOKUP funkció használata az egyes diákok matematikai pontszámának megszerzéséhez az adott vizsga szintjén.

Lehet vitatkozni hogy jobb megoldás az adatkészlet átalakítása vagy egy kimutató tábla használata. Ha ez neked megfelel, semmi ilyesmi. Sok esetben azonban ragaszkodik a rendelkezésére álló adatokhoz, és az elfordulási táblázat nem biztos, hogy opció.

Ilyen esetekben ez az oktatóanyag az Ön számára készült.

Most itt vannak: kétféle módon kaphatja meg a keresési értéket a VLOOKUP használatával, több kritériummal.

• Segítő oszlop használata.
• A VÁLASZTÁS funkció használata.

VLOOKUP több kritériummal – Segítő oszlop használata

Rajongok a segítő oszlopokért az Excelben.

A segítő oszlopok használatának két jelentős előnyét találom a tömbképletekkel szemben:

• Könnyen megértheti, mi történik a munkalapon.
• Ez gyorsabbá teszi a tömbfüggvényekhez képest (észrevehető a nagy adathalmazokban).

Most ne érts félre. Nem vagyok a tömbképletek ellen. Szeretem azokat a csodálatos dolgokat, amelyeket tömbképletekkel el lehet végezni. Csak azokat a különleges alkalmakra mentem el, amikor az összes többi 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ő felhasználható a helyes érték kikeresésére. Például három Matt szerepel az adatokban, de csak egy Matt és Unit Test, illetve Matt és Mid-Term kombináció létezik.

Íme a következő lépések:

Hogyan működik ez?

Egyéni minősítőket hozunk létre a név és a vizsga minden példányához. Az itt használt VLOOKUP függvényben a keresési érték $ F3-ra módosult & ”|” & G $ 2, így mindkét keresési feltétel kombinálva vannak, és egyetlen keresési értékként használják őket. 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álják a C2: D19 pontszámának megszerzéséhez.

Pontosítások:

Van néhány kérdés, amely valószínűleg eszedbe jut, ezért gondoltam, hogy itt megpróbálja megválaszolni:

• Miért használtam | szimbólum, miközben csatlakozik a két kritériumhoz? – Egyes kivételesen ritka (de lehetséges) körülmények között két különböző kritérium állhat rendelkezésedre, de végül ugyanazt az eredményt adod együtt. Íme egy nagyon egyszerű példa (bocsáss meg nekem itt a kreativitás hiányáért):

Vegye figyelembe, hogy míg A2 és A3 különböznek egymástól, B2 és B3 különböznek, a kombinációk végül ugyanazok. De ha elválasztót használ, akkor még a kombináció is különbözne (D2 és D3).

• Miért illesztettem a segédoszlopot a B és C oszlop közé, és nem a bal szélsőbe? – Nem árt behelyezni a segédoszlopot a bal szélsőbe. Valójában, ha nem akarja az eredeti adatokat mérsékelni, akkor ennek kell lennie. Megtettem, mivel emiatt kevesebb cellát használok a VLOOKUP függvényben. Ahelyett, hogy 4 oszlop lenne a táblázat tömbben, csak 2 oszloppal tudnék kezelni. De ez csak én vagyok.

Most nincs egyetlen méret, amely mindenkinek megfelel. Néhány ember inkább nem használ semmilyen segítő oszlopot, miközben a VLOOKUP-ot használja több kritériummal.

Tehát itt van a nem segítő oszlop-módszer az Ön számára.

Töltse le a példafájlt

VLOOKUP több kritériummal – A VÁLASZTÁS funkció használata

Tömbök képleteinek használata segítő helyett az oszlopok mentik a munkalap ingatlanjait, és a teljesítmény ugyanolyan jó lehet, ha kevesebbször használjuk egy munkafüzetben.

Figyelembe véve a fentiekkel megegyező adatsort, az alábbi képlet adja meg az eredményt :

= VLOOKUP ($ E3 & “|” & 2 USD, KIVÁLASZTÁS ({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óval az Enter helyett.

Hogyan működik ez?

A képlet a helper oszlop fogalmát is használja. A különbség az, hogy ahelyett, hogy a munkalapba helyezné a segítő oszlopot, tekintse virtuális segítő adatoknak, amelyek a képlet részét képezik.

Hadd mutassam meg, mit értek virtuális segítő adatok alatt.

A fenti ábrán, amikor kiválasztom a képlet KIVÁLASZTÁS részét és megnyomom az F9 billentyűt, az azt mutatja, hogy az A CHOOSE képlet adna.

Az eredmény: “” Matt | Unit Test “, 91;” Bob | Unit Test “, 52; ……}

Ez egy tömb, ahol vessző az ugyanazon sor következő celláját jelöli, a pontosvessző pedig azt, 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 rendelkezik, a másik pedig a pontszámmal.

Most , amikor a VLOOKUP függvényt használja, egyszerűen megkeresi az értéket az első oszlopban (ennek a virtuális 2 oszlopnak az adataiból), és visszaadja a megfelelő pontszámot.

Töltse le a Példa fájl

Más képletekkel több feltételes keresést is végezhet (például INDEX / MATCH vagy SUMPRODUCT).