Excel im Controlling

Stephan Nelles zählt zu den führenden Experten, wenn es um den Einsatz von Excel im betriebswirtschaftlichen Umfeld geht. Seit mehr als zehn Jahren ist er als Berater für Unternehmen und Behörden tätig. Er entwirft und implementiert Lösungen mit Microsoft Office und schult Mitarbeiter und Anwender. Eines seiner Spezialgebiete ist der Einsatz von Excel für das Reporting und Controlling. In seinen Büchern und Video-Trainings liefert er konkrete Lösungen aus seiner Beratungs- und Schulungspraxis.

Vorwort ... 29


1. Neuerungen in Excel ... 33


2. Tipps, Tricks und Tastenkürzel -- zeitsparende Techniken für Controller ... 43


2.1 ... Daten effizient eingeben ... 43

2.2 ... Kopieren, Ausschneiden und Einfügen von Daten ... 48

2.3 ... Formelzusammenhänge erkennen ... 51

2.4 ... Cursorsteuerung und Bewegen in Tabellen ... 53

2.5 ... Zellbereiche markieren ... 55

2.6 ... Zahlen- und andere Formate schnell zuweisen ... 56

2.7 ... Inhalte löschen ... 57

2.8 ... Diagramme erstellen und bearbeiten ... 57

2.9 ... AutoFilter und Bearbeitung von sichtbaren Zellen ... 58

2.10 ... Erweiterte Filterfunktionen in Microsoft 365 ... 59

2.11 ... Weitere nützliche Tastenkombinationen ... 61

2.12 ... Tabellenansichten in der Webversion von Excel nutzen ... 62

2.13 ... Kommentare, Notizen und Chat in Excel ... 63



3. xlSMILE -- Excel-Lösungen mit System ... 67


3.1 ... Simplify -- Big Data nutzen und Datenmüll entfernen ... 67

3.2 ... Model -- systematisch arbeiten und Reports automatisieren ... 68

3.3 ... Integrate -- Layouts entwickeln und Tabellen und Diagramme anwenden ... 69

3.4 ... Lead -- Benutzer führen und Fehleingaben verhindern ... 70

3.5 ... Explain -- informieren und zusammenfassen ... 71



4. Daten importieren und bereinigen ... 73


4.1 ... Textkonvertierungs-Assistent ... 75

4.2 ... Transaktionsdaten in einer CSV-Datei auswerten ... 84

4.3 ... Importieren von externen Daten mit Power Pivot ... 96



5. Datenbereinigung mit Power Query effizienter gestalten ... 99


5.1 ... Wozu ist Power Query eigentlich gedacht? ... 99

5.2 ... CSV-Dateien mit Power Query importieren ... 100

5.3 ... Einfache Schritte der Datenbereinigung ausführen ... 105

5.4 ... Verbindungen zu anderen Datenquellen ... 110

5.5 ... Gruppieren und Spalten berechnen ... 122

5.6 ... Bedingte Berechnungen in Spalten ... 126

5.7 ... Power Query als Ersatz für Textfunktionen in Excel ... 128

5.8 ... Weitere Optionen beim Erstellen von Spalten und Gruppierungsmerkmalen ... 133

5.9 ... Suchtabellen durch Anfügen von Abfragen erzeugen ... 138

5.10 ... Alle Excel-Dateien eines Ordners importieren und bereinigen ... 144

5.11 ... Alltäglicher Datensalat -- Power-Query-Lösungen für den Alltag ... 148

5.12 ... Individuelle Datentypen in Power Query erstellen ... 163



6. Unternehmensdaten prüfen und analysieren ... 181


6.1 ... Standardsortierung und benutzerdefiniertes Sortieren ... 181

6.2 ... AutoFilter und Datenschnitte ... 186

6.3 ... Vorteile des erweiterten Filters ... 191

6.4 ... Erweiterter Filter mit einem VBA-Makro ... 200

6.5 ... Verwendung von Datenbankfunktionen ... 203

6.6 ... Konsolidierung von Daten ... 216



7. Dynamische Reports erstellen ... 231


7.1 ... Das 5-Minuten-Datenmodell ... 231

7.2 ... Bestandteile eines Datenmodells ... 233

7.3 ... Datenmodell für einen Forecast erstellen ... 239

7.4 ... Datenmodell zur Kalkulation der optimalen Bestellmenge ... 266

7.5 ... Rollierende Berichte ... 271



8. Wichtige Kalkulationsfunktionen für Controller ... 277


8.1 ... Berechnungen mit Datumsbezug ... 278

8.2 ... Berechnungen mit Zeitangaben ... 293

8.3 ... Arbeiten mit Verweisen und Matrizen ... 296

8.4 ... Funktionen zur Dynamisierung von Tabellen ... 314

8.5 ... Berechnung von Rangfolgen ... 330

8.6 ... Berechnung von Mittelwerten ... 337

8.7 ... Runden von Daten ... 341

8.8 ... Textfunktionen zur Bereinigung und Strukturierung von Daten ... 345

8.9 ... Fehlerunterdrückung ... 351

8.10 ... Einsatz von logischen Funktionen ... 357

8.11 ... Berechnungen mit »LET()« erstellen -- Funktionsweise und Nutzen ... 361

8.12 ... Wie viel VBA benötigen Controller nach der Einführung von »LAMBDA()« noch? ... 372



9. Neue dynamische Matrixfunktionen in Excel für Microsoft 365 ... 387


9.1 ... Das Control-Shift-Enter-Beben ... 388

9.2 ... Neue Optionen für die Erstellung dynamischer Datenmodelle ... 414



10. Bedingte Kalkulationen in Datenanalysen ... 423


10.1 ... Kalkulationen ohne Bedingungen ... 424

10.2 ... Kalkulationen mit einer Bedingung ... 426

10.3 ... Bereichsnamen -- der schnelle Zugriff auf Datenbereiche ... 429

10.4 ... Fehlervermeidung bei der Eingabe von Bedingungen -- die Datenüberprüfung ... 435

10.5 ... Bedingte Kalkulationen mit mehr als einer Bedingung ... 439

10.6 ... Vorteile von »SUMMENPRODUKT()« gegenüber anderen Funktionen zur bedingten Kalkulation ... 447

10.7 ... Multiplikation von Textwerten mit »SUMMENPRODUKT()« ... 448

10.8 ... Bedingte Kalkulation mit ODER im Tabellenblatt »Report_III« ... 449

10.9 ... Ausschluss von Datensätzen bei bedingten Kalkulationen ... 449

10.10 ... Häufigkeiten schnell berechnen ... 451

10.11 ... Mittelwerte ohne Nullwerte berechnen ... 453

10.12 ... Mittelwert bei #DIV/0! ... 454

10.13 ... Fallbeispiel zur bedingten Kalkulation ... 456



11. Pivottabellen und -diagramme ... 461


11.1 ... Vorbereitung der Basisdaten für eine Pivottabelle ... 462

11.2 ... Pivottabellen erstellen ... 466

11.3 ... Pivotcache und Speicherbedarf ... 484

11.4 ... Visuelle interaktive Analyse von Daten ... 485

11.5 ... Zeitbezogene Auswertungen von Pivottabellen mit Zeitachsen ... 493

11.6 ... Filtern von Daten in einer Pivottabelle ... 495

11.7 ... Gruppierungen in Pivottabellen ... 496

11.8 ... Weiterverarbeitung von Daten aus Pivottabellen ... 520

11.9 ... Personaldaten mithilfe von Pivottabellen konsolidieren ... 527

11.10 ... Grundlegendes zu PivotCharts ... 535

11.11 ... Zusammenfassung: Pivottabellen und PivotCharts ... 546



12. Business Intelligence mit Power Pivot ... 549


12.1 ... Arbeiten auf der Self-BI-Baustelle ... 550

12.2 ... Inhaltliches und Organisatorisches zu den Beispielen ... 551

12.3 ... Die Power-Pivot-Oberfläche im Überblick ... 552

12.4 ... Logische Beziehungen statt »SVERWEIS()« und Co. ... 557

12.5 ... Berechnete Spalten und berechnete Felder unterscheiden ... 560

12.6 ... Eine berechnete Spalte erstellen ... 561

12.7 ... Eine Power-Pivot-Tabelle in Excel erstellen ... 563

12.8 ... Mehr Übersichtlichkeit herstellen ... 565

12.9 ... Referenztabellen einbinden ... 567

12.10 ... Einbinden von Daten aus anderen Datenquellen ... 570

12.11 ... Typische Erkennungszeichen für fehlende logische Beziehungen in Datenmodellen ... 572

12.12 ... Tabellen der Arbeitsmappe in das Datenmodell einbinden ... 575

12.13 ... Tabellen des Datenmodells ausblenden ... 578

12.14 ... Berechnete Felder in Power-Pivot-Tabellen verwenden ... 580

12.15 ... Bearbeiten von Measures ... 585

12.16 ... Bedingte Kalkulationen mit »CALCULATE()« ... 587

12.17 ... Datenschnitte und Zeitachsen ... 591

12.18 ... Wie DAX-Funktionen arbeiten ... 596

12.19 ... Verwendung von Zeitintelligenz-Funktionen in einem Datenmodell ... 603

12.20 ... Vorjahresvergleiche mit »SAMEPERIODLASTYEAR()« erstellen ... 606

12.21 ... Individuelle Zeitintervalle mit »DATEADD()« berechnen ... 607

12.22 ... Bedingte Formatierungen und Diagramme in Power-Pivot-Reports ... 609



13. Excel als Planungswerkzeug ... 613


13.1 ... Wettbewerberanalyse ... 613

13.2 ... Potenzialanalyse ... 620

13.3 ... Portfolioanalyse ... 625

13.4 ... Stärken-Schwächen-Analyse ... 630

13.5 ... Absatzplanung ... 636

13.6 ... Prognosen erstellen ... 654

13.7 ... Personalplanung ... 661

13.8 ... Liquiditätsplanung ... 678

13.9 ... Marktanalyse und Absatzplanung ... 685



14. Operatives Controlling mit Excel ... 693


14.1 ... Betriebsabrechnungsbogen ... 693

14.2 ... Divisionskalkulation ... 703

14.3 ... Zuschlagskalkulation ... 706

14.4 ... Äquivalenzziffernrechnung ... 709

14.5 ... Prozesskostenrechnung ... 711

14.6 ... Deckungsbeitragsrechnung ... 715

14.7 ... Dynamische Break-even-Analyse ... 716

14.8 ... Mehrstufige Deckungsbeitragsrechnung ... 725

14.9 ... Planen von Kosten und Erlösen mithilfe von Szenarien ... 726

14.10 ... Produktkalkulation mit Deckungsbeitragsrechnung ... 731

14.11 ... Eigenfertigung oder Fremdbezug (make or buy) ... 745

14.12 ... Zinsen, Tilgung, Annuitäten für Darlehen berechnen ... 748

14.13 ... Abschreibungen ... 753

14.14 ... Methoden der Investitionsrechnung ... 756

14.15 ... Customer Lifetime Value ... 770

14.16 ... Kundenscoring ... 784

14.17 ... Personalstrukturanalyse ... 786

14.18 ... Arbeitszeitanalyse ... 792

14.19 ... Reisekostenabrechnung ... 795

14.20 ... Lieferantenbewertung ... 799



15. Unternehmenssteuerung und Kennzahlen ... 813


15.1 ... Zielkostenmanagement (Target Costing) ... 814

15.2 ... Cashflow ... 824

15.3 ... Free Cashflow ... 827

15.4 ... Discounted Cashflow ... 828

15.5 ... Gewichtete durchschnittliche Gesamtkapitalkosten nach Steuern ... 829

15.6 ... Shareholder-Value ... 830

15.7 ... Economic Value Added -- EVA® ... 835

15.8 ... Market Value Added -- MVA ... 837

15.9 ... Bilanzkennzahlen ... 838

15.10 ... GuV-Gliederung ... 847

15.11 ... Beispieldatei GuV -- Bilanz -- Kapitalfluss ... 852

15.12 ... Return on Investment und DuPont-Schema ... 856

15.13 ... Messung der Mitarbeiterzufriedenheit ... 859

15.14 ... Selbstbewertung nach EFQM ... 875



16. Reporting mit Diagrammen und Tabellen ... 891


16.1 ... Grundlagen ... 891

16.2 ... Das Standarddiagramm in Excel ... 893

16.3 ... Wichtige Gestaltungsregeln ... 898

16.4 ... Umgang mit Farben ... 902

16.5 ... Auswahl des richtigen Diagrammtyps ... 903

16.6 ... Die neuen Diagrammtypen seit Excel 2016 ... 921

16.7 ... Allgemeine Formatierungsregeln ... 935

16.8 ... Elemente und Gestaltungsregeln für Dashboards ... 936

16.9 ... Infografiken seit Excel 2016 ... 938

16.10 ... Piktogramme und Fotos ... 939

16.11 ... 3D-Karten ... 943

16.12 ... Power View ... 944

16.13 ... Kombinationen aus Tabellen und Diagramm erstellen ... 947

16.14 ... Dynamische Diagramme ... 949

16.15 ... Spezielle Diagrammtypen ... 954

16.16 ... Spezielle Formatierungen im Diagramm ... 966

16.17 ... Diagramme in Tabellenblättern ... 974

16.18 ... Dashboards erstellen ... 988

16.19 ... Übernahme in PowerPoint ... 996

16.20 ... Übernahme in Word ... 1004



17. Automatisierung mit Makros -- VBA für Controller ... 1005


17.1 ... Wie alles anfängt -- die Aufzeichnung eines Makros ... 1007

17.2 ... Makros über Schaltflächen aufrufen ... 1015

17.3 ... Quellcode im Editor bereinigen ... 1019

17.4 ... Bereiche adressieren ... 1023

17.5 ... Arbeiten mit Variablen ... 1027

17.6 ... Umgang mit Programmfehlern ... 1031

17.7 ... Kopieren, Verschieben und Filtern von Daten ... 1034

17.8 ... Zugriff auf Dateien über VBA-Makros ... 1047

17.9 ... Fallbeispiel: CSV-Import und Datenaktualisierung für einen Forecast ... 1054

17.10 ... Flusskontrolle mit »If ... Then ... Else« ... 1057

17.11 ... Programmierung von Schleifen in VBA ... 1063

17.12 ... Formeln und Funktionen in VBA-Makros ... 1075

17.13 ... Gestaltung von Dialogen in VBA ... 1079

17.14 ... Benutzerdefinierte Funktionen ... 1088

17.15 ... Die Beispiele aus dem Buch zum Herunterladen ... 1096



Index ... 1097