Vous utilisez un navigateur obsolète. Il se peut que ce site ou d'autres sites Web ne s'affichent pas correctement. Vous devez le mettre à jour ou utiliser un navigateur alternatif.
XL 2010d'un Planning global faire un planning individuel
Bonjour
Dans l'onglet S24 (semaine 24) se trouve le planning global de la semaine avec tout les chantiers à réaliser, les agents et les véhicules à utiliser
de cette onglet je souhaite extrait les infos pour le planning individuel agent qui lui sera remis chaque semaine avec les chantier réaliser et les horaires
Je souhaite aussi que à chaque fois que je change le nom de l'agent les infos change aussi
mais je n arrive pas à extrait les infos vu qu'il y a plusieurs valeur par agent et je n 'arrive pas non plus avec Vlookplist
merci de votre aide ci joint le lien du fichier Planning-sem-teste.xls
cdl
Si Planning agent!B9 est sélectionné, la formule ci-dessus donne
INDIRECT("'Planning agent'!$A"&ENT((9-1)/6)*6+1)
INDIRECT("'Planning agent'!$A"&ENT(8/6)*6+1)
INDIRECT("'Planning agent'!$A"&1*6+1)
INDIRECT("'Planning agent'!$A7")
c-à-d "Lundi"
Dans Planning agent!B10, ENT(10/6) donne aussi 1, donc on obtient aussu "Lundi". Idem dans Planning agent!B11 et Planning agent!B12.
Dans Planning agent!B15, cela donne
INDIRECT("'Planning agent'!$A"&ENT((15-1)/6)*6+1)
INDIRECT("'Planning agent'!$A"&ENT(15/6)*6+1)
INDIRECT("'Planning agent'!$A"&2*6+1)
INDIRECT("'Planning agent'!$A13")
c-à-d "Mardi"
Dans Planning agent!B21, on obtient
INDIRECT("'Planning agent'!$A"&ENT((21-1)/6)*6+1)
INDIRECT("'Planning agent'!$A"&ENT(21/6)*6+1)
INDIRECT("'Planning agent'!$A"&3*6+1)
INDIRECT("'Planning agent'!$A19")
c-à-d "Mercredi"
La syntaxe de cette fonction est DECALER(cellule ou plage sélectionnée;décalage de x lignes;décalage de y colonnes;nbre z de lignes renvoyées;nbre w de colonnes renvoyées)
DECALER('S24'!$E$4:$K$400 "sélectionne" la plage 'S24'!$E$4:$K$400 (donc celle correspondant au lundi) (7 colonnes, 397 lignes).
x = 0 puisque qu'on a ;;.
EQUIV(.....) renvoie la position de nomjour dans la plage 'S24'!$F$2:$AP$2 (F2="Lundi" (y = 1 - 1 = 0), M2="Mardi" (y = 8 -1 =7), T2="Mercredi" (y= 15 - 1=14) et ainsi de suite).
On ne précise pas z et w, donc la formule renvoie une plage contenant autant de lignes et de colonnes que $E$4:$K$400.
Donc DECALER(....) renvoie la plage correspondant soit au Lundi, soit au mardi, soit au mercredi... Pour visualiser cela, il faut sélectionner par ex Planning agent!B21, ouvrir le gestionnaire de nom, sélectionner plagejour, et cliquer sur la définition en bas de la fenêtre. Des pointillés entoure la plage "mercredi".
INDEX(plagejour;;2) donne la deuxième colonne de plagejour décrit précédemment.
NDEX(plagejour;;3) donne la troisième. Et ainsi de suite.
Dans SOMMELIGNE on a donc, pour tous les jours de la semaine, sauf pour le samedi (et le dimanche ) la somme, ligne par ligne, des colonnes 2 à 7, et pour le samedi, des colonnes 2 et 3 de la plage nomjour. Autrement dit, cela nous dit s'il y a des heures de marquées sur telle ou telle ligne. Si la somme de telle ligne donne 0, c'est que l'agent n'a pas travaillé dans cette entreprise ce jour là.
'Planning agent'!$B$2='S24'!$D$4:$D$400 renvoie VRAI pour chaque ligne de la plage D4: D400 contenant le même nom que B2 (le même agent), FAUX dans le cas contraire. On obtient donc une liste du style {FAUX;FAUX;FAUX;FAUX;VRAI;FAUX;FAUX;FAUX;VRAI;VRAI.......} en fonction de B2 et du contenu de la colonne D.
SOMMELIGNE<>0 renvoie VRAI pour toutes les lignes contenant des horaires pour plagejour. On a donc là aussi une liste de VRAI et de FAUX.
Excel donne VRAI*VRAI = 1, VRAI*FAUX = FAUX*VRAI = FAUX*FAUX = 0.
On obtient donc une liste {0;0;0;0;1;0;0;0;1;1.......}
SI(('Planning agent'!$B$2='S24'!$D$4:$D$400)*(SOMMELIGNE<>0);LIGNE('S24'!$D$4:$D$400)) remplace les 1 par le n° de la ligne correspondante.
PETITE.VALEUR(SI(('Planning agent'!$B$2='S24'!$D$4:$D$400)*(SOMMELIGNE<>0);LIGNE('S24'!$D$4:$D$400));1) renvoie le plus petit de ces n° de ligne.
Et on se retrouve avec DECALER(cellule ou plage sélectionnée;décalage de x lignes;décalage de y colonnes;nbre z de lignes renvoyées;nbre w de colonnes renvoyées).
On part de 'S24'!$E$1, x = le plus petit de ces n° de ligne, y =EQUIV(nomjour;'S24'!$F$2:$AP$2;0)-1, z = 1 (comme E1), w = 3 pour le samedi, 7 pour les autres jours. Comme précédemment, le mieux c'est de visualiser la plage obtenue.
plagejouragt1 donne donc la première plage de plagejour correspondant à l'agent noté dans B2 contenant des heures. On a eu besoin de faire deux tests, 'Planning agent'!$B$2='S24'!$D$4:$D$400, pour ne sélectionner que les lignes correspondant à l'agent en cours, et SOMMELIGNE <>0, pour ne garder que les lignes "travaillées" dans plagejour. Si on ne faisait pas ce dernier test SOMMELIGNE<>0, on aurait la première plage sur la première ligne contenant le nom de l'agent en cours, mais cette plage serait peut être vide (sur ce chantier, l'agent n'a pas travaillé ce jour là, mais un autre jour de la semaine) et cela ne sert à rien de l'afficher.
Autrement dit, plagejour donne toute la plage correspondant à tel ou tel jour, plagejouragt1 donne la 1ère plage incluse dans la précédente, contenant des heures.
Pour plagejouragt2, c'est exactement la même chose, sauf que le 1 à la fin de PETITE.VALEUR(.......;1) est remplacé un 2. On obtient donc la seconde plage incluse dans plagejour, contenant des heures.
Même idée pour plagejouragt3 et plagejouragt4.
On passe aux formules.
Dans Planning agent!B9, on a
qui renvoie le nom de chantier sur la même ligne que plagejouragt1, sauf lorsqu'il n'en trouve pas, et qu'il y a donc une erreur.
Rem : On pourrait certainement faire avec un INDEX('S24'!chantierS;PETITE.VALEUR(SI(('Planning agent'!$B$2='S24'!$D$4:$D$400)*(SOMMELIGNE<>0);LIGNE('S24'!$D$4:$D$400));1)) mais comme on a déjà fait ces tests SI(...) dans plagejouragt1, autant les réutiliser sous la forme LIGNE(plagejouragt1).
Dans Planning agent!B10, on trouve presque la même formule
qui renvoie le nom du second chantier, qui lui, est sur la ligne de plagejouragt2.
Même idée, même méthode en B11 et B12.
On copie le bloc B9:B11 et on le colle dan B15:B18 pour obtenir les chantiers correspondant au mardi, et ainsi de suite en dessous, en faisant un copier-coller par bloc de 4 cellules.
Dans planning agent!C9, on a
Code:
=SI($B9="";"";INDEX(plagejouragt1;COLONNE(A:A)))
qui renvoie le contenu de la première cellule de plagejouragt1, c-à-d le véhicule, car colonne(A:A)=1
On tire cette formule vers la droite et obtient la seconde cellule (heure début matin). Et ainsi de suite vers la droite.
Dans C10, même méthode, mais avec plagejouragt2.
Pour sem-testebisbis du post #14, c'est la même méthode, mais tous les 'S24'!X ont été remplacés par des INDIRECT(sem & "X") avec sem ="'" & 'Planning agent'!$K$3 & "'!" pour aller choisir le n° de semaine donné dans K3, donc la feuille correspondant à cette semaine. Le nom ChantierS a été défini avec INDIRECT(sem & "A:A") de manière à être valable dans tout le classeur, et plus seulement dans la feuille S24. Donc j'ai remplacé INDEX('S24'!chantierS par INDEX(chantierS.
Re : d'un Planning global faire un planning individuel
BONJOUR
merci pour les explications, je vais étudié tout ça pour bien comprend toutes les formules et de pouvoir le refaire seul.
une dernière question, je voulais mettre un filtre dans le 'planning agent' au nom prénom mais ce filtre saute à chaque de fois que je ferme le fichier avez vous une solution et promis je vous embête plus lol!
bonne soirée
Re : d'un Planning global faire un planning individuel
Bonjour
Tu sélectionnes la cellule où tu veux mettre la liste déroulante. Puis --> Données --> Validation des données. Dans la liste déroulante, choisir "Liste". Dans la fenêtre en bas, écrire = suivi de la plage contenant les informations de la future liste déroulante, ou un nom définissant la plage contenant ces informations.
Re : d'un Planning global faire un planning individuel
Bonsoir
Pour améliorer ton fichier, une autre possibilité en pièce jointe, prise là sur le site de J. Boisgontier. Tu écris dans la cellule B2 le début d'un nom, et dans la liste tu obtiens uniquement les noms commençant avec les lettres que tu as déjà écrites (et pas la liste de tous les agents de l'entreprise). C'est beaucoup plus pratique pour ne pas avoir à aller chercher un nom tout en bas d'une liste très longue.
Cela fonctionne avec la liste définie par
Re : d'un Planning global faire un planning individuel
Bonsoir
Mes dernières propositions n'étaient pas complètes, à savoir que certains noms et formules faisaient encore appel à la feuille S24, or il faut systématiquement utiliser maintenant le nom sem et non S24.
avec INDEX('S24'!chantierS. En plus, le nom chantierS n'était valable que dans la feuille S24. Par conséquent, si tu voulais utiliser cette formule pour une autre semaine, c-à-d une autre feuille, cela ne fonctionnait Pas.
et dans les cellules en dessous, dans cette colonne Planning agent ! B.
J'ai corrigé tout cela (les formules dans la colonne Planning agent ! B, à partir de la ligne 9, et le nom chantierS, qui maintenant est valable dans tout le classeur, et est défini avec le nom sem) dans les trois précédentes pièces jointes.
Au passage, une question pour tout le monde :
* Dans les premières pièces jointes de ce fil, le nom chantierS n'était valable que sur la feuille nommée S24.
Dans les dernières versions, il fallait qu'il soit valable sur tout le fichier.
Comme je ne sais pas comment faire cette "bascule" directement dans la fenêtre "Gestionnaire de noms", j'ai procédé comme suit :
* J'ai créé un nouveau nom blablabla valable sur tout le fichier, avec la bonne définition, et je l'ai mis dans les formules.
* J'ai ensuite supprimé l'ancien chantierS, et ai remplacé dans le gestionnaire de noms le nouveau nom blablabla par chantierS.
Les formules de mes dernières propositions ne sont pas "tirables" vers le bas ou vers la droite, entre autre parce que j'ai utilisé un nom différent pour chaque ligne : plagejouragt1avec dans sa définition dans le gestionnaire de noms un PETITE.VALEUR(.......;1) plagejouragt2avec un PETITE.VALEUR(.......;2) sur la ligne suivante plagejouragt3avec un PETITE.VALEUR(.......;3) sur la ligne suivante plagejouragt4avec un PETITE.VALEUR(.......;4) sur la "dernière".
(A chaque fois, c'est presque la même définition, seule change le nombre en rouge ci-dessus)
qui ramène le nom du jour en cours (lundi, mardi, mercredi etc) en fonction de la ligne en cours.
Si tu veux rajouter des chantiers pour chaque jour, il faut :
* créer des plagejouragtx supplémentaires, par ex plagejouragt5avec un PETITE.VALEUR(.......;5) plagejouragt6avec un PETITE.VALEUR(.......;6)
et modifier la définition de nomjour en fonction du nombre d'ateliers
Pour 5 ateliers par jour cela donne
qui renvoie le bon nombre en rouge nécessaire dans le PETITE.VALEUR en fonction de la ligne en cours.
Il ni a plus plagejouragt1, plagejouragt2 et ainsi de suite, mais un seul plagejouragt qui utilise ce lignepetitevaleur (Donc plagejouragt renvoie la même chose que plagejouragt1 en ligne 9, que plagejouragt2 en ligne10, que plagejouragt3 en ligne 11 et ainsi de suite).
qu'il faut valider avec Ctrl+maj+entrer, et qui peut être tirée vers le bas (ce qui n'était pas le cas de la formule matricielle =SI(ESTERREUR(plagejouragt1);"";INDEX(ChantierS;LIGNE(plagejouragt1))) utilisée précédemment).
Dans C9 il y a la formule
Code:
SI($B9="";"";INDEX(plagejouragt;COLONNES($A:A)))
qui peut être tirée vers la droite et vers le bas (ce qui n'était pas le cas de la formule SI($B9="";"";INDEX(plagejouragt1;COLONNE(A:A))) utilisée précédemment).
Le fichier en pièce jointe comporte 6 ateliers pour chaque jour.
Si tu en veux 7 :
* tu défusionnes les dates 13/06/2016, 14/06/2016 etc dans la colonne A.
* Tu insères dans le bas de chaque jour une ligne à chaque fois
* Tu tires toute la plage B14:J14 vers le bas
* Tu copies la plage B9:J15 et tu la colles dans A18 et ainsi de suite en dessous.
Super ! merci pour ton aide pour les chantiers en +
Concernant la dernière formule SOMMEPROD avec les ateliers qui commence par "AAA" super comme formule mais si j'applique cette formule mes autres chantiers ne se calcule plus. As tu une autre solution pour faire la somme des heures chantiers sauf les chantiers commençant "AAA"
Bonjour
ah désolé , je n avais pas vu le J56
super merci beaucoup !
excel s est géniale, et en plus j'apprend plein chose avec ce forum et des personnes comme toi
merci encore
Re : d'un Planning global faire un planning individuel
...
Au passage, une question pour tout le monde :
* Dans les premières pièces jointes de ce fil, le nom chantierS n'était valable que sur la feuille nommée S24.
Après lecture dans un livre sur Excel, j'ai trouvé la réponse, donc je me répond (Cela servira peut être à d'autres) : Il n'est pas possible de modifier directement la portée d'un nom défini dans le gestionnaire de noms, de global (sur tout le fichier) à local (sur une feuille particulière seulement), ou inversement. Il faut définir un nouveau nom (donc en passant par "Nouveau" et pas par "Modifier"), et utiliser la liste déroulante dans la partie zone.
Ce site utilise des cookies pour personnaliser le contenu, adapter votre expérience et vous garder connecté si vous vous enregistrez.
En continuant à utiliser ce site, vous consentez à notre utilisation de cookies.
) la somme, ligne par ligne, des colonnes 2 à 7, et pour le samedi, des colonnes 2 et 3 de la plage nomjour. Autrement dit, cela nous dit s'il y a des heures de marquées sur telle ou telle ligne. Si la somme de telle ligne donne 0, c'est que l'agent n'a pas travaillé dans cette entreprise ce jour là.
