Excel (Physikalische) Formeln per VBA berechnen lassen

[Sebbl1990]

Hallo,

ich habe eine Tabelle "Rohdaten", in der Messdaten enthalten sind. x Messkanäle mit je y aufgenommenen Messreihen.

In einer zweiten Tabelle "Formeln" möchte ich eine Reihe von physikalischen Formeln auf jede Messreihe anwenden.

In der Beispieldatei enthält diese Liste nur 2 Einträge. Tatsächlich sind es ca. 200, also eine größere Anzahl.

Die Berechnungsformeln (P_mechanisch und P_elektrisch im Beispiel) möchte ich im VBA unterbringen, damit ich dort die Formeln in einfach lesbarer Form haben kann. (z.B. P_elektrisch = U * I / 1000 - siehe unten)


Was ich jetzt vom Prinzip her per VBA umsetzen möchte:

1. Es wird geschaut welcher Name in Zelle A2 in der Tabelle "Formeln" steht.
2. Dieser Name wird in der Liste der Formeln, welche im VBA niedergeschrieben steht, gesucht.
3. Wenn die Formel gefunden wurde, gehen dort Größen aus den "Rohdaten" ein, z.B. I und U. Die Werte für I und U müssen dann in der Tabelle "Rohdaten" gesucht werden und für die Berechnung des Formelwerts benutzt werden.
4. Punkt 3 muss dann für die Anzahl der Messreihen wiederholt werden.
5. Punkte 1 bis 4 müssen dann für alle Formeln (Zellen A3, A4 usw.) wiederholt werden.


Die zentrale Frage, die sich mir stellt, ist, in welcher Form gebe ich die physikalischen Formeln dem Excel im VBA mit, damit man dann danach suchen kann und wie suche ich dann danach. Irgendwie in eine Art Array speichern z.B.? Bin für Vorschläge offen.

Ich hoffe, das Ziel ist verständlich, ich freue mich auf Tipps.

Gruß
Sebastian

Da keine xlsm-Dateien hochgeladen werden können, hier der VBA-Code-Schnipsel separat. In der Form möchte ich die Formeln anlegen können:

Sub Formeln()

    Const Pi = 3.14159

    '===Liste mit physikalischen Formeln===
    
    'Mechanische Leistung in kW
    P_mechanisch = n * M * 2 * Pi / 60 / 1000

    'Elektrische Leistung in kW
    P_elektrisch = U * I / 1000

End Sub

 

[Sun_set_1]

Die Formeln als function definieren und dann im Hauptmodel mit dem jeweiligen function name Aufrufen.

Dabei beachten, was ggf global und was public etc zur Verfügung gestellt werden muss, je nachdem wie Du dein Skript aufbauen möchtest.

Hier noch ein gutes VBA Tut.
https://www.vba-tutorial.de/prozeduren/functions.htm

 

[Cheetah1337]

Deinen Entdeckerdrang in allen Ehren aber ... WENN()? und PI() ... reichen? ^^
Oder waren die 200 Einträge auf die Formeln bezogen?

 

[tollertyp]

warum reichen denn da einfache Excel-Formeln nicht? Also was ist der Sinn hinter der Rechnung in VBA?

Also meiner Meinung nach würden Hilfsspalten für Zwischenergebnisse für deutlich mehr Übersicht sorgen als ein VBA...

 

[DieRenteEnte]

Bin @tollertyp Meinung.
Es ist nicht nur sehr übersichtlich, sondern weniger fehleranfällig.
Stell dir vor irgend ein Ergebnis passt nicht. So kannst du einfach in die Tabelle gucken und alles Schritt für Schritt nachvollziehen.

Ich erledige solche Aufgaben immer in einem separaten Arbeitsblatt, welches ich strukturiert, farblich und kommentiert aufbaue.

 

[Sebbl1990]

Hallo,

danke schon mal für den Input.

Die vollständige Datei wird später ca. 200 Formeln und bis zu 10000 Messreihen enthalten. (Meist weniger, aber ich möchte die Option haben, bis zu 10000 Messreihen berechnen lassen zu können)
Von den Formeln sind eine Reihe komplex, beinhalten While-Schleifen usw.

Die Formeln habe ich bereits in "physikalischer Schreibweise" vorliegen (ist in einer durch VBA verarbeitbaren Syntax) und möchte diese eben wegen der geringeren Fehleranfälligkeit per Copy/Paste in Excel nutzen, was vermute ich dann nur im VBA möglich ist. Diese Anforderung ist fix.


Die Benennung der Formelnamen sowie Messkanäle ist standardisiert, ändert sich also nicht.

Was sich allerdings ändern kann: Der Umfang und die Reihenfolge der zu berechnenden Formeln in der Tabelle "Formeln". (die 2 Beispielformeln könnten z.B. mehrere Zeilen nach unten rutschen)
Sowie die Reihenfolge der Messkanäle in der Tabelle "Rohdaten".


Eine Function würde wohl in dem Stile aussehen, wenn ich das richtig verstehe:

Function P_elektrisch(U As Double, I As Double) As Double
    
    'Elektrische Leistung in kW
    P_elektrisch = U * I / 1000

End Function

Das wirft aber für mich 2 Fragen auf:
1. Wie sage ich den gelb markierten Zellen, dass sie in Spalte A schauen sollen, welche Formel in der jeweiligen Zeile berechnet werden soll.
2. Wie kriege ich die Parameter dann in die Function? U und I muss in den Rohdaten gesucht werden. Hinzu kommt noch die Dimension der Messreihen-Nr.

Gruß
Sebastian

 

[tollertyp]

Okay, ich muss halt sagen, dass es anhand des Beispiel-Excels nicht wirklich nachvollziehbar ist, warum du das brauchst.

Vielleicht wären für dich Named Formulas besser.
https://excel.tv/using-named-formulas-in-excel-like-yesterday/

 

[Scientist]

Excel ist der Falsche Ansatz ... Fuer so etwas nutzt man Matlab, Octave, Python oder vergleichbares ...

Wenn es auf unbedingt Excel sein soll:
Auch eigenen Funktionen kann man variabeln uebergeben. Das Beispiel vpn @Sun_set_1 ist nur minimal gehalten.

Zu deinen Beweggruenden:
Am Ende muss das Ergebnis so oder so verifiziert werden. Egal ob die eigentliche Formel kopiert wurde.
Ein Fehler kann auch an anderer Stelle auftreten.
In diem Fall ist es das einfachste, jeden Schritt einzeln haendisch stichprobenartig nachzurechnen.

 

[Sebbl1990]

Okay, ich muss halt sagen, dass es anhand des Beispiel-Excels nicht wirklich nachvollziehbar ist, warum du das brauchst.

Vielleicht wären für dich Named Formulas besser.
https://excel.tv/using-named-formulas-in-excel-like-yesterday/

Kein Problem, ich habe die Hintergründe eingangs nicht erläutert, weil der Beitrag eh schon ziemlich lang war.

Den Namensmanager kenne ich ganz gut, ist aber leider für die Formeln für meine Zwecke nicht geeignet.

Excel ist der Falsche Ansatz ... Fuer so etwas nutzt man Matlab, Octave, Python oder vergleichbares ...

Wenn es auf unbedingt Excel sein soll:
Auch eigenen Funktionen kann man variabeln uebergeben. Das Beispiel vpn @Sun_set_1 ist nur minimal gehalten.

Zu deinen Beweggruenden:
Am Ende muss das Ergebnis so oder so verifiziert werden. Egal ob die eigentliche Formel kopiert wurde.
Ein Fehler kann auch an anderer Stelle auftreten.
In diem Fall ist es das einfachste, jeden Schritt einzeln haendisch stichprobenartig nachzurechnen.

Richtig, ich würde auch lieber Matlab nutzen, leider gibt es Kollegen die auch mit der Datei arbeiten und ggf. auch Änderungen vornehmen müssen und nicht mal was wissen, was Matlab ist. Daher fallen wir auf den kleinsten gemeinsamen Nenner Excel zurück.

Das Prüfen der Formeln auf Fehler ist nicht das Problem, das werden wir beim ersten Test ganz schnell merken, ob Copy&Paste oder irgendetwas anderes nicht funktioniert. Warum das so ist, würde den Rahmen sprengen.


Die Quintessenz bleibt die Fragestellung, ob es eine Möglichkeit gibt, die Formeln per Copy&Paste (bzw. mir geht es insbesondere um die physikalische Schreibweise, die erhalten bleiben muss) nutzbar zu machen. Das Prinzip der Functions habe ich schon verstanden und das geht am ehesten in die Richtung, die Fragen aus meinem letzten Beitrag bleiben allerdings offen, um beurteilen zu können, ob das die Lösung sein könnte. Insbesondere, dass die Parameterübergabe notwendig ist, bereitet mir an der Stelle etwas Bauchschmerzen.

Gruß
Sebastian

 

[tollertyp]

Eine Sache hat sich für mich noch nicht so ganz erschlossen: Wo soll denn das "Ergebnis" stehen am Ende?
Also wenn du P_elektrisch haben willst, wie soll das dann im Sheet (bzw. in der Zelle) aussehen? Also ist die Zelle in der gleichen Zeile innerhalb einer Spalte P_elektrisch oder so?

 

[Sebbl1990]

In "Rohdaten" steht in Spalte A immer die Messreihen-Nr.

In "Formeln" steht in Zeile 1 immer die Messreihen-Nr. (gleiche Anzahl)

Ist also quasi transponiert (das umzusetzen ist nicht das Problem).

Ein Beispiel macht es einfacher:
Für den Eintrag in Formeln!C2 müssen zur Berechnung die Werte aus Rohdaten!Zeile3 verwendet werden. Für P_elektrisch müssten dann nur die Werte U=230,5 und I=100,0 eingehen. M und n sind für P_elektrisch irrelevant.

Für den Eintrag in Formeln!D2 müssen zur Berechnung die Werte aus Rohdaten!Zeile4 verwendet werden. Usw.

 

[new Account()]

@Sebbl1990
Zwei VBA-Funktionen erstellen, eine für P_mechanisch und eine für P_elektrisch.

Die Funktionen haben die Argumente U, I, M - jenachdem was du brauchst.
Der Rückgabewert ist dann P_mechanisch bzw. P_elektrisch.

Könnte sogar mit copy&paste hinhauen jeweils

 

[Scientist]

Ich verstehe gerade das Problem ehrlich gesagt nicht.

Du willst 200 Formeln anwenden, dann wirst du auch 200 Funktionen erstellen muessen und die in jeder Zeile im zweiten Blatt aufrufen muessen.

Deine selbst erstellen Funktionen werden dabei wie jede Standardfunktion verwendet.
D.h., P_elektrisch() wird U und I uebergeben "=P_elektrisch(U;I)" und das Ergebnisse wird anschließend in der Zelle dargestellt.

 

[tollertyp]

Ja, für jedes Ergebnis (Formel-Typ), das er braucht, eine Funktion.
Aber in Bezug auf Parameter widerspreche ich. Diese "öffentlichen" Funktionen brauchen nicht zwingen Parameter, denn die könnte man anhand der Zell-Position ermitteln:

Evtl hilft dir das:
https://stackoverflow.com/questions/5559279/excel-cell-from-which-a-function-is-called

Darüber erfährst du, welche Zelle die Formel enthält, die du berechnen willst.
Du würdest also nur schreiben =P_eletrkisch()

Im VBA kann anhand von P_elektrisch ermittelt werden, welche Zelle das ist und anhand dessen alle notwendigen Eingangsparameter samt Zwischenfunktionsaufrufen.

Ob das aber wirklich besser ist, als eine Mischung aus Hilfsspalten und den Formeln, das weiß nicht, aber kann sein. So 100% sicher, ob ich dein Problem verstanden habe, bin ich mir nach wie vor nicht.

 

[Scientist]

Wobei, bei mehr als 10k Messreihen ergeben sich mehr als 2 Mio Berechnungen.
Dass ist in der Form sicherlich nicht mehr praktikabel.
Es waere sinnvoller komplett auf VBA umzusteigen und jede Berechnungsart fuer alle Messreihen ueber Matrixoperationen auszufuehren.
Das habe ich allerdings noch nie in Excel/VBA gemacht.
Wobei das dann nicht mehr viel mit "Excel" im klassischen Sinne gemein hat.

 

[tollertyp]

Also mir ist das Zielbild immer noch nicht ganz klar, wo die Formeln stehen...

Also ich weiß nicht, ob das in die Richtung geht, was du haben willst, aber diese Funktion ermittel das Produkt aus der Zeile und der Spalte, in der sie verwendet wird:

Function MyFunction() As Double
Dim row
Dim col

row = Range(Application.Caller.Address).row
col = Range(Application.Caller.Address).column

MyFunction = row * col
End Function

Das ist natürlich nicht, was du brauchst, aber anhand der row/col kann man ja die korrekten Messdaten finden.
Keine Ahnung, ob das in die Richtung geht, was du willst.
Die Formel im Excel sieht so aus (alle 6 Zellen haben diese Formel):

Weil mir immer noch nicht klar ist, wo "steht"; was (welche Formel) in Formeln!C2 berechnet werden soll. Geht das aus ihrer Position hervor oder ist die Formel eben so wie bei mir in der Zelle enthalten?


Aber wenn du alle Berechnungen für alle Messreihen haben willst, dann würde ich die komplette Rechnung im VBA haben wollen... also gar keine Formeln mehr im Sheet selbst.

 

[new Account()]

Code:

Function P_mechanisch(n, M)
    Dim Pi: Pi = WorksheetFunction.Pi
    P_mechanisch = n * M * 2 * Pi / 60 / 1000
End Function

Function P_elektrisch(U, I)
    P_elektrisch = U * I / 1000
End Function

Lediglich die Pi-Zeile muss man anpassen. Den Rest kann man einfach copy&pasten

Verwendung (hier direkt neben den Rohdaten, aber kannst du ja ohne Probleme auch vom zweiten Blatt erledigen**):

203 verschiedene Funktionen mit 10.000 Einträgen dauern mit einem i7 930 ne Stunde zum Berechnen.

Geht bestimmt schneller, wenn du Python/Octave/Matlab oder andere Programmiersprachen mit Vektorsupport nutzt, oder einen etwas neueren Rechner 🤓
Excel hat AFAIK keinen Support für Vektorrechnung.






** Berechnung auf dem 2. Blatt transponiert:

Und dann einfach nach rechts ziehen. Die Formel verstehst du hoffentlich selbst 😏
Kann übrigens gut sein, dass es auf dem 2. Blatt auf Grund der komplizierteren Formel nochmal länger dauert.

 

[Scientist]

203 verschiedene Funktionen mit 10.000 Einträgen dauern mit einem i7 930 ne Stunde zum Berechnen.

Geht bestimmt schneller, wenn du Python/Octave/Matlab oder andere Programmiersprachen mit Vektorsupport nutzt, oder einen etwas neueren Rechner 🤓
Excel hat AFAIK keinen Support für Vektorrechnung.

Nicht einmal eine Sekunde wuerde das dauern.
Einen vollstaendige Verlagerung aller berechnung in VBA wuerde das Ganze sicherlich noch spuerbar reduzieren.

Noch ein Hinweis:
Die automatische Akualisierung der Zellen sollte deaktiviert werden.
Ansonsten wartet man nach jeder Eingabe ...

 

[new Account()]

Ansonsten wartet man nach jeder Eingabe ...

Nicht wirklich.
Es wird nur die Berechnung von Zellen angestoßen, die von einer geänderten Zelle abhängen.

 

[Sebbl1990]

Hmmm, ok, ich sehe, die Problematik ist noch nicht ganz rüberkommen.

Eine komplette Berechnung im VBA würde ich sogar bevorzugen! Am liebsten würde ich im Zielbereich (der gelb markierte) am Ende nur noch die numerischen Ergebniswerte stehen haben.


Ich versuche es mal so. Das müsste das VBA Programm können bzw. schrittweise machen:

1. Für eine beliebige Zelle in dem gelb markierten Zielbereich mache folgendes:

2. Schaue in Spalte A, welche Formel berechnet werden soll (z.B. "P_elektrisch").

3. Schaue in Zeile 1, für welche Messreihe die Formel berechnet werden soll (z.B. "1")

4. Suche in meiner VBA-Liste mit allen Formeln nach der Formel P_elektrisch

5. Schaue, was für Eingangsgrößen für die Berechnung von P_elektrisch benötigt werden -> U und I

6a. Suche den numerischen Wert für die 1. Eingangsgrößen (U) für die Messreihe ("1") in "Rohdaten"

6b. Suche den numerischen Wert für die 2. Eingangsgrößen (I) für die Messreihe ("1") in "Rohdaten"

7. Berechne die in 4. gefundene Formel mit den in 6a/b gefundenen Eingangsgrößen

8. Schreibe den numerischen Ergebniswert in die betrachtete Zelle des Zielbereichs in "Formeln"


Lösungen hätte ich für folgende Punkte schon im Kopf:

1. Größe des gelb markierten Zielbereichs (Anzahl Zeilen/Spalten) ist bekannt und der Algorithmus kann dann einfach per 2 for-Schleifen für den gesamten Zielbereich durchlaufen werden

2. und 3. trivial

6.a/b/... Kann man per S- oder WVERWEIS machen. Achtung, ich habe auch Formeln die so 20 Eingangsgrößen haben! Daher tue ich mich mit einer klassischen Function, wo nochmal jeder Parameter aufgelistet werden muss etwas schwer (ist halt lästig - wenn es vermieden werden kann, wäre perfekt)

7. und 8. trivial


Man sieht, Knackpunkt sind die Punkte 4 und 5. Wie lege ich meine Liste an Formeln am besten an, damit danach dann vom Algorithmus nach einer beliebigen Formel gesucht werden kann? Und wie manage ich die Eingangsgrößen am besten?

Und wie gesagt, die physikalische Schreibweise (P_elektrisch = U * I /1000) muss erhalten bleiben und genau in der Form im VBA enthalten sein. Wenn ich P_elektrisch dann nochmal einem Feld in einem Array zuordnen muss, ist das ok. In die Richtung geht mein Lösungsansatz momentan gedanklich. Ob das der Königsweg ist, bin ich mir aber auch nicht ganz sicher und würde mich auch mit der Umsetzung schwer tun.

Gruß
Sebastian und danke für den bisherigen Input