Courbe de tendance sigmoïde sur Excel

Bonjour! J'ai fait un labo sur le potentiel de soluté et j'ai besoin de faire un graphique (avec une courbe de tendance sigmoïde) avec mes données en x et en y. Le problème, c'est que la fonction sigmoïde n'est pas par défaut dans Excel et j'en ai absolument besoin. Je demande donc votre aide : est-il possible d'ajouter facilement une courbe sigmoïde dans Excel, ou alors existe-t-il un logiciel ou un site Internet qui pourrait régler mon problème ? Merci!

Tout dépend : est ce que tu en a besoin "pour relier tes points pour que ça fasse joli" ou réellement parce que tu a besoin de l'expression mathématique ?

Je ne suis pas expert en Excel mais tu peux utiliser le solveur pour faire ce que tu veux (modulo que ce soit motivé pour de bonnes raisons comme le souligne pierre_24 [o/]).

Voilà mon tableau sous Excel avec $X$ et $Y$ les données mesurées avec $N$ observations, $Y_{\text{pred}}$ ton modèle et SCE la Somme au Carré des Écarts entre les données observées et les prédictions de ton modèle $\text{SCE} = \sum \limits_{i = 1}^{N} (Y_i - Y_{\text{pred},i})^2$. J'ai utilisé $Y_{\text{pred}} = \frac{a}{b+c \exp (-d (X - f))}$ - mais là c'est à toi de savoir quel modèle fait sens, toutes les constantes ne sont pas nécessaires.

Donne ensuite des valeurs initiales pour les paramètres $a, b, c, d, e$ et $f$.

Pour trouver les valeurs $a, b, c, d, e$ et $f$ qui minimisent ta fonction d'erreur SCE, ouvre ensuite le solveur d'Excel (ou Libre office) (Données > Solveur pour Excel) avec les paramètres suivants :

• objectif à définir : $E$22 (c'est-à-dire la cellule qui contient la somme des écarts entre les valeurs $Y_i$ observées et prédites par le modèle)
• À : min (on veut minimiser la somme au carré des écarts)
• Cellules variables : $E$1:$E$5 (les cellules qui contiennent les valeurs initiales de tes paramètres)

Et choisit un algorithme d'optimisation non linéaire (j'ai utilisé GRG non linéaire) puis clique sur Résoudre. Note qu'il est possible que l'algorithme soit coincé dans des minimums locaux et que les paramètres obtenus dépendent fortement des valeurs initiales que tu mettras pour $a, b, c, d, e, f$.

Je ne dois pas relier les points, je dois faire un courbe statistique et je sais qu'elle est sigmoïde. Merci adndebanane, mais comment trouve-t-on les constantes a à f? Et qu'est-ce que veut dire SCE? Je n'ai pas un gros bagage mathématique, donc un peu de vulgarisation serait la bienvenue.

J'ai édité mon message en essayant de le rendre un peu plus explicite - est-ce plus clair maintenant ? Comment connais-tu a priori la forme que doit prendre ta "courbe statistique" ? C'est un résultat du cours que tu vérifies ?

Non, je ne comprends toujours pas comment je peux deviner les constantes. C'est moi qui invente les constantes initiales ? Pourquoi dis-tu que je pourrais ne pas en avoir toutes besoin? Pour le reste ça devrait aller, je n'ai qu'à appliquer les formules pour tous les cas de Ypred et de SCE. Je suis sûr à 100 % qu'il s'agit d'une courbe sigmoïde car mes résultats forment un S « plat » et pour que ça colle avec la théorie, ma courbe doit être ainsi. Merci encore pour ton aide.

Non, je ne comprends toujours pas comment je peux deviner les constantes. C'est moi qui invente les constantes initiales ?

Oui, tu mets des constantes soit complètement au pif, soit si tu as du courage des valeurs qui donnent une forme assez proche de ton nuage de point. L'idée, c'est de laisser le soin à l'inverseur d'Excel de trouver les valeurs pour ces constantes qui vont coller le mieux à ton nuage.

Mais en fait, étant donné que tu n'as absolument pas l'air familier avec ce genre de manière de faire, j'ai envie de dire que c'est parce que tu n'en as pas besoin. Tu veux faire quoi exactement ? Quel est le but de l'opération ? Tu veux une sigmoïde qui colle sur ton nuage, d'accord, mais pour en faire quoi ?

Le but de l'opération est simplement de montrer que mes résultats collent avec la théorie, comme je l'ai dit. La courbe sigmoïde est dans les exigences de mon rapport. Je pourrais aussi la dessiner à la main, mais je suis nul en dessin et si Excel permet de le faire, je ne vois pas pourquoi je ne l'utiliserais pas, car ça paraît beaucoup plus propre. Je crois avoir compris le principe, je vous en redonne bientôt des nouvelles !

Le but de l'opération est simplement de montrer que mes résultats collent avec la théorie, comme je l'ai dit.

Et par hasard, dans cette "théorie", tu n'aurais pas une expression pour les sigmoïdes ?

Il y a la formule, mais je ne vois pas où est le rapport.

J'ai fait ce qu'a dit adndebanane, mais où trouve-t-on l'algorithme d'optimisation non linéaire dans le solveur? Et le plus important : comment utilise-t-on ce qu'on obtient pour tracer une courbe (sur le graphique)?

Il y a la formule, mais je ne vois pas où est le rapport.

Le rapport, c'est que ce serait peut être utile de nous donner la formule en question au lieu de te faire utiliser une autre potentiellement plus compliquée que nécessaire.

Pour le reste, je n'utilise pas Excel, je ne pourrais pas aider sur ce point.

Bah en fait c'est la même formule… mon problème, c'était de la rentrer dans Excel.

Quelques points pour peut-être expliciter ce que dit adri1.

Tout d'abord, à propos de la méthode du solveur d'Excel, c'est vrai que je ne l'ai pas trop expliquée, mais je ne suis pas sûr d'y arriver. En gros imagine que notre fonction d'erreur SCE varie avec les paramètres de la fonction que tu essayes d'ajuster à tes données (cela forme un paysage avec des montagnes quand l'erreur est grande et des ravins quand l'erreur est petite). C'est à dire que quand on change les paramètres $a, b, c, d, e$ et $f$, l'erreur augmente ou diminue. Tu peux donc assez intuitivement t'imaginer qu'il est très compliqué de trouver le point $(\hat{a}, \hat{b}, \hat{c}, \hat{d}, \hat{e}, \hat{f})$ qui minimise la SCE (qui trouve le ravin le plus profond) par essai-erreur. Il y a plusieurs façons de procéder, comme résoudre analytiquement ce problème (c'est-à-dire trouver la formule pour obtenir le point $(\hat{a}, \hat{b}, \hat{c}, \hat{d}, \hat{e}, \hat{f})$ qui minimise la fonction SCE$(a,b,c,d,e,f)$) ce qui est en général trop compliqué (à l'exception du modèle linéaire, tu as peut-être déjà entendu parler de la régression linéaire). Du coup, on va utiliser une méthode numérique grâce à un algorithme d'optimisation qui va tester un grand nombre de points $(a,b,c,d,e,f)$, estimer la fonction d'erreur associée SCE$(a,b,c,d,e,f)$ et essayer de comprendre dans quelle direction il doit aller (augmenter un peu $a$ et diminuer un peu $b$ diminue la SCE par exemple) pour minimiser la SCE. Ce faisant, il te donner un point $(\hat{a}, \hat{b}, \hat{c}, \hat{d}, \hat{e}, \hat{f})$ qu'il estime minimiser la fonction SCE - mais il est possible que l'algorithme se trompe et soit coincé dans un minimum local. Les valeurs initiales que tu donnes à tes paramètres indique où l'algorithme doit commencer son exploration du paysage - ce qui influence potentiellement son voyage.

Autre point quant à la démarche scientifique qu'adri1 essaye subtilement de te faire comprendre. La démarche usuelle et courante pour comprendre un phénomène naturel est de formuler des hypothèses à partir de ce que l'on sait, d'essayer de prédire le comportement dudit phénomène en accord avec ces hypothèses (en formalisant l'énoncé de l'hypothèse avec un modèle par exemple) et de vérifier ce comportement expérimentalement (on confronte idées et observations pour affiner nos questions, nos idées et nos expériences futures).

Tu peux tout à fait t'inspirer de cette démarche ici. Tu as une théorie (ton cours) et tu essayes de la valider (ou de l'infirmer) par l'expérience (tes données obtenues en TP). Du coup, plutôt que de reprendre la fonction sigmoïde très générale que j'ai indiquée dans mon premier message, il serait de bon ton d'utiliser la formule de ton cours, d'ajuster ses paramètres à tes données et ensuite essayer de conclure quant à l'ajustement de cette formule théorique à tes données - dit autrement, est-ce que ta formule théorique indiquée dans ton cours rend bien compte du comportement observé expérimentalement ? Sinon, d'où cela peut-il venir ? Erreurs de mesures, de manipulation ou remise en cause de la théorie, reformulation de l'hypothèse, nouveau modèle, etc. : Science, kid.

Je n'utilise pas non plus Excel, je serais bien en peine pour faire un graphique avec - tu as juste eu de la chance que je doive enseigner cette semaine comment utiliser le solveur que je n'avais jamais utilisé auparavant ^^' Je ne peux donc pas t'aider à tracer la courbe - sauf si tu décris mieux ce qui te bloque. Have fun.

Merci de ta réponse. En fait je comprends très bien la méthode scientifique, mais je n'ai ni le temps ni l'intérêt d'essayer de comprendre les détails mathématiques de la fonction sigmoïde (et ce n'était pas le but de l'exercice). La formule que j'ai est la même que celle que tu m'as montré : après tout, une fonction sigmoïde, ça reste une fonction sigmoïde. Cette formule ne m'est donc d'aucun intérêt ici. J'ai simplement besoin de montrer, dans mon exercice, que les points font un sigmoïde, et rien de plus. Je comprends ce que vous essayez de me dire, mais mon problème est plutôt d'origine technique, pas théorique. Je vais essayer de bidouiller pour voir si je ne peux pas tracer une courbe (c'était simplement ça, mon problème). Merci quand même de votre aide.

Je ne comprends pas trop ton soucis du coup - je t'ai quand même donné la méthode pour ajuster la fonction sigmoïde à tes points : il te suffit de réutiliser les paramètres estimés par le solveur $(\hat{a}, \hat{b}, \hat{c}, \hat{d}, \hat{e}, \hat{f})$, générer 10000 points en utilisant $Y_{\text{pred}} = \frac{\hat{a}}{\hat{b}+\hat{c} \exp (-\hat{d} (X - \hat{f}))}$ et les ajouter à ta figure pour que ça soit propre. Ton soucis c'est juste pour construire la figure ou bien c'est pour ajuster la sigmoïde - je suis un peu perdu

Edit :

C'est exactement notre point

Non, je veux dire que MA formule était inutile. Désolé, je me suis peut-être mal exprimé plus tôt. Mon soucis était au départ de bien construire la figure, mais j'ai trouvé comment faire : il suffit de relier les points entre eux. Il ne me reste donc plus qu'un dernier problème à régler : comment générer 10 000 points si je n'ai que 10 valeurs en y?

Il ne me reste donc plus qu'un dernier problème à régler : comment générer 10 000 points si je n'ai que 10 valeurs en y?

Tu ne peux pas. Ça n'aurait aucun sens, même pour faire joli : les données sont là, point. S'il t'en manque, tant pis tu ne vas pas construire des données aux pifs car tu ne sais pas ce qui se passe. Même si le graphique est plus moche, il faut faire avec sinon c'est de la fraude intellectuelle.

Tu ne peux générer des points que si tu utilises l'équation tant décriée en balayant les différentes valeurs possibles prises par X - mais ce n'est pas ce que tu veux, semble-t-il.

Je n'avais pas l'intention d'inventer des résultats. Tant pis, je vais garder ma courbe. Je vais m'en tenir à ça. Merci encore.

Je n'avais pas l'intention d'inventer des résultats.

Quitte à enfoncer des portes ouvertes au risque d'être lourdingue, mais pour être sûr que le point que je soulevais soit bien passé : même si tu voulais ne générer que des points qui passent par ta courbe lissée, tu n'aurais pas pu. Cela signifierait que tu soutiens ta courbe par des données qui n'existent pas ; même si l'allure de la courbe n'est pas modifiée et conserve ses éventuelles irrégularités, elle apparaîtrait plus significative qu'elle ne l'est en réalité et c'est un problème : le message que tu fais passer par ta courbe (« regardez comment qu'elle est belle ma sigmoïde ») est trop fort par rapport à ce que tu as effectivement comme données.

La démarche usuelle et courante pour comprendre un phénomène naturel est de formuler des hypothèses à partir de ce que l'on sait, d'essayer de prédire le comportement dudit phénomène en accord avec ces hypothèses (en formalisant l'énoncé de l'hypothèse avec un modèle par exemple)

Attention, la formalisation des hypothèses, c'est la théorie (la formule de la sigmoïde par exemple). Le modèle, c'est la valeur des paramètres (un point dans le paysage de vallées et cols que tu décrivais plus haut).

Tu peux tout à fait t'inspirer de cette démarche ici. Tu as une théorie (ton cours) et tu essayes de la valider (ou de l'infirmer) par l'expérience (tes données obtenues en TP).

Là aussi, attention. En général, les données vont juste permettre d'infirmer ou confirmer un modèle précédent. Pour remettre en cause la théorie, c'est une autre paire de manche et ça se fait rarement avec des données seules. Il faut aussi venir avec une autre théorie qui a à la fois des justifications fondamentales et un pouvoir explicatif meilleur (ie la capacité à trouver des modèles plus robustes et permettant des prédictions plus proches des données).

@Goeland-croquant : j'ai l'impression que par "ajouter des points", Le Gigot voulait juste dire obtenir une courbe lisse. Ce qu'il n'a apparemment pas réussir à faire, j'ai l'impression qu'il a juste une ligne brisée qui relie ses points alors qu'il voulait faire passer une sigmoïde au milieu à la base. Et c'est là que l'inversion proposée par adndebanane aurait été utile.

Oui, je voulais dire obtenir une courbe lisse à partir de mes 10 points. Comment fonctionne l'inversion proposée par adndebanane ? (C'est juste par curiosité, parce que comme je l'ai mentionné, on peut aussi simplement dessiner la courbe à la main pour ce travail. C'est moi qui me suis compliqué la tâche pour rien.)