Compararea tabelelor de valori 1c 8. Cum se compară două coloane în Excel pentru potriviri

Citirea acestui articol vă va dura aproximativ 10 minute. În următoarele 5 minute puteți compara cu ușurință două coloane în Excel și puteți afla dacă există duplicate în ele, să le ștergeți sau să le evidențiați cu culoare. Deci, a venit timpul!

Excel este o aplicație foarte puternică și foarte cool pentru crearea și procesarea unor cantități mari de date. Dacă aveți mai multe registre de lucru cu date (sau doar un tabel imens), atunci probabil că veți dori să comparați 2 coloane, să găsiți valori duplicate și apoi să faceți ceva cu ele, cum ar fi ștergerea, evidențierea sau ștergerea conținutului . Coloanele pot fi în același tabel, adiacente sau neadiacente, situate pe 2 foi diferite sau chiar în registre de lucru diferite.

Imaginați-vă că avem 2 coloane de nume de oameni - 5 nume pe coloană Ași 3 nume într-o coloană B. Trebuie să comparați numele din aceste două coloane și să găsiți orice duplicat. După cum înțelegeți, acestea sunt date fictive luate doar în scopuri ilustrative. În tabelele reale avem de-a face cu mii, sau chiar zeci de mii de înregistrări.

Opțiunea A: ambele coloane sunt pe aceeași foaie. De exemplu, coloana A si coloana B.

Opțiunea B: Coloanele sunt amplasate pe foi diferite. De exemplu, coloana A pe o foaie Foaia 2 si coloana A pe o foaie Foaia 3.

Excel 2013, 2010 și 2007 au un instrument încorporat Eliminați duplicat(Eliminați duplicatele) dar este neputincios în această situație deoarece nu poate compara datele din 2 coloane. În plus, poate elimina doar duplicatele. Nu există alte opțiuni, cum ar fi evidențierea sau schimbarea culorilor. Și punct!

Comparați 2 coloane în Excel și găsiți intrări duplicate folosind formule

Opțiunea A: ambele coloane sunt pe aceeași foaie

Cheie:În tabelele mari, copierea unei formule va fi mai rapidă dacă utilizați comenzi rapide de la tastatură. Selectați o celulă C1și apăsați Ctrl+C(pentru a copia formula în clipboard), apoi faceți clic Ctrl+Shift+End(pentru a selecta toate celulele care nu sunt goale din coloana C) și în cele din urmă faceți clic Ctrl+V(pentru a lipi formula în toate celulele selectate).

Opțiunea B: două coloane sunt pe foi diferite (în cărți diferite)

Procesarea a găsit duplicate

Grozav, am găsit intrări în prima coloană care sunt prezente și în a doua coloană. Acum trebuie să facem ceva cu ei. Parcurgerea manuală a tuturor intrărilor duplicate dintr-un tabel este destul de ineficientă și necesită prea mult timp. Există modalități mai bune.

Afișați numai rândurile duplicat în coloana A

Dacă coloanele dvs. nu au titluri, atunci trebuie să le adăugați. Pentru a face acest lucru, plasați cursorul pe numărul care indică prima linie și se va transforma într-o săgeată neagră, așa cum se arată în figura de mai jos:

Faceți clic dreapta și selectați din meniul contextual Introduce(Introduce):

Dați nume coloanelor, de exemplu, „ Nume" Și " Duplicat?” Apoi deschide fila Date(Date) și apăsați Filtru(Filtru):

După aceea, faceți clic pe săgeata gri mică de lângă „ Duplicat?„ pentru a extinde meniul de filtrare; debifați toate elementele din această listă, cu excepția Duplicat, și apăsați Bine.

Asta e, acum vezi doar acele elemente de coloană A, care sunt duplicate în coloană ÎN. Există doar două astfel de celule în tabelul nostru de antrenament, dar, după cum înțelegeți, în practică vor fi mult mai multe.

Pentru a afișa din nou toate rândurile unei coloane A, faceți clic pe simbolul filtrului din coloană ÎN, care acum arată ca o pâlnie cu o săgeată mică și selectați Selectează tot(Selectează tot). Sau puteți face același lucru prin Panglică făcând clic Date(Date) > Selectați și filtrați(Sortare și filtrare) > clar(Clar) așa cum se arată în captura de ecran de mai jos:

Schimbați culoarea sau evidențiați duplicatele găsite

Dacă semnele „ Duplicat” nu este suficient pentru scopurile dvs. și doriți să marcați celulele duplicate cu o culoare diferită a fontului, culoarea de umplere sau într-un alt mod...

În acest caz, filtrați duplicatele așa cum se arată mai sus, selectați toate celulele filtrate și faceți clic Ctrl+1 pentru a deschide caseta de dialog Celule de format(Format celulă). De exemplu, să schimbăm culoarea de umplere a celulelor din rândurile cu duplicate în galben strălucitor. Desigur, puteți schimba culoarea de umplere folosind instrumentul Completati fila (Culoare de umplere). Acasă(Acasă), dar avantajul casetei de dialog Celule de format(Format Cells) este că puteți configura toate opțiunile de formatare simultan.

Acum cu siguranță nu vei pierde nicio celulă cu duplicate:

Eliminarea valorilor duplicate din prima coloană

Filtrați tabelul pentru a afișa numai celulele cu valori duplicate și selectați acele celule.

Dacă cele 2 coloane pe care le comparați sunt pe foi diferite, adică în diferite tabele, faceți clic dreapta pe intervalul selectat și selectați din meniul contextual Ștergeți rândul(Ștergeți linia):

Clic Bine când Excel vă cere să confirmați că doriți cu adevărat să ștergeți întregul rând al foii de lucru și apoi ștergeți filtrul. După cum puteți vedea, rămân doar rândurile cu valori unice:

Dacă pe o singură foaie sunt amplasate 2 coloane, aproape unul de celălalt (adiacent) sau nu aproape unul de celălalt (nu alăturat), atunci procesul de eliminare a duplicatelor va fi puțin mai dificil. Nu putem șterge întregul rând cu valori duplicate, deoarece asta ar șterge și celulele din a doua coloană. Deci, pentru a păstra doar intrări unice într-o coloană A, urmează următoarele instrucțiuni:

După cum puteți vedea, eliminarea duplicatelor din două coloane în Excel folosind formule nu este atât de dificilă.

Uneori este nevoie să comparați două fișiere MS Excel. Aceasta poate fi găsirea discrepanțelor de preț pentru anumite articole sau modificarea oricăror indicații, nu contează, principalul lucru este că este necesar să găsiți anumite discrepanțe.

Nu ar fi greșit să menționăm că, dacă există câteva înregistrări în fișierul MS Excel, atunci nu are rost să recurgem la automatizare. Dacă fișierul conține câteva sute sau chiar mii de înregistrări, atunci este imposibil să faci fără ajutorul puterii de calcul a unui computer.

Să simulăm o situație în care două fișiere au același număr de linii, iar discrepanța trebuie căutată într-o anumită coloană sau în mai multe coloane. Această situație este posibilă, de exemplu, dacă trebuie să comparați prețul mărfurilor în funcție de două liste de prețuri sau să comparați măsurătorile sportivilor înainte și după sezonul de antrenament, deși pentru o astfel de automatizare trebuie să existe o mulțime de ele.

Ca exemplu de lucru, să luăm un fișier cu performanța participanților fictivi: alergare de 100 de metri, alergare de 3000 de metri și tracțiuni. Primul fișier este o măsurătoare la începutul sezonului, iar al doilea este sfârșitul sezonului.

Prima modalitate de a rezolva problema. Soluția este utilizarea doar formulelor MS Excel.

Deoarece înregistrările sunt aranjate vertical (aranjamentul cel mai logic), este necesar să folosiți funcția. Dacă utilizați plasarea orizontală a înregistrărilor, va trebui să utilizați funcția.

Pentru a compara performanța de alergare la 100 de metri, formula este următoarea:
=DACĂ(CĂUTAREV($B2,Sheet2!$B$2:$F$13,3,TRUE)<>D2;D2-VLOOKUP($B2;Sheet2!$B$2:$F$13,3,TRUE);„Fără diferență”)
Dacă nu există diferență, este afișat un mesaj că nu există diferență; dacă există o diferență, atunci valoarea de la sfârșitul sezonului este scăzută din valoarea de la începutul sezonului.

Formula pentru alergarea de 3000 de metri este următoarea:
=DACĂ(CĂUTAREV($B2,Sheet2!$B$2:$F$13,4,TRUE)<>E2;„Există o diferență”;„Nu există nicio diferență”)
Dacă valorile finale și inițiale nu sunt egale, este afișat un mesaj corespunzător. Formula pentru tracțiuni poate fi similară cu oricare dintre cele anterioare; nu are rost să o dați suplimentar. Fișierul final cu discrepanțele găsite este prezentat mai jos.

O mica precizare. Pentru a face formulele mai ușor de citit, datele din cele două fișiere au fost mutate într-unul singur (pe foi diferite), dar acest lucru nu s-ar fi putut face.

Video care compară două fișiere MS Excel folosind și funcții.

A doua modalitate de a rezolva problema. Soluție folosind MS Access.

Această problemă poate fi rezolvată dacă importați mai întâi fișiere MS Excel în Access. În ceea ce privește metoda de importare a datelor externe în sine, nu există nicio diferență în găsirea diferitelor câmpuri (oricare dintre opțiunile prezentate va fi potrivită).

Acesta din urmă este o conexiune între fișierele Excel și Access, așa că atunci când modificați datele din fișierele Excel, discrepanțele vor fi găsite automat atunci când executați o interogare în MS Access.

Următorul pas după import este crearea de relații între tabele. Ca câmp de legătură, selectați câmpul unic „Număr articol”.
Al treilea pas este să creați o interogare simplă de selectare folosind Generatorul de interogări.

În prima coloană indicăm ce înregistrări trebuie afișate, iar în a doua - în ce condiții vor fi afișate înregistrările. Desigur, pentru al doilea și al treilea câmp acțiunile vor fi similare.

Video care compară fișierele MS cu Excel folosind MS Access.

Ca urmare a manipulărilor efectuate, sunt afișate toate înregistrările, cu date diferite în câmp: „Alergare 100 de metri”. Fișierul MS Access este prezentat mai jos (din păcate, SkyDrive nu permite încorporarea ca fișier Excel)

Aceste două metode există pentru a găsi discrepanțe în tabelele MS Excel. Fiecare are atât avantaje, cât și dezavantaje. Evident, aceasta nu este o listă exhaustivă de comparații între cele două fișiere Excel. Așteptăm sugestiile dumneavoastră în comentarii.

Avem două tabele de ordine copiate într-o singură foaie de lucru. Este necesar să comparați datele din două tabele în Excel și să verificați ce poziții sunt în primul tabel, dar nu în al doilea. Nu are rost să compari manual valoarea fiecărei celule.

Comparați două coloane pentru potriviri în Excel

Cum se compară valorile în Excel a două coloane? Pentru a rezolva această problemă, vă recomandăm să utilizați formatarea condiționată, care evidențiază rapid elementele care se află într-o singură coloană. Fișă de lucru cu tabele:

Primul pas este denumirea ambelor tabele. Acest lucru face mai ușor de înțeles ce intervale de celule sunt comparate:

Selectați instrumentul FORMULE - Nume definite - Atribuire nume.
În fereastra care apare, în câmpul „Nume:”, introduceți valoarea – Tabel_1.
Utilizați butonul stâng al mouse-ului pentru a face clic pe câmpul de introducere „Interval:” și selectați intervalul: A2:A15. Și faceți clic pe OK.

Pentru a doua listă, efectuați aceiași pași, dă-i doar un nume – Tabel_2. Și specificați intervalul C2:C15 - respectiv.

Sfat util! Numele intervalelor pot fi atribuite mai rapid folosind câmpul de nume. Este situat în stânga barei de formule. Pur și simplu selectați intervale de celule și, în câmpul de nume, introduceți numele adecvat pentru interval și apăsați Enter.

Acum să folosim formatarea condiționată pentru a compara două liste în Excel. Trebuie să obținem următorul rezultat:

Elementele care se află în Tabelul_1, dar nu în Tabelul_2 vor fi afișate cu verde. În același timp, pozițiile care sunt în Tabel_2, dar nu în Tabel_1, vor fi evidențiate cu albastru.

Principiul comparării datelor între două coloane în Excel

La definirea condițiilor de formatare a celulelor coloanei, am folosit funcția COUNTIF. În acest exemplu, această funcție verifică de câte ori apare valoarea celui de-al doilea argument (de exemplu, A2) în lista primului argument (de exemplu, Tabel_2). Dacă numărul de ori = 0, atunci formula returnează TRUE. În acest caz, celulei i se atribuie formatul personalizat specificat în opțiunile de formatare condiționată.

Legătura din al doilea argument este relativă, ceea ce înseamnă că toate celulele din intervalul selectat vor fi verificate una câte una (de exemplu, A2:A15). De exemplu, pentru a compara două liste de prețuri în Excel, chiar și pe foi diferite. A doua formulă funcționează similar. Același principiu poate fi aplicat diferitelor sarcini similare.

Poate că toți cei care lucrează cu date în Excel se confruntă cu întrebarea cum să compare două coloane în Excel pentru asemănări și diferențe. Există mai multe moduri de a face acest lucru. Să aruncăm o privire mai atentă la fiecare dintre ele.

Cum se compară două coloane în Excel rând cu rând

Când comparați două coloane de date, este adesea necesar să comparați datele din fiecare rând individual pentru potriviri sau diferențe. Putem face o astfel de analiză folosind funcția. Să vedem cum funcționează acest lucru folosind exemplele de mai jos.

Exemplul 1: Cum să comparați două coloane pentru potriviri și diferențe pe același rând

Pentru a compara datele din fiecare rând de două coloane din Excel, să scriem o formulă simplă. Formula trebuie inserată în fiecare rând din coloana alăturată, lângă tabelul în care se află datele principale. După ce am creat o formulă pentru primul rând al tabelului, o putem întinde/copia în rândurile rămase.

Pentru a verifica dacă două coloane din același rând conțin aceleași date, avem nevoie de formula:

=IF(A2=B2; „Potrivire”; „”)

Formula care determină diferențele dintre datele a două coloane dintr-un rând va arăta astfel:

=DACA(A2<>B2; "Nu se potriveste"; „”)

Putem potrivi testul de potrivire și diferență între două coloane pe o linie într-o singură formulă:

=IF(A2=B2; „Potriviți”; „Nu se potrivesc”)

=DACA(A2<>B2; "Nu se potriveste"; „Potriviți”)

Un exemplu de rezultat de calcul ar putea arăta astfel:

Pentru a compara datele din două coloane ale aceluiași rând, ținând cont de majuscule și minuscule, utilizați formula:

=DACA(POTRIVIT(A2,B2), „Potrivire”, „Unic”)

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

Excel vă permite să comparați datele din mai multe coloane ale aceluiași rând folosind următoarele criterii:

Găsiți rânduri cu aceleași valori în toate coloanele tabelului;
Găsiți rânduri cu aceleași valori în oricare două coloane ale tabelului;

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

Să ne imaginăm că tabelul nostru este format din mai multe coloane de date. Sarcina noastră este să găsim rânduri în care valorile se potrivesc în toate coloanele. Excel și funcțiile ne vor ajuta în acest sens. Formula de determinare a potrivirilor va fi următoarea:

=DACĂ(ȘI(A2=B2;A2=C2); „Coincide”; „“)

Dacă tabelul nostru are o mulțime de coloane, atunci va fi mai ușor să utilizați funcția în combinație cu:

=IF(COUNTIF($A2:$C2,$A2)=3;”Potrivire”;” “)

În formulă, „5” indică numărul de coloane ale tabelului pentru care am creat formula. Dacă tabelul are mai multe sau mai puține coloane, atunci această valoare ar trebui să fie egală cu numărul de coloane.

Exemplul 2: Cum să găsiți potriviri de pe același rând în oricare două coloane ale unui tabel

Să ne imaginăm că sarcina noastră este să identificăm dintr-un tabel cu date în mai multe coloane acele rânduri în care datele sunt aceleași sau se repetă în cel puțin două coloane. Funcțiile și ne vor ajuta în acest sens. Să scriem o formulă pentru un tabel format din trei coloane de date:

=IF(SAU(A2=B2;B2=C2,A2=C2);”Potrivire”;” “)

În cazurile în care există prea multe coloane în tabelul nostru, formula noastră cu funcția va fi foarte mare, deoarece în parametrii săi trebuie să specificăm criteriile de potrivire între fiecare coloană a tabelului. O modalitate mai ușoară, în acest caz, este să utilizați .

=DACA(NUMĂRĂDAC(B2:D2,A2)+NUMĂRĂDAC(C2:D2,B2)+(C2=D2)=0; „Șir unic”; „Șir nu unic”)

=IF(COUNTIF($B:$B,$A5)=0; „Nu există potriviri în coloana B”; „Sunt potriviri în coloana B”)

Această formulă verifică valorile din coloana B pentru a vedea dacă se potrivesc cu celulele din coloana A.

Dacă tabelul este format dintr-un număr fix de rânduri, puteți specifica un interval clar în formulă (de exemplu, $B2:$B10). Acest lucru va accelera formula.

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

Când căutăm potriviri între două coloane în Excel, este posibil să fie nevoie să vizualizăm potrivirile găsite sau diferențele în date, de exemplu folosind evidențierea culorilor. Cel mai simplu mod de a evidenția potrivirile și diferențele de culoare este să utilizați formatarea condiționată în Excel. Să vedem cum să faceți acest lucru folosind exemplele de mai jos.

Găsirea și evidențierea potrivirilor în culori în mai multe coloane în Excel

În cazurile în care trebuie să găsim potriviri în mai multe coloane, atunci pentru aceasta avem nevoie de:

Selectați coloanele cu date în care doriți să calculați potrivirile;
În fila „Acasă” din Bara de instrumente, faceți clic pe elementul de meniu „Formatizare condiționată” -> „Reguli pentru evidențierea celulelor” -> „Valori repetate”;
În caseta de dialog pop-up, selectați „Repetare” în lista derulantă din stânga, iar în lista verticală din dreapta, selectați în ce valori de repetare a culorii vor fi evidențiate. Faceți clic pe butonul „OK”:
După aceasta, potrivirile vor fi evidențiate în coloana selectată:

Găsiți și evidențiați rândurile care se potrivesc în Excel

Găsirea celulelor care se potrivesc cu date din două sau mai multe coloane și căutarea potrivirilor de rânduri întregi cu date sunt concepte diferite. Vă rugăm să rețineți cele două tabele de mai jos:

Tabelele de mai sus conțin aceleași date. Diferența lor este că în exemplul din stânga am căutat celule potrivite, iar în dreapta am găsit linii întregi care se repetă cu date.

Să vedem cum să găsim rânduri potrivite într-un tabel:

În dreapta tabelului cu datele, vom crea o coloană auxiliară în care, vizavi de fiecare rând cu datele, vom pune o formulă care combină toate valorile rândului tabelului într-o singură celulă:

=A2&B2&C2&D2

În coloana auxiliară veți vedea datele combinate din tabel:

Acum, pentru a determina rândurile care se potrivesc din tabel, parcurgeți următorii pași:

Selectați zona cu date din coloana auxiliară (în exemplul nostru, acesta este un interval de celule E2:E15 );
În fila „Acasă” din Bara de instrumente, faceți clic pe elementul de meniu „Formatizare condiționată” -> „Reguli pentru evidențierea celulelor” -> „Valori repetate”;
În caseta de dialog pop-up, selectați „Repetare” în lista derulantă din stânga, iar în lista verticală din dreapta, selectați în ce culoare vor fi evidențiate valorile care se repetă. Faceți clic pe butonul „OK”:
După aceasta, liniile duplicate vor fi evidențiate în coloana selectată:

Adesea sarcina este de a compara două liste de elemente. A face acest lucru manual este prea plictisitor și, de asemenea, posibilitatea apariției unor erori nu poate fi exclusă. Excel face această operație ușoară. Acest sfat descrie o metodă care utilizează formatarea condiționată.

În fig. Figura 164.1 prezintă un exemplu de două liste de nume cu mai multe coloane. Folosirea formatării condiționate poate face evidente diferențele dintre liste. Aceste exemple de listă conțin text, dar metoda în cauză funcționează și cu date numerice.

Prima listă este A2:B31, acest interval este numit Lista veche. A doua listă este D2:E31, intervalul este numit NewList. Intervalele au fost denumite folosind comanda Formule Nume definite Atribuiți un nume. Nu este necesar să denumești intervalele, dar face lucrul cu ele mai ușor.

Să începem prin a adăuga formatare condiționată la vechea listă.

Selectați celule dintr-un interval Lista veche.
Selectați.
La fereastră Creați o regulă de formatare selectați elementul numit Utilizați formula
Introduceți această formulă în câmpul ferestrei (Fig. 164.2): =COUNTIF(NewList;A2)=0.
Faceți clic pe butonul Formatși specificați formatarea care va fi aplicată atunci când condiția este adevărată. Cel mai bine este să alegeți diferite culori de umplere.
Faceți clic pe OK.

Celulele din gamă NewList utilizați o formulă de formatare condiționată similară.

Selectați celule dintr-un interval NewList.
Selectați Acasă Formatare condiționată Creați o regulă pentru a deschide o casetă de dialog Creați o regulă de formatare.
La fereastră Creați o regulă formatarea elementului selectat Utilizați formula pentru a defini celulele de formatat.
Introduceți această formulă în câmpul ferestrei: =COUNTIF(OldList;D2)=0 .
Faceți clic pe butonul Formatși setați formatarea care urmează să fie aplicată atunci când condiția este adevărată (culoare de umplere diferită).
Faceți clic pe OK.

Ca urmare, vor fi evidențiate nume care se află în lista veche, dar nu și în cea nouă (Fig. 164.3). În plus, sunt evidențiate și nume din noua listă care nu sunt în vechea listă, dar într-o culoare diferită. Numele care apar în ambele liste nu sunt evidențiate.

Ambele formule de formatare condiționată folosesc funcția COUNTIF. Acesta calculează de câte ori apare o anumită valoare într-un interval. Dacă formula returnează 0, înseamnă că elementul nu se află în interval. În acest fel, formatarea condiționată preia și culoarea de fundal a celulei se schimbă.