Grâce aux fonctions Valeur Cible et Solveur, Excel est capable de calculer une valeur manquante dans une équation dont on connaît le résultat final.
Explications.
Quelle note dois-je avoir en français pour avoir 12 de moyenne générale ?
Pour répondre à une question de ce genre, il faut faire appel à la fonction Valeur cible d’Excel.
Au lieu de calculer le résultat d’une formule à l’aide de nombres, le tableur raisonne à l’envers : il part du résultat final afin de déterminer l’élément manquant d’une équation.
Pour cela, nous allons demander à Excel de faire varier automatiquement une des cellules de la formule (ici la note de français), jusqu’à ce que la moyenne atteigne la valeur 12.
C’est une simple équation que le tableur peut parfaitement résoudre… à condition que la solution existe, bien entendu.
Calculez la moyenne générale
Dans notre exemple, nous avons noté les matières en colonne A (Maths, Français, Anglais…), et pour chaque discipline, le coefficient en colonne B et la note en colonne C. Les matières occupent les lignes 2 à 8.
Pour calculer la moyenne générale, il faut tenir compte des coefficients.
Nous allons donc utiliser la colonne D pour y stocker les résultats intermédiaires.
En D2, saisissez la formule =B2*C2 et recopiez-la sur les cellules D3 à D8.
En D9, saisissez la formule =SOMME(D2:D8). La moyenne générale, que nous mettrons en C9, est donnée par la formule =D9/SOMME(B2:B8), soit la somme des notes pondérées divisée par la somme des coefficients.
Lancez la fonction Valeur Cible
Dans le ruban Données, cliquez sur Analyse de scénarios, puis sur Valeur cible.
Une fenêtre s’affiche. Dans le champ Cellule à définir, tapez C9 (la cellule qui contient la moyenne générale).
Vous pouvez également cliquer sur la petite flèche rouge qui apparaît à droite du champ de saisie, pointer une cellule (ici C9), puis cliquer de nouveau sur la petite flèche rouge (ce principe vaut pour toutes les boîtes de dialogue Excel).
Ne vous étonnez pas si vous lisez $C$9 dans la cellule au lieu de C9.
De la même façon, dans le champ Valeur à atteindre, tapez 12 (la note à obtenir).
Enfin, dans Cellule à modifier, tapez C3 (celle contenant la note de français). Cliquez sur OK.
Excel vous informe qu’il a trouvé une solution : cliquez sur OK pour qu’elle soit automatiquement écrite dans la cellule C3.
Vous avez maintenant la réponse : avec les notes de notre exemple, il vous faudra un minimum de 7.67 (valeur arrondie) en français pour avoir 12 de moyenne générale.
Ajoutez une condition
L’outil Valeur Cible présente encore une lacune : il ne vérifie pas la cohérence des résultats.
Ainsi, si vous avez de très mauvaises notes dans les matières à gros coefficient (maths et physique dans notre exemple), Excel n’hésitera pas à vous dire qu’il vous faut une note de 45 en français pour atteindre votre objectif de 12 en moyenne générale, un résultat évidemment absurde.
Pour éviter ce défaut, faites appel au Solveur, une version perfectionnée de l’outil Valeur Clble.
Avant d’utiliser ce Solveur pour la première fois, vous devez l’activer (il est installé sur votre PC, mais pas opérationnel). Pour cela, déroulez le menu Fichier, Options.
Dans l’onglet Compléments, sélectionnez Complément Solveur, puis cliquez sur Atteindre. Cochez de nouveau Complément Solveur et validez par OK.
Dans le ruban Données, cliquez sur Solveur. Vous retrouvez les mêmes éléments que dans la Valeur Cible (cellule à définir, cellule variable…).
Mais maintenant, vous pouvez ajouter une condition à respecter (ici, note de français plafonnée à 20).
Cliquez sur Ajouter. Dans Référence de cellule, tapez C3, choisissez <= (inférieur ou égal à…) et tapez la valeur 20. Enfin, cliquez sur OK puis sur Résoudre.
Là, Excel vous préviendra peut-être qu’il ne trouve pas de résultat correct : on n’obtient pas 12 de moyenne avec 3 en maths et 2 en physique.
