Excel Conseils Utilisez SUMPRODUCT pour calculer les moyennes pondérées - pleine page
Utilisez SUMPRODUCT dans Excel et d'autres programmes de tableur pour le calcul des moyennes pondérées. Pour tout savoir sur la fonction Excel SUMPRODUCT ici.
Utilisez SUMPRODUCT pour calculer les moyennes pondérées
Excel, il est extrêmement facile de calculer la moyenne de plusieurs cellules: Il suffit d'utiliser la fonction moyenne. Mais si certaines valeurs ont plus de « poids » que d'autres? Par exemple, dans de nombreuses classes tests valent plus que les affectations. Pour ces situations, vous devrez calculer la moyenne pondérée.
Bien que Excel ne dispose pas d'une fonction moyenne pondérée, il a une fonction qui fait la plupart du travail pour vous: SUMPRODUCT. Même si vous ne l'avez jamais utilisé SUMPRODUCT avant, vous serez en mesure de l'utiliser comme un pro à la fin de cet article. La méthode que nous utilisons fonctionne avec toutes les versions d'Excel, et il fonctionne également avec d'autres applications de tableur comme Google Sheets.
Si vous souhaitez suivre, vous pouvez télécharger notre exemple.
Configuration de la feuille de calcul
Pour calculer la moyenne pondérée, vous aurez besoin d'au moins deux colonnes. La première colonne (colonne B dans notre exemple) contient les notes pour chaque cession ou test. La deuxième colonne (colonne C) contient les coefficients de pondération. Un poids plus élevé entraînera la cession ou un test d'avoir un plus grand effet sur la note finale.
Vous pouvez penser du poids comme étant le pourcentage de la note finale. Mais dans ce cas, la somme des poids effectivement à plus de 100%. C'est OK parce que notre formule ne fonctionne toujours, peu importe ce que la somme des poids jusqu'à.
Saisie de la formule
Maintenant que nous avons notre feuille de calcul mis en place, nous allons ajouter la formule à la cellule B10 (une cellule vide fonctionnera). Comme pour toute formule, commencer en saisissant un signe égal (=).
La première partie de notre formule sera la fonction SUMPRODUCT. Parce que les arguments seront entre parenthèses, allez-y et tapez une parenthèse ouverte:
Ensuite, nous allons ajouter les arguments à la fonction. SUMPRODUCT peut avoir un certain nombre d'arguments, mais il aura généralement deux. Dans notre exemple, le premier argument sera la plage de cellules B2: B9 cellules -les qui contiennent nos qualités:
Le second argument sera la gamme cellule C2: C9 -la cellules qui contiennent les coefficients de pondération. Vous aurez besoin d'utiliser une virgule pour séparer ces deux arguments. Lorsque vous avez terminé, tapez une parenthèse fermée:
Maintenant, nous allons ajouter la deuxième partie de notre formule. Cette partie divisera SUMPRODUCT par la somme des poids. Par la suite, nous allons parler de pourquoi cela est important.
Commencez par taper une barre oblique (/) pour la division, puis tapez la fonction SOMME:
Nous avons besoin d'un seul argument pour la fonction SOMME: la plage de cellules C2: C9. Rappelez-vous de fermer les parenthèses après l'argument:
C'est tout! Lorsque vous appuyez sur Entrée sur votre clavier, Excel calcule la moyenne pondérée. Dans notre exemple, la note finale est 83,6.
Comment ça marche
Regardons chaque partie de cette formule pour voir comment cela fonctionne, en commençant par la fonction SUMPRODUCT. SUMPRODUCT multiplie (trouver le produit) des temps de qualité de son poids de chaque affectation, puis en ajoutant tous les produits ensemble. En d'autres termes, il trouve la somme des produits. qui est l'endroit où il tire son nom. Donc, pour l'attribution 1, il multiplie 85 par 5, et pour le test, il multiplie 83 par 25.
Si vous vous demandez pourquoi les valeurs nécessaires pour être multipliées en premier lieu, pensent de cette façon: Les missions avec un poids plus élevé sont comptés plusieurs fois. Par exemple, Affectation 2 est compté 5 fois, mais l'examen final est compté 45 fois. Ceci est la raison pour laquelle l'examen final a un plus grand impact sur la note finale. Par comparaison, une moyenne « régulière » compterait chaque mission une fois. de sorte que chacun a le même poids.
Si vous pouviez regarder « sous le capot » de la fonction SUMPRODUCT, vous voyez que c'est ce qu'il calcule en fait:
Heureusement, nous n'avons pas d'écrire une longue formule comme ceci parce SUMPRODUCT fait tout cela automatiquement.
En soi, SUMPRODUCT nous donnera un grand nombre: 10450. C'est là la deuxième partie de notre formule est disponible en: / SOMME (C2: C9). Cette partie porte la valeur vers le bas à une gamme de qualité normale, ce qui rend la réponse 83,6.
La deuxième partie de la formule est en fait très utile, car elle permet la formule de corriger automatiquement ses calculs. Rappelez-vous que les poids ne ont pas besoin d'ajouter jusqu'à 100%? En effet, cette partie de la formule prend en charge que pour nous. Par exemple, si l'on augmente un ou plusieurs des poids, la deuxième partie de la formule divise simplement par un plus grand nombre, la ramenant vers le bas pour la bonne réponse. On pourrait même faire les poids beaucoup plus petits, en leur donnant des valeurs telles que 0,5, 2,5, 3,0 et 4,5, et la formule ne fonctionne toujours parfaitement. Assez propre, hein?