transposer une formule excel en vba?Résolu

Question

Bonjour!

voilà, j'ai une formule dans mon fichier excel (elle me permet de faire une moyenne)

ma question est-il possible de la retranscrire en vba (ai essayé mais telle quelle ca va pas!) afin de ne pas avoir de formules visible dans mon classeur. 

voici ma formule:
=ARRONDI.SUP(((SOMME(SauvegardeVente!B3:BB3)/NBVAL(SauvegardeVente!B3:BB3))*1,1);0)


cette formule va de la ligne 3 à la ligne 45...


merci pour vos conseils!

us_30 · Accepted Answer

Bonjour Fabian123 et Renfield (sans d, je vais le copier 100 fois? - :),

Oui... si tu veux une (ou des) idée meilleur, je peux te proposer beaucoup mieux... Bien sur, il faut développer des notions un peu plus poussées, pour passer dans le calcul d'une probabilité... Passage obligé, si on veut être sérieux... 

=

Mais avant, je propose une petite simplification au code proposé grâce à la fonction MOYENNE à la place de SOMME/NBVAL... Le calcul est identique, mais plus court avec :
Range("A1").FormulaLocal = "=ARRONDI.SUP((MOYENNE(B3:BB3)*1,1);0)"
Range("A1").Value Range("A1").Value


Maintenant, est-ce que prendre un petit peu au-dessus de la moyenne est la meilleure façon de faire ? Réponse : oui ! ? mais? la vraie question est : Combien en plus de la moyenne faut-il prendre ? (Sous entendu pour être le meilleur.) ? Et là, il faut faire appel au calcul de probabilité, et opérer aussi un choix? De plus, on peut faire soit simple, en prenant des hypothèses réductrices, soit plus sophistiqués en essayant de tenir compte de plus de paramètres? 

Je vais n'en tenir déjà pour une première approche au cas simplifié? 

Le calcul est très simple, puisse qu'il repose sur la loi de Gauss? On peut imaginer, que chaque jour de vente est indépendant des uns des autres? ce qui est réducteur, puisse que l'activité de vente peut-être cyclique ou pseudo cyclique, mais sur une année globale sûrement assez juste. Classiquement, il faut connaître la dispersion des données autour de la moyenne. Pour cela, on calcul un paramètre « l'écart type » qui traduit l'étalement des valeurs autour donc de cette moyenne. En effet, on voit bien que si on a sur 3 jours de vente : 100, 100 et 100 articles de vendus, qu'on n'est pas dans la même situation que si on a sur 3 jours de vente : 100, 25, 175 ? Pourtant la moyenne dans les 2 cas est identique, égale à 100? L'écart type donnera une information sur la dispersion? Dans le 1er cas, ce paramètre vaudra 0, et dans le second 75? (c'est pour l'illustrer). Or, il existe un résultat mathématique très classique en probabilité, qui permet de connaître (à l'avance) la répartition des ventes, mais bien sur en terme de probabilité, et sous certaines conditions, c'est la « courbe de Gauss », ou « en Cloche ». 
Pour abréger, si on veut estimer le nombre de vente pour une journée, comment faire ? 
En réalité, mathématiquement, il faut déjà se fixer un seuil de confiance à sa prévision, qu'on quantifie par une probabilité. On dira : j'ai 95% de chance que le nombre de vente dans une journée ne dépasse pas X ventes? Pour le calcul, il suffit de prendre la moyenne auquel on ajoute un certain nombre (non entier) de fois l'écart type, soit : m + k * (écart type) ? En supposant la loi de répartition, il existe des tables donnant le paramètre k. La moyenne m, et l'écart type étant calculé à partir des données connues. Pour 95%, la valeur de k vaut 1,645. 
Sur le 2ième exemple sera donne qu'il y a 95% de chance que les ventes soit comprises entre 0 et 100+1,645*75 = 223 ventes (environ)? Voilà, c'est tout ! 

Maintenant, il faut utiliser les vrais chiffres ! et comme tu utilises Excel, tous ces calculs sont automatiques ! super, non ?!

Voici, le code à utiliser à la place celui au-dessus :

=

Range("A1").FormulaLocal = "=ARRONDI.SUP(MOYENNE(SauvegardeVente!B3:BB3)+ECARTYPE(SauvegardeVente!B3:BB3)*LOI.NORMALE.STANDARD.INVERSE(0,95);0)"
Range("A1").Value Range("A1").Value


Donc si tu veux changer la probabilité pour réduire le risque de se tromper, tu remplace 0,95 par 0,99 (99%)?

IL faut tout de même prendre quelques précautions dues à l'hypothèse réductrice prise (déjà dit) consistant à considérer que la loi de variation suit une loi de Gauss? Si les ventes sont manifestement cyclique (par exemple, les machins vendus sont des parapluies ! ? en été !). Si la variation semble très dispersée (faire un graphique des fréquences)? Dans ce cas, seul la connaissance des données réelles peuvent permettent d'analyser mieux les choses?

Voilà pour une première approche plus précise, mais le champ de l'analyse peut aller encore beaucoup loin, pour une meilleure prévision?

Amicalement,
Us.

Renfield · Answer

tu peux faire ce genre de chose :

range("A1").FormulaLocal = "=ARRONDI.SUP(((SOMME(SauvegardeVente!B3:BB3)/NBVAL(SauvegardeVente!B3:BB3))*1,1);0)"
range("A1").value = range("A1").value

ca te rend autonome vis a vis de toute eventuelle future modif de ta formule ^^



NB. Formula a préferer à FormulaLocal...
pour cela, utiliser la formule:

=ROUNDUP(((SUM(SauvegardeVente!B3:BB3)/COUNTA(SauvegardeVente!B3:BB3))*1.1),0)

us_30 · Answer

Bonsoir,

Je réagi à la formule, qui me semble passablement érronnée... Visiblement, cela correspond à un calcul d'une moyenne... Somme / nb ... mais dont l'objectif est d'avoir un arrondi à l'entier supérieur... OR, il est utilisé (à tord?) un coefficient : 1,1... JE me pose vraiment la question de la raison de ce coefficient, car mathématiquement cela ne veut rien dire... et comme le nom "SauvegardeVente" fait penser à de la compta... ATTENTION ! au compte faux...

Simple application pour l'illustration :

si on a : 100 et 200 ... LA formule proposée renvoi : 165 au lieu de 150...

Je veux juste mettre l'accent sur ce calcul bizarre qui est peut-être justifié pour le pb posé, par ailleurs...

Amicalement,
Us.

cs_Fabian123 · Answer

Bonjour US30, Renfield,

-> Renfield, merci pour le code, je n'ai pas encore eu le temps d'essayer, mais je te tiens informé!

-> US30, pas d'inquiétude à avoir, ne t'en fait pas, je ne traffique pas de comptes!

voici l'explication de la formule (mais si tu as une autre idée, je suis preneur!!)

j'ai donc une "boutique", ouverte une fois par semaine, je fait un petit logiciel, une sorte de petite caisse enregistreuse.

chaque semaine je transfère donc le total de mes ventes (les quantités vendues de chaque article) dans une feuille excel.
cette feuille contient une colonne pour chaque semaine (de la semaine 1 à  52)

la petite formule me renvoie une moyenne des articles vendus, que j'arrondi au chiffre supérieur (difficile de prendre 1,2 paquet de machintruc!) et je rajoute 10% à ce chiffre. Je me dis que la quantité renvoyée par cette formule me permet de savoir combien d'article sont a retirer du stock pour les mettre dans la boutique (la boutique ou les ventes se font n'étant pas à côté du stock, c'est pour éviter de prendre chaque semaine tout le stock avec nous!) 

Mais si tu as une autre idée de formule pour me permettre de savoir combien d'articles prendre (en gardant une marge de sécurité), je suis preneur!!

Encore merci pour votre aide et vos précieux conseils!

Renfield · Answer

us_30, killer de proba.

c'est noté.

cs_Fabian123 · Answer

Alors là, merci!!!

moi qui été juste parti sur un petit calcul de moyenne...!

us_30 · Answer

Bonsoir Renfield et Fabian,

"killer de proba", voilà un métier bien aléatoire...
... de rien Fabian. Bonne continuation,

Amicalement,
Us.

Transposer une formule excel en vba?

7 réponses

Votre réponse

Discussions similaires