01 din 03
Găsiți potriviri aproximative pentru datele cu VLOOKUP din Excel
Cum funcționează funcția VLOOKUP
Funcția VLOOKUP a Excel, care reprezintă o căutare verticală , poate fi utilizată pentru a căuta informații specifice dintr-un tabel de date sau o bază de date.
VLOOKUP returnează în mod normal un singur câmp de date ca ieșire. Cum face acest lucru este:
- Furnizați un nume sau un lookup_value care indică VLOOKUP în care rând sau înregistrare a tabelului de date să căutați datele dorite
- Furnizați numărul coloanei - cunoscut sub numele de col_index_num - al datelor pe care le căutați
- Funcția caută lookup_value în prima coloană a tabelului de date
- VLOOKUP apoi localizează și returnează informațiile pe care le căutați dintr-un alt câmp din aceeași înregistrare folosind numărul de coloană furnizat
Sortarea datelor mai întâi
Deși nu este întotdeauna necesar, este de obicei cel mai bine să se sorteze mai întâi intervalul de date pe care VLOOKUP le caută în ordine ascendentă folosind prima coloană din intervalul pentru cheia de sortare.
Dacă datele nu sunt sortate, VLOOKUP poate întoarce un rezultat incorect.
Sintaxa și argumentele funcției VLOOKUP
Sintaxa unei funcții se referă la aspectul funcției și include numele funcției, paranteze și argumente .
Sintaxa pentru funcția VLOOKUP este:
= VLOOKUP (lookup_value, table_array, col_index_num, interval_lookup)
lookup _value - (necesar) valoarea de căutat - cum ar fi cantitatea vândută în imaginea de mai sus
table_array - (necesar) acesta este tabelul de date pe care VLOOKUP îl caută pentru a găsi informațiile pe care le urmăriți .
- Table_array trebuie să conțină cel puțin două coloane de date
- Prima coloană conține, în mod normal, lookup_value
col_index_num - (obligatoriu) numărul coloanei valorii pe care doriți să o găsiți.
- Numerotarea începe cu coloana search_key ca coloana 1
- Dacă col_index_num este setat la un număr mai mare decât numărul de coloane selectate în argumentul table_array a #REF! eroarea este returnată de către funcție
range_lookup - (opțional) indică dacă intervalul este sau nu ordonat în ordine crescătoare.
- Datele din prima coloană sunt utilizate ca chei de sortare
- O valoare booleană - TRUE sau FALSE sunt singurele valori acceptabile
- Dacă este omisă, valoarea este setată la TRUE în mod implicit
- Dacă este setat la TRUE sau omis și prima coloană a intervalului nu este sortată în ordine ascendentă, poate apărea un rezultat incorect
- Dacă este setată la TRUE sau omisă și nu se găsește o potrivire exactă pentru valoarea _valuei de căutare , cea mai apropiată potrivire care este mai mică în dimensiune sau valoare este utilizată ca tasta search_
- Dacă este setat la FALSE, VLOOKUP acceptă doar o potrivire exactă pentru _value de căutare . Dacă există mai multe valori de potrivire, prima valoare de potrivire este returnată
- Dacă este setat la FALSE și nu este găsit nici o valoare potrivită pentru search_key , funcția este returnată de o eroare # N / A
Exemplu: găsiți rata de reducere pentru cantitatea achiziționată
Exemplul din imaginea de mai sus utilizează funcția VLOOKUP pentru a găsi rata de actualizare care variază în funcție de cantitatea de articole achiziționate.
Exemplul arată că reducerea pentru achiziționarea a 19 articole este de 2%. Aceasta deoarece coloana Cantitate conține valori de valori. Ca rezultat, VLOOKUP nu poate găsi o potrivire exactă. În schimb, trebuie găsit un meci aproximativ pentru a returna rata de actualizare corectă.
Pentru a găsi potriviri aproximative:
- sortați datele în table_array în ordine crescătoare;
- setați argumentul range_lookup la TRUE
În exemplu, următoarea formulă care conține funcția VLOOKUP este utilizată pentru a găsi discountul pentru cantitățile de bunuri achiziționate.
= VLOOKUP (C2, $ C $ 5: $ D $ 8,2, TRUE)
Chiar dacă această formulă poate fi doar tastată într-o celulă de foaie de lucru, o altă opțiune, așa cum este folosită cu pașii enumerați mai jos, este de a utiliza caseta de dialog a funcției pentru a introduce argumentele acesteia.
- Folosind caseta de dialog este de obicei mai ușor să introduceți corect argumentele unei funcții.
Deschiderea casetei de dialog VLOOKUP
Pașii utilizați pentru a introduce funcția VLOOKUP prezentată în imaginea de mai sus în celula B2 sunt:
- Faceți clic pe celula B2 pentru a deveni celula activă - locația unde sunt afișate rezultatele funcției VLOOKUP
- Faceți clic pe fila Formule .
- Selectați Lookup & Reference din panglică pentru a deschide lista verticală de funcții
- Faceți clic pe VLOOKUP din listă pentru a afișa caseta de dialog a funcției
02 din 03
Introducerea argumentelor funcției VLOOKUP ale Excel
Afișarea referințelor la celule
Argumentele pentru funcția VLOOKUP sunt introduse în linii separate ale casetei de dialog așa cum se arată în imaginea de mai sus.
Referințele celulelor care trebuie utilizate ca argumente pot fi introduse în linia corectă sau, așa cum se face în etapele de mai jos, indicând, care implică evidențierea domeniului dorit de celule cu indicatorul mouse-ului, pot fi folosite pentru a le introduce în caseta de dialog .
Avantajele utilizării indicării includ:
- Este mai rapid decât tastarea;
- Se fac mai puține greșeli de introducere a referințelor corecte ale celulelor.
Utilizarea referințelor de celule relative și absolute cu argumente
Nu este neobișnuit să utilizați mai multe copii ale VLOOKUP pentru a returna diferite informații din același tabel de date. Pentru a face mai ușor acest lucru, de multe ori VLOOKUP poate fi copiat de la o celulă la alta. Când funcțiile sunt copiate în alte celule, trebuie să aveți grijă ca referințele de celule rezultate să fie corecte, având în vedere noua locație a funcției.
În imaginea de mai sus, semnele de dolari ( $ ) înconjoară referințele de celule pentru argumentul table_array care indică faptul că acestea sunt referințe absolute ale celulelor , ceea ce înseamnă că ele nu se vor schimba dacă funcția este copiată într-o altă celulă. Acest lucru este de dorit deoarece numeroasele copii ale VLOOKUP ar face referire la același tabel de date ca sursă de informație.
Referința celulară folosită pentru lookup_value, pe de altă parte , nu este înconjurată de semnele dolarului, ceea ce o face o referință de celule relativă. Referințele de celule relative se modifică atunci când sunt copiate pentru a reflecta noua lor locație în raport cu poziția datelor la care se referă.
Introducerea argumentelor funcției
- Faceți clic pe linia Lookup _value din caseta de dialog VLOOKUP
- Faceți clic pe celula C2 din foaia de lucru pentru a introduce această referință de celule ca argumentul search_key
- Faceți clic pe linia Table_array a casetei de dialog
- Evidențiați celulele C5 până la D8 din foaia de lucru pentru a intra în acest interval ca argumentul Table_array - titlurile tabelului nu sunt incluse
- Apăsați tasta F4 de pe tastatură pentru a modifica intervalul până la referințele absolute ale celulelor
- Faceți clic pe linia Col_index_num a casetei de dialog
- Introduceți a 2 pe această linie ca argumentul Col_index_num , deoarece ratele de scont sunt situate în coloana 2 a argumentului Table_array
- Faceți clic pe linia Range_lookup a casetei de dialog
- Introduceți cuvântul True drept argumentul Range_lookup
- Apăsați tasta Enter de pe tastatură pentru a închide caseta de dialog și a reveni la foaia de lucru
- Răspunsul 2% (rata de actualizare pentru cantitatea cumpărată) trebuie să apară în celula D2 a foii de lucru
- Când faceți clic pe celula D2, funcția completă = VLOOKUP (C2, $ C $ 5: $ D $ 8,2, TRUE) apare în bara de formula de deasupra foii de lucru
De ce VLOOKUP a returnat 2% ca rezultat
- În exemplu, coloana Cantitate nu conține o potrivire exactă pentru valoarea search_key de 19.
- Deoarece argumentul is_sorted este setat la TRUE, VLOOKUP va găsi o potrivire aproximativă cu valoarea search_key .
- Valoarea cea mai apropiată de mărime care este încă mai mică decât valoarea search_key de 19 este 11.
- VLOOKUP, prin urmare, caută procentul de reducere în rândul care conține 11 și, ca rezultat, returnează o rată de actualizare de 2%.
03 din 03
Excel VLOOKUP nu funcționează: # N / A și #REF Errors
VLOOKUP Mesaje de eroare
Următoarele mesaje de eroare sunt asociate cu VLOOKUP.
A # N / A ("valoarea nu este disponibilă") Eroare este afișată Dacă:
- Valoarea de căutare nu se găsește în prima coloană a argumentului intervalului
- Argumentul Table_array este inexact. De exemplu, argumentul poate include coloane goale în partea stângă a intervalului
- Argumentul Range_lookup este setat la FALSE și o potrivire exactă pentru argumentul search_key nu poate fi găsită în prima coloană a intervalului
- Argumentul Range_lookup este setat la TRUE și toate valorile din prima coloană a intervalului sunt mai mari decât key search_
Un #REF! ("referință depășită") Eroare este afișată Dacă:
- Argumentul Col_index_num este mai mare decât numărul de coloane din table_array