Applies ToExcel pre Microsoft 365 Excel pre web Excel 2024 Excel 2021 Excel 2019 Excel 2016

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

Typické použitie funkcie VLOOKUP

Š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.

Použitie funkcií INDEX a MATCH na vyhľadávanie hodnoty

Ď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

Stručná referenčná karta: Vyhľadávacie a referenčné funkcie nástroja na obnovenie funkcie VLOOKUP

(odkaz)

Použitie argumentu table_array vo funkcii VLOOKUP

Potrebujete ďalšiu pomoc?

Chcete ďalšie možnosti?

Môžete preskúmať výhody predplatného, prehľadávať školiace kurzy, naučiť sa zabezpečiť svoje zariadenie a ešte oveľa viac.

Komunity pomôžu s kladením otázok a odpovedaním na ne, s poskytovaním pripomienok a so získavaním informácií od odborníkov s bohatými znalosťami.