Am 14. September 2017 haben wir eine überarbeitete Fassung unserer Datenschutzrichtlinie veröffentlicht. Wenn Sie video2brain.com weiterhin nutzen, erklären Sie sich mit diesem überarbeiteten Dokument einverstanden. Bitte lesen Sie es deshalb sorgfältig durch.

Excel 2013 für Profis

Zu Lösungen mit Hilfe des Solvers gelangen

LinkedIn Learning kostenlos und unverbindlich testen!

Jetzt testen Alle Abonnements anzeigen
Der Solver erweitert die Möglichkeiten der Zielwertsuche. Die erzielten Ergebnisse, einschließlich der Ausgangswerte, lassen sich im Antwort-, Sensitivitäts- und Grenzwertbericht darstellen. Erklärt wird auch die Bedeutung des Reduzierten Gradienten und des Lagrange-Multiplikators.

Transkript

Mit eines der wichtigsten Tools zur Datenanalyse ist der "Solver". Aufgabe dieses "Solvers" ist es, unter Vergabe von Nebenbedingungen, Berechnungen durchzuführen, die unter Einhaltung dieser Bedingungen ein bestimmtes maximales oder minimales, oder auch ein vorgegebenes Ergebnis, erzielen. Damit lassen sich teilweise Aufgaben aus dem Bereich des "Operation Research" lösen. "Operation Research" - auf deutsch heißt das "Unternehmensforschung" - gehört zum Teilgebiet der angewandten Mathematik. Bevor Sie aber den "Solver" einsetzen können, müssen Sie ihn - da es sich um ein mitgeliefertes Add-In handelt - zunächst entsprechend laden. Dazu gehen Sie in die "Optionen", wählen "Add-Ins" aus, "Verwalten von Excel Add-Ins", und hier sehen Sie, hier befindet sich der "Solver", den ich jetzt anklicke und mit "OK" dann bestätige. In der Registerkarte "Daten" sehen Sie, dass hier eine weitere Gruppe eingefügt wurde, nämlich die Gruppe "Solver". Hierüber können Sie jetzt mit dem "Solver" entsprechend arbeiten und die Aufgabenstellungen erledigen, die hier beispielsweise die sogenannte "Break-Even"-Analyse an uns stellt. Bei dieser "Break-Even"-Analyse geht es darum, den maximalen Deckungsbeitrag zu ermitteln bei gegebenen Parametern. Die Parameter in diesem Fall sind zum einen die Menge, zum anderen - als begrenzender Parameter - die Minuten. Bevor der "Solver" nun eingesetzt wird, vielleicht nochmal ein paar kurze Worte zu dem, was der "Solver" macht. "Solver" ist englisch, das haben Sie sich wahrscheinlich schon denken können, und bedeutet "Löser", oder - frei übersetzt - "Problemlöser". Er soll uns bei Berechnungsproblemen behilflich sein. Er soll Berechnungen durchführen, wobei sich die Berechnungsgrenzen durch Nebenbedingungen ergeben. Dabei wird nach dem optimalen Wert gesucht. Dies geschieht durch Neuberechnung der Zielzelle, die so lange durchgeführt wird, bis der Wert erreicht ist. Diese Möglichkeit unterscheidet den "Solver" gravierend von der Zielwertsuche, die nicht - wie der "Solver" - die Mehrfach-Iteration beherrscht. Das soll als Theorie genügen. Kommen wir nun zum "Solver" selbst. Ich rufe den "Solver" auf und bekomme ein Fenster, bei dem ich verschiedene Parameter einstellen kann. Ich habe übrigens das Ziel hier bereits festgelegt, auch die Variablenzellen und die Nebenbedingungen. Da ich aber bei Null beginnen möchte, werde ich jetzt alles zurücksetzen und nochmal ganz von vorne beginnen. Beim Ziel habe ich die Auswahl zwischen "Summe Umsatz" und "Summe Deckungsbeitrag". Selbstverständlich entscheide ich mich hier für die Summe des Deckungsbeitrags. Meine Berechnung soll einen maximalen Wert ergeben. Zunächst mit dem maximalen, oder bei "Kosten" mit dem minimalen, Wert zu rechnen ist immer sinnvoll, bevor Sie sich für einen konkreten Wert entscheiden, falls Sie das überhaupt machen wollen. Dann kommen die Variablenzellen. In unserem Falle sind das die Stückzahlen. Die Stückzahlen, die bei entsprechender Auswahl dafür sorgen, dass der Deckungsbeitrag maximiert wird. Ich markiere hier diese "Stückzahlen", und nun kommen meine Nebenbedingungen. Meine Nebenbedingungen begrenzen die Rechenwut des "Solvers" - in unserem Fall sind es die "Fertigungsminuten" - denn sonst würde der "Solver" natürlich ins Unendliche rechnen. Ich gehe jetzt bei "Nebenbedingungen" auf "Hinzufügen", wähle den entsprechenden Zellbezug und sage, dieser soll 49000<= sein. Dann klicke ich auf "OK" und Sie sehen, die Fertigungskapazität wird hier als Nebenbedingung eingetragen. Alle anderen Einstellungen lasse ich im Moment unverändert und möchte gerne lösen. Ich klicke hier auf "Lösen" und der "Solver" beginnt mit der Berechnung. Er kommt auch relativ schnell zu einem Ergebnis. Und wichtig ist, dass hierbei die Meldung erscheint: "Solver hat eine Lösung gefunden. Alle Nebenbedingungen und Optionen wurden eingehalten". Jetzt kann ich diese "Solver"-Lösung akzeptieren, oder ich kann sie natürlich auch wieder entsprechend verwerfen. Vielleicht möchte ich sogar das Ganze reproduzierbar abspeichern. Dann könnte ich hier ein "Szenario" speichern. Davon sehe ich im Moment aber mal ab und klicke hier auf "OK". Er hat hier natürlich etwas - aus rein kaufmännischer Sicht - Sinnvolles gemacht. Er hat gesagt: "Ich baue selbstverständlich nur das Produkt, das mir den höchsten Leistungserfolgssatz, also sprich den Deckungsbeitrag pro Zeiteinheit, bietet. Das ist zwar rechnerisch richtig, aber das wäre vergleichbar, als wenn ein großer Automobilhersteller sagen würde: "Ich produziere nur noch das Luxusprodukt, weil ich hier den höchsten Deckungsbeitrag habe, weil ich hier die meisten Gewinne mit erzielen kann, und die Kleinwagen, die lasse ich außen vor." Das kann man natürlich nicht machen, und in unserem fiktiven Beispiel würde selbstverständlich unser Vertrieb auch sagen: "Das geht natürlich nicht. Von diesem Produkt "C" können wir maximal 4000 Stück absetzen, aber wir müssen beispielsweise von dem Produkt "B" mindestens 3000 Stück anbieten." OK, das werden wir jetzt in den Nebenbedingungen entsprechend berücksichtigen. Ich wähle wieder den "Solver" aus und werde jetzt weitere Nebenbedingungen hinzufügen. Ich klicke auf "Hinzufügen" und die eine Nebenbedingung war: Das Produkt "C" - das ich jetzt hier anklicke - darf maximal 4000 Stück betragen. Das füge ich jetzt hinzu. Und die nächste Nebenbedingung wäre: Das Produkt "B" muss mindestens, also: >=3000 Stück sein. Jetzt kann ich auf "OK" klicken, und Sie sehen, diese beiden Nebenbedingungen wurden hier entsprechend eingetragen. Auch hier klicke ich wieder auf "Lösen", und auch hier findet der "Solver" sofort eine Lösung. Jetzt möchte ich allerdings sehen, was er gemacht hat. Dazu stehen mir entsprechende Berichte zur Verfügung, und zwar der Antwort-, der Sensitivitäts- und der Grenzwertbericht. Außerdem möchte ich gerne mein Szenario speichern. Ich klicke auf "Szenario speichern" und sage: "Das Szenario nenne ich 'Solver1'". Ich klicke auf "OK". Jetzt muss ich allerdings die Berichte wieder markieren, die Lösung akzeptiere ich, ich klicke auf "OK" und Sie sehen, jetzt sehen diese Werte anders aus. Kontrollieren wir ganz kurz, was er gemacht hat: 4000 Stück Produkt "C", 3000 Stück Produkt "B" und mit der restlichen Fertigungskapazität wird dann das Produkt "A" gebaut. Voraussetzung natürlich immer, dass diese Produkte auf den gleichen Maschinen gefertigt werden. Was ist passiert? Das können wir uns nun genauer anschauen mit Hilfe der Berichte, und Sie sehen, hier unten sind drei Berichte angelegt worden. Der Antwortbericht, der Sensitivitätsbericht und der Grenzwertbericht. Zunächst der Antwortbericht. In diesem Antwortbericht wird neben der Uhrzeit und dem Datum festgehalten, dass eine Lösung gefunden wurde. Es wurde weiter festgelegt, mit welchem Modul gerechnet wurde, wie viele Iterationen er durchgeführt hat, wie viele Teilprobleme durchgeführt wurden und die "Solver"-Optionen. Das soll uns im Moment aber nicht so interessieren, sondern uns interessieren die Werte. Wenn Sie sich beispielsweise den Lösungswert, den ursprünglichen Lösungswert, nicht gemerkt haben: Der "Solver" hat das gemacht. Sie sehen hier: Das waren diese 196000. Unser jetziger Lösungswert beträgt 145000. Weiter finden Sie hier die ursprünglichen Stückzahlen und die neuen Stückzahlen. Und dann folgen die Nebenbedingungen. Diese Nebenbedingungen sind einschränkend, das bedeutet soviel: Hätte ich mehr Fertigungskapazität zur Verfügung, könnte ich natürlich bessere Ergebnisse erzielen. Mein Produkt "B" ist ebenfalls einschränkend. Der "Solver" würde lieber weniger Produkte bauen. Und hier bei Produkt "C" ebenfalls einschränkend: Hier würde der "Solver" lieber mehr Produkte bauen. Das ist also der sogenannte "Antwortbericht". Kommen wir zu dem Sensitivitätsbericht. Was müssen Sie sich nun unter diesen Angaben vorstellen? Hier - und das kann man sich beinahe denken - geht es darum, welche Stückzahlen gebaut werden. "Reduziert Kosten" bedeutet den Zuwachs, um den sich der Wert in der Zielzelle verändert, wenn sich das Wachstum in der veränderbaren Zelle um 1 Stück ändert. Also mit anderen Worten: Baue ich ein Stück von meinen Produkt "B" mehr, reduziert sich mein Deckungsbeitrag um 2,50 Euro, baue ich ein Produkt vom Produkt "C" mehr, dann erhöht sich mein Deckungsbeitrag um 7,50 Euro. Der "Zielkoeffizient" stellt den jeweiligen Koeffizienten der Entscheidungsvariablen in der Zielfunktion dar. Er dient der übersichtlichen Darstellung der Ergebnisse. Der "Schattenpreis" wiederum gibt den Zuwachs an, um den sich der Wert in der Zielzelle verändert, wenn sich das Wachstum in der entsprechenden Nebenbedingung um ein Stück ändert. Bedeutet: Wenn ich bei gegebener Konstellation die Fertigungsminuten um eine Minute erhöhe, erhöht sich auch mein Deckungsbeitrag um 2,50 Euro. Transparenter wird das Ganze sicherlich dann, wenn Sie es zusätzlich formatieren würden. Das heißt, hier handelt es sich ja um die Stückzahl. Das heißt, wenn Sie das Ganze dann als "Stück" formatieren - dazu muss natürlich dann die Spalte etwas breiter gemacht werden - dieses hier wäre die Anzahl der Minuten, die Sie entsprechend erhöhen müssen. Ebenfalls - weil ich das ja bereits im Bericht gemacht habe - sind hier die entsprechenden Formate schon vorhanden. Und hier handelt es sich praktisch um Euro-Werte. Das gelingt natürlich mit der Tastenkombination Strg+Umschalt+$, um das entsprechend zu formatieren. Hier nochmal die entsprechenden Minuten. Da kann ich das Format natürlich auch recht einfach über diesen Pinsel übertragen. Kommen wir zum dritten Bericht, dem sogenannten "Grenzwertbericht". Hier müssen wir auch erstmal die Spalten entsprechend verbreitern, so dass die Werte auch entsprechend dargestellt werden. Wir haben hier eine Darstellung, die im Moment - zumindest, was das Zielergebnis betrifft - ebenfalls falsch formatiert ist. Das sind natürlich keine Stück, sondern das sind auch Dollar; dann wird das Ganze transparenter. Sie sehen bei meinem Produkt "A" habe ich einen Endwert von 5000 Stück. Der untere Grenzwert "Null" bedeutet: Ich erziele eine Deckungsbeitrag von 95000 Euro, wenn ich dieses Produkt nicht baue. Baue ich dieses Produkt mit der angegebenen Stückzahl, dann erreiche ich einen Deckungsbeitrag von 145000 Euro. Bei meinem Produkt "B" sieht es ja so aus, dass ich gesagt habe, es müssen mindestens 3000 Stück gebaut werden. Aus diesem Grund ist der untere Grenzwert auch 3000, entspricht dem oberen Grenzwert. Daraus sind keine weiteren Erkenntnisse zu ziehen. Und, wenn ich das Produkt "C" nicht baue, ist mein Zielergebnis 65000. Baue ich dieses Produkt, dann erreiche ich wieder das vorgegebene Ergebnis von 145000. Das kann man relativ schnell ausrechnen. Wenn Sie 145000 Euro nehmen, die 65000 Euro abziehen, sind Sie bei 80000 Euro. Teilen Sie diese 80000 Euro durch 4000 Stück, so erreichen Sie einen Deckungsbeitrag von 20 Euro. Sie sehen also, hier können Sie durch unterschiedliche Stückzahlen zu unterschiedlichen Berechnungen kommen. Wichtig ist, dass der "Solver" dabei immer dafür sorgt, dass diese 49000 Fertigungsminuten nicht überschritten werden können. Der "Solver" bietet übrigens noch in den "Optionen" über die Schaltfläche "Modell speichern" und "Modell laden" eine Möglichkeit, verschiedene Problemlösungsmodelle im Arbeitsblatt zu speichern und nach Belieben wieder zu laden. Die Betonung liegt hier übrigens auf "Arbeitsblatt". Das heißt, die Daten stehen immer nur im jeweiligen Arbeitsblatt zur Verfügung und sind in den anderen Arbeitsblättern nicht sichtbar. In diesem Modell werden z.B. alle Angaben und Parameter gespeichert, die für eine "Solver"-Berechnung notwendig sind. Wenn Sie auf "Modell speichern" klicken, schlägt Excel einen Bereich zur Ausgabe der Parameter automatisch vor. Diesen Bereich können Sie je nach Belieben auf Ihrem Tabellenblatt setzen. Er muss nur die gleiche Größe haben, sollte nicht kleiner als der vorgeschlagene Bereich sein, da sonst nicht alle Angaben gespeichert werden können. Allerdings können Sie es sich hier auch wieder einfach machen: Wählen Sie keinen Bereich aus, sondern wählen Sie lediglich die Anfangszelle aus. So werden die Daten automatisch nach unten und nach rechts ausgefüllt. Sie müssen lediglich darauf achten, dass nach unten genügend Platz zur Verfügung steht. In der Regel sind dies sechs Zellen. Nun soll das Ganze auch entsprechend ausprobiert werden. Gehen Sie in die Zelle H2, rufen Sie nochmals den "Solver" auf und gehen Sie auf "Laden Speichern". Sie sehen, die Zelle, die wir gewählt haben, wird hier vorgeschlagen. An dieser Stelle könnten Sie natürlich nochmal ein andere Zelle entsprechend auswählen. Ich klicke nun auf "Speichern" und "Schließen". Sie sehen, dass er die entsprechenden Angaben hier ausgibt. Damit das Ganze etwas transparenter wird, werde ich das nun entsprechend beschriften, und nachdem ich das getan habe, sind die Angaben natürlich etwas leichter lesbar. Sie sehen hier den Zielwert 145000 Euro, dann die Anzahl der veränderbaren Zellen, die hier natürlich noch nicht korrekt sind, das sind 3 Zellen. Dann die Nebenbedingung ist "Wahr", und zwar wenn C5>3000. Diese Nebenbedingung wurde erfüllt, genauso wie die Nebenbedingung für die Zelle D5: Ich darf maximal 4000 Stück bauen für mein Produkt "C". Und auch die Fertigungskapazität ist hier nochmal angegeben. Weiter die "Einstellungen" im "Solver Array". Hier sehen Sie praktisch nochmals zusammengefasst die Werte, die Sie im "Solver" entsprechend eingestellt haben. Wollten Sie ein weiteres "Solver"-Modell erstellen, so könnten Sie, wenn Sie hier auf die "Solver"-Parameter gehen, ein weiteres "Solver"-Modell erstellen und dieses dann entsprechend über "Laden" bzw. "Speichern" hier hereinholen. Sie sehen, hier würden Sie wieder sämtliche Parameter für diesen "Solver" entsprechend eingeben. Sie haben also gesehen, dass Sie mit Hilfe des "Solvers" komplexe Rechnungen durchführen können. Diese Berechnungen können Sie abspeichern, einmal als "Modell", oder zum anderen mit Hilfe des "Szenario Managers". Damit haben Sie ein leistungsfähiges und wirkungsvolles Instrument zur Verfügung, um Planrechnungen durchführen zu können.

Excel 2013 für Profis

Holen Sie mehr aus Excel 2013 heraus und lernen Sie, Ihre Zahlen übersichtlicher darzustellen, gezielter zu analysieren sowie eigene Lösungen zu entwickeln.

7 Std. 56 min (96 Videos)
Derzeit sind keine Feedbacks vorhanden...
 
Hersteller:
Exklusiv für Abo-Kunden
Erscheinungsdatum:30.04.2013

Dieser Online-Kurs ist als Download und als Streaming-Video verfügbar. Die gute Nachricht: Sie müssen sich nicht entscheiden - sobald Sie das Training erwerben, erhalten Sie Zugang zu beiden Optionen!

Der Download ermöglicht Ihnen die Offline-Nutzung des Trainings und bietet die Vorteile einer benutzerfreundlichen Abspielumgebung. Wenn Sie an verschiedenen Computern arbeiten, oder nicht den ganzen Kurs auf einmal herunterladen möchten, loggen Sie sich auf dieser Seite ein, um alle Videos des Trainings als Streaming-Video anzusehen.

Wir hoffen, dass Sie viel Freude und Erfolg mit diesem Video-Training haben werden. Falls Sie irgendwelche Fragen haben, zögern Sie nicht uns zu kontaktieren!