Vyhľadajte jednu hodnotu pomocou funkcie VLOOKUP a HLOOKUP v Exceli

Najpopulárnejšie funkcie vyhľadávania v Exceli 2013 sú funkcie HLOOKUP (pre horizontálne vyhľadávanie) a VLOOKUP (pre vertikálne vyhľadávanie). Tieto funkcie sa nachádzajú v rozbaľovacej ponuke Lookup & Reference na karte Vzorce na páse s nástrojmi, ako aj v kategórii Lookup & Reference 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 VLOOKUP prehľadáva vertikálne (zhora nadol) stĺpec úplne vľavo vo vyhľadávacej tabuľke, kým program nenájde hodnotu, ktorá sa zhoduje s hodnotou, ktorú hľadáte, alebo ju presahuje. Funkcia HLOOKUP prehľadáva vodorovne (zľava doprava) najvrchnejší riadok vyhľadávacej tabuľky, kým nenájde hodnotu, ktorá sa zhoduje s hodnotou, ktorú hľadáte, alebo ju presahuje.

Funkcia VLOOKUP používa nasledujúcu syntax:

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

Funkcia HLOOKUP má takmer identickú syntax:

HLOOKUP(vyhľadávacia_hodnota,pole_tabuľky,index_riadku,[vyhľadávanie_rozsahu])

V oboch funkciách je argument lookup_value hodnotou, ktorú chcete vyhľadať vo vyhľadávacej tabuľke, a table_array je rozsah buniek alebo názov vyhľadávacej tabuľky, ktorý obsahuje hodnotu na vyhľadanie a súvisiacu hodnotu, ktorá sa má vrátiť.

Argument col_index_num vo funkcii VLOOKUP je číslo stĺpca, ktorého hodnoty sa porovnávajú s argumentom lookup_value vo vertikálnej tabuľke. Argument row_index_num vo funkcii HLOOKUP je číslo riadku, ktorého hodnoty sa porovnávajú s hodnotou lookup_value v horizontálnej tabuľke.

Pri zadávaní argumentov col_index_num alebo row_index_num vo funkciách VLOOKUP a HLOOKUP musíte zadať hodnotu väčšiu ako nula, ktorá nepresahuje celkový počet stĺpcov alebo riadkov vo vyhľadávacej tabuľke.

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.

Ak zadáte hodnotu PRAVDA alebo vynechať argument typ argument funkcie VLOOKUP alebo VVYHLEDAT, Excel zistí 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 vtedy, keď hľadáte číselné položky (namiesto textu) v prvom stĺpci alebo riadku vertikálnej alebo horizontálnej vyhľadávacej tabuľky. 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 .

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.

Obrázok ukazuje príklad použitia funkcie VLOOKUP na vrátenie 15% alebo 20% prepitného z tabuľky sprepitného v závislosti od súčtu šeku pred zdanením. 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.

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 v prvom stĺpci vyhľadávacej tabuľky, ktorá obsahuje rozsah buniek A2:C101 a má názov Tip_Table.

Excel potom posunie hodnoty v prvom stĺpci tabuľky Tip_Table nadol, kým nenájde zhodu, načo program použije argument col_index_num vo funkcii VLOOKUP na určenie sumy prepitného z daného riadku tabuľky, ktorá sa má vrátiť do bunky F3.

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 príklad tabuľky tipov používa funkciu IF na určenie argumentu col_index_num pre funkciu VLOOKUP v bunke F3.

Funkcia IF určuje číslo stĺpca, ktorý sa má použiť v tabuľke sprepitného, ​​prirovnaním percenta zadaného v Tip_Percentage (bunka F1) s 0,15. Ak sa zhodujú, funkcia vráti 2 ako argument col_index_num a funkcia VLOOKUP vráti hodnotu z druhého stĺpca (15 % stĺpec B) v rozsahu Tabuľka tipov.

V opačnom prípade funkcia IF vráti hodnotu 3 ako argument col_index_num a funkcia VLOOKUP vráti hodnotu z tretieho stĺpca (20 % stĺpec C) v rozsahu Tabuľka tipov.

Nasledujúci obrázok zobrazuje príklad, ktorý používa funkciu HLOOKUP na vyhľadanie ceny každej položky pekárne uloženej v samostatnej tabuľke vyhľadávania cien a potom na vrátenie tejto ceny do stĺpca Cena/Doz v zozname Denný predaj. Bunka F3 obsahuje pôvodný vzorec s funkciou HLOOKUP, ktorý sa potom skopíruje do stĺpca F:

=HLOOKUP(položka,Cenová_tabuľka,2,FALSE)

V tomto VVYHLEDAT funkcie, názov položky rozsahu, ktorý je daný stĺpci Položka v rozmedzí C3: C62 je definovaná ako lookup_value argumentu a rozsah bunka názov Cena stola, ktorý je daný rozsah buniek I1: M2 je table_array argumentom.

Argument row_index_num je 2, pretože chcete, aby Excel vrátil ceny v druhom riadku tabuľky na vyhľadávanie cien, a voliteľný argument range_lookup je FALSE, pretože názov položky v zozname denného predaja sa musí presne zhodovať s názvom položky v tabuľke vyhľadávania cien .

Tým, že funkcia HLOOKUP použije rozsah cenovej tabuľky na zadanie ceny za tucet pre každú položku pekárenského tovaru v zozname denných predajov, bude aktualizácia ktoréhokoľvek z predajov v zozname veľmi jednoduchá.

Jediné, čo musíte urobiť, je zmeniť jeho cenu/doz v tomto rozsahu a funkcia HLOOKUP okamžite aktualizuje novú cenu v zozname denných predajov všade, kde sa položka predáva.