Calculer les valeurs seuils sur une colonne inconnue

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


Sujets que vous pourriez aussi aimer :


Max et Min sur colonne variable

Cette nouvelle astuce Excel montre comment calculer et faire ressortir les valeurs seuils sur une colonne qui n'est pas définie à l'avance.



Dans l'exemple finalisé illustré par la capture, l'étude se base sur un tableau recensant sur plusieurs lignes les points cumulés par plusieurs équipes. Pour chaque équipe choisie dynamiquement par le biais d'une liste déroulante, nous souhaitons connaître le plus grand nombre de points, le plus petit et la somme de tous ces points. Il devient alors un jeu d'enfants de comparer les scores entre les équipes.

Source et présentation
Pour la démonstration de cette astuce, nous proposons d'appuyer nos travaux sur ce tableau qu'il convient donc de récupérer.

• Télécharger le classeur max-colonne-dynamique.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 équipes sont listées en première ligne du tableau. Dans la feuille, il s'agit de la cinquième ligne. Cette plage est reconnue sous le nom Equipes. Vous pouvez le constater en déployant la zone Nom en haut à gauche de la feuille Excel. Les points sont archivés sur douze lignes pour chacune des équipes. Cette plage est reconnue sous le nom Points.

Une liste déroulante permet de choisir l'une de ces équipes en cellule J8. En fonction de ce choix, nous devons être en mesure de calculer le score le plus grand en cellule J11 et le plus petit en cellule J14. Enfin, nous devons livrer le total de points pour l'équipe ainsi désignée dynamiquement. Et ce résultat doit apparaitre en cellule J17.

Maximum sur colonne dynamique
Comme la colonne est définie dynamiquement en fonction du choix de l'équipe par la liste déroulante, l'extraction des valeurs seuils peut se faire grâce à l'imbrication des fonctions Index et Equiv encapsulées dans la fonction de synthèse (Max). C'est en effet la fonction Equiv qui doit retourner à la fonction Index, l'indice de colonne à partir duquel l'extraction doit être réalisée.

• Sélectionner la valeur Max à trouver en cliquant sur sa cellule J11,
• Taper le symbole égal (=) pour initier la syntaxe de la formule,
• Inscrire la fonction pour la plus grande valeur, suivie d'une parenthèse, soit : Max(,
• Inscrire la fonction d'extraction suivie d'une parenthèse, soit : Index(,
• Désigner le tableau des points par son nom, soit : Points,
• Puis, taper deux points-virgules, soit : ;;,

En effet, le deuxième argument de la fonction Index concerne l'indice de ligne. Celui-ci sera implicitement retourné par le repérage de la fonction Max dans la colonne dynamique qu'il nous reste encore à trouver. Avec cette succession de points-virgules, nous ignorons donc le deuxième argument sur l'indice de ligne. Et nous nous trouvons propulsés dans le troisième argument de la fonction Index, celui sur l'indice de colonne.

• Inscrire la fonction de recherche de position, suivie d'une parenthèse, soit : Equiv(,
• Désigner l'équipe à chercher en cliquant sur sa cellule J8,
• Taper un point-virgule (;) pour passer dans l'argument de la rangée de recherche,
• Désigner la plage des équipes par son nom, soit : Equipes,
• 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,
• Fermer la parenthèse de la fonction Index,
• Puis, fermer la parenthèse de la fonction Max,
• Enfin, valider le calcul avec la touche Entrée du clavier,

Le résultat tombe et indique que le plus grand nombre de points engrangés par l'équipe des violets est 90. Il apparaît tout à fait cohérent et il est très simple à vérifier.



Et à ce propos, nous proposons de découvrir une astuce efficace pour corroborer rapidement ce résultat.

• Avec la liste déroulante, choisir une autre équipe, par exemple les Jaunes,

Aussitôt le calcul du maximum s'actualise puisque la colonne d'extraction change. La valeur max est cette fois de 80.

• Sélectionner alors tous les points de cette équipe, soit la plage de cellules D6:D17,
• En bas à droite de la fenêtre Excel, cliquer avec le bouton droit sur la barre d'état,
• Dans le menu contextuel, choisir l'option Maximum,

Désormais, cette information de synthèse s'affichera pour la sélection en cours, au même titre que la somme et la moyenne. Et l'indication fournie vient parfaitement recouper le résultat livré par notre calcul dynamique d'extraction.

Vous l'avez compris, la méthode est identique pour extraire le plus petit score d'une colonne variable. Dans la syntaxe précédemment construite, il suffit simplement de remplacer la fonction Max par la fonction Min.

• En cellule J14, adapter la formule comme suit : =Min(INDEX(Points;; EQUIV(J8; Equipes; 0))),

Là encore, le résultat est parfaitement cohérent et s'avère très simple à vérifier.

Sommer sur une colonne variable
Pour calculer la somme des points en fonction de l'équipe choisie, il est question de caler l'analyse sur la bonne colonne. Comme précédemment, nous devons trouver sa position avec la fonction Equiv. Et cette fois, nous devons nous y déplacer à l'aide la fonction Decaler. La hauteur de la plage du calcul peut se définir dynamiquement grâce à la fonction NbVal. La fonction Somme doit naturellement imbriquer toutes les autres.

• Sélectionner la somme à calculer en cliquant sur sa cellule J17,
• Taper le symbole égal (=) pour initier la syntaxe du calcul,
• Inscrire la fonction d'addition suivie d'une parenthèse, soit : Somme(,
• Puis, inscrire la fonction d'ajustement suivie d'une parenthèse, soit : Decaler(,
• Désigner les premiers points du tableau en cliquant sur sa cellule B6,

Ainsi, nous définissons le point de départ de la plage. Mais celui-ci est amené à être ajusté en colonne en fonction du choix de l'équipe. De plus, il s'agira de définir la hauteur dynamique, si d'aventure d'autres lignes de points venaient à être ajoutées à la suite du tableau.

• Taper deux points-virgules, soit : ;;, pour ignorer l'argument du décalage en ligne,

Nous sommes en effet déjà placés sur la bonne ligne avec cette cellule de départ en B6. C'est le décalage en colonne qui nous intéresse. Et précisément, nous sommes désormais positionnés dans l'argument qui le concerne.

• Inscrire la fonction de recherche de position suivie d'une parenthèse, soit : Equiv(,
• Cliquer sur la cellule J8 pour désigner l'équipe à chercher,
• Taper un point-virgule (;) pour passer dans l'argument de la ligne de recherche,
• Désigner les équipes par leur nom de plage, soit : Equipes,
• Taper un point-virgule suivi du chiffre 0, soit : ;0, pour une recherche exacte,
• Dès lors, fermer la parenthèse de la fonction Equiv,
• Puis, retrancher une unité à ce résultat, soit : -1,

En effet, la cellule de départ (B6) est déjà incluse dans le décompte. Pour ne pas fausser le décalage en colonne, nous la sortons du dénombrement. A ce stade, nous sommes censés avoir déplacé les bornes du calcul, soit de la somme, sur la première cellule de la colonne correspondant à l'équipe choisie. Nous devons encore préciser sur quelle hauteur les valeurs doivent être additionnées. Et c'est précisément l'argument suivant qui concerne ce réglage.

• Taper un point-virgule (;) pour passer dans l'argument de la hauteur de la fonction Decaler,
• Inscrire la fonction comptant les cellules non vides, suivie d'une parenthèse, soit : NbVal(,
• Désigner l'intégralité de la colonne B, soit : B:B,
• Fermer la parenthèse de la fonction NbVal,
• Retrancher trois unités à ce décompte, soit : -3,

En effet, nous devons ignorer les trois cellules de titre au-dessus du tableau.

• Fermer la parenthèse de la fonction Decaler,
• Puis, fermer la parenthèse de la fonction Somme,
• Enfin, valider le calcul avec la touche Entrée du clavier,

Le résultat tombe et une fois encore il est très simple à corroborer grâce à la barre d'état après avoir sélectionné les points de la colonne concernée. Et comme toujours, si vous changez d'équipe à l'aide de la liste déroulante, tous les calculs s'actualisent aussitôt.

La syntaxe complète de la formule que nous avons construite est la suivante :

=SOMME(DECALER(B6;; EQUIV(J8; Equipes; 0)-1; NBVAL(B:B)-3))

Repérer les valeurs seuils
Pour une application parfaitement aboutie, nous proposons de faire surgir en couleur les valeurs seuils dans la colonne de l'équipe choisie dynamiquement. Pour cela, nous devons bâtir deux règles de mise en forme conditionnelle. Elles doivent établir la correspondance entre la valeur seuil extraite et le nom de l'équipe choisie.

• Sélectionner tous les points du tableau, soit la plage de cellules B6:H17,
• 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 ...,
• Cliquer 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 critères, suivie d'une parenthèse, soit : Et(,
• Désigner les premiers points en cliquant sur la cellule B6, soit : $B$6,
• Enfoncer trois fois la touche F4 du clavier pour libérer complètement la cellule, soit : B6,

Souvenez-vous, l'analyse d'une mise en forme conditionnelle est chronologique. Pour chercher le score le plus grand en le comparant à la valeur extraite depuis sa colonne dynamique, nous devons passer en revue tous les points. C'est la raison pour laquelle nous libérons en ligne comme en colonne la cellule utilisée comme point de départ de l'analyse.

• Taper le symbole égal (=) pour annoncer le critère à vérifier,
• Désigner la valeur max extraite en cliquant sur sa cellule J11, ce qui donne : $J$11,

Cette fois, nous conservons figée cette cellule de référence. Bien que l'analyse déplace son curseur au fil de la progression, la comparaison doit toujours être établie par rapport à ce résultat calculé et extrait.

• Taper un point-virgule (;) pour poursuivre l'énumération des conditions,
• Sélectionner cette fois la première équipe en cliquant sur sa cellule B5, soit : $B$5,
• Enfoncer une fois la touche F4 du clavier pour la libérer en colonne, soit : B$5,

En effet, l'équipe choisie par l'utilisateur doit être trouvée sur cette cinquième ligne. Nous la conservons donc figée. Mais pour qu'elles puissent toutes être comparées au fil de l'analyse, nous la laissons se déplacer en colonne.

• Taper le symbole égal (=) pour annoncer la seconde condition à honorer,
• Puis, désigner l'équipe choisie en cliquant sur sa cellule J8, ce qui donne : $J$8,
• Fermer alors la parenthèse de la fonction Et,

Lorsque ces deux critères sont vérifiés, soit lorsque l'équipe coïncide et que la valeur max est identique, nous devons faire ressortir explicitement la cellule décelée par des attributs de format.

• En bas de la boîte de dialogue, cliquer sur le bouton Format,
• Dans la boîte de dialogue qui suit, activer l'onglet Police,
• Avec la seconde liste déroulante, choisir un vert plutôt vif pour la couleur du texte,
• Cliquer sur le bouton Ok pour valider cet attribut de format,
• De retour sur la première boîte de dialogue, valider la création de la règle par le bouton Ok,

Aussitôt, le score le plus grand pour la colonne de l'équipe désignée surgit. Et si vous changez d'équipe, la couleur de repérage se déplace parfaitement. Désormais, nous devons bâtir une seconde règle sensiblement identique. Elle doit établir la correspondance sur l'équipe désignée. Et dans le même temps, elle doit vérifier que la valeur en cours d'analyse est bien la plus petite extraite par le calcul. Sur la même plage que précédemment, la syntaxe est la suivante : =ET(B6=$J$14; B$5=$J$8). Il convient de lui associer un orange vif, semblable au tableau de bord, pour la couleur du texte.



Et désormais, toutes les correspondances sont établies dynamiquement, aussi bien pour les extractions que pour les repérages en couleur.