So verwenden Sie das Zielsuch- und Lösungsprogramm von Excel, um unbekannte Variablen zu lösen

Excel ist sehr gut geeignet, wenn Sie über alle Daten verfügen, die Sie für Ihre Berechnungen benötigen.

Aber wäre es nicht schön, wenn es könnte nach unbekannten Variablen suchen?

Mit Goal Seek und dem Solver-Add-In ist dies möglich. Und wir zeigen dir wie. Lesen Sie weiter, um eine vollständige Anleitung zur Lösung einer einzelnen Zelle mit Goal Seek oder eine kompliziertere Gleichung mit Solver zu erhalten.

So verwenden Sie die Zielsuche in Excel

Goal Seek ist bereits in Excel integriert. Es ist unter der Daten Registerkarte, in der Was, wenn die Analyse Speisekarte:

In diesem Beispiel verwenden wir sehr einfache Zahlen. Wir haben Verkaufszahlen von drei Quartalen und ein Jahresziel. Mit Goal Seek können wir herausfinden, wie viele Zahlen in Q4 sein müssen, um das Ziel zu erreichen.

Wie Sie sehen, beträgt der Verkaufserlös derzeit 114.706 Einheiten. Wenn wir bis Ende des Jahres 250.000 verkaufen wollen, wie viele müssen wir im vierten Quartal verkaufen? Excel's Goal Seek wird es uns sagen.

So verwenden Sie Goal Seek Schritt für Schritt:

1. Klicken Daten> WENN-Analyse> Zielsuche. Sie sehen dieses Fenster:
   
2. Lege das “gleich” Teil Ihrer Gleichung in der Zelle einstellen Feld. Dies ist die Zahl, die Excel zu optimieren versucht. In unserem Fall ist dies die laufende Summe unserer Verkaufszahlen in Zelle B5.
3. Geben Sie Ihren Zielwert in das Feld ein Schätzen Feld. Wir suchen nach insgesamt 250.000 verkauften Einheiten “250.000” in diesem Bereich.
4. Sagen Sie Excel, für welche Variable Sie das Problem lösen möchten Durch Ändern der Zelle Feld. Wir möchten sehen, wie unser Umsatz in Q4 aussehen muss. Also sagen wir Excel, dass es für Zelle D2 zu lösen ist. Es wird so aussehen, wenn es fertig ist:
   
5. Schlagen OK für dein Ziel zu lösen. Wenn es gut aussieht, einfach schlagen OK. Excel informiert Sie, wenn Goal Seek eine Lösung gefunden hat.
   
6. Klicken OK wieder und Sie sehen den Wert, der Ihre Gleichung in der Zelle löst, für die Sie sich entschieden haben Durch Ändern der Zelle.
   

In unserem Fall liegt die Lösung bei 135.294 Einheiten. Natürlich hätten wir das auch finden können, indem wir die laufende Summe vom Jahresziel abziehen. Goal Seek kann aber auch für eine Zelle verwendet werden hat bereits Daten darin. Und das ist nützlicher.

Beachten Sie, dass Excel unsere vorherigen Daten überschreibt. Es ist eine gute Idee zu Führen Sie Goal Seek für eine Kopie Ihrer Daten aus. Es ist auch eine gute Idee, eine Notiz zu den kopierten Daten zu machen, die mit Goal Seek generiert wurde. Sie möchten es nicht mit aktuellen, genauen Daten verwechseln.

Goal Seek ist also eine nützliche Excel-Funktion. 16 Excel-Formeln, die Ihnen beim Lösen realer Probleme helfen. 16 Excel-Formeln, die beim Lösen realer Probleme helfen. Das richtige Werkzeug ist die Hälfte der Arbeit. Excel kann Berechnungen lösen und Daten schneller verarbeiten, als Sie Ihren Rechner finden können. Wir zeigen Ihnen die wichtigsten Excel-Formeln und zeigen, wie Sie diese verwenden. Lesen Sie mehr, aber es ist nicht so beeindruckend. Schauen wir uns ein Tool an, das viel interessanter ist: das Solver-Add-In.

Was macht Excel's Solver??

Kurz gesagt, Solver ist wie ein multivariate Version von Goal Seek. Es benötigt eine Zielvariable und passt eine Reihe anderer Variablen an, bis die gewünschte Antwort erhalten wird.

Es kann nach einem Maximalwert einer Zahl, einem Minimalwert einer Zahl oder einer genauen Zahl auflösen.

Und es funktioniert innerhalb von Einschränkungen. Wenn also eine Variable nicht geändert werden kann oder nur innerhalb eines bestimmten Bereichs variieren kann, wird Solver dies berücksichtigen.

Es ist eine großartige Möglichkeit, nach mehreren unbekannten Variablen in Excel zu suchen. Aber es zu finden und zu verwenden ist nicht einfach.

Lassen Sie uns einen Blick auf das Laden des Solver-Add-Ins werfen. Dann lernen Sie, wie Sie Solver in Excel 2016 verwenden.

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 Pro. Starten Sie 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. Bei allen Daten, die Sie verarbeiten müssen, besteht die Möglichkeit, dass jemand eine Excel-App dafür erstellt hat. Hier ist eine Auswahl. Lesen Sie mehr, Sie müssen es zuerst laden. Zum Glück ist es bereits auf Ihrem Computer.

Geh zu Datei> Optionen> Add-Ins. Dann klicken Sie auf Gehen neben an Verwalten: Excel-Add-Ins.

Wenn diese Dropdown-Liste etwas anderes anzeigt als “Excel-Add-Ins,” Sie müssen es ändern:

Im daraufhin angezeigten Fenster sehen Sie einige Optionen. Stellen Sie sicher, dass das Kästchen neben Solver-Add-In wird geprüft und getroffen OK.

Sie sehen jetzt die Löser Taste in der Analyse Gruppe der Daten Tab:

Wenn Sie bereits das Datenanalyse-ToolPak verwendet haben. Grundlegende Datenanalyse in Excel. Grundlegende Datenanalyse in Excel. Excel ist nicht für die Datenanalyse gedacht, kann aber dennoch Statistiken verarbeiten. Wir zeigen Ihnen, wie Sie mit dem Data Analysis Toolpak-Add-In Excel-Statistiken ausführen. Weitere Informationen finden Sie unter der Schaltfläche Datenanalyse. Wenn nicht, erscheint Solver von selbst.

Nachdem Sie das Add-In geladen haben, werfen wir einen Blick auf seine Verwendung.

So verwenden Sie 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 Daten> Löser. Das Fenster "Solver-Parameter" wird unten angezeigt. (Wenn Sie die Solver-Schaltfläche nicht sehen, lesen Sie den vorherigen Abschnitt zum Laden des Solver-Add-Ins.)
   
2. Legen Sie Ihr Zellziel fest und teilen Sie Excel Ihr Ziel mit. Das Objektiv befindet sich oben im Solver-Fenster und besteht aus zwei Teilen: der Zielzelle und der Wahl zwischen Maximieren, Minimieren oder einem bestimmten Wert.
   
   Wenn Sie auswählen Max, Excel passt Ihre Variablen an, um die größtmögliche Anzahl in Ihrer Zielzelle zu erhalten. Mindest ist das Gegenteil: Solver minimiert die Zielanzahl. Wert von können Sie eine bestimmte Zahl angeben, nach der Solver suchen soll.
3. Wählen Sie die variablen Zellen aus, die Excel ändern kann. Die variablen Zellen werden mit eingestellt Durch Ändern variabler Zellen Feld. 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 diese sind alle Zellen das kann variieren. Wenn Sie nicht möchten, dass sich eine Zelle ändert, wählen Sie sie nicht aus.
   
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 beliebige variable Zelle in eine beliebige Anzahl zu ändern, können Sie die Einschränkungen angeben, die erfüllt werden müssen. Weitere Informationen finden Sie im Abschnitt zum Festlegen von Einschränkungen.
5. Wenn alle Informationen vorhanden sind, klicken Sie auf Lösen um deine Antwort zu bekommen. Excel aktualisiert Ihre Daten, um die neuen Variablen aufzunehmen (aus diesem Grund empfehlen wir, zuerst eine Kopie Ihrer Daten zu erstellen)..

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

So legen Sie Einschränkungen in Solver fest

Sie können Excel sagen, dass eine Variable größer als 200 sein muss. Wenn andere Variablenwerte verwendet werden, geht Excel mit dieser bestimmten Variable nicht unter 201.

Um eine Einschränkung hinzuzufügen, klicken Sie auf die Schaltfläche Hinzufügen neben der Einschränkungsliste. Sie erhalten ein neues Fenster. Wählen Sie die Zelle (oder Zellen) aus, für die die Einschränkung erforderlich ist Zellreferenz Wählen Sie dann einen Operator aus.

Hier sind die verfügbaren Operatoren:

• <= (weniger als oder gleich)
• = (gleich)
• => (größer als oder gleich wie)
• int (muss eine ganze Zahl sein)
• Behälter (muss entweder 1 oder 0 sein)
• Alle anders

Alle anders ist etwas verwirrend. Es gibt an, dass jede Zelle in dem Bereich, für den Sie auswählen, ausgewählt wird Zellreferenz muss eine andere Nummer sein. Es gibt aber 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.

Es ist wichtig zu wissen, dass Sie es können Wählen Sie mehrere Zellen aus für Zellreferenz. Wenn Sie 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 nicht für jede Zelle eine Einschränkung 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.

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:

Wir haben fünf verschiedene Jobs, von denen jeder einen anderen Preis bezahlt. Wir haben auch die Anzahl der Stunden, die ein theoretischer Mitarbeiter in jeder Woche an einem dieser Jobs gearbeitet hat. Wir können das Solver-Add-In verwenden, um herauszufinden, wie Sie den Gesamtlohn maximieren und dabei bestimmte Variablen innerhalb bestimmter Einschränkungen halten können.

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

• Keine Jobs kann unter vier Stunden fallen.
• Job 2 muss sein mehr als acht Stunden.
• Job 5 muss sein weniger als elf Stunden.
• Die Gesamtarbeitszeit muss sein gleich 40.

Es kann hilfreich sein, Ihre Einschränkungen vor der Verwendung von Solver auszulesen.

So haben wir das in Solver eingerichtet:

Beachten Sie zuerst das Ich habe eine Kopie der Tabelle erstellt Daher überschreiben wir nicht die ursprüngliche, die unsere aktuellen Arbeitszeiten enthält.

Zweitens: Stellen Sie fest, dass die Werte in den Größer-als-Weniger-als-Einschränkungen liegen eine höher oder niedriger als das, was ich oben erwähnt habe. Das liegt daran, dass es keine Optionen gibt, die größer oder kleiner sind. Es gibt nur größer als oder gleich und weniger als oder gleich.

Lass uns schlagen Lösen und sehen was passiert.

Solver hat eine Lösung gefunden! Wie Sie links im Fenster oben sehen können, ist unser Gewinn um 130 $ gestiegen. Und alle Einschränkungen wurden erfüllt.

Stellen Sie sicher, dass die neuen Werte beibehalten werden Lösung halten wird geprüft und getroffen OK.

Wenn Sie 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, sagen Sie Excel, ob Sie sie umreißen möchten (ich empfehle es), und klicken Sie auf OK.

Die Berichte werden auf neuen Arbeitsblättern in Ihrer Arbeitsmappe erstellt und enthalten 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 nicht viele interessante Informationen. Wenn Sie jedoch eine komplexere Solver-Gleichung ausführen, finden Sie in diesen neuen Arbeitsblättern möglicherweise nützliche Berichtsinformationen. Klicken Sie einfach auf + Schaltfläche an der Seite eines Berichts, um weitere Informationen zu erhalten:

Löser Erweiterte Optionen

Wenn Sie sich mit Statistiken nicht auskennen, können Sie die erweiterten Optionen von Solver ignorieren und einfach so ausführen, wie sie sind. Wenn Sie jedoch große, komplexe Berechnungen ausführen, möchten Sie diese vielleicht genauer untersuchen.

Das offensichtlichste ist die Lösungsmethode:

Sie können zwischen GRG Nonlinear, Simplex LP und Evolutionary wählen. Excel bietet eine einfache Erklärung, wann Sie sie verwenden sollten. Eine bessere Erklärung erfordert einige Kenntnisse in Statistiken. Lernen Sie Statistiken kostenlos mit diesen 6 Ressourcen. Lernen Sie Statistiken kostenlos mit diesen 6 Ressourcen. Statistiken haben den Ruf eines Themas, das schwer zu verstehen ist. Wenn Sie jedoch von der richtigen Quelle lernen, werden Sie die Umfrageergebnisse, Wahlberichte und Ihre Klassenzuweisungen in kürzester Zeit besser verstehen. Lesen Sie mehr und Rückschritt.

Um weitere Einstellungen vorzunehmen, drücken Sie einfach die Optionen Taste. Sie können Excel die Integer-Optimalität mitteilen, Berechnungszeiteinschränkungen festlegen (nützlich für umfangreiche Datensätze) und anpassen, wie die GRG- und Evolutionary-Lösungsmethoden ihre Berechnungen durchführen.

Wenn Sie nicht wissen, was dies bedeutet, machen Sie sich keine Sorgen. Wenn Sie mehr darüber wissen möchten, welche Lösungsmethode Sie verwenden möchten, bietet Engineer Excel einen guten Artikel, der es für Sie herausstellt. Wenn Sie maximale Genauigkeit wünschen, ist Evolutionary wahrscheinlich ein guter Weg. Seien Sie sich nur bewusst, dass es lange dauern wird.

Goal Seek und Solver: Excel auf die nächste Stufe bringen

Da Sie jetzt mit den Grundlagen der Lösung unbekannter Variablen in Excel vertraut sind, steht Ihnen eine völlig neue Welt der Kalkulationstabellenberechnung offen.

Mit Goal Seek können Sie Zeit sparen, indem Sie einige Berechnungen beschleunigen. Mit Solver können Sie die Berechnungsfähigkeiten von Excel um ein Vielfaches erweitern. Berechnen der grundlegenden Statistiken in Excel: Handbuch für Anfänger Berechnen der grundlegenden Statistiken in Excel: Das Handbuch für Einsteiger von Microsoft Excel mache Statistiken! Sie können Prozentsätze, Durchschnittswerte, Standardabweichung, Standardfehler und Student-T-Tests berechnen. Weiterlesen .

Es geht nur darum, sich mit ihnen vertraut zu machen. Je öfter Sie sie verwenden, desto nützlicher werden sie.

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

Erfahren Sie mehr über: Microsoft Excel, Microsoft Office-Tipps, Tabellenkalkulation.