Preuzmite primjer radne knjige
Ovaj vodič prikazuje kako se koristi Excel HLOOKUP funkcija u Excelu za traženje vrijednosti.
Pregled funkcije HLOOKUP
HLOOKUP funkcija Hlookup označava horizontalno traženje. Traži vrijednost u gornjem retku tablice. Zatim vraća vrijednost određeni broj redaka prema pronađenoj vrijednosti. Isto je kao i vlookup, osim što traži vrijednosti vodoravno umjesto okomito.
(Obratite pažnju na to kako se pojavljuju unosi formule)
Sintaksa i unos funkcije HLOOKUP:
| 1 | = HLOOKUP (lookup_value, table_array, row_index_num, range_lookup) |
lookup_value - Vrijednost koju želite tražiti.
matrica_tablica -Tablica iz koje se dohvaćaju podaci.
red_indeks_broj - Broj retka iz kojeg se dohvaćaju podaci.
range_lookup -[izborno] Logičko polje za označavanje točnog ili približnog podudaranja. Zadano = TRUE = približno podudaranje.
Što je funkcija HLOOKUP?
Kao jedna od starijih funkcija u svijetu proračunskih tablica, koristi se funkcija HLOOKUP Horizontalna Potrage. Ima nekoliko ograničenja koja se često prevladavaju drugim funkcijama, poput INDEX/MATCH. Također, većina je tablica izgrađena okomito, ali postoji nekoliko puta kada je korisno horizontalno pretraživanje.
Osnovni primjer
Pogledajmo uzorak podataka iz razredne knjige. Dotaknut ćemo se nekoliko primjera za izvlačenje informacija za određene učenike.
Ako želimo saznati u kojoj je klasi Bob, napisali bismo formulu:
| 1 | = HLOOKUP ("Bob", A1: E3, 2, FALSE) |
Važno je zapamtiti da stavka koju tražimo (Bob) mora biti u prvom redu našeg raspona pretraživanja (A1: E3). Rekli smo funkciji da želimo vratiti vrijednost iz 2nd redak raspona pretraživanja, koji je u ovom slučaju redak 2. Konačno, naznačili smo da želimo napraviti an točno podudaranje stavljajući False kao posljednji argument. Ovdje će odgovor biti "Čitanje".
Bočni savjet: Također možete koristiti broj 0 umjesto False kao posljednji argument, jer imaju istu vrijednost. Nekim se ljudima ovo sviđa jer je brže pisati. Samo znajte da je oboje prihvatljivo.
Pomaknuti podaci
Da bismo dodali pojašnjenje u naš prvi primjer, stavka za pretraživanje ne mora biti u prvom retku vaše proračunske tablice, već samo u prvom retku raspona pretraživanja. Koristimo isti skup podataka:
Sada pronađimo ocjenu za razred Znanosti. Naša formula bila bi
| 1 | = HLOOKUP ("Znanost", A2: E3, 2, FALSE) |
Ovo je još uvijek valjana formula jer je prvi red našeg raspona pretraživanja redak 2, gdje će se pronaći naš pojam za pretraživanje "Znanost". Vraćamo vrijednost od 2nd redak raspona pretraživanja, koji je u ovom slučaju redak 3. Odgovor je tada „A-“.
Korištenje zamjenskih znakova
Funkcija HLOOKUP podržava korištenje zamjenskih znakova "*" i "?" prilikom pretraživanja. Na primjer, recimo da smo zaboravili napisati Frankovo ime i samo smo htjeli potražiti ime koje počinje s "F". Mogli bismo napisati formulu
| 1 | = HLOOKUP ("F*", A1: E3, 2, FALSE) |
To bi moglo pronaći ime Frank u stupcu E, a zatim vratiti vrijednost iz 2nd relativni red. U ovom slučaju odgovor će biti "znanost".
Netočno podudaranje
Većinu vremena morate biti sigurni da je zadnji argument u HLOOKUP -u lažan (ili 0) kako biste dobili točno podudaranje. Međutim, postoji nekoliko slučajeva u kojima biste mogli tražiti netočno podudaranje. Ako imate popis razvrstanih podataka, također možete upotrijebiti HLOOKUP za vraćanje rezultata za stavku koja je ista ili sljedeća najmanja. To se često koristi kada se radi o povećanju raspona brojeva, na primjer u poreznoj tablici ili bonusima za provizije.
Recimo da želite pronaći poreznu stopu za prihod unet u ćeliju H2. Formula u H4 može biti:
| 1 | = HLOOKUP (H2, B1: F2, 2, ISTINA) |
Razlika u ovoj formuli je u tome što je naš posljednji argument "Istina". U našem konkretnom primjeru možemo vidjeti da će, kada naši pojedinci unesu prihod od 45.000 USD, imati poreznu stopu od 15%.
Bilješka: Iako obično želimo točno podudaranje s False kao argumentom, zaboravite navesti 4th argument u HLOOKUP -u, zadana vrijednost je True. To može uzrokovati neočekivane rezultate, osobito kada se radi o tekstualnim vrijednostima.
Dinamički red
HLOOKUP zahtijeva da navedete argument u kojem retku želite vratiti vrijednost, ali može se dogoditi prilika kada ne znate gdje će se red nalaziti ili želite dopustiti korisniku da promijeni iz kojeg se redaka treba vratiti. U tim slučajevima može biti korisno upotrijebiti funkciju MATCH za određivanje broja retka.
Razmotrimo ponovno primjer naše knjige ocjena, s nekim ulazima u G2 i G4. Da bismo dobili broj stupca, mogli bismo napisati formulu
| 1 | = MATCH (G2, A1: A3, 0) |
Time će se pokušati pronaći točan položaj “Ocjene” unutar raspona A1: A3. Odgovor će biti 3. Znajući to, možemo ga uključiti u funkciju HLOOKUP i napisati formulu u G6 ovako:
| 1 | = HLOOKUP (G4, A1: E3, MATCH (G2, A1: A3, 0), 0) |
Dakle, funkcija MATCH će procijeniti na 3, a to govori HLOOKUP -u da vrati rezultat iz 3rd reda u rasponu A1: E3. Sve u svemu, tada dobivamo željeni rezultat "C". Naša je formula sada dinamična jer možemo promijeniti ili redak za gledanje ili naziv za pretraživanje.
Ograničenja HLOOKUP -a
Kao što je spomenuto na početku članka, najveći pad HLOOKUP -a je taj što zahtijeva da se pojam za pretraživanje nalazi u krajnjem lijevom stupcu raspona pretraživanja. Iako postoje neki fantastični trikovi koje možete učiniti kako biste to prevladali, uobičajena alternativa je korištenje INDEX -a i MATCH -a. Ta vam kombinacija daje veću fleksibilnost, a ponekad čak može biti i brži izračun.
HLOOKUP u Google tablicama
Funkcija HLOOKUP funkcionira potpuno isto u Google tablicama kao i u Excelu:
dodatne napomene
Upotrijebite funkciju HLOOKUP za izvođenje horizontalnog pretraživanja. Ako ste već upoznati s funkcijom VLOOKUP, HLOOKUP radi na potpuno isti način, osim što se pretraživanje izvodi vodoravno, a ne okomito. HLOOKUP traži točno podudaranje (range_lookup = FALSE) ili najbliže podudaranje koje je jednako ili manje od lookup_value (ilirange_lookup = TRUE, samo numeričke vrijednosti) u prvom retku tablice. Zatim vraća odgovarajuću vrijednost, n broj redaka ispod podudaranja.
Kada koristite HLOOKUP za pronalaženje točnog podudaranja, prvo definirate identifikacijsku vrijednost koju želite tražiti kao lookup_value. Ova identifikacijska vrijednost može biti SSN, ID zaposlenika, naziv ili neki drugi jedinstveni identifikator.
Zatim definirate raspon (koji se naziva stol_pored) koji sadrži identifikatore u gornjem retku i sve vrijednosti koje na kraju želite tražiti u retcima ispod njega. VAŽNO: Jedinstveni identifikatori moraju biti u gornjem retku. Ako nisu, morate pomaknuti redak na vrh ili upotrijebiti MATCH / INDEX umjesto HLOOKUP -a.
Treće, definirajte broj retka (red_indeks) od stol_pored koje se želite vratiti. Imajte na umu da je prvi redak koji sadrži jedinstvene identifikatore redak 1. Drugi red je redak 2 itd.
Na kraju, morate naznačiti hoćete li tražiti točno podudaranje (FALSE) ili najbliže podudaranje (TRUE) u range_lookup. Ako je odabrana opcija točnog podudaranja, a točno podudaranje nije pronađeno, vraća se pogreška (#N/A). Da bi formula bila prazna ili "nije pronađena", ili bilo koju drugu vrijednost umjesto vrijednosti pogreške (#N/A), upotrijebite funkciju IFERROR s HLOOKUP -om.
Da biste pomoću funkcije HLOOKUP vratili približni skup podudaranja: range_lookup = ISTINA. Ova je opcija dostupna samo za numeričke vrijednosti. Vrijednosti se moraju sortirati uzlaznim redoslijedom.
Primjeri HLOOKUP -a u VBA
Također možete koristiti funkciju HLOOKUP u VBA. Tip:application.worksheetfunction.hlookup (lookup_value, table_array, row_index_num, range_lookup)
Izvršavanje sljedećih VBA izraza
| 123456 | Raspon ("G2") = Application.WorksheetFunction.HLookup (Raspon ("C1"), Raspon ("A1: E3"), 1)Raspon ("H2") = Application.WorksheetFunction.HLookup (Raspon ("C1"), Raspon ("A1: E3"), 2)Raspon ("I2") = Application.WorksheetFunction.HLookup (Raspon ("C1"), Raspon ("A1: E3"), 3)Raspon ("G3") = Application.WorksheetFunction.HLookup (Raspon ("D1"), Raspon ("A1: E3"), 1)Raspon ("H3") = Application.WorksheetFunction.HLookup (Raspon ("D1"), Raspon ("A1: E3"), 2)Raspon ("I3") = Application.WorksheetFunction.HLookup (Raspon ("D1"), Raspon ("A1: E3"), 3) |
će proizvesti sljedeće rezultate
Za argumente funkcije (lookup_value itd.) Možete ih unijeti izravno u funkciju ili definirati varijable koje ćete umjesto toga koristiti.
Povratak na popis svih funkcija u Excelu
