Hoe cellen in één kolom te transponeren op basis van unieke waarden in een andere kolom?
Stel dat je een gegevensbereik hebt dat twee kolommen bevat, en nu wil je de cellen in één kolom horizontaal transponeren op basis van unieke waarden in een andere kolom om het volgende resultaat te krijgen. Heb je goede ideeën om dit probleem in Excel op te lossen?
Cellen in één kolom transponeren op basis van unieke waarden met formules
Cellen in één kolom transponeren op basis van unieke waarden met VBA-code
Cellen in één kolom transponeren op basis van unieke waarden met Kutools voor Excel
Cellen in één kolom transponeren op basis van unieke waarden met formules
Met de volgende matrixformules kun je de unieke waarden extraheren en hun bijbehorende gegevens transponeren naar horizontale rijen. Volg deze stappen:
1. Voer deze matrixformule in: =INDEX($A$2:$A$16, MATCH(0, COUNTIF($D$1:$D1, $A$2:$A$16), 0)) in een lege cel, bijvoorbeeld D2, en druk tegelijkertijd op Shift + Ctrl + Enter om het juiste resultaat te krijgen, zie screenshot:
Opmerking: In de bovenstaande formule is A2:A16 de kolom waaruit je de unieke waarden wilt weergeven, en D1 is de cel boven deze formulecel.
2. Sleep vervolgens de vulgreep omlaag naar de cellen om alle unieke waarden te extraheren, zie screenshot:
3. Ga daarna 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 Shift + Ctrl + Enter te drukken om het resultaat te krijgen, zie screenshot:
Opmerking: In bovenstaande formule: B2:B16 is de kolomdata die je wilt transponeren, A2:A16 is de kolom waarop je de waarden wilt transponeren, en D2 bevat de unieke waarde die je in Stap 1 hebt geëxtraheerd.
4. Sleep vervolgens de vulgreep naar rechts van de cellen waarin je de getransponeerde gegevens wilt weergeven totdat er 0 wordt weergegeven, zie screenshot:
5. En ga door met het slepen van de vulgreep omlaag naar het bereik van cellen om de getransponeerde gegevens te krijgen zoals in de volgende schermafbeelding te zien is:
Cellen in één kolom transponeren op basis van unieke waarden met VBA-code
Misschien zijn de formules te complex voor jou om te begrijpen, hier kun je de volgende VBA-code uitvoeren om het gewenste resultaat te krijgen.
1. Houd de toetsen ALT + F11 ingedrukt om het venster Microsoft Visual Basic for Applications te openen.
2. Klik op Invoegen > Module, en plak de volgende code in het Modulevenster.
VBA-code: Cellen in één kolom transponeren 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 Sub3. Druk vervolgens op de toets F5 om deze code uit te voeren, en er verschijnt een pop-upvenster dat je eraan herinnert het gegevensbereik te selecteren dat je wilt gebruiken, zie screenshot:
4. Klik daarna op de knop OK, en er verschijnt een ander pop-upvenster dat je eraan herinnert een cel te selecteren om het resultaat in te plaatsen, zie screenshot:
6. Klik op OK, en de gegevens in kolom B zijn getransponeerd op basis van unieke waarden in kolom A, zie screenshot:
Cellen in één kolom transponeren op basis van unieke waarden met Kutools voor Excel
Als je Kutools voor Excel hebt, kun je door de combinatie van Geavanceerd samenvoegen van rijen en Cellen splitsen snel deze taak uitvoeren zonder formules of code.
Nadat je Kutools voor Excel hebt geïnstalleerd, doe dan het volgende:
1. Selecteer het gegevensbereik dat je wilt gebruiken. (Als je de originele gegevens wilt behouden, kopieer en plak de gegevens eerst naar een andere locatie.)
2. Klik vervolgens op Kutools > Samenvoegen & Splitsen > Geavanceerd samenvoegen van rijen, zie screenshot:
3. Voer in het dialoogvenster Rijen combineren op basis van kolom de volgende handelingen uit:
(1.) Klik op de kolomnaam waarop je gegevens wilt transponeren, en selecteer Hoofdsleutel;
(2.) Klik op een andere kolom die je wilt transponeren, en klik op Samenvoegen en kies een scheidingsteken om de gecombineerde gegevens te scheiden, zoals spatie, komma, puntkomma.
4. Klik vervolgens op de knop Ok, en de gegevens in kolom B zijn samengevoegd in één cel op basis van kolom A, zie screenshot:
5. Selecteer vervolgens de samengevoegde cellen, en klik op Kutools > Samenvoegen & Splitsen > Cellen splitsen, zie screenshot:
6. Selecteer in het dialoogvenster Cellen splitsen de optie Verdeel Gegevens onder de Type-optie, en kies vervolgens het scheidingsteken dat je gecombineerde gegevens scheidt, zie screenshot:
7. Klik vervolgens op de knop Ok, en selecteer een cel om het gesplitste resultaat in te plaatsen in het pop-upvenster, zie screenshot:
8. Klik op OK, en je krijgt het resultaat zoals je nodig hebt. Zie screenshot:
Download en probeer Kutools voor Excel nu gratis uit!
Demo: Cellen in één kolom transponeren op basis van unieke waarden met Kutools voor Excel
Beste productiviteitstools voor Office
Verbeter je Excel-vaardigheden met Kutools voor Excel en ervaar ongeëvenaarde efficiëntie. Kutools voor Excel biedt meer dan300 geavanceerde functies om je productiviteit te verhogen en tijd te besparen. Klik hier om de functie te kiezen die je het meest nodig hebt...
Office Tab brengt een tabbladinterface naar Office en maakt je werk veel eenvoudiger
- Activeer tabbladbewerking en -lezen in Word, Excel, PowerPoint, Publisher, Access, Visio en Project.
- Open en maak meerdere documenten in nieuwe tabbladen van hetzelfde venster, in plaats van in nieuwe vensters.
- Verhoog je productiviteit met50% en bespaar dagelijks honderden muisklikken!
Alle Kutools-invoegtoepassingen. Eén installatieprogramma
Kutools for Office-suite bundelt invoegtoepassingen voor Excel, Word, Outlook & PowerPoint plus Office Tab Pro, ideaal voor teams die werken met Office-toepassingen.
- Alles-in-één suite — invoegtoepassingen voor Excel, Word, Outlook & PowerPoint + Office Tab Pro
- Eén installatieprogramma, één licentie — in enkele minuten geïnstalleerd (MSI-ready)
- Werkt beter samen — gestroomlijnde productiviteit over meerdere Office-toepassingen
- 30 dagen volledige proef — geen registratie, geen creditcard nodig
- Beste prijs — bespaar ten opzichte van losse aanschaf van invoegtoepassingen