Parfois, vous devrez peut-être retourner les données dans Excel, c'est-à-dire inverser l'ordre des données à l'envers dans un jeu de données vertical et de gauche à droite dans un jeu de données horizontal.
Maintenant, si vous pensez qu'il doit y avoir une fonctionnalité intégrée pour le faire dans Excel, 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 en utilisant une simple astuce de tri, des formules ou VBA.
Dans ce didacticiel, je vais vous montrer comment retourner les données dans les lignes, les colonnes et les tableaux dans Excel.
Alors, commençons!
Retourner les données à l'aide de la colonne SORT et Helper
L'un des moyens les plus simples d'inverser l'ordre des données dans Excel serait d'utiliser une colonne d'assistance, puis d'utiliser cette colonne d'assistance pour trier les données.
Retournez les données verticalement (ordre inverse à l'envers)
Supposons que vous ayez un ensemble de données de noms dans une colonne comme indiqué ci-dessous et que vous souhaitiez inverser ces données :
Voici les étapes pour retourner les données verticalement :
- Dans la colonne adjacente, saisissez « Helper » comme titre de la colonne
- Dans la colonne d'aide, saisissez une série de nombres (1,2,3, etc.). Vous pouvez utiliser les méthodes indiquées ici pour le faire rapidement
- Sélectionnez l'ensemble de données complet, y compris la colonne d'aide
- Cliquez sur l'onglet Données
- Cliquez sur l'icône Trier
- Dans la boîte de dialogue Trier, sélectionnez « Aide » dans la liste déroulante « Trier par »
- Dans la liste déroulante Ordre, sélectionnez « Du plus grand au plus petit »
- Cliquez sur OK
Les étapes ci-dessus trieraient les données en fonction des valeurs de la colonne d'assistance, ce qui conduirait également à inverser l'ordre des noms dans les données.
Une fois cela fait, n'hésitez pas à supprimer la colonne d'aide.
Dans cet exemple, je vous ai montré comment retourner les données lorsque vous n'avez qu'une colonne, mais vous pouvez également utiliser la même technique si vous avez une table entière. Assurez-vous simplement de sélectionner la table entière, puis utilisez la colonne d'aide pour trier les données par ordre décroissant.
Retourner les données horizontalement
Vous pouvez également suivre la même méthodologie pour retourner les données horizontalement dans Excel.
Excel a une option pour trier les données horizontalement à l'aide de la boîte de dialogue Trier (la fonctionnalité « Trier de gauche à droite »).
Supposons que vous ayez un tableau comme indiqué ci-dessous et que vous souhaitiez retourner ces données horizontalement.
Vous trouverez ci-dessous les étapes à suivre :
- Dans la ligne ci-dessous, saisissez « Helper » comme titre de la ligne
- Dans la ligne d'aide, entrez une série de nombres (1,2,3, etc.).
- Sélectionnez l'ensemble de données complet, y compris la ligne d'aide
- Cliquez sur l'onglet Données
- Cliquez sur l'icône Trier
- Dans la boîte de dialogue Trier, cliquez sur le bouton Options.
- Dans la boîte de dialogue qui s'ouvre, cliquez sur « Trier de gauche à droite »
- Cliquez sur OK
- Dans la liste déroulante Trier par, sélectionnez la ligne 3 (ou toute autre ligne contenant votre colonne d'aide)
- Dans la liste déroulante Ordre, sélectionnez « Du plus grand au plus petit »
- Cliquez sur OK
Les étapes ci-dessus renverseraient la table entière horizontalement.
Une fois cela fait, vous pouvez supprimer la ligne d'aide.
Retourner les données à l'aide de formules
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)
Utilisation de la fonction SORTBY (disponible dans Microsoft 365)
Supposons que vous ayez un tableau comme indiqué ci-dessous 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à où vous voudriez que le tableau inversé
Maintenant, utilisez la formule suivante sous la cellule dans l'en-tête le plus à gauche :
=TRIER PAR($A$2:$B$12,LIGNE(A2:A12),-1)
La formule ci-dessus trie les données et utilise le résultat de la fonction ROW comme base pour le trier.
La fonction ROW dans ce cas renverrait un tableau de nombres qui représente les numéros de ligne entre la plage spécifiée (qui dans cet exemple serait une série de nombres tels que 2, 3, 4, et ainsi de suite).
Et puisque le troisième argument de cette formule est -1, cela forcerait la formule à trier les données par ordre décroissant.
L'enregistrement qui a le numéro de ligne le plus élevé viendrait en haut et celui qui a le numéro de règle le plus bas irait en bas, renversant essentiellement l'ordre des données.
Une fois cela fait, vous pouvez convertir la formule en valeurs pour obtenir un tableau statique.
Utilisation de la fonction INDEX
Si vous n'avez pas accès à la fonction SORTBY, ne vous inquiétez pas - vous pouvez utiliser l'incroyable fonction INDEX.
Supposons que vous ayez un ensemble de données de noms comme indiqué ci-dessous et que vous souhaitiez inverser ces données.
Ci-dessous la formule pour le faire :
=INDICE($A$2:$A$12, LIGNES(A2:$A$12))
Comment fonctionne cette formule ?
La formule ci-dessus utilise la fonction INDEX qui renverrait la valeur de la cellule en fonction du nombre spécifié dans le deuxième argument.
La vraie magie se produit dans le deuxième argument où j'ai utilisé la fonction ROWS.
Puisque j'ai verrouillé la deuxième partie de la référence dans la fonction ROWS, dans la première cellule, cela renverrait le nombre de lignes entre A2 et A12, ce qui serait 11.
Mais quand il descend les lignes, la première référence deviendrait A3, puis A4, et ainsi de suite, tandis que la deuxième référence resterait telle quelle parce que je l'ai verrouillée et rendue absolue.
Au fur et à mesure que nous descendons les lignes, le résultat de la fonction ROWS diminuerait de 1, de 11 à 10 à 9, et ainsi de suite.
Et puisque la fonction INDEX nous renvoie la valeur basée sur le nombre dans le deuxième argument, cela nous donnerait finalement les données dans l'ordre inverse.
Vous pouvez utiliser la même formule même si vous avez plusieurs colonnes dans l'ensemble de données. cependant, vous devrez spécifier un deuxième argument qui spécifierait le numéro de colonne à partir duquel les données doivent être extraites.
Supposons que vous ayez un ensemble de données comme indiqué ci-dessous et que vous souhaitiez inverser l'ordre de l'ensemble du tableau :
Vous trouverez ci-dessous la formule qui le fera pour vous :
=INDICE($A$2:$B$12,LIGNES(A2:$A$12),COLONNES($A$2:A2))
Il s'agit d'une formule similaire dans laquelle j'ai également ajouté un troisième argument qui spécifie le numéro de colonne à partir duquel la valeur doit être extraite.
Pour rendre cette formule dynamique, j'ai utilisé la fonction COLUMNS qui continuerait à changer la valeur de la colonne de 1 à 2 à 3 lorsque vous la copiez vers la droite.
Une fois cela fait, vous pouvez convertir les formules en valeurs pour vous assurer d'avoir un résultat statique.
Remarque : lorsque vous utilisez une formule pour inverser l'ordre d'un ensemble de données dans Excel, elle ne conservera pas la mise en forme d'origine. Si vous avez également besoin de la mise en forme d'origine sur les données triées, vous pouvez soit l'appliquer manuellement, soit copier et coller la mise en forme de l'ensemble de données d'origine vers le nouvel ensemble de données triées.Retourner les données à l'aide de VBA
Si retourner les données dans Excel est quelque chose que vous devez faire assez souvent, vous pouvez également essayer la méthode VBA.
Avec un code de macro VBA, vous pouvez le copier et le coller une fois dans le classeur dans l'éditeur VBA, puis le réutiliser encore et encore dans le même classeur.
Vous pouvez également enregistrer le code dans le classeur de macros personnelles 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 qui retournerait les données sélectionnées verticalement dans la feuille de calcul.
Sub FlipVerically() 'Code par Sumit Bansal de TrumpExcel.com Dim TopRow As Variant Dim LastRow As Variant Dim StartNum As Integer Dim EndNum As Integer Application.ScreenUpdating = False StartNum = 1 EndNum = Selection.Rows.Count Faire tant que StartNum < EndNum TopRow = Selection.Rows(StartNum) LastRow = Selection.Rows(EndNum) Selection.Rows(EndNum) = TopRow Selection.Rows(StartNum) = LastRow StartNum = StartNum + 1 EndNum = EndNum - 1 Loop Application.ScreenUpdating = True End Sub
Pour utiliser ce code, vous devez d'abord effectuer la sélection de l'ensemble de données que vous souhaitez inverser (à l'exclusion des 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.
Il utilise ensuite la boucle Do While où le tri des données se produit.
La façon dont il trie ces données consiste à prendre la première et la dernière ligne et à les échanger. Il passe ensuite à la 2e rangée de l'avant-dernière rangée, puis les échange. Ensuite, il passe à la troisième ligne de la troisième dernière ligne 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 lors de l'exécution du code, puis la redéfinit sur TRUE lorsque l'exécution du code est terminée.
Cela garantit que vous ne voyez pas les changements se produire en temps réel sur votre écran, cela accélère également le processus.
Comment utiliser le Code ?
Suivez les étapes ci-dessous pour copier et coller ce code dans l'éditeur VB :
- Ouvrez le fichier Excel où vous souhaitez ajouter le code VBA
- Maintenez la touche ALT enfoncée et appuyez sur la touche F-11 (vous pouvez également aller dans l'onglet Développeur et cliquer sur l'icône Visual Basic)
- Dans l'éditeur Visual Basic qui s'ouvre, il y aurait un explorateur de projet sur la partie gauche de l'éditeur VBA. Si vous ne le voyez pas, cliquez sur l'onglet « Affichage » puis cliquez sur « Explorateur de projet »
- Cliquez avec le bouton droit sur l'un des objets du classeur dans lequel vous souhaitez ajouter le code
- Accédez à l'option Insérer, puis cliquez sur Module. Cela ajoutera un nouveau module au classeur
- Double-cliquez sur l'icône du module dans l'explorateur de projet. Cela ouvrira la fenêtre de code pour ce module
- Copiez et collez le code VBA ci-dessus dans la fenêtre de code
Pour exécuter le code de macro VBA, sélectionnez d'abord l'ensemble de données que vous souhaitez retourner (à l'exception des en-têtes).
Une fois les données sélectionnées, accédez à l'éditeur VB et cliquez sur le bouton de lecture vert dans la barre d'outils, ou sélectionnez n'importe quelle ligne du code, puis appuyez sur la touche F5
Voici donc quelques-unes des méthodes que vous pouvez utiliser pour retourner les données dans Excel (c'est-à-dire inverser l'ordre de l'ensemble de données).
Toutes les méthodes que j'ai couvertes dans ce didacticiel (formules, fonction SORT et VBA) peuvent être utilisées pour retourner les données verticalement et horizontalement (vous devrez ajuster la formule et le code VBA en conséquence pour le retournement horizontal des données).
J'espère que vous avez trouvé ce tutoriel utile.
