Table Of ContentWolf-Gert Matthäus, Jörg Schulze
Statistik mit Excel
Wolf-Gert Matthäus, Jörg Schulze
Statistik mit Excel
Beschreibende Statistik
für jedermann
Im
Teubner
B. G. Teubner Stuttgart· Leipzig' Wiesbaden
Bibliografische Information der Deutschen Bibliothek
Die Deutsche Bibliothek verzeichnet diese Publikation in der Deutschen Nationalbibliographie;
detaillierte bibliografische Daten sind im Internet über <http://dnb.ddb.de> abrufbar.
1. Auflage April 2003
Alle Rechte vorbehalten
© B. G.T eubner / GWV Fachverlage GmbH, Wiesbaden 2003
Der B. G.T eubner Verlag ist ein Unternehmen der Fachverlagsgruppe BertelsmannSpringer.
www.teubner.de
Die Wiedergabe von Gebrauchsnamen, Handelsnamen, Warenbezeichnungen usw. in diesem Werk
berechtigt auch ohne besondere Kennzeichnung nicht zu der Annahme, dass solche Namen im Sinne
der Warenzeichen- und Markenschutz-Gesetzgebung als frei zu betrachten wären und daher von
jedermann benutzt werden dürften.
Umschlaggestaltung: Ulrike Weigel, www.CorporateDesignGroup.de
Gedruckt auf säurefreiem und chlorfrei gebleichtem Papier.
ISBN 978-3-519-00424-0 ISBN 978-3-322-94879-3 (eBook)
DOI 10.1007/978-3-322-94879-3
Vorwort
Da wurde nun mit großem Fleiß in wochenlanger Arbeit eine große Datenmenge
aus Beobachtungen, Messungen, Befragungen, Untersuchungen, Analysen oder
anderen Quellen zusammengetragen. Und dann steht man mehr oder weniger hilf
los vor diesem Datenhaufen und fragt sich:
Wie weiter? Was nun? Was tun?
Wie bekommt man denn nun die Daten in den pe? Wie können sie dort verwaltet,
aufbereitet, ausgewertet, komprimiert, präsentiert und wirksam grafisch darge
stellt werden?
Ob im Studium, in der Schule, im Labor oder in der beruflichen Praxis - hier wird
das vorliegende Buch helfen: Wir wollen von der Datenerfassung bis hin zur Ge
winnung aussagekräftiger Grafiken und statistischer Kennzahlen, verständlich und
stets unmittelbar nachvollziehbar, den Umgang mit umfangreichem Datenmaterial
schildern, auf Fallen verweisen, auch manch kleinen Trick präsentieren.
Und all das basiert auf Excel, dem bewährten und beliebten Office-Programm, mit
dem umzugehen wohl niemand an den oben genannten Arbeitsplätzen heutzutage
irgendwelche grundlegenden Schwierigkeiten haben dürfte. Mehr als Excel
Grundkenntnisse werden für das Buch ohnehin nicht benötigt.
Die Idee, ein solches Buch zu schreiben, wurde aus vielen Quellen gespeist. Wäh
rend der eine Autor vor allem an seine Scharen von Studierenden dachte, erinnerte
sich der andere zusätzlich noch an die unzähligen Konsultationen und Lehrgänge,
die er zu diesem Thema schon durchführte.
Insbesondere erinnerte er sich an einen Studenten der Journalistik, der in seiner
Diplomarbeit mit riesigem Fleiß die Sportberichterstattung großer Zeitungen über
einen beachtlichen Zeitraum gezielt qualitativ und quantitativ auswerten wollte.
Und der - natürlich - zur Datenerfassung, Datenanalyse, Datenaufbereitung und
Datenpräsentation sein geliebtes Excel nutzen wollte. Seine Anfragen standen Pa
te bei einigen Kapitelüberschriften.
Der ergänzende Untertitel "Beschreibende Statistik für jedermann" signalisiert:
Wir wenden uns eben nicht an die Statistik-Spezialisten, sondern vor allem an
AnHinger und Einsteiger. Ihnen wollen wir wirksam helfen.
Und diese Vokabel ,Jedermann", unsere erwünschte Leserschar charakterisierend,
begleitete uns auf dem gesamten Weg der Manuskripterarbeitung.
6 Vorwort
Wenn die Leitungen zwischen der Altmark und dem Zittauer Gebirge glühten und
wieder in heftiger Diskussion zu entscheiden war, ob dieses oder jenes noch hin
zugenommen werden sollte, ob man hier vertiefen oder dort auf die Hintergründe
eingehen sollte, ob man Beispielrechnungen mit statistischen Formeln aufnimmt
oder dafiir lieber noch weitere Excel-Tabellen mit Daten und Formeln und Funk
tionen darstellt - immer dann mischte sich dieses ,jedermann" mit einem großen
Ausrufungszeichen in das Gespräch und erinnerte uns daran, dass wir für alle eine
leicht lesbare und praxisverbundene Handreichung vorlegen wollten.
Deshalb haben wir auf Formeln nahezu völlig verzichtet. Dafiir sind unsere Excel
Beispiele stets so gewählt, dass man sie sofort nachvollziehen kann; wir sind da
rur auch auf Dinge eingegangen, über die der Excel-Kundige nur müde lächelt.
Unser Buch soll eben von jedermann verstanden werden können.
Für Rückmeldungen und kritische Hinweise sind wir sehr dankbar. Besuchen Sie
uns doch gelegentlich im Internet unter \Vww.w-g-m.de, liebe Leserin, lieber
Leser, und sagen Sie uns Ihre Meinung.
Abschließend möchten wir allen, die uns während der Zeit der Manuskripterarbei
tung unterstützten und viel Verständnis entgegenbrachten, in erster Linie waren
das natürlich unsere lieben Frauen, ganz herzlich danken.
Dem Teubner-Verlag und Herrn J. Weiß in Leipzig danken wir rur die Anregung
zu diesem Projekt und fiir die entgegenkommende, konstruktive Zusammenarbeit.
Uenglingen / Görlitz, im März 2003 Wolf-Gert Matthäus
Jörg Schulze
Inhaltsverzeichnis
Einleitung ............................................................................................................. 11
1 Grundlagen .................................................................................................. 17
1.1 Statistische Grundbegriffe ......................................................................... 17
1.2 Datenniveau, Skalierung der Merkmale .................................................... 18
1.2.1 Kardinalskala (metrische Skala) ........................................................ 18
1.2.2 Ordinalskala ....................................................................................... 19
1.2.3 Nominalskala ..................................................................................... 19
1.3 Verarbeitungsmöglichkeiten von Daten .................................................... 20
1.4 Zahlen - die großen Verführer .................................................................. 21
2 Datenerfassung und -verw-altung ............................................................... 23
2.1 Mehrdimensionales Datenmaterial ............................................................ 23
2.1.1 Überschriften ..................................................................................... 23
2.1.2 Spalten mit Gesetzmäßigkeiten ......................................................... 28
2.1.3 Dateneingabe ..................................................................................... 29
2.2 Eindimensionales Datenmaterial ............................................................... 31
2.3 Umgang mit dem Datenmaterial ............................................................... 31
2.4 Kontrolle des Datenbestandes ................................................................... 34
2.4.1 Nützliche Auskunftsfunktionen ......................................................... 34
2.4.2 Erfasste Merkmalswerte und das Filtern ........................................... 35
2.4.3 Prüfen von Minimum und Maximum ................................................ 39
3 Absolute Häufigkeiten ................................................................................. 41
3.1 Einfaches Abzählen mit ZÄHLENWENN ( • ; .) ...................................... .41
3.2 Klassenbildung .......................................................................................... 49
3.2.1 Klassenzuordnung mit der Funktion WENN ( • ; • ; • ) ...................... 50
3.2.2 Die Funktion VERWEIS (.; • ; • ) ................................................... 53
3.2.3 Die Funktion HÄUFIGKEIT (. ; • ) ................................................. 54
3.2.4 Das Werkzeug Histogramm .............................................................. 54
3.3 Modalwert .................................................................................................. 57
8 Inhaltsverzeichnis
3.4 Häufigkeiten von Paaren: Pivot-Tabellen .................................................. 59
3.4.1 Aufgabenstellung und Bezeichnungen .............................................. 59
3.4.2 Herstellung einer einfachen Pivot-Tabelle ........................................ 60
3.4.3 Feldeinstellungen ............................................................................... 63
3.4.4 Präsentation von Pivot-Tabellen ........................................................ 64
3.4.5 Abzählen mittels Pivot-Tabellen ....................................................... 65
3.4.6 Variable Pivot-Tabellen ..................................................................... 67
3.4.7 Pivot-Tabellen mit Datumsangaben .................................................. 69
3.5 Absolute Summenhäufigkeiten ................................................................. 74
4 Grafische Darstellungen ............................................................................. 75
4.1 Histogramme: Werkzeug HISTOGRAMM .............................................. 75
4.2 Die [IITJ-Methode zur Herstellung von Grafiken .................................... 78
4.2.1 Zusammenhängende Daten. ............................................................... 78
4.2.2 Nicht zusammenhängende Daten ...................................................... 82
4.3 Zeitreihen ................................................................................................... 83
4.4 Skalierung .................................................................................................. 88
4.4.1 Bedeutung .......................................................................................... 88
4.4.2 Logarithmische Skalierung ................................................................ 90
4.5 Grafische Darstellung von Pivot-Tabellen ................................................ 92
4.6 Darstellung von absoluten Summenhäufigkeiten .......................................... 94
4.6.1 Bereits berechnete Summenhäufigkeiten .......................................... 94
4.6.2 100-Prozent-Diagramme ................................................................... 95
4.6.3 100-Prozent-Diagramme im Zeitablauf.. ........................................... 97
5 Relative Häufigkeiten, empirische Verteilung .......................................... 99
5.1 Berechnung relativer Häufigkeiten ............................................................ 99
5.2 Excel rechnet falsch !? ............................................................................ 10 2
5.3 Kumulierte relative Häufigkeiten ............................................................ 108
6 Statistische Maßzahlen .............................................................................. 113
6.1 Mittelwerte ............................................................................................... 113
6.1.1 Median und arithmetisches Mittel (Durchschnitt) ............................ 113
6.1.2 Weitere Mittelwerte ........................................................................... 117
Inhaltsverzeichnis 9
6.2 Streuungsmaße ......................................................................................... 118
6.2.1 Spannweite ........................................................................................ 118
6.2.2 Mittlere absolute Abweichung .......................................................... 118
6.2.3 Empirische Standardabweichung und Varianz ................................. 119
6.2.4 Einschub: Normalverteilung ............................................................. 120
6.3 Schiefe und Wölbung ............................................................................... 124
6.4 Die Verführung: Populationskenngrößen ................................................. 126
7 Korrelation und Regression ....................................................................... 131
7.1 Der Korrelationskoeffizient von Bravais-Pearson ....................................... 131
7.1.1 Begriff, Bedeutung, Berechnung ....................................................... 131
7.1.2 Herstellung der Punktwolke .............................................................. 134
7.2 Regression ................................................................................................ 136
7.2.1 Berechnung der Regressionskonstanten ............................................ 136
7.2.2 Rechnung mit dem Werkzeug Regression ........................................ 138
7.3 Nichtlineare Zusammenhänge .................................................................. 142
7.3.1 Grundlagen ........................................................................................ 142
7.3.2 Transformationen .............................................................................. 146
8 Zeitreihen ................................................................................................... 149
8.1 Begriff und Erfassung ............................................................................... 149
8.2 Experimentell-visuelle Trendermittlung. .................................................. 150
8.3 Lineare Zeitreihen und die TREND-Funktion ........................................... 156
8.4 Gleitender Durchschnitt ............................................................................ 158
Anhang: Vom Problem zur Excel-Lösung ...................................................... 161
Literatur ............................................................................................................. 169
Sachwortveneiehnis .......................................................................................... 171
Einleitung
Die Leserschar dieses Buches möge uns verzeihen, dass wir bewusst darauf ver
zichten, ausfiihrlichst noch einmal zu erklären, was eine Excel-Mappe ist, dass sie
Tabellen enthält, die ihrerseits aus Zeilen und Spalten bestehen, womit die Zellen
gebildet werden, in die man numerische Werte und nichtnumerische Werte eintra
genkann.
Wichtig erscheinen uns dagegen einige einfiihrende Festlegungen und Vereinba
rungen.
Beschreibung von Bereichen
Zusammenhängende Excel-Bereiche werden, wie allgemein üblich, durch Angabe
der beiden Randzellen, abgetrennt durch einen Doppelpunkt, beschrieben.
• Handelt es sich um einen echten Rechteckbereich, dann sind die beiden Zellen
links oben und rechts unten anzugeben (Beispiel: A1 :54).
• Handelt es sich um eine ganze Zeile, dann wird zweimal die Zeilennummer
angegeben (3. Zeile 3:3).
• Handelt es sich um einen Zeilenbereich (d.h. um den Teil einer Zeile), dann
sind die beiden Rand-Zellen links und rechts anzugeben (C2:BK2).
• Handelt es sich um eine ganze Spalte, dann wird zweimal der Spaltenbuch
stabe bzw. zweimal das Buchstabenpaar der Spalte angegeben (3. Spalte C:C,
28. Spalte AB:AB).
• Handelt es sich um einen Spaltenbereich (d.h. um den Teil einer Spalte), dann
sind die beiden Rand-Zellen oben und unten anzugeben (CA10:CA1000).
Beschreibung von Bedienhandlungen
Zur Beschreibung von Bedienhandlungen, bei denen nacheinander verschiedene
Menüpunkte von Excel ausgewählt werden müssen, werden Darstellungen mit
Kapitälchen und Pfeilen benutzt.
So wird zum Beispiel beschrieben, wie man veranlassen kann, dass der numeri
sche Inhalt einer Zelle auf vier Dezimalstellen nach dem Komma gerundet ange
zeigt wird:
FORMAT ~ ZELLEN ... ~ ZAHLEN ~ ZAHL ~ DEZIMALSTELLEN ~ 4.
W.-G. Matthäus et al., Statistik mit Excel
© B. G. Teubner / GWV Fachverlage GmbH, Wiesbaden 2003
Kopieren von Formeln
Es ist ein scheinbar kleines Problem, aber bewegt doch viele, die mit Datenmen
gen in Excel-Tabellen umgehen müssen.
Wie erzeugt man Dutzende, Hunderte, Tausende von Formeln? Wie muss man
beispielsweise vorgehen, damit nach dem Eintrag der ersten Kodierungsformel in
die Zelle C2 in allen darunter liegenden Zellen C3 bis Ci 001 die jeweils richtige
Formel erscheint, wie in der Beispieltabelle zu sehen?
Wir werden die Aufforderung "Kopiere die Formel aus der Zelle C2 sachlich
richtig in den Bereich C3:C1001" erst einmal formalisieren, indem wir in den
Ziel bereich lediglich das Kommando Paste C2 eintragen
Doch damit ist das Problem nicht gelöst - wie muss man denn nun praktisch vor
gehen, um die gewünschten 999 Formeln zu erzeugen?
Die ersten beiden Methoden sind wohl allgemein bekannt:
I
• Methode 1: Man wählt C2, dann BEARBEITEN ~ KOPIErRnEN Odal Strg + ~ ,
markiert anschließend entweder mit der Maus oder mit und -J, den Bereich
von C3 bis zur letzten Zeile des Datenbestandes. Dann folgt BEARBEITEN ~
QJ .
EINFÜGEN, oder man drückt ~ + ~ , oder einfach nur die Enter-Taste
Dazu braucht man viel Geduld ...
