Comment utiliser les fonctions de tri et de sort dans Microsoft Excel

Les fonctions de tri et sortby vous permettent d'extraire certaines colonnes et lignes à partir d'un ensemble de données et de les trier dans un certain ordre, tout en préservant les données source. Même s'ils travaillent de manière similaire, il existe des différences clés que vous devez connaître si vous décidez laquelle utiliser.

Dans tous les exemples de ce guide, les données source sont formatées sous forme de table Excel. En effet, les fonctions de tri et de tri fonctionnent mieux lorsque les données source sont structurées de cette manière. En conséquence, toutes les formules utilisent des références structurées. Pour appliquer les mêmes principes dans des gammes régulières, utilisez plutôt des références de cellules directes.

Les syntaxes de tri et de sortby

Avant d'entrer dans les différents comportements des fonctions de tri et de sort dans Excel, voici un aperçu rapide de leurs syntaxes et un exemple simple de chaque utilisation.

La fonction de tri de Microsoft Excel a quatre arguments – un requis et trois facultatifs:

=SORT(a,b,c,d)

où

• un (requis) est le tableau (une table entière ou une partie d'une table) contenant les colonnes ou les lignes que vous souhaitez trier,
• b (Facultatif) est un numéro d'index indiquant quelle colonne ou ligne à trier,
• c (facultatif) est un nombre déterminant l'ordre de tri (1 = ascendant; -1 = descendant), et
• d (Facultatif) est une valeur booléenne indiquant la direction de tri (false = tri verticalement par ligne; true = tri horizontalement par colonne).

Si l'argument b est omis, la première ligne ou colonne est retournée. Si l'argument c est omis, Excel s'engage à une commande ascendante. Si l'argument d est omis, les données sont triées verticalement par ligne.

Si vous avez besoin de trier vos données horizontalement, plutôt que d'utiliser l'argument dEnvisagez de transposer vos données afin que les lignes deviennent des colonnes et que les colonnes deviennent des lignes.

Dans cet exemple, taper:

=SORT(T_Scores,5,-1)

extrait et trie l'intégralité du tableau T_Score par la colonne totale (5) dans l'ordre descendant (-1).

Que vous utilisiez la fonction SORT ou SORTBY, pour éviter que les en-têtes de colonne de la table soient considérés comme des lignes de données dans le processus de tri, assurez-vous que cet argument un ne comprend que les lignes sous la rangée d'en-tête. Ensuite, dupliquez les en-têtes de colonne directement au-dessus du résultat en les tapant manuellement ou en utilisant une formule, telle que:

=T_Scores(#Headers)

D'un autre côté, la fonction SORTBY a deux arguments obligatoires, et le nombre d'arguments facultatifs que vous utilisez dépend du nombre de lignes ou de colonnes par lesquelles vous souhaitez trier les données:

=SORTBY(a,b¹,b²,c¹,c²...)

où

• un (requis) est le tableau (une table entière ou une partie d'une table) contenant les colonnes ou les lignes que vous souhaitez trier,
• B¹ (requis) est le premier tableau à trier,
• b² (Facultatif) est un nombre déterminant l'ordre de tri du premier tableau de tri (1 = ascendant; -1 = descendant),
• c¹ (Facultatif) est le deuxième tableau à trier, et
• c² (Facultatif) est le numéro de commande de deuxième tri.

Autrement dit, B¹ et b² sont le premier couple d'ordre en tableau, c¹ et c² sont le deuxième appariement d'ordre de tableau, et le nombre maximum de ces couples est déterminé par le nombre de lignes ou de colonnes dans l'ensemble de données que vous souhaitez trier.

Si vous n'avez qu'un seul argument (B¹), vous pouvez omettre l'argument de trimestre (b²), et le résultat sera par défaut à une commande ascendante. Cependant, si vous avez plus d'un argument triable, ils doivent tous être accompagnés d'un argument de tri.

L'ensemble du tableau et des tableaux triés doivent contenir le même nombre de lignes si vous triagez les données verticalement, ou le même nombre de colonnes si vous triez les données horizontalement.

Par exemple, taper:

=SORTBY(T_Scores,T_Scores(Total),-1)

extrait et trie l'intégralité du tableau T_Score par la colonne totale (T_Scores (total)) dans l'ordre descendant (-1).

Compatibilité avec différentes versions d'Excel

Sur Windows et Mac, les fonctions de tri et de sort sont prises en charge dans les versions autonomes d'Excel publiées en 2021 ou ultérieurement et Excel pour Microsoft 365. Vous pouvez également utiliser ces fonctions dans Excel pour le Web, la version gratuite en ligne du programme. Alternativement, les fonctions de tri et de sort sont disponibles pour ceux qui utilisent les applications mobiles et tablettes Excel ou l'application mobile Copilot Microsoft 365.

Identifier le tableau trié par

Je préfère souvent utiliser la fonction Sortby car elle est plus fiable que la fonction de tri lorsque des colonnes sont ajoutées ou supprimées des données source.

Ici, après avoir extrait la table T_Scores et le tri par la colonne totale dans l'ordre descendant à l'aide de la fonction Sortby, j'ai inséré une colonne dans l'ensemble de données source. Étant donné que la fonction SORTBY identifie la colonne tri-by par un tableau ou un nom de colonne sélectionné, le résultat est toujours trié par la colonne totale.

Cependant, si j'insère une colonne dans les données source après avoir utilisé la fonction de tri, le numéro d'index dans l'argument tri-by ne reconnaît pas la modification de la structure de l'ensemble de données source. En d'autres termes, la colonne totale est maintenant la colonne 5, mais la formule fait toujours référence à la colonne 4. En conséquence, je dois soit mettre à jour la formule manuellement ou trouver un moyen d'automatiser ce processus.

Donc, si vous envisagez votre forme de changement de données, la fonction Sortby sera la meilleure option à long terme.

Extraction et trier une seule colonne

L'utilisation la plus élémentaire des fonctions de tri et de sort est d'extraire et de trier un seul tableau à partir d'un ensemble de données.

Supposons que vous souhaitiez extraire et trier alphabétiquement les équipes de ce tableau T_PL, qui est actuellement trié par la colonne de capacité du plus grand au plus petit.

Pour ce faire en utilisant la fonction de tri, vous devez taper:

=SORT(T_PL(Team))

Étant donné que cette fonction trie automatiquement les données par la première colonne dans l'ordre croissant (alphabétique) si seul le premier argument était donné, la formule est belle et courte.

D'un autre côté, pour faire de même en utilisant la fonction Sortby, vous devez taper:

=SORTBY(T_PL(Team),T_PL(Team))

Étant donné que cette fonction vous oblige à saisir à la fois un tableau et un tableau tri-by.

Ainsi, la fonction de tri est beaucoup plus efficace que la fonction Sortby lors de l'extraction et du tri d'un tableau unique à partir d'un ensemble de données, surtout si vous voulez que le résultat soit dans l'ordre croissant.

Extraction et tri de données par plusieurs niveaux

L'une des plus grandes différences entre les fonctions de tri et de tri est la méthode que vous devez utiliser pour extraire et trier vos données par plus d'une variable.

Dans cet exemple, disons que vous souhaitez extraire et trier le tableau T_Scores par la colonne totale, et où deux valeurs dans cette colonne sont les mêmes (dans ce cas, les totaux de Tom et Dave), vous voulez trier les données alphabétiquement par les noms des joueurs.

Pour y parvenir en utilisant la fonction Sortby, dont la syntaxe est spécialement conçue pour répondre à un tri à plusieurs niveaux, vous devez taper la formule suivante:

=SORTBY(T_Scores,T_Scores(Total),-1,T_Scores(Name),1)

où

• T_score est le nom de la table que vous souhaitez extraire et trier,
• T_score (total), – 1 Comme le premier couple tri-à l'ordre indique Excel que la colonne principale par laquelle vous souhaitez trier le tableau est la colonne totale, en ordre décroissant, et
• T_score (nom), 1 Comme le deuxième appariement triable par ordre indique Excel que la colonne secondaire par laquelle vous souhaitez trier le tableau est la colonne de nom, dans l'ordre croissant (alphabétique).

Remarquez comment les données extraites sont triées par la colonne totale, et Dave et Tom – dont les totaux sont les mêmes – sont triés par ordre alphabétique.

D'un autre côté, si vous essayez d'atteindre le même résultat en utilisant la fonction de tri, vous frapperez un obstacle, car – à la valeur nominale – il vous permet seulement de saisir un tableau de tri et un numéro d'index trié. Cependant, vous pouvez surmonter cet obstacle en utilisant des constantes de tableau:

=SORT(T_Scores,{4,1},{-1,1})

où

• T_score est le nom de la table que vous souhaitez extraire et trier,

• {4,1} dit à Excel que vous souhaitez d'abord trier les données par la quatrième colonne avant de le tri par la première colonne, et

• {-1,1} dit à Excel que la quatrième colonne devrait être en ordre décroissant et que la première colonne devrait être en ordre croissant.

Comme avec l'exemple Sortby ci-dessus, le résultat extrait et trie les données par la colonne totale, et lorsque toutes les valeurs sont égales, les données sont ensuite également triées par la colonne de nom.

Dans l'ensemble, les fonctions Sortby et Sort peuvent trier les données à plus d'un niveau, et les deux ont leurs avantages. Si vous prévoyez de partager votre classeur avec d'autres, ils peuvent être plus familiers avec l'utilisation de la syntaxe de formule conventionnelle sans constantes de tableau, donc l'utilisation de la fonction Sortby serait l'option préférée. D'un autre côté, la formule de tri contenant des constantes de tableau est beaucoup plus courte, donc si vous cherchez à gagner du temps, cela pourrait être le meilleur itinéraire.

Cependant, comme je l'ai discuté précédemment, la fonction Sortby est meilleure pour faire face à de nouvelles colonnes en cours d'ajout aux données source, car elle ne reposait pas sur les numéros d'index de colonne. En conséquence, c'est toujours quelque chose à garder à l'esprit lors du choix de la fonction à utiliser.

Comportement des résultats

La beauté des fonctions de tri et de sort est que si les valeurs des données source changent, elles se reflètent dans le résultat trié. De plus, SORT et SORTBY sont des fonctions de tableau dynamique, ce qui signifie que si les données source sont dans une table Excel formatée, le résultat se développe ou rétrécit si vous ajoutez ou supprimez les lignes.

Vous savez qu'une fonction produit des tableaux dynamiques lorsque (1) le résultat déborde de la cellule où vous avez tapé la formule aux cellules adjacentes, et (2) une bordure bleue temporaire apparaît lorsque vous sélectionnez une cellule dans le résultat. Ici, même si la formule SORTBY est tapée dans la cellule F2, le résultat déborde de la cellule F2 à la cellule i5.

Cependant, avec ce dynamisme vient un inconvénient majeur: vous ne pouvez pas utiliser les fonctions de tri et de sort dans une table Excel formatée. En effet, les tables Excel ne sont pas compatibles avec les formules de tableau dynamique, car ces deux fonctionnalités Excel sont conçues pour se développer et rétrécir automatiquement.

Vous pouvez également faire face à des problèmes si l'argument du tableau de tri pour l'une ou l'autre des fonctions fait référence à un ensemble de données dans un autre classeur Excel. Plus précisément, les fonctions de tableau dynamique faisant référence aux sources externes ne fonctionnent que lorsque les deux classeurs sont ouverts. Donc, si vous fermez le classeur source, toutes les formules de tableau dynamique liées renvoient un #Ref! Erreur lorsqu'elle est rafraîchie.

Résumé: Devriez-vous utiliser Soi ou Sortby?

Que vous utilisiez la fonction de tri ou la fonction Sortby dépend de nombreux facteurs, tels que si vous prévoyez d'insérer des colonnes dans l'ensemble de données source, le nombre de niveaux de type que vous souhaitez appliquer et si vous prévoyez de partager votre classeur avec d'autres.

Voici un résumé des principales similitudes et différences:

---

Pour extraire et trier les tableaux non adjacents dans vos données, nidine les fonctions ChooseCols ou ChooseRows à l'intérieur de votre sorte ou de votre formule SORTBY. Alternativement, pour extraire, filtrer et trier les colonnes à partir d'un ensemble de données simultanément, nid la fonction de filtre à la place.