http://tssperformance.com/wp-admin/theme-editor.php?file=content-archive-portfolio.php&theme=canvas

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

Bonjour

Je suis sûr que le titre n’est pas clair. Beh j’avoue que c’est pas toujours évident de trouver le bon titre d’un article. Dans tous les cas mon objectif aujourd’hui est de vous apprendre une technique que j’utilise souvent dans la mise en place de mes tableaux de bord dans Excel. J’utilise une zone de liste déroulante pour sélectionner le mois à représenter dans mon tableau de bord.

Prenons le cas simplifié ci-dessous:

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

donnees 

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

 rapport dynamique

 

Pour le faire il faut avoir certaines armes à sa portée:

  • Les fonctions, GRANDE.VALEUR(), INDEX(), DECALER()
  • L’imbrication de ces fonctions doit être « prêt de vous »
  • Petite mise en forme conditionnelle en barre de données;
  • Zone de liste déroulante facile à insérer;
  • Les références relatives et absolues doivent être claires
  • Bien sûr le dernier ingrédient sera un bonne tasse de café…

 

Ok on va pouvoir commencer (ne pas oublier sa tasse de café):

  • Insérer notre zone de liste déroulante avec ses données 

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

    • Sur une de tes feuilles du classeur, insérer les données Janv, Févr… Déc 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

zone nom

 

    • Pour insérer la zone de liste déroulante (je suppose ici qu’on l’insère dans une autre feuille pour le rapport), dans le ruban Développeur/Contrôles/Insérer/ je clique sur Zone de liste déroulante

zone liste deoul

    • Avec la souris , je me mets sur la feuille, je clique et je ne lâche pas pour former la liste déroulante
    • Je clique sur la zone de liste déroulante avec le bouton droit de la souris et je clique format de contrôle
    • Dans la nouvelle boite de dialogue, je la remplis comme ci-dessous:

 Rempli zone

 

A ce niveau ma liste déroulante est prête et à chaque fois que je choisis un élément dessus, la valeur de la cellule A1 change aussi. Bien sûr je vais sur la base que nous sommes sur une autre feuille que celle du tableau initial.

 

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

L’autre difficulté ici est de non seulement retrouver les valeurs du mois mais en plus les classer du plus grand au plus petit. Pour y arriver nous allons utiliser entre autre la fonction DECALER() . Pour en savoir plus sur la puissante fonction DECALER() , cliquer ici, ici, ici et ici .

    • En E8 par exemple, on insèrera 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, on écrira

=GRANDE.VALEUR(DECALER(Feuil1!$A$4:$A$10;;$A$1);1)

Il faut savoir que la fonction GRANDE.VALEUR retrouve la kième plus grande valeur d’une plage de cellules. Dans notre cas, la plage de cellule est DECALER(Feuil1!$A$4:$A$10;;$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.

 

Ok, ok, je pense que j’ai oublié de vous dire de prendre une gorgée de café avant d’avoir les maux de têtes… C’est fait? Alors on continue: 

    • On aura pour finir en E9:

=GRANDE.VALEUR(DECALER(Feuil1!$A$4:$A$10;;Feuil2!$A$1);LIGNE()-8)

Cette formule peut maintenant être incrémenter (ou copier) jusqu’à la cellule E15. Cette formule marche car nous sommes sur la ligne 8, ainsi LIGNE()-8 en s’incrémentant donnera comme résultat: 1, 2, 3, …7

 

Normalement voici où nous en sommes:

enregistrement rapport encours 

 

  • Nous allons maintenant retrouver les noms pour chaque vente

    • En D9 on écrira: =INDEX(Feuil1!$A$4:$A$10;EQUIV(E9;DECALER(Feuil1!$A$4:$A$10;;$A$1);0))

Pour en savoir plus sur la fonction INDEX() et EQUIV() cliquer ici et ici

    • On incrémentera la formule jusqu’en D15 et le résultat sera parfait

 Voici le résultat en cours:

enregistrement rapport encours1 

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

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

mfc

Voici le résultat final

final 

Nous avons enfin fini, une autre tasse de café peut être encore nécessaire.

Pour télécharger le fichier cliquer ici (puis sur télécharger)aller dans Ressources/Téléchargement/ Techniques Tdb/ Rapport dynamique

N’hésitez surtout pas si vous avez des questions, utilisez l’espace commentaire .

N’hésitez pas à apprendre la technique des 3T (tableau, tableaux croisés dynamiques, tableau de bord) pour construire facilement des tableaux de bord dans Excel dans mes ebooks: « Tableau de bord commercial » (cliquer ici) et « Booster votre reporting financier » (cliquer ici).

 

, , , ,

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

  1. Agathe 3 mars 2016 at 11 h 58 min #

    Bonjour!
    Tout d’abord merci pour l’article qui m’a beaucoup aidée pour la mise en place d’un suivi des ventes par catégories de produits !
    Bref! J’ai un souci de doublon dans la fonction!!!
    En effet, plusieurs données chiffrées sont identiques et pour le classement cela prend toujours la première valeur texte et la répète autant de fois qu’il y a la même donnée chiffrée ?
    Y a t-il un moyen d’intégrer à la fonction de prendre la valeur suivante si la valeur de référence est identique ?
    Merci beaucoup pour votre réponse !!

Laisser un commentaire