Montag, 16. September 2019
19:47 Uhr
19:47 Uhr
Gleichzeitiges Transponieren und Verknüpfen von Inhalten in Excel per Formel MTRANS am Beispiel Leistungsmengen und Leistungsabgeltung aus Haushaltsplänen im Vergleich
Schon im Artikel "Leistungsmengen im Grundbudget je Fächergruppe (Cluster) im Vergleich oder bedingte Formatierung für Minimalwerte und Maximalwerte" bin ich auf ein Thema im Bereich Hochschulberichtswesen etwas ausführlicher eingegangen und habe die Leistungsmengen und Leistungsabgeltung innerhalb eines Clusters miteinander verglichen.
In einem etwas vereinfachten Modell setzt sich das Grundbudget wie folgt dar:
Hier sind zum Beispiel als Leistungsmenge die Studierende in Regelstudienzeit je Fächergruppe (Cluster) und je nach Hochschulart (vereinfacht gesagt FH und Uni) mit unterschiedlichen Clusterpreisen bewertet.
So könnte eine Gegenüberstellung der einzelnen Hochschulen wie folgt aussehen:
Daneben gibt es aber noch weitere Parameter, die sich zum sogenannten Erfolgsbudget zusammen setzen.
Dieses kann sich zum Beispiel aus dem Drittmittelvolumen berechnen.
In einer umfangreicheren Excel-Tabelle habe ich die Summen der einzelnen Budgetwerte (zum Beispiel Grundbudget und Erfolgsbudget) und die Summen der Leistungsmengen (Studierende und Drittmittelvolumen) miteinander in Verbindung gesetzt.
Die einzelnen Daten sind als Jahresdaten in einzelnen Tabellenblättern abgespeichert.
Zur besseren Übersicht habe ich einmal die Tabellenblätter auf ein Blatt zusammengefasst.
In einer weiteren Tabelle möchte ich nun diese Daten nicht mehr als Spalten sondern als Zeilen einfügen um hier über eine Pivot-Tabelle oder einer anderen Auswertung diese Daten miteinander in Bezug zu setzen.
Meine Zieltabelle sieht dabei wie folgt aus:
In einen anderen Fall (siehe Artikel "Bilanzreporting und GuV Planung in der Profit-Center-Rechnung sowie Konsolidierung einzelner Bereiche am Beispiel der Planung der Haushaltsrechnung" hätte ich angewandt auf diese Auswertungr einfach alle Hochschulen eingetragen und per Verweisfunktion auf die einzelnen Seiten Bezug genommen.
Allerdings gab es in oberen Beispiel einige Veränderungen die man ebenfalls beachten sollte.
Im Jahr 2018 ist eine neue FH hinzugekommen (FH B) und im Jahr 2019 eine neue Uni (Uni C). Daher möchte ich auf eine andere Herangehensweise hinweisen.
Inhalte einfügen > Inhalte einfügen
Im folgenden Fenster stellt sich die Frage, was genau eingefügt werden soll und darüber hinaus haben wir zwei Fuktionen die für uns interessant sind um die kopierten Daten ins Blatt einfügen zu können.
Im oberen Abschnitt ist festgehalten welche Daten eingefügt werden sollen. Hier kann es hilfreich sein die Option "Werte" zu wählen, da beim Einfügen sonst das gesamte Format mit übertragen wird.
Weiter unten gibt es dann die Funktion Transponieren und Verknüpfen.
Grundsätzlich kommt tatsächlich die Funktion Verknüpfen sehr an meine Vorstellungen ran, da damit nicht etwa die Werte in der Zieltabelle eingefügt werden sondern die Zellbezüge der eingefügten Daten.
Am einfachsten lässt sich dieses in folgender Abbildung ersehen, in der ich die Hochschulnamen kopiert habe und per VERKNÜPFEN eingebunden habe.
Hier sind nicht etwa die Werte sondern tatsächlich, wie in Zelle C2 zu sehen, Bezüge zu der Quelltabelle eingebunden.
Allerdings wollte ich hier die Einträge nicht mehr als Spalten sondern als Zeilen eingebunden haben. Daher schauen wir uns einmal die Funktion TRANSPONIEREN näher an.
Sobald ich die Option Transponieren markiert habe ist es nicht mehr möglich die Funktion VERKNÜPFEN zu wählen.
Dennoch ist das Ergebnis hilfreich.
Und soll ebenfalls an der Formel für Grundbudget (die eine Summenformel ist) und der Hochschule erläutert werden.
Im Ergebnis ist hier die Tabelle gedreht und statt in Spalten ist die Hochschule als Zeile und unter Betrag/Menge ist der relative Bezug nun auf Zeilen statt Spalten ausgerichtet (Summe C2, Summe C3, Summe C4 statt Summe B2, C2, D2).
Es wird also auch die Formel transponiert sprich gedreht.
Wenn ich nun mit absoluten Bezügen gearbeitet hätte wäre die in 2010 vorgestellte Methode "Zeilen und Spalte vertauschen (Daten transponieren)" interessant gewesen, in der ich einfach die Formel durch einen Text ersetzt hatte.
Aber das Schöne an Excel ist nicht nur, dass es viele Funktionen und Formeln hat sondern auch, dass hier immer wieder etwas neues Entdeckt werden kann.
Zur Vorbereitung kopiere ich nun aber das Geschäftsjahr ebenso wie die Budgetbeschreibung in der Quelltabelle um diese zu übertragen.
In der Zieltabelle sehen die eingefügten Daten dann dank der Schaltfläche FORMELN ANZEIGEN in der Befehlsgruppe FORMELÜBERWACHUNG in der Symbolleiste FORMELN wie in der folgenden Abbildung für das Jahr 2017 aus:
Das einzelne Feld aus Spalte B1 im Tabellenblatt 2017 habe ich als Verknüpfung eingefügt, so dass dieses direkt aktualisiert werden kann. Ebenso bin ich mit Grundbudget (Zelle B2 im Tabellenblatt 2017) verfahren.
Lediglich die Hochschulen habe ich transponiert, so dass diese direkt als Zeilen eingetragen sind.
Tatsächlich hilft hier eine Matrixformel weiter.
Im Gegensatz zu sonstigen Excelformeln muss diese statt mit ENTER durch die Tastenkombination STRG, UMSCHALTTASTE (Shift) und EINGABETASTE (Enter) bestätigt werden.
Die Formel wird von Exceel 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.
Beim Betrachten des Formelassistenten dürfte dieses etwas klarer werden, da hier die zu transponierende Matrix aus den einzelnen Werten des Grundbudget im Jahr 2017 für die Hochschulen besteht.
Betrachten wir uns nun wieder unsere Zieltabelle dürfte das Ergebnis klar ersichtlich sein.
Wie erhofft sind hier die einzelnen Werte zum Grundbudget automatisch transponiert worden.
Im Beispiel von 2017 ist hier der Name Grundbudget_2017 gewählt worden.
Ebenso verfahre ich auch mit Erfolgsbudegt_2017, Drittmittelvolumen_2017 und Studierende_2017 bzw. passe dieses auf das jeweilige Jahr in den anderen Tabellen an.
Damit kann also tatsächlich über die Matrixformel und per Transponieren aus Budget und Jahr für die einzelnen Hochschulen eine Grundtabelle gefüllt werden und diese dann verglichen werden.
Wenn es um die Formel INDIREKT geht, kann ich diesmal tatsächlich (mal wieder) auf Tabellenexperte.de verweisen. Hier stellt der Artikel "Die INDIREKT-Funktion: Ich kenne einen, der jemanden kennt…" diese Funktion vollständig dar. Besonders lohnen sich hier nebenbei die Kommentare ;-).
Hier ermöglicht es Excel aber lediglich identisch aufgebaute Tabellen (zum Beispiel einzelne Quartale) zu einen gesamten Jahr zusammenzuführen
Dieses entspricht dann leider nicht unseren Vorgaben. Vielmehr sollen später per SUMMEWENN oder auch per Verweisfunktion die Grundtabelle ausgewertet werden und entsprechende Berechnungen durchgeführt werden.
Alternativ bietet sich hier natürlich auch eine Pivot-Tabelle an.
Der Artikel "SUMMEWENN über mehrere Spalten in Excel oder Personalkostenhochrechnung auf Innenauftrag zusammenfassen" stellt dieses recht gut dar.
Bezüglich der Verweisfunktionen möchte ich gerne auf eine Ergänzung im Artikel "Grundlagen in Excel Verweisfunktionen SVERWEIS WVERWEIS und VERWEIS" hinweisen.
m Artikel "XVERWEIS statt SVERWEIS, WVERWEIS und INDEX VERGLEICH: Vorfreude!!!" erläutert Katharina Schwarzer (Soprani Software) die künftige Funktion XVERWEIS die ggf. eine sehr gute Nachfolge zu der Kombination aus INDEX und VERGLEICH (siehe Artikel "Index und Vergleich statt SVERWEIS endlich verstanden und Suche über Verweis nur, wenn es auch etwas zu finden gibt") bietet.
Die weitere Aufbereitung der hier erhobenen Daten kann zum Beispiel in den Artikeln "Datentrends für Drittmittelstatistik mit Sparklines ab Excel 2010 darstellen durch Liniendiagramme in Zellen" wenn es um Verlauf von einzelnen Daten geht oder auch wie im Artikel "Pivottabellen ab Excel 2010 dynamischer filtern mit Datenschnitten am Beispiel Hochschulfinanzstatistik" beschrieben dank Datenschnitten ein Dashboard zur Aufbereitung der Daten geliefert werden.
Letztere Option bietet sich auch für "als Tabelle formatieren" Datenquellen an, auf die ebenfalls Datenschnitten angewendet werden können.
Ein schönes Beispiel zum Thema Vergleich von Leistungsmengen und Leistungsabgeltung kann auch im Artikel "Excel Pivottabelle Darstellung Grenzwerte Einnahmen auf Projekte je Person durch Zuordnung VZÄ auf verantwortlicher Kostenstelle" als Anregung genommen werden.
Neben Berichten innerhalb SAP ist natürlich auch die Auswertung in SAP als Datengrundlage interessant.
Eine aufwändige Variante dafür ist im Artikel "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" beschrieben und natürlich findet sich auch in meinen Publikationen ein guter Ansatz für das eigene Berichtswesen nicht nur im Bereich von Hochschulen mit SAP.
Hinweis: Aktuelle Buchempfehlungen besonders SAP Fachbücher sind unter Buchempfehlungen inklusive ausführlicher Rezenssionen und Bestellmöglichkeit zu finden.
In einem etwas vereinfachten Modell setzt sich das Grundbudget wie folgt dar:
Hier sind zum Beispiel als Leistungsmenge die Studierende in Regelstudienzeit je Fächergruppe (Cluster) und je nach Hochschulart (vereinfacht gesagt FH und Uni) mit unterschiedlichen Clusterpreisen bewertet.
So könnte eine Gegenüberstellung der einzelnen Hochschulen wie folgt aussehen:
Daneben gibt es aber noch weitere Parameter, die sich zum sogenannten Erfolgsbudget zusammen setzen.
Dieses kann sich zum Beispiel aus dem Drittmittelvolumen berechnen.
In einer umfangreicheren Excel-Tabelle habe ich die Summen der einzelnen Budgetwerte (zum Beispiel Grundbudget und Erfolgsbudget) und die Summen der Leistungsmengen (Studierende und Drittmittelvolumen) miteinander in Verbindung gesetzt.
Die einzelnen Daten sind als Jahresdaten in einzelnen Tabellenblättern abgespeichert.
Zur besseren Übersicht habe ich einmal die Tabellenblätter auf ein Blatt zusammengefasst.
Kleiner Hinweis am Rande:
Natürlich sind die Zahelnbeispiele hier, wie auch in anderen Artikeln rein fiktiv. In der Regel nutze ich hier die Formel ZUFALLSZAHL oder ZUFALLSBEREICH um mir passende Beispieldaten zu generieren. Außerdem ist es wohl eher unwahrscheinlich, dass in einen solch kurzen Zeitraum neue Hochschulen gegründet werden ;-).
In einer weiteren Tabelle möchte ich nun diese Daten nicht mehr als Spalten sondern als Zeilen einfügen um hier über eine Pivot-Tabelle oder einer anderen Auswertung diese Daten miteinander in Bezug zu setzen.
Meine Zieltabelle sieht dabei wie folgt aus:
In einen anderen Fall (siehe Artikel "Bilanzreporting und GuV Planung in der Profit-Center-Rechnung sowie Konsolidierung einzelner Bereiche am Beispiel der Planung der Haushaltsrechnung" hätte ich angewandt auf diese Auswertungr einfach alle Hochschulen eingetragen und per Verweisfunktion auf die einzelnen Seiten Bezug genommen.
Allerdings gab es in oberen Beispiel einige Veränderungen die man ebenfalls beachten sollte.
Im Jahr 2018 ist eine neue FH hinzugekommen (FH B) und im Jahr 2019 eine neue Uni (Uni C). Daher möchte ich auf eine andere Herangehensweise hinweisen.
Unterschied Verknüpfen und Transponieren
Kopiere ich nun die Überschrift meiner Grundtabelle (zum Beispiel die Überschriften aus Tabelle 2018) kann ich diese in meiner Zieltabelle über die rechte Maustaste mit INHALTE EINFÜGEN und dort auf die Funktion INHALTE EINFÜGEN ein fügen.
Inhalte einfügen > Inhalte einfügen
Im folgenden Fenster stellt sich die Frage, was genau eingefügt werden soll und darüber hinaus haben wir zwei Fuktionen die für uns interessant sind um die kopierten Daten ins Blatt einfügen zu können.
Im oberen Abschnitt ist festgehalten welche Daten eingefügt werden sollen. Hier kann es hilfreich sein die Option "Werte" zu wählen, da beim Einfügen sonst das gesamte Format mit übertragen wird.
Weiter unten gibt es dann die Funktion Transponieren und Verknüpfen.
Grundsätzlich kommt tatsächlich die Funktion Verknüpfen sehr an meine Vorstellungen ran, da damit nicht etwa die Werte in der Zieltabelle eingefügt werden sondern die Zellbezüge der eingefügten Daten.
Am einfachsten lässt sich dieses in folgender Abbildung ersehen, in der ich die Hochschulnamen kopiert habe und per VERKNÜPFEN eingebunden habe.
Hier sind nicht etwa die Werte sondern tatsächlich, wie in Zelle C2 zu sehen, Bezüge zu der Quelltabelle eingebunden.
Allerdings wollte ich hier die Einträge nicht mehr als Spalten sondern als Zeilen eingebunden haben. Daher schauen wir uns einmal die Funktion TRANSPONIEREN näher an.
Sobald ich die Option Transponieren markiert habe ist es nicht mehr möglich die Funktion VERKNÜPFEN zu wählen.
Dennoch ist das Ergebnis hilfreich.
Und soll ebenfalls an der Formel für Grundbudget (die eine Summenformel ist) und der Hochschule erläutert werden.
Im Ergebnis ist hier die Tabelle gedreht und statt in Spalten ist die Hochschule als Zeile und unter Betrag/Menge ist der relative Bezug nun auf Zeilen statt Spalten ausgerichtet (Summe C2, Summe C3, Summe C4 statt Summe B2, C2, D2).
Es wird also auch die Formel transponiert sprich gedreht.
Wenn ich nun mit absoluten Bezügen gearbeitet hätte wäre die in 2010 vorgestellte Methode "Zeilen und Spalte vertauschen (Daten transponieren)" interessant gewesen, in der ich einfach die Formel durch einen Text ersetzt hatte.
Aber das Schöne an Excel ist nicht nur, dass es viele Funktionen und Formeln hat sondern auch, dass hier immer wieder etwas neues Entdeckt werden kann.
Zur Vorbereitung kopiere ich nun aber das Geschäftsjahr ebenso wie die Budgetbeschreibung in der Quelltabelle um diese zu übertragen.
In der Zieltabelle sehen die eingefügten Daten dann dank der Schaltfläche FORMELN ANZEIGEN in der Befehlsgruppe FORMELÜBERWACHUNG in der Symbolleiste FORMELN wie in der folgenden Abbildung für das Jahr 2017 aus:
Das einzelne Feld aus Spalte B1 im Tabellenblatt 2017 habe ich als Verknüpfung eingefügt, so dass dieses direkt aktualisiert werden kann. Ebenso bin ich mit Grundbudget (Zelle B2 im Tabellenblatt 2017) verfahren.
Lediglich die Hochschulen habe ich transponiert, so dass diese direkt als Zeilen eingetragen sind.
Kombination von Verknüpfen und Transponieren
Für die Leistungsabgeltung und Leistungsmengen wäre es nun tatsächlich spannend sowohl eine Verknüpfung zu den Quelldaten als auch diese zu transponieren ohne, dass hier die Formel in den Quelldaten sich auf die Zieltabelle statt der Quelltabelle bezieht.Tatsächlich hilft hier eine Matrixformel weiter.
Im Gegensatz zu sonstigen Excelformeln muss diese statt mit ENTER durch die Tastenkombination STRG, UMSCHALTTASTE (Shift) und EINGABETASTE (Enter) bestätigt werden.
Die Formel wird von Exceel 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.
Matrixformel MTRANS
In unseren Fall bietet sich die Matrixformel MTRANS an, die es ermöglicht eine transponierte Matrix zur angegebenen Matrix auszugeben. Dabei ist die Matrix in einen anderen Tabellenblatt die ich entsprechend transponieren möchte.Beim Betrachten des Formelassistenten dürfte dieses etwas klarer werden, da hier die zu transponierende Matrix aus den einzelnen Werten des Grundbudget im Jahr 2017 für die Hochschulen besteht.
Betrachten wir uns nun wieder unsere Zieltabelle dürfte das Ergebnis klar ersichtlich sein.
Wie erhofft sind hier die einzelnen Werte zum Grundbudget automatisch transponiert worden.
MTRANS und definierte Namen
Besonders wenn mehr als ein Tabellenblatt (im Beispiel also zum Beispiel die Haushaltspläne von 2005-2020) verglichen werden sollen bietet es sich an im jeden Tabellenblatt per Namensmanager einen Namen für die enthaltenen Daten zu hinterlegen.Im Beispiel von 2017 ist hier der Name Grundbudget_2017 gewählt worden.
Ebenso verfahre ich auch mit Erfolgsbudegt_2017, Drittmittelvolumen_2017 und Studierende_2017 bzw. passe dieses auf das jeweilige Jahr in den anderen Tabellen an.
MTRANS Matrix per INDIREKT auswählen
Ziel des Ganzen ist es nun nicht mehr die Matrix als Bezug in die Formel einzutragen sondern den Namen zur Matrix aus der Spalte Budget und das Geschäftsjahr ableiten zu lassen.
Damit kann also tatsächlich über die Matrixformel und per Transponieren aus Budget und Jahr für die einzelnen Hochschulen eine Grundtabelle gefüllt werden und diese dann verglichen werden.
Wenn es um die Formel INDIREKT geht, kann ich diesmal tatsächlich (mal wieder) auf Tabellenexperte.de verweisen. Hier stellt der Artikel "Die INDIREKT-Funktion: Ich kenne einen, der jemanden kennt…" diese Funktion vollständig dar. Besonders lohnen sich hier nebenbei die Kommentare ;-).
Alternativen zur Konsolidierung von Datentabellen
Im Artikel "Auf den Punkt gebracht: Daten in Excel konsolidieren" von Martin Weiß (Tabellenexperte.de) oder auch "Daten in Excel konsolidieren" von Gerhard Pundt (clevercalcul) ist das Datentool in der Symbolleiste DATEN in der Befehlsgruppe DATENTOOLS über die Schaltfläche Konsolidieren beschrieben.Hier ermöglicht es Excel aber lediglich identisch aufgebaute Tabellen (zum Beispiel einzelne Quartale) zu einen gesamten Jahr zusammenzuführen
Dieses entspricht dann leider nicht unseren Vorgaben. Vielmehr sollen später per SUMMEWENN oder auch per Verweisfunktion die Grundtabelle ausgewertet werden und entsprechende Berechnungen durchgeführt werden.
Alternativ bietet sich hier natürlich auch eine Pivot-Tabelle an.
Der Artikel "SUMMEWENN über mehrere Spalten in Excel oder Personalkostenhochrechnung auf Innenauftrag zusammenfassen" stellt dieses recht gut dar.
Bezüglich der Verweisfunktionen möchte ich gerne auf eine Ergänzung im Artikel "Grundlagen in Excel Verweisfunktionen SVERWEIS WVERWEIS und VERWEIS" hinweisen.
m Artikel "XVERWEIS statt SVERWEIS, WVERWEIS und INDEX VERGLEICH: Vorfreude!!!" erläutert Katharina Schwarzer (Soprani Software) die künftige Funktion XVERWEIS die ggf. eine sehr gute Nachfolge zu der Kombination aus INDEX und VERGLEICH (siehe Artikel "Index und Vergleich statt SVERWEIS endlich verstanden und Suche über Verweis nur, wenn es auch etwas zu finden gibt") bietet.
FAZIT
Insgesamt überzeugt mich hier die Funktion MTRANS sehr und bietet gerade für solche Auswertungen in Kombination mit Namensdefinitionen eine gute Datengrundlage für komplexere Auswertungen.Die weitere Aufbereitung der hier erhobenen Daten kann zum Beispiel in den Artikeln "Datentrends für Drittmittelstatistik mit Sparklines ab Excel 2010 darstellen durch Liniendiagramme in Zellen" wenn es um Verlauf von einzelnen Daten geht oder auch wie im Artikel "Pivottabellen ab Excel 2010 dynamischer filtern mit Datenschnitten am Beispiel Hochschulfinanzstatistik" beschrieben dank Datenschnitten ein Dashboard zur Aufbereitung der Daten geliefert werden.
Letztere Option bietet sich auch für "als Tabelle formatieren" Datenquellen an, auf die ebenfalls Datenschnitten angewendet werden können.
Ein schönes Beispiel zum Thema Vergleich von Leistungsmengen und Leistungsabgeltung kann auch im Artikel "Excel Pivottabelle Darstellung Grenzwerte Einnahmen auf Projekte je Person durch Zuordnung VZÄ auf verantwortlicher Kostenstelle" als Anregung genommen werden.
Neben Berichten innerhalb SAP ist natürlich auch die Auswertung in SAP als Datengrundlage interessant.
Eine aufwändige Variante dafür ist im Artikel "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" beschrieben und natürlich findet sich auch in meinen Publikationen ein guter Ansatz für das eigene Berichtswesen nicht nur im Bereich von Hochschulen mit SAP.
ein Angebot von Espresso Tutorials
unkelbach.link/et.books/
unkelbach.link/et.reportpainter/
unkelbach.link/et.migrationscockpit/
Diesen Artikel zitieren:
Unkelbach, Andreas: »Gleichzeitiges Transponieren und Verknüpfen von Inhalten in Excel per Formel MTRANS am Beispiel Leistungsmengen und Leistungsabgeltung aus Haushaltsplänen im Vergleich« in Andreas Unkelbach Blog (ISSN: 2701-6242) vom 16.9.2019, Online-Publikation: https://www.andreas-unkelbach.de/blog/?go=show&id=1053 (Abgerufen am 15.5.2024)
Keine Kommentare - Permalink - Office
Artikel datenschutzfreundlich teilen
🌎 Facebook 🌎 Twitter 🌎 LinkedIn