Inverser l’ordre d’une colonne dans Excel

Numéroter les lignes dans une colonne d’aide

La colonne d’aide sert à donner au tri une clé qu’il puisse renverser, puisqu’aucune des colonnes de l’export n’en fait l’affaire. Trier sur Commande rangerait les codes par ordre alphabétique et trier sur Date par ordre chronologique, ce qui tomberait juste sur notre exemple mais raterait le premier export où les codes ne suivent pas les lignes. Une suite de numéros, elle, dit uniquement la place de chaque ligne dans le tableau, ce qui est exactement ce qu’on veut renverser.

1. 01

   Écris No en E1, pour donner un en-tête à la colonne d’aide, sans quoi le tri prendrait la première ligne pour une donnée.

2. 02

   Tape 1 en E2 et 2 en E3, sélectionne les deux cellules, puis tire la poignée de recopie jusqu’en E7. Excel poursuit la série jusqu’à 6.

3. 03

   Dans Microsoft 365, Excel 2024 et Excel 2021, la formule ci-dessous remplit la colonne d’un coup depuis E2, et se recalcule si le tableau s’allonge.

Garde en tête qu’une formule qui déborde ne se laisse pas trier, parce que ses six résultats appartiennent à la seule cellule E2. Si tu passes par SEQUENCE, copie donc la colonne puis recolle-la en valeurs avant de trier. La recopie à la poignée, elle, écrit six nombres indépendants et n’a besoin de rien.

Trier la colonne d’aide en ordre décroissant

Le tri décroissant sur la colonne de numéros fait passer le 6 en première ligne et le 1 en dernière, et l’export entier suit ce mouvement. L’onglet Données porte les deux boutons du groupe Trier et filtrer, Ordre croissant et Ordre décroissant, et c’est le second qu’il te faut ici.

1. 01

   Sélectionne tout le tableau, de A1 à E7, colonne d’aide comprise. La ligne 8 du total reste en dehors de la sélection, pour qu’elle ne parte pas dans le tri.

2. 02

   Clique sur une cellule de la colonne No, par exemple E2, pour que le tri prenne cette colonne comme clé.

3. 03

   Dans l’onglet Données, clique sur le bouton Ordre décroissant du groupe Trier et filtrer. Le bouton Trier, juste à côté, ouvre une fenêtre qui fait la même chose en nommant la colonne.

Le tableau ressort alors dans l’ordre inverse, de CMD-1037 à CMD-1042, et la colonne Cumul se recalcule au passage. Elle affiche 980, 2 110, 2 850, 4 460, 5 350 puis 6 600, c’est-à-dire un vrai cumul de la plus ancienne commande à la plus récente. Le tri réécrit en effet chaque formule sur sa nouvelle ligne, comme si tu l’y avais recopiée, et le total de la ligne 8 ne bouge pas de 6 600.

Étendre la sélection pour que tout le tableau suive le tri

Le piège de cette méthode tient en une fenêtre, celle qui s’ouvre quand tu lances le tri depuis la seule colonne d’aide. Sous le titre Attention, Excel y écrit que « Microsoft Excel a trouvé des données près de votre sélection. Comme elles ne sont pas sélectionnées, … », et te laisse le choix entre Étendre la sélection et Continuer avec la sélection en cours. Le second bouton ne trie que la colonne de numéros, qui se retrouve à l’envers pendant que l’export reste dans son ordre de départ.

Une fois le mal fait, rien ne signale que les lignes ne correspondent plus, puisque chaque colonne reste bien remplie. C’est pour éviter cette fenêtre qu’il vaut mieux sélectionner tout le tableau avant de trier, comme à la première étape ci-dessus. Si elle s’ouvre malgré tout, choisis Étendre la sélection, et vérifie ensuite que la ligne du haut porte bien le montant de 980.

Supprimer la colonne d’aide sans défaire l’ordre inversé

La colonne de numéros a fait son travail une fois le tri passé, et rien ne la retient ensuite dans le tableau. Fais un clic droit sur la lettre E, choisis Supprimer, et l’export garde son nouvel ordre. Un tri déplace en effet les lignes pour de bon, contrairement à un filtre qui se contente de masquer.

Garde plutôt cette colonne si tu comptes revenir en arrière, car un second tri, croissant cette fois, remet l’export exactement comme il était. Une fois les numéros effacés, en revanche, plus rien sur la feuille ne dit dans quel ordre les lignes se lisaient, et l’ordre de départ est perdu pour de bon.

Écrire TRIERPAR avec une SEQUENCE en clé de tri

La fonction TRIERPAR trie une plage d’après une autre, et SEQUENCE lui fournit la clé qui manquait, à savoir la place de chaque ligne. Le troisième argument, le nombre -1, demande l’ordre décroissant, et la liste ressort donc retournée. Écrite en F2, la formule ci-dessous déverse les six commandes sur F2:I7, en emmenant les quatre colonnes.

Pour ne retourner qu’une colonne, remplace les deux plages par celle qui t’intéresse, et la formule =TRIERPAR(A2:A7;SEQUENCE(LIGNES(A2:A7));-1) rend les six codes sur une seule colonne. Regarde en revanche ce que devient le Cumul dans la copie, où il affiche 6 600, 5 620, 4 490, 3 750, 2 140 puis 1 250. TRIERPAR recopie les chiffres déjà calculés au lieu des formules, si bien que le cumul descend au lieu de monter et ne veut plus rien dire. Laisse donc cette colonne hors de la plage, ou refais-la à côté du résultat.

TRIERPAR existe dans Microsoft 365, Excel 2024 et Excel 2021, et la copie occupe autant de place que l’original. Prévois six lignes libres à partir de la cellule où tu l’écris, sans quoi la moindre cellule occupée sur leur chemin lui fait afficher #PROPAGATION! à la place de la liste.

Retourner un tableau entier avec INDEX et SEQUENCE

La fonction INDEX fait le même travail avec deux SEQUENCE, l’une qui descend les numéros de ligne et l’autre qui parcourt les colonnes. La première part du nombre de lignes et avance d’un pas de -1, ce qui donne 6, 5, 4, 3, 2, 1, et INDEX va chercher les lignes dans cet ordre. La seconde n’a pas de premier argument, puisqu’elle ne compte que des colonnes.

INDEX n’ouvre aucune version de plus que TRIERPAR, puisque la SEQUENCE qui lui sert de clé demande elle aussi Microsoft 365, Excel 2024 ou Excel 2021. Elle rend service ailleurs, dans un classeur qui empile déjà des INDEX et dont tu veux garder une seule façon d’écrire, au prix d’une formule nettement plus longue à relire six mois plus tard. Si ton classeur est plus ancien que ces versions, la colonne d’aide et le tri restent la seule route.

Prendre la dernière ligne en premier avec CHOISIRLIGNES

La fonction CHOISIRLIGNES rend les lignes que tu nommes, dans l’ordre où tu les nommes, et ses numéros négatifs comptent à partir du bas. Le -1 désigne la dernière ligne de la plage, le -2 l’avant-dernière, si bien qu’une suite de numéros négatifs suffit à retourner un tableau court.

Écrire six numéros à la main devient vite pénible, et une SEQUENCE qui descend de -1 à -6 les fournit toute seule. La forme courte ci-dessous rend exactement la même chose, et elle s’adapte à un tableau qui s’allonge. Attention en revanche au numéro qui dépasse la taille de la plage, car =CHOISIRLIGNES(A2:D7;-7) affiche #VALEUR! au lieu d’une ligne vide.

CHOISIRLIGNES est arrivée après les autres, et elle ne tourne que dans Microsoft 365 et Excel 2024. Un classeur ouvert dans Excel 2021 affiche donc l’erreur de nom sur cette formule, là où TRIERPAR et INDEX passent sans rien dire.

Figer la liste retournée en valeurs

Une liste retournée par formule suit sa source, ce qui est un avantage tant que l’export continue de vivre, et une gêne dès que tu veux la retoucher. Tant que la formule est là, tu ne peux ni corriger une cellule du résultat ni en supprimer une ligne, et le bloc entier se recalcule au moindre changement de l’original.

Pour arrêter ce lien, sélectionne le résultat, copie-le avec Ctrl+C, puis recolle-le par-dessus en choisissant Valeurs dans le menu du bouton Coller. Tu récupères alors six lignes ordinaires, modifiables une par une, et tu peux supprimer l’export d’origine sans casser quoi que ce soit.

Envoyer le tableau dans l’éditeur Power Query

Power Query ne lit pas une plage quelconque, il lui faut un tableau nommé, celui que Ctrl+L fabrique. Cette première étape lui en donne un et ouvre l’éditeur dessus, sans rien changer à la feuille.

1. 01

   Sélectionne A1:D7, puis appuie sur Ctrl+L. Dans la fenêtre qui s’ouvre, vérifie que la plage s’arrête à la ligne 7 et que la case des en-têtes est cochée, puis valide par OK.

2. 02

   Dans le groupe Propriétés de l’onglet Création de tableau, remplace le nom proposé, Tableau1 par exemple, par Commandes.

3. 03

   Dans l’onglet Données, clique sur À partir d’un tableau/d’une plage. L’éditeur Power Query s’ouvre sur les six commandes.

Inverser les lignes puis charger le résultat dans la feuille

Dans l’éditeur, la commande Inverser les lignes retourne la table d’un clic, et une étape nommée Lignes inversées s’ajoute au volet des étapes appliquées, à droite. C’est cette étape qui se rejouera à chaque actualisation, sur l’export que tu auras collé entre-temps.

1. 01

   Clique sur Inverser les lignes. L’aperçu montre aussitôt CMD-1037 en première ligne et CMD-1042 en dernière.

2. 02

   Ouvre la flèche du bouton Fermer et charger, puis choisis Fermer et charger dans. Le bouton seul se contente parfois de créer une connexion, sans rien poser dans le classeur.

3. 03

   Choisis Tableau et Nouvelle feuille de calcul, puis valide. Le tableau retourné arrive dans une feuille neuve.

Si tu ne trouves pas la commande dans le ruban de l’éditeur, la requête ci-dessous fait le même travail. Colle-la dans l’Éditeur avancé d’une requête vide, à condition que ton tableau porte bien le nom Commandes.

let
    Source = Excel.CurrentWorkbook(){[Name="Commandes"]}[Content],
    #"Lignes inversées" = Table.ReverseRows(Source)
in
    #"Lignes inversées"

Une semaine plus tard, tu remplaces le contenu du tableau par le nouvel export, puis tu cliques sur Actualiser tout dans l’onglet Données. La feuille de résultat se refait toute seule, et l’export d’origine reste tel que tu l’as collé.

Sélectionner la plage à retourner avant de lancer la macro

La macro travaille sur ce que tu as sélectionné, et sur rien d’autre, d’où l’importance de ce geste. Sélectionne A1:D7, ligne d’en-tête comprise, puis lance la macro par Alt+F8 depuis la liste des macros. L’en-tête reste en place pendant que les six commandes se retournent sous lui, parce que le réglage garderEntete laisse la première ligne de la sélection tranquille.

Cette même règle devient un piège si tu sélectionnes A2:D7, c’est-à-dire les données sans leur en-tête. La macro prend alors la ligne 2 pour un en-tête et ne retourne que les lignes 3 à 7, si bien que CMD-1042 reste en haut. Le compte rendu le dit d’ailleurs en toutes lettres, avec « A3:D7 retourné (5 ligne(s)) » au lieu de six. Passe garderEntete à False pour retourner une sélection qui ne contient que des données.

La sélection doit enfin tenir d’un seul bloc, et une sélection en plusieurs morceaux faite avec Ctrl est refusée avant que rien ne bouge. Pour échanger deux colonnes précises plutôt que renverser tout l’ordre, c’est l’autre macro qu’il te faut, celle de la page inverser deux colonnes.

Garder le cumul juste quand les lignes changent de place

C’est sur la colonne Cumul que la macro se sépare des autres méthodes, et l’aperçu ci-dessus le montre ligne par ligne. Chaque formule de la plage se réécrit sur sa nouvelle ligne comme si tu l’y avais recopiée, ses références figées restant figées et les autres suivant le décalage. La formule =SOMME($C$2:C2) de la ligne 2 part donc en ligne 7 sous la forme =SOMME($C$2:C7), et le cumul affiche 980, 2 110, 2 850, 4 460, 5 350 puis 6 600.

Les formules posées autour de la plage gardent, elles, leur texte d’origine. La plage retournée occupe exactement les mêmes cellules qu’avant, si bien qu’une somme comme =SOMME(C2:C7) en ligne 8 continue de rendre 6 600, et qu’une formule d’une autre feuille qui lit Commandes!C2 lit toujours Commandes!C2, dont le contenu vaut désormais 980. La macro compte ces formules et donne leurs adresses dans son compte rendu, pour que tu saches lesquelles regarder.

Ce travail de réécriture est le même que celui d’un tri, et c’est voulu. Là où TRIERPAR recopie les chiffres déjà calculés et laisse un cumul qui descend, la macro et le tri rendent tous deux un cumul qui monte du plus ancien au plus récent.

Régler le sens, l’en-tête et l’échange des seules valeurs

Trois lignes marquées « À MODIFIER » ouvrent la macro, juste après ses déclarations, et se changent directement dans le code collé.

• sens = "lignes" "colonnes" retourne l’ordre des colonnes, de gauche à droite, au lieu de l’ordre des lignes, de haut en bas.
• garderEntete = True Laisse en place la première ligne de la sélection, ou sa première colonne en mode "colonnes", pour une sélection qui part de l’en-tête. False retourne tout, en-tête compris.
• contenuSeul = False True n’échange que les valeurs saisies. Chaque cellule garde alors sa mise en forme, et les formules de la plage restent à leur place. Une plage qui porte une formule est refusée dans ce mode.

Le mode contenuSeul sert quand la mise en forme dit quelque chose que l’ordre ne doit pas emporter, une ligne surlignée en jaune qui marque la ligne en cours par exemple. Les valeurs changent de place, les couleurs restent où elles sont, et un texte comme 01000 garde son zéro de tête au lieu de redevenir un nombre.

Installer la macro Inverser_ordre_plage dans un module

Se méfier des codes VBA qui passent par un tableau de valeurs

Les codes qu’on trouve en ligne pour retourner une plage lisent presque tous la plage dans un tableau de valeurs, retournent le tableau, puis le réécrivent d’un bloc. Le geste paraît propre et tient en dix lignes, mais il ne déplace rien du tout, il recopie des valeurs par-dessus les cellules. Les six formules de la colonne Cumul y laissent leur peau, remplacées par les nombres 6 600, 5 620, 4 490, 3 750, 2 140 et 1 250, qui ne se recalculent plus jamais et descendent au lieu de monter.

La mise en forme reste au passage collée à sa ligne d’origine, puisque seules les valeurs ont bougé. Sur notre exemple, un surlignage posé sur la ligne de CMD-1042 reste sur la ligne 2 alors que la commande est partie en ligne 7, et une note attachée à cette première cellule ne bouge pas davantage. Notre macro déplace les cellules elles-mêmes, exactement comme si tu les faisais glisser, ce qui emmène la couleur, la bordure et la note avec la ligne.

La seconde forme, celle qui échange deux cellules par une variable déclarée en texte, transforme chaque valeur en chaîne de caractères au passage, puisque c’est tout ce qu’une telle variable sait porter. Notre macro ne fait jamais ce détour, même dans son mode contenuSeul, où elle remet au contraire l’apostrophe qui garde un code comme 01000 en texte au lieu de le laisser redevenir le nombre 1000.

Connaître les limites de la macro Inverser_ordre_plage

• Relancer la macro sur la même sélection, qui reste en place après le geste, ramène le tableau à son ordre de départ, formules au texte identique. C’est le seul retour en arrière, puisque Ctrl+Z n’annule pas le retournement.

• Elle est faite pour un tableau, pas pour un gros export, parce qu’elle déplace les lignes deux par deux. Compte une seconde ou deux pour cinquante lignes, une poignée pour deux cents, et nettement plus dès que le classeur porte déjà beaucoup de formules. Sur un export de plusieurs milliers de lignes, la colonne d’aide et le tri font le même travail en un instant.

• Une ligne masquée à la main reste masquée à son numéro de ligne, et le masquage ne suit donc pas ses données. Affiche les lignes masquées avant de lancer la macro si leur masquage a un sens.

• Dans le mode par défaut, une plage prise dans un tableau créé par Ctrl+L est refusée, et le compte rendu nomme le tableau. Retourne-le par un tri sur une colonne de numéros, ou convertis-le en plage depuis l’onglet Création de tableau. Le mode contenuSeul ne pose pas ce refus, puisqu’il ne déplace aucune cellule.

• Sont refusées de la même façon une sélection en plusieurs morceaux, une sélection qui ne compte qu’une ligne à retourner, des cellules fusionnées, une plage qui touche le résultat d’une formule qui déborde, un tableau croisé dynamique, une feuille filtrée et une feuille protégée. Chaque refus dit pourquoi dans la barre d’état, et rien ne bouge.

• Elle a enfin besoin d’une ligne libre tout au bout de la feuille pour y faire passer les tranches, et refuse le geste quand cette ligne est occupée. Une feuille remplie jusqu’à sa dernière ligne demande donc d’être allégée par le bas avant que la macro accepte de tourner.

• Elle ne trie pas, faute de clé. Elle ne fait que renverser l’ordre où les lignes se trouvent, et deux exécutions de suite ramènent donc le tableau à son point de départ.

• Comme toute macro, elle ne tourne pas dans Excel pour le web, qui n’exécute pas le VBA.

Écrire CHOISIRCOLS avec des numéros de colonne négatifs

La fonction CHOISIRCOLS fonctionne comme CHOISIRLIGNES, mais sur les colonnes, et ses numéros négatifs partent eux aussi de la fin. La formule ci-dessous, écrite sous le tableau, rend les trois trimestres dans l’ordre T1, T2, T3, avec leurs chiffres dessous.

Fais attention à ne pas prendre la colonne des libellés dans la plage, car =CHOISIRCOLS(A1:D2;-1;-2;-3;-4) renverrait aussi les mots Trimestre et Chiffre, envoyés en dernière position. La macro de cette page fait le même travail sur place quand tu passes son réglage sens à "colonnes", et son réglage garderEntete laisse alors la colonne A tranquille.

Distinguer l’ordre inversé de la transposition

La confusion écartée en tête de page revient sur un tableau couché, et pour une bonne raison, c’est là que la transposition donne le résultat qui ressemble le plus à celui qu’on cherchait. Les chiffres de forme suffisent pourtant à trancher, puisque l’onglet Trimestre tient sur quatre colonnes de deux lignes avant comme après le retournement de ses colonnes.

Transposé, le même onglet rend au contraire deux colonnes de quatre lignes, où les trimestres descendent les uns sous les autres. C’est le travail du collage spécial et de sa case Transposé, ou de la fonction TRANSPOSE, et la fiche du lexique sur transposer un tableau détaille les deux routes. Si tu cherchais plutôt à échanger deux colonnes précises sans toucher aux autres, la page inverser deux colonnes tient ce cas-là.