Transpose-Funktion in Excel

Von Zeit zu Zeit kann ein Excel-Benutzer die Aufgabe haben, eine Reihe von Daten, die eine horizontale Struktur haben, in eine vertikale umzuwandeln. Dieser Vorgang wird Transposition genannt. Dieses Wort ist den meisten Menschen neu, da Sie diese Operation bei normaler PC-Arbeit nicht verwenden müssen. Wer jedoch mit großen Datenmengen arbeiten muss, muss wissen, wie es geht. Heute werden wir ausführlicher darüber sprechen, wie es ausgeführt wird, mit welcher Funktion, und uns auch einige andere Methoden im Detail ansehen.

TRANSPOSE-Funktion – Zellbereiche in Excel transponieren

Eine der interessantesten und funktionellsten Tabellentranspositionsmethoden in Excel ist die Funktion TRANSP. Mit seiner Hilfe können Sie einen horizontalen Datenbereich in einen vertikalen umwandeln oder die umgekehrte Operation ausführen. Mal sehen, wie man damit arbeitet.

Funktionssyntax

Die Syntax für diese Funktion ist unglaublich einfach: TRANSPOSE(Array). Das heißt, wir müssen nur ein Argument verwenden, nämlich einen Datensatz, der in eine horizontale oder vertikale Ansicht konvertiert werden muss, je nachdem, was er ursprünglich war.

Vertikale Bereiche von Zellen (Spalten) transponieren

Angenommen, wir haben eine Spalte mit dem Bereich B2:B6. Sie können sowohl vorgefertigte Werte als auch Formeln enthalten, die Ergebnisse an diese Zellen zurückgeben. Es ist für uns nicht so wichtig, eine Umsetzung ist in beiden Fällen möglich. Nach Anwendung dieser Funktion entspricht die Zeilenlänge der ursprünglichen Bereichsspaltenlänge.

Die Reihenfolge der Schritte zur Verwendung dieser Formel ist wie folgt:

1. Wählen Sie eine Zeile aus. In unserem Fall hat es eine Länge von fünf Zellen.
2. Bewegen Sie danach den Cursor auf die Formelleiste und geben Sie dort die Formel ein =TRANSP(B2:B6).
3. Drücken Sie die Tastenkombination Strg + Umschalt + Enter.

In Ihrem Fall müssen Sie natürlich den Bereich angeben, der für Ihre Tabelle typisch ist.

Horizontale Zellbereiche (Zeilen) transponieren

Im Prinzip ist der Wirkungsmechanismus fast derselbe wie im vorherigen Absatz. Angenommen, wir haben eine Zeichenfolge mit den Start- und Endkoordinaten B10:F10. Es kann auch sowohl direkt Werte als auch Formeln enthalten. Machen wir daraus eine Spalte, die die gleichen Abmessungen wie die ursprüngliche Zeile hat. Die Reihenfolge der Aktionen ist wie folgt:

1. Wählen Sie diese Spalte mit der Maus aus. Sie können auch die Tasten auf der Tastatur Strg und den Abwärtspfeil verwenden, nachdem Sie auf die oberste Zelle dieser Spalte geklickt haben.
2. Danach schreiben wir die Formel =TRANSP(B10:F10) in der Bearbeitungsleiste.
3. Wir schreiben es als Matrixformel mit der Tastenkombination Strg + Umschalt + Enter.

Transponieren mit Paste Special

Eine weitere mögliche Transpositionsoption ist die Verwendung der Funktion „Inhalte einfügen“. Dies ist kein Operator mehr, der in Formeln verwendet wird, aber es ist auch eine der beliebten Methoden, um Spalten in Zeilen umzuwandeln und umgekehrt.

Diese Option befindet sich auf der Registerkarte Startseite. Um darauf zuzugreifen, müssen Sie die Gruppe „Zwischenablage“ und dort die Schaltfläche „Einfügen“ finden. Öffnen Sie danach das Menü, das sich unter dieser Option befindet, und wählen Sie den Punkt „Transponieren“. Zuvor müssen Sie den Bereich auswählen, den Sie auswählen möchten. Als Ergebnis erhalten wir die gleiche Reichweite, nur das Gegenteil wird gespiegelt.

3 Möglichkeiten, eine Tabelle in Excel zu transponieren

Tatsächlich gibt es jedoch viel mehr Möglichkeiten, Spalten in Zeilen umzuwandeln und umgekehrt. Lassen Sie uns 3 Methoden beschreiben, mit denen wir eine Tabelle in Excel transponieren können. Wir haben zwei davon oben besprochen, aber wir werden noch ein paar Beispiele geben, damit Sie eine bessere Vorstellung davon bekommen, wie Sie dieses Verfahren durchführen.

Methode 1: Inhalte einfügen

Diese Methode ist die einfachste. Es genügt, ein paar Knöpfe zu drücken, und der Benutzer erhält eine transponierte Version der Tabelle. Lassen Sie uns ein kleines Beispiel für mehr Klarheit geben. Angenommen, wir haben eine Tabelle mit Informationen darüber, wie viele Produkte derzeit auf Lager sind und wie viel sie insgesamt kosten. Die Tabelle selbst sieht so aus.

Wir sehen, dass wir eine Kopfzeile und eine Spalte mit Produktnummern haben. In unserem Beispiel enthält die Kopfzeile Informationen darüber, welches Produkt, wie viel es kostet, wie viel es auf Lager ist und wie hoch die Gesamtkosten aller mit diesem Artikel verbundenen Produkte sind, die auf Lager sind. Wir erhalten die Kosten gemäß der Formel, bei der die Kosten mit der Menge multipliziert werden. Um das Beispiel anschaulicher zu machen, machen wir die Kopfzeile grün.

Unsere Aufgabe ist es, sicherzustellen, dass die in der Tabelle enthaltenen Informationen horizontal angeordnet sind. Das heißt, dass die Spalten zu Zeilen werden. Die Reihenfolge der Aktionen in unserem Fall ist wie folgt:

1. Wählen Sie den Datenbereich aus, den wir rotieren müssen. Danach kopieren wir diese Daten.
2. Platzieren Sie den Cursor irgendwo auf dem Blatt. Klicken Sie dann auf die rechte Maustaste und öffnen Sie das Kontextmenü.
3. Klicken Sie dann auf die Schaltfläche „Inhalte einfügen“.

Nachdem Sie diese Schritte abgeschlossen haben, müssen Sie auf die Schaltfläche „Transponieren“ klicken. Aktivieren Sie stattdessen das Kontrollkästchen neben diesem Element. Wir ändern keine anderen Einstellungen und klicken dann auf die Schaltfläche „OK“.

Nachdem Sie diese Schritte ausgeführt haben, bleibt dieselbe Tabelle, nur ihre Zeilen und Spalten sind anders angeordnet. Beachten Sie auch, dass Zellen mit denselben Informationen grün hervorgehoben sind. Frage: Was ist mit den Formeln passiert, die im ursprünglichen Sortiment waren? Ihr Standort hat sich geändert, aber sie selbst sind geblieben. Die Adressen der Zellen änderten sich einfach zu denen, die nach der Transposition gebildet wurden.

Fast die gleichen Aktionen müssen durchgeführt werden, um Werte zu transponieren, nicht Formeln. In diesem Fall müssen Sie auch das Menü Inhalte einfügen verwenden, aber vorher den Datenbereich auswählen, der die Werte enthält. Wir sehen, dass das Fenster „Inhalte einfügen“ auf zwei Arten aufgerufen werden kann: über ein spezielles Menü in der Multifunktionsleiste oder das Kontextmenü.

Methode 2. TRANSP-Funktion in Excel

Tatsächlich wird diese Methode nicht mehr so ​​aktiv genutzt wie zu Beginn des Erscheinens dieses Tabellenkalkulationsprogramms. Dies liegt daran, dass diese Methode viel komplizierter ist als die Verwendung von Inhalte einfügen. Es findet jedoch seine Verwendung bei der Automatisierung der Tabellentransposition.

Außerdem ist diese Funktion in Excel vorhanden, daher ist es unbedingt erforderlich, sie zu kennen, auch wenn sie fast nicht mehr verwendet wird. Früher haben wir über das Verfahren nachgedacht, wie man damit arbeitet. Nun werden wir dieses Wissen um ein weiteres Beispiel ergänzen.

1. Zuerst müssen wir den Datenbereich auswählen, der zum Transponieren der Tabelle verwendet wird. Sie müssen nur den Bereich im Gegenteil auswählen. In diesem Beispiel haben wir beispielsweise 4 Spalten und 6 Zeilen. Daher ist es notwendig, einen Bereich mit entgegengesetzten Eigenschaften auszuwählen: 6 Spalten und 4 Zeilen. Das Bild zeigt es sehr gut.
2. Danach fangen wir sofort an, diese Zelle auszufüllen. Es ist wichtig, die Auswahl nicht versehentlich zu entfernen. Daher müssen Sie die Formel direkt in der Formelleiste angeben.
3. Als nächstes drücken Sie die Tastenkombination Strg + Umschalt + Enter. Denken Sie daran, dass dies eine Matrixformel ist, da wir mit einer großen Menge von Daten auf einmal arbeiten, die in eine andere große Menge von Zellen übertragen werden.

Nachdem wir die Daten eingegeben haben, drücken wir die Eingabetaste, woraufhin wir das folgende Ergebnis erhalten.

Wir sehen, dass die Formel nicht in die neue Tabelle übertragen wurde. Auch die Formatierung ging verloren. Dichter

All dies muss manuell erfolgen. Denken Sie außerdem daran, dass diese Tabelle mit der ursprünglichen verwandt ist. Sobald also einige Informationen im Originalbereich geändert werden, werden diese Anpassungen automatisch an der transponierten Tabelle vorgenommen.

Daher ist diese Methode gut geeignet, wenn Sie sicherstellen müssen, dass die transponierte Tabelle mit der ursprünglichen verknüpft ist. Wenn Sie einen speziellen Einsatz verwenden, entfällt diese Möglichkeit.

Übersichtstabelle

Dies ist eine grundlegend neue Methode, die es ermöglicht, nicht nur die Tabelle zu verschieben, sondern auch eine Vielzahl von Aktionen auszuführen. Der Transpositionsmechanismus wird zwar im Vergleich zu den vorherigen Methoden etwas anders sein. Die Reihenfolge der Aktionen ist wie folgt:

1. Lassen Sie uns eine Pivot-Tabelle erstellen. Dazu müssen Sie die Tabelle auswählen, die wir transponieren müssen. Gehen Sie danach zum Punkt „Einfügen“ und suchen Sie dort nach „Pivot-Tabelle“. Ein Dialogfeld wie in diesem Screenshot wird angezeigt.
2. Hier können Sie den Bereich neu zuweisen, aus dem es erstellt werden soll, sowie eine Reihe weiterer Einstellungen vornehmen. Uns interessiert jetzt vor allem der Ort der Pivot-Tabelle – auf einem neuen Blatt.
3. Danach wird das Layout der Pivot-Tabelle automatisch erstellt. Es ist notwendig, die Gegenstände, die wir verwenden werden, darin zu markieren, und dann müssen sie an die richtige Stelle verschoben werden. In unserem Fall müssen wir das Element „Produkt“ zu „Spaltennamen“ und „Preis pro Stück“ zu „Werte“ verschieben.
4. Danach wird die Pivot-Tabelle endgültig erstellt. Ein zusätzlicher Bonus ist die automatische Berechnung des Endwertes.
5. Sie können auch andere Einstellungen ändern. Deaktivieren Sie beispielsweise den Punkt „Preis pro Stück“ und aktivieren Sie den Punkt „Gesamtkosten“. Als Ergebnis haben wir eine Tabelle mit Informationen darüber, wie viel die Produkte kosten. Diese Transpositionsmethode ist viel funktioneller als die anderen. Lassen Sie uns einige der Vorteile von Pivot-Tabellen beschreiben:

1. Automatisierung. Mit Hilfe von Pivot-Tabellen können Sie Daten automatisch zusammenfassen, sowie die Position von Spalten und Spalten beliebig verändern. Dazu müssen Sie keine weiteren Schritte ausführen.
2. Interaktivität. Der Benutzer kann die Informationsstruktur beliebig oft ändern, um seine Aufgaben zu erfüllen. Sie können beispielsweise die Reihenfolge der Spalten ändern und Daten beliebig gruppieren. Dies kann so oft durchgeführt werden, wie der Benutzer benötigt. Und es dauert buchstäblich weniger als eine Minute.
3. Einfach zu formatierende Daten. Es ist sehr einfach, eine Pivot-Tabelle so anzuordnen, wie es eine Person möchte. Dazu genügt ein paar Mausklicks.
4. Werte bekommen. Die überwältigende Anzahl von Formeln, die zur Erstellung von Berichten verwendet werden, befinden sich im direkten Zugriff einer Person und lassen sich leicht in eine Pivot-Tabelle integrieren. Dies sind Daten wie Summierung, Bildung des arithmetischen Mittels, Bestimmung der Anzahl der Zellen, Multiplikation, Finden der größten und kleinsten Werte in der angegebenen Stichprobe.
5. Möglichkeit, zusammenfassende Diagramme zu erstellen. Wenn PivotTables neu berechnet werden, werden die zugehörigen Diagramme automatisch aktualisiert. Es können beliebig viele Diagramme erstellt werden. Alle können für eine bestimmte Aufgabe geändert werden und sind nicht miteinander verbunden.
6. Möglichkeit, Daten zu filtern.
7. Es ist möglich, eine Pivot-Tabelle basierend auf mehr als einem Satz von Quellinformationen zu erstellen. Daher wird ihre Funktionalität noch größer.

Richtig, bei der Verwendung von Pivot-Tabellen sind folgende Einschränkungen zu beachten:

1. Nicht alle Informationen können zur Generierung von Pivot-Tabellen verwendet werden. Bevor sie für diesen Zweck verwendet werden können, müssen die Zellen normalisiert werden. In einfachen Worten, machen Sie es richtig. Zwingende Anforderungen: Vorhandensein einer Kopfzeile, Fülle aller Zeilen, Gleichheit der Datenformate.
2. Die Daten werden halbautomatisch aktualisiert. Um neue Informationen in der Pivot-Tabelle zu erhalten, müssen Sie auf eine spezielle Schaltfläche klicken.
3. Pivot-Tabellen nehmen viel Platz ein. Dies kann zu einer Unterbrechung des Computers führen. Außerdem lässt sich die Datei dadurch nur schwer per E-Mail versenden.

Außerdem hat der Benutzer nach dem Erstellen einer Pivot-Tabelle nicht die Möglichkeit, neue Informationen hinzuzufügen.