Tabelele pivot oferă un filtru Top 10. Este rece. Este flexibil. Dar îl urăsc și vă voi spune de ce.
Iată un tabel pivot care arată veniturile în funcție de client. Venitul total este de 6,7 milioane de dolari. Observați că cel mai mare client, Roto-Rooter, reprezintă 9% din venitul total.
Ce se întâmplă dacă managerul meu are atenția unui pește de aur și vrea să vadă doar primii cinci clienți? Pentru a începe, deschideți meniul drop-down în A3 și selectați Value Filters, Top 10.
Dialogul super-flexibil Top 10 Filter permite partea de sus / partea de jos. Poate face 10, 5 sau orice alt număr. Puteți solicita primele cinci articole, primele 80% sau suficienți clienți pentru a ajunge la 5 milioane de dolari.
Dar iată problema: raportul rezultat arată cinci clienți și totalul de la acei clienți în loc de totalurile de la toată lumea. Roto-Rooter, care anterior era 9% din total, reprezintă 23% din noul total.
Dar mai întâi, câteva cuvinte importante despre filtrul automat
Îmi dau seama că acest lucru pare o întrebare simplă. Dacă doriți să activați meniurile drop-down Filtru pentru un set de date obișnuit, cum o faceți? Iată trei moduri cu adevărat comune:
- Selectați o celulă din datele dvs. și faceți clic pe pictograma Filtrare din fila Date sau apăsați Ctrl + Shift + L.
- Selectați toate datele dvs. cu Ctrl + * și faceți clic pe pictograma Filtrare din fila Date.
- Apăsați Ctrl + T pentru a formata datele ca tabel.
Acestea sunt trei modalități foarte bune. Atâta timp cât știți pe oricare dintre ele, nu este absolut necesar să cunoașteți alt mod. Dar iată un mod incredibil de obscur, dar magic de a porni filtrul:
- Mergeți la rândul dvs. de anteturi și apoi mergeți la celula din partea dreaptă. Mutați o celulă spre dreapta. Din motive necunoscute, când vă aflați în această celulă și faceți clic pe pictograma Filtru, Excel filtrează setul de date din stânga. Habar n-am de ce funcționează. Într-adevăr nu merită să vorbiți, deoarece există deja trei moduri foarte bune de a activa listele drop-down Filtru. Eu numesc această celulă celula magică.
Și acum, înapoi la tabelele pivot
Există o regulă care spune că nu puteți utiliza AutoFilter atunci când vă aflați într-un tabel pivot. Vezi mai jos? Pictograma Filtru este gri, deoarece am selectat o celulă din tabelul pivot.
Nu știu de ce Microsoft rezolvă asta. Trebuie să fie ceva intern care spune că AutoFilter și un tabel pivot nu pot coexista. Deci, în echipa Excel există cineva care se ocupă de oprirea pictogramei Filtru. Acea persoană nu a auzit niciodată de celula magică. Selectați o celulă din tabelul pivot, iar filtrul devine gri. Faceți clic în afara tabelului pivot și Filtrul este activat din nou.
Dar asteapta. Dar celula magică despre care tocmai v-am spus? Dacă faceți clic în celula din dreapta ultimului titlu, Excel uită să îngrășeze pictograma Filtru!
Destul de sigur, Excel adaugă drop-down-uri AutoFilter în rândul de sus al tabelului pivot. Și AutoFilter funcționează diferit decât un filtru de tabel pivot. Accesați meniul derulant Venituri și alegeți Filtre numerice, Top 10 …
În dialogul Top 10 AutoFilter, alegeți Top 6 Elemente. Nu este o greșeală de eroare … dacă doriți cinci clienți, alegeți 6. Dacă doriți 10 clienți, alegeți 11.
Pentru AutoFilter, rândul total mare este cel mai mare element din date. Primii cinci clienți ocupă pozițiile de la 2 la 6 în date.
Prudență
În mod clar, rupeți o gaură în țesătura Excel cu acest truc. Dacă modificați ulterior datele subiacente și reîmprospătați tabelul pivot, Excel nu va reîmprospăta filtrul deoarece, din câte știe Microsoft, nu există nicio modalitate de a aplica un filtru unui tabel pivot!
Notă
Scopul nostru este să păstrăm acest lucru secret de Microsoft, deoarece este o caracteristică destul de interesantă. A fost „spart” de ceva timp, așa că există o mulțime de oameni care s-ar putea baza pe asta până acum.
O soluție complet legală în Excel 2013+
Dacă doriți un tabel pivot care să vă prezinte primii cinci clienți, dar totalul de la toți clienții, trebuie să vă mutați datele în afara Excel. Dacă aveți Excel 2013 sau o versiune mai nouă care rulează în Windows, există o modalitate foarte convenabilă de a face acest lucru. Pentru a vă arăta acest lucru, am șters tabelul pivot original. Alege Insert, Pivot Table. Înainte de a face clic pe OK, bifați caseta de selectare Adăugați aceste date la modelul de date.
Construiți-vă tabelul pivot în mod normal. Utilizați meniul derulant din A3 pentru a selecta Filtre valoare, Top 10 și solicitați primii cinci clienți. Cu o celulă din tabelul pivot selectată, accesați fila Proiectare din panglică și deschideți meniul derulant Subtotale. Alegerea finală din meniul derulant este Includeți elementele filtrate în totaluri. În mod normal, această alegere este gri. Dar, deoarece datele sunt stocate în Modelul de date în locul unui cache pivot normal, această opțiune este acum disponibilă.
Alegeți opțiunea Includeți elemente filtrate în totaluri, iar Marele dvs. total include acum un asterisc și totalul tuturor datelor, așa cum se arată mai jos.
Acest truc de celule magice mi-a venit inițial de la Dan la seminarul meu din Philadelphia și a fost repetat 15 ani mai târziu de un alt Dan față de seminarul meu de la Cincinnati. Mulțumim lui Miguel Caballero pentru sugerarea acestei caracteristici.
