Andreas Unkelbach
Logo Andreas Unkelbach Blog

Andreas Unkelbach Blog

ISSN 2701-6242

Artikel über Controlling und Berichtswesen mit SAP, insbesondere im Bereich des Hochschulcontrolling, aber auch zu anderen oft it-nahen Themen.


Werbung


Montag, 16. August 2021
21:27 Uhr

Excel Arbeitsmappen vom Wert in der Summenzelle per Klick auf Einzelposten per HYPERLINK

Je nachdem mit welchen Themen ich mich aktuell beschäftige wandern hier auch im Blog unterschiedliche, sodass diese Woche auch das Thema Excel hinzugekommen ist.

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).

Personalkosten

Ferner sind auch die Gebäudekosten vergleichbar nach Bewirtschaftung und Bauunterhalt aufgelistet.

Bauunterhaltung und Bewirtschaftung

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.

HNF - Raumnutzung je Kostenstelle

Für die Personalkosten die Vollzeitäquivalente (VZÄ), wie in folgender Abbildung ersichtlich ist.

VK - Fulltime enviroment Vollzeitkraft
 

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.

Budgetberechnung auf Basis von Kosten Vorjahr und geplanten Kennzahlen

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).

Direkte Zellbearbeitung nicht zulassen

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"
oder auch auf Netzlaufwerke.


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:

HYPERLINK auf aktuelle Daten

Die einzelnen Formeln lauten:
HYPERLINK aktuelle Daten
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.

Hyperlink auf Budgetberechnung

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:

Budgetberechnung mit HYPERLINK auf Datenherkunft
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.

Bearbeitung der Formel HYPERLINK

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.

Excel Formatvorlagen

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:

Beispiel Datenschnitt - Slicer

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.

Hinweis: Aktuelle Buchempfehlungen besonders SAP Fachbücher sind unter Buchempfehlungen inklusive ausführlicher Rezenssionen und Bestellmöglichkeit zu finden.
Espresso Tutorial - die digitale SAP Bibliothek

Tags: Excel

Keine Kommentare - - Office

Artikel datenschutzfreundlich teilen

🌎 Facebook 🌎 Twitter 🌎 LinkedIn


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 26.9.2021)

Diesen und weitere Texte von finden Sie auf http://www.andreas-unkelbach.de


Keine Kommentare

Kommentieren?


Beim Versenden eines Kommentars wird mir ihre IP mitgeteilt. Diese wird jedoch nicht dauerhaft gespeichert; die angegebene E-Mail wird nicht veröffentlicht: beim Versenden als "Normaler Kommentar" ist die Angabe eines Namen erforderlich, gerne kann hier auch ein Pseudonyme oder anonyme Angaben gemacht werden (siehe auch Kommentare und Beiträge in der Datenschutzerklärung).

Eine Rückmeldung ist entweder per Schnellkommentar oder (weiter unten) als normalen Kommentar möglich. Eine persönliche Rückmeldung (gerne auch Fragen zum Thema) würde mich sehr freuen.

Schnellkommentar (Kurzes Feedback, ausführliche Kommentare bitte unten als normaler Kommentar)

Name (sofern kein Name angegeben wird erscheint Anonym):



Ich nutze zum Schutz vor Spam-Kommentaren (reine Werbeeinträge) eine Wortliste, so dass diese Kommentare nicht veröffentlicht werden. Sollte ihr Kommentar nicht direkt veröffentlicht werden, kann dieses an einen entsprechenden Filter liegen.

Im Zweifel besteht auch immer die Möglichkeit eine Mail zu schreiben oder die sozialen Medien zu nutzen. Meine Kontaktdaten finden Sie auf »Über mich« oder unter »Kontakt«. Ansonsten antworte ich tatsächlich sehr gerne auf Kommentare und freue mich auf einen spannenden Austausch.
Name:

E-Mail (wird nicht veröffentlicht und ist nicht erforderlich):

Homepage:

Kommentar:





* Amazon Partnerlink/Affiliatelinks/Werbelinks
Als Amazon-Partner verdiene ich an qualifizierten Käufen über Amazon.
Weitere Partnerschaften sind unter Onlineshop und unter Finanzierung und Transparenz aufgeführt. Hinauf






Logo Andreas-Unkelbach.de
Andreas Unkelbach Blog
ISSN 2701-6242

© 2004 - 2021 Andreas Unkelbach
Gießener Straße 75,35396 Gießen,Germany
andreas.unkelbach@posteo.de
Andreas Unkelbach

Stichwortverzeichnis
(Tagcloud)


Aktuelle Infos (Abo)

Facebook Twitter XING Linkedin

Amazon Autorenwelt Librarything

Berichtswesen im SAP®-Controlling von Andreas Unkelbach

Espresso Tutorials

Onlinezugriff auf alle Medien

SAP Weiterbildung & Online Training

Privates

Kaffeekasse 📖 Wunschliste