Interpolation non linéaire dans excel
chamël
-
Nyctaclope Messages postés 5315 Date d'inscription Statut Membre Dernière intervention -
Nyctaclope Messages postés 5315 Date d'inscription Statut Membre Dernière intervention -
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
Merci
A voir également:
- Excel interpolation polynomiale
- Liste déroulante excel - Guide
- Word et excel gratuit - Guide
- Déplacer colonne excel - Guide
- Si ou excel - Guide
- Excel moyenne - Guide
2 réponses
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
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
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
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