Renverser les données dans Excel (inverser l’ordre des données dans les colonnes/lignes)

Parfois, vous devrez peut-être retourner les données dans Excel, c'est-à-dire inverser l'ordre des données dans un ensemble de données vertical et inverser l'ordre des données de gauche à droite dans un ensemble de données horizontal.

Maintenant, si vous pensez qu'il doit y avoir une fonction intégrée dans Excel pour ce faire, je crains que vous ne soyez déçu.

Bien qu'il existe plusieurs façons de retourner les données dans Excel, il n'y a pas de fonctionnalité intégrée.Mais vous pouvez facilement le faire avec des astuces de tri simples, des formules ou VBA.

Dans ce didacticiel, je vais vous montrer comment inverser les données des lignes, des colonnes et des tableaux dans Excel.

Alors, commençons!

Retourner les données à l'aide de SORT et de colonnes auxiliaires

L'un des moyens les plus simples d'inverser l'ordre des données dans Excel consiste à utiliser une colonne d'assistance, puis à utiliser cette colonne d'assistance pour trier les données.

Retourner les données verticalement (ordre inverse)

Supposons que vous ayez une colonne comme ci-dessousNom de l'ensemble de données, et vous souhaitez retourner ces données :

Voici les étapes pour retourner les données verticalement :

1. Dans la colonne adjacente, entrez "Helper" comme en-tête de colonne
2. Dans la colonne d'aide, entrez une série de chiffres (1, 2, 3, etc.).Vous pouvez le faire rapidement en utilisant la méthode montrée ici
3. Sélectionnez l'ensemble de données complet, y compris les colonnes auxiliaires
4. Cliquez sur l'onglet Données
5. Cliquez sur l'icône de tri
6. Dans la boîte de dialogue Trier, sélectionnez Assistant dans le menu déroulant Trier par
7. Dans le menu déroulant de la commande, sélectionnez "Grand à petit"
8. Cliquez sur OK

Les étapes ci-dessus trieront les données en fonction des valeurs des colonnes d'assistance, ce qui entraînera également l'ordre inverse des noms dans les données.

N'hésitez pas à supprimer la colonne d'aide lorsque vous avez terminé.

Dans cet exemple, je vous montre comment inverser les données lorsqu'il n'y a qu'une seule colonne, mais vous pouvez utiliser la même technique si vous avez le tableau entier.Assurez-vous simplement de sélectionner l'intégralité du tableau, puis utilisez la colonne d'assistance pour trier les données par ordre décroissant.

Retourner les données horizontalement

Vous pouvez également retourner les données horizontalement dans Excel de la même manière.

Excel a la possibilité de trier les données horizontalement à l'aide de la boîte de dialogue Trier (fonction Trier de gauche à droite).

Supposons que vous disposiez d'un tableau comme indiqué ci-dessous et que vous souhaitiez retourner ces données horizontalement.

Voici les étapes à suivre :

1. Dans la ligne ci-dessous, saisissez "Helper" comme titre de la ligne
2. Dans la ligne de l'assistant, entrez une série de nombres (1, 2, 3, etc.).
3. Sélectionnez l'ensemble de données complet, y compris les lignes auxiliaires
4. Cliquez sur l'onglet Données
5. Cliquez sur l'icône de tri
6. Dans la boîte de dialogue Trier, cliquez sur le bouton Options.
7. Dans la boîte de dialogue qui s'ouvre, cliquez sur Trier de gauche à droite
8. Cliquez sur OK
9. Dans la liste déroulante Trier par, sélectionnez la ligne 3 (ou tout ce qui a votre colonne d'assistance)
10. Dans le menu déroulant de la commande, sélectionnez "Grand à petit"
11. Cliquez sur OK

Les étapes ci-dessus retourneront toute la table horizontalement.

Une fois terminé, vous pouvez supprimer la ligne d'assistance.

Utilisez Excel pour renverser les formulesretourner les données

Microsoft 365 propose de nouvelles formules qui permettent d'inverser très facilement l'ordre d'une colonne ou d'un tableau dans Excel.

Dans cette section, je vais vous montrer comment faire cela en utilisant la formule SORTBY (si vous utilisez Microsoft 365) ou la formule INDEX (si vous n'utilisez pas Microsoft 365)

Utiliser la fonction SORTBY (disponible dans Microsoft 365)

Supposons que vous disposiez d'un tableau comme celui-ci et que vous souhaitiez inverser les données de ce tableau :

Pour ce faire, copiez d'abord les en-têtes et placez-les à l'endroit où vous souhaitez retourner le tableau.

Utilisez maintenant la formule suivante sous la cellule d'en-tête la plus à gauche :

=SORTBY($A$2:$B$12,ROW(A2:A12),-1)

La formule ci-dessus trie les données et les trie en fonction du résultat de la fonction ROW.

Dans ce cas, la fonction ROW renverra un tableau de nombres représentant les numéros de ligne entre la plage spécifiée (dans cet exemple, une série de nombres, tels que 2, 3, 4, etc.).

Étant donné que le troisième paramètre de cette formule est -1, il force la formule à trier les données par ordre décroissant.

L'enregistrement avec le numéro de ligne le plus élevé sera en haut et l'enregistrement avec le numéro de règle le plus bas sera en bas, inversant essentiellement l'ordre des données.

Une fois cela fait, vous pouvez convertir la formule en une valeur pour obtenir un tableau statique.

Utiliser la fonction index

Si vous ne pouvez pas utiliser la fonction SORTBY, ne vous inquiétez pas - vous pouvez utiliser l'étonnante fonction INDEX.

Supposons que vous disposiez d'un ensemble de données de noms comme indiqué ci-dessous et que vous souhaitiez inverser ces données.

Voici la formule pour le faire :

= INDEX ($ A $ 2: $ A $ 12, LIGNES (A2: $ A $ 12))

 Comment fonctionne cette formule ?

La formule ci-dessus utilise la fonction INDEX, qui renverra la valeur de la cellule en fonction du nombre spécifié dans le deuxième paramètre.

La vraie magie se produit lorsque j'utilise le deuxième paramètre de la fonction ROWS.

Depuis que j'ai verrouillé la deuxième partie de la référence dans la fonction ROWS, dans la première cellule, elle renverra le nombre de lignes entre A2 et A12, soit 11.

Mais au fur et à mesure qu'il descend dans la rangée, la première référence deviendra A3, puis A4, et ainsi de suite, tandis que la deuxième référence restera la même car je l'ai verrouillée et rendue absolue.

Au fur et à mesure que nous descendons, le résultat de la fonction ROWS diminuera de 1, passant de 11 à 10 à 9, et ainsi de suite.

Étant donné que la fonction INDEX renvoie une valeur basée sur le nombre dans le deuxième argument, cela finit par nous donner les données dans l'ordre inverse.

Vous pouvez utiliser la même formule même s'il y a plusieurs colonnes dans le jeu de données.Cependant, vous devez spécifier un deuxième paramètre, qui spécifiera le numéro de colonne à partir de laquelle les données doivent être extraites.

Supposons que vous ayez un ensemble de données qui ressemble à ceci et que vous souhaitiez inverser l'ordre de toute la table :

Voici la formule qui le fera pour vous :

=INDEX($A$2:$B$12,ROWS(A2:$A$12),COLUMNS($A$2:A2))

Voici une formule similaire où j'ai également ajouté un troisième paramètre qui spécifie le numéro de colonne à partir de laquelle la valeur doit être prise.

Pour rendre cette formule dynamique, j'ai utilisé la fonction COLUMNS, qui continue de changer la valeur de la colonne de 1 à 2 à 3 lorsque vous la copiez à droite.

Une fois cela fait, vous pouvez convertir la formule en une valeur pour garantir des résultats statiques.

Remarque : Lorsque vous utilisez une formule pour inverser l'ordre d'un jeu de données dans Excel, elle ne conserve pas la mise en forme d'origine.Si vous avez également besoin de la mise en forme d'origine des données triées, vous pouvez l'appliquer manuellement ou copier et coller la mise en forme de l'ensemble de données d'origine dans le nouvel ensemble de données triées.

Retourner les données à l'aide de VBA

Si vous devez souvent retourner des données dans Excel, vous pouvez également essayer la méthode VBA.

À l'aide du code de macro VBA, vous pouvez le copier et le coller une fois dans un classeur dans l'éditeur VBA, puis le réutiliser encore et encore dans le même classeur.

Vous pouvez également enregistrer le code dans un classeur de macros personnel ou en tant que complément Excel et pouvoir l'utiliser dans n'importe quel classeur de votre système.

Vous trouverez ci-dessous le code VBA permettant de retourner verticalement les données sélectionnées dans la feuille de calcul.

Sous FlipVerically()

Dim TopRow comme variante
Dim LastRow comme variante
Dim StartNum comme entier
Dim EndNum sous forme d'entier

Application.ScreenUpdating = Faux

NumDébut = 1
EndNum = Sélection. Lignes. Nombre

Faire tant que NumDébut < NumFin
TopRow = Sélection.Lignes(StartNum)
LastRow = Sélection. Rows(EndNum)
Selection.Rows(EndNum) = TopRow
Selection.Rows (StartNum) = LastRow
NumDébut = NumDébut + 1
NumFin = NumFin - 1
boucle

Application.ScreenUpdating = True
End Sub

Pour utiliser ce code, vous devez d'abord sélectionner le jeu de données que vous souhaitez inverser (hors en-têtes), puis exécuter ce code.

Comment fonctionne ce code ?

Le code ci-dessus compte d'abord le nombre total de lignes dans l'ensemble de données et l'affecte à la variable EndNum.

Ensuite, il utilise une boucle Do While pour trier les données.

La façon dont il trie ces données consiste à prendre la première et la dernière ligne et à les échanger.Ensuite, il passe à la deuxième rangée de l'avant-dernière rangée, puis les permute.Ensuite, il passe à la troisième rangée de la troisième à la dernière rangée, et ainsi de suite.

La boucle se termine lorsque tout le tri est terminé.

Il utilise également la propriété Application.ScreenUpdating et la définit sur FALSE lorsque le code est en cours d'exécution, puis la remet sur TRUE lorsque l'exécution du code est terminée.

Cela garantit que vous ne voyez pas les changements se produire à l'écran en temps réel et accélère également le processus.

Comment utiliser le code ?

Copiez et collez ce code dans l'éditeur VB comme suit :

1. Ouvrez le fichier Excel où vous souhaitez ajouter le code VBA
2. Maintenez ALT et appuyez sur F-11 (vous pouvez également accéder à l'onglet développeur et cliquer sur l'icône Visual Basic)
3. Dans l'éditeur Visual Basic qui s'ouvre, il y aura un explorateur de projet sur le côté gauche de l'éditeur VBA.Si vous ne le voyez pas, cliquez sur l'onglet Affichage, puis sur Explorateur de projets
4. Faites un clic droit sur n'importe quel objet du classeur où vous souhaitez ajouter du code
5. Allez dans l'option Insérer et cliquez sur Module.Cela ajoutera un nouveau module au classeur
6. Double-cliquez sur l'icône du module dans l'explorateur de projet.Cela ouvrira la fenêtre de code pour ce module
7. Copiez et collez le code VBA ci-dessus dans la fenêtre de code

Pour exécuter le code macro VBA, sélectionnez d'abord l'ensemble de données à retourner (à l'exclusion de l'en-tête).

Avec les données sélectionnées, allez dans l'éditeur VB et cliquez sur le bouton de lecture vert dans la barre d'outils, ou sélectionnez n'importe quelle ligne dans le code et appuyez sur F5

Voici donc quelques-unes des façons dont vous pouvez inverser les données dans Excel (c'est-à-dire inverser l'ordre d'un ensemble de données).

Toutes les méthodes que j'ai couvertes dans ce didacticiel (formules, fonctions SORT et VBA) peuvent être utilisées pour retourner les données verticalement et horizontalement (vous devez ajuster les formules et le code VBA en conséquence pour le retournement horizontal des données).

J'espère que vous avez trouvé ce tutoriel utile.