Ex : la moyenne pondérée
Cet billet explique le code d'une macro-commande pour calculer une moyenne pondérée ( coefficientée) dans le contexte décrit par l'illustration ci-dessous :
Les coefficients figurent en ligne 2, ils sont facultatifs et leur valeur par défaut est 1.
La moyenne figure en colonne A est s'obtient par le calcul suivant :
la somme (coefficientée) des notes est à diviser par la somme des coefficients associés aux notes existantes.
Par exemple :
- pour Aline : le calcul est : ( 5*2 + 2,25*1 + 10*2 + 5*1) / ( 2+ 1+ 2+ 1 )
- pour Emilie : le calcul est : ( 2*2 + 12*2 + 6*1) / ( 2 + 2 + 1 )
Sous forme d'une formule dans un tableur cela devient :
- pour Aline : le calcul est : ( D3*D2 + E3*1 + F3*F2 + G3*1 / ( D2+ 1 + F2+ 1)
- pour Emilie : le calcul est : ( D3*D2 + F3*F2 + G3*1 / ( D2+ F2+ 1)
Les celulles E2 et G2 étant vide, il faut utiliser leur valeur 1 par défaut (cf le contexte).
Pour Aline, le calcul peut être obtenu, grâce à la formule matricielle SOMME(D3:G3*D2:G2) à condition de tester l'existence du coefficient.
La forme matricielle de la fonction SI répond à cette problématique :
SI(ESTNUM(D2:G2);D2:G2;1) rend le coefficient s'il est défini et rend 1 sinon.
Pour Aline la formule devient donc :
- pour le numérateur :
- SOMME(SI(ESTNUM(D2:G2);D3:G3*D2:G2;D3:G3))
- pour le dénominateur :
- SOMME(SI(ESTNUM(D2:G2);D2:G2;1))
Pour généraliser ( et l'appliquer à Emilie et à tous les autres ), il faut tester de la même matière la présence des notes. La formule devient donc :
- pour le numérateur : SOMME(SI(ESTNUM(D3:G3);SI(ESTNUM(D2:G2);D3:G3*D2:G2;D3:G3);0))
- la partie SI(ESTNUM(D3:G3);SI(ESTNUM(D2:G2);D3:G3*D2:G2;D3:G3);0) de cette formule rend
- le produit de la note par le coefficient s'ils sont tous deux définis - partie soulignée de la formule
- la note si seule la note est définie ( donc le coefficient vaut 1) - partie en italique de la formule
- 0 s'il n'y a pas de note.
- la partie SI(ESTNUM(D3:G3);SI(ESTNUM(D2:G2);D3:G3*D2:G2;D3:G3);0) de cette formule rend
- pour le dénominateur : SOMME(SI(ESTNUM(D3:G3);SI(ESTNUM(D2:G2);D2:G2;1);0))
- la partie SI(ESTNUM(D3:G3);SI(ESTNUM(D2:G2);D2:G2;1);0) de cette formule rend
- le coefficient si la note et le coefficient sont tous deux définis - partie soulignée de la formule
- 1 si seule la note est définie ( donc le coefficient vaut 1 par défaut ) - partie en italique de la formule
- 0 s'il n'y a pas de note.
- la partie SI(ESTNUM(D3:G3);SI(ESTNUM(D2:G2);D2:G2;1);0) de cette formule rend
La formule qui calcule cette moyenne est donc :
SOMME(SI(ESTNUM(D3:G3);SI(ESTNUM(D2:G2);D3:G3*D2:G2;D3:G3);0))
/
SOMME(SI(ESTNUM(D3:G3);SI(ESTNUM(D2:G2);D2:G2;1);0))
NB1 : dans cette formule, il faudra remplacer le nombre 3 par le numéro de la ligne pour l'adapter à chaque ligne à calculer.
NB2 : pour une recopie, il faudra utiliser des références (semi)absolues.
SOMME(SI(ESTNUM(D3:G3);SI(ESTNUM(D$2:G$2);D3:G3*D$2:G$2;D3:G3);0))
/
SOMME(SI(ESTNUM(D3:G3);SI(ESTNUM(D$2:G$2);D$2:G$2;1);0))
Pour rendre notre formule dynamique et la rendre indépendante du nombre de notes enregistrées ( donc du nombre de colonnes du tableau), il faut remplacer les plages "D3:G3" et "D2:G2" par une formule qui identifie la plage de cellules recevant les notes (ie : le nombre de colonnes utilisées pour saisir des notes). C'est le rôle de la fonction DECALER() et de la variable NbNotes ( qui correspond au nombre maximal de notes saisies)
- DECALER(D2;0;0;1;NbNotes) pour la plage des coefficients
- DECALER(D3;0;0;1;NbNotes) pour la plage des notes saisies
Dans notre exemple NbNotes =4 et s'obtient ainsi :
NbNotes =0
Tant Que i < nb lignes du tableau faire
NbNotesI = NBVAL($i :$i)
Si NbNotes I > NbNotes alors NbNotes <-- NbNotesI
i++
Fin TQ
Finalement la formule est :
SOMME(
SI(
ESTNUM(
DECALER(D3;0;0;1;4)
) ;
SI(
ESTNUM(
DECALER(D2;0;0;1;4)
);
DECALER(D3;0;0;1;4)*DECALER(D2;0;0;1;4);
DECALER(D3;0;0;1;4)
);
0)
)
/
SOMME(
SI(
ESTNUM(
DECALER(D3;0;0;1;4)
);
SI(
ESTNUM(
DECALER(D2;0;0;1;4)
);
DECALER(D2;0;0;1;4);
1);
0)
)
Ouf ... et en conservent à l'esprit les nota bene précédant
