Vyhledejte jednu hodnotu pomocí SVYHLEDAT a VVYHLEDAT v Excelu

Nejoblíbenější z vyhledávacích funkcí v Excelu 2013 jsou funkce HLOOKUP (pro horizontální vyhledávání) a SVYHLEDAT (pro vertikální vyhledávání). Tyto funkce se nacházejí v rozevírací nabídce Hledat a reference na kartě Vzorce na pásu karet a také v kategorii Vyhledávání a reference 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 SVYHLEDAT prohledává svisle (shora dolů) sloupec úplně vlevo ve vyhledávací tabulce, dokud program nenajde hodnotu, která odpovídá nebo přesahuje tu, kterou hledáte. Funkce HLOOKUP prohledává vodorovně (zleva doprava) nejvyšší řádek vyhledávací tabulky, dokud nenajde hodnotu, která odpovídá nebo přesahuje hodnotu, kterou hledáte.

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

SVYHLEDAT(vyhledávací_hodnota;pole_tabulky;číslo_indexu_sloupce;[vyhledání_rozsahu])

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

HLOOKUP(vyhledávací_hodnota;pole_tabulky;číslo_indexu_řádku;[vyhledání_rozsahu])

V obou funkcích je argument lookup_value hodnota, kterou chcete vyhledat ve vyhledávací tabulce, a table_array je rozsah buněk nebo název vyhledávací tabulky, který obsahuje jak hodnotu, kterou chcete vyhledat, tak související hodnotu, která se má vrátit.

Argument col_index_num ve funkci SVYHLEDAT je číslo sloupce, jehož hodnoty jsou porovnávány s argumentem lookup_value ve vertikální tabulce. Argument row_index_num ve funkci HLOOKUP je číslo řádku, jehož hodnoty jsou porovnány s hodnotou lookup_value v horizontální tabulce.

Při zadávání argumentů col_index_num nebo row_index_num ve funkcích SVYHLEDAT a HLOOKUP musíte zadat hodnotu větší než nula, která nepřesáhne celkový počet sloupců nebo řádků ve vyhledávací tabulce.

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 hodnotu TRUE nebo vynecháte argument range_lookup ve funkci SVYHLEDAT nebo HLOOKUP, Excel najde přibližnou shodu. Když zadáte FALSE jako argument range_lookup , Excel najde pouze přesné shody.

Hledání přibližných shod se týká pouze případů, kdy hledáte číselné položky (spíše než text) v prvním sloupci nebo řádku vertikální nebo horizontální vyhledávací tabulky. 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 .

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.

Obrázek 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.

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é posouvá hodnoty v prvním sloupci tabulky Tip_Table dolů, dokud nenajde shodu, načež program pomocí argumentu col_index_num ve funkci SVYHLEDAT určí, jaké množství spropitného z tohoto řádku tabulky se vrátí do buňky F3.

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 příklad tabulky tipů používá funkci IF k určení argumentu col_index_num pro funkci SVYHLEDAT v buňce F3.

Funkce IF určuje číslo sloupce, který se má použít v tabulce spropitného, ​​porovnáním procenta zadaného v Tip_Percentage (buňka F1) s 0,15. Pokud se shodují, funkce vrátí 2 jako argument col_index_num a funkce SVYHLEDAT vrátí hodnotu z druhého sloupce (15% sloupec B) v rozsahu Tip_Table.

Jinak funkce IF vrátí 3 jako argument col_index_num a funkce SVYHLEDAT vrátí hodnotu ze třetího sloupce (20% sloupec C) v rozsahu Tip_Table.

Následující obrázek ukazuje příklad, který používá funkci HLOOKUP k vyhledání ceny každé položky pekárny uložené v samostatné tabulce pro vyhledávání cen a poté k vrácení této ceny do sloupce Cena/Doz v seznamu Denní prodej. Buňka F3 obsahuje původní vzorec s funkcí HLOOKUP, který se poté zkopíruje do sloupce F:

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

V tomto VVYHLEDAT funkce, název položky rozsahu, který je dán sloupci Položka v rozmezí C3: C62 je definována jako lookup_value argumentu a rozsah buňka název Cena stolu, který je dán rozsah buněk I1: M2 je table_array argumentem.

Argument číslo_řádku je 2, protože chcete, aby Excel vrátil ceny ve druhém řádku vyhledávací tabulky cen, a volitelný argument hledání_rozsahu je NEPRAVDA, protože název položky v seznamu Denní prodej se musí přesně shodovat s názvem položky v tabulce Vyhledávání cen. .

Tím, že funkce HLOOKUP použije rozsah cenové tabulky k zadání ceny za tucet pro každou položku pekařského zboží v seznamu denních prodejů, bude aktualizace libovolného prodeje v seznamu velmi jednoduchá záležitost.

Jediné, co musíte udělat, je změnit jeho cenu/doz cenu v tomto rozsahu a funkce HLOOKUP okamžitě aktualizuje novou cenu v seznamu denních prodejů, kdekoli se položka prodává.