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

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

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

Transposer des cellules dans une colonne en fonction des valeurs uniques avec du code VBA

Transposer des cellules dans une colonne en fonction des valeurs uniques avec Kutools pour Excel

Transposer des cellules dans une colonne en fonction des 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, veuillez procéder 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, puis appuyez simultanément sur les touches Maj + Ctrl + Entrée pour obtenir le bon résultat, voir capture d'écran :

Remarque : Dans la formule ci-dessus, A2:A16 est la colonne à partir de laquelle vous souhaitez lister les valeurs uniques, et D1 est la cellule au-dessus de cette cellule de formule.

2. Ensuite, faites glisser la poignée de recopie vers le bas jusqu'aux cellules pour extraire toutes les valeurs uniques, voir capture d'écran :

3. Puis continuez en entrant cette formule dans la cellule E2 : =SIERREUR(INDEX($B$2:$B$16, EQUIV(0, NB.SI($D2:D2,$B$2:$B$16)+SI($A$2:$A$16<>$D2, 1, 0), 0)), 0), et n'oubliez pas d'appuyer sur les touches Maj + Ctrl + Entrée pour obtenir le résultat, voir capture d'écran :

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

4. Ensuite, faites glisser la poignée de recopie vers la droite des cellules où vous souhaitez lister les données transposées jusqu'à ce qu'un 0 s'affiche, voir capture d'écran :

5. Continuez ensuite à faire glisser la poignée de recopie vers le bas sur la plage de cellules pour obtenir les données transposées comme le montre la capture d'écran suivante :

Transposer des cellules dans une colonne en fonction des valeurs uniques avec du code VBA

Les formules peuvent être complexes à comprendre pour vous, ici, vous pouvez exécuter le code VBA suivant pour obtenir le résultat souhaité.

1. Maintenez les touches ALT + F11 enfoncées pour ouvrir la fenêtre Microsoft Visual Basic pour Applications.

2. Cliquez sur Insertion > Module, puis collez le code suivant dans la fenêtre Module.

Code VBA : Transposer des cellules dans une colonne en fonction des 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. Appuyez ensuite sur la touche F5 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. Cliquez ensuite sur le bouton OK, une autre boîte de dialogue apparaîtra pour vous rappeler de sélectionner une cellule où placer le résultat, voir capture d'écran :

6. Cliquez sur le bouton OK, 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 :

Transposer des cellules dans une colonne en fonction des valeurs uniques avec Kutools pour Excel

Si vous avez Kutools pour Excel, en combinant les utilitaires Fusion avancée des lignes et Diviser les cellules, vous pouvez rapidement accomplir cette tâche sans aucune formule ni code.

Après avoir installé Kutools pour Excel, veuillez procéder comme suit :

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

2. Cliquez ensuite sur Kutools > Fusionner et diviser > Fusion avancée des lignes, voir capture d'écran :

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

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

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

4. Cliquez ensuite sur le bouton Ok, les données de la colonne B ont été regroupées dans une seule cellule en fonction de la colonne A, voir capture d'écran :

5. Sélectionnez ensuite les cellules combinées, et cliquez sur Kutools > Fusionner et diviser > Diviser les cellules, voir capture d'écran :

6. Dans la boîte de dialogue Diviser les cellules, sélectionnez Diviser les données sous l'option Type, puis choisissez le séparateur qui sépare vos données combinées, voir capture d'écran :

7. Cliquez ensuite sur le bouton Ok, et sélectionnez une cellule pour placer le résultat divisé dans la boîte de dialogue qui apparaît, voir capture d'écran :

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

Téléchargez et essayez gratuitement Kutools pour Excel dès maintenant !

Démo : Transposer des cellules dans une colonne en fonction des valeurs uniques avec Kutools pour Excel