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!

 

De ceva vreme încoace se tot anunță apariția metafuncției Lambda() cea care permite crearea de funcții personalizate în Excel fără utilizarea de VBA.

Personal, odată cu limitările și riscurile care apar în utilizarea macrourilor VBA am evitat constant să le utilizez sau predau, chiar dacă mulți clienți de Excel au solicitat constant cunoștințe suplimentare în acest domeniu. Pentru cei care nu știu prea multe, VBA era utilizat pentru a crearea funcțiilor personalizate sau pentru manipularea datelor în foile de calcul.

Manipularea datelor este rezolvată de ceva vreme prin utilizarea pachetelor Power Query care permit: importul, manipularea, transformarea și încărcarea datelor din diferite surse.

De câteva zile a apărut în versiunea de Excel din Office 365 funcția Lambda() alături de alte funcții corelate:

• REDUCE – Reduce o matrice (bloc de celule) la o valoare acumulată prin aplicarea unei funcții LAMBDA fiecărei valori și returnând valoarea totală în celula rezultat;
• SCAN – Scanează o matrice (bloc de celule) aplicând un LAMBDA fiecărei valori și returnează o matrice care are fiecare valoare intermediară;
• MAKEARRAY – Returnează o matrice calculată (bloc de celule) cu dimensiunea de rânduri și coloane specificate, prin aplicarea unei funcții LAMBDA;
• BYCOL – Aplică o funcție LAMBDA fiecărei coloane dintr-un bloc de celule (matrice) și returnează o matrice a rezultatelor cu același număr de coloane dar cu o singură linie cu rezultatele cumulate ale execuției LAMBDA;
• BYROW – Aplică o funcție LAMBDA fiecărei linii dintr-un bloc de celule (matrice) și returnează o matrice a rezultatelor cu același număr de linii dar cu o singură coloană cu rezultatele cumulate ale execuției LAMBDA;
• ISOMITTED – funcție de testare a parametrilor dintr-o funcție LAMBDA;
• MAP – Returnează o matrice formată prin maparea fiecărei valori din matrice(e) (blocul de valori) la o nouă valoare prin aplicarea unei funcții LAMBDA pentru a crea o nouă matrice de valoari.

Versiunea mea de Excel: Microsoft® Excel® for Microsoft 365 MSO (Version 2201 Build 16.0.14827.20186) 64-bit. Nu sunt înrolat în Office Insider, deci mesajul din paginile de help nu mai este de actualitate.

Structura funcției Lambda() de pe pagina oficială:

În continuare voi prezenta un exemplu simplu de creare a unei funcții personalizate pentru calculul prețului cu TVA pentru anumite produse.

În prima parte se creează și testează funcția cu parametrii dedicați:

În care:

• Valoare și Cotatva – sunt parametrii
• Valoare+valoare*cotatva este formula de calcul
• (B4;C4) – celulele în care sunt valorile parametrilor pentru testarea formulei.

Metoda aceasta o folosim doar pentru a compune și testa funcția. După validare funcției și rezultatului adăugăm funcția fără parametrii de testare în Name Manager din Formulas.

După definirea numelui fTVA putem folosi acum funcția fTVA() cu cei doi parametri.

În acest exemplu am folosit o contantă (19%) ca parametru al funcție fTVA()

 

Sper să fie util cuiva!