Recherche conseils avises pour finaliser la realisation d'un tableau de bord

GUILLAUME1980

XLDnaute Nouveau
Bonjour a tous,

Nouveau venu sur ce forum que j’ai découvert au hasard de mes pérégrinations sur le net, je tiens en premier lieu à saluer votre travail et vos contributions à tous qui m’on déjà permit de découvrir quelques astuces.

Dans un premier temps je souhaite me présenter avant d’énoncer l’objet de ce message.

Je m’appelle Guillaume, j’ai 31 ans et j’utilise Excel au quotidien de manière professionnelle, au fil de mon parcours, j’ai hérité et créé de nombreux tableurs qui sont mes outils de travail, j’ai par la suite modifié/adapté/complété ces documents au fur et a mesure des mes découvertes et de ma compréhension d’Excel.

Je n’ai pas reçu a proprement parler de formation concernant Excel, mon apprentissage initial s’est déroulé au gré de mes rencontres professionnelles ainsi qu’a la suite de mes nombreuses recherches Excelienne.

Les opportunités et les hasards de la vie m’amènent aujourd’hui à devoir réaliser un document de travail qui dépasse un peu le champ de mes compétences sur Excel…

Il s’agit de réaliser le tableau de bord d’une TPE de - de 10 salariés dans le domaine du BTP, je vous soumets ci-joint le modèle de ce document constitué de 5 onglets.

Bien que j’ai pris connaissance de la charte du forum avant d’envoyer ce message, je ne trouve pas pertinent d’isoler une partie du document afin d’illustrer mes questions et préfère vous transmettre le fichier dans son intégralité afin de mieux appréhender son fonctionnement et vous permettre de juger de son efficacité (ou non…).

Bien plus qu’une réponse toute faite ou qu’une formule toute prête, je souhaiterai d’avantage une critique sur le fonctionnement du tableur, car a la suite de mes nombreuses visites, je sais que vous êtes nombreux à éditer sur ce site des documents de travail de qualité.

Fonctionnement du tableau de bord :

Ce document est un tableau de données qui a pour objectif de collecter au quotidien les données économiques sur le fonctionnement de l’entreprise.
Une saisie journalière des dépenses et des moyens humains permet de contrôler à la fois les effectifs (relevé d’heures des compagnons sur l’onglet Planning) ainsi que les moyens matériels (relevé des dépenses journalières sur l’onglet Fournitures).

Un premier onglet intitulé Répertoire permet la collecte des informations générales qui apparaissent par la suite de manière récurrente dans les différents onglets du document (Fournitures, Planning, Récap. Chantier, Récap Main d’Œuvre).
Afin d’éviter les saisies répétitives, des « listes de noms » on étés créées, certaines sont « dynamiques », c'est-à-dire qu’elles permettent, grâce à la fonction « décaler » d’obtenir des « listes déroulantes de choix » dimensionnées suivant le nombre d’entrées des différents tableaux du répertoire (Catégorie, Fournisseurs, Affaires, etc…)

Un second onglet intitulé Fournitures permet le relevé quotidien des dépenses journalières, la saisie des factures fournisseurs s’effectue en ligne.
Concernant les colonnes Catégorie, Fournisseurs et Affaire, la saisie a été optimisée au moyen de « listes déroulantes de choix » « dynamiques », référencées sous forme de tableaux dans l’onglet Répertoire.
La mise en forme conditionnelle du tableau fait apparaître en rouge les factures échues non soldées.
Les filtres (à partir d’Excel 2007) permettent de réaliser une recherche immédiate a partir du tableau (filtrage notamment par catégorie, par fournisseurs, par date, par affaire, etc…) afin de visualiser les montants totaux engagés, le solde, les montants à échéance a une date donnée.

Un troisième onglet intitulé Planning permet le relevé quotidien des heures effectuées par les compagnons.
Nota : du fait de la limitation à 255 colonnes (il me semble ?) sur Excel 2007, la mise en forme du Planning à été réalisée de manière verticale.
Sa saisie s’effectue sous forme de codes (référencés au Tableau Horaires planning de l’onglet Répertoire) au moyen de « listes déroulantes de choix » dans les cellules du calendrier, par ailleurs, il est associé a chaque code une valeur numérique (Ex : AM=4.00) via le gestionnaire de nom afin de permettre l’emploi des codes dans les formules.
La « mise en forme conditionnelle » appliquée à chaque type de code sur l’ensemble du calendrier permet d’augmenter sa lisibilité et de limiter les éventuelles erreurs de saisie.

Les feuilles de synthèse (En cours de création…)

Les onglets Récap. Chantier et Récap Main d’Œuvre sont des tableaux de synthèse, ils réalisent la recherche et la collecte et des données saisies dans les onglets Répertoire, Fournitures et Planning.

L’onglet intitulé Récap. Chantier se présente sous forme de 4 tableaux distincts :

Les 3 tableaux superposés de gauche nommés Matériaux, Matériels et Sous-traitants ont pour but de lister les fournisseurs par catégorie respectives (soit MTX, MTLS, ST) et de collecter les montants engagés par fournisseurs (dans l’onglet Fournitures) dans chaque catégorie en fonction de l’affaire sélectionnée dans la cellule Jaune en tête de tableau.

Le tableau de droite constitue une synthèse de l’onglet Planning, il a pour but de lister l’ensemble des compagnons intervenant sur une affaire en fonction de l’affaire sélectionnée dans la cellule Jaune en tête de tableau et de répercuter les heures par type (Heures ouvrées, Heures Sup. à 25% / 50%, Intempéries, Maladie, Congés, etc…)

Conclusion : Ce document est une synthèse par Affaire/Chantier, il apporte des informations importantes concernant le suivi des affaires en cours, il permet par ailleurs le contrôle des dépenses et indique le crédit d’heure alloué à une affaire par rapport à l’étude réalisée.

L’onglet intitulé Récap Main d’Œuvre se présente sous forme de 2 tableaux distincts :

Le tableau supérieur constitue une synthèse de l’onglet Planning, c’est un récapitulatif mensuel et annuel des heures par type (Heures ouvrées, Heures Sup. à 25% / 50%, Intempéries, Maladie, Congés, etc…) en fonction du compagnon sélectionnée dans la cellule Jaune en tête de tableau

Le tableau inférieur constitue une synthèse globale de l’onglet Planning, c’est un récapitulatif mensuel et annuel des heures par type (Heures ouvrées, Heures Sup. à 25% / 50%, Intempéries, Maladie, Congés, etc…) pour l’ensemble des compagnons et sur l’ensemble des affaires

Conclusion et Remarques :

Si vous en êtes arrivés là, j’espère ne pas vous avoir assommé avec mon long développement, cependant je pense qu’il semble nécessaire d’exposer le fonctionnement du document afin de comprendre mes remarques et mes interrogations.

Pardonnez-moi par avance de ne pas utiliser le langage approprié pour décrire les éventuelles fonctions que je cherche à créer, comme je vous l’ai indiqué précédemment je n’ai pas reçu de formation « académique » à Excel…

Comme vous l’aurez compris, ce sont la création des formules de recherche des tableaux de synthèse des onglets Récap. Chantier et Récap Main d’Œuvre qui me posent problème, par conséquent, je vais les rédiger de la manière la plus naturelle qui soit pour moi: en Français….mais Excel ne comprends pas le Français… (Cf. formules en rouges dans les tableaux des onglets Récap. Chantier et Récap Main d’Œuvre)

Dans l’idéal, je souhaiterai créer des fonctions « dynamiques », c'est-à-dire, d’une part, qui permettent de ne pas « figer » le document de travail (possibilité d’ajouter des compagnons au planning et non limiter a 15 colonnes « primaires » constituées de 5 colonnes « secondaires »…)

La notion de colonnes primaires et secondaires me semble difficile à définir mais c’est notamment ce qui me pose problème dans la rédaction des formules (recherche a plusieurs critères/conditions dans des plages de différentes tailles a la fois horizontales Ex : Nom des compagnons et verticales Ex : Affaires et Codes Horaires du Planning) dans le tableau de Main d’Œuvre de l’onglet Récap. Chantier

D’autre part, ce que j’entends par fonction « Dynamique », c’est la possibilité qu’une fonction soit en mesure de répercuter une valeur recherchée dans un tableau, par exemple le nom du premier compagnon dans le tableau de Main d’Œuvre de l’onglet Récap. Chantier en fonction d’un certain nombre de critères à rechercher dans l’onglet Planning, mais qu’en plus de cela, elle soit aussi en mesure de passer du 2nd au 15ème compagnon si les critères de recherches pour le nième compagnon ne sont pas validés….et que la formule qui suit tienne compte du résultat de la précédente….

Par ailleurs, est-il possible d’ajuster automatiquement le nombre de lignes d’un tableau de synthèse en fonction du nombre de valeurs trouvées ? Ex : je recherche le nom du premier compagnon dans le tableau de Main d’Œuvre de l’onglet Récap. Chantier en fonction d’un certain nombre de critères à rechercher dans l’onglet Planning, admettons que seul 3 compagnons valident les critères de recherche, j’aurais donc 3 valeurs dans mon tableau et 12 lignes vierges… à l’inverse, admettons que 16 compagnons valident les critères de recherche alors que mon tableau ne fait que 15 lignes !!! Manquera-t-il une valeur à ma recherche ? Mon résultat sera donc faux !

J’ai réalisé de nombreuses recherches sur le site d’Excel-downloads, la page perso de Jacques Boisgontier ainsi que developpez.com et quelques autres sites que j’oublie certainement…
J’ai découvert grâce à l’ensemble des contributeurs de nombreuses formules tel que Index, Equiv, décaler… téléchargé de nombreux exemples et tutoriels permettant d’expliquer et d’illustrer ces différentes formules, ma capacité intellectuelle étant ce qu’elle est… je n’ai pas toujours réussi à transposer ces formules à des applications concrètes et encore moins à les imbriquer/associer entre elles ce qui m’amène aujourd’hui à vous solliciter sur le forum.

Une dernière chose, je ne maitrise absolument pas la VBA, ce langage m’est complètement inconnu, dans l’éventualité ou l’un(e) d’entre vous serait disposé à me proposer une solution en créant une formule VBA, je vous saurais gré de ne pas masquer le code afin que je puisse essayer de la comprendre.

Je vous remercie par avance de votre sollicitude et de vos éventuelles réponses.

Guillaume.
 

Pièces jointes

  • TABLEAU DE BORD 2012 BETA.zip
    124.7 KB · Affichages: 277

GUILLAUME1980

XLDnaute Nouveau
Re : Recherche conseils avises pour finaliser la realisation d'un tableau de bord

Dugenou,

Tu trouveras ci dessous l'ultime version du fichier en Ind C:

http://legrand-etudes.perso.sfr.fr/TABLEAU_DE_BORD_2012_IndC.zip

Si le doublage des lignes s'effectue dans la table de calcul des heures, cela n'a aucune incidence!
Si, de plus, i il est possible de supprimer la colonne des HS, le planning s'en trouvera simplifié et la solution sera proche de la perfection!
 

Dugenou

XLDnaute Barbatruc
Supporter XLD
Re : Recherche conseils avises pour finaliser la realisation d'un tableau de bord

Bonjour,
Je n'ai pas encore regardé le fichier C.
Mais j'ai préparé un plainning à mon idée : juste avec le répertoire et le planning pour la démo.
J'ai masqué les lignes 400 à 11 000 ou sera la table (je ne suis pas allé jusqu'au bout des 15 salariés)
et j'ai fait un TCD
Dis moi si ça convient
ensuite je reviendrai sur les heures sup mais ta règle semble claire maintenant.
Cordialement
 

Pièces jointes

  • TABLEAU_DE_BORD_2012_IndBV4_demo_planning.zip
    450.4 KB · Affichages: 76
  • TABLEAU_DE_BORD_2012_IndBV4_demo_planning.zip
    450.4 KB · Affichages: 75
  • TABLEAU_DE_BORD_2012_IndBV4_demo_planning.zip
    450.4 KB · Affichages: 77

GUILLAUME1980

XLDnaute Nouveau
Re : Recherche conseils avises pour finaliser la realisation d'un tableau de bord

Dugenou,

J'ai jeté un œil et ça fonctionne, j'ai fais des test en modifiant les étiquettes et filtres du TCD c'est génial (+ que 2 TCD à créer dans ma récap MAIN D'OEUVRE, 1 pour les MOIS en colonne et 1 avec les COMPAGNONS en colonne...

J'ai même essayé de réaliser un Graphique Croisé dynamique avec les mois en abscisse et les heures en ordonnées...reste à travailler les mises en formes mais je suis très satisfait par ta proposition comme d'habitude....

j'ai , juste une question, la mise en forme de la table de données oblige à "empiler" les compagnons, peut-t-on automatiser la recopie des données en cas d'ajout de compagnons?
 

Bebere

XLDnaute Barbatruc
Re : Recherche conseils avises pour finaliser la realisation d'un tableau de bord

bonjour Guillaume,Dugenou
je suis vos discussions avec intérêt
Je progresse de mon côté
je joins un fichier avec code qui met les données de planning dans une feuille ,données structurées comme une base de données
cela pourrait peut être vous aider
à bientôt
 

Pièces jointes

  • PourFeuilPlanning.xls
    55 KB · Affichages: 84

GUILLAUME1980

XLDnaute Nouveau
Re : Recherche conseils avises pour finaliser la realisation d'un tableau de bord

Bonjour Bebere,

Heureux d'apprendre que tu suis toujours le fil de ce post, merci pour ta contribution, je vais regarder ça avec attention.

J'espère que de ton coté tu nous présentera bientôt une solution 100% VBA tenant compte de la problématique des HS...

Guillaume.
 

Dugenou

XLDnaute Barbatruc
Supporter XLD
Re : Recherche conseils avises pour finaliser la realisation d'un tableau de bord

Bonsoir à tous,
Bebere : c'est super si tu peux faire ça à partir du planning !
Pourrais tu plutôt mettre le motif sur la même ligne que les heures ?
Si on peut faire une ligne par jour avec affaire , salarié (le N° suffit), date, nb d'heures (chiffre), motif; on se débrouille pour le reste. le pb est qu'on peut avoir 2 affaires et 2 motifs par jour et par salarié donc le nb de ligne par date varie. Mais j'ai l'impression que ce n'est pas un probleme avec ton code.
Le pb est que je ne vois pas la feuille avec le planning.
Guillaume,
Pas de pb pour préparer les formules d'avance pour 15 compagnons c'est juste de la patience pour copier coller.
Mais j'espère beaucoup du code de bebere qui pourrais faire la table de données à la demande.
Ensuite avec une zone nommée dynamique on alimente le TCD qui sert à récupérer toutes les données.
Cordialement
 

Bebere

XLDnaute Barbatruc
Re : Recherche conseils avises pour finaliser la realisation d'un tableau de bord

Je regarde pour faire ce que tu demandes
feuille planning avec
à bientôt
update
bonjour Guillaume,Dugenou
nouvelle version avec planning
 

Pièces jointes

  • PourFeuilPlanningV1.xls
    211.5 KB · Affichages: 111
Dernière édition:

Dugenou

XLDnaute Barbatruc
Supporter XLD
Re : Recherche conseils avises pour finaliser la realisation d'un tableau de bord

Bonsoir le forum,
Gillaume : merci de regarder ce travail sur les heures sup dans la feuille spéciale.
J'ai enlevé la notion de HS dans le planning et j'ai augmenté les H des quantités hs enlevées : merci de vérifier si ça convient.

Si c'est ça, on pourra le faire dans ton fichier final à partir de la récap d'heures de bebere ou de la mienne.
 

Pièces jointes

  • TABLEAU_DE_BORD_2012_IndBV4_demo_planning.zip
    467.7 KB · Affichages: 77
  • TABLEAU_DE_BORD_2012_IndBV4_demo_planning.zip
    467.7 KB · Affichages: 77
  • TABLEAU_DE_BORD_2012_IndBV4_demo_planning.zip
    467.7 KB · Affichages: 74

GUILLAUME1980

XLDnaute Nouveau
Re : Recherche conseils avises pour finaliser la realisation d'un tableau de bord

Bonjour Dugenou, Bebere,

Excusez moi pour le délai de réponse mais bien que je sois très enthousiaste quand à la finalisation du projet je n'ai pas eu beaucoup de temps hier & aujourd'hui...

C'est parfait à quelques détails près:

Ne serait-il pas judicieux de réaliser la "table de calcul des heures normales et des heures sup" sur un autre onglet que le TCD de récap des heures qui est "dynamique" et risque d'écraser la la table suite à l'utilisation des filtres du TCD?

Je me rends compte que je n'ai pas été clair concernant la définition des heures:

Heures Normales = HN = 39H (Dont 4H supplémentaires déjà prises en compte, 35H+4HS=39H, les 4 HS intégrées à la durée légale de travail hebdo HN sont majorées de 25%, le taux horaire indiqué dans le fichier: 30 € H.T. tiens compte de la majoration)

Heures supplémentaires à 25% = HS25% = 4 H de la 40ème à la 43ème heure inclue

Heures supplémentaires à 50% = heures au delà de la 43ème heure (Je pense créer une MFC avec les H en rouge si dépassement de la duré maximale de travail hebdo admise - mais le cas de figure semble peu probable...)

Dans la formule: =SI(OU(GAUCHE($A21,3)="tot",C8=0),C8,MIN(C8,$D$19)) à quoi correspond "tot", je ne comprends pas le jan="tot"?

J'ai modifié ton travail conformément à la définition des heure ci-dessus, je te joins le lien ci-dessous pour télécharger le fichier actualisé:

http://legrand-etudes.perso.sfr.fr/TABLEAU_DE_BORD_2012_IndBV4_demo_planning_IndB.xlsx

Dugenou, je vois que tu envisage te te diriger vers une solution "hybride" (formules + VBA) en utilisant la solution de Bebere (Merci au passage...), je vous joins ci dessous nom point de vue:

Solution VBA de Bebere:

Bebere, je sais qu'en VBA tu peux ajouter en vert du commentaire a l’intérieur du code, te serait il possible (si tu as le temps et la patience...) de décrire les commandes exécutées des lignes de code...

La solution très "propre" et séparée du planning sur un nouvel onglet avec la possibilité de le masquer

je ne suis pas capable aujourd'hui d’interpréter le code et de le modifier par la suite le cas échéant

je ne sais pas si l'ajout d'un 16ème compagnon en fin de planning est automatiquement pris en charge dans la table de calcul des heures?

Est-ce que le fait de masquer 1 compagnon (maintenant 4 colonnes sans HS, Merci Dugenou pour ta solution!) l’exclut-il du résultat dans la table de calcul des heures?

Solution "formules" de Dugenou

Peut-on optimiser la table de calcul des heures en fin de planning de manière à ce qu'elle recopie automatiquement l'ajout d'un nouveau compagnon?

Ne devrait-on pas séparer la "table de calcul des heures" du planning sur un nouvel onglet avec la possibilité de le masquer

Est-ce que le fait de masquer 1 compagnon (maintenant 4 colonnes sans HS, Merci Dugenou pour ta solution!) l’exclut-il du résultat dans la table de calcul des heures?

Au risque de me répéter, je tiens vraiment à vous témoigner ma reconnaissance pour votre aide.

A bientôt.

Guillaume
 

Dugenou

XLDnaute Barbatruc
Supporter XLD
Re : Recherche conseils avises pour finaliser la realisation d'un tableau de bord

Guillaume, bebere, le forum,
Bien sûr qu'on peut mettre la table des heures dans un autre onglet c'est bien plus pratique.
A mon avis, les tcd peuvent aussi être dans d'autres feuilles : je te montrerai comment faire des boutons et un tout petit code pour naviguer entre les feuilles autorisées au utilisateurs.
Si on fait la table par formule, il suffit de prévoir 20 salariés et de ne tenir compte que de ceux qui ont un nom.
A mon avis ce sera assez simple (peut être même automatique) en code pour bebere de toujours utiliser toutes les plages qui ont un nom de salarié.
Mon probleme est que je suis en congés demain soir. J'ai plus trop de temps au bureau et ce dimanche je pars.
Donc j'essaye de faire un truc avant samedi soir. Peux tu m'envoyer un message sur mon profil avec un mail où t'envoyer le fichier complet ?
Le code de Bebere me semble très bien : il ne sera pas compliqué de modifier la plage traitée et je pense qu'on doit pouvoir l'automatiser.
Bebere : Je suis intéressé par des explications de principe sur ubound et redim preserve, je pense qu'il faudrait juste expliquer les boucles à Guillaume car tu as déjà commenté les données récupérées
A plusse
Bon voilà , j'ai fait un truc rapide
Il n'y a que les tcd sans mise en forme, il faut adapter en ramenant le nom du salirié, je m'en occupe demain.
la méthode bebere est intégrée et partiellement commentée.
Le fichier est bien plus léger sans toutes ces formules
 

Pièces jointes

  • TDB_2012_IndC_v2.zip
    279.5 KB · Affichages: 83
Dernière édition:

GUILLAUME1980

XLDnaute Nouveau
Re : Recherche conseils avises pour finaliser la realisation d'un tableau de bord

Dugenou,

J'ai pris connaissance de ton travail et je t'en remercie,

Vu que le fichier comporte des fonctions d'excel que je ne maitrise pas ou peu, je vais essayer de transposer cela sur mon fichier pour me familiariser avec ces nouveau outils...

je vais aussi procéder à la mise en forme des TCD afin de me rapprocher des tableaux de synthèse d'origine.

Ok pour la formule permettant la récupération du nom des compagnon

Je mettrai le nouveau document en ligne une fois finalisé

A bientôt.

Guillaume
 

Dugenou

XLDnaute Barbatruc
Supporter XLD
Re : Recherche conseils avises pour finaliser la realisation d'un tableau de bord

Salut Guillaume,
J'ai un peu modifié les données issues du tableau de bebere pour faciliter le tcd qui en découle.
Pour ta question sur "tot" c'est pour traiter le cas ou il est écrit total sur la ligne : dans ce cas il faut afficher le total et non les heures limitées à la valeur en d19.
Il faudrait que tu mette en ligne une version sans la colonne hs dans le planning pour qu'on teste et qu'on adapte le code.
A ta disposition pour expliquer les formules qui sont nouvelles. Je suis là jusque Lundi finalement.
 

Pièces jointes

  • TDB_2012_IndC_v2b.zip
    296.5 KB · Affichages: 82
Haut Bas