Note: The other languages of the website are Google-translated. Back to English
English English

Găsiți valorile lipsă

Există cazuri când trebuie să comparați două liste pentru a verifica dacă o valoare a listei A există în lista B în Excel. De exemplu, aveți o listă de produse și doriți să verificați dacă produsele din lista dvs. există în lista de produse furnizată de furnizorul dvs. Pentru a îndeplini această sarcină, aici am enumerat trei moduri mai jos, nu ezitați să o alegeți pe cea care vă place.

găsiți valorile lipsă 1

Găsiți valorile lipsă cu MATCH, ISNA și IF
Găsiți valorile lipsă cu VLOOKUP, ISNA și IF
Găsiți valorile lipsă cu COUNTIF și IF


Găsiți valorile lipsă cu MATCH, ISNA și IF

A descoperi dacă toate produsele din lista dumneavoastră există în lista furnizorilor dumneavoastră după cum se arată în captura de ecran de mai sus, puteți utiliza mai întâi funcția MATCH pentru a prelua poziția unui produs din lista dvs. (valoarea listei A) în lista furnizorilor (lista B). MATCH va returna eroarea #N/A atunci când un produs nu este găsit. Apoi puteți transmite rezultatul către ISNA pentru a converti erorile #N/A în adevăruri, ceea ce înseamnă că acele produse lipsesc. Funcția IF va returna apoi rezultatul așteptat.

Sintaxa generică

=IF(ISNA(MATCH("lookup_value",lookup_range,0)),"Missing","Found")

√ Notă: Puteți modifica „Lipsă”, „Găsit” la orice valoare după cum aveți nevoie.

  • valoare_cercare: Valoarea MATCH folosită pentru a-și prelua poziția dacă există în gama_cercetării sau #N/A eroare dacă nu. Aici se referă la produsele din lista dvs.
  • interval_consultare: Gama de celule de comparat cu lookup_value. Aici se referă la lista de produse a furnizorului.

A descoperi dacă toate produsele din lista dumneavoastră există în lista furnizorilor dumneavoastră, vă rugăm să copiați sau să introduceți formula de mai jos în celula H6 și apăsați Intrați pentru a obține rezultatul:

=DACA(ISNA(POTIT(30002,$ B $ 6: $ B $ 10,0)),"Lipsă","Găsit")

Sau utilizați o referință de celulă pentru a face formula dinamică:

=DACA(ISNA(POTIT(G6,$ B $ 6: $ B $ 10,0)),"Lipsă","Găsit")

√ Notă: semnele dolarului ($) de mai sus indică referințe absolute, ceea ce înseamnă gama_cercetării în formulă nu se va modifica atunci când mutați sau copiați formula în alte celule. Cu toate acestea, nu există semne de dolar adăugate la lookup_value deoarece vrei să fie dinamică. După ce ați introdus formula, trageți mânerul de umplere în jos pentru a aplica formula celulelor de mai jos.

găsiți valorile lipsă 2

Explicația formulei

Aici folosim formula de mai jos ca exemplu:

=IF(ISNA(MATCH(G8,$B$6:$B$10,0)),"Missing","Found")

  • MATCH(G8, $B$6:$B$10,0): Meciul_tip 0 obligă funcția MATCH să returneze o valoare numerică care indică poziția primei potriviri a lui 3004, valoarea din celula G8, din matrice $ B $ 6: $ B $ 10. Cu toate acestea, în acest caz, MATCH nu a putut găsi valoarea în matricea de căutare, așa că va returna #N / A eroare.
  • ISNA(MATCH(G8, $B$6:$B$10,0)) = ISNA(#N / A): ISNA lucrează pentru a afla dacă o valoare este eroare „#N/A” sau nu. Dacă da, funcția va returna TURE; Dacă valoarea este altceva decât eroarea „#N/A”, va returna FALSE. Deci, această formulă ISNA va reveni TURE.
  • DACĂ(ISNA(MATCH(G8, $B$6:$B$10,0)),"Lipsă","Găsit") = IF(TRUE,"Lipsă","Găsit"): Funcția IF va returna Missing dacă comparația făcută de ISNA și MATCH este TRUE, în caz contrar va returna Found. Deci, formula va reveni Dispărut.

Găsiți valorile lipsă cu VLOOKUP, ISNA și IF

Pentru a afla dacă toate produsele din lista dvs. există în lista furnizorului dvs., puteți înlocui funcția MATCH de mai sus cu VLOOKUP, deoarece funcționează la fel ca MATCH și va returna eroarea #N/A dacă valoarea nu există în altă listă, sau spunem că lipsește.

Sintaxa generică

=IF(ISNA(VLOOKUP("lookup_value",lookup_range,1,FALSE)),"Missing","Found")

√ Notă: Puteți modifica „Lipsă”, „Găsit” la orice valoare după cum aveți nevoie.

  • valoare_cercare: Valoarea VLOOKUP folosită pentru a-și prelua poziția dacă există în gama_cercetării sau #N/A eroare dacă nu. Aici se referă la produsele din lista dvs.
  • interval_consultare: Gama de celule de comparat cu lookup_value. Aici se referă la lista de produse a furnizorului.

Pentru a afla dacă toate produsele din lista dvs. există în lista furnizorilor dvs., vă rugăm să copiați sau să introduceți formula de mai jos în celula H6 și apăsați Intrați pentru a obține rezultatul:

=DACĂ(ISNA(CĂUTARE V(30002,$ B $ 6: $ B $ 10,1,FALSE)),"Lipsă","Găsit")

Sau utilizați o referință de celulă pentru a face formula dinamică:

=DACĂ(ISNA(CĂUTARE V(G6,$ B $ 6: $ B $ 10,1,FALSE)),"Lipsă","Găsit")

√ Notă: semnele dolarului ($) de mai sus indică referințe absolute, ceea ce înseamnă gama_cercetării în formulă nu se va modifica atunci când mutați sau copiați formula în alte celule. Cu toate acestea, nu există semne de dolar adăugate la lookup_value deoarece vrei să fie dinamică. După ce ați introdus formula, trageți mânerul de umplere în jos pentru a aplica formula celulelor de mai jos.

găsiți valorile lipsă 3

Explicația formulei

Aici folosim formula de mai jos ca exemplu:

=IF(ISNA(VLOOKUP(G8,$B$6:$B$10,1,FALSE)),"Missing","Found")

  • CĂUTARE V (G8, $ B $ 6: $ B $ 10,1, FALSE): Range_lookup FALS obligă funcția CĂUTARE V să caute și să returneze valoarea care se potrivește exact 3004, valoarea din celula G8. Dacă valoarea_căutare 3004 există în 1prima coloană a matricei $ B $ 6: $ B $ 10, VLOOKUP va returna acea valoare; În caz contrar, va returna valoarea de eroare #N/A. Aici, 3004 nu există în matrice, deci, rezultatul ar fi #N / A.
  • ISNA(CĂUTARE V (G8, $ B $ 6: $ B $ 10,1, FALSE)) = ISNA(#N / A): ISNA lucrează pentru a afla dacă o valoare este eroare „#N/A” sau nu. Dacă da, funcția va returna TURE; Dacă valoarea este altceva decât eroarea „#N/A”, va returna FALSE. Deci, această formulă ISNA va reveni TURE.
  • DACĂ(ISNA(CĂUTARE V (G8, $ B $ 6: $ B $ 10,1, FALSE)),"Lipsă","Găsit") = IF(TRUE,"Lipsă","Găsit"): Funcția IF va returna Missing dacă comparația făcută de ISNA și VLOOKUP este TRUE, în caz contrar va returna Found. Deci, formula va reveni Dispărut.

Găsiți valorile lipsă cu COUNTIF și IF

Pentru a afla dacă toate produsele din lista dvs. există în lista furnizorilor dvs., puteți utiliza o formulă mai simplă cu funcțiile COUNTIF și IF. Formula profită de faptul că Excel va evalua orice număr cu excepția zero (0) ca fiind ADEVĂRAT. Deci, dacă o valoare există într-o altă listă, funcția COUNTIF va returna numărul de apariții din acea listă, atunci IF va lua numărul ca TURE; Dacă valoarea nu există în listă, funcția COUNTIF va returna 0, iar IF o va lua ca FALS.

Sintaxa generică

=IF(COUNTIF("lookup_range",lookup_value),"Found","Missing")

√ Notă: Puteți modifica „Găsit”, „Lipsă” la orice valoare după cum aveți nevoie.

  • interval_consultare: Gama de celule de comparat cu lookup_value. Aici se referă la lista de produse a furnizorului.
  • valoare_cercare: Valoarea COUNTIF folosită pentru a returna numărul de apariții în gama_cercetării. Aici se referă la produsele din lista dvs.

Pentru a afla dacă toate produsele din lista dvs. există în lista furnizorilor dvs., vă rugăm să copiați sau să introduceți formula de mai jos în celula H6 și apăsați Intrați pentru a obține rezultatul:

=DAC(NUMĂRĂDAC($ B $ 6: $ B $ 10,30002),"Găsit","Lipsă")

Sau utilizați o referință de celulă pentru a face formula dinamică:

=DAC(NUMĂRĂDAC($ B $ 6: $ B $ 10,G6),"Găsit","Lipsă")

√ Notă: semnele dolarului ($) de mai sus indică referințe absolute, ceea ce înseamnă gama_cercetării în formulă nu se va modifica atunci când mutați sau copiați formula în alte celule. Cu toate acestea, nu există semne de dolar adăugate la lookup_value deoarece vrei să fie dinamică. După ce ați introdus formula, trageți mânerul de umplere în jos pentru a aplica formula celulelor de mai jos.

găsiți valorile lipsă 4

Explicația formulei

Aici folosim formula de mai jos ca exemplu:

=IF(COUNTIF($B$6:$B$10,G8),"Found","Missing")

  • COUNTIF($B$6:$B$10,G8): Funcția COUNTIF contează de câte ori face 3004, valoarea din celula G8, apar în matrice $ B $ 6: $ B $ 10. Aparent, 3004 nu există în matrice, deci, rezultatul ar fi 0.
  • DACĂ(COUNTIF($B$6:$B$10,G8),"Găsit","Lipsă") = IF(0,"Găsit","Lipsă"): Funcția IF va evalua 0 ca FALS. Deci, formula va reveni Dispărut, valoarea de returnat atunci când prima creștere este evaluată la FALSE.

Funcții conexe

Funcția Excel IF

Funcția IF este una dintre cele mai simple și utile funcții din registrul de lucru Excel. Realizează un test logic simplu care, în funcție de rezultatul comparației, și returnează o valoare dacă un rezultat este ADEVĂRAT sau o altă valoare dacă rezultatul este FALS.

Funcția Excel MATCH

Funcția Excel MATCH caută o anumită valoare într-un interval de celule și returnează poziția relativă a valorii.

Funcția Excel VLOOKUP

Funcția Excel VLOOKUP caută o valoare prin potrivirea pe prima coloană a unui tabel și returnează valoarea corespunzătoare dintr-o anumită coloană din același rând.

Funcția Excel COUNTIF

Funcția COUNTIF este o funcție statistică în Excel care este utilizată pentru a număra numărul de celule care îndeplinesc un criteriu. Acceptă operatorii logici (<>, =, > și <) și caracterele metalice (? și *) pentru potrivirea parțială.


Formule conexe

Căutați o valoare care conține text specific cu metacaractere

Pentru a găsi prima potrivire care conține un anumit șir de text într-un interval în Excel, puteți utiliza o formulă INDEX și MATCH cu caractere wildcard - asteriscul (*) și semnul de întrebare (?).

Potrivire parțială cu VLOOKUP

Există momente când aveți nevoie de Excel pentru a prelua date bazate pe informații parțiale. Pentru a rezolva problema, puteți utiliza o formulă CUTARE V, împreună cu caractere wildcard - asteriscul (*) și semnul de întrebare (?).

Potrivire aproximativă cu INDEX și MATCH

Există momente în care trebuie să găsim potriviri aproximative în Excel pentru a evalua performanța angajaților, nota scorurilor elevilor, calcularea taxelor poștale în funcție de greutate etc. În acest tutorial, vom vorbi despre modul de utilizare a funcțiilor INDEX și MATCH pentru a recupera rezultatele de care avem nevoie.

Căutați cea mai apropiată valoare de potrivire cu mai multe criterii

În unele cazuri, poate fi necesar să căutați valoarea de potrivire cea mai apropiată sau aproximativă pe baza mai multor criterii. Cu combinația funcțiilor INDEX, MATCH și IF, puteți realiza rapid în Excel.


Cele mai bune instrumente de productivitate Office

Kutools pentru Excel - Vă ajută să vă distingeți de mulțime

Doriți să vă finalizați munca zilnică rapid și perfect? Kutools pentru Excel aduce 300 de funcții avansate puternice (Combinați registre de lucru, sumă după culoare, divizați conținutul celulei, convertiți data și așa mai departe ...) și economisiți 80% timp pentru dvs.

  • Conceput pentru 1500 de scenarii de lucru, vă ajută să rezolvați 80% probleme Excel.
  • Reduceți mii de clicuri pe tastatură și mouse în fiecare zi, ușurați-vă ochii și mâinile obosite.
  • Deveniți expert Excel în 3 minute. Nu mai este nevoie să vă amintiți formule dureroase și coduri VBA.
  • Încercare gratuită nelimitată de 30 de zile. Garanție de restituire a banilor de 60 de zile. Upgrade gratuit și asistență timp de 2 ani.
Panglică Excel (cu Kutools pentru Excel instalat)

Fila Office - Activați lectura și editarea cu file în Microsoft Office (includeți Excel)

  • O secundă pentru a comuta între zeci de documente deschise!
  • Reduceți sute de clicuri de mouse pentru dvs. în fiecare zi, spuneți adio de la mâna mouse-ului.
  • Vă crește productivitatea cu 50% atunci când vizualizați și editați mai multe documente.
  • Aduce file eficiente în Office (include Excel), la fel ca Chrome, Firefox și noul Internet Explorer.
Captură de ecran a Excel (cu fila Office instalată)
Comentarii (0)
Încă nu există evaluări. Fii primul care evaluează!
Nu există comentarii postate aici încă
Lăsa comentarii
Postare ca invitat
×
Evaluează această postare:
0   Caractere
Locații sugerate