Vyhledání dat v aplikaci Excel 2007 pomocí HLOOKUP a VLOOKUP

Nejoblíbenější vyhledávací funkce v Excelu 2007 jsou HLOOKUP (pro horizontální vyhledávání) a SVYHLEDAT (pro vertikální vyhledávání). Tyto funkce se nacházejí v kategorii Vyhledávání a reference na kartě Vzorce na pásu karet a v dialogovém okně Vložit funkci. Jsou součástí výkonné skupiny funkcí, které mohou vracet hodnoty tím, že je vyhledávají v datových tabulkách.

Funkce HLOOKUP prohledává vodorovně (zleva doprava) nejvyšší řádek tabulky, dokud nenajde hodnotu, která odpovídá nebo přesahuje hodnotu, kterou hledáte. Funkce SVYHLEDAT prohledává svisle (shora dolů) sloupec úplně vlevo v tabulce, dokud program nenajde hodnotu, která odpovídá nebo přesahuje tu, kterou hledáte.

Při použití funkcí SVYHLEDAT a VVYHLEDAT musí být textové nebo číselné položky ve sloupci nebo řádku vyhledávání (tj. sloupec zcela vlevo vertikální vyhledávací tabulky nebo horní řádek horizontální vyhledávací tabulky) jedinečné. Tyto položky musí být také uspořádány nebo seřazeny ve vzestupném pořadí; to znamená abecední pořadí pro textové položky a od nejnižšího k nejvyššímu pořadí pro číselné položky.

Funkce HLOOKUP používá následující syntaxi:

=HLOOKUP(vyhledávací_hodnota,pole_tabulky,číslo_řádku,[hledání_rozsahu])

Funkce VLOOKUP má téměř identickou syntaxi:

=SVYHLEDAT(vyhledávací_hodnota,pole_tabulky,číslo_sloupce,[hledání_rozsahu])

V obou funkcích je argument lookup_value hodnota, kterou chcete vyhledat v tabulce, table_array je rozsah buněk nebo název tabulky, který obsahuje jak hodnotu, která se má vyhledat, tak související hodnotu, kterou chcete vrátit. Argument číslo_indexu_řádku ve funkci HLOOKUP je číslo řádku, jehož hodnotu chcete vrátit; col_index_num argument funkce SVYHLEDAT je číslo sloupce, jehož hodnota se chcete vrátit.

Volitelný argument range_lookup ve funkcích SVYHLEDAT i HLOOKUP má logickou hodnotu TRUE nebo FALSE, která určuje, zda chcete, aby aplikace Excel nalezla přesnou nebo přibližnou shodu pro hodnotu lookup_value v poli table_array . Když zadáte TRUE nebo vynecháte argument range_lookup , Excel najde přibližnou shodu. Když jako argument range_lookup zadáte FALSE , Excel najde pouze přesné shody.

Hledání přibližných shod se týká pouze při vyhledávání číselných položek (spíše než textu). Když Excel nenajde přesnou shodu v tomto vyhledávacím sloupci nebo řádku, vyhledá další nejvyšší hodnotu, která nepřesahuje argument lookup_value, a poté vrátí hodnotu ve sloupci nebo řádku určeném argumenty col_index_num nebo row_index_num .

Obrázek níže ukazuje příklad použití funkce SVYHLEDAT k vrácení buď 15% nebo 20% spropitného z tabulky spropitného, ​​v závislosti na součtu před zdaněním šeku. Buňka F3 obsahuje funkci VLOOKUP:

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

Tento vzorec vrátí částku spropitného na základě procenta spropitného v buňce F1 a částky před zdaněním šeku v buňce F2.

Pomocí funkce SVYHLEDAT vrátíte množství spropitného, ​​které se má přidat z vyhledávací tabulky.

Chcete-li použít tuto tabulku spropitného, ​​zadejte procento spropitného (15 % nebo 20 %) do buňky F1 (s názvem Tip_Percentage) a částku šeku před zdaněním do buňky F2 (s názvem Pretax_Total). Excel poté vyhledá hodnotu, kterou zadáte do buňky Pretax_Total v prvním sloupci vyhledávací tabulky, která obsahuje oblast buněk A2:C101 a má název Tip_Table.

Excel poté posune hodnoty v prvním sloupci tabulky Tip_Table dolů, dokud nenajde shodu. Pokud Excel zjistí, že hodnota zadaná v buňce Pretax_Total (16,50 $ v tomto příkladu) přesně neodpovídá jedné z hodnot v prvním sloupci tabulky Tip_Table, program pokračuje v prohledávání srovnávacího rozsahu, dokud nenarazí na první hodnotu, která překračuje celková částka před zdaněním (v tomto příkladu 17,00 v buňce A19). Excel se poté přesune zpět na předchozí řádek v tabulce a vrátí hodnotu ve sloupci, která odpovídá argumentu col_index_num funkce SVYHLEDAT (je to proto, že volitelný argument range_lookup byl z funkce vynechán).

Všimněte si, že argument číslo_indexu_sloupce používá k určení hodnoty sloupce, která má být vrácena, příkaz IF. V tomto případě, pokud je hodnota Tip_Percentage 0,15, pak funkce vrátí hodnotu ve druhém sloupci tabulky v řádku, který byl identifikován hodnotou lookup_value. V opačném případě vrátí hodnotu ve třetím sloupci stejného řádku.