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.
| Kutools for Excel : avec plus de 300 compléments Excel pratiques, essai gratuit sans limitation en 30 jours. |
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
Meilleurs outils de productivité bureautique
Améliorez vos compétences Excel avec Kutools for Excel et faites l'expérience d'une efficacité comme jamais auparavant. Kutools for Excel offre plus de 300 fonctionnalités avancées pour augmenter la 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 facilite grandement votre travail
- Activer 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 réduit des centaines de clics de souris chaque jour!
