Excel INDEX függvény (példák + videó)
Mikor kell használni az Excel INDEX függvényt?
Az Excel INDEX függvény akkor használható, ha az értéket le szeretné kérni egy táblázatos adatból, és rendelkezik az adatpont sorszámával és oszlopszámával. Például az alábbi példában az INDEX funkcióval megszerezheti a „Tom” jelet a fizikában, ha ismeri az adathalmaz sorszámát és oszlopszámát.
Mit ad vissza
Visszaadja a táblázatból a megadott sor- és oszlopszám értékét.
Szintaxis
= INDEX (tömb, sorszám, [oszlopszám])
= INDEX (tömb, sorszám, [oszlopszám], [területszám])
Az INDEX függvénynek 2 szintaxisa van. Az elsőt a legtöbb esetben használják, azonban háromirányú keresések esetén a másodikat használják (az 5. példában leírtak szerint).
Beviteli érvek
- tömb - a cellatartomány vagy tömbállandó.
- sor_száma - a sorszám, amelyből az értéket le kell kérni.
- [col_num] - az oszlop száma, amelyből le kell kérni az értéket. Bár ez nem kötelező argumentum, de ha a sor_száma nincs megadva, akkor meg kell adni.
- [terület_szám] - (Nem kötelező) Ha a tömb argumentum több tartományból áll, akkor ezt a számot használjuk a referencia kiválasztására az összes tartományból.
További megjegyzések (unalmas dolgok… de fontos tudni)
- Ha a sor vagy az oszlop száma 0, akkor a teljes sor vagy oszlop értékeit adja vissza.
- Ha az INDEX funkciót cellahivatkozás (például A1 :) előtt használja, akkor cellahivatkozást ad vissza érték helyett (lásd az alábbi példákat).
- Leggyakrabban a MATCH funkcióval együtt használják.
- A VLOOKUP -tal ellentétben az INDEX függvény a keresési érték bal oldaláról ad vissza értéket.
- Az INDEX függvénynek két formája van - tömb és referencia űrlap
- A „tömb űrlap” az, ahol az adott táblázatból származó sor- és oszlopszám alapján lekér egy értéket.
- A „referencia űrlap” az a hely, ahol több tábla található, és a area_num argumentum segítségével válassza ki a táblázatot, majd a sor- és oszlopszám használatával lekér egy értéket belsejében (lásd az alábbi élő példát).
Excel INDEX függvény - Példák
Íme hat példa az Excel INDEX függvény használatára.
1. példa - Tom jeleinek megtalálása a fizikában (kétirányú keresés)
Tegyük fel, hogy rendelkezik az alább látható adatkészlettel:
Ahhoz, hogy megtalálja Tom jeleit a fizikában, használja az alábbi képletet:
= INDEX ($ B $ 3: $ E $ 10,3,2)
Ez az INDEX képlet a tömböt $ B $ 3: $ E $ 10 -ként határozza meg, amely rendelkezik az összes alanyra vonatkozó jelöléssel. Ezután a sorszámot (3) és az oszlopszámot (2) használja Tom jeleinek lekéréséhez a fizikában.
2. példa - A LOOKUP érték dinamikussá tétele a MATCH funkció használatával
Előfordulhat, hogy nem mindig lehetséges manuálisan megadni a sor és az oszlop számát. Lehet, hogy hatalmas adathalmaza van, vagy dinamikussá kell tennie, hogy automatikusan azonosítsa a cellákban megadott nevet és/vagy tárgyat, és a helyes eredményt adja.
Valami az alábbiak szerint:
Ezt az INDEX és a MATCH funkció kombinációjával lehet megtenni.
Íme a képlet, amely dinamikussá teszi a keresési értékeket:
= INDEX ($ B $ 3: $ E $ 10, MATCH ($ G $ 5, $ A $ 3: $ A $ 10,0), MATCH ($ H $ 4, $ B $ 2: $ E $ 2,0))
A fenti képletben a sor- és az oszlopszám kemény kódolása helyett a MATCH függvényt használjuk dinamikussá.
- A dinamikus sorszámot a képlet következő része adja meg - MATCH ($ G $ 5, $ A $ 3: $ A $ 10,0). Beolvassa a diákok nevét és azonosítja a keresési értéket (ebben az esetben $ 5 $). Ezután visszaadja a keresési érték sorszámát az adatkészletben. Például, ha a keresési érték Matt, akkor 1 -et ad vissza, ha Bob, akkor 2 -t és így tovább.
- A dinamikus oszlopszámot a képlet következő része adja meg - MATCH ($ H $ 4, $ B $ 2: $ E $ 2,0). Beolvassa a tárgyneveket és azonosítja a keresési értéket (ebben az esetben $ H $ 4). Ezután visszaadja a keresési érték oszlopszámát az adatkészletben. Például, ha a keresési érték matematika, akkor 1 -et ad vissza, ha fizika, akkor 2 -t és így tovább.
3. példa - A legördülő listák használata keresési értékként
A fenti példában manuálisan kell megadnunk az adatokat. Ez időigényes és hibára hajlamos lehet, különösen, ha hatalmas keresési értékekkel rendelkezik.
Jó ötlet ilyen esetekben, ha létrehoz egy legördülő listát a keresési értékekről (ebben az esetben lehet diáknevek és tantárgyak), majd egyszerűen választhat a listából. A kiválasztás alapján a képlet automatikusan frissíti az eredményt.
Valami az alábbiak szerint:
Ez jó irányítópult -összetevőt jelent, mivel hatalmas adathalmaza lehet, több száz tanulóval a hátsó végén, de a végfelhasználó (mondjuk egy tanár) gyorsan megszerezheti a tanuló érdemjegyeit egy tantárgyból, ha egyszerűen kiválasztja a a legördülő.
Ennek elkészítése:
A képlet ebben az esetben ugyanaz, mint a 2. példában.
= INDEX ($ B $ 3: $ E $ 10, MATCH ($ G $ 5, $ A $ 3: $ A $ 10,0), MATCH ($ H $ 4, $ B $ 2: $ E $ 2,0))
A keresési értékek legördülő listákká alakultak át.
Íme az Excel legördülő lista létrehozásának lépései:
- Válassza ki azt a cellát, amelyben a legördülő listát szeretné megjeleníteni. Ebben a példában, a G4 -ben a diákok nevét akarjuk.
- Lépjen az Adatok -> Adateszközök -> Adatellenőrzés menüpontra.
- Az Adatok érvényesítése párbeszédpanelen, a beállítások lapon válassza a Lista lehetőséget az Engedélyezés legördülő menüből.
- A forrásban válassza a $ A $ 3: $ A $ 10 lehetőséget
- Kattintson az OK gombra.
Most megjelenik a legördülő lista a G5 cellában. Hasonlóképpen létrehozhat egyet a H4 -ben az alanyoknak.
4. példa - Értékek visszaadása egy egész sorból/oszlopból
A fenti példákban az Excel INDEX funkciót használtuk egy kétirányú kereséshez és egyetlen érték lekéréséhez.
Mi van akkor, ha meg akarja szerezni a diák összes jegyét. Ez lehetővé teszi, hogy megtalálja az adott tanuló maximális/minimális pontszámát, vagy az összes tantárgyból elért pontszámot.
Egyszerű angol nyelven először meg kell kapnia a tanuló (mondjuk Bob) teljes pontsorát, majd ezen az értékeken belül azonosítani kell a legmagasabb pontszámot vagy az összes pontszámot.
Itt a trükk.
Az Excel INDEX függvényében, amikor megadja a oszlop száma 0, visszaadja az egész sor értékeit.
Tehát a képlet a következő lenne:
= INDEX ($ B $ 3: $ E $ 10, MATCH ($ G $ 5, $ A $ 3: $ A $ 10,0), 0)
Most ez a képlet. ha a jelenlegi állapot szerint használják, akkor visszaadja az #ÉRTÉK értéket! hiba. Miközben megjeleníti a hibát, a háttérben egy tömböt ad vissza, amely tartalmazza a Tom összes pontszámát - {57,77,91,91}.
Ha szerkesztési módban választja ki a képletet, és megnyomja az F9 billentyűt, akkor láthatja a tömböt, amelyet visszaad (lásd alább): 
Hasonlóképpen, a keresési érték alapján, ha az oszlopszám 0 -ként van megadva (vagy üresen marad), akkor visszaadja a keresési érték sorának összes értékét
A Tom által kapott összpontszám kiszámításához egyszerűen használhatjuk a fenti képletet a SUM függvényen belül.
= SUM (INDEX ($ B $ 3: $ E $ 10, MATCH ($ G $ 5, $ A $ 3: $ A $ 10,0), 0))
Hasonló sorokban a legmagasabb pontszám kiszámításához használhatjuk a MAX/LARGE és a minimum kiszámításához a MIN/SMALL értéket.
5. példa - Háromirányú keresés az INDEX/MATCH használatával
Az Excel INDEX funkció a háromirányú keresések kezelésére szolgál.
Mi az a háromirányú keresés?
A fenti példákban egy táblázatot használtunk a pontszámokkal a különböző tantárgyak diákjai számára. Ez egy példa a kétirányú keresésre, mivel két változót használunk a pontszám lekérésére (tanuló neve és tárgya).
Most tegyük fel, hogy egy év alatt egy diáknak három különböző szintű vizsgája van: egységvizsga, félév és záróvizsga (ez volt a diákkorom).
A háromirányú lekérdezés az a képesség, amely lehetővé teszi, hogy a hallgató egy adott tantárgyból érdemjegyeket szerezzen a vizsga meghatározott szintjéről. Ez háromirányú keresést eredményezne, mivel három változó létezik (tanuló neve, tantárgy neve és a vizsga szintje).
Íme egy példa a háromirányú keresésre:
A fenti példában a tanuló nevének és tantárgyának nevén kívül a vizsga szintjét is kiválaszthatja. A vizsga szintje alapján a három táblázat egyikének megfelelő értékét adja vissza.
Itt található a H4 cellában használt képlet:
= INDEX (($ B $ 3: $ E $ 7, $ B $ 11: $ E $ 15, $ B $ 19: $ E $ 23), MATCH ($ G $ 4, $ A $ 3: $ A $ 7,0), MATCH ($ H $ 3, $ B $ 2: $ E $ 2,0), IF ($ H $ 2 = "Unit Test", 1, IF ($ H $ 2 = "Midterm", 2,3)))
Bontsuk le ezt a képletet, hogy megértsük, hogyan működik.
Ez a képlet négy érvet tartalmaz. Az INDEX az Excel azon funkcióinak egyike, amelyek több szintaxissal rendelkeznek.
= INDEX (tömb, sorszám, [oszlopszám])
= INDEX (tömb, sorszám, [oszlopszám], [területszám])
A fenti példában eddig az első szintaxist használtuk, de a háromirányú kereséshez a második szintaxist kell használnunk.
Most nézzük meg a képlet minden részét a második szintaxis alapján.
- tömb - ($ B $ 3: $ E $ 7, $ B $ 11: $ E $ 15, $ B $ 19: $ E $ 23): Ebben az esetben egyetlen tömb használata helyett három tömböt használtunk zárójelben.
- row_num - MATCH ($ G $ 4, $ A $ 3: $ A $ 7,0): A MATCH függvény a diák nevének pozícióját keresi meg a $ G $ 4 cellában a diák nevének listájában.
- col_num - MATCH ($ H $ 3, $ B $ 2: $ E $ 2,0): A MATCH függvény a tárgynév pozíciójának megkeresésére szolgál az alany névlistájának $ H $ 3 cellájában.
- [terület_szám] - HA ($ H $ 2 = ”Unit Test”, 1, IF ($ H $ 2 = ”Midterm”, 2,3)): A terület számértéke megmondja az INDEX függvénynek, hogy melyik tömböt kell választani. Ebben a példában három tömb van az első argumentumban. Ha a legördülő menüben az Egység teszt lehetőséget választja, az IF függvény 1-et ad vissza, az INDEX függvény pedig az 1. tömböt választja ki a három tömbből (ez $ B $ 3: $ E $ 7).
6. példa - Referencia létrehozása az INDEX függvény használatával (dinamikus elnevezett tartományok)
Ez az Excel INDEX függvény vad használata.
Vegyünk egy egyszerű példát.
Van egy listám a nevekről az alábbiak szerint:
Most már használhatok egy egyszerű INDEX függvényt, hogy megszerezzem a vezetéknevet a listán.
Íme a képlet:
= INDEX ($ A $ 2: $ A $ 9, COUNTA ($ A $ 2: $ A $ 9))
Ez a függvény egyszerűen megszámolja a nem üres cellák számát, és visszaadja a lista utolsó elemét (csak akkor működik, ha nincsenek üres helyek a listában).
Nos, itt jön a varázslat.
Ha a képletet egy cellahivatkozás elé helyezi, a képlet az egyező érték cellahivatkozását adja vissza (maga az érték helyett).
= A2: INDEX ($ A $ 2: $ A $ 9, COUNTA ($ A $ 2: $ A $ 9))
Azt várná, hogy a fenti képlet visszatérjen = A2: „Josh” (ahol Josh az utolsó érték a listában). Viszont visszatér = A2: A9, és így kap egy tömb nevet az alábbiak szerint:
Egy praktikus példa, ahol ez a technika hasznos lehet, dinamikus elnevezett tartományok létrehozása.
Ennyi ebben az oktatóanyagban. Megpróbáltam bemutatni az Excel INDEX funkció használatának főbb példáit. Ha további példákat szeretne hozzáadni ehhez a listához, tudassa velem a megjegyzések részben.
Megjegyzés: A legjobb tudásom szerint próbáltam elolvasni ezt az oktatóanyagot, de ha bármilyen hibát vagy helyesírási hibát talál, kérjük, tudassa velem 🙂
Excel INDEX függvény - Videó bemutató
- Excel VLOOKUP funkció.
- Excel HLOOKUP függvény.
- Excel INDIRECT függvény.
- Excel MATCH függvény.
- Excel OFFSET funkció.
A következő Excel oktatóanyagok is tetszhetnek:
- VLOOKUP vs. INDEX/MATCH
- Excel Index egyezés
- Keresési és visszatérési értékek egy egész sorban/oszlopban.
