VBA: Zielwertsuche (Goal Seek)

Die Zielwertsuche (Goal Seek) ist ein sehr nützliches Instrument in Excel, um die Argumente einer Formel so zu gestalten, dass am Ende ein gewünschtes Ergebnis erreicht wird.

In einem früheren Beitrag habe ich Dir die Zielwertsuche in Excel bereits vorgestellt. In diesem Beitrag geht es nun darum, die Zielwertsuche mit einem VBA-Makro umzusetzen. In VBA heißt die Zielwertsuche „Goal Seek“.

1. Die durchschnittliche Fehlerquote sichern

In einem ersten Beispiel wurde die Fehlerquote eines Mitarbeiters über fast zwei Wochen (9 Arbeitstage) in einer Liste erfasst.

Der Durchschnitt über die 9 Arbeitstage beträgt 9,444%.

Ziel ist es, den Durchschnitt nicht über 9,5% wachsen zu lassen.

Es bleibt nun noch der 10. Arbeitstag. Die Frage ist, wie hoch die Fehlerquote an diesem Tag maximal sein darf, um das Ziel zu erreichen?

Schreibe dazu dieses Makro in ein Modul im VBA-Editor:

Eröffne z.B. mit

Sub GoalSeek1()

Die Zielzelle soll C14 sein, dort willst Du am Ende einen Wert <=9,5% sehen. Schreibe also

Range(„C14“).GoalSeek

GoalSeek ist die angewandte Methode.

Definiere nun die Zielgröße. Schreibe nicht „9.5%“, sondern „0.095“. Ergänze dazu die letzte Zeile mit

Range(„C14“).GoalSeek Goal:=0.095

Im letzten Schritt musst Du noch die veränderbare Zelle benennen. Das geschieht mit „ChangingCell“. Im Beispiel soll das C13 sein, denn dort fehlt noch die Fehlerquote.

Range(„C14“).GoalSeek Goal:=0.095, ChangingCell:=Range(„C13“)

Beende die Prozedur mit

End Sub

Das Makro ist nun fertig und kann in Aktion treten:

Sub GoalSeek1()

Range(„C14“).GoalSeek Goal:=0.095, ChangingCell:=Range(„C13“)

End Sub

Starte es z.B. aus dem Modul heraus mit Ausführen / Sub/UserForm ausführen.

Das Makro ermittelt nun den Wert der veränderbaren Zelle C13, der Zielwert in C14 ist 9,5%.

Was bedeutet das nun?

Der Mitarbeiter darf am 10. Arbeitstag eine Fehlerquote von 10% erreichen, besser wäre natürlich weniger.

2. Einen möglichen Rabatt bestimmen

Stell Dir vor, Du bist Einkäufer in einem Metallbetrieb.

Du befindest Dich in Preisverhandlungen mit potentiellen Lieferanten. Darin möchtest Du herausfinden, wieviel Rabatt Dir ein Lieferant auf den Gesamtpreis i. H. v. 17.300 Euro gewähren muss, damit der gewünschte Einkaufspreis i. H. v. 15.700 Euro erzielt wird.

Für die Zielwertsuche benötigst Du diese drei Kennzahlen:

• Gesamtpreis
• Rabatt
• Einkaufspreis

Der Einkaufspreis ergibt sich durch Subtraktion des Rabatts vom Gesamtpreis.

Die Rabatthöhe ergibt sich aus Multiplikation des Rabattsatzes mit dem Gesamtpreis.

Erstelle Dir dazu eine kleine Tabelle.

Gehe nun wieder in den VBA-Editor und eröffne ein Makro z.B. mit

Sub GoalSeek2()

Die Zielzelle soll D6 sein, dort willst Du am Ende den Wert 15.700 sehen. Schreibe also

Range(„D6“).GoalSeek

Definiere nun die Zielgröße. Schreibe in D6 den Wert 15.700. Ergänze dazu die letzte Zeile mit

Range(„D6“).GoalSeek Goal:=15,700

Im letzten Schritt musst Du noch die veränderbare Zelle benennen. Das geschieht mit „ChangingCell“. Im Beispiel soll das C5 sein, denn dort fehlt noch der Rabattsatz.

Range(„D6“).GoalSeek Goal:=15700, ChangingCell:=Range(„C5“)

Beende die Prozedur mit

End Sub

Das Makro ist nun fertig und kann in Aktion treten:

Sub GoalSeek2()

Range(„D6“).GoalSeek Goal:=15700, ChangingCell:=Range(„C5“)

End Sub

Starte nun das Makro.

Die Prozedur wird wie gewünscht ausgeführt und der Zielwert 15.700 erreicht.

In der veränderbaren Zelle C5 siehst Du den errechneten Rabattsatz i. H. v. 9,25%. Damit errechnet sich in D5 der Rabatt, den Dir ein Lieferant gewähren muss.

Sicher ist Dir bewusst, dass Du dieses Makro nur für den einen ausgewählten Fall nutzen kannst, denn Du hast im Code „Goal:=15700“ definiert.

Schreibe deshalb in eine beliebige Zelle die Zielgröße, die Du beliebig verändern kannst, und lasse das Makro darauf zurückgreifen. Diese Möglichkeit hast Du übrigens bei der Zielwertsuche mit Excel nicht.

Das Makro sieht hierbei so aus:

Sub GoalSeek3()

Range(„D6“).GoalSeek Goal:=Range(„C2“).Value, ChangingCell:=Range(„C5“)

End Sub

Lege Dir für das Makro noch eine Schaltfläche an.

Trage nun Deinen Zielwert in C2 ein, diesmal soll es 15.500 sein. Ein Klick auf die Schaltfläche und die Berechnung wird ausgeführt.

Der Lieferant muss Dir einen Rabatt von 10,40% gewähren, damit Du Dein Einkaufsziel erreichen kannst.