Innerhalb Controlling ist Excel natürlich nicht nur in Kombination mit SAP ein Thema (siehe "SAP und Office Integration Excel im SAP GUI (Excel-Inplace) oder als separates Fenster (Excel-Outplace)"), sondern auch sonst aus dem beruflichen Alltag im Controlling nicht ganz wegzudenken.

Mit Office 365 (siehe "Microsoft Office Produkte") sind nicht nur technische Herausforderungen, sondern auch praktische neue Funktionen hinzugekommen. Auf eine davon mag ich im folgenden Artikel gerne näher eingehen. Neben einer praktischen Fragestellung werden hier auch die "Matrixformel (Arrayformel)" sowie ÜBERLAUF in Microsoft Excel für Office 365 erläutert.


Ausgangslage:
In einer Liste werden mehrere Projekte aufgeführt, die unterschiedlichen Programmlinien zugeordnet werden. Dabei unterscheiden wir die Programme A, B und C. Die Zuordnung der einzelnen Projekte zu den einzelnen Programmen erfolgt jährlich und in der Regel sollte sich an der Zuordnung nichts ändern. Es soll je nach Programmlinie die Summe der Beträge für die einzelnen Jahre ausgegeben werden.

Kurzer Export zur Rolle des Controlling
Ein gängiges Vorurteil gegenüber Controlling ist, dass Controlling von Kontrolle kommt, obgleich eigentlich bekannt sein sollte, dass das interne Rechnungswesen (und damit auch das Controlling) in der Betriebswirtschaftslehre (BWL) mehr mit steuern in Verbindung gebracht wird. Controlling hat also mehr eine Lotsenfunktion (daher ist auch Kirsten Lotse die Protagonistin meiner Bücher zum "Schnelleinstieg ins SAP Controlling)" und weniger von Kontrolle geprägt.

Die Betriebswirtschaftslehre sieht im Controlling die Planung, Steuerung und ja auch die Kontrolle von Unternehmensprozessen vor, um die Zielerreichung zu unterstützen. Ziel sollte weniger die Kontrolle im Sinne von Überprüfung von Ergebnissen sein, sondern auch die aktive Steuerung von Unternehmensaktivitäten.

Entsprechend ist ein Werkzeug zur Unterstützung auch die Qualitätssicherung und ja auch die Kontrolle. Dies dürfte auch eine Brücke sein, die sowohl vom externen als auch internen Rechnungswesen beschritten wird. Im SAP Umfeld ist es ohnehin so, da hier die Geschäftsbereiche (Line of Business) die ehemalige Modulsicht ablösen. So umfasst Finance sowohl FI und CO als auch Treasury and Risk Management.

Aber zurück zum eigentlichen Thema.

Meine Tabelle hat folgenden Aufbau:

1. In der Spalte "Projekt" werden die einzelnen Projekte ausgewiesen.
2. In den folgenden Spalten werden je Jahr die entsprechenden Erträge ausgewiesen
3. In der Hilfsspalte (Zuordnung) werden den Projekten aus der 1. Spalte entsprechende Programme zugeordnet. Zur Vereinfachung nutzen wir hier die Programme A, B und C.
4. Zum Abgleich der Zuordnung habe ich die Programmzuordnung der vorherigen Jahre mit aufgeführt.

Nun möchte ich neben der Summe der einzelnen Projekte auch eine Teilsumme je Programm ausgeben. Dazu verwende ich die Formel SUMMEWENN und beziehe mich ebenfalls auf die Hilfspalte (Zuordnung). Mit einem besonderen Twist bin ich im Artikel "SUMMEWENN über mehrere Spalten in Excel oder Personalkostenhochrechnung auf Innenauftrag zusammenfassen" ebenfalls auf diese Formel eingegangen, in diesem Beispiel ist es jedoch etwas einfacher.

Konkret ist dies auch im Screenshot der Tabelle zu sehen.



Die Formel in Zelle C10 lautet:

=SUMMEWENN( $H$3:$H$8;  $H10;  C$3:C$8)

Suchbereich ist die aktuelle Zuordnung (Spalte H Zeile 3 bis 8), Suchkriterium die aktuelle Zuordnung in Spalte H für Programm A (hier in Zelle H10 "A") und Summenbereich sind die Werte für 2025 in den Zellen C3 bis C8.
Entsprechend sind die Formeln auch für die anderen Programme und Jahre aufgeführt.

Jetzt gibt es zwei Methoden, um die aktuelle Zuordnung mit der Vorjahreszuordnung zu vergleichen.

Ich nutze in beiden Methoden Funktionen, die als Ergebnis einer Überprüfung WAHR oder FALSCH ausgegeben. Wenn solche Werte in Berechnungen verwendet werden, entspricht WAHR 1 und FALSCH entspricht 0.

1.) Formel IDENTISCH (mehrere Werte nacheinander vergleichen)

Die Formel IDENTISCH prüft, ob zwei Zeichenfolgen identisch sind und gibt WAHR oder FALSCH zurück. Dabei unterscheidet sie auch zwischen Groß- und Kleinschreibung.
Wie oben erwähnt werden WAHR als 1 und FALSCH als 0 in Berechnungen verwendet, sodass ich hier folgende Berechnung durchführen kann, indem ich die einzelnen Werte mit den Vorjahreswerten vergleiche.

=IDENTISCH(H3;I3) * IDENTISCH(H3;J3) * IDENTISCH(H3;K3)

Das Ergebnis zeigt sich in der ersten Kontrollspalte. Da die Werte übereinstimmen, ist der zurückgegebene Wert 1 ( WAHR * WAHR * WAHR).

Alternativ hätte ich auch mit 
=IDENTISCH(H4;I4) & IDENTISCH(H4;J4) & IDENTISCH(H4;K4)

arbeiten können, dann wäre die Ausgabe in der Form "WAHRWAHRFALSCH", was zum schnellen Lesen etwas umständlich ist. 
In den Zeilen 3 und 4 ist in Spalte L das Ergebnis und in Spalte M die Formel zu sehen.

2.) Kombination aus UND / IDENTISCH oder Formeln mit mehreren Ergebnissen

Der Nachteil der ersten Lösung ist, dass die Formel recht lang werden kann, daher wäre eine Matrix zum Vergleich von mehreren Zellen interessant.

Entsprechend wollte ich per 

=IDENTISCH(A4; G4:I4)

einen Abgleich der Zelle A4 mit den Zellen G4 bis I4 machen.

 Matrixformel (Arrayformel) in Excel

Hier unterscheidet sich das Excel in Microsoft Office 365  von vorherigen Excelfunktionen.

Formeln die nicht nur einen Einzelwert sondern eine Reihe von Werten zurückgeben, geben diese Werte an benachbarte Zellen zurück. Dieses Verhalten für Array wird als Überlaufen bezeichnet.  Wird obige Formel per ENTER (Eingabetaste) bestätigt, passt Excel die Größe des Ausgabebereichs dynamisch an und platziert die Ergebnisse in den folgenden Zellen.


Das Ergebnis wird in den Spalten L, M und N ausgegeben.

Können die Ergebnisse der Formel nicht vollständig ausgegeben werden, da bspw. ein Wert in den Zellen steht, gibt es eine neue Fehlermeldung für die Formel #ÜBERLAUF!. Weitere Ursachen können auch verbundene Zellen sein oder aus anderen Gründen der Platz nicht ausreicht.



Im Gegensatz zu Matrixfunktionen (per STRG + SHIFT + ENTER) sehen wir hier auch alle Ergebnisse. Das dynamische Array wird auch über einen blauen Balken hervorgehoben.

Verarbeitung von mehreren Ergebnissen einer Prüfung per UND

Um nun aber nicht mehrere Zellen lesen zu müssen, kombiniere ich das Ergebnis über folgende Formel:

=UND(IDENTISCH(H7; I7:K7))

Die Formel UND prüft, ob alle übergebene Argumente WAHR sind und gibt in diesen Fall WAHR zurück. Sofern es zu Abweichungen kommt, lautet das Ergebnis FALSCH.



Auch hier könnte ich mit * 1 das Ergebnis auf 0 oder 1 verändern.

In vorherigen Excel-Versionen hätte ich diese Formel als Matrixformel nutzen müssen, indem ich zur Bestätigung STRG + UMSCHALTTASTE (Shitft)  + EINGABETASTE (Enter) gedrückt hätte.



Die Matrixformel ist nach STRG + UMSCH + EINGABE anhand der geschwungene Klammer {}erkennbar.
 

Matrixformel (Arrayformel) unter Office 365 und Web-App

Unter Office 365 funktioniert dies durch einfaches Bestätigen der Formel.
Dennoch sind weiterhin solche Matrixformeln möglich.

Um eine dynamische Arrayformel nutzen zu können reicht der Abschluss der Formel per Eingabetaste. In früheren Excel-Versionen muss die Formel als herkömmliche Matrixformel behandelt werden. Hier passt sich der Ausgabebereich nicht automatisch an und muss manuell ausgewählt werden und die Formel per STRG+UMSCHALT+EINGABETASTE  abgeschlossen werden. Excel stellt diese Formeln dann automatisch mit einer geschweiften Klammer am Anfang und Ende der Formel dar. In der Web-App (Excel für das Web) können Arrayformeln angezeigt werden (sofern schon vorher vorhanden) aber nicht angelegt werden. Selbst per STRG+UMSCHALT+EINGABETASTE ist dies nicht möglich, so dass das Arbeiten im Sinne von Erstellen von Matrixformeln nur in der Excel-Desktopanwendung möglich ist.

Fazit

Im Ergebnis liefert auch diese kompakte Formel ein brauchbares Ergebnis und kann bei Abweichungen von Werten wie hier die Zuordnung direkt nachfragen, wie es zur Änderung kam oder entsprechend nachbessern.

Natürlich kann ich nun auch per bedingter Formatierung statt 0 und 1 auch ein entsprechendes Symbol ausgeben. Alternativ können hier auch per WENN entsprechende Symbole verwendet werden. Dies ist auch Thema im Artikel "Excel: bedingte Formatierung mit Pfeilen (Darstellung Tendenzen bei Veränderungen)" gewesen.

Die Änderungen unter Excel für Microsoft 365 erleichtern durchaus praktisch. Nebenbei dynamischen Array-Funktionen und Überlauf-Operator sind nur in aktuellen Excel-Versionen verfügbar (Excel 2021, 2024 und Microsoft 365) in anderen Versionen würde obiges Beispiel nur mit der Matrixformel aus der Kombination von UND / IDENTISCH per STRG + UMSCH + EINGABE funktionieren.

Weitere Verbesserungen beziehungsweise für mich interessante Funktionen sind neue Verweisfunktionen (XVERWEIS) aber sicherlich auch andere Formeln und Funktionen die sich positv auswirken können. Ein guter Ansatz, und das über einige Jahr(zehnte?) schon sind hier übrigens Blogs wie tabellenexperte.de, blog.soprani.at oder auch die Beiträge von Florian Sandmann-Reetz in diversen Netzwerken.

Für Kolleg*innen, die gerade auf Office 365 gewechselt sind, verweise ich hier auch gerne auf folgende Artikel vom Tabellenexperten "Goodbye SVERWEIS, willkommen XVERWEIS!" oder "Die Raute: Ein kleines Zeichen mit großer Wirkung!".

Ein paar Worte zum Schluss und Entwicklungen hier im Blog

Ich gehe davon aus, dass auch hier im Blog das Thema Microsoft 365 und die einzelnen Office-Apps auch neben SAP eine Fülle an neuen Artikeln bescheren wird. 

Daneben sind für 2025 aber auch einige andere Themen und Projekte angesetzt, auf die ich mich ebenfalls sehr freue. Dabei kommen sowohl neue Onlinevideos als auch ein neues Buchprojekt auf meine Agenda und ich bin gespannt wie sich dieses umsetzen lassen wird. Das Thema Videobearbeitung wird mich (hoffentlich) nicht nur durch mein erfolgreiches Update auf Davinci Reslove 20 begleiten und auch der Bereich Autorenleben wird noch ein paar Punkte bringen. Immerhin die Steuererklärung ist nun inklusive EÜR abgegeben und dieses Jahr war ich sehr begeistert von der schnellen Übernahme in die Steuersoftware.

Hier nutze ich seit Jahren Taxman von Lexware (*) und bin froh, dass mit einer entsprechenden Vorerfassung der Belege die Abgabe der Steuererklärung erstaunlich schnell vollbracht ist.

Um jetzt aber wieder einen Bogen zur Einleitung (Controlling und Kontrolle) zu schlagen, mag ich das Thema Berichtswesen und interne Qualitätssicherung doch noch in den Fokus stellen. So schnell und technisch einfach eine Tabelle und entsprechende Eingabemasken auch bereitet sein mögen, so schnell kann es hier zu Unstimmigkeiten oder gar Abweichungen kommen, die im ersten Moment nicht auffallen. 

Daher habe ich es mir angewöhnt, sowohl kleinere Kontrollen als auch direkt Analysen zu Tabellen außerhalb des Druckbereiches anzulegen, sodass bei einem späteren Bericht auf diese Ergebnisse zurückgegriffen werden kann.

Bevor eine solche Tabelle jedoch versendet wird, sollte sich versichert werden, ob auch die Person, die später mit der Tabelle arbeitet, ebenfalls die passende Officevariante einsetzt. Andernfalls kann dies zu bösen Überraschungen führen. Für meine eigenen Auswertungen und Tabellen bin ich aber zum Glück selbst verantwortlich und kann daher recht einfach davon ausgehen, dass auch aktuellere Formeln wohl funktionieren werden :-).

Die Behandlung von Sonderzeichen, wie das Währungszeichen Euro-Zeichen € war teilweise in den Standard-Einstellungen von Screenreadern nicht unterstützt und mussten entsprechend manuell aktiviert werden. Mittlerweile scheint dies zumindest beim Euro-Zeichen in den meisten als Zeichenaussprache eingeschaltet zu sein, dennoch stellte sich die Frage, ob eine Darstellung von Werten in Berichten nicht statt € in EUR erfolgen kann.

Über die rechte Maustaste und auf Zelle formatieren oder in der Symbolleiste (Menüband) im Registerkarte Start in der Gruppe Zahl auf das Symbol "Buchhaltungszahlenformat" lassen sich diverse Symbole als Währungsformat darstellen.


Beispiele sind

• € Euro (123 €)
• £ Englisch (Vereinigtes Königreich)
• $ Englisch (Vereinigte Staaten)

Im Bericht stellt sich nun jedoch die Frage, wie statt der Symbole eine Kurzform für die Währung zur Formatierung genutzt werden kann.

Für die Beispiele wäre hier folgende Option interessant:

• EUR  Euro
• GBP Britische Pfund
• USD US-Dollar

In Microsoft Excel kann hier unter Symbol weit nach unten gescrollt werden und tatsächlich stehen hier auch EUR, GBP oder USD zur Verfügung. Die Symbole sind weit unterhalb der Sonderzeichen zu finden.

Dabei sind diese direkt auszuwählen. Beispiele zu solchen Formaten wären:

• EUR
• GBP
• USD

wodurch die Währung dann hinter den Betrag ausgewiesen wird.

Diese Symbole stehen sowohl im Zahlenformat Währung (1) als auch Buchhaltung (2) in der folgenden Abbildung zur Verfügung.



Der Unterschied zwischen den beiden Formaten wirkt sich ausschließlich auf die Darstellung (Formatierung) der Zellen aus. Im Buchhaltungsformat werden die Währungssymbole und die Dezimalkommas von Zahlen in der Spalte ausgerichtet und statt 0,00 ein Stich - angegeben.  Im Währungsformat erfolgt keine Ausrichtung an der Spalte, sodass im Beispiel das Währungszeichen mit weniger Abstand am Ende der Zelle

Neben den Währungs- oder Buchhaltungsformatangaben kann auch die Benutzerdefiniert(e) Formatierung genutzt werden.

So könnte ein solches Benutzerdefiniertes Fomrat zum Beispiel als:
#.##0,00 "EUR";[Rot]-#.##0,00 "EUR";0,00 "EUR"
formatiert sein. 

In der folgenden Darstellung ist der Unterschied der im Artikel angesprochenen Formatierungen ersichtlich.

Im Beispielscreenshot ist dies für die Zeile 6 der Fall. Hier werden dann negative Zahlen auch als Rot dargestellt und es muss keine bedingte Formatierung genutzt werden :-).


Die Auswirkung auf die Formatierung ist besonders dadurch relevant, dass intern weiterhin die reinen Zahlenwerte in der Zelle stehen und mit diesen auch gerechnet werden.

Unterhalb der Tabelle ist dies durch die Formel

="Es ergibt sich ein Ergebnis i. H. v. "&D12
erfolgt.
Im Ergebnis ist die Summe als 4 ohne Währung angegeben.

Wenn nun aber Tausenderstellen und das Währungszeichen verwendet werden soll, bietet sich hier die Formel TEXT an, mit der Werte in ein bestimmtes Textformat überführt werden können.

Im Beispiel möchte ich die Summe auch in Textform ausgeben und verwende folgende Formel:

="Es ergibt sich ein Ergebnis i. H. v. "&TEXT(D12;"#.##0,00")&" EUR."

Ohne diese Formatierung würde die Zahl direkt ausgegeben werden (Standardformat).

Zu beachten ist, dass in diesem Beispiel die Währungseinheit per & direkt als Text ausgegeben wird.

So flexibel die Formel TEXT auch ist, habe ich doch hier im Blog relativ wenig Beispiele dafür bisher gefunden. Im Artikel "Datum in Excel so umwandeln, dass es ohne Punkt in der Form TTMMJJJJ verwendet werden kann"  oder "Datumsfunktionen in Excel hier am Beispiel aus Stichtag Vormonat ermitteln" finden sich aber vergleichbare Beispiele, die mich auch an aktuelle Projekte denken lässt, bei denen Datumsformate eine gewisse Rolle spielen.

Ein Grund zum Bloggen ist sicherlich auch hier auf Zeitreise in die eigene Vergangenheit gehen zu können, um alte Lösungsansätze oder Herausforderungen erneut zu finden :-).
 

Die letzten Tage habe ich mich, durchaus im doppelten Wortsinne, mit Abschlussarbeiten zum aktuellen Buchprojekt (u.a. Controlling und SAP S/4HANA) und meiner SAP Schulung zur Datenmigration nach SAP S/4HANA mit Migrationscockpit und Migrationsobjektmodellierer beschäftigt. Die letzte Live-Session wird diese Woche stattfinden und es gibt auch schon aktuelle Termine für den Herbst (Ende September 2022).

Damit aber nicht nur SAP S/4HANA hier ein Thema im Blog ist, mag ich noch eine kurze Empfehlung zum Thema Berichtswesen im Controlling mit Excel geben.

Gerade bei umfangreichen Texten bin ich recht froh auf einfache Weise ein "Inhaltsverzeichnis in Excel über vorhandene Tabellenblätter" per VBA Makro anlegen zu können. Wenn ich aber, bspw. für einen statistischen Anhang, ebenfalls HYPERLINKS zu einzelnen Arbeitsmappen und Zellen innerhalb einer Arbeitsmappe manuell setzen möchte, hilft hier eine Variante der Formel HYPERLINK weiter.


Im Artikel "Berichtsdokumentation in Excel - Hyperlink auf Tabellenblätter indirekt setzen" bin ich schon auf die Formel HYPERLINK eingegangen, um hier den Tabellenblattnamen aus einer anderen Zelle zu vererben und hier einen dynamischen Hyperlink innerhalb der Excel-Arbeitsmappe zu setzen.

Für eine andere umfangreichere Arbeitsmappe sind die Hyperlinks jedoch von vorneherin klar zu setzen und müssen nicht indirekt in der Formel gesetzt werden.

Hier hatte ich als Formel

= HYPERLINK ( "#" & "Tabelle1!A2"; "Tabelle1")

Gesetzt und bekam direkt die Fehlermeldung "Der Bezug ist ungültig.".

Allerdings funktioniert es, wenn der Tabellenblattname in Hochkommata gesetzt ist und per [#] auf einen internen Link gesetzt wird.

= HYPERLINK( "[#]'Tabelle1'!A2";"Tabelle1")

Dabei sind folgende Bestandteile der Formel relevant:

• = HYPERLINK(
  
  Die Formel HYPERLINK hat zwei relevante Parameter
  HYPERLINK(Hyperlink_Adresse, Anzeigename )
  Wobei der Anzeigename opitonal ist.
   
• "[#]'Tabelle1'!A2"
  
  Als Hyperlink_Adresse ist hier in "" der Dateiname als [#]
  quasi als Referenz zu sich selbst gefolgt vom Tabellenblattnamen 'Tabelle1' in Hochkommata und per !A2 die Zelle A2 (Spalte A Zeile 2) verlinkt)
   
• ;"Tabelle1")
  
  als Anzeigename oder freundlicher Name ist erneut das Tabellenblatt genannt.

Dank [#] kann diese Datei nun auch unter einem neuen Namen gespeichert werden und funktioniert ebenfalls.

Da ich immer einmal wieder selbst hier im Blog nach der passenden Syntax für diese Formel suche, hoffe ich einfach einmal künftig problemlos auf diesen Artikel landen zu können.

Das Thema Datenvisualisierung sind nicht nur durch "Datentrends für Drittmittelstatistik mit Sparklines ab Excel 2010 darstellen durch Liniendiagramme in Zellen" mit unterschiedlichen Diagrammtypen oder auch Datenschnitten wie im Artikel "Pivottabellen ab Excel 2010 dynamischer filtern mit Datenschnitten am Beispiel Hochschulfinanzstatistik" ein wichtiges Thema im Berichtswesen, sondern auch die Beziehungen von Daten untereinander wie im Artikel "SVERWEIS in Pivot-Tabellen dank Power Pivot zur Darstellung der Beziehungen von Datentabellen in Excel" können in Excel immer einmal wieder das Berichtswesen bereichern.

Ich werde sicherlich auch weiterhin auf Reporting-Tools unter SAP ERP und SAP S/4HANA eingehen, aber manchmal ist Excel im Controlling dann doch sehr praktisch.... :-)
 

2017 hatte ich schon die erste Auflage »Excel Pivot-Tabellen für dummies« von Martin Weiß gelesen und dieses immer wieder Kolleginnen und Kollegen, nicht nur im Controlling, weiterempfohlen. Im Artikel "Buchempfehlung »Excel Pivot-Tabellen für dummies« von Martin Weiß ( tabellenexperte.de )" sowie den Buchvorstellungen "Excel Pivot-Tabellen (Excel, Pivot-Tabellen / Pivot-Charts, Datenmodelle, Dashboard)" hatte ich die erste Auflage näher vorgestellt und imer einmal wieder das Buch als Leseempfehlungen an Kolleginnen und Kollegen aus Planung, Controlling, Personal und anderen Abteilungen weiter gegeben und positive Rückmeldungen dazu erhalten.

Da ich mich aber auch intensiver mit Datenverbindungen und Power Query innerhalb einer Excel-Arbeitsmappe auseinandersetzen wollte, war auch die Entscheidung schnell gefallen. Die ersten Auswirkungen der neuen Technik sind ja auch schon im Artikel "SVERWEIS in Pivot-Tabellen dank Power Pivot zur Darstellung der Beziehungen von Datentabellen in Excel" praktisch umgesetzt worden.

Nachdem nun auch die zweite Auflage veröffentlicht wurde und hier ein größeres Thema ausführlicher behandelt wurde, hatte ich mir diese Ausgabe direkt bestellt, sodass nun die erste Auflage im Büro und die zweite erweiterte Auflage daheim oder im mobilen Office vorhanden ist.


Buchempfehlung: Excel Pivot-Tabellen für dummies

 

Schon in der ersten Auflage hatte mir der Schwerpunkt auf Optik und Feintuning von Tabellen, aber auch die Aufbereitung von Pivot-Tabellen für Dashboards gefallen. Dabei waren die Beispiele immer praxisnah und mit Humor gut beschrieben. Gerade das Thema Datenverbindung und Datenmodelle sind aber auch etwas, wo ich mich intensiver mit auseinandersetzen wollte und da die erste Auflage immer mal wieder jemand anderen zum Reinlesen ausgeliehen wurde, klang der Schwerpunkt auf Power Pivot und Power Query verlockend, sodass sich für mich die zweite Auflage direkt gelohnt hatte. Der vierte Abschnitt (Teil IV: Mit Power in höhere Sphären vordringen) mit den beiden Kapiteln Power Query und Power Pivot ist nicht nur gut beschrieben, sondern regt direkt an das Thema an eigenen Berichtsanforderungen zu versuchen und in die berufliche Praxis umzusetzen.

Persönlich bin ich noch immer davon beeindruckt, dass das Buch sowohl zum Einstieg in das Thema Pivot Tabelle als auch für Fortgeschrittene geeignet ist und neben einer Prise Humor auch immer die Verständlichkeit der aufbereiteten Daten sowie der erstellten Berichte im Blick behält. Martin Weiß kann hier als Tabellenexperte tatsächlich überzeugen und für Excel und besonders für Pivot-Tabellen begeistern.

Inhaltlich wurde das Buch überarbeitet und ist insgesamt in fünf Teile aufgeteilt und bietet mit elf Kapiteln das notwendige Wissen vom Einstieg bis zur Expertise rund  um Excel und Pivot-Tabellen.

Teil I: Die Grundlagen

Auch in der neuen Auflage werden auf ordentlich formatierte und saubere Quelldaten zur Vorbereitung festgelegt. Danach kann aber auch schon die erste Pivot-Tabelle angelegt werden. Von den Grundlagen aus gibt es auch immer weitere Empfehlungen, so dass schon mit den Grundlagen eine saubere Aufbereitung der Daten möglich ist und hier auch viel über Design von Daten gelehrt wird.

Teil II: Das Auge isst mit: Optik und Feintuning

Aber auch weitere Formen der Formatierung, das Arbeiten mit Formatvorlagen aber auch Filterungen und Gruppierungen von Daten in einer Pivot sowie die Gestaltung von Pivot-Charts werden ausführlich erklärt.

Besonders gut gefällt mir die Erläuterung der bedingten Formatierung in der Pivot-Tabelle und die praktischen Hinweise, wie Überschriften auch lesbar und verständlich gehalten werden können. Neben der Datenfilterung hat mir auch die Gruppierung von Daten sei es über Datumsfelder oder Artikelnummern und die manuelle Sortierung und Gruppierung gut gefallen, da dieses oftmals auch bei uns im Berichtswesen ein großes Thema ist.

Auch wenn ich mit Pivot-Charts seltener arbeite, machen die Beispiele doch Lust darauf, diese auch in eigenen Berichten anzuwenden und direkt umzusetzen.
 

Teil III: Fortgeschrittene Techniken

Unter den fortgeschrittenen Techniken sind meine Lieblingstools wie Datenschnitten, Zeitachsen oder auch berechnete Felder zu finden. Das ganze endet dann auch noch zum Amaturenbrett für das Management in Richtung praktischer Gestaltung eines Dashboards.

Die hier beschriebenen Kapitel waren auch schon in der vorherigen Auflage überzeugend und gerade was Excel und Controlling anbelangt kann ich diese auch weiterhin empfehlen.

Es ist auch kein Wunder, dass Artikel wie "Excel Berechnete Felder in Pivottabellen" oder "Pivottabellen ab Excel 2010 dynamischer filtern mit Datenschnitten am Beispiel Hochschulfinanzstatistik" immer wieder im Hochschulcontrolling relevant sind.
 

Teil IV: Mit Power in höhere Sphären vordringen

Der vierte Teil war für mich der absolute Grund die Neuauflage ebenfalls zu lesen und zu erwerben. Neben der Vertiefung der Themen rund um Datenmodelle und Alternativen zum SVERWEIS dank Power Query hat hier auch Power Pivot und die Möglichkeiten des Umgangs mit Datenmodellen und Berechnungen überzeugt.

Gerade die Formeln in Power Pivot und Verarbeitung der Daten innerhalb des Datenmodells werden mich das Buch auch als Nachschlagewert noch häufiger verwenden lassen. Da bei mir oftmals die Daten schon formatiert in der Tabelle vorliegen bin ich froh hier nun direkt im Datenmodell komplexe Berechnungen und Verbindungen diverser Datenquellen vornehmen zu können.

Power Query hingegen kann interessant sein, wenn aus unterschiedlichen externen Quellen Daten zusammengefasst werden und gerade bei größeren Datenmengen spielt es einige Vorteile aus.

Teil V: Der Top-Ten Teil

Zu jedem Buch der Dummies-Serie gehört ein Top-Ten-Teil in dem zehn Kniffe beim Thema aufgeführt sind. Hier sind tatsächlich einige Antworten auf Fragen zu finden, die immer einmal wieder gestellt werden. Sowohl die Formel PIVOTDATENZUORDNEN ist hier ausführlich erläutert als auch der Umgang mit Fehlerwerten oder auch ganz einfache Dinge wie Namenszuweisung von Pivot-Tabellen, die dann erheblich die Arbeit erleichtern.

Fazit

Ich habe schon die erste Auflage sowohl Kolleg:innen aus der Planung, Controlling als auch der Personalabteilung empfohlen und stets positives Feedback erhalten. Persönlich merke ich auch, dass viele Daten und Berichte von mir an der Arbeit eine positive Verbesserung erhalten haben, da hier auch grundsätzliche Fragen in Richtung Berichtswesen beantwortet werden.

Wer noch immer Zweifel hat, kann sich auf der Internetseite und Blog von Martin Weiß (tabellenexperte.de) einen Eindruck auf die Art und Weise wie aktuelle Themen rund um Excel erklärt und aufbereitet werden.

Ich kenne nur wenige Excel-Blogs, die vergleichbar sind (siehe Webempfehlungen Excel Blogs) und denke, dass sowohl Fortgeschrittene als auch Neulinge im Bereich der Pivot-Tabellen hier gute Anregungen und Grundlagen finden.

Insgesamt ist das Buch für mich eine Bereicherung und gehört zu denjenigen Büchern, die ich auch selbst immer einmal wieder zum Nachschlagen verwende, um ein Problem zu lösen oder eine Anregung für einen neuen Bericht in Excel zu erhalten.

Außerdem hilft es, das Buch kurzfristig auszuleihen, um die Begeisterung für Excel auch im Büro abteilungsübergreifend teilen zu können.

Für einen einfachen Finanzbericht haben wir eine Liste mit gebuchten Beträgen auf Kostenstellen und Projekten. Diese sollen mit einer Pivot-Tabelle zusammengefasst werden.



In der Tabelle sind Art (Projekt oder Kostenstelle), Kostenart, Kostenstelle, Innenauftrag und Betrag ausgewiesen.

Eine Pivot-Tabelle ist über den Ribbon (Symbolleiste/Menüleiste) Einfügen und hier die Schaltfläche PivotTable schnell erstellt.


Als Zeilen in der Pivot-Tabelle sind die Spalten Art, Kostenstelle und Innenauftrag aufgenommen worden und als Wert wird die Summe der Spalte Betrag ausgewiesen. Allerdings sollen zu beiden CO-Objekten jeweils auch Stammdaten ergänzt werden.

Vor Excel 2013 war es hier erforderlich in der Grundtabelle die Stammdaten zum Beispiel durch SVERWEIS oder einer anderen im Artikel "Grundlagen in Excel Verweisfunktionen SVERWEIS WVERWEIS und VERWEIS" vorgestellten Verweisfunktionen zu ergänzen oder alternativ wie im Artikel "Index und Vergleich statt SVERWEIS endlich verstanden und Suche über Verweis nur, wenn es auch etwas zu finden gibt" beschrieben andere Formeln zur Zuordnung von Daten zu verwenden.

Mit Excel 2013 sind jedoch Beziehungen innerhalb der "intelligenten" Tabellen hinzugekommen. Diese ist möglich mit wenigen Schritten durchzuführen und das Ergebnis ist eine erhebliche Erleichterung für das Berichtswesen.

Die einzelnen Schritte mag ich, auch für Kolleginnen und Kollegen im Controlling, etwas ausführlicher beschreiben, da ich die Funktionsweise selbst erst vor wenigen Tagen für mich entdeckt habe.
 

Schritt 1: Grundtabellen als "intelligente" Tabelle zu formatieren.

Im Artikel ""Als Tabelle formatieren" um eine dynamische Datenquelle für Pivot-Tabellen zu erhalten" bin ich schon auf Vorzüge vom Formatieren von Tabellen eingegangen und hier ist dieses besonders praktisch.

Für unseren Fall habe ich neben der obigen Berichtstabelle noch zwei weitere Tabellen, die Stammdaten zu den CO Objekten Kostenstelle und Innenauftrag enthalten.

Im Ribbon (Symbolleiste) Start kann über die Schaltfläche "Als Tabelle formatieren" Daten in eine Datentabelle formatiert werden und einen passenden Tabellennamen zugewiesen werden.



Ich gebe den einzelnen Tabellen meist einen passenden Kurznamen mit vorangestellten "T_".

Im Beispiel sind das folgende drei Tabellen, die ich nun zur Verfügung habe.

1.1. Tabelle T_Bericht

Hier sind die einzelnen Daten unseres Berichtes vorhanden.

1.2. Tabelle T_Auftrag

Hier sind die Stammdaten zum Feld Auftrag zu finden.

1.3. Tabelle T_Kostenstelle

In dieser Tabelle sind die Stammdaten der Kostenstelle enthalten.
 

Stammdatenlisten in SAP ERP System
Im SAP Umfeld können solche Stammdatenlisten (für Innenauftrag und Kostenstellen) entweder einfache Stammdatenlisten wie KS13 oder KOK5 sein oder auch Eigenentwicklungen wie SAP Query. Ein Beispiel ist im Artikel "SAP Query Stammdaten PSM / CO Innenauftrag" beschrieben, aber auch sonst finden sich im Blog und meinem Buch zum Berichtswesen im SAP Controlling einige Beispiele für das Erstellen geeigneter Berichte.

Nun haben wir also insgesamt drei Tabellen mit eigenen Namen:

• T_Bericht
• T_Innenauftrag
• T_Kostenstelle

Schritt 2: Daten - Datentools - Beziehungen

Nachdem die relevanten Tabellen als Tabelle formatiert wurden (ich bezeichne diese nun als Datentabellen) aktiviert sich die Schaltfläche "Beziehungen" im Ribbon "Daten" unter Datentools.



Danach können Beziehungen gepflegt werden.



Nun kann die Berichtstabelle mit den einzelnen Stammdatentabellen verknüpft werden. In der folgenden Abbildung sind beide Tabellen mit der Tabelle T_Bericht verknüpft.



Es handelt sich hier um eine N:1 Beziehung,

Während in der Tabelle T_Bericht Kostenstelle und Innenauftrag mehrmals vorkommen (N) können, sind diese in der Stammdatentabelle T_Auftrag oder T_Kostenstelle jeweils nur einmal vorhanden (1).

Als Verknüpfung wird daher die Tabelle T_Bericht gewählt mit der Spalte Innenauftrag (im Beispiel 1)  oder Kostenstelle (Beispiel 2) als Fremdschlüssel (Spalte (fremd) und die Verwandte Tabelle ist dann die Tabelle in der die Daten jeweils einmalig vorkommen. Hier sind als Verwandte Tabellen T_Auftrag und die Verwandte Spalte (primär) Auftrag (Beispiel 1) sowie T_Kostenstelle und die verwandte Spalte (primär) Kostenstelle (Beispiel 2).

Dabei muss die Beschriftung der Spalte nicht identisch sein, aber es bedarf einer Übereinstimmung der beiden Daten.

Im Ergebnis sind also zwei N:1 Verknüpfungen zwischen T_Bericht und den Stammdatentabellen als verwandte Nachschlagetabellen unter Beziehungen verwalten gepflegt und werden so auch in der folgenden Abbildung dargestellt.



Im Ergebnis haben Sie nun intern ein Datenmodell mit Verknüpfungen in der Arbeitsmappe erstellt, auf das sie nun auch zugreifen können.
 

Schritt 3 PivotTable mit Datenmodell der Arbeitsmappe erstellen

Bei der Erstellung eines Datenmodells mit der Schaltfläche PivotTabele in der Symbolleiste Einfügen unter Tabellen gibt es nun nicht nur Tabelle oder Bereiche als zu analysierende Daten, sondern auch den Punkt "Das Datenmodell dieser Arbeitsmappe verwenden", welcher vorher noch nicht vorhanden war.



Innerhalb der Feldauswahl der PivtotTable-Felder erscheinen nun nicht nur die Felder der Berichtstabelle, sondern auch die Stammdatenfelder zur Auswahl.



Für unsere Pivot-Tabelle kann ich nun folgende Felder übernehmen

Zeilen:

• Art (T_Bericht)
• Kostenstelle (T_Bericht)
• Kurztext (T_Kostenstelle)
• Verantwortlich (T_Kostenstelle)
• Innenauftrag (T_Bericht)
• Kurztext (T_Auftrag)
• Arbeitsbeginn (T_Auftrag)
• Arbeitsende (T_Auftrag)

In der Klammer sind jeweils die Tabellen, aus denen anhand des Datenmodells die Spalte übernommen wird, aufgeführt.

Als Werte wir Betrag (T_Bericht) genommen und eine Summe gebildet.

Meine fertige Auswertung sieht dabei wie folgt aus:



Hier sind nun zu den Kostenstellen und Aufträgen auch die jeweiligen Stammdaten mit aufgeführt.

a) Kostenstelle mit Stammdaten:

b) Auftrag mit Stammdaten:


Im Ergebnis entspricht dieses einer Grundtabelle, in der ich vorher mühsam die Stammdaten hätte ergänzen müssen.

Anmerkung 1: (Leer) in Pivot Tabelle (Zelle) entfernen

Da ich zur Kostenstelle in der Pivot-Tabelle verständlicherweise keine Daten zum Feld Innenauftrag habe, erscheint hier in der Pivot der Hinweis (Leer).



Dieser kann einfach mit einer Leertaste überschrieben werden

Anmerkung 2: Datenmodell verwalten unter Power Pivot

Unter der Schaltfläche "Datenmodell verwalten" unter Datentools in der Symbolleiste Daten können die im Datenmodell vorhandenen Daten auch noch weiter bearbeitet werden.



Hier können neue Berechnungen durchgeführt werden, Spalten eingefügt werden und weitere Bearbeitungen des Datenmodells vorgenommen werden.

In der Standardansicht sind hier alle im Datenmodell vorhandenen Tabellen aufgeführt und es können auch noch externe Daten hinzugefügt werden.



Über die Diagrammansicht ist auch die Verknüpfung der gewählten Tabellen ersichtlich.



Hier ist auch die 1:N Verknüpfung direkt ersichtlich.
 

Fazit

Die Verknüpfung von Daten mit Beziehungen innerhalb einer Arbeitsmappe und der damit verbundene Vorteil von Datentabellen ist nur ein Vorteil, den Power Pivot und Power Query für Datenmodelle in neueren Excelversionen anbietet.

Dank der Neuauflage von "Excel Pivot-Tabellen für dummies" von Martin Weiß (www.tabellenexperte.de) habe ich mich nun auch endlich einmal intensiver mit der Thematik beschäftigt.

 

Eine ausführliche Vorstellung des Buches folgt hier noch im Blogartikel. Dennoch mag ich an dieser Stelle das Buch schon einmal als Empfehlung weiter geben.

Insgesamt bin ich der Meinung, dass damit das Berichtswesen mit Excel auch noch ein Stück vereinfacht werden kann und die Frage einer Kollegin, wie SVERWEIS in Pivot Tabellen genutzt werden können hoffentlich dank der Beschreibung hier im Artikel auch schon ein Stück weiter geholfen hat.

 

Berichtswesen nicht nur mit Excel
Beruflich ist ein Schwerpunkt meiner Arbeit das Controlling und Berichtswesen. Neben Excel arbeite ich hier auch besonders gerne mit SAP. Schon bei der Konzeption eines umfangreichen Berichtes und etwaiger Dashboards ist es hier hilfreich sich im Vorfeld passende Gedanken zu machen. Hier habe ich im Buch »Berichtswesen im SAP®-Controlling« (Buchvorstellung, für 19,95 EUR bestellen) einige Punkte festgehalten.

Im Blog finden Sie aber auch regelmäßig Praxisbeispiele rund um die Themen SAP, Berichtswesen und Controlling. Viele Beispiele sind dabei mit Bezug zur Hochschule, aber können, wie der Artikel "Statistische Kennzahlen für Verrechnung in SAP - Umlage und Verteilung nicht nur im Hochschulcontrolling und Hochschulberichtswesen" auch für andere Branchen genutzt und als Grundlage zum Aufbau eines eigenen Berichtswesens genutzt werden. Aber auch sonst finden sich hier immer wieder Artikel, die nicht nur im Bereich Controlling und Berichtswesen an Hochschulen von Bedeutung sind.

Ich würde mich freuen, wenn meine Bücher (Publikationen) aber auch Schulungen (Workshop & Seminare) auch für Sie interessant wären. Weitere Partnerangebote, wie auch eine Excel Schulung zu Pivot finden Sie ebenfalls unter der Rubrik Onlineshop.

Meiner Erfahrung nach lohnt es sich immer sich auch in scheinbar vertrauter Software, wie eben auch Excel, auch die Zeit zu nehmen um Dashboards oder auch neue Techniken, wie hier bei Pivot Tabellen zu lernen und in der Praxis einzusetzen.

Beispiele für ein erweitertes Berichtswesen auch in Richtung Dashboards sind auch in den Artikeln "Pivottabellen ab Excel 2010 dynamischer filtern mit Datenschnitten am Beispiel Hochschulfinanzstatistik" und besonders "Datentrends für Drittmittelstatistik mit Sparklines ab Excel 2010 darstellen durch Liniendiagramme in Zellen" festgehalten.