Mit Hilfe von MTrans einen transponierten Bereich verknüpfen

Zu den Excel-Funktionen, die Anwendern das (Arbeits-)Leben mitunter deutlich erleichtern, zählt die Einfügeoption „Transponieren“. Damit lässt sich ganz einfach eine Anordnung von Zeilen und Spalten tauschen. Leider ist dieser schöne Effekt dahin, sobald eine Aktualisierung der Werte ansteht. Das passende Pendant ist in diesem Falle die Matrixformel MTrans(). In Verbindung mit der „Transponieren“-Funktion sorgt sie dafür, dass die Änderungen übernommen werden. Schauen Sie sich das gerne genauer an – unser Excel-Experte Norbert Engelhardt erklärt, wie das im Einzelnen funktioniert.

Sicherlich kennen Sie die Excel Einfügeoption „Transponieren“, mit der Sie für einen ausgewählten Bereich die Zeilen- und Spaltenanordnung tauschen können. Vor allem bei der Verarbeitung von automatisch generierten Werten, die häufig im Pivot-Format geliefert werden, findet diese Option Anwendung.

Dabei werden die Werte jedoch als Festwerte übergeben und sind nicht mit den Quelldaten verknüpft. Das heißt, dass bei einer Aktualisierung die Werte nicht automatisch übernommen werden. Hier unterstützt die Matrixformel MTrans(). Mit dieser Formel wird ein vertikaler Zellbereich als horizontaler Bereich zurückgegeben oder umgekehrt. Vorteil: Der Bereich ist verknüpft und übernimmt alle Änderungen der Werte. Schauen wir uns die Formel nun genauer an:
{=MTRANS(Zellenbereich)}

Es werden Ihnen sofort die {} Klammern aufgefallen sein, die typisch und notwendig für eine Matrixformel sind. Diese Klammern werden nicht manuell eingegeben, sondern von Excel ergänzt, sobald Sie die Formel mit der Tastenkombination STRG + Umschalttaste + Eingabetaste abschließen.

In unserem Beispiel werden die Zeitangaben in den Zeilen, die Produktangaben in den Spalten abgebildet. Wir sind es jedoch gewohnt, einen zeitlichen Verlauf von links nach rechts zu lesen, weil uns das die Aufnahme und Interpretation der Werte erleichtert.

Um diesen Bereich mit Hilfe der Formel MTrans zu transponieren, markieren wir einen Bereich, welcher der Spalten- und Zeilenanzahl des Quellbereichs entspricht, d.h. es werden im Quellbereich 7 Spalten und 16 Zeilen verwendet. Wir markieren nun 16 Spalten und 7 Zeilen im Zielbereich, beginnen mit der Formel =MTRANS($B$5:$H$21) und schließen die Eingabe mit der Tastenkombination STRG + Umschalttaste + Eingabetaste ab. Auf diese Weise erhalten Sie nun die transponierten Werte.

Wenn Sie nun Werte im Quellbereich anpassen, werden diese im Zielbereich übernommen.


Eine weitere Einsatzmöglichkeit ist die Verwendung der Matrixformel mit benannten Bereichen. In unserem Beispiel wurden die vier Quartalszeilen pro Jahr (2010, 2011 etc.) markiert und mit Hilfe des Namensmanagers benannt (Y_2010, Y_2011 etc). Im zweiten Beispiel kann in Zelle C36 das Jahr ausgewählt werden. Durch die Kombination der Formel Indirekt (die das ausgewählte Jahr aus Zelle C36 übernimmt) mit der Matrixformel MTrans lässt sich ein dynamischer Bereich erstellen.

Norbert Engelhardt

Senior Consultant - Information Design & Reporting

pmOne AG

Norbert Engelhardt hat nach seiner kaufmännischen Ausbildung im Pharmabereich in den Vetriebsinnendienst gewechselt und war über drei Jahre als Analyst für das Controlling im Vertrieb und Marketing tätig. Er verantwortete das übergreifende Berichtswesen sowie die Marktforschung für die Business Unit. Frühzeitig hat er sich mit dem Konzept und dem Aufbau des Berichtswesens beschäftigt, vorzugsweise basierend auf den heutigen Microsoft-Technologien. Im Rahmen der Green Belt Zertifizierung hat er bereits mehrere Projekte als Teilprojektleiter oder Projektleiter begleitet. Seit 2011 ist er als Consultant für Information Design und Reporting für die pmOne AG tätig und der Experte für komplexe Berichtsanforderungen. Norbert Engelhardt ist HICHERT CERTIFIED CONSULTANT (HCC).

Blog-Beiträge von diesem Autor