Deduplicarea în #Excel

De multe ori la importul datelor din diferite surse, consolidarea acestora, sau pur si simplu prin colectare, pot apărea în tabelele de date, valori duplicat. Uneori pot fi duplicate doar anumite părți dintr-o înregistrare, alteori, întreaga înregistrare, sau în cele mai rele cazuri doar o coloană poate fi diferită.

Tabelul din acest articol este un demo care se referă la operațiunile de înregistrare cronologică a stocurilor de produse prin inventariere. La final de lună ne interesează să scoatem din acel tabel doar ultima valoare a stocului, corespondentă ultimei date calendaristice în care s-a efectuat operațiunea.

Pentru identificarea duplicatelor de pe o coloană, folosim formatarea condiționată, apoi filtrul pe culoare.

Ca să eliminăm înregistrările dublate, sau chiar triplate, treaba este destul de simplă. Folosim meniul Data, Remove Duplicates. În acest exemplu, o singură înregistrare era duplicată complet.

În cazul în care, este necesar, putem deselecta una sau mai multe coloane din tabel, după care putem obține rezultate diferite de deduplicare (*).

(*) – Deduplicarea este un neologism nerecunoscut în Dex, dar care se folosește destul de frecvent în domeniul stocării volumelor mari de date, în special în domeniul bazelor de date.

Uneori în anumite cazuri ca să pot identifica înregistrările duplicat pe toate valorile, folosesc o coloană suplimentară de concatenare după care aplic formatarea condiționată pe această coloană. Concatenarea se poate face clasic cu simbolul & (ampersand) sau cu ajutorul funcției ARRAYTOTEXT().

Cum funcționează Remove Duplicates?

Excelul identifică tuplul următor de combinații unice și-l elimină, păstrând pe ecran doar prima înregistrare întâlnită în tabel pe ordinea de sus în jos. Ca să putem avea un minim control pe ceea ce se elimină, în cazul în care nu selectăm toate coloanele, atunci putem să facem artificii din sortare.

De exemplu, din tabelul de produse, ne interesează doar codul și ultima dată cronologică de înregistrare a stocului cu valoarea cea mai mică.

În acest caz ar trebui să sortăm tabelul după codul produsului ascendent și după data înregistrării descendent, după care să aplicăm deduplicarea doar după primele două coloane.

În exemplul meu, am făcut ordonare și după stoc. Deduplicarea o realizez doar după Cod produs rămânând pe ecran doar valorile aferente fiecărui cod, ultimei date și celui mai mic Stoc, așa cum a fost declarat în sortare.

Această tehnică în schimb este riscantă în cazul volumelor mari de date, cu multe înregistrări și coloane.

De asemenea, eu diseminez permanent principiul nealterării sursei de date. În cazul în care facem o copie a datelor, tot ne va fi destul de greu să verificăm datele care s-au eliminat.

Deduplicarea cu funcții dynamic array

În urmă cu ceva timp scriam un articol despre determinarea numărului de înregistrări unice dintr-un tabel. Funcțiile dynamic array ne ajută în schimb să rezolvăm mai rapid problemele de duplicate. Articolul menționat rămâne valabil în contextul în care nu dispuneți de versiunile de Excel din Office 365 sau Office 2021.

Primul pas în deduplicare este să determinăm valorile unice ale codurilor și (opțional) sortarea ascendentă a acestora.

Celula G2: =SORT(UNIQUE(A2:A26)) în care Unique() extrage codurile unice de pe coloana A (Cod Produs) iar Sort() le aranjează crescător.

Celula H2: =MAX(FILTER($B$2:$B$26;$A$2:$A$26=G2)) reprezintă un filtru clasic pe baza codului de produs din G2. De pe coloana B se extrag toate datele calendaristice aferente acelui cod. Ca să obținem o singură valoare avem nevoie de funcția Max(). Mai multe detalii despre funcția Filter() puteți găsi în articolul: Funcția Filter() din #Excel 365. Reamintesc că ea este disponibilă în Excel din Office 365 și Excel 2021.

Celula I2: =MIN(FILTER($C$2:$C$26;($A$2:$A$26=G2)*($B$2:$B$26=H2))) reprezintă un minim aplicat pe un filtru cu două condiții: Prima condiție se referă la codul produsului din G2 iar a doua la data obținută în H2.

Această tehnică ne permite să păstrăm sursa de date nealterată, pentru asigurarea trasabilității și în același timp putem replica mult mai rapid problema pe seturi diferite de date de la o perioadă la alta sau putem identifica raportul în timp real. Eu prefer tehnica Tables pentru sursele de date ceea ce mă ajută să nu mai refac formulele în momentul în care apar noi date în tabel.

Operațiunile descrise în acest articol sunt disponibile și în Google Sheets. Link aici la fișierul din Google Sheet.

 

Sper să vă fie util!