Pivottabeller tilbyr et topp 10-filter. Det er kult. Det er fleksibelt. Men jeg hater det, og jeg vil fortelle deg hvorfor.
Her er en pivottabell som viser inntekt etter kunde. Inntektene er totalt $ 6,7 millioner. Legg merke til at den største kunden, Roto-Rooter, er 9% av den totale inntekten.
Hva om lederen min har gullfiskens oppmerksomhet og bare vil se de fem beste kundene? For å starte, åpne rullegardinmenyen i A3 og velg Verdifilter, Topp 10.
Den superfleksible topp 10-filterdialogen tillater topp / bunn. Det kan gjøre 10, 5 eller noe annet nummer. Du kan be om de fem beste artiklene, de beste 80% eller nok kunder til å komme til $ 5 millioner.
Men her er problemet: Den resulterende rapporten viser fem kunder og totalen fra disse kundene i stedet for totalene fra alle. Roto-Rooter, som tidligere var 9% av totalen, er 23% av den nye totalen.
Men først, noen få viktige ord om autofilter
Jeg skjønner at dette virker som et spørsmål utenfor veggen. Hvis du vil slå på Filter-rullegardinene på et vanlig datasett, hvordan gjør du det? Her er tre veldig vanlige måter:
- Velg en celle i dataene dine, og klikk på Filter-ikonet i Data-fanen, eller trykk Ctrl + Shift + L.
- Velg alle dataene dine med Ctrl + *, og klikk på Filter-ikonet i kategorien Data.
- Trykk Ctrl + T for å formatere dataene som en tabell.
Dette er tre veldig gode måter. Så lenge du kjenner noen av dem, er det absolutt ikke behov for å vite en annen måte. Men her er en utrolig uklar, men magisk måte å slå på filteret:
- Gå til rad med overskrifter og deretter til cellen til høyre retning. Flytt en celle til høyre. Av ukjent grunn, når du er i denne cellen og klikker på Filter-ikonet, filtrerer Excel datasettet til venstre. Jeg aner ikke hvorfor dette fungerer. Det er virkelig ikke verdt å snakke om det, fordi det allerede er tre veldig gode måter å slå på Filter-rullegardinene. Jeg kaller denne cellen for den magiske cellen.
Og nå, tilbake til pivottabeller
Det er en regel som sier at du ikke kan bruke AutoFilter når du er i en pivottabell. Se nedenfor? Filterikonet er nedtonet fordi jeg har valgt en celle i pivottabellen.
Jeg vet ikke hvorfor Microsoft gråter dette. Det må være noe internt som sier at AutoFilter og et pivottabell ikke kan eksistere sammen. Så det er noen i Excel-teamet som har ansvaret for å gråte ut filterikonet. Den personen har aldri hørt om den magiske cellen. Velg en celle i pivottabellen, og filteret blir nedtonet. Klikk utenfor pivottabellen, og Filter er aktivert igjen.
Men vent. Hva med den magiske cellen jeg nettopp fortalte deg om? Hvis du klikker i cellen til høyre for den siste overskriften, glemmer Excel å gråte ut filterikonet!
Visst nok, Excel legger til AutoFilter-rullegardinlistene til den øverste raden i pivottabellen. Og AutoFilter fungerer annerledes enn et pivottabelfilter. Gå til rullegardinmenyen Inntekter og velg Antall filtre, Topp 10….
Velg Topp 6-elementer i dialogboksen Topp 10 autofilter. Det er ikke en skrivefeil … Hvis du vil ha fem kunder, velger du 6. Hvis du vil ha 10 kunder, velger du 11.
For AutoFilter er den totale totaleraden det største elementet i dataene. De fem beste kundene inntar posisjonene 2 til 6 i dataene.
Forsiktighet
Det er klart at du river et hull i Excel-stoffet med dette trikset. Hvis du senere endrer de underliggende dataene og oppdaterer pivottabellen din, vil ikke Excel oppdatere filteret fordi, så vidt Microsoft vet, er det ingen måte å bruke et filter på en pivottabell!
Merk
Målet vårt er å holde dette hemmelig fra Microsoft fordi det er en ganske kul funksjon. Det har vært "ødelagt" i ganske lang tid, så det er mange mennesker som kanskje stoler på det nå.
En helt lovlig løsning i Excel 2013+
Hvis du vil ha en pivottabell som viser deg de fem beste kundene, men summen fra alle kunder, må du flytte dataene dine utenfor Excel. Hvis du har Excel 2013 eller nyere i Windows, er det en veldig praktisk måte å gjøre dette på. For å vise deg dette har jeg slettet den originale pivottabellen. Velg Sett inn, pivottabell. Før du klikker OK, merker du av i avmerkingsboksen Legg til disse dataene i datamodellen.
Bygg pivottabellen som vanlig. Bruk rullegardinmenyen i A3 for å velge Verdifilter, Topp 10, og be om de fem beste kundene. Med en celle i pivottabellen valgt, gå til Design-fanen i båndet og åpne rullegardinmenyen Delsummer. Det endelige valget i rullegardinmenyen er Inkluder filtrerte elementer i totaler. Normalt er dette valget nedtonet. Men fordi dataene er lagret i datamodellen i stedet for en vanlig pivotbuffer, er dette alternativet nå tilgjengelig.
Velg alternativet Inkluder filtrerte gjenstander i totaler, og totalsummen inkluderer nå en stjerne og totalen av alle dataene, som vist nedenfor.
Dette magiske celle-trikset kom opprinnelig til meg fra Dan i seminaret mitt i Philadelphia og ble gjentatt 15 år senere av en annen Dan enn seminaret mitt i Cincinnati. Takk til Miguel Caballero for å foreslå denne funksjonen.
