Comment automatiser le suivi des résultats scolaires avec Excel (sans VBA !)
Dans ce tutoriel, je vais vous montrer comment créer un tableau de bord interactif dans Excel pour analyser les résultats scolaires d’élèves. Nous allons découvrir pas à pas comment préparer et organiser nos données, utiliser des fonctions essentielles pour enrichir nos calculs, créer un tableau croisé dynamique pour synthétiser l’information, appliquer une mise en forme conditionnelle afin de mettre en évidence les performances clés et enfin automatiser le tout grâce à des macros.
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 utiliser le tableau suivant dans lequel sont répertoriées les notes obtenues par plusieurs élèves dans différentes matières. Ce tableau comporte quatre colonnes intitulées « Nom », « Matière », « Note » et « Date d'examen ». Chaque ligne représente un enregistrement des résultats d’un élève pour une matière donnée à une date précise.
Nous allons maintenant souhaiter exploiter ces données pour en extraire des statistiques pertinentes, comme la moyenne des notes par élève et par matière, et pour mettre en place des formats conditionnels qui permettront d’identifier facilement les élèves en difficulté ou ceux qui excellent.
Cet exemple sera bien entendu facilement adaptable à d’autres contextes, comme le suivi de performances commerciales ou d’indicateurs de production.
2. Préparation et Organisation des Données
Nous sommes maintenant prêts à passer à la préparation et à l’organisation de nos données, étape cruciale pour garantir la fiabilité de notre analyse. Dans cette première partie, nous allons découvrir comment convertir notre tableau en un objet « Tableau Excel », vérifier les formats, ajouter des colonnes de calcul et préparer le terrain pour la suite.
Pour cela, nous sélectionnons l’une des cellules de la table, puis nous nous rendons dans le menu « Insertion » et cliquons sur « Tableau ».
Dans la boîte de dialogue qui s’ouvre, nous nous assurons de cocher la case « Mon tableau comporte des en-têtes » avant de valider.
Cette manipulation nous permettra de bénéficier d’un filtrage automatique, d’un tri dynamique et d’une mise à jour facile lorsque nous ajouterons de nouvelles lignes de données.
Nous allons également renommer le tableau pour faciliter les références dans les formules. Pour cela, nous sélectionnons une cellule du tableau, puis nous accédons à l’onglet « Création » qui apparaît automatiquement quand nous sélectionnons l’une des cellules d’un tableau structuré afin de modifier le champ « Nom du tableau » et entrons le nom désiré (par exemple « RésultatsScolaires »).
Cela évitera les erreurs et simplifiera la lecture des formules dans notre classeur.
Afin d’enrichir notre analyse, nous allons ajouter une colonne de calcul pour afficher le pourcentage de réussite.
Supposons que la note maximale soit 20. Nous créons une nouvelle colonne intitulée « Pourcentage » et dans la première cellule de cette colonne, nous saisissons la formule suivante :
=(C2/20)*100
L’un des avantages des tableaux structurés, c’est qu’il suffit de saisir une formule dans une cellule pour que celle-ci s’étende automatiquement sur toute la colonne.
Cette opération nous permet d’obtenir directement en pourcentage la performance de chaque élève, ce qui peut être plus parlant dans une analyse rapide.
Nous allons également utiliser la fonction SOMME.SI pour additionner certaines notes selon des critères définis.
Cette fonction SOMME.SI additionne en effet les valeurs d’une plage de cellules lorsque le critère spécifié est rempli. Elle permet de réaliser des calculs conditionnels, ce qui est très utile pour filtrer des données selon un critère particulier que nous pouvons spécifier en argument.
La fonction SOMME.SI s’utilise de la manière suivante :
=SOMME.SI(Plage;Critère;[Somme_plage])
Ici, « Plage » représente la plage de cellules sur laquelle la fonction va vérifier la condition, « Critère » est la condition à remplir et « Somme_plage » (facultatif) définit la plage de cellules à additionner. Si « Somme_plage » est omis, Excel additionnera les valeurs de la « Plage ».
Ainsi, pour connaître la somme des notes des élèves qui ont obtenu une note supérieure ou égale à 12. Si nos notes se trouvent dans la colonne « Note » du tableau (disons de C2 à C11), nous pouvons écrire la formule suivante :
=SOMME.SI(C2:C11;">=12")
Dans cet exemple, nous n’utilisons pas l’argument facultatif « Somme_plage » puisque la somme se fait directement sur la plage vérifiée. Nous pourrons étendre ce principe pour d’autres analyses, par exemple pour additionner les notes d’une matière spécifique en combinant avec d’autres critères via la fonction SOMME.SI.ENS.
3. Création du Tableau Croisé Dynamique (TCD)
Nous passons à la création du tableau croisé dynamique (TCD) qui est un outil puissant pour synthétiser et analyser nos données. Dans cette partie, nous allons voir comment transformer notre tableau de résultats scolaires en un TCD permettant d’extraire des informations clés telles que la moyenne des notes par élève et par matière.
Nous commençons par sélectionner une cellule quelconque dans notre tableau structuré « RésultatsScolaires », puis nous nous rendons dans le menu « Insertion » et nous cliquons sur « Tableau croisé dynamique ».
Une boîte de dialogue s’ouvre et nous demandons de choisir l’emplacement du TCD. Nous optons pour une « Nouvelle feuille de calcul » afin de garder notre feuille de données intacte et de disposer d’un espace dédié à notre synthèse.
Une fois le TCD créé, nous remarquons sur la droite le volet des champs qui nous permet d’organiser nos données.
Nous glissons le champ « Nom » dans la zone « Lignes » afin d’obtenir une liste de tous les élèves.
Nous faisons ensuite glisser le champ « Matière » dans la zone « Colonnes » pour répartir les résultats par matière.
Enfin, nous glissons le champ « Note » dans la zone « Valeurs ».
Par défaut, Excel additionne les notes. Nous souhaitons obtenir la moyenne, nous cliquons donc sur la flèche à côté de « Note » dans la zone « Valeurs » puis nous sélectionnons « Paramètres des champs de valeur ».
Dans la fenêtre qui s’ouvre, nous choisissons « Moyenne » et validons en cliquant sur « Ok ».
Ainsi, notre TCD affiche la moyenne des notes pour chaque élève selon la matière.
Si nous le souhaitons, nous pouvons personnaliser ce TCD pour qu’il soit clair et lisible. Par exemple, nous pouvons renommer « Moyenne de Note » en « Moyenne » en double-cliquant sur l’intitulé dans le TCD.
Nous pouvons également ajouter un filtre par date.
Pour cela, nous glissons le champ « Date d'examen » dans la zone « Filtres », ce qui nous permet de sélectionner une période précise et voir l’évolution des résultats dans le temps.
Cette fonctionnalité est particulièrement utile pour analyser la progression des élèves au fil des examens.
Nous allons aussi modifier la mise en forme du TCD.
Pour cela, dans l’onglet « Création de tableaux croisés dynamiques », nous choisissons un style de rapport « Tabulaire ».
Cette disposition rend la lecture des informations plus intuitive car elle évite des regroupements inutiles.
Attention, le fonctionnement des TCD repose sur l’utilisation d’un cache en mémoire, qui permet d’obtenir des résultats rapides. Nous devons donc penser à actualiser celui-ci chaque fois que nous modifions les données sources.
Pour cela, nous cliquons sur le TCD puis nous effectuons un clic droit sur celui-ci, afin de sélectionner « Actualiser ». Cette étape est indispensable pour que notre tableau de bord reflète toujours les informations les plus récentes.
Maintenant, supposons que nous souhaitions créer un second tableau croisé dynamique, pour analyser les notes successives obtenues par les élèves dans une matières données.
Rassurez-vous, ici il ne sera pas nécessaire de recommencer toutes les étapes.
Il suffira en effet de dupliquer la feuille contenant le TCD en sélectionnant l’onglet de la feuille de calcul, puis en le faisant glisser vers la droite, tout en maintenant la touche [Ctrl] enfoncée.
Puis nous modifions celui-ci en faisant glisser le champ « Matière » au niveau des filtres, et les « Dates d’examen » au niveau des colonnes.
Nous conservons les notes dans les valeurs.
Maintenant, il nous suffit de sélectionner une matière pour retrouver toutes les notes obtenues par les élèves et analyser l’évolution correspondante.
4. Application de la Mise en Forme Conditionnelle
Nous en arrivons maintenant à l’étape de la mise en forme conditionnelle qui va rendre notre TCD plus visuel et intuitif. La mise en forme conditionnelle nous permet d’appliquer automatiquement des formats (couleurs, polices, bordures) aux cellules qui répondent à des critères précis.
Tout d’abord, nous sélectionnons uniquement la plage de cellules du corps du TCD contenant les moyennes, en prenant soin de ne pas inclure les totaux.
Ensuite, nous nous rendons dans le menu « Accueil » et nous cliquons sur « Mise en forme conditionnelle ». Dans le menu déroulant, nous choisissons l’option « Nuances de couleurs ». Excel nous propose alors une échelle de couleurs par défaut, généralement du rouge pour les valeurs les plus faibles, passant par le jaune pour les valeurs médianes, et jusqu’au vert pour les valeurs élevées. Cette échelle crée immédiatement une carte de chaleur visuelle pour nos données.
Si nous souhaitons personnaliser l’échelle, nous cliquons sur « Plus de règles… ». Dans la fenêtre qui s’ouvre, nous avons la possibilité de définir précisément la valeur minimale, la valeur médiane et la valeur maximale. Par exemple, nous pouvons fixer la valeur minimale à la note la plus faible attendue (ou à 0), la valeur médiane à une note correspondant à une performance moyenne, et la valeur maximale à 20. Nous pouvons ensuite choisir les couleurs correspondantes pour chaque point de l’échelle. Cela nous permet d’obtenir une visualisation sur mesure qui correspond exactement à notre grille de notes.
Une fois les paramètres définis, nous validons en cliquant sur « Ok ». La carte de chaleur s’applique alors automatiquement à la plage sélectionnée, chaque cellule prenant une couleur en fonction de sa valeur relative dans l’ensemble des moyennes. Nous vérifions rapidement le rendu en modifiant quelques valeurs dans le tableau source pour être sûrs que l’échelle se met à jour correctement.
Il est important de s’assurer que seules les cellules du corps du TCD soient concernées par cette mise en forme afin d’éviter que les totaux et sous-totaux n’interfèrent avec l’analyse visuelle. Si des totaux apparaissent dans la sélection, nous revenons dans le « Gestionnaire des règles » pour ajuster la plage d’application de la carte de chaleur. Ainsi, seules les données pertinentes sont affichées avec le dégradé de couleurs.