Inhaltsverzeichnis

Alle Kapitel aufklappen
Alle Kapitel zuklappen
Vorwort
29
1 Neuerungen in Excel
33
2 Tipps, Tricks und Tastenkürzel – zeitsparende Techniken für Controller
43
2.1 Daten effizient eingeben
43
2.1.1 Eingabe von Werten aus Listen
44
2.1.2 Benutzerdefinierte Listen
44
2.1.3 AutoAusfüll-Optionen
46
2.1.4 Einfügen von aktuellen Datums- und Zeitwerten
47
2.1.5 Blitzvorschau – Einträge trennen und auf Spalten verteilen
47
2.2 Kopieren, Ausschneiden und Einfügen von Daten
48
2.3 Formelzusammenhänge erkennen
51
2.4 Cursorsteuerung und Bewegen in Tabellen
53
2.5 Zellbereiche markieren
55
2.6 Zahlen- und andere Formate schnell zuweisen
56
2.7 Inhalte löschen
57
2.8 Diagramme erstellen und bearbeiten
57
2.9 AutoFilter und Bearbeitung von sichtbaren Zellen
58
2.10 Erweiterte Filterfunktionen in Microsoft 365
59
2.11 Weitere nützliche Tastenkombinationen
61
2.12 Tabellenansichten in der Webversion von Excel nutzen
62
2.13 Kommentare, Notizen und Chat in Excel
63
3 xlSMILE – Excel-Lösungen mit System
67
3.1 Simplify – Big Data nutzen und Datenmüll entfernen
67
3.2 Model – systematisch arbeiten und Reports automatisieren
68
3.3 Integrate – Layouts entwickeln und Tabellen und Diagramme anwenden
69
3.4 Lead – Benutzer führen und Fehleingaben verhindern
70
3.5 Explain – informieren und zusammenfassen
71
4 Daten importieren und bereinigen
73
4.1 Textkonvertierungs-Assistent
75
4.1.1 Ein Datenmodell in Excel während des Imports erstellen
80
4.1.2 Fehlerhafte Datenformate nachträglich umwandeln
82
4.2 Transaktionsdaten in einer CSV-Datei auswerten
84
4.2.1 Nicht benötigte Zeilen aus Transaktionsdaten entfernen
85
4.2.2 Überflüssige Leerzeilen mit einem Makro entfernen
86
4.2.3 Gruppierung nach Standort und Konten
88
4.2.4 Kontengruppen in Transaktionsdaten zusammenfassen
90
4.2.5 Reporting von Zahlungsbewegungen mit AutoFilter, Teilergebnissen und Sparklines
92
4.2.6 Nur Zahlungseingänge der gefilterten Konten addieren
93
4.2.7 Ein- und Ausgänge mit Sparklines visualisieren
95
4.3 Importieren von externen Daten mit Power Pivot
96
5 Datenbereinigung mit Power Query effizienter gestalten
99
5.1 Wozu ist Power Query eigentlich gedacht?
99
5.2 CSV-Dateien mit Power Query importieren
100
5.3 Einfache Schritte der Datenbereinigung ausführen
105
5.4 Verbindungen zu anderen Datenquellen
110
5.4.1 Verbindung zu einer Access-Datenbank
111
5.4.2 Mit einem SQL-Datenbankserver verbinden
113
5.4.3 Mit einer Website verbinden
114
5.4.4 Daten aus einer aktiven Arbeitsmappe importieren
117
5.4.5 Daten aus dem Power BI Service nutzen
118
5.4.6 OneDrive-Ordnerinhalte importieren
120
5.5 Gruppieren und Spalten berechnen
122
5.6 Bedingte Berechnungen in Spalten
126
5.7 Power Query als Ersatz für Textfunktionen in Excel
128
5.8 Weitere Optionen beim Erstellen von Spalten und Gruppierungsmerkmalen
133
5.8.1 Datumsgruppierungen in Power Query erstellen
133
5.8.2 Neue Spalten aus Beispielen erstellen
133
5.8.3 Führende Nullen in einer Schlüsselspalte ergänzen
135
5.8.4 Zwei Tabellen über eine Schlüsselspalte in Power Query zusammenführen
136
5.9 Suchtabellen durch Anfügen von Abfragen erzeugen
138
5.9.1 Abfragen organisieren
143
5.10 Alle Excel-Dateien eines Ordners importieren und bereinigen
144
5.10.1 Import aller Dateien eines Ordners
145
5.10.2 Bereinigung der importierten Dateien eines Ordners
147
5.11 Alltäglicher Datensalat – Power-Query-Lösungen für den Alltag
148
5.11.1 Tabelleninhalte vergleichen
148
5.11.2 Entpivotieren von Rohdaten aus ERP-Systemen
152
5.11.3 Eindeutigen Schlüssel aus mehreren Spalten erstellen
153
5.11.4 Zellinhalte trennen
156
5.11.5 Manuell erstellte Tabellen in Listen umwandeln
159
5.12 Individuelle Datentypen in Power Query erstellen
163
5.12.1 Erste praktische Annäherung an die neuen Datentypen
165
5.12.2 Internetseiten als Quelle für eigene Datentypen verwenden
167
5.12.3 Nutzung von individuell erstellten Datentypen in einer Auswertung
170
5.12.4 Überlauf von Formeln beim Verweis auf Rich Data
172
5.12.5 Rich-Data-Felder als Argumente in Kalkulationsfunktionen wie »XVERWEIS()«
174
5.12.6 Eigene Datentypen im Team verfügbar machen
176
5.12.7 Eine Empfohlene Tabelle aus Power BI als Datentyp ins Menü einbinden
178
6 Unternehmensdaten prüfen und analysieren
181
6.1 Standardsortierung und benutzerdefiniertes Sortieren
181
6.1.1 Erstellen einer benutzerdefinierten Liste
182
6.1.2 Benutzerdefiniertes Sortieren in Kombination mit Teilergebnissen
183
6.2 AutoFilter und Datenschnitte
186
6.2.1 AutoFilter und die Funktion TEILERGEBNIS()
190
6.3 Vorteile des erweiterten Filters
191
6.3.1 Aufbau des erweiterten Filters
192
6.3.2 Ausführen des Filtervorgangs
193
6.3.3 Kombination mehrerer Kriterien mit UND
194
6.3.4 Kombination mehrerer Kriterien mit ODER
196
6.3.5 Verknüpfung von Kriterien mit UND in einer Spalte
196
6.3.6 Vergleichsoperatoren bei numerischen Filterkriterien
197
6.3.7 Vergleichsoperatoren bei Textkriterien
198
6.3.8 Berechnete Filterkriterien
199
6.4 Erweiterter Filter mit einem VBA-Makro
200
6.4.1 Quelltext des VBA-Makros
200
6.4.2 Einsatzgebiete für das VBA-Makro
202
6.5 Verwendung von Datenbankfunktionen
203
6.5.1 Grundstruktur der Datenbankfunktionen
204
6.5.2 Definition der Kriterien für die Berechnung von Datenbankfunktionen
205
6.5.3 Verfügbare Datenbankfunktionen
205
6.5.4 Editieren und Kopieren von Datenbankfunktionen
206
6.5.5 Soll-Ist-Vergleich mithilfe von Datenbankfunktionen
207
6.5.6 Auswahl von Produktcode oder Kategorie über eine Eingabeliste
208
6.5.7 Ausgabe von Artikelname und Listenpreis
209
6.5.8 Darstellung der Ist- und Soll-Umsätze mittels Datenbankfunktion
210
6.5.9 Darstellung der Soll-Ist-Ergebnisse im Diagramm
210
6.5.10 Formatierung des Diagramms
212
6.6 Konsolidierung von Daten
216
6.6.1 Betrachtung der Ausgangsdaten
217
6.6.2 Verwendbare Spalten für die Konsolidierung
218
6.6.3 Verwendung von Spaltenüberschriften bei der Konsolidierung
219
6.6.4 Konsolidierung der Daten einer Arbeitsmappe
219
6.6.5 Übernahme der Beschriftung und Konsolidierung aus der linken Spalte
221
6.6.6 Konsolidierung auf Basis der Spaltenüberschriften
223
6.6.7 Verknüpfung der Konsolidierung mit den Originaldaten
225
6.6.8 Konsolidierung von Daten aus unterschiedlichen Arbeitsmappen
226
6.6.9 Konsolidierung durch Nutzung von Bereichsnamen
227
6.6.10 Konsolidierung mit geöffneten Dateien
229
7 Dynamische Reports erstellen
231
7.1 Das 5-Minuten-Datenmodell
231
7.2 Bestandteile eines Datenmodells
233
7.2.1 Grundsätzliche Überlegungen zu den Elementen eines Datenmodells
233
7.2.2 Grundsätzliche Überlegungen zu Berechnungen in einem Datenmodell
237
7.2.3 Basisanforderungen an die Erstellung von multivariablen Datenmodellen und Reports
238
7.3 Datenmodell für einen Forecast erstellen
239
7.3.1 Festlegung der Arbeitsmappenstruktur für den Forecast
241
7.3.2 Strukturierte Bezüge und Bereichsnamen
244
7.3.3 Liste eindeutiger Produktcodes erstellen
248
7.3.4 Dynamische Zeilen- und Spaltenbeschriftungen
249
7.3.5 Bedingte Kalkulation für Soll, Ist und Prognose
251
7.3.6 Methoden zur Berechnung von Prognosen
253
7.3.7 Berechnung einer Prognose mithilfe des gleitenden Mittelwertes
255
7.3.8 Steuerelemente für die Benutzereingaben im Forecast
255
7.3.9 Datenblatt für die Diagrammdaten
258
7.3.10 Rollierende Liniendiagramme
259
7.3.11 Dynamische Tabelle mit der Funktion »INDEX()«
260
7.3.12 Formate, Formatvorlagen, Diagrammvorlagen
261
7.3.13 Dynamische Bereichsnamen im Diagramm
263
7.3.14 Kommentare in Datenmodellen einsetzen
265
7.4 Datenmodell zur Kalkulation der optimalen Bestellmenge
266
7.4.1 Definition der Bereichsnamen für die Kalkulationsfaktoren
268
7.4.2 Das Formelgerüst der Optimierung
268
7.4.3 Darstellung der Optimierung im Diagramm
269
7.4.4 Formatierung und Zellschutz
270
7.5 Rollierende Berichte
271
7.5.1 Dynamische Bereichsnamen als Grundlage von dynamischen Diagrammen
274
7.5.2 Dynamische Bereichsnamen in Diagrammen
275
7.5.3 Dynamischer Diagrammtitel
276
8 Wichtige Kalkulationsfunktionen für Controller
277
8.1 Berechnungen mit Datumsbezug
278
8.1.1 Dynamische Datumslisten ohne Wochenenden
281
8.1.2 Berechnung der Kalenderwoche nach ISO 8601:2000 und des Quartals
282
8.1.3 Berechnung von Nettoarbeitstagen
283
8.1.4 Berechnung der verbleibenden Tage bis zum Monats- oder Projektende
285
8.1.5 Feiertage berechnen
286
8.1.6 Dynamischer Kalender für alle Bundesländer
287
8.1.7 Berechnung des Enddatums für Vorgänge
291
8.1.8 Berechnung von Datumsdifferenzen mit »DATEDIF()«
291
8.1.9 Weitere nützliche Funktionen in der Kategorie »Datum & Zeit«
292
8.2 Berechnungen mit Zeitangaben
293
8.2.1 Formatierung von Uhrzeiten
294
8.2.2 Umrechnung von Dezimal- in Industriezeit
294
8.2.3 Berechnung von Arbeitszeiten bei Schichtbetrieb
295
8.3 Arbeiten mit Verweisen und Matrizen
296
8.3.1 Die Vorteile von »XVERWEIS()« gegenüber anderen Verweisfunktionen
297
8.3.2 »SVERWEIS()« durch »XVERWEIS()« ersetzen
298
8.3.3 »WVERWEIS()« und »INDEX()/VERGLEICH()« durch »XVERWEIS()« ersetzen
302
8.3.4 »XVERWEIS()« im Kontext einer Kalkulationsfunktion
304
8.3.5 Überlaufbereiche der Ergebnisspalten bei der Verwendung von »XVERWEIS()«
305
8.3.6 Summe der Suchwerte einer Referenztabelle direkt bilden
307
8.3.7 Zweidimensionale Verweise bei der Nutzung von »XVERWEIS()«
308
8.3.8 Tabellen spalten- und zeilenweise mit »SVERWEIS()« und »WVERWEIS()« durchsuchen
309
8.3.9 Finden des letzten Eintrags einer Spalte oder Zeile
312
8.4 Funktionen zur Dynamisierung von Tabellen
314
8.4.1 Dynamischen Summenbereich mit »BEREICH.VERSCHIEBEN()« erstellen
315
8.4.2 Zusammengesetzte Zellbezüge mit »INDIREKT()« erstellen
319
8.4.3 »INDIREKT()« zum Ansteuern von Zellen in anderen Tabellenblättern
322
8.4.4 Finden und Berechnen von Daten mit »INDEX()« und »VERGLEICH()«
323
8.4.5 Auswahl von Berechnungsalternativen – »WAHL()« statt »WENN()«
328
8.5 Berechnung von Rangfolgen
330
8.5.1 Funktionen zur Bildung von Rangfolgen
332
8.5.2 Eindeutige Rangfolge bei identischen Werten der Liste
333
8.5.3 Eindeutige Rangfolge berechnen
334
8.5.4 Eindeutige Ursprungsdaten erzeugen
336
8.6 Berechnung von Mittelwerten
337
8.6.1 Mittelwert, Median, Modalwert
337
8.6.2 Gestutzter Mittelwert
339
8.6.3 Bedingte Mittelwerte
340
8.7 Runden von Daten
341
8.7.1 Runden auf ganze Zehner, Hunderter oder Tausender
343
8.7.2 »OBERGRENZE()« und »UNTERGRENZE()«
344
8.7.3 Runden auf ein Vielfaches mit »VRUNDEN()«
344
8.8 Textfunktionen zur Bereinigung und Strukturierung von Daten
345
8.8.1 Texte als Zahl formatieren
347
8.8.2 Neue Textfunktionen ab Excel 2016 (Microsoft 365)
349
8.9 Fehlerunterdrückung
351
8.9.1 Formelüberwachung als Mittel der Ursachenanalyse
352
8.9.2 Überprüfen der Bezüge innerhalb der Arbeitsmappe und zu anderen Dateien
353
8.9.3 Unterdrücken von Fehlerwerten
355
8.9.4 Praktische Anwendung
356
8.10 Einsatz von logischen Funktionen
357
8.10.1 Mehrfachprüfungen mit der Funktion »WENNS()« (seit Excel 2016)
359
8.10.2 Codierungen umwandeln mit »ERSTERWERT()« (seit Excel 2016)
361
8.11 Berechnungen mit »LET()« erstellen – Funktionsweise und Nutzen
361
8.11.1 Einfache Beispiele für Berechnungen mithilfe von »LET()«
362
8.11.2 Berechnung einer Provision durch mehrfache Verwendung einer Variablen in »LET()«
364
8.11.3 Verwendung von anderen Kalkulationsfunktionen in »LET()«
366
8.11.4 Bestehende Kalkulationsfunktionen vereinfachen, fehlende ergänzen
367
8.11.5 Funktionsweise von »LET()« in Überlaufbereichen
370
8.12 Wie viel VBA benötigen Controller nach der Einführung von »LAMBDA()« noch?
372
8.12.1 Abzinsungsfaktor mit »LAMBDA()« berechnen
373
8.12.2 LAMBDA()-Funktionen mit mehreren Parametern erstellen
376
8.12.3 »LAMBDA()« in Kombination mit »LET()«
377
8.12.4 Rekursive Berechnungen in »LAMBDA()«
378
8.12.5 Testen einer rekursiven LAMBDA()-Funktion mit ME
380
8.12.6 Rekursive LAMBDA()-Funktionen zur Bereinigung von Zellinhalten
382
9 Neue dynamische Matrixfunktionen in Excel für Microsoft 365
387
9.1 Das Control-Shift-Enter-Beben
388
9.1.1 Grundlagen der neuen dynamischen Matrixfunktionen
388
9.1.2 Speicherort und Editierbarkeit der neuen Matrixfunktionen
390
9.1.3 Excel läuft über … und schon sind Fehlerwerte möglich
391
9.1.4 Mit dem Spiller auf Überlaufbereiche zugreifen
393
9.1.5 Überlauf in Zellbereiche und Funktionen verhindern
397
9.1.6 Übersicht über die neuen dynamischen Matrixfunktionen
397
9.1.7 Automatisches Sortieren von Daten mit »SORTIEREN()« und »SORTIERENNACH()«
400
9.1.8 Ein Ergebnis, aber viele Sortierkriterien – »SORTIERENNACH()«
402
9.1.9 Automatische Datenauszüge mit »FILTER()« erstellen
402
9.1.10 Mehrfachkriterien mit logischem UND/ODER beim automatischen Filtern verwenden
404
9.1.11 Duplikate aus Listen mit der Funktion »EINDEUTIG()« entfernen
405
9.1.12 Eindeutige Werte auf Basis mehrerer Spalten mithilfe von »WAHL()« extrahieren
406
9.1.13 Dynamische Datenreihen mit der Funktion »SEQUENZ()« generieren
408
9.1.14 Dynamische Datumsreihen durch die Kombination von Datumsfunktionen und »SEQUENZ()« erzeugen
409
9.1.15 Zufallszahlen mit der Funktion »ZUFALLSMATRIX()« erstellen
411
9.1.16 Extrahieren einzelner Werte mithilfe von »EINZELW()«
412
9.2 Neue Optionen für die Erstellung dynamischer Datenmodelle
414
9.2.1 Erzeugen dynamischer Produktlisten und Datumsreihen mit »EINDEUTIG()«
415
9.2.2 Bedingte Kalkulationen im Kontext der neuen Matrixfunktionen
416
9.2.3 Einbindung eines Forecasts mit veränderlichem Datumsbereich
418
9.2.4 Auswahl von Datenbereichen mit »WAHL()« und Überlaufbereichen
420
10 Bedingte Kalkulationen in Datenanalysen
423
10.1 Kalkulationen ohne Bedingungen
424
10.2 Kalkulationen mit einer Bedingung
426
10.3 Bereichsnamen – der schnelle Zugriff auf Datenbereiche
429
10.3.1 Verwendung sprechender Bereichsnamen
431
10.3.2 Editieren von Bereichsnamen
434
10.4 Fehlervermeidung bei der Eingabe von Bedingungen – die Datenüberprüfung
435
10.4.1 Eingabe von Duplikaten mit der Datenüberprüfung vermeiden
437
10.4.2 Datenüberprüfungen bearbeiten oder entfernen
439
10.5 Bedingte Kalkulationen mit mehr als einer Bedingung
439
10.5.1 Mehrfachbedingungen mit logischem ODER
442
10.6 Vorteile von »SUMMENPRODUKT()« gegenüber anderen Funktionen zur bedingten Kalkulation
447
10.7 Multiplikation von Textwerten mit »SUMMENPRODUKT()«
448
10.8 Bedingte Kalkulation mit ODER im Tabellenblatt »Report_III«
449
10.9 Ausschluss von Datensätzen bei bedingten Kalkulationen
449
10.10 Häufigkeiten schnell berechnen
451
10.11 Mittelwerte ohne Nullwerte berechnen
453
10.12 Mittelwert bei #DIV/0!
454
10.13 Fallbeispiel zur bedingten Kalkulation
456
10.13.1 Anzahl unterschiedlicher Zahlenwerte im Datenbereich
457
10.13.2 Häufigste Artikelbezeichnung im Datenbereich
457
10.13.3 Bedingte Kalkulation in Tabelle und Diagramm über Auswahlliste steuern
458
11 Pivottabellen und -diagramme
461
11.1 Vorbereitung der Basisdaten für eine Pivottabelle
462
11.2 Pivottabellen erstellen
466
11.2.1 Datenlabels hinzufügen, entfernen und anders anordnen
469
11.2.2 Anpassungen und Abkürzungen beim Erstellen des Pivottabellenlayouts
472
11.2.3 Berechnungsfunktionen ändern
473
11.2.4 Prozentual oder absolut? Rangfolge oder Kumulation? Die Datendarstellung macht den Report
474
11.2.5 Fallbeispiel 1: Anteil eines regionalen Artikels am Gesamtergebnis
478
11.2.6 Fallbeispiel 2: Auswertung nach KW und Kumulation der KW-Ergebnisse
479
11.2.7 Fallbeispiel 3: Kundenranking auf Basis des Bestellwertes
481
11.2.8 Fallbeispiel 4: Bewertung der Datenqualität
482
11.3 Pivotcache und Speicherbedarf
484
11.4 Visuelle interaktive Analyse von Daten
485
11.4.1 Datenschnitt in der Pivottabelle aktivieren
487
11.4.2 Gestaltung und Anordnung der Datenschnitttools
488
11.4.3 Datenanalyse mithilfe der Datenschnitttools
489
11.4.4 Mehrere Pivottabellen per Datenschnitt steuern
491
11.4.5 Weitere Einstellungen für die Datenschnitttools
492
11.5 Zeitbezogene Auswertungen von Pivottabellen mit Zeitachsen
493
11.5.1 Automatische Gruppierung von Datumswerten ab Excel 2019
493
11.5.2 Zeitachsen einfügen
494
11.6 Filtern von Daten in einer Pivottabelle
495
11.7 Gruppierungen in Pivottabellen
496
11.7.1 Manuelle Gruppierung von Produkten
497
11.7.2 Tabellenlayouts
499
11.7.3 Standardlayout für Pivottabellen festlegen
501
11.7.4 Sortieroptionen
502
11.7.5 Gruppierungen mittels berechneter Produktgruppen
503
11.7.6 Aufbau eines Datenmodells zur Gruppierung
508
11.7.7 Automatische Gruppierung nach Kalenderwochen
511
11.7.8 Kalenderwochen nach ISO 8601
513
11.7.9 Pivottabellen mit berechneten Feldern
514
11.8 Weiterverarbeitung von Daten aus Pivottabellen
520
11.8.1 »PIVOTDATENZUORDNEN()« bei einem Soll-Ist-Vergleich
521
11.8.2 Anpassung der Funktion »PIVOTDATENZUORDNEN()«
522
11.8.3 Der Fehler »#BEZUG!« bei Anwendung von »PIVOTDATENZUORDNEN()«
522
11.8.4 »PIVOTDATENZUORDNEN()« zum Umsetzen von Reportlayouts
523
11.8.5 Andere Formen der Weiterverarbeitung von Pivottabellen
525
11.9 Personaldaten mithilfe von Pivottabellen konsolidieren
527
11.9.1 Erste Spalte anpassen, um Konsolidierung zu optimieren
529
11.9.2 Personaldaten konsolidieren
530
11.9.3 Personalnummern und Namen der Konsolidierungsspalte trennen
531
11.9.4 Daten durch Konsolidierung »pivotierbar« machen
533
11.10 Grundlegendes zu PivotCharts
535
11.10.1 Einschränkungen bei Pivotdiagrammen
537
11.10.2 Schaltflächen in Pivotdiagrammen
538
11.10.3 Punkt-(XY-)Diagramm aus einer Pivottabelle erstellen
539
11.10.4 Alternativen bei der Erstellung eines XY-Diagramms aus Pivotdaten
542
11.10.5 Andere Techniken der grafischen Darstellung von Pivottabellen
542
11.11 Zusammenfassung: Pivottabellen und PivotCharts
546
12 Business Intelligence mit Power Pivot
549
12.1 Arbeiten auf der Self-BI-Baustelle
550
12.2 Inhaltliches und Organisatorisches zu den Beispielen
551
12.3 Die Power-Pivot-Oberfläche im Überblick
552
12.4 Logische Beziehungen statt »SVERWEIS()« und Co.
557
12.5 Berechnete Spalten und berechnete Felder unterscheiden
560
12.6 Eine berechnete Spalte erstellen
561
12.7 Eine Power-Pivot-Tabelle in Excel erstellen
563
12.8 Mehr Übersichtlichkeit herstellen
565
12.9 Referenztabellen einbinden
567
12.10 Einbinden von Daten aus anderen Datenquellen
570
12.11 Typische Erkennungszeichen für fehlende logische Beziehungen in Datenmodellen
572
12.12 Tabellen der Arbeitsmappe in das Datenmodell einbinden
575
12.13 Tabellen des Datenmodells ausblenden
578
12.14 Berechnete Felder in Power-Pivot-Tabellen verwenden
580
12.14.1 Implizite und explizite Measures
581
12.14.2 Aggregierungsfunktionen in Power Pivot
582
12.15 Bearbeiten von Measures
585
12.16 Bedingte Kalkulationen mit »CALCULATE()«
587
12.17 Datenschnitte und Zeitachsen
591
12.18 Wie DAX-Funktionen arbeiten
596
12.18.1 »CALCULATE()« in einem Filterkontext
597
12.18.2 Filter fließen immer nur abwärts
598
12.18.3 Datenschnittfilter aufheben
601
12.19 Verwendung von Zeitintelligenz-Funktionen in einem Datenmodell
603
12.20 Vorjahresvergleiche mit »SAMEPERIODLASTYEAR()« erstellen
606
12.21 Individuelle Zeitintervalle mit »DATEADD()« berechnen
607
12.22 Bedingte Formatierungen und Diagramme in Power-Pivot-Reports
609
13 Excel als Planungswerkzeug
613
13.1 Wettbewerberanalyse
613
13.1.1 Datenüberprüfungen im Bewertungsformular
614
13.1.2 Bereichsnamen der Codierung
615
13.1.3 Kopieren der Datenüberprüfungen
616
13.1.4 Berechnung der erreichten Punktzahl
616
13.1.5 Visualisierung mit Sparklines
617
13.2 Potenzialanalyse
620
13.2.1 Grafische Darstellung der Potenziale
621
13.2.2 Anzeige von Linie und Wert in einer Zelle
623
13.2.3 Kopieren der Liniendiagramme
623
13.2.4 Gegenüberstellung von Potenzialen und Handlungsfeldern
623
13.2.5 Erstellen der Stärken-Schwächen-Diagramme
625
13.3 Portfolioanalyse
625
13.3.1 Erstellen des Blasendiagramms
626
13.3.2 Nachbearbeitung des Blasendiagramms
627
13.3.3 Beschriftung der Datenpunkte im Blasendiagramm
628
13.3.4 Betrachtung weiterer Portfoliodimensionen
629
13.4 Stärken-Schwächen-Analyse
630
13.4.1 Erstellen der Datenbasis für das Stärken-Schwächen-Diagramm
631
13.4.2 Einfügen der zweiten Datenreihe
632
13.4.3 Anpassen des Diagramms
633
13.4.4 Werte aus Zellen als Beschriftung des Diagramms übernehmen
635
13.5 Absatzplanung
636
13.5.1 Planung auf Basis einer strukturierten Eingabetabelle
637
13.5.2 Berechnen statt kopieren – Übertragen der Daten in ein neues Blatt zur Trendberechnung
638
13.5.3 Übernahme der Stückzahlangaben mit »INDEX()«
639
13.5.4 Verwendung der Funktion »SCHÄTZER()« für die Prognose
640
13.5.5 Verwendung des Szenario-Managers in der Umsatzplanung
641
13.5.6 Planung auf Basis von Transaktionsdaten
643
13.5.7 Sichtung der Datenbasis mittels Pivottabelle
644
13.5.8 Kumulierte Darstellung der Monatsdaten
645
13.5.9 Pivotdiagramm mit dynamischer Beschriftung
645
13.5.10 Sichtung der Vorjahresdaten mit Datenschnitttool
647
13.5.11 Auswertung per Pivottabelle und Datenschnitt
648
13.5.12 Nutzung der Trendfunktion zum Erstellen einer Umsatzprognose
649
13.5.13 Umwandlung der exportierten Liste in eine gestaltete Tabelle
650
13.5.14 Anwendung der Trendfunktion
651
13.5.15 Visualisierung der Umsatzplanung mit Sparklines
652
13.5.16 Gliederung von Umsatz- und Prognosewerten
653
13.6 Prognosen erstellen
654
13.6.1 Datenqualität beurteilen: Korrelationskoeffizient und Bestimmtheitsmaß
654
13.6.2 Bestimmtheitsmaß im Diagramm anzeigen
655
13.6.3 Bestimmtheitsmaß berechnen
656
13.6.4 Berechnung des Korrelationskoeffizienten
656
13.6.5 Trendbereinigung
657
13.6.6 Gleitender Mittelwert
658
13.6.7 Exponentielle Glättung
660
13.7 Personalplanung
661
13.7.1 Eingabe der Personalstrukturdaten
663
13.7.2 Berechnung und Anpassung der Grundgehälter
665
13.7.3 Berechnung der vermögenswirksamen Leistungen
666
13.7.4 Zuordnung der Telefonpauschale
667
13.7.5 Berechnung der Kfz-Zuschläge und Pensionen
668
13.7.6 Berechnung der Sozialabgaben
669
13.7.7 Berechnung der weiteren Sozialabgaben
670
13.7.8 Darstellung von Zwischenergebnissen
670
13.7.9 Vorbereitung möglicher Auswertungen des Personalkosten-Forecasts
671
13.7.10 Erstellen der Pivottabelle
673
13.7.11 Soll-Ist-Vergleiche der Personalkosten
674
13.7.12 Soll-Ist-Vergleich für einen Mitarbeiter erstellen
675
13.7.13 Berechnung der Soll-Werte auf Grundlage der Gesamtkostentabelle
676
13.7.14 Berechnung der Ist-Werte auf Basis der Downloaddaten
677
13.7.15 Fazit – Personalplanung
678
13.8 Liquiditätsplanung
678
13.8.1 Gliederung aus Berechnungen erstellen
679
13.8.2 Summen für Spalten und AutoGliederung
681
13.8.3 Fenster fixieren
681
13.8.4 Strukturierung von Tabellen mit Designfarben
683
13.8.5 Erstellen eigener Designfarben
683
13.8.6 Zuweisen von RGB-Werten nach CI-Vorgaben
684
13.9 Marktanalyse und Absatzplanung
685
13.9.1 Daten der Marktanalyse
685
13.9.2 Struktur der Vertriebsdaten
686
13.9.3 Bestimmung der Artikel und Vertriebskanäle mit Absatzpotenzial
687
13.9.4 Berechnung der Potenziale
688
13.9.5 Berechnung der Potenzialhöhe
689
13.9.6 Darstellung der Potenziale im Diagramm
690
14 Operatives Controlling mit Excel
693
14.1 Betriebsabrechnungsbogen
693
14.1.1 Arbeitsmappenstruktur des Betriebsabrechnungsbogens
694
14.1.2 Konsolidierung von Standorten oder Monaten
696
14.1.3 Anpassung der Bereichsnamen
697
14.1.4 Umlage der Primärkosten im BAB
699
14.1.5 Verteilungsschlüssel der Sekundärkostenumlage
699
14.1.6 Berechnung der kalkulatorischen Abschreibungen
700
14.1.7 Einbeziehung der kalkulatorischen Zinsen
702
14.1.8 Berechnung der kalkulatorischen Risiken
702
14.2 Divisionskalkulation
703
14.2.1 Durchführung der Vorkalkulation
705
14.2.2 Durchführung der Nachkalkulation
705
14.2.3 Zellschutz für die Kalkulationsbereiche
705
14.3 Zuschlagskalkulation
706
14.3.1 Durchführung der Vorkalkulation
707
14.3.2 Durchführung der Nachkalkulation
708
14.4 Äquivalenzziffernrechnung
709
14.4.1 Bildung der Äquivalenzziffern
710
14.4.2 Verwendung der Äquivalenzziffern in der Kostenkalkulation
710
14.5 Prozesskostenrechnung
711
14.5.1 Arbeitsschritte zur Durchführung der Prozesskostenrechnung
712
14.5.2 Tabellenaufbau bei Anwendung der Prozesskostenrechnung
712
14.5.3 Berechnung des Prozesskostensatzes und der Selbstkosten
713
14.5.4 Zuordnung der leistungsmengenneutralen Kosten
714
14.6 Deckungsbeitragsrechnung
715
14.7 Dynamische Break-even-Analyse
716
14.7.1 Erstellen der Datenreihen für das Diagramm
718
14.7.2 Berechnung der Umsatz- und Kostenwerte
719
14.7.3 Erstellen des Liniendiagramms
719
14.7.4 Einfügen des Drehfeldes
720
14.7.5 Generieren einer dynamischen Beschriftung im Diagramm
723
14.7.6 Einfügen der dynamischen Beschriftung in das Liniendiagramm
725
14.8 Mehrstufige Deckungsbeitragsrechnung
725
14.9 Planen von Kosten und Erlösen mithilfe von Szenarien
726
14.9.1 Erstellen eines Szenarios aus einer Gewinnschwellenanalyse
727
14.9.2 Erfassen des ersten Szenarios
728
14.9.3 Abrufen der Szenarien
730
14.9.4 Erstellen eines Szenarioberichts
730
14.10 Produktkalkulation mit Deckungsbeitragsrechnung
731
14.10.1 Berechnungsgrundlage von Deckungsbeitrag I und II
732
14.10.2 Arbeitsmappenstruktur der Beispielanwendung
733
14.10.3 Berechnung von Deckungsbeitrag I
734
14.10.4 Erfassung und Berechnung der kundenbezogenen Prozesskosten
735
14.10.5 Berechnung des Deckungsbeitrags II und quartalsweise Auswertung
737
14.10.6 Bedingte Kalkulation auf Basis von Datum und Kunden-ID
738
14.10.7 Übertragung der Funktionen auf die weiteren Quartale
740
14.10.8 Gliederung der Daten und Fixierung des Fensters
741
14.10.9 Durchführung der Produktkalkulation
741
14.10.10 Datenüberprüfungen zur Artikel- und Prozessauswahl
741
14.10.11 Formeln und Funktionen zur Berechnung der Herstellkosten
742
14.10.12 Abschluss und Schutz der Berechnungen
744
14.11 Eigenfertigung oder Fremdbezug (make or buy)
745
14.11.1 Aufbau des Kalkulationsmodells
745
14.11.2 Bestimmung der kritischen Menge
746
14.11.3 Darstellung der Kostenverläufe im Diagramm
746
14.11.4 Schlussbemerkung
748
14.12 Zinsen, Tilgung, Annuitäten für Darlehen berechnen
748
14.12.1 Raten mit festen Annuitäten
749
14.12.2 Aufteilung in Zinsen und Tilgung
750
14.12.3 Monatsraten und Zinsen
750
14.12.4 Tilgung berechnen
751
14.12.5 Zukünftigen Wert berechnen
752
14.12.6 Effektiv- und Nominalzins berechnen
752
14.12.7 Barwert auf Basis regelmäßiger zukünftiger Zahlungen
753
14.13 Abschreibungen
753
14.13.1 Arithmetisch-degressive Abschreibung
754
14.13.2 Weitere Abschreibungsmethoden und -funktionen
755
14.14 Methoden der Investitionsrechnung
756
14.14.1 Kostenvergleichsmethode
757
14.14.2 Eingabe der Kosten in das Kalkulationsformular
758
14.14.3 Gewinnvergleich
759
14.14.4 Rentabilitätsvergleich
760
14.14.5 Amortisationsrechnung
761
14.14.6 Kapitalwertmethode
762
14.14.7 Methode des internen Zinsfußes
763
14.14.8 Internen Zinsfuß mit der Zielwertsuche finden
764
14.14.9 Modifizierter interner Zinsfuß
765
14.14.10 Annuitätenmethode
766
14.14.11 Berechnung der Annuitäten
767
14.14.12 Zusammenführung aller Berechnungsergebnisse
768
14.14.13 Investitionsentscheidungen mit Szenarien unterstützen
769
14.14.14 Regeln bei der Erstellung der Szenarien
770
14.15 Customer Lifetime Value
770
14.15.1 Übersicht über die Funktionen der Beispielanwendung
771
14.15.2 Bestandteile des Customer Lifetime Values
772
14.15.3 Erfassung und Zuordnung der Umsätze
774
14.15.4 Prognose der diskontierten Umsätze eines Kunden
774
14.15.5 Auswahl des Kunden
775
14.15.6 Berechnung der vorhandenen Deckungsbeiträge des Kunden
775
14.15.7 Prognose der zu erwartenden Kundenumsätze
776
14.15.8 Berechnung des Abzinsungsfaktors
778
14.15.9 Diskontierung der prognostizierten Umsätze
778
14.15.10 Bestimmung der prozessbezogenen Kosten
779
14.15.11 Berechnung der entstandenen Kosten pro Kunde
780
14.15.12 Prognose der Kosten – Herstellkosten, Boni und Rabatte
781
14.15.13 Erfassung sämtlicher anderer Kostenarten
781
14.15.14 Bestimmungsgrößen des Referenzwertes
781
14.15.15 Der Referenzindex in der Beispieldatei
782
14.15.16 Dokumentation der Bewertungsergebnisse
783
14.15.17 Der Bindungsindex in der Beispieldatei
784
14.16 Kundenscoring
784
14.17 Personalstrukturanalyse
786
14.17.1 Auswertung der Altersstruktur
788
14.17.2 Auswertung nach Alter und Geschlecht
789
14.17.3 Altersstruktur im Diagramm darstellen
791
14.17.4 Auswertung der Betriebszugehörigkeit
792
14.18 Arbeitszeitanalyse
792
14.18.1 Festlegung der Konsolidierungsbereiche
793
14.18.2 Erstellen des Soll-Ist-Vergleichs
795
14.19 Reisekostenabrechnung
795
14.19.1 Sperren von Zellen und Schutz des Tabellenblattes
796
14.19.2 Druckbereich festlegen und überflüssige Spalten/Zeilen ausblenden
797
14.19.3 Dateifenster konfigurieren und schützen
798
14.20 Lieferantenbewertung
799
14.20.1 Aufbau der Beispielanwendung
799
14.20.2 Elemente des Eingabeformulars
801
14.20.3 Erstellen der ActiveX-Kombinationsfelder
801
14.20.4 Definition der Formular-Eingabefelder
802
14.20.5 Erstellen der Formularsteuerelemente
802
14.20.6 Struktur des Makros zum Erstellen der Excel-Liste
803
14.20.7 Aufrufen des VBA-Editors
804
14.20.8 Inhalt des VBA-Makros zum Erstellen der Excel-Liste
805
14.20.9 Deklarieren einer Variablen
806
14.20.10 Programmieren einer Schleife zur Suche der nächsten Leerzeile
807
14.20.11 Überprüfung einer Bedingung
807
14.20.12 Anhängen der Daten an die Excel-Liste
808
14.20.13 Leeren der Zellen im Tabellenblatt »Zusammenfassung«
809
14.20.14 Lieferantenbewertung – Zwischenrechnung
809
14.20.15 Durchschnittliche Bewertung der Lieferanten
810
14.20.16 Bildung der Rangfolge
810
14.20.17 Automatische Sortierung der Daten
810
14.20.18 Grafische Darstellung der Lieferantenbewertung
811
15 Unternehmenssteuerung und Kennzahlen
813
15.1 Zielkostenmanagement (Target Costing)
814
15.1.1 Ausgangslage der Zielkostenberechnung
815
15.1.2 Bestimmung der Zielkosten
817
15.1.3 Analyse der Kostenstruktur und Identifizierung der Kostenlücke
818
15.1.4 Bestimmung der Ziellücke
819
15.1.5 Schema für die Anpassung der Kostenstruktur
819
15.1.6 Ermittlung der Kundenpräferenzen
819
15.1.7 Bildung des Zielkostenindex
820
15.1.8 Umsetzung der Kostenstrukturanpassung in Excel
820
15.1.9 Berechnung der Einsparpotenziale
821
15.1.10 Tabellenaufbau und Navigation durch die Tabellenabschnitte
822
15.2 Cashflow
824
15.2.1 Beispieldateien und Datenmodelle
824
15.2.2 Direkte Ermittlung des Cashflows
825
15.2.3 Indirekte Ermittlung des Cashflows
826
15.3 Free Cashflow
827
15.4 Discounted Cashflow
828
15.5 Gewichtete durchschnittliche Gesamtkapitalkosten nach Steuern
829
15.6 Shareholder-Value
830
15.6.1 Free Cashflows und Residualwert
831
15.6.2 Barwerte der Free Cashflows berechnen
833
15.6.3 Berechnung des Residualwertes
834
15.6.4 Abschließende Bildung des Shareholder-Values
834
15.7 Economic Value Added – EVA®
835
15.7.1 Aufbau der Beispieldatei
835
15.7.2 Berechnung NOPAT
835
15.7.3 Berechnung der Net Operating Assets
836
15.7.4 Berechnung der Gesamtkapitalkosten und des EVA®
836
15.7.5 Allgemeine Informationen zum EVA®
837
15.8 Market Value Added – MVA
837
15.8.1 Aufbau der Beispieldatei
837
15.8.2 Unternehmenswert berechnen
838
15.9 Bilanzkennzahlen
838
15.9.1 Gliederungsschema der Bilanz nach HGB
839
15.9.2 Internationalisierung der Rechnungslegung
840
15.9.3 Vorgaben zur Bilanzerstellung nach IAS/IFRS
840
15.9.4 Kennzahlennavigator
842
15.9.5 Übersicht und Interpretation von Vermögens- und Liquiditätskennzahlen
844
15.10 GuV-Gliederung
847
15.10.1 Gesamtkosten- und Umsatzkostenverfahren nach HGB
848
15.10.2 Kennzahlen zu Rentabilität und Kapitalstruktur
850
15.11 Beispieldatei GuV – Bilanz – Kapitalfluss
852
15.11.1 Mehrjährige GuV-Analyse
853
15.11.2 Erfassung und Berechnung der Bilanzdaten im Fünfjahresvergleich
854
15.11.3 Berechnung des Cashflows aus GuV- und Bilanzdaten
855
15.12 Return on Investment und DuPont-Schema
856
15.12.1 Einzelschritte bei der ROI-Berechnung
857
15.12.2 Interpretation der Ergebnisse des DuPont-Schemas
857
15.12.3 Fazit
859
15.13 Messung der Mitarbeiterzufriedenheit
859
15.13.1 Ablauf von Befragungen zur Mitarbeiterzufriedenheit
860
15.13.2 Aufbau eines Fragebogens
860
15.13.3 Vermeidung der Mehrfachbeantwortung einer Frage
861
15.13.4 Definition einer Fehlermeldung
862
15.13.5 Übertragung der Datenüberprüfung auf die weiteren Fragen
862
15.13.6 Festlegung und Automatisierung des Auswertungsablaufs
863
15.13.7 Speichern des ausgefüllten Fragebogens unter einem eindeutigen Dateinamen
864
15.13.8 Zuordnung einer Schaltfläche zum VBA-Makro
865
15.13.9 Aufbau der Auswertungstabelle der Fragebogendatei
865
15.13.10 Verbergen des Tabellenblattes zur Auswertung der Antworten
866
15.13.11 Automatisierte Auswertung der Fragebögen
867
15.13.12 Aufbau der Beispieldatei
867
15.13.13 Kurzbeschreibung des VBA-Makros zum Datenimport
868
15.13.14 Quelltext des VBA-Makros zum Datenimport
869
15.13.15 Makro – Teil 1: Definition der Arbeitsumgebung
870
15.13.16 Makro – Teil 2: Öffnen der Antwortdateien durch eine Schleife
871
15.13.17 Makro – Teil 3: Ermitteln der nächsten freien Spalte
871
15.13.18 Makro – Teil 4: Kopieren und Einfügen der Antwortdaten
872
15.13.19 Makro – Teil 5: Schließen der Antwortdatei/Wiederholen der Prozedur
872
15.13.20 Namensdefinition für die Auswertung der importierten Daten
872
15.13.21 Auswertung der Fragebögen
873
15.14 Selbstbewertung nach EFQM
875
15.14.1 Übersicht über die neun Kriterien des EFQM-Modells
876
15.14.2 Erstellen der Kriterienübersicht als Schaubild
877
15.14.3 Kopieren und Anpassen der AutoForm-Vorlage
877
15.14.4 Beschriftung der AutoFormen
877
15.14.5 Formular zur Bestimmung von Erfüllungsgrad und Handlungsbedarf
878
15.14.6 Berechnung der Ergebnisse der Selbst- und Fremdbewertung
880
15.14.7 Bestimmung des Handlungskoeffizienten
881
15.14.8 Bestandteile und Aufbau des EFQM-Cockpits
882
15.14.9 Vergleich von Erfüllungsgrad und Handlungsbedarf im Netzdiagramm
882
15.14.10 Interpretationen der Datendarstellung im Netzdiagramm
884
15.14.11 Diagramme des Cockpits
885
15.14.12 Performancedarstellung mit Bullet Graphs
885
15.14.13 Balkendiagramm zur Darstellung des Handlungsbedarfs
886
15.14.14 Ampeldarstellung für die Handlungskoeffizienten
887
15.14.15 Schützen der Cockpit- und Fragebogeninhalte
888
15.14.16 Weitere Kennzahlen im EFQM-Cockpit
889
16 Reporting mit Diagrammen und Tabellen
891
16.1 Grundlagen
891
16.1.1 Zu viel und doch zu wenig?
892
16.1.2 Mut zur Lücke! Aber was kann man weglassen?
892
16.1.3 Was Sie stattdessen wissen und nutzen sollten
893
16.2 Das Standarddiagramm in Excel
893
16.2.1 Diagrammerstellung über das Menüband
894
16.2.2 Bestimmen der Datenreihen und Beschriftungen
897
16.2.3 Zwei Vorgehensweisen – ein Ziel: Änderung von Elementeigenschaften
897
16.3 Wichtige Gestaltungsregeln
898
16.4 Umgang mit Farben
902
16.5 Auswahl des richtigen Diagrammtyps
903
16.5.1 Vergleich von Werten und Darstellung von Rangfolgen – Balkendiagramm und Säulendiagramm
904
16.5.2 Vergleich mehrerer Datenreihen und des Gesamtergebnisses – Stapelsäulen
905
16.5.3 Wertevergleich bei mehr als einer Größenachse – Netzdiagramm
907
16.5.4 Entwicklung von Werten in Zeitreihen – Liniendiagramm
908
16.5.5 Darstellung der Anteile an einem Gesamtergebnis – Balken- oder Säulendiagramm
910
16.5.6 Darstellung von Abweichungen – Säulendiagramm oder Liniendiagramm
912
16.5.7 Darstellung der Korrelation zwischen Werten – Punktdiagramm
913
16.5.8 Trendlinie und Bestimmtheitsmaß im Punktdiagramm
914
16.5.9 Aufnahme einer dritten Koordinate – Blasendiagramm
916
16.5.10 Darstellung von Datenverteilungen
918
16.5.11 Darstellung des Verlaufs von Aktienkursen oder Rohstoffpreisen – Kursdiagramm
919
16.5.12 Verbunddiagramme
920
16.6 Die neuen Diagrammtypen seit Excel 2016
921
16.6.1 Wasserfalldiagramm
922
16.6.2 Trichter- oder Funneldiagramm
924
16.6.3 Histogramm
925
16.6.4 Sunburst-Diagramm
929
16.6.5 Treemap-Diagramm
931
16.6.6 Kastendiagramm
933
16.7 Allgemeine Formatierungsregeln
935
16.7.1 Verwendung und Funktionsweise der Designfarben
935
16.7.2 Erstellen eigener Designfarben
935
16.8 Elemente und Gestaltungsregeln für Dashboards
936
16.9 Infografiken seit Excel 2016
938
16.10 Piktogramme und Fotos
939
16.11 3D-Karten
943
16.12 Power View
944
16.13 Kombinationen aus Tabellen und Diagramm erstellen
947
16.14 Dynamische Diagramme
949
16.14.1 Verwendung von individuellen Bereichsnamen in Diagrammen
951
16.14.2 Berechnung des dynamischen Bereichs für die Summenbildung
951
16.14.3 Berechnung des dynamischen Bereichs für das Diagramm
952
16.14.4 Einfügen des Bereichsnamens in das Diagramm
953
16.15 Spezielle Diagrammtypen
954
16.15.1 Tachometerdiagramm mit Ampeldarstellung und Werteskala
954
16.15.2 Thermometerdiagramm
959
16.15.3 Wasserfalldiagramm
960
16.15.4 Tornadodiagramm
961
16.15.5 Gantt-Diagramm
963
16.16 Spezielle Formatierungen im Diagramm
966
16.16.1 Werteabhängige Formatierung: Kennzeichnung von Maximal- und Minimalwert
966
16.16.2 Bedingte Formatierung von Datenpunkten
971
16.17 Diagramme in Tabellenblättern
974
16.17.1 Erstellen einer Heatmap
975
16.17.2 Textfunktionen und grafische Tabellendarstellung
979
16.17.3 Nutzung von Sparklines
982
16.18 Dashboards erstellen
988
16.18.1 Verwendung von Sparklines in Dashboards
990
16.18.2 Darstellung geografischer Daten in Dashboards
992
16.18.3 Verwendung von Ringdiagrammen in Dashboards
994
16.19 Übernahme in PowerPoint
996
16.19.1 Erstellen von Tabellen und Diagrammen in PowerPoint
997
16.19.2 Verwenden einer Tabelle oder eines Diagramms als Verknüpfung
997
16.19.3 Bearbeitung von Verknüpfungen in PowerPoint
998
16.19.4 Einbetten eines Excel-Objekts in PowerPoint
1000
16.19.5 Verwendung von Designfarben in PowerPoint
1000
16.20 Übernahme in Word
1004
17 Automatisierung mit Makros – VBA für Controller
1005
17.1 Wie alles anfängt – die Aufzeichnung eines Makros
1007
17.1.1 Testen des aufgezeichneten Makros
1009
17.1.2 Ein Blick hinter die Kulissen – Ihr Makro im Makro-Editor
1009
17.1.3 Struktur des aufgezeichneten Makros
1010
17.1.4 Quelltext des aufgezeichneten Makros – Objekt, Methode, Eigenschaft
1011
17.1.5 Weitere Informationen und Hilfen im Makro-Editor nutzen
1012
17.1.6 Makro im Editor überarbeiten
1014
17.1.7 Testen des überarbeiteten Makros
1015
17.2 Makros über Schaltflächen aufrufen
1015
17.2.1 Alternativen zum Aufruf von Makros über Schaltflächen
1017
17.2.2 Zugriff über die Symbolleiste für den Schnellzugriff
1017
17.2.3 Zugriff über eine Funktionsgruppe im Menüband
1018
17.3 Quellcode im Editor bereinigen
1019
17.3.1 Zusammenfassung mit »With … End With«
1019
17.3.2 Entfernen von Standardwerten
1020
17.3.3 Kopieren und Verschieben auf direktem Weg
1022
17.4 Bereiche adressieren
1023
17.4.1 Markieren von Zellen über »Range« und »Cells«
1024
17.4.2 Auswählen von Zellen in anderen Tabellenblättern
1025
17.4.3 Den aktiven Bereich markieren
1025
17.4.4 »ActiveCell« und »Offset« zum Markieren nutzen
1026
17.4.5 Verwendung von Bereichsnamen
1026
17.5 Arbeiten mit Variablen
1027
17.5.1 Deklaration von Variablen
1027
17.5.2 Verwendung einer Variablen zur Suche nach der ersten leeren Zeile
1029
17.5.3 Eine weitere Variable zum Suchen nach der ersten leeren Spalte
1029
17.5.4 Verwenden der »SpecialCells«-Methode
1030
17.6 Umgang mit Programmfehlern
1031
17.6.1 Debugging-Modus
1032
17.6.2 Nutzung von Haltepunkten
1032
17.6.3 Testen des Makros im Einzelschrittmodus
1033
17.6.4 Nutzung des Direktfensters
1034
17.7 Kopieren, Verschieben und Filtern von Daten
1034
17.7.1 Aufzeichnung eines Kopiervorgangs
1035
17.7.2 Daten per Makro bestehenden Datenbeständen anhängen
1036
17.7.3 Deklaration der Variablen
1036
17.7.4 Mit den Variablen auf Objekte verweisen
1037
17.7.5 Variablen mit einem berechneten Wert füllen
1037
17.7.6 Verkürzung der Anweisung zum Kopieren
1037
17.7.7 Verwendung des Variablenwertes als Zellbezug des Kopiervorgangs
1038
17.7.8 Verwendung von dynamischen Bereichen statt Variablen
1039
17.7.9 Daten einer Tabelle anhängen
1040
17.7.10 Ermittlung der Größe von Quell- und Zieldatenbereich
1040
17.7.11 Ausschneiden der aktuellen Daten – Anhängen an die vorhandenen Daten
1040
17.7.12 Anwendung des erweiterten Filters in einem Makro
1042
17.7.13 Deklaration der Variablen für das erweiterte Filtern
1043
17.7.14 Bestimmung der Tabellengröße des Listenbereichs
1043
17.7.15 Erstellen des Kriterienbereichs und Zuweisen des Bereichs zu einer Variablen
1044
17.7.16 Flexible Erweiterung des Kriterienbereichs
1044
17.7.17 Erstellen des weiteren Bereichs und Variablenzuweisungen
1044
17.7.18 Durchführung des erweiterten Filtervorgangs
1045
17.7.19 Testen des Makros
1045
17.7.20 Fazit zum Thema Kopieren, Verschieben und Filtern
1046
17.8 Zugriff auf Dateien über VBA-Makros
1047
17.8.1 Auswählen einer Datei über den Datei-öffnen-Dialog
1047
17.8.2 Öffnen einer Datei aus Excel heraus
1048
17.8.3 Anpassung des Codevorschlags aus der VBA-Hilfe
1049
17.8.4 Die »If«-Anweisung beim Öffnen der Datei
1050
17.8.5 Öffnen von beliebigen Dateitypen aus einer Arbeitsmappe heraus
1051
17.8.6 Angabe der Lokalisierungswerte
1051
17.8.7 Einfügen einer CSV-Datei in eine geöffnete Arbeitsmappe
1052
17.8.8 Quelltext des Makros zum Einfügen von CSV-Dateien
1052
17.9 Fallbeispiel: CSV-Import und Datenaktualisierung für einen Forecast
1054
17.9.1 Importieren und Anhängen der aktuellen Daten
1055
17.9.2 Betrachten des aktuellen Reports
1056
17.10 Flusskontrolle mit »If … Then … Else«
1057
17.10.1 Fettdruck und Farbe für Summenzeilen mit »If … Then … End If«
1057
17.10.2 Adressierung der Zellbereiche in diesem Makro
1059
17.10.3 »Else«-Anweisung im »If … Then«
1060
17.10.4 »Select Case« als Lösung für Mehrfachbedingungen
1061
17.10.5 »Select Case« am Beispiel einer bedingten Formatierung
1061
17.10.6 Verwendung von »Case Else«
1062
17.11 Programmierung von Schleifen in VBA
1063
17.11.1 Erstellen einer »For … Next«-Schleife
1063
17.11.2 Definition des Zählers
1064
17.11.3 Verlassen der Schleife und Ausführen einer Anweisung
1064
17.11.4 Verwendung anderer Variablenbezeichnungen im Zähler
1065
17.11.5 Exkurs: Leere Zeilen ohne Schleifen finden und löschen
1065
17.11.6 Praxisbeispiel: Kostenstellendaten auf verschiedene Tabellenblätter verteilen
1066
17.11.7 Voraussetzungen in dieser Beispieldatei
1066
17.11.8 Deklaration der Variablen
1067
17.11.9 Zuweisung der Objekte zu den Variablen
1067
17.11.10 Festlegung des Zählerwertes und Beginn der Schleife
1067
17.11.11 Bestimmung der einzelnen Kostenstellen als Filterkriterium
1068
17.11.12 Schleifen mit Objektvariablen und »For Each … In … Next«
1070
17.11.13 Schrift- und Hintergrundfarben mit »For Each … In … Next« zählen
1070
17.11.14 Erzeugen einer Uploaddatei für Fremdsysteme mit »Do Until … Loop«
1071
17.11.15 Beschreibung der Kopieranweisungen im »Do Until«-Block
1072
17.11.16 Definition der Bedingung für die Ausführung von »Do Until … Loop«
1073
17.11.17 Schleifen mit »Do While … Loop«
1074
17.12 Formeln und Funktionen in VBA-Makros
1075
17.12.1 Grundzüge der Z1S1-Adressierung im Tabellenblatt
1075
17.12.2 Übertragen der Z1S1-Methode auf den Quelltext des Makros
1076
17.12.3 Definition von Formeln im Quelltext eines Makros
1076
17.12.4 Kopieren von Formeln und Funktionen in VBA
1078
17.12.5 Definition der Formeln und Funktionen nach der R1C1-Methode
1078
17.13 Gestaltung von Dialogen in VBA
1079
17.13.1 Inputbox und Messagebox
1079
17.13.2 Ausgabe von Werten in der aktiven Zelle
1081
17.13.3 Ausgabe von Werten in einer vordefinierten Zelle
1082
17.13.4 Entwurf und Nutzung von Formularen
1082
17.13.5 Bausteine für eine formulargesteuerte Dateneingabe
1083
17.13.6 Erstellen eines Formulars im VB-Editor
1083
17.13.7 Starten des Formulars mit einer Schaltfläche und einem Makro
1084
17.13.8 Anweisung zum Schließen des Formulars zuweisen
1085
17.13.9 Schreiben der Formularfeldinhalte in das Tabellenblatt
1086
17.13.10 Übernahme der vorhandenen Werte aus der Tabelle in das Formular
1086
17.13.11 Schließen des Formulars durch den Benutzer verhindern
1087
17.14 Benutzerdefinierte Funktionen
1088
17.14.1 Definition einer benutzerdefinierten Funktion
1088
17.14.2 Aufrufen einer benutzerdefinierten Funktion
1089
17.14.3 KW nach ISO 8601 – Nutzung einer VBA-Funktion als benutzerdefinierte Funktion
1091
17.14.4 Die VBA-Funktion »DatePart«
1091
17.14.5 Berechnung der KW nach ISO 8601
1092
17.14.6 Benutzerdefinierte Funktionen mit mehreren Argumenten
1093
17.14.7 Das Argument zur Bestimmung des Farbcodes
1094
17.14.8 Zellen mit farblicher Gestaltung zählen
1094
17.14.9 Gewichtete durchschnittliche Kapitalkosten als benutzerdefinierte Funktion
1095
17.15 Die Beispiele aus dem Buch zum Herunterladen
1096
Index
1097