Excel 2016 im Controlling

Stephan Nelles ist IT-Berater und Dozent. Er hat seit 1987 Seminar-Erfahrung, unter anderem schult er zum Thema "Excel in kaufmännischen Anwendungen".

  Vorwort ... 29

  1.  Neuerungen in Excel 2016 ... 35
       1.1 ... Neue Funktionen in Pivottabellen ... 35
       1.2 ... Neue Diagrammtypen und der Power BI Desktop ... 36
       1.3 ... Daten in Dashboards und Karten visualisieren und teilen ... 38
       1.4 ... Neue Kalkulationsfunktionen ... 40
       1.5 ... Das Beste zum Schluss ... 41
       1.6 ... Was ist neu in diesem Buch? ... 42

  2.  Tipps, Tricks und Tastenkürzel -- zeitsparende Techniken für Controller ... 43
       2.1 ... Daten effizient eingeben ... 43
       2.2 ... Kopieren, Ausschneiden und Einfügen von Daten ... 48
       2.3 ... Formelzusammenhänge erkennen ... 50
       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 ... 58
       2.8 ... Diagramme erstellen und bearbeiten ... 59
       2.9 ... AutoFilter und Bearbeitung von sichtbaren Zellen ... 60
       2.10 ... Weitere nützliche Tastenkombinationen ... 61

  3.  xlSMILE -- Excel-Lösungen mit System ... 63
       3.1 ... Simplify -- Big Data nutzen und Datenmüll entfernen ... 63
       3.2 ... Model -- systematisch arbeiten und Reports automatisieren ... 64
       3.3 ... Integrate -- Layouts entwickeln und Tabellen und Diagramme anwenden ... 65
       3.4 ... Lead -- Benutzer führen und Fehleingaben verhindern ... 66
       3.5 ... Explain -- informieren und zusammenfassen ... 67

  4.  Daten importieren und bereinigen ... 69
       4.1 ... Textdatei aus einem Warenwirtschaftssystem importieren ... 70
       4.2 ... Transaktionsdaten in einer CSV-Datei auswerten ... 81
       4.3 ... Daten mit Microsoft Query importieren und SollIstVergleich durchführen ... 94
       4.4 ... Daten von einem SQL Server aus Excel abfragen ... 102
       4.5 ... Vorhandene Datenverbindungen nutzen ... 103
       4.6 ... OLAP-Cubes und Analysis Services ... 105
       4.7 ... Importieren von externen Daten mit PowerPivot ... 108
       4.8 ... Importieren von Webinhalten ... 110
       4.9 ... Importieren und Exportieren von XML-Daten ... 112
       4.10 ... Zusammenfassung: Datenimport und -bereinigung ... 114

  5.  Datenbereinigung mit PowerQuery effizienter gestalten ... 117
       5.1 ... Wozu PowerQuery eigentlich gedacht ist ... 117
       5.2 ... CSV-Dateien mit PowerQuery importieren ... 118
       5.3 ... Einfache Schritte der Datenbereinigung ausführen ... 123
       5.4 ... Gruppieren und Spalten berechnen ... 127
       5.5 ... Suchtabellen durch Anfügen von Abfragen erzeugen ... 131
       5.6 ... Alle Excel-Dateien eines Ordners importieren und bereinigen ... 135

  6.  Unternehmensdaten prüfen und analysieren ... 141
       6.1 ... Standardsortierung und benutzerdefiniertes Sortieren ... 141
       6.2 ... AutoFilter und Datenschnitte ... 146
       6.3 ... Vorteile des erweiterten Filters ... 151
       6.4 ... Erweiterter Filter mit einem VBA-Makro ... 161
       6.5 ... Verwendung von Datenbankfunktionen ... 164
       6.6 ... Konsolidierung von Daten ... 180
       6.7 ... Zusammenfassung: Basisanalyse ... 195

  7.  Dynamische Reports erstellen ... 197
       7.1 ... Das 5-Minuten-Datenmodell ... 197
       7.2 ... Bestandteile eines Datenmodells ... 199
       7.3 ... Datenmodell für einen Forecast erstellen ... 206
       7.4 ... Datenmodell zur Kalkulation der optimalen Bestellmenge ... 236
       7.5 ... Rollierende Berichte ... 243
       7.6 ... Zusammenfassung: Datenmodelle ... 249

  8.  Wichtige Kalkulationsfunktionen für Controller ... 251
       8.1 ... Berechnungen mit Datumsbezug ... 252
       8.2 ... Berechnungen mit Zeitangaben ... 268
       8.3 ... Arbeiten mit Verweisen und Matrizen ... 271
       8.4 ... Funktionen zur Dynamisierung von Tabellen ... 278
       8.5 ... Berechnung von Rangfolgen ... 298
       8.6 ... Berechnung von Mittelwerten ... 305
       8.7 ... Runden von Daten ... 310
       8.8 ... Neue Textfunktionen in Excel 2016 ... 313
       8.9 ... Fehlerunterdrückung ... 315
       8.10 ... Einsatz von logischen Funktionen ... 320

  9.  Bedingte Kalkulationen in Datenanalysen ... 325
       9.1 ... Kalkulationen ohne Bedingungen ... 326
       9.2 ... Kalkulationen mit einer Bedingung ... 329
       9.3 ... Bereichsnamen -- der schnelle Zugriff auf Datenbereiche ... 332
       9.4 ... Fehlervermeidung bei der Eingabe von Bedingungen -- die Datenüberprüfung ... 338
       9.5 ... Bedingte Kalkulationen mit mehr als einer Bedingung ... 343
       9.6 ... Vorteile von SUMMENPRODUKT() gegenüber anderen Funktionen zur bedingten Kalkulation ... 351
       9.7 ... Multiplikation von Textwerten mit SUMMENPRODUKT() ... 352
       9.8 ... Bedingte Kalkulation mit ODER im Tabellenblatt Report_III ... 353
       9.9 ... Ausschluss von Datensätzen bei bedingten Kalkulationen ... 354
       9.10 ... Häufigkeiten schnell berechnen ... 355
       9.11 ... Mittelwerte ohne Nullwerte berechnen ... 358
       9.12 ... Mittelwert bei #DIV/0! ... 359
       9.13 ... Fallbeispiel zur bedingten Kalkulation ... 361
       9.14 ... Zusammenfassung: Bedingte Kalkulationen ... 366

10.  Pivottabellen und -diagramme ... 369
       10.1 ... Vorbereitung der Basisdaten für eine Pivottabelle ... 370
       10.2 ... Pivottabellen mit Excel 2016 erstellen ... 374
       10.3 ... Pivotcache und Speicherbedarf ... 393
       10.4 ... Visuelle interaktive Analyse von Daten ... 395
       10.5 ... Zeitbezogene Auswertungen von Pivottabellen mit Zeitachsen ... 402
       10.6 ... Filtern von Daten in einer Pivottabelle ... 404
       10.7 ... Gruppierungen in Pivottabellen ... 405
       10.8 ... Weiterverarbeitung von Daten aus Pivottabellen ... 428
       10.9 ... Personaldaten mit Hilfe von Pivottabellen konsolidieren ... 436
       10.10 ... Grundlegendes zu PivotCharts ... 446
       10.11 ... Zusammenfassung: Pivottabellen und PivotCharts ... 457

11.  Business Intelligence mit PowerPivot ... 461
       11.1 ... Arbeiten auf der Self-BI-Baustelle ... 462
       11.2 ... Inhaltliches und Organisatorisches zu den Beispielen ... 463
       11.3 ... Die PowerPivot-Oberfläche im Überblick ... 464
       11.4 ... Logische Beziehungen statt SVERWEIS() und Co. ... 470
       11.5 ... Berechnete Spalten und berechnete Felder unterscheiden ... 473
       11.6 ... Eine berechnete Spalte erstellen ... 474
       11.7 ... Eine PowerPivot-Tabelle in Excel erstellen ... 476
       11.8 ... Mehr Übersichtlichkeit herstellen ... 478
       11.9 ... Referenztabellen einbinden ... 480
       11.10 ... Einbinden von Daten aus anderen Datenquellen ... 482
       11.11 ... Typische Erkennungszeichen für fehlende logische Beziehungen in Datenmodellen ... 485
       11.12 ... Tabellen der Arbeitsmappe in das Datenmodell einbinden ... 487
       11.13 ... Tabellen des Datenmodells ausblenden ... 491
       11.14 ... Berechnete Felder in PowerPivot-Tabellen verwenden ... 493
       11.15 ... Bearbeiten von berechneten Feldern ... 499
       11.16 ... Bedingte Kalkulationen mit CALCULATE() ... 500
       11.17 ... Datenschnitte und Zeitachsen ... 505
       11.18 ... Wie DAX-Funktionen arbeiten ... 510
       11.19 ... Bedingte Formatierungen und Diagramme in PowerPivot-Reports ... 518

12.  Excel als Planungswerkzeug ... 521
       12.1 ... Wettbewerberanalyse ... 521
       12.2 ... Potenzialanalyse ... 529
       12.3 ... Portfolioanalyse ... 534
       12.4 ... Stärken-Schwächen-Analyse ... 540
       12.5 ... Absatzplanung ... 545
       12.6 ... Prognosen erstellen ... 564
       12.7 ... Personalplanung ... 572
       12.8 ... Liquiditätsplanung ... 589
       12.9 ... Marktanalyse und Absatzplanung ... 597

13.  Operatives Controlling mit Excel ... 605
       13.1 ... Betriebsabrechnungsbogen ... 606
       13.2 ... Divisionskalkulation ... 617
       13.3 ... Zuschlagskalkulation ... 620
       13.4 ... Äquivalenzziffernrechnung ... 623
       13.5 ... Prozesskostenrechnung ... 626
       13.6 ... Deckungsbeitragsrechnung ... 630
       13.7 ... Dynamische Break-even-Analyse ... 632
       13.8 ... Mehrstufige Deckungsbeitragsrechnung ... 642
       13.9 ... Planen von Kosten und Erlösen mit Hilfe von Szenarien ... 643
       13.10 ... Produktkalkulation mit Deckungsbeitragsrechnung ... 648
       13.11 ... Eigenfertigung oder Fremdbezug (make or buy) ... 662
       13.12 ... Zinsen, Tilgung, Annuitäten für Darlehen berechnen ... 666
       13.13 ... Abschreibungen ... 672
       13.14 ... Methoden der Investitionsrechnung ... 674
       13.15 ... Customer Lifetime Value ... 690
       13.16 ... Kundenscoring ... 706
       13.17 ... Personalstrukturanalyse ... 709
       13.18 ... Arbeitszeitanalyse ... 715
       13.19 ... Reisekostenabrechnung ... 719
       13.20 ... Lieferantenbewertung ... 722

14.  Unternehmenssteuerung und Kennzahlen ... 739
       14.1 ... Zielkostenmanagement (Target Costing) ... 740
       14.2 ... Cashflow ... 750
       14.3 ... Free Cashflow ... 754
       14.4 ... Discounted Cashflow ... 755
       14.5 ... Gewichtete durchschnittliche Gesamtkapitalkosten nach Steuern ... 756
       14.6 ... Shareholder-Value ... 758
       14.7 ... Economic Value Added -- EVA® ... 763
       14.8 ... Market Value Added -- MVA ... 766
       14.9 ... Bilanzkennzahlen ... 767
       14.10 ... GuV-Gliederung ... 776
       14.11 ... Beispieldatei GuV -- Bilanz -- Kapitalfluss ... 781
       14.12 ... Return on Investment und DuPont-Schema ... 786
       14.13 ... Messung der Mitarbeiterzufriedenheit ... 789
       14.14 ... Selbstbewertung nach EFQM ... 808

15.  Reporting mit Diagrammen und Tabellen ... 825
       15.1 ... Grundlagen ... 825
       15.2 ... Das Standarddiagramm in Excel ... 827
       15.3 ... Wichtige Gestaltungsregeln ... 833
       15.4 ... Umgang mit Farben ... 836
       15.5 ... Auswahl des richtigen Diagrammtyps ... 838
       15.6 ... Die neuen Diagrammtypen in Excel 2016 ... 858
       15.7 ... Allgemeine Formatierungsregeln ... 872
       15.8 ... Elemente und Gestaltungsregeln für Dashboards ... 874
       15.9 ... Infografiken in Excel 2016 ... 876
       15.10 ... Power View ... 878
       15.11 ... 3D-Karten ... 881
       15.12 ... Kombinationen aus Tabellen und Diagramm erstellen ... 882
       15.13 ... Dynamische Diagramme ... 885
       15.14 ... Spezielle Diagrammtypen ... 890
       15.15 ... Spezielle Formatierungen im Diagramm ... 903
       15.16 ... Diagramme in Tabellenblättern ... 913
       15.17 ... Dashboards erstellen ... 928
       15.18 ... Übernahme in PowerPoint ... 934
       15.19 ... Übernahme in Word ... 942

16.  Automatisierung mit Makros -- VBA für Controller ... 943
       16.1 ... Wie alles anfängt: die Aufzeichnung eines Makros ... 944
       16.2 ... Makros über Schaltflächen aufrufen ... 954
       16.3 ... Quellcode im Editor bereinigen ... 958
       16.4 ... Bereiche adressieren ... 963
       16.5 ... Arbeiten mit Variablen ... 968
       16.6 ... Umgang mit Programmfehlern ... 973
       16.7 ... Kopieren, Verschieben und Filtern von Daten ... 976
       16.8 ... Zugriff auf Dateien über VBA-Makros ... 990
       16.9 ... Fallbeispiel: CSV-Import und Datenaktualisierung für einen Forecast ... 999
       16.10 ... Flusskontrolle mit »If ... Then ... Else« ... 1002
       16.11 ... Programmierung von Schleifen in VBA ... 1009
       16.12 ... Formeln und Funktionen in VBA-Makros ... 1022
       16.13 ... Gestaltung von Dialogen in VBA ... 1027
       16.14 ... Benutzerdefinierte Funktionen ... 1038
       16.15 ... Die Beispiele aus dem Buch zum Herunterladen ... 1047

  Stichwortverzeichnis ... 1049