Microsoft 365 Construction d'un tableau de bord agence architecture

[Jennifer Nourry]

Bonjour à tous, j'essaye de construire avant mes congés un tableau excel qui me permette de gérer le planning global de l'agence par affaire, puis par phase au sein de chaque affaire. J'aimerais créer un planning automatisé avec une couleur par phase tel que dans l'ongle 1 du présent fichier, menu déroulant pour attribuer l'affaire à un architecte. Le but in fine de ce tableau est d'avoir un planning qui se décale en fonction des données de début et durée variables (semaines) et que cela me permette de chiffrer ces données en CA prévisionnel.
En fait chaque phase représente un montant de CA qui se recalculerait mensuellement en fonction de la donnée durée de chaque phase.

Je ne suis malheureusement pas assez calée pour arriver à automatiser mon idée. J'ai beau avoir regarder plusieurs tutos, je n'ai pas l'impression d'avoir trouver la bonne formule à appliquer.

J'ai utilisé le menu déroulant pour les phases, cependant cela ne copie pas les propriétés de la cellule mais je pourrais passer par la mise en forme conditionnelle. J'aimerais ainsi que chaque phase se représente tant par sa couleur que par sa durée sur le planning (1ère ligne de chaque affaire) et que sur chaque ligne de phase j'ai une formule qui viennent calculer le montant mensuel de chaque phase en fonction de sa durée.

Assez complexe mais si j'arrive à faire cela, je gagnerais beaucoup de temps? Je peux le faire manuellement, nous sommes d'accord mais le but est également qu'en cas de retard on puisse changer les données et que cela se recalcule.

En tout cas pour une première ici, je vous remercie par avance pour l'aide ou non que vous pourrez me porter.

Le nombre de lots me serviraient uniquement à calculer le temps nécessaires pour l'assistante pour traiter le nombre de situations avec une moyenne de temps par mois par situations à raison d'une heure et demi par lot par mois cela prendrait.

Voilà pour mon projet, je suis ouverte à toutes suggestions, attention, je suis débrouillarde mais je ne connais pas suffisamment les macros et VBA, j'apprends...

 

[goube]

Bonsoir,
Un essai à tester, avec la gestion par tranche de 7 jours il y a un problème sur les dates de fin et de reprise de la phase suivante.
Cordialement.

 

[goube]

Bonsoir,
Un essai palliant au problème de date;
Cordialement.

 

[Jennifer Nourry]

Bonsoir,
Un essai palliant au problème de date;
Cordialement.

Bonjour,

Je suis très impressionnée par votre travail. Je vais voir pour le mettre en pratique avec les données.
Pourriez vous m'en dire plus sur le problème de date ?

Je reviens vers vous très vite.

PS : tout cela vous l'avez réalisé avec des fonctions ou en utilisant les VBA ou les macros ?

 

[Jennifer Nourry]

Bonsoir,
Un essai palliant au problème de date;
Cordialement.

Bien que n'ayant pas vu ce problème date, je suis très satisfaite de votre aide. Cependant, j'ai besoin de savoir comment faire pour que sur la ligne comportant le nom de l'affaire on puisse retrouver les couleurs des phases décrites en dessous comme un récap sans le CA inscrit ?

Merci par avance pour votre soutien.

Ps: Quelle est la formule pour inscrire le CA dans la ligne en fonction de la date, je ne la comprends pas...

 

[Jennifer Nourry]

J'ai une autre demande si jamais il n'est pas possible de faire une ligne par affaire est il possible de garder en l'état mais de masque les chiffres à l'intérieur des cellules du planning ?

Si oui merci de m'indiquer comment faire ?

Merci par avance et une excellente journée.

 

[goube]

Bonjour,
La ligne 2 contient les dates de référence pour chaque semaine. Elles ne correspondent pas toujours avec le début de semaine (date de début en colonne C + 7jours)

Cependant, j'ai besoin de savoir comment faire pour que sur la ligne comportant le nom de l'affaire on puisse retrouver les couleurs des phases décrites en dessous comme un récap sans le CA inscrit ?

=SI((OU(ESTNUM(H6);ESTNUM(H7)));$A6;SI(ESTNUM(H8);$A8;SI(ESTNUM(H9);$A9;"")))

Si les lignes sous la ligne Affaire contiennent des nombres on récupère la donnée de la colone A que l'on masque à l'aide de la MFC.

Ps: Quelle est la formule pour inscrire le CA dans la ligne en fonction de la date, je ne la comprends pas...

Pour la première colonne :

=SIERREUR(SI(ET($C6<=H$2;H$2<=$E6);ARRONDI($F6/$D6;2);"");"")

Si la date de début est inférieure ou égale à la date de début de semaine et la date de début de semaine inférieure ou égale à la date de fin on divise le montant HT par la durée.
le SIERREUR est là pour éviter les valeurs d'erreur.
Pour les autres :

=SIERREUR(SI(SOMME($H6:H6)<$F6;SI(ET($C6<=I$2;I$2<=$E6);ARRONDI($F6/$D6;2);"");"");"")

si la somme des colonnes précédentes est inférieure au montant HT on applique la formule au dessus.

Pour masquer les valeurs, on utilise dans les Mise en Forme Conditionnelles la même couleur de police de la couleur de remplissage. Pour lrs lignes détail j'ai utilisé une couleur plus sombre. A toi de voir.

Teste le fichier joint et dis moi.
Cordialement.

 

[Jennifer Nourry]

Bonjour,
La ligne 2 contient les dates de référence pour chaque semaine. Elles ne correspondent pas toujours avec le début de semaine (date de début en colonne C + 7jours)

=SI((OU(ESTNUM(H6);ESTNUM(H7)));$A6;SI(ESTNUM(H8);$A8;SI(ESTNUM(H9);$A9;"")))

Si les lignes sous la ligne Affaire contiennent des nombres on récupère la donnée de la colone A que l'on masque à l'aide de la MFC.


Pour la première colonne :

=SIERREUR(SI(ET($C6<=H$2;H$2<=$E6);ARRONDI($F6/$D6;2);"");"")

Si la date de début est inférieure ou égale à la date de début de semaine et la date de début de semaine inférieure ou égale à la date de fin on divise le montant HT par la durée.
le SIERREUR est là pour éviter les valeurs d'erreur.
Pour les autres :

=SIERREUR(SI(SOMME($H6:H6)<$F6;SI(ET($C6<=I$2;I$2<=$E6);ARRONDI($F6/$D6;2);"");"");"")

si la somme des colonnes précédentes est inférieure au montant HT on applique la formule au dessus.

Pour masquer les valeurs, on utilise dans les Mise en Forme Conditionnelles la même couleur de police de la couleur de remplissage. Pour lrs lignes détail j'ai utilisé une couleur plus sombre. A toi de voir.

Teste le fichier joint et dis moi.
Cordialement.

Bonjour,
La ligne 2 contient les dates de référence pour chaque semaine. Elles ne correspondent pas toujours avec le début de semaine (date de début en colonne C + 7jours)

=SI((OU(ESTNUM(H6);ESTNUM(H7)));$A6;SI(ESTNUM(H8);$A8;SI(ESTNUM(H9);$A9;"")))

Si les lignes sous la ligne Affaire contiennent des nombres on récupère la donnée de la colone A que l'on masque à l'aide de la MFC.


Pour la première colonne :

=SIERREUR(SI(ET($C6<=H$2;H$2<=$E6);ARRONDI($F6/$D6;2);"");"")

Si la date de début est inférieure ou égale à la date de début de semaine et la date de début de semaine inférieure ou égale à la date de fin on divise le montant HT par la durée.
le SIERREUR est là pour éviter les valeurs d'erreur.
Pour les autres :

=SIERREUR(SI(SOMME($H6:H6)<$F6;SI(ET($C6<=I$2;I$2<=$E6);ARRONDI($F6/$D6;2);"");"");"")

si la somme des colonnes précédentes est inférieure au montant HT on applique la formule au dessus.

Pour masquer les valeurs, on utilise dans les Mise en Forme Conditionnelles la même couleur de police de la couleur de remplissage. Pour lrs lignes détail j'ai utilisé une couleur plus sombre. A toi de voir.

Teste le fichier joint et dis moi.
Cordialement.

Bonjour à toi,

Merci encore pour ton aide. Je viens de reprendre le tableau avec les évolutions que je voulais ajouter.
Pour cacher le texte dans les cellules, j'ai trouvé plus simple, il suffit dans la règle de mise en forme conditionnelle, dans FORMAT puis NOMBRE puis Personnalisée taper ;;; dans type et plus aucun texte dans les cellules concernées. Je n'ai pas encore fini de le configurer mais je pense que si on arrive à faire une ligne qui reproduit toutes les mises en forme, je n'aurai besoin de le faire que sur cette ligne. J'ai fini d'ajouter toutes les variables possibles avec les codes couleurs dans les données.

Concernant ta formule pour reprendre les infos des lignes suivantes, je comprend partiellement, cependant avec la complexité que j'ai ajouté depuis la première version, j'ai du mal à reproduire le schéma de la formule.

Je te mets la nouvelle version de mon tableau, j'ai supprimé les données nominative des affaires.

Bien à toi,

PS : est ce que tu penses que je pourrais complexifier et dans l'ongle Données reporter chaque mois le nombre d'affaires en fonction des phases. (exemple dans Données)

 

[goube]

Bonjour,
Je pense qu'il faut revoir l'ergonomie de ton tableau pour avoir quelque chose de simple à gérer.

Mettre toutes les étapes de chaque dossier et filtrer sur les lignes vides de la colonne Date début pour n'afficher que les lignes avec données. Pour ce faire, toutes les lignes contiennent une donnée masquée par la couleur de police si pas utile. (la date du jour pour Chinon, Paris et Nom-Affaire). Les dossiers non actifs sont neutralisé car pas de date (formule Min dans la colonne Date début sur la ligne grisée)

Je pense avoir mis à jour toutes les MFC, à vérifier.

Pour le tableau récap, j'ai utilisé Power Query. Je pense que la requête peut être optimisée, à voir. Il y à un petit écart dû aux arrondis mais pour un tableau prévisionnel je ne pense pas que cela soit problématique.

Pour actualiser la requête, Onglet Données, Requêtes et connexions, Actualiser tout, il est possible d'automatiser cela avec une instruction VBA.

Teste et tiens moi au courant.

Cordialement.

 

[goube]

Bonjour,

Pas de retour ?
Cordialement.

 

[Jennifer Nourry]

Bonjour,

Désolée, j'étais en congés, je reprends le fil et reviens vers vous très rapidement.

Bien à vous,

 

[vinzwanted]

Bonjour le sujet est ancien. Je souhaiterais savoir si vous avez avancé sur ce genre de tableau.
Merci pour votre retour
Cordialement

 

[goube]

Bonjour,
C'est à dire ?
Cordialement

 

[vinzwanted]

Bonjour,

Je voulais savoir si le fichier disponible avait abouti à une version plus élaboré.
Je suis à la recherche de fichier excel sur ce sujet.