Création d'un planning en mois à partir de dates en jours

[pingouinal]

Bonjour le forum,

J'ai un petit problème qu'encore une fois je ne sais dire dans un titre explicite. Donc désolé pour celui que j'ai mis.

Mon souci est le suivant : je pars d'une base de données comprenant un nom de produit, une date de début, une date de fin et le nombre de quantités pour la période. J'aimerais ensuite pouvoir créer un planning (en gros un tableau avec les produits en ligne et les mois en colonnes) me montrant le nombre de quantités par mois.
Bien sûr j'ai pensé au tableau croisé dynamique, mais le problème est que dans ma base de données je n'ai pas le détail des quantités par mois. Je n'ai d'ailleurs pas de mois en soit, mais des dates au format jours. J'ai par exemple 60 produits entre le 01.01.2011 et le 31.03.2011. Le résultat souhaité pour ce cas serait 20 produits par mois (les quantités sont réparties équitablement entre les mois si ceux-ci sont complets).

Je bloque donc sur détailler les dates pour en faire des mois, et comment diviser les quantités au prorata.

Comme je pense ne pas avoir été clair dans mes explications, je vous joins un fichier plus explicite.

D'avance merci pour votre aide.

 

[hoerwind]

Re : Création d'un planning en mois à partir de dates en jours

Bonjour,

L'énoncé de la question et l'exemple ne me semblent pas suffisamment explicites.

Faut-il faire une répartition au prorata des jours ou des demi-mois ?
Sous l'exemple les délais commencent toujours le 1er ou 15 du mois et se terminent toujours le 15 ou le dernier jour du mois, mais est-ce toujours le cas ?

En pièce jointe une proposition de répartition au prorata des jours.
Il se pourrait (c'est même évident) que pour certains délais la somme des quantités réparties ne soit pas tout à fait identique à quantité globale, mais ce sera également le cas si la quantité globale n'est pas divisible par le nombre de jours ou de demi-mois.

C'est aisément corrigible, mais il y aurait lieu de préciser préalablement:
- au prorata des jours ou des demi-mois ?
- que faire d'un reliquat éventuel d'une division ?

 

[pingouinal]

Re : Création d'un planning en mois à partir de dates en jours

Bonjour hoerwind,

Tout d'abord merci beaucoup pour ta réponse.

Pour répondre à tes questions :
- je me rends effectivement compte que les dates sont forcément des débuts, milieux ou fins de mois, désolé pour le manque de détails dans mon premier énoncé. Je confirme donc que les dates sont forcément des quinzaines "standards" c'est à dire commençant en début ou en milieu de mois
- comme il s'agit d'une division, le relicat devrait être dans les décimales, donc avec l'arrondi cela devrait se solder tout seul.

J'ai tester ton fichier qui est une très bonne base, mais j'ai noté deux points pour lesquels j'aurais de nouveau besoin de ton aide :

- le premier est lieu au fait que les périodes sont de multiples de quizaines. J'aurais besoin d'avoir la même quantité par demi-mois. Par exemple si j'ai 100 unités du 1er janvier au 28 février, il me faudrait comme résultat 50 en janvier en 50 en février (même si janvier à 3 jours de plus).
- le deuxième point concerne les entêtes de colonnes pour le planning. Pour janvier tu as mis la valeur "01.01.2011". Serait-il possible selon toi de supprimer la référence à l'année? Ce fichier sera utilisé tous les ans, je souhaiterais donc ne pas avoir à changer l'année dans les 14 cellules à chaque fois.

Encore une fois je te remercie d'avance pour ton aide.

 

[hoerwind]

Re : Création d'un planning en mois à partir de dates en jours

Re,

Je note donc qu'on travaille par demi-mois (ou quinzaine).

Il peut il y avoir des reliquats.
Exemple : une quantité de 20 à répartir entre 3 quinzaines renverra 3 fois 7, mais avec décimales 3 fois 6.66, soit visuellement au total 19.99

Pour les dates il faut nécessairement les années, faute de quoi il n'y a pas moyen de faire une répartition sur une période allant de novembre à mars, par exemple.

Je regarde si je trouve une formule pouvant faire cette répartition.

 

[pingouinal]

Re : Création d'un planning en mois à partir de dates en jours

Re,

Pour ce qui est des reliquats, ce n'est pas très grave, comme je n'affiche pas les décimales, le total affiché sera bien de 20. Par contre j'aurais effectivement 7 affiché à chaque mois (soit un total théorique de 21), mais vu que les quantités seront certainement assez grande, ça ne dérange pas.

Je me contenterai donc des années comme elles sont maintenant. Après tout ce n'est pas ça le problème dans ce fichier.

Merci d'avance pour ta formule.

 

[pingouinal]

Re : Création d'un planning en mois à partir de dates en jours

Re hoerwind, et re les éventuels autres lecteurs de ce fil,

En essayant de trouver une solution de mon côté, je viens de réaliser qu'il y avait un autre problème : un même produit peut apparaître sur plusieurs périodes. Dans certains rare cas, il peut même arriver que plusieurs périodes pour un même produit se croisent (exemple 24D37 dans le fichier ci-joint).
Du coup je suis complètement perdu dans la recherche de formule. Peut-être qu'une macro serait plus appropriée dans ce cas.

D'avance merci pour votre aide.

 

[hoerwind]

Re : Création d'un planning en mois à partir de dates en jours

Re,

Un essai en pièce jointe.
A vérifier quand même !

S'il faut un mot d'explication pour les formules : ne pas hésiter.

Edition : je n'avais pas pris connaissance de ton dernier message.
Regarde si le problème se pose avec mon dernier exemple, si oui joint le fichier avec un mot d'explication.

 

[pingouinal]

Re : Création d'un planning en mois à partir de dates en jours

Re hoerwind,

Merci beaucoup pour ce fichier, c'est exactement la répartition que je veux.
Par contre, comme je le dis dans mon message précédent j'aurais besoin de fusionner les donner pour un même produit. Je joins le fichier d'exemple, je l'avais oublié tout à l'heure.
Le problème est que ta formule est lié à la ligne en cours, et j'aurais besoin de la liée au numéro de produit. Par contre je ne vois pas comment le faire. Aurais-tu une idée?

Merci d'avance.

 

[hoerwind]

Re : Création d'un planning en mois à partir de dates en jours

Re,

Est-ce qu'une mise en page comme sous la pièce jointe pourrait te convenir ?
Si les résultats doivent se trouver sur la même ligne, pourquoi ne pas le faire aussi pour les données ?

Ainsi en additionnant deux formules, telle que celle proposée sous ma dernière pièce jointe, on devrait obtenir le résultat souhaité

Si cette mise en page ne peut être acceptée, je crains qu'on part vers une sérieuse usine à gaz !

Edition : pour tes prochaines pièces jointes, songe à modifier le nom du fichier, c'est plus facile pour s'y retrouver

 

[pingouinal]

Re : Création d'un planning en mois à partir de dates en jours

Re,

A priori, cette mise en page est faisable mais le problème est qu'il faut prévoir large, certain produits peuvent apparaître pendant beaucoup de périodes, pas seulement 2 comme dans l'exemple.
C'est pour ça que j'ai également pensé à la macro, mais là je suis encore plus perdu.

 

[hoerwind]

Re : Création d'un planning en mois à partir de dates en jours

Re,

Si tu as plusieurs plages de dates pour une même référence cela devient effectivement plus complexe.
Il me semble aussi que dans ce cas il vaudrait mieux se tourner vers la macro, mais là je ne puis t'aider.

 

[pingouinal]

Re : Création d'un planning en mois à partir de dates en jours

Il n'y aura pas plusieurs pages de dates pour une même référence, mais disons que cela peut aller jusqu'à 6 ou 8.
Donc si une âme charitable spécialiste de la macro voulait bien jeter un coup d'oeil à mon fichier, ça m'aiderait beaucoup.

Hoerwind, je suis quand même intéressé par la formule dont tu parlais avec la nouvelle mise en page. Ca serait au moins un début pour les références qui n'ont pas plus de deux ou trois dates.

D'avance merci.

 

[hoerwind]

Re : Création d'un planning en mois à partir de dates en jours

Bonjour,

Il me semblait t'avoir donné la méthode à suivre sous un précédent message :
- Insérer trois lignes à droite de la colonne D (début, fin et quantités)
- Dans la formule en ligne 1, ajouter la nouvelle plage sous la fonction MAX
- Dans la formule en I2, copier la formule existante et la coller à la suite de celle-ci en la faisant précéder du signe +
- Dans la partie de formule collée, remplacer les références B, C, D respectivement par E, F et G
Vois l'exemple sous la pièce jointe.
- Copier cette nouvelle formule dans tout le tableau

Même procédure pour des plages complémentaires de dates.
S'il y en a plusieurs plages il serait à conseiller de nommer ces bouts de formules (menu Insertion - Nom - Définir) et de faire appel à ces formules par le nom qui leur a été attribué

Vois également la proposition de Plan (menu Données) qui a été créé
En cliquant sur le "-" dans la marge supérieure les plages de dates sont masquées, ce qui facilité la lecture du planning
Le "+", qui a remplacé le "-", permet d'ouvrir ces plages afin d'en ajouter ou de les modifier

 

[pingouinal]

Re : Création d'un planning en mois à partir de dates en jours

Bonjour hoerwind,

Merci beaucoup pour cette formule. Je vais regarder pour la dupliquer pour 6 ou 8 périodes en renommant des parties.

Encore merci pour ton aide précieuse.

 

[pingouinal]

Re : Création d'un planning en mois à partir de dates en jours

Bonjour tout le monde,

Je reviens vers mon sujet avec cette fois-ci une question pour les pros de la macro.
Pour pouvoir adapter mon fichier aux formules que m'a donné hoerwind, j'ai besoin de faire un transfert. J'ai dans un onglet toutes mes données dans une liste verticale et j'aurais besoin de le transférer dans une liste horizontale basée sur les modèles (dans le fichier joint : transfert de l'onglet 1 à l'onglet 2), c'est à dire une seule ligne par modèle avec les dates et quantités dans les colonnes de droite.
L'exemple joint est relativement simple, mais dans la réalité je peux avoir énormément de modèle et un nombre de durées indéterminé (entre 1 et 15). Je suis donc complètement perdu.

Ma question est donc: quelle macro pourrait me faire passer de l'onglet 1 à l'onglet 2?

D'avance merci à tous ceux qui pourront m'aider.