Hoe transponeert u cellen uit één kolom op basis van unieke waarden in een andere kolom?
Stel dat u een gegevensbereik hebt met twee kolommen en u de cellen uit één kolom horizontaal wilt transponeren op basis van unieke waarden uit de andere kolom om het gewenste resultaat te krijgen. Kent u een efficiënte oplossing hiervoor in Excel?
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 extraheert u eenvoudig unieke waarden en transponeert u hun bijbehorende gegevens naar horizontale rijen. Volg deze stappen:
1. Voer deze matrixformule in een lege cel in, bijvoorbeeld D2:=INDEX($A$2:$A$16;VERGELIJKEN(0;AANTAL.ALS($D$1:$D1;$A$2:$A$16);0)), 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 u de unieke waarden wilt ophalen, en D1 de cel direct boven de formulecel.
2. Sleep daarna de vulgreep naar beneden om alle unieke waarden op te halen. Zie de screenshot:
3. Voer vervolgens de volgende formule in cel E2 in:=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 de bovenstaande formule is B2:B16 de kolom met gegevens die u wilt transponeren, A2:A16 de kolom waarop u de transpositie baseert, en bevat D2 de unieke waarde die u in stap 1 hebt geëxtraheerd.
4. Sleep daarna de vulgreep naar rechts tot u de cellen hebt bereikt waarin u de getransponeerde gegevens wilt weergeven, totdat er 0 verschijnt. Zie screenshot:
5. Sleep vervolgens de vulgreep verder naar beneden over het celbereik om de getransponeerde gegevens te verkrijgen, zoals in de onderstaande screenshot wordt weergegeven:
Cellen in één kolom transponeren op basis van unieke waarden met VBA-code
De formules kunnen lastig te begrijpen zijn. Voer in plaats daarvan de volgende VBA-code uit 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 Module-venster.
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 daarna op de toets F5 om deze code uit te voeren. Er verschijnt een dialoogvenster waarin u wordt gevraagd het gegevensbereik te selecteren dat u wilt gebruiken. Zie screenshot:
4. Klik daarna op de knop OK. Er verschijnt een nieuw dialoogvenster waarin u wordt gevraagd een cel te selecteren om het resultaat in te plaatsen. Zie screenshot:
6. Klik op de knop OK en de gegevens in kolom B worden getransponeerd op basis van de unieke waarden in kolom A. Zie screenshot:
Cellen in één kolom transponeren op basis van unieke waarden met Kutools voor Excel
Als u Kutools voor Excel hebt, kunt u deze taak razendsnel uitvoeren—zonder formules of code—door de hulpmiddelen Geavanceerd samenvoegen van rijen en Cellen splitsen te combineren.
Na installatie van Kutools voor Excel, volgt u deze stappen:
1. Selecteer het gegevensbereik dat u wilt gebruiken. (Als u de oorspronkelijke gegevens wilt behouden, kopieer en plak deze dan eerst naar een andere locatie.)
2. Klik vervolgens op Kutools > Samenvoegen en splitsen > Geavanceerd samenvoegen van rijen. Zie screenshot:
3. Voer in het dialoogvenster Rijen samenvoegen op basis van kolom de volgende handelingen uit:
(1.) Klik op de kolomnaam waarop u de gegevenstranspositie wilt baseren en selecteer Primaire sleutel;
(2.) Klik op een andere kolom die u wilt transponeren, klik op Combineren en kies een scheidingsteken om de gecombineerde gegevens te scheiden, zoals een spatie, komma of puntkomma.
4. Klik daarna op de knop Ok. De gegevens in kolom B zijn nu samengevoegd in één cel op basis van kolom A. Zie screenshot:
5. Selecteer daarna de samengevoegde cellen en klik op Kutools > Samenvoegen en splitsen > Cellen splitsen. Zie screenshot:
6. Selecteer in het dialoogvenster Cellen splitsen onder de optie Splitsen naar kolommen het type scheidingsteken waarmee uw gecombineerde gegevens zijn gescheiden. Zie screenshot:
7. Klik daarna op de knop Ok en selecteer in het verschijnende dialoogvenster een cel om het splitsresultaat te plaatsen. Zie screenshot:
8. Klik op OK en u krijgt het gewenste resultaat. Zie de screenshot:
Download en probeer gratis Kutools voor Excel nu!
Demo: Cellen in één kolom transponeren op basis van unieke waarden met Kutools voor Excel
Beste Office-productiviteitshulpmiddelen
Geef uw Excel-vaardigheden een boost met Kutools voor Excel en ervaar efficiëntie zoals nooit tevoren.Kutools voor Excel biedt meer dan 300 geavanceerde functies om de productiviteit te verhogen en Tijd besparen.Klik hier om de functie te krijgen die u het meest nodig heeft...
Office Tab brengt een tabbladinterface naar Office en maakt uw werk veel eenvoudiger
- Schakel tabbladbewerking en -lezen in voor Word, Excel, PowerPoint, Publisher, Access, Visio en Project.
- Open en maak meerdere documenten aan in nieuwe tabbladen binnen hetzelfde venster, in plaats van in afzonderlijke vensters.
- Verhoogt uw productiviteit met 50 % en bespaart u dagelijks honderden muisklikken!
Alle Kutools-add-ins in één installatieprogramma.
Kutools for Office bundelt add-ins voor Excel, Word, Outlook en PowerPoint, plus Office Tab Pro—ideaal voor teams die met meerdere Office-apps werken.
- Alles-in-één suite— add-ins voor Excel, Word, Outlook & PowerPoint plus Office Tab Pro
- Één installatieprogramma, één licentie— binnen enkele minuten klaar (MSI-geschikt)
- Werkt beter samen— gestroomlijnde productiviteit in alle Office-apps
- 30 dagen volledig functionele proefversie— geen registratie, geen creditcard
- Beste prijs-kwaliteitverhouding— bespaar ten opzichte van het afzonderlijk kopen van add-ins