Tip: Skúste použiť nové funkcie XLOOKUP a XMATCH , vylepšené verzie funkcií popísané v tomto článku. Tieto nové funkcie fungujú v ľubovoľnom smere a predvolene vracajú presné zhody, vďaka čomu sa používajú jednoduchšie a pohodlnejšie ako predchádzajúce funkcie.
Predpokladajme, že máte zoznam čísel miest kancelárie a potrebujete vedieť, ktorí zamestnanci sa nachádzajú v jednotlivých kanceláriách. Tabuľkový hárok je obrovský, takže si možno myslíte, že je to náročná úloha. S vyhľadávacou funkciou je to vlastne veľmi jednoduché.
Funkcie VLOOKUP a HLOOKUP spolu s funkciami INDEX a MATCH sú niektoré z najužitočnejších funkcií v Exceli.
Poznámka: Funkcia Sprievodcu 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 B2 prvým argumentom – prvkom údajov, ktorý funkcia potrebuje na fungovanie. V prípade funkcie VLOOKUP je tento prvý argument hodnotou, ktorú chcete nájsť. Tento argument môže byť odkaz na bunku alebo pevná hodnota, napríklad kováč alebo 21 000. Druhým argumentom je rozsah buniek C2 –:E7, v ktorom sa má vyhľadať hľadaná hodnota. Tretí argument je stĺpec v danom rozsahu buniek, ktorý obsahuje hľadanú 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, ak ponecháte štvrtý argument prázdny alebo zadáte hodnotu TRUE, získate väčšiu flexibilitu.
Tento príklad znázorňuje, ako funkcia funguje. Keď zadáte hodnotu do bunky B2 (prvý argument), funkcia VLOOKUP prehľadá bunky v rozsahu C2:E7 (2. argument) a vráti najbližšiu približnú zhodu z tretieho stĺpca v rozsahu, stĺpca 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.
Ak sa oboznámite s funkciou VLOOKUP, funkcia HLOOKUP sa rovnako jednoducho používa. Zadávajú sa rovnaké argumenty, ale vyhľadáva sa v riadkoch namiesto stĺpcov.
Použitie funkcií INDEX a MATCH namiesto funkcie VLOOKUP
Používanie funkcie VLOOKUP má určité obmedzenia – funkcia VLOOKUP dokáže vyhľadať iba hodnotu zľava doprava. Znamená to, že stĺpec obsahujúci hľadanú hodnotu 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.
Tento príklad znázorňuje malý zoznam, v ktorom sa hodnota, na ktorej chceme hľadať, Chicago, nenachádza v stĺpci úplne vľavo. Preto nemôžeme použiť funkciu VLOOKUP. Namiesto toho použijeme funkciu MATCH na vyhľadanie Chicaga v rozsahu B1:B11. Nachádza sa v riadku 4. Potom funkcia INDEX použije danú hodnotu ako argument vyhľadávania a nájde základný súbor pre Chicago v 4. stĺpci (stĺpci D). Použitý vzorec sa zobrazí v bunke A14.
Ďalšie príklady používania funkcií INDEX a MATCH namiesto funkcie VLOOKUP nájdete v článku , https://www.mrexcel.com/excel-tips/excel-vlookup-index-match/ Bill Jelen, MVP spoločnosti Microsoft.
Skúste to
Ak chcete experimentovať s vyhľadávacími funkciami predtým, 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. |
#N/A |
|
=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. |
#N/A |
|
=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 funkcie 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 funkcií INDEX a MATCH
V tomto poslednom príklade sa spoločne používajú funkcie INDEX a MATCH, aby sa vrátilo najskoršie číslo faktúry a príslušný dátum 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 |
