Office: Solver aus function ohne worksheet-Berechnung?

hallo zusammen,
nutze den solver für nichtlineare Fits, mit üblichen Weg der Berechnung der Abweichungsquadrate zwischen Soll- und Ist-Funktion, die via solver minimiert werden. Ganz einfaches Beispiel:
Sub Solver_NLS_Fit()
Worksheets("Tabelle1").Activate
SolverReset
SolverOptions Precision:=0.000001
SolverOk SetCell:=Range("SDQA"), MaxMinVal:=2, ByChange:=Range("B2:B5"), _
Engine:=1, EngineDesc:="GRG Nonlinear"
SolverAdd CellRef:="$B$4", Relation:=1, FormulaText:="-150"
SolverAdd CellRef:="$B$4", Relation:=3, FormulaText:="-250"
SolverSolve UserFinish:=True
SolverSave SaveArea:=Range("A13")
End Sub

Im worksheet wird im Range "SDQA" die Summe der Abweichungsquadrate berechnet und im Range "B2:B5" stehen die zu optimierenden Koeffizienten, die der solver anpasst. Funktioniert alles super und stabil, aber ein Teil der Rechnung erfolgt immer im worksheet. Ich möchte aber die Berechnung innerhalb einer function umsetzen, die nur die Eingabewerte (Vektoren X und Y_soll(X)) einliest und allen Berechnungen selbst ohne worksheet-Zugriff löst. Aber wie kriege ich die solver-Funktionen dazu, auf innerhalb der function berechnete Werte zuzugreifen??? Also statt z.B. SolverOk SetCell:=Range("SDQA"), ... als worksheet-Zugriff, den innerhalb der function berechneten Wert zu nutzen. Wie kann man das umsetzen??

:)

 

Hallo Hapevo,

innerhalb des Solveraufrufs dürfte es meiner Meinung nach keine solche Möglichkeit geben, den Zellbezug der Zielzelle durch einen vba-internen Funktionsaufruf zu ersetzen.

Dennoch solltest du in der Lage sein, komplizierte Berechnung vom Worksheet runter zu holen, indem du eine entsprechende UDF schreibst. Sprich deine gewünschte Funktion in einem allgemeinen Modul schreiben und als public deklarieren.

Code:

In deine Zielzelle kommt dann der entsprechend Funktionsaufruf "=MeineFormel(...)"
Habe das eben mal anhand eines ganz simplen Ablaufs probiert. Müsste klappen.

Besten Gruß

 

Danke, aber wie das tatsächlich funktionieren soll, ist mir nicht klar. Bei SetCell:=... und ByChange:=... erwartet der solver Zell-Bezüge im worksheet und nicht einfach Variant-Werte, die ich aus Funktionen hole. Oder was mache ich hier falsch (möglichst einfaches Beispiel):

Public Function Y_ber(ByVal X As Double, Koeff() As Variant) As Double
'Berechnung des Fkt.-Wertes einer Beispielfkt.
'Koeff soll hier sein Koeff(0 to 2)

Y_ber = Koeff(0) + Koeff(1) * X / (X + Koeff(2))
End Function

Public Function SDQA(X_Vektor() As Double, YSoll_Vektor() As Double, Koeff() As Variant) As Variant
'Berechnung der Summe der Abweichungsquadrate
'Koeff soll hier sein Koeff(0 to 2)

Dim StartIndex As Integer, EndIndex As Integer, ii As Integer
Dim Summe_SDQA As Double

StartIndex = LBound(X_Vektor)
EndIndex = UBound(X_Vektor)

Summe_SDQA = 0
For ii = StartIndex To EndIndex
Summe_SDQA = Summe_SDQA + Sqr(YSoll_Vektor(ii) - Y_ber(X_Vektor(ii), Koeff()))
Next I
SDQA = Summe_SDQA

End Function

Public Function Fkt_Wert_Fit(ByVal X As Double, XY_Bereich As Range) As Double
'Berechnet Fkt-Wert an Stelle X mit gefitteten Werten aus Wertepaaren X_Vektor und Y_Vektor
'Fkt.-Typ via Y_ber
'XY_Bereich zwei Spalten im worksheet mit X- bzw- Y_Werten

Dim X_Vektor() As Double, Y_Vektor() As Double
Dim Koeff(0 To 2) As Variant 'Koeffizienten der Fkt.
Dim Anzahl As Integer 'Anzahl Wertepaare
Dim ii As Integer

Anzahl = XY_Bereich.Rows.Count 'Anzahl der Wertepaare

ReDim X_Vektor(1 To Anzahl) As Double
ReDim Y_Vektor(1 To Anzahl) As Double

'Einlesen XY-Werte aus Excel-Worksheet
For ii = 1 To Anzahl
X_Vektor(ii) = XY_Bereich.Cells(ii, 1).Value
Y_Vektor(ii) = XY_Bereich.Cells(ii, 2).Value
Next ii

'Vorgabe Startwerte Koeffizienten
Koeff(0) = 100
Koeff(1) = -200
Koeff(2) = 0.05

SolverReset
SolverOptions Precision:=0.0001
SolverOk SDQA, _
MaxMinVal:=2, _
ByChange:=Koeff(), _
Engine:=1, _
EngineDesc:="GRG Nonlinear"
SolverSolve UserFinish:=True

Fkt_Wert_Fit = Y_ber(X, Koeff)

End Function

Die eigentliche function die den solver nutzen soll, ist FKt_Wert_Fit. Die holt sich die XY-Wertepaare aus dem Excel-Sheet als Range, die Abweichungsquadrate werden in der function SDQA berechnet, der eigentliche Funktionsweret in Y_ber. Aber wie muss der solver angesteuert werden, dass der Variantwert SDQA und die zu variierenden Variant-Werte Koeff vom Solver akzeptiert werden, anstelle von Zell-Bezügen???

 

Also da hast du mich falsch verstanden. Sowohl der Zielwert als auch die Veränderbaren Koeffizienten müssen auf deinem Worksheet stehen und können dem Solver nur als Zell-Bezug mitgegeben werden. Das kriegst du nicht geändert.

Ich nahm an, dass die Berechnung für den Wert in der Zelle, die du als "SDQA" benannt hast, vorher vollständig auf dem Worksheet ablief? Vermutlich mit mehreren Zwischenergebnissen? Jetzt hättest du eine Function die du auch in der Zelle "SDQA" abrufen kannst. Mehr Auslagerung nach VBA ist hier glaube ich nicht möglich.

P.S.: Bitte nutze Zukünftig die Möglichkeit Code formatiert zu posten, ansonsten ist das Lesen echt eine Qual.

 

Du könnest die Funktion per Regression selbst berechnen.
Ein Beispiel findest Du unter Polynom Regression Normalengleichung – GeoGebra (Grundlagen)
oder auch bei Excelformeln: http://excelformeln.de/formeln.html?gruppe=7

 

Besten Dank,
Fazit: den Solver kann man ohne die Berechnungen im Worksheet und den Zugriff darauf nicht nutzen.
Wäre aber mal ein super Vorschlag an Microsoft, das zu realisieren!!! (auch wenn man die Iterationsschleife um die eigentliche solver-Funktion selbst basteln muss...). Das ist für sehr viele verschiedene Anwendungen eine sehr effektive Methode, nicht nur für Kurvenfits (Minimierung der Abweichungsquadrate), ohne jedes mal die passenden Algorithmen selbst zu schreiben oder zu "suchen"...
Wer gibt solche Vorschläge/Wünsche weiter?