Datenanalyse mit Excel: Deskriptive Statistik automatisieren 5

Excel und Statistik. Zwei Begriffe, die bei vielen Menschen vermutlich vor lauter Begeisterung die Fußnägel kräuseln lassen. Andere wiederum können sich nichts Schöneres vorstellen… Da meine Kenntnisse auf dem Gebiet der Statistik vorsichtig formuliert sehr überschaubar sind, kommt ein Gastautor zu Wort, der wesentlich mehr Erfahrung mitbringt.

Robert Grünwald von NOVUSTAT hat schon ein paar mal hier im Blog Statistik-Artikel veröffentlicht und ist daher vielleicht dem einen oder anderen Leser schon bekannt. Er stellt heute ein kleines Tool vor, mit dem sich bestimmte statistische Analysen in Excel vereinfachen lassen.

Vorhang auf!

Um Statistik kommt keiner rum. Sei es, dass man mal kurz den Mittelwert angegeben oder die Häufigkeiten ausrechnen muss. Dabei steckt kein großer Hokuspokus hinter den Berechnungen. Es ist durchaus möglich, mit Excel Datenanalyse durchzuführen.

Mit dem Gastartikel wollen wir dir einfach und verständlich zeigen, welche statistische Auswertung Excel für welche Arten von Variablen anwenden kann. Du erfährst außerdem, wie die Berechnungen in einem selbsterstellten Excel Analyse Tool durchgeführt werden. Am Ende erhältst du ein Tabellenblatt, das dir automatisiert die wichtigsten Kennzahlen ausgibt. Natürlich erfährst du zudem, was die Kennwerte aus der Datenanalyse Excel bedeuten (die Beispieldatei kannst du dir hier herunterladen).

Variablen vs. Spalten: Die Dateneingabe für die Excel Datenanalyse

Bei der Dateneingabe in Excel hast du grundsätzlich sehr große Freiheiten. Damit man die statistische Auswertung Excel ohne Probleme durchführen kann, sollte man aber ein paar Grundregeln befolgen.

• Jede Spalte in Excel steht für genau eine Variable oder ein Merkmal (z. B. Geburtsdatum, Geschlecht, Größe, …)
• Jede Zeile des Tabellenblattes beinhaltet die Infos von einer Beobachtungseinheit (z. B. Patient, Proband, Experiment, …)
• In den ersten Zeilen der Datenmatrix ist der eindeutige Name der jeweiligen Variable angegeben.
• Alle Ausprägungen einer Variable (Spalteneinträge) besitzen das gleiche Format, bzw. den gleichen Datentyp und die gleiche Einheit.

Sofern du für das Daten-Analysieren Excel ausschließlich verwendest, reichen diese Grundregeln aus. Wenn die Daten von anderen Programmen weiterverarbeitet werden müssen, musst du noch einige andere Regeln beachten, damit der Export problemfrei klappt (s. auch https://www.tabellenexperte.de/konvertierung-von-excel-zu-spss/).

Datenbeispiel

Im Datenbeispiel werden das Geschlecht und die Größe von 18 Kursteilnehmern erfasst. In der ersten Zeile sind die Variablennamen angegeben, ab Zeile 2 beginnen die Daten der Teilnehmer. Jedem Kursteilnehmer ist eine Zeile zugeordnet. Die Geschlechter werden als m (männlich), w (weiblich), d (divers) und unbek (unbekannt) erfasst. Dabei ist wichtig, dass die Codierung einheitlich durchgeführt wird. Alle Männer haben in der Spalte A die Ausprägung „m“ usw. Es hat sich bewährt, für fehlende Angaben einen Code zu verwenden. Dadurch erkennt man versehentliche Löschungen sehr schnell und erspart sich mühsame Recherche im Nachhinein.

Das Merkmal Größe ist in Spalte B eingegeben. Alle Ausprägungen liegen hier als Zahlenwert, zum Teil mit Kommazahl, in cm vor.

Quantitative und qualitative Variablen

Die beiden Variablen Geschlecht und Größe im Datenbeispiel unterscheiden sich voneinander hinsichtlich ihres Informationsgehaltes. Für die korrekte Excel Datenanalyse und Anwendung der statistischen Funktionen ist eine Unterscheidung zwischen qualitativen und quantitativen Variablen notwendig.

• Qualitative Variablen (z. B. Geschlecht): Die Ausprägungen (Zelleinträge) dieses Variablentyps sind Kategorien oder Gruppen. Es kann sein, dass diese Gruppen geordnet werden können (z. B. Noten), dies ist aber nicht zwangsläufig notwendig. Die Gruppen werden meist kodiert, d. h. mit Kürzeln eingetragen. Diese Codes können auch Zahlen sein (z. B. 1 = männlich). Qualitative Variablen werden synonym als kategoriale Merkmale bezeichnet.
• Quantitative Variablen (z. B. Größe): Die Ausprägungen (Zelleinträge) dieses Variablentyps sind Zahlen und können prinzipiell beliebig viele Ausprägungen annehmen. Kommazahlen sind möglich, aber nicht notwendig.

Diese Unterscheidung genügt, um festzustellen, welche statistischen Kennzahlen man im Excel Analyse Tool berechnen kann.

Datenanalyse mit Excel: Auswertung qualitativer Variablen

Für die Beschreibung qualitativer Variablen werden absolute und relative Häufigkeiten angegeben. Dabei zählt man, wie oft jede Kategorie im Datensatz vorkommt. Die absoluten Häufigkeiten geben ganze Zahlen aus, relative Häufigkeiten Prozentwerte bezogen auf die Gesamtzahl.

Die Auszählungstabelle wird in drei Schritten erstellt:

1. Zunächst ermittelt man die verschiedenen Kategorien.
2. Danach bestimmt man die absoluten Häufigkeiten.
3. Zuletzt berechnet man die relativen Häufigkeiten.

Als Grundlage dient dabei folgendes Tabellenblatt:

Auswertung qualitativer Daten: Häufigkeitstabelle

Kategorien ermitteln

Bevor Excel zählen kann, müssen die verschiedenen Ausprägungen, d. h.  alle möglichen Kategorien, angegeben werden. Dies kann man manuell durchführen. Bei vielen Beobachtungen, sprich vielen Zeilen, ist das aber oft mühsam und unübersichtlich. Eine elegantere Lösung ist die Anwendung einer (geschachtelten) Funktion.

In Zelle G2 gibt man folgende Formel ein:
=WENNFEHLER(INDEX(A$2:A$1000;VERGLEICH(1;(ZÄHLENWENN(G$1:G1;A$2:A$1000)=0)*(A$2:A$1000<>"");0));"")

Da es sich um eine Matrix Funktion handelt, muss diese Formel mit Strg+Umschalt+Eingabe oder Befehlstaste + Eingabe (Mac) abgeschlossen werden.

Daraufhin wird die Formel von Excel automatisch in geschweifte Klammern gesetzt und die erste vorkommende Kategorie wird angegeben.

Um die weiteren Kategorien angezeigt zu bekommen, kopiert man G2 in die Zellen G3 bis G19. So bekommt man maximal 19 verschiedenen Kategorien aufgelistet. Im vorliegenden Beispiel werden die 4 Kategorien „m“, „w“, „d“ und „unbek“ angegeben, alle restlichen Zellen bleiben leer.

Zur Formel: Sicherlich möchtest du wissen, was hinter der Formel steckt. Eine ausführliche Erklärung findest du im Blogartikel https://www.tabellenexperte.de/liste-nur-mit-eindeutigen-werten-erstellen/.
 

Berechnung der absoluten Häufigkeiten

Zur Berechnung der absoluten Häufigkeiten bietet sich die Funktion
=ZÄHLENWENN(Bereich; Kriterium)

In einem definierten Bereich wird gezählt, wie oft das Kriterium vorkommt. In obigem Datenbeispiel kannst du die absoluten Häufigkeiten in Zelle H2 berechnen mit
=ZÄHLENWENN(A$2:A$100;G2)

ZÄHLENWENN-Funktion zur Auszählung festgelegter Kategorien

Die Formel kann durch kopieren in die anderen Zellen erhältst du die Häufigkeitsauszählung für alle Kategorien.

WICHTIG: Damit beim Kopieren die Zellbezüge erhalten bleiben müssen absolute Bezüge mit Dollarzeichen für die Bereichsauswahl benutzt werden.
 

Berechnung der relativen Häufigkeiten

Für die Berechnung der relativen Häufigkeiten in der Spalte I wird zunächst die Gesamtzahl der Beobachtungen errechnet. Dafür werden in Zelle H20 die absoluten Häufigkeiten summiert mit der Funktion
=SUMME(H2:H19)

Die relativen Häufigkeiten für Zelle I2 ergeben sich als Quotient der absoluten Häufigkeit und der Gesamtzahl als
=H2/H$20

Wiederum benötigt man die absoluten Bezüge mit dem vorgestellten Dollarzeichen, um beim Kopieren in die Zellen I3-I19 den Nenner konstant auf der Zelle I20 zu halten. Die errechneten Werte lassen sich dann im Nachhinein über Format – Zelle – Zahlen als Prozent formatieren.

Statistische Auswertung quantitativer Variablen

Für die Beschreibung quantitativer Merkmale steht eine Vielzahl von Maßzahlen für die Datenanalyse in Excel zur Verfügung. Ziel der deskriptiven Zusammenfassung ist es, die oft unüberschaubare Datenmenge mit wenigen, charakteristischen Kennzahlen zu beschreiben. Dabei unterscheidet man grob zwischen Lagemaßen, Streuungsmaßen und Formmaßen.
 

Lagemaße

Lagemaße geben charakteristische Kennpunkte der Datenverteilung an. Die wichtigsten Lagemaße und die zugehörigen Funktionsaufrufe für die Datenanalyse Excel sind:

• Mittelwert (auch arithmetisches Mittel genannt, Durchschnitt): gibt an, welche Ausprägung die Beobachtungen hätten, falls die Variable für alle Beobachtungen den gleichen Wert annimmt.

Funktion: MITTELWERT()

• Median: gibt den Wert an, für den gleich viele Beobachtungen größer bzw. kleiner sind.

Funktion: MEDIAN()

• Minimum: kleinster vorkommender Wert

Funktion: MIN()

• Maximum: größter vorkommender Wert

Funktion: MAX()

• Quantile: gibt den Wert an, für den ein bestimmter Prozentsatz an Beobachtungen kleiner sind. Der Median kann als 50% Quantil angesehen werden. Wichtige Quantile sind 25% und 75% Quantil, die sogenannten Quartile.

Funktion: QUANTIL()

Meist werden mehrere Parameter benötigt, um sich ein Bild von der Datenstruktur zu machen.
 

Streuungsmaße

Neben der zentralen Tendenz der Daten ist es wichtig, auch noch Streuungsmaße zu berücksichtigen. Streuungsmaße geben an, wie breit „gestreut“ die Daten sind. Gebräuchliche Streuungsmaße, die im Excel Analyse Tool Verwendung finden, sind:

• Varianz: Die Varianz gibt den mittleren (oder durchschnittlichen) quadrierten Abstand vom Mittelwert an. Das Quadrat stört etwas in der Interpretation, ist aber für die Berechnung notwendig, da sich bei der Abstandsbildung zum Mittelwert sonst positive und negative Differenzen aufheben würden.

Funktion: VAR.S()

• Standardabweichung: Die Standardabweichung ist die Quadratwurzel aus der Varianz. Die Standardabweichung hat die gleiche Einheit wie die Variable selbst . Sie kann als durchschnittliche Entfernung der Ausprägungen vom Mittelwert interpretiert werden.

Funktion: STABW()

• Standardfehler: Der Standardfehler wird manchmal irrtümlicherweise mit der Standardabweichung gleichgesetzt, beinhaltet allerdings komplett andere Informationen. Der Standardfehler beschreibt die Variabilität des Mitteilwertes: Wenn die Stichprobe nochmals so gezogen wird (18 Größenmessungen unter den gleichen Bedingungen), dann würde man erwarten, dass der Mittelwert um den Wert des Standardfehlers schwankt. Da Mittelwerte ja aus vielen Einzelwerten gebildet werden, ist der Mittelwert viel stabiler und der Standardfehler wesentlich kleiner als die Standardabweichung.

Funktion: MITTELABW()

• Spannweite: Die Spannweite gibt den Abstand zwischen größten und kleinsten Beobachtungswert an. Dieser Wert ist sehr anfällig gegenüber Ausreißern oder extremen Werten.

Funktion: MAX() – MIN()

• Interquartilsabstand: Der Interquartilsabstand gibt den Abstand zwischen 25% Quantil und 75% Quantil an.

Funktion: QUANTIL(  ;0,75) – QUANTIL (  ;0,25)

ACHTUNG: Für die Berechnung der Varianz und der Standardabweichung stehen 2 Funktionen zur Verfügung. Hat man eine Stichprobe vorliegen, muss man die Funktion VAR.S() bzw. STABW() anwenden, um korrekte Schätzungen zu erhalten.
 

Formparameter

Parameter der Form geben an, welche charakteristische Gestalt die Verteilung der Daten besitzt. Hierbei haben sich Schiefe und Kurtosis als Maßzahlen etabliert.

• Schiefe: Bei linksschiefen Verteilungen liegen viele große Werte und wenig kleine Werte vor. Rechtsschiefe Verteilungen dagegen besitzen viele kleine und wenig hohe Werte. Bei einer symmetrischen Verteilung nimmt die Schiefe einen Wert von 0 an. Rechtsschiefe Verteilungen besitzen ein positives, linksschiefe ein negatives Vorzeichen.

Funktion: SCHIEFE()

Schiefe

• Kurtosis: Die Kurtosis gibt an, wie spitz oder flach eine Verteilung ist. Die Maßzahl ist immer positiv. Je geringer die Kurtosis ist, umso flachgipfliger ist die Verteilung der Daten. Die Normalverteilung hat eine Kurtosis von 3.

Funktion: KURT()

Kurtosis

Das Excel Analyse Tool: Automatisierte Ausgabe der Kennzahlen

Jede der Maßzahlen deckt einen anderen Gesichtspunkt der Datenverteilung ab. Erst die Gesamtheit der Maßzahlen erlaubt ein differenziertes Bild der Daten. Eine Übersicht über die deskriptiven Kenngrößen eines quantitativen Merkmals kann man in Form eines Excel Analyse Tools wie folgt erstellen:

Das Excel Analyse Tool für quantitative Variablen

Kopiert man Daten in Spalte A, so werden die Maßzahlen automatisch aktualisiert. Dazu ist es notwendig, in der Excel Datenanalyse den Zellbezug mit A:A über alle Zeilen der Spalte A festzusetzen.

Deskriptive Datenanalyse mit Excel: Fazit

Excel bietet als Allround-Programm einen großen Funktionsumfang zur Beschreibung von Daten. Deskriptive Datenanalyse mit Excel erfordert eine Vielzahl unterschiedlicher Funktionen. Die einzelnen Funktionen müssen per Hand eingegeben und für verschiedene Datenbereiche angepasst werden. Dies erfordert methodisches Hintergrundwissen. Insbesondere die Berechnung quantitativer Maßzahlen wie z. B. Mittelwert oder Standardabweichung bei zahlenkodierten kategoriellen Merkmalen wird ohne weitere Warnung in der Excel Datenanalyse durchgeführt. Oder, um es mit Martin Weiß zu sagen: „Das Problem sitzt meistens vor dem Computer“ – du musst wissen, was du machst.

Das Excel Analyse Tool stellt die wichtigsten Maßzahlen für quantitiative und qualitative Merkmale zur Verfügung, das durch Kopieren neuer Variablen die Maßzahlen automatisch berechnet.

Wird für eine differenzierte statistische Auswertungen Excel herangezogen, beispielsweise die Berechnung deskriptiver Kenngrößen für verschiedene Gruppen, so ist die manuelle Berechnung per Excel oft mühsam. Die Daten können dann exportiert werden und mit Statistikprogrammen, wie SPSS oder R weiter analysiert werden. Dies ist auch dann sinnvoll, wenn der Auswertungsbedarf über den Funktionsumfang der Excel Datenanalyse hinaus geht.  In diesem Zusammenhang ist es oftmals empfehlenswert, eine professionelle statistische Beratung hinzuzuziehen.

Das könnte dich auch interessieren:

Wenig(er) bekannte Datumsfunktionen

Wieviele sind das eigentlich?

Das Schweizer Taschenmesser in Excel: SVERWEIS!

Links, rechts, vor, zurück…