Comptabiliser les meilleurs résultats 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 :


Résultats statistiques et dénombrements

Nous entamons ici les entraînements Excel du niveau avancé. Cet exercice propose de dépouiller les données issues des résultats d'une compétition d'athlétisme. Différents intervenants ont participé au saut en longueur, au saut en hauteur et à la course du 100 mètres.



Le tableau illustré par la capture ci-dessus livre les performances de chacun. Des lignes de calculs vides attendent les résultats statistiques mentionnés. Il va s'agir d'employer les fonctions dédiées d'Excel. Et nous allons le voir, grâce à elles, les cellules de texte intercalées ne seront pas un problème.

Source et présentation de la problématique
Avant toute chose, nous proposons de récupérer le tableau modèle afin de concentrer l'étude sur l'élaboration des calculs.

• Télécharger le classeur resultats-et-statistiques.xlsx en cliquant sur son lien,
• Double cliquer sur le nom du fichier téléchargé pour l'ouvrir dans Excel,
• Puis, cliquer sur le bouton Activer la modification du bandeau jaune de sécurité,

Ce classeur est constitué d'une unique feuille nommée Statistiques. Les athlètes sont énumérés en colonne B entre les lignes 5 et 12. Leurs résultats dans chaque discipline mentionnée en ligne 4, sont inscrits entre les colonnes C et E pour les lignes respectives.

Pour chacune de ces épreuves, il va s'agir de dresser le bilan des résultats entre les 13 et 18 sous forme de données statistiques. Le dernier calcul en ligne 18 est spécifique. Il s'agit de dénombrer les athlètes qualifiés selon les contraintes respectives à chaque épreuve. Et ces conditions de sélection sont inscrites en ligne 19.

A l'issue des calculs, nous exploiterons des règles de mise en forme conditionnelle de manière à faire ressortir dynamiquement les sportifs les plus vaillants dans chaque discipline.

Calculs statistiques avec Excel
Toutes ces fonctions statistiques sont regroupées et naturellement proposées dans Excel. Elles sont en effet très courantes.

• Tout à fait à droite du ruban Accueil, cliquer sur la flèche du bouton Somme automatique,

Souvenez-vous, ce bouton Somme automatique est matérialisé par la lettre grecque Sigma ressemblant à une lettre M orientée à 90 degrés.



Au clic sur cette flèche, une liste apparaît. Toutes les fonctions usuelles et dérivées de la somme y sont proposées. Quasiment toutes les fonctions nécessaires à la réalisation de cet exercice sont présentes. La moyenne parle d'elle-même. Elle doit agir sur une plage de cellules à désigner, comme nous l'avons appris. Mais c'est aussi le cas pour les autres. La fonction Max extrait la plus grande des valeurs, soit la meilleure performance ici. La fonction Min isole la plus petite des données, soit la moins bonne performance. La fonction NB comptabilise toutes les cellules numériques d'une plage. En d'autres termes, elle permettra de calculer le nombre de participants pour chaque épreuve.

Optimiser les calculs par les références absolues
Il existe une méthode classique pour exploiter chacune de ces fonctions. Comme nous l'avons appris, elle consiste à :

• Sélectionner la cellule du résultat à trouver,
• Choisir la fonction appropriée dans la liste associée à la somme automatique,
• Redéfinir la plage de cellules à la souris, si nécessaire,
• Valider le calcul et le répliquer avec la poignée de la cellule.

Mais il est aussi possible de saisir directement au clavier le nom de la fonction à utiliser. Nous proposons donc de calculer la moyenne dans un premier temps. Et nous allons répliquer le calcul de cette fonction sur toutes les cellules entre les colonnes C et E et entre les lignes 13 et 17. Nous adapterons ensuite le nom de la fonction, selon le calcul statistique demandé.

Pour que ce calcul puisse être répliqué en colonne, les références des cellules impliquées doivent être libres de suivre le mouvement sur la largeur. En revanche, bien que le calcul soit répliqué sur les lignes du dessous, les plages impliquées doivent toujours être bornées entre les lignes 5 et 12. Les références des cellules doivent donc être figées en ligne.

Et comme nous avons atteint le niveau Excel avancé, nous proposons de mettre en oeuvre la technique de calcul la plus efficace avec Excel. Elle consiste à présélectionner toutes les cellules des résultats à livrer.

• Sélectionner la plage de cellules C13:E17,
• Taper le symbole égal (=) pour débuter la formule,

Nous insistons à chaque occasion sur cette notion essentielle permettant la mise en oeuvre de cette méthode professionnelle. Dans une plage de cellules sélectionnées, la première d'entre elles n'est pas grisée. Cela signifie qu'elle est active par défaut. C'est la raison pour laquelle nous allons pouvoir construire le calcul comme s'il lui était dédié, soit sur la première colonne C.

• Saisir le nom de la fonction suivi d'une parenthèse, soit Moyenne(,

La casse importe peu. C'est l'orthographe de la fonction qui est essentielle. Une fonction est reconnue par son nom strict. Vous pouvez donc le saisir indifféremment en minuscules ou en majuscules.

• Sélectionner tous les résultats pour la discipline de la hauteur, soit la plage C5:C12,
• Enfoncer la touche F4 du clavier,

C'est ainsi qu'entrent en jeu les références absolues. En l'état, les cellules impliquées sont complètement figées : $C$5:$C$12. Le dollar devant l'indice de ligne interdit tout déplacement à la verticale. C'est bien ce que nous souhaitons. Le dollar devant l'indice de colonne empêche tout déplacement à l'horizontale. Or les moyennes doivent être calculées pour les autres disciplines, donc sur les autres colonnes.

• Enfoncer une nouvelle fois la touche F4 du clavier,

Cette fois, seuls les dollars devant les indices de ligne de la plage persistent : C$5:C$12. Si nous enfoncions une nouvelle fois cette touche F4, les dollars se déplaceraient devant les indices de colonne. Une dernière impulsion sur la touche les ferait disparaître pour retrouver l'état d'origine de la plage.

• Fermer la parenthèse de la fonction Moyenne,
• Puis, valider nécessairement le calcul par le raccourci clavier CTRL + Entrée,

C'est cette combinaison de touches, sur une plage présélectionnée, qui permet de parachever le processus de cette fabuleuse méthode de calcul.

Comme vous le constatez, la logique est répliquée sur toutes les cellules désignées, sans avoir eu besoin de tirer la poignée du résultat. Les moyennes sont cohérentes pour chaque colonne. A ce stade, elles sont certes reproduites à l'identique sur chaque ligne. C'est volontaire. Comme nous le disions, fort de nos acquis, nous adapterons ensuite ces calculs en modifiant simplement le nom de la fonction à exploiter.

La formule que nous avons bâtie, sur la base de la première cellule de la plage (C13), est la suivante :

=MOYENNE(C$5:C$12)

• Double cliquer sur la cellule E13 pour visualiser sa syntaxe,

En même temps que la syntaxe apparaît en effet, Excel attribue une légère couleur de fond aux cellules impliquées afin de les identifier plus facilement.

La logique ayant été déplacée deux colonnes sur la droite, la formule a suivi ce mouvement sur la largeur pour réaliser la moyenne pour l'épreuve du 100 mètres, soit sur la colonne E :

=MOYENNE(E$5:E$12)

En revanche, comme nous avons judicieusement empêché le déplacement à la verticale, en cellule E17 par exemple, soit quatre lignes plus bas, la syntaxe est strictement identique : =MOYENNE(E$5:E$12). Le calcul devra bien porter sur cette plage mais le nom de la fonction devra changer pour adapter le résultat à la demande. Il est intéressant de constater que les cellules de texte sont ignorées. C'est la raison pour laquelle nous avons désigné l'ensemble de la plage les incluant. Nous procurons ainsi des calculs dynamiques capables d'intégrer ces valeurs en cas de mise à jour.

En ligne 14, nous devons désormais adapter la formule de manière à extraire la meilleure performance dans chaque discipline. La judicieuse méthode que nous avons exploitée est valable en modification. La technique consiste à présélectionner la plage, à engager la modification sur la première cellule et à répliquer la logique par le même raccourci clavier.

• Enfoncer la touche Echap du clavier pour abandonner la modification de formule,
• Sélectionner toutes les meilleures performances à calculer, soit la plage de cellules C14:E14,

Une fois encore, la première cellule (C14) n'est pas grisée. Elle est donc active par défaut dans sa plage.

• Dans la barre de formule, remplacer le nom : Moyenne par Max ,
• Puis, valider nécessairement par le raccourci CTRL + Entrée pour répliquer le calcul,

Toutes les meilleures performances sont effectivement extraites instantanément. Grâce à l'intervention pertinente des références absolues, une simple adaptation du nom de la fonction a suffi pour livrer tous les résultats statistiques demandés. Lorsque la mise en forme conditionnelle sera appliquée, ces résultats de synthèse seront d'autant plus flagrants. Néanmoins pour la discipline du 100 mètres, une inversion fort logique est à constater. La meilleure performance correspond au temps le plus faible. Spécifiquement pour ce résultat, nous devrions donc remplacer la fonction Max par la fonction Min.

Une petite balise active se déclenche à proximité du premier résultat. Il s'agit d'une simple suggestion de la part d'Excel. Ce dernier juge la formule potentiellement incohérente dans la mesure où des cellules adjacentes n'ont pas été intégrées dans le calcul. En effet, les résultats précédents des moyennes ne doivent pas être inclus.

• Cliquer sur la flèche de cette balise active,
• Dans la liste, choisir Ignorer l'erreur,

Si vous double cliquez sur le dernier résultat de cette ligne pour afficher sa syntaxe, soit sur la cellule E14, vous constatez que la plus grande valeur est en effet estimée, non plus sur la colonne C mais sur la colonne E : =MAX(E$5:E$12). La logique est donc parfaitement répliquée et adaptée. Pensez-bien à utiliser la touche Echap du clavier pour abandonner la modification de la formule.

Nous proposons de reproduire la même méthode pour adapter les calculs suivants. En ligne 15, nous devons extraire la moins bonne performance dans chaque discipline. Nous devons donc exploiter la fonction Excel Min.

• Sélectionner les résultats à adapter, soit la plage de cellules C15:E15,
• Dans la barre de formule, remplacer la fonction Moyenne par la fonction Min,
• Puis, valider par CTRL + Entrée pour répliquer la modification sur la plage de cellules,

Les valeurs se mettent instantanément à jour. Les plus mauvaises performances sont extraites. Ces résultats statistiques cumulés sont précieux pour offrir une lecture transversale des informations. La remarque est la même que précédemment pour l'épreuve du 100 mètres. Spécifiquement pour ce résultat, nous devrions intervertir les fonction Min et Max.

La formule est désormais la suivante : =MIN(C$5:C$12). Elle continue bien d'agir entre les lignes 5 et 12 inamovibles. Deux rangées plus loin pour l'épreuve du 100 mètres, les références se sont déplacées pour adapter la synthèse : =MIN(E$5:E$12).

En ligne 16 nous devrons livrer un résultat qui sera d'autant plus intéressant lorsque celui de la ligne 17 sera calculé à son tour. Il s'agit de connaître le nombre d'athlètes ayant participé à l'épreuve. Vous remarquez l'inscription de la mention Absent pour certains sportifs. Nous l'avons évoqué, la fonction Excel Nb comptabilise toutes les cellules numériques dans une plage définie. En d'autres termes, toutes les cellules vides et les cellules de texte sont exclues du décompte.

• Sélectionner les résultats à livrer, soit la plage de cellules C16:E16,
• Dans la barre de formule, remplacer la fonction Moyenne par la fonction Nb,
• Valider par CTRL + Entrée pour répliquer la modification sur les autres cellules,

Vous remarquez que les dénombrements divergent fort logiquement à cause d'absences dans certaines disciplines. Ces résultats numériques sont des entiers. Les décimales ne sont pas nécessaires et alourdissent la présentation. Nous profitons donc de la sélection toujours active sur la plage.

• Dans la section Nombre du ruban Accueil, cliquer 2 fois sur le bouton Réduire les décimales,

Il est intéressant de noter que la signature de ces fonctions est identique. Elles requièrent toutes un seul paramètre. Il s'agit de la plage de cellules sur laquelle l'opération doit être réalisée. Bien sûr, le calcul lui, diffère selon la fonction utilisée.

Et nous allons le voir, pour la suivante, le principe est le même. L'objectif est de comptabiliser le nombre d'inscrits par épreuve. En d'autres termes, sur la plage de cellules, il faut être en mesure de comptabiliser toutes les cellules non vides, textuelles ou numériques. Cette fonction n'est pas proposée dans la liste associée à la somme automatique. Elle se nomme NbVal.

• Sélectionner tous les résultats à trouver, soit la plage de cellules C17:E17,
• Dans la barre de formule, remplacer la fonction Moyenne par la fonction NbVal,
• Valider par CTRL + Entrée pour répliquer la modification sur toutes les cellules,

8 athlètes sont effectivement inscrits dans chaque discipline.

• Cliquer 2 fois sur le bouton Réduire les décimales dans le ruban Accueil,

Si vous supprimez l'une des mentions absent dans le tableau, instantanément, le résultat statistique précédent se met à jour. Nos formules sont parfaitement dynamiques pour proposer un modèle à exploiter.

Dénombrement selon critère
Le dernier calcul en ligne 18 est particulier. Il s'agit bien d'un dénombrement mais conditionnel. Ces conditions sont respectivement inscrites sur la ligne du dessous. Il s'agit de connaître le nombre de sportifs qualifiés dans chaque discipline. Tous les athlètes ayant franchi au moins 1,30 mètre sont qualifiés pour le saut en hauteur. Tous ceux ayant dépassé 5 mètres sont qualifiés pour la longueur. Tous les candidats ayant franchi la ligne en moins de 12,50 secondes sont retenus pour le 100 mètres.

Comme cette fonction doit analyser un critère pour effectuer le décompte, sa signature est nécessairement différente. Cette fonction se nomme Nb.Si. Sa syntaxe est la suivante :

=Nb.Si(Plage_sur_laquelle_compter; Critère_à_verifier)

La plage de cellules pour le dénombrement est identique à celles exploitées jusqu'alors. Le critère doit être dynamique. Il doit donc se nourrir de la contrainte inscrite en ligne 19. Mais cette dernière doit être accompagnée d'un opérateur de comparaison. Il s'agit en effet de savoir si la performance de chaque candidat analysé est inférieure ou supérieure à la contrainte imposée. Cet opérateur doit être inscrit entre guillemets pour être interprété par Excel. Il doit donc être concaténé à la cellule du critère. Souvenez-vous, la concaténation consiste à assembler des informations. Et cet assemblage se réalise par le Et Commercial (&) intercalé entre chaque donnée à joindre. Cet opérateur est situé en haut à gauche du clavier, sur la touche 1.

• Sélectionner tous les résultats à trouver, soit la plage de cellules C18:E18,
• Taper le symbole égal (=) pour débuter le calcul,
• Saisir le nom de la fonction suivi d'une parenthèse, soit Nb.Si(,

Attention, il s'agit bien d'un point (.) entre les deux termes et non d'une virgule. Aussitôt après avoir ouvert la parenthèse, vous constatez qu'une info-bulle apparaît. Elle guide le concepteur pour renseigner les arguments. Et comme nous l'avons évoqué, la plage doit d'abord être désignée. Dès lors le critère pourra être renseigné. Les deux doivent être séparés d'un point-virgule.

• Sélectionner les résultats de la première discipline, soit la plage de cellules C5:C12,

Les références absolues ne sont plus nécessaires ici. En répliquant la formule sur les autres disciplines, les références des cellules doivent bien se déplacer en colonne. Et ce calcul, ne doit pas être reproduit sur d'autres lignes.

• Taper un point-virgule (;) pour passer à l'argument du critère,

Vous remarquez que l'info-bulle continue de vous accompagner. Elle mentionne en gras l'argument qu'il s'agit désormais de renseigner, à savoir le critère.

• Taper l'opérateur Supérieur ou égal entre guillemets, soit : '>=',
• Enfoncer la touche 1 en haut à gauche pour ajouter l'opérateur de concaténation (&),
• Puis, sélectionner la cellule du critère pour assembler sa référence C19 dans la syntaxe,

Là encore les références absolues ne doivent pas entrer en jeu. Les critères doivent s'adapter au fur et à mesure que la formule est répliquée sur la droite. Donc les cellules doivent suivre le déplacement en colonne.

• Fermer la parenthèse de la fonction Nb.Si,
• Puis, valider la formule par le raccourci clavier CTRL + Entrée,
• Cliquer deux fois sur le bouton Réduire les décimales dans le ruban Accueil,

La formule que nous avons bâtie est la suivante : =NB.SI(C5:C12;'>='&C19).



4 athlètes ont donc franchi 1,30 mètre au saut en hauteur. 3 candidats seulement ont été en mesure de franchir 5 mètres au saut en longueur. Ces résultats sont parfaitement cohérents en consultant brièvement les données détaillées du tableau. Pour le saut en longueur la formule s'est donc naturellement adaptée sur la colonne D, pour la plage et le critère : =NB.SI(D5:D12;'>='&D19). Et il en va de même pour l'épreuve du 100 mètres en colonne E : =NB.SI(E5:E12;'>='&E19). Mais pour cette dernière, l'opérateur doit être adapté. En effet, il s'agit de compter les sportifs ayant couru le 100 mètres en moins de 12,5 secondes. En l'état, la formule dénombre tous ceux qui ont couru en plus de 12,5 secondes.

• Sélectionner le dernier résultat, soit la cellule E18,
• Dans la barre de formule, remplacer l'opérateur supérieur (>) par l'opérateur inférieur (<),
• Puis, valider la modification par la touche Entrée,

Le dénombrement conduit au même résultat. L'inégalité n'est pas stricte en effet. L'athlète Damien ayant couru précisément en 12,5 secondes, se retrouve dans les deux catégories. Il est temps justement de vérifier le bon dynamisme des calculs.

• En cellule E19, changer le critère par la valeur 12,4 et valider,

Instantanément, le candidat Damien est exclu et le dénombrement ne conduit plus qu'à 3 sportifs qualifiés.

Repérages visuels dynamiques
Les règles de mise en forme conditionnelle sont précieuses pour simplifier l'interprétation des données d'un tableau. Ces alertes visuelles consistent à repérer des valeurs seuilles par des jeux de couleur dynamiques. Elles varient en fonction du contenu des cellules. Excel propose des règles toutes faites qu'il s'agit simplement d'adapter. Pour chaque discipline, nous souhaiterions par exemple faire ressortir en vert, chaque meilleure performance. Nous n'avons pas pris soin de le faire pour les calculs, mais cette fois nous devons adapter la règle à la discipline. Pour les deux premières épreuves, il s'agit de faire ressortir le meilleur score. Pour le 100 mètres, nous devons mettre en lumière le temps le plus petit.

• Sélectionner les résultats de la première épreuve, soit la plage de cellules C5:C12,
• Dans la section Styles du ruban Accueil, cliquer sur le bouton Mise en forme conditionnelle,
• Dans la liste, pointer sur la rubrique Règles des valeurs de plage Haute/Basse,
• Dans le sous menu qui suit, cliquer sur 10 valeurs les plus élevées,

Nous souhaitons repérer uniquement la meilleure performance et non les 10 meilleures. C'est la raison pour laquelle une boîte de dialogue apparaît. Elle permet d'affiner la règle et de régler la couleur dynamique associée.

• Dans la boîte de dialogue, remplacer le nombre 10 par le chiffre 1,
• A droite, choisir un remplissage vert avec la liste déroulante,
• Puis, valider ces réglages en cliquant sur le bouton Ok,

Instantanément, la meilleure performance est identifiée sur un fond vert.



Et fort logiquement, elle recoupe le résultat statistique fournit par la fonction Max, juste en dessous.

Plutôt que de les refaire et malgré la présence de règles, une mise en forme conditionnelle est un format, certes spécifique. Il peut donc se répliquer. La plage de cellules C5:C12 doit toujours être sélectionnée.

• Cliquer sur l'outil Reproduire la mise en forme, en haut à gauche dans le ruban Accueil,

Nous prélevons ainsi les formats présents sur cette plage avec les règles.

• Sélectionner toutes les performances du saut en longueur, soit la plage D5:D12,

Grâce au pinceau, nous répliquons sur cette plage, tous les attributs prélevés depuis la précédente, y compris ceux de la mise en forme conditionnelle. Et comme vous le remarquez, la meilleure performance est instantanément identifiée. Elle recoupe parfaitement là encore, le résultat livré par la fonction Max pour la même colonne.

Nous l'avons dit, la règle doit être adaptée pour le 100 mètres.

• Sélectionner tous les résultats de l'épreuve, soit la plage de cellules E5:E12,
• Dans le ruban Accueil, cliquer sur le bouton Mise en forme conditionnelle,
• Dans la liste, pointer sur la rubrique Règles des valeurs de plage Haute/Basse,
• Dans le sous menu, cliquer cette fois sur 10 valeurs les moins élevées,
• Dans la boîte de dialogue, remplacer le nombre 10 par le chiffre 1,
• Définir un remplissage vert à l'aide de la liste déroulante,
• Puis, valider cette règle en cliquant sur Ok,

Le dernier Athlète Paul est définitivement le meilleur de tous. C'est encore une fois sa performance qui est mise en lumière. Elle recoupe cette fois le résultat statistique livré par la fonction Min.

• En E12, changer son résultat 11,9 par 13,1 et valider,

Instantanément, le repérage change de cellule et les calculs statistiques se mettent à jour notamment pour la moins bonne performance et le nombre de qualifiés.



Nous avons bâti un modèle parfaitement dynamique dont l'exploitation peut être dérivée. Les références absolues ont une fois de plus joué un rôle prépondérant.