Comment automatiser le suivi des résultats scolaires avec Excel (sans VBA !)

Guide « 108 astuces pour grands débutants sur Excel ! » Offert
Booster votre productivité en téléchargeant gratuitement votre ebook PDF des meilleures astuces pour les débutants et les pros d'Excel
Excel formation - 00 Astuces - 100
 

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 :

This site is protected by reCAPTCHA and the Google Privacy Policy and Terms of Service apply.
 

 

 

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.

Excel formation - 0035-relevés notes - 01

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.

Excel formation - 0035-relevés notes - 02

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 »).

Excel formation - 0035-relevés notes - 03

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 ».

Excel formation - 0035-relevés notes - 04

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.

Excel formation - 0035-relevés notes - 05

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 ».

Excel formation - 0035-relevés notes - 06

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 ».

Excel formation - 0035-relevés notes - 07

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.

Excel formation - 0035-relevés notes - 08

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.

Excel formation - 0035-relevés notes - 09

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.



Articles qui pourraient vous intéresser

Vous ne devinerez jamais comment j’ai boosté mes analyses Excel en 5 minutes !
Comment transformer vos tableaux Excel en tableaux de bord avec des émojis
Comment construire un rapport cliquable à la souris sur Excel
Votre Excel est lent ? Découvrez L'ASTUCE qui va tout changer !
Comment identifier les cellules identiques en 1 clic sur Excel (couleur selon valeur) ?
Comment créer un tableau de bord qui s’actualise et identifie automatiquement les meilleures performances sur Excel !
L’erreur cachée qui plombe vos tableaux Excel (et comment l’éviter)
Comment entourer automatiquement cellules non conformes Excel sans MF conditionnelle ni formule ?
Comment créer un tableau de bord commercial intelligent avec Excel ?
Comment créer un graphique dessiné à la main avec Excel ?
Comment créer un graphique de distribution en 5 minutes chrono ?
L’astuce Excel pour imprimer le tableau que vous voulez en un clic !

Contact - Plan du site - À propos - Contact

© Excelformation.fr 2018-2025 - Excel, simplement

Haut de la page

Excelformation.fr propose de nombreuses formations pratiques et gratuites sur Microsoft Excel : des cours pour apprendre à structurer et analyser les données, des tutos sur les fonctions avancées, les formules et les formules avancées d'Excel, les formules matricielles, les mise-en-formes conditionnelles, les graphiques Excel (xls, xlsx, xlsm,…). Des tutoriels pour apprendre à automatiser les tâches répétitives (via des macro-commandes en VBA) de vos classeurs Excel.