Excel 2016 Aufbauwissen (eBook)

Vorwort 3
Inhaltsverzeichnis 5
1Was Sie über Formeln und Funktionen wissen sollten 15
1.1Berechnungen mit Excel - ein Überblick 16
Grundlagen 16
Eine einfache Formel eingeben 17
Summe und einfache Funktionen berechnen 18
Die Schnellanalyse nutzen 19
1.2Zellbezüge in Formeln und Funktionen 21
Formeln und Funktionen kopieren 21
Automatisches Anpassen von Zellbezügen (relative Zellbezüge) 21
Anpassen der Zellbezüge durch absolute Zellbezüge verhindern 21
Gemischte Bezüge 23
Zellbezüge auf andere Tabellenblätter 23
Zellbezüge auf Arbeitsmappen (Arbeitsmappen verknüpfen) 24
1.3Namen anstelle von Zellbezügen verwenden 25
Namen erstellen 25
Namen verwalten 27
Namen in Formeln verwenden 29
1.4Eingabe und Syntax von Funktionen 30
Den Funktionsassistenten zur Eingabe nutzen 30
Eine Funktion über die Funktionsbibliothek auswählen 32
Funktion über die Tastatur eingeben 32
Aufbau einer Funktion/Funktionssyntax 33
1.5Formeln und Funktionen als Argumente 34
Funktion mit dem Funktionsassistent einfügen 34
Verschachtelte Funktionen per Tastatur eingeben 37
1.6Formeln überprüfen und korrigieren 38
Formeln editieren 38
Formeln im gesamten Tabellenblatt anzeigen 39
Die Excel-Fehlerkontrolle 39
Weitere Möglichkeiten der Formelkontrolle 40
1.7Matrixformeln 42
Matrixformel eingeben 43
Zellbezüge in Matrixformeln 44
Formel nachträglich in eine Matrixformel umwandeln 45
1.8Zusammenfassung 45
2Funktionen für Fortgeschrittene 47
2.1Logikfunktionen 48
Die Funktion WENN 48
Mehrere Bedingungen mit WENN prüfen (Verschachteln) 49
Bedingungen mit Logikfunktionen verknüpfen 50
Fehlerwerte mit Logikfunktionen vermeiden 51
Informationen und Zellinhalte abfragen 53
2.2Werte runden 55
2.3Auswertungs- und Statistikfunktionen 56
Anzahl der Werte ermitteln 57
Durchschnittswerte berechnen 60
Summe mit Bedingungen verknüpfen 62
Häufigkeit ermitteln 64
Korrelationskoeffizient berechnen 65
Regressionsanalysen 66
2.4Nachschlage- und Verweisfunktionen 68
SVERWEIS 68
WVERWEIS 71
INDEX und VERGLEICH 72
Zellbereiche mit BEREICH.VERSCHIEBEN anpassen 74
2.5Finanzmathematische Funktionen 77
2.6Datums- und Uhrzeitfunktionen 79
Aktuelles Datum bzw. aktuelle Uhrzeit 79
Teilwerte eines Datums ermitteln 80
Monat oder Wochentag als Text 82
Differenz zwischen Datumswerten berechnen 84
Berechnungen mit Zeitwerten 86
2.7Textfunktionen 88
Text in Zahl umwandeln 88
Zeichenfolgen aneinanderfügen (Verketten) 91
Zeichenfolgen aus Text 91
2.8Zufallszahlen generieren 95
Zufallszahlen mit Funktionen erstellen 95
Neuberechnung von Zufallszahlen 96
2.9Hilfe zu Funktionen erhalten 97
Funktionsübersicht 97
Hilfe bei der Funktionseingabe 98
2.10Weitere Add-In Funktionen laden 98
2.11Zusammenfassung 100
3Umfangreiche Tabellen und Datenbanken 101
3.1Eine Datenbank anlegen 102
Was sollten Sie beim Anlegen einer Datenbank beachten? 102
Einen Zellbereich als dynamische Tabelle formatieren 103
3.2Daten bei der Eingabe überprüfen 107
Wertebereich und Datentyp vorgeben 107
Die Eingabe auf die Auswahl aus einer Liste beschränken 109
Zulässige Eingaben mit einer Formel berechnen 110
Tipps zur Datenüberprüfung 111
Nachträglich ungültige Daten kennzeichnen 112
3.3Tabellen sortieren 112
Was Sie beim Sortieren berücksichtigen sollten 112
Einfaches Sortieren 113
Benutzerdefiniertes, erweitertes Sortieren 115
3.4Filter einsetzen 117
Einfache Filter/AutoFilter 117
Spezialfilter 119
Filtern mit Datenschnitten 121
3.5Tabellen zusammenfassen und auswerten 124
Teilergebnisse berechnen 124
Mehrere Tabellen zusammenfassen (konsolidieren) 127
3.6Häufige Probleme in Datenbanken beheben 130
Text in Spalten aufteilen 130
Duplikate in Tabellen 132
3.7Zusammenfassung 134
4Werkzeuge zur Datenanalyse 135
4.1Bedingte Formatierung 136
Vorlagen und einfache Regeln 137
Bedingte Formate wieder entfernen 138
Datumswerte hervorheben 139
Eigene Regeln definieren 139
Regeln verwalten 142
4.2Datentabellen berechnen (Mehrfachoperation) 143
Datentabelle mit einer Variablen 143
Mehrfachoperation mit zwei Variablen 145
4.3Szenarien erstellen und verwalten 146
Tabelle mit Ausgangswerten erstellen 146
Szenario erstellen 147
Szenarien verwalten 149
Szenarien anzeigen 150
4.4Zielwertsuche 151
4.5Das Tool Prognoseblatt 154
4.6Zusammenfassung 156
5Pivot-Tabellen und Pivot-Diagramme 157
5.1Grundlagen 158
Was sind Pivot-Tabellen? 158
Welche Daten eignen sich für Pivot-Tabellen? 158
Aufbau von Pivot-Tabellen 159
5.2Eine Pivot-Tabelle erstellen 160
Der Aufgabenbereich PivotTable-Felder 162
Tabelle löschen oder verschieben 163
5.3Felder anordnen, umstellen und gruppieren 163
Die Bereiche einer Pivot-Tabelle 163
Felder anordnen 164
Mit einer leeren Pivot-Tabelle beginnen 165
Mehrere Zeilen- und Spaltenfelder verwenden 166
5.4Pivot-Tabelle aktualisieren 168
5.5Layout und Formatierung 169
Tabellenformate 169
Tabellenlayout bearbeiten 169
Die Anzeige von Gesamt- und Zwischenergebnissen steuern 171
Anpassung der Spaltenbreite 171
Tabellenelemente markieren und hervorheben 172
5.6Werteberechnung und -anzeige steuern 173
Funktion auswählen 173
Beispiel Umfrageauswertung 175
Prozentanteile anzeigen 176
5.7Mit Pivot-Tabellen arbeiten 177
Tabelle filtern 177
Mit Datenschnitten filtern 179
Tabelle sortieren 181
Einzeldatensätze anzeigen 182
5.8Erweiterte Pivot-Tabellenfunktionen 182
Elemente eines Feldes zu Gruppen zusammenfassen 182
Gruppen aus Datumswerten bilden 183
Felder berechnen 185
Elemente eines Feldes berechnen 186
Die Funktion Pivotdatenzuordnen 188
Optionen für Pivot-Tabellen 188
5.9Pivot-Diagramme (PivotChart) 189
Pivot-Diagramm aus Pivot-Tabelle erstellen 189
Pivot-Diagramm bearbeiten 190
Neues Pivot-Diagramm erstellen 192
5.10Daten mit Power Pivot importieren und analysieren 193
Was ist Power Pivot? 194
Power Pivot öffnen und Daten importieren 195
Daten bearbeiten 196
Pivot-Tabelle erstellen 198
5.11Zusammenfassung 199
6Diagramme für Fortgeschrittene 201
6.1Spezialdiagramme und neue Diagrammtypen 202
Punktdiagramm (XY) 202
Blasendiagramm 204
Ein Histogramm erstellen 205
Wasserfalldiagramm 207
Hierarchische Darstellung und Größenvergleich mit Treemap 210
6.2Diagrammelemente bearbeiten 210
Elemente hinzufügen/entfernen 210
Elemente im Aufgabenbereich bearbeiten 211
Elemente zur Diagrammanalyse 212
Tipps und Tricks 214
6.3Rund um die Diagrammachsen 216
Achseneinteilung 216
Einheiten und Zahlenformat 217
Achsenposition 218
Sekundärachse und Diagrammtypen kombinieren 219
6.4Problembehandlung in Diagrammen 221
Fehlende Werte 221
Zahlen als Achsenbeschriftung 222
Diagrammbereich automatisch anpassen 223
6.53D-Geodaten visualisieren 224
Karte erstellen 225
Karte verwenden 228
Weitere Möglichkeiten der Kartengestaltung 228
6.6Zusammenfassung 230
7Datenimport 231
7.1Textdateien öffnen und konvertieren 232
Dateiformate 232
Textdatei (.txt) öffnen 233
7.2Einfache Verbindung zu externen Daten 235
Externe Daten abrufen 236
Daten aus einer Verbindung aktualisieren 236
Sicherheitseinstellungen 237
7.3Zugriff auf externe Daten mit Power Query 238
Beispiel: Daten aus einer Excel-Mappe abrufen 238
Daten im Abfrage-Editor bearbeiten 239
Bearbeitete Daten aus dem Abfrage-Editor laden 243
Mit den Abfragedaten arbeiten 243
Webabfragen mit Power Query 244
7.4Zusammenfassung 246
8Makros 247
8.1Grundlagen 248
Was sind Makros eigentlich? 248
Vorbereitungen 248
Sicherheitseinstellungen 250
8.2Einfache Makros 252
Makro aufzeichnen 252
Makro ausführen 253
8.3Zellbezüge in Makros 255
8.4Makroausführung starten 257
Tastenkombination zuweisen 257
Makro über die Symbolleiste für den Schnellstart starten 257
Makros im Menüband einfügen 258
Makro über eine Befehlsschaltfläche im Tabellenblatt starten 260
8.5Beispiel: Diagramm mit Makros steuern 262
8.6Zusammenfassung 264
9Einführung in die VBA-Programmierung 265
9.1Vorbemerkungen 266
9.2Der VBA-Editor 267
Das Editor-Fenster 267
Module und Prozeduren 269
Befehlssyntax 271
Kommentare 274
Prozeduren ausführen 274
Die VBA-Hilfe 274
9.3Grundlegende Sprachelemente von VBA 275
Variablen 275
Konstanten 277
Operatoren und Ausdrücke 278
9.4Einfache Dialoge 279
Eine Meldung ausgeben 279
Benutzereingaben mit InputBox 280
9.5Kontrollstrukturen 281
Entscheidungsstrukturen oder Verzweigungen 281
Wiederholungsschleifen 284
Bedingungsschleifen 286
9.6Die Excel-Objekte 289
Objekteigenschaften 290
Methoden und Ereignisse 290
Der Objektkatalog 291
9.7Zugriff auf Excel-Objekte 292
Zugriff auf Tabellenblätter 292
Zugriff auf Arbeitsmappen 294
Zellen und Zellbereiche adressieren 294
Werte in Zellen eintragen 296
Ausschneiden, Kopieren und Einfügen 296
Berechnungen mit Formeln und Funktionen 297
Position ermitteln 299
Zellbereiche ermitteln 299
Beispiel: Jede zweite Zeile mit einer Füllfarbe formatieren 301
9.8Diagramme steuern 303
9.9Mit Arrays arbeiten 306
Deklaration und Wertezuweisung 306
Beispiel: Import aus einer Textdatei automatisieren 308
9.10Prozeduren testen 310
Einzelschritte ausführen 310
Variablen überwachen 310
Haltepunkte verwenden 312
Fehlerbehandlung 312
Anweisungszeilen auskommentieren 314
9.11Benutzerdefinierte Funktionen 315
Benutzerdefinierte Funktionen erstellen 315
Benutzerdefinierte Funktionen verwenden 316
Als Add-In speichern 316
9.12Zusammenfassung 318
10Steuerelemente in Tabellenblättern 319
10.1Wozu Steuerelemente? 320
10.2Grundlagen Formularsteuerelemente 320
Wo finden Sie die Formularsteuerelemente? 320
Formularsteuerelement einfügen 321
Formularsteuerelement bearbeiten 322
10.3Beispiele für Formularsteuerelemente 323
Befehlsschaltfläche 323
Kombinationsfeld und Listenfeld 323
Kontrollkästchen 325
Drehfeld und Bildlaufleiste 326
Weitere Steuerelementeigenschaften 326
10.4ActiveX-Steuerelemente 328
Die Eigenschaften eines ActiveX-Kombinationsfeldes 328
Ein ActiveX-Kombinationsfeld in Verbindung mit VBA 330
10.5Zusammenfassung 332
Tastenkombinationen 335
Glossar 339
Index 343