comparați listele cu VLOOKUP
o cerință comună în Excel este necesitatea de a compara două liste. În acest tutorial, vom compara listele cu VLOOKUP.
poate fi necesar să comparați două liste pentru a evidenția înregistrările lipsă, pentru a evidenția înregistrările care se potrivesc sau pentru a returna o valoare.
funcția VLOOKUP poate fi utilizată pentru a compara listele în Excel căutând o valoare dintr-o listă în alta. Puteți lua apoi măsurile necesare.
acest tutorial arată cum să comparați listele cu VLOOKUP. Funcția VLOOKUP poate fi utilizată cu alte formule sau caracteristici Excel, pentru a obține rezultate diferite implicate în compararea a două liste.
combinați cu funcția IF pentru a returna o valoare
să presupunem că avem două liste de clienți precum imaginea de mai jos. Ambele liste sunt identice, cu excepția faptului că lista 1 are mai puțini clienți decât lista 2.
vrem să comparăm cele două liste și să identificăm diferențele. Câmpul ID va fi folosit pentru a verifica ambele liste, deoarece este un câmp unic.
în acest prim exemplu, vom căuta clienții din lista 1 din lista 2 și vom returna textul „nu a fost găsit” dacă lipsesc. Dacă se găsesc în lista 2, vom păstra celula necompletată.
formula de mai jos utilizează funcția VLOOKUP împreună cu funcțiile IF și ISNA pentru a atinge acest obiectiv.
să aruncăm o privire la rolul pe care fiecare funcție îl joacă în această formulă.
VLOOKUP: Funcția VLOOKUP a fost utilizată pentru a căuta clientul în a doua listă și pentru a returna ID-ul clientului (specificat ca 1 în formulă).
dacă funcția VLOOKUP nu poate găsi clientul, returnează mesajul de eroare #N/A. Aceasta este ceea ce ne interesează, deoarece înseamnă că clientul este pe lista 1, dar nu pe lista 2.
ISNA: funcția ISNA este utilizată pentru a detecta mesajul #N/A adus înapoi de VLOOKUP și a-l traduce în adevărat. Dacă un ID este returnat returnează False.
dacă: Funcția IF va afișa textul” not found ” dacă ISNA returnează True și va păstra celula necompletată dacă ISNA returnează False. Această funcție este utilizată pentru a lua orice acțiune doriți pe baza constatărilor din VLOOKUP.
comparați listele cu VLOOKUP pentru a evidenția înregistrările lipsă
această formulă poate fi utilizată și cu formatare condiționată pentru a evidenția diferențele dintre cele două liste, în loc să returnați o valoare.
acest exemplu arată cum să modificați culoarea celulei întregului rând al tabelului atunci când un client lipsește din a doua listă. Formatarea întregului rând, mai degrabă decât o singură celulă, va face mai clară identificarea înregistrărilor lipsă, în special într-un tabel cu multe coloane.
- Selectați toate celulele tabelului, cu excepția rândului antet.
- Faceți clic pe Formatare condiționată în fila Pornire a Panglicii și selectați regulă nouă.
- selectați Utilizați o formulă pentru a determina ce celule să formatați din jumătatea superioară a ferestrei. În jumătatea de jos introduceți formula de mai jos în caseta furnizată.
- Faceți clic pe butonul Format și alegeți formatarea pe care doriți să o aplicați rândului înregistrărilor lipsă
comparați două liste și evidențiați înregistrările care se potrivesc
pentru a compara cele două liste și a evidenția înregistrările care se potrivesc, funcția NOT va trebui adăugată la formula anterioară.
=NOT(ISNA(VLOOKUP($A2,'Customers 2'!$A:$F,1,FALSE)))
funcția NOT este utilizată pentru a inversa rezultatul VLOOKUP. Deci, în loc să evidențieze înregistrările lipsă, evidențiază meciurile.