エクセルの多項式近似について

質問

エクセルのグラフの機能で、多項式近似というものがあるんですが、この多項式近似に使用されている、数値解析の考え方はなんでしょうか？
最小二乗法が使われているのでしょうか？
ご存知の方お教え下さい。

回答No.2

neKo_deux · ベストアンサー

ヘルプで「近似曲線」などを調べてみると、

| この分析方法は、回帰分析とも呼ばれます。

との事で、一般的に回帰分析と呼ばれる方法が利用されます。

--
Excelでの回帰分析に関して、以下のページが参考になります。１次式でしか使った事無かったですが、なるほど。

マック、まんが、アニメのページ？ - Excelでn次多項式の係数を求めたい【Mac/Windows】
http://www.amy.hi-ho.ne.jp/fujisawa/mac/tips/excel_lesq.html

回答No.4

stomachman

回答がつかないようなのでExcel Xでちょっと実験してみましたところ、グラフに出てくる多項式近似の係数は、単純な線形最小二乗法で計算した結果と一致しています。
実験のやり方は以下の通りでした。

まずグラフにするxとyの表を作りました。
(1) A列3行目からA列20行目までにテキトーな数値を入力します。これがxの値。（たとえば1から順番に整数を入れるのでも良いのです。）
(2) C列1行目からF列1行目までに それぞれテキトーな数値を入力します。これが多項式の係数です。たとえば順に3、４、７、6とでも入れてみます。
(3) B列3行目に 式
=C$1*A3^3+D$1*A3^2+E$1*A3+F$1+$A$1*(rnd()-0.5)
を入れ、B列20行目まで「以下同様」ってやります。これが多項式に誤差を入れて計算したyです。誤差の大きさはA行1列に適当な値を入れることで調節します。たとえば1000とか入れてみます。

次に散布図のグラフを描かせて、近似多項式を計算させました。
(4) A列とB列を使ってグラフを描き、グラフの「近似曲線の追加」機能を使って次数を3に指定した多項式近似をやらせて、その多項式を表示させます。
誤差$A$1*(rnd()-0.5)を入れたので、多項式の係数は当然ながらC列1行目～F列1行目で与えた係数とは違った値になります。

さて、自前で線形最小二乗法による近似多項式の係数を計算します。本来まじめに数値計算をやるには、計算誤差や処理効率に配慮すべきなのですが、この場合は手抜きで十分です。Excelの逆行列の機能を利用して簡単にやっつけてしまいます。
(5) C列3行目に=A3^3、D列3行目に=A3^2、E列3行目に=A3、F列3行目に=1を入れます。そして、C列3行目からF列20行目までを「以下同様」ってやります。（この結果、F列には1ばっかりが入ります。）
(6) B列22行目からF列25行目に４行５列の行列
{=MMULT(TRANSPOSE(C3:F20),B3:F20)}
を作ります。（もし行列の入力の操作方法をご存じなければ、マニュアルを見てください。カンでやったんじゃ旨く行きませんから。Excelのここんとこはもお、酷い仕様だと思います。）
これが正規方程式の係数になっています。これを解きます。
(7) C列27行目からF列30行目に４行４列の行列
{=MINVERSE(C22:F25)}
を作ります。
(8) B列27行目からB列30行目に４行1列の行列つーかベクトル
{=MMULT(C27:F30,B22:B25)}
を作ります。これがxとyの値だけに基づいて最小二乗法で計算した係数です。グラフに表示されている近似式の係数と比較すると、イロイロ条件をいじっても表示した桁数の範囲で一致していました。

回答No.3

sanori

＃１です。

そうそう。「回帰」という単語、ありましたね。
昔のＬｏｔｕｓ１－２－３では、１次式の最小二乗法を行なうメニューが「回帰」という名前でした。
前にも書きましたが、２次式以上は、私は手作りでやりました。

さて、ちょっとは数学的表現も書いておいたほうがよいかなと思いますので、追記します。

いちばん簡単な例で、２次の近似曲線の導出の始まり部分を書いてみましょう。
今、手元に文献がないので、頼りない私の自力で書きます。（笑）

点(x1,y1),(x2,y2)・・・(xn,yn)で構成されるデータに対する２次の近似曲線を考えます。

各点がその近似曲線からどれだけ外れるかをεで表します。
y1 = a・x1^2 + b・x1 + c + ε1
y2 = a・x2^2 + b・x2 + c + ε2
・・・
yn = a・xn^2 + b・xn + c + εn

εが左辺に行くように直します。
-εk = a・xk^2 + b・xk + ck - yk

はずれの程度εは、プラスの値とマイナスの値がありますから、２条で考えるほうが合理的です。
（１次の最小二乗法と同じ考え方）
そこで両辺を２乗します。

(εk)^2 = ・・・（略）

次に、k=1～n の範囲の全てを足し算します。
すると、

Σ(εk)^2 = ・・・（略）
（k=1～n）

最小二乗法の考え方なのですから、要するに、これの左辺が最小になればよいのです。

・・・ということは、右辺の微分がゼロになればよいわけです。（関数が極小値を取るときは、そこでの微分の値がゼロになりますよね？）

右辺の微分とは言っても、変数が a, b, c の３個あるので、それぞれの偏微分を計算することになります。

途中の計算を書くと煩雑なので思いっきり省略しますが、
a, b, c について、それぞれ偏微分して、
その偏微分して得られた式の値がゼロということで計算すれば、
たしか３個の式による連立方程式が得られたと思います。

こうして、a, b, c の値が求まります。
→ということは、２次の近似曲線が求まったことになります。

＃１のところに書いていた「収束云々ではなく、一意に解ける」とは、このことです。

以上、だいたいこんな感じですが、ざざーっと書きましたので、どこか書き落とし・誤りがあったら、ごめんなさい。

回答No.1

sanori

私は、こんな経験があります。

エクセルがまだこの世に登場していない時期だったと思いますが、Ｌｏｔｕｓ１－２－３で、多項式近似を計算してグラフにしていました。

「最小二乗法」は、おそらく、１次式にのみ使われる言葉だったと思いますが、
最小二乗法の考え方を２次以上の式へ近似するときへも拡張した解法があります。
その近似式というのは、収束させるとか云々ではなくて、一意に「解く」ことができます。
例えば、「点がＮ個あったら、それに対する５次の近似式は、これです」という感じで。

以下、あくまでも予想ですが、
昔のＬｏｔｕｓ１－２－３で、私ごときが簡単にできた計算ですから、きょう日のＥｘｃｅｌで自動計算出来ないはずはないと思いますし、たぶん、それではないかと思います。