Vyhľadanie údajov v Exceli 2010 pomocou HLOOKUP a VLOOKUP

Najpopulárnejšie funkcie vyhľadávania v Exceli 2010 sú HLOOKUP (pre horizontálne vyhľadávanie) a VLOOKUP (pre vertikálne vyhľadávanie). Tieto funkcie sa nachádzajú v kategórii Lookup & Reference na karte Vzorce na páse s nástrojmi a v dialógovom okne Vložiť funkciu. Sú súčasťou výkonnej skupiny funkcií, ktoré dokážu vrátiť hodnoty tak, že ich vyhľadajú v dátových tabuľkách.

Funkcia HLOOKUP prehľadáva vodorovne (zľava doprava) najvrchnejší riadok vyhľadávacej tabuľky, kým nenájde hodnotu, ktorá sa zhoduje alebo takmer zhoduje s hodnotou, ktorú hľadáte. Funkcia VLOOKUP prehľadáva vertikálne (zhora nadol) stĺpec úplne vľavo vo vyhľadávacej tabuľke, kým nenájde hodnotu, ktorá sa zhoduje alebo takmer zhoduje s hodnotou, ktorú hľadáte.

Pri používaní funkcií VLOOKUP a HLOOKUP musia byť textové alebo číselné položky v stĺpci alebo riadku vyhľadávania (t. j. v ľavom stĺpci vertikálnej vyhľadávacej tabuľky alebo v hornom riadku horizontálnej vyhľadávacej tabuľky) jedinečné. Tieto položky musia byť tiež usporiadané alebo zoradené vo vzostupnom poradí; to znamená abecedné poradie pre textové položky a od najnižšieho po najvyššie poradie pre číselné položky.

Funkcia HLOOKUP používa nasledujúcu syntax:

=HLOOKUP(vyhľadávacia_hodnota,pole tabuľky,index_riadku,[vyhľadávanie_rozsahu])

Funkcia VLOOKUP má takmer identickú syntax:

=VLOOKUP(vyhľadávacia_hodnota,pole_tabuľky,číslo_indexu stĺpca,[vyhľadávanie_rozsahu])

V oboch funkciách je argument lookup_value hodnota, ktorú chcete vyhľadať v tabuľke, a table_array je rozsah buniek alebo názov tabuľky, ktorý obsahuje hodnotu na vyhľadanie a súvisiacu hodnotu, ktorá sa má vrátiť. Argument row_index_num vo funkcii HLOOKUP je číslo riadku, ktorého hodnotu chcete vrátiť; col_index_num argument funkcie VLOOKUP je číslo stĺpca, ktorého hodnota sa chcete vrátiť.

Voliteľný argument typ argumentu v oboch VLOOKUP a VVYHLEDAT je logický true alebo false, ktorá určuje, či chcete Excel nájsť presný alebo približný zápas pre lookup_value v table_array . Keď zadáte hodnotu TRUE alebo vynecháte argument range_lookup , Excel nájde približnú zhodu. Keď zadáte FALSE ako argument range_lookup , Excel nájde iba presné zhody.

Hľadanie približných zhôd sa týka iba prípadov, keď hľadáte číselné položky (a nie text). Keď Excel nenájde presnú zhodu v tomto vyhľadávacom stĺpci alebo riadku, nájde ďalšiu najvyššiu hodnotu, ktorá nepresahuje argument lookup_value, a potom vráti hodnotu v stĺpci alebo riadku určenom argumentmi col_index_num alebo row_index_num .

Obrázok nižšie ukazuje príklad použitia funkcie VLOOKUP na vrátenie 15% alebo 20% prepitného z tabuľky sprepitných. Bunka F3 obsahuje funkciu VLOOKUP:

=VLOOKUP(Pretax_Total,Tip_Table,IF(Tip_Percentage=0,15;2,3))

Tento vzorec vráti sumu prepitného na základe percenta prepitného v bunke F1 a sumy pred zdanením šeku v bunke F2.

Použitie funkcie VLOOKUP na vrátenie sumy prepitného, ​​ktoré sa má pridať z vyhľadávacej tabuľky.

Ak chcete použiť túto tabuľku sprepitného, ​​zadajte percento prepitného (15 % alebo 20 %) do bunky F1 (s názvom Tip_Percentage) a sumu šeku pred zdanením do bunky F2 (s názvom Pretax_Total). Excel potom vyhľadá hodnotu, ktorú zadáte do bunky Pretax_Total vo vyhľadávacej tabuľke, ktorá obsahuje rozsah buniek A2:C101 a má názov Tip_Table.

Excel potom posúva hodnoty v prvom stĺpci tabuľky tipov nadol, kým nenájde zhodu. Ak Excel zistí, že hodnota zadaná v bunke Pretax_Total (16,50 USD v tomto príklade) sa presne nezhoduje s jednou z hodnôt v prvom stĺpci tabuľky Tip_Table, program pokračuje v prehľadávaní porovnávacieho rozsahu, kým nenarazí na prvú hodnotu, ktorá presahuje celková suma pred zdanením (v tomto príklade 17,00 v bunke A19). Excel sa potom presunie späť na predchádzajúci riadok v tabuľke a vráti hodnotu v stĺpci, ktorá sa zhoduje s argumentom col_index_num funkcie VLOOKUP (je to preto, že voliteľný argument range_lookup bol z funkcie vynechaný).

Všimnite si, že argument col_index_num používa príkaz IF na určenie hodnoty stĺpca, ktorá sa má vrátiť. V tomto prípade, ak je hodnota Tip_Percentage 0,15, funkcia vráti hodnotu v druhom stĺpci tabuľky . V opačnom prípade vráti hodnotu v treťom stĺpci toho istého riadku.