Comment transposer les données en colonnes en lignes?

Comment transposer les données en colonnes en lignes?

Si les données sont:

Joe some city some state Bob no city no state 

Et j'utilise Collage spécial » Transposer , je reçois

 Joe some city some state Bob no city no state 

Ce que je veux, c'est:

 Joe some city some state Bob no city no state 

Je ne connais pas VBA.

Nous allons vous apprendre quelques VBA ce matin et vous allez vous en vouloir! Mettez vos bottes de fusée et préparez-vous à naviguer!

Cette macro le fera pour vous si tout est dans la colonne A

Appuyez sur Alt + F11 pour afficher l'éditeur VBA. Cliquez avec le bouton droit de la souris insert - module . Collez le code ci-dessous dans le module et lancez le debug - compile project et appuyez sur F5 pour l'exécuter. Facile a vent.

 Sub transposedelete() Dim rownum As Long Dim colnum As Long Dim data, result colnum = 1 Application.ScreenUpdating = False 'check if the data on the sheet start where the code expects it If Range("a1") = "" Then Exit Sub ' define data range With Range("a1", Cells(Rows.Count, "a").End(xlUp)).Resize(, 2) data = .Value numrows = UBound(data) 'loop it For rownum = 2 To numrows Range((Cells(rownum, colnum)), (Cells(rownum + 1, colnum))).Copy ' transpose Cells(rownum - 1, colnum + 1).Select Selection.PasteSpecial Paste:=xlPasteAll, Operation:=xlNone, SkipBlanks:= _ False, Transpose:=True 'delete rows (this could be cleaner) Rows(rownum).Select Application.CutCopyMode = False Selection.Delete Shift:=xlUp Rows(rownum).Select Application.CutCopyMode = False Selection.Delete Shift:=xlUp Next End With Application.ScreenUpdating = True End Sub 

Facile comme 1, 2, 3:

1.
Entrez la description de l'image ici
2.
Entrez la description de l'image ici
3.
Entrez la description de l'image ici

Voyez, cela n'était pas difficile ou intimidant, n'est-ce pas? Et maintenant, vous avez un truc dans la manche! L'apprentissage est très amusant!

Mise à jour: Excel 2007 et versions ultérieures possèdent une fonctionnalité d'une colonne à plusieurs lignes. Lisez la suite ici . Pour la raison pour laquelle la fonction de transposition ne fait pas ce que vous voulez, voir ci-dessous:

Pour que la transposition fonctionne comme vous le souhaitez, vos données d'origine devraient être organisées de la manière suivante:

 Joe Bob city1 city2 state1 state2 

La transposition change de lignes en colonnes et vice versa. La façon dont vos informations sont formatées dans votre exemple, c'est toute une longue colonne et Excel la modifie dans une longue rangée. Il n'a aucun moyen de savoir que vous voulez qu'il traite votre colonne avec six éléments comme deux blocs de trois éléments chacun.

Cette réponse utilise les données de Comment convertir les données de format de ligne en format de colonne? , Qui a été fermé comme un double de cette question. Ainsi, cette réponse répond à cette question, avec des modifications triviales (c.-à-d. Ignorez les parties de cette réponse qui parlent des étiquettes de données.


Tout d'abord, je suppose que vos données sont ce que vous dites:

Créez une nouvelle feuille et entrez les en-têtes de colonnes correspondant à vos trois étiquettes de données:

(Vous pouvez le faire en copiant les cellules A1:A3 de Sheet1 et en les transposant, mais, pour seulement trois, il est probablement plus facile de les taper manuellement.) Maintenant, notez que, chaque fois que vous déplacez une cellule (c'est-à-dire une colonne ) À droite sur Sheet2 , ce qui correspond à déplacer vers le bas une ligne sur Sheet1 . Et chaque fois que vous déplacez une rangée vers le bas sur Sheet2 , cela correspond à déplacer vers le bas trois lignes sur Sheet1 . Donc, supposons que =ROW()*3 + COLUMN() soit une formule utile, et entrez cela dans les cellules A2:C4 :

Notez que les valeurs progressent comme nous le voulons. Mais nous voulons que Sheet2!A2 sa valeur de Sheet1!A1 , alors soustrayons 7 et utilisons le résultat comme un décalage. Entrer

 =OFFSET(Sheet1!$A$1, ROW()*3+COLUMN()-7, 0) 

Dans les cellules A2:C4 :

Quelles sont les données que vous voulez, mais avec les étiquettes de données toujours attachées. Donc, pour corriger cela, modifiez la formule pour

 =SUBSTITUTE(OFFSET(Sheet1!$A$1, ROW()*3+COLUMN()-7, 0), A$1 & ": ", "") 

Pour supprimer l'étiquette (à partir de la ligne 1 de la colonne actuelle) des données de la cellule:

Et ensuite vous pouvez copier et coller des valeurs.