Funkcija Excel HLOOKUP - vodoravno poiščite referenco

Prenesite primer delovnega zvezka

Prenesite primer delovnega zvezka

Ta vadnica prikazuje, kako uporabljati Excel HLOOKUP funkcija v Excelu poiščite vrednost.

Pregled funkcije HLOOKUP

Funkcija HLOOKUP Hlookup pomeni vodoravno iskanje. Išče vrednost v zgornji vrstici tabele. Nato vrne vrednost določeno število vrstic navzdol od najdene vrednosti. Enako je kot vlookup, le da poišče vrednosti vodoravno namesto navpično.

(Upoštevajte, kako so prikazani vnosi formule)

Sintaksa in vnos funkcije HLOOKUP:

1	= HLOOKUP (iskalna_vrednost, matrika_maza, vrstica_indeks_številka, obseg_ogled)

lookup_value - Vrednost, ki jo želite iskati.

matrika_maza -Tabela, iz katere se pridobivajo podatki.

row_index_num - Številka vrstice, iz katere želite pridobiti podatke.

range_lookup -[neobvezno] Logična vrednost za natančno ali približno ujemanje. Privzeto = TRUE = približno ujemanje.

Kaj je funkcija HLOOKUP?

Kot ena od starejših funkcij v svetu preglednic se uporablja funkcija HLOOKUP Horizontal Iskanje. Ima nekaj omejitev, ki jih pogosto premagamo z drugimi funkcijami, kot je INDEX/MATCH. Večina tabel je tudi zgrajenih navpično, vendar je nekajkrat koristno, če iščete vodoravno.

Osnovni primer

Poglejmo vzorec podatkov iz razredne knjige. Obravnavali bomo več primerov pridobivanja informacij za določene študente.

Če želimo ugotoviti, v katerem razredu je Bob, bi zapisali formulo:

1	= HLOOKUP ("Bob", A1: E3, 2, FALSE)

Pomembno si zapomnite, da mora biti predmet, ki ga iščemo (Bob), v prvi vrstici našega obsega iskanja (A1: E3). Funkciji smo povedali, da želimo vrniti vrednost iz 2^nd vrstico obsega iskanja, ki je v tem primeru vrstica 2. Nazadnje smo pokazali, da želimo narediti an natančno ujemanje tako da za zadnji argument umesti False. Tukaj bo odgovor "Branje".

Stranski nasvet: Kot zadnji argument lahko uporabite tudi številko 0 namesto False, saj imata enako vrednost. Nekaterim je to všeč, ker je hitreje pisati. Samo vedite, da je oboje sprejemljivo.

Premaknjeni podatki

Če želite prvemu primeru dodati nekaj pojasnil, ni nujno, da je iskalni element v prvi vrstici vaše preglednice, ampak samo v prvi vrstici vašega obsega iskanja. Uporabimo isti nabor podatkov:

Zdaj pa poiščimo oceno za razred Znanost. Naša formula bi bila

1	= HLOOKUP ("Znanost", A2: E3, 2, LAŽNO)

To je še vedno veljavna formula, saj je prva vrstica našega obsega iskanja vrstica 2, kjer bo naš iskalni izraz "Znanost". Vrnemo vrednost iz 2^nd vrstico obsega iskanja, ki je v tem primeru vrstica 3. Odgovor je nato “A-“.

Uporaba nadomestnih znakov

Funkcija HLOOKUP podpira uporabo nadomestnih znakov "*" in "?" pri iskanju. Na primer, recimo, da smo pozabili, kako se piše Frankovo ​​ime, in samo želeli smo poiskati ime, ki se začne s "F". Formulo bi lahko zapisali

1	= HLOOKUP ("F*", A1: E3, 2, FALSE)

Tako bi lahko v stolpcu E našli ime Frank in nato vrnili vrednost iz 2^nd relativna vrstica. V tem primeru bo odgovor "znanost".

Nenatančno ujemanje

Večino časa se boste morali prepričati, da je zadnji argument v HLOOKUP napačen (ali 0), da dobite natančno ujemanje. Vendar pa lahko nekajkrat iščete nenatančno ujemanje. Če imate seznam razvrščenih podatkov, lahko uporabite tudi HLOOKUP, da vrnete rezultat za element, ki je enak ali naslednji najmanjši. To se pogosto uporablja pri obravnavi naraščajočih razponov številk, na primer v davčni tabeli ali provizijah.

Recimo, da želite najti davčno stopnjo za dohodek, vnesen v celico H2. Formula v H4 je lahko:

1	= HLOOKUP (H2, B1: F2, 2, TRUE)

Razlika v tej formuli je, da je naš zadnji argument "res". V našem konkretnem primeru lahko vidimo, da bodo imeli, ko bodo naši posamezniki vnesli dohodek v višini 45.000 USD, davčno stopnjo 15%.

Opomba: Čeprav ponavadi želimo natančno ujemanje z False kot argumentom, pozabite navesti 4^th Argument v HLOOKUP -u je privzeta vrednost True. To lahko povzroči nepričakovane rezultate, zlasti pri obravnavi besedilnih vrednosti.

Dinamična vrstica

HLOOKUP zahteva, da navedete argument, iz katere vrstice želite vrniti vrednost, vendar se lahko pojavi priložnost, ko ne veste, kje bo vrstica, ali pa želite uporabniku dovoliti, da spremeni, iz katere vrstice se vrne. V teh primerih je lahko koristno uporabiti funkcijo MATCH za določitev številke vrstice.

Ponovno razmislimo o našem primeru knjižice z nekaj vložki v G2 in G4. Da bi dobili številko stolpca, bi lahko napisali formulo

1	= MATCH (G2, A1: A3, 0)

S tem boste poskušali najti točen položaj "razreda" v območju A1: A3. Odgovor bo 3. Če to vemo, ga lahko priključimo v funkcijo HLOOKUP in formulo v G6 napišemo takole:

1	= HLOOKUP (G4, A1: E3, MATCH (G2, A1: A3, 0), 0)

Torej bo funkcija MATCH ocenjena na 3, kar pove HLOOKUP -u, naj vrne rezultat iz 3^rd vrstici v območju A1: E3. Na splošno dobimo želeni rezultat "C". Naša formula je zdaj dinamična, saj lahko spremenimo vrstico za ogled ali ime za iskanje.

Omejitve HLOOKUP

Kot je bilo omenjeno na začetku članka, je največji padec HLOOKUP -a ta, da zahteva iskalni izraz v skrajnem levem stolpcu iskalnega območja. Čeprav obstaja nekaj domiselnih trikov, ki jih lahko odpravite, je skupna alternativa uporaba INDEX in MATCH. Ta kombinacija vam daje večjo prilagodljivost, včasih pa je lahko celo hitrejši izračun.

HLOOKUP v Google Preglednicah

Funkcija HLOOKUP deluje v Google Preglednicah popolnoma enako kot v Excelu:

Dodatne opombe

Za horizontalno iskanje uporabite funkcijo HLOOKUP. Če ste že seznanjeni s funkcijo VLOOKUP, HLOOKUP deluje na popolnoma enak način, le da se iskanje izvede vodoravno namesto navpično. HLOOKUP išče natančno ujemanje (range_lookup = FALSE) ali najbližje ujemanje, ki je enako ali manjše od lookup_value (alirange_lookup = TRUE, samo številčne vrednosti) v prvi vrstici matrike_tabel. Nato vrne ustrezno vrednost, n število vrstic pod ujemanjem.

Ko za iskanje natančnega ujemanja uporabite HLOOKUP, najprej določite identifikacijsko vrednost, ki jo želite poiskati kot lookup_value. Ta identifikacijska vrednost je lahko SSN, ID zaposlenega, ime ali kak drug edinstven identifikator.

Nato določite obseg (imenovan matrika_maza), ki vsebuje identifikatorje v zgornji vrstici in vse vrednosti, ki jih na koncu želite poiskati v vrsticah pod njo. POMEMBNO: Enolični identifikatorji morajo biti v zgornji vrstici. Če niso, morate vrstico premakniti na vrh ali uporabiti MATCH / INDEX namesto HLOOKUP.

Tretjič, določite številko vrstice (row_index) od matrika_maza ki se ga želite vrniti. Upoštevajte, da je prva vrstica z edinstvenimi identifikatorji vrstica 1. Druga vrstica je vrstica 2 itd.

Nazadnje morate v datoteki range_lookup. Če je izbrana možnost natančnega ujemanja in natančnega ujemanja ni, se vrne napaka (#N/A). Če želite, da formula vrne prazno ali "ni najdeno" ali katero koli drugo vrednost namesto vrednosti napake (#N/A), uporabite funkcijo IFERROR s HLOOKUP.

Če želite s funkcijo HLOOKUP vrniti približen nabor ujemanj: range_lookup = RES. Ta možnost je na voljo samo za številske vrednosti. Vrednosti je treba razvrstiti po naraščajočem vrstnem redu.

Primeri HLOOKUP v VBA

V VBA lahko uporabite tudi funkcijo HLOOKUP. Vrsta:
application.worksheetfunction.hlookup (lookup_value, table_array, row_index_num, range_lookup)

Izvajanje naslednjih stavkov VBA

123456

Obseg ("G2") = Application.WorksheetFunction.HLookup (Range ("C1"), Range ("A1: E3"), 1)Obseg ("H2") = Application.WorksheetFunction.HLookup (Range ("C1"), Range ("A1: E3"), 2)Obseg ("I2") = Application.WorksheetFunction.HLookup (Range ("C1"), Range ("A1: E3"), 3)Obseg ("G3") = Application.WorksheetFunction.HLookup (Range ("D1"), Range ("A1: E3"), 1)Obseg ("H3") = Application.WorksheetFunction.HLookup (Range ("D1"), Range ("A1: E3"), 2)Obseg ("I3") = Application.WorksheetFunction.HLookup (Range ("D1"), Range ("A1: E3"), 3)

bo prinesel naslednje rezultate

Za argumente funkcije (lookup_value itd.) Jih lahko vnesete neposredno v funkcijo ali pa namesto tega definirate spremenljivke.

Vrnite se na seznam vseh funkcij v Excelu