Résultats de synthèse grâce aux moyennes conditionnelles

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


Sujets que vous pourriez aussi aimer :


Moyennes conditionnelles avec Excel

Réaliser des moyennes répondant à des conditions précises est un atout intéressant pour dresser des bilans adaptés aux contextes.



Dans l'exemple finalisé illustré par la capture, nous souhaitons évaluer les performances des commerciaux. Nous calculons donc la moyenne des chiffres d'affaires pour le commercial désigné par le biais d'une liste déroulante. Cependant, dans la base de données, des absences sont révélées par des chiffres d'affaires nuls. L'idée consiste à pouvoir les exclure pour calculer une moyenne effective, donc conditionnelle. Cette condition consiste à réaliser l'opération lorsque le chiffre d'affaires n'est pas nul.

Source et problématique
Pour exercer directement nos travaux, nous proposons de réceptionner premièrement une petite base de données Excel.

• Télécharger le classeur moyennes-valeurs-positives.xlsx en cliquant sur ce lien,
• Double cliquer sur le fichier téléchargé pour l'ouvrir dans Excel,
• Puis, cliquer sur le bouton Activer la modification du bandeau de sécurité,

Sur la feuille active, une base de données énumère les chiffres d'affaires réalisés par les différents commerciaux de l'entreprise. Comme vous pouvez le voir en consultant la colonne D, certains résultats sont sanctionnés par un score nul. Ici, nous considérons qu'il s'agit d'une absence du commercial. En conséquence, la donnée ne doit pas être intégrée pour évaluer la moyenne de ses actions. En d'autres termes, nous devons être en mesure de calculer une moyenne conditionnelle ignorant les cellules nulles.

Un tableau de bord est proposé sur la droite de la base de données. Une seule contrainte doit être posée dynamiquement. Il s'agit du commercial à évaluer. C'est pourquoi, il existe une liste déroulante en cellule J7 permettant de choisir parmi les quatre noms des commerciaux. Au choix de l'un d'entre eux, la moyenne réelle doit être calculée en J9 et la moyenne considérée doit être livrée en J11. La moyenne réelle intègre les résultats nuls tandis que la moyenne considérée doit les exclure.

• En haut à gauche de la feuille Excel, déployer la liste déroulante de la zone Nom,

Comme vous le constatez, chaque colonne de la base de données est reconnue par un nom, celui de son titre de champ. Ce nommage a été effectué automatiquement par une fonctionnalité proposée par Excel. Nous utiliserons ces noms pour simplifier la construction et la syntaxe des calculs.

De même, vous pouvez remarquer le repérage dynamique des enregistrements du commercial, au choix de son nom dans la liste déroulante en cellule J7. C'est bien sûr une règle de mise en forme conditionnelle qui a été bâtie en amont.

Moyenne conditionnelle
Excel propose des fonctions dédiées pour calculer des moyennes conditionnelles. Il s'agit respectivement des fonctions Moyenne.Si et Moyenne.Si.Ens. La première effectue l'opération sur une plage en considérant une condition sur cette même plage ou une autre à préciser. La seconde effectue le calcul sur une plage numérique en considérant un ensemble de critères à recouper.

Pour calculer la moyenne réelle du vendeur en cellule J9 avec des techniques classiques de calcul, la syntaxe à construire est la suivante :

=MOYENNE.SI(Vendeur; J7; Réalisé)

En deuxième argument, nous passons le critère, soit le nom du commercial à repérer sur la colonne identifiée par son nom en premier argument. Dès lors, dès qu'un enregistrement concordant est repéré, la moyenne des chiffres est calculée sur la colonne nommée Réalisé.

Mais bien sûr, ici nous continuons notre assimilation des techniques matricielles. Et nous allons le voir, avec une syntaxe aussi simple, nous allons aboutir rapidement au même résultat.

• En cellule J9, taper le symbole égal (=) pour initier le calcul matriciel,
• Saisir la fonction pour la moyenne suivie d'une parenthèse, soit : Moyenne(,
• Saisir la fonction conditionnelle suivie d'une parenthèse, soit : Si(,
• Désigner la matrice des commerciaux par son nom, soit : Vendeur,
• Taper le symbole égal (=) pour annoncer la condition à respecter,
• Sélectionner le nom du commercial choisi en cliquant sur sa cellule J7,
• Taper un point-virgule (;) pour passer dans la branche Alors de la fonction Si,
• Désigner la matrice des chiffres d'affaires par son nom, soit : Réalisé,
• Taper un point-virgule (;) pour passer dans la branche Sinon de la fonction Si,
• Taper deux guillemets ('') pour conserver le résultat vierge en cas de non correspondance,
• Fermer la parenthèse de la fonction Si,
• Fermer la parenthèse de la fonction Moyenne,
• Puis, valider nécessairement la formule par le raccourci clavier CTRL + MAJ + Entrée,

La touche MAJ est aussi connue sous la désignation anglaise SHIFT. C'est ainsi que nous transformons le calcul en opération matricielle. Pour chaque ligne de la première matrice honorant la condition, le chiffre d'affaires de la même ligne de la seconde matrice est intégré dans le calcul de la moyenne.

En consultant la barre de formule de la cellule du résultat, vous constatez que la syntaxe est encadrée d'accolades :

{=MOYENNE(SI(Vendeur=J7; Réalisé; ''))}

Ce sont elles qui indiquent qu'il s'agit d'un calcul matriciel.

Si vous changez de commercial avec la liste déroulante en cellule J7, vous constatez que la moyenne s'actualise parfaitement en fonction des résultats de chacun. La moyenne que nous avons calculée considère les résultats nuls.

Moyenne multicritère
Désormais, nous devons calculer la moyenne considérée en cellule J11. Pour l'obtenir, deux conditions croisées sont à respecter. La première concerne le nom du commercial. La seconde consiste à exclure tous les chiffres d'affaires nuls. En effet, autant la fonction Moyenne exclut naturellement les cellules vides, autant elle inclut fort logiquement les cellules portant la valeur zéro. Avec les techniques classiques de calculs, la syntaxe est la suivante :

=MOYENNE.SI.ENS(Réalisé; Vendeur; J7;Réalisé; '>0')

La fonction Moyenne.Si.Ens est nécessaire pour calculer une moyenne honorant plusieurs conditions. La plage du calcul est mentionnée en premier paramètre. Puis sont énumérés alternativement les plages des conditions et leurs critères. Il est temps d'aboutir au même résultat par raisonnement matriciel.

• Sélectionner la cellule J11 et taper le symbole égal (=) pour initier l'opération matricielle,
• Saisir la fonction pour la moyenne suivie d'une parenthèse, soit : Moyenne(,
• Saisir la fonction conditionnelle suivie d'une parenthèse, soit : Si(,
• Ouvrir une nouvelle parenthèse pour accueillir la première matrice conditionnelle,
• Désigner la plage des commerciaux par son nom, soit : Vendeur,
• Taper la symbole égal (=) pour la condition à honorer,
• Sélectionner le commercial choisi par la liste déroulante en cliquant sur sa cellule J7,
• Fermer la parenthèse de cette première matrice conditionnelle,
• Taper le symbole de l'étoile (*) pour recouper avec une seconde condition matricielle,
• Ouvrir une parenthèse pour accueillir la seconde matrice conditionnelle,
• Désigner la plage des chiffres d'affaires par son nom, soit : Réalisé,
• Taper le symbole supérieur suivi du chiffre zéro, soit : >0, pour la seconde condition,

Grâce à elle, sur les commerciaux repérés par la précédente matrice conditionnelle, nous cherchons à exclure du calcul tous les chiffres nuls.

• Fermer la parenthèse de cette seconde matrice conditionnelle,
• Taper un point-virgule (;) pour passer dans la branche Alors de la fonction Si,
• Désigner la plage du calcul par son nom, soit : Réalisé,
• Taper un point-virgule (;) pour passer dans la branche Sinon de la fonction Si,
• Taper deux guillemets ('') pour ne pas inclure le résultat en cas de non correspondance,
• Fermer la parenthèse de la fonction Si,
• Fermer la parenthèse de la fonction Moyenne,
• Puis, valider nécessairement la formule par le raccourci clavier CTRL + MAJ + Entrée,

Le résultat tombe et fort logiquement, il est supérieur à la moyenne réelle.



La formule matricielle que nous avons construite, encadrée d'accolades, est la suivante :

{ =MOYENNE(SI((Vendeur=J7)*(Réalisé>0); Réalisé; ''))}

Nous obtenons la même conclusion qu'avec la fonction Moyenne.Si.Ens, pour une syntaxe finalement plus légère. Si vous modifiez le nom du commercial avec la liste déroulante, vous remarquez l'adaptation instantanée des résultats.

La dernière moyenne à calculer est triviale. Aucune condition n'est à honorer. Tous les commerciaux sont concernés quels que soit les chiffres renseignés. Sa vocation est de servir d'étalon à titre de comparaison pour chacun des vendeurs.

En cellule J13, il convient donc de réaliser le calcul classique suivant : =MOYENNE(Réalisé). Avec cette dernière donnée, il est intéressant de constater que le vendeur Hamalibou est le seul dont la moyenne réelle est inférieure à ce résultat. Et pourtant dans le même temps, il s'agit du vendeur qui réalise et de loin, la meilleure moyenne considérée.