Goal Seek ist eines der Was-wäre-wenn-Analysetools von Excel, das Ihnen hilft, den richtigen Eingabewert einer Formel zu finden, um die gewünschte Ausgabe zu erhalten. Es zeigt, wie sich ein Wert in einer Formel auf einen anderen auswirkt. Mit anderen Worten, wenn Sie einen Zielwert haben, den Sie erreichen möchten, können Sie die Zielsuche verwenden, um den richtigen Eingabewert zu finden, um ihn zu erreichen.
Nehmen wir zum Beispiel an, Sie haben in einem Fach insgesamt 75 Punkte erzielt und Sie benötigen mindestens 90, um in diesem Fach eine S-Note zu erhalten. Zum Glück haben Sie einen letzten Test, der Ihnen helfen könnte, Ihre durchschnittliche Punktzahl zu erhöhen. Sie können die Zielsuche verwenden, um herauszufinden, wie viel Punktzahl Sie bei diesem Abschlusstest benötigen, um eine S-Note zu erhalten.
Goal Seek verwendet eine Trial-and-Error-Methode, um das Problem durch Eingabe von Vermutungen zurückzuverfolgen, bis das richtige Ergebnis erzielt wird. Goal Seek kann den besten Eingabewert für die Formel in Sekundenschnelle finden, deren manuelle Ermittlung lange gedauert hätte. In diesem Artikel zeigen wir Ihnen anhand einiger Beispiele, wie Sie das Tool Zielsuche in Excel verwenden.
Komponenten der Zielsuchfunktion
Die Zielsuchfunktion besteht aus drei Parametern, nämlich:
- Zelle festlegen – Dies ist die Zelle, in die die Formel eingegeben wird. Es gibt die Zelle an, in der Sie die gewünschte Ausgabe wünschen.
- Schätzen – Dies ist der Ziel-/Sollwert, den Sie als Ergebnis der Zielsuchoperation wünschen.
- Durch das Wechseln der Zelle – Dies gibt die Zelle an, deren Wert angepasst werden muss, um die gewünschte Ausgabe zu erhalten.
So verwenden Sie die Zielsuche in Excel: Beispiel 1
Um die Funktionsweise an einem einfachen Beispiel zu demonstrieren, stellen Sie sich vor, Sie betreiben einen Obststand. Im folgenden Screenshot zeigt Zelle B11 (unten) an, wie viel es kostet, Obst für Ihren Stand zu kaufen, und Zelle B12 zeigt Ihren Gesamtumsatz aus dem Verkauf dieser Früchte. Und Zelle B13 zeigt den Prozentsatz Ihres Gewinns (20%).
Wenn Sie Ihren Gewinn auf „30%“ steigern möchten, aber Ihre Investition nicht erhöhen können, müssen Sie Ihren Umsatz steigern, um Gewinn zu erzielen. Aber wie viel? Die Zielsuche hilft Ihnen, es zu finden.
Sie verwenden die folgende Formel in Zelle B13, um den Gewinnprozentsatz zu berechnen:
=(B12-B11)/B12Nun möchten Sie die Gewinnspanne so berechnen, dass Ihr Gewinnprozentsatz 30% beträgt. Sie können dies mit der Zielsuche in Excel tun.
Wählen Sie zunächst die Zelle aus, deren Wert Sie anpassen möchten. Jedes Mal, wenn Sie die Zielsuche verwenden, müssen Sie eine Zelle auswählen, die eine Formel oder Funktion enthält. In unserem Fall wählen wir Zelle B13 aus, da sie die Formel zur Berechnung des Prozentsatzes enthält.
Gehen Sie dann zur Registerkarte "Daten", klicken Sie in der Gruppe "Prognose" auf die Schaltfläche "Was wäre wenn Analyse" und wählen Sie "Zielsuche".
Das Dialogfeld "Zielsuche" wird mit 3 Feldern angezeigt:
- Zelle festlegen – Geben Sie den Zellbezug ein, der die Formel enthält (B13). Dies ist die Zelle, die Ihre gewünschte Ausgabe haben wird.
- Schätzen – Geben Sie das gewünschte Ergebnis ein, das Sie erreichen möchten (30%).
- Durch das Wechseln der Zelle – Fügen Sie den Zellbezug für den Eingabewert ein, den Sie ändern möchten (B12), um zum gewünschten Ergebnis zu gelangen. Klicken Sie einfach auf die Zelle oder geben Sie den Zellbezug manuell ein. Wenn Sie die Zelle auswählen, fügt Excel das "$"-Zeichen vor dem Spaltenbuchstaben und der Zeilennummer hinzu, um sie zu einer absoluten Zelle zu machen.
Wenn Sie fertig sind, klicken Sie auf "OK", um es zu testen.
Dann wird ein Dialogfeld „Goal Seek Status“ angezeigt, das Sie darüber informiert, ob eine Lösung gefunden wurde, wie unten gezeigt. Wenn eine Lösung gefunden wurde, wird der Eingabewert in der „ändernden Zelle (B12)“ auf einen neuen Wert angepasst. Dies ist, was wir wollen. In diesem Beispiel hat die Analyse also ergeben, dass Sie einen Umsatz von 1635,5 USD (B12) erzielen müssen, um Ihr Gewinnziel von 30 % zu erreichen.
Klicken Sie auf „OK“ und Excel ändert die Zellenwerte oder klicken Sie auf „Abbrechen“, um die Lösung zu verwerfen und den ursprünglichen Wert wiederherzustellen.
Den Eingabewert (1635,5) in Zelle B12 haben wir mit der Zielsuche herausgefunden, um unser Ziel (30 %) zu erreichen.
Was Sie bei der Verwendung von Goal Seek beachten sollten
- Die Set Cell muss immer eine Formel enthalten, die von der Zelle „By Change Cell“ abhängig ist.
- Sie können die Zielsuche jeweils nur für einen einzelnen Zelleneingabewert verwenden
- Die 'Durch Ändern der Zelle' muss einen Wert und keine Formel enthalten.
- Wenn die Zielsuche die richtige Lösung nicht finden kann, zeigt sie den nächstgelegenen Wert an, den sie erzeugen kann, und teilt Ihnen mit, dass die Meldung „Zielsuche hat möglicherweise keine Lösung gefunden“ hat.
Was tun, wenn die Excel-Zielsuche nicht funktioniert?
Wenn Sie sich jedoch sicher sind, dass es eine Lösung gibt, können Sie einige Dinge versuchen, um dieses Problem zu beheben.
Parameter und Werte der Zielsuche prüfen
Es gibt zwei Dinge, die Sie überprüfen sollten: Überprüfen Sie zunächst, ob sich der Parameter "Zelle setzen" auf die Formelzelle bezieht. Stellen Sie zweitens sicher, dass die Formelzelle (Zelle festlegen) direkt oder indirekt von der sich ändernden Zelle abhängt.
Anpassen der Iterationseinstellungen
In den Excel-Einstellungen können Sie die Anzahl der möglichen Versuche, die Excel unternehmen kann, um die richtige Lösung zu finden, sowie deren Genauigkeit ändern.
Um die Iterationsberechnungseinstellungen zu ändern, klicken Sie in der Registerkartenliste auf „Datei“. Klicken Sie dann unten auf "Optionen".
Klicken Sie im Fenster "Excel-Optionen" im linken Bereich auf "Formeln".
Ändern Sie im Abschnitt "Berechnungsoptionen" diese Einstellungen:
- Maximale Iterationen – Gibt die Anzahl der möglichen Lösungen an, die Excel berechnen wird; je höher die Zahl, desto mehr Iterationen können ausgeführt werden. Wenn Sie beispielsweise „Maximale Iterationen“ auf 150 setzen, testet Excel 150 mögliche Lösungen aus.
- Maximale Änderung – Gibt die Genauigkeit der Ergebnisse an; die kleinere Zahl ergibt eine höhere Genauigkeit. Wenn Ihr Eingabezellenwert beispielsweise gleich „0“ ist, die Zielsuche jedoch bei „0,001“ aufhört zu berechnen, sollte die Änderung in „0,0001“ das Problem lösen.
Erhöhen Sie den Wert „Maximale Iterationen“, wenn Excel mehr mögliche Lösungen testen soll, und verringern Sie den Wert „Maximale Änderung“, wenn Sie ein genaueres Ergebnis wünschen.
Der Screenshot unten zeigt den Standardwert:
Keine Rundschreiben
Wenn eine Formel auf eine eigene Zelle verweist, wird dies als Zirkelbezug bezeichnet. Wenn Sie möchten, dass die Excel-Zielsuche ordnungsgemäß funktioniert, sollten Formeln keinen Zirkelbezug verwenden.
Excel Zielsuche Beispiel 2
Nehmen wir an, Sie leihen sich von jemandem Geld in Höhe von 25000 USD. Sie leihen Ihnen das Geld zu einem Zinssatz von 7% pro Monat für einen Zeitraum von 20 Monaten, was eine Rückzahlung von „1327 USD“ pro Monat ergibt. Aber Sie können es sich nur leisten, 20 Monate lang 1.000 USD pro Monat mit einem Zins von 7% zu zahlen. Goal Seek kann Ihnen dabei helfen, den Kreditbetrag zu finden, der eine monatliche Zahlung (EMI) von „1000 USD“ ergibt.
Geben Sie die drei Eingabevariablen ein, die Sie zur Berechnung Ihrer PMT-Berechnung benötigen, d. h. Zinssatz von 7%, Laufzeit von 20 Monaten und Kapitalbetrag von 25.000 USD.
Geben Sie die folgende PMT-Formel in Zelle B5 ein, die Ihnen einen EMI-Betrag von 1.327,97 USD ergibt.
Die Syntax der PMT-Funktion:
=PMT(Zinssatz/12, Laufzeit, Kapital)Die Formel:
=PMT(B3/12,B4,-B2)
Wählen Sie die Zelle B5 (Formelzelle) aus und gehen Sie zu Daten –> Was-wäre-wenn-Analyse –> Zielsuche.
Verwenden Sie diese Parameter im Fenster „Zielsuche“:
- Zelle festlegen – B5 (die Zelle, die die Formel enthält, die EMI berechnet)
- Schätzen – 1000 (das gesuchte Formelergebnis/Ziel)
- Durch das Wechseln der Zelle – B2 (dies ist der Kreditbetrag, den Sie ändern möchten, um den Zielwert zu erreichen)
Klicken Sie dann auf „OK“, um die gefundene Lösung beizubehalten, oder auf „Abbrechen“, um sie zu verwerfen.
Die Analyse zeigt Ihnen, dass der maximale Geldbetrag, den Sie leihen können, 18825 USD beträgt, wenn Sie Ihr Budget überschreiten möchten.
So verwenden Sie die Zielsuche in Excel.