Kan Excel een filter toepassen op gegevens die zijn gesubtotaliseerd? Dit artikel laat zien hoe.
Bekijk video
- RA vraagt? Kun je gegevens filteren die zijn gesubtotaliseerd?
- Hoe kun je ervoor zorgen dat de subtotalen bovenaan elke groep verschijnen?
- Schakel Samenvatting onder gegevens uit wanneer u de subtotalen maakt
- Kunt u een filter gebruiken in een dataset die subtotalen heeft ondergaan?
- Ja, maar u moet ook lege cellen opnemen
- Hoe kun je ervoor zorgen dat de percentageberekening werkt in de subtotaalrijen?
- Op dit moment is het percentage het totaal van de andere rijen in plaats van de berekening uit te voeren
- Kopieer na het toevoegen van subtotalen de percentageberekening van een rij zonder subtotalen naar alle andere rijen.
Videotranscriptie
Leer Excel van Podcast, aflevering 2174: filter subtotaalgegevens.
Oké, de vraag van vandaag van RA. Hij zegt: "Iemand heeft me een dataset gestuurd met subtotalen bovenaan elke groep. Hoe doen ze dat? En dan creëerden ze ondertitels die je kunt filteren. Hoe kun je dat doen? En dan, later, hoe kunnen we de procentuele berekening op de subtotaalrij goed laten werken? "
Het mooie van de functie Subtotaal - = SUBTOTAAL 9 - is dat het andere subtotalen in de set negeert. Maar het sluit ook altijd uitgefilterde cellen uit als u het filter gebruikt. Als iemand de rijen handmatig heeft verborgen, zou u 109 moeten gebruiken in plaats van 9. Maar in dit geval hebben we het alleen over filters, dus het zou vrij eenvoudig moeten zijn.
Dus laten we beginnen. We hebben vandaag echt drie dingen te doen. We moeten subtotalen toevoegen bovenaan de groep. Dus we gaan naar het tabblad Gegevens, hier ga ik ondertitels van klanten toevoegen, dus het eerste wat ik doe is Sorteren op klant, waarop deze gegevens al zijn gesorteerd, en klik vervolgens bij elke wijziging op het commando Subtotaal in Klant. We gaan de functie SOM gebruiken - ik ga het aan alle vijf toevoegen, ook al gaat dit brutowinstpercentage niet werken - en als ik wil dat de totalen bovenaan verschijnen voor elke dataset verwijder ik het vinkje bij 'Samenvatting onder gegevens, klik op OK. En wat ik krijg is: hier zijn ABC Stores en het totaal van de ABC Stores wordt daarbovenop weergegeven, oké? Dus zo krijg je de subtotalen bovenop.
Vervolgens willen we deze gegevens kunnen filteren. En dus ga ik filteren op basis van product, dus ik kies een cel, klik op het filterpictogram en ik wil alleen de totalen voor ABC zien. Oké. Dus in eerste instantie kom ik hier binnen en kies ik alleen ABC, zo klik ik op OK. En ik realiseerde me dat ik de ABC-records zie, maar nu zie ik de totalen niet. Dus de truc hier zal zijn: als ik alle ABC-records en de totalen voor product ABC wil zien, kies ik zowel ABC als (blanco), omdat de totalen verschijnen waar het product is blanco, oké?
Dus nu zie ik dat AT&T twee verschillende records voor ABC had, en laten we hier een kleine test doen. Kies die twee cellen, totaal 23.978, en ja hoor, het werkt - 23.978. Als ik dit filter zou wissen, is het totaal van AT & T $ 498.000. Maar de 23.000 van Product ABC. Oké, dus laten we dit opnieuw proberen voor een ander record. Kies DEF, kies (Blanks) en klik op OK. En we zien dat AT & T - alle DEF-records, waar ze veel van kopen - 237.000 is. Dus, inderdaad, de filteropdracht werkt met het subtotaal. Oké, wat cool is dat dat werkt.
Maar wat hier niet werkt, is het brutowinstpercentage. Oké, dus we hebben deze vier records voor ABC Stores, en het telt ze op - 225% … En het zou niet eens correct zijn om dit in een gemiddelde te veranderen. Het zou gemiddeld deze 4 zijn, maar als je, weet je, grotere bestellingen en kleinere bestellingen had, is dat niet het juiste antwoord. En om dat te bewijzen … dus hier is het gemiddelde 56,3. Nu, wat we gaan doen is, we nemen een van de formules die werken - dus G4 gedeeld door E4 - we gaan die formule kopiëren, en we gaan hem overal plakken de hele kolom, oké? Inclusief de totalen, dus ik zal plakken. En we zien dat het gemiddelde brutowinstpercentage, met behulp van deze cijfers hier - winst en omzet, niet 56,3 is, maar in feite 56%.Dit is een van die regels waarbij je niet zomaar het gemiddelde van de totaalrij kunt nemen, zeker niet de som. Maar door dezelfde percentageformule die u gebruikt voor de detailrijen naar de subtotaalrijen te kopiëren, werkt het.
Oké. Mijn boek, Power Excels with, Edition 2017, bevat veel subtotale onderwerpen. Als je een subtotaal-fan bent, zul je dit boek geweldig vinden. Klik op die "I" in de rechterbovenhoek.
Oké. Vandaag, RA: kun je gegevens filteren die zijn gesubtotaliseerd? Ja, eigenlijk kan dat. Het gaat gewoon werken, op voorwaarde dat je ook de lege cel toevoegt. Hoe zorg je ervoor dat de subtotalen bovenaan elke groep verschijnen? Wanneer u de subtotalen maakt, schakelt u het selectievakje uit voor een samenvatting onder gegevens. En dan: "Hé, waarom werken de percentageberekeningen niet in de subtotaalrijen?" Nou, omdat het een van die berekeningen is die je opnieuw moet doen in de rij Subtotaal. Kies dus gewoon een van de percentageberekeningen uit een gedetailleerde rij en kopieer deze naar de andere rijen.
Nou, ik wil RA bedanken voor het insturen van die vraag, en ik wil je bedanken voor het langskomen. Ik zie je de volgende keer voor nog een netcast van.
Download bestand
Download het voorbeeldbestand hier: Podcast2174.xlsm
