StatAnaly15

Statistische Analysen mit Excel
Statistische Nutzung an Beispielen
Mathematisches Seminar am 05.Juni 2002 9.15 Uhr, Raum 30
Vortragender: Robert Vogel
1
Inhalt des Vortrages:
……………………………………2
ZWEI-STICHPROBEN F-TEST
……………………………………3
STABW
……………………………………4
STABWA
……………………………………6
QUANTIL
……………………………………8
ZUFALLSZAHL
..…………….……………………9
SCHÄTZER
..…………………………………10
TREND
..…………………………………11
KONFIDENZ
..…………………………………14
RKP
..…………………………………16
RGP
..…………………………………20
Anhang
..…………………………………27
2
Zwei-Stichproben F-TEST
Gibt die Teststatistik eines F-Tests zurück. Ein F-Test berechnet die einseitige
Wahrscheinlichkeit, dass sich die Varianzen von Matrix1 und Matrix2 nicht signifikant
unterscheiden. Mit dieser Funktion können Sie feststellen, ob zwei Stichproben
unterschiedliche Varianzen haben. Sind beispielsweise die Prüfungsergebnisse öffentlicher
und privater Schulen bekannt, können Sie ermitteln, ob bei diesen Schultypen ein
unterschiedlich breites Notenspektrum üblich ist. Untersucht wird der Unterschied in der
Streuung (oder Vielfalt) der Prüfungsergebnisse.
Syntax
FTEST(Matrix1;Matrix2)
Matrix1 ist die erste Matrix oder der erste Wertebereich.
Matrix2 ist die zweite Matrix oder der zweite Wertebereich.
Hinweise



Als Argumente müssen entweder Zahlen oder Namen, Matrizen oder Bezüge
angegeben werden, die Zahlen enthalten.
Enthält ein als Matrix oder Bezug angegebenes Argument Text, Wahrheitswerte oder
leere Zellen, werden diese Werte ignoriert. Zellen, die den Wert 0 enthalten, werden
dagegen berücksichtigt.
Enthält eines der Argumente Matrix1 oder Matrix2 weniger als 2 Datenpunkte oder ist
die Varianz von Matrix1 oder Matrix2 gleich 0, gibt FTEST den Fehlerwert #DIV/0!
zurück.
Beispiel
Noten
1
2
3
4
5
6
Schule A
Schule B
Daten 1
Daten 2
6
20
7
28
9
31
15
38
21
40
Formel
Beschreibung (Ergebnis)
=FTEST(A2:A6;B2:B6)
F-Test für die oben genannten
Datenmengen (0,648318)
3
STABW
Schätzt die Standardabweichung ausgehend von einer Stichprobe. Die Standardabweichung
ist ein Maß dafür, wie weit die jeweiligen Werte um den Mittelwert (Durchschnitt) streuen.
Syntax
STABW(Zahl1;Zahl2;…)
Zahl1;Zahl2;... sind 1 bis 30 numerische Argumente, die einer Stichprobe aus der
Grundgesamtheit entsprechen. Anstelle der durch Semikola voneinander getrennten
Argumente können Sie eine Matrix oder einen Bezug auf eine Matrix angeben.
Hinweise



STABW geht davon aus, dass die ihr übergebenen Argumente eine Stichprobe,
gezogen aus einer Grundgesamtheit, darstellen. Entsprechen die als Argumente
übergebenen Daten dagegen einer Grundgesamtheit, sollte die zugehörige
Standardabweichung mit Hilfe der Funktion STÄBEN berechnet werden.
Die berechnete Standardabweichung ist eine erwartungsgetreue Schätzung der
Standardabweichung der Grundgesamtheit. Das bedeutet, es wird durch n-1 anstatt
durch n geteilt.
Logische Werte wie WAHR und FALSCH sowie Text werden nicht berücksichtigt.
Wenn logische Werte und Text berücksichtigt werden sollen, verwenden Sie die
Arbeitsblattfunktion STABW.
Beispiel
Angenommen, 10 Schraubenschlüssel, die auf derselben Maschine geschmiedet wurden und
zu demselben Fertigungslos gehören, werden zufällig zu einer Stichprobe zusammengestellt
und auf ihre Bruchfestigkeit hin überprüft.
4
A
Bruchfestigkeit
1
1345
2
1301
3
1368
4
1322
5
1310
6
1370
7
1318
8
1350
9
1303
10
1299
11
Formel
=STABW(A2:A11)
Beschreibung (Ergebnis)
Standardabweichung der Bruchfestigkeit (27,46391572)
5
STABWA
Schätzt die Standardabweichung ausgehend von einer Stichprobe. Die Standardabweichung
ist ein Maß für die Streuung von Werten bezüglich deren Mittelwert (dem Durchschnitt). Text
und Wahrheitswerte wie WAHR und FALSCH werden bei der Berechnung berücksichtigt.
Syntax
STABWA(Wert1;Wert2;...)
Wert1;Wert2;... sind 1 bis 30 Werte, die einer Stichprobe aus der Grundgesamtheit
entsprechen. Anstelle der durch Semikola voneinander getrennten Argumente können Sie eine
Matrix oder einen Bezug auf eine Matrix angeben.
Hinweise



STABWA geht davon aus, dass die ihr übergebenen Argumente eine Stichprobe einer
Grundgesamtheit entsprechen. Für den Fall, dass die zugehörigen Daten eine
Grundgesamtheit angeben, sollten Sie die Standardabweichung mit der Funktion
STABWNA berechnen.
Argumente, die den Wert WAHR enthalten, werden als 1 berücksichtigt; Argumente,
die Text oder den Wahrheitswert FALSCH enthalten, werden als 0 (Null)
berücksichtigt. Soll die Berechnung weder Text noch Wahrheitswerte berücksichtigen,
verwenden Sie statt dessen die Arbeitsblattfunktion STABW.
Die berechnete Standardabweichung ist eine erwartungsgetreue Schätzung der
Standardabweichung der Grundgesamtheit. Das bedeutet, es wird durch n-1 anstatt
durch n geteilt.
Beispiel
Angenommen, 10 Schraubenschlüssel, die auf derselben Maschine geschmiedet wurden und
zu demselben Fertigungslos gehören, werden zufällig zu einer Stichprobe zusammengestellt
und auf ihre Bruchfestigkeit hin überprüft.
6
A
Bruchfestigkeit
1
2
3
4
5
6
7
8
9
10
11
1345
1301
1368
1322
1310
1370
1318
1350
1303
1299
Formel
Beschreibung (Ergebnis)
=STABWA(A2:A11) Standardabweichung der Bruchfestigkeit aller Werkzeuge
(27,46391572)
7
QUANTIL
Gibt das Alpha-Quantil einer Gruppe von Daten zurück. Mit Hilfe dieser Funktion können Sie
einen Akzeptanzschwellenwert festlegen. So könnten Sie beispielsweise entscheiden, dass nur
Kandidaten untersucht werden, deren Prüfungsergebnisse oberhalb des 90 %-Quantils liegen.
Syntax
QUANTIL(Matrix;Alpha)
Matrix ist eine Matrix oder ein Datenbereich, die/der die relative Lage der Daten beschreibt.
Alpha ist ein Wert aus dem geschlossenen Intervall von 0 bis 1.
Hinweise




Enthält Matrix keine oder mehr als 8191 Datenpunkte, gibt QUANTIL den
Fehlerwert #ZAHL! zurück.
Ist Alpha kein numerischer Ausdruck, gibt QUANTIL den Fehlerwert #WERT!
zurück.
Ist Alpha < 0, oder ist Alpha > 1, gibt QUANTIL den Fehlerwert #ZAHL! zurück.
Ist Alpha kein Vielfaches von 1/(n - 1), interpoliert QUANTIL, um das AlphaQuantil zu bestimmen.
Beispiel
A
Daten
1
1
2
3
3
2
4
4
5
Formel
=QUANTIL(A2:A5;0,3)
Beschreibung (Ergebnis)
30 %-Quantil der obigen Liste (1,9)
8
ZUFALLSZAHL
Gibt eine Zufallszahl zwischen 0 und 1 zurück. Bei jeder Neuberechnung in der jeweiligen
Tabelle wird eine neue Zufallszahl gezogen.
Syntax
ZUFALLSZAHL( )
Hinweise

Mit der folgenden Anweisung können Sie eine reelle Zufallszahl erzeugen, die
zwischen a und b liegt:
ZUFALLSZAHL()*(b-a)+a

Für den Fall, dass Sie mit ZUFALLSZAHL zwar eine Zufallszahl erzeugen möchten,
aber nicht wünschen, dass sich deren Wert bei jeder Neuberechnung der
entsprechenden Zelle ändert, können Sie auch =ZUFALLSZAHL() in die
Bearbeitungszeile eingeben und anschließend F9 drücken, um die Formel in eine
Zufallszahl zu ändern.
Beispiel
1
2
3
A
B
Formel
Beschreibung (Ergebnis)
=ZUFALLSZAHL()
Eine (variierende) Zufallszahl zwischen 0 und 1
=ZUFALLSZAHL()*100 Eine (variierende) Zufallszahl, die größer gleich 0 und
kleiner 100 ist
9
SCHÄTZER
Gibt den Schätzwert für einen linearen Trend zurück. Der Vorhersagewert ist ein Y-Wert bei
einem gegebenen X-Wert. Bei den bekannten Werten handelt es sich um vorhandene X- und
Y-Werte, und der neue Wert wird, ausgehend von einer linearen Regression, vorhergesagt.
Diese Funktion ermöglicht Ihnen, zukünftige Umsätze, erforderliche Lagerbestände oder
Verbrauchertrends vorherzusagen.
Syntax
SCHÄTZER(x;Y_Werte;X_Werte)
x ist der Datenpunkt, dessen Wert Sie schätzen möchten.
Y_Werte ist eine abhängige Matrix oder ein abhängiger Datenbereich.
X_Werte ist eine unabhängige Matrix oder ein unabhängiger Datenbereich.
Hinweise



Ist x kein numerischer Ausdruck, gibt SCHÄTZER den Fehlerwert #WERT! zurück.
Sind Y_Werte und X_Werte leer oder umfassen sie unterschiedlich viele Datenpunkte,
gibt SCHÄTZER den Fehlerwert #NV zurück.
Ist die Varianz von X_Werte gleich 0, gibt SCHÄTZER den Fehlerwert #DIV/0!
zurück.
Beispiel
1
2
3
4
5
6
A
B
Y-Wert
X-Wert
6
20
7
28
9
31
15
38
21
40
Formel
Beschreibung (Ergebnis)
=SCHÄTZER(30;A2:A6;B2:B6) Gibt den Schätzwert für einen Y-Wert bei einem
gegebenen X-Wert von 30 (10,60725) zurück
10
TREND
Liefert Werte, die sich aus einem linearen Trend ergeben. Diese Funktion passt den als
Matrizen Y_Werte und X_Werte übergebenen Werten eine Gerade an (nach der Methode der
kleinsten Quadrate). Als Ergebnis liefert die Funktion die auf der Geraden liegenden y-Werte,
die zu den in Neue_X_Werte angegebenen x-Werten gehören.
Syntax
TREND(Y_Werte;X_Werte;Neue_X_Werte;Konstante)
Y_Werte sind die y-Werte, die Ihnen bereits aus der Beziehung y = mx + b bekannt sind.


Besteht die Matrix Y_Werte aus nur einer Spalte, wird jede Spalte der Matrix
X_Werte als eigenständige Variable interpretiert.
Besteht die Matrix Y_Werte aus nur einer Zeile, wird jede Zeile der Matrix X_Werte
als eigenständige Variable interpretiert.
X_Werte sind optionale x-Werte, die Ihnen möglicherweise bereits aus der Beziehung y =
mx + b bekannt sind.


Die Matrix X_Werte kann eine oder mehrere Variablengruppen umfassen. Wird nur
eine Variable verwendet, können Y_Werte und X_Werte Bereiche beliebiger Form
sein, solange sie dieselben Dimensionen haben. Werden mehrere Variablen
verwendet, muss Y_Werte ein Vektor sein (das heißt ein Bereich, der aus nur einer
Zeile oder nur einer Spalte besteht).
Fehlt die Matrix X_Werte, wird an ihrer Stelle die Matrix {1;2;3;...} angenommen,
die genauso viele Elemente wie Y_Werte enthält.
Neue_X_Werte sind die neuen x-Werte, für die die TREND-Funktion die zugehörigen yWerte liefern soll.



Analog zur Matrix X_Werte muss auch Neue_X_Werte für jede unabhängige
Variable eine eigene Spalte (oder Zeile) bereitstellen. Daher müssen die Matrizen
X_Werte und Neue_X_Werte gleich viele Spalten haben, wenn Y_Werte sich in
einer einzelnen Spalte befindet. Befindet sich Y_Werte in einer einzelnen Zeile,
müssen die Matrizen X_Werte und Neue_X_Werte gleich viele Zeilen haben.
Fehlt die Matrix Neue_X_Werte, wird angenommen, dass sie mit der Matrix
X_Werte identisch ist.
Fehlt sowohl die Matrix X_Werte als auch die Matrix Neue_X_Werte, werden diese
als die Matrix {1;2;3;...} angenommen, die genauso viele Elemente wie die Matrix
Y_Werte enthält.
Konstante ist ein Wahrheitswert, der angibt, ob die Konstante b den Wert 0 annehmen soll.


Ist Konstante mit WAHR belegt oder nicht angegeben, wird b normal berechnet.
Ist Konstante mit FALSCH belegt, wird b gleich 0 (Null) gesetzt und m so angepasst,
dass y = mx gilt.
11
Hinweise




Informationen darüber, wie Microsoft Excel eine Gerade an Daten anpasst, finden Sie
unter "RGP".
Sie können TREND zur Anpassung von Polynomen verwenden, indem Sie eine
lineare Regression bezüglich Potenzen einer Variablen durchführen. Nehmen wir
beispielsweise an, die Spalte A enthält y-Werte und die Spalte B enthält x-Werte. Sie
könnten x^2 in Spalte C, x^3 in Spalte D eingeben usw. und anschließend eine
Regression für die Spalten B bis D gegen die Spalte A durchführen.
Formeln, die als Ergebnis eine Matrix liefern, müssen als Matrixformeln eingegeben
werden.
Wird anstelle eines Arguments (z.B. X_Werte) eine Matrixkonstante eingegeben,
müssen Sie Semikola verwenden, um die zu einer Zeile gehörenden Werte
voneinander zu trennen, und Punkte, um die Zeilen selbst voneinander zu trennen.
Beispiel
Die erste Formel zeigt entsprechende Werte zu den bekannten Werten an. Die zweite Formel
liefert Schätzwerte für die nächsten Monate, wenn der lineare Trend anhält.
A
B
C
Monat
Ansch_Wert
Formel (Entsprechende Kosten)
1
$133.890
2
$135.000
1
3
$135.790
2
4
$137.300
3
5
$138.130
4
6
$139.100
5
7
$139.900
6
8
$141.120
7
9
$141.890
8
10
$143.230
9
11
$144.000
10
12
$145.290
11
Monat
Formel (Geschätzte Kosten)
12
13
=TREND(B2:B13;A2:A13;A15:A19)
13
14
=TREND(B2:B13;A2:A13)
15
16
17
12
Anmerkung Die Formel im Beispiel muss als Matrixformel eingegeben werden. Nachdem
Sie das Beispiel in ein leeres Arbeitsblatt kopiert haben, markieren Sie den Bereich C2:C13
oder B15:B19 beginnend mit der Formelzelle. Drücken Sie F2 und dann
STRG+UMSCHALT+EINGABE. Wenn die Formel nicht als Matrixformel eingegeben wird,
lauten die Einzelergebnisse 133.953,3333 und 146.171,5152.
13
KONFIDENZ
Ermöglicht die Berechnung des 1-Alpha Konfidenzintervalls für den Erwartungswert einer
Zufallsvariablen. Ein Konfidenzintervall ist ein Bereich, der sich links und rechts des
jeweiligen Stichprobenmittels erstreckt. Beispielsweise können Sie für eine Ware, die Sie per
Post zugestellt bekommen, mit einer bestimmten Sicherheit (Konfidenzniveau) vorhersagen,
wann die Ware frühestens beziehungsweise spätestens bei Ihnen eintrifft.
Syntax
KONFIDENZ(Alpha;StandardAbweichung;Umfang)
Alpha ist die Irrtumswahrscheinlichkeit bei der Berechnung des Konfidenzintervalls. Das
Konfidenzintervall ist gleich 100*(1 - Alpha)%, was bedeutet, dass ein Wert für Alpha von
0,05 einem Konfidenzniveau von 95% entspricht.
StandardAbweichung ist die als bekannt angenommene Standardabweichung der
Grundgesamtheit.
Umfang ist der Umfang der Stichprobe.
Hinweise






Ist eines der Argumente nichtnumerisch, gibt KONFIDENZ den Fehlerwert #WERT!
zurück.
Ist Alpha ≤ 0 oder Alpha ≥ 1, gibt KONFIDENZ den Fehlerwert #ZAHL! zurück.
Ist StandardAbweichung ≤ 0, gibt KONFIDENZ den Fehlerwert #ZAHL! zurück.
Ist Umfang keine ganze Zahl, wird der Dezimalanteil abgeschnitten.
Ist Umfang < 1, gibt KONFIDENZ den Fehlerwert #ZAHL! zurück.
Ist Alpha gleich 0,05, dann muss die Fläche unter der Kurve der standardisierten
Normalverteilung berechnet werden, die dem Wert (1 - Alpha) bzw. 95% entspricht.
Dieser Wert ist ± 1,96.
Beispiel
Angenommen, eine Stichprobe bei 50 Berufspendlern ergibt, dass diese im Mittel 30 Minuten
benötigen, um zu ihrem Arbeitsplatz zu gelangen, wobei die Standardabweichung der
Grundgesamtheit 2,5 beträgt. Dann gilt mit einer Wahrscheinlichkeit von mindestens 95%,
dass der Mittelwert der Grundgesamtheit im folgenden Intervall liegt:
14
A
B
Daten
Beschreibung
0,05
Irrtumswahrscheinlichkeit
1
2,5
Standardabweichung der Grundgesamtheit
2
50
Umfang der Stichprobe
3
Formel
Beschreibung (Ergebnis)
4
=KONFIDENZ(A2;A3;A4) 1-Alpha Konfidenzintervall für den Erwartungswert einer
Zufallsvariablen. Dies bedeutet eine mittlere Fahrzeit zur
Arbeit von 30 Minuten ± 0,692951 Minuten, also
zwischen 29,3 und 30,7 Minuten. (0,692951)
15
RKP
In Regressionsanalysen berechnet diese Funktion eine Exponentialkurve, die möglichst gut an
die von Ihnen bereitgestellten Daten angepasst ist, und liefert ein Wertarray, die diese Kurve
beschreibt. Da diese Funktion ein Wertarray liefert, muss die Formel als Matrixformel
eingegeben werden.
Die Gleichung der Kurve lautet
y = b*m^x oder
y = (b*(m1^x1)*(m2^x2)*_) (bei mehreren x-Werten)
wobei der abhängige y-Wert eine Funktion der unabhängigen x-Werte ist. Jeder m-Wert ist
eine Basis, zu der ein entsprechender x-Wert als Exponent gehört, und b ist eine Konstante.
Beachten Sie, dass y, x und m Vektoren sein können. Eine von RKP ausgegebene Matrix hat
die Form {mn.mn-1 ... .m1.b}.
Syntax
RKP(Y_Werte;X_Werte;Konstante;Stats)
Y_Werte sind die y-Werte, die Ihnen aus der jeweiligen Beziehung y = b*m^x bereits
bekannt sind.


Besteht die Matrix Y_Werte aus nur einer Spalte, wird jede Spalte der Matrix
X_Werte als eigenständige Variable interpretiert.
Besteht die Matrix Y_Werte aus nur einer Zeile, wird jede Zeile der Matrix X_Werte
als eigenständige Variable interpretiert.
X_Werte ist eine optionale Gruppe von x-Werten, die Ihnen aus der Beziehung y = b*m^x
eventuell bereits bekannt sind.


Die Matrix X_Werte kann eine oder mehrere Gruppen von Variablen umfassen. Wird
nur eine Variable verwendet, können Y_Werte und X_Werte Bereiche beliebiger
Form sein, solange sie dieselben Dimensionen haben. Werden mehrere Variablen
verwendet, müssen Y_Werte als Zellbereiche vorliegen, wobei sich der Bereich nur
über eine Zeile oder eine Spalte erstrecken darf (auch als Vektor bezeichnet).
Fehlt die Matrix X_Werte, wird an ihrer Stelle die Matrix {1;2;3;...} angenommen,
die genauso viele Elemente wie Y_Werte enthält.
Konstante ist ein Wahrheitswert, der angibt, ob die Konstante b den Wert 1 annehmen soll.


Ist Konstante mit WAHR belegt oder nicht angegeben, wird b normal berechnet.
Ist Konstante mit FALSCH belegt, wird b gleich 1 gesetzt, und die m-Werte werden
gemäß y = m^x berechnet.
16
Stats ist ein Wahrheitswert, der angibt, ob zusätzliche Regressionskenngrößen ausgegeben
werden sollen.


Ist Stats mit WAHR belegt, liefert RKP diese zusätzlichen Regressionskenngrößen, so
dass die zurückgegebene Matrix wie folgt aussieht:
{mn.mn-1. ... .m1.b;sen.sen-1. ... .se1.seb;r 2.sey;F.df.ssreg.ssresid}.
Ist Stats mit FALSCH belegt oder nicht angegeben, liefert RKP nur die mKoeffizienten und die Konstante b.
Weitere Informationen zu den zusätzlichen Regressionskenngrößen finden Sie unter RGP.
Hinweise


Je mehr die grafische Darstellung Ihrer Daten einer Exponentialkurve gleicht, desto
besser wird die berechnete Kurve an diese Daten angepasst sein. Wie RGP liefert RKP
eine Matrix, deren Elemente die Beziehung zwischen den Werten beschreiben, wobei
RGP aber Ihren Daten eine gerade Linie anpasst; RKP passt eine Exponentialkurve an.
Weitere Informationen finden Sie unter RGP.
Wenn Sie nur eine unabhängige x-Variable haben, können Sie die Werte für die
Steigung (m) und den y-Schnittpunkt (b) mit den folgenden Formeln direkt ermitteln:
Steigung (m):
INDEX(RKP(Y_Werte;X_Werte);1)
y-Schnittpunkt (b):
INDEX(RKP(Y_Werte;X_Werte);2)
Mit Hilfe der Gleichung y = b*m^x können Sie zukünftige y-Werte abschätzen.
Solche Abschätzungen können Sie aber auch mit der von Microsoft Excel
bereitgestellten VARIATION-Funktion vornehmen. Weitere Informationen finden Sie
unter "VARIATION".



Formeln, die als Ergebnis eine Matrix liefern, müssen als Matrixformeln eingegeben
werden.
Wird eine Matrix-Konstante (wie zum Beispiel X_Werte) als Argument eingegeben,
müssen Sie Punkte verwenden, um Werte derselben Zeile zu trennen, und Semikola,
um die Zeilen zu trennen. Die Trennzeichen können entsprechend der
Ländereinstellung (in der Systemsteuerung unter Ländereinstellungen)
unterschiedlich sein.
Sie sollten daran denken, dass mit Hilfe einer Regressionsgleichung vorhergesagte yWerte eventuell ungültig sind, wenn diese außerhalb des Bereichs der y-Werte liegen,
mit denen Sie die Gleichung ermittelt haben.
17
Beispiel 1 m-Koeffizienten und die Konstante b
A
B
Monat
Einheiten
1
11
33.100
2
12
47.300
3
13
69.000
4
14
102.000
5
15
150.000
6
16
220.000
7
Formel
Formel
=RKP(B2:B7;A2:A7; WAHR; FALSCH)
Anmerkung Die Formel im Beispiel muss als Matrixformel eingegeben werden. Nachdem
Sie das Beispiel in ein leeres Arbeitsblatt kopiert haben, markieren Sie den Bereich A9:B9 ab
der Zelle mit der Formel. Drücken Sie F2 und dann STRG+UMSCHALT+EINGABE. Wird
die Formel nicht als Matrixformel eingegeben, lautet das einzige Ergebnis 1,463275628.
Wird die Formel als Matrix eingegeben, werden die m-Koeffizienten und die Konstante b
zurückgegeben.
y = b*m1^x1 beziehungsweise mit den Werten, die in der Matrix stehen: y = 495,3 * 1,4633x
Sie können die Verkaufszahlen für zukünftige Monate abschätzen, indem Sie den Exponenten
x dieser Gleichung durch die Nummer des gewünschten Monats ersetzen. Sie können hierzu
aber auch die VARIATION-Funktion einsetzen.
Beispiel 2 Vollständige Statistiken
A
B
Monat
Einheiten
1
11
33.100
2
12
47.300
3
13
69.000
4
14
102.000
5
15
150.000
6
16
220.000
7
Formel
=RKP(B2:B7;A2:A7; WAHR; WAHR)
18
Anmerkung Die Formel im Beispiel muss als Matrixformel eingegeben werden. Nachdem
Sie das Beispiel in ein leeres Arbeitsblatt kopiert haben, markieren Sie den Bereich A9:B13
ab der Zelle mit der Formel. Drücken Sie F2 und dann STRG+UMSCHALT+EINGABE.
Wird die Formel nicht als Matrixformel eingegeben, lautet das einzige Ergebnis 1,463275628.
Wird die Formel als Matrix eingegeben, werden folgende Regressionsstatistiken
zurückgegeben. Verwenden Sie diese Taste, um die gewünschte Statistik anzuzeigen.
Mit Hilfe der weiter oben berechneten zusätzlichen Regressionskenngrößen (Zellen A10:B13
in der Matrix weiter oben) können Sie entscheiden, wie gut die Gleichung geeignet ist, um
zukünftige Werte vorherzusagen.
Wichtig Die Methoden, nach denen Sie mit RKP eine Gleichung testen, sind weitgehend
identisch zu den Methoden für RGP. Allerdings basieren die zusätzlichen statistischen
Kenngrößen, die RKP liefert, auf dem folgenden linearen Modell:
ln y = x1 ln m1 + ... + xn ln mn + ln b
Diesen Sachverhalt sollten Sie bei der Auswertung der zusätzlichen statistischen Kenngrößen
beachten, besonders die Tatsache, dass die Werte sei und seb mit ln mi und ln b verglichen
werden müssen, nicht mit mi und b. Weitere Informationen finden Sie sicherlich in einem
ausführlichen Statistikbuch.
19
RGP
Berechnet die Statistik für eine Linie unter Verwendung der Methode der kleinsten Quadrate,
zur Berechnung einer geraden Linie, die für die Daten am geeignetsten ist, und gibt eine
Matrix zurück, die die Linie beschreibt. Da diese Funktion ein Wertarray liefert, muss die
Formel als Matrixformel eingegeben werden.
Die Gleichung einer solchen Geraden lautet:
y = mx + b oder
y = m1x1 + m2x2 + ... + b (bei mehreren Bereichen mit x-Werten)
Dabei ist der abhängige y-Wert eine Funktion der unabhängigen x-Werte. Die m-Werte sind
Koeffizienten, die zu den jeweiligen x-Werten gehören, und b ist eine Konstante. Beachten
Sie, dass y, x und m Vektoren sein können. Eine von RGP ausgegebene Matrix hat die Form
{mn;mn-1;...;m1;b}. RGP kann darüber hinaus zusätzliche Regressionskenngrößen
bereitstellen.
Syntax
RGP(Y_Werte;X_Werte;Konstante;Stats)
Y_Werte sind die y-Werte, die Ihnen bereits aus der Beziehung y = mx + b bekannt sind.


Besteht die Matrix Y_Werte aus nur einer Spalte, wird jede Spalte der Matrix
X_Werte als eigenständige Variable interpretiert.
Besteht die Matrix Y_Werte aus nur einer Zeile, wird jede Zeile der Matrix X_Werte
als eigenständige Variable interpretiert.
X_Werte sind optionale x-Werte, die Ihnen möglicherweise bereits aus der Beziehung y =
mx + b bekannt sind.


Die Matrix X_Werte kann eine oder mehrere Variablengruppen umfassen. Wird nur
eine Variable verwendet, können Y_Werte und X_Werte Bereiche beliebiger Form
sein, solange sie dieselben Dimensionen haben. Werden mehrere Variablen
verwendet, muss Y_Werte ein Vektor sein (das heißt ein Bereich, der aus nur einer
Zeile oder nur einer Spalte besteht).
Fehlt die Matrix X_Werte, wird an ihrer Stelle die Matrix {1;2;3;...} angenommen,
die genauso viele Elemente wie Y_Werte enthält.
Konstante ist ein Wahrheitswert, der angibt, ob die Konstante b den Wert 0 annehmen soll.


Ist Konstante mit WAHR belegt oder nicht angegeben, wird b normal berechnet.
Ist Konstante mit FALSCH belegt, wird b gleich 0 gesetzt, und die m-Werte werden
so angepasst, dass sie zu der Beziehung y = mx passen.
Stats ist ein Wahrheitswert, der angibt, ob zusätzliche Regressionskenngrößen ausgegeben
werden sollen.
20

Ist Stats mit WAHR belegt, liefert RGP weitere Regressionskenngrößen, so dass eine
wie folgt aufgebaute Matrix zurückgegeben wird: {mn;mn-1;...;m1;b.sen;sen1;...;se1;seb.r2;sey.F;df.ssreg;ssresid}.
Ist Stats mit FALSCH belegt oder nicht angegeben, liefert RGP nur die mKoeffizienten sowie die Konstante b.

Die folgenden Regressionskenngrößen (-statistiken) können zusätzlich ermittelt werden:
Kenngröße
(Statistik)
se1,se2,...,sen
Seb
Beschreibung
Sind die Standardfehler der Koeffizienten m1;m2;...;mn.
Der Standardfehler der Konstanten b (seb = #NV, wenn Konstante mit
FALSCH belegt ist).
r2
Das Bestimmtheitsmaß. Vergleicht die berechneten mit den tatsächlichen yWerten und kann Werte von 0 bis 1 annehmen. Hat es den Wert 1, besteht
für die Stichprobe eine vollkommene Korrelation: ein berechneter y-Wert
und der entsprechende tatsächliche y-Wert unterscheiden sich nicht. Im
anderen Extremfall, wenn das Bestimmtheitsmaß 0 ist, ist die
Regressionsgerade ungeeignet, einen y-Wert vorherzusagen. Informationen
darüber, wie r2 berechnet wird, finden Sie weiter unten unter "Hinweise".
sey
Der Standardfehler des Schätzwertes y (Prognosewert).
F
Die F-Statistik (oder der berechnete F-Wert). Anhand der F-Statistik können
Sie entscheiden, ob die zwischen der abhängigen und der unabhängigen
Variablen beobachtete Beziehung zufällig ist oder nicht.
df
Der Freiheitsgrad. Mit diesem Freiheitsgrad können Sie den jeweiligen
kritischen F-Wert (Quantil F) einer entsprechenden statistischen Tabelle
entnehmen. Vergleichen Sie den jeweils auf solche Weise ermittelten
kritischen F-Wert mit der von RGP gelieferten F-Statistik, um das
Konfidenzniveau Ihres Modells zu beurteilen.
ssreg
ssresid
Die Regressions-Quadratsumme.
Die Residual-Quadratsumme (Summe der Abweichungsquadrate).
Die folgende Abbildung zeigt, in welcher Reihenfolge die zusätzlichen
Regressionskenngrößen zurückgegeben werden.
Hinweise

Jede Gerade lässt sich durch ihre Steigung und die jeweilige Anfangsordinate (yAchsenabschnitt) beschreiben:
Steigung (m):
Die Steigung einer Geraden (häufig als m bezeichnet) lässt sich aus zwei Punkten der
Geraden, (x1,y1) und (x2,y2), gemäß der Beziehung (y2 - y1)/(x2 - x1) berechnen.
21
y-Achsenabschnitt (b):
Der y-Achsenabschnitt (häufig als b bezeichnet) ist der y-Wert des Punktes, in dem
die Gerade die y-Achse schneidet.
Eine Gerade wird durch die Gleichung y = mx + b beschrieben. Sobald Sie die Werte
von m und b kennen, können Sie jeden Punkt der Geraden berechnen, indem Sie den
jeweiligen y- oder x-Wert in die Gleichung einsetzen. Sie können dafür auch die
TREND-Funktion verwenden.

Wenn nur eine unabhängige x-Variable vorliegt, können Sie die Steigung und den yAchsenabschnitt direkt mit Hilfe der folgenden Formeln ermitteln:
Steigung:
=INDEX(RGP(Bekannte_y_Werte;Bekannte_x_Werte);1)
y-Achsenabschnitt:
=INDEX(RGP(Bekannte_y_Werte;Bekannte_x_Werte);2)





Die Genauigkeit einer von RGP berechneten Geraden hängt davon ab, wie sehr Ihre
Daten streuen. Je linearer die Daten sind, desto genauer ist das von RGP ermittelte
Modell. RGP verwendet die Methode der kleinsten Quadrate, um die für die
jeweiligen Daten beste Anpassung zu ermitteln. Wenn nur eine unabhängige xVariable vorliegt, werden m und b entsprechend der folgenden Formeln berechnet:
Die Regressionsfunktionen RGP (lineare Regression) und RKP (exponentielle
Regression) können die Koeffizienten der bezüglich der von Ihnen bereitgestellten
Daten optimal angepassten Geraden bzw. Exponentialkurve berechnen. Trotzdem ist
es nach wie vor Ihre Aufgabe, zu entscheiden, welches der beiden Ergebnisse besser
zu Ihren Daten passt. Bei einer Geraden können Sie TREND(Y_Werte;X_Werte),
bei einer Exponentialkurve können Sie VARIATION(Y_Werte, X_Werte)
berechnen. Werden diese Funktionen ohne das Argument Neue_x_Werte
verwendet, liefern sie eine Matrix mit y-Werten, die an den x-Werten Ihrer
tatsächlichen Datenpunkte als Vorhersagewerte auf der Geraden oder
Exponentialkurve liegen. Diese Vorhersagewerte können Sie mit den tatsächlichen
Werten vergleichen. Um eine bessere Vergleichsmöglichkeit zu haben, kann es
sinnvoll sein, die Werte in Diagrammen darzustellen.
Bei einer Regressionsanalyse berechnet Microsoft Excel für jeden Punkt das Quadrat
der Differenz, die zwischen dem für diesen Punkt berechneten y-Wert und dessen
tatsächlichen y-Wert liegt. Die Summe dieser quadrierten Differenzen wird
Residual-Quadratsumme genannt. Danach berechnet Microsoft Excel die Summe
der quadrierten Differenzen, die zwischen den tatsächlichen y-Werten und dem
Mittelwert der y-Werte liegen. Diese Summe wird als Gesamtsumme der
Abweichungsquadrate bezeichnet (Regressions-Quadratsumme + ResidualQuadratsumme). Je kleiner die Residual-Quadratsumme im Vergleich zu der
Gesamtsumme der Abweichungsquadrate ist, desto größer ist der Wert des
Bestimmtheitsmaßes (r2), das anzeigt, wie gut die aus der Regressionsanalyse
resultierende Gleichung die zwischen den Variablen bestehende Beziehung
beschreibt.
Formeln, die als Ergebnis eine Matrix liefern, müssen als Matrixformeln eingegeben
werden.
Wird eine Matrix-Konstante (wie zum Beispiel X_Werte) als Argument eingegeben,
müssen Sie Punkte verwenden, um Werte derselben Zeile zu trennen, und Semikola,
um die Zeilen zu trennen. Die Trennzeichen können entsprechend der
22

Ländereinstellung unterschiedlich sein (in der Systemsteuerung unter
Ländereinstellungen.
Beachten Sie, dass mit Hilfe einer Regressionsgleichung vorhergesagte y-Werte
eventuell keinen Sinn machen, wenn diese außerhalb des Bereiches der y-Werte
liegen, die Sie zur Ermittlung der Gleichung verwendet haben.
Beispiel 1 Steigung und y-Achsenabschnitt (Anfangsordinate)
A
B
y-Wert
x-Wert
1
1
0
2
3
4
3
5
2
4
7
3
5
Formel
Formel
=RGP(A2:A5;B2:B5;;FALSCH)
Anmerkung Die Formel im Beispiel muss als Matrixformel eingegeben werden. Nachdem
Sie das Beispiel in ein leeres Arbeitsblatt kopiert haben, markieren Sie den Bereich A7:B7 ab
der Zelle mit der Formel. Drücken Sie F2 und dann STRG+UMSCHALT+EINGABE. Wird
die Formel nicht als Matrixformel eingegeben, lautet das einzige Ergebnis 2.
Wird die Formel als Matrix eingegeben, werden die Steigung (2) und der y-Achsenabschnitt
(1) zurückgegeben.
Beispiel 2 Einfache lineare Regression
1
2
3
4
5
6
7
A
B
Monat
Umsatz
1
3100
2
4500
3
4400
4
5400
5
7500
6
8100
Formel
Beschreibung (Ergebnis)
=SUMME(RGP(B2:B7;
A2:A7)*{9;1})
Geschätzter Umsatz für den neunten Monat
(11000)
Im Allgemeinen ist SUMME({m;b}*{x;1}) gleich mx + b; dies ist der geschätzte y-Wert
eines gegebenen x-Wertes. Sie können dafür auch die TREND-Funktion verwenden.
23
Beispiel 3 Multiple lineare Regression
Angenommen, eine Immobilienfirma plant, mehrere Bürogebäude zu kaufen, die in einem gut
eingeführten Geschäftsviertel stehen.Ausgehend von den in der folgenden Tabelle
zusammengestellten Variablen kann die Firma mit Hilfe der multiplen linearen
Regressionsanalyse abschätzen, welchen Wert ein in einer bestimmten Gegend stehendes
Bürogebäude hat.
Variable
Beschreibt
y
den geschätzten Wert eines Bürogebäudes
x1
die Grundfläche in Quadratmetern
x2
die Anzahl von Büros
x3
die Anzahl von Eingängen
x4
das Alter des Bürogebäudes in Jahren
Für dieses Beispiel wird angenommen, dass zwischen den unabhängigen Variablen (x1, x2,
x3 und x4) und der abhängigen Variablen (y), die jeweils den Wert eines in der fraglichen
Umgebung stehenden Bürogebäudes angibt, eine lineare Beziehung besteht.
Die Immobilienfirma wählt aus 1500 möglichen Bürogebäuden nach dem Zufallsprinzip eine
Stichprobe von 11 Bürogebäuden und erhält die folgenden Daten: "Halber Eingang" bedeutet,
dass nur ein Lieferanteneingang vorhanden ist.
A
B
2
3
4
5
6
7
8
9
10
11
12
D
E
Büroräume Eingänge Alter Schätzwert
(x2)
(x3)
(x4)
(y)
Grundfläche (x1)
1
C
2310
2
2
20
142.000
2333
2
2
3,60
144.000
2356
3
1,5
33
151.000
2379
3
2
43
150.000
2402
2
3
53
139.000
2425
4
2
23
169.000
2448
2
1,5
99
126.000
2471
2
2
34
142.900
2494
3
3
23
163.000
2517
4
4
55
169.000
2540
2
3
22
149.000
Formel
=RGP(E2:E12;A2:D12;WAHR;WAHR)
24
Anmerkung Die Formel im Beispiel muss als Matrixformel eingegeben werden. Nachdem
Sie das Beispiel in ein leeres Arbeitsblatt kopiert haben, markieren Sie den Bereich A14:E18
beginnend mit der Formelzelle. Drücken Sie F2 und dann STRG+UMSCHALT+EINGABE.
Wird die Formel nicht als Matrixformel eingegeben, lautet das einzige Ergebnis 234,2371645.
Wird die Formel als Matrix eingegeben, werden folgende Regressionsstatistiken
zurückgegeben. Verwenden Sie diese Taste, um die gewünschte Statistik anzuzeigen.
Mit den in Zeile 14 stehenden Werten kann jetzt die Gleichung für die multiple lineare
Regression (y = m1*x1 + m2*x2 + m3*x3 + m4*x4 + b) formuliert werden:
y = 27,64*x1 + 12.530*x2 + 2.553*x3 - 234,24*x4 + 52.318
Die Immobilienfirma kann jetzt den Schätzwert eines 25 Jahre alten Bürogebäudes ermitteln,
das in demselben Geschäftsviertel steht, eine Grundfläche von 2.500 Quadratmetern hat, drei
Büros umfasst und zwei Eingänge hat. Dazu verwendet die Firma folgende Gleichung:
y = 27,64*2500 + 12530*3 + 2553*2 - 234,24*25 + 52318 = 158.261 Euro
Sie können auch die folgende Tabelle in Zelle A21 der Beispiel-Arbeitsmappe kopieren.
Grundfläche
(x1)
Büroräume
(x2)
Eingänge
(x3)
Alter
(x4)
2500
3
2
25
Schätzwert (y)
=D14*A22 + C14*B22 +
B14*C22 + A14*D22 + E14
Sie können diesen Wert auch mit der TREND-Funktion berechnen.
Beispiel 4 Einsetzen der F- und der R2-Statistik (F-Test und Bestimmtheitsmaß)
In dem vorherigen Beispiel hat der Bestimmtheitskoeffizient (oder r2) den Wert 0,99675
(siehe Zelle A17 in der von RGP erzeugten Ausgabe). Dieser Wert zeigt einen starken
Zusammenhang zwischen den unabhängigen Variablen und dem jeweiligen Verkaufspreis an.
Mit Hilfe der F-Statistik können Sie prüfen, ob diese Ergebnisse mit einem derart großen
Bestimmtheitsmaß (r2) zufällig sind oder nicht.
Stellen Sie dazu die Hypothese auf, dass zwischen den Variablen eigentlich kein
Zusammenhang besteht, sondern dass Sie nur zufällig eine Stichprobe von 11 Bürogebäuden
erhoben haben, für die die statistische Analyse einen starken Zusammenhang anzeigt. Um die
Wahrscheinlichkeit zu beschreiben, mit der irrtümlich ein Zusammenhang ermittelt wird, wird
die Irrtumswahrscheinlichkeit "Alpha" verwendet.
Es besteht ein Zusammenhang zwischen den Variablen, wenn der bei dem F-Test berechnete
F-Wert größer ist als der kritische F-Wert. Den kritischen F-Wert können Sie entsprechenden
Tabellen entnehmen, die Sie in vielen Statistikbüchern finden. Um eine solche Tabelle für
dieses Beispiel auswerten zu können, nehmen Sie Folgendes an: Der Test ist einseitig; als
Alpha-Quantil wird 0,05 verwendet; für die Freiheitsgrade (in den meisten Tabellen mit v1
25
und v2 bezeichnet) werden v1 = k = 4 und v2 = n – (k + 1) = 11 – (4 + 1) = 6 verwendet,
wobei k für die Anzahl von bei der Regressionsanalyse verwendeten Variablen und n für die
Anzahl von Datenpunkten steht. Dafür ergibt sich ein kritischer F-Wert von 4,53.
Der berechnete F-Wert ist 459,753674 (Zelle A18) und damit deutlich größer als der kritische
F-Wert von 4,53. Daher ist die gefundene Regressionsgleichung geeignet, den Schätzwert
eines Bürogebäudes zu ermitteln, das zu dem fraglichen Büroviertel gehört.
Beispiel 5 Berechnen Der T-Statistik (T-Test)
Mit Hilfe einer anderen Hypothese kann festgestellt werden, ob die einzelnen
Steigungskoeffizienten geeignet sind, den Schätzwert eines der im Beispiel 3 aufgeführten
Bürogebäude zu berechnen. Um zum Beispiel den Koeffizienten für das Gebäudealter
bezüglich der statistischen Wahrscheinlichkeit (Sicherheit) zu prüfen, dividieren Sie –234,24
(Steigungskoeffizient für das Alter) durch 13,268 (der in Zelle 15 stehende Standardfehler des
Alterskoeffizienten). Daraus ergibt sich der folgende t-Wert:
t = m4 ÷ se4 = -234,24 ÷ 13,268 = -17,7
Wenn Sie die entsprechende Tabelle eines Statistikbuches zu Rate ziehen, werden Sie
feststellen, dass der kritische t-Wert bei einem einseitigen Test mit 6 Freiheitsgraden und
Alpha = 0,05 den Wert 1,94 hat. Da der Absolutwert von t (17,7) größer als 1,94 ist, ist das
Alter eine zuverlässige Variable, um den Schätzwert eines Bürogebäudes zu ermitteln. Für
alle weiteren unabhängigen Variablen kann die statistische Wahrscheinlichkeit auf dieselbe
Weise geprüft werden. Für die anderen unabhängigen Variablen werden die folgenden tWerte ermittelt:
Variable
Berechneter t-Wert
Grundfläche
5,1
die Anzahl von Büros
31,3
die Anzahl von Eingängen
4,8
Alter
17,7
Alle Werte haben einen Absolutwert, der größer als 1,94 ist. Daher sind alle Variablen, die in
der Regressionsgleichung verwendet werden, geeignet, den Schätzwert eines zum fraglichen
Büroviertel gehörenden Bürogebäudes zu bestimmen.
26
Beschreibung von (metrischen) Daten
univariat
numerisch
grafisch
Mittelwerte
Streuungsmaße
Quantile
Konzentrationsmaße
...
Histogramm
Boxplot
Quantilsplot
Plot gegen Zeit
Symmetrieplot
bivariat
multivariat
Kovarianz
multiple Korrelation
Korrelation
Hauptkomponenten
Assoziationsmaße
Scatterplot
Trendlinien
Umsetzung mit Excel
Chernov-Gesichter
Andrews-Kurven
interaktive 3D-Plots
Scatterplotmatrizen
Clusterdarstellung
MDS
Beispiele/Kommentare
einzelne
numerische
Kennzahlen
entsprechende Formel
aufrufen
(z.B.Funktionsassistent->
Kategorie Statistik-> ...)
Syntax beachten! Hilfe
konsultieren!
=MITTELWERT(A1:A100)
=VARIANZ(A1:A100)
=QUARTILE(A1:A100;3)
=KORREL(B1:B100;C1:C100)
mehrere
numerische
Kennzahlen
Analyse-Funktion
Populationskenngrößen
(Extras->Analyse-Funktionen>...)
Ergebnisse werden als Zahl,
nicht als Formel geliefert
=> keine Änderung beim
Ändern der ursprünglichen
Daten!
Analyse-Funktion
Histogramm
(Extras->Analyse-FunktionenHistogramm >...)
oder Matrix-Funktion
HÄUFIGKEIT() und
Diagramm-Assistent (Säulen)
Klassenzugehörigkeit wird mit
“über ... bis gleich ...” ermittelt!!
Säulenabstände nachträglich
auf 0 setzen!
Beschriftung korrigieren!
nur bei gleichen
Klassenbreiten !!
Plot gegen
Zeit
Diagramm-Assistent ...
Säule oder Linie
Zeitreihe darf keine fehlenden
Werte haben!
Scatterplot
Diagramm-Assistent ... Punkt
(XY)
Trendlinie
im Scatterplot Datenpunkt
markieren
Diagramm-> Trendlinie
hinzufügen ...
Boxplot
Symmetrieplot in Excel nicht standardmäßig
multivariate
implementiert!
Darstellungen
27
Gleitende Durchschnitte
werden dem letzten, nicht dem
mittleren Datenpunkt
zugeordnet.
28