Comment transposer des cellules dans une colonne en fonction de valeurs uniques dans une autre colonne?

En supposant que vous ayez une plage de données contenant deux colonnes, vous souhaitez maintenant transposer les cellules d'une colonne en lignes horizontales en fonction de valeurs uniques dans une autre colonne pour obtenir le résultat suivant. Avez-vous de bonnes idées pour résoudre ce problème dans Excel?

Transposer les cellules dans une colonne en fonction de valeurs uniques avec des formules

Transposer les cellules dans une colonne en fonction de valeurs uniques avec le code VBA

Transposez les cellules dans une colonne en fonction de valeurs uniques avec Kutools for Excel

Transposer les cellules dans une colonne en fonction de valeurs uniques avec des formules

Avec les formules matricielles suivantes, vous pouvez extraire les valeurs uniques et transposer leurs données correspondantes en lignes horizontales, procédez comme suit:

1. Entrez cette formule matricielle: = INDEX ($ A $ 2: $ A $ 16, MATCH (0, COUNTIF ($ D $ 1: $ D1, $ A $ 2: $ A $ 16), 0)) dans une cellule vide, D2, par exemple, et appuyez sur Maj + Ctrl + Entrée clés ensemble pour obtenir le résultat correct, voir capture d'écran:

Notes: Dans la formule ci-dessus, A2: A16 est la colonne dont vous souhaitez répertorier les valeurs uniques, et D1 est la cellule au-dessus de cette cellule de formule.

2. Faites ensuite glisser la poignée de remplissage vers les cellules pour extraire toutes les valeurs uniques, voir capture d'écran:

3. Et puis continuez à entrer cette formule dans la cellule 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), et n'oubliez pas d'appuyer sur Maj + Ctrl + Entrée clés pour obtenir le résultat, voir capture d'écran:

Notes: Dans la formule ci-dessus: B2: B16 correspond aux données de la colonne que vous souhaitez transposer, A2: A16 est la colonne sur laquelle vous souhaitez transposer les valeurs en fonction, et D2 contient la valeur unique que vous avez extraite à l'étape 1.

4. Faites ensuite glisser la poignée de remplissage à droite des cellules dans lesquelles vous souhaitez lister les données transposées jusqu'à ce que affiche 0, voir capture d'écran:

5. Et puis continuez à faire glisser la poignée de remplissage vers la plage de cellules pour obtenir les données transposées comme illustré ci-dessous:

Transposer les cellules dans une colonne en fonction de valeurs uniques avec le code VBA

Peut-être que les formules sont complexes à comprendre, ici, vous pouvez exécuter le code VBA suivant pour obtenir le résultat souhaité dont vous avez besoin.

1. Maintenez le ALT + F11 clés pour ouvrir le Microsoft Visual Basic pour applications fenêtre.

2Cliquez sur insérer > Moduleet collez le code suivant dans le Module Fenêtre.

Code VBA: transposez les cellules dans une colonne en fonction de valeurs uniques dans une autre colonne:

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. Puis appuyez F5 clé pour exécuter ce code, et une boîte de dialogue apparaîtra pour vous rappeler de sélectionner la plage de données que vous souhaitez utiliser, voir capture d'écran:

4. Et puis cliquez OK bouton, une autre boîte de dialogue apparaîtra pour vous rappeler de sélectionner une cellule pour mettre le résultat, voir capture d'écran:

6Cliquez sur OK bouton, et les données de la colonne B ont été transposées en fonction des valeurs uniques de la colonne A, voir capture d'écran:

Transposez les cellules dans une colonne en fonction de valeurs uniques avec Kutools for Excel

Si vous avez Kutools for Excel, combinant le Lignes de combinaison avancées et Cellules divisés utilitaires, vous pouvez terminer rapidement cette tâche sans aucune formule ni code.

Après l'installation de Kutools for Excel, procédez comme suit:

1. Sélectionnez la plage de données que vous souhaitez utiliser. (Si vous souhaitez conserver les données d'origine, veuillez d'abord copier et coller les données dans un autre emplacement.)

2. Puis clique Kutools > Fusionner et fractionner > Lignes de combinaison avancées, voir capture d'écran:

3. Dans le Combiner des lignes en fonction de la colonne boîte de dialogue, veuillez effectuer les opérations suivantes:

(1.) Cliquez sur le nom de la colonne sur laquelle vous souhaitez transposer les données, puis sélectionnez Clé primaire;

(2.) Cliquez sur une autre colonne que vous souhaitez transposer, puis cliquez sur Combiner puis choisissez un séparateur pour séparer les données combinées, telles que l'espace, la virgule, le point-virgule.

4. Puis clique Ok bouton, les données de la colonne B ont été combinées dans une cellule basée sur la colonne A, voir capture d'écran:

5. Et puis sélectionnez les cellules combinées et cliquez sur Kutools > Fusionner et fractionner > Cellules divisés, voir capture d'écran:

6. Dans le Cellules divisés boîte de dialogue, sélectionnez Fractionner en colonnes sous le Type option, puis choisissez le séparateur qui sépare vos données combinées, voir capture d'écran:

7. Puis clique Ok et sélectionnez une cellule pour mettre le résultat de la division dans la boîte de dialogue qui apparaît, voir capture d'écran:

8Cliquez sur OK, et vous obtiendrez le résultat dont vous avez besoin. Voir la capture d'écran:

Téléchargez et essayez gratuitement Kutools for Excel Now!

Démo : transposez les cellules dans une colonne en fonction de valeurs uniques avec Kutools for Excel