Lag ad-hoc-totaler for et Excel-datasett der du skal filtrere dataene. Også i denne videoen: Bruk SUBTOTAL (109,) for å summere data som har vært manuelt skjulte rader. Bruk Ctrl + Ditto til å kopiere den eksakte formelen ned en rad.
Etter at du har brukt et filter, vil det være flott å se totalt antall synlige celler.
Velg den tomme cellen under hver av de numeriske kolonnene. Klikk på AutoSum-knappen.
I stedet for å sette inn SUM-formler, setter Excel inn =SUBTOTAL(9,… )formler. Formelen nedenfor viser totalt bare de synlige cellene.
Sett inn noen tomme rader over dataene dine. Klipp formlene under dataene og lim inn til rad 1 med etiketten Total Synlig.
Nå som du endrer filtrene, selv om dataene fyller mer enn en full skjerm med data, vil du se totalene øverst på regnearket.
Takk til Sam Radakovitz i Excel-teamet for Filter by Selection - ikke for å foreslå Filter by Selection, men for å formalisere Filter by Selection!
Se på video
- Lag ad-hoc-totaler for et Excel-datasett der du skal filtrere dataene. Også i denne videoen: Bruk SUBTOTAL (109,) for å summere data som har vært manuelt skjulte rader. Bruk Ctrl + Ditto til å kopiere den eksakte formelen ned en rad.
Videoutskrift
Podcasten er sponset av "MrExcel XL", en bok med 40+ Excel-tips, Excel-tegneserier, cocktailer, tweets og vitser.
Lær Excel fra podcast, episode 1976 - Total the Visible Cells!
Hei, velkommen tilbake til netcast, jeg er Bill Jelen. Selvfølgelig er alle, den aller første formelen, som folk flest lager, AutoSum. Du er her nede under en kolonne med tall, du får AutoSum-knappen, og den vil bruke sumfunksjonen til å summe tallene over den, til den møter enten en formel eller tekst. Ikke sant? Eller Alt + = gjør nøyaktig det samme. Eller, enda bedre, velg flere celler og trykk deretter på AutoSum, og det vil sette alle disse formlene, uten at du trenger å bekrefte formlene.
Men her er det kule trikset, greit, så la oss slå på AutoFilter. Så vi skal bruke Filtrer etter utvalg-trikset, fra gårsdagens podcast. Dataene blir filtrert. Jeg skal gå til den aller første tomme raden under dataene, og deretter trykke på AutoSum-knappen. Men det skjer noe helt annet, skjønner du. I stedet for SUM-funksjonen får vi = SUBTOTAL (9! Og hva dette er, dette er Total av de synlige cellene. Greit så, veldig kult at det fungerer.
Nå er det tingen, jeg gjorde et live Power Excel-seminar for snart ti år siden, i Fort Wayne, IN, for Institute of Internal Auditors. Og noen viste meg dette trikset, og jeg elsker virkelig trikset. Jeg hadde aldri sett trikset før, det var kult, jeg sa dette er flott, jeg skal legge dette til seminaret. Og det neste året var jeg tilbake i Fort Wayne, og lederen til den fyren dukket opp, ikke sant, han hadde ikke kommet det første året, fordi han trodde han visste alt om Excel. Men han kom tilbake for å hevde dette trikset, og han sa: "Se, jeg hører at min finansanalytiker fortalte deg dette trikset, men han fortalte deg ikke det beste, gå opp over dataene dine og sett inn to rader der oppe. Og så når du har de totale synlige cellene nederst, klipper du, CTRL + X og limer inn, CTRL + V, greit, hva det gjør er,Hvis du bytter til en annen kunde, "OK, så velger jeg noen flere kunder her." Og nå har jeg flere kunder enn det som passer på skjermen! Og jeg trenger ikke å bla ned til bunnen for å se totalene, totalene er alltid der oppe! "
Greit nå, dette trikset, ved hjelp av AutoSum under filterdata, fungerer når du gjemmer radene med filteret. Hva om du skjuler radene manuelt? Så her valgte jeg noen rader, jeg gikk allerede til Format, Hide & Unhide, Hide Rows! Så nå har vi skjulte rader. Og jeg kommer ned til bunnen, og jeg trykker på Alt + = eller AutoSum, og det gir meg ikke SUBTOTAL-funksjonen, det gir meg SUM-funksjonen.
Akkurat nå, på dette punktet, skal jeg gjøre en spesiell ting, jeg skal gjøre CTRL + '(ditto, apostrof). Det vil bringe nøyaktig samme formel ned, uten å endre cellereferansen. Så det er E2: E8 endret, eller endret seg, vanligvis hvis du ville kopiere og lime inn til en endring til E3: E9. Jeg skal endre dette til SUBTOTAL (9, og du vil se at det ikke fungerer, det gir oss fortsatt alle cellene. 1000 + 400 + 800 er ikke 4500, det inkluderer de skjulte radene. Men her er hva du må gjøre, komme inn i CTRL + 'og endre den 9, til en 109, så får du: The True Total Visible Based on Manually Hidden Rows!
Ok, der går du, så et par forskjellige triks, AutoSum, Alt + = gir oss bare SUM-funksjonen. Hvis du er under dataene og trykker på AutoSum, vil du få SUBTOTAL (9, som fungerer bra for filtrert, eller du kan bare skrive SUBTOTAL manuelt (109, for å få totalen av det synlige når du har filtrert manuelt.
Greit, her er en oppsummering av konseptene i dagens video: Dette er forresten ikke engang tips nr. 3 i boka, dette er bonustips nr. 2b riktig, så mellom tips 2 og tips 3. Boken dekker alle disse gjenstander. Vel takk til alle som foreslo dette, folket på Fort Wayne Indiana Chapter of The IIA, var der jeg først hørte det. Det er en flott bok, du kan kjøpe den på trykk eller e-bok, klikke på "i" øverst til høyre. Du vil bli ført til mer informasjon om boka.
OK, jeg vil takke deg for at du var innom, vi sees neste gang for nok en netcast fra!
