Tabelul pivot Top 10 Filter oferă un total de rânduri vizibile
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.
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.
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.
- 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, mergeți la celula de direcție din dreapta. 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 …
Deci, există o regulă care spune că nu puteți utiliza filtrele automate 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 m-am gândit niciodată cu adevărat de ce Microsoft face 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 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 AutoFilterul funcționează diferit față de filtrele din tabelul 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 scriere … 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 2016, 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, selectați caseta care spune 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 total, iar Grand Totalul dvs. include acum un asterisc și totalul tuturor datelor.
Acest truc mi-a venit inițial de la Dan la seminarul meu din Philadelphia. Mulțumim lui Miguel Caballero pentru sugerarea acestei caracteristici.
Urmăriți videoclipul
- Tabelul pivot Top 10 Filter oferă un total de rânduri vizibile
- Includeți elementele filtrate în Totaluri este gri
- Mod ciudat de a invoca Filtrul de date din celula magică
- Filtrele de date nu sunt permise în tabelele pivot
- Excel nu reușește să stingă filtrul de date din celula magică
- Solicitați primii 6 pentru a obține primii 5 plus Grand Total
- Util pentru filtrarea după un anumit element pivot
- Excel 2013 sau mai nou: mod diferit de a obține adevăratul total
- Trimiteți-vă datele prin Modelul de date
- Includeți elementele filtrate în Totaluri vor fi disponibile
- Obțineți Total cu asterisc
- Am învățat acest truc acum 10+ ani de la Dan în Philadelphia
Transcriere video
Aflați Excel pentru Podcast, Episodul 1999 - Pivot Table True Top Five
Podcastez această carte întreagă. Există o listă de redare, dați clic pe I în colțul din dreapta sus pentru a urmări acea listă de redare. Bine ați venit înapoi pe netcast. Eu sunt Bill Jelen.
Bine, așa că vom crea un tabel pivot și vrem să arătăm nu toți clienții, ci doar primii cinci clienți. INSERT, tabel pivot. Bine, voi pune Clientul în partea stângă și Venituri. Bine, așa că iată întreaga noastră listă de clienți cu 6,7 milioane de dolari. Excel, face mai ușor să faci un top cinci. Accesați etichetele rândurilor, filtrele de valoare, top 10. Nu trebuie să fie top. Poate fi sus sau jos. Nu trebuie să fie cinci. Poate fi douăzeci, patruzeci, poate fi orice. Top optzeci la sută, dă-mi suficiente înregistrări pentru a ajunge la trei milioane de dolari sau patru milioane de dolari, dar iată-ne. Top cinci articole. Acum amintiți-vă 6,7 milioane de dolari, faceți clic pe OK și marea mea problemă aici este că acel mare total nu este de 6,7 milioane. Când voi da acest lucru vicepreședintelui vânzărilor, se va speria, spunând, așteptați o secundă,Știu că am făcut mai mult de 3,3 milioane de dolari. Corect, așa că vom anula, anula acest lucru și vom reveni la datele originale.
Acum, următorul truc l-am învățat în timpul unuia dintre seminariile mele Power Excel din Philadelphia. Un tip pe nume Dan în rândul doi mi-a arătat asta. Acum mai bine de zece ani mi-a arătat acest truc și mai întâi trebuie să vorbim despre Filtre. Deci, în mod normal, dacă folosiți filtrul obișnuit, acest filtru aici, alegeți orice celulă din setul de date și faceți clic pe pictograma Filtru sau unii oameni aleg întregul set de date, CONTROL * și faceți clic pe pictograma Filtru, dar există o a treia cale. Un mod de care nimănui nu îi pasă. Dacă mergeți la ultima celulă de titlu, în cazul meu, acesta este Cost în L1 și mergeți la o celulă la dreapta. Eu numesc aceasta celula magică, nu am idee de ce, dar dintr-un motiv necunoscut, din această celulă, pot filtra setul de date adiacent. Bine, este ca un mod ciudat și nimănui nu-i pasă de asta.
Corect, pentru că există alte două modalități foarte bune de a invoca un filtru, nimeni nu trebuie să știe despre celula magică, dar iată ce este, vezi în interiorul unui tabel pivot, este gri. Nu aveți voie să utilizați aceste filtre. Este împotriva regulilor. Acum, dacă ies aici, sunt mai mult decât binevenit să folosesc Filtrul, dar în interior se clasifică. Nu știu cine este persoana care greșește acest lucru, dar nu au auzit niciodată micul meu discurs despre celula magică, pentru că dacă merg la ultima celulă de direcție și merg o celulă la dreapta, uită-te la asta, au uitat să îngrășeze filtrul și acum tocmai am adăugat filtrele automate vechi în tabelul pivot. Așa că vin aici, merg la Number Filters, care este diferit de Value Filters. Se numește încă Top Ten. Puțin diferit, voi cere primii cinci, nu primii șase.Primele șase, deoarece pentru acest filtru, Totalul mare este doar un alt rând, iar Marele total este cel mai mare articol, iar atunci când mi se solicită articolele de la 2 la 6, primesc primele cinci articole.
Bine, așa că suntem. Un hack de filtru interesant, care ne oferă primele cinci articole și adevăratul total al tuturor. Bine acum, câteva lucruri. Nu uitați de celula magică. Bine, nu există nicio modalitate de a dezactiva acest filtru, decât dacă te întorci la celula magică. În regulă, așa că trebuie să vă amintiți celula magică. De asemenea, dacă modificați datele subiacente și reîmprospătați tabelul pivot, acestea nu vor reîmprospăta filtrul deoarece, din câte știe Microsoft, nu aveți voie să aveți un filtru.
Acest lucru este util pentru alte lucruri. Uneori avem produse care merg peste tot. Să mergem aici într-un formular tabelar. Nu este necesar, îmi place doar să obțin titluri reale. Gizmo, Widget, Gadgets, Doodads. Bine și poate ești managerul Doodads și trebuie să vezi doar clienții care aveau o anumită valoare și Doodads. Așa că mă duc la celula magică, pornesc Filtrul și apoi în Doodads pot cere articole mai mari decât zero. Faceți clic pe OK. Bine, acest tip de filtrare nu ar fi posibil pe un tabel pivot obișnuit, dar este posibil folosind celula magică.
Bine acum să anulăm lista. Să oprim acest filtru și să eliminăm tabelul pivot și, dacă sunteți în Excel 2013 sau nou, vă voi arăta un mod complet legal de a obține totalul corect în partea de jos. Introduceți tabelul pivot, aici jos, începând cu Excel 2013, această casetă foarte inofensivă, nu sună foarte interesant, adăugați aceste date la modelul de date. Aceasta trimite datele, în culise, către motorul Power Pivot. Construiți exact același raport. Clienții din partea stângă. Venituri în inima tabelului pivot. Apoi, accesați filtrele obișnuite, topul filtrelor de valoare 10. Cereți primele cinci. Observați din nou că avem 6,7 milioane de dolari după ce fac asta, 3,3 milioane de dolari, dar iată diferența. Când mergi la fila Design, sub Subtotale, această caracteristică numită Include elemente filtrate în Totaluri,nu mai este cenușiat. La un tabel pivot obișnuit nu este disponibil. Avem un mic asterisc acolo și este totalul tuturor. Bine, acum desigur că funcționează numai în Excel 2013 sau mai nou.
În regulă, va dura șase săptămâni până când voi scoate toată această carte aici pe YouTube. Există atât de multe sfaturi bune aici. Sfaturi care ar putea începe să vă economisească timp, imediat. Cumpărați întreaga carte chiar acum și veți avea acces la toate cele 40, de fapt sunt mult mai mult de 40 de sfaturi. Taste de comenzi rapide Excel. Tot felul de lucruri grozave din această carte.
Bine, recapitulează. Deci, atunci când facem un filtru Pivot Top 10 Filter, acesta ne oferă numărul total, dar numai rândurile vizibile, nu lucrurile pe care le-a filtrat. Da, dacă mergem la cea de-a doua filă și căutăm Subtotale, Elemente filtrate și Totale, este gri, dar există o modalitate ciudată de a invoca Vechiul Filtru de Date din celula magică. Ultima celulă de titlu, mergeți cu o celulă la dreapta, nu puteți utiliza filtre și tabele pivot, dar dacă mergeți la celula magică, uitați să o îngrădiți. Acum, în Filtrul numeric, solicitați primii șase pentru a obține primii cinci, plus marele total. De asemenea, util pentru filtrarea la un anumit articol pivot: Doodads, orice element care a avut mai mult de 0 în Doodads sau primele 5 Doodads. Excel 2013 sau mai nou, există o modalitate diferită de a obține True Total.Bifați căsuța pentru Modelul de date și apoi includeți Elementele filtrate în Totaluri vor fi disponibile. Obțineți totalul cu un asterisc. Și mulțumesc lui Dan din Philadelphia care mi-a arătat la unul dintre seminariile mele Power Excel, acum mai bine de zece ani, și mi-a dat acest mic truc. O modalitate prin care filtrul se poate strecura prin peretele mesei clubului pivot. În mod normal, nu permit filtrul automat.
Hei, vreau să-ți mulțumesc că ai trecut pe aici. Ne vedem data viitoare, pentru un alt netcast de la MRExcel.
Descărcare fișier
Descărcați exemplarul de fișier aici: Podcast1999.xlsx
