Maak ad-hoctotalen voor een Excel-gegevensset waarin u de gegevens gaat filteren. Ook in deze video: SUBTOTAAL (109,) gebruiken voor het optellen van gegevens die handmatig verborgen rijen zijn geweest. Gebruik Ctrl + Ditto om de exacte formule één rij naar beneden te kopiëren.
Nadat u een filter heeft toegepast, zou het geweldig zijn om het totaal van de zichtbare cellen te zien.
Selecteer de lege cel onder elk van uw numerieke kolommen. Klik op de knop AutoSum.
In plaats van SUM-formules in te voegen, voegt Excel =SUBTOTAL(9,… )formules in. De onderstaande formule toont u het totaal van alleen de zichtbare cellen.
Voeg een paar lege rijen in boven uw gegevens. Knip de formules van onder de gegevens en plak ze in rij 1 met het label Total Visible.
Als u nu de filters wijzigt, ziet u de totalen bovenaan uw werkblad, zelfs als de gegevens meer dan één volledig scherm met gegevens vullen.
Met dank aan Sam Radakovitz van het Excel-team voor Filter op selectie - niet voor het voorstellen van Filter op selectie, maar voor het formaliseren van Filter op selectie!
Bekijk video
- Maak ad-hoctotalen voor een Excel-gegevensset waarin u de gegevens gaat filteren. Ook in deze video: SUBTOTAAL (109,) gebruiken voor het optellen van gegevens die handmatig verborgen rijen zijn geweest. Gebruik Ctrl + Ditto om de exacte formule één rij naar beneden te kopiëren.
Videotranscriptie
De podcast wordt gesponsord door "MrExcel XL", een boek met 40+ Excel-tips, Excel-cartoons, cocktails, tweets en grappen.
Leer Excel van podcast, aflevering 1976 - Total the Visible Cells!
Hé, welkom terug bij de netcast, ik ben Bill Jelen. Natuurlijk is iedereen, de allereerste formule die de meeste mensen maken, de AutoSum. Je bent hier beneden een kolom met getallen, je krijgt de AutoSom-knop en deze zal de somfunctie gebruiken om de getallen erboven bij elkaar te tellen, totdat het een formule of tekst tegenkomt. Rechtsaf? Of Alt + = doet precies hetzelfde. Of, nog beter, selecteer meerdere cellen en klik vervolgens op AutoSom, en het zal al die formules plaatsen, zonder dat u de formules hoeft te bevestigen.
Maar hier is de coole truc, oké, dus laten we het AutoFilter aanzetten. Dus we gaan de Filter by Selection-truc gebruiken, van de podcast van gisteren. De gegevens worden gefilterd. Ik ga naar de allereerste lege rij onder de gegevens en druk vervolgens op de knop AutoSum. Maar er gebeurt iets heel anders, ziet u. In plaats van de SOM-functie krijgen we = SUBTOTAAL (9! En wat dit is, dit is het Totaal van de zichtbare cellen. Oké, heel gaaf dat dat werkt.
Nu hier is het ding, ik deed bijna tien jaar geleden een live power Excel-seminar, in Fort Wayne, IN, voor het Institute of Internal Auditors. En iemand heeft me deze truc laten zien, en ik hou echt van de truc. Ik had de truc nog nooit eerder gezien, het was een coole, ik zei dat dit geweldig is, ik ga dit toevoegen aan het seminar. En het jaar daarop was ik weer terug in Fort Wayne, en de manager van die kerel komt opdagen, juist, hij was het eerste jaar niet gekomen, omdat hij dacht dat hij alles wist van Excel. Maar hij kwam terug om deze truc te claimen en hij zei: "Kijk, ik hoor dat mijn financieel analist je deze truc heeft verteld, maar hij vertelde je het beste niet, ga boven je gegevens uit en voeg daar twee rijen in. als je eenmaal het totaal aantal zichtbare cellen onderaan hebt, knippen, CTRL + X en plakken, CTRL + V, oké, wat dat doet is,als je overschakelt naar een andere klant: "Oké, dus ik kies hier nog een paar klanten." En nu heb ik meer klanten dan er op het scherm passen! En ik hoef niet naar beneden te scrollen om de totalen te zien, de totalen staan altijd bovenaan! "
Oké, deze truc, met behulp van de AutoSom onder filtergegevens, werkt wanneer je de rijen verbergt met het filter. Wat als u de rijen handmatig verbergt? Dus hier heb ik een paar rijen geselecteerd, ging al naar Opmaak, Verbergen & zichtbaar maken, Rijen verbergen! Dus nu hebben we rijen verborgen. En ik kom naar beneden, en ik druk op Alt + = of de AutoSom, en het geeft me niet de SUBTOTAAL-functie, het geeft me de SOM-functie.
Oké, op dit punt ga ik iets speciaals doen, ik ga CTRL + 'doen (idem, apostrof). Het zal exact dezelfde formule naar beneden halen, zonder de celverwijzing te wijzigen. Het is dus de E2: E8 die is gewijzigd, of meestal niet is gewijzigd, als je zou kopiëren en plakken naar een wijziging in E3: E9. Ik ga dit veranderen in SUBTOTAAL (9, en je zult zien dat het niet werkt, het geeft ons nog steeds alle cellen. 1000 + 400 + 800 is niet 4500, het is inclusief de verborgen rijen. Maar hier is wat je moet doen, ga in CTRL + 'en verander die 9, in een 109, en je krijgt: The True Total Visible Based on Manually Hidden Rows!
Oké, alsjeblieft, dus een paar verschillende trucs, AutoSum, Alt + = geeft ons gewoon de SOM-functie. Als u zich onder de gegevens bevindt en u drukt op AutoSum, krijgt u het SUBTOTAAL (9, wat uitstekend werkt voor gefilterd, of u kunt gewoon handmatig het SUBTOTAAL typen (109, om het totaal van de zichtbare, wanneer je hebt handmatig gefilterd.
Oké, hier is een samenvatting van de concepten in de video van vandaag: dit is trouwens niet eens tip # 3 in het boek, dit is juist bonustip # 2b, dus tussen tip 2 en tip 3. Het boek behandelt al deze artikelen. Dank aan iedereen die dit heeft voorgesteld, de mensen van het Fort Wayne Indiana Chapter van The IIA, is waar ik het voor het eerst hoorde. Het is een geweldig boek, je kunt het in gedrukte vorm of in e-book kopen, klik op die "i" in de rechterbovenhoek. U zou naar meer informatie over het boek worden geleid.
OK, ik wil je bedanken voor het langskomen, we zien je de volgende keer voor een nieuwe netcast van!
