#Excel – Determinarea numărului de înregistrări unice


În viața reală apar tot felul de cazuri și metode de utilizare ale Excel-ului. Unele probleme cu datele vin din însăși modul eronat de utilizare a acestuia și anume colectarea de date. În acest proces de colectare, eventual centralizat din surse multiple este posibil să înregistrăm duplicate, care ne pot afecta rezultate finale ale unor rapoarte. Alteori duplicatele nu apar din eroarea de colectare ci pur și simplu ca excepții de proces.

Acest articol își propune să pună la dispoziție câteva metode de determinare a numărului de înregistrări unice folosind o singură formulă, fără coloane ajutătoare, macros sau pivoți.

Metodele manuale, aparent mai simple sunt cele de formatare condițională pe duplicate (aplicabile la nivel de coloană) și filtrarea tabelului cu date pe diferite criterii, sau eliminarea duplicatelor din sursa de date direct (Meniul Data, Remove Duplicates). Se poate! Dar ca orice operațiune manuală… oameni fiind, mai putem și greși.

În continuarea acestui articol voi exemplifica mai multe metode de determinare a numărului de înregistrări unice.

Pentru a descărca fișierul acestui demo vă rog să accesați adresa: https://1drv.ms/x/s!ApGubfWFh8Nuq_lhIVWEAtZqt8rz1g

Tabelul de date conține o listă de agenți cu numărul lor de marcă, numele și orașul pe care l-au vizitat sau în care au efectuat operațiuni. Mai multe înregistrări sunt duplicate intenționat.

Trebuie specificat faptul că aceste modele de probleme nu se pot rezolva corect prin folosirea tehnicii tabelelor pivot pentru că în acestea nu avem posibilitatea de a număra unicitățile pe diferite criterii.

Determinarea numărului unic de înregistrări pe o coloană

În celula F3 este specificat numărul de înregistrări totale din tabel prin folosirea funcției COUNTA() care numără celulele de tip text dintr-un bloc de căsuțe.

Pentru a determina numărul unic de numere de marcă vom folosi în celula F5 formula array:

=SUMPRODUCT(1/COUNTIF(A2:A20;A2:A20))

În care COUNTIF(A2:A20;A2:A20) realizează o vector (array) cu numărul de apariții a fiecărui element din lista A2:A20 cu condiția să fie egal cu oricare valoare a elementului din lista A2:20. Este un artificiu care determină numărul de apariții. Apoi 1/numărul de apariții determină un vector de valori care însumate dar numărul unic de înregistrări.

Formulele array se introduc cu ajutorul combinației de taste CTRL+SHIFT+ENTER. La fel putem calcula numele unice și numărul de localități unice.

Determinarea numărului unic de apariții pe o coloană în funcție de condițiile de pe altă coloană

Dacă dorim să știm câți agenți unici au fost în fiecare localitate atunci va trebui să numărăm numele unice ale agenților (mai corect ar fi numerele de marcă) care au fost într-o localitate specificată în condiție. Este cazul tabelului de la F10.

Formula array din G11 va fi:

=SUM(–(FREQUENCY(IF($C$2:$C$20=F11;MATCH($B$2:$B$20;$B$2:$B$20;0));ROW($B$2:$B$20)-ROW($B$2)+1)>0))

În care se folosește o combinație de formule din care cele mai importante sunt IF() pentru stabilirea condiției problemei: numărarea agenților unici care au fost în localitatea respectivă, și FREQUENCY care este o formulă prin natura sa Array care determină frecvența de apariție a unui număr pe baza unei liste de valori unice (bit). Bit-ul este determinat prin combinația de comenzi ROW(). O traducere a formulei: însumează valorile rezultate în vectorul de determinare a frecvenței de apariție a numelor unice de agenți (funcția MATCH()) specifice orașului dat (funcția IF) pentru numele unice de pe B2 (funcțiile ROW()).

Hint: Pentru a putea vedea cum funcționează formula, puteți reduce numărul de înregistrări la 5, apoi apelați opțiunea Evaluate formula din meniul Formulas.

 

Determinarea valorilor unicelor pe baza a două condiții

În cazul în care dorim să facem un raport de tip matrice (asemănător unui tabel pivot) în care dorim să numărăm doar înregistrările unice apărute va trebui să folosim aceeași formulă ca în exemplul precedent. Pentru a specifica două condiții trebuie să specificăm acestea cu (condiție)*(condiție) în partea de IF iar numărarea să o facem pe coloana de marca:

Formula din G24 pentru determinarea numărului unic de deplasări ale unui agent într-un oraș (în formatul logic: 0 nu a fost, 1 a fost) este:

=SUM(–(FREQUENCY(IF(($C$2:$C$20=$F24)*($B$2:$B$20=G$23);MATCH($A$2:$A$20;$A$2:$A$20;0));ROW($A$2:$A$20)-ROW($A$2)+1)>0))

Sper să vă fie util și vă rog să consultați pentru mai multe explicații articolele din referințele de mai jos.

Resurse:

 

Referințe web:

2 comentarii

  1. Hey,

    Pentru a variabiliza ultimul rand din range, adica pentru a vedea numarul de inregistrari unice de la randul 1 pana ultimul rand non-blank dintr-o coloana, se poate folosi o variatiune de genul:

    {=SUM(IFERROR(1/COUNTIF(A1:INDEX(A:A;SUMPRODUCT(MAX(($A:$A””)*(ROW(A:A)))));A1:INDEX(A:A;SUMPRODUCT(MAX(($A:$A””)*(ROW(A:A))))));0))}

    Este fix functia prezentata de Valy dar in loc de A1:A20, range-ul este A1:A & LastUsedRow

    Sper sa va fie de folos.

    Cheers,
    Adrian Botoc

    Apreciat de 1 persoană

  2. Foarte fain si detaliat articolul. Multumim!
    Sper sa vina odata Dynamic Arrays and Functions ca pe cei cu O365 ii va scuti de multe ore de „gandeala” :).
    Un overview fain poate fi gasit aici: http://bit.ly/2OorwWA.

    Pana atunci putem simplifica totusi rezolvarea cu DISTINCTCOUNT si COUNTROWS in DAX.
    Aveti pe mail rezolvarea cu tabel pivot (Power Pivot) si 3 masuri simple in DAX.

    Apreciază

Comentariile sunt închise.