Cum se compară două liste în Excel

Excel este un program eficient de procesare a datelor. Și una dintre metodele de analiză a informațiilor este compararea a două liste. Dacă comparați corect două liste în Excel, organizarea acestui proces va fi foarte ușoară. Este suficient doar să urmărim câteva dintre punctele care vor fi discutate astăzi. Implementarea practică a acestei metode depinde în totalitate de nevoile persoanei sau organizației la un moment dat. Prin urmare, ar trebui luate în considerare mai multe cazuri posibile.

Compararea a două liste în Excel

Desigur, puteți compara manual două liste. Dar va dura mult timp. Excel are propriul set de instrumente inteligent care vă va permite să comparați datele nu numai rapid, ci și să obțineți informații care nu sunt atât de ușor de obținut cu ochii. Să presupunem că avem două coloane cu coordonatele A și B. Unele valori sunt repetate în ele.

Formularea problemei

Deci trebuie să comparăm aceste coloane. Procedura de comparare a două documente este următoarea:

1. Dacă celulele unice ale fiecăreia dintre aceste liste sunt aceleași, iar numărul total de celule unice este același, iar celulele sunt aceleași, atunci aceste liste pot fi considerate la fel. Ordinea în care sunt stivuite valorile din această listă nu contează atât de mult.
2. Putem vorbi despre coincidența parțială a listelor dacă valorile unice sunt aceleași, dar numărul de repetări este diferit. Prin urmare, astfel de liste pot conține un număr diferit de elemente.
3. Faptul că cele două liste nu se potrivesc este indicat de un set diferit de valori unice.

Toate aceste trei condiții simultan sunt condițiile problemei noastre.

Rezolvarea problemei

Să generăm două intervale dinamice pentru a facilita compararea listelor. Fiecare dintre ele va corespunde fiecăreia dintre liste.

Pentru a compara două liste, procedați în felul următor:

1. Într-o coloană separată, creăm o listă de valori unice care sunt specifice ambelor liste. Pentru aceasta folosim formula: ЕСЛИОШИБКА(ЕСЛИОШИБКА( ИНДЕКС(Список1;ПОИСКПОЗ(0;СЧЁТЕСЛИ($D$4:D4;Список1);0)); ИНДЕКС(Список2;ПОИСКПОЗ(0;СЧЁТЕСЛИ($D$4:D4;Список2);0))); «»). Formula în sine trebuie scrisă ca o formulă matrice.
2. Să stabilim de câte ori apare fiecare valoare unică în matricea de date. Iată formulele pentru a face acest lucru: =COUNTIF(Lista1,D5) și =COUNTI(Lista2,D5).
3. Dacă atât numărul de repetări, cât și numărul de valori unice sunt aceleași în toate listele care sunt incluse în aceste intervale, atunci funcția returnează valoarea 0. Aceasta indică faptul că potrivirea este XNUMX%. În acest caz, titlurile acestor liste vor căpăta un fundal verde.
4. Dacă tot conținutul unic este în ambele liste, atunci returnat prin formule =СЧЁТЕСЛИМН($D$5:$D$34;»*?»;E5:E34;0) и =СЧЁТЕСЛИМН($D$5:$D$34;»*?»;F5:F34;0) valoarea va fi zero. Dacă E1 nu conține zero, dar o astfel de valoare este conținută în celulele E2 și F2, atunci în acest caz intervalele vor fi recunoscute ca potrivire, dar numai parțial. În acest caz, titlurile listelor corespunzătoare vor deveni portocalii.
5. Și dacă una dintre formulele descrise mai sus returnează o valoare diferită de zero, listele vor fi complet nepotrivite.

Acesta este răspunsul la întrebarea cum să analizăm coloanele pentru potriviri folosind formule. După cum puteți vedea, cu ajutorul funcțiilor, puteți implementa aproape orice sarcină care, la prima vedere, nu are legătură cu matematica.

Exemplu de testare

În versiunea noastră a tabelului, există trei tipuri de liste de fiecare tip descris mai sus. Are potrivire parțială și completă, precum și nepotrivire.

Pentru a compara datele, folosim intervalul A5:B19, în care inserăm alternativ aceste perechi de liste. Despre care va fi rezultatul comparației, vom înțelege prin culoarea listelor originale. Dacă sunt complet diferite, atunci va fi un fundal roșu. Dacă o parte din date este aceeași, atunci galben. În cazul identității complete, rubricile corespunzătoare vor fi verzi. Cum să faci o culoare în funcție de rezultatul? Acest lucru necesită formatare condiționată.

Găsirea diferențelor în două liste în două moduri

Să mai descriem două metode de găsire a diferențelor, în funcție de dacă listele sunt sincrone sau nu.

Opțiunea 1. Liste sincrone

Aceasta este o opțiune ușoară. Să presupunem că avem astfel de liste.

Pentru a determina de câte ori nu au convergit valorile, puteți folosi formula: =SUMPRODUS(—(A2:A20<>B2:B20)). Dacă am obținut 0 ca rezultat, aceasta înseamnă că cele două liste sunt aceleași.

Opțiunea 2: Liste amestecate

Dacă listele nu sunt identice în ordinea obiectelor pe care le conțin, trebuie să aplicați o caracteristică precum formatarea condiționată și să colorați valorile duplicate. Sau folosiți funcția COUNTIF, folosind care determinăm de câte ori apare un element dintr-o listă în a doua.

Cum se compară 2 coloane rând cu rând

Când comparăm două coloane, de multe ori trebuie să comparăm informațiile care se află în rânduri diferite. Pentru a face acest lucru, operatorul ne va ajuta DACĂ. Să aruncăm o privire la modul în care funcționează în practică. Pentru a face acest lucru, prezentăm mai multe situații ilustrative.

Exemplu. Cum să compari 2 coloane pentru potriviri și diferențe într-un singur rând

Pentru a analiza dacă valorile care sunt pe același rând, dar coloane diferite sunt aceleași, scriem funcția IF. Formula este inserată în fiecare rând plasat în coloana auxiliară unde vor fi afișate rezultatele prelucrării datelor. Dar nu este deloc necesar să-l prescrieți în fiecare rând, doar copiați-l în celulele rămase din această coloană sau utilizați marcatorul de completare automată.

Ar trebui să scriem o astfel de formulă pentru a înțelege dacă valorile din ambele coloane sunt aceleași sau nu: =IF(A2=B2, „Potrivire”, „”). Logica acestei funcții este foarte simplă: compară valorile din celulele A2 și B2, iar dacă sunt aceleași, afișează valoarea „Coincide”. Dacă datele sunt diferite, nu returnează nicio valoare. De asemenea, puteți verifica celulele pentru a vedea dacă există o potrivire între ele. În acest caz, formula utilizată este: =IF(A2<>B2, „Nu se potrivește”, „”). Principiul este același, mai întâi se efectuează verificarea. Dacă se dovedește că celulele îndeplinesc criteriul, atunci este afișată valoarea „Nu se potrivește”.

De asemenea, este posibil să utilizați următoarea formulă în câmpul de formulă pentru a afișa atât „Potriviți” dacă valorile sunt aceleași, cât și „Nu se potrivesc” dacă sunt diferite: =IF(A2=B2; „Potriviți”, „Nu se potrivesc”). De asemenea, puteți utiliza operatorul de inegalitate în locul operatorului de egalitate. Doar ordinea valorilor care vor fi afișate în acest caz va fi ușor diferită: =IF(A2<>B2, „Nu se potrivește”, „Coincide”). După utilizarea primei versiuni a formulei, rezultatul va fi următorul.

Această variație a formulei nu face distincție între majuscule și minuscule. Prin urmare, dacă valorile dintr-o coloană diferă de altele doar prin faptul că sunt scrise cu majuscule, atunci programul nu va observa această diferență. Pentru a face comparația sensibilă la majuscule și minuscule, trebuie să utilizați funcția din criterii CORECT. Restul argumentelor sunt lăsate neschimbate: =IF(EXACT(A2,B2), „Potrivire”, „Unic”).

Cum să comparați mai multe coloane pentru potriviri într-un singur rând

Este posibil să se analizeze valorile din liste în funcție de un întreg set de criterii:

1. Găsiți acele rânduri care au aceleași valori peste tot.
2. Găsiți acele rânduri în care există potriviri în doar două liste.

Să ne uităm la câteva exemple despre cum să procedăm în fiecare dintre aceste cazuri.

Exemplu. Cum să găsiți potriviri pe un rând în mai multe coloane ale unui tabel

Să presupunem că avem o serie de coloane care conțin informațiile de care avem nevoie. Ne confruntăm cu sarcina de a determina acele rânduri în care valorile sunt aceleași. Pentru a face acest lucru, trebuie să utilizați următoarea formulă: =DACĂ(ȘI(A2=B2;A2=C2), „potrivire”, „ „).

Dacă există prea multe coloane în tabel, atunci trebuie doar să-l utilizați împreună cu funcția IF operator COUNTIF: =IF(COUNTIF($A2:$C2,$A2)=3;”potrivire”;” “). Numărul utilizat în această formulă indică numărul de coloane de verificat. Dacă diferă, atunci trebuie să scrieți atât cât este adevărat pentru situația dvs.

Exemplu. Cum să găsiți potriviri pe un rând în oricare 2 coloane ale unui tabel

Să presupunem că trebuie să verificăm dacă valorile dintr-un rând se potrivesc în două coloane cu cele din tabel. Pentru a face acest lucru, trebuie să utilizați funcția ca o condiție OR, unde se scrie alternativ egalitatea fiecăreia dintre coloane cu cealaltă. Iată un exemplu.

Folosim aceasta formula: =ЕСЛИ(ИЛИ(A2=B2;B2=C2;A2=C2);”Совпадают”;” “). Poate exista o situație în care există o mulțime de coloane în tabel. În acest caz, formula va fi uriașă și poate dura mult timp pentru a selecta toate combinațiile necesare. Pentru a rezolva această problemă, trebuie să utilizați funcția COUNTIF: =DACA(NUMĂRĂDAC(B2:D2,A2)+NUMĂRĂDAC(C2:D2,B2)+(C2=D2)=0; „Șir unic”; „Șir nu unic”)

Vedem că în total avem două funcții COUNTIF. Cu primul, determinăm alternativ câte coloane au o asemănare cu A2, iar cu a doua, verificăm numărul de asemănări cu valoarea lui B2. Dacă, ca rezultat al calculului prin această formulă, obținem o valoare zero, aceasta indică faptul că toate rândurile din această coloană sunt unice, dacă sunt mai multe, există asemănări. Prin urmare, dacă în urma calculului prin două formule și adăugării rezultatelor finale obținem o valoare zero, atunci se returnează valoarea text „Șir unic”, dacă acest număr este mai mare, se scrie că acest șir nu este unic.

Cum să compari 2 coloane în Excel pentru potriviri

Acum să luăm un exemplu. Să presupunem că avem un tabel cu două coloane. Trebuie să verificați dacă se potrivesc. Pentru a face acest lucru, trebuie să aplicați formula, unde va fi folosită și funcția IF, și operatorul COUNTIF: =IF(COUNTIF($B:$B,$A5)=0, „Fără potriviri în coloana B”, „Există potriviri în coloana B”)

Nu este necesară nicio acțiune suplimentară. După calcularea rezultatului prin această formulă, obținem dacă valoarea celui de-al treilea argument al funcției IF chibrituri. Dacă nu există, atunci conținutul celui de-al doilea argument.

Cum să compari 2 coloane în Excel pentru potriviri și să evidențiezi cu culoare

Pentru a facilita identificarea vizuală a coloanelor care se potrivesc, le puteți evidenția cu o culoare. Pentru a face acest lucru, trebuie să utilizați funcția „Formatare condiționată”. Să vedem în practică.

Găsirea și evidențierea potrivirilor după culoare în mai multe coloane

Pentru a determina potrivirile și a le evidenția, trebuie mai întâi să selectați intervalul de date în care va fi efectuată verificarea, apoi să deschideți elementul „Formatizare condiționată” din fila „Acasă”. Acolo, selectați „Valori duplicate” ca regulă de selecție a celulelor.

După aceea, va apărea o nouă casetă de dialog, în care în lista pop-up din stânga găsim opțiunea „Repetare”, iar în lista din dreapta selectăm culoarea care va fi folosită pentru selecție. După ce facem clic pe butonul „OK”, va fi selectat fundalul tuturor celulelor cu asemănări. Apoi comparați doar coloanele după ochi.

Găsirea și evidențierea liniilor care se potrivesc

Tehnica de verificare a potrivirii șirurilor este ușor diferită. Mai întâi, trebuie să creăm o coloană suplimentară și acolo vom folosi valorile combinate folosind operatorul &. Pentru a face acest lucru, trebuie să scrieți o formulă de forma: =A2&B2&C2&D2.

Selectăm coloana care a fost creată și conține valorile combinate. În continuare, efectuăm aceeași secvență de acțiuni descrisă mai sus pentru coloane. Liniile duplicate vor fi evidențiate în culoarea pe care o specificați.

Vedem că nu este nimic dificil în căutarea repetărilor. Excel conține toate instrumentele necesare pentru aceasta. Este important să exersați înainte de a pune toate aceste cunoștințe în practică.