Pivottabeller tilbyder et Top 10-filter. Det er cool. Det er fleksibelt. Men jeg hader det, og jeg vil fortælle dig hvorfor.
Her er en pivottabel, der viser indtægter efter kunde. Den samlede omsætning er $ 6,7 millioner. Bemærk, at den største kunde, Roto-Rooter, udgør 9% af den samlede omsætning.
Hvad hvis min manager har en guldfisk opmærksomhed og kun ønsker at se de fem bedste kunder? For at starte skal du åbne rullemenuen i A3 og vælge Værdifiltre, Top 10.
Den superfleksible Top 10-filterdialog tillader Top / Bottom. Det kan gøre 10, 5 eller ethvert andet nummer. Du kan bede om de fem bedste varer, de øverste 80% eller nok kunder til at komme til $ 5 millioner.
Men her er problemet: Den resulterende rapport viser fem kunder og det samlede antal fra disse kunder i stedet for totalerne fra alle. Roto-Rooter, som tidligere var 9% af det samlede antal, er 23% af det nye total.
Men først et par vigtige ord om autofilter
Jeg er klar over, at dette virker som et spørgsmål uden for væggen. Hvis du vil aktivere rullemenuerne Filter på et almindeligt datasæt, hvordan gør du det? Her er tre rigtig almindelige måder:
- Vælg en celle i dine data, og klik på filterikonet under fanen Data, eller tryk på Ctrl + Shift + L.
- Vælg alle dine data med Ctrl + *, og klik på ikonet Filter på fanen Data.
- Tryk på Ctrl + T for at formatere dataene som en tabel.
Dette er tre rigtig gode måder. Så længe du kender nogen af dem, er der absolut ingen grund til at kende en anden måde. Men her er en utrolig uklar, men magisk måde at tænde filteret på:
- Gå til din række med overskrifter, og gå derefter til den øverste celle til højre. Flyt en celle til højre. Af en ukendt grund, når du befinder dig i denne celle og klikker på ikonet Filter, filtrerer Excel datasættet til venstre for dig. Jeg aner ikke hvorfor dette fungerer. Det er virkelig ikke værd at tale om, fordi der allerede er tre rigtig gode måder at aktivere Filter-rullelisterne på. Jeg kalder denne celle for den magiske celle.
Og nu, tilbage til pivottabeller
Der er en regel, der siger, at du ikke kan bruge AutoFilter, når du er i en pivottabel. Se nedenunder? Filterikonet er nedtonet, fordi jeg har valgt en celle i pivottabellen.
Jeg ved ikke, hvorfor Microsoft gråter dette. Det skal være noget internt, der siger, at AutoFilter og en pivottabel ikke kan eksistere sammen. Så der er nogen i Excel-teamet, der har ansvaret for at nedtone filterikonet. Den person har aldrig hørt om den magiske celle. Vælg en celle i pivottabellen, og filteret bliver nedtonet. Klik uden for pivottabellen, og Filter aktiveres igen.
Men vent. Hvad med den magiske celle, jeg lige har fortalt dig om? Hvis du klikker i cellen til højre for den sidste overskrift, glemmer Excel at gråte filterikonet ud!
Sikker nok tilføjer Excel AutoFilter-rullelister til den øverste række i din drejetabel. Og AutoFilter fungerer anderledes end et pivottabelfilter. Gå til rullemenuen Indtægter, og vælg Antal filtre, Top 10….
I Top 10 AutoFilter dialog skal du vælge Top 6 Items. Det er ikke en skrivefejl … hvis du vil have fem kunder, skal du vælge 6. Hvis du vil have 10 kunder, skal du vælge 11.
For autofilter er den samlede række den største vare i dataene. De fem bedste kunder indtager position 2 til 6 i dataene.
Advarsel
Det er klart, at du river et hul i stoffet i Excel med dette trick. Hvis du senere ændrer de underliggende data og opdaterer din pivottabel, opdaterer Excel ikke filteret, for så vidt Microsoft ved, er der ingen måde at anvende et filter på en pivottabel!
Bemærk
Vores mål er at holde dette hemmeligt fra Microsoft, fordi det er en ret cool funktion. Det er blevet "brudt" i nogen tid, så der er mange mennesker, der måske stoler på det nu.
En fuldstændig lovlig løsning i Excel 2013+
Hvis du vil have en pivottabel, der viser dig de fem bedste kunder, men det samlede antal fra alle kunder, skal du flytte dine data uden for Excel. Hvis du kører Excel 2013 eller nyere i Windows, er der en meget bekvem måde at gøre dette på. For at vise dig dette har jeg slettet den originale pivottabel. Vælg Indsæt, drejetabel. Marker afkrydsningsfeltet Tilføj disse data til datamodellen, før du klikker på OK.
Byg dit drejebord som normalt. Brug rullemenuen i A3 til at vælge Værdifiltre, Top 10, og bede om de fem bedste kunder. Med en celle i den valgte pivottabel skal du gå til fanen Design i båndet og åbne rullemenuen Subtotals. Det endelige valg i rullemenuen er Inkluder filtrerede varer i totaler. Normalt er dette valg nedtonet. Men fordi dataene er gemt i datamodellen i stedet for en normal drejecache, er denne mulighed nu tilgængelig.
Vælg indstillingen Inkluder filtrerede genstande i totaler, og din samlede sum inkluderer nu en stjerne og summen af alle data, som vist nedenfor.
Dette magiske celletrick kom oprindeligt til mig fra Dan i mit seminar i Philadelphia og blev gentaget 15 år senere af en anden Dan end mit seminar i Cincinnati. Tak til Miguel Caballero for at foreslå denne funktion.
