Fieting, Olaf 1 von 19 23.11.2013, 15:05
Lösen von Extremwertaufgaben mit EXCEL In der Wissenschaft, aber auch in der Wirtschaft, spielt das Lösen von Extremwertaufgaben eine große Rolle. Immer wieder wird die Frage danach gestellt, was unternommen werden muss, damit die minimalsten Kosten, der größte Gewinn, die größte Ausbeute oder auch der kleinste Materialeinsatz erreicht werden kann. In diesem Beitrag soll demonstriert werden, wie neben dem Einsatz der Differentialrechnung auch der in EXCEL integrierte Solver zur Lösung dieser Probleme genutzt werden kann. Jeder, der beide Verfahren parallel zueinander erprobt hat, wird dann feststellen können, welch eine Reduzierung des Arbeitsaufwandes beim Einsatz von EXCEL erreicht werden kann. Natürlich kann keinem der Nutzer von EXCEL erspart bleiben, dass er bei beiden Verfahren die ersten gleichen Schritte vornehmen muss. In diesem Beitrag sollen dem Anwender von EXCEL beide Verfahrensweisen am Standardbeispiel des Mathematikunterrichts aufgezeigt werden. Des Weiteren sollen im letzten Teil des Beitrages weitere mögliche Aufgabenstellungen aufgeführt werden. (Die mathematischen als auch die Lösungen in EXCEL können über den Autor bezogen werden).
Inhaltsverzeichnis 1 Aufgabenstellung ..................................................................................................................... 2
2 Mathematische Lösung............................................................................................................ 2
2.1 Finden des mathematischen Ansatzes .................................................................................... 3
2.2 Aufstellen der mathematischen Funktion ................................................................................ 3
2.3 Bestimmen der lokalen Extrema .............................................................................................. 4
2.4 Analysieren des Verhaltens der Funktion an den Randstellen ............................................... 5
2.5 Formulieren der/des Ergebnisse/s ........................................................................................... 5
3 Lösen von Extremwertaufgaben mit EXCEL ........................................................................... 6
3.1 Erstellen des entsprechenden Tabellenblattes ....................................................................... 6
3.2 Lösen der Extremwertaufgabe mit Hilfe des in EXCEL integrierten Solvers ........................... 6
4 Praktische Anwendungsbeispiele ............................................................................................ 8
4.1 Berechnung Materialverbrauch für eine Konservendose ........................................................ 8
4.1.1 Mathematische Lösung............................................................................................................ 9
4.1.2 Lösen der Aufgabe mit dem Solver ....................................................................................... 10
4.2 Berechnung Sportanlage ....................................................................................................... 11
4.2.1 Mathematische Lösung.......................................................................................................... 12
4.2.2 Lösen der Aufgabe mit dem Solver ....................................................................................... 13
4.3 Berechnung Bewässerungskanal .......................................................................................... 13
4.3.1 Mathematische Lösung.......................................................................................................... 14
4.3.2 Lösen der Aufgabe mit dem Solver ....................................................................................... 16
4.4 Berechnung Kosten Wasserleitung ....................................................................................... 17
4.2.1 Mathematische Lösung.......................................................................................................... 17
4.2.2 Lösen der Aufgabe mit dem Solver ....................................................................................... 19
Fieting, Olaf 2 von 19 23.11.2013, 15:05
1 Aufgabenstellung Aus einem rechteckigen Blech mit den Seitenlängen S1 = 16 cm und S2 = 6 cm soll ein Gefäß mit maximalem Fassungsvermögen (Volumen) hergestellt werden, indem aus jeder Ecke ein Quadrat herausgeschnitten wird, der Rest zu einem offenen Quader zusammengebogen und verschweißt wird. Wie groß müssen die Seiten des herauszuschneidenden Quadrates sein, damit die Aufgabe realisiert werden kann? Wie groß sind die Seiten a und b des Gefäßes? Wie groß ist das Volumen des Behälters?
2 Mathematische Lösung Zur Lösung von Extremwertaufgaben wird in der Mathematik als Mittel die Differentialrechnung herangezogen. Dabei ist in folgenden Schritten vorzugehen: (1) Finden des mathematischen Ansatzes
(2) Aufstellen der mathematischen Funktion und Festlegung des Definitionsbereiches
(3) Bestimmen der lokalen Extrema
(4) Analysieren des Verhaltens der Funktion an den Randstellen
(5) Formulieren des Ergebnisses
Bei Problemstellungen, die sich mit quadratischen Funktionen beschreiben lassen sind keine Mittel der Differentialrechnung notwendig. Hier reichen Scheitelpunktbetrachtungen. Als Beispiel kann die Aufgabe herangezogen werde, dass man mit 100 m Zaun, eine möglichst große rechteckige Fläche eingezäunt werden soll.
x
S1
S2
x
a
b
Fieting, Olaf 3 von 19 23.11.2013, 15:05
2.1 Finden des mathematischen Ansatzes In diesem Schritt kommt es darauf an, die Größe zu definieren, die das entsprechende Extremum darstellt, von welcher Größe es abhängt und von welcher Art dieses Extremum ist. Weiterhin sind Nebenbedingungen und Einschränkungen sowie weitere, auf der Grundlage der zu suchenden abhängigen Größe, zu bestimmende Werte mathematisch festzulegen. Lösungsansatz
1 Gesuchtes Extremum: Volumen (V)
2 Art des Extremums: Maximum
3 Bestimmende Größe des Extremums: Seitenlänge des Quadrates (x)
4 Nebenbedingungen/Einschränkungen: 0 < x <s2 und 0 < x <3
Gesuchte Lösungen
Maximale Volumen (Vmax)
Länge und Breite des Behälters (a, b)
2.2 Aufstellen der mathematischen Funktion
Die mathematische Funktion, die das Volumen des Behälters beschreibt, kann dabei mit:
definiert werden. Bei Extremwertaufgaben, die zunächst eine Funktion mehrerer Variablen ist, muss durch Anwenden der Nebenbedingungen, diese in eine Funktion mit einer Variablen überführt werden. Aus der obigen Darstellung ist zu erkennen, dass a mit und b mit festgelegt werden kann. Daraus ergibt sich für die Formel des Volumens folgender Ausdruck: Nach Einsetzen der Werte für s1 = 16 cm und s2 = 6 cm erhält man: Das Auflösen dieser Formel ergibt den folgenden Ausdruck:
xbaxbafV ;;
xsa 21
xsb 22
xxsxsxfV )2)(2( 21
xxxxfV )26)(216(
xxxxfV 96444 23
Fieting, Olaf 4 von 19 23.11.2013, 15:05
2.3 Bestimmen der lokalen Extrema Bestimmen der Extremwertstelle Zum Ermitteln der Extremwertstelle (x-Wert der Funktion, an der die Funktion ihren Extremwert hat) wird die erste Ableitung der Funktion f(V) gebildet. Anschließend muss die erste Ableitung der Funktion zu Null gesetzt werden und man erhält folgende Gleichung: Da es sich hier um eine quadratische Gleichung handelt, kann diese mit Hilfe des Vieta'schen Wurzelsatzes gelöst werden. Dabei muss beachtet werden, dass zwei Lösungen ermittelt werden können. Daraus ergibt sich folgender Term: Nach Auflösung der entsprechenden Formel erhält man zwei Ergebnisse.
xxxxf 96444 23
968812 2 xxxf I
0968812 2 xx
qpp
x 22,1 )
2(
2
8)6
22(
6
22 2
2,1 x
083
222 xx
36
196
6
222,1 x
6
14
6
222,1 x
66
361 x
333333,16
82 x
Fieting, Olaf 5 von 19 23.11.2013, 15:05
Auswertung der Ergebnisse Auf Grund der vorher festgestellten Nebenbedingungen 0 < x < 3 ist zu ersehen, dass der Wert für x1 = 6 cm keine Lösung der Aufgabe sein kann. Es kommt also nur die Lösung x2 = 1,33333 cm als gültiges Ergebnis in Frage. Bestimmen der Art des Extremwertes Hier muss festgestellt werden, ob es sich bei dem ermittelten Ergebnis wirklich um ein Extremum (Maximum, Minimum) handelt. Dabei ist es notwendig die zweite Ableitung der aufgestellten Funktion, welcher die erste Ableitung zugrunde liegt, zu ermitteln. Für x ist der möglich ermittelte Wert aus der Lösung der ersten Ableitung einzusetzen. In diesem Falle also 1,33333 oder auch 4/3. Sollte das Ergebnis kleiner 0 sein, handelt es sich um ein Maximum, wie es im gegeben Fall erstrebt wurde. Sollte das Ergebnis positiv sein, handelt es sich um ein Minimum.
2.4 Analysieren des Verhaltens der Funktion an den Randstellen Weiterhin muss das Verhalten an den Randstellen der Funktion betrachtet werden. Im Falle des betrachteten Beispiels sind es die Werte, die die Nebenbedingungen beschreiben, also x=0 und X=3. Beim Einsetzen dieser Werte in die Ausgangsfunktion erhält man jeweils ein Volumen von 0, was wiederum ein Minimum bedeuten würde, also nicht das erstrebt Ergebnis.
2.5 Formulieren der/des Ergebnisse/s Wie in den vorangegangenen Betrachtungen ermittelt wurde ist ein maximales Volumen zu erreichen, wenn die Seitenlänge der herauszuschneidenden Quadrate 4/3 cm betragen. Das maximale Volumen, das erreicht wird beträgt: Die Länge der Seiten a und b betragen dementsprechend 13 1/3 cm bzw. 3 1/3 cm.
968812 2 xxxfI
8824 xxfII
56883
424)
3
4( fxf
II
3
496
3
444
3
44
3
4 23 )()()(fV
.....26,5927
1600)
3
4( fV
Fieting, Olaf 6 von 19 23.11.2013, 15:05
3 Lösen von Extremwertaufgaben mit EXCEL Wenn die Tabellenkalkulation EXCEL eingesetzt wird, hat der Nutzer ein mächtiges Werkzeug zur Verfügung. Dabei ist natürlich davon auszugehen, dass er die mathematisch dargelegten Schritte natürlich ebenfalls vorzunehmen hat. Das betrifft das Finden des mathematischen Ansatzes, das Definieren der mathematischen Funktion und auch der entsprechenden Nebenbedingungen/Einschränkungen. Eine einfache, jedoch sehr arbeitsaufwendige, Lösung besteht z.B. darin, dass eine lange Liste (eventuell über 2000 - 3000 Zeilen) erstellt wird, die den gesuchten Wert x in 1/1000 Schritten berechnet. Danach könnte man mit Hilfe der Funktionen MAX oder MIN von allen ermittelten Werten den größten bzw. kleinsten Betrag zu bestimmen. Wesentlich eleganter und vor allem auch zeitsparender ist der Einsatz des in EXCEL integrierten Solvers.
3.1 Erstellen des entsprechenden Tabellenblattes Für das Erstellen eines funktionierenden Tabellenblattes sind natürlich die oben genannten Schritte vorzunehmen. Dabei kann man an einigen Stellen auf komplexe Formeln verzichten, da man über EXCEL auf die einzelnen Zwischenlösungen ganz einfach zurückgreifen kann. Zum Lösen der genannten Aufgabe soll das folgende Tabellenblatt genutzt werden. Dabei wird der notwendige Wert in der Zelle H8 bestimmt. Vor Einsatz des Solvers soll der Inhalt dieser Zell leer bleiben (also auf „Null“ stehen).
3.2 Lösen der Extremwertaufgabe mit Hilfe des in EXCEL integrierten Solvers Bei der Lösung der o.g. Extremwertaufgabe ist in folgender Reihenfolge vorzugehen: (1) Aufrufen des Solvers
(2) Eintragen der Zielzelle (H10)
(3) Einstellen der Art des Zielwertes (hier Maximum)
(4) Eintragen der veränderbaren Zelle (H8)
Fieting, Olaf 7 von 19 23.11.2013, 15:05
(5) Festlegen der Nebenbedingungen (H8>=0, H8<=3), die über die Schaltfläche "Hinzufügen" eingegeben werden können.
(6) Nach dem Festlegen aller Parameter (siehe unten stehende Abbildung) wird mit
Anklicken der Schaltfläche "Lösen" die Berechnung ausgelöst. Sollte durch den Server eine Lösung gefunden werden, meldet er sich mit unten
stehendem Dialogfeld. Wenn die Lösung angenommen werden soll, muss die Option „Lösung verwenden“ mit „OK“ bestätigt werden.
Fieting, Olaf 8 von 19 23.11.2013, 15:05
Die Lösungen werden dann in die veränderbare Zelle eingetragen. Wie aus dem Tabellenblatt zu ersehen ist, liegen auch bei diesem Verfahren die gleichen Lösungen vor.
4 Praktische Anwendungsbeispiele
4.1 Berechnung Materialverbrauch für eine Konservendose Welche Maße muss eine zylindrische Konservendose besitzen, damit bei gefordertem Inhalt von einem Liter (1000 cm³) zu ihrer Herstellung möglichst wenig Blech verbraucht wird?
h
r
Fieting, Olaf 9 von 19 23.11.2013, 15:05
4.1.1 Mathematische Lösung Gesuchte Lösungen
Höhe der Dose (h)
Radius (r) bzw. Durchmesser (d)
Erstellen der Ausgangsformel mit bzw. Nach Einsetzen von h in die Formel für die Oberfläche ergibt sich oder Bestimmen von Radius und Höhe der Dose Nach Umstellen der Gleichung ergibt sich für den Radius
hrrhrfO 22; 2
hrV 2
2r
Vh
2
2 22r
VrrrfO
12 22 rVrrfO
12 22 rVrrf
224 rVrrfI
024 2 rVr
3
2
Vr
3
2
1000
r
425,r
Fieting, Olaf 10 von 19 23.11.2013, 15:05
und für die Höhe der Dose Dazu ist aber eine kleine Anmerkung notwendig. Die ideal Dose sollte einen quadratischen Achsenschnitt haben. Das ist aber in der Praxis oft nicht der Fall. Das kann z. B. daran liegen, dass eine Cola-Dose auch handlich sein soll und damit vom Optimum deutlich abweicht. Trotzdem ist der Materialverbrauch in der Regel nur 2 % höher. Bestimmen der Art des Extremwertes Nach Lösen der Gleichung ergibt sich Da dieser Wert größer Null ist, handelt es sich um ein Minimum.
4.1.2 Lösen der Aufgabe mit dem Solver Erstellen des entsprechenden Arbeitsblattes
2r
Vh
2425
1000
,
h
8410,h
3
3
2
44
V
Vrf
II
12II
rf
Fieting, Olaf 11 von 19 23.11.2013, 15:05
Einstellen der Lösungsbedingungen für den Solver
Ergebnis der Lösung
4.2 Berechnung Sportanlage Es soll eine Leichtathletikanlage gebaut werden. Diese muss eine Tartanbahn mit einer Länge von 400 m besitzen. Wie müssen die Maße der Bahn beschaffen sein, damit eine maximale Spielfläche Amax entsteht.
l
r
Amax
Spielfläche
Fieting, Olaf 12 von 19 23.11.2013, 15:05
4.2.1 Mathematische Lösung Gesuchte Lösungen
Länge Mittelstück (l)
Radius (r) der Halbkreise
Erstellen der Ausgangsformel Die Länge der Bahn wird mit wie folgt berechnet. Laut Forderung soll die Tartanbahn 400 m lang sein. Nach l umgestellt ergibt sich: Die Formel für die Spielfläche lautet: Bestimmen des Radius und der Länge der Spielfläche
Bestimmen der Art des Extremwertes Da das Ergebnis der zweiten Ableitung negativ ist, handelt es sich um ein Maximum.
lrL 22
lrlrfA 2;max
40022 lr
rl 200
)200(2 rrrf
)2400 2rrrf
rrfI
4400
04400 r
83931100
4
400,
r
830931200 , l
100l
4II
rf
Fieting, Olaf 13 von 19 23.11.2013, 15:05
4.2.2 Lösen der Aufgabe mit dem Solver Erstellen des entsprechenden Arbeitsblattes
Einstellen der Lösungsbedingungen für den Solver Ergebnis der Lösung
4.3 Berechnung Bewässerungskanal
Für eine Bewässerungsanlage soll ein trapezförmiger Kanal (Querschnitt) bebaut werden. Es stehen Platten mit den Maßen 4 m x 4 m zur Verfügung. Die Platten sind so anzuordnen, dass möglichst viel Wasser (maximaler Querschnitt) transportiert werden kann. Wie breit muss die obere Öffnung des Kanals sein, damit die gestellte Aufgabe gelöst werden kann? Wie tief wird der Kanal und in welchem Böschungswinkel müssen die Seitenplatten verlegt?
Fieting, Olaf 14 von 19 23.11.2013, 15:05
4.3.1 Mathematische Lösung
Gesuchte Lösungen
Obere Öffnung des Kanales (b)
Tiefe des Kanales (h)
Böschungswinkel (α)
Mathematischer Ansatz Nebenbedingungen Einschränkungen für x 0<= x <= s/2 Aufstellen der mathematischen Formel
s
s
s h
x x
b
hbs
A
2
xsb 2
22 xsh
hbs
A
2
22
2
)2(xs
xssxfA
Fieting, Olaf 15 von 19 23.11.2013, 15:05
Für s = 4 gilt: Bestimmen der lokalen Extrema Es wird zuerst die erste Ableitung mit Hilfe der Produktregel gebildet. Nach Lösen der Funktion dritten Grades erhält man folgende Lösungen: und Auf Grund der oben genannten Einschränkungen kommt für die Lösung nur In Frage. Überprüfen der Art des Extremwertes Mit gilt Da das Ergebnis negativ ist, handelt es sich hier um ein Maximum.
22)( xsxsxf
)()( 222 xsxsxf
)16()4( 22 xxxf
)2()24()16()4(2 22 xxxxxfI
128244 23 xxxfI
0128244 23 xx
0326 23 xx
421 ,x
23 x
23 x
128244 23 xxxfI
xxxfII
4812 2
23 x
144II
xf
Fieting, Olaf 16 von 19 23.11.2013, 15:05
4.3.2 Lösen der Aufgabe mit dem Solver Erstellen des entsprechenden Arbeitsblattes Einstellen der Lösungsbedingungen für den Solver Ergebnis der Lösung
Fieting, Olaf 17 von 19 23.11.2013, 15:05
4.4 Berechnung Kosten Wasserleitung Von einem Wasserturm W soll zu den Hauptgebäuden H eine Wasserleitung gebaut werden. Durch eine Nebenleitung soll außerdem ein abseits der Hauptleitung gelegenes Gebäude S mit Wasser versorgt werden. Dieses hat von der Hauptleitung einen Abstand von 1 km. Der Fußpunkt des von S auf die Hauptleitung gefällten Lotes liegt in einem Abstand von 2 km von den Hauptgebäuden entfernt. Die Entfernung zwischen Hauptgebäuden und Wasserturm beträgt 6 km. Die Kosten für einen Meter Wasserleitung werden wie folgt veranlagt: Hauptleitung (HL): 30 Einheiten
Entlastete Hauptleitung (EHL): 22 Einheiten
Nebenleitung (NL): 12 Einheiten
Alle Leitungen werden geradlinig verlegt. In welcher Entfernung vom Wasserturm muss die Nebenleitung von der Hauptleitung abgezweigt werden, damit die Baukosten möglichst niedrig werden?
4.4.1 Mathematische Lösung Gesuchte Lösungen
Entfernung Abzweigpunkt vom Wasserturm (x)
Kosten sollen minimal sein
Erstellen der Ausgangsformel
HL
HL
EHL
1 km
6 km
4 km
x km 2 km (4-x) km H W
S
EHLNLHL KKKK
24112242230 xxxxfK
Fieting, Olaf 18 von 19 23.11.2013, 15:05
Bestimmen der Entfernung vom Wasserturm (x)
Nach Umstellen der Gleichung ergibt sich folgende Formel: Da es sich hier um eine quadratische Gleichung handelt, kann diese mit Hilfe des Vieta'schen Wurzelsatzes gelöst werden. Dabei muss beachtet werden, dass zwei Lösungen ermittelt werden können. Daraus ergibt sich folgender Term: Die erste Lösung entfällt (siehe Zeichnung).
2817121328 xxxxfK
2
12817121328 xxxxf
828172
1128 2
12
xxxf I
2817
8268
xx
xxf
I
2817
48128
xx
xxf
I
0817
48128
2
xx
x
021582 ,xx
qpp
x 22,1 )
2(
2
21516421
,,
x
80421
,,
x
8944270421
,,
x
89442741
,x
kmx 1131055732
,,
Fieting, Olaf 19 von 19 23.11.2013, 15:05
Bestimmen der Art des Extremwertes Mit dem Ergebnis für x=3,11 ergibt sich für die die zweite Ableitung ein Wert von ca. 5,00. Da dieser Wert positiv ist, handelt es sich um ein Minimum.
4.4.2 Lösen der Aufgabe mit dem Solver
Erstellen des entsprechenden Arbeitsblattes Einstellen der Lösungsbedingungen für den Solver Ergebnis der Lösung
32817
12
xx
xfII