Remplir un tableau automatiquement en fonction de critères et 2 fichiers

Bonsoir à tous,

Je souhaite remplir automatiquement un tableau Excel en fonction de critères et 2 fichiers source Excel, et je ne sais pas comment faire...

En PJ, vous trouverez 3 fichiers :

Les 2 fichiers sources sont DEM_ENCOURS.xls et DEM_TRAITEES.xls qui sont une liste de demandes Etudes ou Réalisation avec des valeurs, des dates avec heures, et des états.

Le fichier de calcul cible est Tableau_Indicateurs_exemple.xls

Dans ce dernier fichier, 3 onglets :

Equipe : ce sont les membres de l'équipe concernée

Paramètres : le cœur du truc. J'ai essayé d'écrire les calculs avec mes mots pour chacun des indicateurs. Je ne savais pas comment le retranscrire pour que ça vous facilite la tache, n'hésitez pas à me dire s'il faut le présenter différemment.

Indicateurs : c'est la feuille de résultats des calculs, en fonction du mois.

L'objectif est que en fonction du mois de traitement en B1 de la feuille Paramètres, la colonne du mois dans la feuille Indicateurs se remplisse automatiquement avec les paramètres et critères décris dans la feuille Paramètres, en s'appuyant sur les 2 fichiers sources.

Chaque mois, il y aura de nouveaux fichiers sources, et l'idée est de remplir petit à petit mois par mois les colonnes de la feuille Indicateurs, en changeant le mois de traitement en cellule B1 de la feuille Paramètres.

J'ai essayé de faire des formules directement dans les cellules, j'arrive à sortir quelques résultats avec des NB.SI.ENS (une des seules formule que je connaisse…), mais c'est compliqué à gérer, je souhaiterai le faire en mode macro, et là, je suis à sec.

C'est pourquoi je sollicite votre aide pour me donner un coup de main, je ne sais pas comment écrire les macros, gérer les dates avec des heures dedans, etc …

Choses importantes à savoir :

Les noms des personnes ont été changé

Les fichiers sources contiennent réellement beaucoup plus de colonnes, je n'ai gardé que le minimum pour plus de clarté.

Tout le reste est identique au vrai fichier (les noms des fichiers sources, feuilles, colonnes, les formats des cellules, les valeurs, etc …), vous pouvez vous appuyer dessus pour le code.

Voilà, si ma problématique intéresse l'un d'entre vous, ça serait génial.

S'il vous manque quoique ce soit, si je ne suis pas clair, ou si vous identifiez des pb que je n'ai pas vu, n'hésitez pas à me dire.

Merci beaucoup pour votre aide.

Sébastien

8dem-encours.xlsx (18.91 Ko)
9dem-traitees.xlsx (12.90 Ko)

Bonjour,

Il y a un gros travail à faire !...

Regarde du côté de Récupérer et transformer (Power Query) pour charger tes 2 fichiers à traiter dans ton fichier de synthèse.

Tu crées alors des TCDs pour synthétiser les données avant de les restituer dans la feuille Indicateurs.

Mais tu peux aussi envisager de créer un tableau de bord regroupant des données choisies et des petits graphiques (avec des segments pour le pilotage).

Cela éviterait un tableau d'indicateurs surchargé !...

Je te mets un petit exemple de résultats dans le fichier joint.

Dans tous les cas, il faut préalablement structurer les données rigoureusement pour faciliter les mises à jour mensuelles.

A te relire.

Cdlt.

Bonjour Jean-Eric,

Merci beaucoup pour ta réponse.

Je dois avouer ne pas tout comprendre, mais je vais creuser Power Query ! Je n'ai jamais compris les TCD, il m'arrive quand même d'en faire des simples, mais comme beaucoup de mes collegues, on teste le résultat en glissant les items au hasard dans les 4 zones ...

Quand tu parles de "tableau de bord regroupant des données choisies et des petits graphiques", as-tu un exemple pour ma compréhension ?

Et qu'entends-tu par "il faut préalablement structurer les données rigoureusement " ? Tu parles des données en input ? Ou les données du tableau final ? Les structurer de quelle manière ? Et comment ? N'hésites pas non plus à me donner un exemple.

Je suis vraiment désolé de ne pas tout comprendre

Sebos

Bonjour Jean-Eric,

Je creuse, je creuse

Donc j'ai essayé de refaire pour ma compréhension ce que vous aviez proposé dans votre fichier. Je commence à mieux comprendre l'importance d'organisation des données.

J'ai fais quelques tuto sur Power Query que je ne connaissais pas, ça a l'air d'être assez balèze et efficace quand c'est bien utilisé ! Ce qui me plait est qu'il n'y a pas besoin de savoir coder pour s'en sortir Et ça me va tout à fait

Je souhaiterai juste que vous me validiez le fichier et votre process.

Dans le fichier Tableau_indicateurs_exemple en PJ, j'ai crée une connexion vers le fichier DEM_ENCOURS.xlsx avec Power Query, j'ai juste modifié la date/heure en date pour les colonnes concernées, en dernière étape "Type modifié".

J'ai ensuite créer un TCD en utilisant la connexion crée, et construit le tableau avec les données. (je pensais que l'on pouvait faire cette phase là directement dans Power Query au début ...)

Est-ce bien comme ça qu'il faut procéder ?

Ensuite, j'obtiens le tableau dans l'onglet TCD du fichier joint, je ne suis pas loin ! Mais je n'arrive pas à avoir tout à fait la même chose que vous. Déjà dans la forme et la présentation, comment faites vous pour avoir D_DEMARRAGE et STATUT_FR en en tete ?

Si je pars bien, je vais pouvoir avancer !

Du coup, pour l'indicateur NB_DEM_ENT_ETU_PEC_CNT par exemple, concernant la prise en compte en moins de 24h, comment puis-je intégrer une colonne qui me calcule la différence entre 2 dates/heures, avec en sortie une condition sur >24h ?

En tout cas, merci pour vos lumières, ça me permet d'apprendre.

Cordialement,

Sébos

4dem-encours.xlsx (18.91 Ko)
2dem-traitees.xlsx (12.90 Ko)

Bonjour,

1 - Pour afficher les en-têtes de colonnes :

Ruban, Outil de tableau croisé dynamique, Création, Disposition, Disposition du rapport : Opter pour l'affichage sous forme tabulaire.

2 - C'est brouillon !...

De quoi parles tu ? Quel fichier ? Quelles dates de départ et de fin pour mesurer l'écart ? Quelles seraient les formules si tu devais les créer sous Excel ?

A te relire.

Cdlt.

Merci pour le retour rapide !

Désolé pour le brouillon, je vais essayer d'être plus clair.

Concernant ma problématique de calcul de temps, je cherche à calculer un indicateur qui s'écrirait de cette manière :

Prendre les derniers fichiers du jour.

Dans le fichier Tableau_Indicateurs_exemple, onglet Indicateurs, en cellule O10 (NB_DEM_ENT_ETU_PEC_CNT pour nov-18) :

=Somme des nb des demandes dans les fichiers DEM_ENCOURS et DEM_TRAITEES dont la date de démarrage dans la colonne D_DEMARRAGE est dans le mois en B1 de l'onglet Parametres (du fichier Tableau_Indicateurs_exemple), dont la valeur dans la colonne CRO_CATEGORIE_FR est égale à "Etude", dont les membres de l'équipe en colonne ACT_INTERV_NOM_FR sont listés dans l'onglet Equipe (du fichier Tableau_Indicateurs_exemple), et dont (D_DEMARRAGE-D_AFFECTATION)< ou = à 24h

Est-ce que c'est plus clair ?

N'hésitez pas à me dire

Merci

Sebos

Re,

Je ne comprends pas tout !...

Exemple : Fichier demande , Différence entre D_DEMARRAGE et D_AFFECTATON

Comment peut-on avoir des heures négatives (si Power Query calcule et affiche, ce n'est pas le cas d'Excel)

snip 20181215134632

Jean-Eric,

On va dire que c'est normal si tu trouves des heures négatives, ça veut dire ... que les collaborateurs utilisent mal l'outil de gestion des demandes !

Ils démarrent une demande avant de l'affecter, ce qui n'est pas le process à suivre, mais malheureusement pour le moment ça n'est pas suivi ni piloté

Cela me donnera de bonnes infos pour corriger l'utilisation de l'outil de traitement des demandes ...

Dans l'attente de te lire, merci beaucoup !

Sebos

Re,

Ah oui, et donc dans le cas d'heure négative, ça n'est pas considéré comme un traitement en plus de 24h.

Cordialement,

Sebos

Rechercher des sujets similaires à "remplir tableau automatiquement fonction criteres fichiers"