[Excel,VBA] Zielwertsuche

Gepostet am: Sep 03, 2012 9:54:20 PM

Die Zielwertsuche ist sicherlich dem einen oder anderen bekannt. Dabei handelt es sich um eine Möglichkeit eine Gleichung mit einer Variable näherungsweise zu berechnen. Für die Verwendung hier ein kurzes Video.

Ich möchte mich aber heute nicht der Problematik falsch rechnender Zielwertsuchen oder Solver widmen, sondern einem ganz anderen Thema. Wenn man für Kunden fertige Lösungen produzieren soll, kann man nicht darauf vertrauen, dass die Benutzer die Zielwertsuche kennen. Das macht es schwerer diese Funktionalität einzusetzen. 

Folgende Ausgangssituation: Man hat ein Arbeitsblatt, das verformelt ist. Das heißt im konkreten Fall, wenn ich r (Radius) und h (Höhe) entsprechend in die vorgesehenen Zellen eingebe, bekomme ich das Volumen. Soweit ein sehr einfaches Arbeitsblatt. Wenn ich nun aber ausgehend vom Volumen und einer weiteren Größe (zB Radius oder Höhe) die fehlende Größe errechnen möchte, habe ich grundsätzlich zwei Möglichkeiten.

• 1. Ich forme die Formel im Kopf um, dass man die fehlende Größe berechnen kann.

  2. Oder ich verwende die Zielwertsuche. Dabei ist der Zielwert das Volumen, das ich kenne und die veränderbare Zelle diejenige, die ich nicht kenne. 

Soweit hoffentlich nachvollziehbar. 

Und bei der Verwendung der Zielwertsuche stehen gerade ungeübte Excel-Benutzer vor dem Problem, dass sie teilweise nicht wissen, wie man diese anwendet. Wie kann man also dem Otto-Normal-Excel-Endanwender eine Möglichkeit zur Verfügung stellen, dass er in die drei vorgesehenen Zellen die gegebenen Werte eingibt und als Ergebnis die fehlende Größe bekommt? Dies könnte man - wie in diesem Beispiel - über einen Lösen-Button ermöglichen. Dazu braucht man noch ein paar Zeilen Code, um das gewünschte Ergebnis zu berechnen.

Solve_Tabellenblatt

Sub Solve_Tabellenblatt()Dim FormelZelle As StringDim FormelString As String' zu editieren FormelZelle = "$H$8" FormelString = "=1/3*PI()*(H5^2)*H6"' der Rest kann gleichgelassen werden' Ermitteln der leeren ZelleDim i As IntegerDim j As IntegerDim FoundMissing As IntegerDim AddressMissing As String FoundMissing = 0For i = 1 To 100     For j = 1 To 100         If Cells(i, j).Style = "Eingabe" Then             If Cells(i, j).Value = "" Then                 FoundMissing = FoundMissing + 1                 AddressMissing = Cells(i, j).Address             End If         End If     NextNextIf FoundMissing = 0 Then     MsgBox ("keine leere Zelle")ElseIf FoundMissing > 1 Then     MsgBox ("Mehr als eine leere Zelle")     Exit SubElse     If AddressMissing = FormelZelle Then         Range(FormelZelle).Formula = FormelString     Else         Dim currentValue As Double         currentValue = Range(FormelZelle).Value         Range(FormelZelle).Formula = FormelString         Range(FormelZelle).GoalSeek Goal:=currentValue, ChangingCell:=Range(AddressMissing)     End IfEnd IfEnd Sub

Eine kurze Erläuterung: Angepasst werden müssen die zwei Variablen (FormelZelle und FormelString). Formelzelle ist die Absolute Adresse der Zelle, die normalerweise das Ergebnis beinhaltet. Und FormelString ist die Formel, wie sie in der FormelZelle stehen würde. 

Was macht das Skript: Es durchsucht alle Zellen, vom Typ "Eingabe" und ermittelt als erstes, welche die leere Zelle ist. Ist die leere Zelle, die Zelle deren Formel wir kennen (also im konkreten Fall die Formel für das Volumen), dann verwendet er die Formel von "FormelString". Ist es es eine andere Zelle, dann verwendet er die Zielwertsuche.

Der Vorteil diese Vorgehensweise ist, dass es völlig egal ist, welche Zelle leergelassen wird!

Für noch komplexere Anwendungen, kann man diese Überlegungen auch im Zusammenhang mit dem Solver verwenden.