XL 2019 Vue d'ensemble (planning) de l'activité d'une équipe sur l'année

[Doublenico79]

Hello,

Dans mon premier onglet, j'ai une liste d'évènements qui comportent chacun une date de début et une date de fin et un manager associé, un manager pouvant s'occuper de plusieurs évènements sur l'année
Dans mon deuxième onglet, j'ai un planning avec la liste des managers sur l'année pour avoir une vue d'ensemble de l'activité de l'équipe et éventuellement voir si une ressource ne serait pas prise sur une même période.

Je ne sais vraiment pas comment attaquer ce projet si ce n'est que je ne souhaite pas avoir de macro pour des raisons de maintenance et que la recherche V et la mise en forme conditionnelle semblent de bonnes pistes alors je fais appel à la communauté en me disant que mon besoin a déjà été traité par quelqu'un d'autres.
J'ai regardé également du côté de Microsoft 365, y a planner mais je n'y trouve pas mon compte et hélas pas de licence Microsoft project.


Donc si vous avez une ébauche de fichier à partir duquel je pourrai partir ou des conseils sur la meilleure méthode pour travailler tout ça, je suis preneur.

Merci d'avance

 

[JHA]

Bonjour à tous,

Peut-être un début de piste par formule et MFC.

JHA

 

[Doublenico79]

Bonjour,

Merci beaucoup
Pourrais tu m'expliquer plus en détail ce que tu as fait s'il te plaît ?
D'après ce que j'ai cru comprendre tu vérifies d'abord le nombre d'event pour un manager et ensuite...je ne suis plus. Mais du coup ca signifie qu'il faut prévoir un nombre de lignes à l'avance pour les évenements d'un manager d'où les lignes masquées ?

Merci

 

[JHA]

Bonjour à tous,

Effectivement, j'ai laissé 10 lignes par nom pour les évènements mais tu peux en mettre plus.
Pour rechercher les dates suivant les projets, j'utilise les fonctions ligne et indirect.
Exemple cette matricielle:

=LIGNE(INDIRECT(INDEX('Staffing 2023'!$D$6:$D$11;EQUIV(planning!$A$6&planning!$B6;'Staffing 2023'!$F$6:$F$11&'Staffing 2023'!$B$6:$B$11;0))&":"&INDEX('Staffing 2023'!$E$6:$E$11;EQUIV(planning!$A$6&planning!$B6;'Staffing 2023'!$F$6:$F$11&'Staffing 2023'!$B$6:$B$11;0))))

renvoie {44942;44943;44944;44945;44946;44947;44948;44949}
On regarde si une de ces dates appartient à "C$4" avec la fonction

=LIGNE(INDIRECT(INDEX('Staffing 2023'!$D$6:$D$11;EQUIV(planning!$A$6&planning!$B6;'Staffing 2023'!$F$6:$F$11&'Staffing 2023'!$B$6:$B$11;0))&":"&INDEX('Staffing 2023'!$E$6:$E$11;EQUIV(planning!$A$6&planning!$B6;'Staffing 2023'!$F$6:$F$11&'Staffing 2023'!$B$6:$B$11;0))))=planning!C$4

qui renvoie {FAUX;FAUX;FAUX;FAUX;FAUX;FAUX;FAUX;FAUX} et j'utilise la fonction estnum(equiv(vrai;{FAUX;FAUX;FAUX;FAUX;FAUX;FAUX;FAUX;FAUX};0)) pour déterminer si il trouve la valeur vrai dans la liste.
Cette fonction equiv() renvoie une position dans la liste. l'ajout de estnum() permet d'avoir vrai ou faux.

Si il y a un vrai dans la liste alors on renvoie vrai sinon faux
Pour la MFC si la formule de la cellule A6 renvoie vrai alors on colore en jaune.

Il y a certainement plus simple mais il faut passer par VBA mais là, je ne peux pas t'aider.

JHA

 

[Doublenico79]

Un grand merci pour tes explications ! je ne comprends pas tout mais je vais essayer s'y aller pas par pas.
Question subsidiaire : est ce possible de n'avoir qu'une seule ligne par manager ?

Merci encore

 

[JHA]

Bonjour à tous,

Par formule, trop compliqué et que fais-tu des évènements qui démarrent le même jour pour la même personne.

JHA

 

[Doublenico79]

Bonjour à tous,

Par formule, trop compliqué et que fais-tu des évènements qui démarrent le même jour pour la même personne.

JHA

Idéalement dans la MFC j'aurai rajouté une couleur.
Mais je peux faire sans effectivement.
Pour la colonne des Events de l'onglet planning, il y a dans la formule le "-5", je n'arrive pas à comprendre à quoi ca sert ?
LIGNE('Staffing 2023'!$B$6:$B$15)-5)

=SIERREUR(INDEX('Staffing 2023'!$B$6:$B$15;PETITE.VALEUR(SI('Staffing 2023'!$F$6:$F$15=planning!$A$6;LIGNE('Staffing 2023'!$B$6:$B$15)-5);LIGNE(1:1)));"")

Et merci encore

 

[Hasco]

Bonsoir,

Fait pour le fun, une version power query (inclus dans excel 2016)
La cellule B1 qui contient l'année se nomme 'Planning_An' et sert de départ à la requête
Les cellules des managers ayant plusieurs évènements un même jour sont en orangé foncé les autres, en clairs.

Cordialement
[Edition] tri des dates avant pivotage ajouté[/Edition]

 

[Doublenico79]

Bonsoir,

Fait pour le fun, une version power query (inclus dans excel 2016)
La cellule B1 qui contient l'année se nomme 'Planning_An' et sert de départ à la requête
Les cellules des managers ayant plusieurs évènements un même jour sont en orangé foncé les autres, en clairs.

Cordialement
[Edition] tri des dates avant pivotage ajouté[/Edition]

Merci, le résultat est vraiment bien.
J'ai ouvert la requête en vitesse pour regarder la requête mais je n'ai jamais fait de power query.
Pourrais tu me détailler les différentes étapes s'il te plaît de la requête pour que je puisse m'approprier l'ensemble mais surtout comment on passe de la requêteba cette affichage aux petits ognions ?


Merci encore

 

[JHA]

Bonjour à tous,

Concernant l'éclairage sur "LIGNE('Staffing 2023'!$B$6:$B$15)-5)"
La fonction petite.valeur est utilisée avec une condition pour retrouver la valeur dans une liste.
si on enlève le "-5" la formule retourne:
{6;7;8;9;10;11;12;13;14;15}.
Avec le -5, tu auras:
{1;2;3;4;5;6;7;8;9;10}
la formule recherche pour le nom du manager "Amélie" tous les "events" du tableau. Afin de retourner la première valeur de la liste ("LIGNE(1:1)"), il faut remettre cet index de recherche à 1 et pour cela on retranche 5 car la recherche se fait à partir de "B6".

D'ailleurs, il y a une modification à apporter pour rendre dynamique les plages de la formule avec le tableau "Table1".

=SIERREUR(INDEX('Staffing 2023'!$B$6:$B$11;PETITE.VALEUR(SI('Staffing 2023'!$F$6:$F$11=planning!$A$6;LIGNE('Staffing 2023'!$B$6:$B$11)-5);LIGNE(1:1)));"")

si tu ajoutes des données dans le tableau "Table1" les plages des formules s'adapteront.

JHA

 

[Doublenico79]

Bonjour à tous,

Concernant l'éclairage sur "LIGNE('Staffing 2023'!$B$6:$B$15)-5)"
La fonction petite.valeur est utilisée avec une condition pour retrouver la valeur dans une liste.
si on enlève le "-5" la formule retourne:
{6;7;8;9;10;11;12;13;14;15}.
Avec le -5, tu auras:
{1;2;3;4;5;6;7;8;9;10}
la formule recherche pour le nom du manager "Amélie" tous les "events" du tableau. Afin de retourner la première valeur de la liste ("LIGNE(1:1)"), il faut remettre cet index de recherche à 1 et pour cela on retranche 5 car la recherche se fait à partir de "B6".

D'ailleurs, il y a une modification à apporter pour rendre dynamique les plages de la formule avec le tableau "Table1".

=SIERREUR(INDEX('Staffing 2023'!$B$6:$B$11;PETITE.VALEUR(SI('Staffing 2023'!$F$6:$F$11=planning!$A$6;LIGNE('Staffing 2023'!$B$6:$B$11)-5);LIGNE(1:1)));"")

si tu ajoutes des données dans le tableau "Table1" les plages des formules s'adapteront.

JHA

Un grand merci pour les explications, je vais analyser tout ça en détail

 

[Hasco]

Bonjour le fil,
Bonjour @Doublenico79 (79 des Deux-Sèvres ?) , @JHA

Dans le fichier joint j'ai commenté chaque étape des requêtes. @Doublenico79, laissez traîner votre pointeur de souris sur le nom des étapes pour faire apparaître l'info-bulle explicative de l'étape.
Si votre éditeur power query ne vous présente pas la barre de formule des étapes, au-dessus de la partie du milieu, voir son menu affichage.

Il y a deux étapes qui nécessitent une intervention dans la barre de formule :
1 - l'étape délaboration des listes de dates de la requête RQ_Events :

= Table.AddColumn(#"Type modifié", "Dates", each List.Dates( [Début], Duration.Days([Fin]-[Début])+1,#duration(1,0,0,0)))

2 - l'étape de regroupement pour ne conserver que les évènements.

= Table.Group(#"Listes développées", {"Dates", "Event Manager"}, {{"Events", each Text.Combine(_[Event],"#(lf)")}})

Dans une deuxième requête PQ_Events (2), faite pour l'exemple, cette dernière étape est décomposée en plusieurs qui peuvent être réalisées avec l'interface à la souris et sans intervention dans la barre de formule.

Pour la mise en forme, c'est excel qui s'en occupe pour la majeur partie. Pour les mises en formes conditionnelles voir dans le menu idoine du ruban (Accueil/style/bouton 'Mise en forme conditionnelle').

Cordialement

 

[Doublenico79]

Vous êtes vraiment trop forts et sympa ici (mais je le savais déjà).
Je vais regarder en détails tout ça