Atsisiųskite darbaknygės pavyzdį
Ši pamoka parodo, kaip naudotis „Excel HLOOKUP“ funkcija „Excel“, kad surastumėte vertę.
HLOOKUP funkcijų apžvalga
„HLOOKUP“ funkcija „Hlookup“ reiškia horizontalią paiešką. Ji ieško vertės viršutinėje lentelės eilutėje. Tada grąžina reikšmę nurodytu eilučių skaičiumi žemyn nuo rastos vertės. Tai tas pats, kas „vlookup“, išskyrus tai, kad jis vertes ieško horizontaliai, o ne vertikaliai.
(Atkreipkite dėmesį, kaip atrodo formulės įvestys)
Funkcijos HLOOKUP sintaksė ir įvestis:
| 1 | = HLOOKUP (lookup_value, table_array, row_index_num, range_lookup) |
lookup_value - Vertė, kurios norite ieškoti.
table_array -Lentelė, iš kurios galima gauti duomenis.
eilutės_indekso_numeris - eilutės numeris, iš kurio reikia gauti duomenis.
range_lookup -[neprivaloma] loginė reikšmė, nurodanti tikslią arba apytikslę atitiktį. Numatytasis = TRUE = apytikslė atitiktis.
Kas yra funkcija HLOOKUP?
Kaip viena iš senesnių funkcijų skaičiuoklių pasaulyje, naudojama funkcija HLOOKUP Horizontali Peržiūros. Jis turi keletą apribojimų, kurie dažnai įveikiami naudojant kitas funkcijas, pvz., INDEX/MATCH. Be to, dauguma stalų yra pastatyti vertikaliai, tačiau yra keletas kartų, kai naudinga ieškoti horizontaliai.
Pagrindinis pavyzdys
Pažvelkime į pažymių knygos duomenų pavyzdį. Išnagrinėsime keletą pavyzdžių, kaip išgauti informaciją konkretiems studentams.
Jei norime sužinoti, kuriai klasei priklauso Bobas, parašytume formulę:
| 1 | = PAKEITIMAS („Bobas“, A1: E3, 2, FALSE) |
Svarbu atsiminti, kad mūsų ieškomas elementas (Bobas) turi būti pirmoje paieškos diapazono eilutėje (A1: E3). Mes pasakėme funkcijai, kad norime grąžinti vertę iš 2antra paieškos diapazono eilutę, kuri šiuo atveju yra 2 eilutė. Galiausiai nurodėme, kad norime atlikti an tikslus atitikimas paskutiniu argumentu pateikdami klaidingą. Čia atsakymas bus „Skaitymas“.
Šoninis patarimas: Galutiniame argumente vietoj klaidingo galite naudoti skaičių 0, nes jie turi tą pačią reikšmę. Kai kuriems žmonėms tai labiau patinka, nes greičiau rašoma. Tiesiog žinokite, kad abu yra priimtini.
Perkelti duomenys
Norėdami patikslinti pirmąjį pavyzdį, paieškos elementas neturi būti skaičiuoklės 1 eilutėje, o tik pirmoje paieškos diapazono eilutėje. Naudokime tą patį duomenų rinkinį:
Dabar suraskime gamtos mokslų klasės pažymį. Mūsų formulė būtų
| 1 | = HLOOKUP („Mokslas“, A2: E3, 2, FALSE) |
Tai vis dar galiojanti formulė, nes pirmoji mūsų paieškos diapazono eilutė yra 2 eilutė, kurioje rasite mūsų paieškos terminą „Mokslas“. Grąžiname vertę iš 2antra paieškos diapazono eilutę, kuri šiuo atveju yra 3 eilutė. Tada atsakymas yra „A-“.
Pakaitos simbolių naudojimas
Funkcija HLOOKUP palaiko pakaitos simbolių „*“ ir „?“ Naudojimą darant paieškas. Pavyzdžiui, tarkime, kad pamiršome, kaip parašyti Franko vardą, ir tiesiog norėjome ieškoti vardo, prasidedančio „F“. Galėtume parašyti formulę
| 1 | = PAKEITIMAS ("F*", A1: E3, 2, FALSE) |
Taip E stulpelyje būtų galima rasti vardą Frank, o tada grąžinti reikšmę iš 2antra santykinė eilutė. Šiuo atveju atsakymas bus „mokslas“.
Netiksli atitiktis
Dažniausiai norėsite įsitikinti, kad paskutinis HLOOKUP argumentas yra klaidingas (arba 0), kad gautumėte tikslią atitiktį. Tačiau kartais galite ieškoti netikslios atitikties. Jei turite surūšiuotų duomenų sąrašą, taip pat galite naudoti HLOOKUP, kad grąžintumėte tos pačios arba kitos mažesnės prekės rezultatą. Tai dažnai naudojama sprendžiant didėjančius skaičių diapazonus, pvz., Mokesčių lentelėje ar komisinių premijas.
Tarkime, kad norite rasti pajamų, įvestų į langelį H2, mokesčio tarifą. H4 formulė gali būti tokia:
| 1 | = PERŽIŪRĖTI (H2, B1: F2, 2, TRUE) |
Šios formulės skirtumas yra tas, kad paskutinis mūsų argumentas yra „tiesa“. Mūsų konkrečiame pavyzdyje matome, kad kai mūsų individas gauna 45 000 USD pajamų, jiems bus taikomas 15%mokesčių tarifas.
Pastaba: Nors paprastai norime tikslios atitikties, o argumentas yra „False“, pamirštate nurodyti 4tūkst HLOOKUP argumentas, numatytasis yra „True“. Dėl to galite gauti netikėtų rezultatų, ypač kai kalbate apie teksto reikšmes.
Dinaminė eilutė
„HLOOKUP“ reikalauja pateikti argumentą, nurodant, iš kurios eilutės norite grąžinti vertę, tačiau tokia proga gali atsirasti, kai nežinote, kur bus eilutė, arba norite leisti vartotojui pakeisti, iš kurios eilutės grįžti. Tokiais atvejais gali būti naudinga naudoti MATCH funkciją, kad būtų galima nustatyti eilutės numerį.
Dar kartą apsvarstykime savo pažymių knygos pavyzdį su kai kuriomis G2 ir G4 įvestimis. Norėdami gauti stulpelio numerį, galime parašyti formulę
| 1 | = MATCH (G2, A1: A3, 0) |
Taip bus bandoma rasti tikslią „Įvertinimo“ padėtį diapazone A1: A3. Atsakymas bus 3. Žinodami tai, galime prijungti jį prie HLOOKUP funkcijos ir parašyti formulę G6 taip:
| 1 | = PAKEITIMAS (G4, A1: E3, MATCH (G2, A1: A3, 0), 0) |
Taigi funkcija MATCH bus įvertinta iki 3, o tai reiškia, kad HLOOKUP grąžins rezultatą iš 3rd eilutė A1: E3 diapazone. Apskritai gauname norimą „C“ rezultatą. Mūsų formulė dabar yra dinamiška, nes mes galime pakeisti žiūrimą eilutę arba ieškomą pavadinimą.
HLOOKUP apribojimai
Kaip minėta straipsnio pradžioje, didžiausias „HLOOKUP“ trūkumas yra tas, kad reikia, kad paieškos terminas būtų surastas kairiajame paieškos diapazono stulpelyje. Nors yra keletas išgalvotų triukų, kuriuos galite padaryti, kad tai įveiktumėte, įprasta alternatyva yra naudoti INDEX ir MATCH. Šis derinys suteikia daugiau lankstumo ir kartais netgi gali būti greitesnis skaičiavimas.
PALAIKYMAS „Google“ skaičiuoklėse
Funkcija HLOOKUP „Google“ skaičiuoklėse veikia taip pat, kaip ir „Excel“:
papildomi užrašai
Norėdami atlikti horizontalią paiešką, naudokite funkciją HLOOKUP. Jei jau esate susipažinę su VLOOKUP funkcija, HLOOKUP veikia lygiai taip pat, išskyrus tai, kad paieška atliekama horizontaliai, o ne vertikaliai. HLOOKUP ieško tikslios atitikties (range_lookup = FALSE) arba artimiausia atitiktis, lygi arba mažesnė už lookup_value (range_lookup = TRUE, tik skaitinės vertės) pirmoje lentelės_masyvo eilutėje. Tada ji grąžina atitinkamą reikšmę, n eilučių skaičių žemiau atitikties.
Naudodami HLOOKUP norėdami rasti tikslią atitiktį, pirmiausia nustatykite identifikavimo vertę, kurios norite ieškoti kaip lookup_value. Ši identifikavimo vertė gali būti SSN, darbuotojo ID, vardas ar kitas unikalus identifikatorius.
Toliau apibrėžiate diapazoną (vadinamą table_array), kuriame yra identifikatoriai viršutinėje eilutėje ir visos reikšmės, kurių galiausiai norite ieškoti žemiau esančiose eilutėse. SVARBU: unikalūs identifikatoriai turi būti viršutinėje eilutėje. Jei jų nėra, turite arba perkelti eilutę į viršų, arba naudoti MATCH / INDEX, o ne HLOOKUP.
Trečia, apibrėžkite eilutės numerį (eilutės_indeksas) iš table_array kad nori sugrįžti. Atminkite, kad pirmoji eilutė, kurioje yra unikalūs identifikatoriai, yra 1 eilutė. Antroji eilutė yra 2 eilutė ir t. T.
Galiausiai turite nurodyti, ar ieškoti tikslios atitikties (FALSE), ar artimiausios atitikties (TRUE) range_lookup. Jei pasirinkta tikslios atitikties parinktis ir nerasta tiksli atitiktis, grąžinama klaida (#N/A). Jei norite, kad formulė būtų tuščia arba „nerasta“, arba bet kokia kita vertė, o ne klaidos vertė (#N/A), naudokite funkciją IFERROR su HLOOKUP.
Jei norite naudoti apytikslę atitikties rinkinį, naudodami funkciją HLOOKUP: range_lookup = TIESA. Ši parinktis galima tik skaitinėms reikšmėms. Vertės turi būti surūšiuotos didėjančia tvarka.
HLOOKUP pavyzdžiai VBA
Taip pat galite naudoti funkciją HLOOKUP VBA. Tipas:application.worksheetfunction.hlookup (lookup_value, table_array, row_index_num, range_lookup)
Vykdydami šiuos VBA teiginius
| 123456 | Diapazonas ("G2") = Application.WorksheetFunction.HLookup (Diapazonas ("C1"), diapazonas ("A1: E3"), 1)Diapazonas ("H2") = Application.WorksheetFunction.HLookup (Diapazonas ("C1"), diapazonas ("A1: E3"), 2)Diapazonas ("I2") = Application.WorksheetFunction.HLookup (Diapazonas ("C1"), diapazonas ("A1: E3"), 3)Diapazonas ("G3") = Application.WorksheetFunction.HLookup (Diapazonas ("D1"), diapazonas ("A1: E3"), 1)Diapazonas ("H3") = Application.WorksheetFunction.HLookup (Diapazonas ("D1"), diapazonas ("A1: E3"), 2)Diapazonas ("I3") = Application.WorksheetFunction.HLookup (Diapazonas ("D1"), diapazonas ("A1: E3"), 3) |
duos šiuos rezultatus
Funkcijos argumentams („lookup_value“ ir pan.) Galite juos įvesti tiesiai į funkciją arba nustatyti kintamuosius, kuriuos naudoti.
Grįžkite į visų „Excel“ funkcijų sąrašą
