Il filtro Top 10 della tabella pivot fornisce un totale delle righe visibili
Le tabelle pivot offrono un filtro Top 10. È fresco. È flessibile. Ma lo odio e ti dirò perché.
Ecco una tabella pivot che mostra le entrate per cliente. Il totale delle entrate è di 6,7 milioni di dollari.
E se il mio manager ha la capacità di attenzione di un pesce rosso e vuole vedere solo i primi cinque clienti?
Per iniziare, apri il menu a discesa in A3 e seleziona Value Filters, Top 10.
La finestra di dialogo Filtro Top 10 super flessibile consente Top / Bottom. Può fare 10, 5 o qualsiasi altro numero. Puoi chiedere i primi cinque articoli, il miglior 80% o un numero sufficiente di clienti per arrivare a $ 5 milioni.
Ma ecco il problema: il report risultante mostra cinque clienti e il totale di quei clienti invece dei totali di tutti.
Ma prima, alcune parole importanti sul filtro automatico
Mi rendo conto che questa sembra una domanda fuori dal comune. Se vuoi attivare i menu a discesa Filtro su un normale set di dati, come fai? Ecco tre modi davvero comuni:
- Seleziona una cella nei dati e fai clic sull'icona Filtro nella scheda Dati.
- Seleziona tutti i tuoi dati con Ctrl + * e fai clic sull'icona Filtro nella scheda Dati.
- Premi Ctrl + T per formattare i dati come una tabella.
Questi sono tre modi davvero buoni. Finché conosci qualcuno di loro, non c'è assolutamente bisogno di conoscere un altro modo. Ma ecco un modo incredibilmente oscuro ma magico per attivare il filtro:
- Vai alla tua riga di intestazioni, vai alla cella di intestazione più a destra. Sposta una cella a destra. Per qualche motivo sconosciuto, quando ti trovi in questa cella e fai clic sull'icona Filtro, Excel filtra il set di dati alla tua sinistra. Non ho idea del perché funzioni. Non vale davvero la pena parlarne perché ci sono già tre ottimi modi per attivare i menu a discesa Filtro. Chiamo questa cella la cella magica.
E ora, torniamo alle tabelle pivot …
Quindi, c'è una regola che dice che non puoi usare i filtri automatici quando sei in una tabella pivot. Vedi sotto? L'icona Filtro è disattivata perché ho selezionato una cella nella tabella pivot.
Non ho mai veramente considerato il motivo per cui Microsoft lo rende grigio. Deve essere qualcosa di interno che dice che il filtro automatico e una tabella pivot non possono coesistere. Quindi, c'è qualcuno nel team di Excel che ha il compito di rendere grigia l'icona del filtro. Quella persona non ha mai sentito parlare della cellula magica. Seleziona una cella nella tabella pivot e il filtro diventa grigio. Fare clic all'esterno della tabella pivot e il filtro viene nuovamente abilitato.
Ma aspetta. E la cella magica di cui ti ho appena parlato? Se fai clic nella cella a destra dell'ultima intestazione, Excel dimentica di disattivare l'icona del filtro!
Abbastanza sicuro, Excel aggiunge i menu a discesa del filtro automatico nella riga superiore della tabella pivot. E il filtro automatico funziona in modo diverso rispetto ai filtri delle tabelle pivot. Vai al menu a discesa Entrate e scegli Filtri numerici, Top 10 …
Nella finestra di dialogo Top 10 AutoFilter, scegli Top 6 Items. Non è un errore di battitura…. Se desideri cinque clienti, scegli 6. Se desideri 10 clienti, scegli 11.
Per il filtro automatico, la riga del totale generale è l'elemento più grande nei dati. I primi cinque clienti occupano le posizioni da 2 a 6 nei dati.
Attenzione
Chiaramente, stai facendo un buco nel tessuto di Excel con questo trucco. Se in seguito modifichi i dati sottostanti e aggiorni la tabella pivot, Excel non aggiornerà il filtro, perché, per quanto ne sa Microsoft, non c'è modo di applicare un filtro a una tabella pivot!
Nota
Il nostro obiettivo è mantenerlo segreto a Microsoft, perché è una funzionalità piuttosto interessante. È stato "rotto" per un bel po 'di tempo, quindi ci sono molte persone che potrebbero fare affidamento su di esso ormai.
Una soluzione completamente legale in Excel 2013+Se desideri una tabella pivot che mostri i primi cinque clienti ma il totale di tutti i clienti, devi spostare i tuoi dati al di fuori di Excel. Se hai Excel 2013 o 2016, c'è un modo molto conveniente per farlo. Per mostrarti questo, ho eliminato la tabella pivot originale. Scegli Inserisci, Tabella pivot. Prima di fare clic su OK, selezionare la casella che dice Aggiungi questi dati al modello di dati.
Costruisci la tua tabella pivot normalmente. Utilizza il menu a discesa in A3 per selezionare Value Filters, Top 10 e chiedi i primi cinque clienti. Con una cella nella tabella pivot selezionata, vai alla scheda Progettazione nella barra multifunzione e apri il menu a discesa Totale parziale. La scelta finale nel menu a discesa è Includi elementi filtrati nei totali. Normalmente, questa scelta è disattivata. Tuttavia, poiché i dati sono archiviati nel modello di dati anziché in una normale cache pivot, questa opzione è ora disponibile.
Scegli l'opzione Includi elementi filtrati nel totale e il tuo totale complessivo ora include un asterisco e il totale di tutti i dati.
Questo trucco originariamente mi è venuto da Dan nel mio seminario a Philadelphia. Grazie a Miguel Caballero per aver suggerito questa funzione.
Guarda un video
- Il filtro Top 10 della tabella pivot fornisce un totale delle righe visibili
- Includi elementi filtrati nei totali è disattivato
- Strano modo per richiamare il filtro dati dalla cella magica
- I filtri di dati non sono consentiti nelle tabelle pivot
- Excel non riesce a disattivare il filtro dati dalla cella magica
- Chiedi i primi 6 per ottenere i primi 5 più il totale complessivo
- Utile per filtrare in base a un elemento pivot specifico
- Excel 2013 o più recente: un modo diverso per ottenere il vero totale
- Invia i tuoi dati tramite il modello di dati
- Sarà disponibile l'opzione Includi elementi filtrati nei totali
- Ottieni il totale con l'asterisco
- Ho imparato questo trucco più di 10 anni fa da Dan a Philadelphia
Trascrizione del video
Impara Excel per Podcast, episodio 1999 - Tabella pivot True Top Five
Sto podcasting l'intero libro. C'è una playlist, fai clic sulla I nell'angolo in alto a destra per seguire quella playlist. Bentornato al netcast. Sono Bill Jelen.
Va bene, quindi creeremo una tabella pivot e vogliamo mostrare non tutti i clienti, ma solo i primi cinque clienti. INSERT, tabella pivot. Ok, metto Cliente a sinistra e Entrate. Bene, ecco il nostro intero elenco di clienti annotati come 6,7 milioni di dollari. Excel, rende facile fare i primi cinque. Vai in Etichette di riga, Filtri valore, primi 10. Non è necessario che sia primo. Può essere in alto o in basso. Non devono essere cinque. Può essere venti, quaranta, può essere qualunque cosa. Top ottanta per cento, dammi dischi sufficienti per arrivare a tre milioni di dollari o quattro milioni di dollari, ma eccoci qui. I primi cinque elementi. Ora ricorda 6,7 milioni di dollari, fai clic su OK e il mio grosso problema qui è che il totale complessivo non è 6,7 milioni. Quando lo do al vicepresidente delle vendite, andrà fuori di testa, dicendo: aspetta un secondo,So di aver guadagnato più di 3,3 milioni di dollari. Bene, quindi annulleremo, annulleremo e torneremo ai dati originali.
Ora il prossimo trucco che ho imparato durante uno dei miei seminari su Power Excel a Philadelphia. Un ragazzo di nome Dan nella seconda fila, me lo ha mostrato. Sono passati più di dieci anni da quando mi ha mostrato questo trucco e prima dobbiamo parlare dei filtri. Quindi normalmente, se hai intenzione di utilizzare il filtro normale, questo filtro qui, scegli una cella qualsiasi nel tuo set di dati e fai clic sull'icona del filtro, oppure alcune persone scelgono l'intero set di dati, CONTROL * e fai clic sull'icona del filtro, ma c'è una terza via. Un modo che non interessa a nessuno. Se vai all'ultima cella di intestazione, nel mio caso, è Costo in L1 e vai di una cella a destra. Chiamo questa cella magica, non ho idea del perché ma per qualche motivo sconosciuto, da questa cella, posso filtrare il Data Set adiacente. Va bene, è un modo strano e a nessuno importa di questo.
Bene, perché ci sono altri due modi davvero buoni per invocare un Filtro, nessuno ha bisogno di sapere della cella magica, ma ecco il punto, vedi all'interno di una tabella pivot, è disattivato. Non sei autorizzato a utilizzare quei filtri. È contro le regole. Ora, se esco qui, sono più che benvenuto a usare il filtro, ma dentro si scalano. Non so chi sia la persona che lo rende grigio, ma non hanno mai sentito il mio breve discorso sulla cella magica, perché se vado all'ultima cella di intestazione e vado di una cella a destra, guarda quello, dimenticano di disattivare il filtro e ora ho appena aggiunto i vecchi filtri automatici alla tabella pivot. Quindi vengo qui, vado a Number Filters, che è diverso dai Value Filters. Si chiama ancora Top Ten. Leggermente diverso, chiederò i primi cinque, no i primi sei.I primi sei perché per questo filtro il totale generale è solo un'altra riga e il totale generale è l'elemento più grande e quindi quando viene chiesto per gli elementi da 2 a 6, ottengo i primi cinque elementi.
Va bene, quindi eccoci qui. Un fantastico trucco per i filtri, che ci dà i primi cinque elementi e il vero totale di tutti. Va bene ora, un paio di cose. Non dimenticare la cella magica. Va bene, non c'è modo di disattivare questo filtro, a meno che non torni alla cella magica. Va bene, quindi devi ricordare la cella magica. Inoltre, se modifichi i dati sottostanti e aggiorni la tabella pivot, non aggiorneranno il filtro perché, per quanto ne sa Microsoft, non ti è permesso avere un filtro.
Questo è utile per altre cose. A volte abbiamo prodotti che vanno in alto. Andiamo qui in una forma tabulare. Non necessario, mi piace solo ottenere titoli reali. Gizmo, Widget, Gadget, Doodads. Va bene e forse sei il manager di Doodads e hai bisogno di vedere solo i clienti che avevano un valore particolare e Doodads. Quindi vado alla cella magica, accendo il filtro e poi sotto Doodads posso chiedere elementi maggiori di zero. Fare clic su OK. Va bene, quel tipo di filtro non sarebbe possibile su una normale tabella pivot, ma è possibile utilizzando la cella magica.
Va bene, ora annulliamo l'elenco. Spegniamo questo filtro e rimuoviamo la tabella pivot e, se sei in Excel 2013 o nuovo, ti mostrerò un modo completamente legale per ottenere il totale corretto in fondo. Inserisci tabella pivot, qui in basso, a partire da Excel 2013 questa scatola molto innocua, non sembra molto eccitante, aggiungi questi dati al modello di dati. Questo invia i dati, dietro le quinte, al motore Power Pivot. Crea lo stesso identico rapporto. Clienti in basso sul lato sinistro. Entrate nel cuore della tabella pivot. Quindi, vai ai filtri regolari, i filtri di valore in alto 10. Chiedi i primi cinque. Notate ancora una volta che abbiamo 6,7 milioni di dollari dopo averlo fatto, 3,3 milioni di dollari, ma ecco la differenza. Quando vado alla scheda Progettazione, in Subtotali, questa funzione chiamata Includi elementi filtrati nei totali,non è più disattivato. In una normale tabella pivot non è disponibile. Otteniamo un piccolo asterisco lì ed è il totale di tutto. Va bene, ora ovviamente funziona solo in Excel 2013 o versioni successive.
Va bene, mi ci vorranno sei settimane per pubblicare l'intero libro qui su YouTube. Ci sono così tanti buoni consigli qui. Suggerimenti che potrebbero farti risparmiare tempo, subito. Acquista subito l'intero libro e avrai accesso a tutti e 40, in realtà sono molto più di 40 consigli. Tasti di scelta rapida di Excel. Tutti i tipi di grandi cose in questo libro.
Va bene, ricapitoliamo. Quindi, quando facciamo un filtro top 10 di una tabella pivot, ci dà il totale ma solo le righe visibili, non le cose che sono state filtrate. Sì, se andiamo alla seconda scheda e cerchiamo Subtotali, Elementi filtrati e Totali, è disattivato, ma c'è un modo strano per richiamare il vecchio filtro dati dalla cella magica. L'ultima cella di intestazione, vai di una cella a destra, non puoi usare filtri e tabelle pivot, ma se vai alla cella magica si dimenticano di disattivarla. Ora nel filtro dei numeri, chiedi ai primi sei di ottenere i primi cinque, più il totale generale. Utile anche per filtrare un elemento pivot specifico: Doodads, tutto ciò che aveva maggiore di 0 in Doodads o primi 5 Doodads. Excel 2013 o versioni successive, esiste un modo diverso per ottenere il totale reale.Selezionare la casella per il modello di dati e quindi includere elementi filtrati nei totali sarà disponibile. Ottieni il totale con un asterisco. E grazie a Dan a Filadelfia che mi ha mostrato a uno dei miei seminari su Power Excel, più di dieci anni fa, e mi ha dato questo piccolo grande trucco. Un modo per far passare il filtro attraverso il muro del tavolo pivot del club. Normalmente non consentono quel filtro automatico.
Ehi, voglio ringraziarti per essere passato. Ci vediamo la prossima volta, per un altro netcast da MRExcel.
Download file
Scarica il file di esempio qui: Podcast1999.xlsx
