Hoe cellen in een kolom transponeren op basis van unieke waarden in een andere kolom?
Stel dat u een gegevensbereik heeft dat twee kolommen bevat, nu wilt u cellen in één kolom transponeren naar horizontale rijen op basis van unieke waarden in een andere kolom om het volgende resultaat te krijgen. Heeft u goede ideeën om dit probleem in Excel op te lossen?
Transponeer cellen in één kolom op basis van unieke waarden met formules
Transponeer cellen in één kolom op basis van unieke waarden met VBA-code
Transponeer cellen in één kolom op basis van unieke waarden met Kutools voor Excel
Transponeer cellen in één kolom op basis van unieke waarden met formules
Met de volgende matrixformules kunt u de unieke waarden extraheren en de bijbehorende gegevens in horizontale rijen transponeren, doe dit als volgt:
1. Voer deze matrixformule in: = INDEX ($ A $ 2: $ A $ 16, MATCH (0, AANTAL.ALS ($ D $ 1: $ D1, $ A $ 2: $ A $ 16), 0)) in een lege cel, bijvoorbeeld D2, en druk op Shift+Ctrl+Enter toetsen samen om het juiste resultaat te krijgen, zie screenshot:
Note: In de bovenstaande formule, A2: A16 is de kolom waarvan u de unieke waarden wilt weergeven, en D1 is de cel boven deze formulecel.
2. Sleep vervolgens de vulgreep naar de cellen om alle unieke waarden te extraheren, zie screenshot:
3. En ga dan verder met het invoeren van deze formule in cel E2: =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), en vergeet niet om op te drukken Shift+Ctrl+Enter toetsen om het resultaat te krijgen, zie screenshot:
Note: In bovenstaande formule: B2: B16 zijn de kolomgegevens die u wilt transponeren, A2: A16 is de kolom waarop u de waarden wilt transponeren op basis van, en D2 bevat de unieke waarde die u in stap 1 heeft geëxtraheerd.
4. Sleep vervolgens de vulgreep naar rechts van de cellen waarvan u de getransponeerde gegevens wilt weergeven totdat 0 wordt weergegeven, zie screenshot:
5. En ga vervolgens door met het slepen van de vulgreep naar het celbereik om de getransponeerde gegevens te krijgen zoals in het volgende screenshot:
Transponeer cellen in één kolom op basis van unieke waarden met VBA-code
Misschien zijn de formules ingewikkeld om te begrijpen, hier kunt u de volgende VBA-code uitvoeren om het gewenste resultaat te krijgen dat u nodig hebt.
1. Houd de ALT + F11 toetsen om de te openen Microsoft Visual Basic voor toepassingen venster.
2. Klikken Invoegen > Moduleen plak de volgende code in het Module Venster.
VBA-code: transponeer cellen in één kolom op basis van unieke waarden in een andere kolom:
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. Druk vervolgens op F5 toets om deze code uit te voeren, en er verschijnt een promptvenster om u eraan te herinneren het gegevensbereik te selecteren dat u wilt gebruiken, zie screenshot:
4. En klik vervolgens op OK knop, verschijnt er een ander promptvenster om u eraan te herinneren een cel te selecteren om het resultaat te plaatsen, zie screenshot:
6. Klikken OK knop, en de gegevens in kolom B zijn getransponeerd op basis van unieke waarden in kolom A, zie screenshot:
Transponeer cellen in één kolom op basis van unieke waarden met Kutools voor Excel
Als je Kutools for Excel, het combineren van de Geavanceerd Combineer rijen en Gespleten cellen hulpprogramma's, kunt u deze taak snel voltooien zonder formules of code.
Kutools for Excel : met meer dan 300 handige Excel-invoegtoepassingen, gratis te proberen zonder beperking in 30 dagen. |
Na het installeren van Kutools for Excelgaat u als volgt te werk:
1. Selecteer het gegevensbereik dat u wilt gebruiken. (Als u de originele gegevens wilt behouden, kopieer en plak de gegevens dan eerst op een andere locatie.)
2. Dan klikken Kutools > Samenvoegen en splitsen > Geavanceerd Combineer rijen, zie screenshot:
3. In de Combineer rijen op basis van kolom dialoogvenster, voer dan de volgende bewerkingen uit:
(1.) Klik op de kolomnaam waarop u gegevens wilt transponeren op basis van en selecteer Hoofdsleutel;
(2.) Klik op een andere kolom die u wilt transponeren, en klik Combineren Kies vervolgens een scheidingsteken om de gecombineerde gegevens te scheiden, zoals spatie, komma, puntkomma.
4. Dan klikken Ok knop, zijn de gegevens in kolom B gecombineerd in één cel op basis van kolom A, zie screenshot:
5. En selecteer vervolgens de gecombineerde cellen en klik Kutools > Samenvoegen en splitsen > Gespleten cellen, zie screenshot:
6. In de Gespleten cellen dialoogvenster, selecteer Splitsen in kolommen onder de Type optie en kies vervolgens het scheidingsteken dat uw gecombineerde gegevens scheidt, zie screenshot:
7. Dan klikken Ok knop en selecteer een cel om het gesplitste resultaat in het uitgeklapte dialoogvenster te plaatsen, zie screenshot:
8. Klikken OK, en je krijgt het resultaat zoals je nodig hebt. Zie screenshot:
Download en gratis proef Kutools voor Excel nu!
Demo: transponeer cellen in één kolom op basis van unieke waarden met Kutools voor Excel
Beste Office-productiviteitstools
Geef uw Excel-vaardigheden een boost met Kutools voor Excel en ervaar efficiëntie als nooit tevoren. Kutools voor Excel biedt meer dan 300 geavanceerde functies om de productiviteit te verhogen en tijd te besparen. Klik hier om de functie te krijgen die u het meest nodig heeft...
Office-tabblad Brengt een interface met tabbladen naar Office en maakt uw werk veel gemakkelijker
- Schakel bewerken en lezen met tabbladen in Word, Excel, PowerPoint in, Publisher, Access, Visio en Project.
- Open en maak meerdere documenten in nieuwe tabbladen van hetzelfde venster in plaats van in nieuwe vensters.
- Verhoogt uw productiviteit met 50% en vermindert honderden muisklikken voor u elke dag!