Ez az oktatóanyag megtanítja, hogyan kell használni az INDEX & MATCH kombinációt az Excel és a Google Táblázatok kereséséhez.
INDEX & MATCH, A tökéletes páros
Nézzük meg közelebbről az INDEX és a MATCH funkciók kombinálásának néhány módját. A MATCH függvényt úgy tervezték, hogy visszaadja egy elem relatív pozícióját egy tömbön belül, míg az INDEX függvény lekérhet egy elemet egy adott pozícióból álló tömbből. Ez a kettő közötti szinergia lehetővé teszi számukra, hogy szinte bármilyen típusú keresést elvégezzenek, amire szüksége lehet.
Az INDEX / MATCH kombinációt történelmileg a VLOOKUP funkció helyettesítésére használták. Az egyik elsődleges ok a bal oldali keresés lehetősége (lásd a következő részt).
Megjegyzés: az új XLOOKUP funkció bal oldali kereséseket végezhet.
Nézzen balra
Használjuk ezt a kosárlabda statisztikai táblázatot:
Meg akarjuk találni Bob játékosát #. Mivel a Játékos # a név oszloptól balra található, nem használhatunk VLOOKUP -ot.
Ehelyett végezhetnénk egy alapvető MATCH kérést Bob sorának kiszámításához
= MATCH (H2, B2: B5, 0)
Ez a „Bob” szó pontos egyezését fogja keresni, és így a függvényünk a 2 -es számot adja vissza, mivel a „Bob” szerepel a 2 -bennd pozíció.
Ezután az INDEX függvény segítségével visszaadhatjuk a lejátszó #számát, ami egy sornak felel meg. Egyelőre csak manuálisan írjuk be a „2” -t a függvénybe:
= MUTATÓ (A2: A5, 2)
Itt az INDEX hivatkozik az A3 -ra, mivel ez a 2nd cellát az A2: A5 tartományon belül, és a 42. eredményt adja vissza.
= MUTATÓ (A2: A5, MATCH (H2, B2: B5, 0))
Ennek az az előnye, hogy vissza tudtunk adni egy találatot a keresésünktől balra lévő oszlopból.
Kétdimenziós keresés
Nézzük az előző táblázatunkat:
Ezúttal azonban egy konkrét statisztikát szeretnénk lekérni. Felhívtuk, hogy a H1 cellában szeretnénk visszapattanásokat keresni. Ahelyett, hogy több IF utasítást kellene írnia annak meghatározásához, hogy melyik oszlopból kapja meg az eredményt, használhatja ismét a MATCH függvényt. Az INDEX funkció lehetővé teszi a sor értékének megadását és az oszlop értéke. Egy másik MATCH függvényt adunk hozzá, hogy meghatározzuk, melyik oszlopot szeretnénk. Így fog kinézni
= MATCH (H1, A1: E1, 0)
A H1 -es cellánk egy legördülő menü, amelyből kiválaszthatjuk, hogy milyen kategóriát szeretnénk keresni, majd a MATCH meghatározja, hogy a táblázat melyik oszlopa tartozik. Csatlakoztassuk ezt az új részt a korábbi képletünkhöz. Ne feledje, hogy az első érvet két dimenzióra kell módosítanunk, mivel már nem csak az A oszlopból szeretnénk eredményt elérni.
= MUTATÓ (A2: E5, MATCH (H2, B2: B5, 0), MATCH (H1, A1: E1, 0))
Példánkban szeretnénk megtalálni a visszapattanásokat Charlie számára. A képletünk így fogja értékelni:
= INDEX (A2: E5, MATCH ("Charlie", B2: B5, 0), MATCH ("Rebounds", A1: E1, 0)) = INDEX (A2: E5, 3, 4) = D4 = 6
Létrehoztunk egy rugalmas beállítást, amely lehetővé teszi a felhasználó számára, hogy bármilyen értéket lekérjen a táblázatunkból anélkül, hogy több képleteket vagy elágazó IF utasításokat kellene írnia.
Több szakasz
Nem gyakran használják, de az INDEX -nek van egy ötödik argumentuma, amely megadható annak meghatározásához terület argumentumon belül egy használni. Ez azt jelenti, hogy szükségünk van arra, hogy több területet is belefoglaljunk az első érvbe. Ezt további zárójelek használatával teheti meg. Ez a példa szemlélteti, hogyan szerezhet be eredményeket a munkalap különböző tábláiból az INDEX használatával.
Íme az elrendezés, amelyet használni fogunk. Három különböző játékrészre vonatkozóan vannak statisztikáink.
A H1: H3 cellákban különböző választási lehetőségeinkhez elkészítettük az Adatok érvényesítése legördülő listákat. A negyed legördülő menüje a J2: J4 -ből származik. Ezt egy másik MATCH utasításhoz fogjuk használni annak meghatározásához, hogy melyik területet kell használni. A H4 képletünk így fog kinézni:
= MUTATÓ ((A3: E6, A10: E13, A17: E20), MATCH (H2, B3: B6, 0), MATCH (H1, A2: E2, 0), MATCH (H3, J2: J4, 0))
Már megbeszéltük, hogyan működik a belső két MATCH funkció, ezért koncentráljunk az első és utolsó érvekre:
= MUTATÓ ((A3: E6, A10: E13, A17: E20),…, MATCH (H3, J2: J4, 0))
Az INDEX függvénynek több tömböt adtunk az első argumentumban, zárójelbe zárva. A másik módja ennek a Képletek - Név meghatározása. Meghatározhat egy „MyTables” nevű nevet a
= INDEX (MyTable, MATCH (H2, Table 1347 [Name], 0), MATCH (H1, Table 1347 [#Headers], 0), MATCH (H3, J2: J4,0))
Térjünk vissza az egész állításhoz. Különféle MATCH funkcióink pontosan megmondják az INDEX függvénynek, hogy hol kell keresni. Először is megállapítjuk, hogy „Charlie” a 3rd sor. Ezután „Rebounds” -t szeretnénk, ami a 4th oszlop. Végül elhatároztuk, hogy a 2 -es eredményt akarjuknd asztal. A képlet így fogja értékelni:
= MUTATÓ ((A3: E6, A10: E13, A17: E20), MATCH (H2, B3: B6, 0), MATCH (H1, A2: E2, 0), MATCH (H3, J2: J4, 0)) = MUTATÓ ((A3: E6, A10: E13, A17: E20), 3, 4, 2) = MUTATÓ (A10: E13, 3, 4) = D13 = 14
Amint azt a példa elején említettük, a táblázatok ugyanazon a munkalapon vannak. Ha meg tudja írni a helyes módszereket, hogy megmondja az INDEX -nek, hogy melyik sorból, oszlopból és/vagy területről szeretne adatokat lekérni, akkor az INDEX nagyon jól szolgálja Önt.
Google Táblázatok -INDEX & MATCH
A fenti példák mindegyike pontosan ugyanúgy működik a Google Táblázatokban, mint az Excelben.
