Excel Datenanalyse für Dummies (E-Book) von Stephen L. Nelson

Einführung 21

Über dieses Buch 21

Was Sie problemlos ignorieren können 21

Was Sie nicht ignorieren sollten (es sei denn, Sie sind ein Masochist) 22

Törichte Annahmen über den Leser 22

Wie dieses Buch aufgebaut ist 23

Icons in diesem Buch 24

Beispieldateien zum Buch 24

Wie es weitergeht 24

Teil I Erste Schritte bei der Datenanalyse 25

Kapitel 1 Grundlagen von Excel-Tabellen 27

Was ist eine Tabelle und was hat sie mit mir zu tun? 27

Tabellen erstellen 30

Export aus einer Datenbank 30

Eine Tabelle auf die harte Tour erstellen 30

Eine Tabelle auf die weniger harte Tour erstellen 30

Daten in Tabellen analysieren 34

Einfache statistische Auswertungen 34

Tabellendaten sortieren 36

Eine Tabelle mit einem AutoFilter filtern 38

Filter entfernen 41

Den AutoFilter deaktivieren 41

Einen benutzerdefinierten AutoFilter verwenden 41

Eine gefilterte Tabelle filtern 44

Spezialfilter verwenden 44

Kapitel 2 Daten aus externen Quellen abrufen 49

Die Daten über die Variante Export/Import erhalten 49

Exportieren: Der erste Schritt 49

Importieren: Der zweite Schritt (falls erforderlich) 55

Externe Datenbanken und Tabellen auf Webseiten abrufen 64

Eine Webabfrage verwenden 64

Eine Datenbanktabelle importieren 67

Eine externe Datenbank abfragen 69

Die rohen Daten kochen 76

Kapitel 3 Daten säubern 77

Ihre importierte Arbeitsmappe bearbeiten 77

Unnötige Spalten löschen 77

Unnötige Zeilen löschen 78

Spaltenbreite anpassen 78

Zeilenhöhe anpassen 81

Nicht benötigte Zellinhalte löschen 81

Numerische Werte formatieren 82

Arbeitsblattdaten kopieren 83

Arbeitsblattdaten verschieben 83

Daten in Feldern ersetzen 83

Daten mit den Textfunktionen aufräumen 84

Na, und? 84

Die Antwort auf einige Ihrer Probleme 86

Die Funktion ERSETZEN 86

Die Funktion FEST 87

Die Funktion FINDEN 87

Die Funktion GLÄTTEN 88

Die Funktion GROSS 88

Die Funktion GROSS2 88

Die Funktion IDENTISCH 89

Die Funktion KLEIN 89

Die Funktion LÄNGE 89

Die Funktion LINKS 90

Die Funktion RECHTS 90

Die Funktion SÄUBERN 90

Die Funktion SUCHEN 91

Die Funktion T 91

Die Funktion TEIL 91

Die Funktion TEXT 92

Die Funktion VERKETTEN 92

Die Funktion WECHSELN 93

Die Funktion WERT 94

Die Funktion WIEDERHOLEN 94

Die Ergebnisse der Textfunktionen in Text konvertieren 94

Mit Gültigkeitsprüfungen für korrekte Daten sorgen 95

Teil II PivotTables und PivotCharts 99

Kapitel 4 PivotTables verwenden 101

Aus unterschiedlichen Perspektiven einen Blick auf die Daten werfen 101

Vorbereitungen für das Pivotieren 102

Den PivotTable-Assistenten verwenden 103

Mit der PivotTable herumspielen 109

Pivotieren und erneut pivotieren 109

PivotTable-Daten filtern 110

Einen Datenschnitt oder eine Zeitachse verwenden 112

PivotTable-Daten aktualisieren 113

PivotTable-Daten sortieren 114

Pseudosortierung 116

Datenelemente gruppieren und deren Gruppierung aufheben 117

Dies auswählen, jenes auswählen 119

Wo kommen die Zahlen dieser Zelle her? 119

Die Wertfeldeinstellungen festlegen 120

Festlegen, wie PivotTables aussehen und funktionieren 122

PivotTable-Optionen konfigurieren 122

Kapitel 5 PivotTable-Formeln erstellen 133

Eine weitere Standardberechnung hinzufügen 133

Berechnungsoptionen erstellen 137

Berechnete Felder und Elemente verwenden 142

Ein berechnetes Feld einfügen 142

Ein berechnetes Element hinzufügen 145

Berechnete Felder und Elemente entfernen 148

Die Formeln für berechnete Felder und Elemente überprüfen 149

Die Lösungsreihenfolge ansehen und ändern 150

Daten aus einer PivotTable abrufen 152

Alle Werte einer PivotTable abrufen 152

Einen Wert aus einer PivotTable abrufen 153

Argumente der Funktion PIVOTDATENZUORDNEN 154

Kapitel 6 PivotCharts verwenden 157

Warum sollte ich ein PivotChart verwenden? 157

Vorbereitungen für das Pivotieren 158

Den PivotChart-Assistenten einsetzen 159

Mit dem PivotChart herumspielen 164

Pivotieren und erneut pivotieren 164

PivotChart-Daten filtern 165

PivotChart-Daten aktualisieren 168

Datenelemente gruppieren und deren Gruppierung aufheben 169

Mit den Diagrammbefehlen PivotCharts erstellen 171

Kapitel 7 PivotCharts anpassen 173

Einen Diagrammtyp wählen 173

Diagrammformatvorlagen verwenden 174

Das Diagrammlayout ändern 174

Diagrammtitel und Achsentitel 174

Diagrammlegende 177

Datenbeschriftungen 178

Diagrammdatentabelle 180

Diagrammachsen 181

Diagrammgitternetzlinien 183

Ein Diagramm verschieben 183

Die Zeichnungsfläche formatieren 185

Den Diagrammbereich formatieren 186

Füllmuster für das Diagramm 186

Diagrammschriften formatieren 187

3D-Diagramme formatieren 187

Die Wände eines 3D-Diagramms formatieren 187

Den Befehl »3D-Drehung« verwenden 187

Teil III Fortgeschrittene Werkzeuge 189

Kapitel 8 Die Datenbankfunktionen verwenden 191

Schneller Überblick zu Funktionen 191

Die Syntaxregeln von Funktionen 192

Eine Funktion manuell eingeben 192

Eine Funktion mit dem Funktionsassistenten eingeben 193

Die Funktion DBMITTELWERT verwenden 198

Die Funktionen DBANZAHL und DBANZAHL2 verwenden 201

Die Funktion DBAUSZUG verwenden 203

Die Funktionen DBMAX und DBMIN verwenden 205

Die Funktion DBPRODUKT verwenden 206

Die Funktionen DBSTDABW und DBSTDABWN verwenden 207

Die Funktion DBSUMME verwenden 209

Die Funktionen DBVARIANZ und DBVARIANZEN verwenden 210

Kapitel 9 Die statistischen Funktionen verwenden 213

Elemente eines Datasets zählen 213

ANZAHL: Zellen mit Werten zählen 213

ANZAHL2: Alternative Zählweise von Zellen mit Werten 215

ANZAHLLEEREZELLEN: Leere Zellen zählen 215

ZÄHLENWENN: Zellen zählen, die Bedingungen entsprechen 215

ZÄHLENWENNS: Zellen zählen, die Bedingungen entsprechen 216

VARIATIONEN und VARIATIONEN2: Permutationen zählen 216

KOMBINATIONEN: Kombinationen zählen 217

Mittelwert, Modus und Median 217

MITTELABW: Durchschnittliche absolute Abweichung 217

MITTELWERT: Arithmetisches Mittel 218

MITTELWERTA: Alternative Berechnung des Durchschnitts 218

MITTELWERTWENN und MITTELWERTWENNS: Wählerische Durchschnitte 219

GESTUTZTMITTEL: Den Mittelwert stutzen 220

MEDIAN: Den Median berechnen 221

MODUS: Den Modalwert berechnen 221

GEOMITTEL: Geometrisches Mittel 222

HARMITTEL: Harmonisches Mittel 222

Werte, Ränge und Quantile 222

MAX: Der größte Wert 223

MAX2: Alternative Berechnung des größten Werts 223

MIN: Der kleinste Wert 223

MIN2: Alternative Berechnung des kleinsten Werts 223

KGRÖSSTE: Den k-größten Wert ermitteln 224

KKLEINSTE: Den k-kleinsten Wert ermitteln 224

RANG, RANG.MITTELW und RANG.GLEICH: Den Rang eines Arraywerts bestimmen 224

QUANTILSRANG.EXKL und QUANTILSRANG.INKL: Den Quantilsrang bestimmen 226

QUANTIL.EXKL und QUANTIL.INKL: Einen bestimmten Quantilsrang finden 226

QUARTILE.EXKL und QUARTILE.INKL: Einen bestimmten Quartilsrang finden 227

HÄUFIGKEIT: Häufigkeitsverteilung ermitteln 228

WAHRSCHBEREICH: Wahrscheinlichkeit von Werten 230

Standardabweichungen und Varianzen 231

STABW.S: Standardabweichung einer Stichprobe 231

STABWA: Alternative Berechnung der Standardabweichung einer Stichprobe 232

STABW.N: Standardabweichung einer Grundgesamtheit 232

STABWNA: Alternative Berechnung der Standardabweichung einer Grundgesamtheit 233

VAR.S: Varianz einer Stichprobe 233

VARIANZA: Alternative Varianz 234

VAR.P: Varianz einer Grundgesamtheit 234

VARIANZENA: Alternative Berechnung der Varianz einer Grundgesamtheit 234

KOVARIANZ.P und KOVARIANZ.S: Kovarianzen 234

SUMQUADABW: Summe der quadrierten Abweichungen 235

Normalverteilungen 235

NORM.VERT: Wahrscheinlichkeit, dass X auf oder unter einen bestimmten Wert fällt 235

NORM.INV: X, das einer angegebenen Wahrscheinlichkeit entspricht 236

NORM.S.VERT: Wahrscheinlichkeit, dass eine Variable innerhalb von z-Standardabweichungen liegt 237

NORM.S.INV: Quantil ermitteln, das einer vorgegebenen Wahrscheinlichkeit entspricht 237

STANDARDISIERUNG: Z-Wert für einen angegebenen Wert 238

KONFIDENZ: Konfidenzintervall für den Mittelwert einer Grundgesamtheit 238

KURT: Kurtosis 239

SCHIEFE und SCHIEFE.P: Schiefe einer Verteilung 240

GAUSS: Wahrscheinlichkeit, dass ein Wert in einen Bereich fällt 240

PHI: Dichtefunktion einer Standardnormalverteilung 240

t-Verteilungen 241

T.VERT: Linksseitige Student-t-Verteilung 241

T.VERT.RE: Rechtsseitige t-Verteilung 241

T.VERT.2S: t-Verteilung für zwei Endflächen 242

T.INV: Linksseitiges Quantil der t-Verteilung 242

T.INV.2S: Zweiseitiges Quantil der t-Verteilung 242

T.TEST: Wahrscheinlichkeit, dass zwei Stichproben aus

Grundgesamtheiten mit demselben Mittelwert stammen 243

F-Verteilungen 243

F.VERT: Wahrscheinlichkeit der linksseitigen F-Verteilung 243

F.VERT.RE: Wahrscheinlichkeit der rechtsseitigen F-Verteilung 244

F.INV: Linksseitiger F-Wert für die Wahrscheinlichkeit einer F-Verteilung 244

F.INV.RE: Rechtsseitiger F-Wert für die Wahrscheinlichkeit einer F-Verteilung 244

F.TEST: Wahrscheinlichkeit, dass sich die Varianzen signifikant unterscheiden 245

Binomialverteilungen 245

BINOM.VERT: Wahrscheinlichkeit bei Binomialverteilungen 245

BINOM.INV: Wahrscheinlichkeit bei Binomialverteilungen 246

BINOM.VERT.BEREICH: Binomialwahrscheinlichkeit eines Versuchsergebnisses 246

NEGBINOM.VERT: Negative Binomialverteilung 247

KRITBINOM: Kumulierte Wahrscheinlichkeiten der Binomialverteilung 248

HYPGEOM.VERT: Hypergeometrische Verteilung 248

Chi-Quadrat-Verteilungen 249

CHIQU.VERT.RE: Chi-Quadrat-Verteilung 249

CHIQU.VERT: Chi-Quadrat-Verteilung 250

CHIQU.INV.RE: Perzentile der rechtsseitigen Chi-Quadrat-Verteilung 251

CHIQU.INV: Perzentile der linksseitigen Chi-Quadrat-Verteilung 251

CHIQU.TEST: Chi-Quadrat-Test 251

Regressionsanalyse 252

PROGNOSE.LINEAR: Abhängige Variable unter Verwendung der Ausgleichsgeraden schätzen 252

PROGNOSE.ETS: Zukünftige Werte mit einer exponentiellen Glättungsmethode schätzen 253

ACHSENABSCHNITT: Gerade schneidet die y-Achse 254

RGP 255

STEIGUNG: Steigung einer Regressionsgeraden 255

STFEHLERYX: Standardfehler 255

TREND: Linearer Trend 255

RKP: Exponentielle Regression 256

VARIATION: Exponentielle Variation 256

Korrelation 256

KORREL: Korrelationskoeffizient 256

PEARSON: Pearson-Korrelationskoeffizient 257

BESTIMMTHEITSMASS: Quadrat des Pearsonschen Korrelationskoeffizienten 257

FISHER 257

FISHERINV 257

Einige ziemlich ausgefallene Wahrscheinlichkeitsverteilungen 258

BETA.VERT: Kumulierte Verteilungsfunktion der Betaverteilung 258

BETA.INV: Quantil der Betaverteilung einer Zufallsvariablen 258

EXPON.VERT: Wahrscheinlichkeiten für exponentialverteilte Zufallsvariable 259

GAMMA: Wert der Gammafunktion 259

GAMMA.VERT: Wahrscheinlichkeiten für gammaverteilte Zufallsvariable 260

GAMMA.INV: × für eine angegebene Wahrscheinlichkeit bei einer Gammaverteilung 260

GAMMALN und GAMMALN.GENAU: Natürlicher Logarithmus der Gammafunktion 260

LOGNORM.VERT: Wahrscheinlichkeiten für lognormalverteilte Zufallsvariablen 261

LOGNORM.INV: Quantile der Lognormalverteilung 261

POISSON.VERT: Wahrscheinlichkeiten einer Poissonverteilung 261

WEIBULL.VERT: Weibullverteilung 262

G.TEST: Wahrscheinlichkeit für einen Gauß-Test 262

Kapitel 10 Deskriptive Statistik 263

Populationskenngrößen ermitteln 264

Histogramm erstellen 268

Rang und Quantilsrang berechnen 271

Gleitenden Durchschnitt berechnen 274

Exponentielles Glätten 276

Zufallszahlen erzeugen 279

Stichproben ziehen 280

Kapitel 11 Mathematische Statistik 285

Das Datenanalysewerkzeug Zweistichproben t-Test verwenden 286

Das Datenanalysewerkzeug Gauß-Test verwenden 289

Ein Streudiagramm (Punktdiagramm) erstellen 291

Das Analysewerkzeug Regression verwenden 295

Das Analysewerkzeug Korrelation verwenden 298

Das Analysewerkzeug Kovarianz verwenden 300

Die ANOVA-Analysetools verwenden 301

Einen Zwei-Stichproben F-Test durchführen 303

Die Fourieranalyse verwenden 303

Kapitel 12 Modelloptimierung mit Solver 305

Modelloptimierung verstehen 306

Optimieren Sie Ihren Gewinn 306

Nebenbedingungen erkennen 306

Ein Solver-Arbeitsblatt einrichten 307

Ein Modelloptimierungsproblem lösen 311

Die Solver-Berichte durchsehen 316

Der Antwortbericht 316

Der Sensitivitätsbericht 318

Der Grenzwertbericht 319

Weitere Hinweise zu den Solver-Berichten 320

Die Solver-Optionen 321

Die Registerkarte »Alle Methoden« verwenden 322

Die Registerkarte »GRG-Nichtlinear« verwenden 323

Die Registerkarte »EA (Evolutionärer Algorithmus)« verwenden 325

Modellinformationen speichern und wiederverwenden 326

Die Solver-Fehlermeldungen verstehen 327

Solver hat eine Lösung gefunden 327

Solver hat die aktuelle Lösung durch Konvergieren erreicht 328

Solver kann die aktuelle Lösung nicht verbessern 328

Solver wurde beim Erreichen der Höchstzeit abgebrochen 328

Solver wurde auf Anforderung des Benutzers abgebrochen 328

Solver wurde beim Erreichen der Iterationsgrenze abgebrochen 328

Die Werte der Zielzelle konvergieren nicht 329

Solver konnte keine realisierbare Lösung finden 329

Die für diesen LP Solver erforderlichen Linearitätsbedingungen sind nicht erfüllt 329

Das Problem ist für die Verarbeitung durch Solver zu groß 329

Fehlerwert bei Solver in der Zielzelle oder einer Nebenbedingungszelle 330

Es ist nicht genügend Arbeitsspeicher zur Lösung des Problems verfügbar 330

Fehler im Modell. Überprüfen Sie, ob alle Zellen und Nebenbedingungen gültig sind 330

Teil IV Der Top-Ten-Teil 331

Kapitel 13 Zehn Dinge, die Sie über Statistik wissen sollten 333

Beschreibende Statistiken sind unkompliziert 333

Durchschnitte sind nicht immer einfach 334

Standardabweichungen beschreiben die Streuung 335

Eine Beobachtung ist eine Beobachtung 336

Eine Stichprobe ist eine Untermenge von Werten 336

Induktive Statistik ist cool, aber auch kompliziert 337

Wahrscheinlichkeitsverteilungsfunktionen sind nicht immer kompliziert 338

Gleichverteilung 338

Normalverteilung 339

Parameter sind nicht so kompliziert 340

Die Schiefe und die Wölbung beschreiben die Form der Wahrscheinlichkeitsverteilung 341

Konfidenzintervalle scheinen auf den ersten Blick kompliziert zu sein, sie sind jedoch nützlich 341

Kapitel 14 Fast zehn Tipps für die Präsentation von Tabellenergebnissen und die Analyse von Daten 343

Geben Sie sich beim Import der Daten Mühe 343

Entwerfen Sie Informationssysteme so, dass sie aussagekräftige Daten produzieren 344

Vergessen Sie die externen Datenquellen nicht! 345

Addieren Sie! 345

Erkunden Sie immer die beschreibenden Statistiken 346

Achten Sie auf Trends 346

Kreuztabellierung 347

Diagramme, bitte! 347

Hüten Sie sich vor induktiver Statistik 347

Kapitel 15 Zehn Tipps zur visuellen Analyse und Präsentation von Daten 349

Finden Sie den passenden Diagrammtyp 349

Verwenden Sie die Botschaft Ihres Diagramms als Diagrammtitel 351

Seien Sie bei Kreisdiagrammen auf der Hut 352

Erwägen Sie bei kleinen Datasets den Einsatz von PivotCharts 352

Vermeiden Sie 3D-Diagramme 354

Verwenden Sie niemals 3D-Kreisdiagramme 356

Achten Sie auf falsche Datenmarkierungen 357

Verwenden Sie logarithmische Skalen 358

Vergessen Sie das Experimentieren nicht 360

Besorgen Sie sich Tufte 360

Glossar zur Datenanalyse und zu Excel 361

Stichwortverzeichnis 369