of 19/19
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

Lösen von Extremwertaufgaben mit EXCEL - oeftg.deoeftg.de/header_index/Mathematik_mit_EXCEL/Extremwertaufgaben/... · Fieting, Olaf 1 von 19 23.11.2013, 15:05 Lösen von Extremwertaufgaben

  • View
    218

  • Download
    0

Embed Size (px)

Text of Lösen von Extremwertaufgaben mit EXCEL -...

  • Fieting, Olaf 1 von 19 23.11.2013, 15:05

    Lsen von Extremwertaufgaben mit EXCEL In der Wissenschaft, aber auch in der Wirtschaft, spielt das Lsen von Extremwertaufgaben eine groe Rolle. Immer wieder wird die Frage danach gestellt, was unternommen werden muss, damit die minimalsten Kosten, der grte Gewinn, die grte 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 Lsung dieser Probleme genutzt werden kann. Jeder, der beide Verfahren parallel zueinander erprobt hat, wird dann feststellen knnen, welch eine Reduzierung des Arbeitsaufwandes beim Einsatz von EXCEL erreicht werden kann. Natrlich 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 mgliche Aufgabenstellungen aufgefhrt werden. (Die mathematischen als auch die Lsungen in EXCEL knnen ber den Autor bezogen werden).

    Inhaltsverzeichnis 1 Aufgabenstellung ..................................................................................................................... 2

    2 Mathematische Lsung............................................................................................................ 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 Lsen von Extremwertaufgaben mit EXCEL ........................................................................... 6

    3.1 Erstellen des entsprechenden Tabellenblattes ....................................................................... 6

    3.2 Lsen der Extremwertaufgabe mit Hilfe des in EXCEL integrierten Solvers ........................... 6

    4 Praktische Anwendungsbeispiele ............................................................................................ 8

    4.1 Berechnung Materialverbrauch fr eine Konservendose ........................................................ 8

    4.1.1 Mathematische Lsung............................................................................................................ 9

    4.1.2 Lsen der Aufgabe mit dem Solver ....................................................................................... 10

    4.2 Berechnung Sportanlage ....................................................................................................... 11

    4.2.1 Mathematische Lsung.......................................................................................................... 12

    4.2.2 Lsen der Aufgabe mit dem Solver ....................................................................................... 13

    4.3 Berechnung Bewsserungskanal .......................................................................................... 13

    4.3.1 Mathematische Lsung.......................................................................................................... 14

    4.3.2 Lsen der Aufgabe mit dem Solver ....................................................................................... 16

    4.4 Berechnung Kosten Wasserleitung ....................................................................................... 17

    4.2.1 Mathematische Lsung.......................................................................................................... 17

    4.2.2 Lsen der Aufgabe mit dem Solver ....................................................................................... 19

  • Fieting, Olaf 2 von 19 23.11.2013, 15:05

    1 Aufgabenstellung Aus einem rechteckigen Blech mit den Seitenlngen S1 = 16 cm und S2 = 6 cm soll ein Gef mit maximalem Fassungsvermgen (Volumen) hergestellt werden, indem aus jeder Ecke ein Quadrat herausgeschnitten wird, der Rest zu einem offenen Quader zusammengebogen und verschweit wird. Wie gro mssen die Seiten des herauszuschneidenden Quadrates sein, damit die Aufgabe realisiert werden kann? Wie gro sind die Seiten a und b des Gefes? Wie gro ist das Volumen des Behlters?

    2 Mathematische Lsung Zur Lsung 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 mglichst groe rechteckige Flche eingezunt 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 Gre zu definieren, die das entsprechende Extremum darstellt, von welcher Gre es abhngt und von welcher Art dieses Extremum ist. Weiterhin sind Nebenbedingungen und Einschrnkungen sowie weitere, auf der Grundlage der zu suchenden abhngigen Gre, zu bestimmende Werte mathematisch festzulegen. Lsungsansatz

    1 Gesuchtes Extremum: Volumen (V)

    2 Art des Extremums: Maximum

    3 Bestimmende Gre des Extremums: Seitenlnge des Quadrates (x)

    4 Nebenbedingungen/Einschrnkungen: 0 < x

  • 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. Anschlieend muss die erste Ableitung der Funktion zu Null gesetzt werden und man erhlt folgende Gleichung: Da es sich hier um eine quadratische Gleichung handelt, kann diese mit Hilfe des Vieta'schen Wurzelsatzes gelst werden. Dabei muss beachtet werden, dass zwei Lsungen ermittelt werden knnen. Daraus ergibt sich folgender Term: Nach Auflsung der entsprechenden Formel erhlt man zwei Ergebnisse.

    xxxxf 96444 23

    968812 2 xxxf I

    0968812 2 xx

    qpp

    x 22,1 )2

    (2

    8)6

    22(

    6

    22 22,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 fr x1 = 6 cm keine Lsung der Aufgabe sein kann. Es kommt also nur die Lsung x2 = 1,33333 cm als gltiges 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. Fr x ist der mglich ermittelte Wert aus der Lsung 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 erhlt man jeweils ein Volumen von 0, was wiederum ein Minimum bedeuten wrde, 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 Seitenlnge der herauszuschneidenden Quadrate 4/3 cm betragen. Das maximale Volumen, das erreicht wird betrgt: Die Lnge der Seiten a und b betragen dementsprechend 13 1/3 cm bzw. 3 1/3 cm.

    968812 2 xxxf I

    8824 xxf II

    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 Lsen von Extremwertaufgaben mit EXCEL Wenn die Tabellenkalkulation EXCEL eingesetzt wird, hat der Nutzer ein mchtiges Werkzeug zur Verfgung. Dabei ist natrlich davon auszugehen, dass er die mathematisch dargelegten Schritte natrlich ebenfalls vorzunehmen hat. Das betrifft das Finden des mathematischen Ansatzes, das Definieren der mathematischen Funktion und auch der entsprechenden Nebenbedingungen/Einschrnkungen. Eine einfache, jedoch sehr arbeitsaufwendige, Lsung 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 knnte man mit Hilfe der Funktionen MAX oder MIN von allen ermittelten Werten den grten 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 Fr das Erstellen eines funktionierenden Tabellenblattes sind natrlich die oben genannten Schritte vorzunehmen. Dabei kann man an einigen Stellen auf komplexe Formeln verzichten, da man ber EXCEL auf die einzelnen Zwischenlsungen ganz einfach zurckgreifen kann. Zum Lsen 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 Lsen der Extremwertaufgabe mit Hilfe des in EXCEL integrierten Solvers Bei der Lsung 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 vernderbaren Zelle (H8)

  • Fieting, Olaf 7 von 19 23.11.2013, 15:05

    (5) Festlegen der Nebenbedingungen (H8>=0, H8

  • Fieting, Olaf 8 von 19 23.11.2013, 15:05

    Die Lsungen werden dann in die vernderbare Zelle eingetragen. Wie aus dem Tabellenblatt zu ersehen ist, liegen auch bei diesem Verfahren die gleichen Lsungen vor.

    4 Praktische Anwendungsbeispiele

    4.1 Berechnung Materialverbrauch fr eine Konservendose Welche Mae muss eine zylindrische Konservendose besitzen, damit bei gefordertem Inhalt von einem Liter (1000 cm) zu ihrer Herstellung mglichst wenig Blech verbraucht wird?

    h

    r

  • Fieting, Olaf 9 von 19 23.11.2013, 15:05

    4.1.1 Mathematische Lsung Gesuchte Lsungen

    Hhe der Dose (h)

    Radius (r) bzw. Durchmesser (d)

    Erstellen der Ausgangsformel mit bzw. Nach Einsetzen von h in die Formel fr die Oberflche ergibt sich oder Bestimmen von Radius und Hhe der Dose Nach Umstellen der Gleichung ergibt sich fr den Radius

    hrrhrfO 22; 2

    hrV 2

    2r

    Vh

    2

    2 22r

    VrrrfO

    12 22 rVrrfO

    12 22 rVrrf

    224 rVrrf I

    024 2 rVr

    3

    2

    Vr

    3

    2

    1000

    r

    425,r

  • Fieting, Olaf 10 von 19 23.11.2013, 15:05

    und fr die Hhe 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 % hher. Bestimmen der Art des Extremwertes Nach Lsen der Gleichung ergibt sich Da dieser Wert grer Null ist, handelt es sich um ein Minimum.

    4.1.2 Lsen der Aufgabe mit dem Solver Erstellen des entsprechenden Arbeitsblattes

    2r

    Vh

    2425

    1000

    ,

    h

    8410,h

    3

    3

    2

    44

    V

    Vrf

    II

    12IIrf

  • Fieting, Olaf 11 von 19 23.11.2013, 15:05

    Einstellen der Lsungsbedingungen fr den Solver

    Ergebnis der Lsung

    4.2 Berechnung Sportanlage Es soll eine Leichtathletikanlage gebaut werden. Diese muss eine Tartanbahn mit einer Lnge von 400 m besitzen. Wie mssen die Mae der Bahn beschaffen sein, damit eine maximale Spielflche Amax entsteht.

    l

    r

    Amax

    Spielflche

  • Fieting, Olaf 12 von 19 23.11.2013, 15:05

    4.2.1 Mathematische Lsung Gesuchte Lsungen

    Lnge Mittelstck (l)

    Radius (r) der Halbkreise

    Erstellen der Ausgangsformel Die Lnge der Bahn wird mit wie folgt berechnet. Laut Forderung soll die Tartanbahn 400 m lang sein. Nach l umgestellt ergibt sich: Die Formel fr die Spielflche lautet: Bestimmen des Radius und der Lnge der Spielflche

    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

    rrf I 4400

    04400 r

    83931100

    4

    400,

    r

    830931200 , l

    100l

    4IIrf

  • Fieting, Olaf 13 von 19 23.11.2013, 15:05

    4.2.2 Lsen der Aufgabe mit dem Solver Erstellen des entsprechenden Arbeitsblattes

    Einstellen der Lsungsbedingungen fr den Solver Ergebnis der Lsung

    4.3 Berechnung Bewsserungskanal

    Fr eine Bewsserungsanlage soll ein trapezfrmiger Kanal (Querschnitt) bebaut werden. Es stehen Platten mit den Maen 4 m x 4 m zur Verfgung. Die Platten sind so anzuordnen, dass mglichst viel Wasser (maximaler Querschnitt) transportiert werden kann. Wie breit muss die obere ffnung des Kanals sein, damit die gestellte Aufgabe gelst werden kann? Wie tief wird der Kanal und in welchem Bschungswinkel mssen die Seitenplatten verlegt?

  • Fieting, Olaf 14 von 19 23.11.2013, 15:05

    4.3.1 Mathematische Lsung

    Gesuchte Lsungen

    Obere ffnung des Kanales (b)

    Tiefe des Kanales (h)

    Bschungswinkel ()

    Mathematischer Ansatz Nebenbedingungen Einschrnkungen fr x 0

  • Fieting, Olaf 15 von 19 23.11.2013, 15:05

    Fr s = 4 gilt: Bestimmen der lokalen Extrema Es wird zuerst die erste Ableitung mit Hilfe der Produktregel gebildet. Nach Lsen der Funktion dritten Grades erhlt man folgende Lsungen: und Auf Grund der oben genannten Einschrnkungen kommt fr die Lsung nur In Frage. berprfen 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 xxxxxf I

    128244 23 xxxf I

    0128244 23 xx

    0326 23 xx

    421 ,x

    23 x

    23 x

    128244 23 xxxf I

    xxxf II 4812 2

    23 x

    144IIxf

  • Fieting, Olaf 16 von 19 23.11.2013, 15:05

    4.3.2 Lsen der Aufgabe mit dem Solver Erstellen des entsprechenden Arbeitsblattes Einstellen der Lsungsbedingungen fr den Solver Ergebnis der Lsung

  • Fieting, Olaf 17 von 19 23.11.2013, 15:05

    4.4 Berechnung Kosten Wasserleitung Von einem Wasserturm W soll zu den Hauptgebuden H eine Wasserleitung gebaut werden. Durch eine Nebenleitung soll auerdem ein abseits der Hauptleitung gelegenes Gebude S mit Wasser versorgt werden. Dieses hat von der Hauptleitung einen Abstand von 1 km. Der Fupunkt des von S auf die Hauptleitung gefllten Lotes liegt in einem Abstand von 2 km von den Hauptgebuden entfernt. Die Entfernung zwischen Hauptgebuden und Wasserturm betrgt 6 km. Die Kosten fr 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 mglichst niedrig werden?

    4.4.1 Mathematische Lsung Gesuchte Lsungen

    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 gelst werden. Dabei muss beachtet werden, dass zwei Lsungen ermittelt werden knnen. Daraus ergibt sich folgender Term: Die erste Lsung entfllt (siehe Zeichnung).

    2817121328 xxxxfK

    21

    2817121328 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 fr x=3,11 ergibt sich fr die die zweite Ableitung ein Wert von ca. 5,00. Da dieser Wert positiv ist, handelt es sich um ein Minimum.

    4.4.2 Lsen der Aufgabe mit dem Solver

    Erstellen des entsprechenden Arbeitsblattes Einstellen der Lsungsbedingungen fr den Solver Ergebnis der Lsung

    32817

    12

    xx

    xfII