[ 初めての方へ | 一覧(最新更新順) | 全文検索 | 過去ログ ]
『近似曲線係数抽出』（うりぼー）
散布図に近似曲線を追加し、それぞれの項の係数を任意のセルへの
抽出を考えています。
直線の場合はわかるのですが、曲線の係数は一体どのようにするのでしょうか？
マイスター様、是非是非ご教授の程を宜しくお願い申し上げます。
[エクセルのバージョン]
Excel2003
[OSのバージョン]
WindowsXP
グラフは苦手ですが･･･ 曲線を右クリックして近似線の追加を選択し、オプションの「グラフに数式を表示する」 にチェックを入れるとグラフ上に近似式が表示されます。 これを利用することはだめでしょうか？的外れ？ (1or8)
私自身ほんのさわりくらいしか理解してませんが 近似曲線の係数はほとんどの場合 エクセルの統計関数で求める事が出来ると思います。
>曲線の係数は一体どのようにするのでしょうか？ 具体的にはどんな曲線でしょうか？
例えば
x y 5 25 4 15 1 35 2 45 3 10
↑この散布図で「線形近似(L)」 [グラフに数式を表示] [r-2乗値を表示] にチェックで
y=5x+41 r^2=0.3049
グラフ上にこのように表示されます。
適当なセルに =SLOPE(B2:B6,A2:A6) ← 5x [傾き] =INTERCEPT(B2:B6,A2:A6) ← 41 [切片] =CORREL(A2:A6,B2:B6)^2 ← 0.3049 [決定係数R2乗] ほんの一例ですがこのように求められますし 別の関数、数式でも同様に表示させる事ができます。
当然ですが、実際に使用する近似曲線の種類によって 関数も違ってきますね。 http://atiboh.sub.jp/05toukei-kansuu.html
↑詳細に説明、解説されてると思います。 （jun53）
いろいろとご説明ありがとうございました。
情報に不備がありましたことをお詫びいたします。 実際のデータは以下の通りです。 X Y -247.7 -9.6 -246.1 -9.6 -244.5 -9.6 -242.9 -9.8 -242.9 -11.2 -241.3 -11.2 -239.7 -11.2 -238.1 -11.4 -236.7 -11.5 -235.1 -11.5 上記データを散布図作成して頂き、多項式近似で６次を選択し、 グラフと式を表示させます。 各次数の係数を任意のセルに出力したくあります。 slopeだと1次式の係数となります。 ２次以上の場合はどのようにするのかがわかりませんでした。 (うりぼー)
多項式の６次ですか？ 一気に未知の分野に足を踏み入れた感じです、困りました。 以下は間違ってる可能性大です、自信有りません、検証確認をお願いします。
最初に２次で作ってみます。 データ加工が必要なので、B列を一列挿入して「x^2」データを作ります。
A B C 1 X x^2 Y 2 -247.7 61355 -9.6 3 -246.1 60565 -9.6 4 -244.5 59780 -9.6 5 -242.9 59000 -9.8 6 -242.9 59000 -11.2 7 -241.3 58225 -11.2 8 -239.7 57456 -11.2 9 -238.1 56691 -11.4 10 -236.7 56026 -11.5 11 -235.1 55272 -11.5
B2に =A2^2 下にコピー
ツールメニューから[分析ツール]-[回帰分析] OK 入力Y範囲に C2:C11 入力X範囲に A2:B11 OK 新規シートに結果が作成されます。下段に 切片 467.12 X 値 1 4.1463 X 値 2 0.0089 が表示されてると思います。
[うりぼー]さんが提示されたデータで散布図「２次多項式」を作成。 グラフに数式を表示させると y = 0.009x2 + 4.1464x + 467.12 桁数処理の都合で数値が違ってますが、詳しく見れば一致します。
６次は５列挿入してデータ加工します。
A B C D E F G 1 X x^2 x^3 x^4 x^5 x^6 Y 2 -247.7 -9.6
B2(x^2) =A2^2 C2(x^3) =A2^3 D2(x^4) =A2^4 E2(x^5) =A2^5 F2(x^6) =A2^6 全て下にコピー
[回帰分析] で 入力Y範囲に g2:g11 入力X範囲に A2:f11 OK
以下、同様手順
２次迄しか確認してないので出来るかどうか心配ですが これでどうでしょうか？
また、この分野に詳しい方、訂正、添削をお願いします。 （jun53）
関数では↓このようにすると求める事が出来るようです。
[Excelの関数を用いて多項式近似(2次)の定数を求めます。] http://atiboh.sub.jp/t09takoushiki2.html
２，３行読んだけど目がチカチカします、私はギブアップです。 もし、時間と興味が有りましたら確認してください。 （jun53）
X Y
-247.7 -9.6 -246.1 -9.6 -244.5 -9.6 -242.9 -9.8 -242.9 -11.2 -241.3 -11.2 -239.7 -11.2 -238.1 -11.4 -236.7 -11.5 -235.1 -11.5
に対し、=INDEX(LINEST(B2:B18,A2:A18^{1,2,3,4,5,6},1,1),1)
INDEX(LINEST(B2:B18,A2:A18^{1,2,3,4,5,6},1,1),2)
.
.
.
INDEX(LINEST(B2:B18,A2:A18^{1,2,3,4,5,6},1,1),5)
のようにすると、５次式までは係数は抽出できましたが、
なぜか、６次の係数がエクセルのグラフ上の式の係数と異なります。
う〜ん、なぜなのでしょう？
=INDEX(LINEST(B2:B8,C2:C8^{0,1,2,3,4,5,6},,FALSE),1,1)
.
.
.
INDEX(LINEST(B2:B18,A2:A18^{1,2,3,4,5,6},1,1),5)
そして、以下のデータだと、６次の係数が出力できました。
1 4,200 2 6,100 3 7,300 4 7,850 5 8,700 6 10,500 7 16,650
どなたかわかりませんでしょうか？
（うりぼー）
挑戦している間に追加の書き込みがありましたね。 追加の分、 4,200 6,100 7,300 7,850 8,700 10,500 16,650 がよくわからないのですが、↓とりあえず書いていたものです。
============
何か大変な事になってきました。ミスリードで無ければ良いのですが...
リンク先、関数での「※グリコのおまけ1」 (9)作業列を使用しないで...の方法ですね？
挑戦してみました。
説明に 「INDEX関数とLINEST関数を使用します。
説明変数(x)に対し、配列で{1,2}と入力、べき乗します。(3次なら{1,2,3})」
と、あります。
A B 1 X Y 2 -247.7 -9.6 3 -246.1 -9.6 4 -244.5 -9.6 5 -242.9 -9.8 6 -242.9 -11.2 7 -241.3 -11.2 8 -239.7 -11.2 9 -238.1 -11.4 10 -236.7 -11.5 11 -235.1 -11.5
↓例えば15以降に(端数処理してます)
A B C D E F G H 15 切片 x値1 x値2 x値3 x値4 x値5 x値6 16 ２次 467.12 4.146 0.00897 17 ３次 47923 593.893 2.45155 0.003 18 ４次 -2835999 -47219.9 -294.787 -0.8177 -0.0008 19 ５次 -556432 0 96.4332 0.8027 0.0025 2.7796 20 ６次 -2541132 0 607.493 6.4485 0.0288 0.00006 5.015
私の解釈では、LINESTの配列方法が少し違う気がしますが、
２次
B16 =INDEX(LINEST($B$2:$B$11,$A$2:$A$11^{1,2},1,1),1,3)
C16 =INDEX(LINEST($B$2:$B$11,$A$2:$A$11^{1,2},1,1),1,2)
D16 =INDEX(LINEST($B$2:$B$11,$A$2:$A$11^{1,2},1,1),1,1)
３次
B17 =INDEX(LINEST($B$2:$B$11,$A$2:$A$11^{1,2,3},1,1),1,4)
C17 =INDEX(LINEST($B$2:$B$11,$A$2:$A$11^{1,2,3},1,1),1,3)
D17 =INDEX(LINEST($B$2:$B$11,$A$2:$A$11^{1,2,3},1,1),1,2)
E17 =INDEX(LINEST($B$2:$B$11,$A$2:$A$11^{1,2,3},1,1),1,1)
４次
B18 =INDEX(LINEST($B$2:$B$11,$A$2:$A$11^{1,2,3,4},1,1),1,5)
C18 =INDEX(LINEST($B$2:$B$11,$A$2:$A$11^{1,2,3,4},1,1),1,4)
D18 =INDEX(LINEST($B$2:$B$11,$A$2:$A$11^{1,2,3,4},1,1),1,3)
E18 =INDEX(LINEST($B$2:$B$11,$A$2:$A$11^{1,2,3,4},1,1),1,2)
F18 =INDEX(LINEST($B$2:$B$11,$A$2:$A$11^{1,2,3,4},1,1),1,1)
５次
B19 =INDEX(LINEST($B$2:$B$11,$A$2:$A$11^{1,2,3,4,5},1,1),1,6)
C19 =INDEX(LINEST($B$2:$B$11,$A$2:$A$11^{1,2,3,4,5},1,1),1,5)
D19 =INDEX(LINEST($B$2:$B$11,$A$2:$A$11^{1,2,3,4,5},1,1),1,4)
E19 =INDEX(LINEST($B$2:$B$11,$A$2:$A$11^{1,2,3,4,5},1,1),1,3)
F19 =INDEX(LINEST($B$2:$B$11,$A$2:$A$11^{1,2,3,4,5},1,1),1,2)
G19 =INDEX(LINEST($B$2:$B$11,$A$2:$A$11^{1,2,3,4,5},1,1),1,1)
６次
B20 =INDEX(LINEST($B$2:$B$11,$A$2:$A$11^{1,2,3,4,5,6},1,1),1,7)
C20 =INDEX(LINEST($B$2:$B$11,$A$2:$A$11^{1,2,3,4,5,6},1,1),1,6)
D20 =INDEX(LINEST($B$2:$B$11,$A$2:$A$11^{1,2,3,4,5,6},1,1),1,5)
E20 =INDEX(LINEST($B$2:$B$11,$A$2:$A$11^{1,2,3,4,5,6},1,1),1,4)
F20 =INDEX(LINEST($B$2:$B$11,$A$2:$A$11^{1,2,3,4,5,6},1,1),1,3)
G20 =INDEX(LINEST($B$2:$B$11,$A$2:$A$11^{1,2,3,4,5,6},1,1),1,2)
H20 =INDEX(LINEST($B$2:$B$11,$A$2:$A$11^{1,2,3,4,5,6},1,1),1,1)
私の解釈ではこんな計算式になります。 理解もせず始めて挑戦したので、間違っていたら [うりぼー]さんの方法でお願いします。
参考まで↓回帰分析で求めた係数です。(端数処理してます)
２次 ３次 ４次 係数 係数 係数 切片 467.1 切片 47923.1 切片 -2835999.3 X 値 1 4.15 X 値 1 593.89 X 値 1 -47219.99 X 値 2 0.009 X 値 2 2.452 X 値 2 -294.787 X 値 3 0.003 X 値 3 -0.818 X 値 4 -0.001 ======================
５次 ６次 係数 係数 切片 -556432 切片 -2541132 X 値 1 0 X 値 1 0 X 値 2 96.433 X 値 2 607.494 X 値 3 0.803 X 値 3 6.449 X 値 4 0.003 X 値 4 0.029 X 値 5 0 X 値 5 0 X 値 6 0
このように抽出されました。 （jun53）
私の知ってる分析法のひとつとしては 何らかの方法で係数を抽出し、誤差を求め、項を増やして確率を求める、なのですが。
A B C D 1 X Y 2 -247.7 -9.6 -7.74567 -19.3159375 3 -246.1 -9.6 4 -244.5 -9.6 5 -242.9 -9.8 6 -242.9 -11.2 7 -241.3 -11.2 8 -239.7 -11.2 9 -238.1 -11.4 10 -236.7 -11.5 11 -235.1 -11.5
グラフ上数式からは ２次のグラフ上数式で y = 0.009x2 + 4.1464x + 467.12 C2に =( 0.009*A2^2) +( 4.1464*A2) + 467.12 D2に =(C2-B2)/B2*100 どちらも、下にコピー このようにして誤差を求め、項を増やして確率を求める。 ６次では y = 9E-05x6 + 0.1346x5 + 81.212x4 + 26134x3 + 5E+06x2 + 5E+08x + 2E+10 ６次では係数自体が異なりますか？
何故異なるのか私にも解りません、すみません。
ただし、 エクセル統計関数や分析ツールには、ものによってバグが有るようです。 精度も低い、こんな情報も見た覚えがあります。 http://aoki2.si.gunma-u.ac.jp/Hanasi/excel/ http://aoki2.si.gunma-u.ac.jp/Hanasi/StatTalk/11-1.html
x の値が大きすぎると精度が悪くなるそうです。 私自身確認した訳では有りませんが、このような事かもしれませんね。 （jun53）
jun53さん、ありがとうございます。
計算の仮定でどちらかの結果かが発散とかしてるんでしょうかね。 ちなみに、Xを-1/100倍、Yを-100倍とかして、B2を96とかにすると、係数がかなり近い値になります。 どちらの方が精度が良いかわかりませんので、5次フィッティングを計算に使いたいと 思います。
どうも、ありがとうございました。
（うりぼー）
[ 一覧(最新更新順) ]
YukiWiki 1.6.7 Copyright (C) 2000,2001 by Hiroshi Yuki.
Modified by kazu.