Sie werden wahrscheinlich auf den Bedarf an laufenden Summen stoßen, wenn Sie mit irgendwelchen täglichen Daten arbeiten.
Stellen Sie sich vor, Sie verfolgen den Umsatz jeden Tag. Ihre Daten enthalten für jedes Datum eine Zeile mit einem Gesamtumsatz, aber vielleicht möchten Sie auch 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 Verkäufe des aktuellen Tages.
In diesem Beitrag werden wir mehrere Möglichkeiten zur Berechnung einer laufenden Summe für Ihre täglichen Daten behandeln. Wir werden untersuchen, wie Sie Arbeitsblattformeln, Pivot-Tabellen, Power Pivot mit DAX und Power Query verwenden können.
Wir werden auch untersuchen, was mit der Berechnung der laufenden Summe passiert, wenn Sie Datenzeilen einfügen oder löschen, und wie Sie die Ergebnisse aktualisieren.
Holen Sie sich die Datei mit allen Beispielen.
Inhaltsverzeichnis
Summenberechnung mit einer einfachen Formel
Eine einfache Summenformel lässt sich mit dem Operator + erstellen.
Wir müssen jedoch zwei verschiedene Formeln verwenden, um die Aufgabe zu erledigen.
- =C3 wird die erste Formel sein und nur in der ersten Zeile der laufenden Summe stehen.
- =C4+D3 wird in der zweiten Zeile stehen und kann in die restlichen Zeilen der laufenden Summe 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. Dadurch würde in der laufenden Summe ein #VALUE! Fehler erscheinen, da das + nicht mit Textwerten umgehen kann. Wir vermeiden dies mit einer anderen Formel in der ersten Zeile, die nicht auf die Zelle darüber verweist.
Was passiert mit der laufenden Summe, wenn wir Zeilen in unseren 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 oberhalb der neu eingefügten Zeilen bis zur letzten Zeile nach unten kopieren.
Das Löschen von Zeilen führt zu #REF!-Fehlern, da das Löschen einer Zeile das Löschen einer Zelle bedeutet, auf die die Formel darunter verweist. Um dies zu beheben, müssen wir die Formel von der letzten fehlerfreien Zelle bis zur letzten Zeile kopieren.
Summenbildung mit einer SUMmenformel
Wir können die Unannehmlichkeit vermeiden, zwei verschiedene Formeln in unserer Spalte für die laufende Summe zu verwenden, indem wir die SUMmenfunktion anstelle des +-Operators verwenden. Wenn die SUM-Funktion auf eine Textzelle stößt, behandelt sie diese so, als ob sie eine 0 enthielte.
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 bezieht sich auf 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 werden wir immer noch die gleichen Probleme mit leeren Zellen und Fehlern haben. Wir können sie auf die gleiche Weise beheben wie bei laufenden Summen in der einfachen Formelmethode.
Laufende Summen mit teilweise festem Bereich
Eine andere Möglichkeit mit der Funktion SUMME ist, nur die Spalte Umsatz zu referenzieren und einen teilweise festen Bereichsbezug zu verwenden.
Wenn wir die folgende Formel =SUMME($C$3:C3) verwenden, können wir diese kopieren und in den Bereich einfügen. Es wird keine Spaltenüberschriften referenzieren und der referenzierte Bereich wird zu jeder Zeile wachsen.
Leider gibt es auch hier die gleichen Probleme (und Lösungen) mit dem Einfügen oder Löschen von Zeilen.
Summen mit einem relativ benannten Bereich ausführen
Wir können die Probleme mit dem Einfügen und Löschen von Zeilen aus unseren Daten vermeiden, wenn wir einen relativ benannten Bereich verwenden. Dieser bezieht sich auf die Zelle direkt darüber, egal wie viele Zeilen wir einfügen oder löschen.
Dieser Trick besteht darin, dass wir das Excel-Referenzformat vorübergehend von A1 auf R1C1 umstellen. Dann definieren Sie einen benannten Bereich mit der Notation R1C1. Dann schalten Sie den Referenzstil wieder auf A1 um.
Im R1C1-Referenzstil werden Zellen dadurch bezeichnet, wie weit sie von der Zelle entfernt sind, die die Referenz verwendet. Zum Beispiel bezieht sich =RC auf die Zelle 2 oberhalb und 3 rechts von der Zelle, die diese Formel verwendet.
Wir können diese relative Referenzierung verwenden, um einen benannten Bereich zu erstellen, der immer eine Zelle oberhalb der referenzierenden Zelle mit der Formel =RC liegt.
Um den Referenzstil zu wechseln, gehen Sie auf die Registerkarte Datei und wählen Sie Optionen. Gehen Sie im Excel-Menü Optionen zum Abschnitt Formel und aktivieren Sie das Kontrollkästchen Referenzstil R1C1 und drücken Sie dann die Schaltfläche OK.
Nun können wir unseren benannten Bereich hinzufügen. Gehen Sie auf die Registerkarte Formel im Excel-Ribbon und wählen Sie den Befehl Name definieren.
Geben Sie einen Namen wie „Oben“ als Namen des Bereichs ein. Fügen Sie die Formel =RC in die Eingabe Referenziert auf ein und drücken Sie die OK-Schaltfläche.
Wir können nun Excel wieder auf den Standard-Referenzstil umstellen. Gehen Sie dazu auf die Registerkarte Datei > Optionen im Abschnitt Formel > entfernen Sie das Häkchen bei der Referenzvorlage R1C1 > und drücken Sie die Schaltfläche OK.
Nun können wir die Formel =SUMME(,Oben) in unserer laufenden Summenspalte verwenden.
Der benannte Bereich Oben wird sich immer auf die Zelle direkt darüber beziehen. Wenn wir Zeilen einfügen oder löschen, passt sich der relative benannte Bereich entsprechend an und es ist keine Aktion erforderlich.
Wenn wir unsere Daten in einer Excel-Tabelle platzieren, 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 irgendwelche Formeln zu kopieren.
Summen mit einer Pivot-Tabelle bilden
Pivot-Tabellen sind super nützlich, um jede Art von Daten zusammenzufassen. Sie können mehr als nur addieren, zählen und Durchschnittswerte ermitteln. Es sind viele andere Arten von Berechnungen eingebaut, und es gibt sogar eine laufende Gesamtberechnung!
Zunächst müssen wir eine Pivot-Tabelle basierend auf den Daten einfügen. Markieren Sie dazu eine Zelle innerhalb der Daten, gehen Sie auf die Registerkarte Einfügen und wählen Sie den Befehl PivotTable. Wählen Sie dann im Fenster „PivotTable erstellen“ aus, wo die Pivot-Tabelle eingefügt werden soll, entweder in einem neuen Arbeitsblatt oder irgendwo in einem bestehenden.
Fügen Sie das Feld Datum in den Bereich Zeilen der Pivot-Tabelle ein, dann das Feld Umsatz in den Bereich Werte der Pivot-Tabelle. Fügen Sie nun eine weitere Instanz des Feldes „Sales“ in den Bereich „Rows“ ein.
Wir sollten nun zwei identische Felder „Umsatz“ haben, von denen eines mit „Summe Umsatz2“ beschriftet ist. Wir können diese Beschriftung jederzeit umbenennen, indem wir sie einfach mit etwas wie „Laufende Summe“ überschreiben.
Klicken Sie mit der rechten Maustaste auf einen der Werte im Feld „Summe des Umsatzes2“ und wählen Sie „Wert anzeigen als“ und dann „Laufende Summe in“.
Wir wollen die laufende Summe nach Datum anzeigen, also 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 innerhalb der Pivot-Tabelle anzeigt.
Was passiert, wenn wir eine Zeile in unseren Quelldaten hinzufügen oder löschen, wie wirkt sich das auf die laufende Summe aus? Die Berechnungen der Pivot-Tabelle sind dynamisch und berücksichtigen alle neuen Daten in der Berechnung der laufenden Summe, wir müssen nur die Pivot-Tabelle aktualisieren.
Klicken Sie mit der rechten Maustaste auf eine beliebige Stelle in der Pivot-Tabelle und wählen Sie Aktualisieren aus dem Menü.
Summenberechnung mit Power Pivot und DAX-Maßen
Die ersten Schritte hierfür sind genau die gleichen wie bei einer normalen Pivot-Tabelle.
Markieren Sie eine Zelle innerhalb der Daten und wählen Sie auf der Registerkarte Einfügen den Befehl PivotTable.
Wenn Sie zum Menü „PivotTable erstellen“ kommen, aktivieren Sie das Kontrollkästchen „Diese Daten zum Datenmodell hinzufügen“, um die Daten zum Datenmodell hinzuzufügen und sie für die Verwendung mit Power Pivot zu aktivieren.
Platzieren Sie das Feld „Datum“ im Bereich „Zeilen“ und das Feld „Umsatz“ im Bereich „Werte“ der Pivot-Tabelle.
Mit Power Pivot müssen wir alle gewünschten zusätzlichen Berechnungen mithilfe der DAX-Sprache erstellen. Klicken Sie mit der rechten Maustaste auf den Tabellennamen im Fenster PivotTable-Felder und wählen Sie dann Messung hinzufügen, um eine neue Berechnung zu erstellen. Beachten Sie, dass dies nur mit dem Datenmodell verfügbar ist.
=CALCULATE ( SUM ( Sales ), FILTER ( ALL (Sales ), Sales Nun können wir unsere neue laufende Gesamtmaßnahme erstellen.
- Im Fenster „Maßnahme“ müssen wir einen Maßnahmennamen hinzufügen. In diesem Fall können wir das neue Measure als „Running Total“ benennen.
- Wir müssen auch die obige Formel in das Feld „Formel“ eingeben.
- Das Tolle an Power Pivot ist die Möglichkeit, einem Measure ein Zahlenformat zuzuweisen. Wir können das Währungsformat für unsere Kennzahl wählen. Wenn wir diese Kennzahl in einer Pivot-Tabelle verwenden, wird das Format automatisch angewendet.
Drücken Sie auf die Schaltfläche „OK“ und die neue Kennzahl wird erstellt.
Im Fenster „PivotTable-Felder“ wird ein neues Feld angezeigt. Es hat ein kleines fx-Symbol auf der linken Seite, um anzuzeigen, dass es eine Kennzahl und kein normales Feld in den Daten ist.
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 einzufügen.
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 mithilfe von Power Query hinzufügen.
Zuerst müssen wir die Tabelle in Power Query importieren. Markieren Sie die Datentabelle, gehen Sie auf die Registerkarte Daten und wählen Sie die Option Aus Tabelle/Bereich. Dadurch wird der Power Query-Editor geöffnet.
Nächstens können wir unsere Daten nach Datum sortieren. Dies ist ein optionaler Schritt, den wir hinzufügen können, so dass, wenn wir die Reihenfolge unserer Quelldaten ändern, die laufende Summe immer noch nach Datum angezeigt wird.
Klicken Sie auf den Filter-Knopf in der Überschrift der Datumsspalte und wählen Sie „Aufsteigend sortieren“ aus den Optionen.
Wir müssen eine Indexspalte hinzufügen. Diese wird später bei der Berechnung der laufenden Summe verwendet. Gehen Sie auf die Registerkarte Spalte hinzufügen und klicken Sie auf den kleinen Pfeil neben der Indexspalte, um einen Index beginnend bei 1 in der ersten Zeile einzufügen.
Wir müssen eine neue Spalte zu unserer Abfrage hinzufügen, um die laufende Summe zu berechnen. Gehen Sie auf die 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 List.Range-Funktion erstellt eine Liste von Werten aus der Spalte „Umsatz“, beginnend mit der 1. Zeile (0. Element), die sich über eine Anzahl von Zeilen erstreckt, die auf dem Wert in der Indexspalte basiert.
Die List.Sum-Funktion 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 fertig mit dem Abfrage-Editor. Wir können die Abfrage schließen und die Ergebnisse in ein neues Arbeitsblatt laden. Gehen Sie zur Registerkarte Start des Abfrageeditors 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 der Tabelle und wählen Sie Aktualisieren, um die Tabelle zu aktualisieren.
Mit dem optionalen Sortierschritt oben, wenn wir Daten in falscher 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 Optionen für die Berechnung von laufenden Summen in Excel.
Wir haben uns mit Optionen wie Formeln im Arbeitsblatt, Pivot-Tabellen, Power-Pivot-DAX-Formeln und Power Query beschäftigt. Einige bieten eine robustere Lösung beim Hinzufügen oder Entfernen von Datenzeilen, andere Methoden bieten eine einfachere Implementierung.
Einfache Formeln im Arbeitsblatt sind einfach einzurichten, kommen aber mit dem Einfügen oder Löschen neuer Datenzeilen nicht gut zurecht. Andere Lösungen wie Pivot-Tabellen, DAX und Power Query sind robuster und können das Einfügen oder Löschen von Datenzeilen einfach handhaben, sind aber schwieriger einzurichten.
Es ist gut, sich der Vor- und Nachteile jeder Methode bewusst zu sein und die am besten geeignete zu wählen. Wenn Sie keine neuen Daten einfügen oder löschen wollen, dann sind Arbeitsblattformeln vielleicht die richtige Wahl.