Créer un tableau de bord interactif avec Excel 2016

Affiner le graphique de projection contrôlé via les spineurs sur dates

Testez gratuitement nos 1325 formations

pendant 10 jours !

Tester maintenant Afficher tous les abonnements
Finalisez la construction du graphique de projection interactif en ajoutant la régression linéaire et en testant rapidement le fonctionnement.
07:06

Transcription

Nous allons maintenant faire les préparatifs pour ce graphique. Première chose on va ici mettre par exemple un " Titre ". On pourrait mettre pour être objectif " Project (naive) des ventes ". On va faire un " Clic Droit " sur le graphe et dire " Sélectionner des données ". On va modifier la " Série 1 " ici. On va mettre " Ventes " même si de toute façon elle ne se verra pas. Du moins la légende de la série ne se verra pas. Ensuite au niveau de la " Valeurs de la série des abscisses X " on va mettre donc la feuille " Calculs " " AST_Dates " c'est-ce qu'on vient de faire précédemment. Et pour les ordonnées pareil on va chercher dans la feuille " Calculs " " AST_Values ". On valide par " OK ". Et là on peut voir qu'on a un graphe. Maintenant bon évidemment il faudra adapter un petit peu tout ça. L'axe ici des Y, il ne faudrait pas qu'il y ait de " Minimum " qui est en dessous de 0 donc on va le fixer à " 0 ". Ensuite ici les dates on peut mettre dans une police évidemment minimale. Maintenant on peut déplacer ce graphique comme il nous convient. Ici, et ensuite on peut le mettre à une dimension qui est du " type 6 par 7 ". Si on s'inspire un petit peu de ce qu'il y avait à gauche, qui faisait de 6 de hauteur aussi. Alors évidemment après idéalement il faut les aligner les graphiques, " Aligner en haut " comme ceci. Et puis au niveau de la largeur on va ici aligner ici celui de droite, bien. Maintenant qu'on a ça, bon il y a une chose qui n'est pas très acceptable sur le fond, c'est qu'on a plein de point " 0 ". Alors ça il faudrait déjà s'en débarrasser avant de faire notre régression linéaire projective. Donc si on fait un " Clic Droit " sur le graphique et qu'on va dans " Sélectionner des données on voit pourtant qu'il est déjà en mode ignoré. Donc là on se dit : " Zut! Il y a un problème ". En fait oui il y a un problème puisqu'Excel ignore les valeurs en réalité que s'il y a ici un " #N/A ". Donc là il faut qu'on dise dans la formule, si on ouvre la parenthèse, donc si notre calcul donne " 0 ", alors à ce moment-là si ça vaut " 0 ", n'affiche non pas rien mais affiche " #N/A " qui il y a une erreur dans Excel. Sinon affiche le résultat de notre calcul, comme ceci. On ferme la parenthèse de la fonction " SI " et on incrémente ça jusqu'en bas. Maintenant qu'on a fait ça, on retourne dans la feuille " Graphs " et comme on peut le voir, vous voyez effectivement on n'a plus tous les points qui sont à 0, ce qui est quand même nettement meilleur. Maintenant on peut toujours s'amuser ici avec les dates donc regarder si ça marche. Donc là on voit qu'effectivement on est en train d'éliminer des points qui sont " dans le futur de notre passé " si vous voyez où je veux en venir. Donc notre graphique ici sur le fond il marche superbement bien. Donc ça c'est clair que c'est une démonstration par l'exemple qui est on va dire robuste. Après ça n'empêche pas qu'il faut toujours vérifier vos formules parce que par exemple dans les formules qu'on a fait avant, on n'est pas à l'abri d'une erreur moi non plus, et normalement on fait ce que l'on appelle un triple check. C'est-à-dire on se relit, ça c'est le premier check. On s'auto-vérifie. Ensuite on fait vérifier par une tierce personne. Et ensuite on fait utiliser par un groupe de test. Donc maintenant qu'on voit que ça ça à l'air de marcher pas trop mal, il nous reste que la partie projective. Donc on fait un " Clic Droit " sur le graphe. On " Ajoute une courbe de tendance " de type " Linéaire ". Ensuite cette courbe de tendance on va faire ici " Afficher l'équation sur le graphique ". On va dire faire une projection sur par exemple " 20 périodes ". Maintenant là cette courbe de tendance on va mettre l'équation ailleurs. Par exemple ici, et bien évidemment vu la couleur de la courbe de tendance et franchement ce n'est pas ce qu'il y a de mieux. Là on va dire " Trait plein " et puis on va aller prendre ici une couleur noir. Au niveau " Type de tiret " donc aucun. Voilà donc la maintenant on a ici quelque chose qui vaut ce qui vaut, parce que c'est un modèle linéaire très naïf. Mais ce qu'il est intéressant d'observer c'est que si on enlève ici des dates dans le temps, vous allez voir que notre régression linéaire et bien elle va s'adapter. Elle s'adapte là elle est tendance baissière. Là à peu près donc constant. Là il y a une petite tendance haussière. En fait on a presque à faire à un bruit blanc quoi, à un mouvement brwonien on dirait mais bon ça c'est évidemment il faut faire un test après. Par exemple de Jacques Berat mais ça il faut voir. Vous voyez là à nouveau on voit que ça change. Donc c'est pas mal, ça fonctionne pas trop mal. Après évidemment il faut adapter ça avec du VBA pour que l'équation ne se retrouve pas trop n'importe où, n'importe où puis il faut ajouter les intervalles de confiance et de confidence qui nécessitent de gros calculs. On a globalement donc une petite tendance haussière avec bien évidemment le fait ici qu'on a un problème, c'est qu'au temps " 0 ", notre régression pour le coup vaut " -75687 " ce qu'il y a aucun sens. Donc là évidemment on pourra aller dans les propriétés de la régression linéaire et de dire qu'en fait il faudrait que ça " valait 0 à l'interception ". Et le problème qu'a Excel c'est que pour lui le temps 0 c'est le 01.01.1901. Donc en fait, il faut faire après pas mal d'adaptation et de calculs pour faire qu'en réalité notre droite ce soit une droite qui passe par l'origine. Et tout en faisant en sorte que l'équation soit juste Même si évidemment il y a une régression linéaire. Dans ce cas-là, on se doute bien que ce n'est pas quelque chose d'adapté. Voilà pour notre démonstration d'un graphe dynamique utilisant des curseurs avec des zones de nom. Avec la fonction " DECALER ". Avec la fonction " EQUIV ". Finalement ce n'est pas mal du tout. Ça vous donne des idées pour faire des choses après encore plus compliqué.

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 !