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

Préparer un graphique projectif contrôlable via spineurs sur dates

TESTEZ LINKEDIN LEARNING GRATUITEMENT ET SANS ENGAGEMENT

Tester maintenant Afficher tous les abonnements
Suivez un exemple de contrôle du contenu d'un graphique standard projectif via un contrôle de défilement réduit. Vous pourrez ainsi définir la plage de temps à analyser et la plage d'application du modèle projectif en trois étapes.
10:21

Transcription

Nous avons donc utilisé précédemment un type de graphique qui n'était pas géré par les tableaux croisés dynamiques avec une liste déroulante et des fonctions matricielles qui permet déjà d'avoir l'élément interactif que nous avons vu précédemment. Maintenant, on va aussi dans un autre niveau de complication. On va faire un graphique de type projectif avec on va dire une période de projection fixe et sans intervalle de confiance ni intervalle de prédiction. Donc au niveau statistique, on va uniquement se concentrer sur un niveau terminal. Et on veut que ce graphique, l'analyse temporel et la projection temporelle utilise des calculs sur une plage de temps variable. C'est-à-dire un intervalle que l'utilisateur doit pouvoir choisir dynamiquement. Alors pour ceci, on va devoir utiliser plusieurs éléments. On va le faire en plusieurs étapes. D'abord on va faire une étape où on va créer le calendrier qui pourra être utilisé par tous les graphiques de ce type. Parce que vous allez comprendre pourquoi il nous faut un calendrier pour ça d'ici quelques minutes. Et puis il nous faudra également utiliser un élément de formulaire de type liste déroulante pour que les collègues puissent jouer avec les intervalles de date de façon ludique. Et on va devoir également utiliser les zones de noms attachés aux graphiques parce qu'à la base, les graphiques dans un tableur comme Excel, n'ont pas été construit pour faire ce type d'analyse. Alors il y a du travail, donc on va retrousser les manches et on y va. Alors première chose dans la feuille " Calculs " qui est ici, je vais d'abord préparer une zone avec une certaine bordure. Et là on peut déjà mettre une petite légende. On peut mettre " Calendriers " voilà comme ceci. Donc là dans cette zone " Calendriers ", on peut déjà faire la seule colonne qui sera unique à tous les graphiques qui auront ce type d'analyse. C'est la colonne des " Dates ". Et au niveau de la colonne des " Dates ", il vous faut prendre et commencer avec la date la plus ancienne de toutes les listes que vous avez dans tous vos classeurs. Donc là par exemple je sais que j'ai seulement une liste pour l'instant et que cette liste n'a que deux colonnes de date. Donc il suffit que je regarde la date la plus ancienne. Donc c'est " 2004 " au niveau de l'année là aussi. Donc dans les calculs, il suffit que je commence le " 01.01.2004 ", voilà. Et ensuite on tire, en se prenant une certaine marge. Donc la marge en question c'est vraiment à vous de décider. Est-ce que vous prenez dix ans ? Vingt ans ? Trente ans ? C'est à vous de voir. Mais attention cette marge ne correspond pas à la projection bien évidemment. Enfin c'est à peu près du moins que vos collègues utilisent cette marge-là. Maintenant ce qu'il nous faut c'est une colonne par graphique où il y aura des calculs. Donc nous on aimerait par exemple un graphique qui nous permet d'analyser la variation des ventes par date. Donc ça veut dire par jour dans le cas présent. Et ensuite faire une projection sur certaines journées. Donc là par exemple on va mettre " Ventes ". Et normalement on fait une colonne ensuite à droite pour chaque type de graphique. Et là on va utiliser la fonction " SOMME.SI ". Et la fonction " SOMME.SI " nous dit j'ai besoin de la plage du critère. Alors ce n'est pas un problème on va prendre les " Date de Commande " et puis en faire un " F4 ". On peut prendre la " Date de paiement " aussi. D'ailleurs allez prenons la " Date de paiement ", F4 ". Ensuite point-virgule, il veut le critère en question. Donc le critère c'est la date qui est en face puisqu'on veut pour une date donnée le cumul des ventes, point-virgule. Ensuite ce dont il faut faire la somme. Et il est évident que c'est le " Prix total avec rabais ". Bon là comme vous pouvez le voir à nouveau j'attaque la source originale et non pas le tableau croisé dynamique, parce que le tableau croisé dynamique sa structure pourrait éventuellement changer. On ferme la parenthèse donc on a un " 0 " qui est trivial et puis là on double clique. Et si on descend on devrait voir des fois des valeurs. Comme vous pouvez le voir voilà. Evidemment il y a des valeurs à " 0 ", donc ça remet en question notre modèle. A ce moment-là il faudrait faire du regroupement et puis on va faire le choix de rester comme ça mais on pourrait se mettre par semaine ou en mois. Ça c'est à vous de décider en fonction du contexte. Bien évidemment dans le cas présent vu que les ventes sont en jours, faire le même type de calcul avec des intervalles de type mois nécessite une meilleure connaissance des formules Excel. Typiquement les formules matricielles ou les fonctions " SOMME.SI " avec un intervalle de date dans les critères. Il y a plusieurs approches possible. Maintenant que l'on a ça, alors c'est là que ça va commencer à devenir amusant. On va à nouveau aller le plus loin possible dans les préparatifs. Donc ça c'est la macro de mise à jour qui se met en marche, voilà. Il faut que nos collègues aient dans cette feuille " Graphs ", un endroit où ils mettent la date de départ d'analyse. Donc là par exemple je vais faire quoi ? Je vais prendre le " 01.01.2004 " donc qui est dans l'autre feuille. Donc vous allez voir, je veux dire " = ". Et on va prendre ici la date de début plus. Donc là je fais un " + ". Et on retourne dans ici la feuille de " Graphs ". Et je vais prendre par exemple la cellule qui se trouve juste à gauche. Voilà donc là on laisse à nouveau la macro s'exécuter. Maintenant vous allez me dire mais dans quel but ? Donc là à nouveau la macro s'exécute et bien parce qu'on va faire ici via à l'onglet " Développeur Insérer " et ici insérer une " barre de défilement ". On va faire ici une petite barre de défilement qu'on va réduire considérablement, voilà comme ceci. Et puis on va aller dans " Format de contrôle " et puis là " Valeur active: 0 ", " Valeur minimale: 0 ", " Valeur maximale " c'est à vous d'adapter en fonction des contextes. Je vais mettre " 10000 " par " pas de 1 ". Ensuite " Changement de page " ici là il n'y a pas lieu donc je laisse tel qu'elle. " Cellule liée ", je vais prendre la cellule où justement on avait fait le " + " juste tout à l'heure. On valide par " OK ", et maintenant observez ce qui se passe. Là à nouveau c'est la macro qui se met en marche, voilà. Observez ce qui passe quand je vais cliquer ici sur la petite flèche. Vous voyez les dates changent. Donc je peux monter et descendre dans les dates de façon relativement ludique. Après vous allez me dire mais je ne vois pas l'intérêt. Mes collègues pourraient tout à fait saisir la date. C'est juste pour montrer que c'était une option. Ça vous donne une piste et des idées pour d'autres contextes. C'est le seul objectif, n'allez pas chercher plus loin. C'est vrai qu'ils pourraient saisir les dates. Bon là maintenant on se dit on voit le chiffre. Une manière de le cacher c'est soit de le mettre de la même couleur que le fond, soit d'essayer de mettre le Spinner dessus mais ce n'est pas toujours l'idéale. Donc ce que je préfère favoriser c'est de lui donner la même couleur que le fond de la feuille comme ça on le voit pas. Ensuite il nous faut la même chose mais avec une date de fin. Donc là on va bien évidemment prendre la date de fin de notre calendrier. Donc je vais me positionner dans la cellule juste du dessous, et écrire " =max ". On va aller prendre dans " Calculs " ici la colonne de " Dates ". Et on va faire de même en disant que on veut un " + ". On retourne dans notre feuille " Graphs " et je prends ici la cellule qui est juste à gauche. Donc là il y a la macro qui s'exécute encore une fois, voilà. La macro s'exécute encore une fois vu que je viens de cliquer. Maintenant on va tout de suite ici déjà changer le format de nombre, en passant à " Format de date ", voilà. Donc on va prendre ce format-là. Et puis maintenant ici qu'est-ce qu'il me faut ? Il me faut aussi un Spinner. Donc je vais par exemple copier celui-là là et le coller en peu plus bas, voilà. Evidemment comme ceci. Voilà je vais le mettre plutôt là finalement. Et puis cette cellule qui est ici je vais la couper et la coller un peu plus bas, voilà, évidemment là c'est le problème de la macro qui s'exécute à chaque fois. Dès fois il vaut mieux je vous dis honnêtement, mettre la macro en commentaire parce que ça devient relativement énervant. Ce que je vais faire c'est que je vais procéder autrement pour la déplacer en déplaçant carrément la cellule. Je vais le mettre là. Parce qu'il y a en fait avec le couper-coller il y a des soucis. Voilà. Et comme ceci. Bon maintenant ce Spinner que je viens de copier-coller, on fait un " Clic Droit " " Format de contrôle ". On lui change sa " Cellule liée " parce que pour le coup il n'a pas la même. On fait " OK ". Puis maintenant on clique ici sur la cellule avec notre formule, voilà. On remarque qu'elle pointe sur " G15 ". Ce n'est pas ce qu'on veut. On veut que ça pointe sur G17, là où on vient de relier notre Spinner. Et puis notre Spinner on va simplement ici à nouveau mettre la couleur de la police avec la même couleur de fond. Voilà, donc maintenant on peut aussi contrôler la date ici maximale. Pour le coup voilà comme vous pouvez le voir ça marche. Maintenant la chose étant c'est que ce n'est pas forcement l'idéale, et je pense vous devinez pourquoi on a fait un " + " ? En fait, il faudrait plutôt faire un " - ". L'idée c'est que les collègues puissent pas aller plus loin que le maximum. Donc ce qui fait que pour le coup quand ils ajoutent, ils remontent dans le temps. Bon donc voilà là on a fait notre premier préparatif. On a des Spinner liés à un calendrier qui permettent de parcourir la plage du calendrier et vous allez voir ces deux spinner vont définir une zone de noms qui elle va permettre de contrôler le graphique. Donc ça c'est notre première étape. La première étape est terminée.

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 !