2 modi per calcolare il conteggio distinto con le tabelle pivot
Linea di fondo: Impara due modi per risolvere la sfida dell’analisi dei dati, calcolando il conteggio distinto, con le tabelle pivot.
Livello di competenza: Intermedio
Video Tutorial
Scaricare il file Excel
Ho incluso sia il file originale che quello della soluzione da scaricare qui:
Analisi dei dati Challenge.xlsx (16.1 KB)
Data Analysis Challenge – Pivot Table Solution FINAL.xlsx (118.4 KB)
Counting Unique Rows
In questo post, daremo uno sguardo a due modi diversi per fare un conteggio distinto usando le tabelle pivot. Questi due metodi sono stati presentati come soluzioni alla sfida di analisi dei dati che potete trovare qui:
Excel Data Analysis Challenge
Per riassumere la sfida, vogliamo creare un report riassuntivo del conteggio dei deal per fase, ma ci sono più righe per deal nei dati CRM. Quindi dobbiamo trovare un modo per creare un conteggio distinto (contando le righe uniche) per ogni affare in modo da poterle sommare.
A proposito, grazie a chiunque abbia presentato una soluzione alla sfida dei dati! Ci sono state molte proposte interessanti.
Soluzione #1 – Usare una colonna di aiuto
La cosa bella di questa soluzione è che può essere usata in qualsiasi versione di Excel.
Inizia trasformando i tuoi dati in una tabella di Excel. Per farlo, basta selezionare qualsiasi cella nel set di dati e cliccare su Formato come tabella nella scheda Home. Clicca con il tasto destro del mouse sul formato di tabella che vuoi e seleziona Applica e Cancella formattazione.
Premete OK quando appare la finestra Formato come tabella.
Ora che i vostri dati sono in formato tabella, aggiungete una colonna di aiuto a destra della tabella ed etichettatela Deal Count. Usate la funzione COUNTIF, dove l’intervallo è la colonna ID affare e il criterio è la cella della colonna ID affare che corrisponde alla riga in cui vi trovate.
La formula restituirà il numero di righe per ogni numero ID affare. Se dividiamo la formula nel numero 1, otterremo delle frazioni in ciascuna di quelle celle che, sommate, conteranno una voce per ogni affare.
Il cambiamento della formula può essere visto in verde qui:
=1/COUNTIF(,])
Ora che abbiamo queste frazioni che ci daranno un conteggio distinto quando creiamo la nostra tabella pivot, possiamo andare avanti e creare la tabella pivot scegliendo Tabella Pivot nella scheda Inserisci.
Per creare il nostro rapporto di riepilogo usando la nuova tabella pivot, mettiamo la Fase vendite nell’area Righe e il Conteggio delle vendite nell’area Somma dei valori.
Questo ci darà il rapporto riassuntivo che stiamo cercando, con il conteggio delle transazioni in ogni fase di vendita.
La cosa bella di usare una tabella pivot è che man mano che aggiungiamo o cancelliamo voci di dati sorgente, possiamo aggiornare la tabella pivot ( Alt + F5 ) per includere quei cambiamenti.
Soluzione # 2 – Usare Power Pivot
Questa soluzione è disponibile solo per le versioni di Excel 2013 o successive per Windows.
Vogliamo ancora i nostri dati formattati come una tabella di Excel, ma non abbiamo bisogno di una colonna di aiuto per questa soluzione.
Questa volta, quando creiamo la nostra tabella pivot, selezioniamo la casella che dice Aggiungi questa tabella al Modello dati. (Data Model è un altro termine per PowerPivot.)
Quando costruite la vostra tabella pivot questa volta, trascinerete Deal ID nell’area Sum of Values.
Questo inizialmente ci dà dei numeri che non vogliamo nel nostro rapporto riassuntivo. Per risolvere questo problema, vogliamo cliccare con il tasto destro del mouse sull’intestazione della colonna Sum of Deal ID e selezionare Value Field Settings. Questo aprirà una finestra dove possiamo scegliere Distinct Count come tipo di calcolo.
La funzione Distinct Count passa attraverso la colonna Deal ID e ci dà un conteggio dei valori unici, quindi il nostro report di riepilogo sarà proprio come nella Soluzione #1.
Confronto tra le due soluzioni
Entrambe queste soluzioni sono ottime perché possono essere aggiornate quando vengono aggiunti nuovi dati alla tabella sorgente.
Il vantaggio della soluzione #1 è che può essere fatta in qualsiasi versione di Excel. Detto questo, se state eseguendo la 2013 o successive in Windows, la soluzione #2 è l’opzione superiore. Questo perché la Soluzione #1 diventa strana quando si cerca di filtrare i dati verso il basso (ad esempio, per un certo prodotto) o di utilizzare gli affettatori per sezionare ulteriormente i dati.
Se volete saperne di più sull’uso delle tabelle pivot, ho un post separato che potete consultare qui: Introduzione alle tabelle pivot e ai cruscotti.
Altre soluzioni
C’erano molte altre ottime soluzioni alla sfida che sono state presentate. Esse includevano l’uso di Power Query e nuove funzioni dinamiche. Daremo un’occhiata a queste in post futuri, ma ho voluto iniziare con queste due perché erano più universali in termini di accesso alla versione di Excel.