Pivottabellens topp 10-filter ger totalt de synliga raderna
Pivottabeller erbjuder ett topp 10-filter. Det är coolt. Det är flexibelt. Men jag hatar det och jag kommer att berätta varför.
Här är en pivottabell som visar intäkter per kund. Intäkterna är totalt 6,7 miljoner dollar.
Vad händer om min chef har en guldfisk och bara vill se de fem bästa kunderna?
För att starta, öppna rullgardinsmenyn i A3 och välj Värde filter, Topp 10.
Den superflexibla Top 10-filterdialogen tillåter Top / Bottom. Det kan göra 10, 5 eller något annat nummer. Du kan be om de fem bästa artiklarna, de bästa 80% eller tillräckligt många kunder för att komma till 5 miljoner dollar.
Men här är problemet: Den resulterande rapporten visar fem kunder och summan från dessa kunder istället för totalen från alla.
Men först, några viktiga ord om AutoFilter
Jag inser att det här verkar som en fråga utan vägg. Om du vill aktivera rullgardinsmenyerna Filter på en vanlig datamängd, hur gör du det? Här är tre riktigt vanliga sätt:
- Välj en cell i dina data och klicka på ikonen Filter på fliken Data.
- Markera alla dina data med Ctrl + * och klicka på filterikonen på fliken Data.
- Tryck på Ctrl + T för att formatera data som en tabell.
Det här är tre riktigt bra sätt. Så länge du känner till någon av dem behöver du absolut inte veta något annat sätt. Men här är ett otroligt dunkelt men magiskt sätt att sätta på filtret:
- Gå till din rad med rubriker, gå till cellen längst till höger. Flytta en cell till höger. Av någon okänd anledning, när du befinner dig i den här cellen och klickar på filterikonen, filtrerar Excel datauppsättningen till vänster. Jag har ingen aning om varför det här fungerar. Det är verkligen inte värt att prata om eftersom det redan finns tre riktigt bra sätt att aktivera Filter-rullgardinsmenyerna. Jag kallar denna cell för magicellen.
Och nu, tillbaka till pivottabeller …
Så det finns en regel som säger att du inte kan använda AutoFilters när du befinner dig i en pivottabell. Se nedan? Filterikonen är nedtonad eftersom jag har valt en cell i pivottabellen.
Jag har aldrig riktigt övervägt varför Microsoft gråter detta. Det måste vara något internt som säger att AutoFilter och en pivottabell inte kan samexistera. Så det finns någon i Excel-teamet som ansvarar för att gråta ut filterikonen. Den personen har aldrig hört talas om den magiska cellen. Välj en cell i pivottabellen och filtret blir nedtonat. Klicka utanför pivottabellen så aktiveras filter igen.
Men vänta. Vad sägs om den magiska cellen som jag just berättade om? Om du klickar i cellen till höger om den sista rubriken glömmer Excel att gråa ut filterikonen!
Visst, Excel lägger till AutoFilter-nedrullningar till den översta raden i din pivottabell. Och AutoFilter fungerar annorlunda än pivottabelfilter. Gå till rullgardinsmenyn Intäkter och välj Nummerfilter, Topp 10 …
I Top 10 AutoFilter dialog, välj Top 6 Items. Det är inte ett stavfel …. Om du vill ha fem kunder väljer du 6. Om du vill ha 10 kunder väljer du 11.
För AutoFilter är den totala raden det största objektet i data. De fem bästa kunderna upptar positionerna 2 till 6 i data.
Varning
Det är uppenbart att du sliter ett hål i Excel-tyget med detta trick. Om du senare ändrar de underliggande uppgifterna och uppdaterar din pivottabell uppdaterar inte Excel filtret, så vitt Microsoft vet finns det inget sätt att tillämpa ett filter på en pivottabell!
Notera
Vårt mål är att hålla detta hemligt från Microsoft, eftersom det är en ganska cool funktion. Det har varit "trasigt" under ganska lång tid, så det finns många människor som kanske litar på det nu.
En helt laglig lösning i Excel 2013+Om du vill ha en pivottabell som visar de fem bästa kunderna men summan från alla kunder, måste du flytta dina data utanför Excel. Om du har Excel 2013 eller 2016 finns det ett mycket bekvämt sätt att göra detta. För att visa dig detta har jag raderat den ursprungliga pivottabellen. Välj Infoga, pivottabell. Markera rutan som säger Lägg till dessa data till datamodellen innan du klickar på OK.
Bygg ditt pivottabell som vanligt. Använd rullgardinsmenyn i A3 för att välja värdefilter, topp 10 och be om de fem bästa kunderna. Med en cell i pivottabellen valt, gå till fliken Design i menyfliksområdet och öppna rullgardinsmen Delsummor. Det slutliga valet i rullgardinsmenyn är Inkludera filtrerade objekt i totalt. Normalt är detta val nedtonat. Men eftersom data lagras i datamodellen istället för en normal pivotcache är det här alternativet nu tillgängligt.
Välj alternativet Inkludera filtrerade objekt i totalt, och din totalsumma innehåller nu en asterisk och summan av alla data.
Detta trick kom ursprungligen till mig från Dan i mitt seminarium i Philadelphia. Tack till Miguel Caballero för att du föreslog den här funktionen.
Kolla på video
- Pivottabellens topp 10-filter ger totalt de synliga raderna
- Inkludera filtrerade objekt i totaler är nedtonade
- Konstigt sätt att åberopa datafiltret från den magiska cellen
- Datafilter är inte tillåtna i pivottabeller
- Excel misslyckas med att gråa ut datafiltret från den magiska cellen
- Be om topp 6 för att få topp 5 plus Grand Total
- Användbar för filtrering efter ett specifikt pivotobjekt
- Excel 2013 eller senare: Annat sätt att få True Total
- Skicka dina data via datamodellen
- Inkludera filtrerade objekt i totala kommer att finnas tillgängliga
- Få Total med asterisk
- Jag lärde mig detta trick för 10+ år sedan av Dan i Philadelphia
Videoutskrift
Lär dig Excel för Podcast, avsnitt 1999 - Pivottabell True Top Five
Jag podcastar hela denna bok. Det finns en spellista, klicka på jag i det övre högra hörnet för att följa den spellistan. Välkommen tillbaka till netcast. Jag heter Bill Jelen.
Okej, så vi ska skapa en pivottabell och vi vill visa inte alla kunder utan bara de fem bästa kunderna. INSERT, pivottabell. Okej, jag lägger ner kunden på vänster sida och intäkter. Okej så här är hela vår kundlista noterad som 6,7 miljoner dollar. Excel, gör det enkelt att göra en topp fem. Gå in i radetiketter, värdefilter, topp 10. Behöver inte vara topp. Det kan vara topp eller botten. Behöver inte vara fem. Det kan vara tjugo, fyrtio, det kan vara vad som helst. Topp åttio procent, ge mig tillräckligt med poster för att få upp till tre miljoner dollar eller fyra miljoner dollar, men nu går vi. Topp fem artiklar. Kom ihåg nu 6,7 miljoner dollar, klicka på OK och mitt stora problem här är att den totala summan inte är 6,7 miljoner. När jag ger detta till VP för försäljning kommer han att freak out och säger, vänta en sekund,Jag vet att jag gjorde mer än 3,3 miljoner dollar. Okej, så vi ska ångra, ångra det och gå tillbaka till originaldata.
Nu detta nästa trick jag lärde mig under ett av mina Power Excel-seminarier i Philadelphia. En kille som heter Dan i rad två, visade mig detta. Det var för mer än tio år sedan att han visade mig detta trick, och först måste vi prata om filtren. Så normalt, om du ska använda det vanliga filtret, det här filtret här, väljer du vilken cell som helst i din datamängd och klickar på filterikonen, eller vissa väljer hela datamängden, KONTROLL * och klickar på filterikonen, men det finns en tredje väg. Ett sätt som ingen bryr sig om. Om du går till den allra sista rubrikcellen, i mitt fall är det kostnad i L1 och går en cell till höger. Jag kallar detta den magiska cellen, jag har ingen aning om varför, men av någon okänd anledning, från den här cellen, kan jag filtrera intilliggande datamängd. Okej, det är som ett konstigt sätt och ingen bryr sig om det här.
Okej, eftersom det finns två andra riktigt bra sätt att åberopa ett filter, behöver ingen veta om den magiska cellen, men här är saken, se inuti ett pivottabell, det är nedtonat. Du får inte använda dessa filter. Det strider mot reglerna. Om jag kommer hit är jag mer än välkommen att använda filtret, men inuti raderar de. Jag vet inte vem personen är som gör det här grått, men de har aldrig hört mina små prata om den magiska cellen, för om jag går till den allra sista rubrikcellen och går en cell till höger, titta på det, de glömmer att gråa ut filtret och nu har jag precis lagt till de gamla automatiska filtren i pivottabellen. Så jag kommer hit, går till Number Filters, det är annorlunda än Value Filters. Det heter fortfarande topp tio. Något annorlunda, jag kommer att be om de fem bästa, nej de sex bästa.De sex bästa eftersom för detta filter är Grand Total bara en rad, och Grand Total är det största objektet och sedan får jag de fem bästa när jag blir ombedd att ta emot artiklarna 2 till 6.
Okej, så där är vi. Ett häftigt filterhack som ger oss de fem bästa artiklarna och den totala summan av alla. Okej nu, ett par saker. Glöm inte den magiska cellen. Okej, det finns inget sätt att stänga av det här filtret, såvida du inte går tillbaka till den magiska cellen. Okej så du måste komma ihåg den magiska cellen. Om du ändrar underliggande data och uppdaterar pivottabellen kommer de inte att uppdatera filtret för så vitt Microsoft vet är det inte tillåtet att ha ett filter.
Detta är användbart för andra saker. Ibland har vi produkter som går överst. Låt oss gå hit i tabellform. Inte nödvändigt, jag vill bara få riktiga rubriker. Gizmo, Widget, Gadgets, Doodads. Okej och kanske är du chef för Doodads och du behöver bara se de kunder som hade ett särskilt värde och Doodads. Så jag går till den magiska cellen, sätter på filtret och sedan under Doodads kan jag be om saker som är större än noll. Klicka på OK. Okej, den typen av filtrering skulle inte vara möjlig i en vanlig pivottabell, men det är möjligt att använda den magiska cellen.
Okej, nu ska vi ångra listan. Låt oss stänga av det här filtret och ta bort pivottabellen, och om du är i Excel 2013 eller ny kommer jag att visa dig ett helt lagligt sätt att få rätt summa längst ner. Infoga pivottabell, nere här längst ner, med start i Excel 2013 denna mycket oskyldiga ruta, låter inte särskilt spännande, lägg till dessa data i datamodellen. Det skickar data bakom kulisserna till Power Pivot Engine. Bygg exakt samma rapport. Kunder på vänster sida. Intäkter i hjärtat av pivottabellen. Gå sedan till de vanliga filtren, Value Filters topp 10. Be om de fem bästa. Lägg märke till igen vi har 6,7 miljoner dollar efter att jag gjort det, 3,3 miljoner dollar men här är skillnaden. När jag går till fliken Design, under delsummor, kallas den här funktionen Inkludera filtrerade objekt i totalt,är inte längre nedtonad. Vid en vanlig pivottabell är inte tillgängligt. Vi får en liten asterisk där och det är summan av allt. Okej, nu fungerar det naturligtvis bara i Excel 2013 eller senare.
Okej, det kommer att ta sex veckor för mig att få hela denna bok här på YouTube. Det finns så många bra tips här. Tips som kan börja spara tid direkt. Köp hela boken just nu så får du tillgång till alla 40, det är faktiskt mycket mer än 40 tips. Genvägar för Excel. Alla typer av bra saker i den här boken.
Okej, sammanfattning. Så när vi gör ett Pivot Table Top 10-filter, ger det oss de totala men bara de synliga raderna, inte de saker som det filtrerade ut. Ja, om vi går till den andra fliken och letar efter delsummor, filtrerade föremål och totaler, är den nedtonad, men det finns ett udda sätt att åberopa det gamla datafiltret från magicellen. Den allra sista rubrikcellen, gå en cell till höger, du kan inte använda filter och pivottabeller, men om du går till den magiska cellen glömmer de att gråa ut den. Nu i nummerfiltret ber du om de sex bästa för att få de fem bästa, plus totalsumman. Också användbart för att filtrera till ett specifikt Pivot-objekt: Doodads, allt som hade större än 0 i Doodads eller topp 5 Doodads. Excel 2013 eller senare, det finns ett annat sätt att få True Total.Markera kryssrutan för datamodellen och inkludera sedan filtrerade objekt i totaler. Du får summan med en asterisk. Och tack till Dan i Philadelphia som visade mig på ett av mina Power Excel-seminarier för mer än tio år sedan och gav mig detta fantastiska lilla trick. Ett sätt för filtret att smyga sig genom Club Pivot Table Wall. De tillåter normalt inte det automatiska filtret.
Hej, jag vill tacka dig för att du kom förbi. Vi ses nästa gång för en ny netcast från MRExcel.
Nedladdning fil
Ladda ner exempelfilen här: Podcast1999.xlsx
