Tárgymutató egyezik több kritériumsorral és oszloppal
Mindannyian nap mint nap használjuk a VLOOKUP-ot az adatok beolvasásához, és tisztában vagyunk azzal is, hogy a VLOOKUP balról jobbra tudja beolvasni az adatokat, ezért a keresési értéknek mindig az eredményoszlopok bal oldalán kell lennie. Számos alternatívánk van azonban, amelyek a VLOOKUP függvény alternatívájaként használhatók az excelben. Fejlett technológiával ezeket az INDEX + MATCH képletet használhatjuk a sorok és oszlopok több kritériumának való megfeleléshez. Tehát ez a speciális cikk részletesen bemutatja ezt a technikát.
Hogyan lehet az INDEX + MATCH képletet több kritériumnak megfelelni?
Itt elmagyarázzuk, hogyan használhatjuk az index + egyezési képletet a sorok és oszlopok több kritériumának megfelelő példákkal.
1. példa - INDEX + MATCH képlet
Az excel felhasználók keresési funkcióinak többsége nem túl a VLOOKUP-on, ennek oka sok lehet. Mindenesetre legyen egy egyszerű bevezetés ehhez a képlethez, mielőtt az emelt szintre lépnénk.
Nézze meg például az alábbi adatszerkezetet az excelben.
Rendelkezünk „Sales Rep” nevekkel és a hozzájuk tartozó értékesítési értékekkel. Másrészt a D2 cellában van egy legördülő lista az „Sale Rep” listáról.
A legördülő listából történő kiválasztás alapján az értékesítési összegnek meg kell jelennie az E2 cellában.
A probléma az, hogy nem alkalmazhatjuk a VLOOKUP képletet, mert az „Értékesítési képviselő” keresési érték az „Értékesítés” eredményoszlop jobb oldalán található, így ezekben az esetekben használhatjuk az INDEX + MATCH kombinált keresési érték képletet.
Az INDEX megkeresi az említett sorszámértéket az A2: A11 tartományban, és ebben a tartományban meg kell adnunk, hogy melyik sorból kell az értékesítési érték származnia. Ez a sorérték az excel legördülő listáján kiválasztott „Sales Rep” néven alapul, ezért a MATCH függvény megkeresi az „Sales Rep” sorszámot a B2: B11 tartományban, és visszaadja az egyező érték sorszámát. .
2. példa - Több kritérium az INDEX + MATCH képletben
Most olyan adatstruktúránk van, mint az alábbiakban.
Havi értékesítési értékeink vannak: „Sales Rep.” Ebből a táblázatból olyan dinamikus eredményekre van szükségünk, mint az A15 cellában, létrehoztam egy „Sales Rep” legördülő listát, a B14 cellában pedig egy „Month” legördülő listát.
A két cellában végzett kiválasztás alapján képletünknek le kell szednie az adatokat a fenti táblázatból.
Például, ha a „Rep 8” és az „Apr” lehetőséget választom, akkor annak a „Rep 8” eladási értékét kell megmutatnia az „ápr.” Hónapra.
Tehát ezekben az esetekben meg kell egyeznünk a sorokkal és az oszlopokkal egyaránt. Kövesse az alábbi lépéseket a képletnek a sorokhoz és az oszlopokhoz való illesztéséhez.
1. lépés: Nyissa meg az INDEX funkciót a B15 cellában.
2. lépés: Az INDEX függvény első argumentuma a „Array”, azaz melyik cellatartományból van szükségünk az eredményre. Tehát ebben az esetben értékesítési értékekre van szükségünk, ezért válassza ki a cellák tartományát a B2-től a G11-ig.
3. lépés: Az INDEX függvény következő argumentuma a kiválasztott tartomány melyik sorából van szükségünk az eredményre. Ebben az esetben el kell érnünk az „Értékesítési képviselő” sorszámot az A15 cella legördülő cellában végzett kiválasztás alapján. Tehát a sorszám dinamikus lekéréséhez a kiválasztás alapján nyissa meg a MATCH függvényt.
4. lépés: A MATCH függvény keresési értéke „Sales Rep”, ezért válassza az A15 cellát referenciaként.
5. lépés: A Keresési tömb az „Értékesítési képviselő” névtartomány lesz a fő táblázatban. Tehát válassza az A2 és A11 közötti tartományt.
6. lépés: A MATCH függvény egyezési típusa pontos lesz, ezért írja be az argumentum értékét nulla.
7. lépés: Az INDEX függvény következő argumentuma az „Oszlop száma”, azaz a kiválasztott cellatartományból, amely oszlopból az eredményre van szükségünk. Ez attól a hónaptól függ, amelyet kiválasztunk a B14 cella legördülő listájából. Tehát az oszlop számának automatikus megszerzéséhez nyisson meg egy másik MATCH funkciót.
8. lépés: Ez az időbeli keresési érték a hónap neve lesz, ezért válassza a B14 cellát referenciaként.
9. lépés: A keresési tömb a főtábla cellatartományának hónapja lesz, azaz a B1-től a G1-ig.
10. lépés: Az utolsó argumentum egyezési típus; válassza az „Pontos egyezés” szempontot. Csukjon be két zárójelet és nyomja meg az Enter billentyűt az eredmény eléréséhez.
Mint fent láthatjuk, a „Rep 6” és az „Apr” hónapot választottuk, és képletünk visszaadta az „Apr 6” eladási értékét a „Rep 6” esetében.
Megjegyzés: A sárga cella a referencia az Ön számára.
Dolgok, amikre emlékezni kell
- Az INDEX + MATCH kombinációja erősebb lehet, mint a VLOOKUP formula.
- Az INDEX & MATCH egyezhet a sorok és az oszlopok fejlécével, és az eredményt a középső táblázatból adhatja vissza.
- A MATCH visszaadhatja mindkét sor és oszlop táblázatfejlécének sorszámát és oszlopszámát.
