Lejupielādējiet darbgrāmatas piemēru
Šī apmācība parāda, kā lietot Excel HLOOKUP funkcija programmā Excel, lai atrastu vērtību.
HLOOKUP funkciju pārskats
Funkcija HLOOKUP Hlookup apzīmē horizontālu uzmeklēšanu. Tā meklē vērtību tabulas augšējā rindā. Pēc tam atgriež vērtību par noteiktu rindu skaitu uz leju no atrastās vērtības. Tas ir tāds pats kā vlookup, izņemot to, ka vērtības tiek meklētas horizontāli, nevis vertikāli.
(Ievērojiet, kā parādās formulas ievades)
Funkcijas HLOOKUP sintakse un ievade:
| 1 | = HLOOKUP (lookup_value, table_array, row_index_num, range_lookup) |
lookup_value - Vērtība, kuru vēlaties meklēt.
table_array -Tabula, no kuras iegūt datus.
rindas_indeksa_numurs - Rindas numurs, no kura izgūt datus.
range_lookup -[pēc izvēles] Būla vērtība, lai norādītu precīzu atbilstību vai aptuvenu atbilstību. Noklusējums = TRUE = aptuvena atbilstība.
Kas ir funkcija HLOOKUP?
Kā viena no vecākajām funkcijām izklājlapu pasaulē tiek izmantota funkcija HLOOKUP Horizontāli Uzmeklējumi. Tam ir daži ierobežojumi, kurus bieži pārvar citas funkcijas, piemēram, INDEX/MATCH. Turklāt lielākā daļa galdu ir veidoti vertikālā veidā, taču dažas reizes ir noderīgi meklēt horizontāli.
Pamata piemērs
Apskatīsim datu paraugu no atzīmju grāmatas. Mēs apskatīsim vairākus piemērus informācijas iegūšanai konkrētiem studentiem.
Ja mēs vēlamies uzzināt, kurā klasē ir Bobs, mēs uzrakstītu formulu:
| 1 | = HLOOKUP ("Bobs", A1: E3, 2, FALSE) |
Svarīgi atcerēties, ka vienumam, kuru meklējam (Bob), ir jāatrodas mūsu meklēšanas diapazona pirmajā rindā (A1: E3). Funkcijai mēs esam teikuši, ka vēlamies atgriezt vērtību no 2nd meklēšanas diapazona rinda, kas šajā gadījumā ir 2. rinda. Visbeidzot, mēs norādījām, ka vēlamies veikt an precīza atbilstība ievietojot False kā pēdējo argumentu. Šeit atbilde būs “Lasīšana”.
Sānu gals: Kā galīgo argumentu False vietā varat izmantot arī skaitli 0, jo tiem ir vienāda vērtība. Daži cilvēki dod priekšroku tam, jo rakstīšana ir ātrāka. Vienkārši ziniet, ka abi ir pieņemami.
Pārvietoti dati
Lai mūsu pirmajam piemēram pievienotu skaidrojumu, uzmeklēšanas vienumam nav jāatrodas izklājlapas 1. rindā, bet tikai meklēšanas diapazona pirmajā rindā. Izmantosim to pašu datu kopu:
Tagad atradīsim dabaszinātņu klases atzīmi. Mūsu formula būtu
| 1 | = HLOOKUP ("Zinātne", A2: E3, 2, FALSE) |
Šī joprojām ir derīga formula, jo mūsu meklēšanas diapazona pirmā rinda ir 2. rinda, kur tiks atrasts mūsu meklēšanas vienums “Zinātne”. Mēs atgriežam vērtību no 2nd meklēšanas diapazona rindā, kas šajā gadījumā ir 3. rinda. Atbilde ir “A-”.
Aizstājējzīmju lietošana
Funkcija HLOOKUP atbalsta aizstājējzīmju “*” un “?” Izmantošanu. veicot meklēšanu. Piemēram, pieņemsim, ka mēs bijām aizmirsuši rakstīt Frenka vārdu un vienkārši vēlējāmies meklēt vārdu, kas sākas ar “F”. Mēs varētu uzrakstīt formulu
| 1 | = HLOOKUP ("F*", A1: E3, 2, FALSE) |
Tas varētu atrast vārdu Frank slejā E un pēc tam atgriezt vērtību no 2nd relatīvā rinda. Šajā gadījumā atbilde būs “Zinātne”.
Nav precīza atbilstība
Lielāko daļu laika jūs vēlaties pārliecināties, vai HLOOKUP pēdējais arguments ir nepatiess (vai 0), lai iegūtu precīzu atbilstību. Tomēr ir gadījumi, kad jūs meklējat neprecīzu atbilstību. Ja jums ir sakārtotu datu saraksts, varat arī izmantot HLOOKUP, lai atgrieztu vienuma rezultātu, kas ir vienāds vai nākamais mazākais. To bieži izmanto, strādājot ar pieaugošu skaitļu diapazonu, piemēram, nodokļu tabulā vai komisijas prēmijās.
Pieņemsim, ka vēlaties atrast nodokļa likmi ienākumam, kas ievadīts šūnā H2. H4 formula var būt šāda:
| 1 | = UZSKATĪŠANA (H2, B1: F2, 2, TRUE) |
Atšķirība šajā formulā ir tāda, ka mūsu pēdējais arguments ir “Patiess”. Mūsu konkrētajā piemērā mēs redzam, ka tad, kad mūsu indivīds iegūs ienākumus 45 000 ASV dolāru apmērā, viņiem būs 15%nodokļa likme.
Piezīme: Lai gan mēs parasti vēlamies precīzu atbilstību ar argumentu False, jūs aizmirstat norādīt 4tūkst HLOOKUP argumentā noklusējuma vērtība ir True. Tā rezultātā jūs varat iegūt negaidītus rezultātus, it īpaši, strādājot ar teksta vērtībām.
Dinamiska rinda
HLOOKUP pieprasa norādīt argumentu, kurā rindā vēlaties atgriezt vērtību, taču var gadīties gadījumi, kad nezināt, kur šī rinda būs, vai arī vēlaties ļaut lietotājam mainīt, no kuras rindas atgriezties. Šādos gadījumos var būt noderīgi izmantot funkciju MATCH, lai noteiktu rindas numuru.
Aplūkosim vēlreiz mūsu atzīmju grāmatas piemēru ar dažiem ievadiem G2 un G4. Lai iegūtu kolonnas numuru, mēs varētu uzrakstīt formulu
| 1 | = MATCH (G2, A1: A3, 0) |
Tas mēģinās atrast precīzu “Novērtējuma” pozīciju diapazonā A1: A3. Atbilde būs 3. Zinot to, mēs varam to pievienot HLOOKUP funkcijai un uzrakstīt formulu G6 šādi:
| 1 | = HLOOKUP (G4, A1: E3, MATCH (G2, A1: A3, 0), 0) |
Tātad funkcija MATCH tiks novērtēta līdz 3, un tas liek HLOOKUP atgriezt rezultātu no 3rd rinda A1: E3 diapazonā. Kopumā mēs iegūstam vēlamo “C” rezultātu. Mūsu formula tagad ir dinamiska, jo mēs varam mainīt vai nu apskatāmo rindu, vai meklējamo nosaukumu.
HLOOKUP ierobežojumi
Kā minēts raksta sākumā, HLOOKUP lielākais kritums ir tāds, ka meklēšanas vienumam ir jāatrod meklēšanas diapazona kreisajā slejā. Lai gan ir daži izsmalcināti triki, ko varat darīt, lai to pārvarētu, izplatītā alternatīva ir izmantot indeksu un atbilstību. Šī kombinācija sniedz jums lielāku elastību, un dažreiz tas var būt pat ātrāks aprēķins.
HLOOKUP pakalpojumā Google izklājlapas
Funkcija HLOOKUP Google izklājlapās darbojas tieši tāpat kā programmā Excel:
papildu piezīmes
Izmantojiet funkciju HLOOKUP, lai meklētu horizontāli. Ja esat jau iepazinies ar funkciju VLOOKUP, HLOOKUP darbojas tieši tāpat, izņemot to, ka meklēšana tiek veikta horizontāli, nevis vertikāli. HLOOKUP meklē precīzu atbilstību (range_lookup = FALSE) vai tuvākā atbilstība, kas ir vienāda vai mazāka par lookup_value (range_lookup = TRUE, tikai skaitliskas vērtības) tabulas_masīva pirmajā rindā. Pēc tam tas atgriež atbilstošu vērtību, n rindu skaitu zem atbilstības.
Izmantojot HLOOKUP, lai atrastu precīzu atbilstību, vispirms definējiet identifikācijas vērtību, kuru vēlaties meklēt kā lookup_value. Šī identifikācijas vērtība var būt SSN, darbinieka ID, vārds vai kāds cits unikāls identifikators.
Tālāk jūs definējat diapazonu (ko sauc par tabulas_masīvs), kas satur identifikatorus augšējā rindā un visas vērtības, kuras jūs galu galā vēlaties meklēt rindās zem tās. SVARĪGI: unikālajiem identifikatoriem jābūt augšējā rindā. Ja tie nav, jums vai nu jāpārvieto rinda uz augšu, vai HLOOKUP vietā jāizmanto MATCH / INDEX.
Treškārt, definējiet rindas numuru (rindas_indekss) no tabulas_masīvs ka vēlaties atgriezties. Paturiet prātā, ka pirmā rinda, kurā ir unikālie identifikatori, ir 1. rinda. Otrā rinda ir 2. rinda utt.
Visbeidzot, sadaļā jānorāda, vai meklēt precīzu atbilstību (FALSE) vai tuvāko atbilstību (TRUE) range_lookup. Ja ir atlasīta precīzās atbilstības opcija un precīza atbilstība netiek atrasta, tiek atgriezta kļūda (#N/A). Lai formula atgrieztos tukša vai “nav atrasta” vai kāda cita vērtība kļūdas vērtības (#N/A) vietā, izmantojiet funkciju IFERROR kopā ar HLOOKUP.
Lai izmantotu funkciju HLOOKUP, lai atgrieztu aptuvenu atbilstības kopu: range_lookup = PATIESA. Šī opcija ir pieejama tikai skaitliskām vērtībām. Vērtības jāsakārto augošā secībā.
HLOOKUP piemēri VBA
VBA varat izmantot arī funkciju HLOOKUP. Tips:application.worksheetfunction.hlookup (lookup_value, table_array, row_index_num, range_lookup)
Izpildot šādus VBA paziņojumus
| 123456 | Diapazons ("G2") = Application.WorksheetFunction.HLookup (Diapazons ("C1"), diapazons ("A1: E3"), 1)Diapazons ("H2") = Application.WorksheetFunction.HL Lookup (Diapazons ("C1"), Diapazons ("A1: E3"), 2)Diapazons ("I2") = Application.WorksheetFunction.HLookup (Diapazons ("C1"), diapazons ("A1: E3"), 3)Diapazons ("G3") = Application.WorksheetFunction.HLookup (Diapazons ("D1"), diapazons ("A1: E3"), 1)Diapazons ("H3") = Application.WorksheetFunction.HLookup (Diapazons ("D1"), diapazons ("A1: E3"), 2)Diapazons ("I3") = Application.WorksheetFunction.HLookup (Diapazons ("D1"), diapazons ("A1: E3"), 3) |
radīs šādus rezultātus
Funkcijas argumentiem (lookup_value utt.) Varat tos ievadīt tieši funkcijā vai definēt mainīgos, ko izmantot.
Atgriezieties visu Excel funkciju sarakstā
