Coordonnées des cellules Max et Min sous contrainte

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


Sujets que vous pourriez aussi aimer :


Coordonnées Max et Min selon critère

Au cours de l'astuce Excel précédente, nous avons découvert comment calculer la ligne et la colonne des valeurs maximales et minimales dans un tableau de données. Et pour cela, nous avions engagé un calcul matriciel très simple exploitant la fonction SommeProd. Ici, nous souhaitons récidiver mais en ajoutant une contrainte. Il ne s'agit plus de considérer ces valeurs seuils dans l'absolu mais dans un contexte variant en fonction d'un choix émis par l'utilisateur.



Dans l'exemple finalisé illustré par la capture, l'utilisateur définit une période à analyser en changeant de mois à l'aide d'une liste déroulante placée sur la droite du tableau. Aussitôt, le max et le min sont repérés dans la colonne correspondante. Et dans le même temps, des calculs placés sous la liste déroulante fournissent les coordonnées de ces cellules contextuelles.

Classeur source
Pour la mise en place de cette nouvelle astuce, nous suggérons de récupérer les travaux réalisés au cours du précédent volet. Ainsi, nous irons droit au but.

• Télécharger le classeur ligne-colonne-seuil-critere.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 retrouvons effectivement le travail accompli à l'occasion de la précédente étape. Des jeux de couleurs dynamiques repèrent les valeurs maximales et minimales par rapport à toutes les données du tableau. Leurs coordonnées sont respectivement extraites en cellules K6 et K7 puis en cellules K10 et K11. Les quatre formules matricielles ont toutes été bâties sur le même moule en alternant l'emploi des fonctions Max et Min, mais aussi des fonctions Ligne et Colonne:

=SOMMEPROD((Valeurs = MAX(Valeurs))*LIGNE(Valeurs))

La plage Valeurs désigne l'intégralité des données numériques du tableau. Dans ce raisonnement matriciel, et dans le cas de cette formule, elle est recoupée avec la valeur la plus grande. Il en résulte forcément une unique concordance repérée par le booléen Vrai dans la matrice retournée, tandis que toutes les autres données sont marquées par le booléen Faux. En multipliant cette matrice résultante par celle qui donne les indices de ligne de chaque valeur, seul le booléen Vrai réagit pour conduire à l'indice de ligne de la valeur maximale dans le tableau de données.

C'est cette syntaxe que nous proposons d'adapter à la nouvelle problématique. Vous notez en effet la présence d'une liste déroulante en cellule K3. Elle permet de désigner un mois, soit une colonne du tableau. Et c'est dans la colonne ainsi désignée dynamiquement que nous devons désormais isoler les coordonnées des valeurs seuils. Le précédent raisonnement matriciel doit donc intégrer ce critère.

Nommer automatiquement les colonnes
C'est une succession d'astuces qui va nous permettre d'aboutir la solution. La première d'entre elles consiste à reconnaître chaque colonne en fonction de son entête correspondant aux choix des mois potentiellement réalisés par l'utilisateur. Et tant qu'à faire, nous allons enclencher une technique automatisée pour les nommer ainsi.

• Sélectionner tous les nombres du tableau avec les titres de colonne, soit la plage C3:H11,
• En haut de la fenêtre Excel, cliquer sur l'onglet Formules pour activer son ruban,
• Dans la section Noms définis du ruban, cliquer sur le bouton Depuis sélection,
• Dans la boîte de dialogue qui suit, cocher la case Ligne du haut et valider par Ok,

De cette manière, chaque colonne numérique porte désormais le nom de son entête. Et vous pouvez facilement le constater en déployant la zone Nom en haut à gauche de la feuille Excel.

Coordonnées des seuils sous contrainte
Le choix d'un mois dans la liste déroulante désigne désormais l'une des colonnes reconnues par ces mêmes noms. Mais à ce stade et comme vous le savez, cette information choisie est considérée comme un simple texte par Excel. Pour qu'elle puisse désigner la plage portant le même intitulé, elle doit être interprétée. Et c'est la vocation de la fonction Excel Indirect que nous avons largement étudiée à l'occasion de précédentes astuces.

• Cliquer sur la cellule K6 pour la sélectionner,

Il s'agit du calcul permettant de déterminer la position en ligne de la valeur maximale dans l'absolu.

• Dans la barre de formule, remplacer toutes les occurrences de la plage Valeurs, par la plage interprétée par la fonction Indirect : Indirect(K3), comme suit :

=SOMMEPROD((INDIRECT(K3) = MAX(INDIRECT(K3)))*LIGNE(INDIRECT(K3)))

• Puis, valider la formule par la touche Entrée du clavier,

Désormais la condition à vérifier sur la valeur Max est émise sur la colonne choisie par l'utilisateur par le biais de la liste déroulante. Et donc grâce à la fonction Ligne agissant sur cette même plage dynamique, nous restituons l'indice de ligne de cette donnée la plus grande. Et comme vous pouvez le voir, pour le mois de Janvier, la formule répond par le chiffre 9. Le chiffre d'affaires le plus grand pour cette colonne est effectivement situé en ligne 9.

Bien entendu, si vous changez de mois avec la liste déroulante, le calcul de l'indice de ligne s'adapte automatiquement pour repérer la position de la valeur maximale dans sa colonne.

Maintenant pour calculer la colonne de la valeur maximale ainsi que la ligne et la colonne de la valeur minimale pour le mois choisi, il convient d'adapter les formules en cellules K7, K10 et K11 respectivement comme suit :

=SOMMEPROD((INDIRECT(K3) = MAX(INDIRECT(K3)))*COLONNE(INDIRECT(K3)))
=SOMMEPROD((INDIRECT(K3) = MIN(INDIRECT(K3)))*LIGNE(INDIRECT(K3)))
=SOMMEPROD((INDIRECT(K3) = MIN(INDIRECT(K3)))*COLONNE(INDIRECT(K3)))

Comme vous pouvez le voir, tous les calculs de positions s'ajustent désormais automatiquement au changement de mois par le biais de la liste déroulante.

Enfin et bien sûr, il convient d'adapter les calculs du maximum et du minimum relatifs désormais en cellules respectives K5 et K9 :

=MAX(INDIRECT(K3)) et =MIN(INDIRECT(K3))

Repérer les Max et Min en couleur
Pour parfaire la solution, nous proposons de faire ressortir de façon évidente ces valeurs seuils dans des couleurs dynamiques. Pour cela, rien de plus simple, deux règles de mise en forme conditionnelle sont nécessaires. Toutes deux doivent vérifier un double critère sur la valeur seuil et sur la colonne choisie.

Toutes les données numériques du tableau sont potentiellement concernées. Donc, elles doivent premièrement être sélectionnées.

• Sélectionner la plage de cellules C4:H11,
• En haut de la fenêtre Excel, cliquer sur l'onglet Accueil pour revenir sur son ruban,
• Dans la section Styles du ruban, 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,
• Taper le symbole égal (=) pour initier la syntaxe de la règle,
• Inscrire la fonction pour recouper des critères, suivie d'une parenthèse, soit : Et(,
• Sélectionner la première donnée numérique à tester en cliquant sur la cellule C4,
• Puis, enfoncer trois fois de suite la touche F4 du clavier pour la libérer complètement,

En effet, dans cette analyse chronologique, toutes les valeurs doivent être comparées avec le max, en partant de la première. C'est la raison pour laquelle nous supprimons la présence des dollars.

• Taper le symbole égal (=) pour annoncer le premier critère à honorer,
• Puis, cliquer sur la cellule K5 de la valeur Max calculée, ce qui donne : $K$5,

Cette fois, nous la conservons figée. Toutes les valeurs doivent lui être confrontées. Donc, elle ne doit pas se déplacer à la faveur de l'analyse chronologique de la règle.

• Taper un point-virgule (;) pour poursuivre l'énumération des critères,
• Cliquer sur la cellule C3 pour le premier mois du tableau,
• Puis, enfoncer une seule fois la touche F4 du clavier, ce qui donne : C$3,

En effet, pour chaque valeur numérique, le titre de la colonne doit être comparé avec le mois choisi par l'utilisateur avec la liste déroulante. Cette information est immuablement placée en ligne 3 que nous conservons figée. Mais pour que les autres titres puissent être comparés au gré de l'analyse, nous la laissons se déplacer en colonne.

• Taper le symbole égal (=) pour annoncer la seconde condition à vérifer,
• Sélectionner alors le mois choisi par l'utilisateur en cliquant sur sa cellule K3, soit : $K$3,
• Dès lors, fermer la parenthèse de la fonction Et pour terminer l'énumération,

Lorsque ces deux conditions sont réunies, elles indiquent que la valeur maximale pour le mois choisi a été isolée. Il convient donc de faire ressortir sa cellule en couleur.

• En bas de la boîte de dialogue, cliquer sur le bouton Format,
• Dans la boîte de dialogue qui suit, activer l'onglet Remplissage,
• Dans la palette de couleurs, choisir un vert clair pour une symbolique positive,
• Activer alors l'onglet Police de la boîte de dialogue,
• Avec la seconde liste déroulante, choisir un gris assez foncé pour la couleur du texte,
• Valider alors ces attributs de format par le bouton Ok,

Nous sommes de retour sur la première boîte de dialogue qui indique l'apparence que doit adopter la cellule cible, concernée par les deux conditions recoupées.

• Cliquer de nouveau sur le bouton Ok pour revenir sur la feuille Excel,

Comme vous pouvez l'apprécier, la donnée la plus grande pour le mois sélectionné ressort automatiquement. Comme la plage de valeurs est encore sélectionnée, nous pouvons en profiter pour bâtir la seconde règle. Elle consiste cette fois à isoler la valeur minimale pour le mois choisi. Le double critère de la règle est quasi-identique. Seule la cellule de correspondance de la première condition change : =ET(C4=$K$9; C$3=$K$3). Il convient cette fois de lui associer un remplissage rouge clair avec un texte gris foncé.

Désormais, à chaque changement de mois avec la liste déroulante, en même temps que les coordonnées des valeurs seuils sont calculées dans la colonne correspondante, ces dernières ressortent également en couleur dans le tableau.



Et bien entendu, si vous modifiez des nombres dans le tableau, les calculs et les couleurs s'adaptent automatiquement.