Verwendung der Zielsuche in Microsoft Excel für Was-wäre-wenn-Szenarien

Eskere Guru

Mit der Was-wäre-wenn-Analyse von Excel können Sie sehen, wie sich das Ändern einer Zelle auf die Ausgabe einer Formel auswirken kann. Zu diesem Zweck können Sie die Excel-Tools verwenden, um den Effekt der Änderung des Werts einer Zelle in einer Formel zu berechnen.

Excel verfügt über drei Arten von Was-wäre-wenn-Analyse-Tools: Szenario-Manager, Zielsuche und Datentabelle. Mit Goal Seek können Sie bestimmen, welche Eingabe Sie benötigen, um die Formel rückwärts in eine bestimmte Ausgabe umzuwandeln.

Die Zielsuchfunktion in Excel ist ein Versuch und Irrtum. Wenn sie also nicht das produziert, was Sie wollen, arbeitet Excel daran, diesen Wert zu verbessern, bis dies der Fall ist.

Was sind Excel-Zielsuchformeln?

Die Zielsuche in Excel gliedert sich im Wesentlichen in drei Hauptkomponenten:

  1. Zelle festlegen : Die Zelle, die Sie als Ziel verwenden möchten.
  2. Zu bewerten : Der Wert, den Sie als Ihr Ziel wollen.
  3. Durch Ändern der Zelle : Die Zelle, die Sie ändern möchten, um Ihren Zielwert zu erreichen.

Wenn diese drei Einstellungen festgelegt sind, versucht Excel, den Wert in der Zelle zu verbessern, die Sie in Durch Ändern der Zelle festgelegt haben, bis die Zelle, die Sie in Zelle festlegen festgelegt haben, den Wert erreicht, den Sie unter An Wert festgelegt haben .

Wenn das alles verwirrend klingt, geben Ihnen die folgenden Beispiele eine gute Vorstellung davon, wie die Zielsuche funktioniert.

Zielsuche Beispiel 1

Angenommen, Sie haben zwei Zellen (A & B) und eine dritte Zelle, die den Durchschnitt dieser beiden berechnet.

Angenommen, Sie haben einen statischen Wert für A und möchten den Durchschnittswert erhöhen, indem Sie den Wert für B ändern. Mithilfe der Zielsuche können Sie berechnen, welcher Wert von B den gewünschten Durchschnitt ergibt.

Verwandte Themen : Tipps zum Zeitsparen in Microsoft Excel

Verwendung der Zielsuche in Excel

  1. Klicken Sie in Excel auf Zelle C1 .
  2. Geben Sie in die Formelleiste den folgenden Code ein:
     =AVERAGE(A1:B1)

    Mit dieser Formel berechnet Excel den Durchschnitt der Werte in den Zellen A1 und B1 und gibt diesen in Zelle C1 aus . Sie können sicherstellen, dass Ihre Formel funktioniert, indem Sie Zahlen in A1 und B1 eingeben. C1 sollte ihren Durchschnitt anzeigen.

  3. Ändern Sie in diesem Beispiel den Wert der A1- Zelle in 16 .
  4. Wählen Sie Zelle C1 aus und wechseln Sie in der Multifunktionsleiste zur Registerkarte Daten .
  5. Wählen Sie auf der Registerkarte Daten die Option Was-wäre-wenn-Analyse und dann Zielsuche aus . Dadurch wird das Fenster "Zielsuche" geöffnet.
  6. Setzen Sie im Fenster Zielsuche die Zelle Set auf C1 . Dies wird Ihre Zielzelle sein. Wenn Sie C1 vor dem Öffnen des Zielsuchfensters markiert haben, wird es automatisch in der Zelle "Festlegen" festgelegt .
  7. Geben Sie unter An Wert den gewünschten Zielwert ein. In diesem Beispiel möchten wir, dass unser Durchschnitt 26 beträgt, sodass der Zielwert ebenfalls 26 beträgt.
  8. Wählen Sie schließlich in der Zelle Durch Ändern die Zelle aus , die Sie ändern möchten, um das Ziel zu erreichen. In diesem Beispiel ist dies die Zelle B2 .
  9. Klicken Sie auf OK , damit die Zielsuche ihre Wirkung entfaltet.
Verwendung der Zielsuche in Microsoft Excel für Was-wäre-wenn-Szenarien - goal seek window

Sobald Sie auf OK klicken, wird ein Dialogfeld angezeigt, in dem Sie darüber informiert werden, dass Goal Seek eine Lösung gefunden hat.

Der Wert von Zelle A1 ändert sich in die Lösung, und dies überschreibt die vorherigen Daten. Es ist eine gute Idee, Goal Seek für eine Kopie Ihres Datenblattes auszuführen, um zu vermeiden, dass Ihre Originaldaten verloren gehen.

Verwandte: Verrückte Excel-Formeln, die erstaunliche Dinge tun

Zielsuche Beispiel 2

Die Zielsuche kann nützlich sein, wenn Sie sie in realen Szenarien verwenden. Um Goal Seek jedoch optimal nutzen zu können, muss zunächst eine geeignete Formel vorhanden sein.

Nehmen wir für dieses Beispiel einen kleinen Bankfall. Angenommen, Sie haben ein Bankkonto, auf dem Sie einen jährlichen Zins von 4% auf das Geld erhalten, das Sie auf Ihrem Konto haben.

Mithilfe der Was-wäre-wenn-Analyse und der Zielsuche können Sie berechnen, wie viel Geld Sie auf Ihrem Konto haben müssen, um einen bestimmten Betrag an monatlicher Zinszahlung zu erhalten.

Nehmen wir für dieses Beispiel an, wir möchten jeden Monat 350 US-Dollar aus Zinszahlungen erhalten. So können Sie das berechnen:

  1. Geben Sie in Zelle A1 Balance ein .
  2. Geben Sie in Zelle A2 die Jahresrate ein .
  3. Geben Sie in Zelle A4 Monthly Profit ein .
  4. In Zelle B2 Typ 4% .
  5. Wählen Sie Zelle B4 aus und geben Sie in der Formelleiste die folgende Formel ein:
     =B1*B2/12

    Der monatliche Gewinn entspricht dem Kontostand multipliziert mit der Jahresrate und geteilt durch die Anzahl der Monate in einem Jahr, 12 Monate.

  6. Gehen Sie zur Registerkarte Daten , klicken Sie auf Was-wäre-wenn-Analyse und wählen Sie dann Zielsuche .
  7. Geben Sie im Fenster Zielsuche B4 in die Zelle Festlegen ein .
  8. Geben Sie 350 in die Zelle An Wert ein. (Dies ist der monatliche Gewinn, den Sie erzielen möchten)
  9. Geben Sie B1 in Durch Ändern der Zelle ein . (Dadurch wird der Kontostand auf einen Wert geändert, der monatlich 350 US-Dollar ergibt.)
  10. Klicken Sie auf OK . Das Dialogfeld " Zielsuchstatus" wird angezeigt.
Verwendung der Zielsuche in Microsoft Excel für Was-wäre-wenn-Szenarien - goal seek status

Im Dialogfeld Zielsuchstatus sehen Sie, dass die Zielsuche in Zelle B4 eine Lösung gefunden hat. In Zelle B1 sehen Sie die Lösung, die 105.000 betragen sollte.

Zielsuchanforderungen

Mit der Zielsuche können Sie sehen, dass nur eine Zelle geändert werden kann, um den Zielwert zu erreichen. Dies bedeutet, dass die Zielsuche eine Lösung nicht lösen und erreichen kann, wenn sie mehr als eine Variable enthält.

In Excel können Sie immer noch nach mehr als einer Variablen suchen, Sie müssen jedoch ein anderes Tool namens Solver verwenden. Weitere Informationen zu Excel's Solver finden Sie in unserem Artikel zur Verwendung von Excel's Solver .

Einschränkungen bei der Zielsuche

Eine Zielsuchformel verwendet den Test- und Verbesserungsprozess, um den optimalen Wert zu erreichen. Dies kann zu Problemen führen, wenn die Formel einen undefinierten Wert erzeugt. So können Sie es selbst testen:

  1. Geben Sie in Zelle A1 3 ein .
  2. Wählen Sie Zelle C1 aus und geben Sie in die Formelleiste die folgende Formel ein:
     =1/(A1-5)

    Diese Formel teilt eins durch A1-5, und wenn A1-5 zufällig 0 ist, ist das Ergebnis ein undefinierter Wert.

  3. Gehen Sie zur Registerkarte Daten , klicken Sie auf Was-wäre-wenn-Analyse und wählen Sie dann Zielsuche .
  4. Geben Sie in der Zelle Set C1 ein .
  5. Geben Sie unter Zu Wert 1 ein .
  6. Schließlich in Durch Ändern des Zelltyps A1 . (Dadurch wird der A1-Wert so geändert, dass er in C1 1 erreicht.)
  7. Klicken Sie auf OK .
Verwendung der Zielsuche in Microsoft Excel für Was-wäre-wenn-Szenarien - goal seek fail

Im Dialogfeld Zielsuchstatus wird angezeigt, dass möglicherweise keine Lösung gefunden wurde, und Sie können überprüfen, ob der A1-Wert weit entfernt ist.

Die Lösung für diese Zielsuche wäre nun, einfach 6 in A1 zu haben. Dies ergibt 1/1, was gleich 1 ist. An einem Punkt während des Test- und Verbesserungsprozesses versucht Excel jedoch 5 in A1, was 1/0 ergibt, was undefiniert ist und den Prozess beendet.

Ein Weg, um dieses Zielsuchproblem zu umgehen, besteht darin, einen anderen Startwert zu haben, der dem Undefinierten im Test- und Verbesserungsprozess ausweichen kann.

Wenn Sie beispielsweise A1 in eine Zahl größer als 5 ändern und dann dieselben Schritte zu Zielsuche für 1 wiederholen, sollten Sie das richtige Ergebnis erhalten.

Machen Sie die Berechnungen mit der Zielsuche effizienter

Was-wäre-wenn-Analyse und Zielsuche können Ihre Berechnungen mit Ihren Formeln erheblich beschleunigen. Das Erlernen der richtigen Formeln ist ein weiterer wichtiger Schritt, um ein Excel-Meister zu werden.