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. Si noti che il cliente più grande, Roto-Rooter, rappresenta il 9% delle entrate totali.
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. Roto-Rooter, che in precedenza era il 9% del totale, è il 23% del nuovo totale.
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 oppure premi Ctrl + Maiusc + L.
- 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 e poi 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 cellula la cellula magica.
E ora, torniamo alle tabelle pivot
C'è una regola che dice che non puoi usare il filtro automatico quando sei in una tabella pivot. Vedi sotto? L'icona Filtro è disattivata perché ho selezionato una cella nella tabella pivot.
Non so perché 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 cellula 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 a un filtro di tabella 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 vuoi cinque clienti, scegli 6. Se vuoi 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 è mantenere questo 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 fuori da Excel. Se hai Excel 2013 o versioni successive in esecuzione su Windows, esiste 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 di controllo 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 nei totali e il tuo totale complessivo ora include un asterisco e il totale di tutti i dati, come mostrato di seguito.
Questo trucco delle cellule magiche originariamente mi venne da Dan nel mio seminario a Filadelfia e fu ripetuto 15 anni dopo da un Dan diverso dal mio seminario a Cincinnati. Grazie a Miguel Caballero per aver suggerito questa funzione.
