Bonjour à toutes et à tous,

pour la gestion de stock de pièces dans un magasin maintenance, nous avons un tableau avec les références, PUHT, les consommation mensuelles de l'an passé, le délai d'approvisionnement. Nous avons également le taux de service, de possession et le coût de passation d'une commande (prix à payer pour toute commande).

Je dois calculer le seuil d'alerte pour passer une commande avant de tomber en rupture. Suivant le nombre moyen de pièces sorties par mois, nous avons deux formules à appliquer, une discrète pour moins de 20 sorties par mois (suivant la loi de Poisson) et une normale pour plus de 20 sorties par mois (utilisant la loi de Gauss), telles que décrites sur ma feuille Excel.

Je souhaiterai créer une fonction pour chacune de ces formules afin d'avoir à éviter de calculer les sommes avec le tableur. Je pensais utiliser VBA et faire une boucle for (pour Gauss) et while (pour Poisson) afin de calculer ces sommes. Etant fainéant, j'aimerai n'avoir que la même fonction à copier d'une cellule à l'autre, la fonction sortant par elle-même le numéro de ligne et faisant les sommes.

N'ayant pas fait de programmation depuis un moment, je suis un peu rouillé sur ce point, j'ai passé la matinée à chercher une solution pour l'une et l'autre des formules, sans succès.

J'espère avoir été suffisamment clair et que mon tableur l'est tout autant.

lien vers la feuille excel : https://1drv.ms/x/s!Ap-ETHuHKDKcjA-rJjwg4vsr9i3l?e=kvv4bX

Je n'ai pas trouvé de sujet correspondant à mon problème sur le forum d'où ce topic. S'il fait doublon, je vous présente mes excuses par avance.

Merci d'avance pour l'aide que vous pourrez m'apporter,

Damien

Salut,

Si j'ai bien compris tu veux créer une formule perso qui s'utilise par exemple en W8 : "=CalculerSA(U8;C8:N8)" et te renvoie la valeur calculer par la bonne méthode (j'ai pas vraiment compris comment tu t'en servais mais j'en ai pas besoin pour t'aider je pense).

Si oui alors il faut créer une fonction personnalisée : https://support.office.com/fr-fr/article/cr%c3%a9er-des-fonctions-personnalis%c3%a9es-dans-excel-2f06c10b-3622-40d6-a1b2-b6748ae8231f?omkt=fr-FR&ui=fr-FR&rs=fr-FR&ad=FR

En gros tu crées un nouveau module dans ton classeur (dans l'éditeur VBA), tu crées ta fonction, puis tu l'utilises comme une fonction normale.

Par exemple avec ce code :

Function CalculerSA(moyenne As Range, sortiesParMois As Range) As Double

    If moyenne.Value <= 20 Then
        CalculerSA = 0
    Else
        CalculerSA = 1
    End If

End Function

Et cette formule en B1 : "=CalculerSA(A1;A2:A4)" j'obtiens 0 si la valeur de A1 est inférieure ou égale à 20 et 1 sinon.

EDIT : et je suis intéressé par ta colonne O, comment tu as fait les petites courbes ?

-
Edité par Stormweaker 30 août 2019 à 13:30:13

Merci beaucoup =),

Je commence par une petite mise à jour, j'ai fait une erreur dans mon premier post. Voici les formules (en vert) utilisées.

J'ai réussi à en faire une (loi de Gauss, avec notamment la fonction ECARTYP).

Je voudrais utiliser ce programme comme le premier, sur la cellule active. J'ai donc mis en argument des cellules qu'on peut choisir.

J'ai réussi à faire un programme simple avec la fonction LOI.GAMMA.N, mais excel me renvoi à chaque fois #VALEUR!. Je voudrais qu'il me renvoi la valeur "r" calculée.

Voici le programme :

Function LOIPOIS(lambda, delai_appro, tx_service)

    r = 0
    t = 1 / delai_appro
    s = (WorksheetFunction.Exp(-1 * lambda * delai_appro))
        
    
    While s < tx_service
    
        r = r + 1
        s = s + WorksheetFunction.Loi.Gamma.n(lambda, r, t) * lambda * t
            
    Wend
    
    ActiveCell.Value = r
    
End Function

Je ne comprends par pourquoi... j'ai essayé avec ByVal devant mes arguments, de mettre mes arguments et variables en single, rien n'y fait. J'ai même essayé d'y mettre un IF...

Pour les petites courbes en colonne O : Onglet "Insertion" -> Case "Graphiques sparkline" -> tu choisis la forme que tu veux, ça ouvre une interface où tu rentres tes valeurs et la case où tu souhaite disposer le graphique. Sachant qu'il est possible de mettre des valeurs dans cette case par dessus le sparkline.

Merci =)

Merci pour les sparklines, je connaissais pas mais ça a l'air cool.

Pour ton code il y a plusieurs problèmes.

Essaie de déclarer les types des variables au maximum, c'est pas obligatoire mais ça rend le code plus facilement lisible je trouve, tu peux ajouter "Option Explicit" en toute première ligne de ton module pour que le compilateur te force à le faire.

Pour récupérer la valeur de retour d'une fonction il faut utiliser son nom. Ici tu écris la valeur directement dans la cellule alors qu'il y a déjà une formule, du coup Excel est pas content.

J'ai eu un problème avec Exp, je l'ai pas dans Worksheetfunction, c'est Exp directement.

Les noms en VBA sont forcément en anglais (et il n'y a pas de points dans le nom d'une méthode) donc pour la loi gamma ça ne peut pas être Worksheetfunction.Loi.Gamma.n, le nom anglais qui correspond a l'air d'être GammaDist : https://docs.microsoft.com/fr-fr/office/vba/api/excel.worksheetfunction.gammadist

En plus de ça il te manque un argument, le booléen pour savoir si la valeur retournée doit être cumulative (voir lien ci-dessus ou la doc de la formule GAMMA.DIST (prends toujours la DOC en anglais car les pages françaises sont traduites par ordi et donc incompréhensibles)).

Avec tous ces commentaires j'ai le code suivant (j'ai commenté la boucle car j'ai pris des valeurs au pif pour GammaDist) :

Function LOIPOIS(lambda, delai_appro, tx_service)

    r = 0
    t = 1 / delai_appro
    s = Exp(-1 * lambda * delai_appro)
         
     
    'While s < tx_service
    
        r = r + 1
        s = s + WorksheetFunction.GammaDist(1, 1, 1, False) * lambda * t
    
    'Wend
     
    LOIPOIS = r
     
End Function

Merci beaucoup,

Effectivement il me manquait le booléen dans la fonction alors que sur mes essais directement sur la feuille je l'avais mis... J'ai toutefois bien pris note de tes autres remarques.

J'ai encore un problème toutefois, la fonction marche mais que lorsque je ne fais pas mon calcul correctement...

Renvoi la valeur #VALEUR! :

[...]
s = s + WorksheetFunction.GammaDist(lambda, r, t, False) * lambda / delai_appro
[...]

Renvoi un nombre comme je le souhaite (je mets juste "* lambda / delai_appro" en commentaire...), mais faux, vu que ce n'est pas le calcul que je veux faire :

[...]
s = s + WorksheetFunction.GammaDist(lambda, r, t, False) '* lambda / delai_appro
[...]

J'ai du mal à comprendre pourquoi il ne veut rien savoir, j'ai essayé avec des parenthèses, de mettre GAMMADIST dans une autre variable sans rien changer... idem avec byval/byref dans ma fonction, c'est vraiment un mystère pour moi car j'ai mis toutes mes variables en "double" même celles qui sont logiquement "integer"... y'a quelque chose qui coince avec WorksheetFunction ?

Code complet :

Function LOIPOIS(ByVal lambda As Double, ByVal delai_appro As Double, ByVal tx_service As Double) As Double
 
    Dim r As Double
    Dim t As Double
    Dim s As Double
    
    r = 0
    t = 1 / delai_appro
    s = Exp(-1 * lambda * delai_appro)
          
    While s < tx_service
    
        r = r + 1
        s = s + WorksheetFunction.GammaDist(lambda, r, t, False) * lambda / delai_appro
     
    Wend
       
    LOIPOIS = r
      
End Function

Vérifie les valeurs des cellules en entrée de la fonction.

J'ai essayé LOIPOIS(5;4;0.5) et ça me renvoie 15.

Bonjour,

en fait je suis trop bête... ma formule n'est pas 

s = s + WorksheetFunction.GammaDist(lambda, r, t, False) * lambda / delai_appro

mais 

s = s + WorksheetFunction.GammaDist(lambda, r, t, False) * lambda / r

parce qu'avec certaines valeurs ça fonctionnait, mais pas avec mes valeurs calculées.... Avec la bonne formule ça marche beaucoup mieux...

Merci infiniment de ton aide en tout cas, ça va bien me servir au travail !