Excel - Formeln und Funktionen

Bernd Held ist von Haus aus gelernter Informatiker und einer der bekanntesten Excel-Autoren im deutschsprachigen Raum. Zu seinen Spezialgebieten zählen Excel, VBA-Programmierung, Access und allgemeine Office- und Tool-Themen. Er wurde von Microsoft mehrfach als MVP (Most Valuable Professional) für den Bereich Excel ausgezeichnet. Seit 2008 arbeitet er mit einem eigenen Team aus Experten zusammen, das Projekte und Schulungen durchführt, Unternehmen berät und Bücher sowie Fachartikel veröffentlicht.

Michael Eichhorn arbeitet hauptberuflich bei einem internationalen Finanzdienstleistungsunternehmen und befasst sich in seiner täglichen Arbeit seit vielen Jahren intensiv mit Excel und VBA. Neben Kalkulationen gehört auch das formatierte und standardisierte Reporting in Excel zu seinen Aufgaben.

Vorwort
23
1
Arbeiten mit Formeln und Bezügen
27

1.1
Rechenformeln eingeben
28
1.2
Das Bezugssystem von Excel
29
1.3
Die unterschiedlichen Zellbezüge
30
1.4
Formeln ausspionieren
31
1.4.1
Formelansicht aktivieren
32
1.4.2
Formelzellen markieren
32
1.5
Formelzusammenhänge feststellen
35
1.6
Formeln überwachen mit dem Überwachungsfenster
36
1.7
Formeln kopieren
37
1.7.1
Kopieren über das Ausfüllkästchen
37
1.7.2
Kopieren über die Zwischenablage
38
1.7.3
Kopieren über das Menü
38
1.8
Formeln schützen
39
1.9
Formeln verstecken
40
1.10
Die Berechnung abschalten
41
1.11
Formeln in Festwerte umwandeln
41
1.12
Teilberechnung der Formeln
43
1.13
Arbeiten mit Namen
43
1.13.1
Generelles zur Namensvergabe
44
1.13.2
Namen vergeben
44
1.13.3
Namen vergeben ohne Zellbezug
46
1.13.4
Bereiche benennen
47

2
Typische Beispiele für den Einsatz von Formeln
49

2.1
Beispiele in der Kategorie Büro
49
2.1.1
Rechnen mit unterschiedlichen Mehrwertsteuersätzen
50
2.1.2
Zahlungsfälligkeit berechnen
52
2.1.3
Eine Preisreduktion herbeiführen
53
2.1.4
Zahlen, Texte und Bezüge mischen
55
2.1.5
Mehrere Spalten miteinander verbinden
56
2.1.6
Rangfolge bestimmen
57
2.1.7
Aktientalfahrt
58
2.1.8
Zielwertsuche
59
2.2
Beispiele in der Kategorie Datum und Zeit
63
2.2.1
Einen Stundenplan erstellen
63
2.2.2
Mit negativen Stundenwerten arbeiten
65
2.2.3
Zeiten umrechnen
66
2.2.4
Das Alter berechnen – eine Geburtstagsliste
68
2.2.5
Zeitwerte kombinieren
69
2.2.6
Eine eigene Datumsleiste erstellen
71
2.2.7
Eine eigene Zeiterfassung erstellen
72
2.3
Beispiele in der Kategorie Produktion bzw. Beispiele aus der Praxis
74
2.3.1
Stückzahlen errechnen
74
2.3.2
Fertigstellungsgrad errechnen
75
2.3.3
Berechnung des Bremsweges
77
2.3.4
Benzinverbrauch ausrechnen
79
2.3.5
Kilometerkontrolle
80
2.3.6
Fahrtenbuch
82
2.3.7
Autokauf
83
2.3.8
Einheiten in der Schifffahrt
84
2.3.9
Amerikanische Maße umrechnen
87
2.4
Sonstige Beispiele
96
2.4.1
Beim Kuchenbäcker – Kalkulation der Preise
96
2.4.2
Schachbrett und Reiskorn
98
2.4.3
Das Normal- und das Idealgewicht
101
2.5
Beispiele in der Kategorie Wirtschaftlichkeitskennzahlen
102
2.5.1
Kosten und Leistung miteinander vergleichen (Wirtschaftlichkeit)
103
2.5.2
Kosten-Wirtschaftlichkeit errechnen
104
2.5.3
Arbeitszeit-Produktivität erhöhen
106
2.5.4
Rohstoff-Produktivität errechnen
107

3
Mit Tabellenfunktionen arbeiten
109

3.1
Funktionen auf die Schnelle aufrufen
109
3.2
Tabellenfunktionen über den Funktionsassistenten einfügen
111
3.3
Tabellenfunktionen suchen
113
3.4
Tabellenfunktionen selbst erfassen
115
3.5
Fragen zu Tabellenfunktionen beantworten
116
3.6
Funktionen aufspüren
118

4
Logikfunktionen
121

4.1
Die Tabellenfunktion UND
121
4.1.1
Auf Wertgrenzen prüfen
122
4.1.2
Umsätze im bestimmten Zeitraum erkennen
123
4.2
Die Tabellenfunktion WENN
125
4.2.1
Zahlenreihen vergleichen
125
4.2.2
Zahlenbereich prüfen
127
4.2.3
Mengenrabatte gewähren
128
4.2.4
Die Quartalsauswertung
130
4.2.5
Anzahl Verschachtelungen
133
4.3
Die Tabellenfunktion ODER
134
4.3.1
Zahlen prüfen
134
4.3.2
Eingabecheck durchführen
135
4.4
Die Tabellenfunktion FALSCH
139
4.5
Die Tabellenfunktion WAHR
140
4.6
Die Tabellenfunktion NICHT
141
4.7
Die Tabellenfunktion WENNFEHLER
143
4.8
Die Tabellenfunktion WENNNV
144
4.9
Die Tabellenfunktion XODER
146
4.9.1
Prüfen, ob ein Argument korrekt ist
147

5
Textfunktionen
149

5.1
Die Tabellenfunktion ASC
150
5.2
Die Tabellenfunktion BAHTTEXT
151
5.3
Die Tabellenfunktion CODE
152
5.4
Die Tabellenfunktion DM
154
5.5
Die Tabellenfunktion ERSETZEN
157
5.5.1
Variable Rechnungsnamen
158
5.6
Die Tabellenfunktion ERSETZENB
159
5.7
Die Tabellenfunktion FEST
160
5.7.1
Zahlen in formatierten Text umwandeln
160
5.8
Die Tabellenfunktion FINDEN
162
5.9
Die Tabellenfunktion FINDENB
164
5.10
Die Tabellenfunktion GLÄTTEN
164
5.11
Die Tabellenfunktion GROSS
165
5.12
Die Tabellenfunktion GROSS2
167
5.13
Die Tabellenfunktion IDENTISCH
168
5.14
Die Tabellenfunktion KLEIN
169
5.15
Die Tabellenfunktion LÄNGE
171
5.16
Die Tabellenfunktion LÄNGEB
174
5.17
Die Tabellenfunktion LINKS
174
5.17.1
Zellinhalte extrahieren
175
5.17.2
Bestimmte Längen variabel übertragen
176
5.17.3
Vornamen extrahieren
177
5.18
Die Tabellenfunktion LINKSB
179
5.19
Die Tabellenfunktion PHONETIC
180
5.20
Die Tabellenfunktion RECHTS
180
5.20.1
Auf Minuszeichen prüfen
181
5.20.2
Nachnamen extrahieren – Variante I
183
5.21
Die Tabellenfunktion RECHTSB
184
5.22
Die Tabellenfunktion SÄUBERN
185
5.23
Die Tabellenfunktion SUCHEN
186
5.23.1
Suchen nach Wörtern
186
5.23.2
Suchen nach Zahlen
187
5.24
Die Tabellenfunktion SUCHENB
190
5.25
Die Tabellenfunktion TEIL
190
5.25.1
Nachnamen extrahieren – Variante II
191
5.25.2
Vorwahl und Durchwahl trennen
192
5.25.3
Eine Quersumme berechnen
193
5.26
Die Tabellenfunktion TEILB
195
5.27
Die Tabellenfunktion TEXT
195
5.27.1
Zahlen in Euro-Textfelder umwandeln
196
5.28
Die Tabellenfunktion UNICODE
197
5.29
Die Tabellenfunktion UNIZEICHEN
198
5.30
Die Tabellenfunktion VERKETTEN
200
5.30.1
Verbinden von Vornamen und Zunamen
200
5.31
Die Tabellenfunktion WECHSELN
202
5.31.1
Punkte durch Kommas ersetzen
202
5.32
Die Tabellenfunktion WERT
204
5.33
Die Tabellenfunktion WIEDERHOLEN
205
5.33.1
Würfelergebnisse grafisch darstellen
205
5.34
Die Tabellenfunktion ZAHLENWERT
207
5.35
Die Tabellenfunktion ZEICHEN
207
5.35.1
Symbolübersicht erstellen
208

6
Datum- und Zeitfunktionen
211

6.1
Die Datumssysteme in Excel
212
6.2
Die Tabellenfunktion ARBEITSTAG
213
6.2.1
Zahlbar bis
213
6.3
Die Tabellenfunktion ARBEITSTAG.INTL
215
6.3.1
Arbeitstage auf anderem Weg
215
6.4
Die Tabellenfunktion BRTEILJAHRE
217
6.4.1
Das Alter auf den Tag genau bestimmen
218
6.5
Die Tabellenfunktion DATUM
219
6.5.1
Ein Datum zusammensetzen
220
6.6
Die Tabellenfunktion DATWERT
221
6.6.1
Importdatum in lesbares Datum umwandeln
222
6.7
Die Tabellenfunktion EDATUM
223
6.7.1
Fälligkeitstermine errechnen
223
6.8
Die Tabellenfunktion HEUTE
225
6.8.1
Einen Wochenplan erstellen
225
6.9
Die Tabellenfunktion ISOKALENDERWOCHE
227
6.9.1
Eine Kalenderwochenübersicht nach ISO
227
6.10
Die Tabellenfunktion JAHR
229
6.10.1
Die Jahreszahl ermitteln
229
6.11
Die Tabellenfunktion JETZT
230
6.11.1
Nur die Uhrzeit einfügen
231
6.12
Die Tabellenfunktion KALENDERWOCHE
232
6.12.1
Die Kalenderwoche bestimmen
233
6.13
Die Tabellenfunktion MINUTE
234
6.13.1
Die Minutenzahl ermitteln
235
6.14
Die Tabellenfunktion MONAT
236
6.14.1
Die Monatszahl ermitteln
237
6.15
Die Tabellenfunktion MONATSENDE
238
6.15.1
Monatsende-Termine errechnen
240
6.16
Die Tabellenfunktion NETTOARBEITSTAGE
241
6.16.1
Lohnabrechnung über einen bestimmten Zeitraum
242
6.17
Die Tabellenfunktion NETTOARBEITSTAGE.INTL
243
6.17.1
Nettoarbeitstage ausrechnen
244
6.18
Die Tabellenfunktion SEKUNDE
245
6.18.1
Die Sekundenzahl ermitteln
246
6.19
Die Tabellenfunktion STUNDE
247
6.19.1
Die Stundenzahl ermitteln
248
6.20
Die Tabellenfunktion TAG
249
6.20.1
Die Tageszahl ermitteln
250
6.21
Die Tabellenfunktion TAGE
251
6.21.1
Die Anzahl der Tage ermitteln
252
6.22
Die Tabellenfunktion TAGE360
252
6.22.1
Datumsdifferenzen auf Basis TAGE360 errechnen
253
6.23
Die Tabellenfunktion WOCHENTAG
255
6.23.1
Unterschiedliche Stundensätze werktags und am Wochenende
255
6.24
Die Tabellenfunktion ZEIT
257
6.24.1
Zeiten zusammensetzen
258
6.25
Die Tabellenfunktion ZEITWERT
259
6.25.1
Zeitangaben erkennen
260

7
Mathematik und Trigonometrie
263

7.1
Die Tabellenfunktion ABRUNDEN
266
7.2
Die Tabellenfunktion ABS
268
7.3
Die Tabellenfunktion AGGREGAT
269
7.4
Die Tabellenfunktion ARABISCH
272
7.5
Die Tabellenfunktion ARCCOS
273
7.6
Die Tabellenfunktion ARCCOSHYP
274
7.7
Die Tabellenfunktion ARCCOT
274
7.8
Die Tabellenfunktion ARCCOTHYP
275
7.9
Die Tabellenfunktion ARCSIN
275
7.10
Die Tabellenfunktion ARCSINHYP
276
7.11
Die Tabellenfunktion ARCTAN
276
7.12
Die Tabellenfunktion ARCTAN2
277
7.13
Die Tabellenfunktion ARCTANHYP
277
7.14
Die Tabellenfunktion AUFRUNDEN
278
7.15
Die Tabellenfunktion BASIS
279
7.16
Die Tabellenfunktion BOGENMASS
280
7.17
Die Tabellenfunktion COS
281
7.18
Die Tabellenfunktion COSEC
281
7.19
Die Tabellenfunktion COSECHYP
282
7.20
Die Tabellenfunktion COSHYP
282
7.21
Die Tabellenfunktion COT
283
7.22
Die Tabellenfunktion COTHYP
283
7.23
Die Tabellenfunktion DEZIMAL
284
7.24
Die Tabellenfunktion EXP
284
7.25
Die Tabellenfunktion FAKULTÄT
285
7.26
Die Tabellenfunktion GANZZAHL
285
7.27
Die Tabellenfunktion GERADE
286
7.28
Die Tabellenfunktion GGT
287
7.29
Die Tabellenfunktion GRAD
288
7.30
Die Tabellenfunktion ISO.OBERGRENZE
289
7.31
Die Tabellenfunktion KGV
289
7.32
Die Tabellenfunktion KOMBINATIONEN
291
7.33
Die Tabellenfunktion KÜRZEN
293
7.34
Die Tabellenfunktion KOMBINATIONEN2
293
7.35
Die Tabellenfunktion LN
294
7.36
Die Tabellenfunktion LOG
294
7.37
Die Tabellenfunktion LOG10
295
7.38
Die Tabellenfunktion MDET
295
7.39
Die Tabellenfunktion MEINHEIT
296
7.40
Die Tabellenfunktion MINV
296
7.41
Die Tabellenfunktion MMULT
297
7.42
Die Tabellenfunktion OBERGRENZE
297
7.43
Die Tabellenfunktion OBERGRENZE.GENAU
299
7.44
Die Tabellenfunktion OBERGRENZE.MATHEMATIK
301
7.45
Die Tabellenfunktion PI
303
7.46
Die Tabellenfunktion POLYNOMIAL
303
7.47
Die Tabellenfunktion POTENZ
303
7.47.1
Die Kubikwurzel einer Zahl errechnen
304
7.48
Die Tabellenfunktion POTENZREIHE
305
7.49
Die Tabellenfunktion PRODUKT
306
7.49.1
Preiserhöhungen flexibel durchführen
306
7.50
Die Tabellenfunktion QUADRATESUMME
308
7.51
Die Tabellenfunktion QUOTIENT
308
7.52
Die Tabellenfunktion REST
310
7.53
Die Tabellenfunktion RÖMISCH
311
7.54
Die Tabellenfunktion RUNDEN
313
7.55
Die Tabellenfunktion SEC
315
7.56
Die Tabellenfunktion SECHYP
315
7.57
Die Tabellenfunktion SIN
316
7.58
Die Tabellenfunktion SINHYP
316
7.59
Die Tabellenfunktion SUMME
317
7.59.1
Einfache Summen bilden
317
7.59.2
Mehrere Bereiche summieren
318
7.60
Die Tabellenfunktion SUMMENPRODUKT
320
7.61
Die Tabellenfunktion SUMMEWENN
320
7.61.1
Umsatz pro Arbeitsgruppe ermitteln
321
7.62
Die Tabellenfunktion SUMMEWENNS
322
7.63
Die Tabellenfunktion SUMMEX2MY2
323
7.64
Die Tabellenfunktion SUMMEX2PY2
323
7.65
Die Tabellenfunktion SUMMEXMY2
324
7.66
Die Tabellenfunktion TAN
324
7.67
Die Tabellenfunktion TANHYP
325
7.68
Die Tabellenfunktion TEILERGEBNIS
325
7.69
Die Tabellenfunktion UNGERADE
327
7.70
Die Tabellenfunktion UNTERGRENZE
328
7.71
Die Tabellenfunktion UNTERGRENZE.GENAU
329
7.72
Die Tabellenfunktion UNTERGRENZE.MATHEMATIK
330
7.73
Die Tabellenfunktion VORZEICHEN
331
7.74
Die Tabellenfunktion VRUNDEN
331
7.74.1
Runden auf 5 Cents
332
7.75
Die Tabellenfunktion WURZEL
333
7.76
Die Tabellenfunktion WURZELPI
334
7.77
Die Tabellenfunktion ZUFALLSBEREICH
334
7.78
Die Tabellenfunktion ZUFALLSZAHL
336
7.79
Die Tabellenfunktion ZWEIFAKULTÄT
336

8
Statistische Funktionen
337

8.1
Die Tabellenfunktion ANZAHL
338
8.2
Die Tabellenfunktion ANZAHL2
339
8.2.1
Anzahl der Textzellen zählen
340
8.3
Die Tabellenfunktion ANZAHLLEEREZELLEN
341
8.4
Die Tabellenfunktion HÄUFIGKEIT
343
8.5
Die Tabellenfunktion KGRÖSSTE
345
8.6
Die Tabellenfunktion KKLEINSTE
347
8.7
Die Tabellenfunktion MAX
349
8.8
Die Tabellenfunktion MAXA
350
8.9
Die Tabellenfunktion MEDIAN
352
8.10
Die Tabellenfunktion MIN
353
8.11
Die Tabellenfunktion MINA
355
8.12
Die Tabellenfunktion MITTELWERT
356
8.13
Die Tabellenfunktion MITTELWERTA
358
8.14
Die Tabellenfunktion MITTELWERTWENN
360
8.15
Die Tabellenfunktion MITTELWERTWENNS
362
8.16
Die Tabellenfunktion RANG
365
8.17
Die Tabellenfunktion RANG.GLEICH
365
8.18
Die Tabellenfunktion RANG.MITTELW
368
8.19
Die Tabellenfunktion ZÄHLENWENN
371
8.20
Die Tabellenfunktion ZÄHLENWENNS
375

9
Funktionen für die Finanzmathematik
379

9.1
Die Tabellenfunktion BW
381
9.1.1
Was darf eine Investition kosten?
382
9.1.2
Investitionen vergleichen
384
9.2
Die Tabellenfunktion DIA
385
9.2.1
Berechnung der degressiven Abschreibung
385
9.3
Die Tabellenfunktion GDA
387
9.3.1
Die degressive Doppelraten-Abschreibung anwenden
388
9.4
Die Tabellenfunktion KAPZ
390
9.4.1
Tilgungsanteil für einen Kredit in einem bestimmten Zeitraum errechnen
391
9.5
Die Tabellenfunktion LIA
392
9.5.1
Die lineare Abschreibung berechnen
393
9.6
Die Tabellenfunktion RMZ
394
9.6.1
Wie hoch ist die Zinsbelastung durch einen Kredit?
395
9.7
Die Tabellenfunktion ZINS
397
9.7.1
Den Zinssatz ausrechnen
398
9.8
Die Tabellenfunktion ZW
399
9.8.1
Geld ansparen
400
9.8.2
Stimmt die Rechnung?
400

10
Datenbankfunktionen
403

10.1
Die Tabellenfunktion DBANZAHL
403
10.1.1
Bücherliste auswerten
404
10.2
Die Tabellenfunktion DBANZAHL2
407
10.2.1
Eine Liste über Stichworte durchsuchen
408
10.3
Die Tabellenfunktion DBAUSZUG
410
10.3.1
Ein Artikelsuchsystem einrichten
411
10.4
Die Tabellenfunktion DBMAX
412
10.4.1
Den höchsten Preis ermitteln
413
10.5
Die Tabellenfunktion DBMIN
415
10.5.1
Den Artikel mit dem niedrigsten Preis selektieren
415
10.5.2
Den ältesten Teilnehmer ermitteln
417
10.6
Die Tabellenfunktion DBMITTELWERT
419
10.6.1
Durchschnittliche Einnahmen pro Kategorie ermitteln
419
10.7
Die Tabellenfunktion DBPRODUKT
421
10.8
Die Tabellenfunktion DBSTDABW
421
10.9
Die Tabellenfunktion DBSTDABWN
422
10.10
Die Tabellenfunktion DBSUMME
423
10.10.1
Verkaufserlöse auswerten
424
10.11
Die Tabellenfunktion DBVARIANZ
425
10.12
Die Tabellenfunktion DBVARIANZEN
426

11
Funktionen aus der Kategorie Nachschlagen und Verweisen
429

11.1
Die Tabellenfunktion SVERWEIS
430
11.1.1
Artikeldaten heraussuchen
431
11.1.2
Den richtigen Tarif abgreifen
432
11.2
Die Tabellenfunktion WVERWEIS
435
11.2.1
Kopfzahlen, Umsätze und Kosten darstellen
436
11.2.2
Zahlen dynamisch aus Matrix heraussuchen
437
11.3
Die Tabellenfunktion VERWEIS
439
11.3.1
Punkte und Prozente
440
11.3.2
Autotexte einsetzen
442
11.3.3
Feiertage ermitteln
443
11.4
Die Tabellenfunktion INDEX
444
11.4.1
Uhrenmarken suchen
446
11.4.2
Umsatz pro Kunde ermitteln
447
11.4.3
Den letzten Wert einer Spalte ermitteln
449
11.5
Die Tabellenfunktion INDIREKT
450
11.5.1
Zahlen aus einer Matrix herauslesen
451
11.5.2
Zugriff auf andere Tabelle
453
11.5.3
Der Sprung zum aktuellen Datum
454
11.5.4
Eine Übersicht erstellen
456
11.6
Die Tabellenfunktion ADRESSE
457
11.6.1
Größte Zahl in Spalte ermitteln
458
11.6.2
Die Adresse der letzten Zelle in einer Spalte ermitteln
460
11.6.3
Umsätze kumulieren
461
11.6.4
Die Adresse des kleinsten Wertes einer Liste bestimmen
463
11.7
Die Tabellenfunktion BEREICH.VERSCHIEBEN
464
11.7.1
Umsätze dynamisch halten
466
11.7.2
Die dynamische Summe
467
11.7.3
Kosten dynamisch kumulieren
468
11.7.4
Maschinenauslastungen taggenau wiederfinden
470
11.7.5
Das dynamische Diagramm
472
11.8
Die Funktion HYPERLINK
475
11.8.1
Der Sprung zum aktuellen Tagesdatum
476
11.8.2
Den aktuellen Monat ansteuern
477
11.8.3
Ins Internet verzweigen
478
11.9
Die Tabellenfunktion MTRANS
479
11.9.1
Eine Tabelle transponieren
480
11.10
Die Tabellenfunktionen SPALTE und SPALTEN
481
11.10.1
Werte ausdünnen
482
11.10.2
Die Adresse der letzten belegten Zelle einer Spalte ermitteln
483
11.11
Die Tabellenfunktionen ZEILE und ZEILEN
484
11.11.1
Den letzten Wert einer Zeile finden
485
11.11.2
Datenreihen ausdünnen
487
11.12
Die Tabellenfunktion WAHL
488
11.12.1
Prozentualen Aufschlag blitzschnell anwenden
489

12
Funktionen für die Informationsgewinnung
491

12.1
Die Tabellenfunktion INFO
492
12.2
Die Tabellenfunktion FEHLER.TYP
493
12.2.1
Fehlermeldungen verschwinden lassen
494
12.3
Die IST-Tabellenfunktionen
496
12.3.1
Nulldivisionen nicht anzeigen
498
12.3.2
Die Fehlermeldung #NV sprechender machen
499
12.3.3
Namen überprüfen
500
12.3.4
Texte aufspüren
502
12.3.5
Leerzeilen erkennen
504
12.3.6
Eingaben automatisch formatieren
506
12.3.7
Artikelnummern überprüfen
508
12.3.8
Das letzte Datum und den letzten Umsatz ermitteln
509
12.3.9
Nur bestimmte Einträge zählen
510
12.4
Die Tabellenfunktion TYP
512
12.4.1
Texte und Zahlen optisch unterscheiden
513
12.5
Die Tabellenfunktion ZELLE
514
12.5.1
Den Dateinamen der Arbeitsmappe ermitteln
518
12.5.2
Zellen mit Datumswerten identifizieren
519
12.6
Die Tabellenfunktion N
520
12.6.1
Uhrzeiten in Industriezeiten umrechnen
521

13
Formeln im Zusammenspiel mit anderen Excel-Werkzeugen
523

13.1
Formeln im Zusammenspiel mit der bedingten Formatierung
523
13.1.1
Bestimmte Einträge in einer Tabelle suchen und kennzeichnen
523
13.1.2
Doppelte Werte eines Bereichs kennzeichnen
526
13.1.3
Die letzte belegte Zelle einer Spalte einfärben
528
13.1.4
Die letzte belegte Zelle einer Zeile einfärben
530
13.1.5
Leere Zellen in einem Bereich kennzeichnen
532
13.1.6
Den Zeitpunkt des besten/schlechtesten Wertes ermitteln
534
13.1.7
Jede dritte Zeile hervorheben
536
13.1.8
Fehlerhafte Formelergebnisse kennzeichnen
538
13.1.9
Eine Ampelfunktion einsetzen
540
13.1.10
Einen Kalender erstellen
542
13.1.11
Spalten miteinander vergleichen
545
13.1.12
Zellen oberhalb/unterhalb des Mittelwertes kennzeichnen
547
13.1.13
Ein GANTT-Diagramm erstellen
548
13.1.14
Der erweiterte Projektplaner
550
13.1.15
Ungeschützte Zellen in einem Bereich erkennen
552
13.2
Formeln im Zusammenspiel mit der Gültigkeitsprüfung
554
13.2.1
Eingabelänge und Eingabeform festlegen
554
13.2.2
Eine Eingabe in eine Tabelle bedingt zulassen
557
13.2.3
Erfassung von Daten nur in 5er-Schritten zulassen
558
13.2.4
Tabellenformatierung ein- und ausschalten
560
13.2.5
Synchronisierende Zellen-Dropdowns erstellen
562
13.3
Formeln im Zusammenspiel mit einer Pivot-Tabelle
565
13.3.1
Werte aus einer Pivot-Tabelle zusammenfassen
565

14
Benutzerdefinierte Funktionen erstellen
569

14.1
Den angemeldeten Anwender ermitteln
569
14.2
Dateiinformationen auslesen
570
14.3
Funktionen zusammenfassen
573
14.4
Funktionen in eigener Kategorie verfügbar machen
574
14.5
Weitere interessante benutzerdefinierte Funktionen
577
14.5.1
Aktive Arbeitsmappe ermitteln
577
14.5.2
Aktives Tabellenblatt ermitteln
578
14.5.3
Ist eine Tabelle leer?
578
14.5.4
Ist eine Tabelle geschützt?
579
14.5.5
Befinden sich Daten in einer bestimmten Spalte?
579
14.5.6
Den letzten Wert einer Spalte ermitteln
580
14.5.7
Den letzten Wert einer Zeile ermitteln
581
14.5.8
Funktion zum Umsetzen von Noten
581
14.5.9
Rangfolge als Text ausgeben
582
14.5.10
Enthält eine bestimmte Zelle ein Gültigkeitskriterium?
583
14.5.11
Enthält eine Zelle einen Kommentar?
584
14.5.12
Ist eine bestimmte Zelle verbunden?
584
14.5.13
Initialen aus Namen erstellen
585
14.5.14
Nur Zellen mit Fettdruck addieren
586
14.5.15
Mit Uhrzeiten rechnen
587
14.5.16
Erweitertes Runden durchführen
589
14.5.17
Schnelles Umrechnen von Geschwindigkeiten
589
14.5.18
Extremwerte berechnen
590
14.5.19
Erste Ziffer in einer Zelle ermitteln
592
14.5.20
Buchstaben aus Zellen entfernen
593
14.5.21
Anzahl der Ziffern einer Zelle ermitteln
595
14.5.22
Römische Zahlen in arabische umwandeln
595
14.5.23
Einen Kommentartext in eine Zelle holen
597
14.5.24
Angabe eines optionalen Parameters bei einer Funktion
598
14.5.25
Leerzeichen in einen String integrieren
599

15
Typische Aufgaben aus der Praxis lösen
601

15.1
Den SVERWEIS umpolen
601
15.1.1
Verwendete Funktionen in dieser Aufgabe
603
15.2
Verfügbare Tage eines Monats zählen
604
15.2.1
Verwendete Funktionen in dieser Aufgabe
605
15.3
Bestimmte Zeilen ansteuern
606
15.3.1
Verwendete Funktionen in dieser Aufgabe
608
15.4
Letzten Wert einer Spalte ermitteln
609
15.4.1
Verwendete Funktionen in dieser Aufgabe
610
15.5
Letzten Wert in einer Zeile ermitteln
611
15.6
Die Anzahl einer Zeichenfolge ermitteln
612
15.6.1
Verwendete Funktionen in dieser Aufgabe
613
15.7
Eine bestimmte Zeichenfolge ersetzen
614
15.8
Spitzenwerte in einem Bereich ermitteln
615
15.8.1
Verwendete Funktion in dieser Aufgabe
616
15.9
Die Anzahl eindeutiger Einträge in einem Bereich ermitteln
616
15.9.1
Verwendete Funktionen in dieser Aufgabe
618
15.10
Den nächsten Termin berechnen
618
15.10.1
Verwendete Funktionen in dieser Aufgabe
620
15.11
Personen einer Altersgruppe zählen
620
15.11.1
Verwendete Funktionen in dieser Aufgabe
621
15.12
Mittelwertberechnung ohne Nullen
622
15.12.1
Verwendete Funktion in dieser Aufgabe
623
15.13
Eine Liste nach Tag und Monat sortieren
624
15.13.1
Verwendete Funktionen in dieser Aufgabe
625
15.14
Minuten in Stunden umrechnen
626
15.15
Eine Zeitdifferenz in Stunden berechnen
627
15.16
Buchungen pro Monat zählen
628
15.16.1
Verwendete Funktionen in dieser Aufgabe
629
15.17
Einen bestimmten Wert aus einer Matrix heraussuchen
630
15.17.1
Verwendete Funktionen in dieser Aufgabe
631
15.18
Wie können Sie eine bedingte Zählung mit Wildcards durchführen?
633
15.18.1
Verwendete Funktion in dieser Aufgabe
634
15.19
Bestimmte Wochentage ausgehend von einem Datum berechnen
634
15.19.1
Verwendete Funktion in dieser Aufgabe
635
15.20
Mehrere Werte pro Tag verdichten
636
15.20.1
Verwendete Funktionen in dieser Aufgabe
637
15.21
Bestimmte Werte zu vorgegebenen Nummern extrahieren
639
15.21.1
Verwendete Funktionen in dieser Aufgabe
640
15.22
Einen Bereich per Formel drehen
641
15.22.1
Verwendete Funktionen in dieser Aufgabe
643
15.23
Das dazugehörige Datum zu einem Extremwert finden
644
15.23.1
Verwendete Funktionen in dieser Aufgabe
645
15.24
Nach bestimmter Zeichenfolge in Zelle suchen
646
15.24.1
Verwendete Funktionen in dieser Aufgabe
647
15.25
Rechnungsbeträge über eine Formel konsolidieren
648
15.25.1
Verwendete Funktion in dieser Aufgabe
650
15.26
Den ersten und letzten Wert eines Tages extrahieren
650
15.26.1
Verwendete Funktionen in dieser Aufgabe
651
15.27
Extremwerte unter einer Bedingung ermitteln
652
15.27.1
Verwendete Funktion in dieser Aufgabe
654
15.28
Einen Wert mittels zweiter Eingaben aufspüren
654
15.28.1
Verwendete Funktionen in dieser Aufgabe
655
15.29
Einen Teil der gefilterten Daten zählen
656
15.29.1
Verwendete Funktionen in dieser Aufgabe
658
15.30
Den Namen der aktuellen Tabelle ermitteln
659
15.30.1
Verwendete Funktionen in dieser Aufgabe
660
15.31
Den Namen der aktiven Arbeitsmappe ermitteln
661
15.32
Eine Eingabe über eine Formel »begleiten«
662
15.32.1
Verwendete Funktionen in dieser Aufgabe
663
15.33
Eine ABC-Analyse von Umsätzen durchführen
664
15.33.1
Verwendete Funktionen in dieser Aufgabe
665
15.34
Prüfung, ob ein bestimmter Bereich leer ist
666
15.34.1
Verwendete Funktionen in dieser Aufgabe
667
15.35
Autotexte in Excel einsetzen
668
15.35.1
Verwendete Funktion in dieser Aufgabe
669
15.36
Das Auftreten eines Zeichens zählen
670
15.36.1
Verwendete Funktionen in dieser Aufgabe
671
15.37
Umsätze bis zu einem bestimmten Monat kumulieren
672
15.37.1
Verwendete Funktionen in dieser Aufgabe
673
15.38
Kosten in Wertegruppen clustern
674
15.38.1
Verwendete Funktionen in dieser Aufgabe
676
15.39
Eine Zellengrafik über eine Formel basteln
677
15.39.1
Verwendete Funktionen in dieser Aufgabe
678
15.40
Menge pro Stunde berechnen
678
15.41
Den Spritverbrauch auf 100 km berechnen
679
15.41.1
Verwendete Funktionen in dieser Aufgabe
681
15.42
Endpreis nach diversen Abschlägen berechnen
681
15.43
Einen Wochentag in einem Zeitraum zählen
682
15.43.1
Verwendete Funktionen in dieser Aufgabe
683
15.44
Ein Datum aus mehreren Spalten zusammensetzen
685
15.44.1
Verwendete Funktion in dieser Aufgabe
686
15.45
Lagerdauer ausrechnen
686
15.45.1
Verwendete Funktion in dieser Aufgabe
688
15.46
Zeiten runden
688
15.46.1
Verwendete Funktion in dieser Aufgabe
689
15.47
Rundung auf volle 5 Cents
690
15.47.1
Verwendete Funktion in dieser Aufgabe
691
15.48
Aufrunden auf 5-Euro-Basis
691
15.48.1
Verwendete Funktionen in dieser Aufgabe
693
15.49
Runden auf volle 100 Euro
694

Anhang
697

A
Alle Tastenkombinationen
697
A.1
Formatieren von Daten
698
A.2
Bearbeiten und Verschieben von Daten
699
A.3
Markieren von Zellen mit besonderen Merkmalen
700
A.4
Markieren von Diagrammelementen
701
A.5
Bewegen innerhalb einer Markierung
701
A.6
Einfügen, Löschen und Kopieren einer Markierung
702
A.7
Bewegen in Tabellenblättern und Arbeitsmappen
702
A.8
Blättern in einem Arbeitsmappenfenster
704
B
Alphabetische Übersicht der Excel-Funktionen
705

Index
755