Excel-Funktion lösen. Aktivieren, Anwendungsfall mit Screenshots

„Nach einer Lösung suchen“ ist ein Excel-Add-In, mit dem es möglich ist, die beste Lösung für Probleme basierend auf den angegebenen Einschränkungen auszuwählen. Die Funktion ermöglicht es, Mitarbeiter einzuplanen, Kosten oder Investitionen zu verteilen. Wenn Sie wissen, wie diese Funktion funktioniert, sparen Sie Zeit und Mühe.

Was ist Suche nach Lösungen

In Kombination mit verschiedenen anderen Optionen in Excel gibt es eine weniger beliebte, aber äußerst notwendige Funktion „Nach einer Lösung suchen“. Obwohl es nicht einfach ist, es zu finden, hilft es, es kennenzulernen und zu verwenden, um viele Probleme zu lösen. Die Option verarbeitet die Daten und gibt die optimale Lösung aus den erlaubten an. Der Artikel beschreibt, wie die Suche nach einer Lösung direkt funktioniert.

So aktivieren Sie die Funktion „Nach einer Lösung suchen“.

Trotz der Effektivität befindet sich die betreffende Option nicht an prominenter Stelle in der Symbolleiste oder im Kontextmenü. Die meisten Benutzer, die in Excel arbeiten, sind sich seiner Anwesenheit nicht bewusst. Standardmäßig ist diese Funktion deaktiviert, um sie anzuzeigen, führen Sie die folgenden Aktionen aus:

1. Öffnen Sie die „Datei“, indem Sie auf den entsprechenden Namen klicken.
2. Klicken Sie auf den Abschnitt „Einstellungen“.
3. Wählen Sie dann den Unterabschnitt „Add-Ons“ aus. Hier werden alle Add-Ons des Programms angezeigt, darunter erscheint die Aufschrift „Verwaltung“. Auf der rechten Seite befindet sich ein Popup-Menü, in dem Sie „Excel-Add-Ins“ auswählen sollten. Klicken Sie dann auf „Los“.
   

   

4. Auf dem Monitor wird ein zusätzliches Fenster „Add-Ins“ angezeigt. Aktivieren Sie das Kontrollkästchen neben der gewünschten Funktion und klicken Sie auf OK.
5. Die gewünschte Funktion erscheint in der Multifunktionsleiste rechts neben dem Abschnitt „Daten“.

Über Modelle

Diese Informationen sind äußerst nützlich für diejenigen, die sich gerade mit dem Konzept des „Optimierungsmodells“ vertraut machen. Bevor Sie die „Suche nach einer Lösung“ verwenden, wird empfohlen, die Materialien zu den Methoden zum Erstellen von Modellen zu studieren:

• Die in Betracht gezogene Option wird es ermöglichen, die beste Methode zu ermitteln, um Mittel für Investitionen, die Beladung der Räumlichkeiten, die Lieferung von Gütern oder andere Maßnahmen zuzuweisen, wenn es erforderlich ist, die beste Lösung zu finden.
• Die „optimale Methode“ in einer solchen Situation würde bedeuten: Einnahmen steigern, Kosten senken, Qualität verbessern etc.

Typische Optimierungsaufgaben:

• Festlegung eines Produktionsplans, bei dem der Gewinn aus dem Verkauf freigegebener Waren maximal ist.
• Ermittlung von Transportkarten, bei denen die Transportkosten minimiert werden.
• Suchen Sie nach der Verteilung mehrerer Maschinen für verschiedene Arten von Arbeiten, damit die Produktionskosten gesenkt werden.
• Bestimmung der kürzesten Zeit für die Fertigstellung der Arbeiten.

Wichtig! Um die Aufgabe zu formalisieren, muss ein Modell erstellt werden, das die Hauptparameter des Fachgebiets widerspiegelt. In Excel ist ein Modell eine Reihe von Formeln, die Variablen verwenden. Die betrachtete Option sucht nach solchen Indikatoren, dass die Zielfunktion größer (kleiner) oder gleich dem angegebenen Wert ist.

Vorbereitungsphase

Bevor Sie eine Funktion auf dem Menüband platzieren, müssen Sie verstehen, wie die Option funktioniert. Beispielsweise gibt es Informationen zum Verkauf von Waren, die in der Tabelle angegeben sind. Die Aufgabe besteht darin, jedem Artikel einen Rabatt zuzuweisen, der 4.5 Millionen Rubel betragen würde. Der Parameter wird in einer Zelle namens Ziel angezeigt. Darauf aufbauend werden weitere Parameter berechnet.

Unsere Aufgabe wird es sein, den Rabatt zu berechnen, mit dem die Summen für den Verkauf verschiedener Produkte multipliziert werden. Diese 2 Elemente sind durch eine Formel verbunden, die wie folgt geschrieben ist: =D13*$G$2. Wobei in D13 die Gesamtmenge für die Implementierung geschrieben wird und $G$2 die Adresse des gewünschten Elements ist.

Funktion verwenden und einrichten

Wenn die Formel fertig ist, müssen Sie die Funktion selbst direkt verwenden:

1. Sie müssen zum Abschnitt „Daten“ wechseln und auf „Nach einer Lösung suchen“ klicken.

2. Es öffnen sich die „Optionen“, in denen die erforderlichen Einstellungen vorgenommen werden. In der Zeile „Zielfunktion optimieren:“ sollten Sie die Zelle angeben, in der die Summe der Rabatte angezeigt wird. Die Koordinaten können Sie selbst vorgeben oder aus dem Dokument auswählen.

3. Als nächstes müssen Sie zu den Einstellungen anderer Parameter gehen. Im Abschnitt „Bis:“ ist es möglich, die maximale und minimale Grenze oder eine genaue Zahl festzulegen.

4. Anschließend wird das Feld „Variablenwerte ändern:“ ausgefüllt. Hier werden die Daten der gewünschten Zelle eingetragen, die einen bestimmten Wert enthält. Koordinaten werden selbstständig eingetragen oder die entsprechende Zelle im Dokument angeklickt.

5. Anschließend wird die Registerkarte „Nach Einschränkungen:“ bearbeitet, in der die Einschränkungen für die angewendeten Daten festgelegt werden. Ausgenommen sind beispielsweise Dezimalbrüche oder negative Zahlen.

6. Danach öffnet sich ein Fenster, in dem Sie Einschränkungen in den Berechnungen hinzufügen können. Die Anfangszeile enthält die Koordinaten einer Zelle oder eines ganzen Bereichs. Nach den Bedingungen der Aufgabe werden die Daten der gewünschten Zelle angezeigt, in der der Rabattindikator angezeigt wird. Dann wird das Vergleichszeichen bestimmt. Es wird auf „größer oder gleich“ gesetzt, damit der Endwert kein Minuszeichen hat. Das in Zeile 3 eingestellte „Limit“ ist in dieser Situation 0. Es ist auch möglich, mit „Hinzufügen“ ein Limit zu setzen. Die nächsten Schritte sind gleich.

7. Wenn die obigen Schritte abgeschlossen sind, erscheint das eingestellte Limit in der größten Zeile. Die Liste kann umfangreich sein und hängt von der Komplexität der Berechnungen ab, in einer bestimmten Situation ist jedoch 1 Bedingung ausreichend.

8. Darüber hinaus ist es möglich, weitere erweiterte Einstellungen auszuwählen. Unten rechts gibt es eine Option „Optionen“, mit der Sie dies tun können.

9. In den Einstellungen können Sie die „Begrenzungsgenauigkeit“ und „Lösungsgrenzen“ festlegen. In unserer Situation besteht keine Notwendigkeit, diese Optionen zu verwenden.

10. Wenn die Einstellungen abgeschlossen sind, startet die Funktion selbst – klicken Sie auf „Lösung finden“.

11. Danach führt das Programm die erforderlichen Berechnungen durch und gibt die endgültigen Berechnungen in den erforderlichen Zellen aus. Dann öffnet sich ein Fenster mit den Ergebnissen, wo die Ergebnisse gespeichert / verworfen werden, oder die Suchparameter entsprechend neu konfiguriert werden. Wenn die Daten den Anforderungen entsprechen, wird die gefundene Lösung gespeichert. Wenn Sie vorher das Kästchen „Zurück zum Dialogfeld mit den Lösungssuchoptionen“ aktivieren, wird ein Fenster mit Funktionseinstellungen geöffnet.

12. Es besteht die Möglichkeit, dass sich die Berechnungen als fehlerhaft erwiesen haben oder dass die Ausgangsdaten geändert werden müssen, um andere Indikatoren zu erhalten. In einer solchen Situation müssen Sie das Einstellungsfenster erneut öffnen und die Informationen noch einmal überprüfen.
13. Wenn die Daten korrekt sind, kann eine alternative Methode verwendet werden. Für diese Zwecke müssen Sie auf die aktuelle Option klicken und die am besten geeignete Methode aus der angezeigten Liste auswählen:

• Finden einer Lösung mit einem verallgemeinerten Gradienten für nichtlineare Probleme. Standardmäßig wird diese Option verwendet, aber es ist möglich, andere zu verwenden.
• Finden von Lösungen für lineare Probleme basierend auf der Simplex-Methode.
• Verwenden der evolutionären Suche, um eine Aufgabe abzuschließen.

Achtung! Wenn die oben genannten Optionen die Aufgabe nicht bewältigen konnten, sollten Sie die Daten in den Einstellungen erneut überprüfen, da dies häufig der Hauptfehler bei solchen Aufgaben ist.

14. Wenn der gewünschte Rabatt eingegangen ist, muss er noch angewendet werden, um die Höhe der Rabatte für jeden Artikel zu berechnen. Dazu wird das Anfangselement der Spalte „Rabattbetrag“ markiert, die Formel geschrieben «=D2*$G$2» und drücken Sie „Eingabe“. Dollarzeichen werden so gesetzt, dass sich G2 nicht ändert, wenn die Formel auf benachbarte Linien ausgedehnt wird.

15. Der Rabattbetrag für den ursprünglichen Artikel wird nun abgerufen. Dann sollten Sie den Cursor über die Ecke der Zelle bewegen, wenn es zu einem „Plus“ wird, die LMB gedrückt wird und die Formel auf die erforderlichen Linien gestreckt wird.
16. Danach wird der Tisch endgültig fertig sein.

Suchoptionen laden/speichern

Diese Option ist nützlich, wenn Sie verschiedene Einschränkungsoptionen anwenden.

1. Klicken Sie im Menü Optionen der Lösungssuche auf Laden/Speichern.
2. Geben Sie den Bereich für den Modellbereich ein und klicken Sie auf Speichern oder Laden.

Beim Speichern des Modells wird ein Verweis auf 1 Zelle einer leeren Spalte eingegeben, in der das Optimierungsmodell platziert wird. Beim Laden des Modells wird ein Verweis auf den gesamten Bereich eingegeben, der das Optimierungsmodell enthält.

Wichtig! Um die letzten Einstellungen im Menü Lösungsoptionen zu speichern, wird eine Arbeitsmappe gespeichert. Jedes darin enthaltene Blatt hat seine eigenen Solver-Add-In-Optionen. Darüber hinaus ist es möglich, mehr als 1 Aufgabe für ein Blatt festzulegen, indem Sie auf die Schaltfläche „Laden oder Speichern“ klicken, um einzelne Aufgaben zu speichern.

Ein einfaches Beispiel für die Verwendung von Solver

Der Container muss mit Containern beladen werden, damit seine Masse maximal ist. Der Tank hat ein Volumen von 32 Kubikmetern. m. Eine gefüllte Kiste hat ein Gewicht von 20 kg, ihr Volumen beträgt 0,15 Kubikmeter. m. Kiste – 80 kg und 0,5 cu. m. Es ist erforderlich, dass die Gesamtzahl der Behälter mindestens 110 Stück beträgt. Die Daten sind wie folgt organisiert:

Die Modellvariablen sind grün markiert. Die Zielfunktion ist rot hervorgehoben. Einschränkungen: nach der kleinsten Anzahl von Behältern (größer oder gleich 110) und nach Gewicht (=SUMMENPRODUKT(B8:C8,B6:C6) – Gesamttaragewicht im Behälter.

Analog betrachten wir das Gesamtvolumen: =SUMMENPRODUKT(B7:C7,B8:C8). Eine solche Formel ist notwendig, um das Gesamtvolumen der Container zu begrenzen. Dann werden über die „Suche nach einer Lösung“ Links zu Elementen mit Variablen, Formeln und den Indikatoren selbst (oder Links zu bestimmten Zellen) eingegeben. Natürlich ist die Anzahl der Container eine ganze Zahl (auch eine Einschränkung). Wir drücken auf „Lösung finden“, wodurch wir eine solche Anzahl von Containern finden, wenn die Gesamtmasse maximal ist und alle Einschränkungen berücksichtigt werden.

Lösungssuche konnte keine Lösungen finden

Eine solche Benachrichtigung wird angezeigt, wenn die betreffende Funktion keine Kombinationen von Variablenbewertungen gefunden hat, die jede Einschränkung erfüllen. Bei der Simplex-Methode ist es durchaus möglich, dass es keine Lösung gibt.

Wenn eine Methode zur Lösung nichtlinearer Probleme verwendet wird, zeigt dies in allen Fällen ausgehend von den anfänglichen Indikatoren der Variablen, dass die mögliche Lösung weit von solchen Parametern entfernt ist. Wenn Sie die Funktion mit anderen Anfangsindikatoren der Variablen ausführen, gibt es wahrscheinlich eine Lösung.

Bei Verwendung der nichtlinearen Methode wurden beispielsweise die Elemente der Tabelle nicht mit Variablen gefüllt und die Funktion fand keine Lösungen. Das bedeutet nicht, dass es keine Lösung gibt. Unter Berücksichtigung der Ergebnisse einer bestimmten Bewertung werden nun andere Daten in die Elemente mit Variablen eingegeben, die den erhaltenen nahe kommen.

In jedem Fall sollten Sie das Modell zunächst auf das Fehlen eines Constraint-Konflikts untersuchen. Oft ist dies mit einer falschen Wahl des Übersetzungsverhältnisses oder des Begrenzungsindikators verbunden.

Im obigen Beispiel beträgt die maximale Volumenanzeige 16 Kubikmeter. m statt 32, weil eine solche Beschränkung den Indikatoren für die Mindestanzahl von Sitzplätzen widerspricht, da sie der Anzahl von 16,5 Kubikmetern entsprechen wird. m.

Zusammenfassung

Auf dieser Grundlage hilft die Option „Nach einer Lösung suchen“ in Excel bei der Lösung spezifischer Probleme, die auf den üblichen Wegen eher schwierig oder unmöglich zu lösen sind. Die Schwierigkeit bei der Anwendung dieser Methode besteht darin, dass diese Option zunächst ausgeblendet ist, weshalb die meisten Benutzer sich ihrer Anwesenheit nicht bewusst sind. Außerdem ist die Funktion recht schwer zu erlernen und anzuwenden, bringt aber bei richtiger Recherche große Vorteile und erleichtert Berechnungen.