Laadige alla töövihiku näide
See õpetus näitab, kuidas Funktsioon Excel HLOOKUP Excelis väärtuse otsimiseks.
Funktsiooni HLOOKUP ülevaade
Funktsioon HLOOKUP Hlookup tähistab horisontaalset otsingut. See otsib väärtust tabeli ülemisest reast. Seejärel tagastab leitud väärtusest teatud arvu ridu allapoole jääva väärtuse. See on sama mis vlookup, ainult et see otsib väärtusi vertikaalselt, mitte horisontaalselt.
(Pange tähele, kuidas valemisisendid ilmuvad)
Funktsiooni HLOOKUP süntaks ja sisend:
| 1 | = HLOOKUP (otsinguväärtus, tabeli_massiiv, rea_indeksi_number, vahemiku_vaade) |
lookup_value - Väärtus, mida soovite otsida.
tabel_massiiv -Tabel, kust andmeid hankida.
rea_indeksi_number - Rea number, kust andmeid hankida.
vahemiku_vaade -[valikuline] tõeväärtus täpse või ligikaudse vaste näitamiseks. Vaikimisi = TRUE = ligikaudne vaste.
Mis on funktsioon HLOOKUP?
Arvutustabelite maailma ühe vanema funktsioonina kasutatakse funktsiooni HLOOKUP Hhorisontaalne Otsingud. Sellel on mõned piirangud, mis sageli ületatakse muude funktsioonidega, näiteks INDEX/MATCH. Lisaks on enamik tabeleid ehitatud vertikaalselt, kuid mõned korrad, kui on kasulik otsida horisontaalselt.
Põhinäide
Vaatame hinderaamatu andmete näidist. Me käsitleme mitmeid näiteid teabe hankimiseks konkreetsete õpilaste jaoks.
Kui tahame teada, mis klassi Bob kuulub, kirjutaksime järgmise valemi:
| 1 | = HLOOKUP ("Bob", A1: E3, 2, FALSE) |
Oluline on meeles pidada, et üksus, mida otsime (Bob), peab olema meie otsinguvahemiku esimesel real (A1: E3). Oleme funktsioonile öelnud, et tahame väärtuse 2 -st tagastadand otsinguvahemiku rida, mis antud juhul on rida 2. Lõpuks märkisime, et soovime teha täpne vaste pannes viimaseks argumendiks False. Siin on vastus "lugemine".
Küljeots: Viimase argumendina võite kasutada ka väärtuse Väärtuse asemel numbrit 0, kuna neil on sama väärtus. Mõned inimesed eelistavad seda, sest kirjutamine on kiirem. Lihtsalt teadke, et mõlemad on vastuvõetavad.
Nihutatud andmed
Meie esimesele näitele selgituse lisamiseks ei pea otsinguüksus olema arvutustabeli 1. reas, vaid ainult teie otsinguvahemiku esimesel real. Kasutame sama andmekogumit:
Leiame nüüd loodusteaduste klassi hinde. Meie valem oleks
| 1 | = HLOOKUP ("Teadus", A2: E3, 2, FALSE) |
See on endiselt kehtiv valem, kuna meie otsinguvahemiku esimene rida on rida 2, kust leitakse meie otsingusõna „Teadus”. Anname tagasi väärtuse 2 -stnd otsinguvahemiku rida, mis antud juhul on rida 3. Vastus on siis „A-“.
Metamärkide kasutamine
Funktsioon HLOOKUP toetab metamärkide “*” ja “?” Kasutamist otsinguid tehes. Oletame näiteks, et olime unustanud Franki nime kirjutamise ja tahtsime lihtsalt otsida nime, mis algab tähega "F". Võiksime valemi kirjutada
| 1 | = HLOOKUP ("F*", A1: E3, 2, FALSE) |
Selle abil oleks võimalik leida veerust E nimi Frank ja tagastada väärtus 2nd suhteline rida. Sel juhul on vastus "Teadus".
Mitte täpne vaste
Enamasti soovite täpse vaste saamiseks veenduda, et HLOOKUPi viimane argument on vale (või 0). Siiski on mõni kord, kui otsite ebatäpset vastet. Kui teil on sorteeritud andmete loend, saate funktsiooni HLOOKUP abil ka tagastada sama või järgmise väikseima üksuse tulemuse. Seda kasutatakse sageli suurenevate numbrivahemikega tegelemisel, näiteks maksutabelis või vahendustasudes.
Oletame, et soovite leida tulu lahtrisse H2 maksumäära. H4 valem võib olla järgmine:
| 1 | = HLOOKUP (H2, B1: F2, 2, TRUE) |
Selle valemi erinevus seisneb selles, et meie viimane argument on “tõene”. Meie konkreetses näites näeme, et kui meie isik sisendab tulu 45 000 dollarit, on neil maksumäär 15%.
Märge: Kuigi tavaliselt tahame täpset vastet, mille argument on vale, unustate 4th argumendis HLOOKUPis, on vaikimisi True. See võib põhjustada ootamatuid tulemusi, eriti tekstiväärtuste käsitlemisel.
Dünaamiline rida
HLOOKUP nõuab argumendi esitamist, milliselt realt soovite väärtuse tagastada, kuid võib juhtuda, et te ei tea, kus rida asub, või soovite lubada kasutajal muuta, milliselt realt tagasi pöörduda. Sellistel juhtudel võib olla kasulik kasutada funktsiooni MATCH reanumbri määramiseks.
Mõelgem uuesti oma hinnete raamatu näitele, mõned sisendid G2 ja G4. Veeru numbri saamiseks võiksime kirjutada valemi
| 1 | = MATCH (G2, A1: A3, 0) |
Sellega püütakse leida hinde täpne asukoht vahemikus A1: A3. Vastus on 3. Seda teades saame selle ühendada funktsiooniga HLOOKUP ja kirjutada G6 -s valemi järgmiselt:
| 1 | = HLOOKUP (G4, A1: E3, MATCH (G2, A1: A3, 0), 0) |
Seega hindab funktsioon MATCH väärtuseks 3 ja see käsib HLOOKUP -il saada tulemuse kolmestrd rida vahemikus A1: E3. Üldiselt saame soovitud tulemuse "C". Meie valem on nüüd dünaamiline, kuna saame muuta kas vaadatavat rida või otsitavat nime.
HLOOKUPi piirangud
Nagu artikli alguses mainitud, on HLOOKUPi suurim langus see, et see nõuab otsingutermini leidmist otsinguvahemiku kõige vasakpoolses veerus. Kuigi selle ületamiseks saate teha mõningaid väljamõeldud trikke, on tavaline alternatiiv kasutada INDEX ja MATCH. See kombinatsioon annab teile rohkem paindlikkust ja mõnikord võib see olla isegi kiirem arvutus.
HLOOKUP Google'i arvutustabelites
Funktsioon HLOOKUP töötab Google'i arvutustabelites täpselt samamoodi nagu Excelis:
lisamärkmed
Horisontaalse otsingu tegemiseks kasutage funktsiooni HLOOKUP. Kui olete VLOOKUP -funktsiooniga juba tuttav, töötab HLOOKUP täpselt samamoodi, välja arvatud see, et otsing toimub vertikaalselt, mitte horisontaalselt. HLOOKUP otsib täpset vastet (vahemiku_vaade = FALSE) või lähim vaste, mis on võrdne või väiksem kui lookup_value (vahemiku_vaade = TRUE, ainult numbrilised väärtused) tabeli_massiivi esimesel real. Seejärel tagastab see vastava väärtuse, n ridade arvu vaste all.
Kui kasutate HLOOKUP -i täpse vaste leidmiseks, määratlege esmalt identifitseeriv väärtus, mida soovite otsida lookup_value. See identifitseeriv väärtus võib olla SSN, töötaja ID, nimi või mõni muu kordumatu tunnus.
Järgmisena määrate vahemiku (nn tabel_massiiv), mis sisaldab ülemise rea identifikaatoreid ja väärtusi, mida soovite selle all olevatelt ridadelt otsida. TÄHTIS: unikaalsed identifikaatorid peavad olema ülemisel real. Kui neid ei ole, peate rea kas üles viima või kasutama HLOOKUP -i asemel MATCH / INDEX -i.
Kolmandaks määrake rea number (rida_indeks) selle tabel_massiiv et soovite tagasi tulla. Pidage meeles, et esimene rida, mis sisaldab kordumatuid tunnuseid, on rida 1. Teine rida on rida 2 jne.
Lõpuks peate kaustast märkima, kas otsida täpset vastet (FALSE) või lähimat vastet (TRUE) vahemiku_vaade. Kui valitud on täpne vaste ja täpset vastet ei leita, tagastatakse tõrge (#N/A). Kui soovite, et valem sisestaks vea väärtuse (#N/A) asemel tühja või „ei leitud” või mõne muu väärtuse, kasutage funktsiooni IFERROR koos HLOOKUP -iga.
Funktsiooni HLOOKUP kasutamine ligikaudse vaste komplekti tagastamiseks: vahemiku_vaade = ÕIGE. See valik on saadaval ainult numbriliste väärtuste puhul. Väärtused tuleb sortida kasvavas järjekorras.
HLOOKUP Näited VBA -s
Funktsiooni HLOOKUP saate kasutada ka VBA -s. Tüüp:application.worksheetfunction.hlookup (lookup_value, table_array, row_index_num, range_lookup)
Järgmiste VBA avalduste täitmine
| 123456 | Vahemik ("G2") = Application.WorksheetFunction.HLookup (Vahemik ("C1"), vahemik ("A1: E3"), 1)Vahemik ("H2") = Application.WorksheetFunction.HLookup (Vahemik ("C1"), vahemik ("A1: E3"), 2)Vahemik ("I2") = Application.WorksheetFunction.HLookup (Vahemik ("C1"), vahemik ("A1: E3"), 3)Vahemik ("G3") = Application.WorksheetFunction.HLookup (Vahemik ("D1"), vahemik ("A1: E3"), 1)Vahemik ("H3") = Application.WorksheetFunction.HLookup (Vahemik ("D1"), vahemik ("A1: E3"), 2)Vahemik ("I3") = Application.WorksheetFunction.HLookup (Vahemik ("D1"), vahemik ("A1: E3"), 3) |
annab järgmised tulemused
Funktsiooniargumentide (lookup_value jne) jaoks saate need kas otse funktsiooni sisestada või määrata selle asemel kasutatavad muutujad.
Tagasi kõigi Exceli funktsioonide loendisse
