Excel, Analyse de données et approximation polynomiale

[chuinul]

Bonjour a tous,

Je viens vous voir car j'ai de gros doutes sur mes compétences : soit je suis vraiment nul, soit j'y connait rien en math mais je suis en école d'ingénieur, donc si c'est le cas je demanderai mon renvoi .

Je vous explique mon problème : je suis actuellement en stage, et j'essai de modéliser des trajectoires (de navires).

Pour cela j'ai tout un tas de données de position en X et en Y, sous Excel je trace ces différens points pour visualiser les trajectoire, jusque là tout va bien!

Une fois avoir tracé ces points je demande a Excel de me tracer "la courbe de tendance" (là je choisi Polynome d'ordre 4, ça me suffit), la courbe tracé est très proche des points préalablement déciné.

Avec cela Excel me donne la formule de ce polynome, a savoir : y = 2E-11x4 + 2E-06x3 + 0,0436x2 + 504,97x + 2E+06 & R2 = 0,999 ... donc semblable à 99,9%....

le seul hic c'est que ce polynome est ARCHI FAUX car si on calcul manuellement Y, avec un des X dans la plage de valeurs que j'utilise, la valeur trouvée est de l'ordre de 3 000 000 au lieu -15 000 dans la réalité. La courbe de ce polynome n'a rien a voir avec le "tracé" d'origine.

Alors a la limite je pourrais me dire Excel gère mal cette fonction, elle est bugée....mais 4 logiciels d'analyse de donnés très renommés me donnent le même résultat.

Donc 3 possibilités :

1. Le 4 logiciels et Excel ont tous faux et ils font exactement les mêmes erreurs (peu probable, voir impossible)
2. Je ne sais pas calculer un polynome manuellement (ça m'embetterai un peu)
3. Je n'ai pas bien compris ce que représente ce polynome (le plus probable a mon sens)

Si quelqu'un a une idée sur le sujet je suis super preneur. En tout Cas merci pour tout

Regarde la pièce jointe MyData.xlsEn PJ mes données que j'utilise pour calculer le polynome, première colonne le X, seconde colonne les Y.

Merci d'avance !!!

ChuisNul

 

[jeanpierre]

Re : Excel, Analyse de données et approximation polynomiale

Bonsoir chuinul, re le forum,

ton polynome : y = 2E-11x4 + 2E-06x3 + 0,0436x2 + 504,97x + 2E+06 & R2, ne serait-ce plutôt pas :

y = 2E-11^4 + 2E-06^3 + 0,0436^2 + 504,97x + 2E+06 & R2

Simplement une piste...

Car là, je n'y comprend pas grand chose au lire de ton fichier.

Bonne soirée.

Jean-Pierre

 

[dixit]

Re : Excel, Analyse de données et approximation polynomiale

bonjour,
y = 2E-11x4 + 2E-06x3 + 0,0436x2 + 504,97x etc ...
à mon avis, se lit : y = 2 10 puissance -11 multiplié par x puissance 4 etc ...
donc y = 0,00000000002*x^4 etc ... si je ne me trompe pas dans les zéros
à tester
il est possible d'accéder à "format des données" en cliquant sur la courbe pour choisir un affichage en nombre (et non en scientifique)

 

[chuinul]

Re : Excel, Analyse de données et approximation polynomiale

ton polynome : y = 2E-11x4 + 2E-06x3 + 0,0436x2 + 504,97x + 2E+06 & R2, ne serait-ce plutôt pas :

y = 2E-11^4 + 2E-06^3 + 0,0436^2 + 504,97x + 2E+06 & R2

Simplement une piste...

Non, ça n'aurait pas vraiment de sens....ce ne serait plus un polynome de degré 4..... cf le fichier Excel a l'adresse ci dessous pour mieux comprendre

bonjour,
y = 2E-11x4 + 2E-06x3 + 0,0436x2 + 504,97x etc ...
à mon avis, se lit : y = 2 10 puissance -11 multiplié par x puissance 4 etc ...
donc y = 0,00000000002*x^4 etc ...

oui tout a fait, c'est ainsi que je l'ai compris et interprété

Sinon, je réexplique mon fichier : ce sont les différentes position d'un bateau dans un repère cartésien (x,y), la première colonne sont les X successifs, la seconde représente les Y succesifs ce sont des donnés mesurés

un excell qui est beaucoup plus clair pour expliquer et montrer mon probleme est disponible a l'adresse suivante :

Ce lien n'existe plus

Merci d'y jeter un coup d'oeil si le pb vous interesse

Merci d'avance

ChuiNul

 

[dixit]

Re : Excel, Analyse de données et approximation polynomiale

re
avec une équation au 2è degré seulement, les résultats ne sont pas si loin
voir classeur joint

 

[Sylvain]

Re : Excel, Analyse de données et approximation polynomiale

Bonsoir,

C'est juste que ce qui est affiché sur le graphique est une approximation et quand on élève à la puissance 4 ça fait bizzare.
Pour avoir les bons coefficients tu peux faire comme suit :

Sur la feuille sheet2, de g8 à g420 tu mets 1.
Tu sélectionnes les cellules de I8 à I12.

dans la barre de formule, tu mets cette formule et tu valides par ctrl maj Entrée.
=PRODUITMAT(PRODUITMAT(INVERSEMAT(PRODUITMAT(TRANSPOSE(C8:G420);C8:G420));TRANSPOSE(C8:G420));J8:J420)

Si tu as bien validé par ctrl maj entrée, tu devrais voir des {} autour de la formule.

Tu devrais avoir les coefficients que tu peux copier en les sélectionnant puis tu fais copier, tu vas en C4 et tu fais collage spécial tu choisis valeurs et tu coches transposé.

statistiques : calcul des coefficients d'une régression linéaire

A+

 

[chuinul]

Re : Excel, Analyse de données et approximation polynomiale

c'est bon pour les '{ }' mais j'ai une erreur #NAME

d'après le lien mis dans le post précédent, il faut 6 colonnes (5 de donnés plus une avec que des 1), or moi j'en ai que 5 (4 de donnés plus une que de 1)

Et sinon, il n'y aurait pas moyen de trouver ces coeffs a partir de donnés qui sont dans 'Sheet1' et juste celles là

merci d'avance,

chuinul

 

[chuinul]

Re : Excel, Analyse de données et approximation polynomiale

ta formule ne prenait pas en compte les X (la colonne A), j'ai donc supprimer la collonne vide et chagé la selection de cette façon, ce qui donne ça :

=PRODUITMAT(PRODUITMAT(INVERSEMAT(PRODUITMAT(TRANSPOSE(A8:F420);A8:F420));TRANSPOSE(A8:F420));I8:I420)

Ben ya toujours #NAME? ...

please help

merci

 

[chuinul]

Re : Excel, Analyse de données et approximation polynomiale

Je pensais avoir trouvé quand j'ai pensé que c'était a cause de la langue de mon Excel a savoir l'anglais, j'ai donc traduit la formule en :

=MMULT(MMULT(MINVERSE(MMULT(TRANSPOSE(A8:F420);A8:F420));TRANSPOSE(A8:F420));I8:I420)

ce n'est plus un #NAME? que j'ai mais désormais un #NUM? .... je sais pas pourquoi !

Si quelqu'un a une idée?

merci

 

[Sylvain]

Re : Excel, Analyse de données et approximation polynomiale

Bonsoir,

je l'ai mis en pj sans le graphique pour la taille.

A+

 

[chuinul]

Re : Excel, Analyse de données et approximation polynomiale

Bonjour sylvain,

Merci pour tout

Mais il y a un problème dans le zip mis en PJ dans ton dernier post.

Si tu regarde les Y prévus, ils sont faux. A part le premier qui es dans le même ordre de grandeur, tous les autres sont très éloignés.

En effet, si l'on regarde le second calcul, il nous donne un Y prévu = -2200855,622 alors que le Y réel est -20470, il y a un facteur 100 entre les deux...

une idée? je suis allé voir sur pas mal de sites, et cette erreurs y est souvent recencé, mais les explications clairs sont rare...

Est-ce une erreur d'Excel dans le calcul de ce polynome, ou une erreur de notre part de son interprétation?

Merci pour tout a bientot

ChuiNul

 

[Sylvain]

Re : Excel, Analyse de données et approximation polynomiale

Bonjour,

je reviens de vacances, je remets le fichier avec la correction de la formule.

A+

 

[Dams90]

Re : Excel, Analyse de données et approximation polynomiale

Bonjour,

je reviens de vacances, je remets le fichier avec la correction de la formule.

A+

Merci cet astuce a enfin resolu tous mes soucis! Jusque la je transposais les coeff. à la main avec des risques de me tromper!

J'ai bien tenté de resoudre ce systeme à quatre equations et quatre inconnues mais quand j'ai ecrit que A valait un succession de 122 caracteres melangeant x1,x2,x3,x4 et leurs multiples, j'ai laissé tombé.

Merci encore.