Comment transposer les cellules d’une colonne en fonction des valeurs uniques d’une autre colonne ?
Supposons que vous disposiez d’une plage de données composée de deux colonnes. Vous souhaitez transposer les cellules de l’une des colonnes en lignes horizontales, en regroupant les valeurs selon les éléments uniques présents dans l’autre colonne, afin d’obtenir le résultat illustré ci-dessous. Connaissez-vous une méthode efficace pour réaliser cette opération dans Excel ?
Transposer des cellules d’une colonne en fonction des valeurs uniques à l’aide de formules
Transposer des cellules d’une colonne en fonction des valeurs uniques à l’aide d’un code VBA
Transposer des cellules d’une colonne en fonction des valeurs uniques avec Kutools pour Excel
Transposer des cellules d’une colonne en fonction des valeurs uniques à l’aide de formules
Grâce aux formules matricielles suivantes, extrayez les valeurs uniques et transposez leurs données correspondantes en lignes horizontales. Voici la marche à suivre :
1. Saisissez cette formule matricielle : =INDEX($A$2:$A$16; EQUIV(0; NB.SI($D$1:$D1; $A$2:$A$16); 0)) dans une cellule vide, par exemple D2, puis appuyez simultanément sur 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 située juste au-dessus de celle contenant la formule.
2. Faites ensuite glisser la poignée de recopie vers le bas jusqu’aux cellules nécessaires pour extraire toutes les valeurs uniques (voir la capture d’écran) :
3. Ensuite, saisissez 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 simultanément 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 dont vous souhaitez transposer les données, A2:A16 est la colonne servant de base à la transposition, et D2 contient la valeur unique extraite à l’étape 1.
4.Faites ensuite glisser la poignée de recopie vers la droite jusqu’aux cellules où vous souhaitez afficher les données transposées, jusqu’à ce que 0 s’affiche (voir capture d’écran) :
5. Faites ensuite glisser la poignée de recopie vers le bas sur la plage de cellules pour obtenir les données transposées, comme illustré dans la capture d’écran suivante :
Transposer des cellules d’une colonne en fonction des valeurs uniques à l’aide d’un code VBA
Les formules peuvent sembler complexes à première vue. Voici un code VBA simple à exécuter pour obtenir instantanément le résultat souhaité.
1. Maintenez enfoncées les touches ALT + F11 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 d’une colonne en fonction des valeurs uniques d’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 Sub3. Appuyez ensuite sur la touche F5 pour exécuter ce code. Une boîte de dialogue s’affichera alors afin que vous puissiez sélectionner la plage de données à utiliser (voir capture d’écran) :
4. Cliquez ensuite sur le bouton OK ; une autre boîte de dialogue s’affiche alors, vous invitant à sélectionner une cellule destinée à recevoir le résultat (voir capture d’écran) :
6. Cliquez sur le bouton OK et les données de la colonne B seront transposées en fonction des valeurs uniques de la colonne A (voir la capture d’écran) :
Transposer des cellules d’une colonne en fonction des valeurs uniques avec Kutools pour Excel
Si vous disposez de Kutools pour Excel, combinez les fonctions Fusion avancée des lignes et Diviser les cellules pour accomplir cette tâche rapidement, sans aucune formule ni code.
Après avoir installé 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 les copier et les coller ailleurs.)
2. Cliquez ensuite sur Kutools > Fusionner et scinder > Fusion avancée des lignes (voir capture d’écran) :
3. Dans la boîte de dialogue Combinez les lignes basées sur la colonne, effectuez les opérations suivantes :
(1.) Cliquez sur le nom de la colonne servant de base à la transposition des données, puis sélectionnez Clé primaire;
(2.) Cliquez sur une autre colonne dont vous souhaitez transposer les données, puis cliquez sur Combiner et choisissez un séparateur (espace, virgule, point-virgule, etc.) pour séparer les données combinées.
4. Cliquez ensuite sur le bouton Ok, et les données de la colonne B seront regroupées dans une seule cellule selon la colonne A (voir capture d’écran) :
5. Sélectionnez ensuite les cellules fusionnées, puis cliquez sur Kutools > Fusionner et scinder > Diviser les cellules (voir capture d’écran) :
6. Dans la boîte de dialogue Diviser les cellules, sélectionnez Diviser en colonnes sous l’option Type, puis choisissez le séparateur utilisé pour combiner vos données (voir capture d’écran) :
7. Cliquez ensuite sur le bouton Ok, puis sélectionnez une cellule dans la boîte de dialogue qui s’affiche pour y insérer le résultat de la séparation (voir capture d’écran) :
8. Cliquez sur OK et vous obtiendrez le résultat souhaité (voir la capture d’écran) :
Téléchargez et testez gratuitement Kutools pour Excel dès maintenant !
Démo : Transposer des cellules d’une colonne en fonction des valeurs uniques avec Kutools pour Excel
Meilleurs outils de productivité Office
Boostez vos compétences Excel avec Kutools pour Excel et découvrez une efficacité inégalée.Kutools pour Excel propose plus de 300 fonctionnalités avancées pour améliorer votre productivité et Gagner du temps.Cliquez ici pour obtenir la fonctionnalité dont vous avez le plus besoin...
Office Tab apporte une interface à onglets à Office et rend votre travail bien plus facile
- Activez l’édition et la lecture par onglets dans Word, Excel, PowerPoint, Publisher, Access, Visio et Project.
- Ouvrez et créez plusieurs documents dans de nouveaux onglets de la même fenêtre, plutôt que dans de nouvelles fenêtres.
- Augmente votre productivité de 50 % et vous fait économiser des centaines de clics de souris chaque jour !
Tous les compléments Kutools. Un seul installateur
Kutools for Office regroupe les compléments pour Excel, Word, Outlook et PowerPoint, ainsi que Office Tab Pro, ce qui en fait le choix idéal pour les équipes travaillant à travers les applications Office.
- Suite tout-en-un— Compléments Excel, Word, Outlook et PowerPoint + Office Tab Pro
- Un seul installateur, une seule licence— installation en quelques minutes (compatible MSI)
- Fonctionne mieux ensemble— productivité optimisée dans toutes les applications Office
- Essai gratuit de 30 jours avec toutes les fonctionnalités— aucune inscription, aucune carte bancaire
- Meilleur rapport qualité-prix— économisez par rapport à l’achat de compléments individuels