Sonntag, 10. April 2016
12:58 Uhr
12:58 Uhr
Pivottabellen ab Excel 2010 dynamischer filtern mit Datenschnitten am Beispiel Hochschulfinanzstatistik
Durch "Pivot Tabellen Datenschnitte: Erstellen, Formatieren, Verbinden, und Sortieren" von Lukas Rohr (excelnova.org) bin ich erstmals auf das Thema Datenschnitten bei Pivot-Tabellen aufmerksam geworden. Seit Excel 2010 ist diese Technik in Excel vorhanden und hilft dabei Filter zu setzen und gleichzeitig einen Überblick über die einzelnen ausgeblendeten Felder zu behalten.
Getreu den Motto "Es ist schon alles geschrieben, nur nicht von jedem!" möchte ich hier ebenfalls ein Beispiel zur Anwendung von Datenschnitten darstellen, auch wenn ich bisher sehr gerne auf diesen Artikel verwiesen habe. Grundlage für die zu erstellende Pivot-Tabelle ist eine Auswertung der Hochschulfinanzstatistik.
Dieser bundeseinheitliche Schlüssel gliedert die Einnahmen und Ausgaben (bzw. Ertrag und Aufwand) sowie Investitionen nach der Systematik der Finanzarten. Die Lehr- und Forschungsbereichen werden nach Fächergruppenschlüssel dargestellt.
Hierbei werden die Hochschulen auf Bundesebene nach ihrer Art des Rechnungswesen unterschieden (kaufmännisch oder kameral). Für das folgende Beispiel betrachten wir den "Fragebogen für Hochschulen mit kaufmännischem Rechnungswesen" der Jahreserhebung zur Hochschulfinanzstatistik.
So wird zum Beispiel die "Evangelische Theologie" mit Fächergruppenschlüssel 020 in der Fächergruppe "01 Sprach und Kulturwissenschaften" und die "Informatik" mit Fächergruppenschlüssel 350 in der Fächergruppe "04 Mathematik, Naturwissenschaften".
Neben der Fächergruppen und Fachgebieten sind dadrunter auch noch die einzelnen Studienfächer einsortiert (als Beispiel innerhalb der Fächergruppe 350 (Informatik) das Studienfach "Bioinformatik - 3540" oder innerhalb der Fächergruppe 020 (Evangelischen Theologie) das Studienfach "Praktische Theologie und Religionspädagogik (evang. Th.) - 0250"
Daneben gibt es noch Zentrale Einrichtungen zu denen unter anderen die Hochschulverwaltung (880) aber auch die Zentralbibliothek (900) oder das Hochschulrechenzentrum (910) gezählt wird, die jedoch auch wiederum feiner gegliedert werden können.
Hierbei berichten die Hochschulen sowohl in einer Personalstandstatistik als auch im Rahmen einer Finanzstatistik über ein Jahr. Für dieses Beispiel wird die Finanzstatistik näher betrachtet.
Wie erwähnt sind alle Aufwendungen (als Beispiel Energiekosten (522)) oder auch Erträge (u.a. aus Drittmitteln vom öffentlichen Bereich (63) oder anderen Bereich (64)) entsprechend in SYF-Code vorhanden.
Im Form eines Fragebogen werden diese Daten dann einzeln an das jeweilige Statistik Amt gesandt und im Rahmen einer Bundesstatistik zusammengefasst.
Über diese Tabelle kann dann über den Ribbon "Einfügen" mit der Schaltfläche "Pivottabelle" eine Pivottabelle erstellt werden.
Die auszuwertende Pivottabelle ist dabei wie folgt aufgebaut.
Als Filter ist hier lediglich Summe gewählt um nicht sowohl die Gruppe "01 Sprach- und Kulturwissenschaften" als auch die hier eingeordneten Lehr- und Forschungsbereiche wie zum Beispiel die Theologie doppelt zu zählen.
Selbstverständlich könnte man nun jede Menge Filter in der Pivottabelle selbst setzen.
Hierzu kann, sofern man in der Pivottabelle ein Feld ausgewählt hat, über den Ribbon "Optionen" innerhalb der Befehlsgruppe "Sortieren und Filtern" die Schaltfläche "Datenschnitt einfügen" einfügen (siehe Abbildung).
Hier besteht nun die Möglichkeit für jedes Feld der Datentabelle ein Datensegment festzulegen. Im einfachen Beispiel sind dies folgende Punkte:
Im Ergebnis haben wir nun mehrere Datensegmente die dynamisch den Inhalt der Pivottabelle filtern. Anhand der Energiekosten in den LFB Informatik für Fachhochschulen im Jahr 2012 ist dieses mit fiktiven Zahlen erläutert.
Mittlerweile sind Datenschnitten einer der Gründe warum ich den Wechsel nach Office 2010 sehr schätze und diese tatsächlich als Innovation wahrnehme. Anhand der Farben sind hier auch in den einzelnen Datenschnitten noch weitere Informationen enthalten.
Dunkelblau hinterlegt sind die Felder welche ausgewählt und auch Daten enthalten sind
Dieses trifft im Beispiel auf die Daten der Hochschulart "FH" oder SYF-Code "522 - Energiekosten" zu.
Innerhalb der Datenschnitten "LFB-Fächergruppe" oder auch "Hochschule" ist darüber hinaus auch zu erkennen, welche Daten durch die Auswahl in den anderen Datenschnittpunkten nicht mit angezeigt werden, obgleich hier Daten hinterlegt sind. Dieses wird durch eine transparente Farbe dargestellt.
Für die Anwendende der Pivot-Tabelle ist nun aber auch möglich anhand der Datensegmente selbst eine Auswahl an darzustellende Werte mit der Maustaste oder für mehrere Werte durch STRG und der Maustaste auszuwählen, so dass hier nicht mit Filtern sondern anhand der dargestellten Werte eine Auswahl getroffen werden kann.
Damals (2014) hatte ich nur auf Facebook auf diesen Artikel hingewiesen (siehe Posting vom 7. August 2014) bin aber auch heute noch sehr begeistert von den dort beschriebenen Möglichkeiten und möchte daher auch hier im Blog nochmals auf die gelungene und umfassende Anleitung von excelnova.org zu diesen Thema verweisen.
Grundsätzlich nutze ich meine Facebook Seite tatsächlich neben Blog eine gute Quelle um über aktuelle Fundstücke rund um das Themengebiet meiner Seite informiert zu werden. Außerdem nutze ich auch selbst diese Seite um kurze Hinweise oder Webfundstücke festzuhalten die nicht unbedingt in einen ganzen Artikel beschrieben werden oder manchmal erst Jahre später als Artikel erscheinen.
Die eingangs verlinkte Anleitung stellt in meinen Augen eine umfassende Anleitung dar, auf die ich bisher immer gerne verwiesen habe und mit diesen Artikel eigentlich nur um einen weiteren Anwendungsfall ergänzen möchte. Vielleicht haben ja auch andere Kolleginnen und Kollegen im Bereich des Hochschulberichtswesen auf diese Art und Weise eine Anregung diese Technik für eigene Auswertungen zu verwenden.
Wie schon im Artikel "Unterschiedliche Auswertungsmöglichkeiten im Controlling (Report Writer, Recherchebericht, SAP Query) und natürlich Excel ;-)" beschrieben sind viele dieser Berichsanforderungen mit SAP zu erfüllen. Technisch liefert hier der Artikel "Grundlagen Kurzeinführung und Handbuch Report Painter Report Writer" ein passendes Werkzeug.
Daneben kann aber auch im Umgang mit öffentlichen Daten für das eigene Arbeitsumfeld eine Menge an interessanter Auswertungen erstellt werden. Im Rahmen des Zensus 2011 wurde dieses auch im Artikel "Ergebnisse des Zensus 2011" am Beispiel einer "Auswertung höchster Berufsabschluss in der Altersklasse 18 bis 29 für die Hochschulstandorte in Hessen" aber auch mit Links zu weiteren öffentlichen Daten rund um Open Data bzw. Bildungswesen ergänzt..
Hinweis: Aktuelle Buchempfehlungen besonders SAP Fachbücher sind unter Buchempfehlungen inklusive ausführlicher Rezenssionenzu finden. Mein Weiterbildungsangebot zu SAP Themen finden Sie auf unkelbach.expert.
Getreu den Motto "Es ist schon alles geschrieben, nur nicht von jedem!" möchte ich hier ebenfalls ein Beispiel zur Anwendung von Datenschnitten darstellen, auch wenn ich bisher sehr gerne auf diesen Artikel verwiesen habe. Grundlage für die zu erstellende Pivot-Tabelle ist eine Auswertung der Hochschulfinanzstatistik.
Finanzen der Hochschule - Hochschulfinanzstatistik
Wie im Artikel "Hochschulstatistische Kennzahlen" erläutert melden Hochschulen ihre "Aufwendungen und Investitionsausgaben" und "Erträge" nach Arten und in fachlicher Gliederung. Hierbei werden die einzelnen Bereiche der Hochschulen nach "Fächergruppen" und "Lehr und Forschungsbereiche" untergliedert. Die Art der Aufwendungen und Erträge werden dabei durch SYF-Codes abgebildet.Dieser bundeseinheitliche Schlüssel gliedert die Einnahmen und Ausgaben (bzw. Ertrag und Aufwand) sowie Investitionen nach der Systematik der Finanzarten. Die Lehr- und Forschungsbereichen werden nach Fächergruppenschlüssel dargestellt.
Hierbei werden die Hochschulen auf Bundesebene nach ihrer Art des Rechnungswesen unterschieden (kaufmännisch oder kameral). Für das folgende Beispiel betrachten wir den "Fragebogen für Hochschulen mit kaufmännischem Rechnungswesen" der Jahreserhebung zur Hochschulfinanzstatistik.
So wird zum Beispiel die "Evangelische Theologie" mit Fächergruppenschlüssel 020 in der Fächergruppe "01 Sprach und Kulturwissenschaften" und die "Informatik" mit Fächergruppenschlüssel 350 in der Fächergruppe "04 Mathematik, Naturwissenschaften".
Neben der Fächergruppen und Fachgebieten sind dadrunter auch noch die einzelnen Studienfächer einsortiert (als Beispiel innerhalb der Fächergruppe 350 (Informatik) das Studienfach "Bioinformatik - 3540" oder innerhalb der Fächergruppe 020 (Evangelischen Theologie) das Studienfach "Praktische Theologie und Religionspädagogik (evang. Th.) - 0250"
Daneben gibt es noch Zentrale Einrichtungen zu denen unter anderen die Hochschulverwaltung (880) aber auch die Zentralbibliothek (900) oder das Hochschulrechenzentrum (910) gezählt wird, die jedoch auch wiederum feiner gegliedert werden können.
Hierbei berichten die Hochschulen sowohl in einer Personalstandstatistik als auch im Rahmen einer Finanzstatistik über ein Jahr. Für dieses Beispiel wird die Finanzstatistik näher betrachtet.
Wie erwähnt sind alle Aufwendungen (als Beispiel Energiekosten (522)) oder auch Erträge (u.a. aus Drittmitteln vom öffentlichen Bereich (63) oder anderen Bereich (64)) entsprechend in SYF-Code vorhanden.
Im Form eines Fragebogen werden diese Daten dann einzeln an das jeweilige Statistik Amt gesandt und im Rahmen einer Bundesstatistik zusammengefasst.
Grundtabelle - Einzeldaten nach Hochschule, Fächergruppe und SYF-Code
Im Rahmen einer Gegenüberstellung mehrere Jahre dieser Finanzstatistik kann eine entsprechende rein fiktive Grundtabelle nun wie in der folgenden Abbildung aussehen.
Über diese Tabelle kann dann über den Ribbon "Einfügen" mit der Schaltfläche "Pivottabelle" eine Pivottabelle erstellt werden.
Pivottabelle anlegen und eventuell um berechnete Felder ergänzen
Soweit ist dieses wie in jeder anderen Pivottabelle möglich. In der Grundtabelle habe ich schon anhand des Feldes "Hochschule" die Hochschulart "Uni" oder "FH" durch die ersten drei Ziffern des Feldes "Hochschule" bestimmt. Dieses ist natürlich auch durch ein "berechnetes Feld" in der eigentlichen Pivottabelle möglich. Die hierzu erforderlichen Einstellungen habe ich in den Artikeln "Excel Berechnete Felder in Pivottabellen" und "Excel Pivottabelle Darstellung Grenzwerte Einnahmen auf Projekte je Person durch Zuordnung VZÄ auf verantwortlicher Kostenstelle" ausführlicher beschrieben.Die auszuwertende Pivottabelle ist dabei wie folgt aufgebaut.
Als Filter ist hier lediglich Summe gewählt um nicht sowohl die Gruppe "01 Sprach- und Kulturwissenschaften" als auch die hier eingeordneten Lehr- und Forschungsbereiche wie zum Beispiel die Theologie doppelt zu zählen.
Selbstverständlich könnte man nun jede Menge Filter in der Pivottabelle selbst setzen.
Filtern von Daten über Datenschnitten
Eine wesentlich komfortablere Variante ist es jedoch nun hier mit Datenschnitten zu arbeiten.Hierzu kann, sofern man in der Pivottabelle ein Feld ausgewählt hat, über den Ribbon "Optionen" innerhalb der Befehlsgruppe "Sortieren und Filtern" die Schaltfläche "Datenschnitt einfügen" einfügen (siehe Abbildung).
Hier besteht nun die Möglichkeit für jedes Feld der Datentabelle ein Datensegment festzulegen. Im einfachen Beispiel sind dies folgende Punkte:
Im Ergebnis haben wir nun mehrere Datensegmente die dynamisch den Inhalt der Pivottabelle filtern. Anhand der Energiekosten in den LFB Informatik für Fachhochschulen im Jahr 2012 ist dieses mit fiktiven Zahlen erläutert.
Mittlerweile sind Datenschnitten einer der Gründe warum ich den Wechsel nach Office 2010 sehr schätze und diese tatsächlich als Innovation wahrnehme. Anhand der Farben sind hier auch in den einzelnen Datenschnitten noch weitere Informationen enthalten.
Dunkelblau hinterlegt sind die Felder welche ausgewählt und auch Daten enthalten sind
Dieses trifft im Beispiel auf die Daten der Hochschulart "FH" oder SYF-Code "522 - Energiekosten" zu.
Innerhalb der Datenschnitten "LFB-Fächergruppe" oder auch "Hochschule" ist darüber hinaus auch zu erkennen, welche Daten durch die Auswahl in den anderen Datenschnittpunkten nicht mit angezeigt werden, obgleich hier Daten hinterlegt sind. Dieses wird durch eine transparente Farbe dargestellt.
Für die Anwendende der Pivot-Tabelle ist nun aber auch möglich anhand der Datensegmente selbst eine Auswahl an darzustellende Werte mit der Maustaste oder für mehrere Werte durch STRG und der Maustaste auszuwählen, so dass hier nicht mit Filtern sondern anhand der dargestellten Werte eine Auswahl getroffen werden kann.
Weitere Erläuterungen und sonstige Anwendungsgebiete
Damals (2014) hatte ich nur auf Facebook auf diesen Artikel hingewiesen (siehe Posting vom 7. August 2014) bin aber auch heute noch sehr begeistert von den dort beschriebenen Möglichkeiten und möchte daher auch hier im Blog nochmals auf die gelungene und umfassende Anleitung von excelnova.org zu diesen Thema verweisen.
Grundsätzlich nutze ich meine Facebook Seite tatsächlich neben Blog eine gute Quelle um über aktuelle Fundstücke rund um das Themengebiet meiner Seite informiert zu werden. Außerdem nutze ich auch selbst diese Seite um kurze Hinweise oder Webfundstücke festzuhalten die nicht unbedingt in einen ganzen Artikel beschrieben werden oder manchmal erst Jahre später als Artikel erscheinen.
Die eingangs verlinkte Anleitung stellt in meinen Augen eine umfassende Anleitung dar, auf die ich bisher immer gerne verwiesen habe und mit diesen Artikel eigentlich nur um einen weiteren Anwendungsfall ergänzen möchte. Vielleicht haben ja auch andere Kolleginnen und Kollegen im Bereich des Hochschulberichtswesen auf diese Art und Weise eine Anregung diese Technik für eigene Auswertungen zu verwenden.
Datemschnitt (Slicers) auf als Tabelle formatierte Tabellen ab Excel 2013
Durch Martin Weiß (tabellenexperte.de) ist ein weiterer Vorteil der Datenschnitten ab Excel 2013 erwähnt. Im Artikel "Bequemer filtern mit Datenschnitten" weist er darauf hin, dass ab Excel 2013 tatsächlich Datenschnitten auch auf "als Tabelle formatierte" Tabellen angewandet werden kann. Bis Excel 2010 ist die Funktion Datenschnitt (englisch “Slicers”) nur für Pivot-Tabellen verfügbar. Dieses spricht eigentlich noch mehr dafür Datentabellen tatsächlich als Tabellen zu formatieren. Ein weiterer Vorteil ist hier auch im Artikel "Mehrere Autofilter im Tabellenblatt einer Exceltabelle setzen" beschrieben.Basis der Auswertungen Report Painter & Report Writer
Wenn es um Auswertungen im Hochschulberichtswesen geht sind auch folgende Artikel interessant:"Auswertung Statistische Kennzahlen auf Innenaufträge für Lehrimport und Lehrexport auf Ebene Studiengänge", "Studienbereiche und Fächergruppenschlüssel".Wie schon im Artikel "Unterschiedliche Auswertungsmöglichkeiten im Controlling (Report Writer, Recherchebericht, SAP Query) und natürlich Excel ;-)" beschrieben sind viele dieser Berichsanforderungen mit SAP zu erfüllen. Technisch liefert hier der Artikel "Grundlagen Kurzeinführung und Handbuch Report Painter Report Writer" ein passendes Werkzeug.
Daneben kann aber auch im Umgang mit öffentlichen Daten für das eigene Arbeitsumfeld eine Menge an interessanter Auswertungen erstellt werden. Im Rahmen des Zensus 2011 wurde dieses auch im Artikel "Ergebnisse des Zensus 2011" am Beispiel einer "Auswertung höchster Berufsabschluss in der Altersklasse 18 bis 29 für die Hochschulstandorte in Hessen" aber auch mit Links zu weiteren öffentlichen Daten rund um Open Data bzw. Bildungswesen ergänzt..
ein Angebot von Espresso Tutorials
unkelbach.link/et.books/
unkelbach.link/et.reportpainter/
unkelbach.link/et.migrationscockpit/
Diesen Artikel zitieren:
Unkelbach, Andreas: »Pivottabellen ab Excel 2010 dynamischer filtern mit Datenschnitten am Beispiel Hochschulfinanzstatistik« in Andreas Unkelbach Blog (ISSN: 2701-6242) vom 10.4.2016, Online-Publikation: https://www.andreas-unkelbach.de/blog/?go=show&id=706 (Abgerufen am 23.11.2024)
2 Kommentare - Permalink - Office
Artikel datenschutzfreundlich teilen
🌎 Facebook 🌎 Twitter 🌎 LinkedIn