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 Sub3. 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
Meilleurs outils de productivité pour Office
Améliorez vos compétences Excel avec Kutools pour Excel, et découvrez une efficacité incomparable. Kutools pour Excel propose plus de300 fonctionnalités avancées pour booster votre productivité et gagner du temps. Cliquez ici pour obtenir la fonctionnalité dont vous avez le plus besoin...
Office Tab apporte l’interface par onglets à Office, simplifiant considérablement votre travail.
- Activez la modification 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é de50 % et réduit des centaines de clics de souris chaque jour !
Tous les modules complémentaires Kutools. Une seule installation
La suite Kutools for Office regroupe les modules complémentaires pour Excel, Word, Outlook & PowerPoint ainsi qu’Office Tab Pro, idéal pour les équipes travaillant sur plusieurs applications Office.
- Suite tout-en-un — modules complémentaires Excel, Word, Outlook & PowerPoint + Office Tab Pro
- Un installateur, une licence — installation en quelques minutes (compatible MSI)
- Une synergie optimale — productivité accélérée sur l’ensemble des applications Office
- Essai complet30 jours — sans inscription, ni carte bancaire
- Meilleure valeur — économisez par rapport à l’achat d’add-ins individuels