Faire un rapport dynamique

Tutoriel Excel

Comment faire un rapport dynamique mensuel grâce à une zone de liste déroulante

Objectif :  Vous apprendre une technique utilisée dans la mise en place de tableaux de bord dans Excel. On utilise une zone de liste déroulante pour sélectionner le mois à représenter dans le tableau de bord.

Dans une feuille de votre classeur Excel, vous avez ce rapport brut :

L’objectif est de pouvoir avoir un rapport dynamique comme celui-ci-dessous :

Pour le faire il faut connaitre :

• Les fonctions : GRANDE.VALEUR(), INDEX(), DECALER()
• L'imbrication de ces fonctions
• La mise en forme conditionnelle en barre de données
• L'insertion d'une Zone de liste déroulante
• Les références relatives et absolues.

1.     Insérer notre liste déroulante avec ses données

Pour insérer la zone de liste déroulante avec les données des mois à l’intérieur, il faudra d’abord créer les données ensuite insérer la liste déroulante et lui « montrer » où récupérer ses données.

• Sur une des feuilles du classeur, insérer les données  Janvier, Février…Décembre par exemple entre B15:B26
• Nommer cette colonne, par exemple «lesmois».  Pour nommer une colonne il suffit de la sélectionner, ensuite se mettre sur Zone de noms et écrire le nouveau nom.

• Pour insérer la zone de liste déroulante, dans le ruban Développeur/Contrôles/Insérer/ cliquer sur le contrôle Zone de liste déroulante 

•  Avec la souris, glisser le contrôle sur la feuille, je clique et je ne lâche pas pour former la liste déroulante.
• Cliquer sur la zone de liste déroulante avec le bouton droit de la souris et cliquer sur Format de contrôle
• Dans la nouvelle boite de dialogue, remplir comme ci-dessous :

A ce niveau la liste déroulante est prête et à chaque fois qu'un élément est choisi, la valeur de la cellule A1 change aussi.

2.     Retrouver les valeurs du mois choisi dans la liste déroulante

Une petite difficulté ici est de retrouver les valeurs du mois mais aussi de les classer du plus grand ou plus petit. Pour y arriver nous allons utiliser la fonction DECALER().

• En E8 par exemple, insérer le mois choisi dans la liste déroulante, pour cela on va écrire la formule : INDEX(Feuil1 !$B$3:$M$3;A1).
• Il faut noter ici que la feuille Feuil1 est celle sur laquelle nous avons nos données initiales.

Nous allons retrouver maintenant les valeurs classées par ordre décroissant :

• En E9 par exemple, écrire :=GRANDE.VALEUR(DECALER(Feuil1!$A$4:$A$7;;$A$1);1)

Il faut savoir que la fonction GRANDE.VALEUR retrouve la kième plus grande valeur d'une place de cellules. Dans notre cas, la plage de cellules est :
        DECALER(Feuil1!$A$4 :$A$7;;$A$1)  et la kième valeur est 1

• Pour pouvoir incrémenter cette formule on pourra remplacer le 1 par cette formule :
  
      LIGNE()-8. La fonction LIGNE() permet de retrouver le numéro de ligne de la cellule active, ici c’est 9.
• On aura pour finir en H16 :
  =GRANDE.VALEUR(DECALER(Feuil1!$A$4:$A$7 ;;Feuil2!$A$1);LIGNE()-8)

Cette formule peut maintenant être incrémentée (ou copier) jusqu’à la cellule H19.

3.     Nous allons maintenant retrouver les noms pour chaque vente

• En G16 on écrira :
  = INDEX(Feuil1!$A$4:$A$7;EQUIV(H16;DECALER(Feuil1 !$A$4:$A$7;;$A$1);0))
• On incrémentera la formule jusqu’en G19 et le résultat sera :

4. Nous allons rajouter les mises en forme conditionnelles en barre de données :

• En I16, écrire =H16
• Incrémenter la formule jusqu’en I19
• Sélectionner I16 :I19
• Dans le ruban, Accueil/Style/Mise en forme conditionnelle/Barre de données/Autres règles…
• Remplir la nouvelle boite de dialogue comme ci-dessous et valider :

Voici le résultat :