Trouver l'une des plus grandes valeurs selon critères

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 l'une des plus grandes valeurs

Cette nouvelle astuce Excel montre comment renvoyer les valeurs parmi les plus élevées dans un ensemble de données selon des critères.



La solution finalisée est illustrée par la capture. Les critères doivent être recoupés par le choix d'un groupe catégorie à l'aide du première liste déroulante et d'un rang souhaité à l'aide d'une seconde liste déroulante. La valeur correspondante doit alors être extraite et repérée en couleur.

Classeur source
Pour le bon déroulement de cette astuce, nous proposons de récupérer un classeur avec ce type de données.

• Télécharger le classeur plus-grande-valeur-groupe.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é,

Les groupes de catégories sont énoncés en colonne C de ce tableau. Les résultats obtenus par groupe sont énumérés en colonne D. Il peut s'agir de quantités vendues. Les deux critères sur le choix du groupe et du rang sont à émettre en cellules respectives F6 et F9. Sur ces conditions recoupées, nous devons extraire la énième plus grande valeur du groupe en cellule F12.

Grande valeur selon critères
Dans un raisonnement matriciel, nous pouvons exploiter les fonctions Grande.Valeur et Si. Dans la matrice des groupes, nous devons trouver la correspondance avec le code choisi par le biais de la première liste déroulante. Dès lors, nous pourrons restituer la matrice filtrée des chiffres sur la plage D6:D15, en concordance avec ce groupe. Mais nous ne souhaitons extraire que la valeur de ce groupe pour laquelle le rang est en accord avec celui mentionné par le biais de la seconde liste déroulante. La fonction Excel Si doit donc être imbriquée dans la fonction Grande.Valeur.

• Sélectionner la case du résultat à trouver en cliquant sur sa cellule F12,
• Taper le symbole égal (=) pour initier la syntaxe de la formule matricielle,
• Inscrire la fonction pour repérer les seuils, suivie d'une parenthèse, soit : Grande.Valeur(,
• Inscrire la fonction conditionnelle suivie d'une parenthèse, soit : Si(,
• Désigner les groupes en sélectionnant la plage de cellules C6:C15,
• Taper le symbole égal (=) pour annoncer le critère à suivre,
• Désigner le groupe choisi par l'utilisateur en cliquant sur sa cellule F6,
• Taper un point-virgule (;) pour passer dans la branche Alors de la fonction Si,
• Désigner toutes les valeurs en sélectionnant la plage de cellules D6:D15,

Ce critère et cette conséquence sont encapsulés dans un raisonnement matriciel. Selon la correspondance établie sur la plage des groupes, seule subsistera la valeur correspondant au rang qui reste encore à définir dans la syntaxe.

• Fermer la parenthèse de la fonction Si,
• Taper un point-virgule pour passer dans l'argument du rang de la fonction Grande.Valeur,
• Désigner le rang choisi par l'utilisateur en cliquant sur la cellule F9,
• Fermer alors la parenthèse de la fonction Grande.Valeur,
• Enfin, valider nécessairement la formule matricielle par le raccourci CTRL + MAJ + Entrée,

La valeur correspondant au groupe défini pour le rang choisi est aussitôt extraite.



Et bien entendu, si vous changez de groupe et/ou de rang, l'extraction s'actualise aussitôt. La formule matricielle que nous avons construite est la suivante : =GRANDE.VALEUR(SI(C6:C15=F6; D6:D15); F9).

En revanche, si vous sélectionnez par exemple le rang 4 pour le groupe G. C, une erreur est retournée : #Nombre!. En effet, le groupe G. C ne compte que trois valeurs contrairement au groupe G. A. Pour gérer cette exception, il suffit simplement d'intégrer la syntaxe matricielle dans la fonction SiErreur de gestion des anomalies :

{=SIERREUR(GRANDE.VALEUR(SI(C6:C15=F6; D6:D15); F9); "")}

Bien sûr, il ne faut pas oublier de valider la formule matricielle par le raccourci clavier CTRL + MAJ + Entrée.

Afficher en couleur la valeur extraite
Il est assez simple de valider la cohérence du résultat obtenu par le calcul matriciel. Mais pour une application aboutie, il est encore plus pertinent de créer deux règles de mise en forme conditionnelle. Le rôle de la première doit consister à faire ressortir dans une couleur différente, les lignes du groupe choisi. Le rôle de la seconde doit permettre de faire surgir la valeur extraite par la formule matricielle. Ainsi, il sera évident de constater son rang dans le groupe et de comparer cette livrée avec les choix émis par l'utilisateur. L'ordre des règles est important pour que la seconde puisse prendre le pas sur la première dans la hiérarchie de la construction.

• Sélectionner toutes les données du tableau, soit la plage de cellules C6:D15,
• 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, bâtir la syntaxe suivante : =$C6=$F$6,

Il faut bien prendre soin de libérer la ligne de la cellule C6 et de conserver figée sa colonne. L'analyse chronologique de la correspondance s'effectue effectivement dans cette unique colonne mais sur toutes les lignes, pour trouver l'égalité avec le groupe défini par l'utilisateur.

Lorsque ce critère est validé, chaque ligne du groupe concordant doit changer dynamiquement de couleur.

• 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 vert assez vif pour la couleur du texte,
• Valider ces attributs de format par le bouton Ok,
• De retour sur la première boîte de dialogue, valider la création de la règle par le bouton Ok,

Comme vous pouvez le voir, toutes les lignes du groupe sélectionné sont ainsi mises en valeur. Et bien entendu, si vous changez de groupe avec la première liste déroulante, les couleurs se déplacent. Au milieu de ce groupe, nous devons faire ressortir la ligne de la valeur extraite par la formule matricielle.

• Sélectionner de nouveau toutes les lignes du tableau, soit la plage de cellules C6:D15,
• 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,
• Y construire la règle suivante : =ET($C6=$F$6; $D6=$F$12),

Grâce à la fonction Et, nous cherchons dans le même temps à établir la correspondance sur le groupe ($C6=$F$6) et sur la valeur extraite ($D6=$F$12). Pour les mêmes raisons que précédemment, les cellules C6 et D6 sont libérées en ligne et conservées figées en colonne.

• Cliquer sur le bouton Format en bas de la boîte de dialogue,
• Dans la boîte de dialogue qui suit, choisir un orange assez vif pour la couleur du texte,
• Valider ces attributs de format par le bouton Ok,
• De retour sur la première boîte de dialogue, valider la création de la règle par le bouton Ok,

Les couleurs et le calcul se mettent instantanément à jour aux changements de groupe et de rang. Il devient désormais évident de valider la cohérence du calcul quant à la valeur du rang dans le groupe mentionné.