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ă 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.
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.
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.
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 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 caută o anumită valoare într-un interval de celule și returnează poziția relativă a valorii.
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 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 (?).
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
Kutools pentru Excel se mândrește cu peste 300 de caracteristici, Asigurați-vă că ceea ce aveți nevoie este la doar un clic distanță...
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 la Office (inclusiv Excel), la fel ca Chrome, Edge și Firefox.
