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: 327

Dugenou

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

Bonjour Guillaume et bienvenue sur le Forum,
Merci pour tes explications détaillées et ton fichier joint.
Un premier coup d'oeil pour répondre à tes interrogations : oui les formules tiendront compte des nouveaux éléments pourvus qu'ils soient appelés dans le tableau de synthèse, pour les lignes : il faut prévoir autant de vides que le max possible, ensuite elle afficheront ou non des valeurs.
Je pense que le résultat peut être obtenu par une formule matricielle un peu complexe ; je vais en préparer une commentée, tu me diras si ça te convient.
Si j'ai des remarques ou des conseils en travaillant dans ton fichier, je ferai les observation dedans.
Il est rare d'avoir autant de détails sur une demande et nul doute que d'autres viendront d'apporter toute sorte de solution qui t'ouvriront de nouveaux horizons.
Cordialement
 

GUILLAUME1980

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

Bonjour Dugenou,

Je ne m'attendais pas a avoir une réponse aussi rapidement, tout d'abord un grand merci d'avoir été jusqu'au bout du message et de plancher sur ma problématique.

Alors, si j'ai bien compris, Concernant l'ajout de nouveaux compagnons dans le planning, il suffit dans la formule de recherche du tableau de synthèse Main d’œuvre de créer une formule avec une plage de recherche supérieure à la taille du planning actuel (Ex 120 colonnes x la hauteur du planning?)

Pour info, le nombre de lignes max a prévoir pour les tableaux de la feuille de synthèse Récap. Chantier:
- Provision de 100 lignes pour chacun des 3 tableaux fournisseurs MATERIAUX, MATERIELS & SOUS-TRAITANS
- Provision de 50 lignes pour les tableau de synthèse Main d'Oeuvre
Si j'ai bien compris, les lignes avec une valeur nulle n'apparaitront pas?

Dans l'attente de te lire.

A + tard
 

Dugenou

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

Bonsoir,
On peut faire une formule qui n'affiche rien si tous les items à afficher sont présents. mais il y aura des lignes vides. S'il faut les masquer ou les supprimer alors il faut du VBA.
Pour les nouveaux compagnons : il faudra que les nouveaux soient dans la liste de validation.
Pour l'instant j'affiche les fournisseurs de mtx pour une affaire, j'ai un pb avec les doublons : il faut que je révise mes connaissances à ce sujet.
A très bientôt
 

GUILLAUME1980

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

Dugenou,

Le VBA serait alors idéal pour la présentation du tableau (en terme de taille d'affichage sur l'écran d'un portable notamment) et l'éventuelle impression (pas prépondérant) mais je ne suis pas certain d'arriver a comprendre le langage VBA si je dois modifier le code par la suite...

L'entrée des nouveaux compagnon se fera impérativement dans la liste de validation du répertoire donc pas de problèmes de ce coté là...

Bonne chance pour le problème de doublons car je ne suis pas en mesure de t'aider...

A bientôt.

Guillaume
 

Dugenou

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

Bonjour,
Je te livre le début de mon travail.
Je crains de ne plus avoir de temps aujourd'hui.
J'espère que d'autres prendront la relève : j'arrive à éliminer les doublons mais pas à les afficher sans "trou"

Cordialement
 

Pièces jointes

  • TABLEAU DE BORD 2012 BETAV1.zip
    121.8 KB · Affichages: 138
  • TABLEAU DE BORD 2012 BETAV1.zip
    121.8 KB · Affichages: 125
  • TABLEAU DE BORD 2012 BETAV1.zip
    121.8 KB · Affichages: 135

Misange

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

Bonjour Guillaume, Bonjour Dugenou,

Je me désole quand je lis ceci : "On peut faire une formule qui n'affiche rien si tous les items à afficher sont présents. mais il y aura des lignes vides. S'il faut les masquer ou les supprimer alors il faut du VBA." alors qu'il y a dans excel 2007 (et 2010 !) une fonctionnalité intégrée qui gère parfaitement ce genre de pb : les tableau.
Onglet accueil/style/mettre sous forme de tableau
ce n'est pas juste une mise en forme esthétique, contrairement à ce que le placement de cet outil dans l'onglet style peut faire croire.
Un tableau permet de gérer dynamiquement des données (et des noms définis automatiquement),sans avoir besoin de définir des formules avec = decaler ..., sans besoin de prévoir des lignes vides pour les données à venir, sans avoir besoin d'écrire à l'avance les formules dans des lignes encore vide.
Toute donnée inscrite juste en dessous d'un tableau s'y intègre, vrée une nouvelle ligne qui recopie les formules précédentes, ajuste les noms définis automatiquement.

J'ai écrit plusieurs pages de tutos sur ce sujet ici :
Ce lien n'existe plus

Les tableaux permettent en plus de trier, filtrer créer les graphiques associés très facilement.
Inutile de figer les volets : quand tu es dans un long tableau, la ligne d'en tête reste toujours visible. C'est très pratique.
Ils sont à utiliser sans modération !

Autre chose Guillaume : tu OUBLIES qu'il existe un bouton fusionner et centrer dans excel.
Si tu veux faire une mise en forme de ce genre tu fais un clic droit, style/alignement/centrer sur plusieurs colonnes.
Les cellules fusionnées sont une source d'emm... maximum dans excel, il faut vraiment les proscrire.

Je t'ai fait un début d'exemple dans les 2 premiers onglets. Ajoute des données sous les tableaux en couleur et observe ce qui se passe.
- 1 seul type d'information par tableau. C'est indispensable pour gérer correctement des données qui évoluent dans le temps et pouvoir sortir les infos pertinentes
- Pas de ligne vide dans les tableaux pour des raisons de présentation. Si tu veux faire des jolies présentation, joue sur la hauteur des lignes, les encadrement ou autres mais n'interrompt jamais un tableau, ca met le bazar ++ si tu veux trier filtrer ou garder dynamique.
- un seul type d'info par cellule : pas de type de fourniture : papier, crayon, gomme.
- toujours répéter les informations de façon à ce que CHAQUE ligne contienne TOUTES les infos. Pas de cellule fusionnée contenant le titre d'un groupe d'infos donc...

Dans un tableau on peut faire référence au cellules soit avec les adresses tableau, genre =[tableau1[dates]] pour la colonne dates par exemple, ou par les adresses classiques.
C'est très pratique dans plein de cas mais il y a quelques manquements dans ce système, comme l'impossibilité de figer une colonne en ajoutant un $ [tableau1[$dates]] ne fonctionne hélas pas.

Mais tu peux parfaitement utiliser les références classiques des cellules $A$2...
et, même si tu tapes = $A2:$A47
si tu ajoutes une valeur en A48, elle sera prise en compte et tes formules s'adapteront toutes seule !

Bref découvre cet outil et tu te simplifieras la vie.
J'ai l'impression de répéter ce baratin sans fin depuis que la version 2007 est sortie mais malheureusement bien trop peu de gens utilisent les tableaux.
 

Pièces jointes

  • TABLEAU DE BORD 2012 BETA.xlsx
    199.9 KB · Affichages: 182

Bebere

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

bonjour Guillaume,Dugenou,Misange
une solution avec vba
pour voir le code de la feuille recap chantiers
clic droit onglet,choisir visualiser code
dans module 2 tu as 2 procédures
RechercheDansFourniture pour la partie matériaux B5:H19
RechercheDansPlanning pour la partie main d'oeuvre J5:J19
feuille recap chantiers,si c2 change,les données aussi
il faudrait savoir si le résultat pourrait donner plus de 15 lignes(si oui arranger la feuille autrement)
commencé un code pour feuille RECAP MAIN D'OEUVRE
ce que tu veux c'est compter les AM,PM,VEN,etc en colonnes c
HS heure supplémentaire et interpréter le 1,2(25%,50%)
le reste colonne F à L ,les formules je ne sais pas t'aider
je peux le faire en vba
le tout suivant le mois
 

Pièces jointes

  • TABLEAU DE BORD 2012 BETAV1.zip
    133.9 KB · Affichages: 98
  • TABLEAU DE BORD 2012 BETAV1.zip
    133.9 KB · Affichages: 92
  • TABLEAU DE BORD 2012 BETAV1.zip
    133.9 KB · Affichages: 90

GUILLAUME1980

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

Bonjour a tous,

@ Dugenou,

Je prends connaissance de ton travail et te ferai un retour dès que possible...Bonne journée.

@ Misange,

Merci de prendre le temps d'étudier mon problème, je prends note de tes remarques pertinentes concernant l'outil tableaux, je vais potasser ton tutoriel sur Excelabo.net, je prends connaissance de ton travail et te ferai part de mes questions par retour de post dès que possible...

@ Bebere,

Merci pour la solution VBA, je parts de loin en ce qui concerne la VBA donc je vais prendre connaissance de ton exemple pour pouvoir répondre à tes questions, je te fais part de mes réponses et remarques dès que possible...

Bonne journée a tous
 

Bebere

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

bonjour Guillaume,Misange,Dugenou
feuille recap chantiers
clic bouton appel userform
choix d'une catégorie
choix d'une affaire de la catégorie
clic bouton valider
code dans module2 et module4
à bientôt
 

Pièces jointes

  • TABLEAU DE BORD 2012 BETAV1.zip
    148.6 KB · Affichages: 98
  • TABLEAU DE BORD 2012 BETAV1.zip
    148.6 KB · Affichages: 112
  • TABLEAU DE BORD 2012 BETAV1.zip
    148.6 KB · Affichages: 96

Dugenou

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

Bonjour Guillaume, bebere, Misange,

J'arrive à afficher la liste sans doublons mais au prix d'une colonne intermédiaire.
Le pavé des couts matériaux me semble OK, je passe aux opérateurs.

Cordialement
 

Pièces jointes

  • TABLEAU DE BORD 2012 BETA V2.xls
    635.5 KB · Affichages: 122

GUILLAUME1980

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

Bonjour a tous,

J'ai bien relevé vos derniers message cependant j'ai été un peu charette ces derniers temps, j'ai bien sur pris le temps d'étudier vos réponses a chacun.

@ Dugenou,

Mille merci pour ton aide, tes solutions sont les plus claires pour moi, ma logique ne m'a pas permis de rédiger des formules avec un tel degré d'imbrication...

Pour la formule "d'appel des fournisseurs" je l'ai un peu décortiquée et commence a comprendre son fonctionnement, par ailleurs je ne connaissais pas les fonctions PETITE.VALEUR et MIN...

A quoi correspond l'écriture de: &"" en fin de formule?

Concernant la formule SOMMEPROD et l'ajout de conditions avec * pour la répercussion des montants de l'onglet FOURNITURE c'est très clair pour moi.

La colonne intermédiaire n'est pas une contrainte dans le sens ou il est possible de la masquer.

Merci et à bientôt, j'attends la suite avec impatience.

@ Misange,

C'est promis je corrige cette mauvaise habitude concernant la fusion de cellules dans des tableaux de données...

Merci pour ta contribution et tes remarques concernant l'outil Tableaux, j'ai un peu révisé et suis allé faire un tour sur excelabo.net.

Ta proposition semble intéressante, avec cet outil il serait possible de réaliser les deux feuilles de synthèses par la création de Tableaux Croisés Dynamiques sans se donner la peine de créer aucune formule, cependant, il semble impossible de réaliser des tableaux à plusieurs niveaux d'entrées comme c'est le cas sur l'onglet PLANNING (avec en abscisses:les compagnons, les affaires, les codes horaires = 3 d° d'informations et en ordonnées: les dates + les semaines = 2 d° d'informations).

J'aimerais bien avoir ton avis sur la question.

@ Bebere,

Merci pour la solution VBA, elle semble la plus aboutie en terme de maîtrise d'excel cependant je ne suis pas encore en mesure d'en déchiffrer le code mais j'ai pu relevé cependant qu'il doit y avoir une erreur dans le code car si tu filtre les données dans l'onglet fournitures (CATEGORIE = MTX + AFFAIRE = AFF.01), tu t'apercevras que tu n'obtiens pas le même montant que dans la récap chantiers...A voir...

Ton second fichier avec la boite de dialogue n'est pas adapté a l'exploitation du document car a des fins d'analyse tu dois pouvoir afficher les 3 catégories simultanément (pour obtenir le total général par ex.)

Je ne désespère pas d'en connaître un peu plus un jour concernant la VBA, j'ai téléchargé un tutoriel assez complet sur développer.com, mais je trouve la lecture un peu indigeste alors si à tout hasard tu connaissais l’existence d'un "lexique" un peu synthétique qui définit les fonctions principales et comment les rédiger...je suis preneur...

Grâce a toi je possède un code VBA pour une application concrète à mes yeux, j'aurais peut-être plus de facilitées a comprendre...

@ Dugenou, Misange, Bebere,

Je réalise une refonte du fichier en parallèle de vos réponses dans laquelle j’intégrerai le meilleur de vos contributions, il est encore un peu tôt pour vous la présenter mais j'y compte bien.

Dans l'attente de vous lire et à bientôt.
 

Misange

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

Bonjour a tous,

Ta proposition semble intéressante, avec cet outil il serait possible de réaliser les deux feuilles de synthèses par la création de Tableaux Croisés Dynamiques sans se donner la peine de créer aucune formule, cependant, il semble impossible de réaliser des tableaux à plusieurs niveaux d'entrées comme c'est le cas sur l'onglet PLANNING (avec en abscisses:les compagnons, les affaires, les codes horaires = 3 d° d'informations et en ordonnées: les dates + les semaines = 2 d° d'informations).
.

Si bien sur tu peux tout à fait garder la même disposition dans un tableau que dans ta feuille ! je ne vois pas ou se trouve le problème pour toi. Un tableau au sens d'excel est juste une fonctionnalité supplémentaire, ça n'enlève aucune des possibilités de données "à plat" dans une feuille.

PAr ailleurs, attention, un tableau ne sert pas seulement à alimenter un tcd et sauf erreur je ne t'ai jamais parlé de traiter tes données par des tcd. J'ai parlé de tableaux dynamique, pas de tableaux CROISES dynamiques. Je ne vois pas trop d'ailleurs comment utiliser un tcd dans ta feuille.
Le tableau je le répète sert à simplifier la gestion des données, que ce soit par formules classiques, par vba, par tcd, peu importe. Son avantage énorme est de créer des plages de données dynamiques et les noms qui vont avec. Ces plages et ces noms tu peux les manipuler ensuite comme tu le ferais avec des noms que tu définis toi même, notamment avec la fonction decaler.
Il y a un énorme avantage à utiliser les tableaux, c'est aussi quand tu dois mettre plusieurs tableaux dynamiques (= appelés à recevoir de nouvelles données ou à en supprimer, pas des tcd) les uns en dessous des auters. La limite de la fonction decaler c'est qu'elle repose sur le nombre de données de la colonne. Dès que tu as d'autres infos que celles du tableau dans la feuille, c'est la M... un tableau délimite tout seul les plages.

Autrement dit, user et abuser des tableaux n'empêche en rien ensuite ni de faire des formules avec des références classiques de cellules, ni de manipuler ça par VBA.
 

Dugenou

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

Bonjour à tous,
Guillaume : ok pour les colonnes intermédiaires.
Je parviens à afficher les noms et j'ai commencé le calcul sur les heures : là aussi avec colonnes intermédiaires.

J'ai repris l'idée de zones nommées équivalentes aux codes pour les calculs d'heures mais j'ai ajouté _ devant toutes.
dis mois ce que tu en penses avant que j'avance
j'aurai des questions sur quelles heures dans quelles colonnes
Cordialement

le &"" en fin de formule index permet d'éviter l'affichage de valeurs d'erreur
 

Pièces jointes

  • TABLEAU DE BORD 2012 BETA V3.xls
    820 KB · Affichages: 145
Dernière édition:

GUILLAUME1980

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

Bonjour Dugenou,

Concernant la colonne intermédiaire du "pavé des couts matériaux", je vais déplacer la colonne intermédiaire en fin de tableau de l'onglet fournitures, la taille de cette colonne doit être équivalente à la colonne fournisseurs...
je m'explique, admettons que dans ta Récap Chantiers tu ai selectionné l'affaire: AFF.01, si tu as 15 factures attribués au fournisseur 1 et une 16ème par exemple attribué au fournisseur 2, tu ne la verras pas s'afficher dans la récap car ta colonne intermédiaire est limitée à 15 lignes (tableaux MTX, MTLS & ST superposés...), par conséquent le fournisseur 2 sera absent du tableau de récap!

Je parviens à afficher les noms et j'ai commencé le calcul sur les heures : là aussi avec colonnes intermédiaires.

Conernant l'affichage des noms, il me semble que la formule ne fonctionne pas:
=INDEX(PLANNING!$3:$3,ColonneNomAffaire), ColonneNomAffaire=PETITE.VALEUR(SI(PLANNING!$A$1:$CM$1<>0,COLONNE(PLANNING!$A$1:$CM$1),200),NBVAL('RECAP CHANTIERS'!$L$4:$L5))
En effet, pour l'AFF.01 seul DUPONT, METAYER & DURAND sont intervenus sur ce chantier! je n'ai pas encore trouvé ou est le problème...

Concernant le calcul des heures, je vais essayer de réfléchir a une solution sans colonne intermédiaires, je trouve que ça fait un peu "Usine à gaz" car si je masque les colonnes intermédiaires situées dans le PLANNING cela complique la possibilité de dupliquer (avec la poignée de recopie) le groupe de colonne du 15ème COMPAGNON afin d'en ajouter d'autres (source d'erreur)

Par ailleurs, dans les 1ère colonnes intermédiaires du planning (Pour comptabiliser les heures ouvrées, dans la formule:
=SI(G7=AffaireSelectChantier,INDIRECT("_"&H7),0)+SI(I7=AffaireSelectChantier,INDIRECT("_"&J7),0) que signifie: "_"&, ATTENTION : a la suite de la 1ère ligne certaines formules incluent les HEURES SUPPLÉMENTAIRES, à modifier...

Dans les secondes colonnes intermédiaires (pour comptabiliser les HS), dans la formule:
=SI(G7=AffaireSelectChantier,SI(ESTVIDE(K7),0,INDIRECT("_"&K7)),0), il convient de la modifier comme ceci:
=SI(I7=AffaireSelectChantier,SI(ESTVIDE(K7),0,INDIRECT("_"&K7)),0), car les HEURES SUPPLÉMENTAIRES s'effectuent l'apres midi et le code affaire matin et après midi peut être différent.

J'ai repris l'idée de zones nommées équivalentes aux codes pour les calculs d'heures mais j'ai ajouté _ devant toutes.
dis mois ce que tu en penses avant que j'avance

Excuses-moi mais je ne comprends pas la signification du: "_", j'en profites pour te demander si tu ne connais pas l'existence d'un lexique excel qui définit succinctement l'utilisation des: ", >, <, _, &, *, etc... ainsi que leur combinaison dans les formules....et merci pour la précision concernant le &"", j'en prends note...

J'ai repris l'idée de zones nommées équivalentes aux codes pour les calculs d'heures mais j'ai ajouté _ devant toutes.
dis mois ce que tu en penses avant que j'avance

Les colonnes intermédiaires du PLANNING additionne les heures par lignes sans tenir compte des code horaires (Ex: METAYER en ligne 11 mélange d'heures ouvrées et de d'heures de Récu...)
La formule un peu longue que j'ai commencer concernant la totalisation des heures semble correcte mais je n'arriva aps à y ajouter la condition COMPAGNON:
Exemple pour les heures de RECUPERATION:
=+NB.SI.ENS(PLANNING!$H$6:$H$371,"REC",PLANNING!$G$6:$G$371,$D$2)*_REC+NB.SI.ENS(PLANNING!$J$6:$J$371,"REC",PLANNING!$I$6:$I$371,$D$2)*_REC
Toutefois il manque la condition: "en fonction du COMPAGNON correspondant dans la ligne du TABLE DE RECAP MAIN D'OEUVRE réaliser l'opération ci dessus dans les colonnes correspondantes au COMPAGNON"

C'est pas évident je sais...je te joins ci-dessous une petite traduction des codes (qui figure aussi en bas de planning, il est attribué a chaque code une valeur numérique en heures décimales figurant au tableau de l'onglet répertoire), les codes sont à additionner par AFFAIRES et par COMPAGNON (2 conditions):

H = HEURES OUVREES = SOMME des CODES AM+PM+VEN+PONDÉRATION HEURES OUVREES(1H+2H+3H+4H)

H SUP. 25% = SOMME DES CODES HEURES SUPPLEMENTAIRES 1HS+2HS+3HS....+8HS comprises entre la 40ème et la 43ème heures (inclue) et comptabilisées par semaines

H SUP. 50% = SOMME DES CODES HEURES SUPPLEMENTAIRES 1HS+2HS+3HS....+8HS au dela de la 43ème heure et comptabilisées par semaines

REC = HEURES DE RECUPERATION = SOMME DES CODES REC
INT = HEURES D'INTEMPERIES = SOMME DES CODES INT
ABS = HEURES D'ABSENCE = SOMME DES CODES ABS
MAL = HEURES DE MALADIE = SOMME DES CODES MAL
CA = CONGES ANNUELS = SOMME DES CODES CA
CSP = CONGES SPECIAUX = SOMME DES CSP
ADM = HEURES ADMINISTRATIVES = SOMME DES ADM

Encore merci pour toutes tes solutions, j'ai progressé plus vite en une semaine sur ce forum qu'en 6 mois seul...

A Bientôt.
 

Discussions similaires

Statistiques des forums

Discussions
311 710
Messages
2 081 781
Membres
101 817
dernier inscrit
carvajal