Suivre les matchs de l’Euro 2020 sur Excel, le fichier !
Aujourd’hui, je vous propose de découvrir un fichier Excel sur un sujet un petit peu plus léger que d’habitude, dans lequel nous allons voir comment suivre le Championnat d’Europe 2020 de football.
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
Le Championnat d’Europe 2020 de football (plus connu en tant qu’ « Euro 2020 ») est la compétition qui voit actuellement s’affronter les plus grandes équipes européennes de football.
Initialement prévu en 2020, ce championnat a débuté le 11 juin, et la finale aura lieu le 11 juillet de cette année.
Ce report étant bien entendu à impacter au Covid.
Autre grande nouveauté de cette édition, les matchs auront lieu dans 11 grandes villes européennes, situées dans autant de pays différents (Rome, Bakou, Copenhague, Saint-Pétersbourg, Amsterdam, Bucarest, Londres, Glasgow, Séville, Budapest et Munich).
Pour pouvoir suivre l’évolution des différentes équipes tout au long de la compétition, je vous ai préparé un petit fichier Excel que vous pourrez télécharger gratuitement en suivant le lien présent tout en haut de cet article.
Comme pour les éditions précédentes, la compétition va s’organiser suivant deux phases : une première phase, dite « phase de poules » composée de 36 matchs, suivi d’une seconde phase dite « phase finale » à élimination directe.
Ce fichier est composé de deux feuilles de calcul, une pour chaque phase de la compétition :
- La phase de poules :
- La phase finale :
2. Phase de poules
2.1. Saisie des résultats
Pour commencer, voyons comment compléter les éléments de la phase de poule.
L’utilisation de cette feuille de calcul est très simple, il suffit de remplir les cases ayant un fond jaune pour que les calculs se mettent à jour.
Nous retrouvons à cet effet un premier tableau dans lequel sont énumérés tous les affrontements de la phase de poule :
Pour chaque match, nous retrouvons les informations utiles pour pouvoir le suivre : date, heure et lieu.
Ensuite, nous retrouvons les noms des pays des équipes avec les fameuses cases jaunes à remplir avec les scores de chaque match.
Maintenant, nous allons simuler les résultats obtenus par les équipes en utilisant la fonction ALEA.ENTRE.BORNES() qui permet d’extraire un nombre entier compris entre deux valeurs :
=ALEA.ENTRE.BORNES(0;4)
Nous saisissons cette formule dans une colonne de chaque colonne.
En effet, étant donné que le tableau est converti sous la forme d’un tableau de données, Excel se chargera ensuite de l’étendre dans toutes les cellules de ces colonnes :
Pour éviter que les résultats simulés ne soient modifiés lors de chaque modification intervenant sur la feuille de calcul, nous allons remplacer ces formules par leur valeur.
Pour cela, nous les sélectionnons, puis nous les copions (en effectuant un clic droit > Copier) :
Puis, sans modifier la sélection, nous allons pouvoir coller les valeurs des cellules (à nouveau clic-droit > Valeurs) :
2.1. Calculs des points par équipe
Maintenant que les scores ont été enregistrés (par simulation comme nous venons de le faire, ou après une saisie manuelle), nous allons pouvoir retrouver le nombre de points obtenus par chaque équipe afin d’établir le classement par poule :
Pour parvenir à cette synthèse, sachez que quelques cellules ont été cachées.
En effet, il suffit de consulter l’en-tête des colonnes pour nous en rendre compte :
Nous pouvons les afficher en sélectionnant les colonnes situées avant et après, puis en effectuant un clic-droit > Afficher :
(Attention, pour éviter toute mauvaise manipulation, le fichier est protégé, le mot de passe pour ôter cette protection est : « euro2020EXCEL »)
Tout d’abord, dans le tableau de saisie des résultats, nous retrouvons trois colonnes :
Dans celles-ci, nous venons saisir les informations suivantes :
- Colonne « Victoires » : Le nom de l’équipe victorieuse
- Colonne « Défaites » : Le nom de l’équipe ayant perdu
- Colonne « Nuls » : En enfin, lorsque les équipes n’ont pas réussi à se départager, la mention « Oui »
Ces trois colonnes vont ensuite être utilisées dans l’autre tableau caché :
Dans celui-ci, nous retrouvons les éléments permettant de calculer la position de chaque équipe par poule.
Pour commencer, nous énumérons le nombre de défaites / victoires / nuls pour chacune d’entre elles, en exploitant justement les informations que nous venons tout juste de découvrir.
Nombre de victoires :
=NB.SI(_baseResultats[Victoire];K10)
Nombre de défaites :
=NB.SI(_baseResultats[Défaite];K10)
Nombre de nuls :
=NB.SI.ENS(C10:C15;K10;I10:I15;"Oui")+NB.SI.ENS(F10:F15;K10;I10:I15;"Oui")
La fonction NB.SI() permet de compter le nombre de cellules répondant à une condition (le nom de l’équipe se trouve-t-il dans la colonne Victoires, ou Défaite).
La fonction NB.SI.ENS() permet quant à elle d’ effectuer plusieurs tests (le nom de l’équipe se trouve-t-il dans une ligne sur laquelle la valeur « Oui » est inscrite dans la colonne Nuls)
Ensuite, nous pouvons calculer le nombre de points obtenus, en suivant la règle suivante :
- Une victoire permet d’obtenir trois points,
- Un match nul permet d’obtenir 1 point,
- Une défaite ne permet pas d’obtenir de point
=L10*3+N10
En cas d’égalité, la règle prévoit d’étoffer le classement en calculant :
- La différence de but (buts marqués – buts encaissés),
- Le nombre de buts marqués
Nous calculons donc les différents éléments correspondants :
Le nombre de buts marqués :
=SOMME.SI(_baseResultats[Équipe 1];K10;_baseResultats[S1])+SOMME.SI(_baseResultats[Équipe 2];K10;_baseResultats[S2])
Le nombre de buts encaissés :
=SOMME.SI(_baseResultats[Équipe 1];K10;_baseResultats[S2])+SOMME.SI(_baseResultats[Équipe 2];K10;_baseResultats[S1])
Et la différence de buts :
=P10-Q10
La fonction SOMME.SI() permet de calculer la somme de plusieurs cellules répondant à une condition.
Il est nécessaire de calculer le nombre de buts marqués ou encaissés pour l’équipe 1, puis pour l’équipe 2.
Nous pouvons maintenant calculer le nombre de points obtenus, en pondérant les trois règles que nous venons de voir (nombre de points, moyenne de buts, buts marqués) :
=O10+(50+R10)/100+(5000+P10)/10000
Et enfin, nous pouvons calculer le nombre de points pondérés obtenus par chaque équipe :
=RANG(S10;$S$10:$S$13)
3. Phase finale
La phase finale commence aux 8e de finales, dans laquelle les 16 meilleures équipes (8 matchs dans lesquels les deux équipes de chaque groupe ainsi que 4 équipes arrivées en troisième placent).
Pour pouvoir renseigner le nom de ces équipes, nous pouvons utiliser le menu déroulant :
Puis, lorsque toutes les équipes ont été sélectionnées, il ne reste plus qu’à saisir le score obtenu sur chaque match :
(ici encore, nous avons mis en place une simulation des scores ;))
Jusqu’à obtenir le nom du vainqueur durant la finale !