Methode 1. Formeln
Beginnen wir zum Aufwärmen mit der einfachsten Option – Formeln. Wenn wir eine nach Datum sortierte kleine Tabelle als Eingabe haben, benötigen wir zur Berechnung der laufenden Summe in einer separaten Spalte eine elementare Formel:
Das Hauptmerkmal hier ist die knifflige Festlegung des Bereichs innerhalb der SUM-Funktion – der Bezug zum Anfang des Bereichs wird absolut (mit Dollarzeichen) und zum Ende relativ (ohne Dollars) gemacht. Dementsprechend erhalten wir beim Kopieren der Formel auf die gesamte Spalte einen expandierenden Bereich, dessen Summe wir berechnen.
Die Nachteile dieses Ansatzes liegen auf der Hand:
- Die Tabelle muss nach Datum sortiert sein.
- Beim Hinzufügen neuer Zeilen mit Daten muss die Formel manuell erweitert werden.
Methode 2. Pivot-Tabelle
Diese Methode ist etwas komplizierter, aber viel angenehmer. Und um es noch schlimmer zu machen, betrachten wir ein ernsteres Problem – eine Tabelle mit 2000 Datenzeilen, in der es keine Sortierung nach der Datumsspalte gibt, aber es gibt Wiederholungen (dh wir können am selben Tag mehrmals verkaufen):
Wir wandeln unsere ursprüngliche Tabelle in eine „intelligente“ (dynamische) Tastenkombination um Ctrl+T oder Team Startseite – Als Tabelle formatieren (Home — Als Tabelle formatieren), und dann bauen wir mit dem Befehl eine Pivot-Tabelle darauf auf Einfügen – PivotTable (Einfügen – Pivot-Tabelle). Wir tragen das Datum in den Zeilenbereich in der Zusammenfassung und die Anzahl der verkauften Waren in den Wertebereich ein:
Bitte beachten Sie, dass bei einer noch nicht ganz alten Excel-Version die Daten automatisch nach Jahren, Quartalen und Monaten gruppiert werden. Wenn Sie eine andere Gruppierung benötigen (oder überhaupt nicht), können Sie dies korrigieren, indem Sie mit der rechten Maustaste auf ein beliebiges Datum klicken und Befehle auswählen Gruppieren / Gruppierung aufheben (Gruppieren / Gruppierung aufheben).
Wenn Sie sowohl die resultierenden Summen nach Perioden als auch die laufende Summe in einer separaten Spalte sehen möchten, dann ist es sinnvoll, das Feld in den Wertebereich zu werfen Ausverkauft erneut, um ein Duplikat des Feldes zu erhalten – darin schalten wir die Anzeige der laufenden Summen ein. Klicken Sie dazu mit der rechten Maustaste auf das Feld und wählen Sie den Befehl aus Zusätzliche Berechnungen – Kumulierte Summe (Werte anzeigen als — Laufende Summen):
Dort können Sie auch die Option auswählen, die Summen prozentual zu vergrößern, und im nächsten Fenster müssen Sie das Feld auswählen, für das die Akkumulation verwendet werden soll – in unserem Fall ist dies das Datumsfeld:
Die Vorteile dieser Vorgehensweise:
- Eine große Datenmenge wird schnell gelesen.
- Es müssen keine Formeln manuell eingegeben werden.
- Beim Ändern in den Quelldaten reicht es aus, die Zusammenfassung mit der rechten Maustaste oder mit dem Befehl Daten – Alle aktualisieren zu aktualisieren.
Die Nachteile ergeben sich aus der Tatsache, dass es sich um eine Zusammenfassung handelt, was bedeutet, dass Sie darin nicht tun können, was Sie wollen (Zeilen einfügen, Formeln schreiben, beliebige Diagramme erstellen usw.) wird nicht mehr funktionieren.
Methode 3: Power-Abfrage
Lassen Sie uns unsere „intelligente“ Tabelle mit Quelldaten mithilfe des Befehls in den Power Query-Abfrageeditor laden Daten – Aus Tabelle/Bereich (Daten – Aus Tabelle/Bereich). In den neuesten Versionen von Excel wurde es übrigens umbenannt – jetzt heißt es Mit Blättern (Vom Blatt):
Dann führen wir die folgenden Schritte aus:
1. Sortieren Sie die Tabelle mit dem Befehl aufsteigend nach der Datumsspalte Aufsteigend sortieren in der Filter-Dropdown-Liste im Tabellenkopf.
2. Etwas später, um die laufende Summe zu berechnen, benötigen wir eine Hilfsspalte mit der ordinalen Zeilennummer. Fügen wir es mit dem Befehl hinzu Spalte hinzufügen – Indexspalte – Von 1 (Spalte hinzufügen – Indexspalte – Von 1).
3. Um die laufende Summe zu berechnen, benötigen wir außerdem einen Verweis auf die Spalte Ausverkauft, wo unsere zusammengefassten Daten liegen. In Power Query werden Spalten auch als Listen (Liste) bezeichnet und um einen Link darauf zu erhalten, klicken Sie mit der rechten Maustaste auf die Spaltenüberschrift und wählen Sie den Befehl aus Detaillierung (Detail anzeigen). Der benötigte Ausdruck erscheint in der Formelleiste, bestehend aus dem Namen des vorherigen Schritts #"Index hinzugefügt", von wo wir die Tabelle und den Spaltennamen nehmen [Verkauf] aus dieser Tabelle in eckigen Klammern:
Kopieren Sie diesen Ausdruck zur weiteren Verwendung in die Zwischenablage.
4. Löschen Sie unnötigen letzten Schritt Ausverkauft und fügen Sie stattdessen eine berechnete Spalte zur Berechnung der laufenden Summe mit dem Befehl hinzu Hinzufügen einer Spalte – Benutzerdefinierte Spalte (Spalte hinzufügen – Benutzerdefinierte Spalte). Die Formel, die wir brauchen, sieht so aus:
Hier die Funktion Liste.Bereich nimmt die ursprüngliche Liste (Spalte [Verkauf]) und extrahiert Elemente daraus, beginnend mit dem ersten (in der Formel ist dies 0, da die Nummerierung in Power Query bei Null beginnt). Die Anzahl der abzurufenden Elemente ist die Zeilennummer, die wir aus der Spalte entnehmen [Index]. Diese Funktion für die erste Zeile gibt also nur eine erste Zelle der Spalte zurück Ausverkauft. Für die zweite Zeile – schon die ersten zwei Zellen, für die dritte – die ersten drei usw.
Nun, dann die Funktion Liste.Summe summiert die extrahierten Werte und wir erhalten in jeder Zeile die Summe aller vorherigen Elemente, dh kumulative Summe:
Es bleibt, die Indexspalte zu löschen, die wir nicht mehr benötigen, und die Ergebnisse mit dem Befehl Home – Close & Load to wieder in Excel hochzuladen.
Das Problem ist gelöst.
Schnell und wütend
Im Prinzip hätte man das stoppen können, aber es gibt einen kleinen Wermutstropfen – der von uns erstellte Request arbeitet im Tempo einer Schildkröte. Auf meinem nicht gerade schwächsten PC wird beispielsweise eine Tabelle mit nur 2000 Zeilen in 17 Sekunden verarbeitet. Was ist, wenn mehr Daten vorhanden sind?
Um dies zu beschleunigen, können Sie die Pufferung mit der speziellen List.Buffer-Funktion verwenden, die die ihr als Argument übergebene Liste (Liste) in den Arbeitsspeicher lädt, was den Zugriff darauf in Zukunft erheblich beschleunigt. In unserem Fall ist es sinnvoll, die Liste #“Added index“[Sold] zu puffern, auf die Power Query zugreifen muss, um die laufende Summe in jeder Zeile unserer 2000-Zeilen-Tabelle zu berechnen.
Klicken Sie dazu im Power Query-Editor auf der Registerkarte Main auf die Schaltfläche Advanced Editor (Home – Advanced Editor), um den Quellcode unserer Abfrage in der in Power Query integrierten M-Sprache zu öffnen:
Und dann fügen Sie dort eine Zeile mit einer Variablen hinzu Meine Liste, deren Wert von der Pufferfunktion zurückgegeben wird, und im nächsten Schritt ersetzen wir den Aufruf der Liste durch diese Variable:
Nach diesen Änderungen wird unsere Abfrage erheblich schneller und bewältigt eine Tabelle mit 2000 Zeilen in nur 0.3 Sekunden!
Eine andere Sache, oder? 🙂
- Pareto-Diagramm (80/20) und wie man es in Excel erstellt
- Schlüsselwortsuche in Text- und Abfragepufferung in Power Query