Pivottabellen Topp 10 Filter gir totalt de synlige radene
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 inntektene etter kunde. Inntektene er totalt $ 6,7 millioner.
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.
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 kategorien Data.
- 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, gå til den øverste cellen. 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 Magic-cellen.
Og nå, Tilbake til pivottabeller …
Så 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 har aldri tenkt på hvorfor Microsoft gråter dette ut. 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 Magic-cellen. Velg en celle i pivottabellen, og filteret blir nedtonet. Klikk utenfor pivottabellen, og Filter er aktivert igjen.
Men vent. Hva med Magic-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 pivottabelfiltre. Gå til rullegardinmenyen Inntekter og velg Nummerfiltre, 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, vil ikke Excel oppdatere filteret, for 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 utenfor Excel. Hvis du har Excel 2013 eller 2016, 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 boksen som sier 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 elementer i totalt, og din totalsum inkluderer nå en stjerne og totalen av alle dataene.
Dette trikset kom opprinnelig til meg fra Dan i seminaret mitt i Philadelphia. Takk til Miguel Caballero for å foreslå denne funksjonen.
Se på video
- Pivottabellen Topp 10 Filter gir totalt de synlige radene
- Inkluder filtrerte gjenstander i totaler er gråtonet
- Merkelig måte å påkalle datafilteret fra den magiske cellen
- Datafiltre er ikke tillatt i pivottabeller
- Excel klarer ikke å gråte ut datafilteret fra den magiske cellen
- Be om topp 6 for å få topp 5 pluss Grand Total
- Nyttig for filtrering etter et bestemt pivotelement
- Excel 2013 eller nyere: Annerledes måte å få den virkelige summen på
- Send dataene dine gjennom datamodellen
- Inkluder filtrerte gjenstander i totaler vil være tilgjengelig
- Få total med stjerne
- Jeg lærte dette trikset for 10+ år siden av Dan i Philadelphia
Videoutskrift
Lær Excel for Podcast, episode 1999 - Pivottabell True Top Five
Jeg podcaster hele denne boka. Det er en spilleliste, klikk på I øverst til høyre for å følge spillelisten. Velkommen tilbake til netcast. Jeg er Bill Jelen.
Greit, så vi skal lage et pivottabell, og vi vil vise, ikke alle kundene, men bare de fem beste kundene. INSERT, Pivot Table. Ok, jeg legger kunden fra venstre side og inntekt. Greit så her er hele listen over kunder som er oppgitt som 6,7 millioner dollar. Excel, gjør det enkelt å gjøre topp fem. Gå inn i Radetiketter, Verdifilter, topp 10. Trenger ikke å være topp. Det kan være topp eller bunn. Trenger ikke å være fem. Det kan være tjue, førti, det kan være hva som helst. Topp åtti prosent, gi meg nok poster til å komme til tre millioner dollar eller fire millioner dollar, men her går vi. Topp fem elementer. Husk nå 6,7 millioner dollar, klikk OK og mitt store problem her er at den totale summen ikke er 6,7 millioner. Når jeg gir dette til salgsdirektøren, kommer han til å freak ut og si, vent et sekund,Jeg vet at jeg gjorde mer enn 3,3 millioner dollar. Ikke sant, så vi skal angre, angre det og gå tilbake til de opprinnelige dataene.
Nå er dette neste trikset jeg lærte under et av Power Excel-seminarene mine i Philadelphia. En fyr som heter Dan på rad to, viste meg dette. Det var for mer enn ti år siden at han viste meg dette trikset, og først må vi snakke om filtrene. Så hvis du skal bruke det vanlige filteret, dette filteret her, velger du en celle i datasettet ditt og klikker på filterikonet, eller noen velger hele datasettet, KONTROLL * og klikker på filterikonet, men det er en tredje vei. En måte som ingen bryr seg om. Hvis du går til den aller siste Heading Cell, i mitt tilfelle, er det Cost i L1, og går en celle til høyre. Jeg kaller dette den magiske cellen, jeg aner ikke hvorfor, men av en ukjent grunn, fra denne cellen, kan jeg filtrere det tilstøtende datasettet. Ok, det er som en merkelig måte, og ingen bryr seg om dette.
Ikke sant, fordi det er to andre veldig gode måter å påkalle et filter på, trenger ingen å vite om den magiske cellen, men her er tingen, se innsiden av et pivottabell, den er gråtonet. Du har ikke lov til å bruke disse filtrene. Det er mot reglene. Nå, hvis jeg kommer ut hit, er jeg mer enn velkommen til å bruke filteret, men inni rangerer de. Jeg vet ikke hvem personen er som gråter dette, men de har aldri hørt min lille snakk om den magiske cellen, for hvis jeg går til den aller siste Heading Cell og går en celle til høyre, se på det, de glemmer å gråne ut filteret, og nå har jeg nettopp lagt til de gamle autofiltrene i pivottabellen. Så jeg kommer hit, går til Number Filters, det er annerledes enn Value Filters. Det kalles fortsatt topp ti. Litt annerledes, jeg kommer til å be om de fem beste, nei de seks beste.De seks beste fordi til dette filteret er totalsummen bare en rad, og totalsummen er den største varen, og når jeg blir bedt om varene 2 til 6, får jeg de fem beste varene.
Ok, så der er vi. Et kult filterhack som gir oss de fem beste elementene og den sanne summen av alle. Greit nå, et par ting. Ikke glem den magiske cellen. Ok, det er ingen måte å slå av dette filteret, med mindre du går tilbake til den magiske cellen. Greit så du må huske den magiske cellen. Hvis du endrer de underliggende dataene og oppdaterer pivottabellen, vil de ikke oppdatere filteret, for så vidt Microsoft vet, har du ikke lov til å ha et filter.
Dette er nyttig for andre ting. Noen ganger har vi produkter som går over toppen. La oss gå her i tabellform. Ikke nødvendig, jeg liker bare å få ekte overskrifter. Gizmo, Widget, Gadgets, Doodads. Greit, og kanskje du er leder for Doodads, og du må se bare kundene som hadde en spesiell verdi og Doodads. Så jeg går til den magiske cellen, slår på filteret og deretter under Doodads kan jeg be om ting som er større enn null. Klikk OK. Ok, den typen filtrering ville ikke være mulig på et vanlig pivottabell, men det er mulig å bruke den magiske cellen.
OK, la oss angre listen. La oss slå av dette filteret og fjerne pivottabellen, og hvis du er i Excel 2013 eller ny, skal jeg vise deg en helt lovlig måte å få riktig total nederst. Sett inn pivottabellen, her nede nederst, og starter i Excel 2013 denne veldig uskyldige boksen, høres ikke veldig spennende ut, legg til disse dataene i datamodellen. Det sender dataene bak kulissene til Power Pivot Engine. Bygg nøyaktig samme rapport. Kunder nede på venstre side. Inntekter i hjertet av pivottabellen. Gå deretter til de vanlige filtre, verdifiltrene topp 10. Be om de fem beste. Legg igjen merke til at vi har 6,7 millioner dollar etter at jeg har gjort dette, 3,3 millioner dollar, men her er forskjellen. Når jeg går til Design-fanen, under Delsummer, kalles denne funksjonen Inkluder filtrerte gjenstander i totaler,er ikke lenger nedtonet. På et vanlig pivottabell er ikke tilgjengelig. Vi får en liten stjerne der, og det er summen av alt. Greit, nå fungerer det selvfølgelig bare i Excel 2013 eller nyere.
Greit det vil ta seks uker for meg å få hele denne boka ut her på YouTube. Det er så mange gode tips her. Tips som kan begynne å spare tid, med en gang. Kjøp hele boka akkurat nå, så får du tilgang til alle 40, det er faktisk mye mer enn 40 tips. Excel hurtigtaster. Alle slags flotte ting i denne boka.
Greit, oppsummering. Så når vi gjør et Pivot Table top 10-filter, gir det oss totalt, men bare de synlige radene, ikke tingene som det filtrerte ut. Ja, hvis vi går til den andre kategorien og ser etter delsummer, filtrerte gjenstander og totaler, er den nedtonet, men det er en merkelig måte å påkalle det gamle datafilteret fra den magiske cellen. Den aller siste overskriftscellen, gå en celle til høyre, du kan ikke bruke filtre og pivottabeller, men hvis du går til den magiske cellen glemmer de å gråne den ut. Nå i tallfilteret ber du om de seks beste for å få topp fem, pluss totalsummen. Også nyttig for filtrering til et bestemt Pivot-element: Doodads, alt som hadde større enn 0 i Doodads eller topp 5 Doodads. Excel 2013 eller nyere, det er en annen måte å få True Total på.Merk av for datamodellen, og inkluder deretter filtrerte gjenstander i totaler. Du får totalen med en stjerne. Og takk til Dan i Philadelphia som viste meg på et av Power Excel-seminarene mine for mer enn ti år siden, og ga meg dette flotte lille trikset. En måte for filteret å snike seg gjennom Club Pivot Table Wall. De tillater normalt ikke det automatiske filteret.
Hei, jeg vil takke deg for at du var innom. Vi sees neste gang, for en ny netcast fra MRExcel.
Last ned fil
Last ned eksempelfilen her: Podcast1999.xlsx
