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