Excel 2016 Formeln und Funktionen (eBook)

Ignatz Schels ist Technik-Informatiker, VBA-Spezialist und zertifizierter Projekt-Fachmann (GPM/IPMA). Er leitet Seminare und Workshops mit Schwerpunkt "Excel und Access im Controlling und Projektmanagement". Sein Spezialgebiet ist die Optimierung von Unternehmensprozessen im ERP- und Microsoft Office-Umfeld. Mit über 50 Fachbüchern, darunter einige Bestseller, ist er einer der erfolgreichsten IT-Autoren.

Inhalt 6
Alphabetische Übersicht der Praxisbeispiele 22
Vorwort 26
Ein Blick auf den Autor 28
1 Das Prinzip der Tabellenkalkulation 30
1.1 Von VisiCalc bis Excel 2016 30
1.1.1 Lotus und Excel 31
1.1.2 Excel wird Standard 32
1.1.3 Mit Excel in der Cloud 32
1.2 Oberfläche und Dateiformate 34
1.2.1 Dateinamenerweiterung einschalten 34
1.2.2 Nach dem Start 35
1.2.3 Von der Multifunktionsleiste zum Menüband 36
1.2.4 Startmenü und Backstage (Datei-Menü) 36
1.2.5 Registerkarten 38
1.2.6 Optionen 39
1.2.7 Menüband anpassen 39
1.2.8 Symbolleiste für den Schnellzugriff 40
1.2.9 Tabellenblattdimensionen 41
1.2.10 Dateiformate 42
1.3 Die Funktionsbibliothek 43
1.3.1 Neue Funktionen in Excel 2010 43
1.3.2 Neue Funktionen in Excel 2013 50
1.3.3 Neue Funktionen in Excel 2016 53
1.4 Optimiert arbeiten mit Funktionen 54
1.4.1 Auf der Oberfläche 54
1.4.2 Tastenkombinationen 56
1.4.3 Optimiert arbeiten in der Tabelle 57
1.5 Eingabe- und Editierhilfen 59
1.5.1 Bezüge konstruieren 59
1.5.2 Formel in mehrere Zellen schreiben 59
1.5.3 Klammerübereinstimmung 60
1.5.4 Großschreibung bei korrekter Eingabe 60
1.5.5 AutoVervollständigen (Formelhilfe) 61
1.5.6 Kompatible Funktionen in der Formelhilfe 62
1.5.7 Abhängige Bereiche werden markiert 62
1.5.8 Alle Formelzellen auswählen 62
1.5.9 Die Blitzvorschau 63
1.5.10 Formeln berechnen 64
1.5.11 Formeln teilberechnen 64
1.5.12 Formeln durch ihre Werte ersetzen 65
1.5.13 Die Formelansicht 66
1.6 Formeln konstruieren 67
1.6.1 Arithmetische Operatoren 68
1.6.2 „Punkt vor Strich“-Regelung 68
1.6.3 Berechnungsreihenfolge 68
1.6.4 Logische Operatoren 68
1.6.5 Textverkettungsoperator 69
1.6.6 Bezüge in Formeln 69
1.6.7 Der Z1S1-Bezug 70
1.6.8 Relative und absolute Bezüge 72
1.6.9 Trennzeichen und Sonderzeichen in Formeln 74
1.6.10 Praxisbeispiel: Abschreibung berechnen 74
1.7 Fehlersuche in der Formel 76
1.7.1 Drei Fehlergruppen 77
1.7.2 Die Kette aus Nummernzeichen (####) 77
1.7.3 Kompatibilitätsfehler mit Analyse-Funktionen 78
1.7.4 Fehlermarkierung in der Formel 79
1.7.5 Fehlermeldungen 79
1.7.6 Fehlermeldungen in der Zelle 81
1.7.7 Informationsfunktionen zur Fehlerprüfung 86
1.7.8 Praxisbeispiel: Materialschein 86
1.7.9 Formelüberwachung 87
1.8 Mit Bereichsnamen arbeiten 93
1.8.1 Namen für eine Zelle festlegen 93
1.8.2 Namen verwalten im Namens-Manager 94
1.8.3 Bereichsnamen aus Zellen übernehmen 95
1.8.4 Rechnen mit Bereichsnamen 97
1.8.5 Lokale und globale Bereichsnamen 98
1.8.6 Konstanten als Bereichsnamen 101
1.9 Mit Tabellen arbeiten 102
1.9.1 Das Problem mit der Excel-Datenbank 102
1.9.2 Was sind Tabellen? 103
1.9.3 Eine Tabelle erstellen 104
1.9.4 Dynamische Ergebniszeilenfunktionen 105
1.9.5 Strukturierte Verweise 106
2 Rechnen mit Funktionen 110
2.1 Funktionen in der Statusleiste 110
2.2 Funktionen schreiben 111
2.2.1 Erste Funktionen 112
2.3 SUMME() und AutoSumme 114
2.3.1 Die AutoSumme 115
2.3.2 Zwischensummen berechnen 117
2.3.3 Weitere Funktionen im Symbol AutoSumme 118
2.4 Die Funktionsbibliothek 119
2.4.1 Funktion einfügen 120
2.4.2 Funktion suchen 122
2.4.3 Die Funktionsargumente 123
2.5 Funktionen schachteln 125
2.5.1 Praxisbeispiel: Liefertermin berechnen 125
2.6 Matrixformeln erstellen 127
2.6.1 Praxisbeispiel: Umsatzauswertung 128
2.6.2 Praxisbeispiel: Eingaben-/Ausgabenrechnung 130
2.7 Mehrsprachig arbeiten mit Funktionen 130
2.7.1 Der Funktions-Translator 131
2.8 Versteckte Funktionen 132
2.8.1 Die Funktion DATEDIF() 132
2.8.2 Die Funktionen FINDENB(), SUCHENB() und ERSETZENB() 134
2.8.3 Die Funktion ISO.OBERGRENZE() 134
2.8.4 Die Funktion JIS() 135
2.8.5 Die Funktion LÄNGEB() 135
2.8.6 Die Funktion LINKSB() 135
2.8.7 Die Funktion OBERGRENZE.GENAU() 136
3 Textfunktionen 138
3.1 Zahl, Text und Textformat 139
3.1.1 Das Textformat 140
3.1.2 Zahl als Text 141
3.1.3 Rechnen mit Text 142
3.2 Die Funktion BAHTTEXT() 143
3.3 Die Funktion DM() 143
3.4 Die Funktion ERSETZEN() 143
3.5 Die Funktion FEST() 144
3.6 Die Funktionen FINDEN() und SUCHEN() 145
3.7 Die Funktion GLÄTTEN() 145
3.7.1 Praxisbeispiel: Mitarbeiterliste säubern 146
3.8 Die Funktionen GROSS(), GROSS2() und KLEIN() 147
3.9 Die Funktion IDENTISCH() 148
3.10 Die Funktionen LINKS() und RECHTS() 149
3.10.1 Praxisbeispiel: EAN-Nummern analysieren 149
3.11 Die Funktion SÄUBERN() 151
3.12 Die Funktion TEIL() 151
3.12.1 Praxisbeispiel: Teilenummern analysieren 152
3.13 Die Funktion TEXT() 153
3.13.1 Praxisbeispiel: Wochentage der Geburtstage ausgeben 154
3.14 Die Funktion LÄNGE() 155
3.14.1 Praxisbeispiel: Vornamen und Nachnamen trennen 155
3.14.2 Praxisbeispiel: Minuszeichen rechts von der Zahl 156
3.14.3 Praxisbeispiel: Quersumme berechnen 157
3.15 Die Funktion VERKETTEN() 158
3.15.1 Praxisbeispiel: Organigramm beschriften 159
3.16 Die Funktion WECHSELN() 161
3.16.1 Praxisbeispiel: Fremdwährungen – Punkte durch Kommas ersetzen 162
3.16.1 Praxisbeispiel: Umlaute und Sonderzeichen austauschen 163
3.16.2 Praxisbeispiel: Zeilenumbrüche entfernen 164
3.17 Die Funktion WIEDERHOLEN() 164
3.17.1 Praxisbeispiel: Artikelnummer mit Nullen auffüllen 164
3.17.2 Praxisbeispiel: Balkenreihe per Funktion erzeugen 166
3.18 Zeichencode-Funktionen 167
3.18.1 Zeichencodes feststellen 167
3.18.2 Codezahl eines Zeichens: CODE() und UNICODE() 168
3.18.3 Praxisbeispiel: Numerische und alphanumerische Einträge sortieren 169
3.18.4 Das Zeichen einer Codezahl: ZEICHEN() und UNIZEICHEN() 170
3.19 Die Funktion ZAHLENWERT() 171
3.19.1 Praxisbeispiel: Fremdwährungen umrechnen 172
4 Datenbankfunktionen 174
4.1 Datenbanken, Listen, Tabellen 174
4.1.1 Von der Liste zur Tabelle 175
4.2 Der Namens-Manager 181
4.2.1 Globale und lokale Bereichsnamen 182
4.2.2 Empfohlene Namenskonventionen für Tabellennamen und Bereichsnamen 182
4.2.3 Tabellennamen über Bereichsnamen verwenden 182
4.2.4 Die dynamische Datenbank 183
4.2.5 Praxisbeispiel: Umsatzauswertung Mehrfachbereiche 184
4.3 Die Datenbankfunktionen 187
4.3.1 Die Funktion DBANZAHL() 189
4.3.2 Die Funktion DBANZAHL2() 191
4.3.3 Praxisbeispiel: Lagermengen zählen 191
4.3.4 Die Funktion DBAUSZUG() 192
4.3.5 Praxisbeispiel: Artikel über Artikelnummer suchen 193
4.4 Die Funktionen DBMAX() und DBMIN() 194
4.4.1 Praxisbeispiel: eBay-Gebote verwalten 194
4.4.2 Die Funktion DBSUMME() 196
4.4.3 Praxisbeispiel: Umsatzbericht 196
4.5 Die Funktion DBMITTELWERT() 198
4.5.1 Praxisbeispiel: Durchschnittsumsatz berechnen 199
4.5.2 Praxisbeispiel: Statistik mit DB-Funktionen 199
5 Statistische Funktionen 204
5.1 Kompatible Funktionen aus früheren Versionen 210
5.2 Analyse-Funktionen 211
5.2.1 Praxisbeispiel: Einfaktorielle Varianzanalyse 213
5.3 Die A-Funktionen 215
5.4 Funktionen prüfen in der Statuszeile 216
5.5 Die Funktion ACHSENABSCHNITT() 217
5.6 Die Funktionen ANZAHL() und ANZAHL2() 218
5.6.1 Praxisbeispiel: Messwerte analysieren 219
5.6.2 Praxisbeispiel: Werte zählen in gefilterten Listen 220
5.7 Die Funktion ANZAHLLEEREZELLEN() 221
5.8 Die Funktion BESTIMMTHEITSMASS() 222
5.8.1 Praxisbeispiel: Trendlinie 222
5.9 Die Funktionen BETA.INV() und BETA.VERT() 223
5.10 Die Funktionen BINOM.INV(), BINOM.VERT() und BINOM.VERT.BEREICH() 224
5.10.1 Praxisbeispiel: Würfel 225
5.11 Die Funktionen CHIQU.INV() und CHIQU.INV.RE() 225
5.12 Die Funktion CHIQU.TEST() 226
5.13 Die Funktionen CHIQU.VERT() und CHIQU.VERT.RE 227
5.14 Die Funktion EXPON.VERT() 227
5.14.1 Praxisbeispiel: Reparaturwahrscheinlichkeit 228
5.15 Die Funktionen F.INV() und F.INV.RE() 228
5.16 Die Funktionen F.VERT() und F.VERT.RE() 229
5.17 Die Funktion FISHER() 230
5.18 Die Funktion FISHERINV() 231
5.18.1 Praxisbeispiel: Werbungskosten 231
5.19 Die Funktion G.TEST() 232
5.20 Die Funktion F.TEST() 233
5.21 Die Funktion GAMMA() 233
5.22 Die Funktion GAMMA.INV() 234
5.23 Die Funktion GAMMA.VERT() 234
5.24 Die Funktionen GAMMALN() und GAMMALN.GENAU() 235
5.25 Die Funktion GAUSS() 235
5.26 Die Funktion GEOMITTEL() 236
5.26.1 Praxisbeispiel: Umsatzsteigerung 236
5.27 Die Funktion GESTUTZTMITTEL() 237
5.27.1 Praxisbeispiel: Mitarbeiterbefragung 238
5.28 Die Funktion HÄUFIGKEIT() 239
5.28.1 Praxisbeispiel: Altersgruppen der Mitarbeiter berechnen 239
5.28.2 Praxisbeispiel: Histogramm Altersstruktur 241
5.29 Die Funktion HYPGEOM.VERT() 241
5.29.1 Praxisbeispiel: Schokoriegel 242
5.29.2 Praxisbeispiel: Lotto 242
5.30 Die Funktionen KGRÖSSTE() und KKLEINSTE() 243
5.30.1 Praxisbeispiel: Auswertung einer Sportergebnisliste 244
5.31 Die Funktionen KONFIDENZ.NORM() und KONFIDENZ.T() 245
5.32 Die Funktion KORREL() 246
5.32.1 Praxisbeispiel: Störche und Geburtenrate 246
5.33 Die Funktionen KOVARIANZ.P() und KOVARIANZ.S() 248
5.34 Die Funktion KURT() 248
5.35 Die Funktion LOGNORM.INV() 249
5.36 Die Funktion LOGNORM.VERT() 249
5.37 Die Funktionen MAX() und MIN() 249
5.37.1 Praxisbeispiel: Kostenstellenanalyse 250
5.38 Die Funktion MEDIAN() 251
5.38.1 Praxisbeispiel: Bundesjugendspiele 252
5.39 Die Funktion MITTELWERT() 253
5.39.1 Praxisbeispiel: Benzinverbrauch berechnen 253
5.40 Die Funktion MITTELWERTWENN() 254
5.40.1 Praxisbeispiel: Umsatzauswertung 255
5.41 Die Funktion MITTELWERTWENNS() 255
5.41.1 Praxisbeispiel: Human Capital Index 255
5.42 Die Funktionen NORM.INV() und NORM.S.INV() 256
5.43 Die Funktion NORM.S.VERT() 257
5.44 Die Funktion PEARSON() 257
5.45 Die Funktion PHI() 258
5.46 Die Funktion POISSON.VERT() 258
5.47 Die Funktion PROGNOSE.LINEAR() 258
5.47.1 Praxisbeispiel: Umsatzprognose 258
5.48 Die Funktion PROGNOSE.ETS() 259
5.49 Die Funktion PROGNOSE.ETS.SAISONALITÄT() 260
5.50 Die Funktion PROGNOSE.ETS.KONFINT() 261
5.51 Die Funktion PROGNOSE.ETS.STAT() 261
5.51.1 Praxisbeispiel: Prognoseblatt 262
5.52 Die Funktionen QUANTIL.EXKL() und QUANTIL.INKL() 263
5.53 Die Funktionen QUARTILE.INKL() und QUARTILE.EXKL() 263
5.53.1 Praxisbeispiel: Umsatzauswertung 264
5.54 Die Funktionen QUANTILSRANG.EXKL()und QUANTILSRANG.INKL() 264
5.55 Die Funktion RANG.GLEICH() 265
5.55.1 Praxisbeispiel: ABC-Analyse 265
5.55.2 Gleicher Rang für doppelte Werte 266
5.56 Die Funktion RANG.MITTELW() 267
5.57 Die Funktionen RGP() und RKP() 267
5.57.1 Kennziffern der Funktion RGP() 268
5.58 Die Funktion SCHÄTZER() 269
5.59 Die Funktionen SCHIEFE() und SCHIEFE.P() 270
5.60 Die Funktionen STABW.N() und STABW.S() 270
5.61 Die Funktion STANDARDISIERUNG() 271
5.62 Die Funktion STEIGUNG() 271
5.63 Die Funktion STFEHLERXY() 271
5.64 Die Funktion SUMQUADABW() 272
5.65 Die Funktionen für den t-Test 272
5.66 Die Funktion TREND() 273
5.66.1 Praxisbeispiel: Trend bei Grundstückspreisen ermitteln 273
5.67 Die Funktionen VAR.P()und VAR.S(), VARIANZA() und VARIANZENA() 275
5.68 Die Funktionen VARIATION() und VARIATIONEN() 275
5.69 Die Funktion VARIATIONEN2() 276
5.70 Die Funktion WAHRSCH() 276
5.70.1 Praxisbeispiel: Maschinenausfälle 277
5.71 Die Funktion WEIBULL.VERT() 277
5.72 Die Funktion ZÄHLENWENN() 278
5.72.1 Praxisbeispiel: Rechnungsjournal 278
5.73 Die Funktion ZÄHLENWENNS() 279
5.73.1 Praxisbeispiel: Personalauswertung 280
6 Matrixfunktionen (Nachschlagen und Verweisen) 282
6.1 Funktionen für die Matrix 282
6.1.1 Aufruf über das Menüband 284
6.2 Die Funktion ADRESSE() 284
6.2.1 Praxisbeispiel: Projektkosten zum Stichtag ermitteln 286
6.3 Die Funktion BEREICH.VERSCHIEBEN() 289
6.3.1 Die Argumente 289
6.3.2 Dynamische Bereichsnamen 290
6.3.3 Praxisbeispiel: Dynamische Monatssummen mit Diagramm 291
6.3.4 Ein dynamisches Diagramm 292
6.4 Die Funktion BEREICHE() 294
6.5 Die Funktion HYPERLINK() 295
6.5.1 Hyperlinks für interne Tabellensteuerung 296
6.5.2 Praxisbeispiel: Hyperlink-Pfade konstruieren 297
6.6 Die Funktion VERGLEICH() 298
6.7 Die Funktion INDEX() 299
6.7.1 Praxisbeispiel: Datenbanken indizieren 300
6.7.2 Praxisbeispiel: Angebotsvergleich 301
6.7.3 Praxisbeispiel: Rechnungsformular mit Kundenauswahl 303
6.8 Die Funktion MTRANS() 305
6.9 Die Funktion SVERWEIS() 306
6.9.1 Praxisbeispiel: Preis ermitteln 306
6.9.2 Fehler abfangen im SVERWEIS() 309
6.9.3 Praxisbeispiel: Provisionsabrechnungen 309
6.10 Die Funktion WVERWEIS() 310
6.10.1 Praxisbeispiel: Mietobjekte abrechnen 311
6.11 Die Funktion VERWEIS() 312
6.11.1 Praxisbeispiel: Reisekostenabrechnung 312
6.12 Tipps für Verweisfunktionen 313
6.12.1 Suchmatrix benennen 313
6.12.2 Fehlermeldungen ausblenden 313
6.12.3 Gültigkeitslisten verwenden 314
6.13 Die Funktionen ZEILE() und SPALTE() 315
6.13.1 Praxisbeispiel: Letzte Buchung suchen 315
6.13.2 Praxisbeispiel: Kalender 317
6.14 Die Funktionen ZEILEN() und SPALTEN() 318
6.14.1 Praxisbeispiel: Datenbankberechnungen 318
6.15 Die Funktion INDIREKT() 319
6.15.1 Praxisbeispiel: Lottozahlenfinder 319
6.15.2 Praxisbeispiel: Tabellennamen in Formeln verwerten 320
6.16 Die Funktion WAHL() 322
6.16.1 Praxisbeispiel: Meilensteinplan durchsuchen 323
6.16.2 Praxisbeispiel: Optionsfelder auswerten 324
6.17 Die Funktion PIVOTDATENZUORDNEN() 325
6.17.1 Praxisbeispiel: Pivot-Tabellenbericht Umsatzauswertung 326
7 Datums- und Zeitfunktionen 330
7.1 Der Excel-Kalender 332
7.1.1 Zahlenformate 332
7.1.2 Jahreszahl zweistellig oder vierstellig? 333
7.1.3 Kalenderbeginn 1900 oder 1904 333
7.2 Rechnen mit Zeit 334
7.2.1 Die Zeit: Zahlenformat und 24-Stundenwert 334
7.2.2 Praxisbeispiel: Zeitwerte über 24 Stunden berechnen 335
7.2.3 Rechnen mit Minuszeiten 337
7.2.4 Negative Stundenwerte 338
7.2.5 Praxisbeispiel: Arbeitszeiten und Überstunden berechnen 338
7.3 Die Funktion HEUTE() 340
7.3.1 Die Funktion JETZT() 340
7.3.2 Praxisbeispiel: Serverkosten aus Serverzeit ermitteln 341
7.4 Die Funktion ARBEITSTAG() 342
7.4.1 Praxisbeispiel: Urlaubstage berechnen 342
7.5 Die Funktion ARBEITSTAG.INTL() 343
7.5.1 Praxisbeispiel: Arbeitstagberechnung mit flexiblen Wochenenden 344
7.5.2 Bitmuster für Wochenendregelung 345
7.6 Die Funktion BRTEILJAHRE() 347
7.7 Die Funktion DATUM() 347
7.7.1 Praxisbeispiel: Alter aus Geburtsdatum berechnen 348
7.7.2 Praxisbeispiel: Monatliche Stundenabrechnung 349
7.8 Die Funktionen JAHR(), MONAT() und TAG() 351
7.8.1 Praxisbeispiel: Quartal berechnen 351
7.8.2 Praxisbeispiel: Geburtstagsliste sortieren 352
7.9 Die Funktion WOCHENTAG() 353
7.9.1 Zahlenformat für den Wochentag 354
7.9.2 Praxisbeispiel: Wochentage im Kalender kennzeichnen 354
7.9.3 Praxisbeispiel: Stundenabrechnung nach Wochentag 355
7.10 Die Funktion DATEDIF() 356
7.11 Die Funktionen KALENDERWOCHE() und ISOKALENDERWOCHE() 358
7.11.1 KW nach DIN 358
7.11.2 KW nach ISO 359
7.11.3 Montag einer Kalenderwoche ermitteln 359
7.11.4 Praxisbeispiel: Kalenderwoche in der Stundenabrechnung 360
7.12 Die Funktion DATWERT() 361
7.12.1 Praxisbeispiel: Der letzte Werktag im Monat 361
7.13 Die Funktion EDATUM() 363
7.14 Die Funktion MONATSENDE() 363
7.14.1 Praxisbeispiel: Schaltjahre berechnen 363
7.15 Die Funktion NETTOARBEITSTAGE() 364
7.15.1 Praxisbeispiel: Produktionsübersicht 365
7.16 Die Funktion NETTOARBEITSTAGE.INT() 366
7.16.1 Praxisbeispiel: Nettoarbeitstagberechnung mit flexiblen Wochenenden 367
7.16.2 Bitmuster für Wochenendregelung 368
7.17 Die Funktion TAGE() 369
7.18 Die Funktion TAGE360() 369
7.19 Die Funktionen ZEIT() und ZEITWERT() 370
7.20 Die Zeitfunktionen STUNDE(), MINUTE() und SEKUNDE() 371
7.21 Kalender- und Feiertagsberechnung 372
7.21.1 Von kirchlichen und weltlichen Feiertagen 372
7.21.2 Feiertage pro Bundesland 372
7.21.3 Ostern berechnen mit Gauß 373
7.21.4 Die restlichen Feiertage 374
7.21.5 Praxisbeispiel: Terminkalender 375
7.21.6 Der Feiertagsrechner 377
8 Informationsfunktionen 380
8.1 Funktionssymbole in der Funktionsbibliothek 381
8.2 Die Funktion BLATT() 382
8.3 Die Funktion BLÄTTER() 382
8.3.1 Praxisbeispiel: 3D-Bezüge analysieren 382
8.4 Die Funktion NV() 383
8.5 Die Funktion FEHLER.TYP() 383
8.5.1 Praxisbeispiel: Umsatzliste auf Division durch 0 prüfen 384
8.6 Die Funktion TYP() 385
8.6.1 Praxisbeispiel: Zahlen und Texte finden 385
8.7 Die Funktion ZELLE() 386
8.7.1 Praxisbeispiel: Pfad und Dateiname auf jeder Druckseite 388
8.7.3 Praxisbeispiel: Währungsbeträge kennzeichnen 389
8.8 Die IST-Funktionen 391
8.8.1 Praxisbeispiel: Fehler im Soll/Ist-Vergleich absichern 392
8.8.2 Praxisbeispiel: SVERWEIS() im Rechnungsvordruck absichern 393
8.9 Die Funktion INFO() 394
8.9.1 Kompatibilität mit neuen Funktionen absichern 395
8.9.2 Ein VBA-Makro für weitere Systeminfos 396
9 Mathematische und trigonometrische Funktionen 398
9.1 Die Funktionen im Menüband 401
9.2 Die Funktion ABS() 402
9.2.1 Praxisbeispiel: Positive und negative Beträge summieren 403
9.3 Die Funktion AGGREGAT() 404
9.3.1 Funktionsnummern 405
9.3.2 Aggregate konstruieren mit der Formelhilfe 406
9.3.3 Praxisbeispiel: Durchschnittliche Anteile 407
9.3.4 Praxisbeispiel: Lagerwertberechnung 407
9.4 Die Funktion ARABISCH() 409
9.5 Die Funktionen AUFRUNDEN() und ABRUNDEN() 409
9.6 Die Funktion BASIS() 410
9.6.1 Praxisbeispiel: Binärtabelle 411
9.7 Die Funktion DEZIMAL() 411
9.8 Die Funktionen FAKULTÄT() und ZWEIFAKULTÄT() 412
9.9 Die Funktionen GERADE() und UNGERADE() 413
9.10 Die Funktionen KGV() und GGT() 413
9.10.1 Praxisbeispiel: Primfaktorzerlegung 414
9.11 Die Funktionen KOMBINATIONEN() und KOMBINATIONEN2() 415
9.11.1 Praxisbeispiel: Teambildung 415
9.12 Die Funktionen GANZZAHL() und KÜRZEN() 416
9.12.1 Praxisbeispiel: Industriestunden/-minuten berechnen 417
9.13 Die Funktion MEINHEIT() 418
9.14 Die Matrixfunktionen MINV(), MDET() und MMULT() 419
9.14.1 MDET() 419
9.14.2 MINV() 419
9.14.3 MMULT() 420
9.14.4 Praxisbeispiel: Innerbetriebliche Leistungsverrechnung 420
9.15 Die Funktionen OBERGRENZE() und UNTERGRENZE() 422
9.15.1 Praxisbeispiel: Runden auf Ober- und Untergrenze 423
9.16 Die Funktionen OBERGRENZE.MATHEMATIK() und UNTERGRENZE.MATHEMATIK() 423
9.17 Die Funktion POLYNOMIAL() 424
9.18 Die Funktion POTENZ() 424
9.19 Die Funktion POTENZREIHE() 425
9.20 Die Funktion PRODUKT() 425
9.20.1 Praxisbeispiel: Leasingraten ermitteln 425
9.21 Die Funktion QUOTIENT() 427
9.22 Die Funktion REST() 427
9.23 Die Funktion RÖMISCH() 429
9.24 Die Funktion RUNDEN() 430
9.24.1 Beispiele 430
9.24.2 Praxisbeispiel: Produktkalkulation mit Rundung 431
9.24.3 Praxisbeispiel: Maschinenlaufzeiten runden 432
9.25 Die Funktion SUMME() 433
9.25.1 Text und Wahrheitswerte 433
9.25.2 Multiplikationssummen 434
9.25.3 Praxisbeispiel: Matrixsumme für monatliche Kostenauswertung 435
9.26 Die Funktion SUMMEWENN() 436
9.26.1 Praxisbeispiel: Wochenstunden pro Mitarbeiter berechnen 437
9.27 Die Funktion SUMMEWENNS() 438
9.27.1 Praxisbeispiel: Umsatzauswertung 438
9.27.2 SUMMEWENNS() in Bereichen und Tabellen 439
9.28 Die Funktion SUMMENPRODUKT() 441
9.28.1 Summenprodukte mit Zeilennummern 442
9.28.2 Praxisbeispiel: Bestellwert ermitteln 442
9.28.3 SUMMENPRODUKT() mit Bedingung 444
9.28.4 Praxisbeispiel: Warengruppenanalyse (ABC-Analyse) 444
9.29 Die Funktion TEILERGEBNIS() 445
9.29.1 Ausgeblendete Zellen berücksichtigen 446
9.29.2 Praxisbeispiel: Umsatzliste mit Zwischensummen 447
9.29.3 Teilergebnisse in gefilterten Listen 448
9.29.4 Teilergebnisse in Tabellen 449
9.30 Die Funktion VORZEICHEN() 450
9.31 Die Funktion VRUNDEN() 451
9.31.1 Praxisbeispiel: Auf 5 Cent auf- oder abrunden 451
9.32 Die Funktionen WURZEL() und WURZELPI() 453
9.32.1 Die n-te Wurzel berechnen 453
9.33 Die Funktionen ZUFALLSZAHL() und ZUFALLSBEREICH() 454
9.33.1 Praxisbeispiel: Lottogenerator 454
9.34 Trigonometrische Funktionen 455
9.34.1 Praxisbeispiel: Funktionskurve mit der Funktion SIN() 456
9.34.2 Praxisbeispiel: Tachometerdiagramm 457
10 Finanzmathematische Funktionen 460
10.1 Bereichsnamen in Finanzfunktionen 463
10.2 Die Funktion AMORDEGRK() 464
10.2.1 Praxisbeispiel: Abschreibung berechnen 464
10.3 Die Funktion AUFGELZINS() 465
10.4 Die Funktion AUFGELZINSF() 466
10.5 Die Funktion AUSZAHLUNG() 466
10.6 Die Funktion BW() 467
10.6.1 Praxisbeispiel: Rentabilität einer Investition 467
10.7 Die Funktion DISAGIO() 468
10.8 Die Funktion DURATION() 468
10.9 Die Funktion EFFEKTIV() 469
10.10 Die Funktion GDA() 469
10.10.1 Praxisbeispiel: Degressive Doppelraten-Abschreibung 470
10.11 Die Funktion KAPZ() 471
10.11.1 Praxisbeispiel: Tilgung eines Darlehens berechnen 471
10.12 Die Funktion KUMKAPITAL() 472
10.12.1 Praxisbeispiel: Summe der Tilgungsbeiträge eines Darlehens berechnen 473
10.13 Die Funktion KUMZINSZ() 474
10.13.1 Praxisbeispiel: Gesamtbetrag der Darlehenszinsen berechnen 474
10.14 Die Funktion KUMZINSZ() 475
10.15 Die Funktion KURS() 476
10.16 Die Funktion KURSFÄLLIG() 476
10.17 Die Funktion MDURATION() 477
10.18 Die Funktion NOMINAL() 477
10.19 Die Funktion NOTIERUNGBRU() 477
10.20 Die Funktion NOTIERUNGDEZ() 478
10.21 Die Funktion PDURATION() 478
10.22 Die Funktion RENDITE() 479
10.23 Die Funktion RENDITEDIS() 479
10.24 Die Funktion RENDITEFÄLL() 480
10.25 Die Funktion RMZ() 480
10.25.1 Praxisbeispiel: Monatliche Zahlungen für ein Darlehen mit Mehrfachoperation (Datentabelle) 480
10.26 Die Funktion TBILLÄQUIV() 483
10.27 Die Funktion TBILLKURS() 483
10.28 Die Funktion TBILLRENDITE() 483
10.29 Die Funktion UNREGER.KURS() 484
10.30 Die Funktion UNREGER.REND() 484
10.31 Die Funktion UNREGLE.KURS() 485
10.32 Die Funktion UNREGLE.REND() 486
10.33 Die Funktion XINTZINSFUSS() 486
10.34 Die Funktion XKAPITALWERT() 487
10.35 Die Funktion ZINS() 487
10.35.1 Praxisbeispiel: Zinssatz eines Darlehens berechnen 488
10.36 Die Funktion ZINSSATZ() 488
10.37 Die Funktion ZINSTERMNZ() 489
10.38 Die Funktion ZINSTERMTAGE() 489
10.39 Die Funktion ZINSTERMTAGNZ() 490
10.40 Die Funktion ZINSTERMTAGVA() 490
10.41 Die Funktion ZINSTERMVZ() 491
10.42 Die Funktion ZINSTERMZAHL() 491
10.43 Die Funktion ZINSZ() 492
10.43.1 Praxisbeispiel: Monatliche Darlehenszinsen berechnen 492
10.44 Die Funktion ZW2() 493
10.45 Die Funktion ZZR() 493
10.45.1 Praxisbeispiel: Rückzahlungszeitraum für Darlehen berechnen 494
10.46 Die Funktionen DIA() und LIA() 495
10.46.1 Praxisbeispiel: Lineare Abschreibung und Restwertberechnung 495
10.47 KURSDISAGIO() 497
11 Logik-Funktionen 498
11.1 Die Funktion UND() 499
11.2 Praxisbeispiel: Geringwertige Wirtschaftsgüter 500
11.2.1 UND() in SUMMEWENN()-Bedingungen 500
11.3 Die Funktion ODER() 501
11.3.1 Praxisbeispiel: Quartal berechnen 501
11.3.2 UND() und ODER() in Matrizen 502
11.3.3 Praxisbeispiel: Preisliste vergleichen 503
11.4 Die Funktion NICHT() 505
11.5 Die Funktion WENN() 505
11.5.1 Praxisbeispiel: Postleitzahlen sortieren 506
11.5.2 WENN() geschachtelt 507
11.6 Die Funktion WENNFEHLER() 510
11.7 Die Funktion WENNNV() 513
11.7.1 Praxisbeispiel: Kostenstelle suchen 513
11.8 Die Funktion XODER() 514
11.8.1 Praxisbeispiel: Budgetüberziehung 514
12 Technische Funktionen 516
12.1 Die Bessel-Funktionen 519
12.1.1 BESSELI() 519
12.1.2 BESSELJ() 520
12.1.3 BESSELK() 520
12.1.4 BESSELY() 520
12.2 Die Umwandlungsfunktionen für Zahlensysteme 520
12.2.1 BITLVERSCHIEB(), BITRLVERSCHIEB() 521
12.2.2 BITODER(), BITUND(), BITXODER() 522
12.2.3 BININDEZ() 523
12.2.4 BININHEX() 523
12.2.5 DEZINBIN() 524
12.2.6 Praxisbeispiel: Binärtabelle 524
12.3 Die Funktion DELTA() 526
12.4 Die Funktionen GAUSSFEHLER() und GAUSSFKOMPL() 526
12.5 Die Funktion GAUSSF.GENAU() 527
12.6 Die Funktion GGANZZAHL() 527
12.7 Funktionen für komplexe Zahlen 527
12.7.1 IMABS() 528
12.7.2 IMAGINÄRTEIL() 529
12.7.3 IMAPOTENZ() 529
12.7.4 IMARGUMENT() 529
12.7.5 IMCOS(), IMCOSHYP() 529
12.7.6 IMCOSEC(), IMCOSECHYP() 529
12.7.7 IMDIV() 530
12.7.8 IMEXP() 530
12.7.9 IMKONJUGIERTE() 530
12.7.10 IMLN() 530
12.7.11 IMLOG10() 530
12.7.12 IMLOG2() 531
12.7.13 IMPRODUKT() 531
12.7.14 IMREALTEIL() 531
12.7.15 (),IMTAN(), IMCOT() 531
12.7.16 IMSUB() 532
12.7.17 IMSUMME() 532
12.7.18 IMWURZEL() 532
12.7.19 KOMPLEXE() 532
12.8 Die Funktion UMWANDELN() 533
12.8.1 Praxisbeispiel: Meter in Yards umrechnen 533
12.8.2 Praxisbeispiel: Maßeinheitenrechner 534
13 Cube- und Web-Funktionen 538
13.1 Vom Data Warehouse zum Cube 539
13.1.1 OLAP-Systeme 539
13.1.2 Excel in DW und OLAP 541
13.2 OLAP-Cubes aus SQL-Server 541
13.2.1 OLAP-Cube einlesen 541
13.2.2 PivotTable aus Cube-Daten erzeugen 544
13.2.3 OLAP-Cube offline erstellen 544
13.3 Cube-Funktionen konstruieren 545
13.4 Die Funktion CUBEWERT() 548
13.5 Die Funktion CUBEELEMENT() 549
13.5.1 Fehlerwert #NV 550
13.6 Die Funktion CUBEKPIELEMENT() 550
13.7 Die Funktion CUBEELEMENTEIGENSCHAFT() 551
13.8 Die Funktion CUBERANGELEMENT() 552
13.9 Die Funktion CUBEMENGE() 553
13.10 Die Funktion CUBEMENGENANZAHL() 554
13.11 Praxisbeispiel: Jahresbilanz aus Cube-Funktionen 555
13.11.1 Aufbau der Verbindung 555
13.11.2 Dimensionen einbinden 558
13.11.3 Tupels aus dem Cube holen 559
13.11.4 Datensätze importieren 559
13.12 Die Web-Funktionen 561
13.12.1 URLCODIEREN() 561
13.12.2 WEBDIENST() 562
13.12.3 XMLFILTERN() 563
13.12.4 Praxisbeispiel: Wikipedia – letzte Änderungen 563
14 Benutzerdefinierte Funktionen 564
14.1 Eigene Funktionen schreiben 565
14.2 Wo sind die Funktionen? 565
14.2.1 Entwicklertools bereitstellen 566
14.3 Ein Projekt für Funktionen 566
14.3.1 Projekte laden 566
14.3.2 Ein neues Projekt 567
14.3.3 Projekt als Makroarbeitsmappe speichern 567
14.4 Modulblätter 568
14.4.1 Ein neues Modulblatt 568
14.4.2 Modulblatt umbenennen 568
14.5 Prozeduren und Funktionen 569
14.5.1 Praxisbeispiel: Die erste Funktion 570
14.5.2 Funktion über Prozedur aufrufen 571
14.5.3 Prozedur starten 573
14.6 Benutzerdefinierte Funktionen im Tabellenblatt 575
14.7 Die Syntax der benutzerdefinierten Funktion 577
14.7.1 Praxisbeispiel: Nur Euro und nur Cent berechnen 577
14.7.2 Der Funktionsaufruf 578
17.7.3 Der Funktionsname 578
14.7.4 Funktion beginnen und beenden 579
14.7.5 Funktionen absichern mit WENNFEHLER() 579
14.7.6 Die Funktionsargumente 580
14.8 Funktionen berechnen 581
14.8.1 Praxisbeispiel: Meldung, wenn Mappe nicht gesichert 581
14.8.2 Praxisbeispiel: Sound abspielen, wenn Benutzername fehlt 582
14.9 Ereignismakros für den Funktionsaufruf nutzen 583
14.9.1 Praxisbeispiel: Funktion beim Öffnen der Mappe ausführen 584
14.9.2 Praxisbeispiel: Funktionsstart bei Änderungen in der Tabelle 585
14.10 Lokale und globale Funktionen 585
14.10.1 Die persönliche Arbeitsmappe 586
14.10.2 Eigene Funktionen schützen 587
14.15 Add-ins erstellen 588
14.15.1 Arbeitsmappe als Add-in speichern 588
14.15.2 Add-in in Excel einbinden 589
14.16 Praxisbeispiele: Benutzerdefinierte Funktionen 590
14.16.1 Kindergeldfunktion 590
14.16.2 Die n-te Wurzel 591
14.16.3 Break-even berechnen 591
14.16.4 Sprachversion feststellen 592
14.16.5 Funktionen im Controlling: Return on Investment 592
14.17 Spezialtipps für benutzerdefinierte Funktionen 595
14.17.1 Die Beschreibung 595
14.17.2 Funktion in Kategorie einordnen 596
14.17.3 Neue Kategorie erstellen 597
Anhang A: Die Tabellenfunktionen – alphabetische Übersicht 600
A.1 Alle Funktionen, alphabetisch sortiert 600
A.2 Kompatible Funktionen 640
Anhang B: Die Tabellenfunktionen in Deutsch und Englisch 642
B.1 Deutsch-Englisch 642
B.2 Englisch-Deutsch 653
Anhang C: Tastenkombinationen 666
Index 668