Stáhněte si ukázkový sešit
Tento výukový program ukazuje, jak používat Funkce HLOOKUP aplikace Excel v Excelu vyhledat hodnotu.
Přehled funkcí HLOOKUP
Funkce HLOOKUP Function Hlookup znamená horizontální vyhledávání. Hledá hodnotu v horním řádku tabulky. Poté vrátí hodnotu o zadaný počet řádků dolů z nalezené hodnoty. Je to stejné jako vlookup, kromě toho, že vyhledává hodnoty horizontálně místo vertikálně.
(Všimněte si, jak se zobrazují vstupy vzorců)
Syntaxe a vstup funkce HLOOKUP:
1 | = HLOOKUP (lookup_value, table_array, row_index_num, range_lookup) |
lookup_value - Hodnota, kterou chcete vyhledat.
tabulka_pole -Tabulka, ze které se načítají data.
row_index_num - Číslo řádku, ze kterého se načítají data.
range_lookup -[nepovinné] Boolean k označení přesné shody nebo přibližné shody. Výchozí = PRAVDA = přibližná shoda.
Co je funkce HLOOKUP?
Jako jedna ze starších funkcí ve světě tabulek slouží funkce HLOOKUP Horizontální Vyhledávání. Má několik omezení, která jsou často překonána jinými funkcemi, jako je INDEX/MATCH. Většina tabulek je také postavena vertikálně, ale několikrát je užitečné hledat horizontálně.
Základní příklad
Podívejme se na ukázku dat z klasifikační knihy. Budeme se zabývat několika příklady získávání informací pro konkrétní studenty.
Pokud chceme zjistit, do jaké třídy Bob patří, napsali bychom vzorec:
1 | = HLOOKUP („Bob“, A1: E3, 2, FALSE) |
Důležité je mít na paměti, že položka, kterou hledáme (Bob), musí být v prvním řádku našeho vyhledávacího rozsahu (A1: E3). Řekli jsme funkci, že chceme vrátit hodnotu z 2nd řádek rozsahu vyhledávání, což je v tomto případě řádek 2. Nakonec jsme naznačili, že chceme provést an přesná shoda umístěním False jako posledního argumentu. Zde bude odpověď „Čtení“.
Boční tip: Jako konečný argument můžete také použít číslo 0 namísto False, protože mají stejnou hodnotu. Někteří lidé tomu dávají přednost, protože se to píše rychleji. Jen vězte, že obojí je přijatelné.
Posunutá data
Abychom přidali nějaké vysvětlení k našemu prvnímu příkladu, vyhledávací položka nemusí být v řádku 1 vaší tabulky, ale pouze v prvním řádku vašeho rozsahu vyhledávání. Použijme stejnou datovou sadu:
Nyní najdeme známku pro třídu vědy. Náš vzorec by byl
1 | = HLOOKUP („Věda“, A2: E3, 2, NEPRAVDA) |
Toto je stále platný vzorec, protože první řádek našeho vyhledávacího rozsahu je řádek 2, kde bude nalezen náš hledaný výraz „Věda“. Vracíme hodnotu z 2nd řádek rozsahu vyhledávání, což je v tomto případě řádek 3. Odpověď pak zní „A-“.
Použití zástupných znaků
Funkce HLOOKUP podporuje použití zástupných znaků „*“ a „?“ při vyhledávání. Řekněme například, že jsme zapomněli, jak se píše Frankovo jméno, a chtěli jsme jen vyhledat jméno, které začíná na „F“. Mohli bychom napsat vzorec
1 | = HLOOKUP ("F*", A1: E3, 2, FALSE) |
To by mohlo najít jméno Frank ve sloupci E a poté vrátit hodnotu z 2nd relativní řada. V tomto případě bude odpověď „Věda“.
Nepřesná shoda
Většinu času se budete chtít ujistit, že poslední argument v HLOOKUP je False (nebo 0), abyste získali přesnou shodu. Existuje však několik případů, kdy možná hledáte nepřesnou shodu. Pokud máte seznam seřazených dat, můžete také použít HLOOKUP k vrácení výsledku u položky, která je buď stejná, nebo další nejmenší. To se často používá při řešení rostoucích rozsahů čísel, například v daňové tabulce nebo provizních bonusech.
Řekněme, že chcete najít sazbu daně pro příjem zadaný do buňky H2. Vzorec v H4 může být:
1 | = HLOOKUP (H2, B1: F2, 2, TRUE) |
Rozdíl v tomto vzorci je v tom, že náš poslední argument je „Pravda“. V našem konkrétním příkladu vidíme, že když naši jednotlivci vloží příjem ve výši 45 000 USD, budou mít daňovou sazbu 15%.
Poznámka: Ačkoli obvykle požadujeme přesnou shodu s argumentem False, zapomenete zadat 4th argument v HLOOKUP, výchozí je True. To může způsobit neočekávané výsledky, zejména při práci s textovými hodnotami.
Dynamická řada
HLOOKUP vyžaduje, abyste zadali argument, ze kterého řádku chcete vrátit hodnotu, ale příležitost může nastat, když nevíte, kde bude řádek, nebo chcete svému uživateli umožnit změnit, ze kterého řádku se má vrátit. V těchto případech může být užitečné použít funkci MATCH k určení čísla řádku.
Podívejme se znovu na náš příklad klasifikační knihy s některými vstupy v G2 a G4. Abychom získali číslo sloupce, mohli bychom napsat vzorec
1 | = MATCH (G2, A1: A3, 0) |
To se pokusí najít přesnou polohu „Grade“ v rozsahu A1: A3. Odpověď bude 3. Když to víme, můžeme to zapojit do funkce HLOOKUP a napsat vzorec v G6 takto:
1 | = HLOOKUP (G4, A1: E3, MATCH (G2, A1: A3, 0), 0) |
Funkce MATCH se tedy vyhodnotí na 3 a to řekne HLOOKUP, aby vrátil výsledek z 3rd řádek v rozsahu A1: E3. Celkově pak získáme požadovaný výsledek „C“. Náš vzorec je nyní dynamický v tom, že můžeme změnit buď řádek, na který se máme dívat, nebo název, který máme hledat.
Omezení HLOOKUP
Jak již bylo zmíněno na začátku článku, největším pádem HLOOKUP je, že vyžaduje, aby se hledaný výraz nacházel v levém sloupci rozsahu vyhledávání. I když existuje několik fantastických triků, které můžete udělat, abyste to překonali, běžnou alternativou je použití INDEX a MATCH. Tato kombinace vám poskytne větší flexibilitu a někdy může jít dokonce o rychlejší výpočet.
HLOOKUP v Tabulkách Google
Funkce HLOOKUP funguje v Tabulkách Google úplně stejně jako v Excelu:
Další poznámky
Pomocí funkce HLOOKUP proveďte horizontální vyhledávání. Pokud jste již obeznámeni s funkcí VLOOKUP, HLOOKUP funguje přesně stejným způsobem, kromě toho, že vyhledávání probíhá horizontálně místo vertikálně. HLOOKUP hledá přesnou shodu (range_lookup = FALSE) nebo nejbližší shoda, která je stejná nebo menší než hodnota lookup_value (range_lookup = TRUE, pouze číselné hodnoty) v prvním řádku pole_tabulky. Potom vrátí odpovídající hodnotu, n počet řádků pod shodou.
Při použití HLOOKUP k nalezení přesné shody nejprve definujete identifikační hodnotu, kterou chcete hledat jako lookup_value. Tato identifikační hodnota může být SSN, ID zaměstnance, jméno nebo jiný jedinečný identifikátor.
Dále definujete rozsah (nazývaný tabulka_pole), který obsahuje identifikátory v horním řádku a jakékoli hodnoty, které chcete nakonec hledat v řádcích pod ním. DŮLEŽITÉ: Jedinečné identifikátory musí být v horním řádku. Pokud tomu tak není, musíte buď přesunout řádek nahoru, nebo místo HLOOKUP použít MATCH / INDEX.
Za třetí, definujte číslo řádku (řádek_index) z tabulka_pole že se chcete vrátit. Pamatujte, že první řádek obsahující jedinečné identifikátory je řádek 1. Druhý řádek je řádek 2 atd.
Nakonec musíte zadat, zda se má v souboru vyhledávat přesná shoda (FALSE) nebo nejbližší shoda (TRUE) range_lookup. Pokud je vybrána možnost přesné shody a není nalezena přesná shoda, je vrácena chyba (#N/A). Chcete -li, aby vzorec vrátil prázdný nebo „nenalezen“, nebo jakoukoli jinou hodnotu místo chybové hodnoty (#N/A), použijte funkci IFERROR s HLOOKUP.
Chcete -li pomocí funkce HLOOKUP vrátit přibližnou sadu shod: range_lookup = PRAVDA. Tato možnost je k dispozici pouze pro číselné hodnoty. Hodnoty musí být seřazeny vzestupně.
HLOOKUP Příklady ve VBA
Můžete také použít funkci HLOOKUP ve VBA. Typ:application.worksheetfunction.hlookup (lookup_value, table_array, row_index_num, range_lookup)
Provedení následujících příkazů VBA
123456 | Rozsah ("G2") = Application.WorksheetFunction.HLookup (Range ("C1"), Range ("A1: E3"), 1)Rozsah ("H2") = Application.WorksheetFunction.HLookup (Range ("C1"), Range ("A1: E3"), 2)Rozsah ("I2") = Application.WorksheetFunction.HLookup (Range ("C1"), Range ("A1: E3"), 3)Rozsah ("G3") = Application.WorksheetFunction.HLookup (Range ("D1"), Range ("A1: E3"), 1)Rozsah ("H3") = Application.WorksheetFunction.HLookup (Range ("D1"), Range ("A1: E3"), 2)Rozsah ("I3") = Application.WorksheetFunction.HLookup (Range ("D1"), Range ("A1: E3"), 3) |
přinese následující výsledky
Argumenty funkce (lookup_value atd.) Můžete buď zadat přímo do funkce, nebo definovat proměnné, které se místo toho použijí.
Zpět na seznam všech funkcí v Excelu