Funkcia Excel INDEX (príklady + video)
Kedy použiť funkciu Excel INDEX
Funkciu Excel INDEX je možné použiť, ak chcete načítať hodnotu z tabuľkových údajov a máte číslo riadka a číslo stĺpca údajového bodu. Napríklad v nižšie uvedenom príklade môžete použiť funkciu INDEX na získanie značiek „Tom“ vo fyzike, keď poznáte v riadku údajov číslo riadka a číslo stĺpca.
Čo vracia
Vráti hodnotu z tabuľky pre zadané číslo riadka a číslo stĺpca.
Syntax
= INDEX (pole, počet_ riadkov, [stĺpcov])
= INDEX (pole, počet_ riadkov, [stĺpcový_číslo], [oblasť_číslo])
Funkcia INDEX má 2 syntaxe. Vo väčšine prípadov sa používa prvý, v prípade trojsmerného vyhľadávania sa však používa druhý (popísaný v príklade 5).
Vstupné argumenty
- pole - a rozsah buniek alebo konštanta poľa.
- row_num - číslo riadka, z ktorého sa má hodnota načítať.
- [col_num] - číslo stĺpca, z ktorého sa má hodnota načítať. Toto je voliteľný argument, ale ak nie je zadaný riadok_číslo, je potrebné ho zadať.
- [area_num] - (Voliteľné) Ak argument poľa pozostáva z viacerých rozsahov, toto číslo by sa použilo na výber referencie zo všetkých rozsahov.
Dodatočné poznámky (Nudné veci … ale dôležité vedieť)
- Ak je číslo riadka alebo číslo stĺpca 0, vráti hodnoty celého riadka alebo stĺpca.
- Ak je funkcia INDEX použitá pred odkazom na bunku (napríklad A1 :), vráti namiesto hodnoty odkaz na bunku (pozri príklady nižšie).
- Najčastejšie sa používa spolu s funkciou MATCH.
- Na rozdiel od funkcie VLOOKUP môže funkcia INDEX vracať hodnotu zľava od vyhľadávanej hodnoty.
- Funkcia INDEX má dve formy - maticový formulár a referenčný formulár
- „Forma poľa“ je miesto, kde z danej tabuľky načítate hodnotu na základe čísla riadka a stĺpca.
- „Referenčný formulár“ je tam, kde je viac tabuliek, a pomocou argumentu area_num vyberiete tabuľku a potom do nej načítate hodnotu pomocou čísla riadka a stĺpca (pozri živý príklad nižšie).
Funkcia Excel INDEX - príklady
Tu je šesť príkladov použitia funkcie Excel INDEX.
Príklad 1 - Hľadanie Tomových značiek vo fyzike (obojsmerné vyhľadávanie)
Predpokladajme, že máte množinu údajov, ako je uvedené nižšie:
Ak chcete nájsť Tomove známky vo fyzike, použite nasledujúci vzorec:
= INDEX ($ B $ 3: $ E $ 10,3,2)
Tento vzorec INDEX určuje pole ako $ B $ 3: $ E $ 10, ktoré má značky pre všetky predmety. Potom použije číslo riadku (3) a číslo stĺpca (2) na načítanie Tomových značiek z fyziky.
Príklad 2 - Dynamická hodnota hodnoty LOOKUP pomocou funkcie MATCH
Nie vždy môže byť možné ručne zadať číslo riadka a číslo stĺpca. Môžete mať veľký súbor údajov, alebo ho chcete dynamizovať, aby automaticky identifikoval meno a/alebo predmet uvedený v bunkách a poskytol správny výsledok.
Niečo, ako je uvedené nižšie:
To sa dá dosiahnuť kombináciou funkcie INDEX a funkcie MATCH.
Tu je vzorec, vďaka ktorému budú hodnoty vyhľadávania dynamické:
= INDEX ($ B $ 3: $ E $ 10, MATCH ($ G $ 5, $ A $ 3: $ A $ 10,0), MATCH ($ H $ 4, $ B $ 2: $ E $ 2,0))
Vo vyššie uvedenom vzorci sa namiesto pevného kódovania čísla riadka a čísla stĺpca používa funkcia MATCH, aby bol dynamický.
- Dynamické číslo riadka je dané nasledujúcou časťou vzorca - ZÁPAS ($ G $ 5, $ A $ 3: $ A $ 10,0). Naskenuje meno študentov a identifikuje vyhľadávaciu hodnotu (v tomto prípade 5 G $ $). Potom vráti číslo riadka vyhľadávanej hodnoty v množine údajov. Ak je napríklad hodnota vyhľadávania Matt, vráti 1, ak je to Bob, vráti 2 a podobne.
- Číslo dynamického stĺpca je dané nasledujúcou časťou vzorca - ZÁPAS ($ H $ 4, $ B $ 2: $ E $ 2,0). Naskenuje názvy predmetov a identifikuje vyhľadávaciu hodnotu (v tomto prípade $ H $ 4). Potom vráti číslo stĺpca vyhľadávanej hodnoty v množine údajov. Ak je napríklad hodnota vyhľadávania matematická, vráti 1, ak je to fyzika, vráti 2 a podobne.
Príklad 3 - Použitie rozbaľovacích zoznamov ako hodnôt vyhľadávania
V uvedenom príklade musíme údaje zadať ručne. To môže byť časovo náročné a náchylné na chyby, najmä ak máte obrovský zoznam vyhľadávacích hodnôt.
V takýchto prípadoch je vhodné vytvoriť rozbaľovací zoznam vyhľadávacích hodnôt (v tomto prípade to môžu byť mená študentov a predmety) a potom si jednoducho vybrať zo zoznamu. Na základe výberu vzorec automaticky aktualizuje výsledok.
Niečo, ako je uvedené nižšie:
Toto je dobrý komponent dashboardu, pretože môžete mať k dispozícii obrovský súbor údajov so stovkami študentov na zadnom konci, ale koncový používateľ (povedzme učiteľ) môže rýchlo získať známky študenta v predmete jednoduchým výberom z rozbaľovací zoznam.
Ako to urobiť:
Vzorec použitý v tomto prípade je rovnaký ako v príklade 2.
= INDEX ($ B $ 3: $ E $ 10, MATCH ($ G $ 5, $ A $ 3: $ A $ 10,0), MATCH ($ H $ 4, $ B $ 2: $ E $ 2,0))
Hodnoty vyhľadávania boli prevedené do rozbaľovacích zoznamov.
Tu je postup, ako vytvoriť rozbaľovací zoznam programu Excel:
- V rozbaľovacom zozname vyberte bunku, do ktorej chcete. V tomto prípade v G4 chceme mená študentov.
- Prejdite na položku Údaje -> Nástroje údajov -> Overenie údajov.
- V dialógovom okne Overenie údajov na karte nastavení v rozbaľovacej ponuke Povoliť vyberte položku Zoznam.
- V zdroji vyberte $ A $ 3: $ A $ 10
- Kliknite na tlačidlo OK.
Teraz budete mať rozbaľovací zoznam v bunke G5. Podobne môžete vytvoriť jeden v H4 pre predmety.
Príklad 4 - Vrátenie hodnôt z celého riadka/stĺpca
Vo vyššie uvedených príkladoch sme použili funkciu Excel INDEX na obojsmerné vyhľadávanie a získanie jednej hodnoty.
Teraz, čo keď chcete získať všetky známky študenta. To vám umožní nájsť maximálne/minimálne skóre daného študenta alebo celkový počet bodov dosiahnutých vo všetkých predmetoch.
V jednoduchej angličtine chcete najskôr získať celý rad skóre pre študenta (povedzme Bob) a potom v rámci týchto hodnôt identifikovať najvyššie skóre alebo súčet všetkých skóre.
Tu je ten trik.
Keď vo funkcii Excel INDEX zadáte príkaz číslo stĺpca ako 0, vráti hodnoty celého tohto riadka.
Vzorec pre toto by teda bol:
= INDEX ($ B $ 3: $ E $ 10, MATCH ($ G $ 5, $ A $ 3: $ A $ 10,0), 0)
Teraz tento vzorec. ak sa použije tak, ako je, vráti #HODNOTU! chyba. Aj keď zobrazuje chybu, v backende vracia pole, ktoré má všetky skóre pre Toma - {57,77,91,91}.
Ak v režime úprav vyberiete vzorec a stlačíte kláves F9, uvidíte pole, ktoré vracia (ako je uvedené nižšie): 
Podobne na základe toho, aká je hodnota vyhľadávania, keď je číslo stĺpca zadané ako 0 (alebo je ponechané prázdne), vráti všetky hodnoty v riadku pre hodnotu vyhľadávania.
Teraz na výpočet celkového skóre získaného Tomom môžeme jednoducho použiť vyššie uvedený vzorec vo funkcii SUMA.
= SUM (INDEX ($ B $ 3: $ E $ 10, MATCH ($ G $ 5, $ A $ 3: $ A $ 10,0), 0))
Na podobných riadkoch na výpočet najvyššieho skóre môžeme použiť MAX/VEĽKÉ a na výpočet minima použiť MIN/MALÉ.
Príklad 5 - Trojcestné vyhľadávanie pomocou INDEX/MATCH
Funkcia Excel INDEX je navrhnutá tak, aby zvládala trojsmerné vyhľadávanie.
Čo je trojcestné vyhľadávanie?
Vo vyššie uvedených príkladoch sme použili jednu tabuľku so skóre pre študentov z rôznych predmetov. Toto je príklad obojsmerného vyhľadávania, pretože na získanie skóre používame dve premenné (meno študenta a predmet).
Teraz, predpokladajme, že za rok bude mať študent tri rôzne úrovne skúšok, jednotkový test, priebežné a záverečné skúšky (to som mal, keď som bol študentom).
Trojstranným vyhľadávaním by bola možnosť získať známky študenta za určený predmet zo zadanej úrovne skúšky. To by znamenalo trojstranné vyhľadávanie, pretože existujú tri premenné (meno študenta, názov predmetu a úroveň skúšky).
Tu je príklad trojsmerného vyhľadávania:
V uvedenom príklade môžete okrem výberu mena a predmetu študenta vybrať aj úroveň skúšky. Na základe úrovne skúšky vráti zodpovedajúcu hodnotu z jednej z troch tabuliek.
Tu je vzorec použitý v bunke H4:
= 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)))
Poďme rozobrať tento vzorec, aby sme pochopili, ako funguje.
Tento vzorec obsahuje štyri argumenty. INDEX je jednou z funkcií programu Excel, ktorá má viac ako jednu syntax.
= INDEX (pole, počet_ riadkov, [stĺpcov])
= INDEX (pole, počet_ riadkov, [stĺpcový_číslo], [oblasť_číslo])
Vo všetkých vyššie uvedených príkladoch sme zatiaľ použili prvú syntax, ale na trojcestné vyhľadávanie musíme použiť druhú syntax.
Teraz sa pozrime na každú časť vzorca na základe druhej syntaxe.
- pole - ($ B $ 3: $ E $ 7, $ B $ 11: $ E $ 15, $ B $ 19: $ E $ 23): Namiesto použitia jediného poľa sme v tomto prípade použili tri polia v zátvorkách.
- row_num - MATCH ($ G $ 4, $ A $ 3: $ A $ 7,0): Funkcia MATCH sa používa na nájdenie polohy mena študenta v bunke $ G $ 4 v zozname mien študentov.
- col_num - MATCH ($ H $ 3, $ B $ 2: $ E $ 2,0): Funkcia MATCH sa používa na nájdenie polohy názvu subjektu v bunke $ H $ 3 v zozname mien subjektu.
- [area_num] - IF ($ H $ 2 = ”Unit Test”, 1, IF ($ H $ 2 = ”Midterm”, 2,3)): Hodnota čísla oblasti hovorí funkcii INDEX, ktoré pole má vybrať. V tomto prípade máme v prvom argumente tri polia. Ak v rozbaľovacom zozname vyberiete Unit Test, funkcia IF vráti hodnotu 1 a funkcie INDEX vyberú prvé pole z troch polí (čo je $ B $ 3: $ E $ 7).
Príklad 6 - Vytvorenie referencie pomocou funkcie INDEX (dynamicky pomenované rozsahy)
Toto je divoké použitie funkcie Excel INDEX.
Zoberme si jednoduchý príklad.
Mám zoznam mien, ako je uvedené nižšie:
Teraz môžem pomocou jednoduchej funkcie INDEX získať priezvisko v zozname.
Tu je vzorec:
= INDEX ($ A $ 2: $ A $ 9, COUNTA ($ A $ 2: $ A $ 9))
Táto funkcia jednoducho spočíta počet buniek, ktoré nie sú prázdne, a vráti poslednú položku z tohto zoznamu (funguje to iba vtedy, ak v zozname nie sú žiadne medzery).
Teraz príde kúzlo.
Ak umiestnite vzorec pred odkaz na bunku, vzorec by vrátil odkaz na bunku zodpovedajúcej hodnoty (namiesto samotnej hodnoty).
= A2: INDEX ($ A $ 2: $ A $ 9, COUNTA ($ A $ 2: $ A $ 9))
Očakávali by ste, že sa vyššie uvedený vzorec vráti = A2: „Josh“ (kde Josh je posledná hodnota v zozname). Vráti však = A2: A9, a preto získate pole mien, ako je uvedené nižšie:
Jeden praktický príklad, kde môže byť táto technika nápomocná, je vytváranie dynamických pomenovaných rozsahov.
To je v tomto návode. Pokúsil som sa pokryť hlavné príklady použitia funkcie Excel INDEX. Ak by ste chceli vidieť viac príkladov pridaných do tohto zoznamu, dajte mi vedieť v sekcii komentárov.
Poznámka: Snažil som sa zo všetkých síl dokázať prečítať si tento návod, ale ak nájdete nejaké chyby alebo pravopisné chyby, dajte mi vedieť 🙂
Funkcia Excel INDEX - video návod
- Funkcia Excel VLOOKUP.
- Funkcia Excel HLOOKUP.
- Excel NEPRIAMA funkcia.
- Funkcia Excel MATCH.
- Funkcia Excel OFFSET.
Tiež by sa vám mohli páčiť nasledujúce návody na Excel:
- VLOOKUP vs. INDEX/ZHODA
- Zhoda indexu programu Excel
- Hodnoty vyhľadávania a vrátenia v celom riadku/stĺpci.
