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.
- Ich forme die Formel im Kopf um, dass man die fehlende Größe berechnen kann.
- 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.
In diesem VBA-Code:
- Zunächst werden die Variablen
FormelZelleundFormelStringdefiniert, die die Zielzelle und die Formel speichern. - Die Schleifen durchlaufen alle Zellen innerhalb eines bestimmten Bereichs (hier 100×100 Zellen) und suchen nach leeren Zellen mit dem Stil „Eingabe“.
- Die Anzahl der gefundenen leeren Zellen und ihre Adressen werden erfasst.
- Je nach Anzahl der gefundenen leeren Zellen werden unterschiedliche Aktionen ausgeführt:
- Keine leere Zelle gefunden: Eine Nachrichtenbox wird angezeigt.
- Mehr als eine leere Zelle gefunden: Eine Nachrichtenbox wird angezeigt und das Makro beendet.
- Genau eine leere Zelle gefunden: Wenn die leere Zelle die Formelzelle ist, wird die Formel eingefügt. Andernfalls wird eine Zielwertsuche durchgeführt, um den Wert in der Formelzelle basierend auf der leeren Zelle zu ermitteln.
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.
