Moyenne des N meilleures valeurs sur plage variable

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


Sujets que vous pourriez aussi aimer :


Moyenne des N meilleures valeurs

Réaliser la moyenne des N plus grandes valeurs est permise grâce à l'imbrication des fonctions Excel Moyenne et Grande.Valeur. Mais lorsque la plage du calcul n'est pas définie à l'avance, le problème se corse.



Sur l'exemple illustré par la capture, nous travaillons sur une base de données des chiffres d'affaires réalisés pour chaque mois de plusieurs années. Sur la droite de ce tableau, l'utilisateur peut définir une année en particulier grâce à une liste déroulante. De fait, la plage du calcul change de colonne. Dans le même temps, il peut spécifier un certain nombre de mois. Il s'agit des N derniers mois pour l'année choisie. De fait, la plage du calcul varie aussi en hauteur.

Source et présentation
Pour structurer l'étude, nous proposons tout d'abord de récupérer ce petit tableau de données.

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

Le tableau des chiffres d'affaires est donc situé entre les colonnes B et F et entre les lignes 5 et 17. En cellule H6, l'utilisateur peut choisir une année par le biais d'une liste déroulante. En cellule H9, l'utilisateur peut inscrire un chiffre pour déterminer les N derniers mois du calcul sur l'année définie. Et ce calcul doit être produit en cellule H12. Il s'agit de livrer la moyenne des trois meilleurs chiffres d'affaires sur la période variable ainsi construite.

Et à ce titre, en modifiant les valeurs de ces deux leviers, celle de l'année et celle du nombre de mois, vous constatez que des couleurs se déplacent dans le tableau. Elles surlignent les trois meilleurs chiffres d'affaires sur cette plage variable et mouvante. Il s'agit du résultat que nous avons abouti lors de la formation précédente.



Pour cela, nous avions construit une règle de mise en forme conditionnelle exploitant la fonction Excel Grande.Valeur. Pour honorer les trois valeurs seuilles à repérer, nous l'avions utilisée dans trois critères énumérés grâce à la fonction OU. Ici, le raisonnement est proche. Mais en dehors d'une règle, nous pouvons utiliser une technique s'apparentant à celles des calculs matriciels. Il n'est plus question d'énumérer les rangs tour à tour. Nous allons les regrouper et les combiner dans une unique formule.

Remarque : En déployant la zone Nom en haut à gauche de la feuille Excel, vous notez que certaines plages ont été nommées. Par exemple, la ligne de titres du tableau porte l'intitulé annees. Nous exploiterons ce nom dans la construction de la formule.

Moyenne sur une plage variable
La fonction Moyenne doit donc encapsuler la fonction Grande.Valeur pour réduire son champ d'action sur les trois meilleurs chiffres. De plus, la fonction Grande.Valeur doit encapsuler la fonction Decaler pour adapter la plage du calcul au gré des modifications de critères imposés par l'utilisateur sur l'année et les mois.

• Sélectionner le résultat à trouver en cliquant sur sa cellule H12,
• Taper le symbole égal (=) pour initier le calcul,
• Inscrire la fonction de la moyenne suivie d'une parenthèse, soit : Moyenne(,
• Inscrire la fonction des grandes valeurs suivie d'une parenthèse, soit : Grande.Valeur(,
• 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,

Ainsi, nous spécifions le point de départ de la plage variable. Mais celui-ci est susceptible de se déplacer en colonne pour s'adapter à l'année choisie. Il doit aussi bouger en ligne puisque l'objectif est d'analyser les N derniers chiffres, selon le nombre de mois défini.

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

En raison de la présence de certaines cellules fusionnées en première ligne, un clic sur l'étiquette de colonne C ne produit pas le résultat escompté. Il est donc souhaitable de taper ces références.

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

L'objectif est en effet de déplacer le pointeur sur le dernier chiffre de la bonne colonne. Ainsi et ensuite, nous pourrons la réajuster en hauteur en remontant sur le nombre de mois indiqué. Une première unité est donc retranchée pour exclure la cellule de titre comptabilisée par la fonction NbVal. Une seconde unité est enlevée pour annuler la présence de la cellule de départ, déjà comptabilisée. Par exemple, la fonction NbVal retourne tout d'abord le résultat de 13 cellules non vides. En lui imputant ces deux unités, par rapport au premier chiffre d'affaires, nous nous déplaçons de 11 lignes vers le bas. Nous sommes désormais bien placés sur le dernier chiffre d'affaires de la première colonne.

Nous devons maintenant opérer un déplacement à l'horizontale pour pointer sur le dernier chiffre d'affaires de la colonne correspondant à l'année choisie.

• Taper un point-virgule (;) pour passer dans l'argument du décalage en colonne,
• Inscrire la fonction de recherche de positions, suivie d'une parenthèse, soit : Equiv(,
• Désigner l'année choisie en cliquant sur sa cellule H6,
• 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 recherchons bien l'année spécifiée dans la ligne de titre du tableau. La position retournée par la fonction Equiv, permettra d'opérer le décalage souhaité en 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é à cette position retournée, soit : -1,

En effet et comme précédemment, la cellule de départ est déjà comptée par la fonction Decaler. Pour ne pas aller une colonne trop loin, nous devons l'exclure du décompte.

• Taper un point-virgule (;) pour passer dans l'argument de la hauteur à ajuster,
• Inscrire le symbole moins (-),

En effet, nous sommes sur la dernière valeur de la colonne correspondant à l'année. Nous devons maintenant remonter d'autant de cases que de mois sont définis.

• Cliquer sur la cellule du nombre de mois choisi, soit H9,
• Fermer la parenthèse de la fonction Decaler,
• Taper un point-virgule (;) pour passer dans l'argument du rang de la fonction Grande.Valeur,

C'est ici que nous devons lui indiquer que le calcul porte non pas sur la meilleure valeur, mais sur les trois plus grandes.

• Inscrire alors la syntaxe suivante : {1.2.3},

Cette énumération entre accolades des rangs séparés par des points, est semblable à une matrice horizontale. Elle permet d'indiquer que le calcul doit porter sur les trois plus grandes valeurs de la plage variable réajustée en position et hauteur.

• Fermer la parenthèse de la fonction Grande.Valeur,
• Puis, fermer la parenthèse de la fonction Moyenne,
• Enfin, valider le calcul avec la touche Entrée du clavier,

Le résultat de la moyenne tombe. Et au gré des modifications des contraintes sur l'année et le nombre de mois, elle s'ajuste automatiquement. Pour corroborer la cohérence de ce calcul enclenché sur une plage variable réajustée par la fonction Decaler, il suffit de sélectionner ensemble les trois cellules surlignées. Dès lors, en consultant la moyenne fournie dans la barre d'état, en bas de la fenêtre Excel, vous constatez que le résultat du calcul dynamique est identique. Identique à la virgule près certes puisque la barre d'état livre une donnée arrondie.