Compararea datelor în Excel pe diferite foi. Potriviți în coloanele Excel

Astăzi vă voi spune cum să căutați o potrivire în coloanele Excel. Să ne uităm la toate subtilitățile folosind exemple.

Problema 1: Există 6 texte în 6 celule. Trebuie să aflați care sunt unice și care se repetă.

Noi vom folosi Formatarea condițională.

Selectați celulele care trebuie comparate;
În fila Acasă accesați „Formatare condiționată -> Reguli de selecție a celulelor -> Valori repetate”;

Apare o fereastră nouă și celulele din tabel care sunt repetate încep să fie evidențiate.

În această fereastră puteți alege două setări: evidențiați Repetitiv sau Celule unice, precum și ce fel de iluminare de fundal să utilizați - chenar, text, culoare text, fundal sau unul unic.

Să ne uităm la un alt exemplu. Trebuie să comparați două coloane în Excel pentru potriviri. Există un tabel care are și potriviri, dar sunt numerice.

Selectați tabelul și accesați Valori duplicate. Toate meciurile vor fi evidențiate.

Puteți, de exemplu, să găsiți potriviri într-o singură coloană. Pentru a face acest lucru, este suficient să o selectați numai înainte de a utiliza opțiunea.

Ei bine, așa cum am spus mai sus, din lista derulantă puteți selecta nu numai celule repetate, ci și unice.

Puteți căuta în acest fel litere, cuvinte, simboluri, texte etc.

În această lecție vă voi spune cum să atașați un antet la fiecare pagină Excel. Acest lucru poate fi necesar dacă lucrați cu mese mari. De obicei, atunci când sunt tipărite, se produc documente cu un număr mare de pagini, iar pentru a facilita lucrul cu acestea, este necesar să salvați antetul tabelului pe fiecare foaie.

În această lecție vă voi spune cum să împărțiți textul în coloane în Excel. Această lecție este potrivită pentru dvs. dacă doriți să împărțiți textul dintr-o coloană în mai multe. Acum voi da un exemplu. Să presupunem că aveți celula „A”, care conține prenumele, numele și patronimul. Trebuie să vă asigurați că în prima celulă „A” există doar numele de familie, în celula „B” - prenumele, iar în celula „C” mijlocul numelui.

În acest articol vă voi spune cum eliminați duplicatele din Excel. Vom lua în considerare cea mai simplă metodă standard, care a apărut începând cu Excel 2007.

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 sunt în Tabel_1, dar nu în Tabel_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.

Întrebare de la un utilizator

Am o sarcină și pentru a treia zi acum mi-am zguduit mintea - nu știu cum să o duc la bun sfârșit. Există 2 tabele (aproximativ 500-600 de rânduri în fiecare), trebuie să luați coloana cu numele produsului dintr-un tabel și să o comparați cu numele produsului din celălalt, iar dacă produsele se potrivesc, copiați și lipiți valoarea din tabelul 2 în tabelul 1. Explicat confuz, dar cred că veți înțelege sarcina din fotografie ( aproximativ : fotografia a fost tăiată de cenzură, este încă informații personale).

Vă mulțumesc anticipat. Andrei, Moscova.

Bună ziua tuturor!

Ceea ce ați descris se referă la sarcini destul de populare care sunt relativ ușor și rapid de rezolvat folosind Excel. Tot ce trebuie să faceți este să puneți cele două tabele în program și să utilizați funcția CĂUTARE V. Mai multe despre munca ei mai jos...

Un exemplu de lucru cu funcția VLOOKUP

Ca exemplu, am luat două semne mici, acestea sunt afișate în captura de ecran de mai jos. În primul tabel (coloane A, B- produs și preț) nu există date pe coloană B; în a doua, sunt completate ambele coloane (produs și preț). Acum trebuie să verificați primele coloane din ambele tabele și automat, dacă se găsește o potrivire, copiați prețul în primul tabel. Pare o sarcină simplă...

Cum să o facă...

Plasați indicatorul mouse-ului în celulă B2- adică în prima celulă a coloanei unde nu avem valoare și scriem formula:

=CĂUTAREV(A2,$E$1:$F$7,2,FALSE)

A2- valoarea din prima coloană a primului tabel (ce vom căuta în prima coloană a celui de-al doilea tabel);

$E$1:$F$7- un al doilea tabel complet selectat (în care vrem să găsim și să copiem ceva). Acordați atenție semnului „$” - este necesar ca atunci când copiați formula, celulele celui de-al doilea tabel selectat să nu se schimbe;

2 - numărul coloanei din care copiem valoarea (rețineți că al doilea tabel selectat are doar 2 coloane. Dacă ar avea 3 coloane, atunci valoarea ar putea fi copiată din coloana a 2-a sau a 3-a);

MINCIUNĂ- cautam o potrivire exacta (in caz contrar se va inlocui primul asemanator care evident nu ne convine).

De fapt, puteți ajusta formula finită la nevoile dvs. modificând-o ușor. Rezultatul formulei este prezentat în imaginea de mai jos: prețul a fost găsit în al doilea tabel și a intrat în modul automat. Totul merge!

Pentru a seta prețul pentru alte nume de produse, pur și simplu extindeți (copiați) formula în alte celule. Exemplu de mai jos.

După care, după cum puteți vedea, primele coloane ale tabelelor vor fi comparate: din rândurile în care valorile celulelor coincid, datele necesare vor fi copiate și înlocuite. În general, este clar că mesele pot fi mult mai mari!

Notă: Trebuie să spun că funcția VLOOKUP este destul de solicitantă pentru resursele computerului. În unele cazuri, cu un document excesiv de mare, compararea tabelelor poate dura destul de mult. În aceste cazuri, merită să luați în considerare fie alte formule, fie soluții complet diferite (fiecare caz este individual).

Asta e tot, succes!

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 singură 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 repetate î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 valori de repetare a culorii vor fi evidențiate. Faceți clic pe butonul „OK”:
După aceasta, liniile duplicate vor fi evidențiate în coloana selectată: