Notez que cet article a été écrit pour la première fois il y a plus de 3 ans, mais n'est pas nécessairement obsolète.
La fonction moyenne pondérée n’existe pas dans Excel. On peut y arriver en utilisant SOMMEPROD():
|
1 |
=SOMMEPROD(C$5:G$5*C6:G6)/SOMME(C$5:G$5) |
Ceci, en supposant que toutes les valeurs sur lesquelles on veut faire une moyenne sont écrites. Sinon, on peut ruser:
|
1 |
=SOMMEPROD(C$5:G$5*C6:G6)/SOMMEPROD(C$5:G$5*ESTNUM(C6:G6)) |
Et s’il peut y avoir des lignes n’ayant aucune donnée:
|
1 |
=SI(NB(C6:G6)>0; SOMMEPROD(C$5:G$5*C6:G6)/SOMMEPROD(C$5:G$5*ESTNUM(C6:G6)); "Aucune donnée") |
Ces instructions correspondent à des données sur la plage C6:G6 et des coefficients (effectifs) sur la plage C5:D5.
Mais si l’on veut encore plus de critères, cela devient vite incompréhensible ou bien même infaisable. Heureusement, il existe le langage de programmation de Excel, qui permet d’élaborer ses propres fonctions.
La fonction suivante permet de calculer une moyenne pondérée. On accède au module de programmation Visual Basic for Applications (VBA) par Alt+F11. Pour insérer une nouvelle fonction: Insertion/Module
On entre alors, pour calculer une moyenne pondérée:
|
1 2 3 4 5 6 7 8 9 10 11 |
Function MoyennePonderee(Notes As Range, Coeff As Range) As Double Dim n As Integer, i As Integer, Moy As Double, SomCoeff As Double n = Notes.Cells.Count For i = 1 To n If (Not IsEmpty(Notes.Cells(i)) And IsNumeric(Notes.Cells(i).Value)) Then Moy = Moy + Coeff.Cells(i).Value * Notes.Cells(i).Value SomCoeff = SomCoeff + Coeff.Cells(i).Value End If Next i MoyennePonderee = Moy / SomCoeff End Function |
On peut agrémenter la fonction de quelques subtilités. Par exemple, pour enlever la valeur la plus basse ayant le poids le plus important (en effet, deux valeurs basses identiques peuvent avoir des poids différents):
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 |
Function MoyMoinsMin(Notes As Range, Coeff As Range) As Double Dim n As Integer, i As Integer, minNote As Double, iMin As Integer, iCount As Integer, Moy As Double, SomCoeff As Double Dim minNotes() As Double n = Notes.Cells.Count ReDim minNotes(n, 2) As Double minNote = Application.WorksheetFunction.min(Notes) ' On cherche la/les note(s) les plus basses et les coefficients respectifs For i = 1 To n minNotes(i, 1) = 0 minNotes(i, 2) = 0 If (Not IsEmpty(Notes.Cells(i)) And IsNumeric(Notes.Cells(i).Value)) Then iCount = iCount + 1 If (Notes.Cells(i).Value = minNote) Then minNotes(i, 1) = minNote minNotes(i, 2) = Coeff.Cells(i).Value End If End If Next i iMin = 0 ' On trouve l'indice de la note la plus basse ayant le coeffcient le plus élevé si l'élève a toutes les notes For i = 1 To n If (minNotes(i, 1) = minNote And minNotes(i, 2) > maxCoeff And iCount = n) Then iMin = i End If Next i ' On fait la moyenne en enlevant la note la plus basse avec le plus gros coefficient For i = 1 To n If (Not IsEmpty(Notes.Cells(i)) And IsNumeric(Notes.Cells(i).Value) And i <> iMin) Then Moy = Moy + Coeff.Cells(i).Value * Notes.Cells(i).Value SomCoeff = SomCoeff + Coeff.Cells(i).Value End If Next i MoyMoinsMin = Moy / SomCoeff End Function |
Et hop, un calcul de moyennes comme l’on désire, idéal pour faire les moyennes d’une classe en fin de trimestre.
Merci, super utile, depuis le temps que je cherchais cela …