Exercice 1 : utilisation du tableau de bord

La meilleure façon de comprendre le fonctionnement du tableau de bord est de récupérer le classeur final (blesses.xlsm) et de le tester.

1) Choisissez l'année 1987 comme année de référence (cellule B1 de la feuille synthese), puis observez que les données (colonnes B et C) et le graphique sont actualisés.

2) Choisissez l'indicateur "blessés graves", puis observez que les données (colonnes B et C) et le graphique (à l'exception de son titre) sont actualisés : un clic sur le bouton Actualiser devient donc nécessaire à cause du titre.

3) Consultez la feuille stockage et vérifiez qu'elle contient les données de Janvier 1984 à Décembre 1990.

4) Ajoutez dans la feuille parametres l'année 1991 (cellule A9) et vérifiez que cette année figure désormais dans la zone de liste (cellule B1 de la feuille synthese).

5) Pour ajouter les données de l'année 1991 dans la feuille stockage et donc pouvoir les faire apparaître dans le tableau de bord, effectuez les manipulations suivantes :

• téléchargez le classeur annee1991.xlsx en le recopiant dans le dossier de votre choix
• indiquez dans la cellule D1 de la feuille parametres le nom du dossier contenant le classeur
• cliquez sur le bouton "Importer classeur" de la feuille parametres, puis indiquez 1991 dans la boîte de dialogue qui apparaît alors.
• vérifiez que les données de l'année 1991 ont été rajoutées dans la feuille stockage
• vérifiez que les données de l'année 1991 sont désormais disponibles dans la feuille synthese

Exercice 2 : réalisation du tableau de bord

L'objectif de cet exercice est de recréer partiellement le tableau de bord en complétant le classeur blesses_0.xlsm.

1) Dans la feuille stockage, ajoutez les formules permettant de calculer la clé (colonne A) qui sera ensuite utilisée par la fonction RechercheV (question 2), ainsi que les formules permettant de calculer le nombre total de blessés (colonne F).

2) Dans les colonnes B et C de la feuille synthese, ajoutez les formules permettant de récupérer les données (voir section consultation).

3) Complétez la feuille synthese avec un bouton de commande nommé Actualiser, associé à une macro permettant de changer le titre de graphique en fonction de l'indicateur sélectionné (voir section "Graphique et formulaire").

4) Complétez le formulaire synthese en proposant plusieurs représentations graphiques :

• la courbe de l'année actuelle avec la courbe de l'année précédente
• la courbe de l'année actuelle (sans la courbe de l'année précédente)
• la courbe de l'évolution entre deux années consécutives (colonne D)
• la courbe des moyennes mobiles (colonne E)

La macro de la question 3 devra donc être complétée.

Exercice 3 : importation d'un classeur ou d'un dossier

Nous repartons du classeur obtenu à l'issue de la question 4 de l'exercice 2 pour automatiser l'ajout de classeurs dans la feuille stockage.

1) Téléchargez le classeur annee1991.xlsx en le recopiant dans le dossier de votre choix.

2) Indiquez dans la cellule D1 de la feuille parametres le nom du dossier contenant le classeur.

3) Démarrez l'enregistreur macro, puis effectuez les opérations suivantes :

• ouvrez le classeur annee1991.xlsx
• recopiez les données de l'année 1991 (Feuil1!A2:D13) dans la feuille stockage en B86
• fermez le classeur annee1991.xlsx
• arrêtez l'enregistreur macro

Voici à titre indicatif le code que j'ai ainsi obtenu, avant de l'adapter à une année quelconque (voir section "automatisation").

Sub Macro3()
    Workbooks.Open Filename:= "D:\accidents\donnees\annee1991.xlsx"
    Sheets("Feuil1").Select
    Range("A2:D13").Select
    Selection.Copy
    Windows("blesses.xlsm").Activate
    Sheets("stockage").Select
    Range("B86").Select
    ActiveSheet.Paste
    Windows("annee1991.xlsx").Activate
    ActiveWorkbook.Close
End Sub

4) Téléchargez et décompressez le fichier donnees.zip, puis précisez dans la cellule D1 de la feuille parametres le nom du dossier contenant les fichiers de données à ajouter dans la feuille stockage.

5) Exécutez la macro parcourirDossier et vérifiez qu'elle affiche le nom de tous les fichiers de ce dossier :

Sub parcourirDossier()
    dossier = Worksheets("parametres").Range("D1") & "\"
    fichier = Dir(dossier)
    While (fichier <> "")
        MsgBox (fichier)
        fichier = Dir() ' fichier suivant
    Wend
End Sub

L'instruction fichier = Dir(dossier) cherche dans le dossier un premier fichier. Ensuite, dans la boucle While (Tantque), le programme affiche le nom du fichier et passe au fichier suivant (fichier = Dir( )). La boucle continue tant qu'il y a un fichier suivant, c'est à dire tant que la variable "fichier" est différente du texte vide (While (fichier <> "")).

Exercice 4 : changement d'architecture

Plutôt que d'avoir un unique graphique dont on peut changer l'indicateur (feuille synthèse), nous allons construire autant de graphiques que d'indicateurs (blessés graves, blessés légers, blessés et nombre d'accidents) en répartissant les calculs sur plusieurs feuilles (synth_graves, synth_legers, synth_blesses et synth_accidents).

1) Ajoutez au classeur blesses_brut.xlsm une feuille "menu" composée des éléments suivants :

Les macros à associer aux boutons seront à réaliser dans les questions 3 et 4.

2) Créez quatre copies de la feuille "synthese" en leur apportant les modifications suivantes :

• Renommez les copies en "synth_graves", "synth_legers", "synth_blesses" et "synth_accidents".
• Supprimez dans chaque copie le formulaire (colonnes F et G de la feuille).
• Supprimez dans chaque copie la zone de liste et ajoutez en B1 la formule =menu!B1.
• Adaptez dans chaque copie les formules dans les plages B2:B13, C2:C13 et E2:E13.

3) Ecrivez une macro, à associer au bouton "Voir feuille", sélectionnant la feuille de calcul correspondant à l'indicateur fixé dans le formulaire.

4) Ecrivez une macro, à associer au bouton "Actualiser", actualisant les quatre graphiques en fonction des choix effectués dans la feuille "menu".