Přeskočit navigaci | Přeskočit na novinky
     

Jste zde: Úvodní stránka » excel » funkce » index-pozvyhledat-vyber-unikatnich-dat

INDEX POZVYHLEDAT prakticky - Excel

Videokurzy Excel

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

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:

INDEX (INDEX)

Vrátí hodnotu nebo odkaz (index řádku, sloupce) na hodnotu z tabulky/oblasti.

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ů.

INDEX - data pro ukázkový příklad - Excel

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í

INDEX - ukázkový příklad - Excel

Ř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

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)
INDEX - ukázkový příklad - Excel

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 soubor ve formátu *.xlsx s využitím funkce INDEX.


POZVYHLEDAT (MATCH)

Vyhledá zadanou položku v oblasti buněk a vrátí relativní pozici dané položky v oblasti

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:

POZVYHLEDAT - data pro praktický příklad - Excel

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í

POZVYHLEDAT - ukázkový příklad - Excel

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

MS Excel 2010 - unikatní data

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 soubor ve formátu *.xlsx 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ě.

Jak nahradit SVYHLEDAT - Excel
=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 soubor ve formátu *.xlsx 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í.

Co je v průsečíku využitím INDEX
=INDEX(B4:D9;C12;C13)

Ukázka ke stažení

Soubor Jak najít co je v průsčíku s funkci INDEX soubor ve formátu *.xlsx - 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.

Co je v průsečíku využitím INDEX a POZVYHLEDAT
=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 soubor ve formátu *.xlsx 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.

INDEX - ve spojení se SUMA, PRŮMĚR, MIN, MAX
=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 soubor ve formátu *.xlsx 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ě.

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: 19.09.2020 10:57

Odměna

Ušetřil vám tento web čas, peníze? Pomohl vyřešit problém? Jste ochotni poskytnout symbolickou odměnu na další rozvoj? Vybrte si formu odměny, která vám vyhovuje.


Pavel Lasák - autor webu

Pavel Lasák

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ů.

   Pavel Lasák LinkedIn Profil    Pavel Lasák Google+ Profil    Pavel Lasák facebook Profil    Pavel Lasák twitter Profil


Komentáře

" ; // echo "kompletni_cesta :" ; // echo $adresar_pro_vkladani ; // echo "
" ; // echo " Tady bude možnost přidání komentáře" ; // echo "
" ; // echo "
" ; $kompletni_cesta = "komentare"; $adresar_pro_vkladani = $kompletni_cesta; ?> \n"; reset($polozky); while (list($key, $val) = each($polozky)) { if ($val != "." && $val != "..") { if (!is_dir($val)) { // echo "$val
\n"; $fp = FOpen ($adresar_pro_vkladani."/".$val, "r"); //otevře soubor book.dat pro čtení $data = FRead ($fp, FileSize($adresar_pro_vkladani."/".$val)); //přečte data ze souboru a uloží do proměnné "data" - kvůli tomu aby se zobrazoval poslední příspěvek nahoře FClose($fp); echo "$data"; } } } ?>




Excel


Sdílejte

Pomohl Vám návod?
Sdílejte na Facebooku, G+
LinkedIn...

Nové články


Reklama


TOPlist Licence Creative Commons webarchiv rss XML

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