|
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« |
457 |
|
|
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 |
|