So automatisieren Sie Excel Solver

In How to Hol More out of Excel Solver habe ich Techniken behandelt, um Ihr Modell zu verbessern und Ihre Analyse zu stärken. Eines der Themen, zu denen ich die meisten Fragen erhielt, ist die Automatisierung des Solvers, um nach mehreren Werten aufzulösen.

Im vorherigen Artikel gab es eine kurze Erklärung, aber nichts widmete sich der Implementierung von Anfang bis Ende. In diesem Artikel gehen wir von Anfang bis Ende in die Tiefe und behandeln die Konzepte und Methoden Schritt für Schritt. Am Ende können Sie jedes Ihrer persönlichen Modelle automatisieren und Ihre Analyse skalieren.

Wie machen wir es

Wir werden die Makroaufzeichnung in Excel nutzen, um den größten Teil des eigentlichen Codes zu verarbeiten. Das Aufnahmemakro-Tool ermöglicht es Excel, unsere manuellen Schritte in VBA-Code zu konvertieren.

Wir werden dann die wichtigsten Codeteile identifizieren, die geändert werden müssen. Dann erstellen wir eine Schleife, um diese Werte dynamisch auszuwählen und zu ersetzen, und führen den Solver erneut aus.

Dies wird halbtechnisch sein, aber durch diesen Entwicklungsstil können wir uns auf den Prozess und die Konzepte konzentrieren, anstatt uns darauf zu konzentrieren, wie man Code schreibt.

Einrichten des Makrorekorders

Die Makroaufzeichnungsfunktionalität befindet sich auf der Registerkarte Entwickler im Menüband. Dies ist standardmäßig nicht verfügbar, aber einfach zu implementieren. Wenn Sie zu Ihren Menübandoptionen navigieren, sehen Sie ein Kontrollkästchen für die Registerkarte Entwickler.

Der Screenshot unten ist für Mac, aber PC-basiertes Excel ist ähnlich.

Wenn Sie das Solver-Add-On nicht installiert haben, können Sie es über das Add-In-Menü installieren. Die Microsoft-Support-Site enthält Anweisungen für alle Plattformen, Laden Sie das Solver-Add-In in Excel.

Aufzeichnen des Makros

Jetzt, da die Einrichtung abgeschlossen ist, sind wir bereit für die Aufnahme! Richten Sie Ihren Arbeitsbereich mit all Ihren Formeln und Referenzen entsprechend ein. Sie sollten alles so einrichten, dass Sie das Solver-Modell erstellen.

Gehen Sie zurück zur Registerkarte Entwickler im Menüband. Sie sehen ein Blatt Papier mit einem roten Punkt. Klicken Sie auf diese Schaltfläche, um den Rekorder zu öffnen.

Geben Sie Ihrem Makro einen Namen und geben Sie die Beschreibung ein, wenn Sie möchten.

Sobald Sie Ok drücken, wird der Rekorder gestartet.

Gehen Sie die Schritte zum Einrichten Ihres Solvers durch und lösen Sie ihn. Sobald dies abgeschlossen ist, kehren Sie zur Registerkarte Entwickler zurück und beenden Sie die Aufnahme. Es befindet sich an derselben Stelle wie die Schaltfläche, mit der Sie die Aufnahme gestartet haben.

Nachdem wir nun den Basiscode haben, können wir ihn bereinigen und für die nächsten Schritte vorbereiten.

Bereinige den Code

Jetzt können wir den Code bearbeiten, alle unnötigen Teile entfernen und sicherstellen, dass wir nur die wesentlichen Teile der Makroaufzeichnung verwenden.

Sie können den Code über den VBA-Editor von Excel bearbeiten. Um auf Ihren aufgezeichneten Code zuzugreifen, klicken Sie auf die Schaltfläche Makros.

Navigieren Sie im neuen Menü zu Ihrem Makro und klicken Sie auf Bearbeiten.

Jetzt, da wir unseren Code-Editor geöffnet haben, können wir mit den Optimierungen beginnen.

Entfernen von unnötigem Code

Wenn Sie sich während der Aufnahme durch eine Reihe anderer Dinge geklickt haben, können Sie diese jetzt löschen und haben etwas Ähnliches wie unten. Wenn es viel zusätzlichen Code gibt und Sie befürchten, ihn zu knacken, können Sie jederzeit neu aufnehmen.

Einrichten der Solver-Referenzen

Ein letzter Schritt, bevor wir fortfahren, gehen Sie zu Tools > References und wählen Sie Solver. Wenn Sie dies nicht tun, wird das Paket nicht im Makro geladen und Sie erhalten eine Fehlermeldung.

Sie können jetzt Ihre Parameter ändern, öffnen Sie das Makro-Menü, wählen Sie Ihr Makro aus, klicken Sie auf Ausführen und die Ergebnisse werden aktualisiert.

Vergleichen wir den Code mit unserem Solver-Modell. Sie werden sehen, wie es ausgerichtet ist. Die Sprache ist anders, aber Sie können zusammenfassen, was das alles bedeutet.

Als nächstes isolieren wir die Teile, die wir ändern müssen, und schließen unsere Automatisierung ab.

Spülen und wiederholen – die Schleife aufbauen

Jetzt haben wir einen funktionierenden Solver. Das müssen wir mehrmals wiederholen. Lassen Sie uns ein Beispiel zusammenstellen und den Plan festigen.

Wir begannen mit einem Budget von 1.000 US-Dollar im ersten Modell, aber wir wollen dasselbe für 100-Dollar-Schritte bis zu 2.500 US-Dollar tun.

Tauchen wir ein! Wir haben eine Liste von Werten in Spalte B und unsere Ausgabe in Spalte C. Wenn wir den Prozess aufschreiben würden, würde er so aussehen:

1. Setzen Sie die Gesamtausgaben auf den Wert in B10.
2. Lösen Sie das Modell.
3. Platzieren Sie die Ausgabe in C10.
4. Wählen Sie den nächsten B-Wert.
5. Legen Sie die Ausgabe in der Zeile C fest.
6. Wiederholen Sie die Schritte 2-5 für alle Werte in B.

Schleife starten

Schleifen sind Codierungskonstrukte, die eine Aktion wiederholen, bis ein logischer Endpunkt erreicht wird. Eine Schleife wird sich hier als nützlich erweisen, um den gleichen Prozess mit dem Solver für jeden Wert zu wiederholen, bis wir das Ende des Werts erreichen.

In diesem Beispiel definieren wir den Endpunkt durch die Anzahl der Zeilen. B10:B25 hat 16 Werte.

Wir starten unsere Schleife und wickeln sie um unseren Solver-Code. Wir richten eine Variable ein, die die Schleifennummer (i) enthält, und gehen jeden Wert durch, wobei wir die Schleife jedes Mal lösen.

Zurücksetzen des Solvers

Wir fügen am Anfang unseres Codes eine weitere Bedingung hinzu. Da wir den Solver bei jeder Iteration erneut ausführen möchten, setzen wir ihn bei jeder Schleife zurück. Dadurch werden die Einstellungen gelöscht und ein neues Modell gestartet.

Wir können dies über SolverReset tun.

Zellbezüge aktualisieren

Wir lösen jetzt sechzehnmal, müssen aber unsere Zellreferenzen aktualisieren. Während wir ursprünglich auf B10 verwiesen haben, müssen wir bei jedem Durchlauf eine Zelle nach unten verschieben, um die Grenzwerte zu aktualisieren. Dies können wir über die Offset-Funktion tun. Wir können „$B$10“ aktualisieren, um auf die Zelle zu verweisen und dann um i Zeilen versetzt (0 beim ersten Durchlauf, 1 Zeile beim zweiten Lauf usw.). Jedes Mal, wenn die Schleife ausgeführt wird, wird i um eins erhöht.

Sammeln der Ausgabe

Die Ausgabe ändert sich jedes Mal, daher möchten wir sie speichern. Wir können dem gleichen Prozess wie oben folgen, aber von C10 versetzt und gleich dem Wert des gelösten Modells setzen. Dann beginnt die Schleife von vorne.

Wir rufen C10 auf und versetzen es um die entsprechende Anzahl von Zeilen und fügen dann den Wert unserer Ausgabe ein.

Umgehen der Solver-Benachrichtigungen

Sparen wir uns Kopfzerbrechen und lernen wir nicht einen Moment durch Beispiel. Erinnern Sie sich, als Sie den Solver ausgeführt haben und dieses Menü angezeigt wurde, in dem Sie gefragt wurden, ob Sie die Lösung akzeptieren möchten? Das wird jedes Mal angezeigt, wenn Sie es nicht deaktivieren. Stellen Sie sich vor, wenn Sie eine Schleife mit Hunderten von Werten einrichten, müssen Sie jedes Mal auf Akzeptieren klicken!

Fahren Sie fort und fügen Sie die folgenden Zeilen nach Ihrem Solver-Code hinzu und fügen Sie (TRUE) zu SolverSolve hinzu. Dies wird imitieren, dass Sie die Änderungen akzeptieren.

Jetzt haben wir den fertigen Code!

In Betrieb

Wir können den Code jetzt ausführen, indem wir ihn aus der Makroliste auswählen und ausführen. Wenn Sie Ihre Arbeitsmappe als Vorlage verwenden möchten, können Sie das Makro zur einfacheren Verwendung einer Schaltfläche zuweisen. Diese finden Sie im Entwickler-Tab.

Sie können auch beliebige Vergleichsdiagramme hinzufügen. In diesem Fall möchten wir die Wachstumsrate der Ausgaben mit dem Wachstum des Conversion-Volumens vergleichen. Dieses Beispiel ist nicht interessant, aber echte Daten können helfen, Effizienz-Breakpoints aufzudecken

Abschluss

Wir haben einiges abgedeckt! Herzlichen Glückwunsch zur Inbetriebnahme Ihres automatisierten Solvers. Das von uns behandelte Beispiel war zu einfach, aber Sie können die gleichen Konzepte auf jedes Solver-Modell anwenden.

Dies kann enorm viel Zeit für Modelle sparen, die Sie mehrmals lösen müssen, und Benutzer dazu anregen, mit verschiedenen Szenarien zu experimentieren.

Ursprüngliches Veröffentlichungsdatum: 30.07.2019