Excel-Formeln für die Analyse-Funktion Populationskenngrößen

Eingestellt: 29.05.2009
Letzte Änderung: 29.05.2009


Keywords: Populationskenngrößen Zufallsstichprobe • erwartungstreue Schätzer für die Momente • Konfidenzintervall
Betrifft:  MITTELWERT, VARIANZ, STABW, MEDIAN, MODALWERT, KURT, SCHIEFE, MAX, MIN, KGRÖSSTE, KKLEINSTE, TINV


 

Einleitung

Dieses Analysetool generiert einen Bericht über eindimensionale (univariate) Statistiken, in dem Informationen über die zentrale Tendenz und Streuung der Daten bereitgestellt werden.1 Im Folgenden wird die Analyse-Funktion Populationskenngrößen so verstanden, dass sie Kenngrößen für eine Zufallsstichprobe liefert.2

Der Vorteil der Analyse-Funktion kann neben der einfachen Handhabung darin gesehen werden, dass weder Excel-Funktionen noch Excel-Formeln notwendig sind. Demgegenüber können folgende Eigenschaften der Analyse-Funktion als Nachteile interpretiert werden:
Bei einer Änderung der Daten erfolgt keine automatische Aktualisierung der Ergebnisse.3
Es werden unabhängig vom Skalenniveau eines Merkmals alle Kenngrößen berechnet.4
Der Eingabebereich muss ein zusammenhängender Bereich sein, d.h. liegen der Untersuchung mehr als zwei Merkmale zugrunde, kann keine beliebige Kombination an Merkmalen ausgewählt werden.5

Daher sollen zu den Populationskenngrößen korrespondierende Excel-Funktionen und Excel-Formeln aufgestellt werden. Hierzu werden zunächst die Formeln für die Populationskenngrößen betrachtet und kurz erläutert sowie die Durchführung der Analyse-Funktion anhand eines Beispiels veranschaulicht.

 

Formeln für die Populationskenngrößen

Aus einer Grundgesamtheit mit Umfang  wird eine Zufallsstichprobe  der Länge  gezogen.

 Kenngröße  Formel
 Mittelwert  
 Standardfehler  
 Median  
 Modus  
 Standardabweichung  
 Stichprobenvarianz  

 Kurtosis

 
 Schiefe  
 Wertebereich  
 Minimum  
 Maximum  
 Summe  
 Anzahl  
 k-größter Wert(h)  
 k-kleinster Wert(l)  
 Konfidenzniveau(1-α)  

Bei der Analyse-Funktion Populationskenngrößen werden erwartungstreue Schätzer für die Momente verwendet. So entspricht die Stichprobenvarianz der korrigierten Stichprobenvarianz und die Standardabweichung der Wurzel aus der korrigierten Stichprobenvarianz. Der Standardfehler (mittlerer Fehler des Mittelwertes) ergibt sich aus der Wurzel des Quotienten von korrigierter Stichprobenvarianz und  Länge der Stichprobe. Zu beachten ist ferner, dass der Exzess als Kurtosis definiert ist.

Auf die Schätzung der Quantile in Excel, insbesondere des Medians, wird im Artikel Excel-Formeln für Quantile eingegangen. Ausführliche Informationen zum verwendeten Algorithmus finden Sie auch im Artikel Der Algorithmus für die Excel-Funktion QUARTILE.

Ist für ein  die Ungleichung  für alle  erfüllt, so heißt  ein Modus (Modalwert, häufigster Wert). Eine Angabe des Modalwertes sollte generell auf den Fall unimodaler Verteilungen beschränkt werden.

Die Kenngröße Wertebereich entspricht dem Streuungsmaß Spannweite, welche im Artikel Spannweite behandelt wird.

Der -grösste Wert aus einer absteigenden Ordnungsstatistik entspricht dem -kleinsten Wert aus einer aufsteigenden Ordnungsstatistik, d.h. , umgekehrt gilt  mit .

Bei der Bestimmung des Konfidenzniveaus für den Erwartungswert wird angenommen, dass  und  eine einfache Stichprobe aus  ist, wobei die Varianz nicht bekannt ist. Daher ergibt sich das -Quantil über die t-Verteilung mit  Freiheitsgraden.

 

Beispiel

Die Durchführung der Analyse-Funktion Populationskenngrößen soll anhand eines Beispiels mit einem Stichprobenumfang von veranschaulicht werden.  Dieses steht Ihnen in nachstehender xls- und xlsx-Datei als Download zur Verfügung:

XLADescr.xls

XLADescr.xlsx

Um die Excel-Funktionen und die korrespondierenden Excel-Formeln dynamisch und übersichtlich zu halten, werden folgende Namen definiert.6


Abbildung 1 - Beispiel

Namen in der Tabelle:
n:=ANZAHL($B:$B)
x:=INDIREKT("$B$2:$B$"&n+1)
alpha:=$E$1
h:=$E$2
l:=$E$3

Nach Installation des Add-Ins Analyse-Funktionen kann die Analyse-Funktion Populationskenngrößen in Excel 2003 in der Menüleiste über Extras / Analyse-Funktionen, in Excel 2007 in der Menüleiste über Daten im Ribbon Analyse mit Datenanalyse aufgerufen werden.


Abbildung 2 - Auswahl der Analyse-Funktion Populationskenngrößen

Markieren Sie die gewünschte Analyse-Funktion, und bestätigen Sie mit OK. Wählen Sie für dieses Beispiel in der Eingabe den Eingabebereich $B$1:$B$11, geordnet nach Spalten und Beschriftungen in erster Zeile.

Der Ausgabebereich soll in diesem Tabellenblatt ab Zelle $G$1 erfolgen, wobei sowohl die statistischen Kenngrößen als auch das Konfidenzniveau für den Mittelwert (mit einem Konfidenzkoeffizienten von 95%), der k-größte Wert (mit ) und der k-kleinste Wert (mit ) ausgegeben werden sollen.


Abbildung 3 - Eingabemaske der Analyse-Funktion Populationskenngrößen

Bestätigen Sie mit OK. Der Dialog wird daraufhin geschlossen und Sie erhalten den Output der Populationskenngrößen.


Abbildung 4 - Output der Analyse-Funktion Populationskenngrößen

 

Excel-Funktionen und Excel-Formeln für die Analyse-Funktion

Die für die Analyse-Funktionen Populationskenngrößen korrespondierende Excel-Funktionen und Excel-Formeln lauten:

 Kenngröße  Excel-Funktion  Excel-Formel
 Mittelwert  =MITTELWERT(x)  =SUMME(x)/ANZAHL(x)
 Standardfehler    =STABW(x)/ANZAHL(x)^0,5
 Median  =MEDIAN(x)  =WENN(REST(ANZAHL(x)/2;1)=0;(KKLEINSTE(x;ANZAHL(x)/2)+KKLEINSTE(x;ANZAHL(x)/2+1))/2;KKLEINSTE(x;(ANZAHL(x)+1)/2))
 Modus  =MODALWERT(x)  {=INDEX(x;VERGLEICH(MAX(ZÄHLENWENN(x;x));ZÄHLENWENN(x;x);0))}
 Standardabweichung  =STABW(x)  {=(SUMME((x-MITTELWERT(x))^2)/(ANZAHL(x)-1))^0,5}
 Stichprobenvarianz  =VARIANZ(x)  {=SUMME((x-MITTELWERT(x))^2)/(ANZAHL(x)-1)}

 Kurtosis

 =KURT(x)  {=(n*(n+1)*SUMME(((x-MITTELWERT(x))/STABW(x))^4)-3*(n-1)^3)/((n-1)*(n-2)*(n-3))}
 Schiefe  =SCHIEFE(x)  {=n/((n-1)*(n-2))*SUMME(((x-MITTELWERT(x))/STABW(x))^3)}
 Wertebereich    =MAX(x)-MIN(x)
 Minimum  =MIN(x)  
 Maximum  =MAX(x)  
 Summe  =SUMME(x)  
 Anzahl  =ANZAHL(x)  
 k-größter Wert(h)  =KGRÖSSTE(x;h)  
 k-kleinster Wert(l)  =KKLEINSTE(x;l)  
 Konfidenzniveau(1-α)    =TINV(alpha;ANZAHL(x)-1) *STABW(x)/ANZAHL(x)^0,5

Mit Ausnahme des Standardfehlers, des Wertebereichs und des Konfidenzniveaus existieren Excel-Funktionen, mit denen sich die entsprechenden Kenngrößen der Analyse-Funktion berechnen lassen. Für die drei genannten Kenngrößen lassen sich jedoch Excel-Formeln aufstellen.

Sowohl bei den Excel-Formeln als auch bei den Excel-Funktionen wird der Name x verwendet, bei den Excel-Formeln der Kenngrößen Schiefe und Kurtosis der Übersicht halber noch zusätzlich der Name n. Beachten Sie zudem, dass einige Excel-Formeln als Arrayformeln einzugeben sind.

Abbildung 5 stellt den Output der Analyse-Funktion Populationskenngrößen und die Excel-Funktionen und Excel-Formeln gegenüber.


Abbildung 5 - Excel-Funktionen und Excel-Formeln für die Populationskenngrößen

Der Stichprobenumfang  ist gerade, so dass sich der Median aus dem Mittel der fünft- und sechstgrößten Beobachtung ergibt mit:

Im Beispiel liegt eine bimodale Verteilung vor, wobei die Analyse-Funktion als auch die Excel-Funktion und Excel-Formel lediglich den Wert 12 als Modus ausgeben. Der Modus 2 wird dagegen nicht aufgeführt. Offenbar gilt hierbei das Kriterium der kleinere Zeilen- und / oder Spaltennummer der ersten Beobachtung. Während die erste Beobachtung mit dem Wert 12 in Zelle B6 steht, findet sich die erste Beobachtung mit dem Wert 2 in Zelle B7. Wie Sie mit Hilfe von Excel-Formeln alle Modalwerte auflisten, eine aufsteigende oder absteigende Sortierung der Modalwerte erzwingen sowie Modalwerte auch für klassierte Daten in Excel berechnen können, finden Sie im weiterführenden Artikel [LINK]. 

Der Wert der siebtgrößten Beobachtung entspricht dem Wert der viertkleinsten Beobachtung, da

ist.

Eine Excel-Formel für das Konfidenzniveau für den Mittelwert lässt sich über die Excel-Funktion TINV und die Excel-Formel für den Standardfehler aufstellen. Zu beachten ist, dass TINV(Wahrsch;Freiheitsgrade) eine modifizierte Umkehrfunktion ist.7 Sie liefert lediglich die positiven Quantile einer t-Verteilung mit 0<Wahrsch<1. So ist zur Berechnung des -Quantils der t-Verteilung für das Argument Wahrsch der Excel-Funktion TINV das Signifikanzniveau  einzusetzen. Die Grenzen des zugehörigen Konfidenzintervalls ergeben sich aus der Subtraktion bzw. Addition von Stichprobenmittel und Konfidenzniveau. Die Länge des Konfidenzintervalls entspricht dem Zweifachen des Konfidenzniveaus. Ferner ist zu beachten, dass dieses Konfidenzniveau nicht mit der Excel-Funktion KONFIDENZ äquivalent ist, da bei dieser Excel-Funktion die Varianz als bekannt vorausgesetzt  und somit eine -Verteilung zugrundegelegt wird.8 Weitere Informationen zum Thema t-Verteilung und den Excel-Funktionen TVERT und TINV erhalten Sie im Artikel [Link].

 

1 Vgl. Excel-Hilfe oder Office-Online-Hilfe: Ausführen statistischer und technischer Analysen mit den Analyse-Funktionen.
2 Diese Auffassung stützt sich darauf, dass erwartungstreue Schätzer für die Momente sowie ein Konfidenzniveau für den Mittelwert bei unbekannter Varianz  berechnet werden. Die Begrifflichkeiten 'Populationskenngrößen' und 'Descriptive Statistics' werden an dieser Stelle nicht diskutiert.
3 Eine automatische Aktualisierung des Outputs der Analyse-Funktion Populationskenngrößen kann jedoch mit Hilfe eines Makros erzielt werden.
4 Dies impliziert, dass im Output nur die Kenngrößen sinnvoll interpretiert werden können, die für das Skalenniveau des zugrunde liegenden Merkmals zulässig sind.
5 Liegen bspw. drei Merkmale in den Spalten A:C vor, so können zwar die Merkmale in den Spalten A:B, A:C und B:C gleichzeitig ausgewählt werden, jedoch nicht die Merkmale in den Spalten A und C.
6 Es lassen sich darüber hinaus Namen definieren, die Antwortausfälle berücksichtigen.
7 Vgl. Microsoft (2007): Excel statistical functions: TINV, Article ID: 828340, Revision: 2.2.
8 Vgl. Microsoft (2005): Statistical Functions Are Different from Descriptive Statistics, Article ID: 179121, Revision: 1.3.

 

Haben Sie Fragen oder Anregungen zu diesem Artikel?

Dann senden Sie einfach eine Nachricht an:

E-Mail:

 

Lesen Sie auch:
___________________________________________________________________

Excel-Formeln für die Analyse-Funktion Rang und Quantil