Office: (Office 365) Hilfe bei VBA-Programmierung

Hallöchen zusammen,

ich bräuchte mal wieder eure Hilfe. Diesmal geht es um ein VBA-Programm bzw. -Funktion.
Folgender Sachverhalt:
Ich habe Rohdaten in einer Textdatei, die ich mir in Excel importiere. Diese Rohdaten bestehen aus einer Vielzahl an Zahlen, aufgegliedert nach verschiedenen "Szenarien". Die Szenarien sind jeweils unter einander gelistet und bestehen aus 80 Zeilen und 260 Spalten.
Zur besseren Verständlichkeit lade ich eine Beispieldatei hoch. Hierin ist allerdings nur ein Ausschnitt enthalten, da die Anzahl an Szenarien später vmtl. bei 1000+ liegen wird.

Das Ziel besteht nun darin, aus diesen Rohdaten in jeweils einem neuen Tabellenblatt folgendes zu berechnen:

• Mittelwert
• Maximum
• Minimum
• Standardabweichung

Zur Berechnung sollen jeweils alle Zahlen herangezogen werden, die in den verschiedenen Szenarien an derselben Stelle stehen. Als Beispiel: Für den ersten Mittelwert der neuen Tabelle (A2) sollen die Zellen A2, A84, A166, A248 , etc. herangezogen werden. Hierbei ist sicher hilfreich zu wissen, dass die jeweiligen Zahlen immer an derselben Stelle stehen - sie sind immer 82 Zeilen auseinander. Die Berechnung soll dann für jede Zelle durchgeführt werden. Am Ende ist das Ziel eine einzige Tabelle (bzw. für Mittelwert, max, min, Sigma jeweils eine) aus der Vielzahl an Szenarien zu erhalten. Die Tabelle ist dann wiederum 80x260 groß.
Ich hoffe das ist soweit verständlich. Ich habe bei der Beispieldatei im Tabellenblatt Mittelwert über eine Formel eingetragen, wie die Berechnung stattfinden soll.
Ich habe das ganze auch bereits über eine Formel berechnet, allerdings ist das sehr rechenlastig (zudem muss die Berechnung nur einmal durchgeführt werden. Die Rohdaten ändern sich nicht bzw. für neue Rohdaten wird das Makro dann einfach nochmals durchgeführt. Es braucht also keine permanenten Verlinkungen).
Formel in Zelle A2:

=MITTELWERT(Rohdaten!A2;WENN(REST(ZEILE(INDIREKT("Rohdaten!"&ADRESSE(1;SPALTE(A2))&":"&ADRESSE(VERGLEICH(0;Rohdaten!A:A;-1);SPALTE(A2))))-ZEILE(A2);82)=0;INDIREKT("Rohdaten!"&ADRESSE(1;SPALTE(A2))&":"&ADRESSE(VERGLEICH(0;Rohdaten!A:A;-1);SPALTE(A2)))))​

Ich wäre über eure Hilfe wirklich sehr dankbar!

Viele Grüße
Andre

PS: Falls es auch über eine simple Formel funktioniert, hätte ich auch nichts dagegen, allerdings würde ich alleine wegen der Performance VBA bevrzugen :)

 

Hallo,

ich warne vor der Auffassung, dass VBA per se schneller als Excel-Formeln ist. Das kann man so pauschal nicht sagen.

 

Moin

Ändere das Layout zu: Szenario - Spalte - Wert
Und dann mit Pivot auswerten.

 

Hey, danke für die Antwort. Leider verstehe ich nicht ganz was du damit meinst.
Ich würde gerne die Rohdaten unverändert auswerten (kein extra Bearbeitungsschritt).
Zudem sollen für die Berechnungen ja nicht alle Werte herangezogen werden, sondern wie beschrieben je Szenario die Werte an derselben Stelle, damit am Ende eine einzige Tabelle (80 Zeilen, 260 Spalten) mit den berechneten Werten 8sei es Mittelwert, Max, Min, etc.) entsteht

 

Das habe ich auch nicht so gesagt/gemeint. Ich habe lediglich das Problem, dass durch die vielen INDIREKT-Funktionen in meiner Formel und der Fülle an Rohdaten, meine Excel-Datei kaum bearbeitbar ist. Da wäre es mMn sicherlich sinnvoller einmal eine VBA-Funktion auszuführen, die die Werte berechnet und dann als Werte in ein Tabellenblatt einfügt (Die Formel zu kopieren und als Werte einzufügen ist nicht das Ziel - die Datei lässt sich durch die Unmenge an Formeln so schon kaum bearbeiten).

 

Ich ändere meinen Vorschlag zu:
Szenario - Spalte - Zeile - Wert

Das ist der neue Tabellenaufbau.
Man benötigt auf jeden Fall einen Bearbeitungsschritt - entweder einmalig oder als Skript.

Wieso INDIREKT()? In der vorliegenden Datei gibt es keine Formeln mit INDIREKT().

 

Mir fällt gerade keine Möglichkeit ein wie ich die Rohdaten händisch schnell in dieses Layout bringen soll.
Da die Anzahl der Szenarien einer Auswertung gut und gerne bei 1000 liegen kann und die Auswertung zudem mit verschiedenen Rohdaten-Sätzen wiederholt wird, wäre mMn ein Skript /VBA-Programm am sinnvollsten. Ich lasse mich aber auch gerne vom Gegenteil überzeugen.
Die Formeln in der beigefügten Datei waren nur dazu da, die Funktionsweise zu erklären. Hierin sind auch nur 7 Szenarien enthalten. In der Originaldatei dann wie gesagt 1000+. Meine ursprüngliche Formel in Zelle A2 habe ich am Ende meines ursprünglichen Posts angefügt. Das wäre die folgende:

=MITTELWERT(Rohdaten!A2;WENN(REST(ZEILE(INDIREKT("Rohdaten!"&ADRESSE(1;SPALTE(A2))&":"&ADRESSE(VERGLEICH(0;Rohdaten!A:A;-1);SPALTE(A2))))-ZEILE(A2);82)=0;INDIREKT("Rohdaten!"&ADRESSE(1;SPALTE(A2))&":"&ADRESSE(VERGLEICH(0;Rohdaten!A:A;-1);SPALTE(A2)))))
​

 

Stichwort: Entpivotieren.
Beste Möglichkeit: PQ
Es wäre sinnvoll die Ur-Tabelle mit Spalten- und Zeilen-Überschriften zu versehen.

 

Dann wäre es aber erforderlich die Ur-Tabelle jedes mal neu anzupassen. Gibt es denn keine Möglichkeit, dies ohne eine Anpassung zu lösen? Das dürfte doch prinzipiell über VBA nicht so kompliziert sein, oder? Mir fehlt nur das Wissen bzgl. der Programmierung/Codes. Wie bereits erwähnt, habe ich das Problem ohne Anpassung mit einer Formel bereits gelöst (s.oben), allerdings ist diese zu rechenintensiv. Es ist ja immer dasselbe Schema. Den Mittelwert bilden über die anfängliche Zelle in Datensatz#0 und jede weitere 82. Zeile der Ur-Tabelle. Der Aufbau ist ja immer 1:1 gleich. Das Ergebnis entspricht dann auch dem selben Aufbau (Tabelle mit 80 Zeilen und 260 Spalten).
Oder stelle ich mir das zu einfach vor? Ich würde eben gerne die Rohdaten nicht bearbeiten, da es etliche Ur-Tabellen geben wird. Das alles sind jeweils bestimmte Messergebnisse, die mir in einer txt Datei ausgegeben werden.

 

Ohne Spalten- und Zeilen-Überschriften?
Woher weiß man denn welche Bedeutung jeder Wert hat bzw. wie er ermittelt wurde?

 

Ja ohne Überschriften.
Das alles sind verschiedene Messpunkte, die mehrfach wiederholt werden. Also ein Szenario wird bspw. 1000 mal wiederholt. Daher auch die Mittelwerte/Max/Min über dieselben Messpunkte der verschiedenen Szenarios. Die Auswertung dient lediglich dazu die Güte des Produkts bzgl. Messgenauigkeit feststellen zu können.

 

Bitte sehr. Den Rest bekommst du selber hin.
Die Datei war zu groß. Ich habe ein paar Spalten und Szenarien gelöscht.

 

Hey, vielen Dank für die Datei!
Könntest du mir eventuell noch sagen, wie du von vom Rohdaten-TB auf das Query-TB gekommen bist?
Bin Power Query habe ich um ehrlich zu sein bislang nicht viel zu tun gehabt.

 

Öffne der/die/das Query-Pane über Daten -> Abfragen und Verbindungen -> Abfragen und Verbindungen.
Mit Doppelklick auf einen Query gelangst du in den Query-Editor. Dort siehst du die Queries mit den Einzelschritten.

 

ah okay, vielen Dank.
Das sind ja einige Schritte. Dann versuche ich mich da mal reinzudenken :)
Nochmals Danke!