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.
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.