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. Observera att den största kunden, Roto-Rooter, är 9% av de totala intäkterna.
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. Roto-Rooter, som tidigare var 9% av totalen, är 23% av den nya totalen.
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å filterikonen på fliken Data eller tryck på Ctrl + Skift + L.
- 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 och gå sedan 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 den här cellen för den magiska cellen.
Och nu, tillbaka till pivottabeller
Det finns en regel som säger att du inte kan använda AutoFilter när du befinner dig i en pivottabell. Se nedan? Filterikonen är nedtonad eftersom jag har valt en cell i pivottabellen.
Jag vet inte 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 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 ett 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älj 6. Om du vill ha 10 kunder, välj 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 underliggande data och uppdaterar din pivottabell, uppdaterar Excel inte 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 kör Excel 2013 eller senare i Windows 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 kryssrutan 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 Subtotals. 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 totala, och din totalsumma innehåller nu en asterisk och summan av alla data, som visas nedan.
Detta magiska celltrick kom ursprungligen till mig från Dan i mitt seminarium i Philadelphia och upprepades 15 år senare av en annan Dan än mitt seminarium i Cincinnati. Tack till Miguel Caballero för att du föreslog den här funktionen.
