Tri Excel avec une formule utilisant SORT et SORTBY - Conseils Excel

Cette semaine, lors de la conférence Ignite à Orlando en Floride, Microsoft a lancé une série de nouvelles formules matricielles plus simples dans Excel. Je couvrirai ces nouvelles formules tous les jours cette semaine, mais si vous souhaitez lire à l'avance:

Lundi a couvert la nouvelle formule = A2: A20, l'erreur SPILL et la nouvelle fonction SINGLE requise à la place de l'intersection implicite
Aujourd'hui couvrira SORT et SORTBY
Mercredi couvrira FILTER
Jeudi couvrira UNIQUE
Vendredi couvrira les fonctions SEQUENCE et RANDARRAY

Le tri avec une formule dans Excel nécessitait une combinaison insensée de formules. Jetez un œil à ces données qui seront utilisées tout au long de cet article.

Données en A3: C11.

Afin de trier cela avec une formule avant cette semaine, il vous suffit de supprimer RANK, COUNTIF, MATCH, INDEX et INDEX. Une fois que vous avez terminé cet ensemble de formules, vous seriez prêt pour une sieste.

L'ancienne façon de trier avec une formule

Joe McDaid et son équipe nous ont apporté SORT et SORTBY.

Commençons par TRIER. Voici la syntaxe=SORT(Array, (Sort Index), (Sort Order), (By Column))

La fonction SORT

Supposons que vous souhaitiez trier A3: C16 par le champ Score. Le score est la troisième colonne du tableau, donc votre index de tri sera de 3.

Les choix pour l'ordre de tri sont 1 pour croissant ou -1 pour décroissant. Je ne me plains pas, mais il n'y aura jamais de support pour Trier par couleur, Trier par formule ou Trier par liste personnalisée à l'aide de cette fonction.

Spécifiez 3 comme colonne de tri et -1 comme ordre de tri décroissant.

Le quatrième argument sera rarement utilisé. Il est possible dans la boîte de dialogue Trier de trier par colonne au lieu de lignes. 99,9% des personnes trient par lignes. Si vous devez trier par colonne, spécifiez True dans l'argument final. Cet argument est facultatif et vaut par défaut False.

Si vous devez trier par colonnes, utilisez True dans le 4ème argument

Voici les résultats de la formule. Grâce au nouveau moteur de calcul, la formule se répand dans les cellules adjacentes. Une formule dans O2 produit cette solution.

Il n'est pas nécessaire d'appuyer sur Ctrl + Maj + Entrée

Les données d'origine sont triées

Et si vous avez besoin d'un tri à deux niveaux? Trier par colonne 2 ascendante et colonne 3 décroissante? Fournissez une constante de tableau pour les 2ème et 3ème arguments:=SORT(A2:C17,(2;3),(1;-1))

Tri à deux niveaux

La fonction SORTBY vous permet de trier par quelque chose qui ne figure pas dans les résultats

La syntaxe de la fonction SORTBY est =SORTBY(array, by_array1, sort_order1,)

TRIER autre chose

Revenir aux données d'origine. Supposons que vous souhaitiez trier par équipe puis par score, mais n'afficher que les noms. Vous pouvez utiliser SORTBY comme indiqué ici.

Trier la colonne A par colonne B et colonne C

Test de drogue aléatoire et aléatoire sans répétition

Des scénarios difficiles tels que les tests aléatoires de dépistage de drogues et les tests aléatoires sans répétition deviennent extrêmement simples lorsque vous combinez SORT avec RANDARRAY.

Dans la figure ci-dessous, vous souhaitez trier les 13 noms de manière aléatoire sans répétition. Utilisez =SORTBY(A4:A16,RANDARRAY(13)). En savoir plus sur RANDARRAY vendredi.

Tri au hasard sans répétition

Est-ce que Ctrl + Maj + Entrée est complètement mort? Non. Il y a encore une utilité pour cela. Disons que vous ne vouliez que les 3 premiers résultats de la fonction SORT. Vous pouvez sélectionner trois cellules, taper la fonction TRIER et la suivre avec Ctrl + Maj + Entrée. Cela empêchera les résultats de déborder au-delà des limites de la formule originale.