Excel / Intégrales & Table de Gauss

[Magic_Doctor]

Bonjour,

Plutôt que d'installer sur une feuille une table de Gauss, connaissant la formule il me semble infiniment plus judicieux d'utiliser celle-ci. Seulement, voilà, il s'agit d'une intégrale...
Pas grand-chose semble impossible à Excel, mais là j'avoue je coince !
Par quel moyen pourrait-on obtenir la veleur de f(t) ?

Merci de me donner vos avis.

 

[JNP]

Re : Excel / Intégrales & Table de Gauss

Bonjour Magic Doctor 🙂,
As-tu déjà téléchargé l'excellent développement de Laurent Longre qui s'appelle MoreFunc ? Ce lien n'existe plus qui contient les lois de Poisson, Normale, etc. Tu devrais y trouver ton bonheur.
Bonne journée 😎
PS : Tu n'as pas réagi sur https://www.excel-downloads.com/threads/probleme-inexplicable-de-conversion-de-fichier.114922/ ...

 

[Magic_Doctor]

Re : Excel / Intégrales & Table de Gauss

Bonjour Jnp,

Merci por ton conseil, mais malheureusement impossible de télécharger cette banque de fonctions. La page serait-elle caduque ?

 

[JNP]

Re : Excel / Intégrales & Table de Gauss

Re 🙂,
Effectivement, je n'avais pas testé le téléchargement, et ça ne fonctionne plus. Si tu veux, envoye moi ton email en MP, j'ai toujours le zip, je le l'envoie en pièce jointe (il fait 1,5 Mo et je ne peux pas le mettre sur le forum).
A + 😎

 

[job75]

Re : Excel / Intégrales & Table de Gauss

Bonjour Magic Doctor, JNP, le forum,

L'intégrale de -infini à x est donnée par la fonction (voir l'aide Excel) :

LOI.NORMALE(x;moyenne;écart_type;cumulative)

avec moyenne=0, écart_type=1, cumulative=VRAI

Ci-joint le fichier. Pour x=0,01 la fonction renvoie 0,503989356314632

A+

 

[Magic_Doctor]

Re : Excel / Intégrales & Table de Gauss

Merci JNP & Job 75 pour vos réponses.

J'ai réussi à télécharger MoreFunc, mais d'un autre site. Tout bêtement j'ai cherché dans Google MoreFunc et sont apparus plein de sites qui proposent son téléchargement.
Concernant la réponse de Job 75, mon problème n'est pas de comment je peux tracer une courbe de Gauss (soit j'utilise la formule complète et un peu longue, soit la fonction toute prête proposée par Excel et qui conduit exactement au même résultat), mais de connaître la surface de ladite courbe entre 2 points (ou abscisses si tu préfères) ; surface qui me donnera la probabilité d'avoir tant d'éléments de la population étudiée compris dans cet intervalle. Et là il faut utiliser une intégrale dont j'avais fait un scan dans mon 1er message.
Curieusement le calcul intégral n'a pas l'air d'être le cheval de bataille d'Excel... On peut aller vers Mathcad, mais c'est un peu le marteau pour assommer la mouche...!!!
Quoi qu'il en soit, je suis persuadé qu'il y a une ou des possibilités de résoudre des problèmes de calcul intégral sur Excel, mais cela dépasse mes compétences...

 

[job75]

Re : Excel / Intégrales & Table de Gauss

Re,

Magic Doctor, la fonction LOI.NORMALE, avec cumulative=VRAI, calcule l'intégrale, donc la surface, entre -infini et x.

Pour avoir la surface (la probabilité) entre 2 valeurs x1 et x2, calculer la fonction pour chaque valeur x1 et x2, puis faire la différence des 2 résultats obtenus.

A+

 

[Magic_Doctor]

Re : Excel / Intégrales & Table de Gauss

Alors là, Job un très grand merci pour ton aide précieuse, car mes souvenirs de TC sont vraiment très loin et qu'en médecine on est en général très rarement amené à aller plus loin que les 4 opérations et la règle de 3 pour les plus audacieux...

Je me permets de t'adresser la feuille que tu m'avais envoyée avec un exemple d'un effectif que j'avais classé et dont j'avais calculé la moyenne et l'écart-type. J'ai donc pris 2 valeurs de x (abscisses ou effectifs) au hasard et ai calculé, selon tes directives, la probabilité correspondant à l'intervalle du nombre de ces effectifs.
Si tu pouvais seulement y jeter un coup d'oeil et me dire OK ou pas, je t'en serais très reconnaissant.

À+

 

[job75]

Re : Excel / Intégrales & Table de Gauss

Re,

C'est bien ainsi qu'il faut procéder, mais je ne vois pas comment tu obtiens cet écart-type.

Je l'ai donc recalculé (en gras), et le résultat est nettement différent.

A+

 

[Magic_Doctor]

Re : Excel / Intégrales & Table de Gauss

Bonsoir Job 75,

L'écart-type a été calculé en fonction d'un effectif classé. Ici, l'intervalle des classes (ou si tu préfères l'amplitude) varie, suivant les classes, entre 80 et 20. La moyenne, la médiane, le mode, la variance et l'écart-type qui découle de cette dernière ont été calculés selon les "vieux" procédés. Les résultats sont justes car toute la démarche provient d'un chapitre de statistiques d'un vieux dictionnaire de mathématiques (1970). Je n'ai fait que transcrire leur métodologie dans Excel, seul moyen pour comprendre ces histoires de courbes normales, écarts-types, etc. que la plupart des quidams et de mes confrères "interprètent" sans rien y comprendre...
Pas plus tard qu'aujourd'hui JNP m'a aiguillé sur une banque de fonctions complémentaires pour Excel : MoreFunc (rechercher ce nom sur Google pour télécharger). Dans la panoplie de fonctions proposées il en est une qui s'appelle ECARTYPEP.GROUPE et qui permet de calculer l'écart-type d'une population groupée en classes. Fantastique : la fonction renvoie exactement le même résultat que celui trouvé dans ma feuille et du dico de math à partir du même exemple.
En conséquence le calcul de l'écart-type par la fonction incorporée dans Excel ne s'applique que pour des effectifs non classés. D'où l'intérêt de télécharger ces fonctions complémentaires.

 

[Magic_Doctor]

Re : Excel / Intégrales & Table de Gauss

À l'attention de Job 75,

J'en profite pour t'adresser une copie sommaire de la feuille où ont été faits les calculs.

La série statistique étudiée concerne la "répartition de 10.000 arbres fruitiers d'après le nombre de fruits produits en une saison". La production variant de 30 à 350 inclus.

Normalement la probabilité de trouver des effectifs dans l'intervalle [30 , 350] devrait être de 100%. Mais puisque la production n'est pas normale à 100 % (Médiane : 188,82 / Moyenne : 189,60 / Mode : 180 ---> petit étalement de la courbe à droite), je pense que c'est la raison pour laquelle quand, dans la solution que tu m'as donnée, je mets X1 = 30 et x2 = 349,88 (en fonction de l'incrément de x pour tracer la courbe je n'arrive pas exactement à 350 ; manuellement avec le Solveur j'y arrive, mais pas automatiquement par le biais d'une macro. Curieusement les fonctions ayant trait au Solver ne sont pas reconnues et personne n'a pu jusqu'à présent m'en donner l'explication...) j'obtiens 99,54%. Mais même si je choisis x1 = 30 et x2 = 350 je n'obtiens pas 100% mais 99,55%.

Bonne fin de soirée.

 

[job75]

Re : Excel / Intégrales & Table de Gauss

Re,

Merci pour les explications. Pour obtenir 100% sur la courbe de Gauss, il faut aller jusqu'à l'infini...

Je reviens sur le calcul intégral :

Curieusement le calcul intégral n'a pas l'air d'être le cheval de bataille d'Excel...

Excel est très bien adapté avec VBA au calcul intégral grâce aux boucles qui permettent de sommer f(x)dx - c'est la définition pure et simple d'une intégrale définie.

Exemple pour f(x)=x^3 sur l'intervalle [0,1] :

Sub Integrale()
Dim a#, b#, dx#, x#, S#
a = 0
b = 1
dx = (b - a) / 1000000
x = a + dx / 2
S = 0
While x < b
S = S + x ^ 3 * dx [COLOR="Red"]'f(x)=x^3[/COLOR]
x = x + dx
Wend
MsgBox S
End Sub

La primitive est x^4/4 qui donne pour résultat 0,25.

A+

 

[Magic_Doctor]

Re : Excel / Intégrales & Table de Gauss

Merci encore Job pour tes explications.
Je vais étudier de plus près ta macro et tenter de me remémorer les notions de dérivées & primitives bien lointaines dans mon cortex...

Pour suivre le fil sur les intégrales, j'en profite pour joindre cette macro que j'avais glanée quelque part (Codes-Sources si mes souvenirs sont bons), mais que je n'ai pas encore testée :

Sub Intégrale_Définie_Simpson()
'APPROXIMATION d'une intégrale définie:
'Formater A1 en texte et écrire une fonction en x,
'sans signe d'égalité.
'Écrire en A2 la borne inférieure
'Écrire en A3 la borne supérieure
'Écrire en A4 un nombre PAIR de sous-intervalles.
'Si la fonction est positive partout sur l'intervalle
'la réponse est une approximation de la superfice
'sous la courbe et au-dessus de OX.
'Si la fonction est négative partout sur l'intervalle
'la valeur absolue de la réponse est une approximation
'de la superfice sous la courbe et au-dessous de OX.
'Si votre fonction est un polynôme de degré inférieur
'à quatre, la réponse obtenue est EXACTE et dans ce cas
'DEUX sous-intervalles suffisent !
Dim x() As Double
Dim y() As Double
Dim somme As Double
px = [A2]: gx = [A3]
n = [A4]
If n / 2 - Int(n / 2) <> 0 Then
MsgBox "Simpson réclame un nombre pair de sous-intervalles.", _
vbExclamation, "Soyez raisonnable !"
Exit Sub
End If
ReDim x(1 To n + 1)
ReDim y(1 To n + 1)
pas = (gx - px) / n
f = [A1]
If Left(f, 1) = "-" Then f = Replace(f, "-", "(-1)*", 1, 1)
f1 = Replace(f, "x", px)
f2 = Replace(f1, ",", ".")
y(1) = Evaluate(f2)
For i = 1 To n
x(i + 1) = px + i * pas
f1 = Replace(f, "x", x(i + 1))
f2 = Replace(f1, ",", ".")
y(i + 1) = Evaluate(f2)
Next i
For i = 1 To n + 1
If i / 2 - Int(i / 2) = 0 Then
sp = sp + y(i)
Else
si = si + y(i)
End If
Next i
py = y(1): gy = y(n + 1)
somme = pas * (py + gy + 4 * sp + 2 * (si - py - gy)) / 3
MsgBox "Simpson, qui a utilisé " & n & " sous-intervalles." _
& Chr(10) & "Intégrale définie : " & somme, vbInformation, _
"Approximation de Monsieur Simpson"
End Sub

Sait-on jamais...

À+