Descărcați exemplul de registru de lucru
Acest tutorial arată cum să utilizați fișierul Funcția Excel HLOOKUP în Excel pentru a căuta o valoare.
Prezentare generală a funcției HLOOKUP
Funcția HLOOKUP Hlookup înseamnă căutare orizontală. Se caută o valoare în rândul de sus al unui tabel. Apoi returnează o valoare un număr specificat de rânduri în jos din valoarea găsită. Este la fel ca un vlookup, cu excepția cazului în care caută valori pe orizontală în loc de verticală.
(Observați cum apar datele introduse de formulă)
Sintaxa și intrarea funcției HLOOKUP:
| 1 | = HLOOKUP (valoare_cercare, tabel_array, rând_index_num, interval_cercare) |
valoare_cercare - Valoarea pe care doriți să o căutați.
table_array -Tabelul din care se pot prelua date.
rând_index_num - Numărul rândului din care să extrageți datele.
range_lookup - [opțional] Un boolean pentru a indica potrivirea exactă sau potrivirea aproximativă. Implicit = ADEVĂRAT = potrivire aproximativă.
Ce este funcția HLOOKUP?
Fiind una dintre funcțiile mai vechi din lumea foilor de calcul, funcția HLOOKUP este folosită Horizontală Căutări. Are câteva limitări care sunt adesea depășite cu alte funcții, cum ar fi INDEX / MATCH. De asemenea, majoritatea tabelelor sunt construite în mod vertical, dar există de câteva ori când este util să căutați pe orizontală.
Exemplu de bază
Să analizăm un eșantion de date dintr-o carte de note. Vom aborda câteva exemple de extragere a informațiilor pentru anumiți studenți.
Dacă vrem să aflăm în ce clasă este Bob, am scrie formula:
| 1 | = HLOOKUP („Bob”, A1: E3, 2, FALSE) |
Lucrurile importante de reținut sunt faptul că elementul pe care îl căutăm (Bob) trebuie să se afle în primul rând din gama noastră de căutare (A1: E3). Am spus funcției că dorim să returnăm o valoare din 2nd rândul intervalului de căutare, care în acest caz este rândul 2. În cele din urmă, am indicat că dorim să facem un potrivire perfecta plasând Fals ca ultim argument. Aici, răspunsul va fi „Lectură”.
Sfat lateral: De asemenea, puteți utiliza numărul 0 în loc de Fals ca argument final, deoarece acestea au aceeași valoare. Unii oameni preferă acest lucru, deoarece este mai rapid să scrieți. Știți doar că ambele sunt acceptabile.
Date schimbate
Pentru a adăuga câteva clarificări primului nostru exemplu, elementul de căutare nu trebuie să se afle în rândul 1 al foii de calcul, ci doar primul rând din intervalul de căutare. Să folosim același set de date:
Acum, să găsim nota pentru clasa Știință. Formula noastră ar fi
| 1 | = HLOOKUP („Știință”, A2: E3, 2, FALS) |
Aceasta este încă o formulă validă, deoarece primul rând al intervalului nostru de căutare este rândul 2, acesta fiind locul în care va fi găsit termenul nostru de căutare „Știință”. Întoarcem o valoare din 2nd rândul intervalului de căutare, care în acest caz este rândul 3. Răspunsul este „A-“.
Utilizarea wildcard
Funcția HLOOKUP acceptă utilizarea comodinelor „*” și „?” când faci căutări. De exemplu, să spunem că am uitat cum să scriem numele lui Frank și am vrut doar să căutăm un nume care începe cu „F”. Am putea scrie formula
| 1 | = HLOOKUP ("F *", A1: E3, 2, FALSE) |
Aceasta ar putea găsi numele Frank în coloana E și apoi va returna valoarea de la 2nd rând relativ. În acest caz, răspunsul va fi „Știință”.
Potrivire neexactă
De cele mai multe ori, va trebui să vă asigurați că ultimul argument din HLOOKUP este Fals (sau 0), astfel încât să obțineți o potrivire exactă. Cu toate acestea, există de câteva ori când este posibil să căutați o potrivire neexactă. Dacă aveți o listă de date sortate, puteți utiliza, de asemenea, HLOOKUP pentru a returna rezultatul pentru elementul care este fie același, fie următorul cel mai mic. Acest lucru este adesea utilizat atunci când se ocupă cu creșterea gamelor de numere, cum ar fi într-un tabel de impozite sau bonusuri de comisioane.
Să presupunem că doriți să găsiți rata de impozitare pentru un venit introdus în celula H2. Formula din H4 poate fi:
| 1 | = HLOOKUP (H2, B1: F2, 2, TRUE) |
Diferența în această formulă este că ultimul nostru argument este „Adevărat”. În exemplul nostru specific, putem vedea că atunci când individul nostru introduce un venit de 45.000 USD, va avea o rată de impozitare de 15%.
Notă: Deși, de obicei, dorim o potrivire exactă cu False ca argument, uitați să specificați 4a argument într-un HLOOKUP, valoarea implicită este True. Acest lucru vă poate determina să obțineți rezultate neașteptate, mai ales atunci când vă ocupați de valorile textului.
Rând dinamic
HLOOKUP vă cere să argumentați din care rând doriți să returnați o valoare, dar poate apărea ocazia când nu știți unde va fi rândul sau doriți să permiteți utilizatorului să schimbe din ce rând să revină. În aceste cazuri, poate fi util să folosiți funcția MATCH pentru a determina numărul rândului.
Să luăm din nou în considerare exemplul de carte de note, cu câteva date în G2 și G4. Pentru a obține numărul coloanei, am putea scrie o formulă de
| 1 | = MATCH (G2, A1: A3, 0) |
Aceasta va încerca să găsească poziția exactă a „Gradului” în intervalul A1: A3. Răspunsul va fi 3. Știind acest lucru, îl putem conecta la o funcție HLOOKUP și putem scrie o formulă în G6 astfel:
| 1 | = HLOOKUP (G4, A1: E3, MATCH (G2, A1: A3, 0), 0) |
Deci, funcția MATCH se va evalua la 3, iar asta îi spune HLOOKUP să returneze un rezultat din 3rd rând din gama A1: E3. În general, obținem rezultatul dorit de „C”. Formula noastră este dinamică acum prin faptul că putem schimba fie rândul pe care trebuie să îl privim, fie numele pe care să îl căutăm.
Limitări HLOOKUP
După cum sa menționat la începutul articolului, cea mai mare cădere a HLOOKUP este că acesta necesită ca termenul de căutare să fie găsit în coloana din stânga cea mai mare a intervalului de căutare. Deși există câteva trucuri fanteziste pe care le puteți face pentru a depăși acest lucru, alternativa obișnuită este să utilizați INDEX și MATCH. Acest combo vă oferă mai multă flexibilitate și uneori poate fi chiar un calcul mai rapid.
CĂUTARE în Foi de calcul Google
Funcția HLOOKUP funcționează exact la fel în Foi de calcul Google ca în Excel:
Note Aditionale
Utilizați funcția HLOOKUP pentru a efectua o căutare orizontală. Dacă sunteți deja familiarizați cu funcția VLOOKUP, un HLOOKUP funcționează exact în același mod, cu excepția căutării se efectuează orizontal în loc de vertical. HLOOKUP caută o potrivire exactă (range_lookup = FALS) sau cea mai apropiată potrivire care este egală sau mai mică decât valoarea de căutare (range_lookup = ADEVĂRAT, numai valori numerice) în primul rând al table_array. Apoi returnează o valoare corespunzătoare, n număr de rânduri sub potrivire.
Când utilizați un HLOOKUP pentru a găsi o potrivire exactă, mai întâi definiți o valoare de identificare pe care doriți să o căutați ca fiind valoare_cercare. Această valoare de identificare poate fi un SSN, un ID de angajat, un nume sau un alt identificator unic.
Apoi definiți intervalul (numit table_array) care conține identificatorii din rândul de sus și orice valori pe care doriți să le căutați în ultimul rând. IMPORTANT: identificatorii unici trebuie să fie în rândul de sus. Dacă nu sunt, trebuie fie să mutați rândul în partea de sus, fie să utilizați MATCH / INDEX în loc de HLOOKUP.
În al treilea rând, definiți numărul rândului (rând_index) din table_array că vrei să te întorci. Rețineți că primul rând, care conține identificatorii unici, este rândul 1. Al doilea rând este rândul 2 etc.
În cele din urmă, trebuie să indicați dacă să căutați o potrivire exactă (FALSĂ) sau cea mai apropiată potrivire (ADEVĂRAT) în range_lookup. Dacă este selectată opțiunea de potrivire exactă și nu se găsește o potrivire exactă, se returnează o eroare (# N / A). Pentru ca formula să revină necompletată sau „nu a fost găsită” sau orice altă valoare în locul valorii de eroare (# N / A) utilizați funcția IFERROR cu HLOOKUP.
Pentru a utiliza funcția HLOOKUP pentru a returna un set aproximativ de potrivire: range_lookup = ADEVĂRAT. Această opțiune este disponibilă numai pentru valorile numerice. Valorile trebuie sortate în ordine crescătoare.
HLOOKUP Exemple în VBA
De asemenea, puteți utiliza funcția HLOOKUP în VBA. Tip:application.worksheetfunction.hlookup (lookup_value, table_array, row_index_num, range_lookup)
Executarea următoarelor instrucțiuni VBA
| 123456 | Range ("G2") = Application.WorksheetFunction.HLookup (Range ("C1"), Range ("A1: E3"), 1)Range ("H2") = Application.WorksheetFunction.HLookup (Range ("C1"), Range ("A1: E3"), 2)Range ("I2") = Application.WorksheetFunction.HLookup (Range ("C1"), Range ("A1: E3"), 3)Range ("G3") = Application.WorksheetFunction.HLookup (Range ("D1"), Range ("A1: E3"), 1)Range ("H3") = Application.WorksheetFunction.HLookup (Range ("D1"), Range ("A1: E3"), 2)Range ("I3") = Application.WorksheetFunction.HLookup (Range ("D1"), Range ("A1: E3"), 3) |
va produce următoarele rezultate
Pentru argumentele funcției (valoare_consultare etc.), le puteți introduce direct în funcție sau puteți defini variabile de utilizat.
Reveniți la lista tuturor funcțiilor din Excel
