Wie kann man Zellen in einer Spalte basierend auf eindeutigen Werten in einer anderen Spalte transponieren?

Angenommen, Sie haben einen Datenbereich, der zwei Spalten enthält, und Sie möchten nun die Zellen in einer Spalte in horizontale Zeilen basierend auf den eindeutigen Werten in einer anderen Spalte transponieren, um das folgende Ergebnis zu erhalten. Haben Sie gute Ideen, wie dieses Problem in Excel gelöst werden kann?

Zellen in einer Spalte basierend auf eindeutigen Werten mit Formeln transponieren

Zellen in einer Spalte basierend auf eindeutigen Werten mit VBA-Code transponieren

Zellen in einer Spalte basierend auf eindeutigen Werten mit Kutools für Excel transponieren

Zellen in einer Spalte basierend auf eindeutigen Werten mit Formeln transponieren

Mit den folgenden Array-Formeln können Sie die eindeutigen Werte extrahieren und die entsprechenden Daten in horizontale Zeilen transponieren. Gehen Sie bitte wie folgt vor:

1. Geben Sie diese Array-Formel ein: =INDEX($A$2:$A$16; VERGLEICH(0; ZÄHLENWENN($D$1:$D1; $A$2:$A$16); 0)) in eine leere Zelle, z.B. D2, und drücken Sie gleichzeitig die Tasten Umschalt + Strg + Eingabe, um das richtige Ergebnis zu erhalten, siehe Screenshot:

Hinweis: In der obigen Formel ist A2:A16 die Spalte, aus der Sie die eindeutigen Werte auflisten möchten, und D1 ist die Zelle über dieser Formelzelle.

2. Ziehen Sie dann den Ausfüllkursor nach unten in die Zellen, um alle eindeutigen Werte zu extrahieren, siehe Screenshot:

3. Geben Sie als Nächstes diese Formel in Zelle E2 ein: =WENNFEHLER(INDEX($B$2:$B$16; VERGLEICH(0; ZÄHLENWENN($D2:D2;$B$2:$B$16)+WENN($A$2:$A$16<>$D2; 1; 0); 0)); 0), und vergessen Sie nicht, die Tasten Umschalt + Strg + Eingabe zu drücken, um das Ergebnis zu erhalten, siehe Screenshot:

Hinweis: In der obigen Formel ist B2:B16 die Spaltendaten, die Sie transponieren möchten, A2:A16 ist die Spalte, auf deren Basis Sie die Werte transponieren möchten, und D2 enthält den eindeutigen Wert, den Sie im Schritt 1 extrahiert haben.

4. Ziehen Sie dann den Ausfüllkursor nach rechts in die Zellen, in denen Sie die transponierten Daten auflisten möchten, bis eine 0 angezeigt wird, siehe Screenshot:

5. Ziehen Sie anschließend den Ausfüllkursor nach unten in den Zellbereich, um die transponierten Daten wie im folgenden Screenshot dargestellt zu erhalten:

Zellen in einer Spalte basierend auf eindeutigen Werten mit VBA-Code transponieren

Vielleicht sind die Formeln zu komplex für Sie zu verstehen. Hier können Sie den folgenden VBA-Code ausführen, um das gewünschte Ergebnis zu erzielen.

1. Halten Sie die Tasten ALT + F11 gedrückt, um das Fenster Microsoft Visual Basic for Applications zu öffnen.

2. Klicken Sie auf Einfügen > Modul und fügen Sie den folgenden Code in das Modulfenster ein.

VBA-Code: Zellen in einer Spalte basierend auf eindeutigen Werten in einer anderen Spalte transponieren:

Sub transposeunique()
'updateby Extendoffice
    Dim xLRow As Long
    Dim i As Long
    Dim xCrit As String
    Dim xCol  As New Collection
    Dim xRg As Range
    Dim xOutRg As Range
    Dim xTxt As String
    Dim xCount As Long
    Dim xVRg As Range
    On Error Resume Next
    xTxt = ActiveWindow.RangeSelection.Address
    Set xRg = Application.InputBox("please select data range(only two columns):", "Kutools for Excel", xTxt, , , , , 8)
    Set xRg = Application.Intersect(xRg, xRg.Worksheet.UsedRange)
    If xRg Is Nothing Then Exit Sub
    If (xRg.Columns.Count <> 2) Or _
       (xRg.Areas.Count > 1) Then
        MsgBox "the used range is only one area with two columns ", , "Kutools for Excel"
        Exit Sub
    End If
    Set xOutRg = Application.InputBox("please select output range(specify one cell):", "Kutools for Excel", xTxt, , , , , 8)
    If xOutRg Is Nothing Then Exit Sub
    Set xOutRg = xOutRg.Range(1)
    xLRow = xRg.Rows.Count
    For i = 2 To xLRow
        xCol.Add xRg.Cells(i, 1).Value, xRg.Cells(i, 1).Value
    Next
    Application.ScreenUpdating = False
    For i = 1 To xCol.Count
        xCrit = xCol.Item(i)
        xOutRg.Offset(i, 0) = xCrit
        xRg.AutoFilter Field:=1, Criteria1:=xCrit
        Set xVRg = xRg.Range("B2:B" & xLRow).SpecialCells(xlCellTypeVisible)
        If xVRg.Count > xCount Then xCount = xVRg.Count
        xRg.Range("B2:B" & xLRow).SpecialCells(xlCellTypeVisible).Copy
        xOutRg.Offset(i, 1).PasteSpecial Paste:=xlPasteAll, Operation:=xlNone, SkipBlanks:=False, Transpose:=True
        Application.CutCopyMode = False
    Next
    xOutRg = xRg.Cells(1, 1)
    xOutRg.Offset(0, 1).Resize(1, xCount) = xRg.Cells(1, 2)
    xRg.Rows(1).Copy
    xOutRg.Resize(1, xCount + 1).PasteSpecial Paste:=xlPasteFormats
    xRg.AutoFilter
    Application.ScreenUpdating = True
End Sub

3. Drücken Sie dann die Taste F5, um diesen Code auszuführen, und es erscheint ein Dialogfeld, das Sie auffordert, den zu verwendenden Datenbereich auszuwählen, siehe Screenshot:

4. Klicken Sie dann auf die Schaltfläche OK, und ein weiteres Dialogfeld erscheint, das Sie auffordert, eine Zelle auszuwählen, in der das Ergebnis platziert werden soll, siehe Screenshot:

6. Klicken Sie auf OK, und die Daten in Spalte B wurden basierend auf den eindeutigen Werten in Spalte A transponiert, siehe Screenshot:

Zellen in einer Spalte basierend auf eindeutigen Werten mit Kutools für Excel transponieren

Wenn Sie Kutools für Excel haben, können Sie durch die Kombination der Funktionen Erweiterte Zeilen zusammenführen und Zellen aufteilen diese Aufgabe schnell ohne Formeln oder Code abschließen.

Nach der Installation von Kutools für Excel gehen Sie bitte wie folgt vor:

1. Wählen Sie den Datenbereich aus, den Sie verwenden möchten. (Wenn Sie die Originaldaten beibehalten möchten, kopieren Sie die Daten zunächst an einen anderen Ort.)

2. Klicken Sie dann auf Kutools > Zusammenführen & Aufteilen > Erweiterte Zeilen zusammenführen, siehe Screenshot:

3. Im Dialogfeld Zeilen basierend auf Spalte zusammenführen führen Sie bitte die folgenden Schritte aus:

(1.) Klicken Sie auf den Spaltennamen, auf dessen Basis Sie die Daten transponieren möchten, und wählen Sie Primärschlüssel;

(2.) Klicken Sie auf eine andere Spalte, die Sie transponieren möchten, und klicken Sie auf Konsolidieren, dann wählen Sie ein Trennzeichen zur Trennung der zusammengeführten Daten, wie Leerzeichen, Komma, Semikolon.

4. Klicken Sie dann auf die Schaltfläche OK, und die Daten in Spalte B wurden basierend auf Spalte A in einer Zelle zusammengeführt, siehe Screenshot:

5. Wählen Sie anschließend die zusammengeführten Zellen aus, und klicken Sie auf Kutools > Zusammenführen & Aufteilen > Zellen aufteilen, siehe Screenshot:

6. Im Dialogfeld Zellen aufteilen wählen Sie unter der Option Typ die Option Daten aufteilen und wählen Sie dann das Trennzeichen, das Ihre zusammengeführten Daten trennt, siehe Screenshot:

7. Klicken Sie dann auf die Schaltfläche OK, und wählen Sie eine Zelle aus, um das geteilte Ergebnis im aufgetauchten Dialogfeld zu platzieren, siehe Screenshot:

8. Klicken Sie auf OK, und Sie erhalten das gewünschte Ergebnis. Siehe Screenshot:

Jetzt Kutools für Excel herunterladen und kostenlos testen!

Demo: Zellen in einer Spalte basierend auf eindeutigen Werten mit Kutools für Excel transponieren