Le tutoriel montre comment utiliser la fonction de tri pour trier les tableaux de données dynamiquement. Vous apprendrez une formule pour trier alphabétiquement dans Excel, organiser des numéros dans l'ordre ascendant ou descendant, trier par plusieurs colonnes, etc.

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

Fonction de tri Excel

La fonction de tri dans Excel trie le contenu d'un tableau ou d'une plage par colonnes ou lignes, dans l'ordre croissant ou descendant.

Le tri appartient au groupe des fonctions de tableau dynamique. Le résultat est un réseau dynamique qui déborde automatiquement vers les cellules voisines verticalement ou horizontalement, selon la forme du réseau source.

La syntaxe de la fonction de tri est la suivante:

Où:

Array (requis) - est un tableau de valeurs ou une gamme de cellules à trier. Il peut s'agir de valeurs, notamment du texte, des nombres, des dates, des heures, etc.

SORT_INDEX (Facultatif) - Un entier qui indique la colonne ou la ligne à trier. Si elle est omise, l'index par défaut 1 est utilisé.

Sort_order (facultatif) - Définit l'ordre de tri:

• 1 ou omis (par défaut) - commande ascendante, c'est-à-dire du plus petit au plus grand
• -1 - Ordre descendant, 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.
• Vrai - Trier par colonne. Utilisez cette option si vos données sont organisées horizontalement dans des colonnes comme dans cet exemple.

Fonction de tri Excel - Conseils et notes

Le tri est une nouvelle fonction de tableau dynamique et, en tant que tel, il a quelques spécificités dont vous devez conna?tre:

• Actuellement, la fonction de tri est uniquement disponible dans Microsoft 365 et Excel 2021. Excel 2019, Excel 2016 ne prend pas en charge les formules de tableau dynamique, donc la fonction de tri n'est pas disponible dans ces versions.
• Si le tableau renvoyé par une formule de tri est le résultat final (c'est-à-dire non transmis à une autre fonction), Excel crée dynamiquement une plage de taille appropriée et le remplit avec les valeurs triées. Alors, assurez-vous d'avoir toujours suffisamment de cellules vides ou / et à droite de la cellule où vous entrez la formule, sinon une erreur #spill se produit.
• Les résultats se mettent à jour dynamiquement à mesure que les données source changent. Cependant, le tableau fourni à la formule ne s'étend pas automatiquement pour inclure de nouvelles entrées qui sont ajoutées en dehors du tableau référencé. Pour inclure ces éléments, vous devez soit mettre à jour la référence du tableau dans votre formule, soit convertir la plage source en une table comme indiqué dans cet exemple, soit créer une plage dynamique nommée.

Formule de tri Excel de base

Cet exemple montre une formule de base pour le tri des données dans Excel dans l'ordre croissant et descendant.

En supposant que vos données sont organisées par ordre alphabétique comme indiqué dans la capture d'écran ci-dessous. Vous cherchez à trier les numéros dans la colonne B sans casser ou mélanger les données.

Formule à trier dans l'ordre croissant

Pour trier les valeurs dans la colonne B de la plus petite au plus grande, voici la formule à utiliser:

=SORT(A2:B8, 2, 1)

Où:

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

étant donné que nos données sont organisées en lignes, le dernier argument peut être omis par défaut en faux - tri par lignes.

Entrez simplement la formule dans n'importe quelle cellule vide (D2 dans notre cas), appuyez sur Entrée , et les résultats se répandont automatiquement à D2: E8.

Formule à trier dans l'ordre descendant

Pour trier les données descendant, c'est-à-dire du plus grand au plus petit, définissez l'argument Sort_Order sur -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 de texte dans l'ordre alphabétique de A à Z ou de Z à A.

Comment trier les données dans Excel en utilisant la formule

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

Excel Trier par colonne

Lorsque vous triez les données dans Excel, vous modifiez pour la plupart l'ordre des lignes. Mais lorsque vos données sont organisées horizontalement avec des lignes contenant des étiquettes et des colonnes contenant des enregistrements, vous devrez peut-être trier de gauche à droite, plut?t que de haut en bas.

Pour trier par colonne dans Excel, définissez l'argument BY_COL sur true. Dans ce cas, Sort_index représentera une ligne, pas 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 les données source à trier
• 2 est l'indice de tri, car nous trions les nombres dans la deuxième ligne
• -1 indique l'ordre descendant de tri descendant
• Le vrai signifie trier les colonnes, pas les lignes

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

Lorsque vous travaillez avec des modèles de données complexes, vous pouvez souvent avoir besoin d'un type à plusieurs niveaux. Cela peut-il être fait avec une formule? Oui, facilement! Ce que vous faites est de fournir des constantes de tableau pour les arguments SORT_INDEX et SORT_ORDER .

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:

• Le tableau est les données de A2: C13.
• SORT_INDEX est la constante de tableau {1,3}, puisque nous triez d'abord par région (1 ^st colonne), puis par qté . (3 ^rd colonne).
• SORT_ORDER est la constante de tableau {1, -1}, car la 1ère colonne doit être triée dans l'ordre croissant et la colonne 3 ^rd dans l'ordre descendant.
• BY_COL est omis car nous trie les lignes, ce qui est par défaut.

En rassemblant les arguments, nous obtenons cette formule:

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

Et ?a marche parfaitement! Les valeurs de texte dans 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

Dans le cas où lorsque vous cherchez à filtrer les données avec certains critères et à mettre la sortie en ordre, utilisez les fonctions de tri et de filtre:

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

La meilleure chose à propos de cette formule est qu'il publie également les résultats comme une plage de déversement dynamique, sans que vous ayez à appuyer sur Ctrl Shift, entrez ou devinez combien de cellules à la copier. Comme d'habitude, vous tapez une formule dans la cellule la plus supérieure et appuyez sur la touche Entrée.

Par exemple, nous allons extraire des éléments avec une quantité égale ou supérieure à 30 (> = 30) à partir des données source dans A2: B9 et organisez les résultats dans l'ordre croissant.

Pour cela, nous avons d'abord configuré la condition, par exemple, dans la cellule E2 comme indiqué dans l'image ci-dessous. Et puis, construisez notre formule Excel de cette manière:

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

Outre le tableau généré par la fonction de filtre, nous spécifions uniquement l'argument SORT_INDEX (colonne 2). Les deux arguments restants sont omis car les défauts fonctionnent exactement comme nous avons besoin (trier ascendant, par ligne).

Obtenez n plus grandes ou les plus petites et triez les résultats

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

Voici une formule générique:

Où n est le nombre des valeurs que vous souhaitez retourner.

à partir de l'ensemble de données ci-dessous, supposons que vous souhaitez obtenir une liste des 3 premiers en fonction des numéros de la colonne C.

Pour l'avoir fait, vous triez d'abord le tableau A2: C13 par la colonne 3 ^rd dans l'ordre décroissant:

SORT(A2:C13, 3, -1)

Et puis, nid la formule ci-dessus dans le premier argument ( tableau ) de la fonction d'index pour que le tableau soit trié du plus haut au plus petit.

Pour le deuxième argument ( ROW_NUM ), qui indique le nombre de lignes à retourner, générez les nombres séquentiels requis en utilisant la fonction de séquence. Comme nous avons besoin de 3 valeurs supérieures, nous utilisons la séquence (3), qui est la même que la fourniture d'une constante de réseau vertical {1; 2; 3} directement dans la formule.

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

Finalement, nous obtenons la formule suivante:

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

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

Pour retourner 3 valeurs inférieures , triez simplement les données d'origine du plus petit au plus grand. Pour cela, modifiez l'argument Sort_Order de -1 à 1:

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

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

En regardant sous un autre angle, que se passe-t-il si vous voulez seulement retourner une position de tri spécifique? Disons, seulement le 1er, seulement le 2ème ou seulement le 3ème enregistrement de la liste triée? Pour le faire, utilisez la version simplifiée de la formule de tri Index discutée ci-dessus:

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

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

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

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

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

Lorsque A2: C13 est des données source, F1 est la position en haut, i1 est la position en bas, et {2,3} sont les colonnes à retourner.

Utilisez une table Excel pour obtenir un tableau de tri pour se développer automatiquement

Comme vous le savez déjà, le tableau trié se met à jour automatiquement lorsque vous apportez des modifications aux données d'origine. Il s'agit du comportement standard de toutes les fonctions de tableau dynamique, y compris le tri. Cependant, lorsque vous ajoutez de nouvelles entrées en dehors du tableau référencé, ils ne sont pas automatiquement inclus dans une formule. Si vous souhaitez que votre formule réponde à ces modifications, convertissez la plage de source en une table Excel entièrement fonctionnelle et utilisez des références structurées dans votre formule.

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

En supposant que vous utilisez la formule de tri Excel ci-dessous pour organiser des valeurs dans la plage A2: B8 dans l'ordre alphabétique:

=SORT(A2:B8, 1, 1)

Ensuite, vous entrez une nouvelle entrée dans la ligne 9… et êtes dé?u de voir que la nouvelle entrée ajoutée est laissée hors de la plage de déversement:

Maintenant, convertissez la plage de source en table. Pour cela, sélectionnez simplement votre gamme, y compris les en-têtes de colonne (A1: B8) et appuyez sur Ctrl T. Lors de la construction de votre formule, sélectionnez la plage source à l'aide de la souris et le nom du tableau sera inséré automatiquement dans la formule (ceci est appelé 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éveloppera automatiquement et les nouvelles données seront incluses dans la plage de déversement de la formule de tri:

La fonction de tri Excel ne fonctionne pas

Si votre formule de tri se traduit par une erreur, c'est probablement pour les raisons suivantes.

#Name Erreur: version ancienne Excel

Le tri est une nouvelle fonction et ne fonctionne que dans Excel 365 et Excel 2021. Dans les anciennes versions où cette fonction n'est pas prise en charge, un #Name? une erreur se produit.

Erreur #spill: quelque chose bloque la plage de déversement

Si une ou plusieurs cellules de la plage de déversement ne sont pas complètement vides ou fusionnées, un #pill! une erreur s'affiche. Pour le réparer, retirez simplement le blocage. Pour plus d'informations, veuillez consulter Excel #spill! Erreur - ce que cela signifie et comment réparer.

# Erreur de valeur: arguments non valides

Chaque fois que vous rencontrez un #Value! Erreur, vérifiez les arguments SORT_INDEX et SORT_ORDER . SORT_INDEX ne doit pas dépasser le nombre de colonnes est le tableau , et Sort_Order doit être 1 (ascendant) ou -1 (descendant).

#Ref Erreur: le classeur source est fermé

étant donné que les tableaux dynamiques ont une prise en charge limitée des références entre les classeurs, la fonction de tri nécessite que les deux fichiers soient ouverts. Si le classeur source est fermé, une formule lancera un #ref! erreur. Pour le réparer, ouvrez simplement le fichier référencé.

Voilà comment trier les données dans Excel en utilisant la formule. Je vous remercie d'avoir lu et j'espère vous voir sur notre blog la semaine prochaine!

Classeur de pratique pour télécharger

Tri en Excel avec des formules (fichier .xlsx)

Ce qui précède est le contenu détaillé de. pour plus d'informations, suivez d'autres articles connexes sur le site Web de PHP en chinois!