Meilleures notes et meilleurs élèves avec Excel

Pour partager cette vidéo sur les réseaux sociaux ou sur un site, voici son url :


Sujets que vous pourriez aussi aimer :


Synthèse des meilleures notes

En déclinaison de la formation précédente, nous proposons d'axer nos travaux sur les résultats obtenus par des étudiants dans différentes matières. Nous souhaitons dresser un bilan statistique sur les premières meilleures notes obtenues dans chaque matière. Et pour chacune, nous souhaitons extraire les lauréats.



Nos travaux vont porter sur un tableau énumérant les notes obtenues par les élèves dans différentes matières.

Source et présentation de la problématique
Pour mener à bien cette étude, nous devons commencer par récupérer ce tableau.

• Télécharger le classeur synthese-meilleures-notes.xlsx en cliquant sur ce lien,
• Double cliquer sur le fichier réceptionné pour l'ouvrir dans Excel,
• Puis, cliquer sur le bouton Activer la modification du bandeau de sécurité,

Nous réceptionnons un tableau archivant des étudiants et pour chacun d'eux, les notes obtenues dans chaque matière. Entre chacune d'elles, vous observez la présence d'une colonne vide. Elle sera utile en temps voulu, pour repérer les élèves correspondant aux meilleures notes que nous devons synthétiser.



C'est la raison de la présence de tableaux de synthèse sur la droite de la feuille. Ils sont vierges pour l'instant. Ils doivent héberger nos calculs.

Il s'agit tout d'abord d'extraire les trois meilleures notes par matières. Et à ce titre, vous notez de nouveau la présence de petites colonnes intercalées une fois sur deux. Elles sont destinées à effectuer des calculs intermédiaires de repérage. En effet, il s'agira ensuite, dans le tableau du dessous, d'extraire les étudiants associés à ces meilleurs résultats, soit les lauréats de la promotion.

Dans le même ordre d'idée, le troisième tableau suggère quant à lui d'extraire les trois pires résultats par matière. Et par voie de conséquence, le tableau suivant doit restituer les élèves associés.

• En haut à gauche de la feuille Excel, déployer la liste déroulante de la zone Nom,

Comme vous pouvez le voir, chaque rangée verticale de la source de données est nommée en fonction de son titre de colonne. Nous exploiterons ces noms pour simplifier la construction de la syntaxe des formules matricielles.

Extraire les premières meilleures notes
Pour réaliser l'extraction des meilleures notes en fonction de la matière, nous avons besoin d'exploiter les fonctions d'extraction Index et Equiv :

=Index(Tableau_de_recherche; Indice__ligne; Indice_de_colonne)
=Equiv(Valeur_cherchée; Colonne_de_recherche; Mode_de_recherche)

La position en colonne de l'information à extraire est connue. Elle dépend de la matière indiquée en en-tête du tableau de bord. Nous exploiterons donc ces cellules. La position en ligne est plus complexe à trouver. Elle dépend de la note. Et nous souhaitons extraire seulement les trois meilleures par matière. Comme nous l'avons vu dans la formation précédente, nous allons exploiter la fonction Excel Grande.Valeur dans un raisonnement matriciel, en guise d'élément de recherche fourni à la fonction Equiv. Dès lors, nous aurons la ligne et la colonne suffisante pour extraire la donnée depuis la source.

• Sélectionner les 3 meilleures notes à extraire pour la première matière, soit la plage N6:N8,
• Taper le symbole égal (=) pour initier la formule matricielle,

Pour la bonne réplication de la logique d'extraction dans un raisonnement matriciel, l'ensemble des cellules concernées doit en effet être préalablement sélectionné.

• Saisir la fonction d'extraction suivie d'une parenthèse, soit : Index(,

En premier argument, nous devons lui spécifier la colonne dans laquelle se situent les meilleures notes à extraire. Le nom de cette colonne est rappelé en en-tête du tableau de bord, plus précisément en cellule N3. Mais comme vous le savez, pour qu'Excel interprète ce texte comme un plage de cellules, nous devons mentionner sa cellule dans la fonction Indirect.

• Taper la fonction d'interprétation suivie d'une parenthèse, soit : Indirect(,
• Sélectionner la première matière du tableau de bord en cliquant sur sa cellule N3,
• Enfoncer deux fois de suite la touche F4 du clavier, ce qui donne : N$3,

Ainsi, nous figeons la ligne et libérons la colonne. Cette cellule doit en effet servir de référence pour les meilleures notes du dessous. Elle ne doit donc pas bouger en ligne. En revanche, pour les autres matières, elle doit se déplacer afin de les désigner.

• Fermer la parenthèse de la fonction Indirect,
• Taper un point-virgule (;) pour passer dans l'argument de la ligne pour la fonction Index,
• Saisir la fonction de recherche de position suivie d'une parenthèse, soit : Equiv(,
• Saisir la fonction donnant les meilleures valeurs, suivie d'une parenthèse, soit : Grande.Valeur(,
• Saisir de nouveau la fonction d'interprétation suivie d'une parenthèse, soit : Indirect(,
• Désigner de nouveau la première matière en cliquant sur sa cellule N3,
• Enfoncer deux fois la touche F4 pour les mêmes raisons que précédemment, soit : N$3,
• Fermer la parenthèse de la fonction Indirect,

Nous venons donc de stipuler à la fonction Grande.Valeur sur quelle colonne elle devait trouver les meilleurs résultats. Nous devons maintenant lui indiquer les rangs que nous souhaitons extraire. Il s'agit des trois meilleures notes.

• Taper un point-virgule (;) pour passer dans l'argument du rang de la fonction Grande.Valeur,
• Saisir alors la syntaxe suivante : {1;2;3},

Il s'agit d'une astuce que nous connaissons bien désormais. Grâce aux accolades dans le contexte d'un calcul matriciel, nous énumérons les trois rangs souhaités. Dans son utilisation classique, la fonction Grande.Valeur impose de définir un seul de ces rangs.

• Fermer la parenthèse de la fonction Grande.Valeur,
• Taper un point-virgule (;) pour passer dans la colonne de recherche de la fonction Equiv,
• Saisir de nouveau la fonction d'interprétation suivie d'une parenthèse, soit : Indirect(,
• Désigner une fois de plus la première matière en cliquant sur sa cellule N3,
• Puis, enfoncer deux fois de suite la touche F4 du clavier, ce qui donne : N$3,
• Fermer la parenthèse de la fonction Indirect,

Nous venons de spécifier à la fonction Equiv dans quelle colonne elle doit déceler la position des plus grandes valeurs mentionnées dans l'argument précédent.

• Taper un point-virgule suivi du chiffre zéro, soit : ;0, pour réaliser une recherche exacte,
• Fermer la parenthèse de la fonction Equiv,
• Taper un point-virgule (;) pour passer dans la colonne pour la fonction Index,
• Saisir le chiffre 1 pour désigner l'unique colonne de recherche spécifiée en premier paramètre,
• Fermer la parenthèse de la fonction Index,
• Enfin, valider nécessairement la formule par le raccourci clavier CTRL + MAJ + Entrée,

Les premiers résultats tombent. Et comme vous pouvez le voir, ils sont parfaitement cohérents. Les meilleures notes pour la matière des Maths, sont extraites dans un ordre décroissant.

Grâce à ce fameux raccourci clavier, nous avons transformé la formule en formule matricielle. Les accolades encadrant la syntaxe dans la barre de formule en attestent :

{=INDEX(INDIRECT(N$3); EQUIV(GRANDE.VALEUR(INDIRECT(N$3); {1;2;3}); INDIRECT(N$3); 0); 1)}

C'est ainsi que l'ensemble des notes de la colonne sont analysées en comparaison des trois rangs mentionnés pour l'extraction.

Comme nous avons judicieusement figé par les références mixtes, les cellules impliquées dans les conditions de ce calcul, nous pouvons facilement répliquer sa logique pour les autres matières.

• Sélectionner les trois résultats, soit la plage de cellules N6:N8,
• Les copier par le raccourci clavier CTRL + C par exemple,
• Sélectionner le point de départ de la matière suivant, soit la cellule P6,
• Coller la formule matricielle par le raccourci clavier CTRL + V par exemple,

Comme vous le constatez, les trois meilleures notes extraites s'adaptent parfaitement à la matière désignée.

• Sélectionner la cellule R6, puis coller (CTRL + V) de nouveau la formule,
• Enfin, faire de même à partir de la cellule T6 pour le français,

Toutes les meilleures notes catégorisées sont extraites avec succès.

Nous devons répliquer cette logique dans le troisième tableau de synthèse. Mais cette fois, il s'agit d'extraire les trois moins bonnes notes par matière. Dans la syntaxe, il suffit simplement de remplacer la fonction Grande.Valeur par la fonction Petite.Valeur :

{=INDEX(INDIRECT(N$3); EQUIV(PETITE.VALEUR(INDIRECT(N$3); {1;2;3}); INDIRECT(N$3); 0); 1)}



Il ne faut néanmoins pas oublier de présélectionner l'intégralité de la plage N16:N18. Il faut veiller à valider la formule par le raccourci clavier CTRL + MAJ + Entrée. Ensuite, elle peut être répliquée sur les autres colonnes par Copier-Coller.

Repérer et identifier les notes des élèves
Par la suite, pour une synthèse explicite, nous devons extraire les noms des étudiants associés à ces meilleurs et moins bons résultats. Mais comme certaines notes sont identiques, le processus se complique. Par défaut, les fonctions d'extraction s'arrêtent sur la première donnée correspondante trouvée. Pour éviter de sortir le même nom à plusieurs reprises, nous devons différencier explicitement les notes équivalentes. C'est ainsi que nous pourrons restituer l'étudiant associé.

Pour les tableaux de synthèse dans un premier temps, il s'agit de concaténer chaque note extraite avec un numéro de répétition (1 si unique, 2 pour doublon et 3 pour triplé). Nous devons faire de même dans le tableau de données. C'est ainsi que la correspondance sans équivoque avec les noms peut s'établir. Pour réaliser ces calculs de repérage, nous devons exploiter les colonnes intermédiaires.

• Sélectionner le premier point de repérage du tableau de bord, soit la cellule O6,
• Taper le symbole égal (=) pour initier la formule,
• Saisir la fonction conditionnelle suivie d'une parenthèse, soit : Si(,
• Taper la fonction pour recouper les critères, suivie d'une parenthèse, soit : Et(,

En effet, le premier cas consiste à savoir si la note extraite existe déjà deux fois. Nous souhaitons donc vérifier ces deux conditions.

• Désigner la cellule située deux lignes au-dessus de la note en cours, soit la cellule N4,
• Taper le symbole égal (=) pour annoncer le premier critère à honorer,
• Désigner la note en cours d'analyse en tapant ses coordonnées, soit N6,
• Taper un point-virgule (;) pour poursuivre l'énumération des conditions,
• Désigner la cellule placée une ligne au-dessus de la note, soit la cellule N5,
• Taper le symbole égal (=) pour annoncer la seconde condition à honorer,
• Puis, taper les coordonnées de la note en cours, soit N6,
• Fermer la parenthèse de la fonction Et,

Comme ce calcul est destiné à être répliqué sur les lignes du dessous, la double condition vérifiera bien l'égalité avec les précédentes notes extraites. La première est forcément considérée comme l'originale, puisqu'aucune extraction n'existe avant elle.

• Taper un point-virgule (;) pour passer dans la branche Alors de la fonction Si,
• Taper les coordonnées de la note en cours, soit N6,
• Inscrire le caractère de concaténation, suivi du chiffre 3, soit : &3,

Comme nous l'avons dit, si le double critère est vérifié, il s'agit d'un triplé. C'est ainsi que nous l'identifions. Nous devons maintenant vérifier le cas où la note n'est répétée qu'une seule fois.

• Taper un point-virgule (;) pour passer dans la branche Sinon de la fonction Si,
• Saisir de nouveau la fonction conditionnelle suivie d'une parenthèse, soit : Si(,
• Sélectionner la cellule située une ligne au-dessus de la note, soit N5,
• Taper le symbole égal (=) pour annoncer la condition à honorer,
• Inscrire les coordonnées de la note en cours, soit N6,
• Taper un point-virgule (;) pour passer dans la branche Alors de cette seconde fonction Si,
• Inscrire de nouveau les coordonnées de la note en cours, soit N6,
• Puis, inscrire le caractère de concaténation suivi du chiffre 2, soit : &2,

C'est ainsi que nous identifions les simples répétitions.

• Taper un point-virgule (;) pour passer dans la branche Sinon de la seconde fonction Si,
• Inscrire les coordonnées de la note en cours, soit N6,
• Puis, taper le caractère de concaténation suivi du chiffre 1, soit : &1,

Lorsque les conditions précédentes ne sont pas honorées, nous en déduisons que la note est bien l'originale. Nous lui greffons le chiffre 1 pour la repérer explicitement comme telle.

• Fermer la parenthèse de la seconde fonction Si,
• Puis, fermer la parenthèse de la première fonction Si,
• Enfin, valider la formule par le raccourci clavier CTRL + Entrée,

La première note est fort logiquement identifiée comme l'originale.

• Double cliquer sur la poignée du résultat pour répliquer la logique du calcul,
• Copier la sélection puis la coller sur chaque première cellule de colonne intermédiaire,

Nous avons bien sûr répliqué ce calcul sur le second tableau pour les plus mauvaises notes. Et comme vous pouvez le voir, toutes les répétitions sont désormais explicitement et distinctement identifiées.

La syntaxe complète de la formule que nous avons construite est la suivante :

=SI(ET(N4=N6; N5=N6); N6&3; SI(N5=N6; N6&2; N6&1))

Ce même type d'identification doit exister dans la source de données. C'est ainsi que la correspondance pourra être établie afin d'extraire les noms des étudiants associés. Le procédé diffère en revanche et il est plus simple. Il suffit de dénombrer la présence de la note en cours sur une plage de cellules qui évolue en même temps que le calcul est répliqué. La première note sera ainsi considérée comme l'originale et les suivantes, comme des répétitions. Pour réaliser ce dénombrement, nous devons exploiter la fonction Excel Nb.Si.

• Sélectionner la première cellule de repérage, soit F4,
• Taper et valider la formule suivante : =E4&NB.SI(E$4:E4; E4),

L'astuce réside dans la plage de cellules passée en premier paramètre de la fonction Nb.Si. Sa borne supérieure est seulement figée en ligne. Sa borne inférieure est totalement libre (E$4:E4). Le décompte du critère passé en deuxième paramètre (E4) s'effectue donc sur une plage qui évolue en même temps que le calcul est répliqué sur les lignes du dessous. Et comme les colonnes ne sont pas figées, il est valable pour les autres matières.

• Double cliquer sur la poignée du résultat pour étendre la logique sur la hauteur du tableau,
• Copier la sélection (CTRL + C),
• Puis, la coller (CTRL + V) sur les cellules H4, J4 et L4,

Tous les marquages sont désormais opérationnels. Dès que répétition est décelée, la note est distinctement identifiée.

Extraire les noms des élèves
Pour réaliser l'extraction des noms des étudiants associés aux notes extraites dans le tableau de bord, nous devons chercher l'équivalence des notes ainsi marquées. Les fonctions Index et Equiv sont essentielles.

=Index(Tableau_de_recherche; indice_ligne; indice_colonne)

La fonction Index permet d'extraire une information située au croisement d'une ligne et d'une colonne. La colonne est fixe. Il s'agit de la colonne C énumérant les noms des étudiants. La ligne est variable en revanche. Elle dépend de la position de la note marquée dans la source de donnée. Pour la trouver, nous devons exploiter la fonction Equiv.

=Equiv(Valeur_cherchée; Colonne_de_recherche; Mode_de_recherche)

En guise de valeur cherchée, nous devons lui indiquer la note marquée issue du tableau de bord. La colonne de recherche est la colonne intermédiaire de la matière considérée. Il en résultera la position en ligne du nom de l'étudiant à extraire.

• Sélectionner la case du premier meilleur étudiant, soit la cellule N11,
• Taper le symbole égal (=) pour initier la formule,
• Saisir la fonction d'extraction suivie d'une parenthèse, soit : Index(,
• Désigner l'intégralité de la colonne des noms par ses coordonnées, soit : C:C,
• Enfoncer la touche F4 du clavier pour la figer, ce qui donne : $C:$C,

En effet, cette recherche doit être vraie pour les autres matières. Malgré les déplacements, les noms sont immuablement référencés en colonne C.

• Taper un point-virgule (;) pour passer dans l'argument de l'indice de ligne,
• Saisir la fonction trouvant la position de recherche, suivie d'une parenthèse, soit : Equiv(,
• Désigner la marque de la première meilleure note en cliquant sur sa cellule O6,
• Taper un point-virgule (;) pour passer dans l'argument de la colonne de recherche,
• Désigner l'intégralité de la colonne des premiers repères, soit : F:F,
• Taper un point-virgule suivi du chiffre zéro, soit : ;0, pour une recherche exacte,
• Fermer la parenthèse de la fonction Equiv,
• Taper un point-virgule (;) pour passer dans l'argument de la colonne de la fonction Index,
• Saisir le chiffre 1 pour pointer sur l'unique colonne désignée en argument de recherche,
• Fermer la parenthèse de la fonction Index,
• Enfin, valider la formule par le raccourci clavier CTRL + Entrée,

Le premier résultat tombe. En consultant la base de données, vous constatez qu'il est tout à fait cohérent.

• Tirer la poignée du résultat sur les deux lignes du dessous,
• Puis, copier la sélection sur les autres colonnes, y compris pour les moins bons étudiants,

Certains élèves font à la fois partie des meilleurs étudiants dans une matière et des moins bons dans une autre.