Inhaltsverzeichnis

Alle Kapitel aufklappen
Alle Kapitel zuklappen
Vorwort
29
1 Neuerungen in Excel 2019
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
50
2.4 Cursorsteuerung und Bewegen in Tabellen
52
2.5 Zellbereiche markieren
54
2.6 Zahlen- und andere Formate schnell zuweisen
55
2.7 Inhalte löschen
57
2.8 Diagramme erstellen und bearbeiten
57
2.9 AutoFilter und Bearbeitung von sichtbaren Zellen
58
2.10 Weitere nützliche Tastenkombinationen
59
3 xlSMILE – Excel-Lösungen mit System
61
3.1 Simplify – Big Data nutzen und Datenmüll entfernen
61
3.2 Model – systematisch arbeiten und Reports automatisieren
62
3.3 Integrate – Layouts entwickeln und Tabellen und Diagramme anwenden
63
3.4 Lead – Benutzer führen und Fehleingaben verhindern
64
3.5 Explain – informieren und zusammenfassen
65
4 Daten importieren und bereinigen
67
4.1 Textdatei aus einem Warenwirtschaftssystem importieren
69
4.1.1 Textkonvertierungs-Assistent
70
4.1.2 Ein Datenmodell in Excel während des Imports erstellen
74
4.1.3 Fehlerhafte Datenformate nachträglich umwandeln
77
4.2 Transaktionsdaten in einer CSV-Datei auswerten
79
4.2.1 Nicht benötigte Zeilen aus Transaktionsdaten entfernen
79
4.2.2 Überflüssige Leerzeilen mit einem Makro entfernen
81
4.2.3 Gruppierung nach Standort und Konten
82
4.2.4 Kontengruppen in Transaktionsdaten zusammenfassen
84
4.2.5 Reporting von Zahlungsbewegungen mit AutoFilter, Teilergebnissen und Sparklines
86
4.2.6 Nur Zahlungseingänge der gefilterten Konten addieren
88
4.2.7 Ein- und Ausgänge mit Sparklines visualisieren
89
4.3 Daten mit Microsoft Query importieren und Soll-Ist-Vergleich durchführen
90
4.3.1 Abfrage auf einer Access-Datenbank
91
4.3.2 Abfrage mit Microsoft Query bearbeiten
94
4.4 Daten von einem SQL Server aus Excel abfragen
99
4.5 Vorhandene Datenverbindungen nutzen
100
4.6 OLAP-Cubes und Analysis Services
102
4.6.1 Technische Voraussetzungen der Analysis Services
103
4.6.2 Bestandteile eines Data Cubes
104
4.6.3 Vorteile von OLAP und Analysis Services
104
4.6.4 Zugriff auf Analysis Services
105
4.7 Importieren von externen Daten mit Power Pivot
105
4.8 Importieren von Webinhalten
107
4.9 Importieren und Exportieren von XML-Daten
109
4.10 Zusammenfassung: Datenimport und -bereinigung
111
5 Datenbereinigung mit Power Query effizienter gestalten
113
5.1 Wozu ist Power Query eigentlich gedacht?
114
5.2 CSV-Dateien mit Power Query importieren
114
5.3 Einfache Schritte der Datenbereinigung ausführen
120
5.4 Gruppieren und Spalten berechnen
125
5.5 Bedingte Berechnungen in Spalten
129
5.6 Power Query als Ersatz für Textfunktionen in Excel
131
5.7 Weitere Optionen beim Erstellen von Spalten und Gruppierungsmerkmalen
136
5.7.1 Datumsgruppierungen in Power Query erstellen
137
5.7.2 Neue Spalten aus Beispielen erstellen
137
5.8 Suchtabellen durch Anfügen von Abfragen erzeugen
139
5.8.1 Abfragen organisieren
143
5.9 Alle Excel-Dateien eines Ordners importieren und bereinigen
145
5.9.1 Import aller Dateien eines Ordners
146
5.9.2 Bereinigung der importierten Dateien eines Ordners
147
5.10 Alltäglicher Datensalat – Power-Query-Lösungen für den Alltag
149
5.10.1 Tabelleninhalte vergleichen
149
5.10.2 Entpivotieren von Rohdaten aus ERP-Systemen
153
5.10.3 Eindeutigen Schlüssel aus mehreren Spalten erstellen
154
5.10.4 Zellinhalte trennen
157
5.10.5 Manuell erstellte Tabellen in Listen umwandeln
160
6 Unternehmensdaten prüfen und analysieren
165
6.1 Standardsortierung und benutzerdefiniertes Sortieren
166
6.1.1 Erstellen einer benutzerdefinierten Liste
166
6.1.2 Benutzerdefiniertes Sortieren in Kombination mit Teilergebnissen
168
6.2 AutoFilter und Datenschnitte
170
6.2.1 AutoFilter und die Funktion TEILERGEBNIS()
173
6.3 Vorteile des erweiterten Filters
174
6.3.1 Aufbau des erweiterten Filters
176
6.3.2 Ausführen des Filtervorgangs
177
6.3.3 Kombination mehrerer Kriterien mit UND
178
6.3.4 Kombination mehrerer Kriterien mit ODER
179
6.3.5 Verknüpfung von Kriterien mit UND in einer Spalte
180
6.3.6 Vergleichsoperatoren bei numerischen Filterkriterien
180
6.3.7 Vergleichsoperatoren bei Textkriterien
181
6.3.8 Berechnete Filterkriterien
182
6.4 Erweiterter Filter mit einem VBA-Makro
183
6.4.1 Quelltext des VBA-Makros
184
6.4.2 Einsatzgebiete für das VBA-Makro
186
6.5 Verwendung von Datenbankfunktionen
186
6.5.1 Grundstruktur der Datenbankfunktionen
187
6.5.2 Definition der Kriterien für die Berechnung von Datenbankfunktionen
188
6.5.3 Verfügbare Datenbankfunktionen
189
6.5.4 Editieren und Kopieren von Datenbankfunktionen
190
6.5.5 Soll-Ist-Vergleich mithilfe von Datenbankfunktionen
190
6.5.6 Auswahl von Produktcode oder Kategorie über eine Eingabeliste
191
6.5.7 Ausgabe von Artikelname und Listenpreis
192
6.5.8 Darstellung der Ist- und Soll-Umsätze mittels Datenbankfunktion
193
6.5.9 Darstellung der Soll-Ist-Ergebnisse im Diagramm
194
6.5.10 Formatierung des Diagramms
196
6.6 Konsolidierung von Daten
200
6.6.1 Betrachtung der Ausgangsdaten
201
6.6.2 Verwendbare Spalten für die Konsolidierung
202
6.6.3 Verwendung von Spaltenüberschriften bei der Konsolidierung
203
6.6.4 Konsolidierung der Daten einer Arbeitsmappe
204
6.6.5 Übernahme der Beschriftung und Konsolidierung aus der linken Spalte
205
6.6.6 Konsolidierung auf Basis der Spaltenüberschriften
207
6.6.7 Verknüpfung der Konsolidierung mit den Originaldaten
209
6.6.8 Konsolidierung von Daten aus unterschiedlichen Arbeitsmappen
211
6.6.9 Konsolidierung durch Nutzung von Bereichsnamen
211
6.6.10 Konsolidierung mit geöffneten Dateien
213
6.7 Zusammenfassung: Basisanalyse
214
7 Dynamische Reports erstellen
217
7.1 Das 5-Minuten-Datenmodell
217
7.2 Bestandteile eines Datenmodells
219
7.2.1 Grundsätzliche Überlegungen zu den Elementen eines Datenmodells
219
7.2.2 Grundsätzliche Überlegungen zu Berechnungen in einem Datenmodell
224
7.2.3 Basisanforderungen an die Erstellung von multivariablen Datenmodellen und Reports
225
7.3 Datenmodell für einen Forecast erstellen
226
7.3.1 Festlegung der Arbeitsmappenstruktur für den Forecast
227
7.3.2 Strukturierte Bezüge und Bereichsnamen
231
7.3.3 Liste eindeutiger Produktcodes erstellen
234
7.3.4 Dynamische Zeilen- und Spaltenbeschriftungen
236
7.3.5 Bedingte Kalkulation für Soll, Ist und Prognose
238
7.3.6 Methoden zur Berechnung von Prognosen
240
7.3.7 Berechnung einer Prognose mithilfe des gleitenden Mittelwertes
242
7.3.8 Steuerelemente für die Benutzereingaben im Forecast
242
7.3.9 Datenblatt für die Diagrammdaten
245
7.3.10 Rollierende Liniendiagramme
246
7.3.11 Dynamische Tabelle mit der Funktion INDEX()
247
7.3.12 Formate, Formatvorlagen, Diagrammvorlagen
248
7.3.13 Dynamische Bereichsnamen im Diagramm
250
7.3.14 Kommentare in Datenmodellen einsetzen
252
7.4 Datenmodell zur Kalkulation der optimalen Bestellmenge
253
7.4.1 Definition der Bereichsnamen für die Kalkulationsfaktoren
255
7.4.2 Das Formelgerüst der Optimierung
255
7.4.3 Darstellung der Optimierung im Diagramm
256
7.4.4 Formatierung und Zellschutz
257
7.5 Rollierende Berichte
259
7.5.1 Dynamische Bereichsnamen als Grundlage von dynamischen Diagrammen
261
7.5.2 Dynamische Bereichsnamen in Diagrammen
262
7.5.3 Dynamischer Diagrammtitel
263
7.6 Zusammenfassung: Datenmodelle
264
8 Wichtige Kalkulationsfunktionen für Controller
267
8.1 Berechnungen mit Datumsbezug
268
8.1.1 Dynamische Datumslisten ohne Wochenenden
271
8.1.2 Berechnung der Kalenderwoche nach ISO 8601:2000 und des Quartals
272
8.1.3 Berechnung von Nettoarbeitstagen
273
8.1.4 Berechnung der verbleibenden Tage bis zum Monats- oder Projektende
275
8.1.5 Feiertage berechnen
276
8.1.6 Dynamischer Kalender für alle Bundesländer
277
8.1.7 Berechnung des Enddatums für Vorgänge
281
8.1.8 Berechnung von Datumsdifferenzen mit DATEDIF()
281
8.1.9 Weitere nützliche Funktionen in der Kategorie »Datum & Zeit«
282
8.2 Berechnungen mit Zeitangaben
283
8.2.1 Formatierung von Uhrzeiten
284
8.2.2 Umrechnung von Dezimal- in Industriezeit
284
8.2.3 Berechnung von Arbeitszeiten bei Schichtbetrieb
285
8.3 Arbeiten mit Verweisen und Matrizen
286
8.3.1 Erste Spalte oder Zeile einer Matrix durchsuchen
287
8.3.2 Transponieren einer Matrix
288
8.3.3 Finden des letzten Eintrags einer Spalte oder Zeile
290
8.4 Funktionen zur Dynamisierung von Tabellen
292
8.4.1 Dynamischen Summenbereich mit BEREICH.VERSCHIEBEN() erstellen
293
8.4.2 Zusammengesetzte Zellbezüge mit INDIREKT() erstellen
297
8.4.3 INDIREKT() zum Ansteuern von Zellen in anderen Tabellenblättern
300
8.4.4 Finden und Berechnen von Daten mit INDEX() und VERGLEICH()
301
8.4.5 Auswahl von Berechnungsalternativen – WAHL() statt WENN()
306
8.5 Berechnung von Rangfolgen
308
8.5.1 Funktionen zur Bildung von Rangfolgen
310
8.5.2 Eindeutige Rangfolge bei identischen Werten der Liste
311
8.5.3 Eindeutige Rangfolge berechnen
312
8.5.4 Eindeutige Ursprungsdaten erzeugen
314
8.6 Berechnung von Mittelwerten
315
8.6.1 Mittelwert, Median, Modalwert
315
8.6.2 Gestutzter Mittelwert
317
8.6.3 Bedingte Mittelwerte
318
8.7 Runden von Daten
320
8.7.1 Runden auf ganze Zehner, Hunderter oder Tausender
321
8.7.2 OBERGRENZE() und UNTERGRENZE()
322
8.7.3 Runden auf ein Vielfaches mit VRUNDEN()
322
8.8 Neue Textfunktionen ab Excel 2016 (Office 365)
323
8.9 Fehlerunterdrückung
324
8.9.1 Formelüberwachung als Mittel der Ursachenanalyse
325
8.9.2 Unterdrücken von Fehlerwerten
326
8.9.3 Praktische Anwendung
327
8.10 Einsatz von logischen Funktionen
329
8.10.1 Mehrfachprüfungen in Excel 2016 mit der Funktion WENNS()
331
8.10.2 Codierungen in Excel 2016 umwandeln mit ERSTERWERT()
332
9 Neue dynamische Matrixfunktionen in Excel für Office 365
335
9.1 Das Control-Shift-Enter-Beben
336
9.1.1 Grundlagen der neuen dynamischen Matrixfunktionen
337
9.1.2 Speicherort und Editierbarkeit der neuen Matrixfunktionen
339
9.1.3 Excel läuft über … und schon sind Fehlerwerte möglich
340
9.1.4 Mit dem Spiller auf Überlaufbereiche zugreifen
342
9.1.5 Überlauf in Zellbereiche und Funktionen verhindern
346
9.1.6 Übersicht über die neuen dynamischen Matrixfunktionen
346
9.1.7 Automatisches Sortieren von Daten mit SORTIEREN() und SORTIERENNACH()
349
9.1.8 Ein Ergebnis, aber viele Sortierkriterien – SORTIERENNACH()
350
9.1.9 Automatische Datenauszüge mit FILTER() erstellen
351
9.1.10 Mehrfachkriterien mit logischem UND/ODER beim automatischen Filtern verwenden
353
9.1.11 Duplikate aus Listen mit der Funktion EINDEUTIG() entfernen
354
9.1.12 Eindeutige Werte auf Basis mehrere Spalten mithilfe von WAHL() extrahieren
355
9.1.13 Dynamische Datenreihen mit der Funktion SEQUENZ() generieren
357
9.1.14 Dynamische Datumsreihen durch die Kombination von Datumsfunktionen und SEQUENZ() erzeugen
358
9.1.15 Zufallszahlen mit der Funktion ZUFALLSMATRIX() erstellen
360
9.1.16 Extrahieren einzelner Werte mithilfe von EINZELW()
361
9.2 Neue Optionen für die Erstellung dynamischer Datenmodelle
363
9.2.1 Erzeugen dynamischer Produktlisten und Datumsreihen mit EINDEUTIG()
364
9.2.2 Bedingte Kalkulationen im Kontext der neuen Matrixfunktionen
365
9.2.3 Einbindung eines Forecasts mit veränderlichem Datumsbereich
367
9.2.4 Auswahl von Datenbereichen mit WAHL() und Überlaufbereichen
369
10 Bedingte Kalkulationen in Datenanalysen
371
10.1 Kalkulationen ohne Bedingungen
372
10.2 Kalkulationen mit einer Bedingung
374
10.3 Bereichsnamen – der schnelle Zugriff auf Datenbereiche
377
10.3.1 Verwendung sprechender Bereichsnamen
379
10.3.2 Editieren von Bereichsnamen
382
10.4 Fehlervermeidung bei der Eingabe von Bedingungen – die Datenüberprüfung
383
10.4.1 Eingabe von Duplikaten mit der Datenüberprüfung vermeiden
386
10.4.2 Datenüberprüfungen bearbeiten oder entfernen
387
10.5 Bedingte Kalkulationen mit mehr als einer Bedingung
388
10.6 Vorteile von SUMMENPRODUKT() gegenüber anderen Funktionen zur bedingten Kalkulation
396
10.7 Multiplikation von Textwerten mit SUMMENPRODUKT()
396
10.8 Bedingte Kalkulation mit ODER im Tabellenblatt »Report_III«
397
10.9 Ausschluss von Datensätzen bei bedingten Kalkulationen
398
10.10 Häufigkeiten schnell berechnen
399
10.11 Mittelwerte ohne Nullwerte berechnen
402
10.12 Mittelwert bei #DIV/0!
403
10.13 Fallbeispiel zur bedingten Kalkulation
404
10.13.1 Anzahl unterschiedlicher Zahlenwerte im Datenbereich
405
10.13.2 Häufigste Artikelbezeichnung im Datenbereich
406
10.13.3 Bedingte Kalkulation in Tabelle und Diagramm über Auswahlliste steuern
407
10.14 Zusammenfassung: Bedingte Kalkulationen
409
11 Pivottabellen und -diagramme
413
11.1 Vorbereitung der Basisdaten für eine Pivottabelle
414
11.2 Pivottabellen erstellen
418
11.2.1 Datenlabels hinzufügen, entfernen und anders anordnen
421
11.2.2 Anpassungen und Abkürzungen beim Erstellen des Pivottabellenlayouts
424
11.2.3 Berechnungsfunktionen ändern
425
11.2.4 Prozentual oder absolut? Rangfolge oder Kumulation? Die Datendarstellung macht den Report
427
11.2.5 Fallbeispiel 1: Anteil eines regionalen Artikels am Gesamtergebnis
430
11.2.6 Fallbeispiel 2: Auswertung nach KW und Kumulation der KW-Ergebnisse
432
11.2.7 Fallbeispiel 3: Kundenranking auf Basis des Bestellwertes
433
11.2.8 Fallbeispiel 4: Bewertung der Datenqualität
435
11.3 Pivotcache und Speicherbedarf
437
11.4 Visuelle interaktive Analyse von Daten
438
11.4.1 Datenschnitt in der Pivottabelle aktivieren
440
11.4.2 Gestaltung und Anordnung der Datenschnitttools
441
11.4.3 Datenanalyse mithilfe der Datenschnitttools
442
11.4.4 Mehrere Pivottabellen per Datenschnitt steuern
443
11.4.5 Weitere Einstellungen für die Datenschnitttools
444
11.5 Zeitbezogene Auswertungen von Pivottabellen mit Zeitachsen
445
11.5.1 Automatische Gruppierung von Datumswerten ab Excel 2019
445
11.5.2 Zeitachsen einfügen
447
11.6 Filtern von Daten in einer Pivottabelle
448
11.7 Gruppierungen in Pivottabellen
448
11.7.1 Manuelle Gruppierung von Produkten
449
11.7.2 Tabellenlayouts
452
11.7.3 Standardlayout für Pivottabellen festlegen
453
11.7.4 Sortieroptionen
455
11.7.5 Gruppierungen mittels berechneter Produktgruppen
456
11.7.6 Aufbau eines Datenmodells zur Gruppierung
460
11.7.7 Automatische Gruppierung nach Kalenderwochen
464
11.7.8 Kalenderwochen nach ISO 8601
465
11.7.9 Pivottabellen mit berechneten Feldern
467
11.8 Weiterverarbeitung von Daten aus Pivottabellen
473
11.8.1 PIVOTDATENZUORDNEN() bei einem Soll-Ist-Vergleich
474
11.8.2 Anpassung der Funktion PIVOTDATENZUORDNEN()
475
11.8.3 Der Fehler #BEZUG! bei Anwendung von PIVOTDATENZUORDNEN()
475
11.8.4 PIVOTDATENZUORDNEN() zum Umsetzen von Reportlayouts
476
11.8.5 Andere Formen der Weiterverarbeitung von Pivottabellen
478
11.9 Personaldaten mithilfe von Pivottabellen konsolidieren
480
11.9.1 Erste Spalte anpassen, um Konsolidierung zu optimieren
481
11.9.2 Personaldaten konsolidieren
482
11.9.3 Personalnummern und Namen der Konsolidierungsspalte trennen
484
11.9.4 Daten durch Konsolidierung »pivotierbar« machen
486
11.10 Grundlegendes zu PivotCharts
488
11.10.1 Einschränkungen bei Pivotdiagrammen
490
11.10.2 Schaltflächen in Pivotdiagrammen
491
11.10.3 Punkt-(XY-)Diagramm aus einer Pivottabelle erstellen
492
11.10.4 Alternativen bei der Erstellung eines XY-Diagramms aus Pivotdaten
495
11.10.5 Andere Techniken der grafischen Darstellung von Pivottabellen
495
11.11 Zusammenfassung: Pivottabellen und PivotCharts
498
12 Business Intelligence mit Power Pivot
501
12.1 Arbeiten auf der Self-BI-Baustelle
502
12.2 Inhaltliches und Organisatorisches zu den Beispielen
503
12.3 Die Power-Pivot-Oberfläche im Überblick
504
12.4 Logische Beziehungen statt SVERWEIS() und Co.
509
12.5 Berechnete Spalten und berechnete Felder unterscheiden
513
12.6 Eine berechnete Spalte erstellen
514
12.7 Eine Power-Pivot-Tabelle in Excel erstellen
515
12.8 Mehr Übersichtlichkeit herstellen
517
12.9 Referenztabellen einbinden
519
12.10 Einbinden von Daten aus anderen Datenquellen
521
12.11 Typische Erkennungszeichen für fehlende logische Beziehungen in Datenmodellen
523
12.12 Tabellen der Arbeitsmappe in das Datenmodell einbinden
526
12.13 Tabellen des Datenmodells ausblenden
529
12.14 Berechnete Felder in Power-Pivot-Tabellen verwenden
531
12.14.1 Implizite und explizite Measures
532
12.14.2 Aggregierungsfunktionen in Power Pivot
533
12.15 Bearbeiten von berechneten Feldern
536
12.16 Bedingte Kalkulationen mit CALCULATE()
537
12.17 Datenschnitte und Zeitachsen
542
12.18 Wie DAX-Funktionen arbeiten
546
12.18.1 CALCULATE() in einem Filterkontext
548
12.18.2 Filter fließen immer nur abwärts
549
12.18.3 Datenschnittfilter aufheben
551
12.19 Bedingte Formatierungen und Diagramme in Power-Pivot-Reports
553
13 Excel als Planungswerkzeug
557
13.1 Wettbewerberanalyse
557
13.1.1 Datenüberprüfungen im Bewertungsformular
558
13.1.2 Bereichsnamen der Codierung
559
13.1.3 Kopieren der Datenüberprüfungen
560
13.1.4 Berechnung der erreichten Punktzahl
560
13.1.5 Visualisierung mit Sparklines
561
13.2 Potenzialanalyse
564
13.2.1 Grafische Darstellung der Potenziale
565
13.2.2 Anzeige von Linie und Wert in einer Zelle
567
13.2.3 Kopieren der Liniendiagramme
567
13.2.4 Gegenüberstellung von Potenzialen und Handlungsfeldern
567
13.2.5 Erstellen der Stärken-Schwächen-Diagramme
569
13.3 Portfolioanalyse
569
13.3.1 Erstellen des Blasendiagramms
570
13.3.2 Nachbearbeitung des Blasendiagramms
571
13.3.3 Beschriftung der Datenpunkte im Blasendiagramm
572
13.3.4 Betrachtung weiterer Portfoliodimensionen
573
13.4 Stärken-Schwächen-Analyse
574
13.4.1 Erstellen der Datenbasis für das Stärken-Schwächen-Diagramm
575
13.4.2 Einfügen der zweiten Datenreihe
576
13.4.3 Anpassen des Diagramms
577
13.5 Absatzplanung
579
13.5.1 Planung auf Basis einer strukturierten Eingabetabelle
580
13.5.2 Berechnen statt kopieren – Übertragen der Daten in ein neues Blatt zur Trendberechnung
581
13.5.3 Übernahme der Stückzahlangaben mit INDEX()
582
13.5.4 Verwendung der Funktion SCHÄTZER() für die Prognose
583
13.5.5 Verwendung des Szenario-Managers in der Umsatzplanung
584
13.5.6 Planung auf Basis von Transaktionsdaten
586
13.5.7 Sichtung der Datenbasis mittels Pivottabelle
587
13.5.8 Kumulierte Darstellung der Monatsdaten
588
13.5.9 Pivotdiagramm mit dynamischer Beschriftung
588
13.5.10 Sichtung der Vorjahresdaten mit Datenschnitttool
590
13.5.11 Auswertung per Pivottabelle und Datenschnitt
591
13.5.12 Nutzung der Trendfunktion zum Erstellen einer Umsatzprognose
592
13.5.13 Umwandlung der exportierten Liste in eine gestaltete Tabelle
593
13.5.14 Anwendung der Trendfunktion
594
13.5.15 Visualisierung der Umsatzplanung mit Sparklines
595
13.5.16 Gliederung von Umsatz- und Prognosewerten
596
13.6 Prognosen erstellen
597
13.6.1 Datenqualität beurteilen: Korrelationskoeffizient und Bestimmtheitsmaß
597
13.6.2 Bestimmtheitsmaß im Diagramm anzeigen
598
13.6.3 Bestimmtheitsmaß berechnen
599
13.6.4 Berechnung des Korrelationskoeffizienten
599
13.6.5 Trendbereinigung
600
13.6.6 Gleitender Mittelwert
601
13.6.7 Exponentielle Glättung
603
13.7 Personalplanung
604
13.7.1 Eingabe der Personalstrukturdaten
606
13.7.2 Berechnung und Anpassung der Grundgehälter
607
13.7.3 Berechnung der vermögenswirksamen Leistungen
608
13.7.4 Zuordnung der Telefonpauschale
609
13.7.5 Berechnung der Kfz-Zuschläge und Pensionen
610
13.7.6 Berechnung der Sozialabgaben
612
13.7.7 Berechnung der weiteren Sozialabgaben
613
13.7.8 Darstellung von Zwischenergebnissen
613
13.7.9 Vorbereitung möglicher Auswertungen des Personalkosten-Forecasts
614
13.7.10 Erstellen der Pivottabelle
616
13.7.11 Soll-Ist-Vergleiche der Personalkosten
617
13.7.12 Soll-Ist-Vergleich für einen Mitarbeiter erstellen
618
13.7.13 Berechnung der Soll-Werte auf Grundlage der Gesamtkostentabelle
619
13.7.14 Berechnung der Ist-Werte auf Basis der Downloaddaten
620
13.7.15 Fazit – Personalplanung
621
13.8 Liquiditätsplanung
621
13.8.1 Gliederung aus Berechnungen erstellen
622
13.8.2 Summen für Spalten und AutoGliederung
624
13.8.3 Fenster fixieren
625
13.8.4 Strukturierung von Tabellen mit Designfarben
626
13.8.5 Erstellen eigener Designfarben
627
13.8.6 Zuweisen von RGB-Werten nach CI-Vorgaben
627
13.9 Marktanalyse und Absatzplanung
628
13.9.1 Daten der Marktanalyse
628
13.9.2 Struktur der Vertriebsdaten
629
13.9.3 Bestimmung der Artikel und Vertriebskanäle mit Absatzpotenzial
630
13.9.4 Berechnung der Potenziale
631
13.9.5 Berechnung der Potenzialhöhe
632
13.9.6 Darstellung der Potenziale im Diagramm
633
14 Operatives Controlling mit Excel
635
14.1 Betriebsabrechnungsbogen
635
14.1.1 Arbeitsmappenstruktur des Betriebsabrechnungsbogens
636
14.1.2 Konsolidierung von Standorten oder Monaten
638
14.1.3 Anpassung der Bereichsnamen
639
14.1.4 Umlage der Primärkosten im BAB
641
14.1.5 Verteilungsschlüssel der Sekundärkostenumlage
641
14.1.6 Berechnung der kalkulatorischen Abschreibungen
642
14.1.7 Einbeziehung der kalkulatorischen Zinsen
644
14.1.8 Berechnung der kalkulatorischen Risiken
644
14.2 Divisionskalkulation
646
14.2.1 Durchführung der Vorkalkulation
647
14.2.2 Durchführung der Nachkalkulation
647
14.2.3 Zellschutz für die Kalkulationsbereiche
647
14.3 Zuschlagskalkulation
648
14.3.1 Durchführung der Vorkalkulation
649
14.3.2 Durchführung der Nachkalkulation
650
14.4 Äquivalenzziffernrechnung
651
14.4.1 Bildung der Äquivalenzziffern
652
14.4.2 Verwendung der Äquivalenzziffern in der Kostenkalkulation
652
14.5 Prozesskostenrechnung
653
14.5.1 Arbeitsschritte zur Durchführung der Prozesskostenrechnung
654
14.5.2 Tabellenaufbau bei Anwendung der Prozesskostenrechnung
654
14.5.3 Berechnung des Prozesskostensatzes und der Selbstkosten
655
14.5.4 Zuordnung der leistungsmengenneutralen Kosten
656
14.6 Deckungsbeitragsrechnung
657
14.7 Dynamische Break-even-Analyse
658
14.7.1 Erstellen der Datenreihen für das Diagramm
660
14.7.2 Berechnung der Umsatz- und Kostenwerte
661
14.7.3 Erstellen des Liniendiagramms
661
14.7.4 Einfügen des Drehfeldes
662
14.7.5 Generieren einer dynamischen Beschriftung im Diagramm
665
14.7.6 Einfügen der dynamischen Beschriftung in das Liniendiagramm
667
14.8 Mehrstufige Deckungsbeitragsrechnung
667
14.9 Planen von Kosten und Erlösen mithilfe von Szenarien
668
14.9.1 Erstellen eines Szenarios aus einer Gewinnschwellenanalyse
669
14.9.2 Erfassen des ersten Szenarios
670
14.9.3 Abrufen der Szenarien
672
14.9.4 Erstellen eines Szenarioberichts
672
14.10 Produktkalkulation mit Deckungsbeitragsrechnung
673
14.10.1 Berechnungsgrundlage von Deckungsbeitrag I und II
674
14.10.2 Arbeitsmappenstruktur der Beispielanwendung
675
14.10.3 Berechnung von Deckungsbeitrag I
676
14.10.4 Erfassung und Berechnung der kundenbezogenen Prozesskosten
677
14.10.5 Berechnung des Deckungsbeitrags II und quartalsweise Auswertung
679
14.10.6 Bedingte Kalkulation auf Basis von Datum und Kunden-ID
680
14.10.7 Übertragung der Funktionen auf die weiteren Quartale
682
14.10.8 Gliederung der Daten und Fixierung des Fensters
683
14.10.9 Durchführung der Produktkalkulation
683
14.10.10 Datenüberprüfungen zur Artikel- und Prozessauswahl
683
14.10.11 Formeln und Funktionen zur Berechnung der Herstellkosten
684
14.10.12 Abschluss und Schutz der Berechnungen
685
14.11 Eigenfertigung oder Fremdbezug (make or buy)
686
14.11.1 Aufbau des Kalkulationsmodells
686
14.11.2 Bestimmung der kritischen Menge
687
14.11.3 Darstellung der Kostenverläufe im Diagramm
687
14.11.4 Schlussbemerkung
689
14.12 Zinsen, Tilgung, Annuitäten für Darlehen berechnen
689
14.12.1 Raten mit festen Annuitäten
690
14.12.2 Aufteilung in Zinsen und Tilgung
691
14.12.3 Monatsraten und Zinsen
691
14.12.4 Tilgung berechnen
692
14.12.5 Zukünftigen Wert berechnen
693
14.12.6 Effektiv- und Nominalzins berechnen
693
14.12.7 Barwert auf Basis regelmäßiger zukünftiger Zahlungen
694
14.13 Abschreibungen
694
14.13.1 Arithmetisch-degressive Abschreibung
695
14.13.2 Weitere Abschreibungsmethoden und -funktionen
696
14.14 Methoden der Investitionsrechnung
697
14.14.1 Kostenvergleichsmethode
698
14.14.2 Eingabe der Kosten in das Kalkulationsformular
699
14.14.3 Gewinnvergleich
700
14.14.4 Rentabilitätsvergleich
701
14.14.5 Amortisationsrechnung
702
14.14.6 Kapitalwertmethode
703
14.14.7 Methode des internen Zinsfußes
704
14.14.8 Interner Zinsfuß mit der Zielwertsuche finden
706
14.14.9 Modifizierter interner Zinsfuß
706
14.14.10 Annuitätenmethode
707
14.14.11 Berechnung der Annuitäten
708
14.14.12 Zusammenführung aller Berechnungsergebnisse
709
14.14.13 Investitionsentscheidungen mit Szenarien unterstützen
710
14.14.14 Regeln bei der Erstellung der Szenarien
711
14.15 Customer Lifetime Value
711
14.15.1 Übersicht über die Funktionen der Beispielanwendung
712
14.15.2 Bestandteile des Customer Lifetime Values
713
14.15.3 Die Bindungsrate
713
14.15.4 Der Kundenumsatz
714
14.15.5 Die Kosten
715
14.15.6 Erfassung und Zuordnung der Umsätze
715
14.15.7 Prognose der diskontierten Umsätze eines Kunden
716
14.15.8 Auswahl des Kunden
716
14.15.9 Berechnung der vorhandenen Deckungsbeiträge des Kunden
717
14.15.10 Prognose der zu erwartenden Kundenumsätze
717
14.15.11 Berechnung des Abzinsungsfaktors
719
14.15.12 Diskontierung der prognostizierten Umsätze
719
14.15.13 Bestimmung der prozessbezogenen Kosten
720
14.15.14 Berechnung der entstandenen Kosten pro Kunde
721
14.15.15 Prognose der Kosten – Herstellkosten, Boni und Rabatte
722
14.15.16 Erfassung sämtlicher anderer Kostenarten
722
14.15.17 Bestimmungsgrößen des Referenzwertes
722
14.15.18 Der Referenzindex in der Beispieldatei
723
14.15.19 Dokumentation der Bewertungsergebnisse
724
14.15.20 Der Bindungsindex in der Beispieldatei
725
14.16 Kundenscoring
726
14.17 Personalstrukturanalyse
728
14.17.1 Auswertung der Altersstruktur
729
14.17.2 Auswertung nach Alter und Geschlecht
731
14.17.3 Altersstruktur im Diagramm darstellen
732
14.17.4 Auswertung der Betriebszugehörigkeit
733
14.18 Arbeitszeitanalyse
733
14.18.1 Festlegung der Konsolidierungsbereiche
734
14.18.2 Erstellen des Soll-Ist-Vergleichs
736
14.19 Reisekostenabrechnung
737
14.19.1 Sperren von Zellen und Schutz des Tabellenblattes
738
14.19.2 Druckbereich festlegen und überflüssige Spalten/Zeilen ausblenden
738
14.19.3 Dateifenster konfigurieren und schützen
739
14.20 Lieferantenbewertung
740
14.20.1 Aufbau der Beispielanwendung
741
14.20.2 Elemente des Eingabeformulars
742
14.20.3 Erstellen der ActiveX-Kombinationsfelder
743
14.20.4 Definition der Formular-Eingabefelder
743
14.20.5 Erstellen der Formularsteuerelemente
743
14.20.6 Struktur des Makros zum Erstellen der Excel-Liste
745
14.20.7 Aufrufen des VBA-Editors
746
14.20.8 Inhalt des VBA-Makros zum Erstellen der Excel-Liste
747
14.20.9 Deklarieren einer Variablen
748
14.20.10 Programmieren einer Schleife zur Suche der nächsten Leerzeile
748
14.20.11 Überprüfung einer Bedingung
749
14.20.12 Anhängen der Daten an die Excel-Liste
749
14.20.13 Leeren der Zellen im Tabellenblatt »Zusammenfassung«
750
14.20.14 Lieferantenbewertung – Zwischenrechnung
750
14.20.15 Durchschnittliche Bewertung der Lieferanten
751
14.20.16 Bildung der Rangfolge
751
14.20.17 Automatische Sortierung der Daten
752
14.20.18 Grafische Darstellung der Lieferantenbewertung
752
15 Unternehmenssteuerung und Kennzahlen
755
15.1 Zielkostenmanagement (Target Costing)
756
15.1.1 Ausgangslage der Zielkostenberechnung
757
15.1.2 Bestimmung der Zielkosten
759
15.1.3 Analyse der Kostenstruktur und Identifizierung der Kostenlücke
760
15.1.4 Bestimmung der Ziellücke
761
15.1.5 Schema für die Anpassung der Kostenstruktur
761
15.1.6 Ermittlung der Kundenpräferenzen
761
15.1.7 Bildung des Zielkostenindex
762
15.1.8 Umsetzung der Kostenstrukturanpassung in Excel
762
15.1.9 Berechnung der Einsparpotenziale
763
15.1.10 Tabellenaufbau und Navigation durch die Tabellenabschnitte
764
15.2 Cashflow
765
15.2.1 Beispieldateien und Datenmodelle
766
15.2.2 Direkte Ermittlung des Cashflows
767
15.2.3 Indirekte Ermittlung des Cashflows
768
15.3 Free Cashflow
769
15.4 Discounted Cashflow
770
15.5 Gewichtete durchschnittliche Gesamtkapitalkosten nach Steuern
771
15.6 Shareholder-Value
772
15.6.1 Free Cashflows und Residualwert
773
15.6.2 Barwerte der Free Cashflows berechnen
775
15.6.3 Berechnung des Residualwertes
775
15.6.4 Abschließende Bildung des Shareholder-Values
776
15.7 Economic Value Added – EVA®
777
15.7.1 Aufbau der Beispieldatei
777
15.7.2 Berechnung NOPAT
777
15.7.3 Berechnung der Net Operating Assets
778
15.7.4 Berechnung der Gesamtkapitalkosten und des EVA®
778
15.7.5 Allgemeine Informationen zum EVA®
779
15.8 Market Value Added – MVA
779
15.8.1 Aufbau der Beispieldatei
779
15.8.2 Unternehmenswert berechnen
780
15.9 Bilanzkennzahlen
781
15.9.1 Gliederungsschema der Bilanz nach HGB
781
15.9.2 Internationalisierung der Rechnungslegung
782
15.9.3 Vorgaben zur Bilanzerstellung nach IAS/IFRS
782
15.9.4 Kennzahlennavigator
784
15.9.5 Übersicht und Interpretation von Vermögens- und Liquiditätskennzahlen
786
15.10 GuV-Gliederung
789
15.10.1 Gesamtkosten- und Umsatzkostenverfahren nach HGB
790
15.10.2 Kennzahlen zu Rentabilität und Kapitalstruktur
792
15.11 Beispieldatei GuV – Bilanz – Kapitalfluss
795
15.11.1 Mehrjährige GuV-Analyse
795
15.11.2 Erfassung und Berechnung der Bilanzdaten im Fünfjahresvergleich
796
15.11.3 Berechnung des Cashflows aus GuV- und Bilanzdaten
797
15.12 Return on Investment und DuPont-Schema
798
15.12.1 Einzelschritte bei der ROI-Berechnung
799
15.12.2 Interpretation der Ergebnisse des DuPont-Schemas
800
15.12.3 Fazit
802
15.13 Messung der Mitarbeiterzufriedenheit
802
15.13.1 Ablauf von Befragungen zur Mitarbeiterzufriedenheit
803
15.13.2 Aufbau eines Fragebogens
803
15.13.3 Vermeidung der Mehrfachbeantwortung einer Frage
804
15.13.4 Definition einer Fehlermeldung
805
15.13.5 Übertragung der Datenüberprüfung auf die weiteren Fragen
806
15.13.6 Festlegung und Automatisierung des Auswertungsablaufs
806
15.13.7 Speichern des ausgefüllten Fragebogens unter einem eindeutigen Dateinamen
807
15.13.8 Zuordnung einer Schaltfläche zum VBA-Makro
808
15.13.9 Aufbau der Auswertungstabelle der Fragebogendatei
808
15.13.10 Verbergen des Tabellenblattes zur Auswertung der Antworten
810
15.13.11 Automatisierte Auswertung der Fragebögen
810
15.13.12 Aufbau der Beispieldatei
810
15.13.13 Kurzbeschreibung des VBA-Makros zum Datenimport
812
15.13.14 Quelltext des VBA-Makros zum Datenimport
812
15.13.15 Makro – Teil 1: Definition der Arbeitsumgebung
813
15.13.16 Makro – Teil 2: Öffnen der Antwortdateien durch eine Schleife
814
15.13.17 Makro – Teil 3: Ermitteln der nächsten freien Spalte
815
15.13.18 Makro – Teil 4: Kopieren und Einfügen der Antwortdaten
815
15.13.19 Makro – Teil 5: Schließen der Antwortdatei/Wiederholen der Prozedur
815
15.13.20 Namensdefinition für die Auswertung der importierten Daten
816
15.13.21 Auswertung der Fragebögen
817
15.14 Selbstbewertung nach EFQM
818
15.14.1 Übersicht über die neun Kriterien des EFQM-Modells
819
15.14.2 Erstellen der Kriterienübersicht als Schaubild
820
15.14.3 Kopieren und Anpassen der AutoForm-Vorlage
820
15.14.4 Beschriftung der AutoFormen
821
15.14.5 Formular zur Bestimmung von Erfüllungsgrad und Handlungsbedarf
822
15.14.6 Berechnung der Ergebnisse der Selbst- und Fremdbewertung
823
15.14.7 Bestimmung des Handlungskoeffizienten
825
15.14.8 Bestandteile und Aufbau des EFQM-Cockpits
825
15.14.9 Vergleich von Erfüllungsgrad und Handlungsbedarf im Netzdiagramm
825
15.14.10 Interpretationen der Datendarstellung im Netzdiagramm
827
15.14.11 Diagramme des Cockpits
828
15.14.12 Performancedarstellung mit Bullet Graphs
829
15.14.13 Balkendiagramm zur Darstellung des Handlungsbedarfs
830
15.14.14 Ampeldarstellung für die Handlungskoeffizienten
831
15.14.15 Schützen der Cockpit- und Fragebogeninhalte
832
15.14.16 Weitere Kennzahlen im EFQM-Cockpit
833
16 Reporting mit Diagrammen und Tabellen
835
16.1 Grundlagen
835
16.1.1 Zu viel und doch zu wenig?
836
16.1.2 Mut zur Lücke! Aber was kann man weglassen?
836
16.1.3 Was Sie stattdessen wissen und nutzen sollten
837
16.2 Das Standarddiagramm in Excel
837
16.2.1 Diagrammerstellung über das Menüband
839
16.2.2 Bestimmen der Datenreihen und Beschriftungen
841
16.2.3 Zwei Vorgehensweisen – ein Ziel: Änderung von Elementeigenschaften
841
16.3 Wichtige Gestaltungsregeln
842
16.4 Umgang mit Farben
846
16.5 Auswahl des richtigen Diagrammtyps
847
16.5.1 Vergleich von Werten und Darstellung von Rangfolgen – Balkendiagramm und Säulendiagramm
848
16.5.2 Vergleich mehrerer Datenreihen und des Gesamtergebnisses – Stapelsäulen
849
16.5.3 Wertevergleich bei mehr als einer Größenachse – Netzdiagramm
851
16.5.4 Entwicklung von Werten in Zeitreihen – Liniendiagramm
852
16.5.5 Darstellung der Anteile an einem Gesamtergebnis – Balken- oder Säulendiagramm
854
16.5.6 Darstellung von Abweichungen – Säulendiagramm oder Liniendiagramm
856
16.5.7 Darstellung der Korrelation zwischen Werten – Punktdiagramm
857
16.5.8 Trendlinie und Bestimmtheitsmaß im Punktdiagramm
858
16.5.9 Aufnahme einer dritten Koordinate – Blasendiagramm
860
16.5.10 Darstellung von Datenverteilungen
861
16.5.11 Darstellung des Verlaufs von Aktienkursen oder Rohstoffpreisen – Kursdiagramm
863
16.5.12 Verbunddiagramme
864
16.6 Die neuen Diagrammtypen in Excel 2016 und 2019
865
16.6.1 Wasserfalldiagramm
866
16.6.2 Trichter- oder Funneldiagramm
868
16.6.3 Histogramm
869
16.6.4 Sunburst-Diagramm
872
16.6.5 Treemap-Diagramm
875
16.6.6 Kastendiagramm
877
16.7 Allgemeine Formatierungsregeln
879
16.7.1 Verwendung und Funktionsweise der Designfarben
879
16.7.2 Erstellen eigener Designfarben
879
16.8 Elemente und Gestaltungsregeln für Dashboards
880
16.9 Infografiken seit Excel 2016
882
16.10 Power View
883
16.11 3D-Karten
887
16.12 Kombinationen aus Tabellen und Diagramm erstellen
888
16.13 Dynamische Diagramme
890
16.14 Spezielle Diagrammtypen
895
16.14.1 Tachometerdiagramm mit Ampeldarstellung und Werteskala
895
16.14.2 Thermometerdiagramm
900
16.14.3 Wasserfalldiagramm
901
16.14.4 Tornadodiagramm
902
16.14.5 Gantt-Diagramm
905
16.15 Spezielle Formatierungen im Diagramm
907
16.15.1 Werteabhängige Formatierung: Kennzeichnung von Maximal- und Minimalwert
907
16.15.2 Bedingte Formatierung von Datenpunkten
912
16.16 Diagramme in Tabellenblättern
916
16.16.1 Erstellen einer Heatmap
916
16.16.2 Textfunktionen und grafische Tabellendarstellung
921
16.16.3 Nutzung von Sparklines
924
16.17 Dashboards erstellen
929
16.17.1 Verwendung von Sparklines in Dashboards
932
16.17.2 Darstellung geografischer Daten in Dashboards
934
16.17.3 Verwendung von Ringdiagrammen in Dashboards
936
16.18 Übernahme in PowerPoint
939
16.18.1 Erstellen von Tabellen und Diagrammen in PowerPoint
939
16.18.2 Verwenden einer Tabelle oder eines Diagramms als Verknüpfung
940
16.18.3 Einbetten eines Excel-Objekts in PowerPoint
943
16.18.4 Verwendung von Designfarben in PowerPoint
943
16.19 Übernahme in Word
947
17 Automatisierung mit Makros – VBA für Controller
949
17.1 Wie alles anfängt: die Aufzeichnung eines Makros
950
17.1.1 Testen des aufgezeichneten Makros
952
17.1.2 Ein Blick hinter die Kulissen: Ihr Makro im Makro-Editor
953
17.1.3 Struktur des aufgezeichneten Makros
954
17.1.4 Quelltext des aufgezeichneten Makros – Objekt, Methode, Eigenschaft
955
17.1.5 Weitere Informationen und Hilfen im Makro-Editor nutzen
955
17.1.6 Makro im Editor überarbeiten
957
17.1.7 Testen des überarbeiteten Makros
959
17.2 Makros über Schaltflächen aufrufen
959
17.2.1 Alternativen zum Aufruf von Makros über Schaltflächen
961
17.2.2 Zugriff über die Symbolleiste für den Schnellzugriff
961
17.2.3 Zugriff über eine Funktionsgruppe im Menüband
962
17.3 Quellcode im Editor bereinigen
963
17.3.1 Zusammenfassung mit »With … End With«
964
17.3.2 Entfernen von Standardwerten
964
17.3.3 Kopieren und Verschieben auf direktem Weg
967
17.4 Bereiche adressieren
967
17.4.1 Markieren von Zellen über »Range« und »Cells«
968
17.4.2 Auswählen von Zellen in anderen Tabellenblättern
969
17.4.3 Den aktiven Bereich markieren
970
17.4.4 »ActiveCell« und »Offset« zum Markieren nutzen
970
17.4.5 Verwendung von Bereichsnamen
970
17.5 Arbeiten mit Variablen
971
17.5.1 Deklaration von Variablen
972
17.5.2 Verwendung einer Variablen zur Suche nach der ersten leeren Zeile
973
17.5.3 Eine weitere Variable zum Suchen nach der ersten leeren Spalte
974
17.5.4 Verwenden der »SpecialCells«-Methode
974
17.6 Umgang mit Programmfehlern
976
17.6.1 Debugging-Modus
976
17.6.2 Nutzung von Haltepunkten
977
17.6.3 Testen des Makros im Einzelschrittmodus
978
17.6.4 Nutzung des Direktfensters
978
17.7 Kopieren, Verschieben und Filtern von Daten
979
17.7.1 Aufzeichnung eines Kopiervorgangs
979
17.7.2 Daten per Makro an bestehende Datenbestände anhängen
980
17.7.3 Deklaration der Variablen
981
17.7.4 Mit den Variablen auf Objekte verweisen
981
17.7.5 Variablen mit einem berechneten Wert füllen
982
17.7.6 Verkürzung der Anweisung zum Kopieren
982
17.7.7 Verwendung des Variablenwertes als Zellbezug des Kopiervorgangs
982
17.7.8 Verwendung von dynamischen Bereichen statt Variablen
983
17.7.9 Daten an eine Tabelle anhängen
984
17.7.10 Ermittlung der Größe von Quell- und Zieldatenbereich
985
17.7.11 Ausschneiden der aktuellen Daten – Anhängen an die vorhandenen Daten
985
17.7.12 Anwendung des erweiterten Filters in einem Makro
986
17.7.13 Deklaration der Variablen für das erweiterte Filtern
987
17.7.14 Bestimmung der Tabellengröße des Listenbereichs
988
17.7.15 Erstellen des Kriterienbereichs und Zuweisen des Bereichs zu einer Variablen
988
17.7.16 Flexible Erweiterung des Kriterienbereichs
989
17.7.17 Erstellen des weiteren Bereichs und Variablenzuweisungen
989
17.7.18 Durchführung des erweiterten Filtervorgangs
990
17.7.19 Testen des Makros
990
17.7.20 Fazit zum Thema Kopieren, Verschieben und Filtern
991
17.8 Zugriff auf Dateien über VBA-Makros
991
17.8.1 Auswählen einer Datei über den Datei-Öffnen-Dialog
992
17.8.2 Öffnen einer Datei aus Excel heraus
993
17.8.3 Anpassung des Codevorschlags aus der VBA-Hilfe
994
17.8.4 Die »If«-Anweisung beim Öffnen der Datei
994
17.8.5 Öffnen von beliebigen Dateitypen aus einer Arbeitsmappe heraus
995
17.8.6 Angabe der Lokalisierungswerte
996
17.8.7 Einfügen einer CSV-Datei in eine geöffnete Arbeitsmappe
997
17.8.8 Quelltext des Makros zum Einfügen von CSV-Dateien
997
17.9 Fallbeispiel: CSV-Import und Datenaktualisierung für einen Forecast
999
17.9.1 Importieren und Anhängen der aktuellen Daten
999
17.9.2 Betrachten des aktuellen Reports
1001
17.10 Flusskontrolle mit »If … Then … Else«
1001
17.10.1 Fettdruck und Farbe für Summenzeilen mit »If … Then … End If«
1002
17.10.2 Adressierung der Zellbereiche in diesem Makro
1004
17.10.3 »Else«-Anweisung im »If … Then«
1004
17.10.4 »Select Case« als Lösung für Mehrfachbedingungen
1005
17.10.5 »Select Case« am Beispiel einer bedingten Formatierung
1005
17.10.6 Verwendung von »Case Else«
1006
17.11 Programmierung von Schleifen in VBA
1007
17.11.1 Erstellen einer »For … Next«-Schleife
1008
17.11.2 Definition des Zählers
1008
17.11.3 Verlassen der Schleife und Ausführen einer Anweisung
1009
17.11.4 Verwendung anderer Variablenbezeichnungen im Zähler
1010
17.11.5 Exkurs: Leere Zeilen ohne Schleifen finden und löschen
1010
17.11.6 Praxisbeispiel: Kostenstellendaten auf verschiedene Tabellenblätter verteilen
1011
17.11.7 Voraussetzungen in dieser Beispieldatei
1011
17.11.8 Deklaration der Variablen
1012
17.11.9 Zuweisung der Objekte zu den Variablen
1012
17.11.10 Festlegung des Zählerwertes und Beginn der Schleife
1012
17.11.11 Bestimmung der einzelnen Kostenstellen als Filterkriterium
1013
17.11.12 Schleifen mit Objektvariablen und »For Each … In … Next«
1014
17.11.13 Schrift- und Hintergrundfarben mit »For Each … In … Next« zählen
1015
17.11.14 Erzeugen einer Uploaddatei für Fremdsysteme mit »Do Until … Loop«
1016
17.11.15 Beschreibung der Kopieranweisungen im »Do Until«-Block
1017
17.11.16 Definition der Bedingung für die Ausführung von »Do Until … Loop«
1018
17.11.17 Schleifen mit »Do While … Loop«
1019
17.12 Formeln und Funktionen in VBA-Makros
1019
17.12.1 Grundzüge der Z1S1-Adressierung im Tabellenblatt
1020
17.12.2 Übertragen der Z1S1-Methode auf den Quelltext des Makros
1021
17.12.3 Definition von Formeln im Quelltext eines Makros
1021
17.12.4 Kopieren von Formeln und Funktionen in VBA
1023
17.12.5 Definition der Formeln und Funktionen nach der R1C1-Methode
1023
17.13 Gestaltung von Dialogen in VBA
1023
17.13.1 Inputbox und Messagebox
1024
17.13.2 Ausgabe von Werten in der aktiven Zelle
1026
17.13.3 Ausgabe von Werten in einer vordefinierten Zelle
1027
17.13.4 Entwurf und Nutzung von Formularen
1027
17.13.5 Bausteine für eine formulargesteuerte Dateneingabe
1028
17.13.6 Erstellen eines Formulars im VB-Editor
1028
17.13.7 Starten des Formulars mit einer Schaltfläche und einem Makro
1030
17.13.8 Anweisung zum Schließen des Formulars zuweisen
1030
17.13.9 Schreiben der Formularfeldinhalte in das Tabellenblatt
1031
17.13.10 Übernahme der vorhandenen Werte aus der Tabelle in das Formular
1032
17.13.11 Schließen des Formulars durch den Benutzer verhindern
1033
17.14 Benutzerdefinierte Funktionen
1033
17.14.1 Definition einer benutzerdefinierten Funktion
1033
17.14.2 Aufrufen einer benutzerdefinierten Funktion
1034
17.14.3 KW nach ISO 8601: Nutzung einer VBA-Funktion als benutzerdefinierte Funktion
1036
17.14.4 Die VBA-Funktion »DatePart«
1036
17.14.5 Berechnung der KW nach ISO 8601
1037
17.14.6 Benutzerdefinierte Funktionen mit mehreren Argumenten
1038
17.14.7 Das Argument zur Bestimmung des Farbcodes
1039
17.14.8 Zellen mit farblicher Gestaltung zählen
1040
17.14.9 Gewichtete durchschnittliche Kapitalkosten als benutzerdefinierte Funktion
1040
17.15 Die Beispiele aus dem Buch zum Herunterladen
1041
Index
1043