laufende Summe (auch bekannt alskumulative Summe) wird in vielen Fällen sehr häufig verwendet.Das ist eine Metrik, die dir sagt, was die Summe der bisherigen Werte ist.
Wenn Sie beispielsweise monatliche Verkaufsdaten haben, zeigt Ihnen eine laufende Summe, wie viele Verkäufe vom ersten Tag des Monats bis zu einem bestimmten Datum getätigt wurden.
Es gibt andere Situationen, in denen häufig Summen verwendet werden, z.
In Microsoft Excel gibt es verschiedene Möglichkeiten, laufende Summen zu berechnen.
Welche Methode Sie wählen, hängt auch von der Struktur der Daten ab.
Wenn Sie beispielsweise einfache Tabellendaten haben, können Sie eine einfache SUMME-Formel verwenden, aber wenn Sie eine Excel-Tabelle haben, ist es besser, strukturierte Verweise zu verwenden.Sie können dazu auch Power Query verwenden.
In diesem Tutorial werde ich alle behandelnBerechnen Sie eine laufende Summe in Excelverschiedene Methoden.
Also lasst uns anfangen!
Inhalte
Berechnen Sie laufende Summen anhand von Tabellendaten
Wenn Sie Tabellendaten haben (d. h. Tabellen in Excel, die nicht in Excel-Tabellen konvertiert wurden), können Sie einige einfache Formeln verwenden, um laufende Summen zu berechnen.
Verwenden Sie den Additionsoperator
Angenommen, Sie haben Verkaufsdaten nach Datum und möchten dies tun在 in Spalte CBerechnungLaufende Summe.
Hier sind die Schritte dazu.
Schritt 1 – Geben Sie in Zelle C2 die erste Zelle ein, für die Sie die Summe ausführen möchten
= B2
Dies erhält nur den gleichen Verkaufswert 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 das Ausfüllkästchen verwenden, um es auszuwählen und zu ziehen, oder einfach Zelle C3 in alle verbleibenden Zellen kopieren und einfügen (dies passt automatisch die Referenz an und liefert das richtige Ergebnis).
Dadurch erhalten Sie das unten gezeigte Ergebnis.
Dies ist eine sehr einfache Methode, die in den meisten Fällen gut funktioniert.
Die Logik ist einfach – jede Zelle nimmt den Wert darauf (das ist die kumulierte Summe bis zum Vortag) und addiert den Wert in der angrenzenden Zelle (das ist der Verkaufswert des aktuellen Tages).
Es gibt nur einen Nachteil: Wenn Sie eine vorhandene Zeile in diesem Datensatz löschen, geben alle darunter liegenden Zellen einen Referenzfehler zurück (#REF!).
Wenn Ihr Datensatz diese Möglichkeit bietet, verwenden Sie die nächste Methode mit der SUM-Formel
Verwenden Sie SUM mit teilweise gesperrten Zellbezügen
Angenommen, Sie haben Verkaufsdaten nach Datum und möchten eine laufende Summe in Spalte C berechnen.
Unten ist die SUMME-Formel, die Ihnen eine laufende Summe gibt.
= SUMME ($ B $ 2: B2)
Lassen Sie mich erklären, wie diese Formel funktioniert.
In der obigen SUMME-Formel habe ich die Referenz verwendet, um sie 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 Zelle darunter kopiere.Wenn Sie also die Formel in die Zelle darunter kopieren, ändert sich die Formel in SUM($B$2:B3)
- B2 - Dies ist der zweite Teil der Referenz, es ist eine relative Referenz, was bedeutet, wenn ich die Formel nach unten oder rechts kopiere, passt sich dies an.Wenn Sie also die Formel in die Zelle darunter kopieren, wird der Wert zu B3
Das Schöne an diesem Ansatz ist, dass sich diese Formel anpasst und Ihnen immer noch die korrekte laufende Summe liefert, wenn Sie Zeilen im Datensatz löschen.
Berechnen Sie die laufende Summe in einer Excel-Tabelle
Wenn Sie mit Tabellendaten in Excel arbeiten, konvertieren Sie diese am besten in eine Excel-Tabelle.Es erleichtert die Verwaltung von Daten und erleichtert auch die Verwendung von Tools wie Power Query und Power Pivot.
Die Verwendung einer Excel-Tabelle bietet mehrere Vorteile, z. B. strukturierte Verweise (die es sehr einfach machen, auf Daten in der Tabelle zu verweisen und sie in Formeln zu verwenden) und die automatische Anpassung von Verweisen, wenn Sie Daten zur Tabelle hinzufügen oder daraus entfernen.
Während Sie die obige Formel, die ich Ihnen in einer Excel-Tabelle gezeigt habe, immer noch verwenden können, möchte ich Ihnen einige bessere Möglichkeiten zeigen, dies zu tun.
Angenommen, Sie haben eine Excel-Tabelle wie die folgende und Sie möchten eine laufende Summe in Spalte C berechnen.
Hier ist die Formel dazu:
=SUM(SalesData[[#Headers],[Sale]]:[@Sale])
Die obige Formel mag etwas lang erscheinen, aber Sie müssen sie nicht selbst schreiben.Was Sie in einer Summationsformel sehen, wird als strukturierte Referenz bezeichnet und ist eine effiziente Möglichkeit für Excel, auf bestimmte Datenpunkte in einer Excel-Tabelle zu verweisen.
Zum Beispiel bezieht sich SalesData[[#Headers],[Sale]] auf den Sales-Header in der SalesData-Tabelle (SalesData ist der Name der Excel-Tabelle, die ich beim Erstellen der Tabelle angegeben habe).
Wohingegen sich [@Sale] auf den Wert in einer Zelle in derselben Zeile der Spalte „Sale“ bezieht.
Ich erkläre das hier nur zum Verständnis, aber selbst wenn Sie nichts über strukturierte Referenzen wissen, können Sie diese Formel trotzdem leicht erstellen.
Hier sind die Schritte dazu:
- Geben Sie in Zelle C2 =SUMME(
- Wählen Sie Zelle B1 aus, die die Überschrift der Spalte mit dem Verkaufswert ist.Sie können die Maus verwenden oder die Pfeiltasten verwenden.Sie werden feststellen, dass Excel automatisch einen strukturierten Verweis auf die Zelle eingibt
- Hinzufügen: (Doppelpunkt)
- Markieren Sie Zelle B2.Excel fügt automatisch wieder strukturierte Verweise auf Zellen ein
- 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, das Excel-Blatt erledigt dies automatisch für Sie.
Ein weiterer Vorteil dieses Ansatzes besteht darin, dass das Excel-Arbeitsblatt automatisch eine laufende Summe für alle neuen Datensätze berechnet, wenn Sie diesem Datensatz einen neuen Datensatz hinzufügen.
Obwohl wir die Spaltenüberschriften in die Formel aufgenommen haben, denken Sie daran, dass die Formel den Kopfzeilentext ignoriert und nur die Daten in der Spalte berücksichtigt
Berechnen Sie laufende Summen mit Power Query
Power Query ist ein erstaunliches Tool, wenn es darum geht, eine Verbindung zu Datenbanken herzustellen, Daten aus mehreren Quellen zu extrahieren und sie zu transformieren, bevor sie in Excel eingefügt werden.
Wenn Sie Power Query bereits verwenden, ist es effizienter, laufende Summen hinzuzufügen, wenn Sie die Daten im Power Query-Editor selbst transformieren (anstatt zuerst die Daten in Excel abzurufen und dann die laufenden Summen mit einer der oben genannten Methoden hinzuzufügen).
Obwohl es in Power Query keine integrierte Funktion zum Hinzufügen laufender Summen gibt (ich wünschte, es gäbe eine), können Sie dies dennoch mit einer einfachen Formel tun.
Angenommen, Sie haben eine Excel-Tabelle, die wie folgt aussieht, und Sie möchten diesen Daten laufende Summen hinzufügen:
Hier sind die Schritte dazu:
- Wählen Sie eine beliebige Zelle in einer Excel-Tabelle aus
- Daten anklicken
- 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] Wenn 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 „Indexspalte“, sondern auf den kleinen schwarzen schrägen Pfeil daneben, um weitere Optionen anzuzeigen).
- Klicken Sie auf die Option "Von 1".Dadurch wird eine neue Indexspalte hinzugefügt, die bei 1 beginnt und in der gesamten Spalte um 1 erhöhte Zahlen eingegeben werden
- Klicken Sie auf das Symbol „Benutzerdefinierte Spalten“ (ebenfalls auf der Registerkarte „Spalten hinzufügen“).
- Geben Sie im sich öffnenden Dialogfeld Benutzerdefinierte Spalten einen Namen für die neue Spalte ein.In diesem Beispiel verwende ich den Namen „Running Total“
- Geben Sie im Feld Benutzerdefinierte Spaltenformel die folgende Formel ein:List.Sum(List.Range(#"Hinzugefügter Index"[Verkauf],0,[Index]))
- Stellen Sie sicher, dass unten im Dialogfeld ein Kontrollkästchen mit der Aufschrift „Keine Syntaxfehler gefunden“ angezeigt wird.
- OK klicken.Dadurch wird eine neue laufende Summenspalte hinzugefügt
- Indexspalte löschen
- Klicken Sie auf die Registerkarte Datei und dann auf Schließen und laden
Die obigen Schritte fügen ein neues Arbeitsblatt mit einer Tabelle mit laufenden Summen in Ihre Arbeitsmappe ein.
Wer nun denkt, dass das im Vergleich zum bisherigen Ansatz mit einfachen Formeln zu viele Schritte sind, der hat recht.
Wenn Sie bereits über ein Dataset verfügen und lediglich laufende Summen hinzufügen müssen, sollten Sie Power Query am besten nicht verwenden.
Die Verwendung von Power Query ist in Situationen sinnvoll, in denen Sie Daten aus einer Datenbank extrahieren oder Daten aus mehreren verschiedenen Arbeitsmappen kombinieren und dabei auch laufende Summen hinzufügen müssen.
Sobald Sie dies mit Power Query automatisiert haben, müssen Sie dies bei der nächsten Änderung Ihres Datasets nicht erneut tun, Sie können die Abfrage einfach aktualisieren, und Sie erhalten Ergebnisse basierend auf dem neuen Dataset.
Wie funktioniert das?
Lassen Sie mich nun kurz erklären, was mit diesem Ansatz passiert.
Als Erstes fügen wir im Power Query-Editor eine Indexspalte ein, die bei 1 beginnt und sich inkrementiert, wenn sie in der Zelle nach unten geht.
Wir tun dies, weil wir diese Spalte verwenden müssen, wenn wir die laufende Summe in einer anderen Spalte berechnen, die im nächsten Schritt eingefügt wird.
Dann fügen wir eine benutzerdefinierte Spalte ein und verwenden die folgende Formel
List.Sum(List.Range(#"Hinzugefügter Index"[Verkauf],0,[Index]))
Dies ist eine List.Sum-Formel, die Ihnen die Summe des darin angegebenen Bereichs liefert.
Der Bereich wird mit der Funktion List.Range angegeben.
Die List.Range-Funktion verwendet als Ausgabe den angegebenen Bereich in der Verkaufsspalte, und der Bereich variiert je nach Indexwert.Beispielsweise ist der Bereich für den ersten Datensatz der Erstverkaufswert.Dieser Bereich erweitert sich, wenn Sie die Zelle nach unten gehen.
Also für die erste Zelle.List.Sum gibt Ihnen nur die Summe des ersten Verkaufswerts, für die zweite Zelle gibt es Ihnen die Summe der ersten beiden Verkaufswerte und so weiter.
Obwohl dieser Ansatz gut funktioniert, kann er bei großen Datensätzen (Tausende von Zeilen) sehr langsam werden.
Berechnen Sie laufende Summen basierend auf Kriterien
Bisher haben wir Beispiele für die Berechnung einer laufenden Summe aller Werte in einer Spalte gesehen.
In einigen Fällen möchten Sie jedoch möglicherweise eine laufende Summe für einen bestimmten Datensatz berechnen.
Unten habe ich beispielsweise einen Datensatz, in dem ich die laufende Summe von Druckern und Scannern in zwei verschiedenen Spalten zählen möchte.
Dies kann mit der SUMMEWENN-Formel erfolgen, die eine laufende Summe berechnet und gleichzeitig sicherstellt, dass bestimmte Bedingungen erfüllt sind.
Hier ist die Formel, um dies für die Druckerspalte zu tun:
=SUMIF($C$2:C2,$D$1,$B$2:B2)
Um die laufende Summe des Scanners zu berechnen, verwenden Sie die folgende Formel:
=SUMIF($C$2:C2,$E$1,$B$2:B2)
In der obigen Formel habe ich SUMIF verwendet, der mir eine Summe in einem Bereich gibt, wenn bestimmte Bedingungen erfüllt sind.
Die Formel benötigt drei Parameter:
- Angebot: Dies ist der Bereich der Bedingungen, die mit der angegebenen Bedingung verglichen werden
- Kriterien: Dies ist das Kriterium, das nur überprüft wird, wenn dieses Kriterium erfüllt ist, dann wird der Wert im dritten Parameter, der Summenbereich, hinzugefügt
- [Summenbereich]: Dies ist der Bereich der Summe der zu addierenden Werte, wenn die Bedingung erfüllt ist
Darüber hinaus inAngebot和Summenbereich-Parameter habe ich den zweiten Teil der Referenz gesperrt, sodass sich der Bereich weiter ausdehnt, wenn wir uns in der Zelle nach unten bewegen.Dadurch können wir nur Werte bis zu diesem Bereich berücksichtigen und addieren (daher eine laufende Summe).
In dieser Formel verwende ich die Kopfspalten (Drucker und Scanner) als Kriterien.Sie können den Standard auch fest codieren, wenn Ihre Spaltenüberschriften nicht genau mit dem Standardtext übereinstimmen.
Wenn Sie mehrere Bedingungen überprüfen müssen, können Sie die SUMME-Formel verwenden.
Laufende Summen in einer Pivot-Tabelle
Wenn Sie Ihren PivotTable-Ergebnissen eine laufende Summe hinzufügen möchten, können Sie dies ganz einfach mit der integrierten Funktionalität in PivotTables tun.
Angenommen, Sie haben eine Pivot-Tabelle wie die folgende mit Daten in einer Spalte und Verkaufswerten in einer anderen.
Hier sind die Schritte zum Hinzufügen einer zusätzlichen Spalte, die die kumulativen Verkäufe nach Datum anzeigt:
- Ziehen Sie das Feld „Sale“ und legen Sie es im Bereich „Wert“ ab.
- Dadurch wird eine weitere Spalte mit dem Verkaufswert hinzugefügt
- Klicken Sie im Bereich Wert auf die Option Gesamtumsatz 2
- Klicken Sie auf die Option Wertfeldeinstellungen
- Ändern Sie im Dialogfeld Wertfeldeinstellungen den benutzerdefinierten Namen in Laufende Summe
- 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 sicher, dass in den Optionen für Basisfelder Datum ausgewählt ist
- OK klicken
Durch die obigen Schritte wird die zweite Verkaufsspalte in eine Spalte "laufende Summe" geändert.
Dies sind also einige der Methoden, die Sie verwenden können, um laufende Summen in Excel zu berechnen.Wenn Sie tabellarische Daten haben, können Sie einfache Formeln verwenden, und wenn Sie Excel-Tabellen haben, können Sie Formeln verwenden, die strukturierte Verweise verwenden.
Ich habe auch behandelt, wie man laufende Summen mit Power Query und PivotTables berechnet.
Ich hoffe, Sie fanden dieses Tutorial nützlich.
