XL 2016 Imputation d'une valeur à partir d'un calcul de moyenne

[SATbonjour]

Bonjour,
J'ai un tableau Excel avec des offres contenant une date d'offre, une date de commande (si commandée), une date de commande estimée (rempli quand l'offre est créée pour prévoir la production, sous la forme T3 2024 par exemple), un type d'offre et un nom de client. Je souhaiterais que la colonne date de commande estimée se remplisse automatiquement par rapport à la moyenne de durée entre la date d'envoi de l'offre et la date de commande des commandes déjà existantes, et ce par type d'offre et par client (par exemple si je rentre une nouvelle offre avec le type d'offre 3 et pour le client 5, je souhaite que la date de commande estimée soit la date de l'envoi de l'offre + la moyenne des durées des commandes ayant le type d'offre 3 et le client 5).
Merci d'avance de votre aide et n'hésitez pas à me demander des précisions si besoin !

T.

 

[mromain]

Bonjour SATbonjour et bienvenue sur le forum,

Vu ta demande, je pense que la fonction MOYENNE.SI.ENS peut répondre à ton besoin.

Si tu as besoin de plus d'info/aide, tu peux fournir un fichier exemple.

A+

 

[SATbonjour]

Re,

Oui j'ai un peu essayé avec cette fonction mais je n'arrive pas à bien la formuler j'ai l'impression.
J'ai créé ce petit fichier test pour que tu puisses essayer des choses, si tu as la moindre interrogation hésite pas !
Merci d'avance

 

[mromain]

Re,

Tu trouveras un exemple ci-joint.
J'ai mis les données sous forme de vrai tableau.
La formule est appliqué sur toutes les lignes et n'affiche une date de commande estimé que si la ligne n'a pas de date de commande réelle et qu'il y a des lignes existantes pour le même couple type d'offre/client.

A+

 

[SATbonjour]

Re,
C'est parfait ça marche ! Il y a juste un tout petit bémol c'est que les commandes qui ont une vraie date de commande ça n'affiche rien, alors que idéalement ça afficherait quand même le trimestre de commande estimée (malgré qu'on sache la vraie date).
Merci d'avance et encore bravo !

Ps : la requête appliquée à mes données donne :
=SIERREUR(SI([@[Date PO]]<>"";"";"T"&ENT((MOIS(([@[Dt envoi Offre]]+MOYENNE.SI.ENS([Date PO];[Date PO];">0";[Client / DO];[@[Client / DO]];[Type offre];[@[Type offre]])-MOYENNE.SI.ENS([Dt envoi Offre];[Date PO];">0";[Client / DO];[@[Client / DO]];[Type offre];[@[Type offre]])))-1)/3)+1&" "&ANNEE(([@[Dt envoi Offre]]+MOYENNE.SI.ENS([Date PO];[Date PO];">0";[Client / DO];[@[Client / DO]];[Type offre];[@[Type offre]])-MOYENNE.SI.ENS([Dt envoi Offre];[Date PO];">0";[Client / DO];[@[Client / DO]];[Type offre];[@[Type offre]]))));"")

 

[mromain]

Re,

les commandes qui ont une vraie date de commande ça n'affiche rien, alors que idéalement ça afficherait quand même le trimestre de commande estimée (malgré qu'on sache la vraie date)

Il est possible de les garder affichées en modifiant la formule ainsi (pour que le calcul se base que sur les commande déjà passées) :

=SIERREUR("T"&ENT((MOIS(([@[Date d''envoi offre]]+MOYENNE.SI.ENS([Date de commande];[Date de commande];">0";[Client];[@Client];[Type d''offre];[@[Type d''offre]];[Date d''envoi offre];"<"&[@[Date d''envoi offre]])-MOYENNE.SI.ENS([Date d''envoi offre];[Date de commande];">0";[Client];[@Client];[Type d''offre];[@[Type d''offre]])))-1)/3)+1&" "&ANNEE(([@[Date d''envoi offre]]+MOYENNE.SI.ENS([Date de commande];[Date de commande];">0";[Client];[@Client];[Type d''offre];[@[Type d''offre]])-MOYENNE.SI.ENS([Date d''envoi offre];[Date de commande];">0";[Client];[@Client];[Type d''offre];[@[Type d''offre]];[Date d''envoi offre];"<"&[@[Date d''envoi offre]])));"")

A+

 

[SATbonjour]

Re,
quand j'ajouterai des commandes, est-ce que les dates estimée vont s'actualiser avec ces nouvelles données ou elles vont rester figées ? l'idéal serait que ça ne bouge pas évidemment

A+

 

[mromain]

Bonjour SATbonjour,

quand j'ajouterai des commandes, est-ce que les dates estimée vont s'actualiser avec ces nouvelles données ou elles vont rester figées ?

La formule estime la date de commande à partir des commandes :

• passées (dont la Date d’envoi offre est antérieure à la ligne actuelle) ;
• du même Client ;
• du même Type d’offre
• ayant une Date de commande renseignée.

Donc lorsque tu saisis une nouvelle commande :

• si tu saisis une Date d’envoi offre postérieure aux commandes passées, les estimations précédentes ne changeront pas ;
• si tu saisis une Date d’envoi offre antérieure aux commandes passées, les estimations précédentes changeront.

A+

 

[SATbonjour]

RE !
Je viens de parler avec mon chef...je t'explique le problème ,il y a trois cas de figures qui doivent être pris en compte dans la moyenne :
les commandes passées (comme tu l'as fait précédemment et c'est parfait)
les commandes encours (donc qui n'ont pas de date de commande mais une date de validité encore valide) et celles-ci il faut faire le calcul en prenant la différence du nombre de jour entre la date d'envoi de l'offre et la date du jour (donc qui bouge tous les jours)
et enfin les commandes expirées (sans date de commande et où la date de fin de validité est dépassée) et là prendre la différence entre la date d'envoi de l'offre et la date de fin de validité de l'offre
Je sais c'est pas si simple et si il faut réexpliquer en d'autres termes hésite pas ! Merci d'avance pour la réflexion apportée, et joyeux Noël !

A+

 

[mromain]

Re,

Tu dis, pour les commandes expirées de prendre la différence entre la date d'envoi de l'offre et la date de fin de validité de l'offre.
Où trouve-t-on la date de fin de validité de l'offre ?

A+

 

[SATbonjour]

Re,

dans mon fichier de base elle est présente, je l'ajoute au fichier test que je mets en pj.
C'est une date qui varie selon le contrat, elle peut être de 2 mois comme de 2 ans.

A+