Wertetabellen und Regression mit Excel

IV. Wertetabellen und Funktionsanalyse

1. händischer Weg

In Excel händisch eine Wertetabelle zu erstellen ist einfach möglich und jeder Schüler sollte das Vorgehen beherrschen.

- möchte man später ein Diagramm erzeugen, so ist es sinnvoll A1 leer zu lassen und in B1 die Funktionsgleichung zu notieren.

- In Spalte A (ab A2) trägt man üblicherweise die x-Werte ein, in Spalte B werden die zugehörigen y-Werte berechnet. Hier ist die erste Entscheidung, mit welcher Schrittweite die Tabelle gewünscht ist.

Beispiel:

Funktionsgleichung f(x) =x^3-2x^2, Schrittweite 0,5, untersucht wird die Funktion im Bereich -3 und 4.

Eintrag in A2: -3

Eintrag in B2: =A2^3 -2*A2^2

Das x wird dabei einfach durch den Zellbezug auf den x-Wert ersetzt.

- Die x-Werte müssen wir bis A16 ausfüllen. Mit F5 A16 anspringen, STRG+UMSCHALT+PfeilAuf und über das Menü START- Füllbereich - Reihe wählen, Inkrement auf 0,5 stellen und mit EINGABE abschließen.

- Nun wechselt man in B16 und markiert mit STRG+UMSCHALT+PfeilAuf den Bereich, in welchem man die Werte erhalten möchte. Mit STRG+U kann man es sich dann einfach machen und die Formel mit Vererbung nach unten kopieren.

Fertig!

Nun hat man viele Werte und kann sie Stück für Stück durcharbeiten, um sich ein Bild des Kurvenverlaufs zu erzeugen.

2. Wertetabelle - Makrovorlage

Für sehende Schüler geht das ganze aber viel schneller, sie geben am Taschenrechner Startwert, Endwert, Schrittweite und Funktionsgleichung ein. Daher habe ich das mit einem Makro nachgebaut.

Sicherheitshinweis: Bitte wirklich nur Makros aktivieren, wenn sie bekannt sind!

Wichtig zu beachten:

- Beim Eingeben der Funktionsgleichung muss zwischen Variable und Koeffizient immer ein * gesetzt werden (Beispiel: nicht 2x^2, sondern 2*x^2)

- als Variable ist immer ein x zu wählen, auch wenn in der Ursprungsfunktion eine andere Variable verwendet wird.

Da abhängig vom Bundesland die Extremwerte direkt für Schüler auslesbar sein dürfen oder eben nicht, gibt es zwei Versionen.

2.1 Wertetabelle_WTR.xlsm

Hier werden die gleichen Werte wie beim WTR abgefragt und daraufhin eine Wertetabelle automatisch erstellt.

2.2 Wertetabelle_GTR.xlsm

Zusätzlich zu der beschriebenen Funktionsweise wird von Excel ermittelt, wo sich Extrempunkte befinden (Minima, Maxima) und mit einem Eintrag in Spalte C gekennzeichnet.

Die Enden werden ebenso als "Min" oder "Max" bezeichnet und geben so Hinweis über den Austrittsverlauf.

Navigiert man mit STRG+PfeilAb durch Spalte C, so kann man die Werte zumindest nährerungsweise ablesen.

Durch Markierung des beschriebenen Bereichs in Spalte A und B kann man, wie in der Diagrammerstellung beschrieben, schnell eine Vorlage für einen Ausdruck erhalten.

Übungen:

a) Erstellen Sie eine Wertetabelle für die Funktion

f(x) =x^3+3x^2 im Bereich -3,5 bis 1,5. Überlegen Sie sich eine angemessene Schrittweite.

b) Erstellen Sie in der GTR Version eine Wertetabelle für die Funktion

f(z)=z^4 -z^2. In welchem Bereich lohnt sich ein näheres Betrachten des Verlaufs?

c) Experimentieren Sie mir selbst gewählten Funktionen.

3. Regression

Aus Wertetabellen lassen sich mit dem WTR oder auch dem GTR per Regression Funktionsgleichungen ermitteln.

Auch mit Excel ist das möglich - allerdings nicht so einfach. Daher liegt auch hier eine fertige Worksheet vor.

In Spalte A werden die X-Werte eingegeben, in Spalte B die Y-Werte.

Dann kann man in Spalte D schauen, wie der Determinationskoeffizient R^2 sich verhält (je näher an 1 desto besser, besonders geeignete Ergebnisse werden hervorgehoben)

In Spalte E sind die Arten der Regression aufgeführt, in F der allgemeine Funktionsterm und in G die ermittelte Funktionsgleichung.

Wichtig zu beachten: möchte man innerhalb von Excel mit der Funktion weiterarbeiten, so muss man nach dem Kopieren im ausführlichen Einfügedialog im Menü Start "Werte" anwählen, da sonst die genutzten Zellbezüge kopiert werden.

Dann ein = vor den Term setzen und das x durch eine Zahl oder einen Zellbezug ersetzen und schon kann man weitere Werte näherungsweise berechnen.

Übungen

Nutzen Sie die Wertetabellen, um die Regression zu prüfen.

a) Finden Sie einen passenden Funktionsterm (lineare Funktion 1.Grades) für folgende Werte:

Zeit t in Jahren	Leistung P in MW
0	24322
1	31181
2	39295
3	47693

b) Finden Sie eine passende Exponentialfunktion (ohne e) für folgende Werte und berechnen Sie im Anschluss die zu erwartende Leistung für 9 Jahre.

Zeit t in Jahren	Leistung P in MW
0	24322
1	31181
2	39295
3	47693
4	59024
5	74122
6	93930
7	120903
8	159213

c) Prüfen Sie, ob näherungsweise ein exponentielles Wachstum vorliegt.

Zeit t nach erstmaligem Auftr. (in h)	Anzahl z der infizierten Computer
2	7250
4	11700
8	30500
12	79200
24	1395000

d) Die Tabelle in Fig. 1 zeigt die Bevölkerungsentwicklung von Sao Paulo in Brasilien. Führen Sie jeweils mit den Daten für die Zeiträume von 1900-1980 und von 1980-2000 eine passende Regression durch. Welche liefert für 2010 den besseren Schätzwert? Welche Schlüsse ziehen Sie daraus?

Jahr	Bevölkerung(in 1000)
1900	240
1910	336
1920	579
1930	916
1940	1326
1950	2198
1960	3667
1970	5925
1980	8493
1990	9541
2000	10434
2010	11254