いくつかの(x,y)値から、関係式（近似）を求めたいです。

質問

手元にx軸とy軸からなる図（グラフ）があり、ここからxとyの関係を表す数式（近似のもの）を求めたくて試行錯誤しているのですが、なかなかうまくいきません…。

xとyの値は図から大体読み取れて、だいたい質問欄下部のようになっています。
xの値が大きくなればなるほど、yの増え方が緩やかになるグラフです。

Excelの近似曲線で求められるかと、いろいろ試してみたのですが、
グラフの一部分が望みとかなりずれてしまい、うまくいきませんでした。

(x,y)の値を5点ほど書くと、下のような感じです。
(x, y)
(0, 0)
(50, 0.75)
(100, 1)
(500, 1.75)
(1000, 2)

(0 < x < 1000)が合っていれば、その他の値はどうでもいい感じです。
Excelを利用する方法でも、それ以外の方法でもよいですが、
何かよい方法がありましたら、教えて頂きたいです。
よろしくお願いします。

（図も、用意できたら添付しようと思います）

回答No.2

inara1 · ベストアンサー

x → ∞で y が一定値になるのなら(1)、x → ∞で y が一定値にならない（増加）なら(2)で近似するのがいいと思います。どちらも x = 0 のとき y = 0 になる関数です。
　　(1)　y = a*[ 1 - exp{ -(x/b)^c } ]
　　(2)　y = a*ln( x/b + 1 )
質問文にある5点データで最小2乗近似すると各パラメータは
　　(1)　a = 2.23704538731092、b = 242.947998815054、c = 0.574668642035933
　　(2)　a = 0.457968779687129、b = 12.1754001626539
となりました。(1)のほうが近似度が優れています。このパラメータでグラフを描いて確かめてみてください。

Excelのソルバーというのを使えば、任意の関数で最小2乗近似することができます。ツールバーの「ツール」に「ソルバー」という項目がなければ、「ツール」→「アドイン」で出た画面の「ソルバーアドイン」をチェックしてＯＫとすればソルバーが使えるようになります（CD-ROMが必要なこともあります）。もしソルバーを使って近似してみたいのならお知らせください。

お礼

ありがとうございます！
かなり欲しいものに近いグラフが得られました。
今後のためにも、自分でもソルバーの近似でグラフを作ってみようと思うのですが教えて頂けますか？

補足

Excel2007ですがソルバーは使えそうです。

回答No.4

inara1

測定データが物理現象に基づいているものなら、普通はその物理モデルを関数形に使います。
ご質問のデータが何か分かりませんが、x を時間として、y が0から増加して飽和していくような変化は電気回路ではよくある現象です。たとえばRC直列回路に電圧 V0 をかけたときのコンデンサの電圧の時間変化は V0*{ 1 - exp( -t/C/R) } になりますが、この形がご質問のデータに似ていたので最初は y = a*[ 1- exp(x/b) ] を試してみました。この関数は、最終値（飽和値）がa、y が最終値の63%になるときの x の値が b なので、a と b の概略値はご質問のデータからすぐに分かります（a = 2.5、b = 200くらい）。しかし、この関数で近似してみるとピッタリ合わないので x/b を (x/b)^c に変えました（この形は電気回路では出てきませんので創作です）。c の値は、ANo.3のように実際にグラフを描いて、少しずつ値を変えてみて概略値を決めました。

関数 y = a*[ 1- exp{ (x/b)^c } ]　は x が大きくなると飽和してしまいますので、もし元のデータが飽和しないものなら、この関数は適切ではありません。そこで代替としてANo.1さんの関数も考えて、y = a*ln( x/b + 1 ) というのも試してみました（x = 0 のとき y= 0 とするためにlnの中に＋１を入れました）。この関数のパラメータは２つだけですが、最初は y = a*ln{ (x/b)^c - 1 } という３つのパラメータでやりました。しかし、c の値を変えてもピッタリ度（データと近似値の差の最大値）があまり変わらなかったので、y = a*ln( x/b + 1 ) に戻しました。この関数はパラメータが２個しかないので、データ中から適当に選んだ２組の（x,y）の値が分かればa, b の概略値が求められます。

なお、多項式（ y = a + b*x + c*x^2 + ・・・）も試してみましたが、これは次数を大きくしても近似曲線が脈打ってしまいうまくいきませんでした。多項式は近似関数を x = 0 の近傍でテイラー展開した式ですから、x が大きいところでは曲線が大きく変化してしまうため、近似関数としてはあまり有用でありません（３次式くらいなら脈打ちませんがピッタリ合わない）。

脈打ちの少ない近似曲線として「パデ近似（Pade近似）」というのがあります。この関数は
　　　y = ( a1 + b1*x + c1*x^2 + ・・・）/( a2 + b2*x + c2*x^2 + ・・・）
です。分母と分子の次数が同じなら、x が大きいときの最終値は最高次の係数の比になりますし、x が大きいときに0になるような場合は分母の次数＞分子の次数とすればいいなど自由度があります。ただ、この関数のパラメータの初期値（概略値）は全く想像できないので、最初は次数の小さい関数
　　　y = ( a1 + b1*x ）/( a2 + b2*x ）
で当てはめてみて、１つずつ次数を大きくしていくのがいいと思います。その場合、増やした項の係数は0.001など小さい数値を初期値とするのがいいです。パデ近似というのは有用なのですが、実際に使われた例はあまり見ません。たぶん初期値をうまく決められず収束しないからだと思いますが。腕試しにパデ近似でやってみてはいかがでしょうか。近似度を見るには、横軸をxの値、縦軸を （y-近似値） としたグラフを描いてみるといいと思います。凹凸が少ない（差の絶対値が小さい）ものが良い近似といえます。

パデ近似の利点について教えて下さい　http://sqa.scienceportal.jp/qa4984989.html

お礼

丁寧な解説、ありがとうございます。
とても勉強になりました。
もう1つ近似してみたいグラフもあるので、
それで、パデ近似やソルバーなど練習しようと思います。
何度も質問に答えて頂き、ありがとうございました!

回答No.3

inara1

平日は仕事なのでお返事遅れました。
Excel2007で説明します。

【ソルバーを組込む】
　（１）　左上隅の「オフィスボタン」をクリックして、一番下の「Excelのオプション」を選択し、左側の「アドイン」をクリック
　（２）　一番下のところの「管理」が「Excelアドイン」になっていたら「設定」をクリック。なっていなかったら「Excelアドイン」を選んで「設定」をクリック。
　（３）　「ソルバーアドイン」の左の□をチェックして「ＯＫ」→「はい」→ソルバーが組み込まれる（時間がかかる）→組込みが終わればワークシートに戻る
　（４）　ワークシートのツールバーの「データ」をクリックすると、右側の「分析」のところに「ソルバー」と出ていたら組込み完了

【ソルバーを使った最小2乗近似】 ----近似関数が y = a*[ 1- exp{ (x/b)^c } ] の場合
　（１）　添付図のように、ワークシートのＡ列の適当な行以降に x の値、A列と対応する行のB列に y の値（実測データ）を貼り付ける
　（２）　セルC2に a の初期値、C3に b の初期値、C4に c の初期値を書込む（ここに記入する数値はある程度合っている値としてください）
　（３）　C列に近似式を使った近似値を記入する
　　　　　　3-1　セルC8に　=$C$2*(1-1/EXP((A8/$C$3)^$C$4)) を貼り付けてEnter --- 他の関数形ならこの式を変えてください
　　　　　　3-2　セルC8をコピーして、C9からC308（データのある最後の行）までペースト
　（４）　D列に実測値（B列）と近似値（C列）の差の２乗を書込む
　　　　　　4-1　セルD8に　=(B8-C8)^2 を貼り付けてEnter
　　　　　　4-2　セルD8をコピーして、D9からD308（データのある最後の行）までペースト
　（５）　セルC5に =SUM(D8:D308) を貼り付けてEnter。C5が実測値と近似値の差の２乗和になります（最終的にこれを最小にする）。
　（６）　A列を横軸、B列とC列を縦軸としたグラフを描く。最小２乗計算にグラフは必要ないですが、描いておくとちゃんと近似されているか確認できます。
　（７）　ツールバーの「データ」をクリックし、右側の「分析」のところに「ソルバー」をクリック
　（８）　目的セルを $C$5 とし、その下の目標値の「最小値」のラジオボタンをクリック（セルC5を最小にする）
　（９）　変化させるセルを $C$2:$C$4 とする（C2～C4の値を変えて C5 が最小になるようにする）
（１０）　右側の「オプション」をクリックし以下のように設定（以下はこちらで実行した例ですので数値は変えて結構です）
　　　　　　　10-1　制限時間 100秒 （100秒で収束しなかったら終了）
　　　　　　　10-2　反復回数 10000 （10000回反復して収束しなかったら終了） --- 1000ではうまく近似できませんでした
　　　　　　　10-3　ｖ精度・公差・収束を 0.000001（この意味はよく分かりませんが、小さい数値にするほど高精度になります。しかし、あまり小さくするとデータによっては収束しない場合もあります）
　　　　　　　10-4　「OK」
（１１）　「実行」をクリック → 「最適解が見つかりました。制約条件は全て満たされました」と出たらOK
（１２）　ワークシートの C2～C4 に出ている数値が最小２乗近似されたパラメータになります。グラフを見て、近似が良くないと思ったら、（１０）の条件を変えてみてください。

お礼

ありがとうございます！
大体、ソルバーの使い方が分かりました。
勉強になりました。
ちなみに、
近似関数を　y = a*[ 1- exp{ (x/b)^c } ]　に決めたり
初期パラメータを決める方法は、どうしているのですか？
やはり経験則 or おおざっぱに計算してみる、
という形なのでしょうか？

もう質問自体は解決したのですが、よければ教えて頂きたいです。

回答No.1

gohtraw

対数でしょう。エクセルでｌｏｇ（ｘ＋６）に対してｙをプロットするとほぼ直線になります。大雑把にしか試していませんが大体この辺りではないかと思います。

お礼

ありがとうございます。
でも、もう少し正確なのが欲しい状態です。
やはりただのlog関数だと近似率がよくないですね。