Statistik mit Excel

Excel Statistikfunktionen mit Vorlagen und Beispielen

Ohne Frames

Hier werden einige Excel Statistik Funktionen erklärt und anhand von Beispielen veranschaulicht (rechte Spalte). 
Grundsätzliche Anmerkungen zu Statistik mit Excel finden sich hier.

Zellenbezogene Funktionen.

Manche EXCELfunktionen sind so genannte Matrixformeln. Das bedeutet, dass bei der Formeleingabe mehrere Zellen gleichzeitig markiert sein müssen damit Excel mehrere Werte gleichzeitig ausgeben kann.Da dies im Detail für jeden Formeltyp anders funktioniert, sei hier allgemein auf die EXCEL Hilfe verwiesen. Matrixformeleingaben müssen mit {Steuerung}+{Umschalten}+{Enter} abgeschlossen werden.

 

Im Folgenden werden einige Excel Statistikfunktionen erläutert. 

In einer separaten Spalte wird des Öfteren auf separate Exceldateien verwiesen, die zum besseren Verständnis dienen sollen.

(Gekennzeichnet mit "X" oder "hier").


Im allgemeinen funktionieren diese Funktionen in den freien Officepaketen (Open Office, Libre Office) nicht wie vorgesehen.


Sämtliche Beispieldateien, auf die in folgender Tabelle verwiesen wird, sind:

Zurück zum Glossar (Excel)



ACHSENABSCHNITT (y-Wertereihe, x-Wertereihe)
Berechnet den y-Achsenabschnitt der Regressionsgeraden beim x-Wert Null, also den Wert b der Geradengleichung Y =mX + b.     Beispiel: X
BETAVERT (x, p, q, untere Grenze, obere Grenze)

Berechnet die (kumulierte) Verteilungsfunktion der Betaverteilung

Falls die beiden Parameter "obere" und "untere Grenze" weggelassen werden, übernimmt Excel die Werte 0 und 1.     Beispiel: X

BETAINV (kumulierter Flächenanteil, p, q, untere Grenze, obere Grenze)
Berechnet die Stelle z auf der horizontalen Achse, die dem kumulierten Flächenanteil der Betaverteilung  entspricht.   Beispiel: X
BINOMVERT(Anzahl Erfolge, Stichprobengrösse, Erfolgswahrscheinlichkeit, kumuliert[j/n])
Ziehung von genau n Elementen aus einer (unendlich grossen) binomialverteilten Grundgesamtheit mit bekanntem Anteil Merkmalsträgern. BINOMVERT berechnet die Wahrscheinlichkeit, dass unter den n gezogenen Elementen 
  • genau x Merkmalsträger sind (kumuliert = falsch)
  • höchstens x Merkmalsträger sind (kumuliert = wahr)

Umkehrfunktion von KRITBINOM.     Beispiele:    X        hier

CHIVERT( Prüfgrösse, Anzahl Freiheitsgrade).

Berechnet das Alpha Risiko einer Chi Quadrat verteilten Zufallsgrösse. Umkehrfunktion von CHIINV.
Chi Quadrat Verteilung Alpharisiko
Beispiele:  X   und  hier
CHIINV (Alpha Risiko, Anzahl Freiheitsgrade)
Berechnet die Prüfgrösse des Vertrauensbereiches einer Chi Quadrat verteilten Zufallsgrösse. Umkehrfunktion von CHIVERT.    Beispiele:  X   und  hier
CHITEST ([tatsächliche Wertereihe], [erwartete Wertereihe])
CHITEST berechnet das Signifikanzniveau dafür, dass die Werte einer Wertereihe (z.B. Häufigkeitsverteilungen) mit den erwarteten Werten übereinstimmen.. 

Hier wird also jeder Einzelwert der einen Reihe mit dem korrespondierenden Wert der zweiten reihe verglichen.   Beispiele:  X   und  hier

FTEST([tatsächliche Wertereihe], [erwartete Wertereihe])
FTEST berechnet das Signifikanzniveau dafür, dass die Varianzen zweier Stichproben gleich sind.    Beispiele:  X   und  hier
FVERT (Prüfgrösse, Anzahl Freiheitsgrade1, Anzahl Freiheitsgrade2).
Berechnet das
Alpha Risiko einer F- verteilten Zufallsgrösse. Umkehrfunktion von FINV.
F Verteilung Alpharisiko
   Beispiele:  X   und  hier
FINV(Alpha Risiko, Anzahl Freiheitsgrade1, Anzahl Freiheitsgrade2).
Berechnet die Prüfgrösse des  Vertrauensbereiches einer F- verteilten Zufallsgrösse. Umkehrfunktion von FVERT.
FISHER(x)
Berechnet die Fisher Transformierte zum Wert x.     Beispiel: hier
FISHERINV(z)
Berechnet den ursprünglichen Wert x der Fisher Transformierten z.     Beispiel: hier
GAMMAVERT (x, Formparameter, Skalenparameter, kumuliert [j/n])
Berechnet die 

Umkehrfunktion von GAMMAINV.      Beispiel: X

GAMMAINV( kumulierte Fläche, Formparameter, Skalenparameter).
Berechnet die Stelle x auf der horizontalen Achse, die dem kumulierten Flächenanteil der Gammaverteilung entspricht.

Umkehrfunktion von GAMMAVERT.      Beispiel: X

GAMMALN(x)
Berechnet den natürlichen Logarithmus der Gammafunktion an der Stelle x.
GEOMITTEL(Wert1....Wertn)
Berechnet das geometrische Mittel einer aus maximal 30 Werten bestehenden Wertereihe
GESTUTZTMITTEL (Wertereihe, Prozent)
Entspricht Winsorisieren.  Beispiel: Prozent = 0.2--> links und rechts werden jeweils 10%der Werte nicht beachtet.
GTEST (Wertereihe, x, Standardabweichung)
Berechnet die relative Lage eines Wertes x im Vergleich zu einer als normalverteilt angenommenen Wertereihe. GTEST(...) = 1,5 bedeutet z.B, dass x das 1,5-fache einer Standardabweichung vom Mittelwert der Wertereihe entfernt liegt. 

Der Parameter Standardabweichung ist optional, bei Weglasssen wird er aus der Wertereihe berechnet.

HARMITTEL (Wert1....Wertn)
Berechnet das harmonische Mittel einer aus maximal 30 Werten bestehenden Wertereihe.  
HYPERGEOMVERT (Anzahl Erfolge, Stichprobengrösse, Anzahl Merkmalsträger der Grundgesamtheit, Grösse der Grundgesamtheit)
Ziehung von genau n Elementen aus einer (endlich grossen, hypergeometrisch verteilten) Grundgesamtheit mit bekannter Anzahl Merkmalsträger. 

HYPERGEOMVERT berechnet die Wahrscheinlichkeit, dass unter den n gezogenen Elementen genau x Merkmalsträger sind.     Beispiel:  hier

KOMBINATIONEN (Gesamte Anzahl N, davon gewählte Anzahl k)
Ziehen ohne Zurücklegen. Berechnet die Anzahl Kombinationen. Formel siehe Kombinatorik unter "Kombinationen".
KONFIDENZ (Alpha Risiko, Standardabweichung, Stichprobengrösse)
Ziehung einer Stichprobe aus einer normalverteilten Grundgesamtheit mit bekannter Standardabweichung

KONFIDENZ berechnet die halbe Breite des Vertrauensbereiches des arithmetischen  Mittelwertes der Stichprobe.     Beispiel: X

KORREL(...)
= PEARSON(...).     Beispiel: X
KOVAR(Wertereihe_1, Wertereihe_2)
Berechnet die Kovarianz einer Reihe von Wertepaaren.     Beispiel: X
KRITBINOM (Anzahl Ziehungen, Anteil Merkmalsträger, Grenzwahrscheinlichkeit)
Ziehung von genau n Elementen aus einer aus einer unendlich grossen Grundgesamtheit mit bekanntem Anteil Merkmalsträgern. 

KRITBINOM berechnet die maximale Anzahl Merkmalsträger, die mit der gegebenen Grenzwahrscheinlichkeit in diesen n Elementen enthalten sind. Die Grenzwahrscheinlichkeit entspricht mathematisch gesehen dem Alpha Risiko.

Umkehrfunktion von BINOMVERT.        Beispiele:  X und hier

KURT(Wert1....Wertn)
Berechnet die Kurtosis einer aus maximal 30 Werten bestehenden Wertereihe.
LOGNORMVERT (x, Mittelwert, Standardabweichung)
Berechnet die (kumulierte) Verteilungsfunktion der Lognormalverteilung bis zur Stelle x.
MEDIAN(Wert1, Wert2,...,Wertn)
Berechnet den mittleren Wert einer Wertereihe.
MODUS(Wert1,....,Wertn)
Berechnet den Modus einer aus maximal 30 Werten bestehenden Wertereihe
NEGBINOMVERT (Anzahl Nicht-Merkmalsträger, Anzahl Merkmalsträger, Erfolgswahrscheinlichkeit)
Fortlaufende Ziehung von  Elementen aus einer binomialverteilten  Grundgesamtheit mit bekanntem Anteil Merkmalsträgern. Man zieht so lange, bis man genau c Merkmalsträger gefunden hat.

NEGBINOMVERT berechnet die Wahrscheinlichkeit, für das Auffinden von genau c Merkmalsträgern genau x "Nicht-Merkmalsräger" zu finden.    Beispiele:  X

NORMVERT (z, Mittelwert, Standardabweichung, kumuliert [j/n]
Berechnet 
  • den Wert der Dichtefunktion der Normalverteilung an der Stelle z (kumuliert = wahr) 
  • die (kumulierte) Normalverteilungsfunktion bis zur Stelle z (kumuliert = wahr)
Beispiele:  X und hier
NORMINV (Kumulierter Flächenanteil, Mittelwert, Standardabweichung)
Berechnet die Stelle z auf der horizontalen Achse, die dem kumulierten Flächenanteil der Normalverteilung  entspricht.    Beispiel:  X
PEARSON (Feld1, Feld2)

Berechnet den Pearson'schen Korrelationskoeffizienten 

(= "der" Korrelationskoeffizient)zweier Wertereihen (x-Werte und y-Werte)

POISSON (Anzahl Ereignisse, mittlere Anzahl Ereignisse, kumuliert [j/n])
Es liege eine poissonverteilte Anzahl Ereignisse/Einheit mit bekanntem Mittelwert vor. 

POISSON berechnet die Wahrscheinlichkeit, dass eine Einheit 

  • genau x Ereignisse enthält (kumuliert = falsch) 
  • höchstens x Ereignisse enthält (kumuliert = wahr)
Beispiel:  hier
QUANTIL (Wertereihe, a)
Umkehrfunktion von QUANTILSRANG. 
Gibt denjenigen Zahlenwert für eine Wertereihe wieder, sodass der relative Anteil
a aller Zahlenwerte der Wertereihe kleiner oder gleich dieses Zahlenwertes ist.
QUANTILSRANG (Wertereihe, x, Genauigkeit)
Umkehrfunktion von QUANTIL. 
Ermittelt die relative Position
a des Wertes x innerhalb einer Wertereihe. 
RANG (Rang, Datenfeld, Richtung)
Berechnet den Rang eines Wertes innerhalb einer Gruppe von Werten. 
  • Richtung = 0 oder leer: Grösster Wert bekommt Rang 1 
  • Richtung = sonstiger Eintrag: Kleinster Wert bekommt Rang 1
RGP (y-Werte, x-Werte, b=0 [j/n], Stats [j/n])
x- und y-Werte sind Wertepare. B ist die Konstante in der Regressionsgeradengleichung y=mx+b. Bei b=0 [j/n] ="falsch" wird b=0 gesetzt.
Stats sind mehrere statistische Kennwerte. 

Man beachte, dass x mehrdimensional sein kann. Dann ist der Befehl RGP als Matrixformel einzugeben.
Für genauere Ausführungen siehe Exceldatei . 

In dieser Beispieldatei ist auch der Fall Multiple Regression behandelt (mehrere Wertereihen x, Matrixformel).     Beispiel:  hier

RKP (y-Werte, x-Werte, b=0 [j/n], Stats [j/n])
Wie RGP, jedoch liegt eine Exponentialgleichung zugrunde : 
y=b* m1x1*m2x2*…*mnxn, bzw: 
ln y = x1 ln m1 + ... + xn ln mn + ln b. 
Der einzige, jedoch wichtige Unterschied zu RGP besteht darin, dass die Standardfehler sich auf die Werte ln(mi), ln(b) beziehen.

SCHÄTZER (x,[bekannte y-Wertereihe],[bekannte x-Wertereihe])
Berechnet eine lineare Regression aus den bekannten Wertepaaren (x,y) und gibt den daraus geschätzten y-Wert an der Stelle x heraus.    Beispiel: X
SCHIEFE (Wert1....Wertn)
Berechnet die Schiefe einer aus maximal 30 Werten bestehenden Wertereihe.
STABW (Wert1, Wert2,...,Wertn) und STABWA (Wert1, Wert2,...,Wertn)
Berechnet die Standardabweichung einer Stichprobe

Erklärung des Unterschiedes   STABW - STABWA siehe hier.

STANDARDISIERUNG (z, Mittelwert, Standardabweichung)
Berechnet den standardisierten Wert z einer Normalverteilung mit bekanntem Mittelwert und Standardabweichung.    Beispiel: X
STANDNORMVERT (z)
Berechnet die kumulierte Standardnormalverteilung zum Wert z.     Beispiel: X
STANDNORMINV (Kumulierter Flächenanteil)
Berechnet die Stelle z auf der horizontalen Achse, die dem kumulierten Flächenanteil der Standardnormalverteilung  entspricht.     Beispiel: X
STEIGUNG (y-Wertereihe, x-Wertereihe)
Berechnet die Steigung der linearen Regressionsgeraden, also den Wert m der Geradengleichung Y =mX + b.      Beispiel: X
STFEHLERYX (y-Werte, x-Werte)
Schätzt den Standardfehler der auf Basis der x-Werte berechneteten y-Werte bei einer linearen Regression.     Beispiel: X
SUMQUADABW (Wertereihe)
Gibt die Summe der quadrierten Abweichungen von Datenpunkten von deren Stichprobenmittelwert zurück. (Varianz)   
TREND ([bekannte y-Wertereihe],[bekannte x-Wertereihe],[neue x-Wertereihe,[j/n]
Ähnlich wie SCHÄTZER, jedoch berechnet TREND die geschätzten y-Werte für mehrere neuen x-Werte gleichzeitig. TREND ist eine Matrixformel.

J/N = „Wahr“:  y-Achsenabschnitt wird aus den gegebenen Daten berechnet.

J/N = „falsch“:  y-Achsenabschnitt wird = 0 gesetzt.      Beispiel: X               

TVERT (Prüfgrösse, Anzahl Freiheitsgrade, 1- oder 2- seitig).
Berechnet das Alpha Risiko einer t- verteilten Zufallsgrösse. Umkehrfunktion von TINV.
t-Verteilung Alpharisiko
Beispiele: X  und hier
TINV (Alpha Risiko, Anzahl Freiheitsgrade)
Berechnet die Prüfgrösse des zweiseitigen Vertrauensbereichs einer t- verteilten Zufallsgrösse. Umkehrfunktion von TVERT. 

Möchte man z.B.den einseitigen 10% Vertrauensbereich, so muss man für das alpha risiko 20% einsetzen.     Beispiele: X  und hier

TTEST ([tatsächliche Wertereihe], [erwartete Wertereihe], 1- oder 2-seitig, Typ)
TTEST berechnet das Signifikanzniveau dafür, dass die Mittelwerte zweier Stichproben gleich sind. 
  • Typ 1: gepaarte Werte 
  • Typ 2: Werte ungepaart, Varianzen gleich 
  • Typ 3: Werte ungepaart, Varianzen ungleich
 Beispiele: X  und hier
VARIANZ (Wert1....Wertn)
Berechnet die Varianz einer aus maximal 30 Werten bestehenden Wertereihe.
VARIATION ([bekannte y-Wertereihe],[bekannte x-Wertereihe],[neue x-Wertereihe,[j/n]
Exponentielle Entsprechung zur linearen Funktion TREND.

VARIATION berechnet die geschätzten y-Werte eines exponentiellen Trends für mehrere neue x-Werte gleichzeitig. VARIATION ist eine Matrixformel.
J/N = „Wahr“:  y-Achsenabschnitt wird aus den gegebenen Daten berechnet.
J/N = „falsch“:  y-Achsenabschnitt wird = 0 gesetzt.

VARIATIONEN(Gesamte Anzahl N, davon gewählte Anzahl k)
Berechnet die Anzahl Variationen ohne Zurücklegen.  Siehe bei Kombinatorik unter "Variation".
WAHRSCHBEREICH (Wertereihe, dazu korrespondierende Häufigkeitswerte, untere Wertegrenze, obere Wertegrenze)
Berechnet die gesamte relative Häufigkeit, mit der die Werte der Wertereihe zwischen einer oberen und unteren Grenze liegen.    Beispiel:  hier
WEIBULL (Zeitpunkt, Formfaktor, Lebensdauer, kumuliert [j/n]
Weibull berechnet die 
  • Dichtefunktion der Weibullverteilung (kumuliert = falsch) 
  • Verteilungsfunktion der Weibullverteilung (kumuliert = wahr) 

zu einem gegebenen Zeitpunkt.    Beispiel:  X


Weitere Excelfunktionen

INDIREKT (..) und ADRESSE (...)

Beispiel: 

 

In B10 steht "27

In E1 steht: "=STABW(B12:INDIREKT(ADDRESSE(12+B10,2)))"

-->

In E1 wird die Standardabweichung aller Werte berechnet, die in den Zellen B12 bis B39 stehen (12+27=39)

Beispiele:  hier   und  hier


SVERWEIS (Suchkriterium, Matrix, Spaltenindex, j/n)

Siehe Beispieldatei für nähere Erläuterungen.

J/N = „Wahr“:  Wird keine exakte Entsprechung gefunden, wird die nächstgrössere zurückgegeben.

J/N = „falsch“: Wird keine exakte Entsprechung gefunden, wird der Fehler N/V zurückgegeben.

Beispiel

Diverse Matrixformeln für bedingtes Berechnen nach mindestens 2 Kriterien.
Beispiel:  ={MITTELWERT(WENN (D3:D35=B42;(WENN(E3:E35=B43;G3:G35))))} 
Beispiel



22.08.2005

Zurück zum Glossar (Excel)