Dans l'épisode d'aujourd'hui, j'ai parlé d'une simple application de carnet de notes dans Excel. Bien que le concept soit simple, il existe plusieurs formules dans le carnet de notes qui peuvent vous tromper. Le carnet de notes ressemble à ceci:
Titres verticaux
Les en-têtes de la ligne 2 sont tournés de leur côté en utilisant Format - Cellules - Alignement. Dans l'onglet Alignement, modifiez l'orientation à 90 degrés.
Ligne possible de points
Il est important d'avoir une ligne sur la feuille de calcul listant le total des points possibles pour chaque devoir. Ne renseignez pas les points possibles pour un devoir tant que vous n’avez pas entré les notes pour ce devoir. Dans la première figure ci-dessus, les points possibles sont dans la ligne 17.
Total des formules de la colonne H
Entrez une fonction = SOMME dans H4 pour totaliser les scores du premier élève. Copiez cette formule dans la colonne H pour chaque élève et pour la ligne de points possibles. Excel modifie automatiquement les références relatives dans la formule pour pointer vers la ligne suivante, etc.
Tricky: formules moyennes dans la colonne I
La formule pour I4 peut sembler simple. Vous pouvez essayer d'entrer =H4/H17, mais cela posera un problème.
Lorsque vous copiez cette formule sur quelques lignes, elle cesse de fonctionner. Ici, à la ligne 6, la formule est devenue =H6/H19. Il est exact qu'Excel a changé H4 en H6, mais vous voulez qu'Excel pointe toujours de manière absolue vers H17 comme diviseur dans le calcul.
Revenez en arrière et modifiez la formule dans I4. Vous pouvez sélectionner I4 et appuyer sur F2, ou simplement double-cliquer sur I4.
Lorsque le point d'insertion se trouve juste après le H17, appuyez sur la touche F4. Excel insère des signes dollar pour changer H17 en $ H $ 17.
Le signe dollar avant le H indique que quelle que soit la direction dans laquelle vous copiez la formule, vous voulez toujours pointer vers la colonne H.
Le signe dollar avant le 17 indique que quelle que soit la direction dans laquelle vous copiez la formule, vous voulez toujours pointer sur la ligne 17.
C'est ce qu'on appelle une référence absolue.
Lorsque vous copiez cette formule à tous les élèves, Excel se divise toujours correctement par le total de points possible à la ligne 17.
Conversion de pourcentages en notes alphabétiques
Vous utiliserez la fonction RECHERCHEV pour convertir les pourcentages en une note alphabétique. Il y a deux façons d'utiliser la fonction RECHERCHEV, et 90% du temps sur ce site, j'utilise la version où Excel doit trouver une correspondance exacte dans la table de recherche. Cette version, cependant, utilise la version de RECHERCHEV dans laquelle la table contient des plages de données.
Dans cette version, la table de recherche doit être triée par ordre décroissant. Comme vous pouvez le voir ci-dessous, cela vous oblige à prendre l'échelle de notation de votre école et à la mettre à l'envers. Dans le tableau ci-dessous, la ligne 4 indique que tout score supérieur à 0 recevra un F.La ligne 5 prend le relais et indique que tout score supérieur à 65% recevra un D.Dans la ligne 6, tout ce qui est à 69% ou plus est un D + . L'idée de base est qu'Excel trouvera le nombre égal ou inférieur au score de l'élève.
- Pour créer la RECHERCHEV dans J4, commencez par
=VLOOKUP( - Le premier argument est le score de l'élève en
I4. - L'argument suivant est la plage avec votre table de recherche. Dans cet exemple, la table est au format
L4:M12. - Vous voulez que la formule pointe toujours vers cette même plage, appuyez donc sur la touche F4 pour la changer en
$L$4:$M$12. - Le dernier argument est le numéro de colonne de votre tableau avec la note en lettre. Dans cet exemple, il s'agit d'un fichier
2. - La formule est
=VLOOKUP(I4,$L$4:$M$12,2).
- Copiez la formule pour tous les élèves.
La version que j'ai utilisée dans l'émission est petite - avec peu d'étudiants et seulement quelques devoirs. Vous pouvez télécharger cette version plus petite pour voir comment les formules fonctionnent. Cliquez avec le bouton droit sur CFHGrades.zip .
