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.
ein Angebot von Espresso Tutorials
unkelbach.link/et.books/
unkelbach.link/et.reportpainter/
unkelbach.link/et.migrationscockpit/
Diesen Artikel zitieren:
Unkelbach, Andreas: »Excel Arbeitsmappen vom Wert in der Summenzelle per Klick auf Einzelposten per HYPERLINK« in Andreas Unkelbach Blog (ISSN: 2701-6242) vom 16.8.2021, Online-Publikation: https://www.andreas-unkelbach.de/blog/?go=show&id=1229 (Abgerufen am 19.5.2024)
Permalink - Office
Artikel datenschutzfreundlich teilen
🌎 Facebook 🌎 Twitter 🌎 LinkedIn