Voila je cherche a trouver une fonction Excel qui me permette de recuperer
les coefficients de la fonction d'interpolation d'un graphe.
Je m'explique : j'ai 3 colonnes et 1 graphe. Le graphe est ColB vs ColA, ce
qui me donne une courbe. Je cherche ensuite graphiquement la fonction
d'interpolation qui sied le mieux a cette courbe et affiche sur le graphe
l'equation correspondante. Or apres j'aimerais pouvoir recuperer les
coefficients pour pouvoir calculer les valeurs de ma ColC . Et j'aimerais que
cela se fasse automatiquement bien sur lorsque je fais varier les valeurs des
colonnes A et B.
C'est facile de le faire lorsque la fonction d'interpolation est
exponentielle, logarithmique ou lineaire (fonctions TREND et LOGEST en
anglais) mais je n'arrive pas a trouver de fonction Excel lorsque la courbe
d'interpolation est de type polynomiale (degre 3 dans mon cas).
Alors effectivement a priori cela marche chez vous. Seulement chez moi Excel n'accepte pas les {} pour le 1/2. A priori cela doit venir des differences entre anglais et francais (par exemple ; changes en ,). Du coup je n'arrive pas a trouver le bon signe ( les crochets ne marchent pas et les parentheses ne donnent pas le bon resultat).
En tous cas si j'ai bien compris si je remplace le 1/2 par 1/3 je devrais pouvoir trouver mon polynome d'ordre 3 ??
Sylvain qui a bien du mal aujourd'hui ;-)
"D.Schneider" wrote:
Petite erreur d'adressage de cellules: Les points X en (B4:B11) et Y en (C4:C11) voilà....
"D.Schneider" a écrit dans le message de news:
Re-bonjour,
Exemple pas à pas : Placer les points: X en (B3:B11) 3.107 2.806 2.505 2.204 1.903 1.602 1.301 1.000
Y en (C3:C11) 1.330 1.328 1.179 1.232 1.083 1.056 0.879 0.891
***Pour activer une matrice: Taper la formule dans la cellule E3 : "=DROITEREG(C4:C11;B4:B11^{12};VRAI;VRAI)" ou LINEST en anglais Selectionner les cellules E3:G10 (les activer) Placer le pointeur de la souris sur la formule dans la barre de formule Taper Ctrl + Maj + Enter .et la formule prend automatiquement la forme: "={DROITEREG(C4:C11;B4:B11^{12};VRAI;VRAI)}"
...et comme résultat, la matrice en E3:G10 : (a ) (b) (c) -0.03452721 0.37277912 0.51871515 0.04654812 0.19322602 0.18444023 0.93194872 0.05467344 #N/A 34.23700335 #N/A 0.20468144 0.01494592 #N/A #N/A #N/A #N/A #N/A #N/A #N/A #N/A #N/A #N/A
Je viens de le faire sur mon tableur, ça marche. @+ - Denis
Alors effectivement a priori cela marche chez vous. Seulement chez moi Excel
n'accepte pas les {} pour le 1/2. A priori cela doit venir des differences
entre anglais et francais (par exemple ; changes en ,). Du coup je n'arrive
pas a trouver le bon signe ( les crochets ne marchent pas et les parentheses
ne donnent pas le bon resultat).
En tous cas si j'ai bien compris si je remplace le 1/2 par 1/3 je devrais
pouvoir trouver mon polynome d'ordre 3 ??
Sylvain qui a bien du mal aujourd'hui ;-)
"D.Schneider" wrote:
Petite erreur d'adressage de cellules:
Les points X en (B4:B11)
et Y en (C4:C11)
voilà....
"D.Schneider" <dPeAnSis.sDcEhSnPeAiMder3@free.fr> a écrit dans le message de
news:eaFUDS5PFHA.3928@TK2MSFTNGP09.phx.gbl...
Re-bonjour,
Exemple pas à pas :
Placer les points:
X en (B3:B11)
3.107
2.806
2.505
2.204
1.903
1.602
1.301
1.000
Y en (C3:C11)
1.330
1.328
1.179
1.232
1.083
1.056
0.879
0.891
***Pour activer une matrice:
Taper la formule dans la cellule E3 :
"=DROITEREG(C4:C11;B4:B11^{12};VRAI;VRAI)" ou LINEST en anglais
Selectionner les cellules E3:G10 (les activer)
Placer le pointeur de la souris sur la formule dans la barre de formule
Taper Ctrl + Maj + Enter
.et la formule prend automatiquement la forme:
"={DROITEREG(C4:C11;B4:B11^{12};VRAI;VRAI)}"
...et comme résultat, la matrice en E3:G10 :
(a ) (b) (c)
-0.03452721 0.37277912 0.51871515
0.04654812 0.19322602 0.18444023
0.93194872 0.05467344 #N/A
34.23700335 #N/A
0.20468144 0.01494592 #N/A
#N/A #N/A #N/A
#N/A #N/A #N/A
#N/A #N/A #N/A
Je viens de le faire sur mon tableur, ça marche.
@+ - Denis
Alors effectivement a priori cela marche chez vous. Seulement chez moi Excel n'accepte pas les {} pour le 1/2. A priori cela doit venir des differences entre anglais et francais (par exemple ; changes en ,). Du coup je n'arrive pas a trouver le bon signe ( les crochets ne marchent pas et les parentheses ne donnent pas le bon resultat).
En tous cas si j'ai bien compris si je remplace le 1/2 par 1/3 je devrais pouvoir trouver mon polynome d'ordre 3 ??
Sylvain qui a bien du mal aujourd'hui ;-)
"D.Schneider" wrote:
Petite erreur d'adressage de cellules: Les points X en (B4:B11) et Y en (C4:C11) voilà....
"D.Schneider" a écrit dans le message de news:
Re-bonjour,
Exemple pas à pas : Placer les points: X en (B3:B11) 3.107 2.806 2.505 2.204 1.903 1.602 1.301 1.000
Y en (C3:C11) 1.330 1.328 1.179 1.232 1.083 1.056 0.879 0.891
***Pour activer une matrice: Taper la formule dans la cellule E3 : "=DROITEREG(C4:C11;B4:B11^{12};VRAI;VRAI)" ou LINEST en anglais Selectionner les cellules E3:G10 (les activer) Placer le pointeur de la souris sur la formule dans la barre de formule Taper Ctrl + Maj + Enter .et la formule prend automatiquement la forme: "={DROITEREG(C4:C11;B4:B11^{12};VRAI;VRAI)}"
...et comme résultat, la matrice en E3:G10 : (a ) (b) (c) -0.03452721 0.37277912 0.51871515 0.04654812 0.19322602 0.18444023 0.93194872 0.05467344 #N/A 34.23700335 #N/A 0.20468144 0.01494592 #N/A #N/A #N/A #N/A #N/A #N/A #N/A #N/A #N/A #N/A
Je viens de le faire sur mon tableur, ça marche. @+ - Denis
Michel41
Bonsoir En fait l'astuce consiste à considérer qu'il existe 3 variables explicatives indépendantes (mon oeil !) -la première est X (celle de vos données) -la seconde est X^2 (calculée dont pseudo indépendante ; X puissance 2) -la troisième est X^3 (calculée aussi ; X puisssance 3) et de faire une régression à 3 inconnues On juge de la qualité de la régression avec le calcul du déterminant (R²) : tout dépend du contexte mais pour que le polynôme soit utilisable il faut qu'il soit supérieur à 0,9 et même 0,99 s'il doit remplacer complètement les données : 0,99 signifie qu'il "explique" à 99% les variations de Y.
Conclusion Régression à 3 inconnues (fonction tableur existante) Déterminant pour juger de la qualité du résultat (fonction tableur existante) Affichage sur le graphe de la fonction par l'autre méthode et de R² (fonctions graphiques ; c'est aussi un moyen de vérifier que l'on n'a pas fait d'erreur avec la fonction de régression matricielle) Point délicat : la fonction de régression ne se valide pas avec un simple "Entrée" et il faut sélectionner la zone exacte d'affichage sur le tableur sinon !!!!!!! Bien étudier l'aide Excel pour la mise en oeuvre : elle est suffisante
@+ M41
"Sylvain" a écrit dans le message de news:
Oui effectivement en fait j'avais mal compris la formule de D.Schneider.
Donc au final j'ai essaye en mettant juste 1/3 plutot que 1/2 et cela ne marche toujours pas. Et donc oui ca plante a mon avis si une des valeurs est negative avec le '1/2'.
Pour finir j'ai essaye d'utiliser la seconde methode de Michel41 et j'avoue ne pas avoir saisie comment entre les 3 differentes valeurs pour les x1 x2 x3 dans la formule. Un probleme de syntaxe sans aucun doute !
"Michel NOLF" wrote:
Ce que j'en disais c'était qu'a priori si $c$36 = -4, quelle la valeur de racine carrée de -4???? ;) amicalement Michel
"Sylvain" a écrit dans le message de news: CECE7B8D-026B-4196-A46C-43B
Bonjour,
Alors en ce qui concerne la suggestion de D. Schneider, j'avoue avoir du mal a l'implementer. En effet outre les problemes de syntaxes (differences versions anglaise-francaise) j'ai fait un essai tres simple avec:
colonne A: valeurs de 1 a 10 colonne B: valeurs de 1^3 a 10^3
ensuite je cherche a obtenir le premier coefficient (qui doit donc etre 1) avec la formule suivante :
=INDEX(LINEST(B1:B10,A1:A10^(1/2/3),,TRUE),1,1)
Le resultat n'est pas tres concluant : 1864.11 !! A noter que l'utilisation {} est tres mal percu par Excel de meme que le . Donc un exemple me ferait effectivement bien plaisir :)
Passons ensuite a Trireme. Il est evident qu'il y a toujours la methode lourde de l'analyse numerique en repartant du debut. Mais ma question etait justement destine a eviter ce genre de calcul qui me ferait perdre pas mal de temps vu que je ne suis pas tres expert avec Excel et que cela fait quelques annees maintenant que je n'ai plus relu mes cours d'AN. Bref cette solution sera vraiment le dernier recours je crois.
Enfin Michel. Votre premiere methode faisant appel a VBA me parait assez fastidieuse et de plus mon cahier des charges stipule une "utilisation minimale de VBA" ! Bref je vais essayer de voir comment appliquer votre 2e methode qui m'a l'air en seconde lecture plutot interessante !
Je vous re-tiens au courant dans la journee !
PS @Michel Nolf: d'un point de vue strictement mathematique il n'y a aucun probleme a ce qu'une valeur en x soit negative a priori !
Sylvain
Bonsoir
En fait l'astuce consiste à considérer qu'il existe 3 variables explicatives
indépendantes (mon oeil !)
-la première est X (celle de vos données)
-la seconde est X^2 (calculée dont pseudo indépendante ; X puissance 2)
-la troisième est X^3 (calculée aussi ; X puisssance 3)
et de faire une régression à 3 inconnues
On juge de la qualité de la régression avec le calcul du déterminant (R²) :
tout dépend du contexte mais pour que le polynôme soit utilisable il faut
qu'il soit supérieur à 0,9 et même 0,99 s'il doit remplacer complètement les
données : 0,99 signifie qu'il "explique" à 99% les variations de Y.
Conclusion
Régression à 3 inconnues (fonction tableur existante)
Déterminant pour juger de la qualité du résultat (fonction tableur
existante)
Affichage sur le graphe de la fonction par l'autre méthode et de R²
(fonctions graphiques ; c'est aussi un moyen de vérifier que l'on n'a pas
fait d'erreur avec la fonction de régression matricielle)
Point délicat : la fonction de régression ne se valide pas avec un simple
"Entrée" et il faut sélectionner la zone exacte d'affichage sur le tableur
sinon !!!!!!!
Bien étudier l'aide Excel pour la mise en oeuvre : elle est suffisante
@+
M41
"Sylvain" <Sylvain@discussions.microsoft.com> a écrit dans le message de
news: 3A9A9D09-DB98-460C-8A1F-B3416D3817F3@microsoft.com...
Oui effectivement en fait j'avais mal compris la formule de D.Schneider.
Donc au final j'ai essaye en mettant juste 1/3 plutot que 1/2 et cela ne
marche toujours pas. Et donc oui ca plante a mon avis si une des valeurs
est
negative avec le '1/2'.
Pour finir j'ai essaye d'utiliser la seconde methode de Michel41 et
j'avoue
ne pas avoir saisie comment entre les 3 differentes valeurs pour les x1 x2
x3
dans la formule. Un probleme de syntaxe sans aucun doute !
"Michel NOLF" wrote:
Ce que j'en disais c'était qu'a priori si $c$36 = -4, quelle la valeur de
racine carrée de -4????
;)
amicalement Michel
"Sylvain" <Sylvain@discussions.microsoft.com> a écrit dans le message de
news: CECE7B8D-026B-4196-A46C-43B variables63B137DAB@microsoft.com...
Bonjour,
Alors en ce qui concerne la suggestion de D. Schneider, j'avoue avoir
du
mal
a l'implementer. En effet outre les problemes de syntaxes (differences
versions anglaise-francaise) j'ai fait un essai tres simple avec:
colonne A: valeurs de 1 a 10
colonne B: valeurs de 1^3 a 10^3
ensuite je cherche a obtenir le premier coefficient (qui doit donc etre
1)
avec la formule suivante :
=INDEX(LINEST(B1:B10,A1:A10^(1/2/3),,TRUE),1,1)
Le resultat n'est pas tres concluant : 1864.11 !! A noter que
l'utilisation
{} est tres mal percu par Excel de meme que le . Donc un exemple me
ferait
effectivement bien plaisir :)
Passons ensuite a Trireme. Il est evident qu'il y a toujours la methode
lourde de l'analyse numerique en repartant du debut. Mais ma question
etait
justement destine a eviter ce genre de calcul qui me ferait perdre pas
mal
de
temps vu que je ne suis pas tres expert avec Excel et que cela fait
quelques
annees maintenant que je n'ai plus relu mes cours d'AN. Bref cette
solution
sera vraiment le dernier recours je crois.
Enfin Michel. Votre premiere methode faisant appel a VBA me parait
assez
fastidieuse et de plus mon cahier des charges stipule une "utilisation
minimale de VBA" ! Bref je vais essayer de voir comment appliquer votre
2e
methode qui m'a l'air en seconde lecture plutot interessante !
Je vous re-tiens au courant dans la journee !
PS @Michel Nolf: d'un point de vue strictement mathematique il n'y a
aucun
probleme a ce qu'une valeur en x soit negative a priori !
Bonsoir En fait l'astuce consiste à considérer qu'il existe 3 variables explicatives indépendantes (mon oeil !) -la première est X (celle de vos données) -la seconde est X^2 (calculée dont pseudo indépendante ; X puissance 2) -la troisième est X^3 (calculée aussi ; X puisssance 3) et de faire une régression à 3 inconnues On juge de la qualité de la régression avec le calcul du déterminant (R²) : tout dépend du contexte mais pour que le polynôme soit utilisable il faut qu'il soit supérieur à 0,9 et même 0,99 s'il doit remplacer complètement les données : 0,99 signifie qu'il "explique" à 99% les variations de Y.
Conclusion Régression à 3 inconnues (fonction tableur existante) Déterminant pour juger de la qualité du résultat (fonction tableur existante) Affichage sur le graphe de la fonction par l'autre méthode et de R² (fonctions graphiques ; c'est aussi un moyen de vérifier que l'on n'a pas fait d'erreur avec la fonction de régression matricielle) Point délicat : la fonction de régression ne se valide pas avec un simple "Entrée" et il faut sélectionner la zone exacte d'affichage sur le tableur sinon !!!!!!! Bien étudier l'aide Excel pour la mise en oeuvre : elle est suffisante
@+ M41
"Sylvain" a écrit dans le message de news:
Oui effectivement en fait j'avais mal compris la formule de D.Schneider.
Donc au final j'ai essaye en mettant juste 1/3 plutot que 1/2 et cela ne marche toujours pas. Et donc oui ca plante a mon avis si une des valeurs est negative avec le '1/2'.
Pour finir j'ai essaye d'utiliser la seconde methode de Michel41 et j'avoue ne pas avoir saisie comment entre les 3 differentes valeurs pour les x1 x2 x3 dans la formule. Un probleme de syntaxe sans aucun doute !
"Michel NOLF" wrote:
Ce que j'en disais c'était qu'a priori si $c$36 = -4, quelle la valeur de racine carrée de -4???? ;) amicalement Michel
"Sylvain" a écrit dans le message de news: CECE7B8D-026B-4196-A46C-43B
Bonjour,
Alors en ce qui concerne la suggestion de D. Schneider, j'avoue avoir du mal a l'implementer. En effet outre les problemes de syntaxes (differences versions anglaise-francaise) j'ai fait un essai tres simple avec:
colonne A: valeurs de 1 a 10 colonne B: valeurs de 1^3 a 10^3
ensuite je cherche a obtenir le premier coefficient (qui doit donc etre 1) avec la formule suivante :
=INDEX(LINEST(B1:B10,A1:A10^(1/2/3),,TRUE),1,1)
Le resultat n'est pas tres concluant : 1864.11 !! A noter que l'utilisation {} est tres mal percu par Excel de meme que le . Donc un exemple me ferait effectivement bien plaisir :)
Passons ensuite a Trireme. Il est evident qu'il y a toujours la methode lourde de l'analyse numerique en repartant du debut. Mais ma question etait justement destine a eviter ce genre de calcul qui me ferait perdre pas mal de temps vu que je ne suis pas tres expert avec Excel et que cela fait quelques annees maintenant que je n'ai plus relu mes cours d'AN. Bref cette solution sera vraiment le dernier recours je crois.
Enfin Michel. Votre premiere methode faisant appel a VBA me parait assez fastidieuse et de plus mon cahier des charges stipule une "utilisation minimale de VBA" ! Bref je vais essayer de voir comment appliquer votre 2e methode qui m'a l'air en seconde lecture plutot interessante !
Je vous re-tiens au courant dans la journee !
PS @Michel Nolf: d'un point de vue strictement mathematique il n'y a aucun probleme a ce qu'une valeur en x soit negative a priori !
