Maximum von „Laufende Summe“ („running total“)

Xtract: Bedingt durch Negativwerte einer Liste ist die laufende Summe nicht immer progressiv (aufsteigend). Unterschiedliche Wege führen zum Ziel, die Zeit-aktuelle Summe zu berechnen.

Per Formel bei (relativ) wenigen Daten

Um mit den gleichen Daten zu arbeiten wie ich, schreiben Sie die folgenden Daten ab oder laden Sie der Bequemlichkeit halber gleich diese Datei von unserem Server herunter. Hier (vorerst) die kleine Datensammlung:

TimeStamp10 Werte
2021-01-01 00:00:001509,6
2021-01-01 00:01:00155,49
2021-01-01 00:02:00-318,48
2021-01-01 00:03:00-666,55
2021-01-01 00:04:00-339,41
2021-01-01 00:05:001798,87
2021-01-01 00:06:00-1057,88
2021-01-01 00:07:00424,7
2021-01-01 00:08:00-1597,82
2021-01-01 00:09:00209,72

Um die laufende Summe mit einer Formel zu berechnen schreiben Sie erst einmal in die Zelle C1 die Überschrift lfd. Summe. Ich erweitere dazu die existierende „Intelligente“ Tabelle um die eine Spalte, indem ich auf das kleine Dreieck in der letzten Zelle der Tabelle klicken und ziehe diese Markierung nach C11. In die Zelle C2 schreiben Sie nun diese Formel: =SUMME(B$2:B2) und automatisch wird der Bereich bis C11 ausgefüllt.

Um das Maximum der so berechneten Werte zu berechnen schreiben Sie in eine beliebige Zelle außerhalb der Tabelle die Formel =MAX(Tabelle1[lfd. Summe]) (oder wenn Sie die Tabelle nicht erweitert hatten: =MAX(C2:C11) ) und der gewünschte Wert wird berechnet. Bei meinen Zahlen ist das der Wert 2.139,52. Eine der Intelligenten Tabelle angepasste Methode: Ergänzen Sie die Tabelle um eine Ergebniszeile und wählen Sie statt der automatisch berechneten Summe das Maximum.

▲ nach oben …

Berechnung per VBA (Makro)

Bei diesen 10 Zeilen „lohnt“ sich eigentlich nicht der Einsatz von VBA. Der Aufwand wäre aus meiner Sicht zu hoch. Das gilt zwar auch prinzipiell für 50 Datenzeilen, aber wenn Sie eine solche Liste als „Spielwiese“ nutzen wollen, dann führen Sie auf die Registerkarte Tabelle1 einen Rechtsklick aus und wählen im Kontextmenü die einzige Möglichkeit Einblenden… Nach einem Klick auf OK wird diese (einzige derzeit versteckte) Registerkarte sichtbar.

Im ersten Schritt werden Sie aus der tabellarischen Darstellung eine Formatierte (Intelligente) Tabelle erstellen. Ich klicke dazu in die Daten und StrgT oder StrgL. Den Namen der Tabelle ändere ich ausnahmsweise einmal nicht, ich belasse es bei Tabelle2. In Spalte C habe ich für Sie schon einmal die aus der vorherigen Übung bekannte Formel angewendet und in E2 die MAX()-Berechnung ausgeführt. Und in F2 sehen Sie das durch eine VBA-Funktion berechnete Ergebnis. Hier der von mir verwendete Code:

Option Explicit
Option Base 1

Function MaxVonRunningTotal(TblName As String)
   Dim Anz As Long
   Dim aData(), aRT()
   Dim loData As ListObject
   Dim ArrZe As Long, MaxWert As Single

   Set loData = ActiveSheet.ListObjects(TblName)
   Anz = loData.ListRows.Count
   'Das Array mit den Daten befüllen
   aData = loData.Range
   MaxWert = aData(2, 2)
   Debug.Print "x"  'Kann entfernt werden, nur zur Kontrolle
   ReDim aRT(Anz)
   aRT(1) = aData(2, 2)

   For ArrZe = 2 To Anz
      aRT(ArrZe) = aRT(ArrZe - 1) + aData(ArrZe + 1, 2)
      If aRT(ArrZe) > MaxWert Then MaxWert = aRT(ArrZe)
   Next ArrZe

   'Rückgabe in Workshet
   MaxVonRunningTotal = MaxWert
End Function

Zugegeben, ich könnte den Code an dieser oder jener Stelle noch straffen. Aber ich habe schon verschiedenes eingesetzt, was über das reine Einsteiger-Wissen hinaus geht. Analysieren Sie den Code gerne, um vielleicht an diese oder jener Stelle etwas Wissen zu schöpfen.

▲ nach oben …

Sehr große Datenmengen mittels Power Query verarbeiten

Die 50 Datensätze aus der letzten Übung sind ja für Excel kaum des Einstiegs wert. 🙂 VBA ist da absolut unterfordert, aber es ging ja „nur“ um das Prinzip. Aber für die PQ-Übung dürfen es gerne ein „wenig“ mehr Daten sein. 👿 Dazu erstellen Sie eine Liste mit kalendarischen Daten, beginnend mit dem 1. Januar 2000 00:00 (!) und endend mit dem 01.04.2020 00:00, also 19 Jahre und 3 Monate umfassend. Und damit Excel bei nicht allzu großzügigen Arbeitsspeicher nicht „in die Knie geht“, mehr als 10 Minuten rechnet oder gar abstürzt, soll der zeitliche Abstand zwischen den Datensätzen 1 Stunde sein. Das werden dann 177.505 Zeilen sein, und diese Datenmenge ist schon ein wirklich gutes Argument für den Einsatz von Power Query.

Natürlich werden es wieder 2 Spalten sein und die Werte-Spalte wird nach dem Zufalls-Prinzip mit einer entsprechenden Funktion generiert. Ach ja, damit es so richtig interessant wird und Sie mit minimalen Änderungen auch einen 10-Minuten-Rhythmus (mit effektiv >1,065 Millionen Zeilen) generieren können, soll (und muss) das Ganze mit PQ geschehen, denn die Grenze eines Tabellenblatts liegt ja bei 1,048 Millionen Zeilen. Versuchen Sie es doch einfach einmal, diese Tabelle/Abfrage zu erstellen und dann (natürlich) auch noch den höchsten Wert der laufenden Summe nach dem gleichen Muster wie bisher aber mit den Mitteln des Power Query zu berechnen.

Ich räume ein, dass das nicht „mal so eben“ zu erledigen ist. Aber wenn Sie genügend Zeit investieren und auch die Logik des PQ einigermaßen verinnerlicht haben, dann werden Sie im Internet fündig. Es gibt mehrere Lösungswege! Ich habe verschiedene Fundstellen ausprobiert und alle als prinzipiell zu schwer nachvollziehbar ausgesondert. Für mich steht das Verstehen eines Ablaufs im Vordergrund, die Copy/Paste-Methode führt bei einer eventuellen Fehlersuche im eigenen Projekt oft zu Frustrationen. Darum habe ich meinen eigenen Weg für die laufende Summe in Power Query erarbeitet, denn ich möchte möglichst schnell verstehen, wie der Ablauf einer Abfrage ist. Und ja, das dauert vielleicht etwas länger, als die anderen vorgestellten Möglichkeiten. Das ist der Preis für Transparenz, den ich gerne bezahle.

Wenn Sie interessiert sind, schreiben Sie mir eine Mail. Gegen eine wirklich kleine Spende (>= 5,00€) sende ich Ihnen eine unkommentierte *.xlsx sofort zu, welche Sie schrittweise im rechten Seitenfenster analysieren können. Wenn Sie einen Erklärungsbeitrag nach bekanntem Muster haben wollen, sagen Sie mir dieses bitte per Mail oder Telefon. Da wäre dann aber etwas Geduld erforderlich, weil ich bis zur ersten diesbezüglichen Anfrage warten werde; meine Erfahrung zeigt, dass extrem wenige Nutzer meines Blogs bereit sind, meine Arbeit auch pekuniär zu honorieren.

Sorry, ich hatte einen Datenverlust. Darum ist dieses Angebot zurzeit nicht aktuell!

▲ nach oben …

Dieser Beitrag wurde unter Code-Schnipsel, Datum & Zeit, Datum und Zeit, Foren-Q&A, Mit VBA/Makro, Ohne Makro/VBA, Power Query, PQ-Formeln (Sprache M), Rechnen & Zahlen, Spende/Donation, Tabelle und Zelle abgelegt und mit , , , , , , verschlagwortet. Setze ein Lesezeichen auf den Permalink.