Exemple de macros : Calcul d'une 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 :

Cliquer pour agrandir l'image

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.
         
  • 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 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