Positions en ligne et colonne des valeurs Max et Min

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


Sujets que vous pourriez aussi aimer :


Positions des valeurs seuils

Cette nouvelle astuce Excel va nous apprendre à identifier rapidement les positions en ligne et en colonne des valeurs seuils dans un tableau. Et nous allons le constater, c'est un calcul matriciel d'une simplicité déconcertante qui permet d'identifier facilement ces positions.

Classeur source
Pour la mise en place de cette astuce, nous suggérons d'appuyer l'étude sur un classeur existant et offrant des données à analyser.

• Télécharger le classeur ligne-colonne-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é,

Nous découvrons un tableau relatant les ventes consolidées de quelques articles sur les six premiers mois de l'année.



Vous notez que deux petites règles de mise en forme conditionnelle font ressortir dynamiquement et explicitement le résultat le meilleur en vert ainsi que le résultat le moins bon en rouge clair. De plus, sur la droite du tableau, deux calculs exploitant respectivement les fonctions Excel Max et Min viennent isoler ces données seuils. Dans les cases situées juste en-dessous de ces résultats, nous devons maintenant trouver quelles sont les positions respectives en ligne et en colonne de ces valeurs. Enfin, si vous déployez la zone Nom en haut à gauche de la feuille, vous remarquez qu'un nom de plage (Valeurs) désigne ces données. Nous exploiterons ce nom pour simplifier la construction de la formule matricielle.

Ligne de la valeur Max
Pour calculer les positions en ligne et colonne des valeurs Max et Min, c'est la fonction SommeProd qui apporte facilement la solution. Mais attention, il ne s'agit pas de réaliser la somme des multiplications respectives comme il est de coutume dans son usage classique. Son emploi est ici dérivé et ce n'est pas une nouveauté comme nous avons pu le constater à l'occasion d'astuces précédentes. Il est question de lui passer une matrice conditionnelle dont la vocation est de déceler la présence de la valeur seuil sur la plage de données. En recoupant cette matrice conditionnelle avec celle renseignant sur les indices de lignes ou de colonnes des cellules impliquées, nous isolerons les positions.

• Cliquer sur la cellule K6 de la ligne à trouver pour la valeur Maximale,
• Taper le symbole égal (=) pour initier la syntaxe de la formule,
• Inscrire le nom de la fonction matricielle, suivi d'une parenthèse, soit : SommeProd(,
• Juste à gauche de la barre de formule, cliquer sur le bouton fx,

Ce cette manière, nous activons l'assistant pour la fonction SommeProd. Il va nous aider à mieux comprendre le mécanisme du raisonnement matriciel à engager.

• Dans la zone Matrice1, ouvrir une parenthèse pour accueillir la matrice conditionnelle,
• Désigner l'ensemble des données numériques par le nom de plage, soit : Valeurs,
• Taper le symbole égal (=) pour annoncer la condition à observer sur cette matrice,
• Inscrire la fonction pour le maximum suivie d'une parenthèse, soit : Max(,
• Désigner de nouveau la plage des données par son nom, soit : Valeurs,
• Fermer la parenthèse de la fonction Max,
• Puis, fermer la parenthèse de la matrice conditionnelle,

Comme vous pouvez le voir, des indicateurs booléens temporaires apparaissent en regard de la zone Matrice1, sur la droite plus précisément.



Ils renseignent sur la position répondant favorablement au critère et sur toutes les positions non concordantes. En effet, dans ce raisonnement matriciel, nous comparons ligne à ligne chaque valeur de la matrice Valeurs avec la plus grande d'entre elles (Max(Valeurs)). C'est la raison pour laquelle la deuxième donnée du tableau (10 102) est repérée par le booléen Vrai. Ce booléen est l'équivalent du chiffre 1 tandis que le booléen Faux est l'équivalent du chiffre 0. Donc, en multipliant cette matrice par celle renseignant sur les indices de lignes de chaque cellule, puisque la multiplication par zéro conduit à zéro, nous ne conserverons que l'indice de ligne de la valeur la plus grande.

• Taper le symbole de l'astérisque (*) pour annoncer la matrice à recouper,
• Inscrire la fonction pour les indices de ligne suivie d'une parenthèse, soit : Ligne(,
• Puis, désigner de nouveau la plage des valeurs par son nom, soit : Valeurs,
• Dès lors, fermer la parenthèse de la fonction Ligne,

Un seul indice de ligne n'est pas multiplié par Zéro. De fait, par le biais de ce recoupement, nous obtenons une valeur positive isolée dans la matrice fournie en indication par la fonction SommeProd.



Comme la vocation de cette dernière est de sommer toutes ces valeurs multipliées à l'issue, il en résultera uniquement l'indice de ligne (4) de la valeur Max dans le tableau de données.

• Cliquer sur le bouton Ok de l'assistant fonction SommeProd pour valider la formule,

Et comme vous pouvez le voir, c'est bien la ligne de la valeur la plus grande, repérée par la mise en forme conditionnelle, qui est retournée. Et bien entendu, si vous trafiquez les ventes pour modifier le maximum, en même temps que la couleur de la mise en forme conditionnelle se déplace pour le repérer, le calcul de la nouvelle position s'ajuste automatiquement en cellule K6.

Colonne de la valeur Max
Il ne nous reste plus qu'à décliner la formule matricielle pour obtenir la position de cette valeur maximale. Dans la fonction SommeProd, il ne s'agit plus de recouper la matrice conditionnelle par la matrice des indices de ligne. Il s'agit naturellement de la recouper par la matrice des indices de colonne. Il suffit donc de remplacer la fonction Ligne par la fonction Colonne.

• En cellule K7, adapter la précédente syntaxe comme suit :

=SOMMEPROD((Valeurs = MAX(Valeurs))*COLONNE(Valeurs))

A validation, vous obtenez bien l'indice de colonne de la valeur la plus grande dans le tableau.

Ligne et colonne de la valeur Min
Bien entendu pour obtenir les indices de ligne et de colonne de la valeur la plus petite, les déclinaisons des précédentes syntaxes sont triviales. Dans la fonction SommeProd, il suffit de remplacer la fonction Max par la fonction Min.

• En cellule K10, inscrire et valider la formule matricielle suivante :

=SOMMEPROD((Valeurs = MIN(Valeurs))*LIGNE(Valeurs))

• En cellule K11, inscrire et valider la formule matricielle suivante :

=SOMMEPROD((Valeurs = MIN(Valeurs))*COLONNE(Valeurs))

Et comme vous pouvez le voir, au gré des modifications de valeurs dans le tableau, nous obtenons bien dynamiquement les positions en ligne et en colonne des valeurs seuils.



Dans un prochain volet, nous pousserons cette technique matricielle pour voir comment il est possible d'obtenir ces positions dynamiques en ajoutant une contrainte, en l'occurrence sur le choix du mois.