RechercheH imbriquées dans une moyenne ? Comment faire
Bonjour à tous,
Je cherche à automatiser le calcul de moyenne grâce à des rechercheh imbriquées dans la formule de la moyenne.
je m'explique, pour un produit donné, chaque semaine j'ai un chiffre qui correspond aux ventes de la semaine, pour chacun de ces produits, j'ai une date de lancement qui correspond à la semaine à laquelle le produit a été mis en vente
Je voudrais pouvoir calculer la moyenne des ventes depuis cette date de lancement jusqu'à la date du jour (qui est elle-même traduite en numéro de semaine)
Pour cela, j'ai un onglet supplémentaire avec les ventes du produit.
J'effectue dans une moyenne pour laquelle les valeurs correspondent à la rechercheH de départ (valeur cherchée = ventes correspondants à la semaine de la date de lancement) et à la rechercheH d'arrivée (valeur cherchée = ventes correspondants à la semaine de la date du jour).
Néanmoins, ma formul me calcul uniquement la moyenne entre la valeur de départ ET la valeur d'arrivée, sans moyenner les valeurs entre les 2.
Par exemple : Si je veux la moyenne entre Semaine 20 ET semaine 25, ma formule me retourne la moyenne des deux valeurs semaine 20 ET 25 et non lamoyenne des valeurs semaine 20, 21, 22, 23, 24 et 25.
Je pensais seulement modifier le ";" par ":" dans la formule de la moyenne mais la rechercheH ayant pour but de renvoyer une valeur précise, cela me donner un message d'erreur ...
A part une macro (pour lesquelles je suis novice), je ne trouve pas d'issu à mon problème ?!
Merci d'avance pour votre aide
Bonjour et bienvenue sur le forum culta,
Pas besoin de macro. Une solution avec la fonction DECALER : formule à mettre en F4 :
=MOYENNE(DECALER(ventes!$G$4;;EQUIV($C$2;ventes!$G$1:$O$1;0)-1;;EQUIV($E4;ventes!$G$1:$O$1;0)-EQUIV($C$2;ventes!$G$1:$O$1;0)+1))Bonjour Vba-new et merci beaucoup pour ce retour rapide.
j'essaie de comprendre et de mettre en pratique cette formule dans mon fichier de travail et vous tiens au courant du résultat !
Merci beaucoup !!!
Re,
Pas de quoi !
Juste un conseil, si tu ne le sais pas déjà, pour comprendre une formule semblant incompréhensible, un outil très utile : l'évaluateur de formule -> Onglets Formules/Audit de formules/Evaluation de formules
Super je vais tester Merci !!
Il y a juste une chose à laquelle je n'arrive pas à répondre, c'est lorsque le délai entre la semaine de lancement et la semaine du jour est de 1, le calcul de fonctionne pas :"#ref!"
Par exemple, si mon produit est lancé semaine 24 et que nous sommes semaine 25, je n'arrive pas à obtenir la moyenne des deux ... càd moyenne entre ventes de la semaine 24 et celle de la semaine 25
Je vais m'y plonger plus longuement mais si vous avez la solution, je suis preneuse !
Dans tous les cas merci beaucoup ! je suis épatée par votre rapidité de réponse ! je pensais n'être pas très claire dans ma demande.
merci beaucoup
Bizarre chez moi ça marche, voir fichier joint (si j'ai bien compris)culta a écrit :Il y a juste une chose à laquelle je n'arrive pas à répondre, c'est lorsque le délai entre la semaine de lancement et la semaine du jour est de 1, le calcul de fonctionne pas :"#ref!"
Vous avez parfaitement compris
En faite je pense avoir compris le problème : dans mon fichier réel de travail, je veux faire la même chose avec des moyennes de prévisions de ventes (pour pouvoir comparer prévision et réel)
Comme mon onglet des prévisions est construit différemment que mon onglet des ventes, le formule ne doit plus fonctionner.
Car en effet, elle fonctionne à merveille sur ma colonne des moyennes de ventes, mais malheureusement il y a un beug sur celle des prévisions
(Cf. fichier ci-joint)
Je suis actuellement dessus mais il faut que je m'habitue à la formule EQUIV que je ne maitrise pour l'instant pas du tout !
merci d'avance
Effectivement, la structure des données entre les ventes et les prév différant, ça change la donne.
Dans ta feuille de prévisions, on peut tomber plusieurs fois sur le même numéro de semaine. Donc au lieu de chercher le numéro de semaine, on cherchera l'année+semaine sur la ligne 3 de ta feuille Prévisions. En plus, l'ordre des semaines est inversé par rapport à la feuille des ventes.
Je ne peux donc que vivement te conseiller d'utiliser la même structure pour les 2 feuilles.
Sinon voici quand même une solution. Formule à mettre en G4 de la feuille de suivi :
=MOYENNE(DECALER(Prévisions!$E$4;;EQUIV(ANNEE(C1)&$C$2;Prévisions!$E$3:$CG$3;0)-1;;EQUIV(ANNEE(C1)&$E4;Prévisions!$E$3:$CG$3;0)-EQUIV(ANNEE(C1)&$C$2;Prévisions!$E$3:$CG$3;0)+1))A toi de voir.
Je te remercie, le problème est que je ne pourrai changer la structure des données sur l'onglet des prèv.
Je peux peut être changer celle des ventes réelles (celle-ci étant issue d'une requête).
Ce que je peux faire c'est structuré de la même manière mon onglet des prévisions, mais les dates resteront inversés ; ce que je veux dire par là c'est que dans mon onglet ventes réelles j'irai toujours en décroissant (25.24.23.22.21 etc ...) alors que dans mes prévisions, j'irai toujours en croissant (24.25.26.27 ...)
Ceci du fait que mes données de ventes réelles sont issus d'un requête prédéfinie.
Serait-il possible de calculer les moyennes des prévisions mais de manière inversée à la première.
En fait, j'ai vraiment du mal à maitriser la formule DECALER & EQUIV car je ne les connaissaient pas auparavant.
Ta formule est vraiment parfaite au niveau des ventes réelles, j'aimerais juste pouvoir l'adapter pour mes prévision et donc l'inverser ; pour la structure, je peux en effet faire en sorte de n'y afficher qu'une seule année, donc une seule fois le numéro de la semaine. Est-il possible d'après toi de faire fonctionner la formule avec un ordre croissant des semaines ?
Je te remercie vivement, tu as en quelques sorte atténué le calvaire que mon cerveau subit à essayer de contourner mes rechercheH imbriquées dans ma moyenne !! Un grand grand merci à toi et à ce forum.
Dans l'attente de te relire, merci
culta a écrit :Serait-il possible de calculer les moyennes des prévisions mais de manière inversée à la première.
Eh bien c'est déjà fait dans la formule que je t'ai donnée précédemment. Suffit de mettre 30 en E4 pour t'en convaincre...culta a écrit :Est-il possible d'après toi de faire fonctionner la formule avec un ordre croissant des semaines ?
Autant pour moi c'est que je n'ai plus le fichier sous le coude.
Je teste ça dès demain, je te tiens informé !
Merci pour tout
Bonjour !
J'ai finalement décidé d'essayer de modifier la structure de mes données en ne raisonnant plus en numéro de semaine mais en annéesemaine (201125).
Pour cela j'ai modifié mes en-tête de colonne qui sont désormais toute du format aaaass.
Je n'ai donc pas utilisé la formule année() dans le calcul approprement parlé mais uniquement dans les cellules auxquelles le calcul se réfère (dans mon fichier E4 et C2).
Malheureusement, mon calcul ne fonctionne plus du tout;
Est-ce du au fait que je fais appel à des nombre calculé ?
Je ne comprends pas pourquoi cela ne fontionnerai pas.
Mes en-tête de prévision ET de ventes réelles sont toutes les deux à ce format (mais toujours inversés), les cases références E4 et C2 le sont aussi ...*
Merci d'avance pour votre aide précieuse
Waouh ! J'ai lu mais j'ai pas compris ! Peux-tu joindre ton nouveau fichier ? (un fichier valant mieux que 1000 mots...)
culta a écrit :Malheureusement, mon calcul ne fonctionne plus du tout;
C'est normal, si tu souhaites avoir la moyenne des ventes entre les semaines 25 et 24, s'il n'y a pas de ventes la semaine 25, ça va être difficile
Je dois avoir un problème
mais même en changeant mes dates de manière à ce que cela soit "possible", mon calcul ne fonctionne pas
Cf fichier ci-joint
Difficile de se concentrer en open space ! il faut vraiment que je remette ce calcul à plat mais c'est dommange que je ne puisse pas t'envoyer mon vrai fichier de travail car promis sur celui-ci je n'y arrive décidément pas !
Merci d'avance
Fais attention à ce que les numéros de semaine existent bien. Essaie avec ces nouvelles formules :culta a écrit :mon calcul ne fonctionne pas
En G4 (pour les ventes) :
=MOYENNE(DECALER(ventes!$G$4;;EQUIV(SI($C$2<F4;F4;$C$2);ventes!$G$1:$O$1;0)-1;;EQUIV(SI($C$2<F4;$C$2;F4);ventes!$G$1:$O$1;0)-EQUIV(SI($C$2<F4;F4;$C$2);ventes!$G$1:$O$1;0)+1))En H4 (pour les prévisions) :
=MOYENNE(DECALER(Prévisions!$E$4;;EQUIV(SI($C$2<F4;$C$2;F4);Prévisions!$E$3:$CG$3;0)-1;;EQUIV(SI($C$2<F4;F4;$C$2);Prévisions!$E$3:$CG$3;0)-EQUIV(SI($C$2<F4;$C$2;F4);Prévisions!$E$3:$CG$3;0)+1))A priori, la formule que tu m'as donnée au départ fonctionnerait !
J'ai juste des #DIV/0! quelques fois dans ma colonne "ventes" car parfois les données sont vides quand il n'y a pas eu de vente dans la semaine.
Je retravaille dessus dès demain mais il me semble que travailler sous ce format (201125, 201126 etc ...) me permettra de conserver l'utilisation continuellement d'une année sur l'autre, il faut que j'y arrive
Bon je croise le doigts mais en partant ça avait l'air de fonctionner, il faut juste que je rajoute des conditions devant qui fasse que lorsque la date d'implantation est supérieure à la date du jour ALORS "Pas implanté", un truc dans le genre !
Je te tiens au jus ! Merci pour tout, je suis décidément très épaté par ton implication et ton aide !
Bonne soirée
Bonjour !
ça y est, mon tableau et mes formules fonctionnent !!!
J'ai crée une petite macro qui remplace toutes mes cellules vides (contenu dans l'onglet des ventes réelles) par des 0 pour ne pas obtenir de DIV/0! et j'ai rajouté des conditions lorsque la date d'implantation est supérieure à la date du jour.
Mon fichier est un peu lourd donc j'espère qu'il ne va pas me lâcher
C'est super ! Je te remercie vraiment pour ton aide et ta patience !
Je ne pensais pas trouver une solution si rapidement !
Encore merci, bonne continuation et probablement à très vite sur ce forum !