Le tabelle pivot di Excel sono fantastiche (so che lo menziono ogni volta che scrivo sulle tabelle pivot, ma è vero).
Con una conoscenza di base e un po' di trascinamento della selezione, puoi portare a termine un carico di lavoro in pochi secondi.
Sebbene si possa fare molto con pochi clic nelle tabelle pivot, ci sono alcune cose che richiedono alcuni passaggi aggiuntivi o un po' di lavoro.
E una cosa del genere è contare valori distinti in una tabella pivot.
In questo tutorial, ti mostrerò come contare valori distinti e valori univoci in una tabella pivot di Excel.
Ma prima di passare a come contare i valori distinti, è importante capire la differenza tra "conteggio distinto" e "conteggio unico"
Conteggio distinto Vs conteggio unico
Anche se possono sembrare la stessa cosa, non è.
Di seguito è riportato un esempio in cui è presente un set di dati di nomi e ho elencato separatamente nomi univoci e distinti.
Valori/nomi unici sono quelli che si verificano una volta sola. Ciò significa che tutti i nomi che si ripetono e hanno duplicati non sono univoci. I nomi univoci sono elencati nella colonna C nel set di dati sopra
Valori/nomi distinti sono quelli che si verificano almeno una volta nel set di dati. Quindi, se un nome appare tre volte, viene comunque conteggiato come un nome distinto. Ciò può essere ottenuto rimuovendo i valori/nomi duplicati e mantenendo tutti quelli distinti. I nomi distinti sono elencati nella colonna B del set di dati sopra.
Sulla base di ciò che ho visto, la maggior parte delle volte quando le persone dicono che vogliono ottenere il conteggio univoco in una tabella pivot, in realtà significano conteggio distinto, che è ciò che sto trattando in questo tutorial.Conta valori distinti nella tabella pivot di Excel
Supponiamo di avere i dati di vendita come mostrato di seguito:
Fare clic qui per scaricare il file di esempio e seguire
Con il set di dati sopra, supponiamo che tu voglia trovare la risposta alle seguenti domande:
- Quanti rappresentanti di vendita ci sono in ciascuna regione (che non è altro che il conteggio distinto dei rappresentanti di vendita in ciascuna regione)?
- Quanti rappresentanti di vendita hanno venduto la stampante nel 2021-2022?
Sebbene le tabelle pivot possano riepilogare istantaneamente i dati con pochi clic, per ottenere il conteggio dei valori distinti, sarà necessario eseguire alcuni passaggi in più.
Se stai usando Excel 2013 o versioni successive, c'è una funzionalità integrata nella tabella pivot che ti dà rapidamente il conteggio distinto. E se stai usando Excel 2010 o versioni precedenti, dovrai modificare i dati di origine aggiungendo una colonna di supporto.I seguenti due metodi sono trattati in questo tutorial:
- Aggiunta di una colonna di supporto nel set di dati originale per contare i valori univoci (funziona in tutte le versioni).
- Aggiunta dei dati a un modello di dati e utilizzo dell'opzione Conteggio distinto (disponibile in Excel 2013 e versioni successive).
C'è un terzo metodo che Roger mostra in questo articolo (che chiama il metodo Pivot the Pivot Table).
Iniziamo!
Aggiunta di una colonna di supporto nel set di dati
Nota: se stai utilizzando Excel 2013 e versioni successive, salta questo metodo e passa a quello successivo (poiché utilizza una funzionalità di tabella pivot incorporata - Conteggio distinto).Questo è un modo semplice per contare valori distinti nella tabella pivot poiché è sufficiente aggiungere una colonna di supporto ai dati di origine. Dopo aver aggiunto una colonna di supporto, puoi utilizzare questo nuovo set di dati per calcolare il conteggio distinto.
Sebbene questa sia una soluzione semplice, ci sono alcuni inconvenienti in questo metodo (trattati più avanti in questo tutorial).
Lascia che ti mostri prima come aggiungere una colonna di supporto e ottenere un conteggio distinto.
Supponiamo di avere il set di dati come mostrato di seguito:
Aggiungi la seguente formula nella colonna F e applicala per tutte le celle che contengono dati nelle colonne adiacenti.
=SE(CONTA.SE($C$2:C2,C2,$B$2:B2,B2)>1,0,1)
La formula sopra utilizza la funzione CONTA.SE per contare il numero di volte in cui un nome appare nella regione specificata. Inoltre, tieni presente che l'intervallo di criteri è $C$2:C2 e $B$2:B2. Ciò significa che continua ad espandersi mentre scendi nella colonna.
Ad esempio, nella cella E2, gli intervalli di criteri sono $C$2:C2 e $B$2:B2 e nella cella E3 questi intervalli si espandono in $C$2:C3 e $B$2:B3.
Ciò garantisce che la funzione CONTA.SE conti la prima istanza di un nome come 1, la seconda istanza del nome come 2 e così via.
Poiché vogliamo solo ottenere i nomi distinti, viene utilizzata la funzione IF che restituisce 1 quando viene visualizzato un nome per una regione la prima volta e restituisce 0 quando appare di nuovo. Questo assicura che vengano conteggiati solo i nomi distinti e non le ripetizioni.
Di seguito è riportato come apparirebbe il tuo set di dati dopo aver aggiunto la colonna helper.
Ora che abbiamo modificato i dati di origine, possiamo utilizzarli per creare una tabella pivot e utilizzare la colonna helper per ottenere il conteggio distinto del rappresentante di vendita in ciascuna regione.
Di seguito sono riportati i passaggi per eseguire questa operazione:
- Seleziona una cella nel set di dati.
- Fare clic sulla scheda Inserisci.
- Fare clic su Tabella pivot (o utilizzare la scorciatoia da tastiera - ALT + N + V)
- Nella finestra di dialogo Crea tabella pivot, assicurati che la tabella/intervallo sia corretta (e includa la colonna helper) e "Nuovo foglio di lavoro" sia selezionato.
- Fare clic su OK.
I passaggi precedenti inserirebbero un nuovo foglio con la tabella pivot.
Trascina il campo "Regione" nell'area Righe e il campo "Conteggio D" nell'area Valori.
Otterrai una tabella pivot come mostrato di seguito:
Ora puoi modificare l'intestazione della colonna da "Somma del conteggio D" a "Rappresentante di vendita".
Svantaggi dell'utilizzo di una colonna di supporto:
Sebbene questo metodo sia piuttosto semplice, devo evidenziare alcuni inconvenienti che derivano dalla modifica dei dati di origine in una tabella pivot:
- L'origine dati con la colonna helper non è dinamica come una tabella pivot. Mentre puoi suddividere e tagliare i dati nel modo che preferisci con una tabella pivot, quando usi una colonna helper, perdi una parte di quell'abilità. Supponiamo di aggiungere una colonna di supporto per ottenere il conteggio di un rappresentante di vendita distinto in ciascuna regione. Ora, cosa succede se si desidera ottenere anche il conteggio distinto delle stampanti che vendono rappresentanti di vendita. Dovrai tornare ai dati di origine e modificare la formula della colonna di supporto (o aggiungere una nuova colonna di supporto).
- Poiché stai aggiungendo più dati all'origine della tabella pivot (che viene aggiunta anche alla cache pivot), ciò può portare a una dimensione maggiore del file Excel.
- Poiché stiamo utilizzando una formula di Excel, potrebbe rallentare la tua cartella di lavoro di Excel nel caso in cui tu abbia migliaia di righe di dati.
Aggiungi dati al modello di dati e riepiloga utilizzando il conteggio distinto
La tabella pivot ha aggiunto una nuova funzionalità in Excel 2013 che consente di ottenere il conteggio distinto durante il riepilogo del set di dati.
Nel caso in cui tu stia utilizzando una versione precedente, non sarai in grado di utilizzare questo metodo (come dovresti provare ad aggiungere la colonna helper come mostrato nel metodo sopra questo).
Supponiamo di avere un set di dati come mostrato di seguito e di voler ottenere il conteggio del rappresentante di vendita unico in ciascuna regione.
Di seguito sono riportati i passaggi per ottenere un valore di conteggio distinto nella tabella pivot:
- Seleziona una cella nel set di dati.
- Fare clic sulla scheda Inserisci.
- Fare clic su Tabella pivot (o utilizzare la scorciatoia da tastiera - ALT + N + V)
- Nella finestra di dialogo Crea tabella pivot, assicurati che la tabella/intervallo sia corretta e che Nuovo foglio di lavoro sia selezionato.
- Seleziona la casella che dice - "Aggiungi questi dati al modello di dati"
- Fare clic su OK.
I passaggi precedenti inserirebbero un nuovo foglio con la nuova tabella pivot.
Trascina la regione nell'area Righe e il rappresentante di vendita nell'area Valori. Otterrai una tabella pivot come mostrato di seguito:
La tabella pivot precedente fornisce il conteggio totale del rappresentante di vendita in ciascuna regione (e non il conteggio distinto).
Per ottenere il conteggio distinto nella tabella pivot, seguire i passaggi seguenti:
- Fare clic con il pulsante destro del mouse su qualsiasi cella nella colonna "Conteggio dei rappresentanti di vendita".
- Fare clic su Impostazioni campo valore
- Nella finestra di dialogo Impostazioni campo valore, seleziona "Conteggio distinto" come tipo di calcolo (potrebbe essere necessario scorrere l'elenco per trovarlo).
- Fare clic su OK.
Noterai che il nome della colonna cambia da "Conteggio rappresentante di vendita" a "Conteggio distinto di rappresentante di vendita". Puoi cambiarlo in quello che vuoi.
Alcune cose che sai quando aggiungi i tuoi dati al modello di dati:
- Se salvi i tuoi dati nel modello di dati e poi li apri in una versione precedente di Excel, ti verrà mostrato un avviso: "Alcune funzioni della tabella pivot non verranno salvate". Potresti non vedere il conteggio distinto (e il modello di dati) quando viene aperto in una versione precedente che non lo supporta.
- Quando aggiungi i tuoi dati a un modello di dati e crei una tabella pivot, non mostrerà le opzioni per aggiungere campi calcolati e colonne calcolate.
Clicca qui per scaricare il file di esempio
Cosa succede se si desidera contare i valori univoci (e non i valori distinti)?
Se vuoi contare valori univoci, non hai alcuna funzionalità integrata nella tabella pivot e dovrai fare affidamento solo sulle colonne di supporto.
Ricorda: i valori univoci e i valori distinti non sono gli stessi. Clicca qui per conoscere la differenza.Un esempio potrebbe essere quando si dispone del set di dati di seguito e si desidera scoprire quanti rappresentanti di vendita sono unici per ciascuna regione. Ciò significa che operano solo in una regione specifica e non nelle altre.
In tali casi, è necessario creare una o più colonne di supporto.
In questo caso, la formula seguente fa il trucco:
=SE(SE(CONT.SE($C$2:$C$1001,C2,$B$2:$B$1001,B2)/CONT.SE($C$2:$C$1001,C2)1,0,1),0)
La formula precedente controlla se il nome di un rappresentante di vendita è presente solo in una regione o in più di una regione. Lo fa contando il numero di occorrenze di un nome in una regione e dividendolo per il numero totale di occorrenze del nome. Se il valore è minore di 1, indica che il nome è presente in due o più di due regioni.
Nel caso in cui il nome sia presente in più di una regione, restituisce uno 0 altrimenti restituisce uno.
La formula controlla anche se il nome è ripetuto nella stessa regione o meno. Se il nome viene ripetuto, solo la prima istanza del nome restituisce il valore 1 e tutte le altre istanze restituiscono 0.
Questo può sembrare un po' complesso, ma dipende ancora da cosa stai cercando di ottenere.
Pertanto, se si desidera contare valori univoci in una tabella pivot, utilizzare le colonne di supporto e se si desidera contare valori distinti, è possibile utilizzare la funzionalità integrata (in Excel 2013 e versioni successive) o utilizzare una colonna di supporto.
Clicca qui per scaricare il file di esempio
Potrebbero piacerti anche i seguenti tutorial sulla tabella pivot:
- Come filtrare i dati in una tabella pivot in Excel
- Come raggruppare le date nelle tabelle pivot in Excel
- Come raggruppare i numeri nella tabella pivot in Excel
- Come applicare la formattazione condizionale in una tabella pivot in Excel
- Affettatrici nella tabella pivot di Excel
- Come aggiornare la tabella pivot in Excel
- Elimina una tabella pivot in Excel
