Diese Episode zeigt drei Möglichkeiten, um laufende Summen zu erstellen.
Eine laufende Summe ist für eine Liste numerischer Werte eine Summe der Werte von der ersten Zeile bis zur Zeile der laufenden Summe. Übliche Verwendungen einer laufenden Summe finden sich in einem Scheckheftregister oder einem Buchhaltungsbogen. Es gibt viele Möglichkeiten, eine laufende Summe von zwei zu erstellen, von denen unten beschrieben wird.
Die einfachste Methode besteht darin, in jeder Zeile die laufende Summe aus der obigen Zeile zum Wert in der Zeile hinzuzufügen. Die erste Formel in Zeile 2 lautet also:
=SUM(D1,C2)
Der Grund, warum wir die SUMME-Funktion verwenden, ist, dass wir in der ersten Zeile den Header in der obigen Zeile betrachten. Wenn wir die einfachere, intuitivere Formel von verwenden, =D1+C2wird ein Fehler generiert, da der Header-Wert Text versus numerisch ist. Die Magie ist, dass die SUMME-Funktion Textwerte ignoriert, die als Nullwerte hinzugefügt werden. Wenn die Formel in alle Zeilen kopiert wird, in denen eine laufende Summe gewünscht wird, werden die Zellreferenzen entsprechend angepasst:
Die andere Technik verwendet ebenfalls die SUMME-Funktion, aber jede Formel summiert alle Werte von der ersten Zeile bis zur Zeile, in der die laufende Summe angezeigt wird. In diesem Fall verwenden wir ein Dollarzeichen ($), um die erste Zelle in der Referenz zu einer absoluten Referenz zu machen, was bedeutet, dass sie beim Kopieren nicht angepasst wird:
Beide Techniken bleiben vom Sortieren und Löschen von Zeilen unberührt. Beim Einfügen von Zeilen muss die Formel jedoch in die neuen Zeilen kopiert werden.
In Excel 2007 wurde die Tabelle eingeführt, die eine Neuimplementierung der Liste in Excel 2003 darstellt. In Tabellen wurden eine Reihe sehr nützlicher Funktionen für Datentabellen wie Formatieren, Sortieren und Filtern eingeführt. Mit der Einführung von Tabellen wurde uns auch eine neue Art der Referenzierung der Teile einer Tabelle bereitgestellt. Dieser neue Referenzierungsstil wird als strukturierte Referenzierung bezeichnet.
Um das obige Beispiel in eine Tabelle zu konvertieren, wählen wir die Daten aus, die in die Tabelle aufgenommen werden sollen, und drücken Strg + T. Nachdem eine Eingabeaufforderung angezeigt wurde, in der wir aufgefordert werden, den Bereich der Tabelle zu bestätigen und festzustellen, ob vorhandene Überschriften vorhanden sind, konvertiert Excel die Daten in eine formatierte Tabelle:
Beachten Sie, dass die zuvor eingegebenen Formeln gleich bleiben.
Eine der nützlichen Funktionen von Tables ist die automatische Formatierung und Formelpflege, wenn Zeilen hinzugefügt, entfernt, sortiert und gefiltert werden. Es ist insbesondere die Formelpflege, auf die wir uns konzentrieren werden und die problematisch sein kann. Damit Tabellen während der Bearbeitung weiterhin funktionieren, verwendet Excel berechnete Spalten, bei denen es sich um Spalten mit Formeln wie Spalte D im obigen Beispiel handelt. Wenn neue Zeilen eingefügt werden, werden die neuen Zeilen automatisch mit der Standardformel für diese Spalte gefüllt. Das Problem mit dem obigen Beispiel ist, dass Excel mit Standardformeln verwechselt wird und diese nicht immer richtig behandelt. Dies wird deutlich, wenn am unteren Rand der Tabelle neue Zeilen hinzugefügt werden (indem Sie die untere rechte Zelle in der Tabelle auswählen und die Tabulatortaste drücken):
Dieser Mangel wird durch die neuere strukturierte Referenzierung behoben. Durch die strukturierte Referenzierung entfällt die Notwendigkeit, bestimmte Zellen mit dem Referenzierungsstil A1 oder R1C1 zu referenzieren, und stattdessen werden Spaltennamen und andere Schlüsselwörter verwendet, um die Teile einer Tabelle zu identifizieren und zu referenzieren. Um beispielsweise die gleiche laufende Gesamtformel zu erstellen, die oben verwendet wurde, aber strukturierte Referenzierung verwendet, haben wir:
=SUM(INDEX((Sales),1):(@Sales))
In diesem Beispiel haben wir einen Verweis auf den Spaltennamen "Sales" zusammen mit dem at-Zeichen (@), um auf die Zeile in der Spalte zu verweisen, in der sich die Formel befindet, die auch als aktuelle Zeile bezeichnet wird.
Um das erste Beispiel oben zu implementieren, in dem wir den laufenden Gesamtwert in der vorhergehenden Zeile zum Verkaufsbetrag in der aktuellen Zeile hinzugefügt haben, können Sie die OFFSET-Funktion verwenden:
=SUM(OFFSET((@(Running Total)),-1,0),(@Sales))
Wenn sich die zur Berechnung der laufenden Summe verwendeten Beträge in zwei Spalten befinden, z. B. eine für "Belastungen" und eine für "Gutschriften", lautet die Formel:
=SUM(INDEX( (Credit),1):(@Credit))- SUM(INDEX( (Debit),1):(@Debit))
Hier verwenden wir die INDEX-Funktion, um die Kredit- und Debitzellen der ersten Zeile zu lokalisieren und die gesamte Spalte bis einschließlich der Werte der aktuellen Zeile zu summieren. Die laufende Summe ist die Summe aller Gutschriften bis einschließlich der aktuellen Zeile abzüglich der Summe aller Belastungen bis einschließlich der aktuellen Zeile.
Für weitere Informationen zu strukturierten Referenzen im Besonderen und Tabellen im Allgemeinen empfehlen wir das Buch Excel-Tabellen: Ein vollständiger Leitfaden zum Erstellen, Verwenden und Automatisieren von Listen und Tabellen von Zack Barresse und Kevin Jones.
Als ich die Leser bat, für ihre Lieblingstipps zu stimmen, waren Tische beliebt. Vielen Dank an Peter Albert, Snorre Eikeland, Nancy Federice, Colin Michael, James E. Moede, Keyur Patel und Paul Peton für den Vorschlag dieser Funktion. Peter Albert hat den Bonus-Tipp für lesbare Referenzen geschrieben. Zack Barresse hat den Running Totals Bonus Tipp geschrieben. Vier Leser schlugen vor, OFFSET zu verwenden, um erweiterte Bereiche für dynamische Diagramme zu erstellen: Charley Baak, Don Knowles, Francis Logan und Cecelia Rieb. Tabellen machen jetzt in den meisten Fällen dasselbe.
Schau Video
- Diese Episode zeigt drei Möglichkeiten, um laufende Summen zu erstellen
- Die erste Methode hat in Zeile 2 eine andere Formel als alle anderen Zeilen
- Die erste Methode ist = Links in Zeile 2 und = Links + Oben in den Zeilen 3 bis N.
- Wenn Sie versuchen, dieselbe Formel zu verwenden, wird ein # Value-Fehler mit = Total + Number angezeigt
- Methode 2 verwendet
=SUM(Up,Left)oder=SUM(Previous Total,This Row Amount) - SUM ignoriert Text, sodass Sie keinen VALUE-Fehler erhalten
- Methode 3 verwendet einen erweiterten Bereich:
=SUM(B$2:B2) - Erweiterungsbereiche sind cool, aber langsam
- Lesen Sie das Whitepaper von Charles Williams zu Excel Formula Speed
- Die dritte Methode ist ein Problem, wenn Sie Strg + T verwenden und neue Zeilen hinzufügen
- Excel kann nicht herausfinden, wie die Formel geschrieben wird
- Die Problemumgehungen erfordern einige Kenntnisse der strukturierten Referenzierung in Tabellen
- Problemumgehung 1 ist die langsame
=SUM(INDEX((Qty),1):(@Qty)) - Problemumgehung 2 ist die flüchtige
=SUM(OFFSET((@Total),-1,0),(@Qty)) - (@Qty) bezieht sich auf die Menge in dieser Zeile
- (Menge) bezieht sich auf alle Mengenwerte
Video-Transkript
Lernen Sie Excel für Podcast, Episode 2004 - Laufende Summen
Ich werde das ganze Buch podcasten. Klicken Sie auf das I oben rechts, um sich anzumelden.
Hey, willkommen zurück zum Mystic Cell Netcast. Ich bin Bill Jelen. Nun zu diesem Thema im Buch wurde ich von meinem Freund Zach Parise beigesteuert. Sprechen Sie über Excel-Tabellen, Zach ist der weltweite Experte für Excel-Tabellen. Er hat ein Buch über Excel-Tabellen geschrieben, aber lassen Sie uns zuerst über das Ausführen von Summen sprechen, die nicht in Tabellen enthalten sind.
Wenn ich also über laufende Summen nachdenke, gibt es drei verschiedene Möglichkeiten, die laufenden Summen zu erstellen, und die Art und Weise, mit der ich immer angefangen habe, ist in der ersten Zeile, die Sie gerade sagen, bringen Sie den Wert über. Also gleich was auch immer links von mir ist. Okay, dieses Format hier ist nur = B2. Dies sind alles Formeltexte hier in der rechten Ecke, damit Sie sehen, was wir verwenden, und von da an ist es eine einfache kleine Formel, die dem vorherigen Wert plus dem aktuellen Wert rechts entspricht, und kopieren Sie diese nach unten Aber Sie wissen jetzt, wir haben das Problem, dass zwei verschiedene Formeln erforderlich sind, und Sie wissen, dass Sie in einer perfekten Situation genau die gleiche Formel haben, und der Grund, warum wir dort in der ersten Zeile eine andere Formel haben müssen, ist Wenn Sie versuchen, gleich 7 plus die Wortsumme zu addieren, ist dies ein Wertfehler.Aber der coole Arbeiter hier draußen ist, nicht nur left plus up zu verwenden, sondern = (SUM) des vorherigen Werts plus der Menge in dieser Zeile zu verwenden, und zu sehen, dass einige weit genug sind, um Texte zu ignorieren. Richtig, damit die gleiche Formel möglich ist. den ganzen Weg hinunter.
Okay, als ich anfing, Excel zu verwenden, habe ich das verwendet und dann den erweiterten Bereich entdeckt. Der erweiterte Bereich besagt, dass wir L $ 2: L2 machen werden, und was passiert, ist, dass dies immer in Zeile 2 beginnt. aber dann geht es runter zur aktuellen Zeile. Wenn Sie sich also ansehen, wie dies funktioniert, wenn es kopiert wird, haben wir immer Zeile 2 gestartet, aber wir gehen zur aktuellen Zeile und dies wurde meine Lieblingsmethode. Ich dachte, oh, das ist so viel ausgefeilter. Wenn wir in die Excel-Optionen gehen, gehen Sie zur Registerkarte Formeln und wählen Sie R1C1 im Referenzstil. Okay, R1C1, alle diese Formeln sind bis zum Ende genau gleich. Ich weiß nicht, ob Sie R1C1 verstehen. Es ist nur gut zu wissen, dass wir die gleichen R1C1-Formeln haben.
Lass uns zurück gehen. Diese Methode hier ist die Methode, die mir gefallen hat, bis Charles Williams, ein Excel-MBP aus England, der ein erstaunliches Papier über die Formelgeschwindigkeit, die Excel-Formelgeschwindigkeit, hat, diese Methode vollständig entlarvt hat. Nehmen wir an, Sie haben bei dieser Methode 10.000 Zeilen. Jede einzelne Formel betrachtet zwei Referenzen. Sie sehen also 20.000 Referenzen, aber diese eine, diese zwei, diese drei, diese vier, fünf und die letzte 10.000 Referenzen und es ist schrecklich langsamer und so hörte ich auf, diese Methode zu verwenden.
Dann lese ich Zack in Kevin Jones 'Buch über Excel-Tabellen und entdecke ein weiteres Problem mit dieser Methode. Eine der nützlichen Funktionen, die die Tabellen bieten, ist "Automatische Formatierung und Formelwartungszeilen werden hinzugefügt, entfernt, sortiert und gefiltert". Okay, das ist ein Zitat aus seinem Buch. Um einer Tabelle eine Zeile hinzuzufügen, gehen Sie einfach zur allerletzten Zelle der Tabelle und drücken Sie die Tabulatortaste. Hier funktioniert also alles. Wir sind auf 70 gesunken, das ist großartig und dann A104 und ich werde hier eine 100 einsetzen. Okay, also sollte sich 70 auf 170 ändern und das tut es auch, aber diese 70 hätte sich überhaupt nicht ändern sollen. Okay, 68 + 2 ist keine 170. Ich werde es wieder tun. Eine 104 und weitere hundert in die letzte setzen ist richtig. Diese beiden sind nicht richtig. Okay, wir haben also eine seltsame Situation, wenn Sie 'Wenn Sie diese Formel verwenden und in eine Tabelle konvertieren, in der Sie Zeilen hinzufügen, funktioniert die laufende Summe nicht. Wie schlimm ist das
Also gut, Zack bietet zwei Workarounds an und beide erfordern ein wenig Wissen darüber, wie Strukturreferenzen funktionieren. Wir werden hier draußen nur eine neue Spalte haben und wenn ich Quantität machen wollte, gleiche Quantität, richtig, so dass = (@ Menge) Quantität in dieser Zeile sagt. Oh cool, nun, es gibt eine andere Art von Referenz, bei der wir die Menge ohne das @ verwenden. Überprüfen Sie dies heraus. Also = SUMME (INDEX ((Menge), 1: (@ Menge)) bedeutet alle Mengen und wir werden sagen, dass wir von der ersten Menge summieren wollen, also (INDEX ((Menge), 1 sagt die Der erste Wert hier, bis auf die aktuelle Zeilenmenge, und dies verwendet eine wirklich spezielle Version des Index. Wenn auf den Index ein Doppelpunkt folgt, ändert sich dieser tatsächlich in eine Zellreferenz. Okay, diese Problemumgehung verstößt leider gegen die Charles Williams-Regel von, wir 'Wir müssen uns jede einzelne Referenz ansehen, und wenn Sie 10.000 Zeilen davon erhalten, wird dies sehr, sehr langsam gehen.
Zach hat eine andere Problemumgehung, die das Charles Williams-Problem nicht verletzt, aber das gefürchtete OFFSET verwendet. OFFSET ist eine flüchtige Funktion. Jedes Mal, wenn Sie etwas berechnen, wird OFFSET neu berechnet und alles, was von den OFFSETs heruntergerechnet wird, wird neu berechnet. Es ist einfach eine großartige Möglichkeit, Ihre Formeln vollständig zu vermasseln, und was dies tut, heißt es, wir nehmen die Summe aus dieser Zeile, gehen eine Zeile nach oben, über null Spalten, und das heißt also: Nehmen Sie die Summe aus der vorherigen Zeile und fügen Sie dann die Menge aus dieser Zeile hinzu. Okay, jetzt werden jedes Mal zwei Referenzen betrachtet, aber leider führt der OFFSET flüchtige Funktionen ein.
Nun, da haben Sie es, mehr als Sie jemals über Running Totals wissen wollten. Ich denke, meine letzte Meinung hier ist, diese Methode zu verwenden, weil es nur zwei sieht. Dieselbe Formel ganz unten und Ihre strukturierten Tabellenreferenzen funktionieren.
Für diese Erkundung und 39 andere wirklich gute Tipps lesen Sie dieses Buch XL, die 40 besten Excel-Tipps aller Zeiten.
Rückblick auf diese Episode haben wir über drei Möglichkeiten gesprochen, um laufende Summen zu erstellen. Die erste Methode hat eine andere Formel, Zeile 2, als alle anderen Zeilen. Es ist gleich links in Zeile 2 und dann gleich links plus oben in den Zeilen 3 bis N, aber wenn Sie versuchen, dieselbe Formel zu verwenden, gleich links plus oben, ganz unten, wie Sie einen # Value-Fehler erhalten . Also = SUMME (Oben, Links), was die vorherige Summe ist, plus diese Roadmap, die großartig funktioniert, keine Wertfehler und dann den erweiterten Bereich, den ich liebe. Sie sind cool, aber bis ich Charles Williams Whitepaper über Excel-Form der Geschwindigkeit gelesen habe. Dann fing ich an, diese wachsenden Referenzen zu hassen. Es gibt auch ein Problem, wenn Sie STRG T verwenden und neue Zeilen hinzufügen. Excel kann nicht herausfinden, wie diese Formel erweitert und neue Zeilen hinzugefügt werden können. Ich liebe diesen Tipp. Gehen Sie zur allerletzten Zelle in der Tabelle und drücken Sie die Tabulatortaste.Dadurch wird eine neue Zeile hinzugefügt, und dann haben wir über eine strukturierte Referenzierung gesprochen, bei der wir die Menge in dieser Zeile und dann alle Mengen verwenden. = SUMME (OFFSET ((@ Gesamt), - 1,00, (@ Menge)).
Okay, ich möchte Zach dafür danken, dass er diesen Tipp beigesteuert hat. Ich möchte mich bei Ihnen für Ihren Besuch bedanken. Wir sehen uns beim nächsten Mal für einen weiteren Netcast von.
Download-Datei
Laden Sie die Beispieldatei hier herunter: Podcast2004.xlsx
