Repérer les trois plus petits selon critère 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 :


Repérer les valeurs seuils sur critère

Avec cette nouvelle astuce Excel, nous allons voir comment repérer automatiquement en couleur les N moins bons ou les X meilleurs résultats. Certes Excel propose déjà des règles de mise en forme conditionnelle toutes faites à ce sujet, mais pas lorsqu'il s'agit d'additionner un critère ou des critères à ce repérage.



Sur l'exemple illustré par la capture, en fonction d'une équipe choisie par le biais d'une liste déroulante, ce sont les trois moins bons résultats qui sont identifiés en orange. Et nous allons le voir, c'est un raisonnement matriciel assimilé qui permet à une règle de mise en forme conditionnelle de réaliser cette prouesse.

Classeur source
Pour la démonstration de cette nouvelle astuce Excel, nous proposons d'appuyer l'étude sur un classeur offrant ces données à manipuler.

• Télécharger le classeur reperer-les-plus-petits.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 découvrons effectivement le tableau des résultats entre les colonnes B et D. En cellule F4, une liste déroulante permet de choisir parmi l'une des trois équipes représentées. Mais bien sûr à ce stade, ce choix n'induit encore aucun repérage de couleur dans le tableau. La colonne des équipes porte le nom Equipes tandis que celle des scores est intitulée Scores. Vous pouvez le constater en déployant la zone Nom en haut à gauche de la feuille Excel.

Les trois pires de l'équipe
Pour débuter, il est donc question de bâtir une règle de mise en forme conditionnelle capable d'identifier en couleur les trois moins bons résultats pour l'équipe choisie avec la liste déroulante. C'est un double critère qui doit être honoré. Il s'agit tout d'abord de vérifier la condition sur l'équipe et ensuite de vérifier que le résultat en regard est bien l'un des trois plus petits, toujours pour l'équipe en question. Et pour cela, Excel offre la fonction Petite.Valeur à exercer sur la plage des scores, restreinte par la condition, avec un rang de trois unités en second argument, pour les trois plus petits. Et comme vous le savez, une mise en forme conditionnelle s'exerce sur une plage de cellules explicitement sélectionnée préalablement.

• Sélectionner toutes les données du tableau sans les titres, soit la plage de cellules B4:D18,
• Dans la section Styles du ruban Accueil, cliquer sur le bouton Mise en forme conditionnelle,
• En bas des propositions, choisir l'option Nouvelle règle,
• Dans la boîte de dialogue qui suit, sélectionner le type : Utiliser une formule pour ...,
• Cliquer alors dans la zone de saisie du dessous pour l'activer,
• Puis, taper le symbole égal (=) pour initier la syntaxe de la règle de mise en forme,
• Inscrire la fonction pour recouper les conditions, suivie d'une parenthèse, soit : Et(,
• Cliquer sur la première équipe pour la désigner, ce qui donne : $C$4,

Nous le répétons à chaque occasion, l'analyse d'une mise en forme conditionnelle est chronologique. Toutes les cellules vont être passées en revue tour à tour. Pour émettre le critère sur l'équipe, nous débutons donc à partir de la première.

• Enfoncer deux fois la touche F4 du clavier, ce qui donne : $C4,

De cette manière, nous ne conservons qu'un seul dollar et il est placé devant l'indice de colonne. En effet, lorsqu'elle correspond aux critères, c'est toute la ligne qui doit surgir en couleur. Mais pour une même ligne, l'analyse de la condition doit se faire en colonne C que nous conservons figée. Nous libérons la ligne pour que le critère puisse être vérifié sur toutes les autres équipes dans la chronologie.

• Taper le symbole égal (=) pour annoncer le critère à vérifier,
• Puis, cliquer sur la cellule F4 de la liste déroulante, ce qui donne : $F$4,

Ainsi, nous établissons la correspondance à observer sur la colonne des équipes avec le choix de l'utilisateur émis par le biais de la liste déroulante. Et naturellement cette fois, nous conservons cette cellule figée. Elle ne doit pas se déplacer avec la chronologie de la règle.

• Taper un point-virgule (;) pour poursuivre l'énumération des critères,

La seconde condition doit être posée sur la plage des scores. Pour une équipe choisie, la valeur doit être l'une des trois plus petites.

• Cliquer sur la cellule D4 du premier score, ce qui donne :$D$4,
• Enfoncer deux fois la touche F4 du clavier pour la libérer seulement en ligne, soit : $D4,
• Puis, inscrire le critère d'inégalité suivant : <=,

Le score doit être inférieur ou égal aux trois plus petites valeurs pour l'équipe sélectionnée.

• Inscrire la fonction des petites valeurs suivie d'une parenthèse, soit : Petite.Valeur(,

Pour ressortir les trois plus petites valeurs à comparer avec le résultat en cours d'analyse par la règle, nous ne devons pas désigner la plage complète des scores. Cette étude doit se faire sur la plage conditionnelle des scores, soit la plage purgée des autres équipes.

• Inscrire la fonction conditionnelle suivie d'une parenthèse, soit : Si(,
• Désigner la plage des équipes par son nom, soit Equipes,
• Taper le symbole égal (=) pour annoncer la condition à honorer,
• Puis, cliquer sur la cellule F4 de la liste déroulante, ce qui donne : $F$4,
• Taper un point-virgule (;) pour passer dans la branche Alors de la fonction Si,
• Puis, désigner la plage des scores par son nom, soit : Scores,

Ainsi, nous allons bien chercher à identifier les trois plus petits résultats, en fonction du critère émis par la fonction Si sur la plage des scores, donc uniquement pour l'équipe choisie par l'utilisateur avec la liste déroulante en cellule F4.

• Fermer la parenthèse de la fonction Si,

Nous ne prenons pas le soin de renseigner la branche Sinon de la fonction Si. Ce cas est hors sujet dans l'analyse que nous avons engagée.

• Taper un point-virgule (;) pour passer dans l'argument du rang de la fonction Petite.Valeur,
• Saisir le chiffre 3 pour isoler les trois moins bons résultats de l'équipe,
• Fermer la parenthèse de la fonction Petite.Valeur,
• Puis, fermer la parenthèse de la fonction Et,

Lorsque ces deux conditions sont vérifiées ensemble, nous devons faire ressortir les lignes concernées dans un orange pâle.

• Pour cela, cliquer sur le bouton Format en bas de la boîte de dialogue,
• Dans la boîte de dialogue qui suit, activer l'onglet Police,
• Avec la seconde liste déroulante, choisir un orange pâle pour la couleur du texte,
• Valider cet attribut de format avec le bouton Ok,
• De retour sur la première boîte de dialogue, valider la règle avec le bouton Ok,

Comme vous pouvez l'apprécier, à chaque changement d'équipe par le biais de la liste déroulante, ce sont bien les trois moins bons scores qui surgissent automatiquement en couleur. La syntaxe de la règle de mise en forme conditionnelle que nous avons bâtie est la suivante :

=Et($C4=$F$4; $D4<=PETITE.VALEUR(SI(Equipes=$F$4; Scores); 3))

Si nous avions souhaité repérer les trois meilleurs résultats, le principe aurait été le même à quelques détails près. Il aurait d'abord suffi de remplacer la fonction Petite.Valeur par la fonction Grande.Valeur. Mais bien entendu, il aurait aussi fallu inverser le critère d'inégalité pour vérifier que le score en cours d'analyse faisait bien partie des trois meilleurs.

Somme des trois pires
Pour une synthèse aboutie, nous souhaitons maintenant nous occuper des résultats à livrer en cellules F7 et F10. Ils concernent respectivement les sommes des trois pires et trois meilleurs résultats pour l'équipe choisie. C'est un raisonnement doublement matriciel que nous devons engager. Avec la fonction SommeProd, nous pouvons croiser plusieurs plages de cellules. La première concerne celle des équipes recoupant le critère de la liste déroulante. La deuxième concerne la vérification du score parmi les trois plus petits sur la plage des scores dans la mesure où l'équipe est bien celle de la liste déroulante. Jusque-là, le raisonnement est similaire à celui de la mise en forme conditionnelle. La fonction SommeProd va répondre par des matrices identifiant les positions concordantes à l'aide de valeurs booléennes sous forme de chiffres 1 et 0. En multipliant une fois encore ces résultats par une troisième matrice, celle des valeurs de la plage des scores, nous obtiendrons à l'issue l'addition uniquement des trois plus petites valeurs pour l'équipe désignée.

• Cliquer sur la cellule F7 pour la sélectionner,
• Taper le symbole égal (=) pour initier la syntaxe de la formule matricielle,
• Inscrire le nom de la fonction matricielle suivi d'une parenthèse, soit : SommeProd(,
• A gauche de la barre de formule, cliquer sur le bouton Assistant fonction (fx),

L'assistant pour la fonction SommeProd apparaît. La zone Matrice1 est active par défaut.

• Dans cette zone, ouvrir une parenthèse pour accueillir la première matrice conditionnelle,
• Désigner la plage des Equipes par son nom, soit Equipes,

En effet, nous ne sommes plus dans un raisonnement chronologique d'une mise en forme conditionnelle. Nous sommes entrés dans une construction matricielle. C'est la raison pour laquelle nous raisonnons sur l'intégralité des plages. Les formules matricielles ont la faculté de les analyser entièrement dans un seul et même calcul non répliqué.

• Taper le symbole égal (=) pour annoncer le critère à honorer,
• Puis, cliquer sur la cellule F4 de l'équipe choisie,

Cette première matrice conditionnelle doit donc bien établir la correspondance entre les équipes listées et celle choisie par l'utilisateur par le biais de la liste déroulante.

• Fermer alors la parenthèse de cette première matrice conditionnelle,

Comme vous pouvez le constater, une matrice de valeurs booléennes rend aussitôt compte de l'interprétation d'Excel sur ce recoupement. Il s'agit d'un enchaînement de Vrai et de Faux pour repérer les positions respectives des équipes concordantes et non concordantes.



Mais pour que la multiplication avec les autres matrices conditionnelles puisse être réalisée, nous devons convertir ces booléens en chiffres. Pour cela, rien de plus simple, il suffit de multiplier cette matrice par le chiffre 1.

• Forcer la conversion en chiffres par la multiplication, soit : *1,

Comme vous pouvez le voir dans la matrice résultante, tous les booléens sont effectivement convertis dans leurs équivalents numériques.

• Cliquer dans la zone Matrice2 pour l'activer,

Comme pour la mise en forme conditionnelle, il est maintenant question d'identifier les trois moins bons résultats pour l'équipe choisie. Les fonctions sont les mêmes mais le raisonnement se fait sur les matrices complètes, comme nous l'avons expliqué.

• Ouvrir une parenthèse pour accueillir la seconde matrice conditionnelle,
• Désigner la plage des scores par son nom, soit : Scores,
• Taper le critère d'inégalité suivant : <=,
• Inscrire la fonction des petites valeurs suivie d'une parenthèse, soit : Petite.Valeur(,

Comme précédemment, ces valeurs minimales doivent être observées en fonction de la condition émise sur l'équipe.

• Inscrire la fonction conditionnelle suivie d'une parenthèse, soit : Si(,
• Désigner la plage des équipes par son nom, soit : Equipes,
• Taper le symbole égal (=) pour annoncer la condition à honorer,
• Cliquer de nouveau sur la cellule F4 pour désigner l'équipe choisie par l'utilisateur,
• Taper un point-virgule (;) pour passer dans la branche Alors de la fonction Si,
• Désigner la plage des résultats par son nom, soit : Scores,
• Puis fermer la parenthèse de la fonction Si,

Nous venons de désigner la plage restreinte des scores en fonction de l'équipe choisie pour en ressortir les trois moins bons résultats. Il nous reste donc à définir le rang de la fonction Petite.Valeur.

• Taper un point-virgule (;) pour passer dans cet argument,
• Inscrire le chiffre 3 pour trouver les trois moins bons,
• Fermer la parenthèse de la fonction Petite.Valeur,
• Puis, fermer la parenthèse de la matrice conditionnelle,

Les indicateurs booléens de position apparaissent aussitôt en regard de la zone Matrice2.

• Cliquer après la parenthèse de la matrice conditionnelle pour y placer le point d'insertion,
• Forcer la conversion de ces valeurs booléennes en chiffres, soit : *1,

Les multiplications par zéro éliminent automatiquement les résultats de l'équipe ne faisant pas partie des trois moins bons scores. En revanche, la multiplication du chiffre 1 par le chiffre 1 conserve les positions des données à exploiter. En multipliant ces chiffres par la matrice des scores, étant donné que la vocation de la fonction SommeProd est d'additionner toutes ces valeurs multipliées à l'issue, nous obtiendrons bien la somme des trois moins bonnes valeurs.

• Cliquer dans la zone Matrice3 pour l'activer,
• Désigner la plage des résultats par son nom, soit : Scores,

Aussitôt, la synthèse est livrée en bas à droite de l'assistant fonction SommeProd. Et il va être très simple de vérifier la véracité de cette donnée.

• Valider la formule par le raccourci clavier CTRL + MAJ + Entrée,

C'est une première ! En effet, la fonction Excel SommeProd raisonne naturellement de façon matricielle. Mais ce n'est pas le cas naturellement de la fonction Petite.Valeur. Pour lui imposer de raisonner sur l'intégralité des plages qui lui sont passées, nous la forçons ainsi à l'intérieur de la fonction SommeProd.

Pour l'équipe 1 par exemple, nous obtenons un total de 470. Si vous sélectionnez ensemble les scores des trois cellules repérées en orange et que vous consultez l'information sur la somme des valeurs sélectionnées dans la barre d'état en bas de la fenêtre Excel, vous constatez que le résultat matriciel est parfaitement cohérent.



Bien entendu, si vous changez d'équipe avec la liste déroulante, la synthèse s'ajuste automatiquement en même temps que les emplacements des couleurs conditionnelles de repérage. La syntaxe complète de la formule matricielle que nous avons construite est la suivante :

{=SOMMEPROD((Equipes=F4)*1; (Scores<=PETITE.VALEUR(SI(Equipes=F4; Scores); 3))*1; Scores)}

Somme des trois meilleurs
Maintenant, il est question de livrer la somme des trois meilleurs scores en fonction de l'équipe choisie. Le principe est identique. Il suffit de remplacer la fonction Petite.Valeur par la fonction Grande.Valeur et bien sûr d'inverser le critère d'inégalité.

• Cliquer sur la cellule F7 pour la sélectionner,
• Dans sa barre de formule, sélectionner et copier (CTRL + C) la syntaxe du calcul,
• Puis, sortir de la barre de formule avec la touche Echap pour ne pas endommager le calcul,
• Sélectionner alors la cellule F10,
• Coller la syntaxe précédemment copiée dans sa barre de formule,
• Remplacer la fonction Petite.Valeur par la fonction Grande.Valeur,
• Puis, inverser le critère d'inégalité : >=,
• Dès lors, valider la formule matricielle par le raccourci clavier CTRL + MAJ + Entrée,

Là aussi, le résultat de synthèse est simple à corroborer en sélectionnant les trois meilleurs résultats : {=SOMMEPROD((Equipes=F4)*1; (Scores>=GRANDE.VALEUR(SI(Equipes=F4; Scores); 3))*1; Scores)}