Stiahnite si ukážkový zošit
Tento návod ukazuje, ako používať Funkcia Excel HLOOKUP v programe Excel vyhľadať hodnotu.
Prehľad funkcií HLOOKUP
Funkcia HLOOKUP Function Hlookup znamená horizontálne vyhľadávanie. Hľadá hodnotu v hornom riadku tabuľky. Potom vráti hodnotu o zadaný počet riadkov nadol od nájdenej hodnoty. Je to to isté ako vlookup, ibaže hodnoty vyhľadáva horizontálne namiesto vertikálne.
(Všimnite si, ako sa zobrazujú vstupy vzorcov)
Syntax a vstup funkcie HLOOKUP:
| 1 | = HLOOKUP (lookup_value, table_array, row_index_num, range_lookup) |
lookup_value - Hodnota, ktorú chcete hľadať.
table_array -Tabuľka, z ktorej sa načítavajú údaje.
row_index_num - Číslo riadku, z ktorého sa majú načítať údaje.
range_lookup -[voliteľné] Boolean na označenie presnej alebo približnej zhody. Predvolená hodnota = PRAVDA = približná zhoda.
Čo je funkcia HLOOKUP?
Ako jedna zo starších funkcií vo svete tabuliek sa na to používa funkcia HLOOKUP Horizontálne Vyhľadávanie. Má niekoľko obmedzení, ktoré sú často prekonávané inými funkciami, ako napríklad INDEX/MATCH. Väčšina tabuliek je tiež postavená vertikálne, ale niekoľkokrát je užitočné vyhľadávať horizontálne.
Základný príklad
Pozrime sa na ukážku údajov z hodnotiacej knihy. Budeme sa zaoberať niekoľkými príkladmi na získavanie informácií pre konkrétnych študentov.
Ak chceme zistiť, do ktorej triedy Bob patrí, napísali by sme vzorec:
| 1 | = HLOOKUP ("Bob", A1: E3, 2, NEPRAVDA) |
Je dôležité si uvedomiť, že položka, ktorú hľadáme (Bob), musí byť v prvom riadku nášho vyhľadávacieho rozsahu (A1: E3). Funkcii sme povedali, že chceme vrátiť hodnotu z 2nd riadok rozsahu vyhľadávania, čo je v tomto prípade riadok 2. Nakoniec sme naznačili, že chceme urobiť an presná zhoda umiestnením Nepravdivé ako posledný argument. Tu bude odpoveď „Čítanie“.
Bočný tip: Ako konečný argument môžete použiť aj číslo 0 namiesto False, pretože majú rovnakú hodnotu. Niektorí ľudia to uprednostňujú, pretože je rýchlejšie písať. Len vedzte, že obidve sú prijateľné.
Posunuté údaje
Aby sme náš prvý príklad doplnili, položka vyhľadávania nemusí byť v riadku 1 vašej tabuľky, ale iba v prvom riadku rozsahu vyhľadávania. Použime rovnakú množinu údajov:
Teraz nájdeme známku pre triedu vedy. Náš vzorec by bol
| 1 | = HLOOKUP („Veda“, A2: E3, 2, NEPRAVDA) |
Toto je stále platný vzorec, pretože prvý riadok nášho vyhľadávacieho rozsahu je riadok 2, kde sa nachádza hľadaný výraz „veda“. Vraciame hodnotu z 2nd riadok rozsahu vyhľadávania, čo je v tomto prípade riadok 3. Odpoveď potom je „A-“.
Použitie zástupných znakov
Funkcia HLOOKUP podporuje používanie zástupných znakov „*“ a „?“ pri vyhľadávaní. Povedzme napríklad, že sme zabudli, ako sa píše Frankovo meno, a chceli sme len nájsť meno, ktoré začína na „F“. Mohli by sme napísať vzorec
| 1 | = HLOOKUP ("F*", A1: E3, 2, FALSE) |
V stĺpci E by bolo možné nájsť meno Frank a potom vrátiť hodnotu z 2nd relatívny riadok. V tomto prípade bude odpoveď „veda“.
Nepresná zhoda
Väčšinu času sa budete chcieť uistiť, že posledný argument v HLOOKUP je nepravdivý (alebo 0), aby ste získali presnú zhodu. Existuje však niekoľko prípadov, keď hľadáte nepresnú zhodu. Ak máte zoznam zoradených údajov, môžete tiež použiť HLOOKUP na vrátenie výsledku pre položku, ktorá je buď rovnaká alebo najbližšia najmenšia. Toto sa často používa pri riešení narastajúcich rozsahov čísel, napríklad v daňovej tabuľke alebo províziách.
Povedzme, že chcete nájsť sadzbu dane pre príjem zadaný do bunky H2. Vzorec v H4 môže byť:
| 1 | = HLOOKUP (H2, B1: F2, 2, TRUE) |
Rozdiel v tomto vzorci je v tom, že náš posledný argument je „Pravda“. Na našom konkrétnom príklade vidíme, že keď naši jednotlivci vložia príjem 45 000 dolárov, budú mať sadzbu dane 15%.
Poznámka: Aj keď zvyčajne chceme presnú zhodu s argumentom False, zabudli ste zadať 4th v HLOOKUP, predvolená hodnota je True. To môže viesť k neočakávaným výsledkom, najmä pri práci s textovými hodnotami.
Dynamický riadok
HLOOKUP vyžaduje, aby ste zadali argument, z ktorého riadku chcete vrátiť hodnotu, ale môže nastať situácia, keď neviete, kde bude riadok, alebo chcete svojmu používateľovi umožniť zmeniť, z ktorého riadku sa má vrátiť. V týchto prípadoch môže byť užitočné použiť funkciu MATCH na určenie čísla riadka.
Zoberme si znova príklad našej hodnotiacej knihy s niektorými vstupmi v G2 a G4. Aby sme získali číslo stĺpca, mohli by sme napísať vzorec pre
| 1 | = ZÁPAS (G2, A1: A3, 0) |
Pokúsi sa nájsť presnú polohu „Grade“ v rozsahu A1: A3. Odpoveď bude 3. Keď to vieme, môžeme to zapojiť do funkcie HLOOKUP a napísať vzorec v G6 takto:
| 1 | = HLOOKUP (G4, A1: E3, MATCH (G2, A1: A3, 0), 0) |
Funkcia MATCH sa teda vyhodnotí na 3 a to povie HLOOKUP, aby vrátil výsledok z 3rd riadok v rade A1: E3. Celkovo potom dostaneme požadovaný výsledok „C“. Náš vzorec je teraz dynamický v tom, že môžeme zmeniť buď riadok, na ktorý sa máme pozrieť, alebo názov, ktorý sa má hľadať.
Obmedzenia HLOOKUP
Ako už bolo spomenuté na začiatku článku, najväčším úskalím HLOOKUP je, že vyžaduje, aby sa hľadaný výraz nachádzal v ľavom stĺpci rozsahu vyhľadávania. Aj keď existuje niekoľko fantastických trikov, ktoré môžete urobiť, aby ste to prekonali, bežnou alternatívou je použitie INDEX a MATCH. Táto kombinácia vám poskytne väčšiu flexibilitu a niekedy môže ísť aj o rýchlejší výpočet.
HLOOKUP v Tabuľkách Google
Funkcia HLOOKUP funguje v Tabuľkách Google úplne rovnako ako v programe Excel:
Doplňujúce Poznámky
Na vykonanie horizontálneho vyhľadávania použite funkciu HLOOKUP. Ak ste už oboznámení s funkciou VLOOKUP, HLOOKUP funguje úplne rovnakým spôsobom, ibaže vyhľadávanie sa vykonáva horizontálne namiesto vertikálne. HLOOKUP hľadá presnú zhodu (range_lookup = FALSE) alebo najbližšia zhoda, ktorá sa rovná alebo je nižšia ako lookup_value (range_lookup = TRUE, iba číselné hodnoty) v prvom riadku poľa table_array. Potom vráti zodpovedajúcu hodnotu, n počet riadkov pod zhodou.
Keď na nájdenie presnej zhody použijete HLOOKUP, najskôr definujete identifikačnú hodnotu, ktorú chcete hľadať ako lookup_value. Táto identifikačná hodnota môže byť SSN, ID zamestnanca, meno alebo iný jedinečný identifikátor.
Ďalej definujete rozsah (nazývaný table_array), ktorý obsahuje identifikátory v hornom riadku a akékoľvek hodnoty, ktoré chcete v konečnom dôsledku hľadať v riadkoch pod ním. DÔLEŽITÉ: Jedinečné identifikátory musia byť v hornom riadku. Ak nie sú, musíte buď presunúť riadok na začiatok, alebo namiesto HLOOKUPU použiť MATCH / INDEX.
Po tretie, definujte číslo riadku (riadkový_index) z table_array že sa chceš vrátiť. Majte na pamäti, že prvý riadok obsahujúci jedinečné identifikátory je riadok 1. Druhý riadok je riadok 2 atď.
Nakoniec musíte označiť, či sa má v range_lookup. Ak je vybratá možnosť presnej zhody a presná zhoda sa nenájde, vráti sa chyba (#N/A). Ak chcete, aby vzorec vracal prázdny alebo „nenájdený“ alebo inú hodnotu namiesto chybovej hodnoty (#N/A), použite funkciu IFERROR s HLOOKUP.
Ak chcete použiť funkciu HLOOKUP na vrátenie približnej sady zhody: range_lookup = PRAVDA. Táto možnosť je k dispozícii iba pre číselné hodnoty. Hodnoty je potrebné zoradiť vzostupne.
HLOOKUP Príklady vo VBA
Vo VBA môžete tiež použiť funkciu HLOOKUP. Typ:application.worksheetfunction.hlookup (lookup_value, table_array, row_index_num, range_lookup)
Vykonanie nasledujúcich vyhlásení 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) |
prinesie nasledujúce výsledky
Argumenty funkcie (lookup_value, atď.) Môžete buď zadať priamo do funkcie, alebo definovať premenné, ktoré sa majú použiť.
Vráťte sa do zoznamu všetkých funkcií v programe Excel
