# 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:

- http://www.reiter1.com/Glossar/Excelfunktionen_Statistik.xls
- http://www.reiter1.com/Glossar/Verteilungen,%20Hypergeomvert_Binomial_Poisson_Normal.xls
- http://www.reiter1.com/Glossar/Excel_Pruefgroessen.xls
- http://www.reiter1.com/Glossar/Binomial.xls
- http://www.reiter1.com/Glossar/RGP_Regression.xls
- http://www.reiter1.com/Glossar/Median.xls
- http://www.reiter1.com/Glossar/Cpk_Kritbinom_Konfidenz_indirekt.xls
- http://www.reiter1.com/Glossar/SVERWEIS.xls
- http://www.reiter1.com/Glossar/Matrixformel.xls

Zurück zum Glossar (Excel)

ACHSENABSCHNITT (y-Wertereihe, x-Wertereihe) Berechnet den y-Achsenabschnitt der Regression sgeraden 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, Stichproben grösse, Erfolgswahrscheinlichkeit, kumuliert[j/n]) Ziehung von genau n Elementen aus einer (unendlich grossen) binomialverteilten Grundgesamtheit mit bekanntem Anteil Merkmal strä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. 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. Beispiele: X und hier
FINV(Alpha Risiko, Anzahl Freiheitsgrade 1, 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 Transformierte n z. Beispiel: hier
GAMMAVERT (x, Formparameter, Skalenparameter, kumuliert [j/n]) Berechnet die Dichtefunktion der Gammaverteilung an der Stelle x (kumuliert = falsch) (kumulierte) Verteilungsfunktion der Gammaverteilung bis zur Stelle x. 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 Merkmal strä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 normalverteilt en Grundgesamtheit mit bekannter Standardabweichung. KONFIDENZ berechnet die halbe Breite des Vertrauensbereiches des arithmetischen Mittel wertes 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 Merkmal strä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. 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)