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

Um mit den gle­ichen Dat­en zu arbeit­en wie ich, schreiben Sie die fol­gen­den Dat­en ab oder laden Sie der Bequem­lichkeit hal­ber gle­ich diese Datei von unserem Serv­er herunter. Hier (vor­erst) die kleine Daten­samm­lung:

Um die laufende Summe mit ein­er Formel zu berech­nen schreiben Sie erst ein­mal in die Zelle C1 die Über­schrift lfd. Summe. Ich erweit­ere dazu die existierende „Intel­li­gente” Tabelle um die eine Spalte, indem ich auf das kleine Dreieck in der let­zten Zelle der Tabelle Klick­en und ziehe diese Markierung nach C11. In die Zelle C2 schreiben Sie nun diese Formel: =SUMME(B$2:B2) und automa­tisch wird der Bere­ich bis C11 aus­ge­füllt.

Um das Max­i­mum der so berech­neten Werte zu berech­nen schreiben Sie in eine beliebige Zelle außer­halb der Tabelle die Formel =MAX(Tabelle1[lfd. Summe]) (oder wenn Sie die Tabelle nicht erweit­ert hat­ten: =MAX(C2:C11) ) und der gewün­schte Wert wird berech­net. Bei meinen Zahlen ist das der Wert 2.139,52. Eine der Intel­li­gen­ten Tabelle angepasste Meth­ode: Ergänzen Sie die Tabelle um eine Ergeb­niszeile und wählen Sie statt der automa­tisch berech­neten Summe das Max­i­mum.

▲ nach oben …

Berechnung per VBA (Makro)

Bei diesen 10 Zeilen „lohnt” sich eigentlich nicht der Ein­satz von VBA. Der Aufwand wäre aus mein­er Sicht zu hoch. Das gilt zwar auch prinzip­iell für 50 Daten­zeilen, aber wenn Sie eine solche Liste als „Spiel­wiese” nutzen wollen, dann führen Sie auf die Reg­is­terkarte Tabelle1 einen Recht­sklick aus und wählen im Kon­textmenü die einzige Möglichkeit Ein­blenden… Nach einem Klick auf OK wird diese (einzige derzeit ver­steck­te) Reg­is­terkarte sicht­bar.

Im ersten Schritt wer­den Sie aus der tabel­lar­ischen Darstel­lung eine For­matierte (Intel­li­gente) Tabelle erstellen. Ich klicke dazu in die Dat­en und StrgT oder StrgL. Den Namen der Tabelle ändere ich aus­nahm­sweise ein­mal nicht, ich belasse es bei Tabelle2. In Spalte C habe ich für Sie schon ein­mal die aus der vorheri­gen Übung bekan­nte Formel angewen­det und in E2 die MAX()-Berechnung aus­ge­führt. Und in F2 sehen Sie das durch eine VBA-Funk­tion berech­nete Ergeb­nis. Hier der von mir ver­wen­dete 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ön­nte den Code an dieser oder jen­er Stelle noch straf­fen. Aber ich habe schon ver­schiedenes einge­set­zt, was über das reine Ein­steiger-Wis­sen hin­aus geht. Analysieren Sie den Code gerne, um vielle­icht an diese oder jen­er Stelle etwas Wis­sen zu schöpfen.

▲ nach oben …

Sehr große Datenmengen mittels Power Query verarbeiten

Die 50 Daten­sätze aus der let­zten Übung sind ja für Excel kaum des Ein­stiegs wert. 🙂 VBA ist da abso­lut unter­fordert, aber es ging ja „nur” um das Prinzip. Aber für die PQ-Übung dür­fen es gerne ein „wenig” mehr Dat­en sein. 👿 Dazu erstellen Sie eine Liste mit kalen­darischen Dat­en, begin­nend mit dem 1. Jan­u­ar 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ügi­gen Arbeitsspe­ich­er nicht „in die Knie geht”, mehr als 10 Minuten rech­net oder gar abstürzt, soll der zeitliche Abstand zwis­chen den Daten­sätzen 1 Stunde sein. Das wer­den dann 177.505 Zeilen sein, und diese Daten­menge ist schon ein wirk­lich gutes Argu­ment für den Ein­satz von Pow­er Query.

Natür­lich wer­den es wieder 2 Spal­ten sein und die Werte-Spalte wird nach dem Zufalls-Prinzip mit ein­er entsprechen­den Funk­tion gener­iert. Ach ja, damit es so richtig inter­es­sant wird und Sie mit min­i­malen Änderun­gen auch einen 10-Minuten-Rhyth­mus (mit effek­tiv >1,065 Mil­lio­nen Zeilen) gener­ieren kön­nen, soll (und muss) das Ganze mit PQ geschehen, denn die Gren­ze eines Tabel­len­blatts liegt ja bei 1,048 Mil­lio­nen Zeilen. Ver­suchen Sie es doch ein­fach ein­mal, diese Tabelle/Abfrage zu erstellen und dann (natür­lich) auch noch den höch­sten Wert der laufend­en Summe nach dem gle­ichen Muster wie bish­er aber mit den Mit­teln des Pow­er Query zu berech­nen.

Ich räume ein, dass das nicht „mal so eben” zu erledi­gen ist. Aber wenn Sie genü­gend Zeit investieren und auch die Logik des PQ einiger­maßen verin­ner­licht haben, dann wer­den Sie im Inter­net fündig. Es gibt mehrere Lösungswege! Ich habe ver­schiedene Fund­stellen aus­pro­biert und alle als prinzip­iell zu schw­er nachvol­lziehbar aus­geson­dert. Für mich ste­ht das Ver­ste­hen eines Ablaufs im Vorder­grund, die Copy­/­Paste-Meth­ode führt bei ein­er eventuellen Fehler­suche im eige­nen Pro­jekt oft zu Frus­tra­tio­nen. Darum habe ich meinen eige­nen Weg für die laufende Summe in Pow­er Query erar­beit­et, denn ich möchte möglichst schnell ver­ste­hen, wie der Ablauf ein­er Abfrage ist. Und ja, das dauert vielle­icht etwas länger, als die anderen vorgestell­ten Möglichkeit­en. Das ist der Preis für Trans­parenz, den ich gerne bezahle.

Wenn Sie inter­essiert sind, schreiben Sie mir eine Mail. Gegen eine wirk­lich kleine Spende (>= 5,00€) sende ich Ihnen eine unkom­men­tierte *.xlsx sofort zu, welche Sie schrit­tweise im recht­en Seit­en­fen­ster analysieren kön­nen. Wenn Sie einen Erk­lärungs­beitrag nach bekan­ntem Muster haben wollen, sagen Sie mir dieses bitte per Mail oder Tele­fon. Da wäre dann aber etwas Geduld erforder­lich, weil ich bis zur ersten dies­bezüglichen Anfrage warten werde; meine Erfahrung zeigt, dass extrem wenige Nutzer meines Blogs bere­it sind, meine Arbeit auch peku­niär zu hon­ori­eren.

Sor­ry, ich hat­te einen Daten­ver­lust. Darum ist dieses Ange­bot zurzeit nicht aktuell!

▲ nach oben …