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 jejich použití je jednodušší a pohodlnější než u jejich předchůdců.
Dejme tomu, že máte seznam čísel kanceláří a chcete zjistit, kteří zaměstnanci jsou v které 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 patří společně s funkcemi INDEX a POZVYHLEDAT k těm nejužitečnějším v Excelu.
Poznámka
V Excelu už není dostupná funkce Průvodce vyhledáváním.
Tady je příklad použití funkce SVYHLEDAT.
=SVYHLEDAT(B2;C2:E7;3;PRAVDA)
V tomto příkladu je B2 prvním argumentem – datovým prvkem, který funkce potřebuje k tomu, aby fungovala. Funkce SVYHLEDAT představuje jako první argument hodnotu, kterou chcete najít. Tímto argumentem může být odkaz na buňku nebo pevná hodnota, jako třeba Novák nebo 21 000. Druhý argument je oblast buněk C2-:E7, ve které chcete najít hodnotu. A třetí argument je sloupec v dané oblasti buněk, který obsahuje hodnotu, kterou hledáte.
Čtvrtý argument je volitelný. Zadejte hodnotu PRAVDA nebo NEPRAVDA. Pokud zadáte hodnotu PRAVDA nebo necháte argument prázdný, funkce vrátí přibližnou shodu s hodnotou zadanou v prvním argumentu. Pokud zadáte NEPRAVDA, funkce najde shodu s hodnotou uvedenou v prvním argumentu. Jinými slovy, když necháte čtvrtý argument prázdný (nebo zadáte PRAVDA), dostanete 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 (2. argument) a vrátí nejbližší přibližnou shodu ze třetího sloupce v oblasti, sloupec E (3. argument).
Čtvrtý argument je prázdný, takže funkce bude vracet i přibližné shody. 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.
Když se seznámíte s funkcí SVYHLEDAT, můžete také snadno používat funkci VVYHLEDAT. Budete zadávat stejné argumenty, ale místo ve sloupcích se budou hledat řádky.
Použití funkcí INDEX a POZVYHLEDAT místo SVYHLEDAT
Používání funkce SVYHLEDAT má určitá omezení – funkce SVYHLEDAT dokáže hledat hodnoty pouze zleva doprava. To znamená, že sloupec obsahující hledanou hodnotu by měl být vždycky umístěný nalevo od sloupce obsahujícího vrácenou hodnotu. Když teď vaše tabulka není takto vytvořená, pak funkci SVYHLEDAT nepoužívejte. Místo nich použijte kombinaci funkcí INDEX a POZVYHLEDAT.
Tento příklad ukazuje malý seznam s hodnotou, kterou chceme hledat, tedy Chicago, která není ve sloupci úplně vlevo. Nemůžeme tedy použít funkci SVYHLEDAT. Místo toho pomocí funkce POZVYHLEDAT najdeme Chicago v oblasti B1:B11. Nachází se na řádku 4. Funkce INDEX pak tuto hodnotu použije jako vyhledávací argument a ve čtvrtém sloupci (sloupec D) najde počet obyvatel pro Chicago. Použitý vzorec je zobrazený v buňce A14.
Vyzkoušejte si to
Pokud chcete s vyhledávacími funkcemi napřed experimentovat a teprve potom si je vyzkoušet na vlastních datech, tady jsou některá ukázková data.
Příklad funkce SVYHLEDAT v praxi
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. | #NENÍ_K_DISPOZICI |
| =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. | #NENÍ_K_DISPOZICI |
| =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 VVYHLEDAT
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 funkcí INDEX a POZVYHLEDAT
V tomto posledním příkladu jsou společně použity funkce INDEX a POZVYHLEDAT k vrácení čísla nejnovější faktury a odpovídajícího data pro pět měst. Protože se datum vrací jako číslo, naformátujeme ho jako datum pomocí funkce HODNOTA.NA.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 |
Viz také
Stručná referenční karta: Funkce SVYHLEDAT – shrnutí
Vyhledávací funkce a funkce pro odkazy (odkazy)