Wie transponiert und verknüpft man Werte in Excel?
In Excel müssen Sie beim Arbeiten mit Daten häufig Informationen neu anordnen oder in einem anderen Format darstellen. Beispielsweise kann es notwendig sein, einen Bereich zu transponieren – also Zeilen in Spalten oder umgekehrt zu verwandeln – und dabei sicherzustellen, dass die transponierten Werte weiterhin mit den Originaldaten verknüpft bleiben, sodass Aktualisierungen der Quelle sofort im transponierten Bereich sichtbar werden. Zwar bietet Excel standardmäßig die Möglichkeit, kopierte Daten als Verknüpfungen einzufügen sowie grundlegende Transponieroperationen durchzuführen, doch beide Funktionen in einem einzigen Schritt zu kombinieren, ist nicht unmittelbar vorgesehen. Dieser Artikel stellt mehrere effiziente Methoden zum gleichzeitigen Transponieren und Verknüpfen von Werten vor – inklusive detaillierter Anleitungen und praktischer Tipps.
Transponieren und Verknüpfen eingefügter Werte mithilfe eines Namens und einer Formel
Transponieren und Verknüpfen mithilfe der Suchen und Ersetzen-Funktion
Transponieren und Verknüpfen mithilfe eines VBA-Makros für dynamische Aktualisierungen
Transponieren und Verknüpfen eingefügter Werte mithilfe eines Namens und einer Formel
Mit dieser Methode können Sie eine Gruppe von Werten transponieren und gleichzeitig dynamisch mit den Originaldaten verknüpfen. Durch die Verwendung eines benannten Bereichs und einer Formel werden Änderungen am Quelldaten sofort im transponierten Bereich widergespiegelt.
1. Wählen Sie den Datenbereich aus, den Sie transponieren und verknüpfen möchten. Geben Sie im Namensfeld (neben der Formelleiste) einen aussagekräftigen Namen für diesen Bereich ein (z. B. SPORT), und drücken Sie Enter. Durch die Benennung können Sie den Bereich später innerhalb der Arbeitsmappe leichter referenzieren.
2. Wenn Sie Excel 365, Excel 2021 oder eine neuere Version verwenden, wählen Sie eine einzelne Zelle aus, in der die transponierten Daten beginnen sollen, geben Sie die Formel =TRANSPOSE(SPORT) ein (ersetzen Sie „SPORT“ durch Ihren Zellbereich) und drücken Sie Enter. Excel verteilt die transponierten Daten automatisch auf mehrere Zellen.
Wenn Sie Excel 2019 oder eine ältere Version verwenden, markieren Sie den Zielbereich, in dem die transponierten und verknüpften Daten angezeigt werden sollen, geben Sie dieselbe Formel ein und drücken Sie Umschalt + Strg + Enter, um eine Arrayformel zu erstellen.
Ihre transponierten Daten werden angezeigt und bleiben stets mit den Originaldaten synchronisiert – sobald sich die Daten im benannten Bereich ändern, aktualisiert sich die transponierte Darstellung automatisch.
Tipps und Hinweise:
- In Excel 2019 und früheren Versionen müssen Sie für Arrayformeln stets einen Bereich auswählen, der exakt der gewünschten Ausgabestruktur entspricht – umfasst Ihre Quelldaten beispielsweise 5 Zeilen, wählen Sie vor Eingabe der Formel 5 Spalten aus.
- Benannte Bereiche vereinfachen Formeln und machen sie zuverlässiger – verzichten Sie auf generische Namen und achten Sie sorgfältig auf die korrekte Schreibweise.
- Wenn Sie den ursprünglichen Datenbereich später erweitern, aktualisieren Sie entsprechend den Bezug des benannten Bereichs, damit der transponierte Bereich die neuen Daten erfasst.
- Wenn Fehler (#WERT!) angezeigt werden, überprüfen Sie die Zellenauswahl und schließen Sie die Formel mit Umschalt + Strg + Enter ab – nicht nur mit Enter.
Transponieren und Verknüpfen mithilfe der Suchen und Ersetzen-Funktion
Diese Methode kombiniert die „Suchen und Ersetzen“-Funktion von Excel mit den „Inhalte einfügen“-Optionen, um gleichzeitig zu transponieren und Verknüpfungen herzustellen. Obwohl der Ansatz etwas indirekt wirkt, eignet er sich ideal, wenn Arrayformeln ungeeignet sind oder Benutzer menügesteuerte Schritte bevorzugen.
1. Markieren Sie Ihren ursprünglichen Datenbereich. Drücken Sie Strg + C, um ihn zu kopieren. Klicken Sie mit der rechten Maustaste auf die Zelle, in die Sie die Daten einfügen möchten, und wählen Sie im Untermenü Inhalte einfügen die Option Verknüpfung. Dadurch werden Bezüge statt statischer Werte eingefügt.
2. Öffnen Sie nun das Dialogfeld Suchen und Ersetzen, indem Sie Strg + H drücken. Geben Sie im Feld Suchen nach ein = und im Feld Ersetzen durch #= ein. Damit werden Formeln vorübergehend geändert, um Probleme beim späteren Transponieren zu vermeiden.
3. Klicken Sie auf Alle ersetzen. Ein Dialogfeld zeigt an, wie viele Ersetzungen vorgenommen wurden. Klicken Sie auf OK und anschließend auf Schließen.
4. Kopieren Sie den modifizierten Bereich erneut, indem Sie Strg + C drücken. Klicken Sie mit der rechten Maustaste auf das gewünschte Ziel, wählen Sie Inhalte einfügen und dann Transponieren. So wird die Ausrichtung für Sie automatisch gewechselt.
5. Öffnen Sie mit dem weiterhin markierten transponierten Bereich erneut Suchen und Ersetzen (Strg + H). Geben Sie in Suchen nach #= ein; in Ersetzen durch geben Sie = ein, um aktive Verknüpfungen wiederherzustellen.
6. Wählen Sie erneut Alle ersetzen, bestätigen Sie den Dialog und schließen Sie das Fenster. Ihre Werte sind jetzt transponiert und bleiben mit den ursprünglichen Eingaben verknüpft.
Tipps und Hinweise:
- Stellen Sie sicher, dass Sie alle Gleichheitszeichen vor dem Transponieren ersetzen – andernfalls können Formelfehler auftreten.
- Überprüfen Sie nach Durchführung der Schritte, ob sich die transponierten Werte ändern, sobald Sie die Originaldaten anpassen – dies bestätigt, dass die Verknüpfung aktiv ist.
- Sollten Formelfehler auftreten, überprüfen Sie sowohl Ihre Aktionen unter „Suchen und Ersetzen“ als auch Ihre Zellenauswahl.
Transponieren und Verknüpfen mithilfe eines VBA-Makros für dynamische Aktualisierungen
Für Benutzer, die eine skalierbare, automatisierte Lösung benötigen – etwa wenn die verknüpften und transponierten Daten einen großen Bereich abdecken oder regelmäßig aktualisiert werden müssen – bietet sich ein VBA-Makro an, um dynamisch transponierte Verknüpfungen zu erstellen. Dieser Ansatz ist besonders wertvoll in umfangreichen oder häufig bearbeiteten Tabellen, in denen manuelle Methoden zu zeitaufwändig wären und der transponierte Bereich stets mit den Quelldaten synchron bleiben soll.
1. Wechseln Sie zur Registerkarte Entwicklertools und klicken Sie auf Visual Basic, um den Editor Microsoft Visual Basic for Applications zu öffnen. Falls die Registerkarte „Entwicklertools“ nicht sichtbar ist, lesen Sie diese Anleitung: Registerkarte Entwicklertools in Excel anzeigen.
2. Klicken Sie im VBA-Editor auf Einfügen > Modul, um ein neues Modul zu erstellen. Fügen Sie anschließend den folgenden Code in das Editorfenster ein:
Sub TransposeLinkDynamic()
Dim SourceRange As Range
Dim DestCell As Range
Dim r As Long, c As Long
Dim nRows As Long, nCols As Long
On Error Resume Next
xTitleId = "KutoolsforExcel"
Set SourceRange = Application.InputBox("Select the range to transpose and link", xTitleId, "", Type:=8)
Set DestCell = Application.InputBox("Select the top-left cell of transposed linked output", xTitleId, "", Type:=8)
nRows = SourceRange.Rows.Count
nCols = SourceRange.Columns.Count
For r = 1 To nCols
For c = 1 To nRows
DestCell.Offset(r - 1, c - 1).Formula = "=" & SourceRange.Cells(c, r).Address(ReferenceStyle:=xlA1, External:=True)
Next c
Next r
End Sub3. Um das Makro auszuführen, klicken Sie auf die Schaltfläche 
oder drücken Sie F5. Zunächst werden Sie aufgefordert, den zu transponierenden Quellbereich auszuwählen. Danach wählen Sie die obere linke Zelle des Ausgabebereichs, an der die transponierten und verknüpften Daten erscheinen sollen.
Alle Daten im Ausgabebereich enthalten Formeln, die dynamisch mit dem ursprünglichen Bereich verknüpft und in transponierter Form dargestellt werden. Das bedeutet: Sobald sich der ursprüngliche Datenbereich ändert, spiegeln sich diese Änderungen sofort im verknüpften transponierten Bereich wider – ohne dass der Vorgang erneut ausgeführt werden muss.
Tipps und Hinweise:
- Alle Formeln werden dynamisch generiert – falls Sie den Quellbereich verschieben oder dessen Größe ändern, führen Sie das Makro bei Bedarf einfach erneut aus.
- Wenn Sie Berechtigungsfehler erhalten oder Makros nicht ausführen können, stellen Sie sicher, dass die Makrosicherheit für Ihre Arbeitsmappe aktiviert ist, und speichern Sie die Datei im Format .xlsm.
Beste Office-Produktivitätstools
Verbessern Sie Ihre Excel-Kenntnisse mit Kutools für Excel und erleben Sie Effizienz wie nie zuvor.Kutools für Excel bietet über 300 erweiterte Funktionen zur Steigerung der Produktivität und Zeit sparen.Klicken Sie hier, um die Funktion zu erhalten, die Sie am dringendsten benötigen...
Office Tab bringt eine tabbasierte Oberfläche in Office und macht Ihre Arbeit viel einfacher
- Aktivieren Sie tabbasiertes Bearbeiten und Lesen in Word, Excel, PowerPoint, Publisher, Access, Visio und Project.
- Öffnen und erstellen Sie mehrere Dokumente in neuen Registerkarten desselben Fensters – statt jedes in einem separaten Fenster zu öffnen.
- Steigert Ihre Produktivität um 50 % und erspart Ihnen täglich Hunderte von Mausklicks!
Alle Kutools-Add-Ins – ein Installationsprogramm
Kutools for Office-Paket bündelt Add-Ins für Excel, Word, Outlook und PowerPoint sowie Office Tab Pro – ideal für Teams, die mit mehreren Office-Anwendungen arbeiten.
- Alles-in-einem-Paket— Add-Ins für Excel, Word, Outlook & PowerPoint sowie Office Tab Pro
- Ein Installationsprogramm, eine Lizenz— innerhalb weniger Minuten eingerichtet (MSI-fähig)
- Funktioniert besser zusammen— optimierte Produktivität über alle Office-Anwendungen hinweg
- 30-tägige Vollversion zum Testen— keine Registrierung, keine Kreditkarte erforderlich
- Bestes Preis-Leistungs-Verhältnis— sparen Sie im Vergleich zum Kauf einzelner Add-Ins