Fonction SORT d'Excel - tri automatique des données à l'aide d'une formule (2024)

Ce tutoriel montre comment utiliser la fonction SORT pour trier dynamiquement des tableaux de données. Vous apprendrez une formule pour trier par ordre alphabétique dans Excel, classer des nombres par ordre croissant ou décroissant, trier par plusieurs colonnes, etc.

La fonctionnalité de tri existe depuis longtemps. Mais avec l'introduction des tableaux dynamiques dans Excel 365, un moyen étonnamment simple de trier avec des formules est apparu. La beauté de cette méthode est que les résultats se mettent à jour automatiquement lorsque les données sources changent.

Fonction SORT d'Excel

La fonction SORT d'Excel permet de trier le contenu d'un tableau ou d'une plage par colonnes ou par lignes, dans l'ordre croissant ou décroissant.

SORT appartient au groupe des fonctions de tableau dynamique. Le résultat est un tableau dynamique qui s'étend automatiquement aux cellules voisines verticalement ou horizontalement, selon la forme du tableau source.

La syntaxe de la fonction SORT est la suivante :

SORT(tableau, [sort_index], [sort_order], [by_col])

Où :

Array (obligatoire) - est un tableau de valeurs ou une plage de cellules à trier. Il peut s'agir de n'importe quelle valeur, y compris du texte, des chiffres, des dates, des heures, etc.

Tri_index (facultatif) - un nombre entier qui indique la colonne ou la ligne à trier. S'il est omis, l'index 1 par défaut est utilisé.

Ordre de tri (facultatif) - définit l'ordre de tri :

• 1 ou omis (par défaut) - ordre ascendant, c'est-à-dire du plus petit au plus grand
• -1 - ordre décroissant, c'est-à-dire du plus grand au plus petit

By_col (facultatif) - une valeur logique qui indique la direction du tri :

• FALSE ou omis (par défaut) - trier par ligne. Vous utiliserez cette option la plupart du temps.
• TRUE - trier par colonne. Utilisez cette option si vos données sont organisées horizontalement en colonnes comme dans cet exemple.

Fonction SORT d'Excel - conseils et notes

SORT est une nouvelle fonction de tableau dynamique et, en tant que telle, elle présente quelques spécificités que vous devez connaître :

• Actuellement, la fonction TRI est uniquement disponible dans Microsoft 365 et Excel 2021. Excel 2019, Excel 2016 ne prennent pas en charge les formules de tableau dynamique, la fonction TRI n'est donc pas disponible dans ces versions.
• Si le tableau renvoyé par une formule SORT est le résultat final (c'est-à-dire qu'il n'est pas transmis à une autre fonction), Excel crée dynamiquement une plage de taille appropriée et la remplit avec les valeurs triées. Assurez-vous donc de toujours avoir suffisamment de cellules vides en bas et/ou à droite de la cellule où vous entrez la formule, sinon une erreur #SPILL se produit.
• Les résultats sont mis à jour de façon dynamique en fonction des modifications des données sources. tableau fournie à la formule ne s'étend pas automatiquement pour inclure les nouvelles entrées qui sont ajoutées en dehors de la formule référencée. tableau Pour inclure de tels éléments, vous devez soit mettre à jour la base de données de l'entreprise. tableau dans votre formule, ou convertissez la plage source en un tableau comme indiqué dans cet exemple, ou créez une plage nommée dynamique.

Formule Excel SORT de base

Cet exemple montre une formule de base pour trier des données dans Excel par ordre croissant et décroissant.

Supposons que vos données soient classées par ordre alphabétique comme indiqué dans la capture d'écran ci-dessous. Vous cherchez à trier les chiffres de la colonne B sans casser ou mélanger les données.

Formule pour trier par ordre croissant

Pour trier les valeurs de la colonne B du plus petit au plus grand, voici la formule à utiliser :

=SORT(A2:B8, 2, 1)

Où :

• A2:B8 est le tableau source
• 2 est le numéro de la colonne à trier
• 1 est l'ordre de tri ascendant

Puisque nos données sont organisées en rangées, le dernier argument peut être omis pour que la valeur par défaut soit FALSE - trier par rangées.

Il suffit d'entrer la formule dans une cellule vide (D2 dans notre cas), d'appuyer sur la touche Entrée, et les résultats se déverseront automatiquement dans D2:E8.

Formule pour trier par ordre décroissant

Pour trier les données de manière descendante, c'est-à-dire du plus grand au plus petit, définissez l'attribut ordre de tri à -1 comme ceci :

=SORT(A2:B8, 2, -1)

Entrez la formule dans la cellule supérieure gauche de la plage de destination et vous obtiendrez ce résultat :

De la même manière, vous pouvez trier les valeurs textuelles par ordre alphabétique de A à Z ou de Z à A.

Les exemples ci-dessous montrent quelques utilisations typiques de la fonction SORT dans Excel et quelques autres non triviales.

Excel TRIE par colonne

Mais lorsque vos données sont organisées horizontalement, avec des lignes contenant des étiquettes et des colonnes contenant des enregistrements, vous pouvez avoir besoin de trier de gauche à droite, plutôt que de haut en bas.

Pour trier par colonne dans Excel, définissez le paramètre par_col à TRUE. Dans ce cas, tri_index représentera une ligne, et non une colonne.

Par exemple, pour trier les données ci-dessous par Qté, du plus élevé au plus bas, utilisez cette formule :

=SORT(B1:H2, 2, 1, TRUE)

Où :

• B1:H2 est la donnée source à trier
• 2 est l'indice de tri, puisque nous trions les nombres de la deuxième ligne.
• -1 indique l'ordre de tri décroissant
• VRAI signifie qu'il faut trier les colonnes et non les lignes.

Trier par plusieurs colonnes dans un ordre différent (tri multi-niveaux)

Lorsque vous travaillez avec des modèles de données complexes, vous avez souvent besoin d'un tri multi-niveaux. Peut-on le faire avec une formule ? Oui, facilement ! Il suffit de fournir des constantes de tableau pour les éléments suivants tri_index et ordre de tri arguments.

Par exemple, pour trier les données ci-dessous d'abord par Région (colonne A) de A à Z, puis par Qté (colonne C) du plus petit au plus grand, définissez les arguments suivants :

• Array est la donnée dans A2:C13.
• Tri_index est la constante du tableau {1,3}, puisque nous trions d'abord par Région (1ère colonne), puis par Qté (3ème colonne).
• Ordre de tri est la constante de tableau {1,-1}, puisque la 1ère colonne doit être triée par ordre croissant et la 3ème colonne par ordre décroissant.
• By_col est omis car nous trions les lignes, ce qui est le cas par défaut.

En rassemblant les arguments, on obtient cette formule :

=SORT(A2:C13, {1,3}, {1,-1})

Les valeurs de texte de la première colonne sont triées par ordre alphabétique et les nombres de la troisième colonne, du plus grand au plus petit :

Trier et filtrer dans Excel

Si vous souhaitez filtrer les données en fonction de certains critères et mettre les résultats en ordre, utilisez les fonctions SORT et FILTER ensemble :

SORT(FILTER(tableau, critères_plage = critères ), [sort_index], [sort_order], [by_col])

La fonction FILTER obtient un tableau de valeurs en fonction des critères que vous définissez et transmet ce tableau au premier argument de SORT.

L'avantage de cette formule est qu'elle produit également les résultats sous la forme d'une plage de déversem*nt dynamique, sans que vous ayez à appuyer sur Ctrl + Shift + Enter ou à deviner dans combien de cellules la copier. Comme d'habitude, vous tapez une formule dans la cellule la plus haute et appuyez sur la touche Enter.

À titre d'exemple, nous allons extraire les articles dont la quantité est égale ou supérieure à 30 (>=30) des données source de A2:B9 et classer les résultats par ordre croissant.

Pour cela, il faut d'abord définir la condition, par exemple dans la cellule E2, comme indiqué dans l'image ci-dessous, puis construire notre formule Excel SORT de cette manière :

=SORT(FILTER(A2:B9, B2:B9>=E2), 2)

En dehors de tableau générée par la fonction FILTER, nous ne spécifions que l'élément tri_index Les deux autres arguments sont omis car les valeurs par défaut fonctionnent exactement comme nous le souhaitons (tri croissant, par ligne).

Obtenez les N valeurs les plus grandes ou les plus petites et triez les résultats.

Lors de l'analyse d'énormes quantités d'informations, il est souvent nécessaire d'extraire un certain nombre de valeurs de premier plan. Peut-être pas seulement les extraire, mais aussi les arranger dans l'ordre souhaité. Et idéalement, choisir les colonnes à inclure dans les résultats. Cela vous semble difficile ? Pas avec les nouvelles fonctions de tableau dynamique !

Voici une formule générique :

INDEX(SORT(...), SÉQUENCE( n ), { colonne1_à_retourner , colonne2_à_retourner , ...})

Où n est le numéro des valeurs que vous voulez retourner.

À partir de l'ensemble de données ci-dessous, supposons que vous souhaitiez obtenir une liste des 3 premiers sur la base des chiffres de la colonne C.

Pour ce faire, vous devez d'abord trier le tableau A2:C13 par la 3ème colonne dans l'ordre décroissant :

SORT(A2:C13, 3, -1)

Et ensuite, imbriquez la formule ci-dessus dans le premier ( tableau ) de la fonction INDEX pour que le tableau soit trié du plus grand au plus petit.

Pour le second ( numéro de rang Comme nous avons besoin de 3 valeurs supérieures, nous utilisons SEQUENCE(3), ce qui revient à fournir une constante de tableau vertical {1;2;3} directement dans la formule.

Pour le troisième ( col_num ), qui définit le nombre de colonnes à renvoyer, fournit les numéros de colonne sous la forme d'une constante de tableau horizontale. Nous voulons renvoyer les colonnes B et C, nous utilisons donc le tableau {2,3}.

Finalement, nous obtenons la formule suivante :

=INDEX(SORT(A2:C13, 3, -1), SÉQUENCE(3), {2,3})

Et cela produit exactement les résultats que nous voulons :

Pour revenir 3 fond il suffit de trier les données d'origine du plus petit au plus grand. Pour cela, il faut modifier le paramètre ordre de tri de -1 à 1 :

=INDEX(SORT(A2:C13, 3, 1), SÉQUENCE(3), {2,3})

Retourner une valeur triée dans une position spécifique

D'un autre point de vue, que se passe-t-il si vous souhaitez uniquement renvoyer une position de tri spécifique ? Par exemple, uniquement le premier, le deuxième ou le troisième enregistrement de la liste triée ? Pour ce faire, utilisez la version simplifiée de la formule INDEX SORT présentée ci-dessus :

INDEX(SORT(...), n , { colonne1_à_retourner , colonne2_à_retourner , ...})

Où n est la position d'intérêt.

Par exemple, pour obtenir une position particulière à partir du haut (c'est-à-dire à partir des données triées par ordre décroissant), utilisez cette formule :

=INDEX(SORT(A2:C13, 3, -1), F1, {2,3})

Pour obtenir une position spécifique à partir du bas (c'est-à-dire à partir des données triées par ordre croissant), utilisez celle-ci :

=INDEX(SORT(A2:C13, 3, 1), I1, {2,3})

Où A2:C13 est la donnée source, F1 est la position depuis le haut, I1 est la position depuis le bas, et {2,3} sont les colonnes à retourner.

Utiliser un tableau Excel pour que le tableau de tri se développe automatiquement

Comme vous le savez déjà, le tableau trié se met automatiquement à jour lorsque vous apportez des modifications aux données d'origine. C'est le comportement standard de toutes les fonctions de tableau dynamique, y compris SORT. Cependant, lorsque vous ajoutez de nouvelles entrées en dehors du tableau référencé, elles ne sont pas automatiquement incluses dans une formule. Si vous souhaitez que votre formule réponde à de tels changements, convertissez la plage source en un tableau entièrement numérique.tableau Excel fonctionnel et utilisez des références structurées dans votre formule.

Pour voir comment cela fonctionne en pratique, veuillez considérer l'exemple suivant.

Supposons que vous utilisiez la formule Excel SORT ci-dessous pour classer les valeurs de la plage A2:B8 par ordre alphabétique :

=SORT(A2:B8, 1, 1)

Ensuite, vous saisissez une nouvelle entrée dans la ligne 9... et vous êtes déçu de voir que l'entrée nouvellement ajoutée est exclue de la plage de déversem*nt :

Maintenant, convertissez la plage source en tableau. Pour cela, il suffit de sélectionner votre plage, y compris les en-têtes de colonne (A1:B8), et d'appuyer sur Ctrl + T . Lorsque vous construisez votre formule, sélectionnez la plage source à l'aide de la souris, et le nom du tableau sera inséré automatiquement dans la formule (c'est ce qu'on appelle une référence structurée) :

=SORT(Table1, 1, 1)

Lorsque vous tapez une nouvelle entrée juste en dessous de la dernière ligne, le tableau se développe automatiquement et les nouvelles données sont incluses dans la plage de déversem*nt de la formule SORT :

La fonction SORT d'Excel ne fonctionne pas

Si votre formule SORT donne lieu à une erreur, c'est très probablement pour les raisons suivantes.

#Erreur de nom : ancienne version d'Excel

SORT est une nouvelle fonction et fonctionne uniquement dans Excel 365 et Excel 2021. Dans les versions plus anciennes où cette fonction n'est pas supportée, une erreur #NAME ? se produit.

#SPILL error : something blocks spill range

Si une ou plusieurs cellules de la plage de déversem*nt ne sont pas complètement vides ou fusionnées, une erreur #SPILL ! s'affiche. Pour la corriger, il suffit de supprimer le blocage. Pour plus d'informations, veuillez consulter la rubrique Erreur #SPILL ! d'Excel - ce qu'elle signifie et comment la corriger.

Erreur #VALUE : arguments non valides

Chaque fois que vous rencontrez une erreur #VALUE !, vérifiez l'option tri_index et ordre de tri arguments. Tri_index ne doit pas dépasser le nombre de colonnes est tableau et ordre de tri doit être soit 1 (ascendant) soit -1 (descendant).

#REF error : le classeur source est fermé

Étant donné que les tableaux dynamiques ne prennent que peu en charge les références entre classeurs, la fonction SORT nécessite que les deux fichiers soient ouverts. Si le classeur source est fermé, une formule déclenchera une erreur #REF ! Pour résoudre ce problème, il suffit d'ouvrir le fichier référencé.

Voilà comment trier des données dans Excel à l'aide de formules. Je vous remercie de votre lecture et j'espère vous voir sur notre blog la semaine prochaine !

Cahier d'exercices à télécharger

Tri dans Excel avec des formules (fichier .xlsx)