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.

 

Neben diverser Berichtstool innerhalb des SAP-Systems ist, insbesondere im Controlling, die Tabellenkalkulation von Microsoft Excel hilfreich und findet eine Vielzahl von Anwendungsmöglichkeiten. Gerade bei der Aufbereitung von Geschäftsdaten, regelmäßigen Berichten oder ganze Dashboards zur Datenaufbereitung haben sich auch bei mir die letzten Jahre die Möglichkeiten von Excel insbesondere auch von Pivot Tabellen bewährt und ich freue mich immer hier neues lernen zu können.

Entsprechend neugierig war ich auf den von Lukas Rohr (excelnova.org) angebotenen Kurs zum Thema "Daten analysieren mit MS Excel Pivot Tabellen".



* Affiliate-Link / Werbung

Da die Schulung selbst sehr umfangreich war habe ich diesen Artikel ebenfalls in mehrere Abschnitte aufgeteilt:

• "Zielgruppe für diesen Onlinekurs"
• "Buchung und Bezahlung"
• "Vorstellung der Lernplattform und Vorteile eines Onlinekurses"
• "Wer hält diesen Onlinekurs?"
• "Wie ist die Schulung aufgebaut?"
  Dabei gehe ich auf die für mich spannende Inhalte je Kapitel ein:
  • Kapitel 1: Was Du lernen wirst und wie Du diesen Kurs am besten durchgehst
  • Kapitel 2: Die erste Pivot Tabelle erstellen, bearbeiten und anordnen
  • Kapitel 3: Daten Analyse automatisiert durchführen
  • Kapitel 4: Unterschiedliche Datenquellen mit Pivot Tabellen verbinden
  • Kapitel 5: Pivot Tabellen anordnen, formatieren und Zeitsparen
  • Kapitel 6: Die Informationen einblenden und zeigen die interessant sind
  • Kapitel 7: Dem Leser die Möglichkeit geben selber zu analysieren
  • Kapitel 8: Informationen Zusammenfassen und Gruppieren
  • Kapitel 9: Pivot Diagramme Erstellen
  • Kapitel 10: Visuell glänzen mit bedingten Formatierungen in Pivot Tabellen
  • Kapitel 11: PIVOTDATENZUORDNEN Formel
  • Kapitel 12: Dashboards - Pivot in der Berichterstattung
• "Teilnahmebescheinigung/Zertifikat"
• "Hat es sich gelohnt?"

Ich habe mich sehr darüber gefreut an dieser Schulung teilnehmen zu können und der Onlinekurs war auch umfangreicher als ich es erwartet hätte. Aber nun möchte ich der Reihe nach die für mich relevanten Punkte vorstellen.
 

Zielgruppe

Gerade bei Kursen zu Microsoft Excel stellt sich oftmals die Frage, an welcher Stelle hier die Personen abzuholen sind. Hier bauen die einzelnen Kapitel des Kurses aufeinander auf, so dass nicht nur Poweruser, sondern auch Menschen ohne Vorkenntnisse sich gut in das Thema einarbeiten kann. Hilfreich ist es allerdings, wenn vorher schon einmal ein Diagramm erstellt worden ist, einfache Formeln geschrieben worden sind oder die Filterfunktion von Tabellen genutzt worden ist.

Buchung des Kurses

Der Kurs selbst findet auf einer Onlinelernplattform statt und kann über "Daten Analysieren mit Pivot Tabellen"* gebucht werden. Nach der Registrierung und damit verbundene Anlage eines Schulungsaccount kann als Zahlungsmittel eine Kreditkarte (Visa, Master Card oder American Express) genutzt werden. Für Teams und Unternehmen besteht auch die Möglichkeit sich direkt an den Schulungsanbieter zu wenden und per Mail ein individuelles Angebot anzufragen.

Positiv aufgefallen ist mir, dass hier auch eine "30 Tage Geld zurück Garantie" geboten wird, sollte der Kurs nicht den Erwartungen getroffen haben. Insgesamt kann der Kurs ein ganzes Jahr nach Kaufdatum genutzt werden, was gerade bei IT Schulungen ein großer Vorteil ist, da man hier schnell erneut in die einzelne Lektion schauen kann, wenn man in der beruflichen Praxis auf ein im Kurs behandeltes Problem landet. 

Lernplattform und Vorteile eines Onlinekurses

Die Vorteile einer Onlineschulung ist neben der freien Zeiteinteilung (und Ersparnis von Reisekosten beziehungsweise der Anreise zum Schulungsort) gegenüber einer Präsenzschulung, dass hier ein eigenes Tempo festgelegt werden kann um die einzelnen Lektionen durchzugehen, die Übungen nachzuvollziehen und manchmal auch das Video noch einmal zurück zu spulen bzw. zwischen den einzelnen Lektionen noch einmal hin und her zu springen.

Dabei kann diese Schulung sowohl von unterwegs am Smartphone oder Tablet als auch am eigenen Rechner durchgeführt werden. Es ist lediglich eine Internetverbindung erforderlich und die Zeit für die einzelnen Lektionen.

Die Schulungsvideos sind zwar auf Office 2019 (Office 365 Abo) aufgezeichnet worden, jedoch sind die einzelnen Lektionen auch auf älteren Office- bzw. Excelversionen nachvollziehbar. Bei Funktionen die nur auf der aktuellsten Office Version funktionieren wird auch erläutert, wie diese in den vorherigen Versionen umgesetzt werden kann.

Neben kurzen Schulungsvideos (meistens zwischen 2 und am Ende einmal 24 Minuten lang) bietet dieser Kurs auch die einzelnen Übungsdateien zum Download an, sowie einzelne PDF mit Zusammenfassungen und Tipps im Umgang mit den gelernten Funktionen. Dabei handelt es sich um Auszüge aus den ebenfalls vom Dozenten angebotenen eBook "101 PowerUser Tipps" die noch einmal auf einer Seite das Gelernte erweitern oder auch prägnant zusammenfasen.

Einzelne Abschnitte werden zusätzlich noch durch ein Quiz aufgelockert, so dass hier am Ende das Gelernte kontrolliert werden kann und teilweise auch Transfer in konkrete Aufgabenstellungen möglich ist.



Nach Beantwortung der einzelnen Fragen wird nicht nur bestätigt, ob man bei der Beantwortung richtig lag, sondern auch eine Rückmeldung mit Erläuterung zur richtigen Lösung gegeben.
 

Kursleitung/Dozent Lukas Rohr

Gerade bei Schulungen ist es, meiner Erfahrung nach, auch erforderlich, dass die Person sympathisch und kompetent die zu lernenden Inhalte erläutern und für das Thema faszinieren kann.

Lukas Rohr gründete 2011 ExcelNova und ist seit diesem Zeitpunkt als Excel Developer tätig. Persönlich lese ich sein Blog auf excelnova.org schon sehr lange und in meiner Erinnerung war es auch eines der ersten Blogs in meiner Wahrnehmung dass sich sowohl durch gute Beschreibungen als auch inhaltlich ganz auf das Thema Excel fokussiert hatte. Neben der Veröffentlichungen im Blog auf seiner Seite bietet er auch Entwicklungen basierend auf Excel und Access die ihren Einsatz in verschiedenste Branchen wie z.B. Banken, Automobilhersteller oder in der Industrie finden.

Interessant war für mich, dass er nich nur als Microsoft Certified Professional (MCP) seine eigene Kenntnisse fortbilden konnte sondern von Microsoft auch als Excel Finanz Modellierungs Exporte 2014 nach Redmond, WA zum Modell Off Gold Class Event eingeladen wurde (siehe sein Beitrag auf "Redmond Experience Recap (und wieder eine Bitte an Euch)"). Daneben lese ich auch gerne seine praxisnahen Artikel im Blog und habe hier schon das ein oder andere für meine eigenen Berichte (und teilweise auch als Anregung für Blogartikel wie unter anderen im Artikel "Pivottabellen ab Excel 2010 dynamischer filtern mit Datenschnitten am Beispiel Hochschulfinanzstatistik") aufgreifen können.

Während der "FICO Forum Infotage 2016" in Zürich hatte ich die Gelegenheit abseits der Veranstaltung Lukas Rohr zu treffen und kann hier auch sagen, dass die sympathische, ruhige und kompetente Art auch im direkten Gespräch rüber kommt und sich diese Tugenden auch in der Schulung wieder finden.

Schulungsinhalt

Die Schulung selbst ist in insgesamt 12 Kapiteln aufgebaut, die zwar aufeinander aufbauen, aber zwischen denen auch gesprungen werden kann. Dabei merkt sich das Schulungssystem auch den eigenen Lernfortschritt, so dass hier an der letzten Stelle die Schulung fortgesetzt werden kann. Darüber hinaus besteht hier auch die Möglichkeit einzelne Lektionen zu wiederholen, so dass sich hier die 12 Monate sicherlich auch zum Nachschlagen eignen.

Ich möchte im Folgenden kurz auf einige in meinen Augen Highlights der einzelnen Kapitel eingehen, so dass sich hier ein Überblick über den Kurs verschafft werden kann. Auf der Seite "Daten Analysieren mit Pivot Tabellen"* besteht auch die Möglichkeit am Ende sich einzelne Lektionen als "Gratisvorschau" anzusehen.

Ich versuche für die einzelnen Kapitel die für mich interessantesten Punkte einmal zusammenzufassen.

Kapitel 1: Was Du lernen wirst und wie Du diesen Kurs am besten durchgehst

Eine der Fragen von Kolleginnen und Kollegen ist oftmals, wo eigentlich der Vorteil einer Pivot Tabelle bspw. gegenüber der Datenaufbereitung in Access oder in SAP liegt. Hier liefert Lukas Rohr einen Vergleich der Berichtserstellung mit bisherigen Möglichkeiten in Excel (bspw. über die SUMMEWENNS) und der Erstellung einer Pivot Tabelle und der Möglichkeiten die diese Kreuztabellen bieten.

Außerdem gibt es Empfehlungen wie der Kurs am Besten genutzt werden kann und weist dabei auf einen Punkt hin, der mich auch immer wieder darin bestärkt auch Grundlagenschulungen oder Bücher einmal erneut zu lesen. Tatsächlich hat man sich oftmals ein recht unebenes Wissen und kennt manche Funktionen in der Tiefe  aber andere Funktionen die vielleicht sogar die Arbeit wesentlich erleichtern würden sind dann nicht bekannt.

Besonders gefallen hat mir, dass für die gesamte Schulung auch ein Lehrplan als PDF zur Verfügung gestellt wird in dem für die einzelnen Schulungsvideos, PDF und Quiz eine Zeitangabe zu finden ist, so dass sich hier im Vorfeld schon einmal Gedanken darum gemacht werden kann, wie diese Schulung in den persönlichen (beruflichen) Alltag eingebunden werden kann.

Der Kurs umfasst insgesamt etwa 5 Stunden und 26 Minuten, so dass der Kurs theoretisch am Stück absolviert werden kann. Tatsächlich kann ich die Empfehlung nur weiter geben den Kurs in 15 - 20 Minuten Abschnitten aufzuteilen, so dass hier ungestört auf das Material konzentriert werden kann und auch noch etwas Zeit für die Überlegung, wie das Gelernte für die eigene Arbeit umgesetzt werden kann.

An dieser Stelle möchte ich schon vorab auf mein Fazit hinweisen, dass dieser Kurs sehr intensiv ist und ich hier wirklich am Kursende das Gefühl habe, Pivot Tabellen umfassend kennengelernt zu haben und trotz einiger eigener Erfahrungen hier ebenfalls Neues gelernt zu haben.

Durch die Übungsdateien kann sich auch selbst in den vorgezeigten Tabellen eingefunden werden und die Beispiele nachvollzogen werden.

Sollten noch Fragen vorhanden sein, können online auch zu den einzelnen Lektionen Fragen gestellt werden, die dann zeitnah beantwortet werden.

 Kapitel 2: Die erste Pivot Tabelle erstellen, bearbeiten und anordnen

Das zweite Kapitel bietet eine erste Einführung in das Erstellen von Pivot Tabellen. Wer schon einmal mit Pivot Tabellen gearbeitet hat, wird sicherlich überlegen dieses Kapitel zu überspringen, aber gerade Kleinigkeiten wie die Schnellanalyse oder auch das Formatieren von Pivot Tabellen sind dann doch etwas, dass man einfach macht ohne sich über alternative Möglichkeiten der Darstellung oder auch Erleichterungen für den späteren Bericht Gedanken zu machen.

Kapitel 3: Daten Analyse automatisiert durchführen

Neben den berechneten Feldern wird hier auch auf die verschiedenen Möglichkeiten der Auswertungen, die Drilldownfunktionen (sowie die Deaktivierung der Anzeige der Grunddaten per Doppelklick auf ein Pivot Tabellen Feld) aber auch auf das Berechnen einer diskreten Anzahl eingegangen. Gerade bei den berechneten Feldern war es für mich sehr hilfreich hier nicht nur die Vorteile dieser Methode dargestellt zu bekommen, sondern auch die Hinweise auf Nachteile dieser Methode sowie alternative Möglichkeiten zur Berechnung innerhalb der Pivot Tabelle. Dieses hat mich auch direkt dazu gebracht einen recht umfangreichen Bericht an der Arbeit unter diesem Gesichtspunkt noch einmal kritisch zu hinterfragen... :-).

Dabei sind auch für mich bisher unbekannte Anwendungen Szenarien gewählt worden, die einprägsam und gut nachvollziehbar sind.

Kapitel 4: Unterschiedliche Datenquellen mit Pivot Tabellen verbinden

Das Thema Datenquellen in Pivot Tabellen hat mir schon lange Zeit am Herzen gelegen, aber bisher fehlte ein wenig die Zeit sich intensiver mit Datenmodellen auseinanderzusetzen auch wenn mir durchaus bekannt war, dass hier die Möglichkeit besteht mehrere unterschiedliche Grundtabellen miteinander zu verknüpfen und damit auch komplexere Auswertungen durchführen zu können.

Ferner war es auch gerade fürs Berichtswesen sehenswert, wie hier Pivot Tabellen versandt werden können, ohne dass die Grunddaten direkt mitgeliefert werden.

Kapitel 5: Pivot Tabellen anordnen, formatieren und Zeitsparen

Neben bedingter Formatierung in Pivot Tabellen sind hier auch die eigenen Templates als Formatvorlage für Pivot Tabellen ebenso wie die Anordnung von Bereichsfiltern etwas, dass ich gerne für künftige Berichte mit übernehmen möchte.

Im Berichtswesen ist die Präsentation der Daten und das Layout einer Information manchmal ebenso bedeutend wie die dargestellte Daten, so dass hier gleichzeitig viel auf die Datenaufbereitung und Nutzung für die empfangende Person eines Berichtes eingegangen wird.
 

Kapitel 6: Die Informationen einblenden und zeigen die interessant sind

Gerade im Zusammenhang mit Filter auf Pivot Tabellen (bspw. nach Fachbereichen oder Lehreinheiten) stellt sich für mich oftmals die Frage, wie ich diese Tabellen dann an die einzelnen Berichtsempfänger versenden möchte. Hier lieferten die Berichtsfilterseiten eine praktische Antwort. Aber auch das Sortieren nach benutzerdefinierte Listen (als Paradebeispiel fallen mir hier virtuelle Lehreinheiten ein die nicht alphanummerisch aufsteigend sind, aber so dargestellt werden sollen) ist ein Punkt den ich aus diesem Kapitel ebenfalls gerne übernehmen möchte.

Das bei den Filtermöglichkeiten über ein Wert auch ein Mehrfachfilter gesetzt werden kann, war mir bisher noch nicht ganz klar, bietet aber eine sehr elegante Möglichkeit der Datenaufbereitung.

Kapitel 7: Dem Leser die Möglichkeit geben selber zu analysieren

Eine Vorstufe zum Thema Dashboard und interaktive Navigation in Pivot Tabellen ist die Möglichkeit Datenschnitten zu verwenden. Dieses Thema verknüpfe ich tatsächlich stark mit ExcelNova da ich hier zum ersten Mal von Datenschnitten in Excel gelesen habe und seitdem ein großer Fan dieser Technik bin.

Kapitel 8: Informationen Zusammenfassen und Gruppieren

Das Gruppieren von einzelnen Zeilen ist ebenfalls ein Thema, das sich zu vertiefen lohnt. So können hier nicht nur aus Einzelposten Kostenstellen gruppiert werden, sondern auch bei Datumswerten ist es möglich diese nach Monaten, Quartalen oder auch nach Wochen zu gruppieren. Hier ist auch sehr direkt auf etwaige Einschränkungen eingegangen worden was bspw. die Gruppierung nach Halbjahren anbelangt.

Kapitel 9: Pivot Diagramme Erstellen

Auch wenn dieses Kapitel relativ kurzgehalten ist, wird hier doch das Thema umfassend erläutert, so dass hier die Besonderheiten von Pivot Charts gegenüber normalen Diagrammen dargestellt werden.

Hierbei wird auch auf die Objektsteuerung eingegangen und erläutert welche Formen von Diagrammen auf Basis einer Pivot Tabelle erstellt werden können.

Kapitel 10: Visuell glänzen mit bedingten Formatierungen in Pivot Tabellen

Bei der bedingten Formatierung von Pivot Tabellen sind einige Punkte zu beachten, die dann doch abweichend zur normalen bedingten Formatierung von Tabellen verhalten. Entsprechend hilfreich ist es hier die Zusammenhänge zu kennen und die unterschiedlichen Möglichkeiten der bedingten Formatierung in Pivot Tabellen zu nutzen.

Kapitel 11: PIVOTDATENZUORDNEN Formel

Diese Formel dient dazu Datenfelder aus einer Pivot Tabelle in anderen Zellen auszulesen. Hier wird nicht nur erklärt wie "normale" Bezüge auf eine Pivot Tabelle gesetzt werden können sondern auch wie diese Formel durch indirekte Verweise dynamisch genutzt werden kann.

Kapitel 12: Dashboards - Pivot in der Berichterstattung

Die Königsdisziplin einer Pivot Tabelle ist die Erstellung eines Dashboards, in dem alle relevanten Informationen intuitiv durch die Berichtsempfänger ausgewertet werden können. Im Beispiel einer Projektzeiterfassung und einer sich selbst aktualsierenden Geburtstagsliste werden alle vorherigen Kapitel noch einmal zusammengefasst und die einzelnen Teile setzen sich wie Puzzleteile zum Gesamtbild zusammen.

So einfach die Beispiele auch auf den ersten Blick wirken mögen habe ich hier doch einige Anregungen für kommende Finanzberichte erhalten und freue mich schon darauf das Gelernte umsetzen zu können.

Teilnahmebescheinigung / Zertifikat

Gerade da das Thema Personalentwicklung in einigen Einrichtungen und Firmen sicherlich an Bedeutung gewinnt bin ich auch sehr froh, dass nach Kursabschluss ein Zertifikat / Teilnahmebescheinigung gibt, so dass der Besuch des gesamten Kurses und das Gelernte nachgewiesen werden kann.



Das Zertifikat habe ich tatsächlich erst zugesandt bekommen nachdem ich die letzte Lektion betrachtet hatte, auch wenn ich einige Zeit vorher die anderen Lektionen durchgegangen war.

Fazit

Für Menschen, die sich intensiver mit Berichtswesen und Excel beschäftigen kann ich diese Schulung aus voller Überzeugung empfehlen.

Dem chinesischen Philosophen Konfuzius wird das Zitat "Sage es mir, und ich werde es vergessen. Zeige es mir, und ich werde es vielleicht behalten. Lass es mich tun, und ich werde es können." zugeschrieben, was sowohl in der Aus- und Weiterbildung auch immer wieder gerne herangezogen wird um eine Verbindung zwischen Theorie und Praxis bei Schulungsunterlagen herzustellen und auch das Gelernte direkt oder auch später anzuwenden.

Dieses ist Lukas Rohr sehr gut gelungen und ich empfand diese Schulung sowohl als intensiv als auch lehrreich und konnte einige für mich interessante Punkte mitnehmen und dabei auch für mein eigenes Berichtswesen anwenden. Sowohl diese Inspirationen als auch die Möglichkeit das eigene Wissen noch einmal zu vertiefen haben mich hier nicht nur angenehm überrascht sondern lassen mich diese Schulung auch als einen Mehrwert ansehen.

Insbesondere in Hinblick auf die Datenmodelle freue ich mich schon darüber diese in der Praxis einsetzen zu können.

Wenn ich ebenfalls Interesse an diesen Kurs geweckt hab würde ich mich über einen Blick auf das Angebot zum Onlinekurs "Daten Analysieren mit Pivot Tabellen" * Affiliate-Link / Werbung freuen.



* Affiliate-Link / Werbung
Als Affiliate verdiene ich am Erwerb dieses Kurses per  Affiliate-Link
 

Der Schwerpunkt der letzten Artikel lag dann doch eher auf it-nahe Themen, so dass ich heute wieder einmal zum Thema SAP und Excel etwas veröffentlichen möchte. Vielen Dank an dieser Stelle auch an Lukas (Excelnova.org) der im Artikel "Quick Tipp: Excel Arbeitsblätter durchnummerieren und ausdrucken" das Thema "Grundlagen Office - Seitenzahlen nachträglich ändern Microsoft Word, Excel, Powerpoint und Adobe Acrobat zum Beispiel für Sammelvermerken an Leitung" ebenfalls noch einmal aufgegriffen hat. Hier freue ich mich tatsächlcih sehr, dass innerhalb der Blogs auch gegenseitig eine Inspiration zu Artikeln gegeben ist :-)
 

Notwendigkeit der Dokumentation von eigenentwickelten Berichten

Wenn ich Berichte in SAP entwickele bemühe ich mich meistens darum auch eine entsprechende Dokumentation zum eigentlichen Bericht anzulegen. Sehr hilfreich ist es dabei im Bericht selbst eine Dokumentation anzulegen. Die technischen Möglichkeiten dazu sind in den Artikeln "Dokumentation von Berichtsgruppe und Berichtsdokumentation bei Report Painter und Writer Berichten" und "Kopfzeilen im Report Painter auch bei Export nach Excel verwenden" für Report Painter Berichte erläutert.

Ebenso findet sich hier eine Möglichkeit für Selektionsvarianten im Artikel "Fachliche und technische Dokumentation von Selektionsvarianten und individuelle Berichte in SAP identifizieren".

Der Nachteil ist allerdings, dass eine Menge an Dokumentationen zu einzelnen Berichten vorhanden ist, es aber an einer Übersicht aller vorhandener Berichte fehlt, so dass ich hier die Anregung einer Kollegin gerne gefolgt bin und eine Gesamtübersicht erstellen möchte.
 

Ausgangslage: Übersicht zu eigenentwickelten Berichten

Die Anforderung eine Übersicht einiger selbstentwickelter Berichte zu erstellen bin ich dann aber tatsächlich wesentlich lieber über das Lieblingstool im Controlling nachgegangen und habe eine Arbeitsmappe in Excel für diese Herausforderung angelegt.

Listen zur Auswahl von Berichtsart und wie diese aufgerufen werden

Im Tabellenblatt "Auswahl" habe ich die vorhandenen Berichtsarten unter der Registerkarte Formeln in der Befehlsgruppe Definierte Namen und hier Name definieren festgelegt.



Dabei sind insgesamt drei Namen definiert:

1. D_Aufruf liefert die Art, wie der Bericht aufgerufen wird
2. D_Berichtsart beschreibt die Art des Berichtes
3. M_Aufruf setzt Aufruf und eine Beschreibung als Matrix zusammen

Im Tabellenblatt Übersicht habe ich nun eine Tabelle in der die einzelnen Bereiche im Ribbon Daten in der Befehlsgruppe Datentools unter Datenüberprüfung als Liste in den ersten beiden Spalten, wie in der folgenden Abbildung zu sehen, hinterlegt sind.



Diese Tabelle soll direkt ein Inhaltsverzeichnis aller dokumentierten Berichte liefern.

Neben den beiden Auswahlfeldern gibt es aber auch noch einige weitere Spalten, auf die ich kurz eingehe.

Die Spalte Hinweis liefert zum ausgewöhlten Aufruf noch eine kurze Erläuterung per Verweisfunktion.



Durch die Funktion WENNFEHLER wird dieser Hinweis nur ausgegeben wenn in der Spalte B (Aufruf) ein passender Eintrag vorhanden ist, ansonsten bleibt es leer.

Indirekter Hyperlink zum Verweis auf Tabellenblatt anhand eines Zellenwertes

Interessant ist nun noch die Spalte D in der der Berichtsname eingetragen wird und die Spalte F in der ein Tabellenblatt zur Dokumentation des Berichtes eingetragen wird.

Unter F kann dieses zum Beispiel ein Transaktionscode wie ZBUDGET oder ZLANDESMITTEL sein und das Tabellenblatt kann dann Doku_ZBUDGET sein.

Wenn ein Tabellenblatt eingetragen wird verweist deine Formel in Spalte G per Link auf dieses Tabellenblatt.

Dazu wird die Formel HYPERLINK und INDIREKT miteinander kombiniert:



Betrachten wir uns hier einmal den Part wenn ein korrekter Tabellenblattname in Spalte F eingetragen ist (in unseren Beispiel als der Tabellenblattname Doku_ZBUDGET in Spalte F.



Der Syntax der Formel lautet:

=HYPERLINK(Hyperlink_Adresse; [Freundlicher Name])

Wobei der Freundliche Name optional ist.

In unseren Fall setzt sich die Formel Hyperlink wie folgt zusammen:

=WENNFEHLER(
  HYPERLINK("#"&F2&"!a2";INDIREKT(F2&"!D2"))
;"")

Durch Wennfehler wird der Link nur gesetzt, wenn das Tabellenblatt auch vorhanden ist.

Die Formel Hyperlink verweist dank der "#" innerhalb der Arbeitsmappe auf das Tabellenblatt welches in Zelle F2 eingetragen ist und dort auf die Zelle A2.

Als freundlicher Name wird per Indirekt aus den Tabellenblatt F2 die Spalte D2 eingetragen.

Vorlage Berichtsdokumentation

Betrachten wir uns nun das Tabellenblatt Doku_ZBUDGET wird dieses auch näher erläutert.



Hier ist dann der eigentliche Bericht etwas ausführlicher erläutert.

In diesem Beispiel verweise ich direkt auf einen Bericht der unter "Mehrjahresvergleich im Recherchebericht mit Beschränkung von Aufwand und Ertrag für das laufende Geschäftsjahr bis aktuelle/selektiere Periode/Jahr und komplette Perioden der Vorjahre sowie übertragene Jahresendsalden" technisch beschrieben ist und eigentlich mit einer "Zeichnung" recht gut zusammengefasst werden kann:



Eben diese Grafik kann ich dann ebenfalls ins Tabellenblatt einfügen, so dass eine umfangreiche Dokumentation von der Berichtsübersicht erreicht werden kann.

Fazit

Auch wenn Dokumentation tatsächlich ein Punkt ist der wichtig für jede Entwicklung ist dürfte der Ausdruck "Der Code ist Dokumentation genug" mittlerweile überholt sein.

Neben einer Übersicht der einzelnen Berichte sind es aber auch Arbeitsmappen selbst denen ich mittlerweile gerne eine entsprechende Formatierung und logischen Aufbau gebe.

Das in der Schule gelernte E-V-A (Eingabe, Verarbeitung, Ausgabe) Prinzip ist auch bei Excel noch immer hilfreich um etwas Struktur in die Tabelle zu geben.

Im Artikel "Buchprojekt und nachträgliche Änderungsnachverfolgung dank Dokuemntenvergleich der einzelnen Arbeitsversionen" habe ich einen Einblick in meine Arbeit als Buchautor insbesondere bezüglich der Zusammenarbeit mit meiner Lektorin gegeben und nebenher bereite ich auch eine neue SAP Schulung vor und nutze hier tatsächlich mit zunehmender Begeisterung eine Präsentationssoftware (siehe "Powerpoint Smart Art als Inhaltsverzeichnis mit Powerpoint für Agenda, Abschnitte und Fortschritt innerhalb der Präsentation" oder auch "Grundlagen und Empfehlungen rund um Powerpoint oder auch andere Präsentationen").

Dennoch sind manche Grundlagen in Office doch etwas, wo immer einmal wieder überlegt werden muss, wie diese nun umzusetzen ist.

So sind auch im  Bereich Hochschulberichtswesen und Hochschulcontrolling bestimmte formale Vorschriften umzusetzen was sich zum Beispiel an geänderte Vorgaben für Vorlagen anbelangt, die an die Leitung gehen sollen.

Ausgangslage: Sammelvermerken mit durchgehender Nummerierung

Manchmal sind Formvorschriften für Unterlagen ganz praktisch mit einer Ausnahme.

Sofern Unterlagen zur Vorlage schon vorbereitet worden sind besteht kurzer Anpassungsbedarf wenn statt einzeln durchnummerierte (Seitenzahl beginnt jeweils mit 1) Anlagen nun durchgehend nummeriert werden sollen.

Als Vorgabe ist nun künftig eine Übersicht vor den ganzen Anlagen festzulegen und die Seitenzahlen durchgehend über das gesamte Werk zu erstellen.

Im Rahmen eines Deckblattes soll bei Sammelvermerken oder Dokumenten mit mehreren Anlagen iene Übersicht der entsprechenden Dokumente und eine Gliederung bzw. Inhaltsübersicht zu den einzelnen Dokumenten mit Seitenangabe erfolgen.

Da aber die Unterlagen in unterschiedlichen Dokumenten und Programmen erstellt worden sind, möchte ich hier als "Grundlagen" kurz auf das Thema Seitenzahlen eingehen.

Die Frage beim jeweiligen Programm ist nur, wie die Seitenzahl so angepasst werden kann, dass diese eben nicht bei 1 beginnt.

Für ausgewählte Programme gehe ich hier direkt drauf ein, so dass für häufige Programme in Office (Textverarbeitung, Tabellenkalkulation oder auch Präsentaitonssoftware) diese erläutert werden. Hier erläutere ich das Vorgehen an Microsoft Office, aber auch in anderen Produkten dürfte die Einstellung an vergleichbarer Stelle zu finden sein.

 

Seitenzahl in Winword anpassen

Unter Winword kann eine Seitenzahl die bspw. in der Fußnote über die Symbolleiste EINFÜGEN in der Befehlsgruppe KOPF UND FUßZEILE eine Seitenzahl eingefügt werden.

Hier kann aber auch die Option SEITENZAHLEN  FORMATIEREN aufgerufen werden alternativ ist dieses mit der rechten Maustaste auf das Feld Seitenzahl möglich.



Hier sind verschiedene Optionen zum Seitenzahlenformat möglich anzugeben.



Hier ist der Abschnitt Seitennummerierung im Fesnter Seitenzahlenformat sehr praktisch, da dadrüber unter Beginnen bei festgelegt werden kann welche Seitennummer die erste Seite haben soll und entsprechend die Nummerierung beginnen kann.

Soll zusätzlich noch die Gesamtzahl der Seiten angegeben werden ist dieses in der Symbolleiste EINFÜGEN in der Befehlsgruppe TEXT über die Schaltfläche SCHNELLBAUSTEIN über FELD möglich.



Hier können dann diverse automatische Felder ausgewählt werden. Unter der Kategorie DOKUMENTENINFORMATIONEN findet sich das Feld NumPages. Über dieses Feld werden die Gesamtzahl der Seitenzahlen eingefügt.

Seitenzahl in Powerpoint formatieren.

In Powerpoint ist eine vergleichbare Funktion ebenfalls vorhanden.

Diee findet sich in der Symbolleiste ENTWURF in der Befehlsgruppe ANPASSEN über die Foliengröße möglich. Hier kann in der Option BENUTZERDEFINIERTE FOLIENGRÖ?E eine vergleichbare Einstellung vorgenommen werden.



Auch hier ist über die Einstellung Nummerierung beginnt bei die Seitenzahl anzupassen.

Seitenzahl in Excel anpassen

Ein klein wenig abweichend verhält es sich bei der Einfügung von Seitenzahlen in Excel.

Tatsächlich kann über die Symbolleiste SEITENLAYOUT und dort auf die Schaltfläche DRUCKTITEL über die Schaltfläche BENUTERDEFINIERTE KOPFZEILE oder BENUTERDEFINIERTE FUßZEILE eine Seitenzahl eingebunden werden.



Für die Kopfzeile habe ich über die Schaltfläche SEITENZAHL eingefügt.



Tatsächlich hat Excel hier den Vorteil, dass es mit solch eingfügten Feldern auch rechnen kann, so wird die Seite um 4 erhöht durch die Angabe von &[Seite]+4 so dass die Seitenzählung ab 5 beginnt.

Durch die Umstellung der ANSICHT (Symbolleiste) in der Befehlsgruppe ARBEITSMAPPENANSICHT und hier auf Seitenlayout lässt sich das Ergebnis auch direkt sehen.


Werden nun allerdings die Arbeitsmappe ausgedruckt beginnt die Seitennummerierung bei jeden Tbaellenblatt erneut bei 1 (bzw. hier 5).

Die Lösung ist hier entweder alle Blätter über die rechte Maustaste auszuwählen-


Einzelne Tabellenblätter lassen sich durch STRG + Mausklick auch einzeln markieren.

Werden auf solche Weise gruppierte Tabellenblätter nun ausgedruckt erfolgt die Seitennummerierung über alle Tabellenblätter hinweg und es wird nicht für das nächste Tabellenblatt erneut fortgefahren.

Seitenzahl mit Adoe Acrobat hinzufügen

Sofern Acrobat (nicht Reader sondern das vollständige Programm) installiert ist kann hier ebenfalls über die Symbolleiste PDF BEARBEITEN unter KOPF- UND FUßZEILE eine Seitenzahl eingefügt werden.

Über die Schaltfläche SEITENZAHL EINFÜGEN können auf den jeweiligen Positionen, wie hier Fußzeilentext rechts (siehe Abbildung) die Seitenzahl eingefügt werden.



Durch den Punkt FORMAT FÜR SEITENZAHLEN UND DATUM lässt sich auch die Seitenzahl bearbeiten.

Im Folgenden Fenster kann dann ebenfalls die erste Seitenzahl ergänzt werden.



Damit beginnt auch hier die Seitenzahl beliebig. Da ich in diesen Fall tatsächlich ein PDF Dokument bearbeitet habe, dass durchgehend nummeriert werden soll ist es hier sehr praktisch nachträglich alle Seiten einzufügen.

Der Nachteil ist nur, dass hier die Pro Version von Adobe erforderlich ist.

Fazit

Sei es nun in den Quelldokumenten (meistens tatsächlich Tabellenkalkulation, Textverarbeitung oder Präsentationen) oder aber im Gesamtdokument eine nachträgliche Anpassung der Seitennummerierung ist mit ein wenig Aufwand verbunden, der sich aber nicht als hohe Hürde erweist, auch wenn sich manchmal daran erinnert werden muss, an welcher Stelle die Seitenzahl nun eigentlich zu formatieren ist.

Mit einer der Gründe für solche Artikel hier im Blog ist es auch immer auf solche Anleitungen verweisen zu können um hier schnelle Unterstützung anbieten zu können.

Immerhin lassen sich so bestehende Dokumente anpassen und ordentlich durchnummerieren, so dass bei der nächsten Sitzung dann ein einheitlicher Bezug durchaus möglich ist :-).