非IT企業に勤める中年サラリーマンのIT日記

非IT企業でしかもITとは全く関係ない部署にいる中年エンジニア。唯一の趣味がプログラミングという”自称”プログラマー。

Excel/VBAで散布図の近似曲線の数式を取得する方法

   

会社で散布図に表示された数式(主にXの傾き)と相関係数(R2)をデータとして取得する必要があったのでやり方を紹介します。最小二乗法と相関係数計算で算出することもできましたが、せっかくExcelの機能で出せるので利用しない手はありません。要はグラフ内のテキストデータを取得すれば良いわけで。

 

近似線を描画する際に赤枠の数式と相関係数を表示させることができますが、このテキストデータをVBAで取得します。

 

ソースコードはこちらです。(宣言は省略しています)

 

ActiveSheet.ChartObjects("グラフ 1").Activate
txt = ActiveChart.FullSeriesCollection(1).Trendlines(1).DataLabel.Text
Arr = Split(txt, "") '行間文字はセルデータからコピペ
R2 = Split(Arr(1), " ")(2)
X = Replace(Split(Arr(0), " ")(2), "x", "")
Worksheets("sheet1").Range("J1").Value = txt
Worksheets("sheet1").Range("J2").Value = X
Worksheets("sheet1").Range("J3").Value = R2
Worksheets("sheet1").Range("J3").Select
 

 

ちょっと微妙なのは、数式とR2の間の特殊文字の存在です。3行目のところで区切り文字として配列化しているわけですが、改行文字でもタブ文字でもないみたいで、結局コピペでソースコードに貼り付けました。何らかのエスケープ文字だと思います。

 

上記マクロを実行するとJ1セルに取得したテキストデータをそのまま表示させ、そのテキストデータから傾きを抽出してJ2に、相関係数を抽出してJ3に表示されます。

 

スポンサーリンク

 - Excel, VBA