Excel im Controlling (eBook)

Aus dem Lektorat 4
Inhaltsverzeichnis 6
Vorwort 52
Der neue Masterplan für Excel 53
Die Macht des Alltäglichen 54
32.767 Zeichen sind genug. Oder? 55
Vom Desktop ins Netz und auf die Mobilgeräte 56
1   Neuerungen in Excel 58
2   Tipps, Tricks und Tastenkürzel – zeitsparende Techniken für Controller 73
2.1   Daten effizient eingeben 74
2.1.1   Eingabe von Werten aus Listen 74
2.1.2   Benutzerdefinierte Listen 76
2.1.3   AutoAusfüll-Optionen 78
2.1.4   Einfügen von aktuellen Datums- und Zeitwerten 79
2.1.5   Blitzvorschau – Einträge trennen und auf Spalten verteilen 80
2.2   Kopieren, Ausschneiden und Einfügen von Daten 81
2.3   Formelzusammenhänge erkennen 86
2.4   Cursorsteuerung und Bewegen in Tabellen 90
2.5   Zellbereiche markieren 94
2.6   Zahlen- und andere Formate schnell zuweisen 96
2.7   Inhalte löschen 99
2.8   Diagramme erstellen und bearbeiten 100
2.9   AutoFilter und Bearbeitung von sichtbaren Zellen 102
2.10   Erweiterte Filterfunktionen in Microsoft 365 104
2.11   Weitere nützliche Tastenkombinationen 107
2.12   Tabellenansichten in der Webversion von Excel nutzen 108
2.13   Kommentare, Notizen und Chat in Excel 110
3   xlSMILE – Excel-Lösungen mit System 113
3.1   Simplify – Big Data nutzen und Datenmüll entfernen 113
3.2   Model – systematisch arbeiten und Reports automatisieren 116
3.3   Integrate – Layouts entwickeln und Tabellen und Diagramme anwenden 118
3.4   Lead – Benutzer führen und Fehleingaben verhindern 119
3.5   Explain – informieren und zusammenfassen 121
4   Daten importieren und bereinigen 123
4.1   Textkonvertierungs-Assistent 126
4.1.1   Ein Datenmodell in Excel während des Imports erstellen 131
4.1.2   Fehlerhafte Datenformate nachträglich umwandeln 135
4.2   Transaktionsdaten in einer CSV-Datei auswerten 139
4.2.1   Nicht benötigte Zeilen aus Transaktionsdaten entfernen 139
4.2.2   Überflüssige Leerzeilen mit einem Makro entfernen 143
4.2.3   Gruppierung nach Standort und Konten 145
4.2.4   Kontengruppen in Transaktionsdaten zusammenfassen 148
4.2.5   Reporting von Zahlungsbewegungen mit AutoFilter, Teilergebnissen und Sparklines 151
4.2.6   Nur Zahlungseingänge der gefilterten Konten addieren 153
4.2.7   Ein- und Ausgänge mit Sparklines visualisieren 156
4.3   Importieren von externen Daten mit Power Pivot 158
5   Datenbereinigung mit Power Query effizienter gestalten 162
5.1   Wozu ist Power Query eigentlich gedacht? 163
5.2   CSV-Dateien mit Power Query importieren 165
5.3   Einfache Schritte der Datenbereinigung ausführen 173
5.4   Verbindungen zu anderen Datenquellen 181
5.4.1   Verbindung zu einer Access-Datenbank 181
5.4.2   Mit einem SQL-Datenbankserver verbinden 184
5.4.3   Mit einer Website verbinden 186
5.4.4   Daten aus einer aktiven Arbeitsmappe importieren 189
5.4.5   Daten aus dem Power BI Service nutzen 191
5.4.6   OneDrive-Ordnerinhalte importieren 194
5.5   Gruppieren und Spalten berechnen 197
5.6   Bedingte Berechnungen in Spalten 203
5.7   Power Query als Ersatz für Textfunktionen in Excel 207
5.8   Weitere Optionen beim Erstellen von Spalten und Gruppierungsmerkmalen 215
5.8.1   Datumsgruppierungen in Power Query erstellen 215
5.8.2   Neue Spalten aus Beispielen erstellen 216
5.8.3   Führende Nullen in einer Schlüsselspalte ergänzen 219
5.8.4   Zwei Tabellen über eine Schlüsselspalte in Power Query zusammenführen 220
5.9   Suchtabellen durch Anfügen von Abfragen erzeugen 224
5.9.1   Abfragen organisieren 230
5.10   Alle Excel-Dateien eines Ordners importieren und bereinigen 233
5.10.1   Import aller Dateien eines Ordners 234
5.10.2   Bereinigung der importierten Dateien eines Ordners 236
5.11   Alltäglicher Datensalat – Power-Query-Lösungen für den Alltag 240
5.11.1   Tabelleninhalte vergleichen 240
5.11.2   Entpivotieren von Rohdaten aus ERP-Systemen 245
5.11.3   Eindeutigen Schlüssel aus mehreren Spalten erstellen 247
5.11.4   Zellinhalte trennen 250
5.11.5   Manuell erstellte Tabellen in Listen umwandeln 255
5.12   Individuelle Datentypen in Power Query erstellen 262
5.12.1   Erste praktische Annäherung an die neuen Datentypen 264
5.12.2   Internetseiten als Quelle für eigene Datentypen verwenden 269
5.12.3   Nutzung von individuell erstellten Datentypen in einer Auswertung 272
5.12.4   Überlauf von Formeln beim Verweis auf Rich Data 275
5.12.5   Rich-Data-Felder als Argumente in Kalkulationsfunktionen wie »XVERWEIS()« 278
5.12.6   Eigene Datentypen im Team verfügbar machen 281
5.12.7   Eine Empfohlene Tabelle aus Power BI als Datentyp ins Menü einbinden 284
6   Unternehmensdaten prüfen und analysieren 288
6.1   Standardsortierung und benutzerdefiniertes Sortieren 289
6.1.1   Erstellen einer benutzerdefinierten Liste 290
6.1.2   Benutzerdefiniertes Sortieren in Kombination mit Teilergebnissen 291
6.2   AutoFilter und Datenschnitte 295
6.2.1   AutoFilter und die Funktion TEILERGEBNIS() 301
6.3   Vorteile des erweiterten Filters 304
6.3.1   Aufbau des erweiterten Filters 306
6.3.2   Ausführen des Filtervorgangs 307
6.3.3   Kombination mehrerer Kriterien mit UND 308
6.3.4   Kombination mehrerer Kriterien mit ODER 310
6.3.5   Verknüpfung von Kriterien mit UND in einer Spalte 311
6.3.6   Vergleichsoperatoren bei numerischen Filterkriterien 312
6.3.7   Vergleichsoperatoren bei Textkriterien 314
6.3.8   Berechnete Filterkriterien 315
6.4   Erweiterter Filter mit einem VBA-Makro 318
6.4.1   Quelltext des VBA-Makros 318
6.4.2   Einsatzgebiete für das VBA-Makro 321
6.5   Verwendung von Datenbankfunktionen 322
6.5.1   Grundstruktur der Datenbankfunktionen 323
6.5.2   Definition der Kriterien für die Berechnung von Datenbankfunktionen 324
6.5.3   Verfügbare Datenbankfunktionen 325
6.5.4   Editieren und Kopieren von Datenbankfunktionen 327
6.5.5   Soll-Ist-Vergleich mithilfe von Datenbankfunktionen 328
6.5.6   Auswahl von Produktcode oder Kategorie über eine Eingabeliste 329
6.5.7   Ausgabe von Artikelname und Listenpreis 330
6.5.8   Darstellung der Ist- und Soll-Umsätze mittels Datenbankfunktion 332
6.5.9   Darstellung der Soll-Ist-Ergebnisse im Diagramm 333
6.5.10   Formatierung des Diagramms 335
6.6   Konsolidierung von Daten 342
6.6.1   Betrachtung der Ausgangsdaten 343
6.6.2   Verwendbare Spalten für die Konsolidierung 344
6.6.3   Verwendung von Spaltenüberschriften bei der Konsolidierung 346
6.6.4   Konsolidierung der Daten einer Arbeitsmappe 347
6.6.5   Übernahme der Beschriftung und Konsolidierung aus der linken Spalte 349
6.6.6   Konsolidierung auf Basis der Spaltenüberschriften 352
6.6.7   Verknüpfung der Konsolidierung mit den Originaldaten 355
6.6.8   Konsolidierung von Daten aus unterschiedlichen Arbeitsmappen 357
6.6.9   Konsolidierung durch Nutzung von Bereichsnamen 358
6.6.10   Konsolidierung mit geöffneten Dateien 361
7   Dynamische Reports erstellen 363
7.1   Das 5-Minuten-Datenmodell 364
7.2   Bestandteile eines Datenmodells 367
7.2.1   Grundsätzliche Überlegungen zu den Elementen eines Datenmodells 367
7.2.2   Grundsätzliche Überlegungen zu Berechnungen in einem Datenmodell 373
7.2.3   Basisanforderungen an die Erstellung von multivariablen Datenmodellen und Reports 375
7.3   Datenmodell für einen Forecast erstellen 377
7.3.1   Festlegung der Arbeitsmappenstruktur für den Forecast 379
7.3.2   Strukturierte Bezüge und Bereichsnamen 384
7.3.3   Liste eindeutiger Produktcodes erstellen 389
7.3.4   Dynamische Zeilen- und Spaltenbeschriftungen 392
7.3.5   Bedingte Kalkulation für Soll, Ist und Prognose 394
7.3.6   Methoden zur Berechnung von Prognosen 398
7.3.7   Berechnung einer Prognose mithilfe des gleitenden Mittelwertes 400
7.3.8   Steuerelemente für die Benutzereingaben im Forecast 401
7.3.9   Datenblatt für die Diagrammdaten 406
7.3.10   Rollierende Liniendiagramme 407
7.3.11   Dynamische Tabelle mit der Funktion »INDEX()« 408
7.3.12   Formate, Formatvorlagen, Diagrammvorlagen 409
7.3.13   Dynamische Bereichsnamen im Diagramm 412
7.3.14   Kommentare in Datenmodellen einsetzen 415
7.4   Datenmodell zur Kalkulation der optimalen Bestellmenge 418
7.4.1   Definition der Bereichsnamen für die Kalkulationsfaktoren 420
7.4.2   Das Formelgerüst der Optimierung 421
7.4.3   Darstellung der Optimierung im Diagramm 422
7.4.4   Formatierung und Zellschutz 424
7.5   Rollierende Berichte 427
7.5.1   Dynamische Bereichsnamen als Grundlage von dynamischen Diagrammen 431
7.5.2   Dynamische Bereichsnamen in Diagrammen 432
7.5.3   Dynamischer Diagrammtitel 433
8   Wichtige Kalkulationsfunktionen für Controller 435
8.1   Berechnungen mit Datumsbezug 437
8.1.1   Dynamische Datumslisten ohne Wochenenden 441
8.1.2   Berechnung der Kalenderwoche nach ISO 8601:2000 und des Quartals 443
8.1.3   Berechnung von Nettoarbeitstagen 444
8.1.4   Berechnung der verbleibenden Tage bis zum Monats- oder Projektende 446
8.1.5   Feiertage berechnen 448
8.1.6   Dynamischer Kalender für alle Bundesländer 450
8.1.7   Berechnung des Enddatums für Vorgänge 454
8.1.8   Berechnung von Datumsdifferenzen mit »DATEDIF()« 455
8.1.9   Weitere nützliche Funktionen in der Kategorie »Datum & Zeit«
8.2   Berechnungen mit Zeitangaben 459
8.2.1   Formatierung von Uhrzeiten 460
8.2.2   Umrechnung von Dezimal- in Industriezeit 461
8.2.3   Berechnung von Arbeitszeiten bei Schichtbetrieb 462
8.3   Arbeiten mit Verweisen und Matrizen 464
8.3.1   Die Vorteile von »XVERWEIS()« gegenüber anderen Verweisfunktionen 466
8.3.2   »SVERWEIS()« durch »XVERWEIS()« ersetzen 467
8.3.3   »WVERWEIS()« und »INDEX()/VERGLEICH()« durch »XVERWEIS()« ersetzen 474
8.3.4   »XVERWEIS()« im Kontext einer Kalkulationsfunktion 476
8.3.5   Überlaufbereiche der Ergebnisspalten bei der Verwendung von »XVERWEIS()« 478
8.3.6   Summe der Suchwerte einer Referenztabelle direkt bilden 480
8.3.7   Zweidimensionale Verweise bei der Nutzung von »XVERWEIS()« 482
8.3.8   Tabellen spalten- und zeilenweise mit »SVERWEIS()« und »WVERWEIS()« durchsuchen 483
8.3.9   Finden des letzten Eintrags einer Spalte oder Zeile 489
8.4   Funktionen zur Dynamisierung von Tabellen 492
8.4.1   Dynamischen Summenbereich mit »BEREICH.VERSCHIEBEN()« erstellen 493
8.4.2   Zusammengesetzte Zellbezüge mit »INDIREKT()« erstellen 500
8.4.3   »INDIREKT()« zum Ansteuern von Zellen in anderen Tabellenblättern 504
8.4.4   Finden und Berechnen von Daten mit »INDEX()« und »VERGLEICH()« 506
8.4.5   Auswahl von Berechnungsalternativen – »WAHL()« statt »WENN()« 514
8.5   Berechnung von Rangfolgen 519
8.5.1   Funktionen zur Bildung von Rangfolgen 520
8.5.2   Eindeutige Rangfolge bei identischen Werten der Liste 522
8.5.3   Eindeutige Rangfolge berechnen 524
8.5.4   Eindeutige Ursprungsdaten erzeugen 526
8.6   Berechnung von Mittelwerten 529
8.6.1   Mittelwert, Median, Modalwert 529
8.6.2   Gestutzter Mittelwert 532
8.6.3   Bedingte Mittelwerte 533
8.7   Runden von Daten 536
8.7.1   Runden auf ganze Zehner, Hunderter oder Tausender 537
8.7.2   »OBERGRENZE()« und »UNTERGRENZE()« 539
8.7.3   Runden auf ein Vielfaches mit »VRUNDEN()« 539
8.8   Textfunktionen zur Bereinigung und Strukturierung von Daten 541
8.8.1   Texte als Zahl formatieren 545
8.8.2   Neue Textfunktionen ab Excel 2016 (Microsoft 365) 548
8.9   Fehlerunterdrückung 551
8.9.1   Formelüberwachung als Mittel der Ursachenanalyse 553
8.9.2   Überprüfen der Bezüge innerhalb der Arbeitsmappe und zu anderen Dateien 554
8.9.3   Unterdrücken von Fehlerwerten 557
8.9.4   Praktische Anwendung 559
8.10   Einsatz von logischen Funktionen 561
8.10.1   Mehrfachprüfungen mit der Funktion »WENNS()« (seit Excel 2016) 564
8.10.2   Codierungen umwandeln mit »ERSTERWERT()« (seit Excel 2016) 565
8.11   Berechnungen mit »LET()« erstellen – Funktionsweise und Nutzen 567
8.11.1   Einfache Beispiele für Berechnungen mithilfe von »LET()« 568
8.11.2   Berechnung einer Provision durch mehrfache Verwendung einer Variablen in »LET()« 571
8.11.3   Verwendung von anderen Kalkulationsfunktionen in »LET()« 573
8.11.4   Bestehende Kalkulationsfunktionen vereinfachen, fehlende ergänzen 575
8.11.5   Funktionsweise von »LET()« in Überlaufbereichen 578
8.12   Wie viel VBA benötigen Controller nach der Einführung von »LAMBDA()« noch? 583
8.12.1   Abzinsungsfaktor mit »LAMBDA()« berechnen 584
8.12.2   LAMBDA()-Funktionen mit mehreren Parametern erstellen 589
8.12.3   »LAMBDA()« in Kombination mit »LET()« 590
8.12.4   Rekursive Berechnungen in »LAMBDA()« 593
8.12.5   Testen einer rekursiven LAMBDA()-Funktion mit ME 596
8.12.6   Rekursive LAMBDA()-Funktionen zur Bereinigung von Zellinhalten 598
9   Neue dynamische Matrixfunktionen in Excel für Microsoft 365 605
9.1   Das Control-Shift-Enter-Beben 606
9.1.1   Grundlagen der neuen dynamischen Matrixfunktionen 607
9.1.2   Speicherort und Editierbarkeit der neuen Matrixfunktionen 610
9.1.3   Excel läuft über … und schon sind Fehlerwerte möglich 611
9.1.4   Mit dem Spiller auf Überlaufbereiche zugreifen 614
9.1.5   Überlauf in Zellbereiche und Funktionen verhindern 620
9.1.6   Übersicht über die neuen dynamischen Matrixfunktionen 621
9.1.7   Automatisches Sortieren von Daten mit »SORTIEREN()« und »SORTIERENNACH()« 625
9.1.8   Ein Ergebnis, aber viele Sortierkriterien – »SORTIERENNACH()« 628
9.1.9   Automatische Datenauszüge mit »FILTER()« erstellen 628
9.1.10   Mehrfachkriterien mit logischem UND/ODER beim automatischen Filtern verwenden 631
9.1.11   Duplikate aus Listen mit der Funktion »EINDEUTIG()« entfernen 633
9.1.12   Eindeutige Werte auf Basis mehrerer Spalten mithilfe von »WAHL()« extrahieren 635
9.1.13   Dynamische Datenreihen mit der Funktion »SEQUENZ()« generieren 637
9.1.14   Dynamische Datumsreihen durch die Kombination von Datumsfunktionen und »SEQUENZ()« erzeugen 639
9.1.15   Zufallszahlen mit der Funktion »ZUFALLSMATRIX()« erstellen 641
9.1.16   Extrahieren einzelner Werte mithilfe von »EINZELW()« 642
9.2   Neue Optionen für die Erstellung dynamischer Datenmodelle 646
9.2.1   Erzeugen dynamischer Produktlisten und Datumsreihen mit »EINDEUTIG()« 647
9.2.2   Bedingte Kalkulationen im Kontext der neuen Matrixfunktionen 649
9.2.3   Einbindung eines Forecasts mit veränderlichem Datumsbereich 651
9.2.4   Auswahl von Datenbereichen mit »WAHL()« und Überlaufbereichen 654
10   Bedingte Kalkulationen in Datenanalysen 656
10.1   Kalkulationen ohne Bedingungen 658
10.2   Kalkulationen mit einer Bedingung 662
10.3   Bereichsnamen – der schnelle Zugriff auf Datenbereiche 667
10.3.1   Verwendung sprechender Bereichsnamen 670
10.3.2   Editieren von Bereichsnamen 674
10.4   Fehlervermeidung bei der Eingabe von Bedingungen – die Datenüberprüfung 676
10.4.1   Eingabe von Duplikaten mit der Datenüberprüfung vermeiden 679
10.4.2   Datenüberprüfungen bearbeiten oder entfernen 681
10.5   Bedingte Kalkulationen mit mehr als einer Bedingung 683
10.5.1   Mehrfachbedingungen mit logischem ODER 687
10.6   Vorteile von »SUMMENPRODUKT()« gegenüber anderen Funktionen zur bedingten Kalkulation 695
10.7   Multiplikation von Textwerten mit »SUMMENPRODUKT()« 696
10.8   Bedingte Kalkulation mit ODER im Tabellenblatt »Report_III« 698
10.9   Ausschluss von Datensätzen bei bedingten Kalkulationen 699
10.10   Häufigkeiten schnell berechnen 701
10.11   Mittelwerte ohne Nullwerte berechnen 705
10.12   Mittelwert bei #DIV/0! 707
10.13   Fallbeispiel zur bedingten Kalkulation 709
10.13.1   Anzahl unterschiedlicher Zahlenwerte im Datenbereich 710
10.13.2   Häufigste Artikelbezeichnung im Datenbereich 711
10.13.3   Bedingte Kalkulation in Tabelle und Diagramm über Auswahlliste steuern 713
11   Pivottabellen und -diagramme 716
11.1   Vorbereitung der Basisdaten für eine Pivottabelle 717
11.2   Pivottabellen erstellen 725
11.2.1   Datenlabels hinzufügen, entfernen und anders anordnen 729
11.2.2   Anpassungen und Abkürzungen beim Erstellen des Pivottabellenlayouts 732
11.2.3   Berechnungsfunktionen ändern 734
11.2.4   Prozentual oder absolut? Rangfolge oder Kumulation? Die Datendarstellung macht den Report 736
11.2.5   Fallbeispiel 1: Anteil eines regionalen Artikels am Gesamtergebnis 741
11.2.6   Fallbeispiel 2: Auswertung nach KW und Kumulation der KW-Ergebnisse 743
11.2.7   Fallbeispiel 3: Kundenranking auf Basis des Bestellwertes 745
11.2.8   Fallbeispiel 4: Bewertung der Datenqualität 747
11.3   Pivotcache und Speicherbedarf 751
11.4   Visuelle interaktive Analyse von Daten 753
11.4.1   Datenschnitt in der Pivottabelle aktivieren 755
11.4.2   Gestaltung und Anordnung der Datenschnitttools 757
11.4.3   Datenanalyse mithilfe der Datenschnitttools 759
11.4.4   Mehrere Pivottabellen per Datenschnitt steuern 761
11.4.5   Weitere Einstellungen für die Datenschnitttools 763
11.5   Zeitbezogene Auswertungen von Pivottabellen mit Zeitachsen 765
11.5.1   Automatische Gruppierung von Datumswerten ab Excel 2019 765
11.5.2   Zeitachsen einfügen 767
11.6   Filtern von Daten in einer Pivottabelle 769
11.7   Gruppierungen in Pivottabellen 770
11.7.1   Manuelle Gruppierung von Produkten 771
11.7.2   Tabellenlayouts 775
11.7.3   Standardlayout für Pivottabellen festlegen 777
11.7.4   Sortieroptionen 779
11.7.5   Gruppierungen mittels berechneter Produktgruppen 781
11.7.6   Aufbau eines Datenmodells zur Gruppierung 788
11.7.7   Automatische Gruppierung nach Kalenderwochen 793
11.7.8   Kalenderwochen nach ISO 8601 795
11.7.9   Pivottabellen mit berechneten Feldern 797
11.8   Weiterverarbeitung von Daten aus Pivottabellen 807
11.8.1   »PIVOTDATENZUORDNEN()« bei einem Soll-Ist-Vergleich 808
11.8.2   Anpassung der Funktion »PIVOTDATENZUORDNEN()« 810
11.8.3   Der Fehler »#BEZUG!« bei Anwendung von »PIVOTDATENZUORDNEN()« 811
11.8.4   »PIVOTDATENZUORDNEN()« zum Umsetzen von Reportlayouts 812
11.8.5   Andere Formen der Weiterverarbeitung von Pivottabellen 815
11.9   Personaldaten mithilfe von Pivottabellen konsolidieren 818
11.9.1   Erste Spalte anpassen, um Konsolidierung zu optimieren 820
11.9.2   Personaldaten konsolidieren 822
11.9.3   Personalnummern und Namen der Konsolidierungsspalte trennen 824
11.9.4   Daten durch Konsolidierung »pivotierbar« machen 827
11.10   Grundlegendes zu PivotCharts 831
11.10.1   Einschränkungen bei Pivotdiagrammen 833
11.10.2   Schaltflächen in Pivotdiagrammen 834
11.10.3   Punkt-(XY-)Diagramm aus einer Pivottabelle erstellen 835
11.10.4   Alternativen bei der Erstellung eines XY-Diagramms aus Pivotdaten 840
11.10.5   Andere Techniken der grafischen Darstellung von Pivottabellen 840
11.11   Zusammenfassung: Pivottabellen und PivotCharts 846
12   Business Intelligence mit Power Pivot 850
12.1   Arbeiten auf der Self-BI-Baustelle 852
12.2   Inhaltliches und Organisatorisches zu den Beispielen 854
12.3   Die Power-Pivot-Oberfläche im Überblick 856
12.4   Logische Beziehungen statt »SVERWEIS()« und Co. 864
12.5   Berechnete Spalten und berechnete Felder unterscheiden 869
12.6   Eine berechnete Spalte erstellen 871
12.7   Eine Power-Pivot-Tabelle in Excel erstellen 874
12.8   Mehr Übersichtlichkeit herstellen 877
12.9   Referenztabellen einbinden 880
12.10   Einbinden von Daten aus anderen Datenquellen 884
12.11   Typische Erkennungszeichen für fehlende logische Beziehungen in Datenmodellen 887
12.12   Tabellen der Arbeitsmappe in das Datenmodell einbinden 891
12.13   Tabellen des Datenmodells ausblenden 897
12.14   Berechnete Felder in Power-Pivot-Tabellen verwenden 900
12.14.1   Implizite und explizite Measures 901
12.14.2   Aggregierungsfunktionen in Power Pivot 903
12.15   Bearbeiten von Measures 908
12.16   Bedingte Kalkulationen mit »CALCULATE()« 911
12.17   Datenschnitte und Zeitachsen 918
12.18   Wie DAX-Funktionen arbeiten 925
12.18.1   »CALCULATE()« in einem Filterkontext 927
12.18.2   Filter fließen immer nur abwärts 929
12.18.3   Datenschnittfilter aufheben 932
12.19   Verwendung von Zeitintelligenz-Funktionen in einem Datenmodell 936
12.20   Vorjahresvergleiche mit »SAMEPERIODLASTYEAR()« erstellen 941
12.21   Individuelle Zeitintervalle mit »DATEADD()« berechnen 943
12.22   Bedingte Formatierungen und Diagramme in Power-Pivot-Reports 946
13   Excel als Planungswerkzeug 951
13.1   Wettbewerberanalyse 952
13.1.1   Datenüberprüfungen im Bewertungsformular 953
13.1.2   Bereichsnamen der Codierung 954
13.1.3   Kopieren der Datenüberprüfungen 955
13.1.4   Berechnung der erreichten Punktzahl 956
13.1.5   Visualisierung mit Sparklines 958
13.2   Potenzialanalyse 963
13.2.1   Grafische Darstellung der Potenziale 964
13.2.2   Anzeige von Linie und Wert in einer Zelle 966
13.2.3   Kopieren der Liniendiagramme 967
13.2.4   Gegenüberstellung von Potenzialen und Handlungsfeldern 967
13.2.5   Erstellen der Stärken-Schwächen-Diagramme 968
13.3   Portfolioanalyse 970
13.3.1   Erstellen des Blasendiagramms 971
13.3.2   Nachbearbeitung des Blasendiagramms 972
13.3.3   Beschriftung der Datenpunkte im Blasendiagramm 973
13.3.4   Betrachtung weiterer Portfoliodimensionen 976
13.4   Stärken-Schwächen-Analyse 978
13.4.1   Erstellen der Datenbasis für das Stärken-Schwächen-Diagramm 979
13.4.2   Einfügen der zweiten Datenreihe 979
13.4.3   Anpassen des Diagramms 980
13.4.4   Werte aus Zellen als Beschriftung des Diagramms übernehmen 984
13.5   Absatzplanung 986
13.5.1   Planung auf Basis einer strukturierten Eingabetabelle 986
13.5.2   Berechnen statt kopieren – Übertragen der Daten in ein neues Blatt zur Trendberechnung 989
13.5.3   Übernahme der Stückzahlangaben mit »INDEX()« 990
13.5.4   Verwendung der Funktion »SCHÄTZER()« für die Prognose 992
13.5.5   Verwendung des Szenario-Managers in der Umsatzplanung 993
13.5.6   Planung auf Basis von Transaktionsdaten 996
13.5.7   Sichtung der Datenbasis mittels Pivottabelle 997
13.5.8   Kumulierte Darstellung der Monatsdaten 998
13.5.9   Pivotdiagramm mit dynamischer Beschriftung 998
13.5.10   Sichtung der Vorjahresdaten mit Datenschnitttool 1001
13.5.11   Auswertung per Pivottabelle und Datenschnitt 1002
13.5.12   Nutzung der Trendfunktion zum Erstellen einer Umsatzprognose 1004
13.5.13   Umwandlung der exportierten Liste in eine gestaltete Tabelle 1005
13.5.14   Anwendung der Trendfunktion 1006
13.5.15   Visualisierung der Umsatzplanung mit Sparklines 1008
13.5.16   Gliederung von Umsatz- und Prognosewerten 1010
13.6   Prognosen erstellen 1012
13.6.1   Datenqualität beurteilen: Korrelationskoeffizient und Bestimmtheitsmaß 1012
13.6.2   Bestimmtheitsmaß im Diagramm anzeigen 1013
13.6.3   Bestimmtheitsmaß berechnen 1014
13.6.4   Berechnung des Korrelationskoeffizienten 1015
13.6.5   Trendbereinigung 1015
13.6.6   Gleitender Mittelwert 1017
13.6.7   Exponentielle Glättung 1019
13.7   Personalplanung 1022
13.7.1   Eingabe der Personalstrukturdaten 1024
13.7.2   Berechnung und Anpassung der Grundgehälter 1027
13.7.3   Berechnung der vermögenswirksamen Leistungen 1028
13.7.4   Zuordnung der Telefonpauschale 1030
13.7.5   Berechnung der Kfz-Zuschläge und Pensionen 1031
13.7.6   Berechnung der Sozialabgaben 1033
13.7.7   Berechnung der weiteren Sozialabgaben 1035
13.7.8   Darstellung von Zwischenergebnissen 1036
13.7.9   Vorbereitung möglicher Auswertungen des Personalkosten-Forecasts 1037
13.7.10   Erstellen der Pivottabelle 1039
13.7.11   Soll-Ist-Vergleiche der Personalkosten 1041
13.7.12   Soll-Ist-Vergleich für einen Mitarbeiter erstellen 1042
13.7.13   Berechnung der Soll-Werte auf Grundlage der Gesamtkostentabelle 1043
13.7.14   Berechnung der Ist-Werte auf Basis der Downloaddaten 1045
13.7.15   Fazit – Personalplanung 1046
13.8   Liquiditätsplanung 1047
13.8.1   Gliederung aus Berechnungen erstellen 1048
13.8.2   Summen für Spalten und AutoGliederung 1051
13.8.3   Fenster fixieren 1052
13.8.4   Strukturierung von Tabellen mit Designfarben 1054
13.8.5   Erstellen eigener Designfarben 1055
13.8.6   Zuweisen von RGB-Werten nach CI-Vorgaben 1057
13.9   Marktanalyse und Absatzplanung 1059
13.9.1   Daten der Marktanalyse 1059
13.9.2   Struktur der Vertriebsdaten 1060
13.9.3   Bestimmung der Artikel und Vertriebskanäle mit Absatzpotenzial 1061
13.9.4   Berechnung der Potenziale 1062
13.9.5   Berechnung der Potenzialhöhe 1064
13.9.6   Darstellung der Potenziale im Diagramm 1066
14   Operatives Controlling mit Excel 1068
14.1   Betriebsabrechnungsbogen 1069
14.1.1   Arbeitsmappenstruktur des Betriebsabrechnungsbogens 1070
14.1.2   Konsolidierung von Standorten oder Monaten 1072
14.1.3   Anpassung der Bereichsnamen 1075
14.1.4   Umlage der Primärkosten im BAB 1076
14.1.5   Verteilungsschlüssel der Sekundärkostenumlage 1077
14.1.6   Berechnung der kalkulatorischen Abschreibungen 1079
14.1.7   Einbeziehung der kalkulatorischen Zinsen 1081
14.1.8   Berechnung der kalkulatorischen Risiken 1082
14.2   Divisionskalkulation 1084
14.2.1   Durchführung der Vorkalkulation 1085
14.2.2   Durchführung der Nachkalkulation 1086
14.2.3   Zellschutz für die Kalkulationsbereiche 1086
14.3   Zuschlagskalkulation 1089
14.3.1   Durchführung der Vorkalkulation 1089
14.3.2   Durchführung der Nachkalkulation 1090
14.4   Äquivalenzziffernrechnung 1093
14.4.1   Bildung der Äquivalenzziffern 1094
14.4.2   Verwendung der Äquivalenzziffern in der Kostenkalkulation 1095
14.5   Prozesskostenrechnung 1097
14.5.1   Arbeitsschritte zur Durchführung der Prozesskostenrechnung 1098
14.5.2   Tabellenaufbau bei Anwendung der Prozesskostenrechnung 1099
14.5.3   Berechnung des Prozesskostensatzes und der Selbstkosten 1100
14.5.4   Zuordnung der leistungsmengenneutralen Kosten 1102
14.6   Deckungsbeitragsrechnung 1104
14.7   Dynamische Break-even-Analyse 1107
14.7.1   Erstellen der Datenreihen für das Diagramm 1108
14.7.2   Berechnung der Umsatz- und Kostenwerte 1110
14.7.3   Erstellen des Liniendiagramms 1110
14.7.4   Einfügen des Drehfeldes 1112
14.7.5   Generieren einer dynamischen Beschriftung im Diagramm 1116
14.7.6   Einfügen der dynamischen Beschriftung in das Liniendiagramm 1119
14.8   Mehrstufige Deckungsbeitragsrechnung 1121
14.9   Planen von Kosten und Erlösen mithilfe von Szenarien 1123
14.9.1   Erstellen eines Szenarios aus einer Gewinnschwellenanalyse 1124
14.9.2   Erfassen des ersten Szenarios 1125
14.9.3   Abrufen der Szenarien 1127
14.9.4   Erstellen eines Szenarioberichts 1127
14.10   Produktkalkulation mit Deckungsbeitragsrechnung 1130
14.10.1   Berechnungsgrundlage von Deckungsbeitrag I und II 1131
14.10.2   Arbeitsmappenstruktur der Beispielanwendung 1131
14.10.3   Berechnung von Deckungsbeitrag I 1133
14.10.4   Erfassung und Berechnung der kundenbezogenen Prozesskosten 1134
14.10.5   Berechnung des Deckungsbeitrags II und quartalsweise Auswertung 1138
14.10.6   Bedingte Kalkulation auf Basis von Datum und Kunden-ID 1139
14.10.7   Übertragung der Funktionen auf die weiteren Quartale 1141
14.10.8   Gliederung der Daten und Fixierung des Fensters 1143
14.10.9   Durchführung der Produktkalkulation 1143
14.10.10   Datenüberprüfungen zur Artikel- und Prozessauswahl 1144
14.10.11   Formeln und Funktionen zur Berechnung der Herstellkosten 1145
14.10.12   Abschluss und Schutz der Berechnungen 1148
14.11   Eigenfertigung oder Fremdbezug (make or buy) 1149
14.11.1   Aufbau des Kalkulationsmodells 1149
14.11.2   Bestimmung der kritischen Menge 1151
14.11.3   Darstellung der Kostenverläufe im Diagramm 1151
14.11.4   Schlussbemerkung 1153
14.12   Zinsen, Tilgung, Annuitäten für Darlehen berechnen 1155
14.12.1   Raten mit festen Annuitäten 1155
14.12.2   Aufteilung in Zinsen und Tilgung 1156
14.12.3   Monatsraten und Zinsen 1158
14.12.4   Tilgung berechnen 1158
14.12.5   Zukünftigen Wert berechnen 1159
14.12.6   Effektiv- und Nominalzins berechnen 1160
14.12.7   Barwert auf Basis regelmäßiger zukünftiger Zahlungen 1161
14.13   Abschreibungen 1163
14.13.1   Arithmetisch-degressive Abschreibung 1163
14.13.2   Weitere Abschreibungsmethoden und -funktionen 1165
14.14   Methoden der Investitionsrechnung 1168
14.14.1   Kostenvergleichsmethode 1169
14.14.2   Eingabe der Kosten in das Kalkulationsformular 1170
14.14.3   Gewinnvergleich 1172
14.14.4   Rentabilitätsvergleich 1174
14.14.5   Amortisationsrechnung 1175
14.14.6   Kapitalwertmethode 1176
14.14.7   Methode des internen Zinsfußes 1179
14.14.8   Internen Zinsfuß mit der Zielwertsuche finden 1180
14.14.9   Modifizierter interner Zinsfuß 1181
14.14.10   Annuitätenmethode 1183
14.14.11   Berechnung der Annuitäten 1184
14.14.12   Zusammenführung aller Berechnungsergebnisse 1185
14.14.13   Investitionsentscheidungen mit Szenarien unterstützen 1186
14.14.14   Regeln bei der Erstellung der Szenarien 1187
14.15   Customer Lifetime Value 1189
14.15.1   Übersicht über die Funktionen der Beispielanwendung 1190
14.15.2   Bestandteile des Customer Lifetime Values 1191
14.15.3   Erfassung und Zuordnung der Umsätze 1194
14.15.4   Prognose der diskontierten Umsätze eines Kunden 1195
14.15.5   Auswahl des Kunden 1196
14.15.6   Berechnung der vorhandenen Deckungsbeiträge des Kunden 1196
14.15.7   Prognose der zu erwartenden Kundenumsätze 1198
14.15.8   Berechnung des Abzinsungsfaktors 1199
14.15.9   Diskontierung der prognostizierten Umsätze 1200
14.15.10   Bestimmung der prozessbezogenen Kosten 1202
14.15.11   Berechnung der entstandenen Kosten pro Kunde 1202
14.15.12   Prognose der Kosten – Herstellkosten, Boni und Rabatte 1203
14.15.13   Erfassung sämtlicher anderer Kostenarten 1204
14.15.14   Bestimmungsgrößen des Referenzwertes 1204
14.15.15   Der Referenzindex in der Beispieldatei 1206
14.15.16   Dokumentation der Bewertungsergebnisse 1207
14.15.17   Der Bindungsindex in der Beispieldatei 1208
14.16   Kundenscoring 1210
14.17   Personalstrukturanalyse 1213
14.17.1   Auswertung der Altersstruktur 1216
14.17.2   Auswertung nach Alter und Geschlecht 1217
14.17.3   Altersstruktur im Diagramm darstellen 1219
14.17.4   Auswertung der Betriebszugehörigkeit 1221
14.18   Arbeitszeitanalyse 1222
14.18.1   Festlegung der Konsolidierungsbereiche 1223
14.18.2   Erstellen des Soll-Ist-Vergleichs 1225
14.19   Reisekostenabrechnung 1227
14.19.1   Sperren von Zellen und Schutz des Tabellenblattes 1228
14.19.2   Druckbereich festlegen und überflüssige Spalten/Zeilen ausblenden 1229
14.19.3   Dateifenster konfigurieren und schützen 1230
14.20   Lieferantenbewertung 1233
14.20.1   Aufbau der Beispielanwendung 1234
14.20.2   Elemente des Eingabeformulars 1235
14.20.3   Erstellen der ActiveX-Kombinationsfelder 1236
14.20.4   Definition der Formular-Eingabefelder 1237
14.20.5   Erstellen der Formularsteuerelemente 1237
14.20.6   Struktur des Makros zum Erstellen der Excel-Liste 1239
14.20.7   Aufrufen des VBA-Editors 1241
14.20.8   Inhalt des VBA-Makros zum Erstellen der Excel-Liste 1242
14.20.9   Deklarieren einer Variablen 1244
14.20.10   Programmieren einer Schleife zur Suche der nächsten Leerzeile 1244
14.20.11   Überprüfung einer Bedingung 1245
14.20.12   Anhängen der Daten an die Excel-Liste 1246
14.20.13   Leeren der Zellen im Tabellenblatt »Zusammenfassung« 1247
14.20.14   Lieferantenbewertung – Zwischenrechnung 1248
14.20.15   Durchschnittliche Bewertung der Lieferanten 1248
14.20.16   Bildung der Rangfolge 1249
14.20.17   Automatische Sortierung der Daten 1250
14.20.18   Grafische Darstellung der Lieferantenbewertung 1251
15   Unternehmenssteuerung und Kennzahlen 1253
15.1   Zielkostenmanagement (Target Costing) 1255
15.1.1   Ausgangslage der Zielkostenberechnung 1256
15.1.2   Bestimmung der Zielkosten 1258
15.1.3   Analyse der Kostenstruktur und Identifizierung der Kostenlücke 1260
15.1.4   Bestimmung der Ziellücke 1261
15.1.5   Schema für die Anpassung der Kostenstruktur 1261
15.1.6   Ermittlung der Kundenpräferenzen 1262
15.1.7   Bildung des Zielkostenindex 1263
15.1.8   Umsetzung der Kostenstrukturanpassung in Excel 1263
15.1.9   Berechnung der Einsparpotenziale 1265
15.1.10   Tabellenaufbau und Navigation durch die Tabellenabschnitte 1266
15.2   Cashflow 1269
15.2.1   Beispieldateien und Datenmodelle 1269
15.2.2   Direkte Ermittlung des Cashflows 1271
15.2.3   Indirekte Ermittlung des Cashflows 1272
15.3   Free Cashflow 1274
15.4   Discounted Cashflow 1275
15.5   Gewichtete durchschnittliche Gesamtkapitalkosten nach Steuern 1277
15.6   Shareholder-Value 1279
15.6.1   Free Cashflows und Residualwert 1280
15.6.2   Barwerte der Free Cashflows berechnen 1282
15.6.3   Berechnung des Residualwertes 1283
15.6.4   Abschließende Bildung des Shareholder-Values 1284
15.7   Economic Value Added – EVA® 1286
15.7.1   Aufbau der Beispieldatei 1286
15.7.2   Berechnung NOPAT 1287
15.7.3   Berechnung der Net Operating Assets 1287
15.7.4   Berechnung der Gesamtkapitalkosten und des EVA® 1288
15.7.5   Allgemeine Informationen zum EVA® 1288
15.8   Market Value Added – MVA 1290
15.8.1   Aufbau der Beispieldatei 1290
15.8.2   Unternehmenswert berechnen 1291
15.9   Bilanzkennzahlen 1292
15.9.1   Gliederungsschema der Bilanz nach HGB 1292
15.9.2   Internationalisierung der Rechnungslegung 1293
15.9.3   Vorgaben zur Bilanzerstellung nach IAS/IFRS 1294
15.9.4   Kennzahlennavigator 1296
15.9.5   Übersicht und Interpretation von Vermögens- und Liquiditätskennzahlen 1298
15.10   GuV-Gliederung 1304
15.10.1   Gesamtkosten- und Umsatzkostenverfahren nach HGB 1304
15.10.2   Kennzahlen zu Rentabilität und Kapitalstruktur 1307
15.11   Beispieldatei GuV – Bilanz – Kapitalfluss 1311
15.11.1   Mehrjährige GuV-Analyse 1311
15.11.2   Erfassung und Berechnung der Bilanzdaten im Fünfjahresvergleich 1312
15.11.3   Berechnung des Cashflows aus GuV- und Bilanzdaten 1314
15.12   Return on Investment und DuPont-Schema 1317
15.12.1   Einzelschritte bei der ROI-Berechnung 1317
15.12.2   Interpretation der Ergebnisse des DuPont-Schemas 1319
15.12.3   Fazit 1320
15.13   Messung der Mitarbeiterzufriedenheit 1322
15.13.1   Ablauf von Befragungen zur Mitarbeiterzufriedenheit 1323
15.13.2   Aufbau eines Fragebogens 1324
15.13.3   Vermeidung der Mehrfachbeantwortung einer Frage 1325
15.13.4   Definition einer Fehlermeldung 1327
15.13.5   Übertragung der Datenüberprüfung auf die weiteren Fragen 1327
15.13.6   Festlegung und Automatisierung des Auswertungsablaufs 1329
15.13.7   Speichern des ausgefüllten Fragebogens unter einem eindeutigen Dateinamen 1329
15.13.8   Zuordnung einer Schaltfläche zum VBA-Makro 1331
15.13.9   Aufbau der Auswertungstabelle der Fragebogendatei 1332
15.13.10   Verbergen des Tabellenblattes zur Auswertung der Antworten 1333
15.13.11   Automatisierte Auswertung der Fragebögen 1334
15.13.12   Aufbau der Beispieldatei 1335
15.13.13   Kurzbeschreibung des VBA-Makros zum Datenimport 1336
15.13.14   Quelltext des VBA-Makros zum Datenimport 1337
15.13.15   Makro – Teil 1: Definition der Arbeitsumgebung 1338
15.13.16   Makro – Teil 2: Öffnen der Antwortdateien durch eine Schleife 1339
15.13.17   Makro – Teil 3: Ermitteln der nächsten freien Spalte 1341
15.13.18   Makro – Teil 4: Kopieren und Einfügen der Antwortdaten 1341
15.13.19   Makro – Teil 5: Schließen der Antwortdatei/Wiederholen der Prozedur 1342
15.13.20   Namensdefinition für die Auswertung der importierten Daten 1343
15.13.21   Auswertung der Fragebögen 1344
15.14   Selbstbewertung nach EFQM 1347
15.14.1   Übersicht über die neun Kriterien des EFQM-Modells 1348
15.14.2   Erstellen der Kriterienübersicht als Schaubild 1349
15.14.3   Kopieren und Anpassen der AutoForm-Vorlage 1349
15.14.4   Beschriftung der AutoFormen 1350
15.14.5   Formular zur Bestimmung von Erfüllungsgrad und Handlungsbedarf 1352
15.14.6   Berechnung der Ergebnisse der Selbst- und Fremdbewertung 1354
15.14.7   Bestimmung des Handlungskoeffizienten 1356
15.14.8   Bestandteile und Aufbau des EFQM-Cockpits 1357
15.14.9   Vergleich von Erfüllungsgrad und Handlungsbedarf im Netzdiagramm 1358
15.14.10   Interpretationen der Datendarstellung im Netzdiagramm 1359
15.14.11   Diagramme des Cockpits 1361
15.14.12   Performancedarstellung mit Bullet Graphs 1362
15.14.13   Balkendiagramm zur Darstellung des Handlungsbedarfs 1364
15.14.14   Ampeldarstellung für die Handlungskoeffizienten 1365
15.14.15   Schützen der Cockpit- und Fragebogeninhalte 1367
15.14.16   Weitere Kennzahlen im EFQM-Cockpit 1368
16   Reporting mit Diagrammen und Tabellen 1369
16.1   Grundlagen 1370
16.1.1   Zu viel und doch zu wenig? 1370
16.1.2   Mut zur Lücke! Aber was kann man weglassen? 1371
16.1.3   Was Sie stattdessen wissen und nutzen sollten 1372
16.2   Das Standarddiagramm in Excel 1374
16.2.1   Diagrammerstellung über das Menüband 1375
16.2.2   Bestimmen der Datenreihen und Beschriftungen 1379
16.2.3   Zwei Vorgehensweisen – ein Ziel: Änderung von Elementeigenschaften 1379
16.3   Wichtige Gestaltungsregeln 1382
16.4   Umgang mit Farben 1387
16.5   Auswahl des richtigen Diagrammtyps 1390
16.5.1   Vergleich von Werten und Darstellung von Rangfolgen – Balkendiagramm und Säulendiagramm 1390
16.5.2   Vergleich mehrerer Datenreihen und des Gesamtergebnisses – Stapelsäulen 1391
16.5.3   Wertevergleich bei mehr als einer Größenachse – Netzdiagramm 1395
16.5.4   Entwicklung von Werten in Zeitreihen – Liniendiagramm 1396
16.5.5   Darstellung der Anteile an einem Gesamtergebnis – Balken- oder Säulendiagramm 1398
16.5.6   Darstellung von Abweichungen – Säulendiagramm oder Liniendiagramm 1400
16.5.7   Darstellung der Korrelation zwischen Werten – Punktdiagramm 1401
16.5.8   Trendlinie und Bestimmtheitsmaß im Punktdiagramm 1403
16.5.9   Aufnahme einer dritten Koordinate – Blasendiagramm 1406
16.5.10   Darstellung von Datenverteilungen 1408
16.5.11   Darstellung des Verlaufs von Aktienkursen oder Rohstoffpreisen – Kursdiagramm 1409
16.5.12   Verbunddiagramme 1411
16.6   Die neuen Diagrammtypen seit Excel 2016 1413
16.6.1   Wasserfalldiagramm 1413
16.6.2   Trichter- oder Funneldiagramm 1416
16.6.3   Histogramm 1418
16.6.4   Sunburst-Diagramm 1422
16.6.5   Treemap-Diagramm 1426
16.6.6   Kastendiagramm 1429
16.7   Allgemeine Formatierungsregeln 1432
16.7.1   Verwendung und Funktionsweise der Designfarben 1432
16.7.2   Erstellen eigener Designfarben 1433
16.8   Elemente und Gestaltungsregeln für Dashboards 1435
16.9   Infografiken seit Excel 2016 1439
16.10   Piktogramme und Fotos 1442
16.11   3D-Karten 1447
16.12   Power View 1450
16.13   Kombinationen aus Tabellen und Diagramm erstellen 1454
16.14   Dynamische Diagramme 1457
16.14.1   Verwendung von individuellen Bereichsnamen in Diagrammen 1458
16.14.2   Berechnung des dynamischen Bereichs für die Summenbildung 1459
16.14.3   Berechnung des dynamischen Bereichs für das Diagramm 1460
16.14.4   Einfügen des Bereichsnamens in das Diagramm 1462
16.15   Spezielle Diagrammtypen 1464
16.15.1   Tachometerdiagramm mit Ampeldarstellung und Werteskala 1464
16.15.2   Thermometerdiagramm 1471
16.15.3   Wasserfalldiagramm 1472
16.15.4   Tornadodiagramm 1474
16.15.5   Gantt-Diagramm 1477
16.16   Spezielle Formatierungen im Diagramm 1481
16.16.1   Werteabhängige Formatierung: Kennzeichnung von Maximal- und Minimalwert 1481
16.16.2   Bedingte Formatierung von Datenpunkten 1489
16.17   Diagramme in Tabellenblättern 1495
16.17.1   Erstellen einer Heatmap 1496
16.17.2   Textfunktionen und grafische Tabellendarstellung 1502
16.17.3   Nutzung von Sparklines 1506
16.18   Dashboards erstellen 1516
16.18.1   Verwendung von Sparklines in Dashboards 1519
16.18.2   Darstellung geografischer Daten in Dashboards 1521
16.18.3   Verwendung von Ringdiagrammen in Dashboards 1525
16.19   Übernahme in PowerPoint 1529
16.19.1   Erstellen von Tabellen und Diagrammen in PowerPoint 1529
16.19.2   Verwenden einer Tabelle oder eines Diagramms als Verknüpfung 1530
16.19.3   Bearbeitung von Verknüpfungen in PowerPoint 1532
16.19.4   Einbetten eines Excel-Objekts in PowerPoint 1534
16.19.5   Verwendung von Designfarben in PowerPoint 1535
16.20   Übernahme in Word 1541
17   Automatisierung mit Makros – VBA für Controller 1542
17.1   Wie alles anfängt – die Aufzeichnung eines Makros 1545
17.1.1   Testen des aufgezeichneten Makros 1548
17.1.2   Ein Blick hinter die Kulissen – Ihr Makro im Makro-Editor 1549
17.1.3   Struktur des aufgezeichneten Makros 1550
17.1.4   Quelltext des aufgezeichneten Makros – Objekt, Methode, Eigenschaft 1551
17.1.5   Weitere Informationen und Hilfen im Makro-Editor nutzen 1552
17.1.6   Makro im Editor überarbeiten 1555
17.1.7   Testen des überarbeiteten Makros 1556
17.2   Makros über Schaltflächen aufrufen 1558
17.2.1   Alternativen zum Aufruf von Makros über Schaltflächen 1560
17.2.2   Zugriff über die Symbolleiste für den Schnellzugriff 1561
17.2.3   Zugriff über eine Funktionsgruppe im Menüband 1562
17.3   Quellcode im Editor bereinigen 1564
17.3.1   Zusammenfassung mit »With … End With« 1564
17.3.2   Entfernen von Standardwerten 1565
17.3.3   Kopieren und Verschieben auf direktem Weg 1568
17.4   Bereiche adressieren 1570
17.4.1   Markieren von Zellen über »Range« und »Cells« 1571
17.4.2   Auswählen von Zellen in anderen Tabellenblättern 1572
17.4.3   Den aktiven Bereich markieren 1573
17.4.4   »ActiveCell« und »Offset« zum Markieren nutzen 1573
17.4.5   Verwendung von Bereichsnamen 1574
17.5   Arbeiten mit Variablen 1576
17.5.1   Deklaration von Variablen 1576
17.5.2   Verwendung einer Variablen zur Suche nach der ersten leeren Zeile 1579
17.5.3   Eine weitere Variable zum Suchen nach der ersten leeren Spalte 1580
17.5.4   Verwenden der »SpecialCells«-Methode 1580
17.6   Umgang mit Programmfehlern 1583
17.6.1   Debugging-Modus 1583
17.6.2   Nutzung von Haltepunkten 1584
17.6.3   Testen des Makros im Einzelschrittmodus 1585
17.6.4   Nutzung des Direktfensters 1586
17.7   Kopieren, Verschieben und Filtern von Daten 1588
17.7.1   Aufzeichnung eines Kopiervorgangs 1588
17.7.2   Daten per Makro bestehenden Datenbeständen anhängen 1590
17.7.3   Deklaration der Variablen 1591
17.7.4   Mit den Variablen auf Objekte verweisen 1591
17.7.5   Variablen mit einem berechneten Wert füllen 1592
17.7.6   Verkürzung der Anweisung zum Kopieren 1592
17.7.7   Verwendung des Variablenwertes als Zellbezug des Kopiervorgangs 1593
17.7.8   Verwendung von dynamischen Bereichen statt Variablen 1594
17.7.9   Daten einer Tabelle anhängen 1595
17.7.10   Ermittlung der Größe von Quell- und Zieldatenbereich 1596
17.7.11   Ausschneiden der aktuellen Daten – Anhängen an die vorhandenen Daten 1597
17.7.12   Anwendung des erweiterten Filters in einem Makro 1598
17.7.13   Deklaration der Variablen für das erweiterte Filtern 1600
17.7.14   Bestimmung der Tabellengröße des Listenbereichs 1601
17.7.15   Erstellen des Kriterienbereichs und Zuweisen des Bereichs zu einer Variablen 1601
17.7.16   Flexible Erweiterung des Kriterienbereichs 1602
17.7.17   Erstellen des weiteren Bereichs und Variablenzuweisungen 1602
17.7.18   Durchführung des erweiterten Filtervorgangs 1603
17.7.19   Testen des Makros 1604
17.7.20   Fazit zum Thema Kopieren, Verschieben und Filtern 1604
17.8   Zugriff auf Dateien über VBA-Makros 1607
17.8.1   Auswählen einer Datei über den Datei-öffnen-Dialog 1608
17.8.2   Öffnen einer Datei aus Excel heraus 1609
17.8.3   Anpassung des Codevorschlags aus der VBA-Hilfe 1611
17.8.4   Die »If«-Anweisung beim Öffnen der Datei 1612
17.8.5   Öffnen von beliebigen Dateitypen aus einer Arbeitsmappe heraus 1612
17.8.6   Angabe der Lokalisierungswerte 1614
17.8.7   Einfügen einer CSV-Datei in eine geöffnete Arbeitsmappe 1615
17.8.8   Quelltext des Makros zum Einfügen von CSV-Dateien 1615
17.9   Fallbeispiel: CSV-Import und Datenaktualisierung für einen Forecast 1619
17.9.1   Importieren und Anhängen der aktuellen Daten 1620
17.9.2   Betrachten des aktuellen Reports 1622
17.10   Flusskontrolle mit »If … Then … Else« 1623
17.10.1   Fettdruck und Farbe für Summenzeilen mit »If … Then … End If« 1623
17.10.2   Adressierung der Zellbereiche in diesem Makro 1625
17.10.3   »Else«-Anweisung im »If … Then« 1626
17.10.4   »Select Case« als Lösung für Mehrfachbedingungen 1627
17.10.5   »Select Case« am Beispiel einer bedingten Formatierung 1628
17.10.6   Verwendung von »Case Else« 1629
17.11   Programmierung von Schleifen in VBA 1632
17.11.1   Erstellen einer »For … Next«-Schleife 1632
17.11.2   Definition des Zählers 1633
17.11.3   Verlassen der Schleife und Ausführen einer Anweisung 1634
17.11.4   Verwendung anderer Variablenbezeichnungen im Zähler 1635
17.11.5   Exkurs: Leere Zeilen ohne Schleifen finden und löschen 1635
17.11.6   Praxisbeispiel: Kostenstellendaten auf verschiedene Tabellenblätter verteilen 1636
17.11.7   Voraussetzungen in dieser Beispieldatei 1637
17.11.8   Deklaration der Variablen 1638
17.11.9   Zuweisung der Objekte zu den Variablen 1638
17.11.10   Festlegung des Zählerwertes und Beginn der Schleife 1639
17.11.11   Bestimmung der einzelnen Kostenstellen als Filterkriterium 1639
17.11.12   Schleifen mit Objektvariablen und »For Each … In … Next« 1642
17.11.13   Schrift- und Hintergrundfarben mit »For Each … In … Next« zählen 1642
17.11.14   Erzeugen einer Uploaddatei für Fremdsysteme mit »Do Until … Loop« 1644
17.11.15   Beschreibung der Kopieranweisungen im »Do Until«-Block 1645
17.11.16   Definition der Bedingung für die Ausführung von »Do Until … Loop« 1647
17.11.17   Schleifen mit »Do While … Loop« 1648
17.12   Formeln und Funktionen in VBA-Makros 1650
17.12.1   Grundzüge der Z1S1-Adressierung im Tabellenblatt 1651
17.12.2   Übertragen der Z1S1-Methode auf den Quelltext des Makros 1652
17.12.3   Definition von Formeln im Quelltext eines Makros 1652
17.12.4   Kopieren von Formeln und Funktionen in VBA 1654
17.12.5   Definition der Formeln und Funktionen nach der R1C1-Methode 1655
17.13   Gestaltung von Dialogen in VBA 1657
17.13.1   Inputbox und Messagebox 1657
17.13.2   Ausgabe von Werten in der aktiven Zelle 1660
17.13.3   Ausgabe von Werten in einer vordefinierten Zelle 1661
17.13.4   Entwurf und Nutzung von Formularen 1662
17.13.5   Bausteine für eine formulargesteuerte Dateneingabe 1663
17.13.6   Erstellen eines Formulars im VB-Editor 1663
17.13.7   Starten des Formulars mit einer Schaltfläche und einem Makro 1666
17.13.8   Anweisung zum Schließen des Formulars zuweisen 1667
17.13.9   Schreiben der Formularfeldinhalte in das Tabellenblatt 1668
17.13.10   Übernahme der vorhandenen Werte aus der Tabelle in das Formular 1669
17.13.11   Schließen des Formulars durch den Benutzer verhindern 1670
17.14   Benutzerdefinierte Funktionen 1672
17.14.1   Definition einer benutzerdefinierten Funktion 1672
17.14.2   Aufrufen einer benutzerdefinierten Funktion 1674
17.14.3   KW nach ISO 8601 – Nutzung einer VBA-Funktion als benutzerdefinierte Funktion 1676
17.14.4   Die VBA-Funktion »DatePart« 1677
17.14.5   Berechnung der KW nach ISO 8601 1678
17.14.6   Benutzerdefinierte Funktionen mit mehreren Argumenten 1679
17.14.7   Das Argument zur Bestimmung des Farbcodes 1681
17.14.8   Zellen mit farblicher Gestaltung zählen 1681
17.14.9   Gewichtete durchschnittliche Kapitalkosten als benutzerdefinierte Funktion 1682
17.15   Die Beispiele aus dem Buch zum Herunterladen 1684
Stichwortverzeichnis 1685
Rechtliche Hinweise 1768
Über den Autor 1769