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í vrací přesné shody, takže jejich používání je snazší a pohodlnější než jejich předchůdci.

Předpokládejme, že máte seznam čísel umístění kanceláře 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 se jedná o náročný úkol. Ve skutečnosti je to docela snadné s vyhledávací funkcí.

Funkce SVYHLEDAT a VVYHLEDAT jsou spolu s funkcemi INDEX a POZVYHLEDAT jedny 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ím argumentem – prvkem dat, který funkce potřebuje k fungování. U funkce SVYHLEDAT je prvním argumentem hodnota, kterou chcete najít. Tímto argumentem může být odkaz na buňku nebo pevná hodnota, například "smith" nebo 21 000. Druhým argumentem je oblast buněk C2-:E7, ve které se má vyhledat hodnota, kterou chcete najít. Třetím argumentem je sloupec v dané oblasti buněk, který obsahuje požadovanou hodnotu.

Čtvrtý argument je volitelný. Zadejte hodnotu PRAVDA nebo NEPRAVDA. Pokud zadáte HODNOTU PRAVDA nebo ponecháte argument prázdný, vrátí funkce přibližnou shodu s hodnotou, kterou zadáte v prvním argumentu. Pokud zadáte NEPRAVDA, bude funkce odpovídat hodnotě zadané prvním argumentem. Jinými slovy, když necháte čtvrtý argument prázdný – nebo zadáte hodnotu PRAVDA – získáte 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 ze třetího sloupce v oblasti, sloupce E (třetí 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 jste obeznámeni s funkcí SVYHLEDAT, je stejně snadné používat funkci VVYHLEDAT. Zadáte stejné argumenty, ale místo sloupců se prohledávají řádky.

Použití funkce INDEX a POZVYHLEDAT místo funkce SVYHLEDAT

Používání funkce SVYHLEDAT má určitá omezení – funkce SVYHLEDAT může vyhledat hodnotu jenom zleva doprava. To znamená, že sloupec obsahující hledanou hodnotu by se měl vždy nacházet nalevo od sloupce obsahujícího vrácenou hodnotu. Pokud teď vaše tabulka není sestavená tímto způsobem, nepoužívejte funkci SVYHLEDAT. Místo toho použijte kombinaci funkcí INDEX a POZVYHLEDAT.

Tento příklad ukazuje malý seznam, kde hodnota, kterou chceme hledat, Chicago, není ve sloupci úplně vlevo. Proto nemůžeme použít funkci SVYHLEDAT. Místo toho pomocí funkce POZVYHLEDAT vyhledáme Chicago v rozsahu B1:B11. Nachází se v řádku 4. Index pak použije danou hodnotu jako argument vyhledávání a najde základní soubor pro Chicago ve 4. sloupci (sloupec D). Použitý vzorec se zobrazí v buňce A14.

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

Další příklady použití funkce INDEX a POZVYHLEDAT místo funkce SVYHLEDAT najdete v článku https://www.mrexcel.com/excel-tips/excel-vlookup-index-match/ Billa Jelena, MVP Microsoftu.

Vyzkoušejte si to

Pokud chcete s vyhledávacími funkcemi experimentovat, než si je vyzkoušíte s vlastními daty, tady jsou ukázková data.

Příklad funkce SVYHLEDAT v práci

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

Tip: Před vložením dat do Excelu nastavte šířku 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

Příklad funkce SVYHLEDAT

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 šířku 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 POZVYHLEDAT

Tento poslední příklad používá funkce INDEX a POZVYHLEDAT společně, aby vrátily číslo nejstarší faktury a odpovídající datum pro každý z pěti měst. Vzhledem k tomu, že datum je vráceno jako číslo, naformátujeme ho jako datum pomocí funkce TEXT. 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 šířku 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

a odkazu

table_array

nástroji SVYHLEDAT

Potřebujete další pomoc?

Chcete další možnosti?

Prozkoumejte výhody předplatného, projděte si školicí kurzy, zjistěte, jak zabezpečit své zařízení a mnohem více.

Komunity vám pomohou klást otázky a odpovídat na ně, poskytovat zpětnou vazbu a vyslechnout odborníky s bohatými znalostmi.