Töltse le a példa munkafüzetet
Ez a bemutató bemutatja, hogyan kell használni Excel HLOOKUP függvény az Excelben az érték megkereséséhez.
A HLOOKUP funkció áttekintése
A HLOOKUP funkció Hlookup a vízszintes keresést jelenti. Értéket keres a táblázat felső sorában. Ezután a megadott értékű sorral lejjebb ad egy értéket a talált értékről. Ez ugyanaz, mint a vlookup, kivéve, hogy függőlegesen, hanem vízszintesen keresi az értékeket.
(Figyelje meg, hogyan jelennek meg a képletbemenetek)
HLOOKUP funkció szintaxisa és bemenete:
| 1 | = HLOOKUP (lookup_value, table_array, row_index_num, range_lookup) |
lookup_value - A keresni kívánt érték.
table_array -A táblázat, amelyből adatokat lehet lekérni.
sor_index_száma - A sorszám, ahonnan adatokat kell lekérni.
range_lookup -[nem kötelező] Logikai érték a pontos egyezés vagy a hozzávetőleges egyezés jelzésére. Alapértelmezett = IGAZ = hozzávetőleges egyezés.
Mi a HLOOKUP funkció?
A táblázatok világának egyik régebbi funkciójaként a HLOOKUP funkciót használják Hvízszintes Keresések. Van néhány korlátozása, amelyeket gyakran más funkciók, például INDEX/MATCH, leküzdenek. Ezenkívül a legtöbb asztal függőleges módon épül, de néhány alkalommal, amikor hasznos a vízszintes keresés.
Alapvető példa
Nézzünk egy adatmintát egy évfolyamkönyvből. Számos példával foglalkozunk bizonyos diákok információinak kinyerésére.
Ha meg akarjuk találni, hogy Bob melyik osztályba tartozik, akkor a következő képletet írjuk fel:
| 1 | = HLOOKUP ("Bob", A1: E3, 2, FALSE) |
Fontos megjegyezni, hogy a keresett elemnek (Bob) a keresési tartományunk első sorában kell lennie (A1: E3). Megmondtuk a függvénynek, hogy vissza akarunk adni egy értéket a 2 -bőlnd a keresési tartomány sora, amely jelen esetben a 2. sor. Végül jeleztük, hogy an pontos egyezés az utolsó argumentumként a Hamis beállítást. Itt a válasz az „Olvasás” lesz.
Oldalsó tipp: A hamis helyett a 0 számot is használhatja végső érvként, mivel ezek értéke azonos. Vannak, akik ezt preferálják, mert gyorsabb az írás. Csak tudd, hogy mindkettő elfogadható.
Váltott adatok
Az első példánk némi tisztázása érdekében a keresési tételnek nem kell szerepelnie a táblázat 1. sorában, csak a keresési tartomány első sorában. Használjuk ugyanazt az adathalmazt:
Most keressük a természettudományok osztályzatát. A képletünk az lenne
| 1 | = HLOOKUP ("Tudomány", A2: E3, 2, HAMIS) |
Ez még mindig érvényes képlet, mivel keresési tartományunk első sora a 2. sor, ahol a „Tudomány” keresési kifejezés található. Visszaadunk egy értéket a 2 -bőlnd a keresési tartomány sora, amely ebben az esetben a 3. sor. A válasz ekkor „A-”.
Helyettesítő karakter használata
A HLOOKUP funkció támogatja a „*” és „?” Helyettesítő karakterek használatát keresések során. Tegyük fel például, hogy elfelejtettük Frank nevét írni, és csak olyan nevet akartunk keresni, amely „F” betűvel kezdődik. Írhatnánk a képletet
| 1 | = HLOOKUP ("F*", A1: E3, 2, FALSE) |
Ez képes lenne megtalálni a Frank nevet az E oszlopban, majd visszaadni a 2 értéketnd relatív sor. Ebben az esetben a válasz „Tudomány” lesz.
Nem pontos egyezés
Legtöbbször meg kell győződnie arról, hogy a HLOOKUP utolsó érve hamis (vagy 0), hogy pontos egyezést kapjon. Előfordulhat azonban néhány olyan eset, amikor nem pontos egyezést keres. Ha rendezett adatok listája van, akkor a HLOOKUP használatával visszaadhatja az elem eredményét, amely vagy ugyanaz, vagy a következő legkisebb. Ezt gyakran használják növekvő számtartományok kezelésekor, például adótáblázatban vagy jutalékbónuszokban.
Tegyük fel, hogy szeretné megtalálni a H2 cellába beírt jövedelem adókulcsát. A H4 képlet a következő lehet:
| 1 | = ELŐKÉPZÉS (H2, B1: F2, 2, IGAZ) |
A különbség ebben a képletben az, hogy utolsó érvünk „igaz”. Konkrét példánkban azt láthatjuk, hogy amikor egyénünk 45 000 dollár bevételt ír elő, akkor 15%-os adókulcsot kell fizetniük.
Jegyzet: Bár általában pontos egyezést szeretnénk, ha a hamis argumentum lenne, elfelejted megadni a 4 -etth argumentum a HLOOKUP -ban, az alapértelmezett True. Ez váratlan eredményeket eredményezhet, különösen szöveges értékek kezelésekor.
Dinamikus sor
A HLOOKUP megköveteli, hogy adjon meg egy érvet, hogy melyik sorból szeretne értéket visszaadni, de előfordulhat olyan alkalom, amikor nem tudja, hol lesz a sor, vagy engedélyezni szeretné a felhasználó számára, hogy módosítsa, melyik sorból térjen vissza. Ezekben az esetekben hasznos lehet a MATCH függvény használata a sorszám meghatározásához.
Tekintsük újra az évfolyam könyv példáját, néhány bemenettel a G2 és G4. Az oszlopszám megszerzéséhez írhatunk egy képletet
| 1 | = MATCH (G2, A1: A3, 0) |
Ez megpróbálja megtalálni a „Grade” pontos pozícióját az A1: A3 tartományban. A válasz 3 lesz. Ennek ismeretében csatlakoztathatjuk a HLOOKUP függvényhez, és a következő képletet írhatjuk a G6 -ba:
| 1 | = HLOOKUP (G4, A1: E3, MATCH (G2, A1: A3, 0), 0) |
Tehát a MATCH függvény 3 -ra értékel, és ez azt mondja a HLOOKUP -nak, hogy adjon vissza eredményt a 3 -bólrd sor az A1: E3 tartományban. Összességében megkapjuk a kívánt „C” eredményt. A képletünk mostantól dinamikus, mivel megváltoztathatjuk akár a megjelenítendő sort, akár a keresendő nevet.
HLOOKUP korlátozások
Amint azt a cikk elején említettük, a HLOOKUP legnagyobb bukása az, hogy megköveteli, hogy a keresési kifejezés megtalálható legyen a keresési tartomány bal bal oszlopában. Bár van néhány divatos trükk, amellyel leküzdheti ezt, a közös alternatíva az INDEX és a MATCH használata. Ez a kombináció nagyobb rugalmasságot biztosít, és néha még gyorsabb számítás is lehet.
HLOOKUP a Google Táblázatokban
A HLOOKUP funkció pontosan ugyanúgy működik a Google Táblázatokban, mint az Excelben:
További megjegyzések
A HLOOKUP függvény segítségével végezzen horizontális keresést. Ha már ismeri a VLOOKUP funkciót, a HLOOKUP pontosan ugyanúgy működik, kivéve, hogy a keresés vízszintesen, nem függőlegesen történik. A HLOOKUP pontos egyezést keres (range_lookup = FALSE) vagy a legközelebbi egyezés, amely egyenlő vagy kevesebb, mint a lookup_value (range_lookup = TRUE, csak numerikus értékek) a table_array első sorában. Ezután egy megfelelő értéket ad vissza, n számú sorral az egyezés alatt.
Ha a HLOOKUP segítségével pontos egyezést keres, először határozza meg az azonosító értéket, amelyet keresni szeretne lookup_value. Ez az azonosító érték lehet SSN, munkavállalói azonosító, név vagy más egyedi azonosító.
Ezután határozza meg a tartományt (az úgynevezett table_array), amely tartalmazza az azonosítókat a felső sorban, és azokat az értékeket, amelyeket végül keresni szeretne az alatta lévő sorokban. FONTOS: Az egyedi azonosítóknak a felső sorban kell lenniük. Ha nem, akkor vagy mozgassa a sort a tetejére, vagy használja a MATCH / INDEX -et a HLOOKUP helyett.
Harmadszor határozza meg a sor számát (sor_index) table_array hogy vissza akar térni. Ne feledje, hogy az egyedi sorokat tartalmazó első sor az 1. sor. A második sor a 2. sor stb.
Végül meg kell adnia, hogy pontos egyezést (HAMIS) vagy legközelebbi egyezést (IGAZ) kell keresnie a range_lookup. Ha a pontos egyezés opció van kiválasztva, és nem talál pontos egyezést, akkor hibaüzenet jelenik meg (#N/A). Ahhoz, hogy a képlet üres vagy „nem található”, vagy bármely más értéket adjon vissza a hibaérték (#N/A) helyett, használja az IFERROR függvényt a HLOOKUP funkcióval.
A HLOOKUP funkció használatával hozzávetőleges egyezési készletet adhat vissza: range_lookup = IGAZ. Ez az opció csak numerikus értékeknél érhető el. Az értékeket növekvő sorrendben kell rendezni.
HLOOKUP Példák a VBA -ban
A VLOOKUP funkciót a VBA -ban is használhatja. Típus:application.worksheetfunction.hlookup (lookup_value, table_array, row_index_num, range_lookup)
A következő VBA utasítások végrehajtása
| 123456 | Tartomány ("G2") = Application.WorksheetFunction.HLookup (tartomány ("C1"), tartomány ("A1: E3"), 1)Tartomány ("H2") = Application.WorksheetFunction.HLookup (tartomány ("C1"), tartomány ("A1: E3"), 2)Tartomány ("I2") = Application.WorksheetFunction.HLookup (tartomány ("C1"), tartomány ("A1: E3"), 3)Tartomány ("G3") = Application.WorksheetFunction.HLookup (tartomány ("D1"), tartomány ("A1: E3"), 1)Tartomány ("H3") = Application.WorksheetFunction.HLookup (tartomány ("D1"), tartomány ("A1: E3"), 2)Tartomány ("I3") = Application.WorksheetFunction.HLookup (tartomány ("D1"), tartomány ("A1: E3"), 3) |
a következő eredményeket hozza
A függvény argumentumokhoz (lookup_value, stb.) Megadhatja azokat közvetlenül a függvénybe, vagy definiálhat helyettük változókat.
Térjen vissza az Excel összes funkciójának listájához
