Questa domanda viene posta una volta ogni decennio: puoi fare una mediana in una tabella pivot. Tradizionalmente, la risposta era No. Ricordo nel 2000 quando assunsi l'MVP di Excel Juan Pablo Gonzalez per scrivere una fantastica macro che creava rapporti che sembravano tabelle pivot ma avevano mediane.
Quindi, quando Alex nel mio seminario Houston Power Excel mi ha chiesto di creare mediane, ho risposto subito "No". Ma poi … mi sono chiesto se DAX avesse una funzione mediana. Una rapida ricerca su Google e sì! Esiste una funzione DAX per Median.
Cosa serve per usare DAX in una tabella pivot? È necessaria la versione Windows di Excel con Excel 2013 o versioni successive.
Ecco il mio set di dati molto semplice che userò per testare come vengono calcolate le mediane della tabella pivot. Puoi vedere che la mediana per Chicago dovrebbe essere 5000 e la mediana per Cleveland dovrebbe essere 17000. Nel caso di Chicago, ci sono cinque valori, quindi la mediana sarà il terzo valore più alto. Ma per l'intero set di dati, ci sono 12 valori. Ciò significa che la mediana è compresa tra 11000 e 13000. Excel calcola la media di questi due valori per restituire 12000.
Per iniziare, seleziona una cella nei tuoi dati e premi Ctrl + T per formattare i dati come una tabella.
Quindi, scegli Inserisci, Tabella pivot. Nella finestra di dialogo Inserisci tabella pivot scegliere la casella Aggiungi questi dati al modello di dati.
Nei campi della tabella pivot, scegli Regione e distretto. Ma non scegliere le vendite. Invece, fai clic con il pulsante destro del mouse sull'intestazione della tabella e scegli Nuova misura. "Misura" è un nome di fantasia per un campo calcolato. Le misure sono più potenti dei campi calcolati nelle normali tabelle pivot.
Nella finestra di dialogo Definisci misura, compila le quattro voci mostrate di seguito:
- Nome misura: mediana delle vendite
- Formula
=MEDIAN((Sales)) - Formato numero: numero
- Posizioni decimali: 0
Dopo aver creato la misura, viene aggiunta all'elenco dei campi, ma è necessario scegliere la voce per aggiungerla all'area Valori della tabella pivot. Come puoi vedere di seguito, la tabella pivot calcola correttamente le mediane.
Guarda un video
Trascrizione del video
Impara Excel da Podcast, episodio 2197: Mediana in una tabella pivot.
Devo dirtelo, sono estremamente entusiasta di questo. quando ero a Houston per un seminario su Power Excel e Alex ha alzato la mano e ha detto: "Ehi, c'è un modo per fare una mediana in una tabella pivot?" No, non puoi fare una mediana in una tabella pivot. Ricordo che 25 anni fa, il mio buon amico, Juan Pablo Gonzales, in realtà portò una macro per fare l'equivalente della mediana, non usando una tabella pivot - semplicemente non è possibile.
Ma ho avuto una scintilla. Ho detto "Aspetta un secondo", apro un browser e cerco "DAX median" e, abbastanza sicuro, c'è una funzione MEDIAN in DAX, il che significa che possiamo risolvere questo problema.
Va bene, quindi, per utilizzare DAX, devi essere in Excel 2013 o Excel 2016 o in Excel 2010 e avere il componente aggiuntivo Power Pivot; non devi avere la scheda PowerPivot, dobbiamo solo avere il modello di dati. Tutto a posto? Quindi sceglierò questi dati, li trasformerò in una tabella con Ctrl + T, e gli daranno un nome privo di fantasia di "Tabella 4". Nel tuo caso, potrebbe essere "Tabella 1". E inseriremo una tabella pivot, aggiungeremo questi dati al modello di dati, fare clic su OK e sceglieremo Regionale sul lato sinistro, ma non Vendite. Invece, voglio creare una nuova misura calcolata. Quindi vengo qui al tavolo e faccio clic con il pulsante destro del mouse e dico: Aggiungi misura. E questa misura verrà chiamata "Mediana delle vendite" e la formula sarà: = MEDIANA. Premi la scheda lì e scegli Vendite,parentesi di chiusura. Posso sceglierlo come un numero con zero cifre decimali, utilizzare un separatore di mille e fare clic su OK. E, vediamo, il nostro nuovo campo viene aggiunto qui, dobbiamo spuntarlo per aggiungerlo e dice che la mediana per il Midwest è 12.000.
Ora controlliamo quello. Quindi, qui, tutto il Midwest, la mediana dell'intero set di dati tra 11.000 e 13.000. Quindi è in media 11 e 13, che è esattamente ciò che la mediana farebbe in Excel normale. Ora aggiungiamo il distretto e si dice che la mediana dei 5.000 di Chicago, la mediana di Cleveland è 17.000; Totale del Midwest e totale complessivo 12.000. Tutto ciò è corretto. Quanto è fantastico? Infine, mediana in una tabella pivot, grazie a un campo calcolato, o misura, come viene chiamato, e al modello di dati.
Ora, molti dei miei suggerimenti preferiti, i suggerimenti per il mio seminario live su Power Excel, in questo libro VIVE, The 54 Greatest Tips of All Time. Fai clic sulla "I" nell'angolo in alto a destra per leggere ulteriori informazioni sul libro.
Tutto a posto. Conclusione: puoi fare una mediana in una tabella pivot? Oh no, sì, sì puoi, grazie al modello di dati. Puoi creare una mediana; Ctrl + T per trasformare i tuoi dati in una tabella; Inserisci tabella pivot; e seleziona la casella Aggiungi questi dati al modello di dati; fare clic con il pulsante destro del mouse sul nome della tabella e scegliere una nuova misura, e la misura sarà = MEDIANA ((Vendite)). È meraviglioso.
Grazie ad Alex per essersi presentato al mio seminario e aver fatto questa domanda, grazie a te per essere passato. Ci vediamo la prossima volta per un altro netcast da.
Scarica il file Excel
Per scaricare il file excel: pivot-table-median.xlsx
