De draaitabel Top 10 Filter geeft een totaal van de zichtbare rijen
Draaitabellen bieden een Top 10-filter. Het is cool. Het is flexibel. Maar ik haat het, en ik zal je vertellen waarom.
Hier is een draaitabel met de omzet per klant. De totale omzet is $ 6,7 miljoen.
Wat als mijn manager de aandachtsspanne heeft van een goudvis en alleen de top vijf klanten wil zien?
Open om te beginnen de vervolgkeuzelijst in A3 en selecteer Waardefilters, Top 10.
Het superflexibele Top 10-filterdialoogvenster maakt Top / Bottom mogelijk. Het kan 10, 5 of een ander nummer doen. U kunt vragen naar de top vijf items, top 80%, of genoeg klanten om $ 5 miljoen te krijgen.
Maar hier is het probleem: het resulterende rapport toont vijf klanten en het totaal van die klanten in plaats van de totalen van iedereen.
Maar eerst een paar belangrijke woorden over AutoFilter
Ik realiseer me dat dit een ongebruikelijke vraag lijkt. Als u de vervolgkeuzelijsten voor filters op een normale gegevensset wilt inschakelen, hoe doet u dat dan? Hier zijn drie veelvoorkomende manieren:
- Selecteer een cel in uw gegevens en klik op het filterpictogram op het tabblad Gegevens.
- Selecteer al uw gegevens met Ctrl + * en klik op het filterpictogram op het tabblad Gegevens.
- Druk op Ctrl + T om de gegevens als een tabel op te maken.
Dit zijn drie echt goede manieren. Zolang u ze kent, hoeft u absoluut geen andere manier te kennen. Maar hier is een ongelooflijk obscure maar magische manier om het filter in te schakelen:
- Ga naar je rij met kopteksten, ga naar de meest rechtse koptekstcel. Verplaats een cel naar rechts. Om een onbekende reden, wanneer u zich in deze cel bevindt en op het filterpictogram klikt, filtert Excel de gegevensset links van u. Ik heb geen idee waarom dit werkt. Het is echt niet de moeite waard om erover te praten, want er zijn al drie echt goede manieren om de vervolgkeuzelijsten voor filters in te schakelen. Ik noem deze cel de Magische cel.
En nu, terug naar draaitabellen …
Er is dus een regel die zegt dat u de AutoFilters niet kunt gebruiken wanneer u zich in een draaitabel bevindt. Zie hieronder? Het filterpictogram is grijs omdat ik een cel in de draaitabel heb geselecteerd.
Ik heb er nooit echt over nagedacht waarom Microsoft dit grijs maakt. Het moet iets intern zijn dat zegt dat AutoFilter en een draaitabel niet naast elkaar kunnen bestaan. Er is dus iemand in het Excel-team die verantwoordelijk is voor het grijs maken van het filterpictogram. Die persoon heeft nog nooit van de Magische cel gehoord. Selecteer een cel in de draaitabel en het filter wordt grijs weergegeven. Klik buiten de draaitabel en Filter wordt weer ingeschakeld.
Maar wacht. Hoe zit het met de Magische cel waarover ik je net vertelde? Als u in de cel rechts van de laatste kop klikt, vergeet Excel het filterpictogram grijs te maken!
En ja hoor, Excel voegt AutoFilter-vervolgkeuzemenu's toe aan de bovenste rij van uw draaitabel. En het AutoFilter werkt anders dan draaitabelfilters. Ga naar de vervolgkeuzelijst Opbrengsten en kies Nummerfilters, Top 10 …
Kies Top 6 items in het dialoogvenster Top 10 AutoFilter. Dat is geen typfout…. Als u vijf klanten wilt, kiest u 6. Als u tien klanten wilt, kiest u 11.
Voor AutoFilter is de rij met het eindtotaal het grootste item in de gegevens. De top vijf klanten bezetten de posities 2 tot en met 6 in de data.
Voorzichtigheid
Het is duidelijk dat je met deze truc een gat in de stof van Excel scheurt. Als u later de onderliggende gegevens wijzigt en uw draaitabel vernieuwt, zal Excel het filter niet vernieuwen, omdat er, voor zover Microsoft weet, geen manier is om een filter op een draaitabel toe te passen!
Opmerking
Ons doel is om dit geheim te houden voor Microsoft, want het is best wel cool. Het is al geruime tijd "gebroken", dus er zijn veel mensen die er nu misschien op vertrouwen.
Een volledig legale oplossing in Excel 2013+Als u een draaitabel wilt met de top vijf van klanten, maar het totaal van alle klanten, moet u uw gegevens buiten Excel verplaatsen. Als u Excel 2013 of 2016 heeft, is er een erg handige manier om dit te doen. Om u dit te laten zien, heb ik de originele draaitabel verwijderd. Kies Invoegen, draaitabel. Voordat u op OK klikt, selecteert u het vakje met de tekst Deze gegevens toevoegen aan het gegevensmodel.
Bouw uw draaitabel zoals gewoonlijk. Gebruik de vervolgkeuzelijst in A3 om Waardefilters, Top 10 te selecteren en vraag naar de vijf beste klanten. Ga met één cel in de draaitabel geselecteerd naar het tabblad Ontwerpen in het lint en open de vervolgkeuzelijst Subtotalen. De laatste keuze in de vervolgkeuzelijst is Gefilterde items opnemen in totalen. Normaal gesproken wordt deze keuze grijs weergegeven. Maar omdat de gegevens worden opgeslagen in het gegevensmodel in plaats van in een normale pivot-cache, is deze optie nu beschikbaar.
Kies de optie Gefilterde items opnemen in totaal en uw eindtotaal bevat nu een asterisk en het totaal van alle gegevens.
Deze truc kwam oorspronkelijk van Dan tijdens mijn seminar in Philadelphia. Met dank aan Miguel Caballero voor het voorstellen van deze functie.
Bekijk video
- De draaitabel Top 10 Filter geeft een totaal van de zichtbare rijen
- Gefilterde items opnemen in totalen wordt grijs weergegeven
- Vreemde manier om het gegevensfilter op te roepen vanuit de magische cel
- Gegevensfilters zijn niet toegestaan in draaitabellen
- Excel slaagt er niet in om het gegevensfilter van de magische cel grijs te maken
- Vraag naar de top 6 om de top 5 plus het eindtotaal te krijgen
- Handig om te filteren op een specifiek spilitem
- Excel 2013 of nieuwer: andere manier om het ware totaal te krijgen
- Stuur uw gegevens via het datamodel
- Inclusief gefilterde items in totalen is beschikbaar
- Krijg Total met asterisk
- Ik heb deze truc 10+ jaar geleden geleerd van Dan in Philadelphia
Videotranscriptie
Leer Excel voor podcast, aflevering 1999 - draaitabel echte top vijf
Ik podcast dit hele boek. Er is een afspeellijst, klik op de I in de rechterbovenhoek om die afspeellijst te volgen. Welkom terug bij de netcast. Ik ben Bill Jelen.
Oké, dus we gaan een draaitabel maken en we willen niet alle klanten laten zien, maar alleen de vijf beste klanten. INSERT, draaitabel. Oké, ik zet Klant aan de linkerkant en Opbrengst. Oké, hier is onze hele lijst met klanten, genoteerd op 6,7 miljoen dollar. Excel, maakt het gemakkelijk om een top vijf te maken. Ga naar Rijlabels, Waardefilters, top 10. Hoeft niet bovenaan te staan. Het kan boven of onder zijn. Hoeft geen vijf te zijn. Het kunnen er twintig, veertig zijn, het kan van alles zijn. Top tachtig procent, geef me genoeg records om op drie miljoen dollar of vier miljoen dollar te komen, maar hier gaan we. Top vijf items. Onthoud nu 6,7 miljoen dollar, klik op OK en mijn grote probleem hier is dat dat totaal niet de 6,7 miljoen is. Als ik dit aan de VP van verkoop geef, zal hij in paniek raken en zeggen, wacht even,Ik weet dat ik meer dan 3,3 miljoen dollar heb gedaan. Goed, dus we gaan het ongedaan maken, dat ongedaan maken en teruggaan naar de oorspronkelijke gegevens.
Nu deze volgende truc die ik heb geleerd tijdens een van mijn Power Excel-seminars in Philadelphia. Een man genaamd Dan in rij twee, liet me dit zien. Het is meer dan tien jaar geleden dat hij me deze truc liet zien, en eerst moeten we het hebben over de filters. Dus normaal gesproken, als u het gewone filter gaat gebruiken, dit filter hier, kiest u een cel in uw gegevensset en klikt u op het filterpictogram, of sommige mensen kiezen de hele gegevensset, CONTROL * en klikken op het filterpictogram, maar er is een derde manier. Een manier waar niemand om geeft. Als je naar de allerlaatste cel gaat, in mijn geval, is dat de kosten in L1, en ga je een cel naar rechts. Ik noem dit de magische cel, ik heb geen idee waarom, maar om een onbekende reden kan ik vanuit deze cel de aangrenzende dataset filteren. Oké, het is een rare manier en niemand geeft hier om.
Juist, omdat er twee andere echt goede manieren zijn om een filter op te roepen, hoeft niemand iets te weten over de magische cel, maar hier is het ding: kijk aan de binnenkant van een draaitabel, het is grijs. U mag die filters niet gebruiken. Het is tegen de regels. Nu, als ik hier naar buiten kom, ben ik meer dan welkom om het filter te gebruiken, maar van binnen scoren ze. Ik weet niet wie de persoon is die dit uitgegrijsd heeft, maar ze hebben mijn praatje over de magische cel nog nooit gehoord, want als ik naar de allerlaatste Heading Cell ga en een cel naar rechts ga, kijk daar dan eens naar, ze vergeten het filter grijs te maken en nu heb ik zojuist de oude autofilters aan de draaitabel toegevoegd. Dus ik kom hier, ga naar Nummerfilters, dat is anders dan Waardefilters. Het heet nog steeds Top Tien. Iets anders, ik ga naar de top vijf vragen, niet naar de top zes.De top zes omdat voor dit filter het eindtotaal gewoon een andere rij is, en het eindtotaal het grootste item is, en als ik vervolgens naar de items 2 tot en met 6 wordt gevraagd, krijg ik de top vijf.
Oké, daar zijn we dan. Een coole Filter-hack, die ons de top vijf van items en het echte totaal van iedereen geeft. Oké, een paar dingen. Vergeet de magische cel niet. Oké, er is geen manier om dit filter uit te schakelen, tenzij je teruggaat naar de magische cel. Oké, je moet de magische cel onthouden. Als u de onderliggende gegevens wijzigt en de draaitabel vernieuwt, wordt het filter niet vernieuwd, omdat u, voor zover Microsoft weet, geen filter mag hebben.
Dit is handig voor andere dingen. Soms hebben we producten die over de top gaan. Laten we hier naar een tabel gaan. Niet nodig, ik vind het gewoon leuk om echte rubrieken te krijgen. Gizmo, Widget, Gadgets, Doodads. Oké en misschien ben je de manager van Doodads en moet je alleen de klanten zien die een bepaalde waarde hadden en Doodads. Dus ik ga naar de magische cel, zet het filter aan en dan kan ik onder Doodads vragen om items die groter zijn dan nul. Klik OK. Oké, dat type filtering zou niet mogelijk zijn op een gewone draaitabel, maar het is mogelijk met behulp van de magische cel.
Oké, laten we de lijst ongedaan maken. Laten we dit filter uitschakelen en de draaitabel verwijderen, en als u zich in Excel 2013 of nieuw bevindt, zal ik u een volledig legale manier laten zien om het juiste totaal onderaan te krijgen. Voeg de draaitabel in, hier onderaan, vanaf Excel 2013 klinkt dit zeer onschadelijke vak niet erg spannend, voeg deze gegevens toe aan het gegevensmodel. Dat stuurt de gegevens achter de schermen naar de Power Pivot Engine. Bouw exact hetzelfde rapport. Klanten aan de linkerkant. Omzet in het hart van de draaitabel. Ga dan naar de reguliere Filters, de Waarde Filters top 10. Vraag naar de top vijf. Merk nogmaals op dat we 6,7 miljoen dollar hebben nadat ik dit heb gedaan, 3,3 miljoen dollar, maar hier is het verschil. Wanneer ik naar het Ontwerptabblad ga, onder Subtotalen, wordt deze functie genaamd Inclusief gefilterde items in totalen,wordt niet langer grijs weergegeven. Aan een gewone draaitafel is niet beschikbaar. We krijgen daar een kleine asterisk en het is het totaal van alles. Oké, dat werkt nu natuurlijk alleen in Excel 2013 of nieuwer.
Oké, het gaat zes weken duren voordat ik dit hele boek hier op YouTube krijg. Er zijn hier zoveel goede tips. Tips die u meteen tijd kunnen besparen. Koop nu het hele boek en je hebt toegang tot alle 40, het zijn eigenlijk veel meer dan 40 tips. Excel-sneltoetsen. Allerlei geweldige dingen in dit boek.
Oké, samenvatting. Dus als we een draaitabel top 10-filter doen, geeft het ons het totaal, maar alleen de zichtbare rijen, niet de dingen die het eruit filterde. Ja, als we naar het tweede tabblad gaan en zoeken naar subtotalen, gefilterde items en totalen, wordt het grijs weergegeven, maar er is een vreemde manier om het oude gegevensfilter op te roepen vanuit de magische cel. De allerlaatste kopcel, ga een cel naar rechts, je kunt geen filters en draaitabellen gebruiken, maar als je naar de magische cel gaat, vergeten ze het grijs te maken. Nu vraag je in het nummerfilter naar de top zes om de top vijf te krijgen, plus het totaal. Ook handig voor het filteren op een specifiek draaipunt: Doodads, alles dat groter was dan 0 in Doodads of top 5 Doodads. Excel 2013 of nieuwer, er is een andere manier om het True Total te krijgen.Vink dat vakje aan voor het gegevensmodel en neem vervolgens gefilterde items op in totalen zal beschikbaar zijn. U krijgt het totaal met een asterisk. En dank aan Dan in Philadelphia die me meer dan tien jaar geleden op een van mijn Power Excel-seminars liet zien en me deze geweldige kleine truc gaf. Een manier voor het filter om door de Club Pivot Table Wall te sluipen. Normaal gesproken staan ze dat automatische filter niet toe.
Hé, ik wil je bedanken voor het langskomen. We zien je de volgende keer, voor nog een netcast van MRExcel.
Download bestand
Download het voorbeeldbestand hier: Podcast1999.xlsx
