Verbessern Sie Ihr Controlling mit Excel! Dieses umfassende Handbuch bietet Ihnen die richtigen Lösungen und Werkzeuge dazu. Stephan Nelles zeigt Ihnen die besten Methoden und Techniken, wie Sie Excel so effizient wie möglich im Controlling einsetzen können. Profitieren Sie von den erfolgreich erprobten Lösungen im Buch und rücken Sie Ihren Aufgaben im Controlling mit maßgeschneiderten Excel-Tools zu Leibe. Das Buch ist aktuell zu Excel 2019, eignet sich aber auch für alle älteren Versionen.

Inkl. Pivottabellen, Power Query, Power Pivot, VBA-Programmierung

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.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.2 ... Transaktionsdaten in einer CSV-Datei auswerten ... 79 4.3 ... Daten mit Microsoft Query importieren und Soll-Ist-Vergleich durchführen ... 90 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.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.8 ... Suchtabellen durch Anfügen von Abfragen erzeugen ... 139 5.9 ... Alle Excel-Dateien eines Ordners importieren und bereinigen ... 145 5.10 ... Alltäglicher Datensalat -- Power-Query-Lösungen für den Alltag ... 149 6. Unternehmensdaten prüfen und analysieren ... 165 6.1 ... Standardsortierung und benutzerdefiniertes Sortieren ... 166 6.2 ... AutoFilter und Datenschnitte ... 170 6.3 ... Vorteile des erweiterten Filters ... 174 6.4 ... Erweiterter Filter mit einem VBA-Makro ... 183 6.5 ... Verwendung von Datenbankfunktionen ... 186 6.6 ... Konsolidierung von Daten ... 200 6.7 ... Zusammenfassung: Basisanalyse ... 214 7. Dynamische Reports erstellen ... 217 7.1 ... Das 5-Minuten-Datenmodell ... 217 7.2 ... Bestandteile eines Datenmodells ... 219 7.3 ... Datenmodell für einen Forecast erstellen ... 226 7.4 ... Datenmodell zur Kalkulation der optimalen Bestellmenge ... 253 7.5 ... Rollierende Berichte ... 259 7.6 ... Zusammenfassung: Datenmodelle ... 264 8. Wichtige Kalkulationsfunktionen für Controller ... 267 8.1 ... Berechnungen mit Datumsbezug ... 268 8.2 ... Berechnungen mit Zeitangaben ... 283 8.3 ... Arbeiten mit Verweisen und Matrizen ... 286 8.4 ... Funktionen zur Dynamisierung von Tabellen ... 292 8.5 ... Berechnung von Rangfolgen ... 308 8.6 ... Berechnung von Mittelwerten ... 315 8.7 ... Runden von Daten ... 320 8.8 ... Neue Textfunktionen ab Excel 2016 (Office 365) ... 323 8.9 ... Fehlerunterdrückung ... 324 8.10 ... Einsatz von logischen Funktionen ... 329 9. Neue dynamische Matrixfunktionen in Excel für Office 365 ... 335 9.1 ... Das Control-Shift-Enter-Beben ... 336 9.2 ... Neue Optionen für die Erstellung dynamischer Datenmodelle ... 36310. 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.4 ... Fehlervermeidung bei der Eingabe von Bedingungen -- die Datenüberprüfung ... 383 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.14 ... Zusammenfassung: Bedingte Kalkulationen ... 40911. Pivottabellen und -diagramme ... 413 11.1 ... Vorbereitung der Basisdaten für eine Pivottabelle ... 414 11.2 ... Pivottabellen erstellen ... 418 11.3 ... Pivotcache und Speicherbedarf ... 437 11.4 ... Visuelle interaktive Analyse von Daten ... 438 11.5 ... Zeitbezogene Auswertungen von Pivottabellen mit Zeitachsen ... 445 11.6 ... Filtern von Daten in einer Pivottabelle ... 448 11.7 ... Gruppierungen in Pivottabellen ... 448 11.8 ... Weiterverarbeitung von Daten aus Pivottabellen ... 473 11.9 ... Personaldaten mithilfe von Pivottabellen konsolidieren ... 480 11.10 ... Grundlegendes zu PivotCharts ... 488 11.11 ... Zusammenfassung: Pivottabellen und PivotCharts ... 49812. 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.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.19 ... Bedingte Formatierungen und Diagramme in Power-Pivot-Reports ... 55313. Excel als Planungswerkzeug ... 557 13.1 ... Wettbewerberanalyse ... 557 13.2 ... Potenzialanalyse ... 564 13.3 ... Portfolioanalyse ... 569 13.4 ... Stärken-Schwächen-Analyse ... 574 13.5 ... Absatzplanung ... 579 13.6 ... Prognosen erstellen ... 597 13.7 ... Personalplanung ... 604 13.8 ... Liquiditätsplanung ... 621 13.9 ... Marktanalyse und Absatzplanung ... 62814. Operatives Controlling mit Excel ... 635 14.1 ... Betriebsabrechnungsbogen ... 635 14.2 ... Divisionskalkulation ... 646 14.3 ... Zuschlagskalkulation ... 648 14.4 ... Äquivalenzziffernrechnung ... 651 14.5 ... Prozesskostenrechnung ... 653 14.6 ... Deckungsbeitragsrechnung ... 657 14.7 ... Dynamische Break-even-Analyse ... 658 14.8 ... Mehrstufige Deckungsbeitragsrechnung ... 667 14.9 ... Planen von Kosten und Erlösen mithilfe von Szenarien ... 668 14.10 ... Produktkalkulation mit Deckungsbeitragsrechnung ... 673 14.11 ... Eigenfertigung oder Fremdbezug (make or buy) ... 686 14.12 ... Zinsen, Tilgung, Annuitäten für Darlehen berechnen ... 689 14.13 ... Abschreibungen ... 694 14.14 ... Methoden der Investitionsrechnung ... 697 14.15 ... Customer Lifetime Value ... 711 14.16 ... Kundenscoring ... 726 14.17 ... Personalstrukturanalyse ... 728 14.18 ... Arbeitszeitanalyse ... 733 14.19 ... Reisekostenabrechnung ... 737 14.20 ... Lieferantenbewertung ... 74015. Unternehmenssteuerung und Kennzahlen ... 755 15.1 ... Zielkostenmanagement (Target Costing) ... 756 15.2 ... Cashflow ... 765 15.3 ... Free Cashflow ... 769 15.4 ... Discounted Cashflow ... 770 15.5 ... Gewichtete durchschnittliche Gesamtkapitalkosten nach Steuern ... 771 15.6 ... Shareholder-Value ... 772 15.7 ... Economic Value Added -- EVA® ... 777 15.8 ... Market Value Added -- MVA ... 779 15.9 ... Bilanzkennzahlen ... 781 15.10 ... GuV-Gliederung ... 789 15.11 ... Beispieldatei GuV -- Bilanz -- Kapitalfluss ... 795 15.12 ... Return on Investment und DuPont-Schema ... 798 15.13 ... Messung der Mitarbeiterzufriedenheit ... 802 15.14 ... Selbstbewertung nach EFQM ... 81816. Reporting mit Diagrammen und Tabellen ... 835 16.1 ... Grundlagen ... 835 16.2 ... Das Standarddiagramm in Excel ... 837 16.3 ... Wichtige Gestaltungsregeln ... 842 16.4 ... Umgang mit Farben ... 846 16.5 ... Auswahl des richtigen Diagrammtyps ... 847 16.6 ... Die neuen Diagrammtypen in Excel 2016 und 2019 ... 865 16.7 ... Allgemeine Formatierungsregeln ... 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.15 ... Spezielle Formatierungen im Diagramm ... 907 16.16 ... Diagramme in Tabellenblättern ... 916 16.17 ... Dashboards erstellen ... 929 16.18 ... Übernahme in PowerPoint ... 939 16.19 ... Übernahme in Word ... 94717. Automatisierung mit Makros -- VBA für Controller ... 949 17.1 ... Wie alles anfängt: die Aufzeichnung eines Makros ... 950 17.2 ... Makros über Schaltflächen aufrufen ... 959 17.3 ... Quellcode im Editor bereinigen ... 963 17.4 ... Bereiche adressieren ... 967 17.5 ... Arbeiten mit Variablen ... 971 17.6 ... Umgang mit Programmfehlern ... 976 17.7 ... Kopieren, Verschieben und Filtern von Daten ... 979 17.8 ... Zugriff auf Dateien über VBA-Makros ... 991 17.9 ... Fallbeispiel: CSV-Import und Datenaktualisierung für einen Forecast ... 999 17.10 ... Flusskontrolle mit »If ... Then ... Else« ... 1001 17.11 ... Programmierung von Schleifen in VBA ... 1007 17.12 ... Formeln und Funktionen in VBA-Makros ... 1019 17.13 ... Gestaltung von Dialogen in VBA ... 1023 17.14 ... Benutzerdefinierte Funktionen ... 1033 17.15 ... Die Beispiele aus dem Buch zum Herunterladen ... 1041 Index ... 1043