Wie man Excel Ziel-Suche und Löser für unbekannte Variablen zu lösen

Excel kann für unbekannte Variablen entweder für eine einzelne Zelle mit Zielsucher oder eine kompliziertere Gleichung mit Solver lösen. Wir zeigen Ihnen, wie es funktioniert.

Excel kann für unbekannte Variablen entweder für eine einzelne Zelle mit Zielsucher oder eine kompliziertere Gleichung mit Solver lösen.  Wir zeigen Ihnen, wie es funktioniert.
Werbung

Excel ist sehr leistungsfähig, wenn Sie alle Daten haben, die Sie für Ihre Berechnungen benötigen.

Aber wäre es nicht nett, wenn es nach unbekannten Variablen suchen würde ?

Mit Goal Seek und dem Solver Add-In kann es. Und wir zeigen Ihnen wie. Lesen Sie weiter für eine vollständige Anleitung zur Lösung für eine einzelne Zelle mit Goal Seek oder eine kompliziertere Gleichung mit Solver.

So verwenden Sie die Zielsuche in Excel

Die Zielsuche ist bereits in Excel integriert. Es befindet sich auf der Registerkarte " Daten" im Menü " Was-wäre-wenn-Analyse" :

Übertreffen Sie die Zielsuche, lösen Sie nach unbekannten Variablen

In diesem Beispiel verwenden wir eine sehr einfache Menge von Zahlen. Wir haben drei Viertel der Verkaufszahlen und ein jährliches Ziel. Wir können die Zielsuche verwenden, um herauszufinden, was die Zahlen in Q4 sein müssen, um das Ziel zu erreichen.

Übertreffen Sie die Zielsuche, lösen Sie nach unbekannten Variablen

Wie Sie sehen können, beträgt die aktuelle Verkaufssumme 114.706 Einheiten. Wenn wir bis Ende des Jahres 250.000 verkaufen wollen, wie viele müssen wir in Q4 verkaufen? Excel Goal Seek wird uns sagen.

So verwenden Sie die Zielsuche Schritt für Schritt:

  1. Klicken Sie auf Daten> Was-wäre-wenn-Analyse> Zielsuche . Sie werden dieses Fenster sehen:
    Übertreffen Sie die Zielsuche, lösen Sie nach unbekannten Variablen
  2. Setzen Sie den Gleichheits-Teil Ihrer Gleichung in das Feld Zellen setzen . Dies ist die Nummer, die Excel zu optimieren versuchen wird. In unserem Fall ist dies die laufende Summe unserer Verkaufszahlen in Zelle B5.
  3. Geben Sie Ihren Zielwert in das Feld Wert ein. Wir suchen nach insgesamt 250.000 verkauften Einheiten, also werden wir "250.000" in diesem Bereich platzieren.
  4. Sagen Sie Excel, welche Variable im Feld Durch Ändern der Zelle zu lösen ist. Wir wollen sehen, was unsere Verkäufe in Q4 sein müssen. Also sagen wir Excel, dass es nach Zelle D2 zu lösen ist. Es sieht so aus, wenn es fertig ist:
    Übertreffen Sie die Zielsuche, lösen Sie nach unbekannten Variablen
  5. Treffe OK, um dein Ziel zu lösen. Wenn es gut aussieht, drücke einfach OK . Excel wird Sie wissen lassen, wenn Goal Seek eine Lösung gefunden hat.
    Übertreffen Sie die Zielsuche, lösen Sie nach unbekannten Variablen
  6. Klicken Sie erneut auf OK, und Sie sehen den Wert, der Ihre Gleichung in der Zelle löst, die Sie für Durch Ändern der Zelle auswählen .
    Übertreffen Sie die Zielsuche, lösen Sie nach unbekannten Variablen

In unserem Fall ist die Lösung 135.294 Einheiten. Natürlich hätten wir das gerade herausfinden können, indem wir die laufende Summe vom Jahresziel abgezogen haben. Die Zielsuche kann jedoch auch für eine Zelle verwendet werden, die bereits Daten enthält . Und das ist nützlicher.

Beachten Sie, dass Excel unsere vorherigen Daten überschreibt. Es ist eine gute Idee, Goal Seek auf einer Kopie Ihrer Daten auszuführen . Es ist auch eine gute Idee, Ihre kopierten Daten zu notieren, dass sie mit der Zielsuche generiert wurden. Sie wollen es nicht mit aktuellen, genauen Daten verwechseln.

So ist Ziel-Suche eine nützliche Excel-Funktion 16 Excel-Formeln, die Ihnen helfen werden, reale Lebensprobleme zu lösen 16 Excel-Formeln, die Ihnen helfen werden, reale Probleme zu lösen Das richtige Werkzeug ist die halbe Arbeit. Excel kann Berechnungen schneller durchführen und Daten verarbeiten, als Sie Ihren Rechner finden können. Wir zeigen Ihnen wichtige Excel-Formeln und demonstrieren, wie Sie sie verwenden. Lesen Sie mehr, aber es ist nicht so beeindruckend. Sehen wir uns ein Tool an, das viel interessanter ist: das Solver-Add-In.

Was macht der Excel-Solver?

Kurz gesagt, Solver ist wie eine multivariate Version von Goal Seek . Es dauert eine Zielvariable und passt eine Reihe anderer Variablen an, bis es die gewünschte Antwort erhält.

Es kann für einen Maximalwert einer Zahl, einen Minimalwert einer Zahl oder eine exakte Zahl aufgelöst werden.

Wenn eine Variable nicht geändert werden kann oder nur innerhalb eines bestimmten Bereichs variieren kann, berücksichtigt Solver dies.

Es ist eine großartige Lösung für mehrere unbekannte Variablen in Excel. Aber es ist nicht einfach, es zu finden und zu benutzen.

Werfen wir einen Blick auf das Laden des Solver-Add-Ins und dann auf die Verwendung von Solver in Excel 2016.

So laden Sie das Solver-Add-In

Excel verfügt standardmäßig nicht über Solver. Es ist ein Add-In also, wie andere leistungsstarke Excel-Funktionen Power-Up-Excel mit 10 Add-Ins zum Verarbeiten, Analysieren und Visualisieren von Daten wie ein Profi Power-Up-Excel mit 10 Add-Ins zum Verarbeiten, Analysieren und Visualisieren von Daten wie ein Pro Vanilla Excel ist erstaunlich, aber Sie können es mit Add-Ins noch leistungsfähiger machen. Egal, welche Daten Sie verarbeiten müssen, wahrscheinlich hat jemand eine Excel-App dafür erstellt. Hier ist eine Auswahl. Lesen Sie mehr, Sie müssen es zuerst laden. Zum Glück ist es bereits auf Ihrem Computer.

Gehen Sie zu Datei> Optionen> Add-Ins . Klicken Sie dann auf Weiter neben Verwalten: Excel-Add-Ins .

Wenn dieses Dropdown etwas anderes als "Excel-Add-Ins" sagt, müssen Sie es ändern:

Übertreffen Sie die Zielsuche, lösen Sie nach unbekannten Variablen

Im resultierenden Fenster sehen Sie einige Optionen. Stellen Sie sicher, dass das Kontrollkästchen neben Solver-Add-In aktiviert ist, und klicken Sie auf OK .

Übertreffen Sie die Zielsuche, lösen Sie nach unbekannten Variablen

In der Gruppe Analyse der Registerkarte Daten wird jetzt die Schaltfläche Solver angezeigt:

Übertreffen Sie die Zielsuche, lösen Sie nach unbekannten Variablen

Wenn Sie das Data Analysis Toolpak bereits verwendet haben, gehen Sie wie folgt vor: Grundlegende Datenanalyse in Excel Grundlegende Datenanalyse in Excel Excel ist nicht für die Datenanalyse gedacht, kann aber trotzdem Statistiken verarbeiten. Wir zeigen Ihnen, wie Sie das Data Analysis Toolpak-Add-In zum Ausführen von Excel-Statistiken verwenden. Lesen Sie mehr, Sie sehen die Schaltfläche Datenanalyse. Wenn nicht, wird Solver von selbst erscheinen.

Nachdem Sie das Add-In geladen haben, schauen wir uns nun an, wie Sie es verwenden können.

Wie verwende ich Solver in Excel?

Jede Solver-Aktion besteht aus drei Teilen: dem Ziel, den variablen Zellen und den Einschränkungen. Wir gehen durch jede der Stufen.

  1. Klicken Sie auf Daten> Solver . Sie sehen das Solver-Parameter-Fenster unten. (Wenn Sie den Solver-Button nicht sehen, lesen Sie den vorherigen Abschnitt zum Laden des Solver-Add-Ins.)
    Übertreffen Sie die Zielsuche, lösen Sie nach unbekannten Variablen
  2. Setzen Sie Ihre Zelle Ziel und erzählen Sie Excel Ihr Ziel. Das Ziel befindet sich oben im Solver-Fenster und besteht aus zwei Teilen: der Zielzelle und einer Auswahl von Maximieren, Minimieren oder einem bestimmten Wert.
    Übertreffen Sie die Zielsuche, lösen Sie nach unbekannten Variablen
    Wenn Sie Max auswählen, passt Excel Ihre Variablen an, um die größtmögliche Anzahl in Ihrer Zielzelle zu erhalten. Min ist das Gegenteil: Solver minimiert die Zielanzahl. Mit Value Of können Sie eine bestimmte Nummer angeben, nach der Solver suchen soll.
  3. Wählen Sie die Variablenzellen, die Excel ändern kann. Die Variablenzellen werden mit dem Feld By Changing Variable Cells festgelegt . Klicken Sie auf den Pfeil neben dem Feld, klicken Sie dann auf und ziehen Sie, um die Zellen auszuwählen, mit denen Solver arbeiten soll. Beachten Sie, dass dies alle Zellen sind, die variieren können. Wenn Sie nicht möchten, dass sich eine Zelle ändert, wählen Sie sie nicht aus.
    Übertreffen Sie die Zielsuche, lösen Sie nach unbekannten Variablen
  4. Legen Sie Einschränkungen für mehrere oder einzelne Variablen fest. Schließlich kommen wir zu den Einschränkungen. Hier ist Solver wirklich mächtig. Anstatt eine der Variablenzellen in eine beliebige Zahl zu ändern, können Sie Einschränkungen angeben, die erfüllt sein müssen. Weitere Informationen finden Sie im folgenden Abschnitt zum Festlegen von Einschränkungen.
  5. Sobald alle diese Informationen vorhanden sind, klicken Sie auf Lösen, um Ihre Antwort zu erhalten. Excel aktualisiert Ihre Daten so, dass sie die neuen Variablen enthalten (deshalb empfehlen wir Ihnen, zuerst eine Kopie Ihrer Daten zu erstellen).

Sie können auch Berichte erstellen, die wir in unserem Solver-Beispiel unten kurz betrachten.

Gewusst wie: Festlegen von Einschränkungen in Solver

Sie könnten Excel sagen, dass eine Variable größer als 200 sein muss. Wenn Sie verschiedene Variablenwerte ausprobieren, geht Excel mit dieser bestimmten Variable nicht unter 201.

Übertreffen Sie die Zielsuche, lösen Sie nach unbekannten Variablen

Um eine Abhängigkeit hinzuzufügen, klicken Sie auf die Schaltfläche Hinzufügen neben der Abhängigkeitsliste. Sie erhalten ein neues Fenster. Wählen Sie die Zelle (oder Zellen), die eingeschränkt werden sollen, im Feld Zellenreferenz aus, und wählen Sie dann einen Operator aus.

Übertreffen Sie die Zielsuche, lösen Sie nach unbekannten Variablen

Hier sind die verfügbaren Operatoren:

  • <= (kleiner oder gleich)
  • = (gleich)
  • => (größer oder gleich)
  • int (muss eine Ganzzahl sein)
  • bin (muss entweder 1 oder 0 sein)
  • Alle anders

AllDifferent ist ein wenig verwirrend. Es gibt an, dass jede Zelle in dem Bereich, den Sie für Zellbezug auswählen, eine andere Nummer sein muss. Aber es gibt auch an, dass sie zwischen 1 und der Anzahl der Zellen liegen müssen. Wenn Sie also drei Zellen haben, erhalten Sie die Zahlen 1, 2 und 3 (aber nicht unbedingt in dieser Reihenfolge).

Fügen Sie schließlich den Wert für die Einschränkung hinzu.

Beachten Sie, dass Sie mehrere Zellen für die Zellenreferenz auswählen können. Wenn Sie beispielsweise möchten, dass sechs Variablen Werte über 10 haben, können Sie sie alle auswählen und Solver mitteilen, dass sie größer oder gleich 11 sein müssen. Sie müssen keine Einschränkung für jede Zelle hinzufügen.

Sie können auch das Kontrollkästchen im Solver-Hauptfenster verwenden, um sicherzustellen, dass alle Werte, für die Sie keine Einschränkungen angegeben haben, nicht negativ sind. Wenn Sie möchten, dass Ihre Variablen negativ werden, deaktivieren Sie dieses Kontrollkästchen.

Übertreffen Sie die Zielsuche, lösen Sie nach unbekannten Variablen

Ein Solver-Beispiel

Um zu sehen, wie all dies funktioniert, verwenden wir das Solver-Add-In, um eine schnelle Berechnung durchzuführen. Hier sind die Daten, mit denen wir beginnen:

Übertreffen Sie die Zielsuche, lösen Sie nach unbekannten Variablen

Wir haben fünf verschiedene Jobs, von denen jeder eine andere Rate zahlt. Wir haben auch die Anzahl der Stunden, die ein theoretischer Arbeiter in einer bestimmten Woche in jedem dieser Jobs gearbeitet hat. Wir können das Solver-Add-In verwenden, um herauszufinden, wie das Gesamtgehalt maximiert werden kann, während bestimmte Variablen innerhalb bestimmter Einschränkungen bleiben.

Hier sind die Einschränkungen, die wir verwenden werden:

  • Keine Jobs können unter vier Stunden fallen.
  • Job 2 muss länger als acht Stunden sein .
  • Job 5 muss weniger als elf Stunden dauern .
  • Die Gesamtarbeitsstunden müssen 40 betragen.

Es kann hilfreich sein, diese Einschränkungen vor der Verwendung von Solver auszugeben.

So stellen wir das in Solver ein:

Übertreffen Sie die Zielsuche, lösen Sie nach unbekannten Variablen

Beachten Sie zunächst, dass ich eine Kopie der Tabelle erstellt habe, sodass wir nicht die ursprüngliche Tabelle überschreiben, die unsere aktuellen Arbeitszeiten enthält.

Und zweitens, sehen Sie, dass die Werte in den Größer-als- und Kleiner -als-Bedingungen höher oder niedriger sind als das, was ich oben erwähnte. Das liegt daran, dass es keine Optionen größer als oder kleiner als gibt. Es gibt nur Größer-als-oder-gleich und Weniger-als-oder-gleich-zu.

Lassen Sie uns Solve treffen und sehen, was passiert.

Übertreffen Sie die Zielsuche, lösen Sie nach unbekannten Variablen

Solver hat eine Lösung gefunden! Wie Sie auf der linken Seite des obigen Fensters sehen können, haben sich unsere Einnahmen um 130 $ erhöht. Und alle Beschränkungen wurden erfüllt.

Um die neuen Werte beizubehalten, stellen Sie sicher, dass Solver Solution beibehalten aktiviert ist, und klicken Sie auf OK .

Wenn Sie jedoch weitere Informationen wünschen, können Sie einen Bericht auf der rechten Seite des Fensters auswählen. Wählen Sie alle gewünschten Berichte aus, teilen Sie Excel mit, ob Sie diese anzeigen möchten (ich empfehle es), und klicken Sie auf OK .

Die Berichte werden in neuen Arbeitsblättern in Ihrer Arbeitsmappe generiert und geben Informationen über den Prozess, den das Solver-Add-In durchlaufen hat, um Ihre Antwort zu erhalten.

In unserem Fall sind die Berichte nicht sehr aufregend und es gibt dort nicht viele interessante Informationen. Wenn Sie jedoch eine kompliziertere Solver-Gleichung ausführen, finden Sie in diesen neuen Arbeitsblättern möglicherweise nützliche Informationen zur Berichterstellung. Klicken Sie einfach auf die Schaltfläche + neben einem Bericht, um weitere Informationen zu erhalten:

Übertreffen Sie die Zielsuche, lösen Sie nach unbekannten Variablen

Solver Erweiterte Optionen

Wenn Sie nicht viel über Statistiken wissen, können Sie die erweiterten Optionen von Solver ignorieren und einfach so ausführen, wie sie ist. Wenn Sie jedoch große, komplexe Berechnungen ausführen, sollten Sie sich diese ansehen.

Am offensichtlichsten ist die Lösungsmethode:

Übertreffen Sie die Zielsuche, lösen Sie nach unbekannten Variablen

Sie können zwischen GRG Nichtlinear, Simplex LP und Evolutionär wählen. Excel bietet eine einfache Erklärung, wann Sie welche verwenden sollten. Eine bessere Erklärung erfordert einige Kenntnisse der Statistik Lernen Sie Statistiken kostenlos mit diesen 6 Ressourcen Lernen Sie Statistiken mit diesen 6 Ressourcen kostenlos Statistiken hat den Ruf eines Faches, das schwer zu verstehen ist. Wenn Sie jedoch von der richtigen Ressource lernen, können Sie Umfrageergebnisse, Wahlberichte und die Klassenzuweisungen Ihrer Statistik schnell verstehen. Lesen Sie mehr und Regression.

Um weitere Einstellungen vorzunehmen, drücken Sie einfach die Schaltfläche Optionen . Sie können Excel über Integer-Optimalität informieren, Rechenzeit-Constraints festlegen (nützlich für massive Datasets) und anpassen, wie die Berechnungsmethoden von GRG und Evolutionary ausgeführt werden.

Auch wenn Sie nicht wissen, was das bedeutet, machen Sie sich keine Sorgen. Wenn Sie mehr darüber wissen möchten, welche Lösungsmethode Sie verwenden möchten, hat Engineer Excel einen guten Artikel, der Ihnen diese Informationen zur Verfügung stellt. Wenn Sie maximale Genauigkeit wünschen, ist Evolutionary wahrscheinlich ein guter Weg zu gehen. Seien Sie sich bewusst, dass es lange dauern wird.

Zielsuche und Solver: Excel auf die nächste Stufe bringen

Jetzt, wo Sie mit den Grundlagen der Lösung für unbekannte Variablen in Excel vertraut sind, steht Ihnen eine völlig neue Welt der Tabellenkalkulation zur Verfügung.

Goal Seek kann Ihnen helfen, Zeit zu sparen, indem Sie einige Berechnungen schneller machen, und Solver fügt den Rechenfähigkeiten von Excel eine gewaltige Menge hinzu. Wie man Grundlegende Statistiken in Excel berechnet: Ein Anfängerhandbuch Wie man Grundlegende Statistiken in Excel berechnet: Ein Anfängerhandbuch Microsoft Excel kann Statistiken machen! Sie können Prozentsätze, Durchschnittswerte, Standardabweichungen, Standardfehler und Schüler-T-Tests berechnen. Weiterlesen .

Es ist nur eine Frage der Bequemlichkeit. Je mehr Sie sie verwenden, desto nützlicher werden sie.

Verwenden Sie Zielsuche oder Solver in Ihren Tabellen? Welche anderen Tipps können Sie geben, um die besten Antworten zu erhalten? Teilen Sie Ihre Gedanken in den Kommentaren unten!

In this article