Comment empiler rapidement plusieurs colonnes en une seule colonne dans Excel ?

Dans Excel, la Conversion de texte en colonnes fonction est conçue pour diviser les données d'une seule colonne en plusieurs colonnes en utilisant un délimiteur spécifié. Cependant, que faire si vous avez besoin de faire l'inverse — combiner ou empiler les valeurs de plusieurs colonnes en une seule colonne, comme illustré dans l'exemple ci-dessous ? Ce scénario se présente souvent lors de la consolidation de jeux de données, de la préparation d'informations pour analyse ou du formatage de rapports pour un traitement ultérieur. Malheureusement, il n'y a pas de fonction intégrée dans Excel qui empile directement les colonnes verticalement, mais il existe plusieurs solutions pratiques que vous pouvez utiliser pour accomplir cette tâche efficacement.

Empiler plusieurs colonnes en une avec une formule

Empiler plusieurs colonnes en une avec VBA

Empiler plusieurs colonnes en une avec Transformer la plage

Empiler plusieurs colonnes en une avec Power Query

Empiler plusieurs colonnes en une avec une formule

Si vous préférez ne pas utiliser de macros ou d'add-ins, vous pouvez empiler plusieurs colonnes en une seule colonne avec une formule matricielle utilisant la fonction INDEX. Cette approche convient aux jeux de données dynamiques ou aux situations où vous souhaitez éviter des opérations manuelles. Un avantage est que la formule mettra à jour automatiquement les résultats si vos données sources changent, mais vous devez être précis avec vos noms de plages et éviter d'insérer ou supprimer des cellules dans la plage référencée.

1. Sélectionnez la plage de données que vous souhaitez empiler (par exemple, A1:C4), puis cliquez dans la Zone de nom (située à gauche de la barre de formule), tapez un nom significatif tel que MesDonnées, et appuyez sur Entrée. Cela nomme votre plage pour une référence facile.

2. Cliquez sur une cellule vide là où vous voulez que la colonne empilée unique commence — généralement sous vos données ou dans une autre feuille. Collez cette formule dans la cellule sélectionnée :

=INDEX(MyData,1+INT((ROW(A1)-1)/COLUMNS(MyData)),MOD(ROW(A1)-1+COLUMNS(MyData),COLUMNS(MyData))+1)

3. Appuyez sur Entrée pour confirmer, puis faites glisser la poignée de remplissage vers le bas jusqu'à ce que vous voyiez une erreur #REF! ou similaire, indiquant que toutes les données ont été listées. Supprimez la cellule d'erreur si nécessaire.

Dans cette formule, MesDonnées fait référence à la plage que vous avez définie à l'étape 1. COLONNES(MesDonnées) s'ajuste automatiquement selon le nombre de colonnes dans vos données. Assurez-vous de ne pas insérer ou supprimer des colonnes à l'intérieur de MesDonnées après avoir entré la formule, car cela peut affecter les résultats. Si vos données contiennent des cellules vides, ces vides seront également empilés — vous pouvez les filtrer ensuite si nécessaire. Pour les grandes plages, faire glisser la poignée de remplissage peut prendre du temps ; envisagez de double-cliquer sur la poignée de remplissage si la colonne à gauche contient des données tout au long.

Si votre version d'Excel prend en charge les tableaux dynamiques (Excel365 ou Excel2021 et versions ultérieures), vous pouvez essayer :

=TOCOL(MyData,1)

Cette formule n'est disponible que dans les versions plus récentes d'Excel et empile instantanément les colonnes en une seule colonne.

Empiler plusieurs colonnes en une avec VBA

Si vous avez fréquemment besoin d'empiler des colonnes ou désirez plus de contrôle, une macro VBA peut automatiser ce processus en quelques clics. Le VBA est idéal pour les utilisateurs avancés traitant de grands jeux de données ou des tâches répétitives. Gardez à l'esprit, cependant, qu'activer des macros peut poser un risque de sécurité si vous n'êtes pas sûr de la source de la macro. Enregistrez toujours votre fichier avant et soyez prudent lorsque vous exécutez du code inconnu.

1. Appuyez sur Alt + F11 pour ouvrir la fenêtre Microsoft Visual Basic for Applications.

2. Dans la fenêtre VBA, cliquez sur Insertion > Module. Copiez et collez le code suivant dans le nouveau module :

VBA : Empiler des colonnes en une seule

Sub ConvertRangeToColumn()
'UpdatebyExtendoffice
Dim Range1 As Range, Range2 As Range, Rng As Range
Dim rowIndex As Integer
xTitleId = "KutoolsforExcel"
Set Range1 = Application.Selection
Set Range1 = Application.InputBox("Source Ranges:", xTitleId, Range1.Address, Type:=8)
Set Range2 = Application.InputBox("Convert to (single cell):", xTitleId, Type:=8)
rowIndex = 0
Application.ScreenUpdating = False
For Each Rng In Range1.Rows
    Rng.Copy
    Range2.Offset(rowIndex, 0).PasteSpecial Paste:=xlPasteAll, Transpose:=True
    rowIndex = rowIndex + Rng.Columns.Count
Next
Application.CutCopyMode = False
Application.ScreenUpdating = True
End Sub

3. Pour exécuter la macro, appuyez sur F5, ou cliquez sur le bouton Exécuter. Après l'exécution, une invite apparaîtra - sélectionnez la plage de données que vous souhaitez empiler et cliquez sur OK.

4. Vous serez ensuite invité à sélectionner une cellule de destination pour le résultat. Cliquez OK à nouveau. Les colonnes seront empilées dans une seule colonne à partir de la cellule de destination.

Si vous rencontrez une erreur, vérifiez à nouveau la plage sélectionnée et assurez-vous qu'il n'y a pas de feuilles protégées ou de cellules fusionnées. Pour une personnalisation plus avancée, comme ignorer les blancs ou empiler uniquement certaines colonnes, le code peut être modifié davantage. N'oubliez pas de sauvegarder votre travail avant d'exécuter le code VBA pour éviter toute perte de données involontaire.

Empiler plusieurs colonnes en une avec Transformer la plage

Si vous préférez une solution rapide et conviviale sans avoir à écrire de formules ou de code, vous pouvez utiliser la fonction Transformer la plage de Kutools pour Excel. Cet utilitaire est particulièrement utile pour les utilisateurs qui souhaitent traiter rapidement les données via une interface intuitive, tels que les administrateurs de bureau ou ceux qui traitent régulièrement des données multidimensionnelles. L'utilisation de Kutools minimise le risque d'erreurs de formule et fait gagner beaucoup de temps, bien qu'elle nécessite l'installation de l'add-in.

Après avoir installé gratuitement Kutools pour Excel, procédez comme suit :

1. Sélectionnez les colonnes ou la plage de données que vous souhaitez empiler en une seule colonne. Puis cliquez sur Kutools > Plage > Transformer la plage pour ouvrir la boîte de dialogue.

2. Dans la boîte de dialogue Transformer la plage choisissez l'option Plage en une seule colonne et cliquez sur OK. Ensuite, sélectionnez une cellule où vous voulez que le résultat apparaisse.

3. Enfin, cliquez sur OK. Les colonnes sélectionnées seront immédiatement empilées en une seule colonne à l'emplacement choisi.

Cette méthode est simple et exempte d'erreurs. Rappelez-vous que vous pouvez facilement annuler la modification (Ctrl + Z) si quelque chose ne semble pas correct, ou réexécuter Transformer la plage au besoin pour différents jeux de données. Si vos données contiennent des cellules fusionnées, il est préférable de les dissocier avant d'utiliser cette fonctionnalité pour éviter des résultats inattendus.

Empiler plusieurs colonnes en une avec Power Query

Power Query (également appelé Obtenir et Transformer dans certaines versions d'Excel) est une autre manière pratique d'empiler plusieurs colonnes en une seule, surtout pour les utilisateurs traitant de grands volumes de données ou ayant besoin d'automatiser des tâches de transformation de données récurrentes. Power Query est intégré à Excel 2016 et versions ultérieures, et est disponible comme add-in gratuit pour Excel 2010 et 2013. Il est très utile pour la préparation avancée des données et peut facilement gérer des opérations complexes de remodelage avec une interface utilisateur simple. Cependant, il y a une courte courbe d'apprentissage pour les premiers utilisateurs.

Chargez votre table source dans Power Query. Sélectionnez vos données dans Excel. Allez dans Données > À partir de la table/plage pour ouvrir l'éditeur Power Query.

2. Sélectionnez toutes les colonnes dans l'aperçu de la requête. Allez dans l'onglet Transformer, trouvez le groupe Tableau, et cliquez sur Transposer. Cela inverse les lignes et les colonnes.

3. Là encore, sélectionnez toutes les colonnes transposées. Sous l'onglet Transformer, dans le groupe Toute colonne, cliquez sur Dépivoter les colonnes.

4. Supprimez la colonne "Attribut" si elle n'est pas nécessaire. Clic-droit sur l'en-tête de la colonne "Attribut" > Sélectionnez Supprimer.

5. Cliquez sur Fermer & Charger pour envoyer le résultat dans Excel sous forme de nouvelle feuille de calcul ou tableau. À tout moment, vous pouvez actualiser ou réappliquer la transformation si vos données sources changent.

Power Query vous permet de sauvegarder ce processus d'empilage en tant que requête réutilisable et de mettre rapidement à jour les résultats si vos données changent à l'avenir.

Remarque : Cette approche pourrait ne pas conserver les informations d'en-tête de colonne originales des données sources.

En résumé, chacune de ces méthodes a ses points forts : les formules sont adaptées aux données mises à jour dynamiquement, le VBA est excellent pour automatiser des tâches répétitives, Kutools pour Excel permet des opérations point-and-click avec peu de connaissances techniques requises, et Power Query est idéal pour des transformations avancées ou récurrentes. Revoyez toujours vos données empilées pour des blancs restants, des cellules fusionnées ou des erreurs de formatage après traitement. Si des erreurs ou résultats inattendus apparaissent, revérifiez vos plages définies ou sélections d'étapes. L'utilisation d'une combinaison de ces méthodes vous aidera à gérer et transformer efficacement vos données Excel pour vos besoins de reporting et d'analyse.