Dans cet exercice, nous vous proposons une révision de nombreuses notions vues tout au long de ce semestre à partir d'un support récupéré d'une autre formation. Au passage, vous découvrirez peut-être quelques nouvelles fonctions. Les parties sur les macros sont laissées à titre indicatifs mais sont hors programme de l'enseignement en MISRH.

Les données

  1. Récupérez un des deux fichiers suivant selon votre préférence :

    Les données de ces classeurs ont été copiées-collées à partir du site mentionné dans la cellule A1 du fichier. Il s'agit des résultats des 5 premières journées des matchs de la ligue 1 de Football en 2018-2019. Des journées complémentaires pourront être ultérieurement ajoutées.

Pré-calculs sur les données

Dans cette partie, nous travaillons dans la feuille Matchs

  1. Observez que dans les colonnes B et D, les noms des équipes ont été doublés.
    Dans les colonnes E et H, calculez les noms d'équipe (équipe 1 en colonne E ; équipe 2 en colonne H) sans doublement.
    • Vous pourrez vous servir, entre autres, de la fonction NBCAR() qui donne le nombre de caractères (la longueur) d'une chaîne de caractères. Une fois que vous avez ce nombre de caractères, vous pouvez le diviser par deux et obtenir ainsi le nombre de caractères à récupérer dans la colonne B ou la colonne D pour répondre à la question respectivement en E et H.
       
  2. En colonnes F, pour chaque ligne, calculez le nombre de buts marqués par l'équipe 1.
    Le résultat sera un nombre (cela est nécessaire pour la suite du TD).
    • Vous aurez besoin de la fonction CNUM() pour transformer en nombre le résultat de l'extraction (sinon dans la suite, certains calculs ne se passeront pas correctement).
    • Remarque. Pour simplifier le problème, vous supposerez que dans un match, une équipe ne peut pas marquer plus de 9 buts (même si cela peut arriver) : ainsi les nombres de buts sont à récupérer en début ou fin de score.
       
  3. En colonnes I, pour chaque ligne, calculez le nombre de buts marqués par l'équipe 2.
     
  4. En colonnes G, pour chaque ligne, calculez le nombre de points marqués par l'équipe 1.
    • Remarque. Les nombres de points marqués par une équipe en cas de match gagné, match perdu, match nul sont stockés en B4, B5 et B6. Vos formules utiliseront une référence à ces cellules (on veut pouvoir faire des simulations avec d'autres valeurs que celles usuellement utilisées).
       
  5. En colonnes J, pour chaque ligne, calculez le nombre de points marqués par l'équipe 2.

Calcul du classement

Nous voulons à présent mettre en place un tableau de bord permettant de déterminer le classement d'une équipe.

  1. Calculez la liste en ordre alphabétique des équipes du championnat en effectuant les manipulations adéquates dans la feuille Tmp.
     
  2. Placez la liste des équipes en colonne C de la feuille Classement (à partir de maintenant, sauf indication contraire, nous travaillons dans cette feuille).
     
  3. En colonne D de cette feuille, pour chaque équipe, comptez le nombre total de buts marqués par l'équipe.
    • Remarque. Vous pourrez utiliser la fonction SOMME.SI().
    • Remarque. Si vous n’avez pas utilisé la fonction CNUM() à la question 2 de la partie « Pré-calculs sur les données », le résultat de la fonction SOMME.SI() sera nul car elle ne trouvera aucun nombre dans la plage calculée (juste des chaînes de caractères représentant des nombres).
       
  4. En colonne E, pour chaque équipe, comptez le nombre total de points marqués par l'équipe dans la saison.
     
  5. Le critère (simplifié) de classement est le nombre de points et, à égalité de nombre de points, le nombre de buts marqués. Notons qu'il peut y avoir des équipes à égalité. Pour pouvoir calculer dynamiquement les classements sans effectuer de tri. Nous attribuons un score à chaque équipe qui est 1000*nb_points + nb_buts. Ce score devient le critère de classement.
    Calculez le score de chaque équipe en colonne F
     
  6. Calculez en colonne G, le rang de chaque équipe.
    • Remarque. Il existe une fonction RANG().

Présentation du classement

  1. Nous avons vu qu'il pouvait y avoir égalité entre 2 équipes. Pour permettre de distinguer les équipes ayant même rang lors de l'affichage, nous rangeons par ordre alphabétique celles ayant le même score. Pour ce faire, nous affinons celui-ci en définissant un « score pour l'affichage » égal à score - numéro de ligne/1000
    En colonne A, calculez ce score d'affichage
    • Remarque. Vous aurez besoin de la fonction LIGNE().
       
  2. En vous basant sur les scores de la colonne A, calculez le rang d'affichage pour chaque équipe en colonne B.
     
  3. Créez une macro pour remplir les colonnes J (rang) et K (équipe) à partir des informations en colonnes G et C (et en utilisant B).
    Votre macro fera les actions suivantes :
    • Positionnement en cellule L1 de la feuille Classement.
    • Copie des informations de la plage B2:G22, puis collage spécial de ces informations à partir de la cellule A1 de la feuille Tmp2.
    • Tri selon les informations de «  Rang pour affichage » (dans la feuille Tmp2)
    • Recopie des informations « Rang » et « Équipe » dans les colonnes J et K de la feuille Classement.
    • Retour dans la cellule L1 de la feuille Classement.
    Ajoutez un bouton pour lancer la macro.
     
  4. Afin de tester ce qui a été mis en place, modifiez le score du match Amiens-Montpellier en journée 2 (cellule C23 de la feuille Matchs) pour qu'il devienne « 0 - 2 » au lieu de « 1 - 2 ».
    Remarque. Le texte « 0 - 2 » sera peut-être interprété comme un nombre.
     
  5. Relancez la macro et vérifiez que les deux équipes classées 18ème sont bien dans l'ordre alphabétique.
     
  6. Effectuez d'autres tests éventuellement, puis revenez aux données initiales (relancez la macro après la dernière modification).
     
  7. Il y a quelques années un match gagné rapportait 2 points. En F4 de la feuille Matchs, modifiez le nombre de points pour un match gagné. Est-ce que tous vos calculs sont encore justes ? Est-ce que cela modifie le classement (regardez dans le milieu du classement) ?

Un peu de mise en forme

  1. Améliorez l'affichage des tableaux en ajoutant un quadrillage.
     
  2. Supprimez l'affichage de la grille du tableur.
     
  3. Ajoutez un formatage conditionnel permettant d'afficher sur fond vert, les informations des colonnes A à G des équipes étant premières au classement (en utilisant le rang calculé en colonne G).
     
  4. Ajoutez un formatage conditionnel permettant d'afficher sur fond rouge, les informations des colonnes A à G des équipes étant dernières au classement (en utilisant le rang calculé en colonne G).
    • Attention ! Quand il y a plusieurs derniers ex-æquo, le rang de ces derniers est le plus grand des rangs.

Autres fonctionnalités dans le tableau de bord

Classement dynamique en fonction du numéro de journée

Nous voulons avoir un tableau de bord permettant d'afficher le classement à l'issue d'une journée sans action via une macro.

Nous allons travailler dans la feuille Classement_dynamique dans laquelle une zone de saisie du nombre de journées à prendre en considération a été mise en place.

Nous commençons par mettre en place le classement avec des formules en se passant d'une macro.

  1. Recopiez les informations et les formules de la plage A3:G22 de la feuille Classement dans la plage A3:G22 de la feuille Classement_dynamique (dans tous les cas, il ne faut pas écraser la ligne 24).
     
  2. Avec une formule, affichez dans la plage K3:K22 les équipes classées dans l'ordre des « Rang pour affichage ».
    • Remarque. Comme on commence en ligne 3, la formule LIGNE()-2 fournit le rang d'affichage de l'équipe à afficher ; à l'aide de la fonction RECHERCHEV(), on peut récupérer le nom de l'équipe.
       
  3. Dans la plage J3:J22, placez le rang (pas le rang d'affichage) de l'équipe. Si ce rang est le même que l'équipe de la ligne au dessus, n'affichez rien.
     
  4. Pour tester que tout fonctionne, re-modifiez le score en C23 de la feuille Matchs en « 0 - 2 ». Observez ce qu'il se passe pour les 18ème. Remettez la valeur initiale du résultat en C23.
     

Nous modifions à présent les formules pour tenir compte du nombre de journées à prendre en compte.
 

  1. En colonne D, modifiez le calcul du nombre de buts pour tenir compte du nombre de journées du championnat à considérer.
    • Remarque. Vous utiliserez la fonction SOMME.SI.ENS() qui permet d'effectuer une somme avec plusieurs critères de filtrage (ici en plus du critère d'équipe, il faut prendre en compte le nombre de journées).
    • Remarque. Pour le critère du nombre de journées, il faut que le numéro de journée des matchs à considérer soit inférieur à la valeur en D24. Vous pourrez exprimer le critère sous la forme : "<="&D24.
       
  2. En colonne E, modifiez le calcul du nombre de points pour tenir compte du nombre de journées du championnat à considérer.
     
  3. Testez en faisant varier le nombre de journées.

Plus de journées

  1. Allez sur le site donné en référence dans la feuille Matchs.
     
  2. Copiez-collez les informations de la sixième journée du championnat
    • Remarque. Vous aurez intérêt à faire le copier-coller dans un autre classeur car il faudra nettoyer (le rendre similaire aux données des autres journées) avant de recopier les données dans le classeur d'exercice. Pour cette phase de nettoyage, nous vous conseillons d'utiliser Open/Libre Office même si usuellement vous utilisez plutôt Microsoft Excel.
       
  3. Adaptez les formules pour prendre en compte cette sixième journée.

Un peu d'anticipation

  1. Sachant qu'il y a 10 matchs par journée et 38 journées, quelles seraient les lignes nécessaires pour stocker l'ensemble des résultats de la saison ?
     
  2. Modifiez les formules pour prendre en compte l'ensemble des journées possibles.
     
  3. Testez en ajoutant les résultats de plusieurs journées.