Zielwertsuche in Excel: Formeln rückwärts rechnen
Normalerweise gibst du Zahlen ein und Excel rechnet das Ergebnis aus. Die Zielwertsuche dreht das um: Du sagst, welches Ergebnis herauskommen soll, und Excel sucht den passenden Eingabewert, zum Beispiel die Stückzahl für einen Zielgewinn.
Kurz gesagt: Daten > Prognose > Was-wäre-wenn-Analyse > Zielwertsuche. Im Dialog trägst du die Zelle mit der Formel ein, das gewünschte Ergebnis und die Zelle, die Excel verändern darf. Die Zielwertsuche ändert genau eine Eingabezelle. Für mehrere gibt es den Solver.
Die Zielwertsuche ist ein Menübefehl, den das Übungsblatt auf dieser Seite nicht nachbaut. In den Übungen löst du deshalb dieselben Fragen mit einer Formel. Das hat einen Vorteil: Eine Formel rechnet bei neuen Zahlen von selbst neu, die Zielwertsuche musst du jedes Mal wieder starten.
Probier es gleich aus: nötige Stückzahl
Ein Produkt bringt pro Stück 30 € Deckungsbeitrag, die Fixkosten liegen bei 84.000 €. Wie viel musst du verkaufen, damit 30.000 € Gewinn übrig bleiben?
Aufgabe Wie viele Stück musst du verkaufen, damit der Gewinn genau den Zielgewinn in B4 erreicht? Mit der Zielwertsuche würdest du die Menge so lange ändern lassen, bis es passt. Rechne es in B6 direkt mit einer Formel aus.
| A | B | |
|---|---|---|
| 1 | Verkaufspreis je Stück € | 50 |
| 2 | Variable Kosten je Stück € | 20 |
| 3 | Fixkosten pro Jahr € | 84000 |
| 4 | Zielgewinn € | 30000 |
| 5 | ||
| 6 | Nötige Stückzahl |
Zielwertsuche Schritt für Schritt
Dieselbe Frage mit der Zielwertsuche. Du brauchst ein Blatt, auf dem der Gewinn per Formel aus der Menge berechnet wird:
| Zelle | Inhalt |
|---|---|
| B1 | Verkaufspreis je Stück: 50 |
| B2 | Variable Kosten je Stück: 20 |
| B3 | Fixkosten: 84.000 |
| B5 | Menge: 3.000 (irgendein Startwert) |
| B6 | Gewinn: =B5*(B1-B2)-B3, ergibt 6.000 |
- Klick auf der Registerkarte Daten in der Gruppe Prognose auf Was-wäre-wenn-Analyse und dann auf Zielwertsuche. Auf dem Mac fehlt die Gruppe, der Befehl steht direkt unter Daten.
- Füll die drei Felder aus, wie in der Tabelle unten.
- Klick auf OK. Excel probiert Mengen durch und zeigt den Status an. In B5 steht jetzt 3.800, in B6 30.000.
- Mit OK behältst du den neuen Wert, mit Abbrechen stellt Excel den alten Wert wieder her.
| Feld | Bedeutung | Im Beispiel |
|---|---|---|
| Zielzelle | Die Zelle mit der Formel, deren Ergebnis stimmen soll | B6 |
| Zielwert | Das gewünschte Ergebnis als Zahl. Kein Zellbezug, sondern direkt eingetippt. | 30000 |
| Veränderbare Zelle | Die Eingabezelle, die Excel anpassen darf. Die Formel in der Zielzelle muss sich auf sie beziehen. | B5 |
In der aktuellen Microsoft-Hilfe heißen der Befehl „Zielsuche“ und das erste Feld „Zelle festlegen“. Je nach Excel-Version siehst du dort „Zielwertsuche“ und „Zielzelle“. Gemeint ist dasselbe.
Noch eine Übung: nötiger Zinssatz
Microsoft erklärt die Zielwertsuche in der Hilfe am Beispiel eines Kredits: Welcher Zinssatz passt zu einer bestimmten Rate? Für Zinsfragen hat Excel aber mit ZINS eine Funktion, die genau diese Suche als Formel erledigt.
Aufgabe Du zahlst jedes Jahr am Jahresende den Betrag in B1 ein. Welcher Jahreszins ist nötig, damit nach der Laufzeit in B2 das Sparziel in B3 erreicht ist? Statt mit der Zielwertsuche rechnest du es in B5 mit ZINS aus.
| A | B | |
|---|---|---|
| 1 | Sparrate pro Jahr € | 2400 |
| 2 | Laufzeit in Jahren | 10 |
| 3 | Sparziel € | 30000 |
| 4 | ||
| 5 | Nötiger Zinssatz p. a. |
Diese Übung gehört zu Premium.
Mit Premium öffnest du alle Übungen jeder Lektion, übst unbegrenzt mit neuen Aufgaben und kannst den Abschlusstest mit Zertifikat machen.
Premium ansehen Schon Premium? Anmelden
Die erste Übung dieser Lektion ist kostenlos.
Zu jeder Finanzfunktion gibt es so ein Gegenstück: ZINS sucht den Zinssatz, ZZR die Laufzeit, RMZ die Rate, BW den Startbetrag. Mehr dazu in Zinsen berechnen.
Wann Zielwertsuche, wann Formel?
Lässt sich die Formel einfach nach der gesuchten Größe umstellen, wie bei der Stückzahl, ist die umgestellte Formel besser: Sie bleibt im Blatt und rechnet mit. Die Zielwertsuche lohnt sich, wenn das Modell zu verschachtelt zum Umstellen ist, etwa ein Gewinn, der über viele Zellen mit Rabattstaffeln und Provisionen berechnet wird. Sie spart dann das Probieren von Hand.
Grenzen der Zielwertsuche und der Solver
- Nur eine veränderbare Zelle. Sollen Preis und Menge gleichzeitig angepasst werden, brauchst du den Solver.
- Das Ergebnis ist ein fester Wert. Ändern sich die Fixkosten, bleibt die alte Menge stehen, bis du die Zielwertsuche noch einmal startest.
- Eine Lösung, nicht alle. Hat eine Formel mehrere Lösungen, landet Excel je nach Startwert bei einer davon. Ist das Ziel gar nicht erreichbar, meldet Excel, dass es möglicherweise keine Lösung gefunden hat.
- Eine Näherung. Excel nähert sich dem Ziel schrittweise. Das Ergebnis kann in den hinteren Nachkommastellen abweichen. Stückzahlen rundest du mit AUFRUNDEN auf.
Der Solver ist ein Add-In, das bei Excel dabei ist. Du aktivierst es unter Datei > Optionen > Add-Ins: Im Feld Verwalten „Excel-Add-Ins“ wählen, auf die Schaltfläche daneben klicken und bei Solver den Haken setzen. Danach steht er auf der Registerkarte Daten in der Gruppe Analyse. Der Solver kann mehrere Zellen gleichzeitig ändern, Nebenbedingungen wie „Menge höchstens 5.000“ beachten und auch ein Maximum oder Minimum suchen statt eines festen Werts.
Weitere Was-wäre-wenn-Werkzeuge
Im selben Menü stehen zwei Verwandte. Die Datentabelle rechnet eine Formel für eine ganze Reihe von Eingabewerten durch, zum Beispiel den Gewinn bei Preisen von 40 bis 60 €. Das ist eine einfache Sensitivitätsanalyse. Der Szenario-Manager speichert ganze Sätze von Eingaben, etwa „vorsichtig“, „normal“ und „optimistisch“, und zeigt sie nebeneinander.
Typische Fehler
In der Zielzelle steht keine Formel
Die Zielzelle muss eine Formel enthalten. Steht dort eine eingetippte Zahl, kann Excel nichts rückwärts rechnen.
Die veränderbare Zelle enthält eine Formel
Excel ändert nur Zellen mit festen Werten. Steht in der veränderbaren Zelle selbst eine Formel, bricht die Zielwertsuche mit einer Meldung ab.
Zielzelle hängt nicht von der veränderbaren Zelle ab
Bezieht sich die Formel nicht auf die veränderbare Zelle, auch nicht über Zwischenschritte, ändert sich das Ergebnis nie, und Excel findet keine Lösung.
Zielwertsuche auf Englisch
| Deutsch | Englisch |
|---|---|
| Was-wäre-wenn-Analyse > Zielwertsuche | What-If Analysis > Goal Seek |
| Zielzelle, Zielwert, Veränderbare Zelle | Set cell, To value, By changing cell |
| Datentabelle, Szenario-Manager, Solver | Data Table, Scenario Manager, Solver |
=ZINS(B2;-B1;0;B3) | =RATE(B2,-B1,0,B3) |
In welchen Excel-Versionen gibt es die Zielwertsuche?
Microsoft beschreibt die Zielwertsuche für Microsoft 365 und Excel 2016, 2019, 2021 und 2024, unter Windows und auf dem Mac. Die Funktion ZINS gibt es in allen aktuellen Versionen.
Häufige Fragen
Wo finde ich die Zielwertsuche?
Unter Daten > Prognose > Was-wäre-wenn-Analyse. Auf dem Mac unter Daten > Was-wäre-wenn-Analyse.
Kann die Zielwertsuche mehrere Zellen gleichzeitig ändern?
Nein, nur eine. Für mehrere veränderbare Zellen und Nebenbedingungen nimmst du den Solver.
Kann ich als Zielwert einen Zellbezug angeben?
Nein, das Feld nimmt nur eine Zahl. Willst du das Ziel aus einer Zelle nehmen, rechne mit einer Hilfszelle die Differenz aus, etwa =B6-B4, und such dort den Zielwert 0.
Wie bekomme ich den alten Wert zurück?
Klick im Statusfenster der Zielwertsuche auf Abbrechen statt auf OK. Dann stellt Excel den ursprünglichen Wert der veränderbaren Zelle wieder her.
Wie finde ich den Zinssatz, bei dem sich eine Investition rechnet?
Das ist der interne Zinsfuß. Excel berechnet ihn direkt mit IKV, siehe Kapitalwert und interner Zinsfuß.