エクセルの折れ線グラフの近似式で得られた式を自動で入力する

質問

エクセルのA列に数字が入力されていてそれを基にエクセルで折れ線グラフを書くとします。

その折れ線グラフをを近似曲線で近似した場合、「近似曲線の書式設定」の「オプション」にある「グラフに数式を表示する」をチェックすると、
その近似曲線の元になる数式が表示されます。
その数式の計算式をB列に入力すると、近似曲線で得られたY値をB列に入力することができます。

ここで質問ですが、A列の数字を変えると、近似曲線の式そのものが変わってしまうため、B列のY値を得るためにはその都度、得られた式をB列に入力しなければなりません。
A列の数字を変えたとき、前もって設定した近似の種類や次数のまま、自動的にB列の近似式の数字も変更する方法（つまり得られた式を自動でB列に入力する方法)はありますか。

回答No.4

xls88 · ベストアンサー

提示されたマクロですが、特に問題はないと思います。
下記マクロを試してください。
数式が、C1:C10に入力されます。
試しに、セルA1:A10に、1から10の連続値を入れてみてください。
blackleonさんが提示されたマクロの実行結果、つまりB1:B10と同じ値になると思います。

Sub test2()
Dim trdtxt As String
trdtxt = ActiveChart.SeriesCollection(1).Trendlines(1).DataLabel.Text
trdtxt = Replace(trdtxt, "x ", "x1")
trdtxt = Replace(Replace(Split(trdtxt, "=")(1), "x", "*A1^"), " ", "")
Range("C1:C10").Formula = "=" & trdtxt
End Sub

しかし得られた値は、グラフに表示されている近似曲線の値とはかなりズレがあります。
下記サポート情報に、解説と対処策が載せられています。
（完璧には対処できないようです。）

[XL]近似曲線「多項式近似曲線」について
http://support.microsoft.com/kb/402428/ja

お礼

ご回答に感謝いたします。
お示しいただいたサイトも大変参考になりました。
ありがとうございました。

回答No.3

xls88

＞回答番号：No.2 この回答へのお礼
お礼を頂きましたが、質問が閉じられないのは、解決には至らない、ということですか？
回答が的を外しているのなら、具体的な例を挙げ補足していただければ、再検討してみます。

補足

ご丁寧にありがとうございます。今いろいろ試行錯誤させていただいております。
お言葉に甘えてもう一つ質問させていただきます。
まず私の目的ですが、あるデータを経時的に取り込んで、その最新のデータの並びを折れ線グラフで表し、それをスムージングして、その各々のXに対応するYの値を得たいということです。
スムージングするので近似式の次数は６次くらいがよいと思っています。
例えばA１からA10まで順に　5,8,9,5,4,7,9,8,10、8というデータが入力されているとして、6次の近似式を作ると
y = 0.0005x6 - 0.0095x5 - 0.0048x4 + 1.1894x3 - 8.8283x2 + 22.406x - 9.9となります。
お示しいただいたマクロを参考にして、この式をB列に入力するマクロを以下のように作ると（Xの値は行数と同じとして順にrow()で置き換えてみました）、X( =行数 )が増えるにしたがって、実際のグラフの値とかけ離れていくのです。
何か間違っているでしょうか。

Sub test()
Dim trdtxt As String
trdtxt = ActiveChart.SeriesCollection(1).Trendlines(1).DataLabel.Text
trdtxt = Replace(Replace(Split(trdtxt, "=")(1), "x6", "*row()^6"), " ", "")
trdtxt = Replace(trdtxt, "x5", "*row()^5")
trdtxt = Replace(trdtxt, "x4", "*row()^4")
trdtxt = Replace(trdtxt, "x3", "*row()^3")
trdtxt = Replace(trdtxt, "x2", "*row()^2")
trdtxt = Replace(trdtxt, "x", "*row()")
Range("B1:B10").Formula = "=" & trdtxt
End Sub

回答No.2

xls88

マクロを組んでみました。
該当グラフをアクティブにしてマクロを実行してください。
実情に合わせて、セル番地、セル範囲等を書き直す必要があります。
線形近似で、１次式と仮定しています。

Sub test1()
Dim trdtxt As String
trdtxt = ActiveChart.SeriesCollection(1).Trendlines(1).DataLabel.Text
trdtxt = Replace(Replace(Split(trdtxt, "=")(1), "x", "*C7"), " ", "")
Range("C8:G8").Formula = "=" & trdtxt
End Sub

お礼

ありがとうございました。
このマクロはとても参考になりました。

回答No.1

hallo-2007

近似曲線が　線形近似（一次近似線）だとして
Y=a*X+bの式の
a（傾き）は、SLOPE関数
b（接点）は、INTERCEPT関数で求められます。

お礼

ご回答ありがとうございました。