Denne episode viser tre måder at køre totaler på.
En løbende total er for en liste over numeriske værdier en sum af værdierne fra den første række til rækken af den løbende total. Almindelige anvendelser af et løbende total er i et kontrolbogsregister eller et regnskabsark. Der er mange måder at oprette i alt to på, hvoraf de er beskrevet nedenfor.
Den enkleste teknik er at på hver række tilføje det samlede løb fra rækken ovenfor til værdien i rækken. Så den første formel i række 2 er:
=SUM(D1,C2)
Årsagen til, at vi bruger SUM-funktionen, er, at vi i første række ser på overskriften i rækken ovenfor. Hvis vi bruger den enklere, mere intuitive formel, =D1+C2genereres der en fejl, fordi headerværdien er tekst versus numerisk. Det magiske er, at SUM-funktionen ignorerer tekstværdier, der tilføjes som nulværdier. Når formlen kopieres ned til alle de rækker, hvor en løbende total ønskes, justeres cellereferencerne i overensstemmelse hermed:
Den anden teknik bruger også SUM-funktionen, men hver formel opsummerer alle værdierne fra den første række til rækken, der viser det samlede løb. I dette tilfælde bruger vi et dollartegn ($) til at gøre den første celle i referencen til en absolut reference, hvilket betyder, at den ikke justeres, når den kopieres:
Begge teknikker påvirkes ikke af sortering og sletning af rækker, men når der indsættes rækker, skal formlen kopieres til de nye rækker.
Excel 2007 introducerede tabellen, som er en genimplementering af listen i Excel 2003. Tabeller introducerede en række meget nyttige funktioner til datatabeller såsom formatering, sortering og filtrering. Med introduktionen af tabeller fik vi også en ny måde at henvise til delene i en tabel på. Denne nye referencestil kaldes struktureret referencer.
For at konvertere ovenstående eksempel til en tabel, vælger vi de data, vi vil medtage i tabellen, og trykker på Ctrl + T. Efter at have vist en prompt, der beder os om at bekræfte tabellens rækkevidde, og om der er eksisterende overskrifter eller ikke, konverterer Excel dataene i en formateret tabel:
Bemærk, at de formler, vi indtastede tidligere, er de samme.
En af de nyttige funktioner, som Tables tilbyder, er automatisk formatering og formelvedligeholdelse, når rækker tilføjes, fjernes, sorteres og filtreres. Det er især formelvedligeholdelsen, som vi vil fokusere på, og som kan være problematisk. For at holde tabeller i gang, mens de manipuleres, bruger Excel beregnede kolonner, der er kolonner med formler som kolonne D i ovenstående eksempel. Når der indsættes nye rækker i bunden, udfylder Excel automatisk de nye rækker med "standard" -formlen for den pågældende kolonne. Problemet med ovenstående eksempel er, at Excel bliver forvirret med standardformler og ikke altid håndterer dem korrekt. Dette gøres klart, når nye rækker føjes til bunden af tabellen (ved at vælge den nederste højre celle i tabellen og trykke på TAB):
Denne mangel løses ved hjælp af den nyere strukturerede henvisning. Struktureret henvisning eliminerer behovet for at henvise til specifikke celler ved hjælp af A1- eller R1C1-referencestil og bruger i stedet kolonnenavne og andre nøgleord til at identificere og henvise til delene i en tabel. For eksempel, for at oprette den samme kørende samlede formel, der er brugt ovenfor, men ved hjælp af struktureret henvisning har vi:
=SUM(INDEX((Sales),1):(@Sales))
I dette eksempel har vi en henvisning til kolonnenavnet "Salg" sammen med at-tegnet (@) for at henvise til rækken i den kolonne, hvor formlen er placeret, som også er kendt som den aktuelle række.
For at implementere det første eksempel ovenfor, hvor vi tilføjede den løbende samlede værdi i den foregående række til salgsbeløbet i den aktuelle række, kan du bruge OFFSET-funktionen:
=SUM(OFFSET((@(Running Total)),-1,0),(@Sales))
Hvis beløbene, der bruges til at beregne det samlede beløb, er i to kolonner, for eksempel en til "Debiteringer" og en til "Kreditter", er formlen:
=SUM(INDEX( (Credit),1):(@Credit))- SUM(INDEX( (Debit),1):(@Debit))
Her bruger vi INDEX-funktionen til at finde den første række kredit- og debetceller og opsummere hele kolonnen til og med den aktuelle række værdier. Det samlede løb er summen af alle kreditter til og med den aktuelle række minus summen af alle debet til og med den aktuelle række.
For mere information om strukturerede referencer i særdeleshed og tabeller generelt anbefaler vi bogen Excel-tabeller: En komplet guide til oprettelse, brug og automatisering af lister og tabeller af Zack Barresse og Kevin Jones.
Da jeg bad læserne om at stemme på deres yndlings tip, var tabeller populære. Tak til Peter Albert, Snorre Eikeland, Nancy Federice, Colin Michael, James E. Moede, Keyur Patel og Paul Peton for at foreslå denne funktion. Peter Albert skrev det tip, der kan læses om referencer. Zack Barresse skrev Running Totals bonustip. Fire læsere foreslog at bruge OFFSET til at skabe voksende intervaller til dynamiske diagrammer: Charley Baak, Don Knowles, Francis Logan og Cecelia Rieb. Tabeller gør nu det samme i de fleste tilfælde.
Se video
- Denne episode viser tre måder at køre totaler på
- Den første metode har en anden formel i række 2 end alle de andre rækker
- Den første metode er = Venstre i række 2 og = Venstre + Op i række 3 til N
- Hvis du prøver at bruge den samme formel, får du en #Value-fejl med = Total + antal
- Metode 2 bruger
=SUM(Up,Left)eller=SUM(Previous Total,This Row Amount) - SUM ignorerer tekst, så du ikke får en VALUE fejl
- Metode 3 bruger et ekspanderende interval:
=SUM(B$2:B2) - Udvidede intervaller er seje, men de er langsomme
- Læs hvidbogen fra Charles Williams om Excel Formula Speed
- Den tredje metode er et problem, når du bruger Ctrl + T og tilføjer nye rækker
- Excel kan ikke finde ud af, hvordan man skriver formlen
- Løsningerne kræver noget kendskab til struktureret henvisning i tabeller
- Løsning 1 er langsom
=SUM(INDEX((Qty),1):(@Qty)) - Løsning 2 er den flygtige
=SUM(OFFSET((@Total),-1,0),(@Qty)) - (@Qty) refererer til Antal i denne række
- (Antal) refererer til alle antal værdier
Videoudskrift
Lær Excel til Podcast, afsnit 2004 - Kører totaler
Jeg podcaster hele denne bog. Klik på at jeg øverst til højre for at abonnere.
Hej velkommen tilbage til den mystiske celle netcast. Jeg er Bill Jelen. Nu dette emne i bogen blev jeg bidraget af min ven Zach Parise. Tal om Excel-tabeller, Zach er verdens ekspert på Excel-tabeller. Han har skrevet en bog om Excel-tabeller, men lad os først tale om at køre totaler ikke i tabeller.
Så når jeg tænker på at køre totaler, er der tre forskellige måder at lave løbende totaler på, og den måde, som jeg altid startede med, er i første række, du siger bare, bring værdien over. Så lige hvad der er til venstre for mig. Okay, så dette format her er bare = B2. Disse er alle formeltekster her i højre hjørne, så du ser, hvad vi bruger, og derefter derfra og ned er det en simpel lille formel, der svarer til den tidligere værdi plus den aktuelle værdi til højre og kopier den ned , men du ved nu, vi har dette problem, at det krævede to forskellige formler, og du ved i en perfekt situation, at du har nøjagtig den samme formel hele vejen ned, og grunden til at vi skal have en anden formel der i første række er at når du prøver at tilføje lige 7 plus ordet total, er det en værdifejl,men den seje arbejdstager herude er ikke bare at bruge venstre plus op, men at bruge = (SUM) af den tidligere værdi plus mængden i denne række, og se nogle er langt nok til at ignorere tekster. Højre så det tillader den samme formel. Hele vejen ned.
Okay så det var da jeg begyndte at bruge Excel, jeg brugte det, og så opdagede jeg det ekspanderende interval, det ekspanderende område siger, at vi skal gøre L $ 2: L2, og hvad der sker er, at dette altid starter ved række 2 men så går det ned til den aktuelle række. Så når du ser på, hvordan dette fungerer, når det bliver kopieret, startede vi altid række 2, men vi går ned til den aktuelle række, og dette blev min yndlingsmetode. Jeg var ligesom, åh, dette er så meget mere sofistikeret, og når vi går ind i Excel-indstillinger, skal du gå til fanen Formler og vælge R1C1 i referencestil. Okay, R1C1, alle disse formler er nøjagtig de samme helt ned. Jeg ved ikke, om du forstår R1C1, det er bare godt at vide, at vi har identiske R1C1-formler helt ned.
Lad os gå tilbage. Så denne metode her er den metode, som jeg kunne lide, indtil indtil Charles Williams, en Excel MBP fra England, der har et fantastisk papir om formelhastighed, Excel-formelhastighed, fuldstændig afskaffede denne metode. Denne metode, lad os sige, at du har 10.000 rækker dette, hver enkelt formel ser på to referencer. Så du kigger på 20.000 referencer, men denne, dette ser på to, dette ser på tre, dette ser på fire, dette ser på fem og den sidste ser på 10.000 referencer, og det er forfærdeligt langsommere og så stoppede jeg med at bruge denne metode.
Så fortsætter jeg med at læse Zack i Kevin Jones bog om Excel-tabeller, og jeg opdager endnu et problem med denne metode. Så en af de nyttige funktioner, som tabellerne tilbyder, er 'automatisk formatering og formelvedligeholdelsesrækker tilføjes, fjernes, sorteres og filtreres'. Okay, det er et citat fra hans bog. Og for at tilføje en række til en tabel skal du bare gå til den sidste celle på bordet og trykke på fanen. Så alt fungerer her. Vi er nede på 70 lige det er fantastisk, og så A104 og jeg sætter en 100 her. Okay, så 70 skulle ændre sig til 170, og det gør det, men denne 70 skulle overhovedet ikke have ændret sig. Okay 68 + 2 er ikke en 170. Jeg gør det igen. En 104 og læg yderligere hundrede i den sidste er rigtig. Disse to er ikke rigtige. Okay, så vi har en underlig situation, hvis du 'genbruger du denne formel, og du konverterer til tabel, begynder du at tilføje rækker, det løbende total fungerer ikke. Hvor slemt er det?
Okay, så Zack tilbyder to work-ups, og begge kræver lidt viden om, hvordan strukturreferencer fungerer. Vi skal bare have en ny kolonne herude, og hvis jeg ville gøre mængde, lige stor, lige så at = (@ Antal) siger mængde i denne række. Åh sejt, ja der er en anden slags reference, hvor vi bruger antallet uden @. Se lige det her. Så = SUM (INDEX ((Qty), 1: (@ Qty)) betyder alle mængderne, og vi vil sige, at vi vil SUM fra den første mængde, så (INDEX ((Qty), 1 siger første værdi her, ned til den aktuelle række mængde, og dette bruger en virkelig speciel version af indeks, når indeks efterfølges af et kolon, ændres det faktisk til en cellehenvisning. Okay, denne løsning er desværre i strid med Charles Williams-reglen af, vi 'bliver nødt til at se på hver eneste reference, og så når du får 10.000 rækker, vil dette gå rigtig langsomt.
Zach har en anden løsning, der ikke krænker Charles Williams-problemet, men det bruger den frygtede OFFSET. OFFSET er en ustabil funktion, så hver gang du beregner noget, vil OFFSET genberegne og alt nedad fra OFFSET vil genberegne. Det er bare en fantastisk måde at fuldstændigt skrue dine formler op, og hvad dette gør, det siger, vi tager det samlede beløb fra denne række, går op en række, over nul kolonner, og så hvad det laver siger: tag fat i summen fra den foregående række, og derefter tilføjer vi mængden fra denne række. Okay, så nu ser det alle på to referencer hver gang, men desværre introducerer OFFSET ustabile funktioner.
Nå, der har du det, mere end du nogensinde har ønsket at vide om Running Totals. Jeg antager, at min endelige mening her er at bruge denne metode, fordi den kun ser den ud to. Samme formel helt ned, og dine strukturerede tabelhenvisninger fungerer.
For denne udforskning og 39 andre rigtig gode tip, tjek denne bog XL, de 40 største Excel-tip nogensinde.
Resumé for denne episode, vi talte om tre måder at lave løbende totaler på. Den første metode har en anden formel, række 2, end alle de andre rækker. Det er lige venstre i række 2 og derefter lige venstre plus op i række 3 til N, men hvis du prøver og bare bruger den samme formel, lige venstre plus op, helt ned, hvordan du får en #Value Error . Så = SUM (op, venstre), som er tidligere i alt plus denne køreplan, der fungerer godt, ingen værdifejl og derefter det ekspanderende interval, som jeg elsker at elske. De er seje, men indtil jeg læser Charles Williams hvidbog om Excel-form af hastighed. Så begyndte jeg at hade disse ekspanderende referencer. Det har også et problem, når du bruger CTRL T og tilføjer nye rækker. Excel kan ikke finde ud af, hvordan man udvider formlen, hvordan man tilføjer nye rækker. Jeg elsker dette tip, gå til den sidste celle i tabellen og tryk på Tab,der tilføjer en ny række, og så talte vi om nogle strukturerede henvisninger, hvor vi bruger mængde i denne række og derefter alle mængder. = SUM (OFFSET ((@ i alt), - 1,00, (@ antal)).
Okay, jeg vil gerne takke Zach for at bidrage med dette tip. Jeg vil gerne takke dig, fordi du kom forbi. Vi ses næste gang til endnu en netcast fra.
Download fil
Download eksempelfilen her: Podcast2004.xlsx
