Tweet

INDEX POZVYHLEDAT prakticky - Excel

Obsah a cíl článku

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:

• Funkce INDEX (INDEX)
  • Teoretický popis
  • Praktické použití INDEX
  • Poslední záznam ve sloupci - pomocí INDEX
  • Hodnota v řádku/sloupci vybrané oblasti - pomocí INDEX
• Funkce POZVYHLEDAT (MATCH)
  • Teoretický popis
  • Praktické použití POZVYHLEDAT
• Praktické užití spojení funkcí INDEX a POZVYHLEDAT
  • Jak získat jedinečné data z tabulky Na praktickém příkladu ukázat použití funkcí INDEX, POZVYHLEDAT pro zjištění unikátních dat
  • Další možnosti při hledání jedinečných dat (podmíněné formátování)
  • Jak nahradit SVYHLEDAT - například pokud prohledávaný (referenční) sloupec není prvním (SVYHLEDAT nedokáže)
  • INDEX hodnota co je v průsečíku daného řádku a sloupce - nutno zadat číslo řádku (sloupce)
  • INDEX POZVYHLEDAT hodnota je v průsečíku daného řádku a sloupce, když je známa hodnota sloupce (řádku), ne jeho pozice)
  • INDEX - ve spojení se SUMA, PRŮMĚR, MIN, MAX hledání, výpočet v zadané oblasti, která se může dynamicky měnit
• Ostatní
  • Související články k POZVYHLEDAT a INDEX

INDEX (INDEX)

Možné způsoby použití funkce

Funkce INDEX (INDEX) může být použita dvěma způsoby:

• 1. Odkazová forma
• 2. Maticová forma

1. Odkazová forma

Vrátí adresu buňky ležící v průsečíku zadaného řádku a sloupce.

INDEX(odkaz;řádek;sloupec;oblast)

• Odkaz - na oblast (jednu či více)
• Řádek - určuje řádek průsečíku
• Sloupec - určuje sloupec průsečíku
• Oblast - určuje oblast, ve které má ležet průsečík - nesouvislé oblasti se závorkami (A1:C4;G10:J15;K41:L44)

2. Maticová forma

Hodnotu prvku tabulky nebo matice označeného indexem řádku a sloupce.

INDEX(pole;řádek;sloupec)

• Pole - je oblast buněk (nebo maticová konstanta)
• Řádek - určuje řádek pole
• Sloupec - určuje sloupec pole

Musí být zadán jeden z argumentu řádek nebo sloupec.

INDEX - prakticky

Praktické použití funkce INDEX. Z tabulky potřebujete zjistit, jaká hodnota se nachází v požadovaném řádku (sloupci).

Data

Připravte si tabulky (v první dopravní prostředky) a v druhé popis sloupců.

Potřebujeme zjistit z první tabulky:

• Co je na řádku 1
• Co je na řádku 4

Potřebujeme zjistit z druhé tabulky:

• Co je ve sloupci 2
• Co je ve sloupci 3

Řešení

Ř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)

Poslední záznam ve sloupci

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.

Možné řešení

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)

Hodnota v řádku/sloupci vybrané oblasti

Máte označenou oblast a potřebujete vypsat hodnotu, která se nachází ve vybraném průsečíku řádku x sloupce.

Poznámka

Podobná funkce je pod názvem POSUN podrobněji v samostatném článku: POSUN - (OFFSET) - funkce Excel .

Možné řešení

=INDEX(B4:D9;C12;C13)

Ukázka ke stažení

Soubor Hodnota v řádku/sloupci vybrané oblasti s využitím funkce INDEX.

POZVYHLEDAT (MATCH)

Syntaxe

POZVYHLEDAT(co; prohledat; [shoda])

• co - hodnotu, kterou chcete nalézt
• prohledat - kde se bude hledat, oblast buněk
• shoda - (-1, 0, 1)
  • 1 (nebo není neuveden) - najde největší hodnotu, která je menší nebo rovna hledané hodnotě (hodnoty řazeny vzestupně ...-2; -1; 0; 1; 2; ...; A–Z; NEPRAVDA; PRAVDA)
  • 0 - první hodnotu, která se přesně shoduje s hledanou hodnotou co
  • -1 - najde nejmenší hodnotu, která je větší nebo rovna hledané hodnotě (musí být řazeno sestupně

Praktický příklad na POZVYHLEDAT (MATCH)

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:

• Na kterém řádku se nachází výrobek: Banán
• Na kterém řádku se nachází cena 15
• Na kterém řádku se nachází cena 30

Řešení

Ř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).

Jak získat jedinečná data z tabulky?

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.

Ukázková data

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

V Microsoft Excelu

Ukázka jak tabulky vypadá v Microsoft Excelu.

Jak na výběr dat - Praktické řešení

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.

Ukázka ke stažení

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.

Další možnosti při hledání jedinečných dat

Získávat jedinečná data lze i jinými způsoby. Například:

Pomocí funkce COUNTIF

Poznámka: Jak na funkci COUNTIF jsem popsal v článku: COUNTIF - Funkce statistické - Excel.

Kontingenční tabulka

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.

Podmíněné formátování

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.

Jak nahradit SVYHLEDAT

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))

Poznámka

Podrobněji o funkci SVYHLEDAT (VLOOKUP)

Ukázka ke stažení

Soubor Jak nahradit/obejít omezení funkce SVYHLEDAT ke stažení zdarma. Soubor je testován v MS Excel 2010.

INDEX co je v průsečíku

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)

Ukázka ke stažení

Soubor Jak najít co je v průsčíku s funkci INDEX - zadává se číslo řádku sloupce. Ke stažení zdarma.

INDEX POZVYHLEDAT hodnota je v průsečíku

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))

Ukázka ke stažení

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.

INDEX - ve spojení se SUMA, PRŮMĚR, MIN, MAX

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.

Ukázka ke stažení

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.

Související články k POZVYHLEDAT a INDEX

Tato část textu je v přípravě.

• Vyhledávací funkce v Excel aneb seznam všech vyhledávacích funkcí, které jsou součásti Microsoft Excel
• SVYHLEDAT (VLOOKUP) - funkce Excel včetně praktických ukázek
• POSUN včetně praktických ukázek ke stažení zdarma

Závěrem k INDEX a POZVYHLEDAT

Do komentářů můžete doplnit vaše způsoby využití funkcí INDEX a POZVYHLEDAT.

Článek byl aktualizován: 05.07.2015 13:04

Pavel Lasák - autor webu

Microsoft Office (Word, Excel, Google tabulky, PowerPoint) se věnuji od roku 2000 (od 2004 na této doméně) - V roce 2017 jsem od Microsoft získal prestižní ocenění MVP (zatím jsem jediný z ČR v kategorií Excel). 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 15 let. Zdarma je zde přes 1.000 návodu, tipů a triků, včetně přes 250 různých šablon, sešitů.

           

Komentáře