Numărați valorile distincte în tabelul pivot Excel (Ghid pas cu pas ușor)

Tabelele pivot Excel sunt uimitoare (știu că menționez acest lucru de fiecare dată când scriu despre tabelele pivot, dar este adevărat).

Cu o înțelegere de bază și un pic de drag and drop, puteți obține o încărcătură de lucru în câteva secunde.

Deși multe se pot face cu câteva clicuri în tabelele pivot, există câteva lucruri care ar necesita câțiva pași suplimentari sau un pic de lucru.

Și un astfel de lucru este să numeri valori distincte într-un tabel pivot.

În acest tutorial, vă voi arăta cum să numărați valori distincte, precum și valori unice într-un tabel Excel pivot.

Dar înainte de a trece la modul de numărare a valorilor distincte, este important să înțelegem diferența dintre „număr distinct” și „număr unic”

Distinct Count Vs Unique Count

Deși acestea pot părea același lucru, nu este.

Mai jos este un exemplu în care există un set de date de nume și am enumerat nume unice și distincte separat.

Valori / nume unice sunt cele care apar doar o singură dată. Aceasta înseamnă că toate numele care se repetă și au duplicate nu sunt unice. Numele unice sunt listate în coloana C din setul de date de mai sus

Valori / nume distincte sunt cele care apar cel puțin o dată în setul de date. Așadar, dacă un nume apare de trei ori, este considerat în continuare ca un singur nume distinct. Acest lucru poate fi realizat prin eliminarea valorilor / denumirilor duplicate și păstrarea tuturor celor distincte. Numele distincte sunt listate în coloana B din setul de date de mai sus.

Pe baza a ceea ce am văzut, de cele mai multe ori când oamenii spun că vor să obțină un număr unic într-un tabel pivot, înseamnă de fapt un număr distinct, ceea ce acoper în acest tutorial.

Numărați valorile distincte în tabelul pivot Excel

Să presupunem că aveți datele despre vânzări așa cum se arată mai jos:

Faceți clic aici pentru a descărca fișierul de exemplu și a continua

Cu setul de date de mai sus, să presupunem că doriți să găsiți răspunsul la următoarele întrebări:

1. Câți reprezentanți de vânzări există în fiecare regiune (care nu este altceva decât numărul distinct de reprezentanți de vânzări din fiecare regiune)?
2. Câți reprezentanți de vânzări au vândut imprimanta în 2021-2022?

În timp ce tabelele pivot pot rezuma instantaneu datele cu câteva clicuri, pentru a obține numărul de valori distincte, va trebui să faceți încă câțiva pași.

Dacă folosiți Excel 2013 sau versiunile ulterioare, există o funcționalitate încorporată în Tabelul pivot care vă oferă rapid un număr distinct. Și dacă folosiți Excel 2010 sau versiunile anterioare, va trebui să modificați datele sursă adăugând o coloană de ajutor.

Următoarele două metode sunt acoperite în acest tutorial:

• Adăugarea unei coloane de ajutor în setul de date original pentru a număra valori unice (funcționează în toate versiunile).
• Adăugarea datelor la un model de date și utilizarea opțiunii Distinct Count (disponibilă în Excel 2013 și versiunile ulterioare).

Există o a treia metodă pe care Roger o arată în acest articol (pe care el o numește metoda Pivot tabelul pivot).

Să începem!

Adăugarea unei coloane de ajutor în setul de date

Notă: dacă utilizați Excel 2013 și versiuni superioare, săriți peste această metodă și treceți la următoarea (deoarece folosește o funcționalitate încorporată a tabelului pivot - Distinct Count).

Acesta este un mod ușor de a număra valori distincte în tabelul pivot, deoarece trebuie doar să adăugați o coloană de ajutor la datele sursă. După ce ați adăugat o coloană de ajutor, puteți utiliza acest nou set de date pentru a calcula numărul distinct.

Deși aceasta este o soluție ușoară, există câteva dezavantaje ale acestei metode (tratate mai târziu în acest tutorial).

Permiteți-mi să vă arăt mai întâi cum să adăugați o coloană de ajutor și să obțineți un număr distinct.

Să presupunem că am setul de date așa cum se arată mai jos:

Adăugați următoarea formulă în coloana F și aplicați-o pentru toate celulele care au date în coloanele adiacente.

= IF (COUNTIFS ($ C $ 2: C2, C2, $ B $ 2: B2, B2)> 1,0,1)

Formula de mai sus folosește funcția COUNTIFS pentru a număra de câte ori apare un nume în regiunea dată. De asemenea, rețineți că intervalul de criterii este de $ C $ 2: C2 și $ B $ 2: B2. Aceasta înseamnă că se extinde în timp ce coborâți în coloană.

De exemplu, în celula E2, intervalele de criterii sunt $ C $ 2: C2 și $ B $ 2: B2, iar în celula E3 aceste intervale se extind la $ C $ 2: C3 și $ B $ 2: B3.

Aceasta asigură faptul că funcția COUNTIFS numără prima instanță a unui nume ca 1, a doua instanță a numelui ca 2 și așa mai departe.

De vreme ce dorim doar să obținem nume distincte, se folosește funcția IF care returnează 1 când apare un nume pentru o regiune prima dată și returnează 0 când apare din nou. Acest lucru asigură faptul că se numără doar numele distincte și nu repetările.

Mai jos este cum ar arăta setul de date atunci când ați adăugat coloana de ajutor.

Acum, după ce am modificat datele sursă, îl putem folosi pentru a crea un tabel pivot și putem utiliza coloana de asistență pentru a obține numărul distinct al reprezentanților de vânzări din fiecare regiune.

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

1. Selectați orice celulă din setul de date.
2. Faceți clic pe fila Inserare.
3. Faceți clic pe Tabelul pivot (sau utilizați comanda rapidă de la tastatură - ALT + N + V)
4. În caseta de dialog Creare tabel pivot, asigurați-vă că tabelul / intervalul este corect (și include coloana de ajutor) și „Foaia de lucru nouă” în selectat.
5. Faceți clic pe OK.

Pașii de mai sus ar insera o foaie nouă care are tabelul pivot.

Trageți câmpul „Regiune” în zona Rânduri și câmpul „Numărare D” în zona Valori.

Veți obține un tabel pivot așa cum se arată mai jos:

Acum puteți schimba antetul coloanei din „Suma numărului D” în „Vânzător reprezentant”.

Dezavantaje ale utilizării unei coloane de ajutor:

Deși această metodă este destul de simplă, trebuie să subliniez câteva dezavantaje care vin odată cu modificarea datelor sursă într-un tabel pivot:

• Sursa de date cu coloana helper nu este la fel de dinamică ca un tabel pivot. Deși puteți tăia și tăia datele în orice fel doriți cu un tabel pivot, atunci când utilizați o coloană de ajutor, pierdeți o parte din acea abilitate. Să presupunem că adăugați o coloană de asistență pentru a obține numărul unui reprezentant de vânzări distinct în fiecare regiune. Acum, ce se întâmplă dacă doriți, de asemenea, să obțineți un număr distinct de reprezentanți de vânzări care vând imprimante. Va trebui să reveniți la datele sursă și să modificați formula coloanei de ajutor (sau să adăugați o nouă coloană de ajutor).
• Întrucât adăugați mai multe date la sursa tabelului pivot (care se adaugă și la memoria cache pivot), acest lucru poate duce la o dimensiune mai mare a fișierului Excel.
• Deoarece utilizăm o formulă Excel, poate face ca registrul dvs. de lucru Excel să încetinească în cazul în care aveți mii de rânduri de date.

Adăugați date la modelul de date și rezumați folosind Distinct Count

Tabelul pivot a adăugat o nouă funcționalitate în Excel 2013, care vă permite să obțineți un număr distinct în timp ce rezumați setul de date.

În cazul în care utilizați o versiune anterioară, nu veți putea folosi această metodă (așa cum ar trebui să încercați să adăugați coloana de ajutor așa cum se arată în metoda de mai sus).

Să presupunem că aveți un set de date așa cum se arată mai jos și doriți să obțineți numărul reprezentantului unic de vânzări din fiecare regiune.

Mai jos sunt pașii pentru a obține o valoare de numărare distinctă în tabelul pivot:

1. Selectați orice celulă din setul de date.
2. Faceți clic pe fila Inserare.
3. Faceți clic pe Tabelul pivot (sau utilizați comanda rapidă de la tastatură - ALT + N + V)
4. În caseta de dialog Creare tabel pivot, asigurați-vă că tabelul / intervalul este corect și Foaia de lucru nouă în selectat.
5. Bifați caseta care scrie - „Adăugați aceste date la modelul de date”
6. Faceți clic pe OK.

Pașii de mai sus ar insera o nouă foaie care are noul tabel pivot.

Trageți regiunea în zona Rânduri și reprezentantul de vânzări în zona Valori. Veți primi un tabel pivot așa cum se arată mai jos:

Tabelul pivot de mai sus oferă numărul total al reprezentanților de vânzări în fiecare regiune (și nu numărul distinct).

Pentru a obține numărul distinct din Tabelul pivot, urmați pașii de mai jos:

1. Faceți clic dreapta pe orice celulă din coloana „Număr reprezentanți vânzări”.
2. Faceți clic pe Setări câmp valoric
3. În caseta de dialog Setări câmp valoric, selectați „Număr distinct” ca tip de calcul (poate fi necesar să derulați lista în jos pentru a o găsi).
4. Faceți clic pe OK.

Veți observa că numele coloanei se schimbă din „Număr de reprezentanți de vânzări” în „Număr distinct de reprezentant de vânzări”. Puteți să o schimbați în orice doriți.

Câteva lucruri pe care le știți când adăugați datele dvs. la Modelul de date:

• Dacă vă salvați datele în modelul de date și apoi deschideți într-o versiune mai veche de Excel, acesta vă va afișa un avertisment - „Unele funcții ale tabelului pivot nu vor fi salvate”. Este posibil să nu vedeți numărul distinct (și modelul de date) atunci când este deschis într-o versiune mai veche care nu o acceptă.
• Când adăugați datele la un model de date și creați un tabel pivot, acesta nu va afișa opțiunile pentru a adăuga câmpuri calculate și coloane calculate.

Faceți clic aici pentru a descărca fișierul de exemplu

Ce se întâmplă dacă doriți să numărați valori unice (și nu valori distincte)?

Dacă doriți să numărați valori unice, nu aveți nicio funcționalitate încorporată în tabelul pivot și va trebui să vă bazați doar pe coloanele de ajutor.

Rețineți - valorile unice și valorile distincte nu sunt aceleași. Faceți clic aici pentru a afla diferența.

Un exemplu ar putea fi atunci când aveți setul de date de mai jos și doriți să aflați câți reprezentanți de vânzări sunt unici pentru fiecare regiune. Aceasta înseamnă că operează numai într-o anumită regiune și nu în celelalte.

În astfel de cazuri, trebuie să creați una dintre mai multe coloane de ajutor.

Pentru acest caz, formula de mai jos face truc:

= IF (IF (COUNTIFS ($ C $ 2: $ C $ 1001, C2, $ B $ 2: $ B $ 1001, B2) / COUNTIF ($ C $ 2: $ C $ 1001, C2) 1,0,1), 0)

Formula de mai sus verifică dacă un nume de reprezentant de vânzări apare doar într-o singură regiune sau în mai multe regiuni. Face acest lucru numărând numărul apariției unui nume într-o regiune și împărțindu-l la numărul total de apariții ale numelui. Dacă valoarea este mai mică de 1, indică faptul că numele apare în două sau mai mult de două regiuni.

În cazul în care numele apare în mai multe regiuni, returnează un alt 0 și returnează unul.

Formula verifică, de asemenea, dacă numele este repetat în aceeași regiune sau nu. Dacă numele se repetă, numai prima instanță a numelui returnează valoarea 1 și toate celelalte instanțe returnează 0.

Acest lucru poate părea puțin complex, dar depinde din nou de ceea ce încercați să realizați.

Deci, dacă doriți să numărați valori unice într-un tabel pivot, utilizați coloane de ajutor și dacă doriți să numărați valori distincte, puteți utiliza funcționalitatea încorporată (în Excel 2013 și mai sus) sau puteți utiliza o coloană de ajutor.

Faceți clic aici pentru a descărca fișierul de exemplu

Vă pot plăcea, de asemenea, următoarele tutoriale din tabelul pivot:

• Cum se filtrează datele într-un tabel pivot în Excel
• Cum să grupați datele în tabelele pivot în Excel
• Cum să grupați numerele în tabelul pivot în Excel
• Cum se aplică formatarea condiționată într-un tabel pivot în Excel
• Slicers în tabelul pivot Excel
• Cum să reîmprospătați tabelul pivot în Excel
• Ștergeți un tabel pivot în Excel