Come contare elementi unici (count distinct) in una tabella pivot Excel. Tre metodi, per diverse versioni di Excel. Ottieni la cartella di lavoro gratuita per seguirci
Grazie a Roger Govier, che ha creato questo tutorial e la cartella di lavoro.
Conteggio degli unici
Se stai usando una tabella pivot per riassumere i tuoi dati, potresti avere domande come:
- Quanti clienti unici (distinti) hanno fatto acquisti in ogni regione?
- Quanti prodotti unici sono stati venduti in ogni negozio?
- Quanti venditori unici hanno venduto ogni prodotto per regione?
Non c’è una funzione integrata di “conteggio unico” in una normale tabella pivot, ma in questo tutorial, vedrete come ottenere un conteggio unico degli elementi in una tabella pivot.
- In Excel 2013 e versioni successive, creare un modello di dati
- In Excel 2010, e versioni successive, utilizzare una tecnica che “Pivot la tabella pivot”.
- Oppure, nelle versioni precedenti, aggiungete una nuova colonna ai dati di origine, e usate CountIf.
- Se l’add-in Power Pivot è installato, usatelo per mostrare un conteggio distinto
Questo video mostra i passi per mostrare un conteggio distinto con il Modello di dati.
Introduzione
Spesso abbiamo grandi insiemi di dati che vogliamo analizzare con l’aiuto di una Tabella Pivot, ma vogliamo solo contare le occorrenze uniche di alcuni dati. Sfortunatamente, le Tabelle Pivot non hanno mai avuto la possibilità di contare i valori unici.
In questo esempio, il file di esempio ha 4999 record che mostrano le vendite dei prodotti, con la regione e il nome del venditore. I primi record sono mostrati nello screenshot qui sotto. Potete scaricare il file di esempio al link qui sotto.
Creare una tabella pivot dai dati ci darebbe qualcosa di simile al seguente, se aggiungessimo Persona all’area Valori della tabella pivot e la riassumessimo con Conteggio.
Tuttavia, questo ci sta dando un conteggio di tutte le transazioni, non il conteggio del numero unico di persone che hanno fatto quelle transazioni all’interno di ogni Regione.
Ottieni un conteggio unico
Ci sono degli espedienti che puoi usare per ottenere un conteggio unico:
- Metodo 1: In Excel 2013 e successivi, aggiungi i dati sorgente della tabella pivot al Modello dati, e un conteggio unico può essere fatto facilmente.
- Metodo 2: In Excel 2010 e successivi, utilizzare la tecnica “pivot a pivot”.
- Metodo 3: Nelle vecchie versioni di Excel, aggiungere una colonna ai dati sorgente per mettere un 1 nelle celle di una riga dove il valore è unico, e uno 0 in tutte le altre celle. Quindi, sommando questa colonna aggiuntiva si ottiene il valore unico.
- Metodo 4: Utilizzare PowerPivot per creare la tabella pivot e utilizzare le sue funzioni per creare un conteggio unico. Vedere i dettagli qui sotto.
Tutti e quattro i metodi sono descritti di seguito, insieme a un confronto delle prestazioni tra i metodi 2 e 3.
1. Aggiungi al modello di dati – Excel 2013 e successivi
In Excel 2013, se si aggiungono i dati sorgente di una tabella pivot al modello di dati della cartella di lavoro, è facile creare un conteggio unico.
NOTA: Questa tecnica crea una tabella pivot basata su OLAP, che ha alcune limitazioni, come ad esempio nessun raggruppamento e nessun campo calcolato o elemento calcolato. Se avete bisogno di queste caratteristiche limitate, provate invece il metodo “pivot a pivot”.
Creare un conteggio unico (Distinct Count)
Per creare la tabella pivot, seguite questi passi:
- Selezionate una cella nella tabella dei dati sorgente.
- In fondo alla finestra di dialogo Crea tabella pivot, aggiungi un segno di spunta a “Aggiungi questi dati al modello dati”
- Clicca OK
Per impostare il layout della tabella pivot, segui questi passi:
- Nella tabella pivot, aggiungere Region all’area Row.
- Aggiungi questi 3 campi all’area Valori — Persona, Unità, Valore
- Il campo Persona contiene del testo, quindi è predefinito a Conteggio delle persone. Il conteggio mostra il numero totale di transazioni in ogni regione, non un conteggio unico di persone
Per ottenere un conteggio unico di persone in ogni regione, seguire questi passi:
- Fare clic destro su uno dei valori nel campo Persona
- Fare clic su Impostazioni campo valore
- Nella lista Riassumi campo valore per, scorrere fino in fondo, e clicca su Conteggio distinto, poi clicca su OK
Il campo Persona cambia, e invece di mostrare il conteggio totale delle transazioni, mostra un conteggio distinto dei nomi dei venditori.
2. Pivot la Pivot – Excel 2010
Per ottenere un conteggio unico dei venditori per regione in Excel 2010, possiamo produrre un Rapporto Pivot, e poi “Pivot la Pivot” per ottenere il nostro risultato finale.
Creare la prima Tabella Pivot
- Creare una Tabella Pivot da questi dati, con Regione e Persona nell’area Righe
- Aggiungere Unità e Valore nell’area Valori. Poiché Persona è un campo di testo, la tabella pivot lo mostrerà automaticamente come “Conta di”.
- Formattare la tabella pivot con il layout del report Tabular
- Impostare tutte le etichette degli elementi da ripetere in ogni riga.
- Cambiare le didascalie, per rimuovere la “Somma di”
TIP: Digitare il nome originale del campo, con uno spazio alla fine, per esempio “unità”
Questa ora sembra una normale tabella di dati, ma è un riassunto della tabella dati originale completa con 4.999 righe.
Creare un intervallo con nome per la cella iniziale
Prossimo, daremo un nome alla prima cella della tabella pivot.
- Selezioniamo la cella B2, che è l’intestazione della colonna Regione della tabella pivot
- Facciamo clic nella barra della formula e digitiamo un nome per la cella — pvtStart
- Premiamo Invio, per completare il nome
Creiamo un intervallo con nome per la riga finale
Il prossimo passo è calcolare la posizione dell’ultima riga della tabella pivot. Useremo la funzione MATCH, con un numero molto grande, per trovare l’ultimo numero nella colonna D, e ottenere il suo numero di riga. Questa formula funziona solo in una colonna con numeri.
- Sul foglio Dati, nella cella M2, inserite questa formula, per calcolare l’ultima riga nella colonna Unità della tabella pivot:
=MATCH(9.9E+307,Method_1!D:D,1)
- Per dare un nome a quella cella, selezionate la cella M2, e nella casella Nome, digitate PvtEnd, e premete Invio.
Creare un intervallo dinamico con nome
In seguito, creeremo un intervallo dinamico con nome, per fare riferimento a questa nuova tabella. Questo intervallo inizierà nella cella chiamata pvtstart (la cella di intestazione della regione), e finirà nell’ultima riga di dati nella colonna E (l’intervallo nominato, PvtEnd).
- Nella scheda Formule della barra multifunzione, cliccate su Definisci nome
- Nella finestra di dialogo Nuovo nome, inserite il nome, myData
- Nel menu a tendina Ambito, selezionate Cartella di lavoro
- Nella casella Riferimenti a, digitate questa formula:
=pvtStart:INDEX(Metodo_1!$E:$E, PvtEnd) - Clicca OK, per creare l’intervallo denominato
Creare la seconda tabella pivot
Infine, creare una seconda tabella pivot, basata sull’intervallo dinamico, myData.
Mettete Regione nell’area della riga, e Persona, Unità e Valore nell’area dei Valori
Questo mostra il numero di venditori unici per regione come mostrato qui sotto. Per esempio, nella prima tabella pivot, la regione Est ha mostrato 8 nomi unici nella colonna persona, e questo è il conteggio in questa tabella pivot.
Rinfresca le tabelle pivot
Naturalmente, dato che ci sono 2 tabelle pivot coinvolte in questa soluzione, entrambe devono essere aggiornate dopo che qualsiasi dato è stato aggiunto o cambiato nella tabella sorgente. È essenziale che la prima PT sia aggiornata per prima, seguita da un aggiornamento della tabella pivot finale.
È possibile aggiornare le tabelle pivot manualmente, o usare una macro. Per usare una macro, aggiungete una delle seguenti procedure al foglio con la vostra tabella pivot finale.
A) Se entrambe le tabelle pivot sono sullo stesso foglio, usate questo codice:
Private Sub Worksheet_Activate() Me.PivotTables(1).PivotCache.Refresh Me.PivotTables(2).PivotCache.Refresh End Sub
B) Se le tabelle pivot sono su fogli diversi, usate questo codice (sostituite il nome del vostro foglio attuale con “yourheetname”):
Private Sub Worksheet_Activate() Sheets("yoursheetname").PivotTables(1).PivotCache.Refresh Me.PivotTables(1).PivotCache.Refresh End Sub
(Opzionale) Dare un nome all’intervallo di dati della tabella pivot
Se state usando delle macro per aggiornare le tabelle pivot, potreste anche usare una macro per resettare l’intervallo chiamato myData dopo ogni aggiornamento. Aggiungete la seguente procedura alla vostra cartella di lavoro, quindi chiamate questa procedura SetmyData nelle procedure Worksheet_Activate.
Sub SetmyData()'Change "PT_A" to whatever name'you have given to your first Pivot Table'that is to be used as the source'for for the final Pivot Table'Also change the sheet name'from "Method_1" to'your sheet name if it is differentDim PTName As StringDim ShName As StringPTName = "PT_A"ShName = "Method_1"ThisWorkbook.Names.Add _ Name:="myData", _ RefersTo:=Sheets(ShName) _ .PivotTables(PTName) _ .TableRange1End Sub
3. Utilizzare COUNTIF — Excel 2007 e precedenti
Nelle versioni precedenti di Excel, le tabelle pivot non hanno la possibilità di ripetere le etichette delle righe. Per ottenere un conteggio unico dei venditori per regione, puoi aggiungere una nuova colonna nei dati di origine e inserire una formula COUNTIF
Puoi scaricare il file di esempio al link qui sotto.
Aggiungi la formula COUNTIF
Seguite questi passi per aggiungere la formula:
Nei dati sorgente, aggiungete una nuova colonna — “Unico” — nella cella J1
Nella cella J2, inserite questa formula, e copiatela fino all’ultima riga di dati:
=IF(COUNTIF($E$5:E5,E5)>1,0,1)
Il primo riferimento nell’intervallo ($E$5) è assoluto, e il secondo è relativo (E5), quindi mentre copiamo la formula lungo la pagina, l’intervallo si espanderà da $E$5:E5 a $E$5:E6, poi $E$5:E7 e così via.
In ogni riga stiamo controllando quante volte il valore nella colonna E (Persona) si è verificato, fino alla riga corrente. Se è maggiore di una volta, il risultato è zero, quindi quella persona non viene contata di nuovo. Questo ci permette di vedere il numero di volte uniche all’interno del dataset che il nome di ogni Persona è apparso.
Con l’aggiunta della colonna extra, i dati avrebbero questo aspetto. La seconda istanza di “Harry” restituisce uno zero nella colonna Unique.
NOTA: Se ci sono persone con lo stesso nome che vendono in diverse Regioni:
- In Excel 2007 o successivo: Usare COUNTIFS, per controllare più criteri. Per esempio, =IF(COUNTIFS($D$5:$D5,D5,D5,$E$5:E5,E5)=1,1,0)
- In Excel 2003 o precedente, concatenare i nomi di Regione e Persona in una nuova colonna, e poi usare la formula COUNTIF per trovare valori unici in quella colonna concatenata.
Creare una tabella pivot
Per vedere i conteggi unici in una tabella pivot:
- Creare una tabella pivot da questi dati, con Regione nell’area Righe
- Aggiungere Unico, Unità e Valore nell’area Valori.
- Cambiare l’intestazione “Unico” in “Conteggio delle persone” o “Persona”
Possiamo vedere che ci sono 30 persone che hanno effettuato le vendite, e il numero di ciascuna che si trova in ogni Regione, e se scegliessimo di espandere qualsiasi Regione allora vedremmo i totali individuali per ogni Persona.
Confronto delle prestazioni
Se possibile, usate il metodo “pivot a pivot”, che è molto più veloce.
Metodo COUNTIF
Utilizzare una formula COUNTIF nei dati sorgente funziona, e ci dà il risultato desiderato, e su un set di dati relativamente piccolo il metodo è accettabile. Ma è molto costoso in termini di tempo di elaborazione, e su serie di dati molto grandi può essere estremamente lento.
Utilizzando “FastExcel” di Charles Williams per calcolare il tempo di calcolo della cartella di lavoro si ottiene il risultato mostrato qui sotto, dove il tempo impiegato per ricalcolare il foglio Dati è di 224 millisecondi.
Metodo Pivot a Pivot
Anche il metodo “Pivot a Pivot” produce il risultato corretto, con un conteggio unico di Persona, come richiesto. Inoltre, non si basa su colonne aggiuntive nei dati di origine, né sull’inserimento di nuove formule.
La cartella di lavoro risultante è più piccola, e sempre usando “FastExcel” di Charles Williams per calcolare il tempo di calcolo del foglio Dati è un incredibilmente veloce 1.2 millisecondi
Con piccoli insiemi di dati i tempi di ricalcolo possono non essere critici, ma quando si ha a che fare con insiemi di dati più grandi di 300.000 – 400.000 righe, allora queste differenze diventano molto rilevanti.
Power Pivot
Se avete installato l’add-in PowerPivot, potete usarlo per mostrare un conteggio distinto per un campo. Questo video mostra i passi per creare una tabella pivot Power Pivot e aggiungere un campo con il conteggio unico.
Per le istruzioni scritte, vedi i passi di Excel 2013 sul mio blog sulla tabella pivot. Ci sono istruzioni anche per Excel 2010.
File di esempio: per seguire il video, scarica il file di esempio usato per questo video
Ottieni i file di esempio
- Modello di dati: Scarica la cartella di lavoro campione Data Model, che ha l’esempio per il Metodo 1 (Data Model). Il file è zippato e in formato xlsx. Il file non contiene macro.
- Nessun modello di dati: Scaricare la cartella di lavoro di esempio non-Data Model, che contiene esempi per il Metodo 2 (pivot a pivot) e il Metodo 3 (colonna nei dati sorgente). Il file è zippato e in formato xlsb. Il file non contiene macro.
- Video Power Pivot: Per seguire il video di Power Pivot nel Metodo 4, scarica il file di esempio che è stato usato per il video
Tutorial correlati
Nominare gli intervalli
Conta unico in PowerPivot
File di esempio – Roger Govier
Chi è lo sviluppatore
Roger Govier è un MVP di Excel con sede nel Regno Unito che svolge incarichi in Excel e VBA per clienti di tutto il mondo. Anche se gli piace la sfida intellettuale di risolvere i problemi con le funzioni del foglio di lavoro, Roger sostiene di essere intrinsecamente pigro, quindi cerca sempre un modo veloce e semplice per fornire soluzioni solide e praticabili.
Trova altri tutorial e file di esempio di Roger qui: Sample Files – Roger Govier
Puoi contattare Roger a: [email protected]
Roger Govier
