Sie werden wahrscheinlich auf die Notwendigkeit laufender Summen stoßen, wenn Sie mit täglichen Daten arbeiten.

Stellen Sie sich vor, Sie verfolgen jeden Tag den Umsatz. Ihre Daten enthalten eine Zeile für jedes Datum mit einem Gesamtumsatzbetrag, aber vielleicht möchten Sie den Gesamtumsatz für den Monat an jedem Tag wissen. Dies ist eine laufende Summe, es ist die Summe aller Verkäufe bis einschließlich der aktuellen Tage Verkäufe.

In diesem Beitrag behandeln wir mehrere Möglichkeiten, eine laufende Summe für Ihre täglichen Daten zu berechnen. Wir werden untersuchen, wie Sie Arbeitsblattformeln, Pivot-Tabellen, Power Pivot mit DAX und Power Query verwenden.

Wir werden auch untersuchen, was mit der laufenden Gesamtberechnung passiert, wenn Datenzeilen eingefügt oder gelöscht werden und wie die Ergebnisse aktualisiert werden.

Holen Sie sich die Datei mit allen Beispielen.

Laufende Summen mit einer einfachen Formel

Es ist möglich, eine grundlegende laufende Gesamtformel mit dem Operator + zu erstellen.

Wir müssen jedoch zwei verschiedene Formeln verwenden, um die Arbeit zu erledigen.

1. =C3 ist die erste Formel und steht nur in der ersten Zeile der laufenden Summe.
2. =C4+D3 befindet sich in der zweiten Zeile und kann für die laufende Summe in die verbleibenden Zeilen kopiert werden.

Die Formel in unserer ersten Zeile kann die Zelle darüber nicht zur Summe hinzufügen, da sie einen Textwert für eine Spaltenüberschrift enthält. Dies würde einen #WERT verursachen! fehler in der laufenden Summe, da das + keine Textwerte verarbeiten kann. Wir vermeiden dies mit einer anderen Formel in der ersten Zeile, die nicht auf die obige Zelle verweist.

Was passiert mit der laufenden Summe, wenn wir Zeilen in unsere Daten einfügen oder löschen?

Das Einfügen einer neuen Zeile führt zu einer Lücke in der laufenden Summe. Um dies zu beheben, müssen wir die Formel von der ersten Zelle über den neu eingefügten Zeilen bis zur letzten Zeile kopieren.

Das Löschen von Zeilen führt zu #REF! fehler, da das Löschen einer Zeile das Löschen einer Zelle bedeutet, auf die in der darunter liegenden Formel verwiesen wird. Um dies zu beheben, müssen wir die Formel von der letzten fehlerfreien Zelle bis zur letzten Zeile kopieren.

Laufende Summen mit einer Summenformel

Wir können die Unbeholfenheit vermeiden, zwei verschiedene Formeln in unserer Spalte laufende Summe zu verwenden, indem wir die Funktion SUMME anstelle des Operators + verwenden. Wenn die Summenfunktion auf eine Textzelle trifft, wird sie wie a behandelt, obwohl sie eine 0 enthält.

Auf diese Weise können wir die folgende Formel einheitlich für jede Zeile einschließlich der ersten Zeile verwenden.

=SUM(C3,D2)

Diese Formel referenziert die Spaltenüberschrift, die Text für die erste Zeile enthält, aber das ist in Ordnung, da sie wie eine 0 behandelt wird.

Beim Einfügen oder Löschen von Zeilen treten immer noch die gleichen Probleme mit leeren Zellen und Fehlern auf. Wir können sie auf die gleiche Weise beheben wie bei laufenden Summen in der einfachen Formelmethode.

Ausführen von Summen mit einem teilweise festen Bereich

Eine weitere Option mit der SUM-Funktion besteht darin, nur auf die Spalte Sales zu verweisen und eine teilweise feste Bereichsreferenz zu verwenden.

Wenn wir die folgende Formel =SUM($C$3:C3) verwenden, können wir diese kopieren und in den Bereich einfügen. Es werden keine Spaltenüberschriften referenziert, und der referenzierte Bereich wächst zu jeder Zeile.

Leider hat auch dies die gleichen Probleme (und Lösungen) beim Einfügen oder Löschen von Zeilen.

Ausführen von Summen mit einem relativen benannten Bereich

Wir können die Probleme beim Einfügen und Löschen von Zeilen aus unseren Daten vermeiden, wenn wir einen relativen benannten Bereich verwenden. Dies bezieht sich auf die Zelle direkt darüber, unabhängig davon, wie viele Zeilen wir einfügen oder löschen.

Dies ist ein Trick, bei dem der Excel-Referenzstil vorübergehend von A1 auf R1C1 umgestellt wird. Definieren Sie dann einen benannten Bereich mit der R1C1-Notation. Wechseln Sie dann den Referenzstil zurück zu A1.

Im R1C1-Referenzstil werden Zellen dadurch referenziert, wie weit sie von der Zelle entfernt sind, die die Referenz verwendet. Zum Beispiel bezieht sich =RC auf die Zelle 2 oben und 3 rechts von der Zelle unter Verwendung dieser Formel.

Wir können diese relative Referenzierung verwenden, um einen benannten Bereich zu erstellen, der immer eine Zelle über der referenzierenden Zelle mit der Formel =RC .

Um den Referenzstil zu wechseln, gehen Sie zur Registerkarte Datei und wählen Sie Optionen. Gehen Sie zum Abschnitt Formel im Excel-Optionsmenü, aktivieren Sie das Kontrollkästchen R1C1-Referenzstil und drücken Sie dann die Schaltfläche OK.

Jetzt können wir unseren benannten Bereich hinzufügen. Wechseln Sie zur Registerkarte Formel des Excel-Menübands und wählen Sie den Befehl Name definieren.

Fügen Sie einen Namen wie „Oben“ als Namen des Bereichs ein. Fügen Sie die Formel = RC in die Eingabe Bezieht sich auf und drücken Sie die OK-Taste.

Wir können Excel jetzt wieder auf den Standardreferenzstil umstellen. Gehen Sie zur Registerkarte Datei > Im Abschnitt Formel > Deaktivieren Sie das Kontrollkästchen R1C1-Referenzstil > Drücken Sie dann die OK-Taste.

Jetzt können wir die Formel =SUM(,Oben) in unserer Spalte running total verwenden.

Der oben genannte Bereich bezieht sich immer auf die Zelle direkt darüber. Wenn wir Zeilen einfügen oder löschen, wird der relative benannte Bereich entsprechend angepasst und es ist keine Aktion erforderlich.

Wenn wir unsere Daten in eine Excel-Tabelle einfügen, wird die Formel automatisch für alle neuen Zeilen ausgefüllt, da die Formel für die gesamte Spalte einheitlich ist. Es ist keine Aktion erforderlich, um Formeln zu kopieren.

Ausführen von Summen mit einer Pivot-Tabelle

Pivot-Tabellen sind sehr nützlich, um jede Art von Daten zusammenzufassen. Sie beinhalten mehr als nur das Addieren, Zählen und Finden von Durchschnittswerten. Es gibt viele andere Arten von Berechnungen eingebaut, und es gibt tatsächlich eine laufende Gesamtberechnung!

Zuerst müssen wir eine Pivot-Tabelle basierend auf den Daten einfügen. Wählen Sie eine Zelle in den Daten aus, wechseln Sie zur Registerkarte Einfügen und wählen Sie den Befehl PivotTable. Gehen Sie dann durch das Fenster PivotTable erstellen, um auszuwählen, wo die Pivottable angezeigt werden soll, entweder in einem neuen Arbeitsblatt oder irgendwo in einem vorhandenen.

Fügen Sie das Datumsfeld in den Zeilenbereich der Pivot-Tabelle und dann das Verkaufsfeld in den Wertebereich der Pivot-Tabelle ein. Fügen Sie nun eine weitere Instanz des Verkaufsfeldes in den Bereich Zeilen ein.

Wir sollten jetzt zwei identische Verkaufsfelder haben, von denen eines als Summe von Verkauf2 bezeichnet wird. Wir können dieses Label jederzeit umbenennen, indem wir es einfach mit etwas wie Running Total .

Klicken Sie mit der rechten Maustaste auf einen der Werte im Feld Summe von Sales2 und wählen Sie Wert anzeigen als und dann Laufende Summe in.

Wir möchten die laufende Summe nach Datum anzeigen, daher müssen wir im nächsten Fenster Datum als Basisfeld auswählen.

Das war’s, wir haben jetzt eine neue Berechnung, die die laufende Summe unserer Verkäufe in der Pivot-Tabelle anzeigt.

Was passiert, wenn wir eine Zeile in unseren Quelldaten hinzufügen oder löschen, wie wirkt sich dies auf die laufende Summe aus? Die Pivot-Tabellenberechnungen sind dynamisch und berücksichtigen alle neuen Daten in der laufenden Gesamtberechnung.Klicken Sie mit der rechten Maustaste auf eine beliebige Stelle in der Pivot-Tabelle und wählen Sie Aktualisieren aus dem Menü.

Ausführen von Summen mit Power Pivot und DAX-Kennzahlen

Die ersten Schritte hierfür sind bei Verwendung einer regulären Pivot-Tabelle genau dieselben.

Wählen Sie eine Zelle in den Daten aus, wechseln Sie zur Registerkarte Einfügen und wählen Sie den Befehl PivotTable.

Wenn Sie zum Menü PivotTable erstellen gelangen, aktivieren Sie das Kontrollkästchen Diese Daten zum Datenmodell hinzufügen, um die Daten zum Datenmodell hinzuzufügen und für die Verwendung mit Power Pivot zu aktivieren.

Platzieren Sie das Feld Date im Bereich Rows und das Feld Sales im Bereich Values der Pivot-Tabelle.

Mit Power Pivot müssen wir alle zusätzlichen Berechnungen mit der DAX-Sprache erstellen. Klicken Sie im Fenster PivotTable-Felder mit der rechten Maustaste auf den Tabellennamen, und wählen Sie dann Kennzahl hinzufügen aus, um eine neue Berechnung zu erstellen. Beachten Sie, dass dies nur mit dem Datenmodell verfügbar ist.

=CALCULATE ( SUM ( Sales ), FILTER ( ALL (Sales ), Sales 

Jetzt können wir unser neues laufendes Gesamtmaß erstellen.

1. Im Measure-Fenster müssen wir einen Measure-Namen hinzufügen. In diesem Fall können wir die neue Kennzahl als laufende Summe bezeichnen.
2. Wir müssen auch die obige Formel in das Formelfeld einfügen.
3. Das Coole an Power Pivot ist die Möglichkeit, einem Measure ein Zahlenformat zuzuweisen. Wir können das Währungsformat für unsere Maßnahme auswählen. Immer wenn wir diese Kennzahl in einer Pivot-Tabelle verwenden, wird das Format automatisch angewendet.

Drücken Sie die OK-Taste und das neue Measure wird erstellt.

Im Fenster PivotTable-Felder wird ein neues Feld aufgelistet. Es hat ein kleines FX-Symbol auf der linken Seite, um anzuzeigen, dass es sich um eine Kennzahl und nicht um ein reguläres Feld in den Daten handelt.

Wir können dieses neue Feld wie jedes andere Feld verwenden und es in den Wertebereich ziehen, um unsere laufende Gesamtberechnung in die Pivot-Tabelle aufzunehmen.

Was passiert mit der laufenden Summe, wenn wir Daten aus der Quelltabelle hinzufügen oder entfernen? Genau wie bei einer normalen Pivot-Tabelle müssen wir einfach mit der rechten Maustaste auf die Pivot-Tabelle klicken und Aktualisieren auswählen, um die Berechnung zu aktualisieren.

Laufende Summen mit einer Power Query

Wir können unseren Daten auch laufende Summen mit Power Query hinzufügen.

Zuerst müssen wir die Tabelle in Power Query importieren. Wählen Sie die Datentabelle aus, wechseln Sie zur Registerkarte Daten und wählen Sie die Option Aus Tabelle / Bereich. Dadurch wird der Power Query-Editor geöffnet.

Als nächstes können wir unsere Daten nach Datum sortieren. Dies ist ein optionaler Schritt, den wir hinzufügen können, sodass die laufende Summe weiterhin nach Datum angezeigt wird, wenn wir die Reihenfolge unserer Quelldaten ändern.

Klicken Sie auf den Filterschalter in der Spaltenüberschrift Datum und wählen Sie Aufsteigend sortieren aus den Optionen.

Wir müssen eine Indexspalte hinzufügen. Dies wird später in der laufenden Gesamtberechnung verwendet. Gehen Sie zur Registerkarte Spalte hinzufügen und klicken Sie auf den kleinen Pfeil neben der Indexspalte, um einen Index ab 1 in die erste Zeile einzufügen.

Wir müssen unserer Abfrage eine neue Spalte hinzufügen, um die laufende Summe zu berechnen. Wechseln Sie zur Registerkarte Spalte hinzufügen und wählen Sie den Befehl Benutzerdefinierte Spalte.

Wir können die Spalte als Laufende Summe benennen und die folgende Formel hinzufügen.

Die Liste.Range-Funktion erstellt eine Liste von Werten aus der Spalte Sales ab der 1. Zeile (0. Element), die eine Reihe von Zeilen basierend auf dem Wert in der Spalte Index umfasst.

Die Liste.Die Summenfunktion addiert dann diese Liste von Werten, die unsere laufende Summe ist.

Wir brauchen die Indexspalte nicht mehr, sie hat ihren Zweck erfüllt und wir können sie entfernen. Klicken Sie mit der rechten Maustaste auf die Spaltenüberschrift und wählen Sie Entfernen aus den Optionen.

Wir haben unsere laufende Summe und sind mit dem Abfrage-Editor fertig. Wir können die Abfrage schließen und die Ergebnisse in ein neues Arbeitsblatt laden. Gehen Sie zur Registerkarte Start des Abfrage-Editors und drücken Sie die Schaltfläche Schließen & Laden.

Was passiert mit der laufenden Summe, wenn wir Zeilen aus unseren Quelldaten hinzufügen oder entfernen? Wir müssen die Power Query-Ausgabetabelle aktualisieren, um die laufende Summe mit den Änderungen zu aktualisieren. Klicken Sie mit der rechten Maustaste auf eine beliebige Stelle in der Tabelle und wählen Sie Aktualisieren, um die Tabelle zu aktualisieren.

Wenn wir mit dem optionalen Sortierschritt oben Daten außerhalb der Reihenfolge zu den Quelldaten hinzufügen, sortiert Power Query nach Datum und gibt die richtige Reihenfolge nach Datum für die laufende Summe zurück.

Schlussfolgerungen

Es gibt viele verschiedene Möglichkeiten, laufende Summen in Excel zu berechnen.

Wir haben Optionen untersucht, darunter Formeln im Arbeitsblatt, Pivot-Tabellen, Power Pivot DAX-Formeln und Power Query. Einige bieten eine robustere Lösung beim Hinzufügen oder Entfernen von Zeilen aus den Daten, andere Methoden bieten eine einfachere Implementierung.

Einfache Formeln im Arbeitsblatt sind einfach einzurichten, können jedoch nicht einfach neue Datenzeilen einfügen oder löschen. Andere Lösungen wie Pivot-Tabellen, DAX und Power Query sind robuster und verarbeiten das Einfügen oder Löschen von Datenzeilen problemlos, sind jedoch schwieriger einzurichten.

Es ist gut, sich der Vor- und Nachteile jeder Methode bewusst zu sein und die am besten geeignete auszuwählen. Wenn Sie keine neuen Daten einfügen oder löschen, sind Arbeitsblattformeln möglicherweise der richtige Weg.