interpolation non linéaire dans excel Fermé

Question

Bonjour, j'ai un tableau de valeur dans EXCEL 2007 que j'aimerais transformer en une fonction polynomiale d'ordre 3. Pour l'instant je crée un graphe à partir du tableau, demande une courbe de tendance polynomiale et recopie tous les coefficients à la main. Comme je dois souvent faire ça, je voulais savoir s'il existait une méthode automatique qui permette d'utiliser les coefficients d'interpolation directement sans avoir à les recopier. Quelqu'un saurait comment faire?
Merci

Nyctaclope · Answer

Bonjour !  
Voyant que tu n'as pas encore de réponses complètes, voici quelques pistes sommaires :  

1// tu cherches une fonction y=Ax^3+Bx^2+Cx^1+D  
Il te faut donc avoir 4 valeurs de y pour 4 valeurs de x, pour résoudre un système de 4 équations à 4 inconnues ( les coefficients A B C D ).  
Voici un début de piste de résolution :  
https://www.ilemaths.net/sujet-systeme-de-4-equations-a-4-inconnues-333106.html  

Ecris dans une feuille à part y et ses valeurs correspondantes obtenues à partir du tableau.  
La dernière doit te donner un premier coefficient, que tu reportes par des formules dans les précédentes, et ainsi de suite, etc ..  
Au besoin, demande à Excel d'effectuer un nombre suffisant d'itérations dans les  calculs indiqués. 
Il serait élégant d'utiliser des cellules à part, pour y "loger" les valeurs de tes coefficients et les "reporter" convenablement dans les formules.  

2// il existe des formules permettant de résoudre directement un tel système, à base de la fonction DETERMINANT(), mais c'est pour moi beaucoup trop loin pour que je m'y replonge sans dommages collatéraux :)) ..  

3// tu dois pouvoir aussi t'en sortir avec une "macro"  
- écris ton tableau ( toujours au même endroit )  
- puis débute l'enregistrement macro, et exécute toutes les opérations manuelles que tu as décrites.  
- termine l'enregistrement, et vas consulter ta macro, et bidouille là au besoin pour la rendre plus fonctionnelle.  
En lui affectant une combinaison de touches clavier pour lancer ta macro, tu devrais pouvoir t'en sortir.  
Ce ne sera peut être pas très élégant, mais cela peut aider pour une première solution rapide.  

Espérant que tu obtiendras des réponses plus complètes que la mienne ..  
Mais tu as déjà de quoi t'amuser, à titre d'exercice ...  

A+ et bonne chance ..  
Nyctaclope

Nyctaclope · Answer

Re 
Comme je me sens courageux, je t'explicite la solution 1// du post précédent : 

1// Ton tableau numérique y en fonction de x ressemble à ceci : 
y1 y2 y3 y4    ( 1,2,3,4 étant les indices des valeurs ) 
x1 x2 x3 x4 
2// tu veux trouver les coefficients A B C D de la formule y = f(x) : 
y = Ax^3 + Bx^2 + Cx^1 + D   ( ^ signifiant la puissance ) 
3// tu réserves donc dans une feuille les cellules A(1) B(1) C(1) D(1) pour contenir les valeurs A B C D qui vont se calculer par itérations successives, 
Si bien que ta formule finale sera y = A(1)x^3 + B(1)x^2 + C(1)x^1 + D(1) 
4// tes 4 résultats numériques connus s'écrivent, en fonction des coefficients inconnus A B C D : 
(1) : y1 = Ax1^3 + Bx1^2 + Cx1^1 + D 
(2) : y2 = Ax2^3 + Bx2^2 + Cx2^1 + D 
(3) : y3 = Ax3^3 + Bx3^2 + Cx3^1 + D 
(4) : y4 = Ax4^3 + Bx4^2 + Cx4^1 + D 
5// la relation (4) te permet de tirer D en fonction du reste ( encore mal connu ..) : 
D = y4 - Ax4^3 - Bx4^2 - Cx4^1, et tu écris DONC dans la cellule D(1) la formule : 
= y4 - A(1)x4^3 - B(1)x4^2 - C(1)x4^1 
Bien sûr tu vas chercher les valeurs y4 et x4 dans les bonnes cellules du tableau .. 
6// la relation (3) ... te permet d'écrire dans la cellule C(1) la formule : 
= ( y3 - A(1)x3^3 - B(1)x3^2  - D(1) ) / x3^1 
7// la relation (2) te permet d'écrire dans B(1) la formule : 
= ( y2 - A(1)x2^3 - C(1)x2^1 - D(1) ) / x2^2 
8// puis la relation (1) te donne en A(1) la formule : 
= ( y1 - B(1)x1^2 - C(1)x1^1 - D(1) ) / x1^3 

9// la "mécanique" : D(1) recoit une première approximation qui donne une première approximation de C(1), laquelle améliore celle de D(1) , donnant une approximation pour B(1) etc etc ... 
En principe, cela doit rapidement converger .. 

S'agissant d'un calcul "auto alimenté" par une boucle sur lui même, Excel va rouspéter, et il y a quelque part à lui indiquer que le calcul doit aller jusqu'à N itérations. 20 à 30 itérations devraient convenir. Cela peut dépendre de la "courbe", si elle est plus ou moins "plate". 
Je crois me souvenir que les itérations se définissent dans les "options". Il va falloir que tu cherches un peu, car je n'ai pas ici Excel sous la main. 

Espérant t'avoir éclairci la chose .. 

A+ 
Nyctaclope

Interpolation non linéaire dans excel

2 réponses

Discussions similaires