20:26 Uhr
Hyperlink Verweis auf Tabellenblatt innerhalb einer Arbeitsmappe ohne Dateiname
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.... :-)
19:49 Uhr
Neues aus der wunderbaren Welt Excel Pivot-Tabellen von Grundlage formatierter Tabelle bis Expertise Dashboards, Datenmodelle mit Power Query und Pivot
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
(17. Februar 2021) Paperback ISBN: 9783527718214
Bei Amazon für 20,00 € *
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.
Microsoft Office 365 Abo verlängern
Microsoft Office 365 Home
Microsoft Office 365 Business Premium
Microsoft Office Produkte - Jahreslizenz und Dauerlizenzen
* Als Amazon-Partner verdiene ich an qualifizierten Käufen über Amazon.
14:33 Uhr
SVERWEIS in Pivot-Tabellen dank Power Pivot zur Darstellung der Beziehungen von Datentabellen in Excel
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)
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.
(17. Februar 2021) Paperback ISBN: 9783527718214
u.a. bei Amazon für 20,00 € *
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.
Smartwatch -Smarte Geräte (Wearable) am Handgelenk
21:27 Uhr
Excel Arbeitsmappen vom Wert in der Summenzelle per Klick auf Einzelposten per HYPERLINK
Für einen umfangreichen Bericht sind unterschiedliche Tabellenblätter mit Buchungen der Finanzbuchhaltung oder auch gebuchten statistischen Kennzahlen zusammengestellt worden und werden an dieser Stelle für eine komplexere Budgetberechnung verwendet. Um das Beispiel etwas übersichtlicher zu gestalten habe ich als Beispiel einen Bereich mit Personalkosten und Gebäudekosten.
Einzeltabellen Personalkosten und Gebäudekosten
In den Details sind die Personalkosten nach Kostenstelle, Kostenart, Betrag und Buchungstext aufgelistet (hier natürlich pseudonymisiert).Ferner sind auch die Gebäudekosten vergleichbar nach Bewirtschaftung und Bauunterhalt aufgelistet.
Auch hier sind die Einzelwerte je Kostenstelle aufgelistet.
Kostensimulation für Blogs:
Im Artikel auf Unkelbach, Andreas: »Zufallswerte in Excel Zellen per Zufallszahlszahl und Zufallsbereich aber auch in Access je Datenzeile einer Access Abfrage generieren« in Andreas Unkelbach Blog (ISSN: 2701-6242) vom 1.12.2020, Online-Publikation: https://www.andreas-unkelbach.de/blog/?go=show&id=1158 bin ich auf die Ermittlung von Pseudozahlen eingegangen, sodass hier Zufallszahlen für Kosten im Rahmen eines Zufallsbereiches ermittelt. Immerhin sollen hier ja keine echten Daten verwendet werden. Da die Daten auch nur auf Zufall bestehen müssen diese nicht unbedingt sinnvoll sein, sondern dienen nur als praktisches Beispiel.
Auswertungen im Hochschulberichtswesen und Hochschulcontrolling mit SAP
Wenn es um Echtdaten geht nutze ich für eine Datenbasis die Möglichkeiten eines SAP Berichtswesen (nicht nur im SAP Modul Controlling). Im Artikel "Grundlagen Kurzeinführung und Handbuch SAP Query" stelle ich die Möglichkeiten der Tabellenauswertung mit SAP Query vor während der Artikel "Grundlagen Kurzeinführung und Handbuch Report Painter Report Writer" die Möglichkeiten der Summenberichte mit Report Painter bzw. Report Writer vorstellt. Eine wichtige Ergänzung sind noch Rechercheberichte die sowohl im Bilanzreporting, Public Sector Management (Haushaltsmanagement) aber auch der Profitcenterrechnung eine Rolle spielen. Im Artikel "Grundlagen: Was sind die Unterschiede zwischen Report Painter und Rechercheberichte?" hatte ich diese im Grundsatz schon einmal vorgestellt, aber auch hier im Blog werden immer wieder Beispiele zu diesen Berichtsarten veröffentlicht.
Einen umfassenderen Überblick über die Möglichkeiten des Berichtswesen sind im Buch »Berichtswesen im SAP®-Controlling« (Buchvorstellung, für 19,95 EUR bestellen) festgehalten.
Kennzahlen HNF und VZÄ als Faktoren zur Budgetberechnung
Basis für die Budgetberechnung sind nun aber die geplanten Kennzahlen, die im Verhältnis zur Kostenentstehung gesetzt werden sollen.Dieses ist zum einen die gewichtete Hauptnutzfläche (HNF) als Basis für die Gebäudekosten.
Für die Personalkosten die Vollzeitäquivalente (VZÄ), wie in folgender Abbildung ersichtlich ist.
Welche Kennzahlen können verwendet werden?
Unter Unkelbach, Andreas: »Statistische Kennzahlen für Verrechnung in SAP - Umlage und Verteilung nicht nur im Hochschulcontrolling und Hochschulberichtswesen« in Andreas Unkelbach Blog (ISSN: 2701-6242) vom 24.4.2021, Online-Publikation: https://www.andreas-unkelbach.de/blog/?go=show&id=1198
habe ich einige mögliche Kennzahlen näher vorgestellt die entsprechend berechnet werden können.
Kennnzahlen erfassen und auswerten in SAP
Im SAP Umfeld werden solche Kennzahlen oft als statististische Kennzahlen erfasst. Auch hier verweise ich gerne auf zwei weiterführende Artikel. Im Beitrag "Hochschulcontrolling: Vergleich Lehrimport von Studiengängen und Kostenanteile einzelner Lehreinheiten - Abschnitte mit abgeleiteten Kennzahlen im Report Painter" werden dei Kennzahlen über mehrere Jahre für einzelne Bereiche (Kostenstellen, Innenaufträge) in Summe verglichen. Teilweise bedarf es manchmal aber auch der Anforderung die gebuchten Kennzahlen als Einzelposten auszuwerten. Hier ist mein Beitrag "Auswertung gebuchter statistischer Kennzahlen jahresübergreifend nach CO Objekten wie Kostenstelle oder Innenauftrag" im Bereich des Hochschulcontrolling und Hochschulberichtswesen mit SAP CO hilfreich und zeigt auch auf Datenbankebene wie hier per Query die Kennzahlen ausgewertet werden können.
Budgetberechnung auf Übersicht
Zur Berechnung der Budgetwerte werden die einzelnen Tabellen miteinander in Verbindung gesetzt.Die Budgetberechnung erfolgt hier im Verhältnis der einzelnen Kennzahlen und der im Verhältnis dazu gebuchten Kosten.
Der Nachteil ist jedoch, dass ich hier per Verknüpfung nur auf die Zellen verweise und hier die Budgetverantwortliche selbst in der Formel nachlesen muss, wie die Berechnung erfolgt.
Excel Optionen - Direkte Zellbearbeitung zulassen und Sprung auf Verknüpfung
Natürlich könnte die Budgetverantwortliche nun, wie im Artikel "Arbeitsrechner neu einrichten Wechsel auf Windos 10, aktuelle Office Version sowie SAP und Excel anpassen" beschrieben die Zellbearbeitung so umstellen, dass per Doppelklick auf diesen Wert automatisch in die jeweilige Tabelle gewechselt wird.Diese Option ist in den Excel-Optionen unter "Erweitert" im Abschnitt "Bearbeitungsoptionen" durch Deaktivieren der Option "Direkte Zellbearbeitung zulassen" möglich (siehe folgende Abbildung).
Nun kann per Doppelklick zum Beispiel auf die HNF direkt auf die jeweilige Summenzeile (im Beispiel Tabellenblatt HNF Zelle B30) gewechselt werden, um diese Werte zu kontrollieren.
Eine wesentlich elegantere Variante habe ich im Artikel "Berichtsdokumentation in Excel - Hyperlink auf Tabellenblätter indirekt setzen" mit der Funktion HYPERLINK zur Umsetzung eines Inhaltsverzeichnisses beschrieben. Diese kann auch dafür verwendet werden, um eine Sprungmarke auf die Basis der Datei zu setzen.
Der Syntax zur Formel HYPERLINK lautet:
= HYPERLINK ( Hyperlink_Adresse , [Anzeigename] )
Um innerhalb der Excelmappe zu verlinken kann hier als Hyperlink_Adresse eine Arbeitsmappe oder direkt ein weiteres Tabellenblatt innerhalb der Zeichen "" angegeben werden. Wird auf die eigene Mappe genommen, ist der Syntaxt der Formel im Beispiel für die oberen HNF mit
=HYPERLINK("#HNF!B30";HNF!B30)
verlinkt.
Statt # könnte mit [ ... ] auch auf eine andere Arbeitsmappe verwiesen werden.
Zum Beispiel
- "[U:HNF.xlsx]HNF!b30"
In unserem Fall wird mit # die aktuelle Arbeitsmappe bezeichnet und dort auf das Tabellenblatt HNF und mit ! auf die Zelle B30, als freundlicher Name (Anzeigename) wird auch direkt der Wert der Zelle HNF!B30 ausgegeben.
Für die obere Tabelle habe ich mit FORMELTEXT einmal alle relevaten Formeln ausgegeben.
Basis zur Berechnung (Kosten des aktuellen Jahres und die entsprechenden aktuellen Kennzahlen:
Die einzelnen Formeln lauten:
Bereich | Formel |
---|---|
Personalkosten | =HYPERLINK("#Personalkosten!C32";Personalkosten!C32) |
Gebäudekosten | =HYPERLINK("#Gebäudekosten!C32";Gebäudekosten!C32) |
VZÄ | =HYPERLINK("#VZÄ!B28";VZÄ!B28) |
HNF | =HYPERLINK("#HNF!B30";HNF!B30) |
Es wird also der Wert (Ergebnis / Summe) der Daten im jeweiligen Tabellenblatt für das aktuelle Jahr angegeben.
Nun kommt es aber zur Berechnung der Budgetwerte.
Zur Budgetberechnung der Folgejahre werden die Personalkosten des aktuellen Jahres (Zelle C5) durch die VZÄ des aktuellen Jahres (Zelle E5) geteilt und mit den angesetzten Planwerten VZÄ der Folgejahre multipliziert.
Ebenso wird mit den Gebäudekosten (Zelle C6) und den HNF (Zelle E6) verfahren.
Die künftigen HNF und VZÄ werden ebenfalls per Formel mit Hyperlink auf die jeweilige Zelle mit der Summe der Kennzahlen verknüpft.
So lautet die Formel für VZÄ in 2022 = HYPERLINK ("#VZÄ!C28";VZÄ!C28)
Sollten Sie, wovon ich auch aus anderen Gründen abrate, einen Tabellennamen mit Leerzeichen haben, wäre dieser in Apostroph / Hochkommata wie zum Beispiel 'Sonstige Kostenarten'. Im Beispiel wäre dies ein Tabellenblatt mit der Bezeichnung "Gemeinkosten Verwaltung" und die Formel würde wie folgt lauten:
HYPERLINK("#'Gemeinkosten Verwaltung'!C32"; 'Gemeinkosten Verwaltung'!C32 )
Da die Hochkommata schnell vergessen werden, und fehleranfällig sind, nutze ich lieber zusammenhängende Tabellennamen.
Im Ergebnis sieht das Tabellenblatt mit der Übersicht wie folgt aus:
Statt direkt in der Zelle den Wert (oder Formel) per Klick zu erhalten wird hier eine Verknüpfung mit Hinweis auf "Klicken Sie einmal, um den Hyperlink zu folgen. Klicken Sie, und halten Sie die Maustaste gedrückt, um die Zelle auszuwählen." ausgegeben.
Durch die zweite Option (Maustaste gedrückt halten) kann die Formel in D10, im Beispiel, erneut bearbeitet werden. Nach Bearbeitung wird die Zelle aber auch wieder als Hyperlink formatiert, sodass hier, zwecks besserer Optik die Formatierung der Zelle angepasst werden sollte.
Nebenbei ist dieses auch ein Grund, warum ich hier gerne mit den im Artikel "Vorteil von Excel Formatvorlagen und Filter nach Farben oder Zellensymbolen aus bedingter Formatierung" beschriebenen Formatvorlagen arbeite. Neben der Einfärbung der Registerblätter (Orange für Grunddaten, Grün für aufbereitete/fertige Daten und Gelb für Tabellenblätter in denen noch Daten fehlen) sind die Formatvorlagen einheitlich und durch die Formatierung schnell ersichtlich, was es mit diesen Daten auf sich hat.
Die verknüpften Kennzahlen (HNF, VZÄ) sind als Ausgabe formatiert und die Budgetberechnung als Berechnung. So ist auch die Datenherkunft schnell erläutert.
Fazit
Neben der verständlichen und klaren Darstellung von Daten, wie im Artikel "Datentrends für Drittmittelstatistik mit Sparklines ab Excel 2010 darstellen durch Liniendiagramme in Zellen" anhand unterschiedlichen Aufbereitungen von Drittmittelzahlen beschrieben, ist auch die Anwendbarkeit von Tabellen und Unterstützung der Budgetverantwortlichen ein wichtiger Punkt bei der Gestaltung von Finanzberichten unabhängig davon, ob diese nun in SAP oder in Excel erfolgen. Gerade bei einem Personalwechsel in der Fachabteilung ist das eine gute Gelegenheit um bewährte Arbeitsmappen noch einmal unter den kritischen Blicken der Verständlichkeit und Handhabe zu hinterfragen und hier entsprechende praktische Anpassungen vorzunehmen.In diesem Beispiel sind die Datenverknüpfungen hilfreich um schnell von der Summenzeile auf die Einzelposten zu wechseln. Gerade bei umfangreicheren Pivot-Tabellen oder als Tabelle formatierte Bereichen nutze ich auch immer wieder gerne Datenschnitte (Slicer) zur besseren Navigation innerhalb einer Tabelle.
In einer sehr einfachen Form können diese dann wie in folgender Abbildung aussehen:
Statt einzelne Filter über eine Tabelle zu setzen sind hier Schaltflächen die eine Filterung auch über Bereiche vornehmen, die nicht direkt dargestellt werden.
Im Artikel "Pivottabellen ab Excel 2010 dynamischer filtern mit Datenschnitten am Beispiel Hochschulfinanzstatistik" bin ich auf die Verwendung in Pivot-Tabellen eingegangen, aber wie schon im Artikel ""Als Tabelle formatieren" um eine dynamische Datenquelle für Pivot-Tabellen zu erhalten" beschrieben ist dieses auch bei anderen Daten möglich.
Eine Alternative sind Dropdownlisten, wie im Artikel "Formulare gestalten in Excel" durch die dann einzelne Tabellen angepasst werden und so eine Datengrundlage für Diagramme oder andere Auswertungen dienen.
Grundsätzlich ist es, meiner Erfahrung nach, immer sinnvoll auch schon etablierte Tabellen, die sich bewährt haben, dahingehend zu überprüfen, ob nicht nur die Berechnung, sondern auch die Handhabung verbessert werden kann um so auch für Kolleginnen und Kollegen, die später einmal die Berichte erstellen oder selbst um weitere Angaben ergänzen müssen hier eine praktische Unterstützung geben zu können.
Aktuelles von Andreas Unkelbach
unkelbach.link/et.reportpainter/
unkelbach.link/et.migrationscockpit/
10:20 Uhr
Matrixformel zur Berechnung von Mittelwert ohne Null oder mit Bedingungen - Excelformel MITTELWERTWENN und MITTELWERTWENNS
Excel Grundlagen: Mittelwert ohne 0 per Matrixformel oder MITTELWERTWENN
Ausgangslage war damals folgende Datentabelle, aus der ein Mittelwert berechnet werden soll:
Hier ist noch die Formel mit MITTELWERT über die Spalten B2 bis B11 ausgewiesen und leider werden hier auch Nullwerte berücksichtigt.
"Die Formel MITTELWERT stellt mathematisch das arithmetische Mittel dar. Hierbei wird eine Summe über alle Einzelwerte gebildet und diese durch die Anzahl der Werte dividiert. Entsprechend werden hier auch die Nullwerte berücksichtigt."
Um nun aber einen Mittelwert ohne 0 Werte zu berechnen, muss ich bei der Anzahl der Werte die 0 ausschließen. Im oberen Artikel bin ich auf mehrere Varianten eingegangen aber die einfachste Version ist direkt die Formel Mittelwert, um eine Bedingung zu erweitern.
Dabei ist die erste Variante eine mit Excel 2007 eingeführte Formel, der direkt eine Bedingung mitgegeben werden kann, während es sich bei der zweiten Variante um eine Matrixformel handelt, die mit STRG + UMSCH + ENTER abgeschlossen werden muss.
Wie in der Abbildung zu sehen wird, für die Zellen B2 bis B11 wie folgt der Mittelwert gebildet:
= MITTELWERTWENN ( B2:B11 ; "<>0" ; B2:B11 )
Hier wird tatsächlich nur ein Mittelwert gebildet, wenn die einzelnen Zellen keine 0 enthalten. Diese Formel ist ab Excel 2007 verfügbar. Davor war es erforderlich eine Matrixformel zu erstellen. Hierzu wird die Formel
= MITTELWERT ( WENN (B2:B11<>0 ; B2:B11) )
genutzt und muss mit STRG, UMSCHALTTASTE (Shift) und EINGABETASTE (Enter) in eine Matrixformel umgewandelt werden. Wobei ich im ursprünglichen Artikel auch noch weitere Möglichkeiten vorgestellt habe.
Die Formel wird von Excel darauf zwischen zwei geschweiften Klammern { und } als Matrixformel dargestellt. Eine direkte Eingabe der Formel mit geschweiften Klammern vor und hinter der Formel konvertiert die Formel jedoch nicht in eine Matrixformel, weswegen diese direkt über die Tastenkombination abgeschlossen wird. Dieses gilt auch, wenn die Formel nachträglich geändert wird und dann erneut mit STRG + UMSCHALT (Shift) + EINGABETASTE (Enter) abgeschlossen werden muss.
Beispiel: Mittelwert über Einnahmen von mehreren Jahren, wenn Werte vorhanden sind (ohne 0 Nullwerte)
Für das obere Beispiel macht dieses vielleicht betriebswirtschaftlich weniger Sinn, da hier einzelne Kostenstellenverantwortliche durch 0 einfach ignoriert werden, aber ein Beispiel im Hochschulbereich macht vielleicht deutlich wofür dieses genutzt werden kann.
Ein einfaches Beispiel ist dabei die Darstellung der Einnahmen innerhalb eines Jahres:
Hier sind für die Jahre 2017 bis 2021 Werte als Einnahmen ausgewertet. Allerdings liegen für 2021 noch keine vollständigen Zahlen vor. Um nun aber mit einem Mittelwert, zum Beispiel für den Artikel "Excel Pivottabelle Darstellung Grenzwerte Einnahmen auf Projekte je Person durch Zuordnung VZÄ auf verantwortlicher Kostenstelle" zu rechnen, sind nicht vorliegende Zahlen naturgemäß dafür geeignet hier das Ergebnis zu verwässern.
Dank der Formel MITTELWERTWENN wird bei der Berechnung des Mittelwertes der nicht vorhandene Wert in 2021 nicht berücksichtigt, sodass die Einnahmen der Jahre 2017 bis 2020 zur Berechnung des Mittelwertes verwendet. Ein Beispiel für solche Grunddaten können die "Monetäre und Nichtmonetäre Kennzahlen und Statistiken im Hochschulumfeld - Aktuelle Daten von Destatis für Hochschulcontrolling und Berichtswesen" sein.
Beispiel Mittelwert ohne Zwischensummen
Für eine differenzierte Darstellung der Einnahmen werden je Fachbereich die Einzelwerte je Lehreinheit (LE * ) ausgewiesen und als Zwischensumme diese dann auf Ebene der Fachbereiche als Summe zusammengefasst (siehe Spalte B Zelle 2 bis 8).So sind hier nicht nur die Lehreinheit 10101 mit 70.000 und Lehreinheit 10102 mit 30.000, sondern auch die Zwischensumme für Fachbereich 01 mit 100.000 ausgewiesen. Ebenso wird hier mit den Lehreinheiten des Fachbereich 02 LE 10201 bis LE10202 verfahren.
Wird nun über alle Werte ein Mittelwert gebildet per =MITTELWERT( B2:B7 ) würden auch die Zwischensummen für die Berechnung des Mittelwertes berücksichtigt werden, sodass der Mittelwert über die Zwischensummen und Einzelwerte der Lehreinheit bei 53.333,33 liegen würde.
Natürlich könnte ich nun mit =MITTELWERT( B2:B3 ; B5:B6 ) auch nur die relevanten Zeilen der Lehreinheiten von Fachbereich 01 und 02 berücksichtigen, müsste aber bei Einführung einer neuen Lehreinheit stets die Formel anpassen.
Syntax MITTELWERTWENN
Eleganter ist hier ebenfalls die Formel MITTELWERTWENN. Optional kann dieser Formel ein drittes Argument mitgegeben werden.Der Syntax der Formel lautet:
= MITTELWERTWENN ( Bereich , Kriterien, [Mittelwert_Bereich])
Bisher haben wir nur ein Kriterium in "<>0" angegeben und keinen gesonderten Mittelwert_Bereich.
Die Kriterien funktionieren wie eine Suchfunktion und können einen Zellwert als Bezug haben und auch mit Platzhaltern arbeiten ? wird dabei als ein Zeichen und * als Zeichenkette genutzt. Soll nach den Zeichen ? oder * gesucht werden, ist eine Tilde ~ , vergleichbar zum Fluchtzeichen # bei SAP, als Maskierungszeichen für dies Sonderzeichen zu verwenden.
Dieses ist im Abschnitt "Kurztext mit # als gesperrt - Maskierungskennzeichen #, + und *" im Artikel "Auswertung für Planbuchungen gesperrter CO Objekte (Kostenstelle oder Innnenauftrag) über interaktive Stammdatengruppen (Selektionsvarianten KS13 und KOK5) und Maskierungskennzeichen in SAP" näher beschrieben.
Ferner können Kriterien auch Operatoren wie > 100 oder auch Bezüge zu Zellen B42 sein.
In oberen Beispiel ist durch die Formel
= MITTELWERTWENN ( A2:A7 ; "LE*" ; B2:B7 )
In der Spalte A (Bereich A2:A7) nach Kriterien Zeilen beginnend mit LE* gesucht worden und hier ein Mittelwert über Spalte B (Mittelwert_Bereich B2:B7) herangezogen worden.
Damit ist auch nur der Mittelwert über die Lehreinheiten mit 40.000 korrekt berechnet worden.
MITTELWERTWENN mit mehreren Kriterien
Neben der Formel MITTELWERTWENN gibt es auch die Formel MITTELWERTWENNS. Hier besteht dann die Möglichkeit mehrere Kriterien zur Berechnung eines Mittelwertes zu hinterlegen.Der Syntax lautet:
MITTELWERTWENNS(Mittelwert_Bereich; Kriterien_Bereich1; Kriterien1; Kriterien_Bereich2; Kriterien2; ...)
Insgesamt können hier bis zu 127 Kriterien angegeben werden.
Als Beispiel könnte ich mir eine Berechnung eines Mittelwertes je Cluster von Lehreinheiten und Mittelgeber bei Drittmittelprojekten vorstellen.
Anhand folgender Abbildung dürfte das klarer werden:
Hier soll eine Summe über die Einnahmen in Spalte D gebildet werden. Dieses ist der Mittelwert_Bereich von D2 bis D7.
Dabei soll als erstes Kriterium das Cluster der Lehreinheit berücksichtigt werden. Dieses entspricht den Kriterien_Bereich1 von B2 bis B7.
Als Kriterium ist hier CL01 bzw. die Zelle B12 genommen worden.
Neben dem Cluster CL01 soll aber auch die Mittelherkunft (Mittelgeber) berücksichtigt werden. Hier ist der Kriterien_Bereich2 von C2 bis C7 relevant.
In unseren Fall sollen die Bundesmittel (Mittelgeber BUND bzw. Zelle C12) als Kriterien2 definiert.
Somit berechnet sich der Mittelwert über alle Bundesmittel im Cluster 01 durch die Formel
=MITTELWERTWENNS( D2:D7 ; B2:B7 ;B12 ; C2:C7 ; C12)
Entsprechend umfangreichere weitere Kriterien zur Berechnung können hier ebenfalls herangezogen werden. Vergleichbar lässt sich auch die Formel SUMMEWENN (siehe Artikel "SUMMEWENN über mehrere Spalten in Excel oder Personalkostenhochrechnung auf Innenauftrag zusammenfassen" als SUMMEWENNS mit mehreren Kriterien und Kriterienbereichen erweitern.
Fazit
Neben der erwähnten Variante mit festen Bedingungen kann natürlich auch im Rahmen eines Dashboards der Mittelwert abhängig von einer Dropdownliste (siehe Artikel "Formulare gestalten in Excel" ) ausgewählt werden.Auch abgesehen davon können solche Formeln, wie auch die Verweisfunktionen (siehe "Grundlagen in Excel Verweisfunktionen SVERWEIS WVERWEIS und VERWEIS" oder die im Artikel "Index und Vergleich statt SVERWEIS endlich verstanden und Suche über Verweis nur, wenn es auch etwas zu finden gibt" beschrieben Funktionen weiter helfen.
Persönlich mag ich sogenannte Matrixformeln sehr, was auch im Artikel "Gleichzeitiges Transponieren und Verknüpfen von Inhalten in Excel per Formel MTRANS am Beispiel Leistungsmengen und Leistungsabgeltung aus Haushaltsplänen im Vergleich" deutlich wird.
Oft hat man sich aber auf ein bestimmtes Set an Formeln festgelegt, sodass neue Formeln in Excel, vielleicht gar nicht direkt auffallen, daher freue ich mich auch immer wieder darüber, wenn scheinbare Grundlagenartikel auch wieder den Arbeitsalltag erleichtern.
Eine entsprechende positive Rückmeldung erhalte ich auch seit Jahren zum Artikel "Prozentuale Veränderung bei negativen Zahlen in Excel" :-). Insgesamt freue ich mich jedes Mal aufs Neue, wenn auch zu älteren Artikeln spannende Fragen und Möglichkeiten der Erweiterung hinzukommen.
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.
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.
Aktuelle Schulungstermine Rechercheberichte mit SAP Report Painter
unkelbach.link/et.reportpainter/
Keine Kommentare - Permalink - Office