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