Ve spolupráci se SEDUO jsem vytvořil několik videokurzů:
Popis vyhledávacích funkcí INDEX, POZVYHLEDAT, spolu s příklady jak tyto funkce prakticky využít. Například k vytvoření seznamu unikátních (jedinečných) dat z tabulky, náhrada funkce SVYHLEDAT.
Článek doplněn: 4.7.2015
Dozvíte jak prakticky využít funkce INDEX a POZVYHLEDAT. Jak možností těchto funkcí využít je ukázano na praktických příkladech spolu se sešity zdarma ke stažení. Pro přehlednost je tento text rozdělen do následujících kapitol:
Vrátí hodnotu nebo odkaz (index řádku, sloupce) na hodnotu z tabulky/oblasti.
Funkce INDEX (INDEX) může být použita dvěma způsoby:
Vrátí adresu buňky ležící v průsečíku zadaného řádku a sloupce.
INDEX(odkaz;řádek;sloupec;oblast)
Hodnotu prvku tabulky nebo matice označeného indexem řádku a sloupce.
INDEX(pole;řádek;sloupec)
Musí být zadán jeden z argumentu řádek nebo sloupec.
Praktické použití funkce INDEX. Z tabulky potřebujete zjistit, jaká hodnota se nachází v požadovaném řádku (sloupci).
Připravte si tabulky (v první dopravní prostředky) a v druhé popis sloupců.
Potřebujeme zjistit z první tabulky:
Potřebujeme zjistit z druhé tabulky:
Řešení pro první tabulku:
=INDEX(B5:B9;1)
=INDEX(B5:B9;4)
Řešení pro druhou tabulku:
=INDEX(B15:E15;2)
=INDEX(B15:E15;3)
V komentářích jsem měl zajímavý dotaz:
Dobrý den.
Zapisuji data ve sloupci B..... číselná data. Potřebuji poslední záznam ve sloupci vidět na místě D5.
Do D5 zadat následující vzorec.
=INDEX(B8:B999;POČET2(B8:B999);1)
Případně pokud budete chtít poslední hodnotu ze sloupce A:
=INDEX(A7:A998;POČET2(A7:A998);1)
Máte označenou oblast a potřebujete vypsat hodnotu, která se nachází ve vybraném průsečíku řádku x sloupce.
Podobná funkce je pod názvem POSUN podrobněji v samostatném článku: POSUN - (OFFSET) - funkce Excel .
=INDEX(B4:D9;C12;C13)
Soubor
Hodnota v řádku/sloupci vybrané oblasti
s využitím funkce INDEX.
Vyhledá zadanou položku v oblasti buněk a vrátí relativní pozici dané položky v oblasti
Syntaxe
POZVYHLEDAT(co; prohledat; [shoda])
Potřebujete-li v tabulce vyhledat na kterém řádku se nachází zadaná hodnota (nebo nejbližší vyšší/nižší). Praktická ukázka bude předvedena na následující tabulce:
Potřebujete zjistit:
Řešení pro odpověď na kterém řádku se nachází výrobek: Banán
=POZVYHLEDAT("Banán";B6:B12;0)
Řešení pro odpověď na kterém řádku se nachází cena 15
=POZVYHLEDAT(C19;E5:E11;1)
=POZVYHLEDAT(C19;E5:E11;0)
Řešení pro odpověď na kterém řádku se nachází cena 30
=POZVYHLEDAT(C25;E5:E11;1)
Najde největší hodnotu, která je menší nebo rovna hledané hodnotě (tj. 45).
Potřebujete-li z tabulky vyselektovat jedinečná data (z TT, TT, TT, AA, AA, AA, bude jen TT, AA). Na ukázkovém příkladu Vám ukáží jak na to. Popis včetně sešitu v Microsoft Excel ke stažení zdarma.
Nejprve si připravte zdrojovou tabulku. Z této tabulky pak automaticky vyselektujete (vyberete) jedinečná data (z TT, TT, TT, AA, AA, AA, bude jen TT, AA). V ukázce bude použita následující tabulka.
Data všechna | Jedinečná data |
---|---|
AA | AA |
AA | BB |
BB | CC |
CC | DD |
CC | |
DD | |
DD | |
DD | |
AA | |
AA |
Ukázka jak tabulky vypadá v Microsoft Excelu.
Předpokládám, že máte připravenou výše uvedenou tabulku. A máte za sebou teoretické úvody funkci POZVYHLEDAT, INDEX.
Mate-li tabulku. Vložte následující vzorec do buňky B1 jako matici, tj. místo Enter stisknout Ctrl + Shift + Enter vzorec se Vám uzavře do složených závorek { } a vypíše první unikátní hodnotu.
=INDEX($A$2:$A$20; POZVYHLEDAT(0; COUNTIF($B$1:B1; $A$2:$A$20); 0))
Protože se jedná o maticová vzorec nezapomenout na Ctrl + Shift + Enter. Poté klasicky rozkopírovat na další buňky.
Pokud již nejsou k dispozici jedinečné hodnoty, vzorec zobrazí chybovou hodnotu #N/A. Což lze ošetřit přidáním funkce IFERROR:
IFERROR(INDEX($A$2:$A$20;POZVYHLEDAT(0;COUNTIF($B$1:B1;$A$2:$A$20);0));"")
Pokud máte oblast $A$2:$A$20 pojmenovanou List můžete si zápis zkrátit:
IFERROR(INDEX(List;POZVYHLEDAT(0;COUNTIF($B$1:B1;List);0));"")
Pokusný vzorec můžete zadat do dalšího sloupce (C) ať vidíte rozdíl. Opět jde o maticové vzorce, takže nezapomenout Ctrl + Shift + Enter.
Nedařili se Vám tento soubor podle mého popisu vytvořit. Můžete si jej zdarma stáhnout. Soubor
Jedinečná unikátní data - jak získat z tabulky
ke stažení zdarma. Soubor je testován v MS Excel 2010.
Získávat jedinečná data lze i jinými způsoby. Například:
Poznámka: Jak na funkci COUNTIF jsem popsal v článku: COUNTIF - Funkce statistické - Excel.
Pro alternativní řešení lze využít i kontingenční tabulku, kde lze tento jedinečný seznam včetně počtu vytvořit daleko rychleji.
Dalším řešením je využít podmíněné formátování, kde vyberete z karty Domů - Podmíněné formátování - Zvýraznit pravidla podle buněk - Duplicitní hodnoty.
Nebo-li jak na omezení funkce SVYHLEDAT, u které musí být referenční sloupec jako první a ve vaši tabulce je referenční sloupec na jiném místě.
=INDEX(F:F;POZVYHLEDAT(A6;G:G;0))
Podrobněji o funkci SVYHLEDAT (VLOOKUP)
Soubor
Jak nahradit/obejít omezení funkce SVYHLEDAT
ke stažení zdarma. Soubor je testován v MS Excel 2010.
Jak pomocí funkce INDEX zjistit jaká hodnota je v buňce na průsečíku zvoleného řádku a sloupce. Zadává se číslo řádku a sloupce.
Pokud neznáte číslo řádku (sloupce), lze funkci INDEX doplnit o POZVYHLEDAT a řešení je hotovo. V další kapitole je praktické řešení.
=INDEX(B4:D9;C12;C13)
Soubor
Jak najít co je v průsčíku s funkci INDEX
- zadává se číslo řádku sloupce. Ke stažení zdarma.
Jak pomocí funkce INDEX a POZVYHLEDAT zjistit jaká hodnota je v buňce na průsečíku zvoleného řádku a sloupce. Informace o řádku (sloupci) se zadává jako hodnota záhlaví těchto sloupců. V předchozí kapitole popsáno, jak vyřešit pokud je známo číslo řádku a sloupce z dané tabulky.
=INDEX(C5:G9;POZVYHLEDAT(E13;B5:B9;0);POZVYHLEDAT(E12;C4:G4;0))
Soubor
Jak najít co je v průsčíku s funkci INDEX a POZVYHLEDAT
pokud je známa hodnota v záhlaví sloupce (řádku), ke stažení zdarma.
Jak pomocí funkce INDEX vyhledávat (maximum, minimum), spočítat (sumu, průměr) v zadané oblasti. Například, když se zvětšuje s přibývajicími daty. Tato úloha lze řešit i ve spojení s funkcí POSUN.
=SUMA(C4:INDEX(C4:E6;H5;H7))
=MAX(C4:INDEX(C4:E6;H5;H7))
=PRŮMĚR(C4:INDEX(C4:E6;H5;H7))
=MIN(C4:INDEX(C4:E6;H5;H7))
Poznámka: Jako maximum pro oblast lze využít i funkci POČET, POČET2.
Soubor
Jak najít spočítat (SUMA, PRŮMĚR, MIN, MAX) v oblasti pomocí INDEX
pokud je známa hodnota v záhlaví sloupce (řádku), ke stažení zdarma.
Tato část textu je v přípravě.
Do komentářů můžete doplnit vaše způsoby využití funkcí INDEX a POZVYHLEDAT.
Článek byl aktualizován: 19.09.2020 10:57
Pomohl vám článek? Vyřešili jste problém? Můžete mě podpořit zakoupení tabulky (samozdřejmě čokoládové), když kafe nepiji ;) Odkaz na zakoupení čokolády. Za veškerou podporu vám děkuji a samozdřejmě jí využiji do zdokonalování a rozšířování webu.
Případně přidejte odkaz na vaši oblíbenou sociální síť, případně využijste hashtag #JakNaExcel .
Děkuji za váš čas a doufám, že jste nalezli odpověď na svůj problém.
Narazili jste v článku na nejasnost, chybu? Máte tip na vylepšení nebo doplnění článku? Budu rád pokud se zmínite v komentářích.
Microsoft Office (Word, Excel, Google tabulky, PowerPoint) se věnuji od roku 2000 (od dubna roku 2004 na této doméně) - V roce 2017 jsem od Microsoft získal prestižní ocenění MVP (zatím 8x za sebou). Své vědomosti a zkušenosti dávám k dispozici i on-line ve videích pro SEDUO. Ve firmách školím a konzultuji, učím na MUNI. Tento web již tvořím přes 20 let (o Excel píší přes 25). Zdarma je zde přes 1.500 návodu, tipů a triků, včetně přes 350 různých šablon, sešitů a přes 70 taháků v pdf.
Pomohl Vám návod? Sdílejte na Facebooku, G+ |
||
LinkedIn... |
Stránky o MS Office (Excel) produktu společnosti Microsoft. Neslouží jako technická podpora.
| Email na autora: pavel.lasak@gmail.com | Copyright © : Pavel Lasák 2004 - 2025 |