Det här avsnittet visar tre sätt att göra löpande summor.
En löpande summa är, för en lista med numeriska värden, en summa av värdena från första raden till raden av den löpande summan. Vanliga användningar av en löpande summa finns i ett checkbokregister eller ett redovisningsblad. Det finns många sätt att skapa en total summa av vilka två beskrivs nedan.
Den enklaste tekniken är att på varje rad lägga till den löpande summan från raden ovan till värdet i raden. Så den första formeln i rad 2 är:
=SUM(D1,C2)
Anledningen till att vi använder SUM-funktionen är att vi i den första raden tittar på rubriken i raden ovan. Om vi använder den enklare, mer intuitiva formeln för =D1+C2då genereras ett fel eftersom rubrikvärdet är text kontra numeriskt. Det magiska är att SUM-funktionen ignorerar textvärden som läggs till som nollvärden. När formeln kopieras ner till alla rader där en löpande summa önskas justeras cellreferenserna i enlighet med detta:
Den andra tekniken använder också SUM-funktionen, men varje formel summerar alla värden från första raden till raden som visar den totala körningen. I det här fallet använder vi ett dollartecken ($) för att göra den första cellen i referensen till en absolut referens vilket innebär att den inte justeras när den kopieras:
Båda teknikerna påverkas inte av att sortera och radera rader, men när du sätter in rader måste formeln kopieras till de nya raderna.
Excel 2007 introducerade tabellen som är en återimplementering av listan i Excel 2003. Tabeller introducerade ett antal mycket användbara funktioner för datatabeller som formatering, sortering och filtrering. Med introduktionen av tabeller fick vi också ett nytt sätt att hänvisa till delarna i ett bord. Denna nya referensstil kallas strukturerad referens.
För att konvertera exemplet ovan till en tabell väljer vi de data vi vill inkludera i tabellen och trycker på Ctrl + T. Efter att ha visat en uppmaning att be oss bekräfta tabellens intervall och om det finns befintliga rubriker eller inte konverterar Excel data till en formaterad tabell:
Observera att formlerna vi angav tidigare är desamma.
En av de användbara funktionerna som Tabeller erbjuder är automatisk formatering och formelunderhåll när rader läggs till, tas bort, sorteras och filtreras. Det är särskilt formelunderhållet som vi kommer att fokusera på och som kan vara problematiska. För att hålla tabellerna fungerande medan de manipuleras använder Excel beräknade kolumner som är kolumner med formler som kolumn D i exemplet ovan. När nya rader läggs till läggs till i botten fyller Excel automatiskt de nya raderna med "standard" -formeln för den kolumnen. Problemet med ovanstående exempel är att Excel blir förvirrad med standardformler och inte alltid hanterar dem korrekt. Detta framgår när nya rader läggs till längst ner i tabellen (genom att markera den nedre högra cellen i tabellen och trycka på TAB):
Denna brist löses genom att använda den nyare strukturerade referensen. Strukturerad referens eliminerar behovet av att referera till specifika celler med hjälp av referensstilen A1 eller R1C1 och använder istället kolumnnamn och andra nyckelord för att identifiera och referera till delarna i en tabell. Till exempel, för att skapa samma löpande totala formel som används ovan men med hjälp av strukturerad referens har vi:
=SUM(INDEX((Sales),1):(@Sales))
I det här exemplet har vi en hänvisning till kolumnnamnet ”Försäljning” tillsammans med at-tecknet (@) för att referera till raden i kolumnen där formeln finns, även känd som den aktuella raden.
För att implementera det första exemplet ovan där vi lade till det löpande totala värdet i föregående rad till försäljningsbeloppet i den aktuella raden kan du använda OFFSET-funktionen:
=SUM(OFFSET((@(Running Total)),-1,0),(@Sales))
Om beloppen som används för att beräkna den löpande summan finns i två kolumner, till exempel en för "Debiteringar" och en för "Krediter", är formeln:
=SUM(INDEX( (Credit),1):(@Credit))- SUM(INDEX( (Debit),1):(@Debit))
Här använder vi INDEX-funktionen för att hitta den första radens kredit- och debiteringsceller och summera hela kolumnen till och med den aktuella radens värden. Den löpande summan är summan av alla krediter till och med den aktuella raden minus summan av alla debiteringar till och med den aktuella raden.
För mer information om strukturerade referenser i synnerhet och tabeller i allmänhet rekommenderar vi boken Excel-tabeller: En komplett guide för att skapa, använda och automatisera listor och tabeller av Zack Barresse och Kevin Jones.
När jag bad läsarna att rösta på sina favorittips var borden populära. Tack till Peter Albert, Snorre Eikeland, Nancy Federice, Colin Michael, James E. Moede, Keyur Patel och Paul Peton för att föreslå den här funktionen. Peter Albert skrev läsbar referensbonus. Zack Barresse skrev bonustipsen Running Totals. Fyra läsare föreslog att man skulle använda OFFSET för att skapa växande intervall för dynamiska diagram: Charley Baak, Don Knowles, Francis Logan och Cecelia Rieb. Tabeller gör nu samma sak i de flesta fall.
Kolla på video
- Det här avsnittet visar tre sätt att göra löpande summor
- Den första metoden har en annan formel i rad 2 än alla andra rader
- Den första metoden är = Vänster i rad 2 och = Vänster + Upp i rader 3 till N
- Om du försöker använda samma formel får du ett #Value-fel med = Total + Number
- Metod 2 använder
=SUM(Up,Left)eller=SUM(Previous Total,This Row Amount) - SUM ignorerar text så att du inte får ett VALUE-fel
- Metod 3 använder ett expanderande intervall:
=SUM(B$2:B2) - Expanderande intervall är coola men de är långsamma
- Läs Charles Williams whitepaper om Excel Formula Speed
- Den tredje metoden är ett problem när du använder Ctrl + T och lägger till nya rader
- Excel kan inte ta reda på hur man skriver formeln
- Lösningarna kräver viss kunskap om strukturerad referens i tabeller
- Lösning 1 är långsam
=SUM(INDEX((Qty),1):(@Qty)) - Lösning 2 är den flyktiga
=SUM(OFFSET((@Total),-1,0),(@Qty)) - (@Qty) hänvisar till Antal på denna rad
- (Antal) avser alla antal värden
Videoutskrift
Lär dig Excel för Podcast, avsnitt 2004 - Running Totals
Jag podcastar hela denna bok. Klicka på jag i det övre högra hörnet för att prenumerera.
Hej välkommen tillbaka till mystic cell netcast. Jag heter Bill Jelen. Nu detta ämne i boken, jag bidrog av min vän Zach Parise. Prata om Excel-tabeller, Zach är världens expert på Excel-tabeller. Han har skrivit en bok om Excel-tabeller, men låt oss först prata om att köra totala inte i tabeller.
Så när jag tänker på att köra totala, finns det tre olika sätt att göra löpande summor, och det sätt som jag alltid började med är på första raden, du säger bara, ta värdet över. Så lika vad som helst till vänster om mig. Okej så det här formatet här är bara = B2. Dessa är alla formeltexter här i det högra hörnet så att du ser vad vi använder, och därifrån och ner är det en enkel liten formel som är lika med det tidigare värdet plus det aktuella värdet till höger och kopierar det ner , men du vet nu, vi har det här problemet att det krävde två olika formler och du vet att i en perfekt situation har du exakt samma formel hela vägen ner, och anledningen till att vi måste ha en annan formel där i första raden är att när du försöker lägga till lika med 7 plus ordet total är det ett värdefel,men den coola arbetaren här ute är att inte bara använda vänster plus upp, utan att använda = (SUM) av det tidigare värdet plus kvantiteten i den här raden, och se några är tillräckligt långt för att ignorera texter. Rätt så att det tillåter samma formel. hela vägen ner.
Okej så det var när jag började använda Excel, jag använde det och sedan upptäckte jag det expanderande intervallet, det expanderande intervallet säger att vi ska göra L $ 2: L2 och vad som händer är att detta alltid börjar på rad 2, men sedan går det ner till den aktuella raden. Så när du tittar på hur detta fungerar när det kopieras började vi alltid rad 2, men vi går ner till den aktuella raden och detta blev min favoritmetod. Jag var som, åh, det här är så mycket mer sofistikerat och när vi går in i Excel-alternativ, gå till fliken Formler och välj R1C1 i referensstil. Okej se, R1C1, alla dessa formler är exakt samma hela vägen. Jag vet inte om du förstår R1C1, det är bara bra att veta att vi har identiska R1C1-formler hela vägen.
Låt oss gå tillbaka. Så den här metoden här är den metod som jag gillade tills Charles Williams, en Excel-MBP från England, som har ett fantastiskt papper om formelhastighet, Excel-formelhastighet, helt avskedade denna metod. Den här metoden, låt oss säga att du har 10 000 rader, varje formel tittar på två referenser. Så du tittar på 20 000 referenser, men den här, den här tittar på två, den här tittar på tre, den här tittar på fyra, den här tittar på fem och den sista tittar på 10 000 referenser, och det är fruktansvärt långsammare och så slutade jag använda den här metoden.
Sedan läser jag Zack i Kevin Jones bok om Excel-tabeller och jag upptäcker ännu ett problem med den här metoden. Så en av de användbara funktionerna som tabellerna erbjuder är "automatisk formatering och formelunderhållsrader läggs till, tas bort, sorteras och filtreras". Okej det är ett citat från hans bok. Och för att lägga till en rad i en tabell går du bara till den sista cellen på bordet och trycker på fliken. Så allt fungerar här. Vi är nere på 70 rätt, det är fantastiskt och sedan A104 och jag lägger in en 100 här. Okej, så att 70 ska ändras till 170 och det gör det, men de här 70 borde inte ha förändrats alls. Okej 68 + 2 är inte en 170. Jag ska göra det igen. En 104 och placera ytterligare hundra i den sista är rätt. Dessa två är inte rätt. Okej, så vi har en konstig situation om duom du använder den här formeln och du konverterar till tabell börjar du lägga till rader, den löpande summan fungerar inte. Hur dåligt är det?
Okej, så Zack erbjuder två arbeten runt och båda kräver lite kunskap om hur strukturreferenser fungerar. Vi kommer bara att ha en ny kolumn här ute och om jag ville göra kvantitet, lika stor, rätt, så att = (@ Antal) säger kvantitet i den här raden. Åh coolt, det finns en annan typ av referens där vi använder antalet utan @. Kolla in det här. Så = SUMMA (INDEX ((Antal), 1: (@ Antal)) betyder alla kvantiteter och vi kommer att säga att vi vill SUMMA från den första kvantiteten, så (INDEX ((Antal), 1 säger första värdet här, ner till den aktuella radkvantiteten, och detta använder en riktigt speciell version av index, när index följs av ett kolon, ändras det faktiskt till en cellreferens. Okej så, den här lösningen bryter tyvärr Charles Williams-regeln av, vi 'Vi måste titta på varje enskild referens, och när du får 10 000 rader kommer detta att gå riktigt, riktigt långsamt.
Zach har en annan lösning som inte bryter mot Charles Williams-problemet, men den använder den fruktade OFFSET. OFFSET är en flyktig funktion, så varje gång du beräknar något kommer OFFSET att räkna om och allt i linje från OFFSET kommer att räkna om. Det är bara ett bra sätt att helt och hållet skruva upp dina formler, och vad det här gör, det säger, vi tar summan från den här raden, går upp en rad, över noll kolumner och så vad det gör är att säga: ta summan från föregående rad och sedan lägger vi till kvantiteten från den här raden. Okej, så nu tittar allt på två referenser varje gång, men tyvärr introducerar OFFSET flyktiga funktioner.
Tja, där har du det, mer än du någonsin velat veta om Running Totals. Jag antar att min slutliga åsikt här är att använda den här metoden, för den ser bara ut den två. Samma formel hela vägen ner och dina strukturerade tabellreferenser fungerar.
För denna utforskning och 39 andra riktigt bra tips, kolla in den här boken XL, de 40 bästa Excel-tipsen genom tiderna.
Sammanfattning för det här avsnittet vi pratade om tre sätt att göra löpande summor. Den första metoden har en annan formel, rad 2, än alla andra rader. Det är lika vänster i rad 2 och sedan lika vänster plus upp i rader 3 till N, men om du försöker bara använda samma formel, lika vänster plus upp, hela vägen ner, hur du kommer att få ett #Value Error . Så = SUMMA (uppåt, vänster), vilket är tidigare totalt, plus den här färdplanen, som fungerar bra, inga värdefel och sedan det växande intervallet som jag brukar älska. De är coola, men tills jag läser Charles Williams vitbok om Excel form av hastighet. Sedan började jag hata dessa expanderande referenser. Det har också ett problem när du använder CTRL T och lägger till nya rader. Excel kan inte ta reda på hur man utvidgar den formeln, hur man lägger till nya rader. Jag älskar det här tipset, gå till den allra sista cellen i tabellen och tryck på Tab,som kommer att lägga till en ny rad och sedan pratade vi om strukturerad referens, där vi använder kvantitet i den här raden och sedan alla kvantiteter. = SUM (OFFSET ((@ Total), - 1,00, (@ Qty)).
Okej, jag vill tacka Zach för att du har bidragit med det tipset. Jag vill tacka dig för att du kom förbi. Vi ses nästa gång för en ny netcast från.
Nedladdning fil
Ladda ner exempelfilen här: Podcast2004.xlsx
