Astuce: Les autres langues sont Google-Traduction. Vous pouvez visiter le English version de ce lien.
Se connecter
x
or
x
x
S'enregistrer
x

or

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

Supposons que vous ayez une plage de données contenant deux colonnes. Maintenant, vous voulez transposer des 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?

doc transpose des valeurs uniques 1

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

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

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



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 de tableau: = 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 touches ensemble pour obtenir le résultat correct, voir capture d'écran:

doc transpose des valeurs uniques 2

Note: Dans la formule ci-dessus, A2: A16 est la colonne dont vous voulez lister 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:

doc transpose des valeurs uniques 3

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 Maj + Ctrl + Entrée touches pour obtenir le résultat, voir capture d'écran:

doc transpose des valeurs uniques 4

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

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

doc transpose des valeurs uniques 5

5. Puis continuez à faire glisser la poignée de remplissage jusqu'à la plage de cellules pour obtenir les données transposées comme suit:

doc transpose des valeurs uniques 6


Peut-être les formules sont complexes pour vous de 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.

2. Cliquez 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 20151207
    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:

doc transpose des valeurs uniques 7

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

doc transpose des valeurs uniques 8

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

doc transpose des valeurs uniques 9


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

Kutools for Excel : avec plus de 300 compléments Excel pratiques, libre d'essayer sans limitation dans 60 jours.

Après l'installation de Kutools pour 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 > Contenu > Lignes de combinaison avancées, voir capture d'écran:

doc transpose des valeurs uniques 10

3. Dans le CLignes d'ombine basées sur la colonne boîte de dialogue, effectuez les opérations suivantes:

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

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

doc transpose des valeurs uniques 11

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:

doc transpose des valeurs uniques 12

5. Puis sélectionnez les cellules combinées et cliquez Kutools > Texte > Cellules divisés, voir capture d'écran:

doc transpose des valeurs uniques 13

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

doc transpose des valeurs uniques 14 14

7. Puis clique Ok Cliquez sur le bouton et sélectionnez une cellule pour placer le résultat de la séparation dans la boîte de dialogue dépliée, voir capture d'écran:

doc transpose des valeurs uniques 15

8. Cliquez OKet vous obtiendrez le résultat dont vous avez besoin. Voir la capture d'écran:

doc transpose des valeurs uniques 16

Télécharger et tester gratuitement Kutools for Excel maintenant!


Kutools for Excel: avec plus que 200 compléments Excel pratiques, libre d'essayer sans limitation dans les jours 60. Téléchargez et essai gratuit maintenant!



Outils de productivité recommandés

Office Tab

étoile d&#39;or1 Apportez des onglets pratiques à Excel et à d'autres logiciels Office, tout comme Chrome, Firefox et Internet Explorer.

Kutools for Excel

étoile d&#39;or1 Incroyable! Augmentez votre productivité dans les minutes 5. Ne nécessite pas de compétences particulières, économisez deux heures par jour!

étoile d&#39;or1 300 Nouvelles fonctionnalités pour Excel, rendent Excel facile et puissant:

  • Fusionner des cellules / lignes / colonnes sans perdre de données.
  • Combiner et consolider plusieurs feuilles et classeurs.
  • Comparez les gammes, copiez plusieurs gammes, convertissez le texte en date, l'unité et la conversion de devise.
  • Compter par couleurs, sous-totaux de recherche, tri avancé et super filtre,
  • Plus Sélectionner / Insérer / Supprimer / Texte / Format / Lier / Commenter / Classeurs / Feuilles de calcul Outils ...

Capture d&#39;écran de Kutools pour Excel

Say something here...
symbols left.
You are guest ( Sign Up? )
or post as a guest, but your post won't be published automatically.
Loading comment... The comment will be refreshed after 00:00.
  • To post as a guest, your comment is unpublished.
    Sean Vizcarrondo · 2 months ago
    So I am working for a company. We have columns for info such as Last name, first name, rank, section, phone number, address. Is there a way I can use a similar formula to transpose the entire row of info to a column by names?
  • To post as a guest, your comment is unpublished.
    kumar · 2 months ago
    Hi can we add each row and give the output in one column, with the above functionality.
  • To post as a guest, your comment is unpublished.
    Raj · 2 months ago
    Need to get the same out put but for predefined columns to be selected would be ($A,$B) and need the output column Position on $D$1.
    If any one have idea's that would be a great help!!!!
  • To post as a guest, your comment is unpublished.
    Kate · 5 months ago
    =INDEX($A$2:$A$16, MATCH(0, COUNTIF($D$1:$D1, $A$2:$A$16), 0)) worked for me to transpose the unique values of a column into a new column BUT...is there a way to ad in a sort function so that the new column created is transposed in ascending order?


    Thanks!
  • To post as a guest, your comment is unpublished.
    Prial · 7 months ago
    Same as Dave, I need to do the exactly opposite of this. Table 2 to transpose to Table 1. Input Table 2, Output Table 1.