Introduction

L’objectif de cette activité est d’approfondir la recopie des formules et d’aborder les points suivants :

  • fonctions : si, estvide, nbval ;
  • techniques : protection des formules.

L’exemple retenu est toujours celui du calcul des moyennes ; le but est d’en créer une nouvelle version de meilleure qualité :

  • éviter les messages “d’erreur” en l’absence de notes ;
  • corriger le calcul de la moyenne en cas d’absence à un devoir ;
  • protéger les formules contre leur effacement involontaire par l’utilisateur.

Étape n°1 - résoudre les erreurs d’affichage

Les formules mises en places ne prennent pas en compte l’absence de saisie et affichent des erreurs. De plus, un élève pourrait ne pas avoir de notes à un devoir, ce qui rend la précédente formule fausse.

  • La fonction ESTVIDE(...) renvoie VRAI si la cellule indiquée en paramètre est vide ; elle va servir ici à additionner le coefficient si l’élève à une note pour le devoir.
  • La fonction NBVAL(...) renvoie le nombre de valeurs saisies dans la plage passée en paramètre.

La formule est complexe, il faut procéder par étape :

  • calculer le total des points dans la cellule F3 (brouillon) : (B3*B$2+C3*C$2+D3*D$2)
  • calculer la somme des coefficients dans la cellule G3 : SI(ESTVIDE(B3);0;B$2) + SI(ESTVIDE(C3);0;C$2) + SI(ESTVIDE(D3);0;D$2) — le coefficient est additionné si l’élève a une note au devoir 
  • la formule de calcul de moyenne en E3 devient : F3/G3 -> SI(NBVAL(B3:D3)=0;"";F3/G3) — on ne calcule pas la moyenne si l’élève n’a aucune note de saisie.

On peut aussi fusionner ces formules de calcul en une seule pour E3 :

=SI(NBVAL(B3:D3)=0;"";
(B3*B$2+C3*C$2+D3*D$2)
/(SI(ESTVIDE(B3);0;B$2)+SI(ESTVIDE(C3);0;C$2)+SI(ESTVIDE(D3);0;D$2)))

En Python, cela aurait la forme suivante :

if nbval(B3:D3) == 0:
    E3 = ""     #n'affiche rien
else:
    points = (B3*B$2+C3*C$2+D3*D$2)
    coeffs = 0
    if estvide(B3):
        coeffs += 0
    else:
        coeffs += B$2
    #idem pour les colonnes C et D
    E3 = points / coeffs

Etape n°2 - cellules de saisie et protection

  • Sélectionner les cellules de saisies : plage A2:D8 et désélectionner (<Ctrl> + clic) A2.
  • Formater les cellules (menu contextuel / clic droit) :
    • choisir un fond gris (par exemple) pour mettre les cellules de saisie en évidence ;
    • décocher la case “Protéger” (l’utilisateur doit pouvoir modifier ces cellules).
  • Protéger la feuille de calcul (sans mot de passe !) : seules les cellules de saisie (dont la protection a été désactivée) restent modifiables.