Excel : Projet tableau de bord entreprise
Bonjour,
Je me présente, je suis Vincent un apprenti ingénieur génie industriel. J'ai pour mission de créer un tableau de bord prospectif.
Voici l'idée : La section montage de l'usine est divisée en 5 UTE (en gros c'est les lignes de montages). Nos services qualité, sécurité, coût et production déterminent des valeurs à remplir dans un tableau / UTE que j'ai déjà créer. Ce tableau va servir d'une base pour compléter la base de données que j'ai crée. La base de données est complétée à l'aide de calculs.
Ensuite nous avons la Scorecard (Tableau de bord prospectif) qui doit résumé les valeurs de ma base de données. Le but est de sélectionner à l'aide des listes déroulantes une année puis un UTE. Ensuite on doit aller chercher les informations ou cellules dans l'onglet BASE DE DONNEES. En gros, si vous préférez, la scorecard est une mise en forme de ma base de données.
Puis à côté des valeurs scorecard, nous devons mettre un smiley vert, jaune ou rouge suivant l'objectif à respecter. L'idéal se serait de mettre ces smileys en automatique. Donc jaune si on est NON OK (objectif non respecté) et vert si OK (objectif respecté). Ensuite l'utilisateur devra changer les jaunes en rouges si nécessaire. De plus, il doit y avoir un plan d'action pour chaque objectif non respecté. C'est à dire qu'on retrouvera autant de plan d'action que de scorecard.
Les problèmes à l'heure actuelle sont :
- nous avons une centaine de fichier excel qui sont triés donc par année puis un par UTE. Le premier but de mon projet est de tout rassembler en un seul fichier (fait).
- le design (fait)
- VBA. Je ne sais absolument pas coder ! C'est pour cela que j'implore votre aide.
- Associer Scorecard et base de données + Associer Scorecard et plan d'action.
- Smiley's en automatique.
J'ai vraiment besoin de votre aide. Ma mission se termine fin Août.
Merci à tous ! Je vous joint le fichier excel. Je le mettrai régulièrement à jour. L'idéal ce serait que je trouve quelqu'un qui pourrait skyper durant le mois d'août pour m'aider. J'ai du oublier certains détails, ça se précisera au fur et à mesure.
MERCI !!
Bonjour,
NB : fichier trouvé
Oui bien sûr, il est PJ lié à mon premier message.
Alors maintenant que j'ai trouvé le fichier :
- Ta base de données (à la fin), est-il possible de la changer de sens ? ligne<->colonne ? ou bien ça a une importance primordiale dans la création de ton fichier ?
Ensuite, est-il possible de savoir où il faut mettre quoi ? une petite fiche de suivi, parce que je veux bien mettre des valeurs dans le tableau de bord mais à quel endroit ?
Enfin, même chose pour les smileys, les indiquer avec les seuils à créer si il doit changer de couleur (ça sera intégré au code et se fera lors du lancement de la macro)
Je peux t'aider si tu me transmet les informations nécessaire à la réalisation du doc (personnellement j'ai pas prévu que ça prenne tout août, ça prendra surement moins de temps que tu ne le pense (je suis en apprentissage ingénieur aussi dans une autre branche et j'ai eu besoin de ce forum un jour et depuis je m'en sépare plus
Oui Evie il est possible de changer le sens ou interchanger les lignes colonnes. Il faut juste que mes formules de changent pas et que mes données soient respectées. Par contre ne pas changer les "A remplir" . Je te joins un fichier avec les onglets Data ça pourrait intéresser.
Je t'envoie une petite fiche de suivi dans un instant.
En gros les smileys ce sera : Si c'est supérieur à OBJECTIF Alors Simley ORANGE Sinon Smiley Vert.
Quand on aura un smiley orange l'utilisateur responsable devra regarder pour juger si il faut un smiley orange ou rouge. Je t'envoie dans un instant un fichier tuto.
Merci beaucoup.
Ce qu'il est possible c'est de mettre un message d'erreur pop up au responsable lorsque c'est orange en lui disant "Le taux (ou truc ou machin) est supérieur à l'objectif est-il acceptable ou non ?" Et en fonction de comment il répond, le smiley prend la couleur qui convient (après ça c'est à voir si ça peut être intéressant ou non !)
PS : pas d'objectif en 2011 ?
Oui c'est très intéressant ! Le but c'est d'informer l'utilisateur qu'il faut mettre ou non un plan d'action en place suivant que l'objectif soit réalisé ou non.
Voici un word avec capture d'écran pour que tu comprennes mieux.
Toutes les idées sont bonnes à prendre. En fait il y aura un plan d'action / scorecard. Du style en 2015 sur l'UTE 31, nous avons pas respectés l'objectif pour les pulls A trois fois de suite, alors il faut une action corrective.
Ca va être compliqué d'affecter un plan d'action pour chaque scorecard
Merci retiens moi au courant de ton avancée.
Non 2011 sert uniquement pour calculer les Pulls / Assy qui sont en 6 mois Rolling. Ainsi que les FTQ en 3 mois Rolling. En fait 2011 permet de lancer la base de données
D'accord je commence à me pencher là dessus, je reviens vers toi dès que je commence à avoir quelques chose (là je change de sens à la base de donnée pour deux raisons :
- Plus facile d'ajouter une ligne et limite plus grande que les colonnes
- Plus facile à exploiter dans le VBA (pour ma part)
- Plus facile pour créer des formules étirables
Je reviens vers toi dès que j'aurais fais un peu plus que la BDD (fin d'aprem sûrement je devrais avoir un début)
Merci Avie !
Par contre faire attention aux formules / valeurs de ma base de données. Je ferai une vérification de toute manière
Pas de formule pour "EFFICACITE, NPMOP, APTP" ? ou bien c'est des données confidentielle peut être ? Je fini le remplissage de la BDD et j'attaquerais les choses sérieuse dans l'aprem !
Pour tes formules tu pourras vérifier les valeurs, après j'utilise des formules tirées pour plus de rapidité, c'est simplement pour le reremplir, tu fera ton remplissage comme tu voudra par la suite mais là c'est surtout pour moi pour inverser la BDD
ok, je ferai le re-remplissage des formules. Sinon pour les trois indicateurs que tu m'as cité, je ne les pas encore défini, je me penche dessus et je te redis
Voilà après le but serait d'afficher des graphiques récapitulatifs ! Très important pour l'entreprise, cela lui permet d'analyser son évolution.
Voici des exemples, Avie redis moi si c'est possible ?
Alors, pour le moment j'ai complété ce que je peux sans macro à savoir :
- Base de donnée mise à la verticale (Cf. onglet BDD (Ancien onglet Base de donnée ne sert plus à rien si jamais cela te convient))
- Recherche des valeurs du scorecard avec un système d'ID pour les lignes assez basique (Année+UTE+Mois) qui permet des recherche rapide sans aller pousser dans les formules complexe.
- Si tu as les formules manquante pour la BDD (à savoir les 3 que j'avais précisé EFFICACITE NPMOP APTP) sinon je ne sais pas quoi mettre, dans tout les cas si tu complète dans la BDD les cases du scorecard sont préconfigurée donc il trouvera.
- Onglet Scorecard : Trend ? il y a une formule à mettre ? Réalisé avant ? y aura t'il quelques chose d'automatique à faire dessus ?
- Pour les smileys de couleur je n'ais pas encore créé la macro j'ai préparer le bouton "Evaluer le tableau" c'est à dire qu'il faudra activer la macro afin d'actualiser les smileys surement (je ne sais pas encore comment je vais procéder pour la création des smileys mais pour le moment c'est l'idée que j'ai)
- J'ai affecter un retour à la page "A REMPLIR" quand tu clique sur la maison (je sais pas trop à quoi elle sert donc je présume que c'est un retour à une sorte de menu ?)
- Je ne sais pas si les smileys sont pertinent (Avec la densité d'informations présente sur la feuille, le management visuel est vraiment à oublier) => Peut-être envisager un coloriage de la case simplement ? (cela enlève juste la création de tout les smileys dans la macro si il en faut 1 par case, sinon le principe d'analyse restera le même)
- Pour les graphiques, on peut en faire, le tout c'est de savoir les données que tu veux analyser en fonction de quoi. Si on part du scorecard pour faire des graphiques l'avantage c'est qu'il y a juste à les créer et c'est terminé, vu que le scorecard se mettra à jour seul, pas besoin de recréer le graphique à chaque changement.
Je ne sais plus trop jusqu'à quelle date j'ai mis les formules, je crois que tu peux aller tranquillement jusqu'à 2030 avec ce fichier si tu garde la même forme sur tout tes onglets.
Voilà l'avancée :
Dis-moi ce que tu en pense, si tu peux m'avoir les infos que je n'ai pas et si tu as des questions je suis là aussi
Merci Avie, c'est génial !
Alors je vais te donner des précisions.
- Je vais changé les indicateurs pour la productivité : il n'y aura surement qu'une formule, mais je te donnerai des précisions plus tard, peut-être la semaine prochaine ?
- Trend : Signifie la tendance. J'aimerais mettre des flèches représentantes d'une augmentation ou d'une diminution entre deux mois.
- Tu as complétement raison pour les smiley's. L'idéal serait de colorier la carré à côté des valeurs (à la place des bonhommes) ou de colorier directement la case où sont noté les valeurs. Le problème pour la deuxième solution, c'est la visibilité des valeurs numériques. A voir tu peux faire comme tu le préfères.
- Je souhaiterais créer un menu d'accueil. Lorsqu'on ouvre le fichier excel, on arrive automatiquement sur cette feuille. Je te ferai un menu. De toute façon c'est avec les liens hypertexte ? Si oui, je sais faire
- Je pense que les graphiques seront assez simples à réaliser. Il faut que je détermine quelles seront les valeurs à sélectionner et aussi quels types de graphiques à choisir pour mes indicateurs. Je pense que ce ne sera pas un problème.
- Cependant, Tu as vu la feuille "Plan d'action" ? Comme je t'ai expliqué un plan d'action doit être mis en place lorsqu'un objectif n'est pas respecté. Je pense partir sur le même système que la scorecard. Je vais créer une base de données avec les même intitulés que mon tableau plan d'action. Cette fois-ci je positionnerai mes données en colonnes
Ensuite l'utilisateur ou le responsable du service (Sécurité, qualité...) devra rentrer ses valeurs dans cette base de données.
Le tableau "feuille plan d'action" servira de récapitulatif à la scorecard. Par exemple on choisit 2015-UTE31 sur la scorecard alors on aura les plans d'action correspondants.
En ce qui concerne le camembert dans l'onglet du tableau Plan action intitulé "Avancée". Ce camembert correspond à l'état d'avancée : PLAN-DO-CHECK-ACT. L'idée serait que l'on puisse colorier les quartiers. Est-ce que cela est possible ?
- Dans BDD, je comprends pas tout à tes formules, je suis pas très bon en Excel.
- Sinon tu fais un superbe travail, c'est hallucinant ! Merci pour ce que tu fais, ça me permet d'avancer plus vite et c'est propre comme travail !
Merci Avie
Si tu veux d'autres précisions n'hésite pas surtout
Voici une idée de coloriage ! En effet c'est plus tape à l'œil, tu avais carrément raison
Je vois ça demain pour continuer ton projet !
Cependant je vais quand même répondre
- Pas de soucis tu me transmet les précisions dès que tu peux j'avance avec ce que tu me donne, si tu me donne rien j'avance pas c'est aussi simple (je ne suis pas constamment à coder/modifier non plus donc il se peut que tu ais des retours plus ou moins long ! par exemple je fais rien le samedi dimanche ^^ mais si je prend un projet généralement je le suis et je donne des nouvelles régulières)
- 1 seule tendance entre 2 mois ? Tu en as 12 dans ton tableau, il faudra que tu me précise au moment où tu en aura besoin car j'ai peur que ce soit mal adapté pour le document ! sauf si tu fais une moyenne de X ou de Y
- Colorier la case rendrait le tableau très peu lisible, mais on peut colorier la case adjacente que tu as préparer, le chiffre resterait lisible !
- Le menu accueil tu peux en faire un avec des boutons et un système de macro, c'est très rapide à faire ! (par ailleur lorsqu'il commencera à y avoir un peu de VBA je t'invite à regarder dans le développeur, ça risque d'être des choses assez basique et généralement je commente afin que la personne à qui je fournis le document puisse se repérer si elle veut modifier quelques choses (si elle se sent bien de le faire ))
- Dès que tu as défini tes graphiques tu les créer sur un nouvel onglet ou bien tu transmet les infos nécessaire pour les construire !
- Plan d'action on verra lorsque tu aura avancer un peu plus de ton coté (BDD, Valeur à mettre, ce qu'il faut ressortir en fonction de quoi, etc...) Pour le camembert ça dépend ce que tu veux mais ça doit être réalisable du moment qu'il n'est à a recréer à chaque fois (Dans le pire des cas j'ai d'autres idées pour le camembert et l'état d'avancé si ça ne marche pas)
- BDD => Les formules sont tes formules mais écrite de manière "indirecte" c'est à dire que dans certaines colonnes masquées sur la droite du tableau, j'ai plusieurs textes correpondant à des chiffres/lettre récurantes ou encore à une partie du nom des onglets.
La fonction INDIRECT te permet d'assembler plusieurs mot pour trouver un chemin
ex : A1 : Salut B1 : D C1 : 4
INDIRECT("'"&A1&"'!"&B1&C1) va te simuler la chaine de caractère : 'Salut'!D4 qui correspond à la cellule D4 de l'onglet Salut
Sur ton fichier les indirects que j'utilise répertorie souvent les cellule 'A REMPLIR - UTE 33'!D5 etc... qui sont laborieuse à écrire alors que là j'ai juste eu à tirer une formule en utilisant quelques caractères fixes.
- De rien ! j'attend tes instructions pour continuer
PS : je viens de voir ton coloriage, du coup ça sera d'autant plus simple que coloriser la case adjacente si on colorise directement le chiffre (Vive les mises en formes conditionnelle !) Je vois ça.
PS2 : Attention avec les fusions de cellules, macro/formule ça fait pas bon ménage avec les cellules fusionnées ! c'est bien quand c'est juste une mise en forme d'un document sans formule
Bonjour Avie,
- La tendance serait : Comparer le mois en cours (par exemple juillet) avec le mois précédent. L'idéal serait de mettre des flèches montantes ou descendantes ou un signe égal. Par exemple " on a augmenté le nombre pulls A entre Juillet et Août, donc fléche montante "
- Oui colorier la case adjacente c'est mieux. Pour la lisibilité des chiffres
- Ok pour les fusions des cellules pas de soucis, on fait sans.
- J'ai déterminé ma formule pour la productivité. Je vais faire ça ce week-end et je te donne des nouvelles pour lundi.
- Pour le plan d'action, il faudrait recréer une base de données sur une autre feuille de calcul avec les même titres. Mais je m'en occuperai et pareil pour lundi. Il faut que je regroupe toutes les données relatives à ces plans d'action depuis 2011.
- Même chose pour les objectifs à atteindre.
Merci beaucoup Avie !
Bonjour,
J'ai des petits soucis actuellement avec internet, n'hésite pas à laisser tes messages/avancées etc.. sur le sujet je reviendrais vers toi dès que ça ira mieux
Bonjour Avie,
Alors j'ai pas mal avancé. J'ai rajouté les données des plans d'actions depuis 2011 dans la feuille nommée "BDDPA".
De plus, j'ai modifié indicateurs de production. L'utilisateurs devra renseigner une quantité produite et un objectif à produire pour le mois. Ensuite ça calculera l'efficacité [(Qté moteurs réalisés)/(Qté moteurs objectifs) * 100 et des pertes/gains : Qté réalisées - Qté objectifs = Valabsolue(Qté pertes)
Donc j'ai créer deux colonnes dans la BDD. Les indicateurs de production seront remplis qu'à partir de Septembre.
Sinon j'ai ça :
- Problème avec les plans d'actions car il y a plusieurs plans d'action / Année et / UTE.
- L'idéal serait que l'utilisateur sélectionne année et UTE (comme pour scorecard) et qu'il renseigne directement dans la feuille "plan d'action" ses plans d'actions. Ensuite il pourrait sauvegarder à l'aide du bouton sauvegarder. Ce bouton pourrait servir si on est amené à modifier d'anciens plans.
- Côté Graphiques. J'ai mis quelques images. L'idée serait de faire un tableau de bord qui récapitule la scorecard. Il faut que ce soit mensuel. Sinon le type de graphique m'importe peu, il faut juste que pour chaque indicateurs nous puissions voir, par exemple qu'entre aôut et semptembre on a eu une baisse ou une augmentation. Ces graphiques doivent pouvoir nous tirer des conclusions. Sinon je n'aime pas trop les graphiques excel. Il y aurait t'il d'autres modèles sur internet?
- Pareil pour les graphiques même système avec les listes déroulantes, sauf que j'en ai rajouté une si jamais nous ne pouvons pas tout compilé en une seule feuille.
Il faut que les feuilles plans d'actions, graphiques et scorecard soit imprimables. Pour l'instant elles le sont.
Merci Avie.
Fichier trop gros... Tu as une adresse mail ?
Ah oui.
Et pour les objectifs dans les "A remplir", il faut faire seulement correspondre ces cases avec la scorecard. Dans les " A remplir" les objectifs sont déjà calculés et ne nécessite aucun passage vers la BDD. Y'a jsute à les retranscrire dans la feuille scorecard en faisant correspondre l'année et l'ute.
Merci.
J'ai rajouté un menu (vite fait ^^)
Dis moi si c'est réalisable, mais je pense que c'est une bonne idée.