Funkce Excel INDEX (příklady + video)
Kdy použít funkci Excel INDEX
Funkci Excel INDEX lze použít, pokud chcete načíst hodnotu z tabulkových dat a máte číslo řádku a číslo sloupce datového bodu. Například v níže uvedeném příkladu můžete použít funkci INDEX k získání značek „Tom“ ve fyzice, když znáte číslo řádku a číslo sloupce v datové sadě.
Co vrací
Vrátí hodnotu z tabulky pro zadané číslo řádku a číslo sloupce.
Syntax
= INDEX (pole, počet řádků, [sloupec])
= INDEX (pole, řádek_číslo, [sloupec_číslo], [oblast_číslo])
Funkce INDEX má 2 syntaxe. První se používá ve většině případů, ale v případě třícestného vyhledávání se používá druhý (popsáno v příkladu 5).
Vstupní argumenty
- pole - A rozsah buněk nebo konstanta pole.
- row_num - číslo řádku, ze kterého má být načtena hodnota.
- [col_num] - číslo sloupce, ze kterého má být načtena hodnota. Ačkoli se jedná o volitelný argument, ale pokud není zadán row_num, je třeba jej zadat.
- [area_num] - (Volitelné) Pokud je argument pole tvořen více rozsahy, bude toto číslo použito k výběru odkazu ze všech rozsahů.
Další poznámky (nudné věci … ale důležité vědět)
- Pokud je číslo řádku nebo číslo sloupce 0, vrátí hodnoty celého řádku nebo sloupce.
- Pokud je funkce INDEX použita před odkazem na buňku (například A1 :), vrátí odkaz na buňku místo hodnoty (viz příklady níže).
- Nejčastěji se používá společně s funkcí MATCH.
- Na rozdíl od funkce VLOOKUP může funkce INDEX vrátit hodnotu zleva od hodnoty vyhledávání.
- Funkce INDEX má dvě formy - maticový formulář a referenční formulář
- „Forma pole“ je místo, kde z dané tabulky načtete hodnotu na základě čísla řádku a sloupce.
- „Referenční formulář“ je tam, kde je více tabulek, a pomocí argumentu area_num tabulku vyberte a poté v ní načtěte hodnotu pomocí čísla řádku a sloupce (viz živý příklad níže).
Funkce Excel INDEX - příklady
Zde je šest příkladů použití funkce Excel INDEX.
Příklad 1 - Hledání Tomových značek ve fyzice (obousměrné vyhledávání)
Předpokládejme, že máte datovou sadu, jak je uvedeno níže:
Chcete -li najít Tomovy známky ve fyzice, použijte následující vzorec:
= INDEX ($ B $ 3: $ E $ 10,3,2)
Tento vzorec INDEX určuje pole jako $ B $ 3: $ E $ 10, které má známky pro všechny předměty. Poté použije číslo řádku (3) a číslo sloupce (2) k načtení Tomových značek z fyziky.
Příklad 2 - Dynamická hodnota hodnoty LOOKUP pomocí funkce MATCH
Číslo řádku a číslo sloupce nemusí být vždy možné zadat ručně. Možná máte velkou datovou sadu, nebo ji chcete dynamizovat, aby automaticky identifikovala jméno a/nebo předmět zadaný v buňkách a poskytla správný výsledek.
Něco, jak je uvedeno níže:
To lze provést kombinací funkce INDEX a funkce MATCH.
Zde je vzorec, díky kterému budou hodnoty vyhledávání dynamické:
= INDEX ($ B $ 3: $ E $ 10, MATCH ($ G $ 5, $ A $ 3: $ A $ 10,0), MATCH ($ H $ 4, $ B $ 2: $ E $ 2,0))
Ve výše uvedeném vzorci se místo pevného kódování čísla řádku a čísla sloupce používá funkce MATCH, aby byl dynamický.
- Dynamické číslo řádku je dáno následující částí vzorce - MATCH ($ G $ 5, $ A $ 3: $ A $ 10,0). Naskenuje jméno studentů a identifikuje vyhledávací hodnotu (v tomto případě $ G $ 5). Potom vrátí číslo řádku vyhledávané hodnoty v datové sadě. Pokud je například vyhledávací hodnota Matt, vrátí 1, pokud je to Bob, vrátí 2 a tak dále.
- Číslo dynamického sloupce je dáno následující částí vzorce - MATCH ($ H $ 4, $ B $ 2: $ E $ 2,0). Naskenuje jména subjektů a identifikuje vyhledávací hodnotu (v tomto případě $ H $ 4). Potom vrátí číslo sloupce vyhledávané hodnoty v datové sadě. Pokud je například vyhledávací hodnota matematická, vrátí 1, pokud je to fyzika, vrátí 2 atd.
Příklad 3 - Použití rozevíracích seznamů jako hodnot vyhledávání
Ve výše uvedeném příkladu musíme data zadat ručně. To může být časově náročné a náchylné k chybám, zvláště pokud máte obrovský seznam hodnot vyhledávání.
V takových případech je dobré vytvořit rozevírací seznam vyhledávacích hodnot (v tomto případě to mohou být jména studentů a předměty) a poté jednoduše vybrat ze seznamu. Na základě výběru by vzorec automaticky aktualizoval výsledek.
Něco, jak je uvedeno níže:
To je dobrá součást řídicího panelu, protože můžete mít obrovskou sadu dat se stovkami studentů na zadním konci, ale koncový uživatel (řekněme učitel) může rychle získat známky studenta v předmětu jednoduchým výběrem z rozevírací seznam.
Jak to udělat:
Vzorec použitý v tomto případě je stejný jako v příkladu 2.
= INDEX ($ B $ 3: $ E $ 10, MATCH ($ G $ 5, $ A $ 3: $ A $ 10,0), MATCH ($ H $ 4, $ B $ 2: $ E $ 2,0))
Hodnoty vyhledávání byly převedeny do rozevíracích seznamů.
Zde jsou kroky k vytvoření rozevíracího seznamu aplikace Excel:
- V rozevíracím seznamu vyberte buňku, ve které chcete. V tomto příkladu v G4 chceme jména studentů.
- Přejděte na Data -> Datové nástroje -> Ověření dat.
- V dialogovém okně Ověření dat na kartě nastavení vyberte v rozevíracím seznamu Povolit seznam.
- Ve zdroji vyberte $ A $ 3: $ A $ 10
- Klikněte na OK.
Nyní budete mít rozevírací seznam v buňce G5. Podobně můžete vytvořit jeden v H4 pro předměty.
Příklad 4 - Vrácení hodnot z celého řádku/sloupce
Ve výše uvedených příkladech jsme použili funkci Excel INDEX k obousměrnému vyhledávání a získání jediné hodnoty.
Nyní, co když chcete získat všechny známky studenta. To vám umožní zjistit maximální/minimální skóre daného studenta nebo celkový počet bodů dosažených ve všech předmětech.
V jednoduché angličtině chcete nejprve získat celou řadu skóre pro studenta (řekněme Bob) a poté v rámci těchto hodnot určit nejvyšší skóre nebo součet všech skóre.
Tady je ten trik.
Když ve funkci Excel INDEX zadáte číslo sloupce jako 0, vrátí hodnoty celého tohoto řádku.
Vzorec pro toto by tedy byl:
= INDEX ($ B $ 3: $ E $ 10, MATCH ($ G $ 5, $ A $ 3: $ A $ 10,0), 0)
Nyní tento vzorec. pokud se použije tak, jak je, vrátí #HODNOTU! chyba. Zatímco zobrazuje chybu, v backendu vrací pole, které má všechna skóre pro Toma - {57,77,91,91}.
Pokud v režimu úprav vyberete vzorec a stisknete F9, uvidíte pole, které vrací (jak je uvedeno níže): 
Podobně podle toho, jaká je vyhledávací hodnota, když je číslo sloupce zadáno jako 0 (nebo je ponecháno prázdné), vrátí všechny hodnoty v řádku pro vyhledávací hodnotu
Nyní pro výpočet celkového skóre získaného Tomem můžeme jednoduše použít výše uvedený vzorec v rámci funkce SUMA.
= SUM (INDEX ($ B $ 3: $ E $ 10, MATCH ($ G $ 5, $ A $ 3: $ A $ 10,0), 0))
Na podobných řádcích můžeme pro výpočet nejvyššího skóre použít MAX/VELKÉ a pro výpočet minima můžeme použít MIN/MALÉ.
Příklad 5 - Trojcestné vyhledávání pomocí INDEX/MATCH
Funkce Excel INDEX je vytvořena pro zpracování třícestného vyhledávání.
Co je třícestné vyhledávání?
Ve výše uvedených příkladech jsme použili jednu tabulku se skóre pro studenty z různých předmětů. Toto je příklad obousměrného vyhledávání, protože k získání skóre používáme dvě proměnné (jméno studenta a předmět).
Předpokládejme, že za rok bude mít student tři různé úrovně zkoušek, Unit Test, Midterm a Final Examination (to jsem měl, když jsem byl student).
Třícestným vyhledáváním by byla možnost získat známky studenta za určený předmět ze zadané úrovně zkoušky. To by způsobilo třísměrné vyhledávání, protože existují tři proměnné (jméno studenta, název předmětu a úroveň zkoušky).
Zde je příklad třícestného vyhledávání:
Ve výše uvedeném příkladu můžete kromě výběru jména studenta a názvu předmětu vybrat také úroveň zkoušky. Na základě úrovně zkoušky vrátí odpovídající hodnotu z jedné ze tří tabulek.
Zde je vzorec použitý v buňce 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)))
Pojďme si tento vzorec rozebrat, abychom pochopili, jak funguje.
Tento vzorec má čtyři argumenty. INDEX je jednou z funkcí v aplikaci Excel, která má více než jednu syntaxi.
= INDEX (pole, počet řádků, [sloupec])
= INDEX (pole, řádek_číslo, [sloupec_číslo], [oblast_číslo])
Ve všech výše uvedených příkladech jsme zatím použili první syntaxi, ale pro třísměrné vyhledávání musíme použít druhou syntaxi.
Nyní se podívejme na každou část vzorce na základě druhé syntaxe.
- pole - ($ B $ 3: $ E $ 7, $ B $ 11: $ E $ 15, $ B $ 19: $ E $ 23): Místo použití jediného pole jsme v tomto případě použili tři pole v závorkách.
- row_num - MATCH ($ G $ 4, $ A $ 3: $ A $ 7,0): Funkce MATCH slouží k vyhledání pozice jména studenta v buňce $ G $ 4 v seznamu jména studenta.
- col_num - MATCH ($ H $ 3, $ B $ 2: $ E $ 2,0): Funkce MATCH se používá k nalezení polohy názvu subjektu v buňce $ H $ 3 v seznamu názvu subjektu.
- [area_num] - IF ($ H $ 2 = ”Unit Test”, 1, IF ($ H $ 2 = ”Midterm”, 2,3)): Hodnota čísla oblasti sděluje funkci INDEX, které pole vybrat. V tomto příkladu máme v prvním argumentu tři pole. Pokud z rozevíracího seznamu vyberete Unit Test, funkce IF vrátí 1 a funkce INDEX vyberou první pole ze tří polí (což je $ B $ 3: $ E $ 7).
Příklad 6 - Vytvoření reference pomocí funkce INDEX (dynamicky pojmenované rozsahy)
Toto je jedno divoké použití funkce Excel INDEX.
Vezměme si jednoduchý příklad.
Mám seznam jmen, jak je uvedeno níže:
Nyní mohu pomocí jednoduché funkce INDEX získat příjmení v seznamu.
Zde je vzorec:
= INDEX ($ A $ 2: $ A $ 9, COUNTA ($ A $ 2: $ A $ 9))
Tato funkce jednoduše spočítá počet buněk, které nejsou prázdné, a vrátí poslední položku z tohoto seznamu (funguje pouze v případě, že v seznamu nejsou mezery).
Nyní přichází kouzlo.
Pokud vzorec umístíte před odkaz na buňku, vzorec by vrátil odkaz na buňku odpovídající hodnoty (namísto samotné hodnoty).
= A2: INDEX ($ A $ 2: $ A $ 9, COUNTA ($ A $ 2: $ A $ 9))
Očekávali byste, že výše uvedený vzorec vrátí = A2: „Josh“ (kde Josh je poslední hodnotou v seznamu). Vrací však = A2: A9, a proto získáte řadu jmen, jak je uvedeno níže:
Jedním praktickým příkladem, kde může být tato technika užitečná, je vytváření dynamických pojmenovaných rozsahů.
To je v tomto tutoriálu. Pokusil jsem se pokrýt hlavní příklady použití funkce Excel INDEX. Pokud byste chtěli vidět další příklady přidané do tohoto seznamu, dejte mi vědět v sekci komentáře.
Poznámka: Snažil jsem se ze všech sil dokázat přečíst si tento návod, ale v případě, že najdete nějaké chyby nebo pravopisné chyby, dejte mi prosím vědět 🙂
Funkce Excel INDEX - video tutoriál
- Funkce VLOOKUP aplikace Excel.
- Funkce Excel HLOOKUP.
- Excel NEPŘÍMÁ funkce.
- Funkce Excel MATCH.
- Funkce Excel OFFSET.
Také by se vám mohly líbit následující výukové programy pro Excel:
- VLOOKUP vs. INDEX/ZÁPAS
- Shoda indexu aplikace Excel
- Hodnoty vyhledávání a vrácení v celém řádku/sloupci.
