La moyenne conditionnelle avec Excel, moyenne selon critères

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


Sujets que vous pourriez aussi aimer :


Réaliser des moyennes spécifiques

La fonction MOYENNE.SI()

La fonction MOYENNE.SI() permet de réaliser la moyenne de nombres lorsqu'un critère est posé. Pour débuter l'illustration de cette fonction :

• Ouvrir un nouveau classeur,
• Reproduire le tableau de la figure ci-dessus.

Il s'agit de la synthèse du nombre de commandes et des chiffres d'affaire associés, réalisés dans une entreprise par secteur. Commençons par calculer la moyenne des CA supérieurs à 150 000 Euros. La moyenne doit donc être effectuée sur la plage de cellules C4:C7 si le chiffre d'affaire est supérieur à 150 000 Euros.

• Cliquer sur la cellule C10 pour l'activer,
• Taper le signe = pour initier le calcul,
• Saisir le nom de la fonction MOYENNE.SI,

Au fur et à mesure de la saisie vous remarquez les suggestions de fonctions commençant par les mêmes lettres. Le fait de double cliquer sur l'une d'entre elles permet d'éviter la saisie intégrale.

• Ouvrir la parenthèse,

Dès que la parenthèse s'ouvre vous remarquez les indications données pour remplir la fonction : plage critères;[plage_moyenne]. Tout d'abord plage est la plage de cellules sur laquelle sera portée le critère. Ensuite le critère à écrire entre guillemets. Puis[plage_moyenne], la plage correspondante sur laquelle sera calculée la moyenne en fonction du critère posé sur l'autre plage. Dans notre cas, le critère et la moyenne sont posés sur la même plage, celle des C.A. Il n'est donc pas nécessaire de l'indiquer deux fois. C'est pourquoi ce troisième argument plage_moyenne est indiqué entre crochets signifiant facultatif dans Excel.

• Sélectionner la plage de cellules des CA (C4:C7),
• Taper un point-virgule pour passer à l'argument suivant,
• Saisir '>150000'sans oublier les guillemets,
• Fermer la parenthèse et valider par la touche Entrée.

Vous obtenez le résultat 162 000 soit la moyenne des C.A. 161 000 et 163 000 qui sont les deux seuls à être supérieurs à 150 000.

Calculons maintenant la moyenne des ventes pour lesquelles le nombre des commandes est supérieur à 60. Cette fois le critère est posé sur la page de cellules des nombres de commandes soit B4:B7. La moyenne elle, est toujours réalisée sur la plage de cellules des chiffres d'affaire soit C4:C7. Nous allons donc utiliser les trois arguments de la fonction MOYENNE.SI() contrairement à l'exemple précédent.

• Cliquer sur la cellule C11 pour l'activer,
• Taper le signe = pour initier le calcul,
• Saisir le nom de la fonction MOYENNE.SI,
• Ouvrir la parenthèse,
• Sélectionner la plage de cellules du critère soit B4:B7,
• Taper un point-virgule pour passer à l'argument suivant,
• Taper le critère '>60' sans oublier les guillemets,
• Taper un point-virgule pour passer à l'argument suivant,
• Sélectionner la plage de cellules des chiffres d'affaire C4:C7,
• Fermer la parenthèse,
• Valider la fonction avec la touche Entrée.

Nous obtenons le résultat de 151333,33 qui est bien la moyenne de 161000, 130000 et 163000.

La moyenne pondérée
La moyenne pondérée intervient lorsque tous les nombres à moyenner n'ont pas la même importance. Par exemple dans le cadre des notes d'un bulletin, les matières sont associées à des coefficents. Ainsi pour calculer la moyenne générale, nous ne pouvons pas sommer l'ensemble des notes pour les diviser par le nombre total d'entre elles. La valeur obtenue ne tenant pas compte des coefficents serait erronée. Pour mettre en pratique ces considérations :

• Créer une nouvelle feuille dans le classeur existant,
• Reproduire le tableau de la figure ci-dessous.

Chaque matière est associée à un coefficent qui lui est propre. Ainsi les mathématiques ont un coefficent 5 tandis que la langue vivante 2 est associée à un coefficient 5 fois inférieur, soit 1. Pour bien comprendre la différence, nous allons tout d'abord réaliser une moyenne classique sans tenir compte des coefficients. Nous allons ensuite réaliser une moyenne pondérée sans les fonctions en tenant compte des coefficients. Puis nous réaliserons une moyenne pondérée à l'aide des fonctions Excel et nous comparerons les résultats.

• Cliquer dans la cellule F5 pour l'activer,
• Utiliser la fonction moyenne pour calculer la moyenne normale de toutes les notes.

Vous devez obtenir une moyenne générale de 13. Calculons maintenant la moyenne pondérée sans l'aide des fonctions Excel. Chaque note doit être multipliée par son coefficient et ajoutée à la suivante, elle-même multipliée par son coefficient et ainsi de suite. Le résultat de cette addition doit être divisé par la somme de tous les coefficients.

• Cliquer sur la cellule F6 pour l'activer,
• Taper le signe = pour initier le calcul,
• Reproduire la formule de la figure ci-dessous.

Notez la présence de doubles parenthèses afin que le résultat total de l'addition puisse être divisé par la somme des coefficients. Le résultat contient trop de décimales.

• Cliquer sur le bouton réduire les décimales de la section Nombre du ruban Accueil jusqu'à ce qu'il ne reste plus que 2 décimales.

Vous devez obtenir une moyenne générale de 12,68, donc inférieure à la précédente, à cause des mathématiques notamment, possédant le plus fort coefficient. Réalisons enfin la moyenne pondérée à l'aide des fonctions Excel. Nous allons utiliser la fonction SOMMEPROD() et la fonction SOMME(). La fonction SOMMEPROD() multiplie la note de chaque matière par son coefficient. Il s'agit donc d'une somme qui tient compte de l'importance du nombre. Il suffira ensuite de diviser par la somme des coefficents pour obtenir la moyenne pondérée automatisée.

• Cliquer dans la cellule F7 pour l'activer,
• Taper le signe = pour initier le calcul,
• Saisir la fonction SOMMEPROD(,
• Sélectionner la plage de cellules des notes B2:H2,
• Taper un point-virgule pour passer à l'agument suivant, la plage des coefficients,
• Sélectionner la plage de cellules des coefficients B3:H3,
• Taper un slash(/) pour la division,
• Saisir la fonction Somme(,
• Sélectionner de nouveau la plage de cellules des coefficients B3:H3,
• Fermer la parenthèse et valider le calcul par la touche Entrée.

Après avoir réduit les décimales, nous obtenons bien le même résultat que le précédent, celui de la moyenne pondérée calculé manuellement. Simplement les fonctions nous aident à gagner du temps et rendent plus lisible le calcul.

#TagPub#