Pivottabellen Top 10 Filter giver i alt de synlige rækker
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.
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.
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å ikonet Filter på fanen Data.
- 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, gå til den øverste celle. 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 …
Så der er en regel, der siger, at du ikke kan bruge autofilterne, når du er i en pivottabel. Se nedenunder? Filterikonet er nedtonet, fordi jeg har valgt en celle i pivottabellen.
Jeg overvejede aldrig rigtig, 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 pivottabelfiltre. 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 temmelig 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 har Excel 2013 eller 2016, 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. Før du klikker på OK, skal du markere det felt, der siger Føj disse data til datamodellen.
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 pivottabellen valgt 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 alt, og din samlede total inkluderer nu en stjerne og summen af alle data.
Dette trick kom oprindeligt til mig fra Dan i mit seminar i Philadelphia. Tak til Miguel Caballero for at foreslå denne funktion.
Se video
- Pivottabellen Top 10 Filter giver i alt de synlige rækker
- Inkluder filtrerede genstande i totaler er nedtonet
- Mærkelig måde at påkalde datafilteret fra den magiske celle
- Datafiltre er ikke tilladt i pivottabeller
- Excel undlader at gråne datafilteret fra den magiske celle
- Bed om top 6 for at få top 5 plus Grand Total
- Nyttig til filtrering efter et specifikt omdrejningspunkt
- Excel 2013 eller nyere: En anden måde at få den rigtige sum på
- Send dine data via datamodellen
- Inkluder filtrerede genstande i totaler vil være tilgængelige
- Få total med en stjerne
- Jeg lærte dette trick for 10+ år siden af Dan i Philadelphia
Videoudskrift
Lær Excel til Podcast, afsnit 1999 - Pivottabel True Top Five
Jeg podcaster hele denne bog. Der er en afspilningsliste, klik på I øverst til højre for at følge afspilningslisten. Velkommen tilbage til netcast. Jeg er Bill Jelen.
Okay, så vi opretter en pivottabel, og vi vil vise ikke alle kunder, men kun de fem bedste kunder. INDSÆT, drejetabel. Okay, jeg lægger kunden ned fra venstre side og indtægter. Okay så her er hele vores liste over kunder noteret som 6,7 millioner dollars. Excel gør det nemt at lave en top fem. Gå ind i rækkeetiketter, værdifiltre, top 10. Behøver ikke at være øverst. Det kan være top eller bund. Behøver ikke være fem. Det kan være tyve, fyrre, det kan være hvad som helst. Top firs procent, giv mig nok poster til at komme til tre millioner dollars eller fire millioner dollars, men her går vi. Top fem varer. Husk nu 6,7 millioner dollars, klik på OK, og mit store problem her er, at den samlede sum ikke er de 6,7 millioner. Når jeg giver dette til VP for salg, vil han freak ud og sige, vent et øjeblik,Jeg ved, at jeg gjorde mere end 3,3 millioner dollars. Ret, så vi vil fortryde, fortryde det og gå tilbage til de originale data.
Nu dette næste trick, jeg lærte under et af mine Power Excel-seminarer i Philadelphia. En fyr ved navn Dan i række to viste mig dette. Det var for mere end ti år siden, at han viste mig dette trick, og først skal vi tale om filtre. Så normalt, hvis du vil bruge det almindelige filter, dette filter her, skal du vælge en celle i dit datasæt og klikke på filterikonet, eller nogle vælger hele datasættet, KONTROL * og klik på filterikonet, men der er en tredje vej. En måde som ingen bryr sig om. Hvis du går til den sidste overskriftscelle, i mit tilfælde er det omkostninger i L1 og går en celle til højre. Jeg kalder dette den magiske celle, jeg aner ikke hvorfor, men af en ukendt grund kan jeg fra denne celle filtrere det tilstødende datasæt. Okay, det er som en underlig måde, og ingen bryr sig om dette.
Ret, fordi der er to andre rigtig gode måder at påberåbe sig et filter på, behøver ingen at vide om den magiske celle, men her er sagen, se inde i en drejetabel, den er nedtonet. Du har ikke tilladelse til at bruge disse filtre. Det er imod reglerne. Nu, hvis jeg kommer ud her, er jeg mere end velkommen til at bruge filteret, men indeni rangerer de. Jeg ved ikke, hvem personen er, der gråter dette, men de har aldrig hørt min lille snak om den magiske celle, for hvis jeg går til den allerførste overskriftscelle og går en celle til højre, se på det, de glemmer at gråne filteret ud, og nu har jeg lige tilføjet de gamle automatiske filtre til pivottabellen. Så jeg kommer her, går til nummerfiltre, det er anderledes end værdifiltre. Det hedder stadig Top Ti. Lidt anderledes vil jeg bede om top fem, nej top seks.De øverste seks, fordi Grand Total til dette filter bare er en anden række, og Grand Total er den største vare, og når jeg bliver bedt om varer 2 til 6, får jeg de fem øverste poster.
Okay, så der er vi. Et sejt filterhack, der giver os de fem bedste ting og den sande sum af alle. Okay, et par ting. Glem ikke den magiske celle. Okay, der er ingen måde at slå dette filter fra, medmindre du går tilbage til den magiske celle. Okay, så du skal huske den magiske celle. Også, hvis du ændrer de underliggende data, og du opdaterer pivottabellen, opdaterer de ikke filteret, for så vidt Microsoft ved, har du ikke lov til at have et filter.
Dette er nyttigt til andre ting. Nogle gange har vi produkter, der går over toppen. Lad os gå her i en tabelform. Ikke nødvendigt, jeg kan bare lide at få rigtige overskrifter. Gizmo, Widget, Gadgets, Doodads. Okay, og måske er du leder af Doodads, og du skal bare se de kunder, der havde en særlig værdi, og Doodads. Så jeg går til den magiske celle, tænder filteret og derefter under Doodads kan jeg bede om ting, der er større end nul. Klik på OK. Okay, denne type filtrering ville ikke være mulig på et almindeligt pivottabel, men det er muligt at bruge den magiske celle.
Okay, lad os fortryde listen. Lad os slukke for dette filter og fjerne pivottabellen, og hvis du er i Excel 2013 eller ny, vil jeg vise dig en helt lovlig måde at få det korrekte total i bunden. Indsæt pivottabel, her nede i bunden, startende i Excel 2013 denne meget uskadelige boks, lyder ikke særlig spændende, tilføj disse data til datamodellen. Det sender dataene bag kulisserne til Power Pivot Engine. Byg nøjagtigt den samme rapport. Kunder nede i venstre side. Omsætning i hjertet af pivottabellen. Gå derefter til de almindelige filtre, Value Filters top 10. Bed om de fem bedste. Bemærk igen, at vi har 6,7 millioner dollars, efter at jeg har gjort dette, 3,3 millioner dollars, men her er forskellen. Når jeg går til fanen Design under subtotaler, kaldes denne funktion Inkluder filtrerede genstande i totaler,er ikke længere nedtonet. Ved en almindelig drejetabel er ikke tilgængelig. Vi får en lille stjerne der, og det er alt i alt. Okay, nu fungerer det naturligvis kun i Excel 2013 eller nyere.
Okay, det tager seks uger for mig at få hele denne bog ud her på YouTube. Der er så mange gode tip her. Tips, der kan begynde at spare dig tid med det samme. Køb hele bogen lige nu, så får du adgang til alle 40, det er faktisk meget mere end 40 tip. Excel-genvejstaster. Alle slags gode ting i denne bog.
Okay, resumé. Så når vi laver et Pivot Table Top 10-filter, giver det os det samlede antal, men kun de synlige rækker, ikke de ting, det filtrerede ud. Ja, hvis vi går til den anden fane og ser efter subtotaler, filtrerede genstande og totaler, er den nedtonet, men der er en underlig måde at påberåbe sig det gamle datafilter fra den magiske celle. Den allerførste overskriftscelle, gå en celle til højre, du kan ikke bruge Filtre og pivottabeller, men hvis du går til den magiske celle, glemmer de at gråne den ud. Nu i nummerfilteret beder du om de seks bedste for at få de fem bedste plus det samlede beløb. Også nyttigt til filtrering til et bestemt drejepunkt: Doodads, alt hvad der havde større end 0 i Doodads eller top 5 Doodads. Excel 2013 eller nyere, der er en anden måde at få True Total på.Marker afkrydsningsfeltet for datamodellen, og inkluder derefter filtrerede emner i totaler, vil være tilgængelige. Du får summen med en stjerne. Og tak til Dan i Philadelphia, der viste mig på et af mine Power Excel-seminarer for mere end ti år siden og gav mig dette fantastiske lille trick. En måde for filteret at snige sig gennem Club Pivot Table Wall. De tillader normalt ikke det automatiske filter.
Hej, jeg vil gerne takke dig for at komme forbi. Vi ses næste gang til endnu en netcast fra MRExcel.
Download fil
Download eksempelfilen her: Podcast1999.xlsx
