Calculer le nombre de périodes qui se chevauchent
Bonjour à tous,
Voici mon problème :
J'ai un tableau avec 3 colonnes, il y a dans ce tableau le nom d'un agent et les dates durant lesquelles il a dû effectuer le remplacement d'un autre agent.
A : nom d'un agent
B : date début période de remplacement
C : date fin de période de remplacement
Il peut arriver qu'un même agent effectue plusieurs remplacements en même temps : les dates ne sont peut-être pas les mêmes mais les périodes peuvent se chevaucher.
Je cherche une formule qui me permette de calculer le nombre de fois qu'un agent a effectué des remplacements sur des périodes égales ou se chevauchant.
Ex :
A B C
Marie 01/01/15 20/01/15
Marie 04/02/15 07/02/15
Marie 05/02/15 15/02/15
Dans un autre petit tableau à coté, j'ai la liste de tous les agents et je veux qu'à la deuxième colonne de ce nouveaux tableau il y est le nombre de fois où l'agent correspondant a fait plusieurs remplacements en même temps.
Dans ce cas-ci la valeur pour Marie serait 2.
Si une simple formule peut suffire ce serait top, mais un code (assez simple) me va aussi. J'ai tenté des trucs avec les formules matricielles mais j'ai du mal à savoir comment formuler avec les dates.
Voilà, merci d'avance !
Bonsoir,
Avec un fichier, ce serait mieux !
Mais questions de base :
1) Dans ton exemple, je ne vois qu'1 chevauchement et non 2, si tu veux parler en termes de jours [rappel qu'un minimum suffisamment elevé de précision de langage est requis], cela fait 3. Il y donc lieu de bien définir ce qu'on compte et comment (selon quelles règles).
2) C'est connexe, le constat précédent concernait des valeurs brutes par rapport aux dates mentionnées. Mais la notation des périodes implique que l'on travaille le samedi et le dimanche. Ceci est à confirmer ?
Cordialement
Bonsoir MFerrand,
Désolée j'espérais être assez précise. Voici le fichier excel.
Donc en fait oui ce que je cherche en fait c'est le nombre de jours durant lesquels une même personne effectue des remplacements différents.
En gros on veut calculer pour chaque remplaçante (il y en aura plusieurs) le nombre de jours où elles ont eu à gérer différents remplacements.
Ensuite non il ne faut pas compter les jours de weekend.
Voilà, j'espère ne rien manquer.
Plutôt que faire une combinaison compliquée de fonctions avec lesquelles je n'étais pas sûr d'aboutir ou d'utiliser des calculs intermédiaires pour épurer les dates et périodes des jours de week-end, j'ai considéré que vba était une solution plus souple et plus précise, mais une macro ordinaire en la matière ne rendant pas les mêmes services, je te propose une fonction personnalisée qui fera le calcul : NBJOURSCHEVAUCH(Nom employée;Tableau des remplacements)
Elle s'utilise comme les fonctions d'Excel, tu mets = devant, puis en argument 1 le nom de la personne (réf cellule qui contient ce nom dans le tableau résultats) et en argument 2 la plage constituée par le tableau. Dans ton tableau résultat, tu recopies la formule en tirant vers le bas, comme pour toute autre formule, aussi si ta plage n'est pas une plage nommée ou un tableau Excel (comme ici) il faudra veiller à la mettre en référence absolue.
Il conviendra aussi que le tableau soit trié par nom comme c'est le cas ici. Elle ne renverra de résultat que pour la première série de lignes consécutives correspondant au nom.
Ceci peut être gênant lors de recalculs, si ton tableau est souvent trié différemment. Dans ce cas on fera un aménagement pour que la fonction fasse son tri elle-même.
Il faudra aussi sans doute compléter, car la fonction ne tient pas compte des jours fériés. C'est donc une modification (complément) à prévoir : dans ton classeur une table des jours fériés à prendre en compte, à indiquer en 3e argument à la fonction (comme dans les fonctions d'Excel qui font ce type de calcul).
Tu peux déjà tester et voir si cela rend le service demandé. Et aussi si tu as besoin d'autres compléments qui n'auraient pas été évoqués ou pris en compte. Une fois faite et entrée dans ta feuille, elle m'a fournie d'emblée les résultats que tu indiquais, je m"n suis donc tenu là pour l'instant, mais une gamme de tests un peu plus étendus s'impose...
Cordialement
A+
Ferrand
Merci beaucoup Ferrand, tu as résolu le plus gros du pb, je ne pensais pas avoir une solution aussi rapidement !
Après les compléments dont tu as parlés sont nécessaires comme tu t'en doutais. Surtout pour les jours fériés.
Comment veux-tu procéder ? Je rajoute sur une autre feuille un tableau avec les jours fériés ?
Concernant la condition de toujours trier par remplaçante, cela ne me dérange pas, je remplierai au fur et à mesure mon tableau et je trierai à chaque fois.
En tout cas un gros merci car cette solution correspond parfaitement à ce que je recherchais.
A+ !
Aurélie
Je viens de tomber sur un problème.
Lorsque je mets Marie à toutes les lignes, le résultat donne zéro. J'ai fait d'autres tests et parfois le calcul ne se fait pas (voir fichier).
Voici le tb réel, j'ai rajouté le tableau des jours fériés sur une autre feuille.
Encore merci,
Aurélie
Je regarde ça. Mais le tableau était-il trié par nom et date de début ?
La fonction travaille sans subtilité particulière (!) : dans la zone d'un nom, elle balaie les dates de début à partir du 2e remplacement et les compare à la date de fin du remplacement précédent, si la date fin est supérieure à la date début du suivant, elle compte un à un les jours de chevauchement en éliminant les samedi et dimanche.
Bévue de ma part !
Il était tard ! (Enfin, plutôt tôt chez moi, on arrivait au matin ! Mais j'ai vu que tu étais très matinale !
Version initiale rectifiée.
Je vais m'attaquer aux jours fériés.
(Et ensuite, je vais la rendre non dépendante du tri de la plage, c'est plus sûr ! Mais là faut remanier l'ensemble.)
A+
NB- Je retire ma remarque sur le caractère matinal....
Version jours fériés inclus.
L'argument fériés est optionnel. Lorsqu'il mentionné, ce doit être une plage verticale sur une colonne (comme c'est le cas dans ton modèle).
NB- Je l'ai nommé Fériés. Je n'ai pas testé (sauf le fait que l'argument étant omis, la fonction fonctionnait normalement [ceci pour vérifier que la façon dont je le teste dans la fonction n'entraînait pas d'erreur]).
Pause Excel : Je m'attaque à une version trieuse dès que je peux m'y remettre.
Génial ça fonctionne parfaitement ! Je vais voir au fur et à mesure si je ne rencontre pas de soucis mais après quelques essais je n'ai rien remarqué ! Merci beaucoup !
Et pour le caractère matinal, tout dépend du fuseau horaire
En tout cas merci beaucoup, cette version là me satisfait à 100% !
A bientôt !
Bon finalement je suis allée trop vite, les jours fériés ne sont pas pris en compte. Comment dois-je mentionner l'option fériés ?
Là j'ai fait : =NBJOURSCHEVAUCH(K2;Tableau2[[Remplaçante]:[Fin]];Fériés)
De plus, le calcul ne se fait pas toujours correctement, j'ai mis l'exemple dans le fichier.
Je n'avais pas prévu que le cas où il y ait 2 mêmes dates de début. Dans ce cas il faut que la colonne date de fin soit également triée.
Je vais revoir en conséquence, le tri fait par la version en cours.
J'espère qu'il n'y a pas de triple remplacement !
Pour les jours fériés, ils sont bien reconnus, mais curieusement la commande de sortie de boucle dans ce cas n'est pas effectuée. Je n'ai pas d'explication, j'ai contourné le problème.
Chez moi, 25 aussi, mais 01h57 seulement.
A+
La même, avec un petit correctif, car il n'est pas exclu que la date de début soit supérieure à celle du remplacement précédent mais que la date de fin en soit inférieure. Et dans ce cas le tri ne suffit plus à garantir le résultat.
Donc inutile de trier sur la 3e colonne, le tri sur le nom et le début suffit.
Mais je vais quand même bien finir par boucler la version qui fait son tri !
Pas cette nuit, je m'arrête.
A+
Je trouve un pb quand une période est comprise dans une autre.
Ex : du 20/07 au 28/07 et du 22/07 au 24/07
Dans ce cas ci au lieu de compter 3 (du 22 au 24) le programme compte 5 (il compte du 22 au 28).
(j'ai rajouté une mini macro pour le tri)
Lis mon post précédent, c'est le cas (non encore rencontré) que j'évoquais.
En principe la dernière version devrait le traiter.
Je regarde dès que possible. Pour le moment j'essaie de boucler la version qui n'aura pas besoin de tri.
A+
La fonction était bien rectifiée... mais la rectification ne l'était pas !!
Je livre la dernière fonction par la même occasion. J'ai légèrement modifié le nom pour que les deux puissent coexister.
Et j'arrête là-dessus pour aujourd'hui (je commence à chercher des erreurs alors que ce n'est pas cette fonction qui est tapée !!)
Bien cordialement
Ferrand
Le tri automatique fonctionne parfaitement ! Merci beaucoup pour cette grande aide, en espérant ne pas t'avoir trop cassé les pieds ! J'ai des idées d'améliorations, mais dis moi si ça t'intéresse et surtout si tu as le temps, dans tous les cas encore merci merci merci !
A bientôt peut-être !
Aurélie
Honnêtement, quand je suis tombé sur ta question, j'ai commencé à répondre en imaginant qu'il s'agissait de quelque chose facile à régler avec des moyens simples. Ce n'était en fait pas si simple et le parcours révélait quelques chausses-trappes, ce qui a-contrario rendait la question plus intéressante à régler (j'espère qu'elle l'est effectivement !)
Si tu as des idées d'améliorations, je suppose qu'elles t'intéressent au premier chef, mais je suis naturellement preneur de tout idée susceptible d'élargir le champ d'investigations. La résolution d'un problème sur le forum n'est nullement un rapport de type client-fournisseur mais un échange égalitaire où chacun apporte et reçoit ou trouve des éléments nouveaux.
Bien cordialement à toi et à une prochaine collaboration.
Ferrand
Je suis en tout cas épatée par ton travail ! J'évite à chaque fois le VBA mais il faudrait que je m'y plonge (mais la route sera longue...).
Enfin, si cela t'intéresse, voici les pistes d'améliorations auxquelles je penses :
1. Il s'agit en fait d'une mise en forme conditionnelle, mais la mise en forme est liée à ton code : remplir d'une couleur les lignes correspondant à des périodes de chevauchement et que les couleurs varient en fonction des remplaçantes.
2. Rajouter un paramètre pour le calcul : définir la période sur laquelle on veut que le calcul s'effectue
Je te remercie encore, cette collaboration fut agréable et très intéressante !
A+
OK
Mais pour débroussailler : si couleur par assistante, c'est autant de MFC que d'assistantes (une par couleur) même si la formule est identique.
Formule faisable je pense si triée par nom et date début (encore qu'il faudra épurer les week-end sur ces dates) ; ou plus facile : que la formule utilise une colonne (éventuellement masquée) pour signaler les lignes répondant à la MFC (cela fonctionnerait sans tri).
Pour être plus puriste, faire une fonction spécifique du même type que celle déjà faite, mais conçue pour MFC (donc renvoyant une valeur booléenne, ce qui simplifie en principe).
Pour la période d'application du calcul, il faut introduire une option dans la fonction. Voir sous quelle forme tu veux fournir l'informations.
A+