So verwenden Sie die Zielsuche und den Solver von Excel zum Auflösen nach unbekannten Variablen

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

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

Mit Goal Seek und dem Solver-Add-In ist dies möglich. Und wir zeigen Ihnen wie. Lesen Sie weiter, um eine vollständige Anleitung zum Auflösen einer einzelnen Zelle mit Goal Seek oder einer komplizierteren 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 einen sehr einfachen Zahlensatz. Wir haben drei Viertel der Verkaufszahlen und ein Jahresziel. Wir können die Zielsuche verwenden, um herauszufinden, welche Zahlen in Q4 erforderlich sind, um das Ziel zu erreichen.

Wie Sie sehen, beträgt die aktuelle Verkaufssumme 114.706 Einheiten. Wenn wir bis Ende des Jahres 250.000 verkaufen wollen, wie viele müssen wir dann im vierten Quartal verkaufen? Die Zielsuche von Excel wird es uns verraten.

So verwenden Sie die Zielsuche Schritt für Schritt:

Klicken Daten > Was-wäre-wenn-Analyse > Zielsuche . Sie sehen dieses Fenster:
Setze den 'gleich'-Teil deiner Gleichung in das Zelle festlegen Gebiet. Dies ist die Zahl, die Excel zu optimieren versucht. In unserem Fall ist dies die laufende Summe unserer Verkaufszahlen in Zelle B5.
Geben Sie Ihren Zielwert in das Schätzen Gebiet. Wir suchen nach insgesamt 250.000 verkauften Einheiten, daher geben wir in diesem Feld „250.000“ an.
Teilen Sie Excel mit, nach welcher Variable im aufgelöst werden soll Durch das Wechseln der Zelle Gebiet. Wir wollen sehen, wie unser Umsatz im vierten Quartal aussehen muss. Also sagen wir Excel, dass es nach Zelle D2 auflösen soll. Es sieht so aus, wenn es einsatzbereit ist:
Schlag OK für Ihr Ziel zu lösen. Wenn es gut aussieht, einfach zuschlagen OK . Excel informiert Sie, wenn Goal Seek eine Lösung gefunden hat.
Klicken OK erneut und Sie sehen den Wert, der Ihre Gleichung löst, in der Zelle, für die Sie sich entschieden haben Durch das Wechseln der Zelle .

In unserem Fall beträgt die Lösung 135.294 Einheiten. Natürlich hätten wir das auch einfach herausfinden können, indem wir die laufende Summe vom Jahresziel abgezogen hätten. Goal Seek kann aber auch auf eine Zelle angewendet werden, die hat schon Daten drin . Und das ist nützlicher.

Beachten Sie, dass Excel unsere vorherigen Daten überschreibt. Es ist eine gute Idee zu Führen Sie die Zielsuche mit einer Kopie Ihrer Daten aus . Es empfiehlt sich auch, auf den kopierten Daten zu vermerken, dass sie mit der Zielsuche generiert wurden. Sie möchten es nicht mit aktuellen, genauen Daten verwechseln.

xbox serie x vs xbox eins x

Zielsuche ist also ein nützliche Excel-Funktion , aber das ist nicht so beeindruckend. Werfen wir einen Blick auf ein viel interessanteres Tool: das Solver-Add-In.

Was macht der Solver von Excel?

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

Es kann nach einem Höchstwert einer Zahl, einem Mindestwert 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, berücksichtigt Solver dies.

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

Werfen wir einen Blick auf das Laden des Solver-Add-Ins und erfahren Sie dann, wie Sie Solver in Excel 2016 verwenden.

So laden Sie das Solver-Add-In

Excel verfügt standardmäßig nicht über Solver. Da es sich um ein Add-In handelt, müssen Sie es wie andere leistungsstarke Excel-Funktionen zuerst laden. Glücklicherweise ist es bereits auf Ihrem Computer.

Gehen Sie zu Datei > Optionen > Add-Ins . Dann klick auf gehen neben Verwalten: Excel-Add-Ins .

Wenn in dieser Dropdown-Liste etwas anderes als 'Excel-Add-Ins' steht, müssen Sie es ändern:

Im resultierenden Fenster sehen Sie einige Optionen. Stellen Sie sicher, dass das Kästchen neben Solver-Add-In wird überprüft, und Hit OK .

Sie sehen jetzt die Löser Knopf im Analyse Gruppe der Daten Tab:

Wenn Sie die bereits verwendet haben Datenanalyse-Toolpak , sehen Sie die Schaltfläche Datenanalyse. Wenn nicht, wird Solver von selbst angezeigt.

Nachdem Sie das Add-In geladen haben, sehen wir uns nun an, wie es verwendet wird.

So verwenden Sie Solver in Excel

Jede Solver-Aktion besteht aus drei Teilen: dem Ziel, den Variablenzellen und den Einschränkungen. Wir gehen jeden der Schritte durch.

Klicken Daten > Solver . Das Fenster Solver-Parameter wird unten angezeigt. (Wenn die Solver-Schaltfläche nicht angezeigt wird, lesen Sie den vorherigen Abschnitt zum Laden des Solver-Add-Ins.)
Legen Sie Ihr Zellziel fest und teilen Sie Excel Ihr Ziel mit. Das Ziel befindet sich oben im Solver-Fenster und besteht aus zwei Teilen: der Zielzelle und einer Auswahl zwischen Maximieren, Minimieren oder einem bestimmten Wert. Wenn Sie auswählen Max , passt Excel Ihre Variablen an, um die größtmögliche Zahl in Ihrer Zielzelle zu erhalten. Mindest ist das Gegenteil: Solver minimiert die Zielzahl. Wert von können Sie eine bestimmte Zahl angeben, nach der Solver suchen soll.
Wählen Sie die variablen Zellen aus, die Excel ändern kann. Die Variablenzellen werden mit dem Durch Ändern von Variablenzellen Gebiet. Klicken Sie auf den Pfeil neben dem Feld, klicken und ziehen Sie dann, um die Zellen auszuwählen, mit denen Solver arbeiten soll. Beachten Sie, dass dies alle Zellen das kann variieren. Wenn Sie nicht möchten, dass sich eine Zelle ändert, wählen Sie sie nicht aus.
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 variablen Zellen in eine beliebige Zahl zu ändern, können Sie Einschränkungen angeben, die erfüllt werden müssen. Weitere Informationen finden Sie im Abschnitt zum Festlegen von Einschränkungen weiter unten.
Sobald alle diese 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 Ihnen, 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 mitteilen, dass eine Variable größer als 200 sein muss. Wenn Sie verschiedene Variablenwerte ausprobieren, geht Excel mit dieser bestimmten Variablen nicht unter 201.

Um eine Einschränkung hinzuzufügen, klicken Sie auf das Hinzufügen Schaltfläche neben der Einschränkungsliste. Sie erhalten ein neues Fenster. Wählen Sie die Zelle (oder Zellen) aus, die eingeschränkt werden sollen Zellreferenz Feld und wählen Sie dann einen Operator aus.

Hier sind die verfügbaren Operatoren:

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

Alle anders ist etwas verwirrend. Es gibt an, dass jede Zelle in dem Bereich, den Sie auswählen, für 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 am Ende 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, sich daran zu erinnern, dass Sie es können mehrere Zellen auswählen für Zellreferenz. Wenn beispielsweise sechs Variablen Werte über 10 haben sollen, 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 Hauptfenster von Solver 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:

Darin haben wir fünf verschiedene Jobs, von denen jeder einen anderen Tarif bezahlt. Wir haben auch die Anzahl der Stunden, die ein theoretischer Arbeiter in jeder dieser Jobs in einer bestimmten Woche gearbeitet hat. Wir können das Solver-Add-In verwenden, um herauszufinden, wie die Gesamtvergütung maximiert werden kann, während bestimmte Variablen innerhalb einiger Einschränkungen gehalten werden.

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

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

Es kann hilfreich sein, Ihre Einschränkungen wie diese zu schreiben, bevor Sie Solver verwenden.

So würden wir das in Solver einrichten:

Beachten Sie zunächst, dass Ich habe eine Kopie der Tabelle erstellt Wir überschreiben also nicht das Original, das unsere aktuellen Arbeitszeiten enthält.

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

Lass uns schlagen Lösen und schau was passiert.

Solver hat eine Lösung gefunden! Wie Sie links im Fenster oben sehen können, sind unsere Einnahmen um 130 USD gestiegen. Und alle Auflagen wurden erfüllt.

Was kann ich auf meinem PC upgraden?

Um die neuen Werte beizubehalten, vergewissern Sie sich, dass Solver-Lösung behalten wird geprüft und getroffen 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 skizziert werden sollen (ich empfehle es) und drücken Sie OK .

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

In unserem Fall sind die Berichte nicht sehr spannend und es gibt nicht viele interessante Informationen. Wenn Sie jedoch eine kompliziertere Solver-Gleichung ausführen, finden Sie möglicherweise einige nützliche Berichtsinformationen in diesen neuen Arbeitsblättern. Klicken Sie einfach auf + Schaltfläche an der Seite eines Berichts, um weitere Informationen zu erhalten:

Erweiterte Solver-Optionen

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

Am offensichtlichsten ist die Lösungsmethode:

Sie können zwischen GRG Nonlinear, Simplex LP und Evolutionary wählen. Excel bietet eine einfache Erklärung, wann Sie jeden verwenden sollten. Eine bessere Erklärung erfordert einige Kenntnisse in Statistik und Regression.

Um zusätzliche Einstellungen anzupassen, drücke einfach auf die Optionen Taste. Sie können Excel über ganzzahlige Optimalität informieren, Berechnungszeitbeschränkungen festlegen (nützlich für umfangreiche Datensätze) und anpassen, wie die GRG- und Evolutions-Lösungsmethoden ihre Berechnungen durchführen.

Auch wenn Sie nicht wissen, was dies bedeutet, machen Sie sich keine Sorgen. Wenn Sie mehr über die zu verwendende Lösungsmethode wissen möchten, bietet Engineer Excel eine guter Artikel, der es für dich erklärt . Wenn Sie maximale Genauigkeit wünschen, ist Evolutionary wahrscheinlich ein guter Weg. Seien Sie sich nur bewusst, dass es lange dauern wird.

Zielsuche und -lösung: Excel auf die nächste Stufe heben

Nachdem Sie sich nun mit den Grundlagen der Lösung unbekannter Variablen in Excel vertraut gemacht haben, steht Ihnen eine völlig neue Welt der Tabellenkalkulation offen.

Goal Seek kann Ihnen helfen, Zeit zu sparen, indem es einige Berechnungen schneller macht, und Solver fügt eine enorme Menge an Leistung hinzu Die Rechenfähigkeiten von Excel .

Es geht nur darum, sich mit ihnen wohlzufühlen. Je mehr Sie sie verwenden, desto nützlicher werden sie.

Verwenden Sie in Ihren Tabellenkalkulationen die Zielsuche oder den Solver? Welche anderen Tipps können Sie geben, um die besten Antworten aus ihnen herauszuholen? Teilen Sie Ihre Gedanken in den Kommentaren unten mit!

Teilen Teilen Tweet Email 5 Tipps zum Aufladen Ihrer VirtualBox-Linux-Maschinen

Haben Sie genug von der schlechten Leistung virtueller Maschinen? Hier ist, was Sie tun sollten, um Ihre VirtualBox-Leistung zu steigern.

Weiter lesen Verwandte Themen

Produktivität
Kalkulationstabelle
Microsoft Excel
Tipps zu Microsoft Office

Über den Autor Dann Albright(506 veröffentlichte Artikel)

Dann ist ein Content-Strategie- und Marketingberater, der Unternehmen dabei unterstützt, Nachfrage und Leads zu generieren. Er bloggt auch über Strategie und Content Marketing auf dannalbright.com.

Mehr von Dann Albright

Abonniere unseren Newsletter

Abonnieren Sie unseren Newsletter für technische Tipps, Rezensionen, kostenlose E-Books und exklusive Angebote!

Klicken Sie hier, um zu abonnieren