Filtrer et trier les N meilleures valeurs avec Excel

Pour partager cette vidéo sur les réseaux sociaux ou sur un site, voici son url :


Sujets que vous pourriez aussi aimer :


Trier les N meilleures valeurs

Grâce à la fonction Trier et à d'autres fonctions que nous connaissons déjà, nous allons maintenant pouvoir réaliser des réorganisations de données très spécifiques et particulièrement affinées.



Sur le tableau résultant illustré par la capture, nous repartons du cas précédent. Nous trions dans l'ordre décroissant les résultats en fonction d'un critère sur le score et d'un autre sur le nombre d'essais. Mais cette fois, une contrainte supplémentaire est ajoutée dans la troisième case bleue. Il s'agit d'une limitation à observer sur le nombre de données à extraire. En effet dans ce cas précis, il ne subsiste que les trois meilleurs scores triés dans l'ordre décroissant. De plus, et pour articuler tous nos acquis, nous choisissons de limiter l'extraction aussi en colonnes, en éliminant celle des dates de naissance.

Classeur Excel à télécharger
Pour débuter, il convient de récupérer le classeur Excel offrant la formule permettant de trier les données sur des critères recoupés.

• Télécharger le classeur trier-n-meilleurs.xlsx en cliquant sur ce lien,
• Double cliquer sur le fichier réceptionné pour l'ouvrir dans Excel,
• Puis, cliquer sur le bouton Activer la modification du bandeau de sécurité,

Nous trouvons bien le tableau sur lequel il s'agit de réaliser le tri filtrant restrictif, entre les colonnes B et F. Une extraction est déjà réalisée dans la grille sur sa droite, entre les colonnes H et L. En cellule H4, elle exploite la formule suivante, issue du volet précédent :

=TRIER(FILTRE(tab; (scores>=N4)*(essais<=N7)); {3.5}; {-1.1})

Elle ne concerne que les personnes ayant réalisé un score au moins égal à la valeur mentionnée en N4 et qui a été atteint avec un nombre d'essais inférieur ou égal à la valeur stipulée en cellule N7. Les données résultantes sont d'abord triées dans l'ordre décroissant sur les scores et en cas d'égalité, dans l'ordre croissant sur les essais. C'est la raison de l'imbrication de la fonction Filtre dans la fonction Trier.

Initialiser l'extraction des promus
Même si une extraction sélective est déjà en place, nous allons la rendre encore plus chirurgicale. En effet, sur les critères et clés de tri existants, nous allons choisir de ne conserver que les N meilleurs. Pour cela, nous allons engager le précédent calcul dans la fonction Index, afin de limiter les résultats.

• Cliquer sur la première cellule de la grille d'extraction pour sélectionner la case H4,
• Dans la barre de formule, cliquer juste après le symbole égal pour y placer le point d'insertion,
• Inscrire la fonction d'extraction suivie d'une parenthèse ouvrante, soit : Index(,

Elle englobe ainsi le précédent calcul et s'apprête à réaliser des coupes franches sur des données déjà filtrées et triées.

• Cliquer à la toute fin de la syntaxe pour y replacer le point d'insertion,
• Taper un point-virgule (;) pour passer dans l'argument de l'indice de ligne de la fonction Index,

Les premiers résultats filtrés
Nous souhaitons récupérer les N premiers meilleurs scores pour répondre favorablement à l'indication numérique fournie en cellule N10. Donc, nous ne pouvons pas nous contenter de fournir un seul indice de ligne à cette fonction Index. Nous devons lui fournir une série partant de la première position (1) jusqu'à rejoindre ce seuil. Et comme nous l'avons déjà appris, c'est la fonction matricielle Sequence qui permet de construire ces séries dynamiques.

• Taper la fonction de suites logiques, suivie d'une parenthèse, soit : Sequence(,
• Désigner le plafond de cette série en cliquant sur la cellule N10,
• Fermer la parenthèse de la fonction Sequence,
• Puis, taper un point-virgule (;) pour passer dans l'argument de l'indice de colonne,

Restreindre les colonnes résultantes
Nous l'avons dit, nous souhaitons extraire tous les meilleurs résultats concordants à l'exception de l'information sur la date de naissance du candidat. Toutes les colonnes doivent donc être extraites, sauf la numéro 2. Ce n'est donc pas un seul indice de colonne que nous devons passer en dernier argument de la fonction Index, mais une matrice horizontale des numéros de colonne élus.

• A la suite de la syntaxe, créer la matrice horizontale suivante : {1.3.4.5},

Dans une matrice horizontale, ce sont en effet des points qui sont utilisés comme séparateurs.

• Fermer la parenthèse de la fonction Index,
• Enfin, valider la formule par la touche Entrée du clavier,

Comme vous pouvez l'apprécier, selon les trois conditions en vigueur, seuls les trois meilleurs scores sont extraits dans l'ordre décroissant.

Structurer la destination
Cependant, un défaut fort naturel saute aux yeux. Comme les dates de naissance ont été exclues, les scores ont été décalés en deuxième rangée dans la grille d'extraction. Or cette dernière est à l'origine formatée pour recevoir des dates. Nous devons réaliser quelques petits travaux d'ajustement.

• Sélectionner les titres de la cellule J3 à la cellule L3,
• Les copier, par exemple avec le raccourci clavier CTRL + C,
• Cliquer sur la cellule I3 pour désigner le nouveau point de départ de ces entêtes,
• Puis, les coller par exemple avec le raccourci clavier CTRL + V,

Ils s'enchaînent désormais correctement. De fait, le dernier entête en doublon (Essais) peut être supprimé.

• Sélectionner toutes les cellules de la nouvelle plage des scores, soit I4:I16,
• Dans la section Nombres du ruban Accueil, déployer la liste déroulante des formats,
• Dans les propositions, choisir le format Standard,

Cette fois, tout rentre dans l'ordre. Les scores sont de nouveau formatés comme des nombres et non plus comme des dates. Et désormais, si vous actionnez différents leviers dans les cellules N4, N7 ou encore N10, vous avez le plaisir de constater que le tri filtrant et restreint aux meilleurs, s'actualise en parfaite cohérence avec la demande.



Cependant, si vos conditions sont trop restrictives, il est fort probable que les données concordantes à extraire deviennent insuffisantes. Dans ce contexte, la fonction Index répond par des erreurs. Sur le cas illustré par la capture, seuls cinq candidats croisent favorablement les deux premiers critères. Pour palier le problème, il suffit d'embarquer la syntaxe générale dans la fonction SiErreur de gestion des anomalies, comme suit :

=SIERREUR(INDEX(TRIER(FILTRE(tab; (scores>=N4)*(essais<=N7)); {3.5}; {-1.1}); SEQUENCE(N10); {1.3.4.5});"")