Bună Paula, poți compara liste în Excel?
sau
Care este cea mai bună modalitate de a compara două seturi de date în Excel?
De foarte multe ori există o cerință în Excel de a compara două liste, sau două seturi de date pentru a găsi elemente lipsă sau de potrivire. Fiind vorba de Excel, există întotdeauna mai multe moduri de a face lucrurile, inclusiv de a compara date. De la formule și formatare condiționată la Power Query. În acest articol vom analiza mai multe modalități de a compara două liste în Excel și vom analiza, de asemenea, compararea unor rânduri întregi dintr-un set de date.
Poate că modalitatea dvs. preferată nu este inclusă mai jos. Dacă nu, de ce să nu lăsați un comentariu mai jos și să ne împărtășiți cum vă place să comparați două liste sau seturi de date în Excel. Aștept cu nerăbdare să vă citesc comentariile.
Dorim să comparăm Lista 1 cu Lista 2.
Contenit
Formatul condiționat rapid pentru a compara două coloane de date
Cercetarea formatării condiționate
Compararea datelor în Excel cu ajutorul funcției MATCH
Comparați 2 liste în Excel 365 cu MATCH sau XMATCH ca o Funcția Dynamic Array
MATCH și array-uri dinamice pentru a compara 2 liste
XMATCH Excel 365 pentru a compara două liste
Tabele – Compararea listelor în Excel în cazul în care dimensiunile intervalelor s-ar putea schimba
Subliniați diferențele din liste utilizând Formatarea condițională personalizată
Copierea formula în Custom Conditional Formatting
Alte formule utilizate pentru a compara două liste în Excel
VLOOKUP pentru a compara două liste în Excel
XLOOKUP pentru a compara două liste în Excel
COUNTIF pentru a compara două liste în Excel
Cum se compară 2 seturi de date în Excel
.
Compararea listelor sau a seturilor de date utilizând Power Query
Formatare condiționată rapidă pentru a compara două coloane de date
Formatul condiționat vă va permite să evidențiați o celulă sau un interval pe baza unor criterii predefinite. Cel mai rapid și mai simplu mod de a compara vizual rapid aceste două coloane este de a utiliza regula predefinită de evidențiere a valorilor duplicate.
Începeți prin a selecta cele două coloane de date.
Din fila Home, selectați lista derulantă Conditional Formatting (Formatare condiționată). Apoi selectați Highlight Cells Rules (Reguli de evidențiere a celulelor). Apoi selectați Duplicate values.
Se va deschide o casetă de setări Duplicate Values în care puteți defini formatarea și puteți selecta între Duplicate sau Unique values.
Prin selectarea Duplicate, toate intrările care se repetă vor fi setate la formatarea selectată. Acum puteți vedea rapid elementele din Lista 1 care se regăsesc în Lista 2, deoarece acestea sunt elementele formatate. De asemenea, puteți vedea rapid elementele din lista 2 care nu se află în lista 1, deoarece acestea nu au formatarea aplicată.
Cu toate acestea, puteți formata și elementele Unice. Acest lucru se poate realiza prin selectarea Unique din caseta de configurare Duplicate Values.
În acest caz, am aplicat două formate condiționale diferite. Cel roșu indicând dublurile și cel verde indicând elementele unice.
Rețineți, nu am luat doar celulele cu date, ci am luat toate coloanele de la A la C. Coloana B nu are date, deci nu poate afecta rezultatele. Cu toate acestea, aceste celule conțin formatarea condiționată aplicată, așa că ar fi o practică mai bună să selectați doar celulele de care aveți nevoie.
Ștergerea formatării condiționate
Pentru a șterge toată formatarea condiționată, selectați mai întâi celula, sau intervalul. Apoi selectați picătura de formatare condiționată de pe panglica Home. Apoi selectați Clear Rules (Ștergere reguli). În cele din urmă selectați Clear Rules from Selected Cells (Ștergeți regulile din celulele selectate).
Dacă aveți mai multe formatare condiționate aplicate simultan și doriți să eliminați doar una dintre acestea, selectați Manage Rules (Gestionați regulile) din meniul derulant de formatare condiționată. Selectați regula pe care doriți să o ștergeți și apoi selectați Delete Rule.
Prin apăsarea OK, regula va fi eliminată din Rules Manager și celulele nu vor mai conține formatarea.
Atunci acesta este cel mai simplu mod în care puteți compara două liste în Excel. Este rapid, simplu și eficient. De asemenea, puteți aplica formatarea condiționată bazată pe formule, pe care o vom analiza mai târziu în acest articol.
Potriviți datele în Excel folosind funcția MATCH
Există multe formule de căutare pe care le puteți folosi pentru a compara două intervale sau liste în Excel. Prima pe care o vom analiza este funcția MATCH.
Funcția MATCH returnează poziția relativă într-o listă. Un număr bazat pe poziția sa, dacă este găsit, în matricea de căutare.
Sintaxa pentru MATCH este
=MATCH (lookup value, Lookup array, Match type)
Unde valoarea de căutare este valoarea pentru care doriți să găsiți o potrivire. Lookup array este lista în care căutați o potrivire. Iar tipul de potrivire vă permite să selectați între o potrivire exactă sau aproximativă.
Vrem să scriem o formulă de potrivire pentru a vedea dacă elementele din Lista 2 se află în Lista 1.
În celula E3 putem introduce formula
=MATCH(C2, $A$2:$A$21,0)
Completând această formulă, pentru valorile la care Excel găsește o potrivire, va fi returnată poziția acelei potriviri. În cazul în care nu există nicio potrivire, valoarea returnată va fi un #N/A.
De foarte multe ori, poziția relativă sau #N/A nu are nicio valoare pentru noi și trebuie să convertim aceste valori în adevărat sau fals. Pentru a face acest lucru, putem extinde cu ușurință formula noastră Match folosind o funcție logică. Deoarece Match returnează un număr, putem folosi funcția ISNUMBER
=ISNUMBER(MATCH(C2, $A$2:$A$21,0))
Comparați 2 liste în Excel 365 cu MATCH sau XMATCH ca funcție Dynamic Array
Dacă folosiți Excel 365, aveți alternative suplimentare atunci când folosiți MATCH pentru a compara liste sau date. Având în vedere că Excel 365 gândește în array-uri, acum putem trece un array ca valoare de căutare a funcției MATCH și rezultatele noastre se vor vărsa pentru noi. Acest lucru elimină necesitatea de a copia formula în jos și cu o singură formulă, foaia dvs. de calcul va fi mai puțin predispusă la erori și mai compactă.
MATCH și array-uri dinamice pentru a compara 2 liste
Dacă nu sunteți încă familiarizați cu array-urile dinamice, vă sugerez să citiți acest articol: Excel Dynamic Arrays – Un nou mod de a vă modela foile de calcul Excel: pentru a înțelege mai bine modul în care acestea funcționează și intervalele de răsfrângere.
Singura modificare a formulei de potrivire este că, în loc să selectăm celula C2 ca valoare de căutare, vom selecta intervalul C2:12
=ISNUMBER(MATCH (C2:C12,$A$2:$A$21,0))
După cum puteți vedea, o singură formulă răstoarnă rezultatele în coloana E.
XMATCH Excel 365 pentru a compara două liste
Excel 365 introduce, de asemenea, noua funcție XMATCH. La fel ca și funcția MATCH, XMATCH returnează o poziție relativă într-o listă. Acum sunteți familiarizat cu XLOOKUP, care înlocuiește vechea funcție VLOOKUP, știți că XLOOKUP vine cu o putere suplimentară. Aceasta vine sub forma unor noi condiții în sintaxa formulei, cum ar fi modul de căutare și tipurile de potrivire. Ei bine, XMATCH are, de asemenea, această putere suplimentară față de predecesorul său MATCH.
Sintaxa pentru XMATCH este
XMATCH (Lookup Value, Lookup Array, ,)
Unde
Lookup Value este valoarea a cărei poziție relativă doriți să o găsiți
Lookup Array este rândul sau coloana care conține Lookup Value
Modul de potrivire este opțional. Spre deosebire de vechea funcție MATCH, valoarea implicită este o potrivire exactă. De asemenea, puteți selecta între
- Exact match or next smallest
- Exact match or next largest
- Wildcard match
Search mode is also optional. Opțiunea implicită (și singura opțiune din vechea funcție MATCH) este de a căuta de sus în jos. De asemenea, puteți selecta căutări de la ultimul la primul și căutări binare. În cazul în care lucrați cu căutări binare. Opțiunea de potrivire cu wildcard nu funcționează.
Cu XMATCH putem folosi fie array-uri dinamice, fie referințe de celule pentru a crea formula, la fel cum am analizat cu MATCH. Pentru acest exemplu, vom folosi Dynamic Arrays. Formula este foarte asemănătoare cu cea pe care am folosit-o cu MATCH; cu excepția faptului că nu trebuie să selectăm 0 pentru o potrivire exactă, deoarece în XMATCH aceasta este setarea implicită. Haideți să amestecăm puțin lucrurile, de data aceasta o să ne uităm la găsirea elementelor din lista 2 și nu din lista unu.
În acest caz putem folosi formula
=NOT(ISNUMBER(XMATCH(C2:C12,A2:A21)))
Unde not va transforma true în false și false în trues.
Tabele – Compararea listelor în Excel în cazul în care dimensiunile intervalelor se pot schimba
În fiecare dintre formulele pe care le-am analizat până acum, am selectat un interval de celule în funcțiile noastre Match care nu este dinamic. Aceasta înseamnă că, dacă adăugăm date noi într-una din liste, avem un pas manual pentru a actualiza formula noastră pentru a include noile date.
Pentru a converti listele în tabele, selectați una dintre liste și apăsați CTRL. Aceasta este prescurtarea de la tastatură pentru a converti într-un tabel. Dacă ați selectat antetul din intervalul de celule, asigurați-vă că ați bifat căsuța pentru a confirma că tabelul dvs. are anteturi.
Tabelele, prin natura lor, folosesc o denumire structurată. Prin urmare, atunci când scrieți o formulă și selectați o coloană dintr-un tabel, aceasta nu va afișa referințele celulelor, ci numele coloanei.
Cu privire la formula noastră anterioară care utilizează XMATCH pentru a găsi elementele din lista 2 care nu se află în lista 1, putem rescrie această funcție acum utilizând referințele tabelului nostru
=NOT (ISNUMBER(XMATCH(list2,list1))))
Acum, deoarece am folosit tabele, dacă adăugăm un nou rând la oricare dintre tabele, intervalul nostru de vărsare va crește, de asemenea, pentru a include noile date.
Evidențiați diferențele din liste folosind formatarea condiționată personalizată
Mai devreme în acest articol am analizat o modalitate foarte rapidă de a compara aceste două liste folosind o regulă predefinită pentru duplicate. Putem însă să folosim și Formatarea condiționată personalizată (Custom Conditional Formatting). Dacă nu sunteți familiarizați cu Formatul condiționat personalizat, vă sugerez să consultați acest articol: Trucuri de formatare condiționată dinamică în Excel:
Trebuie să analizăm aici două abordări diferite pentru a evidenția diferențele. Atunci când nu folosim tabele și am creat o formulă adevărat/false pentru a identifica diferențele, putem lua o copie a formulei și o putem adăuga la Formatarea personalizată. Cu toate acestea, cu ajutorul tabelelor trebuie să forțăm utilizarea referințelor de celulă.
Copiați formula în Formatarea condiționată personalizată
Începeți prin a lua o copie a formulei. Deoarece am testat formula în foaia de calcul, putem vedea că aceasta funcționează înainte de a o utiliza în formatarea condiționată. Aceasta este cea mai bună practică, deoarece foarte des, cu referințele relative și absolute ale celulelor, poate fi dificil să obținem formula corectă.
Selectați celulele cărora doriți să le aplicați formatarea personalizată. Apoi, de pe panglica Home, selectați meniul derulant Formatare condiționată și selectați New Rule
Se va deschide caseta de setare New Formatting Rule și selectați Use Formula pentru a determina ce celule să formatați. Apoi inserați formula și setați tipul de formatare
Aceasta va avea ca rezultat faptul că toate celulele care se află în ambele liste vor fi formatate în formatul ales de dumneavoastră.
Rețineți că am selectat un interval de celule pentru a aplica această formatare condiționată și că nu este dinamică. Dacă am folosi tabele, aceasta s-ar actualiza fără a fi nevoie să modificăm nimic!
Alte formule folosite pentru a compara două liste în Excel
Există multe formule pe care le puteți folosi pentru a compara două liste în Excel. Ne-am uitat deja la MATCH și XMATCH, dar acum ne vom uita la alte câteva. Oricare dintre funcțiile de căutare va funcționa cu adevărat împreună cu altele!
VLOOKUP pentru a compara două liste în Excel
Dacă nu sunteți familiarizați cu VLOOKUP, puteți citi despre el aici. Pe scurt, VLOOKUP va returna o valoare corespunzătoare dintr-o celulă, dacă nu există o valoare corespunzătoare va fi returnată o eroare #N/A. În exemplul nostru lucrăm cu text. Așadar, putem efectua un VLOOKUP și să testăm dacă returnează text. Dacă am folosi numere, am putea înlocui ISTEXT cu ISNUMBER.
Am putea folosi funcția =ISTEXT(VLOOKUP(C2, $A$2:$A$21,1,FALSE))
Sau dacă am folosi array-uri dinamice în Excel 365, am putea folosi funcția
=ISTEXT(VLOOKUP (C2:C12,$A$2:$A$21,1,FALSE))
XLOOKUP pentru a compara două liste în Excel
XLOOKUP a fost introdus în Excel 365 și puteți afla mai multe despre el aici. Foarte asemănător cu VLOOKUP, XLOOKUP va returna o valoare corespunzătoare dintr-o celulă și puteți defini un rezultat în cazul în care valoarea nu este găsită. Folosind array-uri dinamice, funcția ar fi
=ISTEXT(XLOOKUP (C2:C12,A2:A21,A2:A21))
COUNTIF pentru a compara două liste în Excel
Funcția COUNTIF va număra de câte ori o valoare, sau un text este conținut într-un interval. Dacă valoarea nu este găsită, se returnează 0. Putem combina această funcție cu o instrucțiune IF pentru a returna valorile noastre adevărate și false.
=IF(COUNTIF (A2:A21,C2:C12)<>0, „True”, „False”)
Cum să comparăm 2 seturi de date în Excel
Compararea a două liste este destul de ușoară și am analizat acum mai multe moduri de a face acest lucru. Dar compararea a două seturi de date poate fi un pic mai dificilă.
Să ne uităm la un exemplu. Avem două tabele de date, fiecare conținând aceleași anteturi de coloană. Dacă ne uităm la imagine, putem vedea că potrivirea acestor două tabele ar necesita examinarea mai multor coloane.
Când trebuie să examinați mai mult de o coloană, soluția ar fi crearea unei coloane compozite care să combine datele într-o singură coloană. Acest lucru va crea o coloană unică pentru fiecare rând pe care o putem folosi apoi ca și coloană de corespondență
În acest exemplu am putea combina Name și DoB pentru a da fiecărui tabel un identificator unic
Există mai multe moduri de a uni conținutul unei celule, în acest caz vom face o simplă concatenare. Deoarece folosim tabele, formula va încălța formatul de denumire a tabelelor.
= &”-„&
Repetă pașii pe al doilea tabel.
Acum putem folosi oricare dintre exemplele de mai sus pentru a potrivi aceste două noi coloane de date. Acolo unde se potrivesc, știm că se potrivesc cu întregul rând.
Compararea listelor sau a seturilor de date folosind Power Query
De asemenea, puteți compara liste și seturi de date folosind Excels Power Query. Prin conectarea la tabele și apoi îmbinarea tabelelor, folosind diferite tipuri de îmbinare, putem compara ambele liste.
În acest videoclip veți învăța cum să comparați sau să reconciliați două seturi de date diferite folosind Excels Power Query
Există un set de date care să însoțească acest videoclip și o practică pe care o puteți lua din acest articol.
.