Comment convertir des notes en lettres avec Excel ?
Si vous travaillez dans le monde de la formation (ou de l’enseignement supérieur), vous avez sans doute déjà été confronté(e) à ce casse-tête épouvantable : convertir une note sur 20 en note lettrée (A, A+, B, B+…).
Effectivement avec le système de notation anglo-saxon qui débarque, les notes lettrées deviennent de plus en plus souvent la norme.
Et là, c’est la panique si vos bulletins et les évaluations de vos formateurs n’ont pas été pensées comme ça.
Rassurez-vous dans cet article, on va voir comment transformer une note sur 20 en note sur 4, puis comment transformer cette note sur 4 en note lettrée.
Le tout est résumé dans une vidéo d’une minute en bas de l’article, ici, on prend le pas-à-pas, pour s’assurer que vous compreniez toute la démarche.
C’est parti !
Créer une table de correspondance
La première chose dont vous allez avoir besoin, c’est d’une table de correspondance.
En d’autres termes, c’est un petit tableau qui fait le lien entre la note sur 4 (ou sur 20 selon votre barème) et son équivalent lettré.
Vous pouvez mettre cette table d’équivalence dans un autre onglet, elle n’a pas besoin d’être visible pour tout le monde puisqu’elle ne servira qu’à faire le calcul d’équivalence.
Dans l’exemple ci-dessus, il faut comprendre qu’une note de 4/4 correspond à la lettre A ; qu’une note entre 3,67 et 3,99 correspond à la lettre A+ et ainsi de suite.
Autrement dit, la note à droite correspond à la limite haute après laquelle on passera à la lettre au-dessus.
Récupérer les notes chiffrées
Dans un autre onglet, vous aurez récupéré les notes chiffrées des apprenants. C’est généralement comme ça que vos intervenants vous transmettront les maquettes de notes en vous laissant le plaisir de faire la conversion par vous-même.
Petite parenthèse ici : si vous souhaitez centraliser toutes les notes depuis plusieurs maquettes (une par module par exemple), on vous recommande très fortement de vous former à Power Query, vous allez gagner un temps fou !
Revenons à nos moutons.
Vos notes seront présentées dans un tableau également qui ressemblera peu ou prou au tableau ci-dessous.
Vos étudiants se trouvent dans une colonne, leur note chiffrée dans une autre, et une troisième colonne, vide pour le moment, dans laquelle nous allons pouvoir faire la conversion vers la note lettrée.
Avec ça, vous êtes prêt à réaliser la conversion. Accrochez-vous une fois, et le problème sera réglée pour toujours.
Etape optionnelle : passer d’une note sur 20 à une note sur 4
Si votre table de correspondance convertit directement les notes sur 20 en lettres, vous pouvez passer à l’étape suivante directement. Pour se compliquer la vie, et parce que l’expérience nous a régulièrement mis face au problème, on a ajouté une petite étape : transformer les notes sur 20 en notes sur 4 pour pouvoir faire la conversion.
Ici, pas besoin de fonctions, un simple calcul mathématique fera l’affaire : pour passer d’une note sur 20 à une note sur 4, il suffit de diviser la note par 5. Oui, oui, rappelez-vous l’époque des bons vieux produits en croix.
Une fois la note sur 4 obtenue, on va enfin pouvoir récupérer la correspondance dans notre tableau conçu à cet égard, et c’est là que la magie de RECHERCHEX entre en jeu.
Utiliser RECHERCHEX pour récupérer la correspondance
Comprendre RECHERCHEX
Vous connaissez peut-être RECHERCHEV qui pourrait faire l’affaire ici, mais on a choisi de faire la manipulation avec RECHERCHEX, sa version la plus puissante (et pas plus compliquée).
Il faut voir RECHERCHEX comme un assistant qui va vous aider à récupérer une information à partir d’une autre. Dans la vraie vie, on pourrait lui demander quelque chose comme : “Va me chercher dans l’armoire le dossier qui correspond au client DUPONT”.
RECHERCHEX prendrait alors le nom du client (valeur_cherchée), partirait dans l’armoire (table_recherche) et récupérerait le dossier (table_renvoyé).
Utiliser RECHERCHEX dans notre exemple
Ici, on va donc demander à notre ami RECHERCHEX d’aller récupérer dans la table de correspondance, la note lettrée qui correspond à la note sur 4 d’un étudiant donné.
Autrement dit :
- La note sur 4, correspond à valeur_cherchée : c’est l’information qui va permettre à la fonction de retrouver la correspondance.
- La table de correspondance correspond à table_recherche : c’est l’endroit où la fonction va aller fouiller pour trouver la correspondance
- La colonne des notes lettrées correspond à table_renvoyé : c’est ici que la fonction va prendre la valeur à ramener.
Notre formule est presque terminée et ressemble à quelque chose comme cela (si vous utilisez le mode Tableau).
=RECHERCHEX((C5/5);Tableau2[Notes];Tableau2[Lettres]…)
Il nous reste une toute dernière étape pour finaliser tout ça.
Utiliser le mode de correspondance
Le problème qu’on a là, c’est qu’il est possible qu’un étudiant est obtenu 3,8/4. Or ce 3,8 n’apparaît pas dans notre table des correspondances. Excel va donc être perdu et ne pas savoir quelle lettre ramenée.
C’est comme d’aller chercher le dossier du client DUPONT si aucun de vos clients ne s’appelle DUPONT…
La bonne nouvelle, c’est qu’Excel permet de gérer cela avec RECHERCHEX grâce à l’argument optionnel [mode_correspondance].
Grâce à ça, on va pouvoir dire à Excel s’il doit aller chercher la note inférieure la plus proche pour prendre sa correspondance respective en utilisant “-1”.
De cette façon, si un étudiant obtient un 3,8/4, Excel va aller chercher la note directement inférieure pour attribuer la lettre qui correspond à l’intervalle dans laquelle se trouve la note.
La formule finale ressemble donc à quelque chose comme :
=RECHERCHEX([@Note]/5;Tableau2[Notes];Tableau2[Lettres];;-1)
Libre à vous de l’adapter à vos maquettes de note et de ne plus jamais vous prendre la tête avec ce genre de manipulation.
Voilà la vidéo qui résume tout l’article en moins d’une minute. N’hésitez pas à la suivre pas-à-pas si nécessaire.
Nos formations pour les écoles et les étudiants
Nous formons régulièrement des équipes dans des établissements d’enseignement supérieur pour gagner du temps au quotidien sur leur utilisation d’Excel en collant au plus proche de leurs besoins réels. Si ça vous intéresse, nous pouvons en discuter ensemble ici.
Egalement, nous formons les étudiants dans le cadre de leur parcours en utilisant une pédagogique ludique et hybride qui a déjà fait ses preuves sur plus de 10 000 apprenants. Nous serions ravis de vous la faire tester. Vous pouvez nous contacter à ce sujet ici.
Et si vous voulez aller plus loin ?
Nous avons également réalisé une vidéo qui reprend les mêmes éléments et qui vous permet de récupérer une éventuelle mention pour chacun des apprenants à partir des notes générées. N’hésitez pas à y jeter un coup d’oeil ici.
