Cum să remediați o eroare #N/A în funcția VLOOKUP

Notă:  Dorim să vă oferim cel mai recent conținut de ajutor, cât mai rapid posibil, în limba dvs. Această pagină a fost tradusă automatizat și poate conține erori gramaticale sau inexactități. Scopul nostru este ca acest conținut să vă fie util. Vă rugăm să ne spuneți dacă informațiile v-au fost utile, în partea de jos a acestei pagini. Aici se află articolul în limba engleză , ca să îl puteți consulta cu ușurință.

Acest subiect descrie cele mai comune motive pentru un rezultat eronate pe funcția VLOOKUP și oferă sugestii pentru utilizarea funcțiilor INDEX și MATCH în schimb.

Sfat: De asemenea, face referire la cartelă de referințe rapide: VLOOKUP-sfaturi de depanare care prezintă motivele comune pentru problemele #NA într-un fișier PDF convenabil. Puteți să partajați fișierul PDF cu alte persoane sau imprimat pentru propria referință.

Problemă: Valoarea de căutare nu se află în prima coloană a argumentului matrice_tabel

O restricție de VLOOKUP este că acesta poate să vadă doar pentru valori din coloana din stânga din matrice de tabel. Dacă nu vă valoarea de căutat este în prima coloană din matrice, veți vedea eroarea #N/A.

În tabelul următor, dorim să regăsim numărul de unități vândute pentru Kale.

Eroarea #NA în VLOOKUP: Valoarea de căutare nu se află în prima coloană a matricei de tabel

Eroarea #N/A rezultate, deoarece valoarea de căutat "Kale" apare în a doua coloană (legume și fructe) din argumentul matrice_tabel A2:C10. În acest caz, Excel este căutați aceasta în coloana A, coloana B.

Soluție: puteți încerca să remediați prin ajustarea vă VLOOKUP pentru a face referire coloana corecte. Dacă nu este posibil, apoi încercați mutarea coloanelor. Care, de asemenea, poate fi foarte lucru imposibil, dacă aveți mare sau complexă foi de calcul în cazul în care valorile celulelor sunt rezultatele alte calcule- sau poate exista alte motive logice ce pur și simplu nu puteți muta coloanele în jurul. Soluția este să utilizați o combinație a funcțiilor INDEX și MATCH, care poate căuta o valoare dintr-o coloană, indiferent de poziția sa în locația din tabelul de căutare. Consultați secțiunea următoare.

Luați în considerare utilizarea INDEX/MATCH în schimb

INDEX și MATCH sunt bun opțiuni pentru multe cazuri în care VLOOKUP nu corespunde necesităților dvs. Avantajul cheie INDEX/potrivire este că puteți căuta o valoare dintr-o coloană în orice locație din tabelul de căutare. INDEX returnează o valoare dintr-un tabel/zonă precizată, în funcție de poziția sa. POTRIVIRE returnează poziția relativă a unei valori dintr-un tabel/zonă. Utilizarea funcțiilor INDEX și MATCH împreună într-o formulă pentru a căuta o valoare dintr-un tabel/matrice, specificând poziția relativă a valorii în matricea de tabel.

Există mai multe avantaje ale utilizării INDEX/MATCH în locul VLOOKUP:

  • Cu INDEX și MATCH, și valoarea returnată nu trebuie să fie în aceeași coloană ca căutare. Acest lucru este diferit de VLOOKUP, în care valoarea returnată trebuie să fie în zona specificată. Cum se face acest lucru? Cu VLOOKUP, trebuie să aflați numărul de coloane care conține valoarea returnată. În timp ce acest lucru nu poate părea dificilă, poate fi dificil atunci când aveți un tabel mari și au pentru a contoriza numărul de coloane. De asemenea, dacă vă Adăugare/eliminare o coloană în tabel, trebuie să povestesc și actualizați argumentul num_index_col . Cu INDEX și MATCH, numărând nu este necesar ca coloană de căutare este diferită de coloană care conține valoarea returnată.

  • Cu INDEX și MATCH, puteți specifica un rând sau a unei coloane dintr-o matrice, sau să specificați ambele. Acest lucru înseamnă că puteți căuta valori atât pe verticală și pe orizontală.

  • INDEX și MATCH pot fi utilizate pentru a căuta valori din orice coloană. Spre deosebire de VLOOKUP, în care vă pot doar căutarea într-o valoare din prima coloană dintr-un tabel, INDEX și MATCH vor funcționa dacă vă valoarea de căutat este în prima coloană, ultimul sau oriunde în între.

  • INDEX și MATCH oferă flexibilitate din dinamic referința la coloana care conține valoarea returnată. Acest lucru înseamnă că puteți adăuga coloane la tabel fără întreruperea INDEX și MATCH. Pe de altă parte, VLOOKUP încalcă dacă trebuie să adăugați o coloană în tabel, deoarece acest lucru este o referință statice la tabel.

  • INDEX și MATCH oferă mai multă flexibilitate cu potriviri. INDEX și MATCH puteți găsi o potrivire exactă sau o valoare care este mai mare sau mai mică decât valoarea de căutat. VLOOKUP va arăta numai pentru o cel mai bine cu o valoare (implicit) sau o valoare exactă. VLOOKUP, de asemenea, presupune implicit că prima coloană din tabelul matrice este sortată în ordine alfabetică, și să presupunem că tabel nu este configurat așa, VLOOKUP va returna prima cel mai bine în tabel, ceea ce nu pot fi date pe care le căutați.

Sintaxă

Pentru a construi sintaxa pentru INDEX/potrivire, trebuie să utilizați argumentul matrice/referințe din funcția INDEX și imbrica sintaxa MATCH în interiorul său. Acest lucru ia forma:

= INDEX(array or reference, MATCH(lookup_value,lookup_array,[match_type])

Să utilizăm INDEX/MATCH pentru a înlocui VLOOKUP din exemplul de mai sus. Sintaxa va arăta astfel:

=INDEX(C2:C10,MATCH(B13,B2:B10,0))

Mai simplu, înseamnă:

= INDEX (returnează o valoare din C2:C10, care se va potrivi (Kale, care se află undeva din matrice B2:B10, în care valoarea returnată este prima valoare corespunde Kale))

Funcțiile INDEX și MATCH pot fi utilizate ca înlocuitori pentru VLOOKUP

Formula caută prima valoare din C2:C10 care corespunde la Kale (din B7) și returnează valoarea din C7 (100), adică prima valoare care se potrivește cu Kale.

Problemă: Nu se găsește potrivirea exactă

Când argumentul zonă_căutare este FALSE, și VLOOKUP este imposibilitatea de a găsi o potrivire exactă în datele-returnează eroarea #N/A.

Soluție: dacă sunteți sigur date relevante există în foaia de calcul și VLOOKUP nu este capturare acesta, dedicați timp pentru a verifica că celulele referite nu au ascunse spații sau caractere neimprimabile. De asemenea, asigurați-vă că celulele urmați tipul de date corect. De exemplu, celulele care conțin numere ar trebui să fie formatat ca număr, și nu Text.

De asemenea, luați în considerare utilizarea fie CLEANTRIM funcție sau pentru a curăța date în celule.

Problemă: Valoarea de căutare este mai mică decât cea mai mică valoare din matrice

Dacă argumentul zonă_căutare este setată la TRUE, și valoarea de căutat este mai mică decât cea mai mică valoare din matrice, veți vedea eroarea #N/A. Arată TRUE pentru un aproximativă se potrivesc cu din matrice și returnează cel mai apropiat valoare mai mică decât valoarea de căutat.

În exemplul următor, valoarea de căutare este 100, dar nu există valori în zona B2:C10 mai mici decât 100; de aici apare eroarea.

Eroarea N/A din VLOOKUP atunci când valoarea de căutare este mai mică decât cea mai mică valoare din matrice

Soluție:

  • Corectați valoarea de căutare după cum este necesar.

  • Dacă nu puteți să modificați valoarea de căutat și aveți nevoie de mai multă flexibilitate cu potrivirea valorilor, luați în considerare utilizarea INDEX/MATCH în loc de VLOOKUP, consultați secțiunea de mai sus în acest articol. Cu INDEX/potrivire, poate să vadă-valori mai mari decât, mai puțin la sau egală cu valoarea de căutat. Pentru mai multe informații despre utilizarea INDEX/MATCH în loc de VLOOKUP, consultați secțiunea anterioară din acest subiect.

Problemă: Coloana de căutare nu este sortată în ordine ascendentă

Dacă argumentul zonă_căutare este setată la TRUE, una dintre coloanele de căutare nu este sortată în ordine ascendentă (A-Z), veți vedea eroarea #N/A.

Soluție:

  • Modificați funcția VLOOKUP să caute o potrivire exactă. Pentru a face acest lucru, setați argumentul zonă_căutare la FALSE. Sortarea nu este necesar pentru FALSE.

  • Utilizați funcția INDEX/MATCH pentru a căuta o valoare într-un tabel nesortat.

Problemă: Valoarea este un număr mare în virgulă mobilă

Dacă aveți valori de timp sau un număr zecimal mare din celule, Excel returnează eroarea #N/A, din cauza flotantă punct de precizie. Flotantă punct numerele sunt numere care urmează după un punct zecimal. (Excel stochează valori de timp ca flotantă punct de numere.) Excel nu poate stoca numerele cu puncte flotantă foarte mari, astfel încât pentru funcția să funcționeze corect, flotante indicați numere va trebui să se rotunjește la 5 poziții zecimale.

Soluție: Scurtați numerele, rotunjindu-le la cinci zecimale cu funcția ROUND.

Aveți o întrebare cu privire la funcții?

Postați o întrebare în forumul Comunității Excel

Ajutați-ne să îmbunătățim Excel

Aveți sugestii despre cum putem îmbunătăți următoarea versiune de Excel? În acest caz, consultați subiectele de la UserVoice pentru Excel.

Consultați și

Extindeți-vă competențele Office
Explorați instruirea
Fiți primul care obține noile caracteristici
Alăturați-vă utilizatorilor Office Insider

Au fost utile aceste informații?

Vă mulțumim pentru feedback!

Vă mulțumim pentru feedback! Se pare că ar fi util să luați legătura cu unul dintre agenții noștri de asistență Office.

×