Modalități de comparare a datelor din fișierele Excel

Mai jos sunt descrise câteva modalități de comparare a diferitelor seturi de date în Excel, care se află pe două coloane în aceeași foaie de lucru sau în două fișiere Excel diferite.

Compararea a două coloane

Se selectează o celulă goală, aflată la dreapta coloanelor care vor fi comparate. La compararea a două coloane dintr-o foaie de lucru, rezultatul comparării va fi trecut într-o coloană goală. De exemplu, dacă cele două coloane care vor fi comparate încep din celulele C5 și D5, rezultatul va fi afișat începând cu celula E5.

În celula E5 se tastează formula de comparare pentru primul rând. În acest caz, se compară celulele C5 și D5:

=IF(C5=D5,"Se potrivesc","Nu se potrivesc")

Apoi, cu dublu clic pe marcajul Fill box aflat în partea de jos dreapta a celulei, formula se copie automat în celelalte celule ale coloanei și Excel afișează rezultatul pentru fiecare celulă în parte.

Compararea funcționează la șiruri de caractere, date calendaristice, numere și timp. Formula nu ia în considerare faptul că textul este scris cu litere mici sau cu majuscule (comparația între "EXCEL" și "excel" va avea ca rezultat "Se potrivesc").

Notă: Pentru a compara și textul, se recomandă formula EXACT - în acest caz, =EXACT(C7,D7). Folosind IF și EXACT, formula ar putea fi folosită și astfel:

=IF(EXACT(C5,D5), "Se potrivesc", "Nu se potrivesc")

În acest caz, Excel va compara și modul în care este scris textul (litere mici sau mari).

Compararea a două foi de lucru așezate alăturat

Se deschide prima foaie de lucru. Apoi se deschide a doua foaie de lucru. În acest moment aveți două instanțe Excel deschise în computer.

Clic pe eticheta View în unul din cele două fișiere deschise. În secțiunea Window, clic pe butonul Arange All, apoi în fereastra de dialog Arrange Windows, se alege opțiunea Vertical și clic pe butonul OK.

Foile de lucru vor fi orientate la stânga și la dreapta.

Datorită activării opțiunii Synchronous Scrolling (derulare sincronizată), derulând datele dintr-o fereastră, Excel le va derula și pe cele din cealaltă fereastră. Acest lucru vă va permite să căutați cu ușurință diferențe în timp ce parcurgeți foile de calcul.

Puteți dezactiva derularea sincronizată cu clic pe butonul Synchronous Scrolling din fila View (Vizualizare).

Compararea diferențelor din două foi de lucru

Deschideți registrul de lucru care conține cele două foi pe care doriți să le comparați. Pentru a utiliza această formulă de comparație, ambele foi de lucru trebuie să se afle în același fișier / registru de lucru.

În foaia de lucru nouă, selectați celula A1 și introduceți sau copiați următoarea formulă de comparație (dacă diferă numele foilor de lucru sau al celulelor, formula va fi diferită):

=IF(Foaie1!A1<> Foaie2!A1, "Foaie1:" & Foaie1!A1 & " versus Foaie2: " & Foaie2!A1, "")

În formula IF se compară dacă celula A1 din Foaie1 diferă (<>) de celula A1 din Foaie2 (testul logic scris cu roșu).

Atunci când conținutul celor două celule diferă (întoarce valoarea TRUE după testul logic), va afișa un mesaj cu conținutul celor două celule (mesajul este scris cu verde).

Când conținutul nu diferă (întoarce valoarea FALSE), Excel nu va afișa niciun mesaj (aici reprezentat la finalul formulei de două ghilmele duble fără nimic între ele, "").

Apoi, având selectate celulele, trageți de Fill box și spre dreapta, pentru a compara și coloanele aflate la dreapta coloanei A. De exemplu, dacă foile de calcul ajung până la coloana Q, trageți până la coloana respectivă.

Utilizarea formatului condiționat pentru a diferenția și evidenția valori

Presupunem că sunt două liste de date (lista 1 și lista 2) în foaia de lucru Excel. Aceste liste pot fi comparate pentru a afla dacă au date comune sau pentru a evidenția diferitele seturi de date.

Iată mai jos o modalitate care poate fi utilizată pentru a compara două liste și pentru a identifica datele diferite sau duplicate.

Altă metodă de a compara datele din două foi de calcul Excel

Pentru a compara manual două foi de calcul diferite și a determina dacă datele din ele sunt sau nu diferite sau, alternativ, dacă există rânduri duplicate, este o sarcină de durată. Din fericire, MS Excel are funcția COUNTIF () care poate simplifica această sarcină.

Să presupunem că datele sunt salvate în Sheet1 și Sheet2 din registrul de lucru Excel.

În celula B1 din Sheet1 se introduce următoarea formulă:

= COUNTIF(Sheet2!A:A, Sheet1!A1)

și se apasă tasta Enter. Dacă înregistrarea este unică, rezultatul va fi 0, altfel contorul vă va spune câte rânduri din Sheet2 se potrivesc cu celula A1 din Sheet1.

Se copie formula din celula B1 la toate rândurile din tabelul din Sheet1.

Compararea a două liste folosind funcția VLOOKUP

Presupunem că avem două liste, care se pot afla în foi de lucru sau în registre diferite. Pentru a le putea compara, înregistrările dintr-o listă trebuie să aibă un identificator unic, care să se potrivească exact cu identificatorul din cealaltă listă (trebuie să aibă cel puțin un element comun - de exemplu numărul de inventar, seria de fabricație, codul de bare etc.).

În cazul de mai jos, ambele liste au un ID unic, iar formula de comparare a valorilor inițială și finală este:

=VLOOKUP(D13,[ValoriFinale.xlsx]Foaie1!D$13:J$44,7,FALSE)

unde:

• D13 este ID-ul care va fi căutat în cealaltă foaie de lucru (sau celălalt registru)
• [ValoriFinale.xlsx] este numele registrului în care se caută ID-ul - dacă căutarea se face în alt registru Excel
• Foaie1 este numele foii de lucru în care se face căutarea
• D$13:J$44 este zona în care se caută ID-ul; Excel caută în prima coloană, aici coloana D, dar va întoarce valorile finale aflate în coloana J; pentru liniile din zona de căutare a fost folosită referința absolută: caracterul $;
• 7 este numărul coloanei de unde va fi preluat rezultatul - aici a șaptea coloană, coloana D fiind prima, coloana E - a doua.... , iar coloana J - a șaptea.
• FALSE va întoarce valoarea cerută doar dacă ID-ul unic găsit este identic cu cel căutat.

Sunt situații în care ID-ul nu este găsit, iar Excel afișează ca rezultat al căutării eroarea #NA. Această eroare indică în general că formula nu a găsit ID-ul căutat. Pentru a afișa altceva în loc de #NA, se poate adăuga formula IFERROR, valabilă începând cu Excel 2007.

Sintaxa formulei IFERROR este:

unde:

• value este argumentul care este verificat pentru o eroare - aici funcția VLOOKUP.
• value_if_error este valoarea afișată (sau mesajul afișat) dacă formula din value returnează o eroare. Sunt evaluate următoarele tipuri de erori : # N/A, #VALUE !, #REF !, #DIV / 0 !, #NUM !, #NAME ?, sau #NULL !.

Formula devine:

=IFERROR(VLOOKUP(D13,[ValoriFinale.xlsx]Foaie1!D$13:J$44,7,FALSE),"Nu am gasit inregistrarea")

În acest caz, atunci când funcția VLOOKUP nu va găsi un ID, Excel va afișa mesajul

Nu am gasit inregistrarea