Wie transponiere ich Zellen in einer Spalte basierend auf eindeutigen Werten in einer anderen Spalte?
Angenommen, Sie haben einen Datenbereich, der zwei Spalten enthält. Jetzt möchten Sie Zellen in einer Spalte in horizontale Zeilen basierend auf eindeutigen Werten in einer anderen Spalte transponieren, um das folgende Ergebnis zu erhalten. Haben Sie gute Ideen, um dieses Problem in Excel zu lösen?
Transponieren Sie Zellen in einer Spalte basierend auf eindeutigen Werten mit Formeln
Transponieren Sie Zellen in einer Spalte basierend auf eindeutigen Werten mit VBA-Code
Transponieren Sie Zellen in einer Spalte basierend auf eindeutigen Werten mit Kutools for Excel
Transponieren Sie Zellen in einer Spalte basierend auf eindeutigen Werten mit Formeln
Mit den folgenden Array-Formeln können Sie die eindeutigen Werte extrahieren und die entsprechenden Daten in horizontale Zeilen umwandeln. Gehen Sie dazu wie folgt vor:
1. Geben Sie diese Array-Formel ein: = INDEX ($ A $ 2: $ A $ 16, MATCH (0, COUNTIF ($ D $ 1: $ D1, $ A $ 2: $ A $ 16), 0)) in eine leere Zelle, z. B. D2, und drücken Sie Umschalt + Strg + Eingabetaste Tasten zusammen, um das richtige Ergebnis zu erhalten, siehe Screenshot:
Note: In der obigen Formel A2: A16 ist die Spalte, in der Sie die eindeutigen Werte von und auflisten möchten D1 ist die Zelle über dieser Formelzelle.
2. Ziehen Sie dann den Füllpunkt nach unten in die Zellen, um alle eindeutigen Werte zu extrahieren (siehe Abbildung):
3. Und dann geben Sie diese Formel in Zelle E2 ein: =IFERROR(INDEX($B$2:$B$16, MATCH(0, COUNTIF($D2:D2,$B$2:$B$16)+IF($A$2:$A$16<>$D2, 1, 0), 0)), 0)und denken Sie daran, zu drücken Umschalt + Strg + Eingabetaste Tasten, um das Ergebnis zu erhalten, siehe Screenshot:
Note: In der obigen Formel: B2: B16 sind die Spaltendaten, die Sie transponieren möchten. A2: A16 ist die Spalte, in die Sie die Werte basierend auf und transponieren möchten D2 enthält den eindeutigen Wert, den Sie in Schritt 1 extrahiert haben.
4. Ziehen Sie dann den Füllpunkt rechts neben die Zellen, in denen Sie die transponierten Daten auflisten möchten, bis 0 angezeigt wird (siehe Abbildung):
5. Ziehen Sie dann den Füllpunkt weiter nach unten in den Zellenbereich, um die transponierten Daten wie im folgenden Screenshot zu erhalten:
Transponieren Sie Zellen in einer Spalte basierend auf eindeutigen Werten mit VBA-Code
Möglicherweise sind die Formeln für Sie komplex. Hier können Sie den folgenden VBA-Code ausführen, um das gewünschte Ergebnis zu erzielen.
1. Halten Sie die Taste gedrückt ALT + F11 Schlüssel zum Öffnen der Microsoft Visual Basic für Applikationen Fenster.
2. Klicken Sie Insert > Modulund fügen Sie den folgenden Code in das Feld ein Modul Fenster.
VBA-Code: Transponieren Sie Zellen in einer Spalte basierend auf eindeutigen Werten in einer anderen Spalte:
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. Dann drücken F5 Wenn Sie die Taste drücken, um diesen Code auszuführen, wird ein Eingabeaufforderungsfeld angezeigt, das Sie daran erinnert, den Datenbereich auszuwählen, den Sie verwenden möchten. Siehe Screenshot:
4. Und dann klick OK Wenn Sie auf die Schaltfläche klicken, wird ein weiteres Eingabeaufforderungsfeld angezeigt, das Sie daran erinnert, eine Zelle für das Ergebnis auszuwählen. Siehe Screenshot:
6. Klicken Sie OK Schaltfläche, und die Daten in Spalte B wurden basierend auf eindeutigen Werten in Spalte A transponiert, siehe Screenshot:
Transponieren Sie Zellen in einer Spalte basierend auf eindeutigen Werten mit Kutools for Excel
Wenn Sie Kutools for Excel, kombiniert die Erweiterte Zeilen kombinieren und Zellen teilen Dienstprogramme können Sie diese Aufgabe schnell ohne Formeln oder Code beenden.
| Kutools for Excel : Mit mehr als 300 praktischen Excel-Add-Ins können Sie diese innerhalb von 30 Tagen ohne Einschränkung testen. |
Nach der Installation Kutools for ExcelBitte gehen Sie wie folgt vor:
1. Wählen Sie den Datenbereich aus, den Sie verwenden möchten. (Wenn Sie die Originaldaten behalten möchten, kopieren Sie die Daten zunächst und fügen Sie sie an einem anderen Ort ein.)
2. Dann klick Kutoolen > Zusammenführen & Teilen > Erweiterte Zeilen kombinieren, siehe Screenshot:
3. In dem Zeilen basierend auf Spalte kombinieren Im Dialogfeld führen Sie bitte die folgenden Vorgänge aus:
(1.) Klicken Sie auf den Spaltennamen, auf dessen Grundlage Sie 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 Kombinieren Wählen Sie dann ein Trennzeichen, um die kombinierten Daten wie Leerzeichen, Komma und Semikolon zu trennen.
4. Dann klick Ok Schaltfläche, wurden die Daten in Spalte B in einer Zelle basierend auf der Spalte A zusammengefasst, siehe Screenshot:
5. Wählen Sie dann die kombinierten Zellen aus und klicken Sie auf Kutoolen > Zusammenführen & Teilen > Zellen teilen, siehe Screenshot:
6. In dem Zellen teilen Dialogfeld auswählen In Spalten teilen unter dem Typ Wählen Sie dann das Trennzeichen aus, das Ihre kombinierten Daten trennt (siehe Abbildung):
7. Dann klick Ok Klicken Sie auf die Schaltfläche, und wählen Sie eine Zelle aus, um das Teilungsergebnis in das ausgeblendete Dialogfeld einzufügen. Siehe Screenshot:
8. Klicken Sie OK, und Sie erhalten das Ergebnis nach Bedarf. Siehe Screenshot:
Laden Sie Kutools for Excel jetzt herunter und testen Sie es kostenlos!
Demo: Transponieren Sie Zellen in einer Spalte basierend auf eindeutigen Werten mit Kutools for Excel
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, um die Produktivität zu steigern und Zeit zu sparen. Klicken Sie hier, um die Funktion zu erhalten, die Sie am meisten benötigen ...
Office Tab Bringt die Oberfläche mit Registerkarten in Office und erleichtert Ihnen die Arbeit erheblich
- Aktivieren Sie das Bearbeiten und Lesen von Registerkarten in Word, Excel und PowerPoint, Publisher, Access, Visio und Project.
- Öffnen und erstellen Sie mehrere Dokumente in neuen Registerkarten desselben Fensters und nicht in neuen Fenstern.
- Steigert Ihre Produktivität um 50 % und reduziert jeden Tag Hunderte von Mausklicks für Sie!
