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



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"

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:



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.



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.

Im Artikel "Excel: Verschiedene Möglichkeiten einen Mittelwert über Zahlen ohne Nullwerte zu ermitteln" habe ich unterschiedliche Möglichkeiten zur Berechnung eines Mittelwertes ohne Nullwerte beschrieben. Dabei bin ich sowohl auf die Kombination von MITTELWERT und WENN als auch direkt die Formel MITTELWERTWENN eingegangen,

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.

Für eine Onlineschulung habe ich die praktischen Übungen, wie im Artikel "Videoaufzeichnung für Dokumentationen oder Onlineschulungen von Software nicht nur für elearning oder digitale Lehre (openShot, OpenBrodcastStudio, XMIND)" beschrieben, als Video aufgezeichnet und auf eine Folie in Powerpoint eingebunden.

Powerpoint als PDF exportieren - aber was passiert mit Videos?

Wenn ich nun jedoch die gesamte Präsentation per

• Datei
• Exportieren
• PDF/XPS Dokument erstellen

wird eine PDF mit allen Layout, Formatierungen, Schriftarten und Bildern erstellt, aber statt der Videos wird im PDF nur der erste Frame des Videos eingefügt.

Daher habe ich die einzelnen Schulungsvideos bisher mit Titelbild innerhalb OpenShot  beschnitten und daraus ein neues Video erstellt.

Videos in Powerpoint einbinden

Für eine neue Schulung erstellte ich mir nun jedoch eine Vorlage in der ich die einzelnen Videos einbinden kann.



Ein Video kann als Objekt beziehungsweise über die Symbolleiste Einfügen und dort in der Befehlsgruppe Medien über die Schaltfläche Video vom lokalen Gerät (Dieses Gerät) eingebunden werden.

Ein Klick auf das Video ermöglicht über die Symbolleiste Wiedergabe unter den Videooptionen "Start auf Automatisch zu stellen. Ferner lässt sich hier auch die Lautstärke anpassen sollten Sie aus Versehen Nebengeräusche aufgenommen haben.

Powerpoint als Video exportieren

Nun habe ich den Vorteil, dass ich ein Video in der Präsentation habe und könnte unter

• Datei
• Exportieren
• Video erstellen auswählen

Bisher war mir nur bekannt, dass beim Export entweder eine feste Anzeigedauer für die einzelnen Folien oder aufgezeichnte Zeitabläufe genutzt werden konnten.

Dieses ist tatsächlich sehr praktisch, da hier direkt eine Präsentation eingesprochen werden kann und damit quasi der komplette Vortrag aufgezeichnet wird.

Wenn jedoch keine Zeitabläufe aufgezeichnet sind kann unter Video nicht nur das Videoformat

• Ultra HD (4K) (3840x2160)
• Full HD  (1080p) (1920x1080)
• HD (720P) (1280x720)
• Standard (480p) (852x480)

mit unterschiedlichen Dateigrößen und Qualitätsstufen ausgewählt werden sondern auch die

• Anzeigedauer der einzelnen Folien in Sekunden (05,00)

Hier dachte ich bisher, dass dieses auch für Folien mit Videos so gehandhabt wird.

Allerdings ist die Videolänge hier dann der  Länge der Aufzeichnung entsprechend, so dass ich die Folien mit Videos ergänzend zu der PDF Datei meines Vortrages ergänzen kann und ich mit wenig Aufwand auch Videodateien mit Titel und sogar Layout ergänzend zur Verfügung stellen kann.

Gerade fürs Web dürften die Unterschiede nicht allzu relevant sein.

Dennoch habe ich einmal auf Youtube die gleiche Präsentation in der niedrigsten und der höchsten Variante exportiert. Wobei das in Powerpoint eingebundene Beispielvideo selbst nur eine Auflösung von Full HD (1920x1080) hat, so dass mehr auf die Präsentation drum herum geachtet werden sollte.

Variante Standard 480p (852x480)

Dateigröße: 4,82 MB (5.054.630 Bytes)

Variante HD 720 P (1280x720)

Dateigröße:10,8 MB (11.351.020 Bytes)

Variante Full HD (1920x1080)

Dateigröße:19,2 MB (20.178.216 Bytes)

Variante Ultra HD 4K (3840x2160)

Dateigröße:62,9 MB (66.024.733 Bytes)



Die Empfehlung von Microsoft bei der Auswahl der Formate sind:
 

Formatauswahl Export Video Powerpoint Formate

Format	Auflösung	Dateigröße	Anzeige auf (zur Orientierung)
Ultra HD (4K) nur Win10	3840 x 2160	maximale Dateigröße	Große Monitore, Fernseher
Full HD (1080p)	1920 x 1080	große Dateigröße	Computer- und HD-Bildschirme
HD (720p)	1280 x 720	mittlere Dateigröße	Internet und DVD
Standard (480p)	852 x 480	kleinste Dateigröße	Tragbare Geräte, Tablet, Smartphone

 
Zumindest als Orientierung ist dieses sicher eine gute Ausgangslage.


 

Youtube Videos datenschutzkonform einbinden

Datenschutz und Datenübertragung nach Youtube sind durchaus zu vereinbaren, auch wenn das etwas Mehraufwand verursacht.
 

Video bei Youtube hochladen und Datenschutzmodus

Abseits davon stellt sich die Frage tatsächlich an welcher Stelle die Videos später verwendet werden sollen. Inwieweit hier Youtube noch Anpassungen vornimmt ist eine andere Frage.

Nebenbei beim Einbinden ins Blog für Videos bei Youtube nutze ich auch gerne die Option "datenschutzgerechte Einbindung".

Dazu ist bei Youtube die Schaltfläche Teilen zu wählen:



Neben verschiedenen sozialen Netzwerken kann hier durch die Option "Einbetten" das Video per HTML Code in die eigene Seite eingebunden werden.



Ein in meinen Augen wichtiger Punkt ist hier bei den Optionen zum Einbetten folgende Schaltflächen zu aktivieren:



Damit wird das Video von der Domain youtube-nocookie.com statt youtube.com eingebunden und es wird darauf hingewiesen, dass YouTube keine Informationen über die Besucher auf der Webseite gespeichert werden, es sei denn Sie sehen sich das Video an und stimmen damit direkt zu.

Die Einbindung der Player-Steuerelemente ist ebenfalls sinnvol, aber gerade der Datenschutzmodus dürfte hier entscheidend sein. Es gibt auch noch andere Videodienste, aber Youtube dürfte hier am verbreitesten sein.

Youtube in Wordpress einbinden

Auch Wordpress, als verbreitetes CMS hat die Möglichkeit Youtube Videos als Block im Editor einzubinden.



Allerdings erfolgt hier die Einbindung nicht datenschutzkonform. Daher habe ich hier ein weiteres Plugin in einer Wordpres Installation eingebunden:

Wordpress Plugin WP YouTube Lyte

WP YouTube Lyte Von Frank Goossens (futtta)
URL: https://de.wordpress.org/plugins/wp-youtube-lyte/

Hier konnte ich folgende Einstellungen vornehmen:



Insbesondere durch die Option
Sollen die Vorschaubilder lokal zwischengespeichert werden?
ermöglicht das lokale Zwischenspeichern der Vorschaubilder die Privatsphäre des Besuchers zu verbessern, da standardmäßig keine Anfragen an YouTube geschickt werden bevor das Video abgespielt wird.

Praxisbeispiel:
https://www.kirche-miteinander.de/es-war-einmal-krippenspiel-2020/

Peer von selbststaendig-im-netz.de hat im Artikel "YouTube-Videos datenschutzgerecht einbauen – DSGVO WordPress Video-Plugin" das Plugin noch näher vorgestellt und einige weitere Einstellungen und Anpassungen erläutert :-).

Fazit

Die einzelnen Videoaufzeichnungen habe ich mit Folie_Nummer_Titel-Video.mp4 gespeichert und gebe diese gemeinsam mit der PDF Datei als Schulungsunterlagen an die Teilnehmende weiter.

Als MP4 Format habe ich hier FULL HD gewählt, dass als Format für Computer und HD Bildschirme empfohlen wird. Ich gehe zumindest nicht davon aus, dass die Schulungsunterlagen an einen 4 K Bildschirm betrachtet werden . Grundsätzlich wären auch die kleineren Formate geeignet, aber so bin ich mir sicher, dass alle Details auch gut ersichtlich sind.

Inklusive eingesprochener Stimme bei einer Aufzeichnung der Präsentation könnte auch die gesamte Schulung als Video angeboten werden (bspw. für eine reine Onlineschulung) aber als Handout fällt es mir persönlich manchmal auch leichter hier in der Präsentation noch einmal nachlesen zu können und die einzelnen Übungen separat betrachten zu können.

Neben der Darstellung in Form von Video ist auch das Thema Bilder immer noch einen tieferen Blick wert. Unter "Manchmal sagt ein Bild mehr als tausend Worte 🎨 Screenshots unter Windows erstellen und für Web optimieren (Hardcopy, Irfanview, Windows Snipping Tools und RIOT Optimizer)" ist auf dieses Thema ausführlicher eingegangen worden. :)


Gerade rund um die Gestaltung von Powerpoint nutze ich dies mittlerweile sehr gerne für Vorträge (auch wenn freie Vorträge, wie bspw. am Whiteboard immer wieder beeindruckend sind). Selbstverständlich gibt es auch andere Präsentationslösungen aber nach einiger Eingewöhnung finden sich viele der hier vorgestellten Methoden auch in einer anderen Software wieder.

2016 hatte ich, auch durch aktuelle Veränderungen bzgl. Lernplattformen, im Artikel "Grundlagen und Empfehlungen rund um Powerpoint oder auch andere Präsentationen" und diese immer weiter entwickelt und mir zum Beispiel durch eine Gliederung wie im Artikel "Powerpoint Smart Art als Inhaltsverzeichnis mit Powerpoint für Agenda, Abschnitte und Fortschritt innerhalb der Präsentation" auch direkt eine Struktur für die eigene Vorträge gegeben indem hier immer vier Oberthemen vorgegeben sind.

Neben der Strukturierung in XMIND (siehe "Mindmapping und Sketchnotes im Beruf nutzen für Brainstorming oder Mind Mapping mit XMIND") glaube ich, dass dieses sich auch positiv auf die Vorträge als Dozent im Rahmen "Berichtswesen mit SAP Controlling" auswirkt.
 

Ausgangslage:

Manchmal kann es, sei es für Beispiele hier im Blog oder für eine Anonymisierung einer Statistik, erforderlich sein Daten zu anonymisieren oder zumindest zu pseudonymisieren.

Hier gibt es unterschiedliche Formeln in Office je nachdem ob diese in Excel oder Access genutzt werden sollen.

Zufallszahlen in Excel

Für meine Beispielzahlen in Excel verwende ich meistens entweder die Formel ZUFALLSZAHL() oder ZUFALLSBEREICH(Untere_Zahl;Obere_Zahl) um hier entsprechende Zahlen automatisch errechnen zu lassen.

Dabei generiert Excel diese Zahlen basierend auf  den Mersenne Twister-Algorithmus (MT19937 siehe "Eintrag auf Wikipedia") wodurch Pseudozufallszahlen die als zufällig erscheinen generiert werden.

In einer Datentabelle mit zehn Einträgen (von ID 1 bis 10) und jeweils einen Wert von 100 werden dazu je folgende Zufallszahlen generiert.



Spalte Zufallszahl

Durch die Formel =RUNDEN(ZUFALLSZAHL()*100;2) wird durch ZUFALLSZAHL() eine Zahl größer oder gleich 0 aber kleiner als 1 generiert und diese mit 100 multipliziert und auf 2 Nachkommastellen gerundet.

Spalte Zufallsbereich

Der Zufallsbereich wird durch die Formel =ZUFALLSBEREICH(1;100) definiert als ganzzahlige Zahl (ohne Nachkommastellen) zwischen 1 und 100.

So sind in Excel relativ schnell entsprechende "Zufallszahlen" können dann als Werte für Beispiele herangezogen werden.

Sinnvollerweise werden diese dann kopiert und als Inhalte Einfügen als Werte wieder eingefügt, andernfalls aktualisieren sich diese Zahlen jedes Mal aufs Neue sobald eine andere Berechnung im Tabellenblatt erfolgt.


Ein Grund warum Controller Excel mögen, dürfte wohl auch daran liegen, dass hier schnell gute Ergebnisse erreicht werden können. 


 

Zufallszahlen in Access

Zur identischen Datenzeile ID 1 bis 10 und jeweils die Werte 100 je Datenzeile möchte ich nun aber ebenfalls Zufallszahlen erhalten.

Dazu gibt es in Access die an der Formel RND in VBA angelehnte Formel ZZG() die in Abfragen verwendet werden kann.

Wird diese Formel ohne Parameter (also nur als ZZG() ) verwendet, so wird als Basis zur Berechnung von Zufallswerten der Systemzeitgeber  als Startwert zur Berechnung eines Zufallswertes verwendet. Allerdings wird für jeden Zufallswert der gleiche Startwert verwendet, so dass in der Abfrage die Zufallswerte wie folgt in der Spalte ZZG ausgegeben werden:



Hier sind also für jede Datenzeile der gleiche Zufallswert ausgegeben, was nun zwar eine Anonymisierung aber nicht das gewünschte Ergebnis von unterschiedlichen Zufallswerten je Datenzeilen verursacht. Die Zahlen ändern sich zwar bei jeder erneuten Abfrage, bleiben aber für jeden Eintrag identisch.

Daher habe ich nun versucht mit zwei Schritten die Abfrage etwas anzupassen um letzten Endes doch noch Zufallszahlen je Eintrag zu erhalten.



Insgesamt habe ich hier zwei weitere Felder eingefügt auf deren Ergebnis ich nun eingehen mag.

Spalte ZZG Festwert

ZZG Festwert: RUNDEN ( ZZG(1) * 100 );2 )

Hier gebe ich der Funktion ZZG den Startwert 1 und multipliziere dieses mit 100 um dann die Zufallszahl auf 2 Nachkommastellen zu runden.

Ausgeführt liefert die Abfrage folgende Ausgabe:



Im Ergebnis erhalte ich nun gut lesbare Zahlen pro Eintrag, aber da hier nun statt des Systemzeitgeber eine feste Zahl verwendet worden ist, ist das Ergebnis leider erentu identisch. Beim erneuten Ausführen der Abfrage erhalte ich wiederum neue Werte, die aber ebenfalls fix sind.

Spalte ZZG Bezug

ZZG Bezug: RUNDEN ( ZZG( [Zahl] ) * 100 );2 )

Hier lese ich nun den Wert in der Spalte Zahl aus um diese als Startwert zu nehmen. Auch wenn die Zahlen jeweils identisch sind, wird hier doch für jede Zeile eine neue Zufallszahl berechnet, so dass die Werte in der Spalte ZZG Bezug nun endgültig unterschiedlich sind.

Verschachtele WENN Dann Funktion mit Zufallszahlen

Die erhaltenen Zufallszahlen können dann auch für weitere Berechnungen oder zufällige Wertausgaben genutzt werden. Dieses kann zum Beispiel für "Ja", "Nein" aber auch für komplexere Ausgaben wie die Auswahl aus drei Optionen "Tor A", "Tor B" oder "Tor C" genutzt werden.

Im Beispiel soll hier "Bestanden", "Nicht bestanden" oder "Neuer Versuch per Zufall auf die einzelnen Einträge verteilt werden.

Dazu habe ich folgende Spalten in die Abfrage eingefügt:

• ZZG Bezug: Runden(ZZG([Zahl])*10;2)
  
  Damit wird für ZZG Bezug ein Wert zwischen 0 und 10 ausgegeben.
   
• Ergebnis: Wenn([ZZG Bezug]<3;"Nichtbestanden";Wenn([ZZG Bezug]<7;"Neuer Versuch";"Bestanden"))
  
  Als Ergebnis wird hier bis 3 "Nichtbestanden", bis 7 "Neuer Versuch" und ansonsten "Bestanden" ausgegeben.

Das Ergebnis der Abfrage sieht dafür dann wie folgt aus.



Natürlich können hier auch größere Bandbreiten durch Erhöhung der Abstände ZZG Bezug bis 100 oder auch andere Bestandteile für die Wenn Abfrage genommen werden.

Fazit

Auch wenn in Access die Berechnung des Zufalls etwas umständlicher ist, können so doch auch pseudozufällige Verteilungen der Ausprägung eines Merkmals generiert werden und damit etwaige Anonymisierungen von Statistiken erfolgen.

Immerhin sind solche Fragen, wie auch die Konverttierung einer "als Tabelle formatierten" Datengrundlage in einen Bereich wie im Artikel "Daten in Excel intelligenter formatieren 📊 als intelligente Tabellen bzw. als Tabelle formatieren" beschrieben immer mal wieder Grundlage für spannende Artikel.

 

Schon im Artikel "Als Tabelle formatieren" um eine dynamische Datenquelle für Pivot-Tabellen zu erhalten" habe ich die für mich sehr nützliche Funktion Tabellen als "intelligente Tabellen" zu formatieren vorgestellt. Gerade im Zusammenspiel mit einer Pivot Tabelle sind diese sehr praktisch, da damit die Grunddaten einer Tabelle automatisch erweitert werden, wenn weitere Zeilen an diese Tabelle hinzugefügt werden.

Allerdings haben diese Tabellen auch noch weitere Vorzüge auf die ich gerne eingehen mag.

Daten als Tabelle formatieren

Der erste Schritt ist, dass ich die Tabelle entweder durch die Schaltfläche "Als Tabelle formatieren" (siehe 1 in unterer Abbildung) oder durch die Tastenkombination STRG und T in eine formatierte Tabelle umwandele. Wichtig ist, dass die bisherige Tabelle hier komplett erfasst ist und die Option Tabelle hat Überschriften (siehe 2 in unterer Abbildung) markiert ist.

Datentabelle Namen zuweisen

Damit sieht die Tabelle nicht nur wesentlich hübscher aus sondern kann, wenn man eine Zelle in der Tabelle markiert hat über die Tabellentools im Ribbon Entwurf (1 in unterer Abbildung) einen eigenen Namen (unter 2 in unterer Abbildung) erhalten.



Statt Tabelle1 kann dieser Name zum Beispiel Grunddaten_Drittmittel sein. Dieser Name kann dann als Datenherkunft einer Pivot-Tabelle verwandt werden.

Ergebniszeile bei als Tabelle formatierte Daten

Ein weiterer Vorteil dieser formatierten Tabelle ist, dass hier auch eine Ergebniszeile (aktiviert durch 1 in folgender Abbildung) markiert werden kann und hier über einzelne Spalten Summen, Anzahl oder andere Ergebnisse (siehe 2 in folgender Abbildung) gebildet werden diese aber nicht in die Datenquelle der Pivottabelle mit übernommen werden.

Dabei wird hier als Option direkt die Funktion TEILERGEBNIS angeboten, so dass hier Anzahl, Summe und diverse andere Optionen ausgewählt werden können.

Mehrere Datenfilter setzen auf einen Tabellenblatt

Besonders wenn mehrere Datentabellen angezeigt werden ist ein weiterer Vorteil der als Tabelle formatierten Daten auch, dass mehr als ein Filter innerhalb eines Tabellenblattes gesetzt werden können.

Zur Verdeutlichung kann hier ein Beispiel der Darstellung nach Fachbereichen und Studenfach genutzt werden.



Auch hier verweise ich gerne auf meinen Artikel "Mehrere Autofilter im Tabellenblatt einer Exceltabelle setzen" mit entsprechenden Hinweisen.

Daten sind bei der Beschriftung ohne Fixieren lesbar

Wenn ich eine längere Tabelle habe ist es beim Scrollen auch ein enormer Vorteil, dass die Spaltenüberschriften der formatierten Tabelle statt der üblichen Spaltenüberschrift erscheint, wie anhand folgender Abbildung zu sehen ist.



Statt der Spalte B bis K werden hier die Überschriften der als Tabelle formatierten Daten beim herunterscrollen dargestellt. Was zumindst für die Empfänger von längeren Berichten sehr praktisch ist.

Datenschnitten bei intelligenten Tabellen

Im Artikel "Pivottabellen ab Excel 2010 dynamischer filtern mit Datenschnitten am Beispiel Hochschulfinanzstatistik" bin ich auf die Möglichkeit von Datenschnitten bei Pivot-Tabellen eingegangen.

Ab der Excel Version 2013 ist dieses auch bei "als Tabelle formatierten" Daten möglich, wodurch auch über solche Tabellen ein Dashboard erstellt werden kann.

Dazu ist aus der aktiven Tabelle über die Symbolleiste "Einfügen" die Schaltfläche "Datenschnitt" aus der Befehlsgruppe Filter  hilfreich.



Hier ist dann eine Auswahl aller Spalten vorhanden über die ein Datenschnitt genutzt werden kann.

Mit Spaltenbezeichnung in Tabellen rechnen

Ein weiterer Vorteil ist, dass bspw. für die Personalkostenhochrechnung hier auch möglich direkt die Personalkosten von Mai bis Dezember als Formel zu schreiben statt auf die einzelnen Spalten bewust zu verweisen.



Die Vorzüge dieser Methode sind im Artikel "SUMMEWENN über mehrere Spalten in Excel oder Personalkostenhochrechnung auf Innenauftrag zusammenfassen" beschrieben.
 

Wie bekomme ich wieder eine "normale" Tabelle?

Manchmal möchte man aber aus der vorherigen Tabelle wieder eine "normale" Tabelle bekommen. Dieses ist in den "Tabellentools" möglich.

Hierzu reicht ein Klick auf die "intelligente Tabelle" und nun auf die Symbolleiste Entwurf.



Über die Schaltfläche "In Bereich konvertieren" kann die Tabelle wieder in einen normalen Bereich umgewandelt werden.

Die vorher genutzten Formeln mit Spaltennamensbezug passen sich auch entsprechend an, so dass in der Formel wieder =Summe(F3:M3) statt =SUMME(T_Hochrechnung[@[Mai]:[Dezember]]) zu lesen ist.Allerdings sind dann auch alle schönen Funktionen wie Datenschnitte entfernt.
 

Fazit

Nicht nur bei Pivot Tabellen sondern allgemein im Berichtswesen möchte ich eigentlich auf diese Funktion nicht mehr verzichten selbst wenn es manchmal zur Verwirrtung führt :-)

Vielleicht hilft ja diese kleine Sammlung an Vorteilen dabei noch mehr Menschen von dieser praktischen Funktion in Excel zu überzeugen.



Die Schaltfläche "Als Tabelle formatieren" hilft mir zumindest bei Berichten ebenso weiter wie die Formatvorlagen um hier im Design direkt darauf hinzuweisen, wo eine Berechnung, Eingabe oder eine Ausgabe erfolgt :-) Gerade zum "Vorteil von Excel Formatvorlagen und Filter nach Farben oder Zellensymbolen aus bedingter Formatierung" und ansprechende Berichte helfen dann auch sehr gut (Finanz)Daten verständlich aufzubereiten und mahcen gerade im Berichtswesen dann auch Daten auf einen Blick verständlicher.