Laufende Summe (auch genannt kumulierte Summe) wird in vielen Situationen häufig verwendet. Es ist eine Metrik, die Ihnen sagt, was die Summe der bisherigen Werte ist.
Wenn Sie beispielsweise über die monatlichen Verkaufsdaten verfügen, würde eine laufende Summe Ihnen sagen, wie viel Umsatz bis zu einem bestimmten Tag vom ersten Tag des Monats an getätigt wurde.
Es gibt auch einige andere Situationen, in denen die laufende Summe häufig verwendet wird, z.
In Microsoft Excel gibt es mehrere verschiedene Möglichkeiten, laufende Summen zu berechnen.
Welche Methode Sie wählen, hängt auch davon ab, wie Ihre Daten strukturiert sind.
Wenn Sie beispielsweise einfache Tabellendaten haben, können Sie eine einfache SUM-Formel verwenden, aber wenn Sie eine Excel-Tabelle haben, verwenden Sie am besten strukturierte Verweise. Sie können dazu auch Power Query verwenden.
In diesem Tutorial werde ich all diese verschiedenen Methoden behandeln, um laufende Summen in Excel berechnen.
Also lasst uns anfangen!
Berechnen der laufenden Summe mit Tabellendaten
Wenn Sie tabellarische Daten haben (d. h. eine Tabelle in Excel, die nicht in eine Excel-Tabelle umgewandelt wird), können Sie einige einfache Formeln verwenden, um die laufenden Summen zu berechnen.
Verwenden des Additionsoperators
Angenommen, Sie haben datumsbezogene Verkaufsdaten und möchten die laufende Summe in Spalte C berechnen.
Nachfolgend finden Sie die Schritte dazu.
Schritt 1 - Geben Sie in Zelle C2, der ersten Zelle, in der Sie die laufende Summe haben möchten, ein
=B2
Dadurch erhalten Sie einfach die gleichen Verkaufswerte in Zelle B2.
Schritt 2 - Geben Sie in Zelle C3 die folgende Formel ein:
=C2+B3
Schritt 3 - Wenden Sie die Formel auf die gesamte Spalte an. Sie können den Füllgriff verwenden, um es auszuwählen und zu ziehen, oder einfach die Zelle C3 in alle verbleibenden Zellen kopieren und einfügen (was die Referenz automatisch anpassen und das richtige Ergebnis liefern würde).
Dadurch erhalten Sie das Ergebnis wie unten gezeigt.
Es ist eine wirklich einfache Methode und funktioniert in den meisten Fällen gut.
Die Logik ist einfach - jede Zelle nimmt den darüber liegenden Wert (die kumulierte Summe bis zum vorherigen Datum) und fügt den Wert in der daneben liegenden Zelle hinzu (der Verkaufswert für diesen Tag).
Es gibt nur einen Nachteil - falls Sie eine der vorhandenen Zeilen in diesem Datensatz löschen, würden alle darunter liegenden Zellen einen Referenzfehler (#REF!)
Wenn dies mit Ihrem Datensatz möglich ist, verwenden Sie die nächste Methode, die die SUM-Formel verwendet
Verwenden von SUM mit teilweise gesperrter Zellreferenz
Angenommen, Sie haben datumsbezogene Verkaufsdaten und möchten die laufende Summe in Spalte C berechnen.
Unten ist die Summenformel, die Ihnen die laufende Summe ergibt.
=SUMME($B$2:B2)
Lassen Sie mich erklären, wie diese Formel funktioniert.
In der obigen SUM-Formel habe ich die Referenz verwendet, um als $B$2:B2 . hinzuzufügen
- $B$2 - Dies ist eine absolute Referenz, was bedeutet, dass sich diese Referenz nicht ändert, wenn ich dieselbe Formel in die Zellen darunter kopiere. Wenn Sie also die Formel in die Zelle unten kopieren, ändert sich die Formel in SUM($B$2:B3)
- B2 - Dies ist der zweite Teil der Referenz, der eine relative Referenz ist, was bedeutet, dass sich dies anpassen würde, wenn ich die Formel nach unten oder rechts kopiere. Wenn Sie also die Formel in die Zelle darunter kopieren, wird dieser Wert zu B3
Lesen Sie auch: Absolute, relative und gemischte Zellreferenzen in Excel
Das Tolle an dieser Methode ist, dass, falls Sie eine der Zeilen im Datensatz löschen, diese Formel angepasst wird und Sie immer noch die richtigen laufenden Summen erhalten.
Berechnen der laufenden Summe in der Excel-Tabelle
Wenn Sie mit Tabellendaten in Excel arbeiten, empfiehlt es sich, diese in eine Excel-Tabelle umzuwandeln. Es erleichtert die Verwaltung der Daten erheblich und ermöglicht auch die einfache Verwendung von Tools wie Power Query und Power Pivot.
Die Arbeit mit Excel-Tabellen bietet Vorteile wie strukturierte Verweise (die es wirklich einfach machen, auf die Daten in der Tabelle zu verweisen und sie in Formeln zu verwenden) und die automatische Anpassung von Verweisen, falls Sie Daten in der Tabelle hinzufügen oder löschen.
Während Sie die obige Formel, die ich Ihnen in einer Excel-Tabelle gezeigt habe, weiterhin verwenden können, möchte ich Ihnen einige bessere Methoden dafür zeigen.
Angenommen, Sie haben eine Excel-Tabelle wie unten gezeigt und möchten die laufende Summe in Spalte C berechnen.
Unten ist die Formel, die dies tut:
=SUMME(SalesData[[#Headers],[Sale]]:[@Sale])
Die obige Formel sieht vielleicht etwas lang aus, aber Sie müssen sie nicht selbst schreiben. Was Sie in der Summenformel sehen, werden als strukturierte Referenzen bezeichnet. Dies ist Excels effiziente Möglichkeit, auf bestimmte Datenpunkte in einer Excel-Tabelle zu verweisen.
SalesData[[#Headers],[Sale]] bezieht sich beispielsweise auf den Sale-Header in der SalesData-Tabelle (SalesData ist der Name der Excel-Tabelle, die ich beim Erstellen der Tabelle angegeben habe).
Und [@Sale] bezieht sich auf den Wert in der Zelle in derselben Zeile in der Spalte Sale.
Ich habe dies hier nur zu Ihrem Verständnis erklärt, aber auch wenn Sie keine Ahnung von strukturierten Referenzen haben, können Sie diese Formel dennoch problemlos erstellen.
Im Folgenden sind die Schritte dazu aufgeführt:
- Geben Sie in Zelle C2 =SUM(
- Wählen Sie Zelle B1 aus, die die Überschrift der Spalte mit dem Verkaufswert ist. Sie können die Maus oder die Pfeiltasten verwenden. Sie werden feststellen, dass Excel automatisch die strukturierte Referenz für diese Zelle eingibt
- Fügen Sie ein : (Doppelpunktsymbol) hinzu
- Wählen Sie Zelle B2 aus. Excel würde wieder automatisch die strukturierte Referenz für die Zelle einfügen
- Schließen Sie die Klammer und drücken Sie die Eingabetaste
Sie werden auch feststellen, dass Sie die Formel nicht in der gesamten Spalte kopieren müssen, eine Excel-Tabelle erledigt das automatisch für Sie.
Eine weitere großartige Eigenschaft dieser Methode ist, dass die Excel-Tabelle automatisch die laufende Summe für alle neuen Datensätze berechnet, falls Sie diesem Datensatz einen neuen Datensatz hinzufügen.
Obwohl wir die Kopfzeile der Spalte in unsere Formel aufgenommen haben, denken Sie daran, dass die Formel den Kopfzeilentext ignoriert und nur die Daten in der Spalte berücksichtigt
Berechnen der laufenden Summe mit Power Query
Power Query ist ein erstaunliches Tool, wenn es darum geht, sich mit Datenbanken zu verbinden, die Daten aus mehreren Quellen zu extrahieren und sie zu transformieren, bevor sie in Excel übertragen werden.
Wenn Sie bereits mit Power Query arbeiten, ist es effizienter, laufende Summen hinzuzufügen, während Sie die Daten im Power Query-Editor selbst transformieren (anstatt die Daten zuerst in Excel abzurufen und dann die laufenden Summen mit einer der oben genannten Methoden hinzuzufügen). oben beschriebene Methoden).
Obwohl es in Power Query keine integrierte Funktion zum Hinzufügen von laufenden Summen gibt (was ich mir wünschte), können Sie dies dennoch mit einer einfachen Formel tun.
Angenommen, Sie haben eine Excel-Tabelle wie unten gezeigt und möchten die laufenden Summen zu diesen Daten hinzufügen:
Im Folgenden sind die Schritte dazu aufgeführt:
- Wählen Sie eine beliebige Zelle in der Excel-Tabelle aus
- Klicken Sie auf Daten
- Klicken Sie auf der Registerkarte Abrufen und Transformieren auf das Symbol aus Tabelle/Bereich. Dadurch wird die Tabelle im Power Query-Editor geöffnet
- [Optional] Falls Ihre Datumsspalte noch nicht sortiert ist, klicken Sie auf das Filtersymbol in der Datumsspalte und dann auf Aufsteigend sortieren
- Klicken Sie im Power Query-Editor auf die Registerkarte Spalte hinzufügen.
- Klicken Sie in der Gruppe Allgemein auf das Dropdown-Menü Indexspalte (klicken Sie nicht auf das Symbol für die Indexspalte, sondern auf den kleinen schwarzen, geneigten Pfeil rechts daneben, um weitere Optionen anzuzeigen).
- Klicken Sie auf die Option „Von 1“. Dadurch wird eine neue Indexspalte hinzugefügt, die bei eins beginnt und in der gesamten Spalte um 1 inkrementierende Zahlen eingeben
- Klicken Sie auf das Symbol "Benutzerdefinierte Spalte" (das sich auch auf der Registerkarte "Spalte hinzufügen") befindet.
- Geben Sie im sich öffnenden Dialogfeld für die benutzerdefinierte Spalte einen Namen für die neue Spalte ein. In diesem Beispiel verwende ich den Namen „Running Total“.
- Geben Sie im Feld Formel für benutzerdefinierte Spalte die folgende Formel ein: List.Sum(List.Range(#”Added Index”[Sale],0,[Index]))
- Stellen Sie sicher, dass am unteren Rand des Dialogfelds ein Kontrollkästchen mit der Aufschrift "Es wurden keine Syntaxfehler erkannt" vorhanden ist.
- OK klicken. Dies würde eine neue Spalte für die laufende Summe hinzufügen
- Entfernen Sie die Indexspalte
- Klicken Sie auf die Registerkarte Datei und dann auf "Schließen und Laden".
Die obigen Schritte würden ein neues Blatt in Ihre Arbeitsmappe mit einer Tabelle einfügen, die die laufenden Summen enthält.
Wenn Sie jetzt denken, dass dies im Vergleich zu den vorherigen Methoden zur Verwendung einfacher Formeln einfach zu viele Schritte sind, haben Sie Recht.
Wenn Sie bereits über einen Datensatz verfügen und lediglich laufende Summen hinzufügen müssen, verwenden Sie Power Query besser nicht.
Die Verwendung von Power Query ist sinnvoll, wenn Sie Daten aus einer Datenbank extrahieren oder Daten aus mehreren verschiedenen Arbeitsmappen kombinieren und dabei auch laufende Summen hinzufügen müssen.
Sobald Sie diese Automatisierung mit Power Query durchgeführt haben, müssen Sie die nächsten Änderungen Ihres Datensatzes nicht erneut durchführen. Sie können die Abfrage einfach aktualisieren und erhalten das Ergebnis basierend auf dem neuen Datensatz.
Wie funktioniert das?
Lassen Sie mich nun kurz erklären, was bei dieser Methode passiert.
Das erste, was wir im Power Query-Editor tun, besteht darin, eine Indexspalte einzufügen, beginnend bei eins und inkrementiert um eins, während sie durch die Zellen geht.
Wir tun dies, weil wir diese Spalte verwenden müssen, während wir die laufende Summe in einer anderen Spalte berechnen, die wir im nächsten Schritt einfügen.
Dann fügen wir eine benutzerdefinierte Spalte ein und verwenden die folgende Formel
List.Sum(List.Range(#"Added Index"[Sale],0,[Index]))
Dies ist eine List.Sum-Formel, die Ihnen die Summe des darin angegebenen Bereichs liefert.
Und dieser Bereich wird mit der Funktion List.Range angegeben.
Die Funktion List.Range gibt den angegebenen Bereich in der Verkaufsspalte als Ausgabe aus und dieser Bereich ändert sich basierend auf dem Indexwert. Für den ersten Datensatz wäre der Bereich beispielsweise einfach der erste Verkaufswert. Und wenn Sie die Zellen hinuntergehen, würde sich dieser Bereich erweitern.
Also für die erste Zelle. List.Sum würde Ihnen nur die Summe des ersten Verkaufswerts geben, und für die zweite Zelle die Summe der ersten beiden Verkaufswerte usw.
Diese Methode funktioniert zwar gut, wird aber bei großen Datensätzen - Tausenden von Zeilen - sehr langsam. Wenn Sie mit einem großen Datensatz zu tun haben und laufende Summen hinzufügen möchten, sehen Sie sich dieses Tutorial an, das andere schnellere Methoden zeigt.Berechnen der laufenden Summe basierend auf Kriterien
Bisher haben wir Beispiele gesehen, in denen wir die laufende Summe für alle Werte in einer Spalte berechnet haben.
Es kann jedoch Fälle geben, in denen Sie die laufende Summe für bestimmte Datensätze berechnen möchten.
Im Folgenden habe ich beispielsweise einen Datensatz und möchte die laufende Summe für Drucker und Scanner getrennt in zwei verschiedenen Spalten berechnen.
Dies kann mit einer SUMIF-Formel erfolgen, die die laufende Summe berechnet und gleichzeitig sicherstellt, dass die angegebene Bedingung erfüllt ist.
Unten ist die Formel, die dies für die Druckerspalten tut:
=SUMIF($C$2:C2,$D$1,$B$2:B2)
Um die laufende Summe für Scanner zu berechnen, verwenden Sie auf ähnliche Weise die folgende Formel:
=SUMIF($C$2:C2,$E$1,$B$2:B2)
In den obigen Formeln habe ich SUMIF verwendet, was mir die Summe in einem Bereich ergibt, wenn die angegebenen Kriterien erfüllt sind.
Die Formel benötigt drei Argumente:
- Angebot: Dies ist der Kriterienbereich, der anhand der angegebenen Kriterien geprüft würde
- Kriterien: Dies ist das Kriterium, das nur dann eingecheckt wird, wenn dieses Kriterium erfüllt ist, dann werden die Werte im dritten Argument, dem Summenbereich, hinzugefügt
- [Summenbereich]: Dies ist der Summenbereich, aus dem die Werte addiert würden, wenn die Kriterien erfüllt sind
Auch in der Angebot und Summenbereich Argument habe ich den zweiten Teil der Referenz gesperrt, damit sich der Bereich beim Durchlaufen der Zellen immer weiter ausdehnt. Dies ermöglicht es uns, nur Werte bis zu diesem Bereich zu berücksichtigen und hinzuzufügen (daher laufende Summen).
In dieser Formel habe ich die Kopfspalte (Drucker und Scanner) als Kriterium verwendet. Sie können die Kriterien auch hartcodieren, wenn Ihre Spaltenüberschriften nicht genau mit dem Kriterientext übereinstimmen.
Falls Sie mehrere Bedingungen haben, die Sie überprüfen müssen, können Sie die SUMIFS-Formel verwenden.
Laufende Summe in Pivot-Tabellen
Wenn Sie laufende Summen zu einem Pivot-Tabellenergebnis hinzufügen möchten, können Sie dies ganz einfach mit einer integrierten Funktionalität in Pivot-Tabellen tun.
Angenommen, Sie haben eine Pivot-Tabelle wie unten gezeigt, in der ich das Datum in einer Spalte und den Verkaufswert in der anderen Spalte habe.
Nachfolgend finden Sie die Schritte zum Hinzufügen einer zusätzlichen Spalte, die die laufende Summe der Verkäufe nach Datum anzeigt:
- Ziehen Sie das Feld Verkauf und fügen Sie es in den Bereich Wert ein.
- Dadurch wird eine weitere Spalte mit den Verkaufswerten hinzugefügt
- Klicken Sie im Bereich Wert auf die Option Summe des Verkaufs2
- Klicken Sie auf die Option „Wertfeldeinstellungen“
- Ändern Sie im Dialogfeld "Wertfeldeinstellungen" den benutzerdefinierten Namen in "Laufende Summen".
- Klicken Sie auf die Registerkarte "Wert anzeigen als".
- Wählen Sie im Dropdown-Menü Wert anzeigen als die Option „Laufende Summe in“ aus.
- Stellen Sie in den Basisfeldoptionen sicher, dass Datum ausgewählt ist
- OK klicken
Die obigen Schritte würden die zweite Verkaufsspalte in die Spalte für die laufende Summe ändern.
Dies sind einige der Möglichkeiten, mit denen Sie die laufende Summe in Excel berechnen können. Wenn Sie Daten im Tabellenformat haben, können Sie einfache Formeln verwenden, und wenn Sie über eine Excel-Tabelle verfügen, können Sie Formeln verwenden, die strukturierte Verweise verwenden.
Ich habe auch behandelt, wie die laufende Summe mit Power Query und in Pivot-Tabellen berechnet wird.
Ich hoffe, Sie fanden dieses Tutorial nützlich.
