Repérer les N meilleures valeurs dans une plage mouvante

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 les N meilleures valeurs sur plage variable

Excel propose des règles prédéfinies de mise en forme conditionnelle afin de faire ressortir les N meilleures données sur une plage de cellules. Cependant, une contrainte existe. La plage concernée doit être explicitement désignée. Dans cette formation, l'utilisateur souhaite obtenir des données statistiques pour une année et un certain nombre de mois. Ces deux leviers sont variables. En conséquence, la plage d'action varie elle aussi.



Sur l'exemple illustré par la capture, nous travaillons sur un tableau synthétisant les chiffres d'affaires réalisés pour tous les mois de plusieurs années. L'utilisateur définit une année et un nombre de mois dans une petite zone d'action, sur la droite du tableau. Ce nombre de mois désigne les N derniers pour l'année choisie. Instantanément, les trois meilleurs chiffres sont surlignés sur une plage de cellules variant et se déplaçant en fonction des contraintes émises.

Source et présentation
Pour réaliser ces travaux, nous devons commencer par récupérer ce tableau des chiffres d'affaires.

• Télécharger le classeur reperer-n-meilleurs.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é,

L'utilisateur peut donc définir une année à l'aide d'une liste déroulante en cellule H6. Il peut ensuite croiser cette condition en définissant le nombre des derniers mois pour l'analyse, en l'inscrivant en cellule H9. Par exemple, pour les 7 derniers mois de l'année 2022, l'étude doit porter sur la plage de cellules F11:F17. Et il est question d'y repérer dynamiquement les trois meilleurs chiffres d'affaires.

En déployant la zone Nom en haut à gauche de la feuille Excel, vous notez que des intitulés ont été attribués à certaines plages. Par exemple, la plage C5:F5 des années est reconnue sous la désignation annees. Nous exploiterons ces noms dans la construction de la syntaxe permettant à la règle de mise en forme conditionnelle de s'ajuster sur la bonne plage à analyser.

Construire une plage mouvante
Pour simplifier la construction de la règle de mise en forme conditionnelle, nous suggérons premièrement de fabriquer la plage réagissant en position et hauteur, en fonction des contraintes émises par l'utilisateur. Pour cela, nous devons exploiter le gestionnaire de noms et adapter cette plage grâce à la fonction Excel Decaler.

=Decaler(Point_de_départ; Décalage_ligne; Décalage_colonne; [Hauteur] ; [Largeur])

• 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 Gestionnaire de noms,
• En haut de la boîte de dialogue qui suit, cliquer sur le bouton Nouveau,
• Dans la nouvelle boîte de dialogue, taper l'intitulé pajust dans la zone Nom,
• Supprimer le contenu de la zone Fait référence à en bas de la boîte de dialogue,
• Puis, taper le symbole égal (=) pour initier la nouvelle syntaxe,
• Inscrire la fonction d'ajustement suivie d'une parenthèse, soit : Decaler(,
• Désigner le premier chiffre d'affaires en cliquant sur sa cellule C6,

La syntaxe résultante est la suivante : 'CA-Annees'!$C$6. La cellule est naturellement figée et préfixée du nom de la feuille. Nous venons donc de définir le point de départ de cette plage mouvante.

• Taper un point-virgule (;) pour passer dans l'argument du décalage en ligne,
• Inscrire la fonction pour compter les cellules non vides, suivie d'une parenthèse, soit : NbVal(,
• Désigner l'intégralité de la colonne C, ce qui donne : 'CA-Annees'!$C:$C,

Un clic sur l'étiquette de colonne C désigne la plage B:H en raison des fusions de cellules en ligne 1. Il convient donc de réajuster ses bornes manuellement.

• Fermer la parenthèse de la fonction NbVal,
• Puis, retrancher deux unités à ce dénombrement, soit : -2,

Nous retranchons une première unité pour ne pas considérer le titre du tableau. La seconde unité est enlevée du fait que le point de départ, la cellule C6, est déjà considérée dans le positionnement. De fait, avec ce décalage, nous déplaçons le pointeur de la plage sur la dernière valeur pour l'année 2019. Nous devons maintenant ajuster sa position en colonne, en fonction de l'année choisie. Ensuite, nous nous soucierons de sa hauteur, fonction du nombre de mois défini.

• Taper un point-virgule (;) pour passer dans l'argument du décalage en colonne,
• Taper la fonction pour la position d'une valeur cherchée, suivie d'une parenthèse, soit : Equiv(,
• Désigner l'année choisie en cliquant sur sa cellule H6, ce qui donne : 'CA-Annees'!$H$6,
• Taper un point-virgule (;) pour passer dans l'argument du tableau de recherche,
• Désigner la ligne des années par son nom, soit : annees,

Ainsi, nous cherchons l'emplacement de l'année choisie par l'utilisateur dans la ligne de titre du tableau. Il va en résulter un décalage opéré par la fonction Decaler pour pointer sur la bonne colonne.

• 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, retrancher une unité à ce résultat, soit : -1,

Comme précédemment, le point de départ étant considéré dans la sélection, cette soustraction est nécessaire pour ne pas déplacer le pointeur une colonne trop loin.

• Taper un point-virgule (;) pour passer dans l'argument de la hauteur variable,

A ce stade, nous sommes positionnés sur la dernière valeur de la colonne désignée par l'année choisie. Nous devons agrandir cette plage en remontant d'autant de cases que spécifié par le nombre de mois choisi.

• Inscrire le symbole moins (-) pour signifier le déplacement inverse, soit en hauteur,
• Cliquer alors sur la cellule du mois choisi, ce qui donne : 'CA-Annees'!$H$9,
• Fermer la parenthèse de la fonction Decaler,
• Puis, cliquer sur le bouton Ok pour valider la création de la plage pajust,

Nous sommes de retour dans le gestionnaire de noms. Et notre plage dynamique apparaît désormais listée avec les autres.

• Cliquer sur le bouton Fermer du gestionnaire pour revenir sur la feuille Excel,

Rien de visuel ne change pour l'instant. Cette plage dynamique est certes créée mais elle n'est pas encore exploitée. Sa syntaxe complète est la suivante :

=Decaler('CA-Annees'!$C$6; NbVal('CA-Annees'!$C:$C)-2; Equiv('CA-Annees'!$H$6; annees; 0)-1; -'CA-Annees'!$H$9)

Valeurs seuilles dans plage mouvante
Pour identifier visuellement les trois meilleurs chiffres sur cette plage dynamique, dépendants de l'année et du nombre de mois choisis, nous devons exploiter la fonction Excel Grande.Valeur. Celle-ci, selon la valeur numérique qui lui est passée en second paramètre, permet de retourner la plus grande valeur (1) ou la deuxième plus grande (2) ou encore la troisième (3). Comme le raisonnement matriciel n'est pas permis dans une règle de mise en forme conditionnelle, nous devons combiner ces conditions à vérifier dans une fonction OU.

• Sélectionner tous les chiffres d'affaires du tableau, soit la plage de cellules C6:F17,
• Dans la section Styles du ruban Accueil, cliquer sur le bouton Mise en forme conditionnelle,
• En bas de la liste des propositions, choisir la commande 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 juste en-dessous pour l'activer,
• Taper le symbole égal (=) pour initier la syntaxe de la règle de mise en forme conditionnelle,
• Inscrire la fonction pour énumérer les critères à additionner, avec une parenthèse, soit : Ou(,
• Désigner le premier chiffre d'affaires en cliquant sur sa cellule C6,
• Enfoncer trois fois la touche F4 du clavier pour la libérer complètement,

Ainsi, les dollars disparaissent. En effet, l'analyse d'une mise en forme conditionnelle est chronologique. De cette façon, tous les chiffres vont être passés en revue pour être comparés aux grandes valeurs.

• Taper le symbole égal (=) pour annoncer le critère à honorer,
• Inscrire la fonction des grandes valeurs suivie d'une parenthèse, soit : Grande.Valeur(,
• Désigner la plage variable dynamique par son nom, soit : pajust,
• Taper un point-virgule (;) pour passer dans l'argument du rang de la fonction,
• Saisir le chiffre 1,

Ainsi, toujours dans l'esprit de l'analyse chronologique, nous allons comparer chaque chiffre d'affaires avec la plus grande valeur trouvée sur la plage ajustée en fonction de l'année choisie et du nombre de mois défini.

• Fermer la parenthèse de la fonction Grande.Valeur,
• Taper un point-virgule (;) pour poursuivre l'énumération des conditions possibles,
• Reproduire la même syntaxe que précédemment mais en ajustant le rang, comme suit :

C6=Grande.Valeur(pajust;2)

• Taper un point-virgule (;) pour annoncer la troisième condition possible,
• Adapter de nouveau la syntaxe en ajustant le rang comme suit :

C6=Grande.Valeur(pajust;3)

• Puis, fermer la parenthèse de la fonction Ou,

Désormais, dès que l'un de ces critères est repéré, nous devons faire ressortir les cellules concernées dans des attributs de format explicitement différents.

• Cliquer sur le bouton Format en bas de la boîte de dialogue,
• Dans la boîte de dialogue qui suit, activer l'onglet Remplissage,
• Dans la palette de couleurs, choisir un jaune pâle,
• Activer ensuite l'onglet Police de cette même boîte de dialogue,
• Définir un style gras pour choisir un jaune foncé pour le texte avec la liste déroulante,
• 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,

Nous sommes ainsi de retour sur la feuille Excel. Les couleurs surgissent pour repérer les trois meilleurs chiffres d'affaires dans la plage adaptée aux conditions émises sur l'année et le nombre de mois. Et bien entendu, si vous modifiez ces contraintes, les couleurs se déplacent pour repérer les meilleurs chiffres dans le contexte ajusté.

La syntaxe complète de la règle de mise en forme conditionnelle que nous avons construite est la suivante :

=OU(C6=GRANDE.VALEUR(pajust;1); C6=GRANDE.VALEUR(pajust;2); C6=GRANDE.VALEUR(pajust;3))



Dans la formation suivante, nous exploiterons ces travaux pour livrer la moyenne des N meilleurs chiffres d'affaires sur une plage de cellules mouvante.