Le 14 septembre 2017, nous avons publié une version actualisée de notre Politique de confidentialité. En utilisant video2brain.com vous vous engagez à respecter ces documents mis à jour. Veuillez donc prendre quelques minutes pour les consulter.

Créer un tableau de bord interactif avec Excel 2016

Finaliser le graphique BW contrôlé par des listes déroulantes

TESTEZ LINKEDIN LEARNING GRATUITEMENT ET SANS ENGAGEMENT

Tester maintenant Afficher tous les abonnements
Finalisez la création du graphique et testez les listes déroulantes pour vérifier si tout fonctionne comme prévu.
09:37

Transcription

Donc comme je vous l'ai dit précédemment, on a donc nos trois listes déroulantes. Et maintenant avant de faire un graphique de type boîte à moustache, il nous faut faire en sorte que ces listes déroulantes contrôlent une zone où il y a des formules qui vont rapatrier les statistiques nécessaires pour la boîte à moustache. Alors pour ceci une technique souvent utilisée c'est de créer une feuille qui va être masquée aux collègues et qui va s'appeler " Calculs " et qui va contenir plein de calculs. Et là par exemple, on va mettre ici un petit intitulé " Calculs " " pour B-W plots " donc ça veut dire Box-and-Whisker plot. Et puis typiquement on fait une zone rectangulaire pour chaque type de calcul que l'on aura pour tout notre classeur de tableau de bord. Et là par exemple l'idée c'est d'avoir ici par exemple le " Min ". ici le premier quartile " Q1 ". Ici par exemple la Médiane. Ici le troisième quartile "Q3 ". Et là par exemple le " Max ". Et là ce que l'on veut, c'est ce que les collègues auront choisi via les Contrôles qu'il y a dans " Graphs ". Donc là on va aller dans le premier contrôle puis dire " Cellule liée " et on va prendre la cellule " B3 ". On fait " OK ". Pareil avec le deuxième contrôle. On va dans " Format de contrôle " " Cellule liée : Calculs!$C$3 ". Ensuite on fait OK. On va chercher un autre " Contrôle Format de contrôle Cellules liées " " Calculs!$D$3 ". Maintenant bien évidemment si nos collègues viennent à choisir un article comme ceci, et bien vous allez pouvoir constater bon là évidemment il faudra agrandir un petit peut les listes déroulantes que j'ai les numéros correspondant des articles. Bon maintenant la chose étant c'est que je peux rien en faire directement moi de ces numéros. En fait, j'ai besoin du nom des articles pour faire des statistiques avec des fonctions matricielles ou du matriciel quand même parce que " Min, Médiane, Max et les Quartiles " il n'y a pas de fonction " SI " correspondante. Donc là qu'est-ce que l'on va faire ? On va utiliser la fonction " INDEX ", et puis l'idée c'est que la fonction " INDEX " aille chercher dans la feuille " Menus " qui est ici, comme ceci donc là je sélectionne la colonne " A " avec un point-virgule, le numéro de ligne qui est renvoyé ici par en fait la liste déroulante. Et là on valide par " Entrée ". Et pour le coup j'ai ici " AST Intel 200 ". J'ai ici après quand je tire évidemment pas les bonnes choses parce qu'il fallait mettre un " $ " ici sur la colonne " A ". Donc si je retire. Voilà comme ceci. On a " AST Intel 200, IBM 500, Compaq Presario 100 ". Bon après si on a envie on peut ajuster les colonnes mais de toute façon les collègues ne verront pas cette feuille. On peut contrôler que ça correspond à " AST Intel 200, IBM 500 " c'est tout bon. Maintenant la chose étant c'est qu'il nous faut des formules qui vont chercher les valeurs par rapport à ces statistiques. Donc là ce que l'on va faire c'est qu'on va utiliser les compétences qu'on a acquise lors de la formation statistique où il y avait l'étude des fonctions matricielles avec les fonctions " STAT ". Par exemple on va écrire " =min ", ensuite si on regarde par contre dans " MIN " on se dit : " Tient il y a un "MIN.SI" et ça c'est chouette ! " parce que cela veut dire qu'on n'a pas besoin d'utiliser de fonction matricielle pour ça. Donc on y va, on se dit " =MIN.SI ". Et on va aller chercher dans notre vrai source pas dans le tableau croisé dynamique puisque sa structure pourrait changer donc ce serait dangereux d'en faire une formule. Donc là on va aller chercher dans notre " Liste ". Et dire on veut le minimum du " Prix total avec rabais ". Comme vous pouvez le voir, je sélectionne la colonne " J " où je vais faire un " F4 ". Point-virgule, et ici il me demande la plage de critère. Donc à nouveau dans la liste ça va être la colonne où il y a les " Articles " donc à nouveau un " F4 ". Et ensuite point-virgule le critère lui-même. Donc critère qui est là où il y a les calculs. Et c'est évidemment la cellule avec le nom de l'article en question. On valide après avoir fermé la parenthèse par un " Entrée ". Et là pour le coup je peux maintenant tirer cette formule sur la droite. Après on peut copier cette formule et faire pareil ici pour le Maximum " MAX " comme ceci. On valide par " Entrée " et on tire. Comme ça c'est parfait. Maintenant il y a le premier quartile. Donc on écrit "=quartile ". Et là on se dit " Ils n'ont pas pensé à faire un " QUARTILE.SI ". Et là on va être obligé d'utiliser justement les fonctions matricielles. Là par contre on en n'en aura vraiment pas le choix. Donc "=QUARTILE INCLURE(si(( " on ouvre la parenthèse deux foix puisque c'est une fonction matricielle. Et puis là on va aller chercher la première colonne de critères de vecteur que je vais choisir. Je vais prendre donc comme vecteur les " Article " avec un " F4 ". Et puis on va dire au niveau des " Articles " que l'article doit être égal à ce qu'il y a bien évidemment dans " B4 ". Ensuite on ferme ce vecteur. Et là l'idée c'est de dire point-virgule. Et une fois que l'on a fait ça c'est d'aller chercher l'élément dont on veut calculer le quartile. Donc on sait que c'est la colonne avec les prix totaux avec rabais. Donc on va dans la liste. On prend les prix totaux avec rabais. Ensuite ici je ferme la parenthèse de la fonction " SI ". Et maintenant on fait un point-virgule. Et je dis que je veux ici le premier quartile. On ferme la parenthèse et comme c'est une fonction matricielle on fait " CTRL+SHIFT+ENTER ". Et pour le coup maintenant je tire ceci vers la droite. Et comme c'est une fonction matricielle évidemment pour le coup il ne comprend pas. Donc je vais éditer la fonction qui est là. Voir que la fonction ici bien évidemment elle ne joue pas parce que en fait ici il fallait mettre des " $ ". Donc je mets des " $ " sur " JJ ". Je valide par " CTRL+MAJ+ENTREE " et je retire. Et pour le coup on peut voir que c'est bon. Maintenant on peut ici copier la formule qui est là, la coller pour le quartile, et puis dire que maintenant on veut le troisième quartile et on fait un " CTRL+MAJ+ENTREE ". Et encore une fois on tire. Et une fois que ceci est fait, maintenant l'idée c'est d'aller chercher donc la Médiane. Donc la Médiane on va procéder de même. C'est en réalité le même type de formule parce que si j'écris " =mediane " vous pouvez voir qu'il n'y a pas de " MEDIANE.SI " donc on va aller chercher ici la fonction " mediane ". A nouveau avec le même type de formule simplement la médiane n'a pas de critère comme le 3 ou le 2 pour les quartiles donc on enlève cette partie-là du critère. On valide par " CTRL+SHIFT+ENTREE " étant donné que bien évidemment il s'agit d'une fonction matricielle. Et puis maintenant on enregistre au cas où ça planterait. On sélectionne cette zone-là . On va aller dans " Insérer Graphiques Histogramme ". Et on prend ici ce qu'ils appellent donc le je ne sais pas comment ils l'ont appelé d'ailleurs en français ? Ils ne lui ont pas donné de nom mais bon normalement c'est un graphique boîte à moustache qu'on appelle chez les statisticiens " Box-and-Whisker Plot ". Donc là on le prend et ici on va mettre donc " Box-Whisker Plot ". Ensuite ce graphique là on le coupe. On le mets dans la partie " Graphs ". On peut bien évidemment se remémorer la taille des graphiques précédent. Ici c'est " 8 " et mettre ce graphique aussi à une dimension de " 6 " et de 8 donc " 6 " et ici " 8 ". Maintenant que l'on a ça donc là on s'approche gentiment du but, c'est qu'on peut vérifier si ça marche. Donc là ce qu'on va faire c'est qu'on va se rendre ici dans un des menus. Je vais prendre par exemple " AST Intel 150 " et comme vous pouvez le voir le " Box-and-Whisker Plot " s'adapte automatiquement aux choix que j'effectue. Par contre, c'est clair que le graphique peut être nettement amélioré esthétiquement parlant. Cela va sans dire. Après libre à vous d'aller dans les options du graphique, d'afficher ici les " Etiquettes de données " ou pas. Bref, ce sont les notions de base de graphique. Voilà ce que vous voyez le fait que l'écran bouge à chaque fois, c'est la macro qui s'exécute. Et puis bien évidemment comme je n'ai pas optimisé le code VBA parce que ce n'est pas un cours sur comment faire l'optimisation de VBA ? Parce que ça c'est un cours déjà en soi. Pour le coup vos collègues verront effectivement l'écran bouger un petit peu le temps que la macro fasse le copier-coller. Donc voilà on a réussi ici notre objectif. On a un graphique de " vrai statistique ". C'est déjà plus de statistique que les trois qui sont là au-dessus. Mais ça ne reste quand même pas un graphique de type projectif. Donc ça évidemment c'est un problème qu'on devra résoudre plus tard. C'est déjà un premier élément interactif avec des éléments de formulaires.

Créer un tableau de bord interactif avec Excel 2016

Concevez des tableaux de bord pertinents, qu’ils soient dynamiques ou non, avec Excel. Utilisez pour cela les fonctions, les graphiques, les segments, les macros, etc.

2h42 (27 vidéos)
Aucun commentaire n´est disponible actuellement
 
Logiciel :
Spécial abonnés
Date de parution :18 août 2016

Votre formation est disponible en ligne avec option de téléchargement. Bonne nouvelle : vous ne devez pas choisir entre les deux. Dès que vous achetez une formation, vous disposez des deux options de consultation !

Le téléchargement vous permet de consulter la formation hors ligne et offre une interface plus conviviale. Si vous travaillez sur différents ordinateurs ou que vous ne voulez pas regarder la formation en une seule fois, connectez-vous sur cette page pour consulter en ligne les vidéos de la formation. Nous vous souhaitons un excellent apprentissage avec cette formation vidéo.

N'hésitez pas à nous contacter si vous avez des questions !