Comment construire une grille d'évaluation avec cases à cocher automatique dans Excel ?
Dans ce tutoriel, je vais vous montrer comment créer une fiche d'évaluation complète dans Excel qui calcule toute seule la note d'une présentation orale — et qui détecte même les erreurs de saisie avant qu'elles ne faussent le résultat.
Nous allons construire une grille professionnelle avec les nouvelles cases à cocher d'Excel 365, un calcul automatique de la note sur 20, une appréciation générée selon le score, une mise en forme conditionnelle pour repérer les erreurs, et deux boutons VBA pour remettre la fiche à zéro et lancer l'impression. Si tu veux générer la structure du tableau ou les formules en quelques secondes, l'outil IA Excelformation.fr peut faire ça directement depuis Excel.
Téléchargement
Vous pouvez télécharger le fichier d'exemple de cet article en cliquant sur le lien suivant :
Tutoriel Vidéo
1. Présentation
Pour illustrer ce tutoriel, nous allons pouvoir utiliser la grille d'évaluation d’une présentation orale d'un étudiant que voici :
Cette grille comporte dix critères d'évaluation avec un coefficient différent selon son importance.
Pour chaque critère, l'évaluateur devra cocher l'un des quatre niveaux : « Insuffisant », « Correct », « Bon » ou « Excellent », et Excel se chargera ensuite de convertir automatiquement ces cases en score pondéré, de calculer la note finale sur 20 et d'afficher une appréciation adaptée.
Les cases à cocher seront à insérer dans les colonnes C à F.
2. Insérer les cases à cocher Excel 365
2.1 Le principe des nouvelles cases à cocher
Dans les anciennes versions d’Excel, pour réaliser ce type de grille, il fallait recourir aux contrôles de formulaire : cases d'option, zones de groupe, cellules liées. C'était fonctionnel, mais la mise en place était laborieuse et peu intuitive pour des utilisateurs occasionnels.
Mais heureusement, sur les versions les plus récentes, nous disposons de vraies cases à cocher natives, directement insérées dans les cellules.
Leur fonctionnement est simple : une case cochée renvoie la valeur VRAI, une case décochée renvoie FAUX.
Ces valeurs booléennes sont utilisables directement dans nos formules, sans passer par des cellules liées intermédiaires.
2.2 Ajouter les cases à cocher
Pour ajouter les cases à cocher, nous commençons par sélectionner la plage C13:F22, qui correspond à l'ensemble des cellules devant accueillir les cases à cocher, soit les quatre niveaux pour dix critères.
Nous nous rendons ensuite dans l'onglet Insertion du ruban, puis nous cliquons sur Case à cocher.
Excel insère automatiquement une case à cocher dans chacune des quarante cellules sélectionnées.
Nous pouvons vérifier immédiatement le fonctionnement : en cochant une case, la cellule prend la valeur VRAI ; en la décochant, elle revient à FAUX.
Ces valeurs sont invisibles pour l'utilisateur final, mais elles sont bien présentes dans les cellules et exploitables par nos formules.
Pour voir la valeur réelle d'une cellule contenant une case à cocher, il suffit de cliquer dessus puis de regarder la barre de formule — nous y lisons bien VRAI ou FAUX.
3. Calculer le nombre de choix par ligne
Les cases à cocher Excel ne se comportent pas comme des boutons radio.
Il est tout à fait possible de cocher plusieurs niveaux sur une même ligne : par exemple « Correct » et « Excellent » simultanément pour le critère « Clarté du propos », ce qui rendrait le calcul de la note incohérent.
Pour détecter ces doublons, nous allons ajouter une colonne « Nb choix » en colonne G. Cette colonne comptera simplement le nombre de cases cochées sur chaque ligne de critère.
En G13, nous saisissons :
=NB.SI(C13:F13;VRAI)
Cette formule compte combien de cellules de la plage C13:F13 contiennent la valeur VRAI, c'est-à-dire combien de cases sont cochées sur cette ligne.
Si le résultat est 0, aucune case n'a été cochée. S'il est 1, la saisie est correcte. S'il dépasse 1, nous avons un problème que nous traiterons ensuite.
Nous recopions ensuite cette formule vers le bas jusqu'en G22, en double-cliquant sur la poignée de recopie en bas à droite de la cellule G13.
Ici toutes les autres lignes sont bien égales à 0, ce qui est tout à fait normal.
4. Calculer le score de chaque critère
4.1 Transformer une case cochée en valeur numérique
Les quatre niveaux d'évaluation correspondent aux valeurs suivantes :
- « Insuffisant » vaut 1,
- « Correct » vaut 2,
- « Bon » vaut 3,
- « Excellent » vaut 4.
Ces quatre valeurs sont positionnées dans cet ordre, de gauche à droite, dans les colonnes C à F.
Nous allons utiliser la fonction SOMMEPROD pour multiplier chaque valeur VRAI/FAUX par son niveau correspondant, puis multiplier le résultat par le coefficient du critère.
En H13, nous saisissons :
=SI(G13=0;"";SI(G13>1;"Erreur";SOMMEPROD(--(C13:F13=VRAI);{1.2.3.4})*B13))
Avant d'aller plus loin, revenons sur cette formule qui peut paraître relativement complexe au premier abord.
Elle se décompose en trois étapes distinctes que nous allons détailler une par une.
Première étape : vérifier si une case a été cochée
La première partie de la formule est SI(G13=0;""). Elle interroge la colonne G, qui contient notre compteur de cases cochées. Si aucune case n'a été cochée sur la ligne 13, alors G13 vaut 0, et la formule renvoie simplement une chaîne vide — c'est-à-dire que la cellule reste vide.
Sans ce contrôle, Excel afficherait « 0 » dans toutes les lignes non encore remplies, ce qui rendrait la note finale complètement fausse avant même que l'évaluateur ait commencé à cocher quoi que ce soit. Ce premier SI est donc essentiel pour que la fiche reste propre pendant la saisie.
Deuxième étape : détecter les erreurs de double saisie
Si G13 n'est pas égal à 0, la formule passe au deuxième contrôle : SI(G13>1;"Erreur"). Si plusieurs cases ont été cochées sur la même ligne — par exemple « Correct » et « Excellent » simultanément — alors G13 vaut 2, 3 ou 4, et la formule affiche le texte « Erreur » à la place d'un résultat.
Ce texte « Erreur » a un double rôle. Il signale visuellement le problème à l'évaluateur, et il est également détecté plus loin dans la formule de la note finale via la fonction NB.SI, ce qui empêche toute note d'être calculée tant que l'erreur n'est pas corrigée. Nous avons donc un véritable filet de sécurité en cascade.
Troisième étape : calculer le score du critère
Lorsque les deux tests ont bien été passés, nous pouvons effectuer le véritable calcul avec SOMMEPROD(--(C13:F13=VRAI);{1.2.3.4})*B13.
Cette partie se décompose elle-même en trois sous-éléments.
La partie C13:F13=VRAI compare chaque cellule de la plage C13 à F13 à la valeur VRAI.
Cette comparaison produit un tableau intermédiaire de quatre valeurs logiques. Par exemple, si seule la case « Bon » (colonne E) est cochée, le tableau produit est : {FAUX;FAUX;VRAI;FAUX}.
Or, Excel ne peut pas multiplier directement des valeurs FAUX/VRAI par des nombres.
C'est pourquoi le double signe moins -- intervient : il force la conversion de ces valeurs logiques en nombres entiers. FAUX devient 0 et VRAI devient 1.
La partie {1.2.3.4} est ce qu'on appelle un tableau constant.
Il représente les notes attribuées à chacun des quatre niveaux d'évaluation, dans l'ordre des colonnes : 1 pour « Insuffisant » (colonne C), 2 pour « Correct » (colonne D), 3 pour « Bon » (colonne E), 4 pour « Excellent » (colonne F).
Ce tableau est saisi directement dans la formule, entre accolades, avec des points (« . ») comme séparateurs.
SOMMEPROD reçoit alors ces deux tableaux de quatre éléments chacun et les multiplie terme à terme avant d'en faire la somme.
En reprenant notre exemple où « Bon » est coché, le calcul devient : (0×1) + (0×2) + (1×3) + (0×4) = 3. Si c'était « Excellent » qui était coché, le tableau de gauche serait {0;0;0;1} et le résultat serait (0×1) + (0×2) + (0×3) + (1×4) = 4.
Ce résultat intermédiaire — le niveau sélectionné — est enfin multiplié par B13, qui contient le coefficient du critère.
Pour le critère « Maîtrise du sujet » dont le coefficient est 5, cocher « Excellent » donnerait 4 × 5 = 20 points. Cocher « Correct » donnerait 2 × 5 = 10 points. C'est ainsi que les coefficients permettent de pondérer l'importance de chaque critère dans la note finale.
Il ne reste plus qu’à recopier la formule jusqu'en H22.
5. Calculer la note finale sur 20
Maintenant, pour calculer la note finale, rendons nous dans la cellule B10, afin d’y saisir la formule suivante :
=SI(NB.SI(H13:H22;"Erreur")>0;"Erreur de saisie";SI(NB.SI(G13:G22;0)>0;"";SOMME(H13:H22)/(4*SOMME(B13:B22))*20))
Il s’agit encore d’une formule qui fonctionne en trois étapes :
Première étape : détecter les lignes en erreur
La formule commence par NB.SI(H13:H22;"Erreur")>0. Elle parcourt toute la colonne H — celle qui contient les scores de chaque critère — et compte combien de cellules affichent le texte « Erreur ». Ce texte est celui que nous avons placé dans la formule précédente lorsque plusieurs cases sont cochées sur une même ligne.
Si ce comptage renvoie un résultat supérieur à 0, cela signifie qu'au moins une ligne de la grille est mal remplie.
Dans ce cas, la formule affiche « Erreur de saisie » à la place de toute note, et s'arrête là.
Il est inutile de calculer quoi que ce soit tant que la grille contient des incohérences : une note produite malgré une erreur de saisie serait une note fausse, et donc une note inutile voire trompeuse.
Deuxième étape : vérifier qu'au moins un critère a été saisi
Si aucune erreur n'est détectée, la formule passe au deuxième filtre : SI(NB.SI(G13:G22;0)>0;"Note(s) manquante(s)";.
Le principe est le même, nous calculons le nombre de valeurs égales à zéro dans la colonne G.
Si cette valeur est supérieur à zéro, nous affichons le message d’erreur : "Note(s) manquante(s)".
Troisième étape : calculer la note sur 20
Si les deux filtres précédents sont passés — pas d'erreur, au moins une case cochée — alors la formule effectue le calcul réel : SOMME(H13:H22)/(4*SOMME(B13:B22))*20
Décomposons cette expression en deux parties.
Le numérateur SOMME(H13:H22) additionne tous les scores pondérés de la colonne H, c'est-à-dire la somme des points effectivement obtenus pour chaque critère rempli.
La somme de tous ces scores partiels donne le total brut de la fiche.
Le dénominateur 4*SOMME(B13:B22) calcule le score maximum théorique possible, en multipliant les coefficients correspondants en colonne B par 4, qui est la valeur maximale attribuable à un critère (le niveau « Excellent »).
Le rapport entre ces deux valeurs, multiplié par 20, ramène le score à une note sur 20 selon la règle de trois classique : points obtenus divisés par points maximum, le tout multiplié par 20.
3. Mettre en évidence les erreurs de saisie
Pour simplifier la gestion des notes, nous allons maintenant souhaiter mettre en évidence les notes présentant des incohérences.
Pour cela, nous sélectionnons toutes les cellules du tableau, en dehors des en-têtes (les cellulesA13 à F22)
Puis, nous nous rendons dans l'onglet Accueil, nous cliquons sur Mise en forme conditionnelle, puis sur Nouvelle règle.
Nous choisissons Utiliser une formule pour déterminer pour quelles cellules le format sera appliqué, et nous saisissons :
=$G13>1
Nous choisissons un fond rouge clair ou orange comme format, puis nous validons en cliquant sur « Ok ».
Le signe $ devant G est essentiel : il fixe la colonne G pour toute la ligne, ce qui permet à la règle de s'appliquer à toutes les cellules de la ligne dès lors que la colonne G de cette ligne dépasse 1. Sans ce $, la règle ne fonctionnerait pas correctement sur les autres colonnes.
Dans ce tutoriel, nous avons construit une fiche d'évaluation opérationnelle pour noter une présentation orale directement dans Excel. Nous avons utilisé les cases à cocher natives d'Excel 365 pour simplifier la saisie, SOMMEPROD pour convertir les niveaux en score pondéré, et une formule de contrôle pour détecter automatiquement les erreurs de double saisie. La mise en forme conditionnelle signale les lignes problématiques en couleur, l'appréciation se génère seule selon la note obtenue, et les deux macros VBA permettent de réinitialiser et d'imprimer la fiche en un clic.
Cette grille peut facilement être adaptée à d'autres contextes : évaluation de stage, entretien, soutenance, jury de concours ou même auto-évaluation — il suffit de remplacer les critères, les coefficients et les seuils d'appréciation.
