Vyhledávání hodnot pomocí funkce SVYHLEDAT, INDEX nebo POZVYHLEDAT

Tip: Zkuste použít nové funkce XLOOKUP a XMATCH, vylepšené verze funkcí popsaných v tomto článku. Tyto nové funkce fungují v libovolném směru a ve výchozím nastavení vracejí přesné shody, takže jsou jednodušší a pohodlnější než jejich předchůdci.

Předpokládejme, že máte seznam čísel míst v kanceláři a potřebujete vědět, kteří zaměstnanci jsou v každé kanceláři. Tabulka je obrovská, takže si můžete myslet, že je to náročný úkol. S vyhledávací funkcí je to vlastně docela snadné.

Funkce SVYHLEDAT a VVYHLEDAT společně s funkcemi INDEX a MATCHjsou některé z nejužitečnějších funkcí v Excelu.

Poznámka: Funkce Průvodce vyhledáváním už není v Excelu dostupná.

Tady je příklad použití funkce SVYHLEDAT.

=SVYHLEDAT(B2;C2:E7;3;PRAVDA)

V tomto příkladu je B2 první argument– prvek dat, který funkce potřebuje k práci. U funkce SVYHLEDAT je prvním argumentem hodnota, kterou chcete najít. Tento argument může být odkaz na buňku nebo pevná hodnota, například "novák" nebo 21 000. Druhým argumentem je oblast buněk C2-:E7, ve které se má hledat hodnota, kterou chcete najít. Třetím argumentem je sloupec v této oblasti buněk, který obsahuje hodnotu, kterou hledáte.

Čtvrtý argument je nepovinný. Zadejte hodnotu PRAVDA nebo NEPRAVDA. Pokud zadáte hodnotu PRAVDA nebo necháte argument prázdný, vrátí funkce přibližnou shodu hodnoty, kterou zadáte v prvním argumentu. Pokud zadáte HODNOTU NEPRAVDA, bude se funkce shodovat s hodnotou zadanou prvním argumentem. Jinými slovy, ponechání čtvrtého argumentu prázdného (nebo zadáním hodnoty PRAVDA) vám poskytne větší flexibilitu.

Teď si na příkladu ukážeme, jak funkce funguje. Když zadáte hodnotu do buňky B2 (první argument), funkce SVYHLEDAT prohledá buňky v oblasti C2:E7 (druhý argument) a vrátí nejbližší přibližnou shodu od třetího sloupce v oblasti, sloupce E (3. argument).

Typické použití funkce SVYHLEDAT

Čtvrtý argument je prázdný, takže funkce vrátí přibližnou shodu. Kdybychom chtěli čtvrtý argument vyplnit, bylo by nutné zadat některou z hodnot ve sloupci C nebo D, jinak by vyhledávání nic nenašlo.

Pokud vám funkce SVYHLEDAT pomůže, funkce VVYHLEDAT se stejně snadno používá. Zadáte stejné argumenty, ale vyhledá se v řádcích místo sloupců.

Použití funkcí INDEX a MATCH místo funkce SVYHLEDAT

Používání funkce SVYHLEDAT má určitá omezení – funkce SVYHLEDAT může hledat jenom hodnotu zleva doprava. To znamená, že sloupec obsahující vyhledávací hodnotu by měl být vždy umístěný nalevo od sloupce obsahujícího vrácenou hodnotu. Pokud teď tabulka není vytvořená tímto způsobem, nepoužívejte funkce SVYHLEDAT. Místo toho použijte kombinaci funkcí INDEX a MATCH.

Tento příklad ukazuje malý seznam, ve kterém hodnota, kterou chceme hledat, Chicago, není v levém sloupci. Nemůžeme tedy použít svyhledat. Místo toho použijeme funkci MATCH k vyhledání Chicaga v oblasti B1:B11. Nachází se v řádku 4. Index pak použije tuto hodnotu jako vyhledávací argument a vyhledá základní soubor pro Chicago ve 4. sloupci (sloupci D). Použitý vzorec se zobrazí v buňce A14.

Vyhledání hodnoty pomocí funkcí INDEX a POZVYHLEDAT

Další příklady použití funkcí INDEX a MATCH místo funkce SVYHLEDAT najdete v článku https://www.mrexcel.com/excel-tips/excel-vlookup-index-match/ bill jelen, MVP společnosti Microsoft.

Vyzkoušejte si to

Pokud chcete experimentovat s vyhledávacími funkcemi před jejich vyzkoušet s vlastními daty, tady jsou ukázková data.

Svyhledat – příklad v práci

Zkopírujte následující data do prázdné tabulky.

Tip: Před vložením dat do Excelu nastavte šířky sloupců A až C na 250 pixelů a klikněte na Zalamovat text(karta Domů, skupina Zarovnání).

Hustota

Viskozita

Teplota

0,457

3.55

500

0,525

3,25

400

0,606

2,93

300

0,675

2,75

250

0,746

2,57

200

0,835

2,38

150

0,946

2,17

100

1,09

1,95

50

1,29

1,71

0

Vzorec

Popis

Výsledek

=SVYHLEDAT(1;A2:C10;2)

Hledá hodnotu 1 ve sloupci A při použití přibližné shody, najde ve sloupci A nejvyšší hodnotu menší než nebo rovnou 1, což je 0,946, a potom vrátí hodnotu ze sloupce B na stejném řádku.

2,17

=SVYHLEDAT(1;A2:C10;3;PRAVDA)

Hledá hodnotu 1 ve sloupci A při použití přibližné shody, najde ve sloupci A nejvyšší hodnotu menší než nebo rovnou 1, což je 0,946, a potom vrátí hodnotu ze sloupce C na stejném řádku.

100

=SVYHLEDAT(0,7;A2:C10;3;NEPRAVDA)

Hledá hodnotu 0,7 ve sloupci A při použití přesné shody. Vzhledem k tomu, že sloupec A takovou hodnotu neobsahuje, je vrácena chyba.

#N/A

=SVYHLEDAT(0,1;A2:C10;2;PRAVDA)

Hledá hodnotu 0,1 ve sloupci A při použití přibližné shody. Protože hodnota 0,1 je menší než nejmenší hodnota ve sloupci A, vrátí se chyba.

#N/A

=SVYHLEDAT(2;A2:C10;2;PRAVDA)

Hledá hodnotu 2 ve sloupci A při použití přibližné shody, najde ve sloupci A nejvyšší hodnotu menší než nebo rovnou 2, což je 1,29, a potom vrátí hodnotu ze sloupce B na stejném řádku.

1,71

VVYVYVYTÁPĚT – příklad

Zkopírujte všechny buňky v této tabulce a vložte je do buňky A1 v prázdném excelovém sešitě.

Tip: Před vložením dat do Excelu nastavte šířky sloupců A až C na 250 pixelů a klikněte na Zalamovat text(karta Domů, skupina Zarovnání).

Auta

Bagry

Buldozery

4

4

9

5

7

10

6

8

11

Vzorec

Popis

Výsledek

=VVYHLEDAT("Auta";A1:C4;2;PRAVDA)

Vyhledá "Auta" v řádku 1 a vrátí hodnotu z řádku 2, která je ve stejném sloupci (sloupec A).

4

=VVYHLEDAT("Bagry";A1:C4;3;NEPRAVDA)

Vyhledá "Bagry" v řádku 1 a vrátí hodnotu z řádku 3, která je ve stejném sloupci (sloupec B).

7

=VVYHLEDAT("B";A1:C4;3;PRAVDA)

Vyhledá "B" v řádku 1 a vrátí hodnotu z řádku 3, která je ve stejném sloupci. Protože se nenašla přesná shoda, použila se největší hodnota na řádku 1, která je menší než "B": "Auta", ve sloupci A.

5

=VVYHLEDAT("Buldozery";A1:C4;4)

Vyhledá "Buldozery" v řádku 1 a vrátí hodnotu z řádku 4, která je ve stejném sloupci (sloupec C).

11

=VVYHLEDAT(3; {1,2,3;"a","b","c";"d","e","f"}; 2; PRAVDA)

Vyhledá číslo 3 v třířádkové maticové konstantě a vrátí hodnotu z řádku 2 ve stejném sloupci (v tomto případě třetím). V této maticové konstantě jsou tři řádky hodnot oddělené od sebe středníkem (;). Protože se “c” našlo v řádku 2 a ve stejném sloupci jako 3, vrátí hodnotu “c”.

c

Příklady INDEX a SHODA

Tento poslední příklad používá funkce INDEX a MATCH společně k vrácení prvního čísla faktury a odpovídajícího data pro každé z pěti měst. Vzhledem k tomu, že je datum vráceno jako číslo, používáme funkci TEXT k jeho formátování jako datum. Funkce INDEX potom výsledek funkce POZVYHLEDAT používá jako argument. Kombinace funkcí INDEX a POZVYHLEDAT se v každém vzorci použije dvakrát – za prvé k vrácení čísla faktury a za druhé k vrácení data.

Zkopírujte všechny buňky v této tabulce a vložte je do buňky A1 v prázdném excelovém sešitě.

Tip: Před vložením dat do Excelu nastavte šířky sloupců A až D na 250 pixelů a klikněte na Zalamovat text(karta Domů, skupina Zarovnání).

Faktura

Město

Datum faktury

Nejnovější faktura podle města, s datem

3115

Plzeň

07.04.12

="Plzeň = "&INDEX($A$2:$C$33,POZVYHLEDAT("Plzeň",$B$2:$B$33,0),1)& ", Datum faktury: " & TEXT(INDEX($A$2:$C$33,POZVYHLEDAT("Plzeň",$B$2:$B$33,0),3),"m/d/rr")

3137

Plzeň

09.04.12

="Hodonín = "&INDEX($A$2:$C$33,POZVYHLEDAT("Hodonín",$B$2:$B$33,0),1)& ", Datum faktury: " & TEXT(INDEX($A$2:$C$33,POZVYHLEDAT("Hodonín",$B$2:$B$33,0),3),"m/d/rr")

3154

Plzeň

11.04.12

="Domažlice = "&INDEX($A$2:$C$33,POZVYHLEDAT("Domažlice",$B$2:$B$33,0),1)& ", Datum faktury: " & TEXT(INDEX($A$2:$C$33,POZVYHLEDAT("Domažlice",$B$2:$B$33,0),3),"m/d/rr")

3191

Plzeň

21.04.12

="Nový Bydžov = "&INDEX($A$2:$C$33,POZVYHLEDAT("Nový Bydžov",$B$2:$B$33,0),1)& ", Datum faktury: " & TEXT(INDEX($A$2:$C$33,POZVYHLEDAT("Nový Bydžov",$B$2:$B$33,0),3),"m/d/rr")

3293

Plzeň

25.04.12

="Tábor = "&INDEX($A$2:$C$33,POZVYHLEDAT("Tábor",$B$2:$B$33,0),1)& ", Datum faktury: " & TEXT(INDEX($A$2:$C$33,POZVYHLEDAT("Tábor",$B$2:$B$33,0),3),"m/d/rr")

3331

Plzeň

27.04.12

3350

Plzeň

28.04.12

3390

Plzeň

01.05.12

3441

Plzeň

02.05.12

3517

Plzeň

08.05.12

3124

Hodonín

09.04.12

3155

Hodonín

11.04.12

3177

Hodonín

19.04.12

3357

Hodonín

28.04.12

3492

Hodonín

06.05.12

3316

Domažlice

25.04.12

3346

Domažlice

28.04.12

3372

Domažlice

01.05.12

3414

Domažlice

01.05.12

3451

Domažlice

02.05.12

3467

Domažlice

02.05.12

3474

Domažlice

04.05.12

3490

Domažlice

05.05.12

3503

Domažlice

08.05.12

3151

Nový Bydžov

09.04.12

3438

Nový Bydžov

02.05.12

3471

Nový Bydžov

04.05.12

3160

Tábor

18.04.12

3328

Tábor

26.04.12

3368

Tábor

29.04.12

3420

Tábor

01.05.12

3501

Tábor

06.05.12

Viz taky

Karta Rychlá reference:

(odkaz)

SVYHLEDAT

Potřebujete další pomoc?

Rozšiřte své dovednosti s Office
Projít školení
Získejte nové funkce jako první
Připojte se k účastníkům programu Office Insiders

Byly tyto informace užitečné?

Děkujeme vám za zpětnou vazbu.

Děkujeme vám za váš názor! Pravděpodobně bude užitečné, když vás spojíme s některým z našich agentů podpory Office.

×