Excel Hacks (eBook)

Franz Böhm beschäftigt sich bereits seit über 20 Jahren intensiv mit Microsoft Excel und den freien Varianten OpenOffice/LibreOffice Calc und kennt daher die Gemeinsamkeiten, aber auch die Unterschiede dieser Programme.

Cover 1
Titel 3
Inhaltsverzeichnis 5
Einleitung 15
Kapitel 1: Datum und Uhrzeit 17
1.1 Zeitberechnungen 17
1.1.1 Eine aktualisierbare Uhrzeit einfügen 17
1.1.2 Zahlen in Minuten umwandeln 18
1.1.3 Wie errechnen Sie aus Minuten die Stunden und Minuten als Dezimalzahl? 18
1.1.4 Uhrzeiten runden 19
1.1.5 Mit Uhrzeiten rechnen (Stundenlohn – Tageslohn – Durchschnittsgeschwindigkeit) 21
1.1.6 Positive und negative Zeitdifferenzen darstellen 22
1.1.7 Berechnung der Arbeitszeit mit Pausenzeit nach sechs Stunden 23
1.1.8 In Industrieeinheiten (60 min = 100 Einheiten) erfasste Arbeitszeit in Stunden und Minuten umwandeln 24
1.2 Tagesberechnungen 25
1.2.1 Gestern – Heute – Morgen 25
1.2.2 Welches Datum hat der nächste Freitag? 26
1.2.3 Welches Datum hat der nächste »Freitag, der 13.«? 27
1.2.4 Der wievielte Freitag im Monat ist der 12.08.2022? 28
1.2.5 Den wievielten Tag des Jahres haben wir heute? 29
1.2.6 Wie viele Tage sind es bis zum Jahresende? 31
1.2.7 Individuelle Netto-Arbeitstage 32
1.2.8 Welches Datum hat man nach 10 Arbeitstagen? 34
1.3 Wochen- und Monatsberechnungen 34
1.3.1 Das Datum in einer Woche oder in einem Monat 34
1.3.2 Mit welchem Datum beginnt eine vorgegebene Kalenderwoche? 36
1.3.3 Wochenbeginn und Wochenende zu einem bestimmten Datum 37
1.3.4 Zu welcher Kalenderwoche gehört das Datum? 38
1.3.5 Der Wochentag ohne Zahlenformat 39
1.3.6 Der erste Tag eines Monats als kopierbare Spaltenüberschrift oder kopierbare Zeilenbeschriftung 41
1.3.7 Der letzte Tag eines Monats 42
1.3.8 Der erste oder letzte Arbeitstag eines Monats 43
1.3.9 Die Anzahl der Monate berechnen 44
1.3.10 Monate in Jahre und Monate aufteilen 46
1.3.11 Monate mit weniger als fünf Buchstaben werden ausgeschrieben, die restlichen abgekürzt 47
1.4 Quartals-, Halbjahres- und Jahresberechnungen 48
1.4.1 In welches Quartal fällt das Datum? 48
1.4.2 Quartalsanfang – Quartalsende, Grundlage zur Berechnung des letzten Kündigungstermins 49
1.4.3 Halbjahresanfang – Halbjahresende 51
1.4.4 Beginn von Sommer- oder Winterzeit berechnen 52
1.4.5 Wann haben wir ein Schaltjahr? 53
1.4.6 Ein immerwährender Jahreskalender 55
1.5 Geburtstage und Feiertage 57
1.5.1 Wie alt ist Tante Anna? 57
1.5.2 Wie viele Tage sind es bis zum nächsten Geburtstag? 59
1.5.3 Eine Geburtstagsliste jahresunabhängig sortieren 60
1.5.4 Eine Geburtstagsliste so sortieren, dass immer der nächste Geburtstag angezeigt wird 61
1.5.5 Bevorstehende Geburtstage der nächsten 7 Tage hervorheben 63
1.5.6 Geburtstage des aktuellen Monats farbig hervorheben 65
1.5.7 Runde Geburtstage anzeigen 66
1.5.8 An welchem Tag ist Muttertag? 69
1.5.9 Auf welches Datum fällt der Ostersonntag und welche Feiertage hängen von Ostern ab? 69
1.5.10 Welche Feiertage hängen vom ersten Weihnachtsfeiertag ab und wie werden sie errechnet? 71
1.6 Zeitspannen berechnen 72
1.6.1 Anzahl der Tage eines Zeitraums mit SUMMENPRODUKT() 72
1.6.2 Anzahl der Tage mit SUMME() und WENN() 74
1.6.3 Zeitraum aus einem Datumsbereich ermitteln und anzeigen 76
1.6.4 Datumsdifferenz in Jahren, Monaten und Tagen 77
1.6.5 Jahresdifferenz mit Nachkommastellen 78
1.6.6 Mindesthaltbarkeitsdatum durch Datumsvergleich überprüfen und anzeigen 79
1.6.7 Wann läuft der Garantiezeitraum ab? 80
1.6.8 Wann läuft die Kündigungsfrist ab, wann müssen Sie kündigen? 81
1.7 Datumsteile und Darstellung 83
1.7.1 Ein Datum in Einzelteile zerlegen 83
1.7.2 Datum und Text kombinieren 84
Kapitel 2: Finanzfunktionen 85
2.1 Kapital und Anlagen 85
2.1.1 Wie lange müssen Sie sparen, um sich ein Auto für 40.000 € kaufen zu können? 85
2.1.2 Wie viele Jahre müssen Sie Ihr Geld anlegen, um ein bestimmtes Kapital zu erreichen? 86
2.1.3 Wie viele Zinsen erhalten Sie für Ihr angelegtes Kapital? 87
2.1.4 Wie hoch ist der Auszahlungsbetrag eines Festgeldes? 88
2.1.5 Wie hoch ist der Auszahlungsbetrag eines Wertpapiers mit festem Zinssatz zu einem gegebenen Zeitpunkt? 89
2.1.6 Ein Zinsangebot mit Ablaufdatum 90
2.2 Kredite und Zinsen 91
2.2.1 Wie hoch ist die monatliche Tilgungsrate eines Bankkredits? 91
2.2.2 Welchen Kredit können Sie sich leisten? 92
2.2.3 Wie hoch ist der Zinssatz? 93
2.2.4 Wie viele Zinstage sind es bis zum Jahresende? 94
2.2.5 Wie hoch ist die Zinsbelastung eines Darlehens im dritten Kalenderjahr? 95
2.2.6 Wie hoch ist der Tilgungsbetrag des Darlehens im dritten Kalenderjahr? 96
2.2.7 Wann haben Sie Ihr Bausparkassendarlehen getilgt, wie hoch ist der aktuelle Schuldsaldo? 97
2.3 Abschreibung 98
2.3.1 Wie berechnen Sie die lineare Abschreibung für Ihr Auto? 98
2.3.2 Wie berechnen Sie die arithmetisch-degressive Abschreibung für eine Maschine? 99
2.3.3 Wechsel von der degressiven zur linearen Abschreibung 101
Kapitel 3: Informationsfunktionen 103
3.1 Zellinhalte prüfen 103
3.1.1 Ist eine Zahl eingetragen oder nicht? 103
3.1.2 Enthält die Zelle einen Textwert? 104
3.1.3 Enthält die Zelle ein Datum? 105
3.1.4 Ist die Zelle leer? 106
3.1.5 Ist die Spalte richtig sortiert? 107
3.1.6 Fehlerwerte aufspüren und Fehlermeldungen vermeiden 108
3.2 Zellposition und Name 110
3.2.1 In welcher Zeile oder in welcher Spalte befindet sich die Zelle? 110
3.2.2 Dateinamen ermitteln und in eine Zelle einfügen 111
Kapitel 4: Mathematik – Statistik 113
4.1 Summieren 113
4.1.1 Summe der sieben größten / kleinsten Zahlen eines Bereichs 113
4.1.2 Summe eines Bereichs mit einer Bedingung 114
4.1.3 Werte addieren, die bestimmte Kriterien erfüllen 116
4.1.4 Summe jeder x-ten Zeile oder Spalte 117
4.1.5 Summe der letzten Werte verschieden langer Spalten 118
4.1.6 Zeilensumme bis zu einer bestimmten Spalte 119
4.1.7 Die Werte einer bestimmten Kalenderwoche addieren 121
4.1.8 Laufende Summen berechnen 123
4.1.9 Summieren von gerundeten Werten 124
4.1.10 Den Bezug der Summen-Formel fixieren 125
4.1.11 Summe in Abhängigkeit vom Anfangsbuchstaben 126
4.1.12 Summierung eines Bereichs mit Fehlerwerten 127
4.1.13 Summieren mit der Funktion AGGREGAT() – Fehlerwerte ignorieren 128
4.2 Umsatz- und Gewinnermittlung 129
4.2.1 Den Tagesumsatz errechnen 129
4.2.2 Die Summe der Umsätze von jedem Montag eines Zeitraums 130
4.2.3 Den Monatsumsatz berechnen 132
4.2.4 Der Umsatz eines bestimmten Quartals 133
4.2.5 Der Umsatz vom 1. Januar bis zu einem bestimmten Tag 134
4.2.6 Wie errechnen Sie den Umsatz eines bestimmten Zeitraums in einer chronologisch unsortierten Liste? 135
4.2.7 Einnahmen und Ausgaben getrennt aufsummieren 136
4.2.8 Alle Zahlungseingänge eines bestimmten Kunden finden 137
4.2.9 Umsatz eines Mitarbeiters aus einer Jahrestabelle filtern 140
4.2.10 Der Tag mit dem höchsten Umsatz! 141
4.2.11 Wie entwickelt sich der Umsatz im nächsten Halbjahr? 142
4.2.12 Die Gewinnentwicklung zweier Jahre vergleichen 144
4.2.13 Die Umsatzprognose laufend aktualisieren 145
4.3 Zellen und Werte zählen 146
4.3.1 Alle Zellen zählen, die eine bestimmte Bedingung erfüllen 146
4.3.2 Anzahl bestimmter Werte auf Grundlage mehrerer Kriterien 147
4.3.3 Wie viele Zellen enthalten Werte, wie viele sind leer – wie viele Athleten erreichten das Ziel? 148
4.3.4 Leere Zellen in Abhängigkeit anderer Zellen farbig anzeigen und zählen 150
4.3.5 Wie viele Werte zwischen 25 und 30 Grad Celsius enthält die Spalte? 153
4.3.6 Anzahl unterschiedlicher Einträge in einem Tabellenbereich 155
4.3.7 Anzahl unterschiedlicher Einträge in einer gefilterten Liste 156
4.3.8 Duplikate finden 157
4.4 Rangfolgen ermitteln 159
4.4.1 Zelle mit der kleinsten bzw. größten Zahl ermitteln – »relative« und »absolute« Zelladressierung 159
4.4.2 Die Rangfolge in der Reihe ermitteln 160
4.4.3 Rangfolge in einem nicht zusammenhängenden Bereich 161
4.4.4 Eine Siegerliste mit SVERWEIS() und WENNFEHLER() – Zelleninhalte in unterschiedlichen Bereichen suchen 163
4.4.5 Fortlaufende Rangfolge auch bei identischen Ergebnissen 164
4.4.6 Eine eindeutige Reihenfolge trotz gleichem Rang 166
4.4.7 Eine dynamische »TOP 10«-Liste 168
4.4.8 Siegerliste mit Rang und Namen durch die Funktionen INDEX() und VERGLEICH() 170
4.5 Runden 171
4.5.1 Zahlen AUFRUNDEN() oder ABRUNDEN() 171
4.5.2 Das Ergebnis auf eine bestimmte Dezimalzahl runden 172
4.5.3 Alle Beträge mit VRUNDEN() auf ein Vielfaches runden 173
4.5.4 Rechnungsbeträge mit OBERGRENZE() auf 5 Cent auf- oder abrunden 174
4.5.5 Zahlen auf glatte 100 oder 1000 Einheiten runden 175
4.5.6 Auf GERADE() bzw. UNGERADE() Zahlen runden 176
4.5.7 Als Ergebnis einer Berechnung nur die Zahl links vom Komma anzeigen 178
4.6 Mittelwertberechnung 179
4.6.1 Welcher Zahlenwert liegt in der Mitte der Zahlenreihe? 179
4.6.2 Wie erhalten Sie den Mittelwert einer Datenreihe ohne »Ausreißerwerte«? 180
4.6.3 Das gewogene Mittel berechnen 181
4.6.4 Einen Mittelwert ohne Einbeziehung von »0«-Werten bilden 182
4.7 Weitere Rechenfunktionen 183
4.7.1 Maßeinheiten UMWANDELN() 183
4.7.2 Währungen umrechnen und darstellen 184
4.7.3 Das erste und das letzte Auftreten einer Zahl erfassen 185
4.7.4 Ermittlung des x-größten Werts 186
4.7.5 Differenz zwischen der aktuellen Zeile und der Zeile mit dem letzten Eintrag ermitteln 188
4.7.6 Wie errechnen Sie den Sinus eines bestimmten Winkels? 189
4.7.7 Potenzieren und Wurzel ziehen 190
4.8 Rechenfunktionen in der Praxis 192
4.8.1 Ein Haushaltsbuch mit der Funktion SUMMENPRODUKT() 192
4.8.2 Kfz-Kosten-Verwaltung mit den Funktionen SUMME() und WENN() 194
4.8.3 Kilometergeld nach Entfernungspauschalen berechnen 195
4.8.4 Die Altersgruppen eines Vereins ermitteln 197
4.8.5 Das häufigste Ergebnis einer Versuchsreihe 198
4.8.6 Jahresdurchschnittsnote berechnen 199
4.8.7 Wie hoch sind die Gewinnchancen, wie viele mögliche KOMBINATIONEN() gibt es? 200
Kapitel 5: Tabelle und Datenbank 203
5.1 Zeilen und Spalten 203
5.1.1 x-letzter Eintrag einer Zeile und dessen Zellbezug 203
5.1.2 x-letzter Spaltenwert bei lückenhaften Einträgen 205
5.1.3 Summe der Zahlen einer bestimmten Zeile 207
5.1.4 Wert jeder x-ten Zelle einer Spalte ausgeben 208
5.1.5 Eine Zeilennummerierung erstellen 210
5.1.6 Eine laufende Nummerierung mit ANZAHL2() 211
5.1.7 Eine laufende Nummerierung mit KGRÖSSTE() 212
5.1.8 Jede 5. Zeile nummerieren 214
5.1.9 ZEILEN() und SPALTEN() zählen 215
5.2 Zellinhalte suchen und anzeigen 216
5.2.1 Steht ein bestimmter Wert im Zellbereich? 216
5.2.2 Texte mit SVERWEIS() suchen und zusammenführen 217
5.2.3 SVERWEIS() nach links 218
5.2.4 Mit INDEX() den Inhalt bestimmter Zellen anzeigen 220
5.2.5 Den aktuellen Saldo in der ersten Zeile anzeigen 222
5.2.6 Wert am Schnittpunkt von Zeile und Spalte einer Matrix 223
5.2.7 Wertesuche mit Rückgabe von Zeilen und Spaltenreferenz 224
5.2.8 Eine Tabelle drehen 226
5.3 Zellbereiche auswerten 227
5.3.1 Den günstigsten Preis eines Artikels finden 227
5.3.2 Summierung des Datenbankbereichs mit mehreren Suchkriterien 228
5.3.3 Die Summe eines bestimmten Zeitraums berechnen 230
5.3.4 Summe eines beliebigen Zeitraums mit SUMMEWENNS() berechnen 231
5.3.5 Summe einer gefilterten Liste mit TEILERGEBNIS() 232
5.3.6 Summierung bis zur nächsten Leerzelle 233
5.3.7 Datenauswertung mit benannten Formeln und der Funktion BEREICH.VERSCHIEBEN() 235
5.3.8 Kfz-Kosten-Berechnung in einem »dynamischen Bereich« 238
5.3.9 Anzahl der Besucher an einem bestimmten Tag – Datenauswertung mit der Funktion ERSTERWERT() oder WAHL() 241
5.3.10 Noten anhand einer Punktetabelle vergeben 243
5.4 Zufallszahlen 244
5.4.1 Zufallszahlen erzeugen 244
5.4.2 Zufallszahlen ohne Duplikate erstellen 245
5.4.3 Zufälligen Eintrag aus einer Liste auswählen 247
5.4.4 Zelleninhalt aus einem Tabellenbereich zufällig auswählen 248
5.4.5 Listeneinträge mischen 250
Kapitel 6: Textfunktionen 253
6.1 Texte verbinden oder trennen 253
6.1.1 Inhalt mehrerer Zellen in einer anderen Zelle anzeigen 253
6.1.2 Textbereiche verbinden 254
6.1.3 Den Familiennamen mit einer förmlichen Anrede verbinden 255
6.1.4 Ein Zeilenumbruch mit der Funktion ZEICHEN() 256
6.1.5 Straßennamen und Hausnummer trennen 257
6.1.6 Bankleitzahl aus einer IBAN extrahieren 259
6.2 Texte und Zeichenfolgen suchen 260
6.2.1 Texte SUCHEN() und FINDEN() 260
6.2.2 Die ersten fünf Zeichen von LINKS() oder RECHTS() 262
6.2.3 Die Länge einer Zeichenkette ermitteln 263
6.2.4 Position der ersten Ziffer in einer Zeichenfolge finden 264
6.2.5 Das erste Wort einer Zeichenkette 266
6.2.6 Das letzte Wort einer Zeichenkette 267
6.2.7 Das zweite Wort einer Zeichenkette 269
6.2.8 Ist der Name in der Liste? 270
6.2.9 Texte und Zeichenfolgen suchen und vergleichen 271
6.2.10 Die Häufigkeit eines Zeichens ermitteln 272
6.2.11 Anzahl der Wörter in einer Tabellenzelle ermitteln 273
6.3 Zeichenketten bearbeiten 274
6.3.1 Text in Großbuchstaben oder Kleinbuchstaben umwandeln 274
6.3.2 Zeichenfolgen wiederholen 275
6.3.3 Leerzeichen zwischen Text und Zahl einfügen 277
6.3.4 Wörter umdrehen und Namen und Vornamen trennen 278
6.3.5 Das Minuszeichen von rechts nach links setzen 280
6.3.6 Zeichenfolgen ERSETZEN() 281
6.3.7 Nummerierungen sortieren 282
6.3.8 Nicht druckbare Zeichen und überflüssige Leerzeichen mit SÄUBERN() und GLÄTTEN() entfernen 284
6.3.9 Aus einem Text alle Leerzeichen entfernen, Zeichenfolgen ersetzen oder auswechseln 285
6.3.10 Einen leeren Zellbereich mit dem Währungsstrich — durchstreichen (entwerten) 286
6.3.11 Alle Umlaute entfernen oder Umlaute einfügen 287
6.3.12 Ein Häkchen in einer Zelle erscheinen lassen, um etwas als »erledigt« zu markieren 289
6.3.13 Versteckte Kommentare 290
6.4 Textwerte und Zeichencodes 291
6.4.1 Zahlen mit einer festen Nachkommazahl als Textwert ausgeben 291
6.4.2 Mit Textwerten rechnen 292
6.4.3 Zeichencodes ermitteln 294
6.4.4 RÖMISCH() – ARABISCH() 295
Kapitel 7: Formatvorlagen und weitere nützliche Funktionen 297
7.1 Bedingte Formatierungen 297
7.1.1 Alle Werte über 5.000 € hervorheben 297
7.1.2 Werte über 5.000 € gelb und Werte über 7.000 € grün hervorheben 298
7.1.3 Den niedrigsten und den höchsten Wert einer Reihe markieren 299
7.1.4 Alle Zellen mit dem Wert 0 grün einfärben 300
7.1.5 Leere Zellen hervorheben 301
7.1.6 Textzellen aufspüren 302
7.1.7 Zellen mit Formeln suchen 303
7.1.8 Ungeschützte Formelzellen einfärben 304
7.1.9 Leerzeichen suchen 305
7.1.10 Wochenenden farbig einfärben 306
7.1.11 Drei Tage vor und nach dem aktuellen Datum markieren 307
7.1.12 Bedingte Formatierung als Suchfunktion 308
7.1.13 Nicht identische Zellen mithilfe einer bedingten Formatierung markieren 310
7.1.14 Text als Suchbedingung 310
7.1.15 Duplikate mit einer bedingten Formatierung aufspüren 312
7.1.16 Suchen mit zwei Bedingungen (Vor- und Nachname müssen stimmen) 312
7.1.17 Zwischensummen farbig hervorheben 313
7.1.18 Ampelfunktion für optische Wertanzeige 314
7.2 Zeilen und Spalten einfärben 317
7.2.1 Jede zweite Zeile einfärben 317
7.2.2 Jede zweite Spalte einfärben 318
7.2.3 Jede zweite Zeile erst dann einfärben, wenn sie ausgefüllt wird 319
7.2.4 Jede zweite Zeile auch beim Filtern einfärben 320
7.2.5 Mehrere zusammenhängende Zellen einfärben, wenn in einer der Zellen ein Eintrag erfolgt 321
7.2.6 Drei unterschiedliche Zeilenfarben 322
7.2.7 Zeilen- und Spaltenfarbe als Lesehilfe 323
7.2.8 Fehlerhafte Einträge farbig kennzeichnen 325
7.3 Zahlenformate 327
7.3.1 Zahlen im Tausend-Euro-Format oder in Millionen darstellen 328
7.3.2 Nur bestimmte Zahlen anzeigen 329
7.3.3 Ein bedingtes Zahlenformat mit Text und Farbe 330
7.3.4 Ein Zahlenformat zur Entwertung des Leerraums einer Betragszelle mit einem beliebigen Zeichen 331
7.4 Eigene Konstanten, Funktionen und Datenprüfung 332
7.4.1 Eigene Konstanten erstellen – benannte Formeln 332
7.4.2 Eigene Funktionen erstellen 333
7.4.3 Doppelte Einträge in einer Spalte oder in einer Zeile verhindern 336
7.4.4 Dynamische Dropdownlisten in Tabellen 338
Kapitel 8: Beispielanwendungen zum Download 343
8.1 Haushaltsbuch 343
8.2 Quittungsformular 344
8.3 Urlaubskalender 344
8.4 Jahreskalender 345
8.5 Sternzeichenberechnung 346
Stichwortverzeichnis 347