Denne episoden viser tre måter å kjøre totalsummer på.
En løpende sum er, for en liste over numeriske verdier, en sum av verdiene fra første rad til rad med løpende total. Vanlige bruksområder for en løpende sum er i et sjekkhefteregister eller et regnskapsark. Det er mange måter å lage en løpende-to av som er beskrevet nedenfor.
Den enkleste teknikken er å på hver rad legge til løpende total fra raden over til verdien i raden. Så den første formelen i rad 2 er:
=SUM(D1,C2)
Årsaken til at vi bruker SUM-funksjonen er at vi i første rad ser på overskriften i raden ovenfor. Hvis vi bruker den enklere, mer intuitive formelen til, =D1+C2vil det genereres en feil fordi overskriftsverdien er tekst versus numerisk. Det magiske er at SUM-funksjonen ignorerer tekstverdier, som legges til som nullverdier. Når formelen kopieres ned til alle radene der det ønskes en løpende sum, justeres cellereferansene tilsvarende:
Den andre teknikken bruker også SUM-funksjonen, men hver formel summerer alle verdiene fra den første raden til raden som viser den totale summen. I dette tilfellet bruker vi et dollartegn ($) for å gjøre den første cellen i referansen til en absolutt referanse, noe som betyr at den ikke blir justert når den kopieres:
Begge teknikkene påvirkes ikke av sortering og sletting av rader, men når du setter inn rader, må formelen kopieres til de nye radene.
Excel 2007 introduserte tabellen som er en reimplementering av listen i Excel 2003. Tabeller introduserte en rekke svært nyttige funksjoner for datatabeller som formatering, sortering og filtrering. Med introduksjonen av tabeller fikk vi også en ny måte å referere til delene av et bord på. Denne nye referansestilen kalles strukturert referanse.
For å konvertere eksemplet ovenfor til en tabell, velger vi dataene vi ønsker å ta med i tabellen og trykker på Ctrl + T. Etter å ha vist en ledetekst som ber oss om å bekrefte tabellens rekkevidde, og om det er eksisterende overskrifter, konverterer Excel data i et formatert bord:
Merk at formlene vi skrev inn tidligere er de samme.
En av de nyttige funksjonene som Tables tilbyr, er automatisk formatering og formelvedlikehold når rader blir lagt til, fjernet, sortert og filtrert. Det er spesielt formelen vedlikehold som vi vil fokusere på, og som kan være problematisk. For å holde tabellene i arbeid mens de manipuleres, bruker Excel beregnede kolonner som er kolonner med formler som kolonne D i eksemplet ovenfor. Når nye rader er satt inn i bunnen, fyller Excel automatisk de nye radene med "standard" -formelen for den kolonnen. Problemet med eksemplet ovenfor er at Excel blir forvekslet med standardformler og ikke alltid håndterer dem riktig. Dette blir tydelig når nye rader legges til i bunnen av tabellen (ved å velge cellen nederst til høyre i tabellen og trykke på TAB):
Denne mangelen løses ved å bruke den nyere strukturerte referansen. Strukturert referanse eliminerer behovet for å referere til spesifikke celler ved hjelp av referansestilen A1 eller R1C1, og bruker i stedet kolonnenavn og andre nøkkelord for å identifisere og referere til delene i en tabell. For eksempel, for å opprette den samme løpende totale formelen som brukt ovenfor, men ved hjelp av strukturert referanse har vi:
=SUM(INDEX((Sales),1):(@Sales))
I dette eksemplet har vi en referanse til kolonnenavnet, "Salg", sammen med at-tegnet (@) for å referere til raden i kolonnen der formelen ligger, som også er kjent som den nåværende raden.
For å implementere det første eksemplet ovenfor der vi la den løpende totale verdien i forrige rad til salgsbeløpet i den gjeldende raden, kan du bruke OFFSET-funksjonen:
=SUM(OFFSET((@(Running Total)),-1,0),(@Sales))
Hvis beløpene som brukes til å beregne den løpende summen, er i to kolonner, for eksempel en for "Debiteringer" og en for "Credits", så er formelen:
=SUM(INDEX( (Credit),1):(@Credit))- SUM(INDEX( (Debit),1):(@Debit))
Her bruker vi INDEX-funksjonen til å finne den første radens kreditt- og debetceller, og oppsummere hele kolonnen til og med den nåværende radens verdier. Den løpende summen er summen av alle kreditter til og med gjeldende rad minus summen av alle debetene til og med den gjeldende raden.
For mer informasjon om strukturerte referanser spesielt og Tabeller generelt, anbefaler vi boken Excel Tables: A Complete Guide for Creating, Using and Automating Lists and Tables av Zack Barresse og Kevin Jones.
Da jeg ba leserne om å stemme på favorittipsene sine, var bordene populære. Takk til Peter Albert, Snorre Eikeland, Nancy Federice, Colin Michael, James E. Moede, Keyur Patel og Paul Peton for å foreslå denne funksjonen. Peter Albert skrev det lesbare referansetipset. Zack Barresse skrev Running Totals bonustips. Fire lesere foreslo å bruke OFFSET for å lage utvidende områder for dynamiske diagrammer: Charley Baak, Don Knowles, Francis Logan og Cecelia Rieb. Tabeller gjør nå det samme i de fleste tilfeller.
Se på video
- Denne episoden viser tre måter å kjøre totalsummer på
- Den første metoden har en annen formel i rad 2 enn alle de andre radene
- Den første metoden er = Venstre i rad 2 og = Venstre + Opp i rad 3 til N
- Hvis du prøver å bruke samme formel, får du en #Value-feil med = Total + Number
- Metode 2 bruker
=SUM(Up,Left)eller=SUM(Previous Total,This Row Amount) - SUM ignorerer tekst, slik at du ikke får en VALUE feil
- Metode 3 bruker et utvidende område:
=SUM(B$2:B2) - Utvidende områder er kule, men de er sakte
- Les Charles Williams-papiret om Excel Formula Speed
- Den tredje metoden er et problem når du bruker Ctrl + T og legger til nye rader
- Excel kan ikke finne ut hvordan formelen skal skrives
- Løsningene krever litt kunnskap om strukturert referanse i tabeller
- Løsning 1 er treg
=SUM(INDEX((Qty),1):(@Qty)) - Løsning 2 er den flyktige
=SUM(OFFSET((@Total),-1,0),(@Qty)) - (@Qty) refererer til Antall på denne raden
- (Antall) refererer til alle Antall-verdier
Videoutskrift
Lær Excel for Podcast, episode 2004 - Running Totals
Jeg podcaster hele denne boka. Klikk på jeg øverst til høyre for å abonnere.
Hei velkommen tilbake til den mystiske celle netcast. Jeg er Bill Jelen. Nå dette emnet i boka, ble jeg bidratt av min venn Zach Parise. Snakk om Excel-tabeller, Zach er verdens ekspert på Excel-tabeller. Han har skrevet en bok om Excel-tabeller, men la oss først snakke om å kjøre summer, ikke i tabeller.
Så når jeg tenker på å kjøre totaler, er det tre forskjellige måter å gjøre løpesummen på, og måten jeg alltid startet med er på første rad, du bare sier, ta verdien over. Så lik hva som helst til venstre for meg. Greit så dette formatet her er bare = B2. Alt dette er formeltekst her i høyre hjørne slik at du ser hva vi bruker, og derfra og nedover er det en enkel liten formel som er lik den forrige verdien, pluss den nåværende verdien til høyre og kopier den ned , men du vet nå, vi har dette problemet at det krevde to forskjellige formler, og du vet at i en perfekt situasjon har du nøyaktig samme formel helt ned, og grunnen til at vi må ha en annen formel der på første rad er at når du prøver å legge til lik 7 pluss ordet totalt, er det en verdifeil,men den kule arbeideren her ute, er å ikke bare bruke venstre pluss opp, men å bruke = (SUM) av forrige verdi pluss mengden i denne raden, og se noen er langt nok til å ignorere tekster. Rett slik at det tillater den samme formelen. hele veien ned.
Greit så det var da jeg begynte å bruke Excel, jeg brukte det, og så oppdaget jeg det utvidende området, det utvidende området sier at vi skal gjøre L $ 2: L2 og hva som skjer er at dette alltid begynner på rad 2, men så går det ned til gjeldende rad. Så når du ser på hvordan dette fungerer når det blir kopiert, startet vi alltid rad 2, men vi går ned til den nåværende raden, og dette ble min favorittmetode. Jeg var som, åh, dette er så mye mer sofistikert, og når vi går inn i Excel-alternativer, gå til Formulas-fanen og velg R1C1 i referansestil. Greit, R1C1, alle disse formlene er nøyaktig de samme helt ned. Jeg vet ikke om du forstår R1C1, det er bare godt å vite at vi har identiske R1C1-formler helt ned.
La oss gå tilbake. Så denne metoden her er metoden som jeg likte, til helt til Charles Williams, en Excel MBP fra England, som har et fantastisk papir om formelhastighet, Excel-formelhastighet, fullstendig avkreftet denne metoden. Denne metoden, la oss si at du har 10.000 rader dette, hver eneste formel ser på to referanser. Så du ser på 20.000 referanser, men denne, dette ser på to, dette ser på tre, dette ser på fire, dette ser på fem og den siste ser på 10.000 referanser, og det er fryktelig tregere og så sluttet jeg å bruke denne metoden.
Så fortsetter jeg med å lese Zack i Kevin Jones bok om Excel-tabeller, og jeg oppdager enda et problem med denne metoden. Så en av de nyttige funksjonene tabellene tilbyr, er 'automatisk formatering og formelvedlikeholdsrader blir lagt til, fjernet, sortert og filtrert'. Ok, det er et sitat fra boka hans. Og for å legge til en rad i en tabell, går du bare til den aller siste cellen på bordet og trykker på fanen. Så alt fungerer her. Vi er helt nede i 70, det er kjempebra, og så vil A104 og jeg skal sette inn 100 her. Greit, slik at 70 skulle endres til 170 og det gjør det, men denne 70 burde ikke ha endret seg i det hele tatt. Greit 68 + 2 er ikke en 170. Jeg gjør det igjen. En 104 og satte ytterligere hundre i den siste er riktig. Disse to har ikke rett. Ok, så vi har en merkelig situasjon at hvis duNår du bruker denne formelen, og du konverterer til tabell, begynner du å legge til rader, den løpende summen vil ikke fungere. Hvor ille er det?
Ok, så Zack tilbyr to arbeidsomgivelser, og begge krever litt kunnskap om hvordan strukturreferanser fungerer. Vi skal bare ha en ny kolonne her ute, og hvis jeg ville gjøre mengde, like stor, rett, slik at = (@ Antall) sier antall i denne raden. Oh kult, vel, det er en annen type referanse der vi bruker Antall uten @. Sjekk ut dette. Så = SUM (INDEKS ((Antall), 1: (@ Antall)) betyr alle mengdene, og vi vil si at vi vil SUM fra første mengde, så (INDEKS ((Antall), 1 sier første verdi her, ned til gjeldende radmengde, og dette bruker en virkelig spesiell versjon av indeksen, når indeksen blir fulgt av et kolon, endres det faktisk til en cellehenvisning. OK, denne løsningen er dessverre i strid med Charles Williams-regelen av, vi 'Vi må se på hver eneste referanse, og når du får 10.000 rader, vil dette gå veldig, veldig sakte.
Zach har en annen løsning som ikke bryter med Charles Williams-problemet, men den bruker den fryktede OFFSET. OFFSET er en ustabil funksjon, så hver gang du beregner noe, vil OFFSET beregne på nytt og alt nedover fra OFFSET kommer til å beregne på nytt. Det er bare en fin måte å fullstendig skru opp formlene dine, og hva dette gjør, det sier, vi tar totalen fra denne raden, går opp en rad, over null kolonner, og så hva det gjør er å si: ta tak i totalen fra forrige rad, og så legger vi til mengden fra denne raden. Ok, så nå ser det på to referanser hver gang, men dessverre introduserer OFFSET ustabile funksjoner.
Vel, der har du det, mer enn du noen gang ønsket å vite om Running Totals. Jeg antar at min endelige mening her er å bruke denne metoden, fordi den bare ser ut som den to. Samme formel helt ned, og de strukturerte tabellreferansene vil fungere.
For denne utforskningen og 39 andre virkelig gode tips, sjekk ut denne boken XL, de 40 største Excel-tips gjennom tidene.
Sammendrag for denne episoden vi snakket om tre måter å gjøre løpesummer på. Den første metoden har en annen formel, rad 2, enn alle de andre radene. Det er lik venstre i rad 2 og deretter lik venstre pluss opp i rad 3 til N, men hvis du prøver og bare bruker den samme formelen, lik venstre pluss opp, helt ned, hvordan du får en #Value Error . Så = SUM (opp, venstre), som er totalt totalt, pluss denne veikartet, som fungerer bra, ingen verdifeil og deretter det utvidende området som jeg elsker å elske. De er kule, men til jeg leser Charles Williams-papir om Excel i form av hastighet. Så begynte jeg å hate disse ekspanderende referansene. Det har også et problem når du bruker CTRL T og legger til nye rader. Excel kan ikke finne ut hvordan man utvider den formelen, hvordan man legger til nye rader. Jeg elsker dette tipset, gå til den aller siste cellen i tabellen og trykk på Tab,som vil legge til en ny rad, og så snakket vi om noen strukturerte referanser, der vi bruker antall i denne raden og deretter alle mengder. = SUM (OFFSET ((@ totalt), - 1,00, (@ antall)).
Ok, jeg vil takke Zach for at du bidro med det tipset. Jeg vil takke deg for at du var innom. Vi sees neste gang for en ny netcast fra.
Last ned fil
Last ned eksempelfilen her: Podcast2004.xlsx
