XL 2019 Planning personnel

Guillaume59

XLDnaute Nouveau
Bonjour à tous,

J'aimerais faire un planning personnel comme dans le fichier ci-joint.

1) Onglet du lundi au dimanche puis une synthèse
2) Nom du personnel en colonne A
3) Dans les onglets du lundi au dimanche, j'ai fait une mise en forme conditionnel, si en note X j'ai la couleur associé au salarié, et en colonne BC le total en heure de la journée.
j'ai essayé par le biais de somme_si_couleur mais en vain. Sinon avec un genre INDEX par rapport à la ligne 1 ?
4) L'onglet synthèse, j'aimerai que soit repris les heures apparaissant dans les plages colorées des onglets lundi au dimanche, mais sous forme 08:00 17:00

Un grand grand merci pour votre aide.

Bonne journée à vous,

Guillaume59
 

Pièces jointes

  • planning isabelle.xlsx
    17.5 KB · Affichages: 33

AtTheOne

XLDnaute Impliqué
Supporter XLD
Bonjour à toutes & à tous, bonjour @Guillaume59
j'ai essayé par le biais de somme_si_couleur
A moins que tu ne crées ta fonction VBA pour compter les couleurs (et avec les Mise en Formes Conditionnelles ce n'est pas coton) ce n'est pas une fonction disponible ...
Mais comme tu mets des "x", il suffit de les compter dans ta colonne total par
VB:
=NB.SI($B2:$BB2;"x")
Dans ton tableau de synthèse une formule avec INDIRECT, INDEX et EQUIV fait l'affaire :
Code:
=SIERREUR(INDEX(INDIRECT(B$1&"!BC:BC");EQUIV($A2;INDIRECT(B$1&"!A:A");0));"-")
Le SIERREUR est là pour éviter l'affichage des #REF! quand la feuille du jour n'existe pas, ou les #NA! quand EQUIV échoue.

Regarde le fichier joint
Amicalement
Alain
 

Pièces jointes

  • planning isabelle.xlsx
    21.8 KB · Affichages: 13

GALOUGALOU

XLDnaute Accro
bonjour le fil,bonjour attheone
re guillaume 59

en fonction de ce que j'ai compris, une solution par formule
en colonne BC le total en heure de la journée
je vous propose cette formule, pour compter les cellules non vides, et comme un entier est égal à 24 heures, diviser le résultat par 96 pour compter le nombre de 1/4 d'heure. (cellule résultat au format horaire)
=NBVAL($B2:$BB2)/96
dans la feuille synthese je vous propose une solution avec l'heure de début et l'heure de fin
pour rapatrier les données de la journée de lundi concernant je
pour le début
=SIERREUR(INDEX(Lundi!$B$1:$BB$1;EQUIV(VRAI;INDEX(Lundi!B2:BB2>0;0);0));"")
pour la fin
=SIERREUR(INDEX(Lundi!$B$1:$BB$1;EQUIV("zz";Lundi!$B2:$BB2;1));"")
en cellule P3 pour le cumul
=(C3-B3)+(E3-D3)+(G3-F3)+(I3-H3)+(K3-J3)+(M3-L3)+(O3-N3)
dans le classeur joint je vous ai géré le lundi, tous les autres jours de la semaine reste à faire
évidemment formatez toutes les cellules résultats au format horaire
cdt
galougalou
 

Pièces jointes

  • planning isabelle.xlsx
    18.3 KB · Affichages: 11

AtTheOne

XLDnaute Impliqué
Supporter XLD
Post-Scriptum :

Pour n'avoir que 2 formules dans ton tableau de synthèse, et alléger la lecture des formules, j'ai créer 4 noms définis (j'aime bien ça !) :

ObjetNomDéfinition
Pour renvoyer le jour correspondant à la colonne (change une colonne sur 2)_Jour=INDEX(Synthése!$B$1:$O$1;COLONNE()-MOD(COLONNE();2)-1)
La plage des horaires du jour concerné_Horaire_Jour=INDIRECT(_Jour&"!$B$1:$BB$1")
La liste du personnel du jour concerné (se limite au nombre de personne dans la liste)
Valable s'il n'y a pas de ligne vide intercalée.
_Personnel_Jour=INDIRECT(_Jour&"!$A$2:$A$"&NBVAL(INDIRECT(_Jour&"!$A:$A"))+1)
La plage du planning du jour concerné_Planning_Jour=DECALER(_Horaire_Jour;1;0;LIGNES(_Personnel_Jour); )

Après j'ai ajouté le quart d'heure final aux formules de @GALOUGALOU et j'ai précisé heure d'arrivée - Heure de sortie. Sinon les formules sont celles de @GALOUGALOU dans les lesquelles j'ai glissé les noms définis pour les homogénéiser...

Amicalement
Alain
Modif : Casse pied les émojis automatiques qui te transforment ; ) en ;) du coup j'ai intercalé un espace, et toujours l'orthographe !
 
Dernière édition:

GALOUGALOU

XLDnaute Accro
re attheone
Avec des formules matricielles me semble-t-il.
Je pense qu'il faut le préciser, car si guillaume59 connait, il maitrisera sans problème, dans le cas contraire il faudrait préciser la mise en œuvre,
À tout hasard
Pour valider une formule matricielle, appuyez sur Ctrl Shift Entrée . Une formule matricielle est encadrée par des accolades et les cellules de la matrice du résultat sont automatiquement protégées contre les modifications.
cdt
amitiés Alain
galougalou
 

AtTheOne

XLDnaute Impliqué
Supporter XLD
Re tout le monde
Oups ! j'ai encore oublié que je travaillais avec OFFICE 2021 (Id MS365) où les formules sont matricielles ...
(merci pour ta remarque)

Après un tour sur OFFICE 2007 et deux nouveaux noms (qui sont toujours matriciels) :
ObjetNomDéfinition
N° de la cellule
du 1er quart d'heure
_Arrivée_Jour_Personne=EQUIV(VRAI;INDEX(_Planning_Jour;EQUIV(Synthése!$A12;_Personnel_Jour;0);0)>0;0)
N° de la cellule du
dernier quart d'heure
_Sortie_Jour_Personne=EQUIV("zz";INDEX(_Planning_Jour;EQUIV(Synthése!$A17;_Personnel_Jour;0);0);1)
Plus besoin de valider par CTRL+MAJ+ENTRÉE ...

Voir la pièce jointe

Encore merci
Amicalement
Alain
Modif : encore des fautes d'orthographes !
 

Pièces jointes

  • planning isabelle d.xlsx
    24.8 KB · Affichages: 18
Dernière édition:

Guillaume59

XLDnaute Nouveau
Re à vous,

Wahou, mais quel efficacité.

Je travaille un peu actuellement sur le gestionnaire de nom mais juste pour des listes pour le moment. Je suis loin de tout comprendre ces formules.

Je vous remercie pour votre aide, si précieuse.

Je vais continuer tout cela et vous montrerai d'ici ce week-end la version finale, pour le plaisir de partager cela avec vous.

Bonne soirée à vous,

guillaume
 

AtTheOne

XLDnaute Impliqué
Supporter XLD
Re Guillaume,
Sache que les noms peuvent faire aussi référence à des formules ou des constantes (par exemple tu pourrais créer un nom TVA1 = 0.20)
Quand tu y mets une formule, elle est interprétée comme une formule matricielle (qui renvoie potentiellement plusieurs valeurs.
Une petite explication sur les noms utilisés
_Jour
Le but est de renvoyer le nom du jour qui n'apparaît qu'une colonne sur 2 dans la plage Synthése!$B$1:$O$1​
COLONNE() renvoie le N° de la colonne courante​
MOD(COLONNE();2) renvoie le reste de la division par 2 de COLONNE() (soit 1, 0 , 1, 0 ... )​
On retire 1 pour qu'en B on renvoie 0 et qu'en C on renvoie -1​
du coup INDEX(Synthése!$B$1:$O$1,COLONNE()-MOD(COLONNE();2)-1) renvoie B1 en colonne B et C, D1 en colonne D et E, etc ... Ce qui donne Lundi, Lundi, Mardi, Mardi ...​
_Horaire_Jour
Va renvoyer la plage horaire du jour considéré (il pourrait y avoir des différences de plages horaire certains jours)​
INDIRECT reconstitue une référence à partir d'un texte, le texte est _Jour&"!$B$1:$BB$1" soit pour Lundi : Lundi!$B$1:$BB$1
du coup, en fonction du jour INDIRECT(_Jour&"!$B$1:$BB$1") renvoie la référence à la plage horaire du jour concerné.​
_Personnel_Jour
Va renvoyer la liste du personnel du jour concerné, encore une fois il pourrait y avoir des différences d'un jour à l'autre​
INDIRECT a le même but mais le texte est différent​
_Jour&"!$A$2:$A$"&NBVAL(INDIRECT(_Jour&"!$A:$A"))+1 on compte avec NBVAL le nombre de données dans la colonne A de la feuille _Jour, on y ajoute 1 parce qu'on commence à la ligne 2. s'il y a 6 données dans la colonne on obtient pour Lundi le texte Lundi!$A$2:$A$7
du coup, s'il n'y a aucune ligne vide intercalée, on obtient la liste du personnel pour le jour dit.​
_Planning_Jour
C'est la plage située sous _Horaire_Jour et qui compte autant de lignes que la liste du personnel du jour.​
On utilise la fonction DECALER(Plage, Décalage Lgn, Décalage Col, Nb Lgn, Nb Col).
Ici on se décale d'une ligne, de 0 colonne, le nombre de lignes est celui de la liste du personnel, et on garde le même nombre de colonnes (argument vide). Ce qui donne DECALER(_Horaire_Jour;1;0;LIGNES(_Personnel_Jour); )​
Du coup on a la plage où tu coches les quarts d'heure de présence ...​
_Arrivée_Jour_Personne
Le but est de trouver le n° du premier quart d'heure coché pour une personne et un jour donnés​
Il faut déjà trouver le N° de ligne qui correspond à la personne dans le planning du jour :​
EQUIV(Synthése!$A12;_Personnel_Jour;0) (pour la ligne 12)​
Ensuite renvoyer la ligne du planning qui correspond à ce N° :​
INDEX(_Planning_Jour;EQUIV(Synthése!$A12;_Personnel_Jour;0);0)
Ensuite on teste si chacune des cellules de cette plage est vide (par raccourci >0), on aurait pu tester sur la valeur "x" si l'on était sûr que c'était toujours le cas.​
INDEX(_Planning_Jour;EQUIV(Synthése!$A12;_Personnel_Jour;0);0)>0
(c'est là que la formule matricielle intervient), on récupère ainsi une matrice de VRAI et de FAUX​
Ensuite on renvoie le N° de la première colonne pour laquelle on a VRAI :​
EQUIV(VRAI;INDEX(_Planning_Jour;EQUIV(Synthése!$A12;_Personnel_Jour;0);0)>0;0)
Du coup on a le N° du premier quart d'heure coché​
_Sortie_Jour_Personne
Le but est de trouver le n° du dernier quart d'heure coché pour une personne et un jour donné,​
Même raisonnement mais là on recherche avec une chaîne "supérieure" à "x", on a choisi "zz" en utilisant la fonction EQUIV avec comme dernier argument le 1 : La fonction EQUIV recherche la valeur la plus élevée qui est inférieure ou égale à celle de l’argument valeur_cherchée.​
EQUIV("zz";INDEX(_Planning_Jour;EQUIV(Synthése!$A17;_Personnel_Jour;0);0);1)

Ainsi on a le n° du dernier quart d'heure coché (en supposant qu'il n'y ait pas de vide entre deux quarts d'heure cochés)​

Du coup les deux formules sont nettement simplifiées
Pour les arrivées =SIERREUR(INDEX(_Horaire_Jour;_Arrivée_Jour_Personne);"")
Pour les départ =SIERREUR(INDEX(_Horaire_Jour;_Sortie_Jour_Personne+1);"") (+1 pour avoir l'heure de départ)

Voilà pour les explications complémentaires
Amicalement
Alain
Modif : Et toujours l'orthographe ...
 
Dernière édition:

GALOUGALOU

XLDnaute Accro
re attheone
Bel exemple de démocratisation !!
Cela mériterait d'être vu par tout candidat à la maitrise des formules d'excel. En tout cas je garde le lien précieusement pour le faire partager si l'occasion se présente.
cdt
galougalou
 

Discussions similaires

Réponses
9
Affichages
238
Réponses
4
Affichages
332

Statistiques des forums

Discussions
300 904
Messages
1 988 334
Membres
210 118
dernier inscrit
DARIA23