Extraire les informations liées aux valeurs seuils

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


Sujets que vous pourriez aussi aimer :


Informations liées à la valeur maximale

Avec cette nouvelle astuce Excel, nous allons apprendre à extraire les informations liées aux valeurs seuils.



Dans l'exemple illustré par la capture, nous travaillons sur un tableau des chiffres réalisés au coup par coup par des commerciaux. Sur la droite, nous isolons premièrement le nom du commercial ayant réalisé le chiffre maximum avec sa date. Juste en-dessous et de la même façon, nous réalisons l'extraction du nom du commercial ayant réalisé le chiffre minimum avec la date de l'opération. Et bien entendu, si les chiffres du tableau évoluent, les résultats des données associées s'ajustent en conséquence. Dans le même temps, des couleurs dynamiques repèrent ces lignes dans le tableau.

Classeur source
Pour la démonstration de cette nouvelle astuce, nous proposons d'appuyer nos travaux sur ce tableau des chiffres.

• Télécharger le classeur informations-liees-valeur-max.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 informations associées à la valeur maximale doivent être extraites respectivement en cellules G5 et G6. Les informations associées à la valeur minimale doivent être extraites en cellules respectives G10 et G11.



Si vous déployez la zone Nom en haut à gauche de la feuille Excel, vous notez que chacune des trois colonnes du tableau porte un nom. Par exemple, la colonne des chiffres réalisés se nomme Chiffres. Ces noms seront utiles pour simplifier la syntaxe de la formule d'extraction.

Données liées à la valeur Max
Tout l'enjeu désormais consiste donc à pouvoir rapatrier les informations liées au chiffre le plus grand, sans extraire la valeur maximale elle-même. Et pourtant, c'est bien une unique formule qui doit relever ce défi. La fonction Excel Index est nécessaire pour réaliser l'extraction. L'astuce consiste à lui imbriquer la fonction Equiv dans l'argument de l'indice de ligne. C'est elle qui est en mesure de trouver la position en ligne du chiffre le plus grand.

• Cliquer sur la cellule G5 pour la sélectionner,
• Taper le symbole égal (=) pour initier la syntaxe de la formule d'extraction,
• Inscrire la fonction d'extraction suivie d'une parenthèse, soit : Index(,
• Désigner la plage d'extraction par son nom, soit : Vendeurs,
• Taper un point-virgule (;) pour passer dans l'argument du numéro de ligne,
• Inscrire la fonction de recherche de position, suivie d'une parenthèse, soit : Equiv(,

Nous cherchons à trouver la position en ligne du chiffre d'affaires le plus grand dans la dernière colonne du tableau.

• Inscrire la fonction du maximum suivie d'une parenthèse, soit : Max(,
• Désigner la plage de cellules des montants par son nom, soit : Chiffres,
• Fermer la parenthèse de la fonction Max,
• Taper un point-virgule (;) pour passer dans l'argument de la colonne de recherche,
• Désigner de nouveau la plage des montants par son nom, soit : Chiffres,

En effet, nous recherchons la plus grande valeur dans la plage des chiffres d'affaires.

• Taper un point-virgule suivi du chiffre zéro, soit : ;0, pour réaliser une recherche exacte,
• Fermer la parenthèse de la fonction Equiv,
• Puis, fermer la parenthèse de la fonction Index,
• Dès lors, valider la formule d'extraction par la touche Entrée du clavier,

Le résultat tombe et indique que Céhef est le vendeur ayant réalisé le meilleur chiffre sur une journée. Vous pouvez facilement confirmer la validité de ce résultat en consultant les données du tableau. Il nous reste à extraire la date de cette meilleure journée. Pour cela, il s'agit de réaliser l'extraction non plus sur la plage des vendeurs, mais sur la plage des dates. En cellule G6, il convient donc d'adapter la précédente formule comme suit :

=INDEX(Dates; EQUIV(MAX(Chiffres); Chiffres; 0))

Remarque : Si l'extraction avait été proposée dans l'ordre des colonnes du tableau, Date puis Vendeur, une unique formule à répliquer aurait été possible :

=INDEX($B$4:$C$51; EQUIV(MAX(Chiffres); Chiffres; 0); LIGNE(A1))

La plage d'extraction correspond aux deux premières colonnes. La bonne rangée est alors spécifiée dynamiquement en dernier argument de la fonction Index. La fonction Ligne, initialisée sur une cellule de la première ligne, indique d'extraire d'abord en première colonne, celle des dates. En répliquant le calcul sur la ligne du dessous, la fonction Ligne demande de réaliser l'extraction à partir de la seconde colonne, celle des vendeurs.

Données liées à la valeur Min
Pour rapatrier les informations liées au chiffre d'affaires le plus petit, le principe est tout à fait identique. Il suffit simplement de rechercher la ligne de la valeur la plus petite. Et pour ce faire, dans la syntaxe précédente, nous devons remplacer la fonction Max par la fonction Min.

• En cellules G10 et G11, adapter les précédentes syntaxes comme suit :

=INDEX(Vendeurs; EQUIV(MIN(Chiffres); Chiffres; 0))
=INDEX(Dates; EQUIV(MIN(Chiffres); Chiffres; 0))



Une fois encore, les résultats sont parfaitement cohérents et dynamiques. Ce dernier point, nous le constaterons avec plus de clarté lorsque nous aurons ajouté la petite touche finale à cette solution.

Couleurs dynamiques sur les valeurs seuils
Pour que les valeurs seuils surgissent dynamiquement dans le tableau, nous souhaitons leur attribuer des couleurs automatiques. Les deux règles de mise en forme conditionnelle à construire doivent se contenter de trouver premièrement le plus grand chiffre et secondement, le plus petit de ces chiffres.

• Sélectionner toutes les données du tableau, soit la plage de cellules B4:D51,
• Dans la section Styles du ruban Accueil, cliquer sur le bouton Mise en forme conditionnelle,
• En bas de la liste des propositions, choisir l'option Nouvelle règle,
• Dans la boîte de dialogue qui suit, sélectionner le type : Utiliser une formule pour ...,
• Puis, cliquer dans la zone de saisie du dessous pour l'activer,
• Dès lors, taper le symbole (=) pour initier la syntaxe de la première règle de mise en forme,
• Sélectionner le premier chiffre en cliquant sur sa cellule D4, ce qui donne : $D$4,
• Enfoncer deux fois la touche F4 du clavier pour la libérer en ligne, ce qui donne : $D4,

Nous le savons pertinemment, l'analyse d'une mise en forme conditionnelle est chronologique. L'étude porte sur la plage des chiffres d'affaires. C'est pourquoi nous conservons sa colonne figée. Mais pour que tous ces chiffres puissent être étudiés tour à tour, nous devons libérer la ligne de la première cellule impliquée.

• Taper le symbole égal (=) pour annoncer le critère à honorer,
• Inscrire la fonction du maximum suivie d'une parenthèse, soit : Max(,
• Désigner la plage des montants par son nom, soit : Chiffres,
• Fermer alors la parenthèse de la fonction Max,

Lorsque ce critère est honoré, soit lorsque le plus grand des chiffres d'affaires est trouvé, nous devons faire ressortir la ligne concernée en 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 de police,
• Valider cet attribut de format par le bouton Ok,
• De retour sur la première boîte, valider la création de la règle de mise enforme par Ok,

Nous sommes de retour sur la feuille. La ligne du meilleur score est effectivement mise en valeur dans les attributs définis. De plus, les données du tableau sont toujours sélectionnées. Nous pouvons donc en profiter, selon le même protocole, pour bâtir la seconde règle de mise en forme conditionnelle. Elle doit faire ressortir en orange la ligne du plus petit chiffre d'affaires réalisé, selon la syntaxe suivante : =$D4=Min(Chiffres).



Les lignes des valeurs seuils sont effectivement repérées dynamiquement dans des jeux de couleurs qui leur sont propres. Et si vous modifiez quelques chiffres de manière à changer les bornes inférieures et supérieures, en même temps que les données extraites s'actualisent, les mises en valeur se déplacent pour recouper les résultats des calculs.