Cum se compară două foi Excel (pentru diferențe)

Vizionați video - Cum să comparați două foi Excel pentru diferențe

Compararea a două fișiere Excel (sau compararea a două foi în același fișier) poate fi dificilă, întrucât un registru de lucru Excel arată doar o coală la un moment dat.

Acest lucru devine mai dificil și predispus la erori atunci când aveți o mulțime de date care trebuie comparate.

Din fericire, există câteva caracteristici interesante în Excel care vă permit să deschideți și să comparați cu ușurință două fișiere Excel.

În acest tutorial Excel, vă voi arăta mai multe moduri de a comparați două fișiere (sau foi) Excel diferite și verificați dacă există diferențe. Metoda pe care o alegeți va depinde de modul în care sunt structurate datele dvs. și de tipul de comparație pe care îl căutați.

Să începem!

Comparați două foi Excel în fișiere Excel separate (alăturate)

Dacă doriți să comparați două fișiere Excel separate unul lângă altul (sau două foi în același registru de lucru), există o caracteristică încorporată în Excel pentru a face acest lucru.

Este Vizualizați una lângă alta opțiune.

Acest lucru este recomandat numai atunci când aveți un set de date mic și compararea manuală a acestor fișiere este probabil mai puțin consumatoare de timp și predispusă la erori. Dacă aveți un set de date mare, vă recomand să utilizați metoda condițională sau metoda formulă acoperită mai târziu în acest tutorial.

Să vedem cum să utilizați acest lucru atunci când trebuie să comparați două fișiere separate sau două foi în același fișier.

Să presupunem că aveți două fișiere pentru două luni diferite și doriți să verificați ce valori sunt diferite în aceste două fișiere.

În mod implicit, atunci când deschideți un fișier, este probabil să vă ocupe întregul ecran. Chiar dacă reduceți dimensiunea, vedeți întotdeauna un fișier Excel în partea de sus.

Cu opțiunea de vizualizare side-by-side, puteți deschide două fișiere și apoi le puteți aranja orizontal sau vertical. Acest lucru vă permite să comparați cu ușurință valorile fără a comuta înainte și înapoi.

Mai jos sunt pașii pentru a alinia două fișiere unul lângă altul și a le compara:

1. Deschideți fișierele pe care doriți să le comparați.
2. În fiecare fișier, selectați foaia pe care doriți să o comparați.
3. Faceți clic pe fila Vizualizare
4. În grupul Windows, faceți clic pe opțiunea „Vizualizați unul lângă altul”. Acest lucru devine disponibil numai atunci când aveți două sau mai multe fișiere Excel deschise.

De îndată ce faceți clic pe opțiunea View side by side, Excel va aranja registrul de lucru pe orizontală. Ambele fișiere vor fi vizibile și puteți edita / compara aceste fișiere în timp ce acestea sunt aranjate una lângă alta.

În cazul în care doriți să aranjați fișierele pe verticală, faceți clic pe opțiunea Aranjați toate (în fila Vizualizare).

Aceasta va deschide caseta de dialog „Aranjați Windows”, unde puteți selecta „Vertical”.

În acest moment, dacă derulați în jos într-una din foile de lucru, cealaltă ar rămâne așa cum este. Puteți schimba acest lucru astfel încât, atunci când derulați într-o coală, și cealaltă să deruleze în același timp. Acest lucru face mai ușoară efectuarea unei comparații rând cu rând și identificarea diferențelor.

Dar pentru a face acest lucru, trebuie să activați Defilare sincronă.

Pentru a activa Derularea sincronă, faceți clic pe fila Vizualizare (în oricare dintre registrele de lucru) și apoi faceți clic pe opțiunea Derulare sincronă. Acesta este un buton de comutare (deci, dacă doriți să îl dezactivați, faceți clic pe el din nou).

Compararea mai multor foi în fișiere Excel separate (alăturate)

Cu opțiunea „Vizualizați unul lângă altul”, puteți compara doar două fișiere Excel dintr-o dată.

În cazul în care aveți mai multe fișiere Excel deschise, când faceți clic pe opțiunea View Side by Side, aceasta vă va afișa o casetă de dialog „Comparare Side by Side”, unde puteți alege ce fișier doriți să comparați cu registrul de lucru activ.

În cazul în care doriți să comparați mai mult de două fișiere dintr-o dată, deschideți toate aceste fișiere și apoi faceți clic pe opțiunea Aranjați toate (este în fila Vizualizare).

În caseta de dialog Aranjare Windows, selectați Vertical / Orizontal și apoi faceți clic pe OK.

Aceasta va aranja toate fișierele Excel deschise în ordinea selectată (verticală sau orizontală).

Comparați două foi (Side-by-Side) în același registru de lucru Excel

În cazul în care doriți să comparați două foi separate în același registru de lucru, nu puteți utiliza caracteristica Vizualizare una lângă alta (deoarece funcționează numai pentru fișiere Excel separate).

Dar puteți face totuși aceeași comparație side-by-side.

Acest lucru este posibil prin „Funcția Windows nouă în Excel, care vă permite să deschideți două instanțe pe același registru de lucru. După ce aveți două instanțe deschise, le puteți aranja una lângă alta și apoi le puteți compara.

Să presupunem că aveți un registru de lucru Excel care are două foi pentru două luni diferite (ianuarie și februarie) și doriți să le comparați unul lângă altul pentru a vedea cum s-au schimbat vânzările pe magazin:

Mai jos sunt pașii pentru a compara două foi în Excel:

1. Deschideți registrul de lucru care conține foile pe care doriți să le comparați.
2. Faceți clic pe fila Vizualizare
3. În grupul Window, faceți clic pe opțiunea „New Window”. Aceasta deschide a doua instanță a aceluiași registru de lucru.
4. În fila „Vizualizare”, dați clic pe „Aranjați toate”. Aceasta va deschide caseta de dialog Aranjare Windows
5. Selectați „Vertical” pentru a compara datele din coloane (sau selectați Orizontală dacă doriți să comparați datele în rânduri).
6. Faceți clic pe OK.

Pașii de mai sus aranjează ambele instanțe ale registrului de lucru pe verticală.

În acest moment, ambele registre de lucru ar avea aceeași foaie de lucru selectată. În unul dintre registrele de lucru, selectați cealaltă foaie pe care doriți să o comparați cu foaia activă.

Cum funcționează asta?

Când faceți clic pe fereastră nouă, acesta deschide din nou același registru de lucru cu un nume ușor diferit. De exemplu, dacă numele registrului dvs. de lucru este „Test” și faceți clic pe fereastră nouă, acesta va denumi registrul de lucru deja deschis „Test - 1” și a doua instanță ca „Test - 2”.

Rețineți că acestea sunt în continuare același registru de lucru. Dacă faceți modificări în oricare dintre aceste registre de lucru, aceasta se va reflecta în ambele.

Și când închideți orice instanță a fișierului deschis, numele va reveni la original.

De asemenea, puteți activa derularea sincronă dacă doriți (făcând clic pe opțiunea „Derulare sincronă” din fila „Vizualizare”)

Comparați două foi și evidențiați diferențele (folosind formatarea condiționată)

Deși puteți utiliza metoda de mai sus pentru a alinia registrele de lucru împreună și pentru a parcurge manual rândul cu rândul, nu este o modalitate bună în cazul în care aveți o mulțime de date.

De asemenea, efectuarea manuală a acestui nivel de comparație poate duce la o mulțime de erori.

Deci, în loc să faceți acest lucru manual, puteți utiliza puterea Formatării condiționate pentru a evidenția rapid orice diferențe în cele două foi Excel.

Această metodă este foarte utilă dacă aveți două versiuni în două foi diferite și doriți să verificați rapid ce s-a schimbat.

Rețineți că dumneavoastră NU POTI comparați două foi în cărți de lucru diferite.

Deoarece formatarea condiționată nu se poate referi la un fișier Excel extern, foile pe care trebuie să le comparați trebuie să fie în același registru de lucru Excel. În cazul în care acestea nu sunt, puteți copia o foaie din celălalt fișier în registrul de lucru activ și apoi faceți această comparație.

Pentru acest exemplu, să presupunem că aveți un set de date așa cum se arată mai jos timp de două luni (ianuarie și februarie) în două foi diferite și doriți să comparați rapid datele din aceste două foi și să verificați dacă prețurile acestor articole s-au modificat sau nu.

Mai jos sunt pașii pentru a face acest lucru:

1. Selectați datele din foaia în care doriți să evidențiați modificările. Deoarece vreau să verific cum s-au schimbat prețurile din ianuarie în februarie, am selectat datele din foaia de februarie.
2. Faceți clic pe fila Acasă
3. În grupul Stiluri, faceți clic pe „Formatare condiționată”
4. În opțiunile care apar, faceți clic pe „Regula nouă”
5. În caseta de dialog „Noua regulă de formatare”, faceți clic pe „Utilizați o formulă pentru a determina ce celule să formatați”
6. În câmpul formulă, introduceți următoarea formulă: = B2Jan! B2
7. Faceți clic pe butonul Format
8. În caseta de dialog Formatare celule care apare, faceți clic pe „fila Completare” și selectați culoarea în care doriți să evidențiați datele nepotrivite.
9. Faceți clic pe OK
10. Faceți clic pe OK

Pașii de mai sus ar evidenția instantaneu orice modificare a setului de date din ambele foi.

Cum funcționează asta?

Formatarea condiționată evidențiază o celulă atunci când formula dată pentru acea celulă returnează un TRUE. În acest exemplu, comparăm fiecare celulă dintr-o foaie cu celula corespunzătoare din cealaltă foaie (realizată folosind operatorul care nu este egal cu formula).

Când formatarea condițională găsește vreo diferență în date, aceasta evidențiază faptul că în foaia Jan (cea în care am aplicat formatarea condițională.

Rețineți că am folosit referințe relative în acest exemplu (A1 și nu $ A $ 1 sau $ A1 sau A $ 1).

Când utilizați această metodă pentru a compara două foi în Excel, amintiți-vă următoarele;

• Această metodă este bună pentru a identifica rapid diferențele, dar nu o puteți folosi continuu. De exemplu, dacă introduc un rând nou în oricare dintre seturile de date (sau șterg un rând), mi-ar da rezultate incorecte. De îndată ce introduc / șterg rândul, toate rândurile ulterioare sunt considerate diferite și evidențiate în consecință.
• Puteți compara doar două foi în același fișier Excel
• Puteți compara doar valoarea (nu diferența de formulă sau formatare).

Comparați două fișiere / foi Excel și obțineți diferențele folosind formula

Dacă sunteți interesat doar să comparați rapid și să identificați diferențele dintre două foi, puteți utiliza o formulă pentru a prelua numai acele valori diferite.

Pentru această metodă, va trebui să aveți o foaie de lucru separată unde puteți prelua diferențele.

Această metodă ar funcționa dacă doriți să comparați două cărți de lucru Excel separate sau foi de lucru în același registru de lucru.

Permiteți-mi să vă arăt un exemplu în care compar două seturi de date în două foi (în același registru de lucru).

Să presupunem că aveți setul de date așa cum se arată mai jos într-o foaie numită Jan (și date similare într-o foaie numită Feb) și doriți să știți ce valori sunt diferite.

Pentru a compara cele două foi, mai întâi, introduceți o nouă foaie de lucru (să numim această foaie „Diferență”).

În celula A1, introduceți următoarea formulă:

= IF (Jan! A1Feb! A1, "Jan Value:" & Jan! A1 & CHAR (10) & "Feb Value:" & Feb! A1, "")

Copiați și lipiți această formulă pentru un interval, astfel încât să acopere întregul set de date din ambele foi. Deoarece am un set de date mic, voi copia și insera această formulă numai în intervalul A1: B10.

Formula de mai sus folosește o condiție IF pentru a verifica diferențele. În cazul în care nu există nicio diferență în valori, acesta va reveni necompletat și, în cazul în care există o diferență, va returna valorile din ambele foi în linii separate în aceeași celulă.

Lucrul bun cu această metodă este că vă oferă doar diferențele și vă arată exact care este diferența. În acest exemplu, pot vedea cu ușurință că prețul în celulele B4 și B8 sunt diferite (precum și valorile exacte din aceste celule).

Comparați două fișiere / foi Excel și obțineți diferențele folosind VBA

Dacă trebuie să comparați fișierele sau foile Excel destul de des, este o idee bună să aveți un cod Excel VBA macro pregătit și să-l utilizați ori de câte ori trebuie să faceți comparația.

De asemenea, puteți adăuga macrocomanda la Bara de instrumente de acces rapid, astfel încât să puteți accesa cu un singur buton și să știți instantaneu ce celule sunt diferite în diferite fișiere / foi.

Să presupunem că aveți două foi Jan și Feb și doriți să comparați și să evidențiați diferențele în foaia Jan, puteți utiliza codul VBA de mai jos:

Sub CompareSheets () Dim rngCell ca interval pentru fiecare rngCell din foi de lucru („Jan”). UsedRange Dacă nu rngCell = Worksheets („Feb”). Cells (rngCell.Row, rngCell.Column) Apoi rngCell.Interior.Color = vbYellow End Dacă Următorul rngCell End Sub

Codul de mai sus folosește bucla For Next pentru a parcurge fiecare celulă din foaia Jan (întregul interval utilizat) și o compară cu celula corespunzătoare din foaia Feb. În cazul în care găsește o diferență (care este verificată folosind instrucțiunea If-Then), evidențiază acele celule în galben.

Puteți utiliza acest cod într-un modul obișnuit din Editorul VB.

Și dacă trebuie să faceți acest lucru des, este mai bine să salvați acest cod în registrul de lucru Personal Macro și apoi să îl adăugați la bara de instrumente Acces rapid. În aceste moduri, veți putea face această comparație printr-un clic pe un buton.

Iată pașii pentru obținerea registrului de lucru personal Macro în Excel (nu este disponibil în mod implicit, deci trebuie să îl activați).

Iată pașii pentru a salva acest cod în registrul de lucru pentru macrocomenzi personale.

Și aici veți găsi pașii pentru a adăuga acest cod macro la QAT.

Utilizarea unui instrument terță parte - XL Comparator

O altă modalitate rapidă de a compara două fișiere Excel și de a verifica dacă există potriviri și diferențe este utilizând un instrument gratuit de la terți, cum ar fi XL Comparator.

Acesta este un instrument bazat pe web în care puteți încărca două fișiere Excel și va crea un fișier de comparație care va avea datele obișnuite (sau date diferite în funcție de ce opțiune ați selectat.

Să presupunem că aveți două fișiere care au seturi de date ale clienților (cum ar fi numele și adresa de e-mail) și doriți să verificați rapid ce clienți sunt fișierul 1 și nu în fișierul 2.

Mai jos este modul în care comparați două fișiere Excel și creați un raport de comparație:

1. Deschideți https://www.xlcomparator.net/
2. Utilizați opțiunea Alegeți fișierul pentru a încărca două fișiere (dimensiunea maximă a fiecărui fișier poate fi de 5 MB)
3. Faceți clic pe butonul Următor.
4. Selectați coloana comună din ambele fișiere. Instrumentul va folosi această coloană comună pentru a căuta potriviri și diferențe
5. Selectați una dintre cele patru opțiuni, indiferent dacă doriți să obțineți date potrivite sau date diferite (pe baza fișierului 1 sau a fișierului 2)
6. Faceți clic pe Următorul
7. Descărcați fișierul de comparație care va conține datele (pe baza opțiunii selectate la pasul 5)

Mai jos este un videoclip care arată cum funcționează instrumentul XL Comparator.

O preocupare pe care o puteți avea atunci când utilizați un instrument terță parte pentru a compara fișierele Excel este legată de confidențialitate. Dacă aveți date confidențiale și confidențialitatea este cu adevărat importantă pentru aceasta, este mai bine să utilizați alte metode prezentate mai sus. Rețineți că site-ul web XL Comparator menționează că șterg toate fișierele după 1 oră de la efectuarea comparației.

Acestea sunt câteva dintre metodele pe care le puteți utiliza pentru a compara două fișiere Excel diferite (sau foi de lucru în același fișier Excel). Sper că ați găsit util acest tutorial Excel.