Tip: Skúste použiť nové funkcie XLOOKUP a XMATCH , vylepšené verzie funkcií popísaných v tomto článku. Tieto nové funkcie fungujú v ľubovoľnom smere a na základe predvoleného nastavenia vrátia presné výsledky, vďaka čomu sú jednoduchšie a pohodlnejšie na používanie ako ich predchodcovia.
Predpokladajme, že máte zoznam čísiel umiestnení balíka Office a potrebujete vedieť, ktorí zamestnanci sú v jednotlivých Office. Tabuľkový hárok je obrovský, takže si možno myslíte, že je náročná úloha. Je to vlastne celkom jednoduché s funkciou lookup.
Funkcie VLOOKUP a HLOOKUP spolu s indexom a zhodoupredstavujú niektoré z najužitočnejších funkcií v Exceli.
Poznámka: Funkcia Sprievodca vyhľadávaním už nie je v Exceli k dispozícii.
Tu je príklad používania funkcie VLOOKUP.
=VLOOKUP(B2,C2:E7,3,TRUE)
V tomto príklade je bunka B2 prvým argumentom– prvkom údajov, ktorý funkcia potrebuje na prácu. V prípade funkcie VLOOKUP je tento prvý argument hodnotou, ktorú chcete vyhľadať. Tento argument môže byť odkaz na bunku alebo stála hodnota, napríklad "Smith" alebo 21 000. Druhým argumentom je rozsah buniek, C2-: E7, v ktorom sa má vyhľadať hodnota, ktorú chcete vyhľadať. Tretí argument je stĺpec v rozsahu buniek, ktorý obsahuje požadovanú hodnotu.
Štvrtý argument je voliteľný. Zadajte hodnotu TRUE alebo FALSe. Ak zadáte hodnotu TRUE alebo necháte argument prázdny, funkcia vráti približnú zhodu hodnoty zadanej v prvom argumente. Ak zadáte hodnotu FALSE, funkcia vyhľadá zhodu hodnoty uvedenej v prvom argumente. Inými slovami, ponechanie štvrtého argumentu prázdne – alebo zadanie hodnoty TRUE – vám poskytuje väčšiu flexibilitu.
Tento príklad znázorňuje, ako funkcia funguje. Keď zadáte hodnotu do bunky B2 (prvý argument), funkcia VLOOKUP vyhľadá bunky v rozsahu C2: E7 (2. argument) a vráti najbližšiu približnú zhodu z tretieho stĺpca v rozsahu, stĺpci E (3. argument).
Štvrtý argument je prázdny, takže funkcia vráti približnú zhodu. Ak by sa tak nestalo, na získanie nejakého výsledku by bolo potrebné zadať jednu z hodnôt v stĺpcoch C alebo D.
Keď budete spokojní s funkciou VLOOKUP, funkcia HLOOKUP sa rovnako jednoducho používa. Zadáte rovnaké argumenty, ale namiesto stĺpcov sa vyhľadáva v riadkoch.
Používanie funkcií INDEX a MATCH namiesto funkcie VLOOKUP
Pri používaní funkcie VLOOKUP sa používajú určité obmedzenia – funkcia VLOOKUP môže vyhľadať len hodnotu zľava doprava. To znamená, že stĺpec obsahujúci hodnotu, ktorú hľadáte, by sa mal vždy nachádzať naľavo od stĺpca obsahujúceho vrátenú hodnotu. Ak tabuľkový hárok nie je vytvorený týmto spôsobom, nepoužívajte funkciu VLOOKUP. Namiesto toho použite kombináciu funkcií INDEX a MATCH.
V tomto príklade sa zobrazuje malý zoznam, v ktorom je hodnota, ktorú chcete hľadať, v Chicagu, nie je v stĺpci úplne vľavo. Funkcia VLOOKUP sa preto nedá použiť. Namiesto toho použijeme funkciu MATCH na nájdenie Chicaga v rozsahu B1: B11. Nachádza sa v riadku 4. Potom INDEX použije túto hodnotu ako argument vyhľadávania a nájde obyvateľstvo pre Chicago v štvrtom stĺpci (stĺpec D). Použitý vzorec sa zobrazuje v bunke A14.
Ďalšie príklady použitia funkcií INDEX a MATCH namiesto funkcie VLOOKUP nájdete v článku https://www.MrExcel.com/Excel-Tips/Excel-VLOOKUP-index-Match/ by Bill jelen, Microsoft MVP.
Skúste to
Ak chcete experimentovať s vyhľadávacími funkciami skôr, než ich vyskúšate s vlastnými údajmi, tu je niekoľko vzorových údajov.
Príklad funkcie VLOOKUP v práci
Skopírujte nasledujúce údaje do prázdneho tabuľkového hárka.
Tip: Pred prilepením údajov do Excelu nastavte šírku stĺpcov A až C na 250 pixlov a kliknite na položku Zalomiť text (karta Domov, skupina Zarovnanie).
|
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ýsledok |
|
=VLOOKUP(1;A2:C10;2) |
Hľadá približnú zhodu k hodnote 1 v stĺpci A, nájde najväčšiu hodnotu menšiu alebo rovnú 1 v stĺpci A, ktorá je 0,946, a potom vráti hodnotu zo stĺpca B v rovnakom riadku. |
2,17 |
|
=VLOOKUP(1;A2:C10;3;TRUE) |
Hľadá približnú zhodu k hodnote 1 v stĺpci A, nájde najväčšiu hodnotu menšiu alebo rovnú 1 v stĺpci A, ktorá je 0,946, a potom vráti hodnotu zo stĺpca C v rovnakom riadku. |
100 |
|
=VLOOKUP(0,7;A2:C10;3;FALSE) |
Hľadá úplnú zhodu k hodnote 0,7 v stĺpci A. Pretože stĺpec A úplne zhodnú hodnotu neobsahuje, vráti sa chyba. |
#NEDOSTUPNÝ |
|
=VLOOKUP(0,1;A2:C10;2;TRUE) |
Hľadá približnú zhodu k hodnote 0,1 v stĺpci A. Pretože hodnota 0,1 je menšia ako najmenšia hodnota v stĺpci A, vráti sa chyba. |
#NEDOSTUPNÝ |
|
=VLOOKUP(2;A2:C10;2;TRUE) |
Hľadá približnú zhodu k hodnote 2 v stĺpci A, nájde najväčšiu hodnotu menšiu alebo rovnú 2 v stĺpci A, ktorá je 1,29, a potom vráti hodnotu zo stĺpca B v rovnakom riadku. |
1,71 |
Príklad HLOOKUP
Skopírujte všetky bunky v tejto tabuľke a vložte ich do bunky A1 prázdneho hárka v Exceli.
Tip: Pred prilepením údajov do Excelu nastavte šírku stĺpcov A až C na 250 pixlov a kliknite na položku Zalomiť text (karta Domov, skupina Zarovnanie).
|
Nápravy |
Ložiská |
Skrutky |
|
4 |
4 |
9 |
|
5 |
7 |
10 |
|
6 |
8 |
11 |
|
Vzorec |
Popis |
Výsledok |
|
=HLOOKUP("Nápravy"; A1:C4; 2; TRUE) |
Vyhľadá výraz „Nápravy" v prvom riadku a vráti hodnotu z druhého riadka, ktorý sa nachádza v tom istom stĺpci (stĺpci A). |
4 |
|
=HLOOKUP("Ložiská"; A1:C4; 3; FALSE) |
Vyhľadá výraz „Ložiská" v prvom riadku a vráti hodnotu z tretieho riadka, ktorý sa nachádza v tom istom stĺpci (stĺpci B). |
7 |
|
=HLOOKUP("B"; A1:C4; 3; TRUE) |
Vyhľadá hodnotu B v riadku 1 a vráti hodnotu z riadka 3 toho istého stĺpca. Vzhľadom na to, že presná zhoda sa nenašla, použije sa najväčšia hodnota v prvom riadku, ktorá je súčasne menšia ako hodnota B: Nápravy v stĺpci A |
5 |
|
=HLOOKUP("Svorníky"; A1:C4; 4) |
Vyhľadá výraz „Svorníky" v prvom riadku a vráti hodnotu zo štvrtého riadka, ktorý sa nachádza v tom istom stĺpci (stĺpci C). |
11 |
|
=HLOOKUP(3; {1,2,3;"a","b","c";"d","e","f"}; 2; TRUE) |
Vyhľadá číslo 3 v trojriadkovej konštante poľa a vráti hodnotu z druhého riadka toho istého (v tomto prípade tretieho) stĺpca. V konštante poľa sa nachádzajú tri riadky s hodnotami, jednotlivé riadky sú oddelené bodkočiarkou (;). Keďže hodnota c sa nachádza v druhom riadku a v tom istom stĺpci ako číslo 3, vráti sa hodnota c. |
c |
Príklady INDEXov a ZHÔD
Tento posledný príklad využíva funkcie INDEX a MATCH spolu na vrátenie najskoršieho čísla faktúry a jeho zodpovedajúceho dátumu pre každý z piatich miest. Keďže dátum sa vráti ako číslo, pomocou funkcie TEXT sa toto číslo naformátuje ako dátum. Funkcia INDEX používa výsledok funkcie MATCH ako svoj argument. Kombinácia funkcií INDEX a MATCH sa v každom vzorci použije dvakrát, najskôr na vrátenie čísla faktúry, potom na vrátenie dátumu.
Skopírujte všetky bunky v tejto tabuľke a vložte ich do bunky A1 prázdneho hárka v Exceli.
Tip: Pred prilepením údajov do Excelu nastavte šírku stĺpcov A až C na 250 pixlov a kliknite na položku Zalomiť text (karta Domov, skupina Zarovnanie).
|
Faktúra |
Mesto |
Dátum vystavenia faktúry |
Najstaršia faktúra podľa mesta s uvedením dátumu |
|
3115 |
Brezno |
07.04.12 |
="Brezno = "&INDEX($A$2:$C$33;MATCH("Brezno";$B$2:$B$33;0);1)& ", Dátum vystavenia faktúry: " & TEXT(INDEX($A$2:$C$33;MATCH("Brezno";$B$2:$B$33;0);3);"d/m/rr") |
|
3137 |
Brezno |
09.04.12 |
="Bernolákovo = "&INDEX($A$2:$C$33;MATCH("Bernolákovo";$B$2:$B$33;0);1)& ", Dátum vystavenia faktúry: " & TEXT(INDEX($A$2:$C$33;MATCH("Bernolákovo";$B$2:$B$33;0);3);"d/m/rr") |
|
3154 |
Brezno |
11.04.12 |
="Dudince = "&INDEX($A$2:$C$33;MATCH("Dudince";$B$2:$B$33;0);1)& ", Dátum vystavenia faktúry: " & TEXT(INDEX($A$2:$C$33;MATCH("Dudince";$B$2:$B$33;0);3);"d/m/rr") |
|
3191 |
Brezno |
21.04.12 |
="Nové Zámky = "&INDEX($A$2:$C$33;MATCH("Nové Zámky";$B$2:$B$33;0);1)& ", Dátum vystavenia faktúry: " & TEXT(INDEX($A$2:$C$33;MATCH("Nové Zámky";$B$2:$B$33;0);3);"d/m/rr") |
|
3293 |
Brezno |
25.04.12 |
="Trnava = "&INDEX($A$2:$C$33;MATCH("Trnava";$B$2:$B$33;0);1)& ", Dátum vystavenia faktúry: " & TEXT(INDEX($A$2:$C$33;MATCH("Trnava";$B$2:$B$33;0);3);"d/m/rr") |
|
3331 |
Brezno |
27.04.12 |
|
|
3350 |
Brezno |
28.04.12 |
|
|
3390 |
Brezno |
01.05.12 |
|
|
3441 |
Brezno |
02.05.12 |
|
|
3517 |
Brezno |
08.05.12 |
|
|
3124 |
Bernolákovo |
09.04.12 |
|
|
3155 |
Bernolákovo |
11.04.12 |
|
|
3177 |
Bernolákovo |
19.04.12 |
|
|
3357 |
Bernolákovo |
28.04.12 |
|
|
3492 |
Bernolákovo |
06.05.12 |
|
|
3316 |
Dudince |
25.04.12 |
|
|
3346 |
Dudince |
28.04.12 |
|
|
3372 |
Dudince |
01.05.12 |
|
|
3414 |
Dudince |
01.05.12 |
|
|
3451 |
Dudince |
02.05.12 |
|
|
3467 |
Dudince |
02.05.12 |
|
|
3474 |
Dudince |
04.05.12 |
|
|
3490 |
Dudince |
05.05.12 |
|
|
3503 |
Dudince |
08.05.12 |
|
|
3151 |
Nové Zámky |
09.04.12 |
|
|
3438 |
Nové Zámky |
02.05.12 |
|
|
3471 |
Nové Zámky |
04.05.12 |
|
|
3160 |
Trnava |
18.04.12 |
|
|
3328 |
Trnava |
26.04.12 |
|
|
3368 |
Trnava |
29.04.12 |
|
|
3420 |
Trnava |
01.05.12 |
|
|
3501 |
Trnava |
06.05.12 |
