Embed Size (px)
Citation preview
Fieting, Olaf 1 von 21 23.11.2013, 16:16
Lösen von quadratischen linearen Gleichungssystemen mit EXCEL Am Beispiel eines quadratischen Gleichungssystems mit 2 Gleichungen und 2 Variablen soll Ihnen demonstriert werden, wie Sie unter Nutzung der in EXCEL integrierten Matrixfunktionen als auch des Solvers die Lösungen solcher Gleichungssysteme ermitteln können.
Inhaltsverzeichnis 1 Vorbetrachtungen ........................................................................................................................ 2
2 Erstellen des Gleichungssystems ............................................................................................... 2
3 Lösen des Gleichungssystems mit Hilfe der Substitutionsmethode ........................................... 3
4 Lösen des Gleichungssystems mit Hilfe des Solvers ................................................................. 4
4.1 Erstellen eines Tabellenblattes ................................................................................................... 5
4.2 Lösen des Gleichungssystems mit dem Solver .......................................................................... 5
5 Lösen des Gleichungssystems mit Hilfe der Cramerschen Regel .............................................. 8
5.1 Theoretische Grundlagen ............................................................................................................ 8
5.2 Mathematische Lösung ............................................................................................................... 9
5.3 Lösen des Gleichungssystems mit der integrierten Funktion MDET ........................................ 10
6 Lösen des Gleichungssystems mit Hilfe inverser Matrix und Multiplikation von Matrizen ........ 11
6.1 Theoretische Grundlagen .......................................................................................................... 11
6.2 Mathematische Lösung der Beispielaufgabe ............................................................................ 12
6.2 Lösen des Gleichungssystems mit den integrierten Funktionen MINV und MMULT ............... 13
6.3 Lösen des Gleichungssystems mit den integrierten Funktionen MINV, MMULT und MTRANS ............................................................................................................................ 15
7 Praktische Anwendungsbeispiele ............................................................................................. 16
7.1 Bestimmen der Bestandteile einer Messingplatte ..................................................................... 16
7.2 Entscheidung zum Kauf von Produkten .................................................................................... 18
7.3 Mischen von Teesorten aus Restbeständen ............................................................................. 20
Fieting, Olaf 2 von 21 23.11.2013, 16:16
1 Vorbetrachtungen In der täglichen Berufspraxis kommt es häufig vor, dass Probleme auftauchen, die nur mit Hilfe von quadratischen Gleichungssystemen gelöst werden können. Eine typische Aufgabe aus dem kaufmännischen Bereich ist die Berechnung von Mischungspreisen. Es soll angenommen werden, dass aus einer Kaffeesorte X zu 8,00 EUR je kg und einer anderen Sorte Y zu 12,00 EUR je kg eine Gesamtmischung von 125 kg zu einem Preis von 10,50 EUR das kg hergestellt werden soll. Eine Möglichkeit zur Lösung dieser Aufgabe besteht in der Anwendung des Mischungskreuzes. Da der Schwerpunkt dieses Beitrages aber in der Lösung von quadratischen Gleichungssystemen liegt, soll auf die oben erwähnte Lösungsmöglichkeit nicht eingegangen werden. Ein quadratisches Gleichungssystem liegt vor, wenn die Anzahl der Gleichungen gleich der Anzahl der Variablen ist. Zur Lösung eines solchen Gleichungssystems können verschieden Methoden angewandt werden. Zu diesen gehört der Gaußsche Algorithmus. das Gleichsetzungsverfahren, das Substitutionsverfahren, das Additionsverfahren, die Anwendung der Cramerschen Regel als auch die Nutzung von inversen Matrizen und deren Multiplikation. In diesem Beitrag soll nicht auf alle Varianten eingegangen werden, sondern nur auf die Lösung mit dem Solver, mit der Cramerschen Regel und auf die Anwendung von inversen Matrizen und deren Multiplikation.
2 Erstellen des Gleichungssystems Bevor die o.g. Aufgabe gelöst werden kann, muss ein Gleichungssystem erstellt werden. Für das angeführte Beispiel kann folgendes vorausgesetzt werden: Die Gesamtmenge der Kaffeesorten kann mit der Gleichung
definiert werden. Der Gesamtpreis der Mischung zu 10,50 EUR stellt sich dann wie folgt dar:
Damit ergibt sich für das Gleichungssystem folgende Ansicht:
12511 yx
12550,10128 yx
50,1312128 yx
12511 yx
50,1312128 yx
Fieting, Olaf 3 von 21 23.11.2013, 16:16
3 Lösen des Gleichungssystems mit Hilfe der Substitutionsmethode Die mathematische Lösung soll hier mit Hilfe des Einsetzungsverfahrens demonstriert werden.
Bei Anwendung dieses Verfahrens wird die erste Gleichung nach der Variablen x umgestellt.
Anschließend wird der erhaltene Ausdruck in die zweite Gleichung eingesetzt und nach der Variablen y aufgelöst.
Durch Einsetzen des erhaltenen Wertes für y in die nach x umgestellte erste Gleichung kann der Wert für x ermittelt werden.
Es werden also 46,875 kg der Sorte x und 78,125 kg der Sorte y benötigt, damit die gewünschte Menge zu einem Mischungspreis von 10,50 EUR hergestellt werden kann.
12511 yx
50,1312128 yx
yx 125
50,131212)125(8 yy
50,13121281000 yy
50,3124 y
4
50,312y
125,78y
125,78125x
875,46x
Fieting, Olaf 4 von 21 23.11.2013, 16:16
4 Lösen des Gleichungssystems mit Hilfe des Solvers Der Solver ist in EXCEL ein Werkzeug zur Lösung von Optimierungsaufgaben. Es können mit ihm aber auch Gleichungssysteme mit mehreren Unbekannten gelöst werden. Der Solver ist jedoch bei der Standardinstallation nicht sofort verfügbar. Er muss in allen EXCEL-Versionen erst aktiviert werden. Aktivierung bis EXCEL 2003 (1) Extras – Add-Ins …
(2) Im Dialogfeld „Add-Ins“ die Option „Solver“ auswählen (siehe unten)
(3) Bestätigen mit „OK“
Im Anschluss steht der Solver unter „Extras“ zur Verfügung. Aktivierung in EXCEL 2007 (1) Schaltfläche „Office“
(2) Schaltfläche „EXCEL-Optionen“
(3) Kategorie – Add-Ins …
(4) Verwalten: Excel-Add-Ins – Gehe zu
(5) Im Dialogfeld „Add-Ins“ die Option „Solver“ auswählen (siehe unten)
(6) Bestätigen mit „OK“
Im Anschluss steht der Solver unter „Daten - Analyse“ zur Verfügung. Aktivierung in EXCEL 2010 (1) Menü „Datei“
(2) „Optionen“
(3) Kategorie – Add-Ins …
(4) Verwalten: Excel-Add-Ins – Gehe zu
(5) Im Dialogfeld „Add-Ins“ die Option „Solver“ auswählen (siehe unten)
(6) Bestätigen mit „OK“
Im Anschluss steht der Solver unter „Daten - Analyse“ zur Verfügung.
Fieting, Olaf 5 von 21 23.11.2013, 16:16
4.1 Erstellen eines Tabellenblattes
Zum Lösen dieser Aufgabe mit Hilfe des Solvers muss ein Tabellenblatt erstellt werden, das folgendes Aussehen besitzen kann: In die Zellen A8, A10, E8 und E10 werden die jeweiligen Koeffizienten für die Variablen x und y eingetragen. In die Zellen C8, C10, G8 und G10 wird jeweils eine 0 oder ein anderer beliebiger Wert eingetragen.
Die Zellen I8 und I10 enthalten folgende Formeleinträge: =A8*C8+E8*G8 bzw. =A10*C10+E10*G10
4.2 Lösen des Gleichungssystems mit dem Solver Bei der Lösung des Gleichungssystems, bezogen auf das o.g. Beispiel ist in folgender Reihenfolge vorzugehen: (1) Aufruf des Solvers unter „Extras – Solver“ (bis 2003) bzw. Daten – Analyse – Solver
(ab Version 2007);
(2) Eintragen der Zielzelle (I8)
(3) Einstellen des Zielwertes (Wert und 125)
(4) Eintragen der veränderbaren Zellen (C8 und G8)
(5) Festlegen der Nebenbedingungen (I10=1312,5), die über die Schaltfläche „Hinzufügen“ eingetragen werden können.
Dialogfeld bis Version 2007
Fieting, Olaf 6 von 21 23.11.2013, 16:16
Dialogfeld ab Version 2010
(6) Nach dem Festlegen aller Parameter (siehe unten stehende Abbildung) wird mit
Anklicken der Schaltfläche „Lösen“ die Berechnung ausgelöst.
Dialogfeld bis Version 2007
Dialogfeld ab Version 2010
Fieting, Olaf 7 von 21 23.11.2013, 16:16
Sollte durch den Server eine Lösung gefunden werden, meldet er sich mit folgendem Dialogfeld. Wenn die Lösung angenommen werden soll, muss die Option „Lösung verwenden“ mit „OK“ bestätigt werden.
Dialogfeld bis Version 2007
Dialogfeld ab Version 2010
Die Lösungen werden dann in die veränderbaren Zellen eingetragen.
Auch hier werden für x und y die gleichen Ergebnisse ermittelt, die auch mit Hilfe der Substitutionsmethode bestimmt wurden.
Fieting, Olaf 8 von 21 23.11.2013, 16:16
5 Lösen des Gleichungssystems mit Hilfe der Cramerschen Regel Gabriel Cramer entwickelte für das Lösen quadratischer linearer Gleichungssysteme die nach ihm benannte Regel. Voraussetzung dafür ist jedoch, dass das Gleichungssystem eine eindeutige Lösung besitzt.
5.1 Theoretische Grundlagen Anhand folgenden Gleichungssystems mit zwei Variablen soll demonstriert werden, wie Cramer zu seinen Erkenntnissen kam.
11211 byaxa
22221 byaxa
Zuerst wird die erste Gleichung nach der Variablen y aufgelöst.
xabya 11112
12
111
a
xaby
Der erhaltene Ausdruck für y wird anschließend in die zweite Gleichung für y eingesetzt. Danach wird diese nach der Variablen x aufgelöst.
2
12
1112221 b
a
xabaa
212111222112 )( baxabaxaa
21222111222112 baxaabaxaa
12221222112112 babaxaaxaa
12221222112112 )( babaxaaaa
22112112
122212
aaaa
babax
Durch Multiplikation mit „–1“ wird dann der endgültige Ausdruck erhalten.
21122211
212122
aaaa
babax
Auf ähnliche Weise kann dann der folgende Ausdruck für die Variable y ermittelt werden.
21122211
121211
aaaa
babay
Fieting, Olaf 9 von 21 23.11.2013, 16:16
Wenn nun die einzelnen Bestandteile der Lösungen betrachtet werden, kommt man zur Schlussfolgerung, dass der Nenner beider Ergebnisse und die jeweiligen Zähler mit Hilfe von Determinanten, die aus den entsprechenden Koeffizienten des Gleichungssystems bestehen, bestimmt werden können. Dabei wird die Determinante für den Nenner aus den Koeffizienten der Variablen (Konstantenmatrix) gebildet.
21122211
2221
1211aaaa
aa
aaD
Für die jeweiligen Zähler werden die Koeffizienten der Variablen durch die entsprechenden Absolutglieder (Konstantenmatrix) ersetzt.
212221
222
121baab
ab
abDx
und
211211
221
111abba
ba
baDy
Damit ergibt sich für die Lösung des Gleichungssystems:
D
Dx x
und für D
Dy
y
Dieses Herangehen kann auch für andere Gleichungssysteme höheren Grades genutzt werden. Der Rechenaufwand wird jedoch bedeutend höher ausfallen.
5.2 Mathematische Lösung Bestimmen der Determinanten Für die Determinante des Zählers gilt: Für die beiden anderen Determinanten gilt dann: Ermitteln des x-Wertes
875,464
5,187x
418121128
11D
5187151312121251251312
10125,,
,
,xD
531211258513121513128
01251,,
,
,yD
Fieting, Olaf 10 von 21 23.11.2013, 16:16
Ermitteln des y-Wertes
125,784
5,312y
Es zeigt sich, dass auch mit dieser Methode die gleichen Lösungen ermittelt wurden, die auch mit Hilfe der Substitutionsmethode bestimmt wurden.
5.3 Lösen des Gleichungssystems mit der integrierten Funktion MDET Der oben aufgezeigte Weg kann in EXCEL unter Einsatz der Funktion "MDET" realisiert werden. Die Funktion MDET liefert die Determinante einer Matrix. Die Syntax ist wie folgt festgelegt: = MDET(Matrix) Dabei ist Matrix eine aus Werten bestehende quadratische Matrix, die also die gleiche Anzahl von Zeilen als auch Spalten besitzt. Für die Lösung des einführenden Beispiels könnte folgendes Tabellenblatt genutzt werden In die Zellen A8, A10, E8, E10, I8 und I10 werden die Koeffizienten des Gleichungssystems eingetragen. Da die Funktion MDET nicht getrennt liegende Bereiche ansprechen kann und die mögliche Variante der Koeffizientenmatrix keine Zellangaben verarbeiten kann, müssen außerhalb des Gleichungssystems Hilfsmatrizen (M7:N8, M10:N11, M13:N14) aufgebaut werden. Die dort eingetragenen Werte werden aus den entsprechenden Feldern des Gleichungssystems übernommen. In den Zellen P8, P10 und P13 werden dann die Determinanten für D, Dx und Dy mit folgenden Funktionen ermittelt: Zelle P8: =MDET(M7:N8)
Zelle P10: =MDET(M10:N11)
Zelle P13: =MDET(M13:N14)
Fieting, Olaf 11 von 21 23.11.2013, 16:16
In den Zellen C8 und G8 werden dann die Werte für x und y bestimmt: Zelle C8: =P10/P8
Zelle G8: =P13/P8
Anschließend werden diese Werte in die Zellen C10 bzw. G10 übernommen. Aus diesem Lösungsansatz ist zu ersehen, dass schon bei einem Gleichungssystem mit zwei Variablen ein hoher Aufwand betrieben werden muss. Bei Gleichungssystemen höheren Grades steigt der Aufwand enorm. Deshalb kann der Lösungsansatz über die Nutzung der Funktion MDET als unzweckmäßig betrachtet werden.
6 Lösen des Gleichungssystems mit Hilfe inverser Matrix und Multiplikation von Matrizen Wie im vorhergehenden Punkt beschrieben, lässt sich die Lösung mittels der Funktion MDET nicht komfortabel lösen. Da Ihnen EXCEL aber die Funktionen MINV und MMULT anbietet, soll Ihnen im Folgenden das Herleiten der Lösungen für x und y mittels der inversen Matrix und der Multiplikation der inversen Matrix mit der Konstantenmatrix demonstriert werden.
6.1 Theoretische Grundlagen Bestimmen der Determinante der Koeffizientenmatrix Sollte die Determinante den Wert 0 ergeben, kann keine inverse Matrix gebildet werden. Bilden der inversen Matrix
21122211
2221
1211aaaa
aa
aaD
2221
1211
aa
aaA
D
a
D
a
D
a
D
a
A1121
1222
1
1121
12221 1
aa
aa
DA
Fieting, Olaf 12 von 21 23.11.2013, 16:16
D
ab
D
abx 122221
D
ab
D
aby 212111
Multiplikation der inversen Matrix mit der Konstantenmatrix Die Lösung für beide Variablen lautet dann:
6.2 Mathematische Lösung der Beispielaufgabe Bestimmen der Determinante der Koeffizientenmatrix
418121128
11D
Bestimmen der inversen Matrix
128
11A
18
112
4
11
A
4
12
4
13
4
1
4
8
4
1
4
12
18
112
4
11
A
Multiplikation der inversen Matrix mit der Konstantenmatrix
51312
125
4
12
4
13
,
2
1
1121
1222
b
b
D
a
D
a
D
a
D
a
Fieting, Olaf 13 von 21 23.11.2013, 16:16
Ermitteln de x-Wertes
875,4650,13124
11253 x
Ermitteln des y-Wertes
125,7850,13124
11252 y
Auch hier ist zu sehen, dass dieser Lösungsweg die gleichen Ergebnisse wie die vorangegangenen Wege liefert.
6.2 Lösen des Gleichungssystems mit den integrierten Funktionen MINV und MMULT Der oben aufgezeigte Lösungsweg kann mit Hilfe der integrierten EXCEL-Funktionen MINV und MMULT umgesetzt werden. Die Funktion MINV liefert die Inverse einer Matrix (die zur Matrix gehörende Kehrmatrix). Die Syntax der Funktion ist wie folgt festgelegt: =MINV(Matrix) Dabei ist Matrix eine quadratische Matrix (die Anzahl der Zeilen und Spalten sind gleich). Dabei ist zu beachten, dass Formeln, die Matrizen zurückgeben, als Matrixformeln eingegeben werden müssen. Erstellen einer Matrixformel (a) Eingeben der Formel in die Zelle, wie es auch für normale Formeln und Funktionen
notwendig ist.
Achtung! Diese Formel darf nicht mit der Eingabetaste bestätigt werden. (b) Abschluss der Eingabe und Umwandeln der Formel in eine Matrixformel mit Hilfe der
Tastenkombination STRG + UMSCHALTTASTE + ENTER.
Sollte eine Matrixformel einen Zellbereich umfassen, wie im unten dargestellten Beispiel, so ist dieser Zellbereich zu markieren. In die Ausgangszelle der Markierung ist die entsprechende Formel einzutragen und anschließend in eine Matrixformel umzuwandeln. Die Berechnung wird dann für den gesamten markierten Bereich ausgeführt.
Wenn vorausgesetzt wird, dass eine Matrix folgendes Aussehen besitzt
dc
baA
Fieting, Olaf 14 von 21 23.11.2013, 16:16
bestimmt die Funktion MINV die inverse Matrix nach folgendem Schema: Aug o. g. Beispiel angewandt Ergibt sich für die inverse Matrix Wie aus dem Ergebnis zu ersehen ist, entspricht es dem Ergebnis aus dem Kapitel 6.2. Das Ergebnis der Behandlung der Matrix mit der Funktion MINV muss anschließend mit Hilfe der Funktion MMULT mit der Konstantenmatrix multipliziert werden. Die Funktion MMULT liefert das Produkt zweier Matrizen. Das Ergebnis ist wieder eine Matrix. Diese hat die gleiche Anzahl Zeilen wie die Matrix 1 und die gleiche Anzahl Spalten wie die Matrix 2. Die Syntax ist wie folgt festgelegt:
= MMULT(Matrix1;Matrix2) Für die Lösung des angeführten Beispiels könnte folgendes Tabellenblatt genutzt werden.
)/()/(
)/()/(
cbdaadacbc
dacbbcbdadA
1
128
11A
)/()/(
)/()/(
181121121188
12118118121121
A
4
12
4
13
4
1
4
84
1
4
12
1
A
Fieting, Olaf 15 von 21 23.11.2013, 16:16
Die Angaben in der Koeffizientenmatrix und in der Konstantenmatrix werden aus der linksstehenden Aufgabe übernommen. Die Ergebnisse der Berechnungen mit den Funktionen MMULT und MINV (siehe Abbildung) werden dann an die Zellen C8, C11, G8 und G11 zur Vervollständigung der Tabelle übergeben.
6.3 Lösen des Gleichungssystems mit den integrierten Funktionen MINV, MMULT und MTRANS Eine weitere Möglichkeit der Lösung des Problems besteht darin, dass auf „schmückendes Beiwerk“ verzichtet wird. Das bedeutet, dass nur die Koeffizienten- und Konstantenmatrix erstellt wird. Wenn nur die im vorhergehenden Kapitel genutzte Formel mit MINV und MMULT angewandt wird, erhält man untereinanderliegende Ergebnisse (G6:G7). Zur komplexeren Lösung und damit auch zur einfacheren Lesbarkeit kann mit Hilfe der Funktion MTRANS die senkrechte Lösung in eine horizontale Darstellung umgewandelt werden. Die Funktion MTRANS transponiert eine waagerechte Darstellung in eine senkrechte oder umgekehrt um. Deshalb kann auf eine Ausgabe in den Zellen G6:G7 verzichtet werden. Das heißt, die Ergebnisse können direkt unter der Koeffizientenmatrix ausgegeben werden.
Fieting, Olaf 16 von 21 23.11.2013, 16:16
7 Praktische Anwendungsbeispiele
7.1 Bestimmen der Bestandteile einer Messingplatte Eine Platte aus Messingblech hat die Masse von 9,405 kg und die Abmessungen 500 x 500 x 4,4 (in mm). Wie viel Kupfer und Zink sind in der Legierung enthalten, wenn Kupfer eine Dichte von 8,90 kg/dm³ und Zink eine Dichte von 7,14 kg/dm³ besitzt? Zur Lösung dieses Beispiels ist wie folgt vorzugehen: Ermitteln des Gesamtgewichtes Ermitteln der einzelnen Volumen Ermitteln des Gesamtvolumens Aufstellen des Gleichungssystems Lösung mit dem Solver (Abbildung bis EXCEL 2007)
4059, yx
3
98dm
xmV
cu
cucu
,
3
147dm
xmV
Zn
ZnZn
,
311044055 dmVges ,,
4059, yx
1114798
,,,
yx
Fieting, Olaf 17 von 21 23.11.2013, 16:16
Lösung mit MDET Lösung mit MMULT und MINV Lösung mit MMULT, MINV und MTRANS
Fieting, Olaf 18 von 21 23.11.2013, 16:16
7.2 Entscheidung zum Kauf von Produkten Eine Handelsfirma kauft Produkte zum Listenpreis ein. Die Produkte kosten 9,00 EUR, 11,00 EUR bzw. 17,00 EUR. Insgesamt sollen 1500 Produkte eingekauft werden, wobei der Gesamtpreis 18.100,00 EUR betragen soll. Die Produkte sollen mit 15 %, 20 % bzw. 25 % Aufschlag verkauft werden, wobei ein Gesamtumsatz von 21.830,00 EUR erreicht werden soll. Wie viele Produkte der entsprechenden Sorte müssen eingekauft werden? Hier handelt es sich um ein quadratisches Gleichungssystem mit drei Variablen. An diesem Beispiel soll, auch wenn in der Einleitung nur von quadratischen Gleichungssystemen gesprochen wird, gezeigt werden, wie mittels der aufgezeigten Methoden auch höhere Gleichungssysteme gelöst werden können. Lösung mit dem Solver (Abbildung in EXCEL 2010)
1500 zyx
1810017119 zyx
2183025,212,1335,10 zyx
Fieting, Olaf 19 von 21 23.11.2013, 16:16
Lösung mit MDET Lösung mit MMULT und MINV Lösung mit MMULT, MINV und MTRANS
Fieting, Olaf 20 von 21 23.11.2013, 16:16
7.3 Mischen von Teesorten aus Restbeständen Ein Restposten von 8 kg Tee, je kg zu 16,50 EUR, soll mit einer zweiten Sorte, je kg zu 20,00 EUR, zu einer Mischung verarbeitet werden, die pro kg 19,00 EUR kostet und als Friesentee angeboten werden soll. Wie viel kg der zweiten Sorte sind hinzuzugeben und wie groß ist die Gesamtmenge? Lösung mit dem Solver (Abbildung in EXCEL 2010)
zy 8
1322019 yz
8 yz
zy 192050,168
Fieting, Olaf 21 von 21 23.11.2013, 16:16
Lösung mit MDET Lösung mit MMULT und MINV Lösung mit MMULT, MINV und MTRANS
