Excel VBA 散布図グラフ(Chart)に近似曲線を追加する

今回はグラフ(散布図)に近似値曲線を追加する方法です。

もちろん、グラフのタイプを変更する事で散布図以外にも追加する事が出来ます。

近似曲線の追加方法と、追加した線の書式設定なども併せてご説明します。

その他のグラフ作成方法や、グラフの詳細設定は次の記事をご覧ください。

1.近似曲線の追加方法
2.グラフに近似曲線を追加するサンプルコード

1.近似曲線の追加方法

グラフに近似曲線の追加するには「Trendlines」オブジェクトに「Add」を使用して追加します。

記述方法は次のようになります。

SeriesCollection(1).Trendlines.Add

追加された近似曲線は自動的に番号が振られます。

上記のコードでは、系列(1)の近似曲線(1)が追加されますが、Addを繰り返すと近似曲線(2)、(3)と増えていきます。

追加した近似曲線に対して、それぞれ詳細設定をしていきます。

パラメーター一覧

パラメーター	省略	説明
Type	省略可能	近似曲線の種類を指定します。
Order	省略可能	xlpolynomialの場合に指定可能で、近似曲線の次数を指定します。使用できる値の範囲は、2 ～ 6 の整数です。
Period	省略可能	xlmovingavgの場合に指定可能で、近似曲線の期間を指定します。 1より大きい整数である必要があります。近似曲線を追加するデータ系列のデータポイントの数未満である必要があります。
Forward	省略可能	近似曲線を前方へ拡張するときの区間数 (散布図の場合は単位数) を指定します。
Backward	省略可能	近似曲線を後方へ拡張するときの区間数 (散布図の場合は単位数) を指定します。
Intercept	省略可能	近似曲線の切片を指定します。この引数を省略すると、切片は回帰計算により自動的に設定されます。
DisplayEquation	省略可能	Trueを指定すると、近似曲線の式をグラフに表示します。 (R-2乗値と同じデータラベル内に表示します)。既定値はFalseです。
DisplayRSquared	省略可能	Trueを指定すると、近似曲線のR-2乗値をグラフに表示します。 (式と同じデータラベル内に表示します) 既定値はFalseです。
Name	省略可能	近似曲線の名前を示す文字列を指定します。この引数を省略すると、名前が自動的に作成されます。

Type一覧

各タイプの説明だけでは正直理解が出来ないので、Excelのプロパティで表示されるタイプ名を併せて記載しました。

タイプ	Excel上の名前	説明
xlexponential	指数近似	数式を使用して、複数のポイントを通過する最小二乗値を計算します例y=ab^x
xllinear	線形近似	一次方程式y=mx+bを使用して、複数のポイントを通過する最小二乗値を計算します
xllogarithmic	対数近似	数式y=clnxを使用して、複数のポイントを通過する最小二乗値をを計算します
xlmovingavg	移動平均	連続データの一部から計算した平均のシーケンスを使用します。ポイントの数は、連続データ全体のポイント数から、その区間に指定したポイント数を引いた値です
xlPolynomial	多項式近似	数式を使用して、複数のポイントを通過する最小二乗値をを計算します例y=ax^6+bx^5+cx^4+dx^3+ex^2+fx+g
xlpower	累乗近似	数式を使用して、複数のポイントを通過する最小二乗値を計算します例y=ax^b

2.グラフに近似曲線を追加するサンプルコード

グラフに近似曲線を追加するサンプルコードです。

今回使用するサンプルデータとグラフです。

A列に日付、B列に売上のデータがあり、散布図のグラフを作成したデータです。

こちらのグラフに近似曲線を追加します。

移動平均の近似曲線を追加する

期間を5に設定した移動平均の近似曲線です。

Sub Sample1()

Dim ChartObj    As Object

Set ChartObj = ActiveSheet.ChartObjects(1)

With ChartObj.Chart.SeriesCollection(1) '系列1を指定

    .Select
    .Trendlines.Add '系列1の近似曲線を追加
    .Trendlines(1).Select '追加された近似曲線1を選択
        
    
    Selection.Type = xlMovingAvg 'タイプの移動平均を選択
    Selection.Period = 5 '近似曲線の期間を指定

End With

End Sub

線形の近似曲線を追加する

線形の近似曲線を追加するサンプルコードですが、上記の移動平均の「xlMovingAvg」を「xllinear」に変更して、「Period=5」を削除しただけになります。

Sub Sample2()

Dim ChartObj    As Object

Set ChartObj = ActiveSheet.ChartObjects(1)

With ChartObj.Chart.SeriesCollection(1) '系列1を指定

    .Select
    .Trendlines.Add '系列1の近似曲線を追加
    .Trendlines(1).Select '追加された近似曲線1を選択
        
    
    Selection.Type = xllinear 'タイプの線形近似を選択

End With

End Sub

近似曲線の色、線の太さを変更するサンプルコード

追加した近似曲線の色や線の太さを変更するサンプルコードになります。

Sub Sample16()

Dim ChartObj    As Object

Set ChartObj = ActiveSheet.ChartObjects(1)

With ChartObj.Chart.SeriesCollection(1) '系列1を指定

    .Select
    .Trendlines.Add '系列1の近似曲線を追加
    .Trendlines(1).Select '追加された近似曲線1を選択
        
    
    Selection.Type = xlMovingAvg 'タイプの移動平均を選択
    Selection.Period = 5 '近似曲線の期間を指定
    
    Selection.Format.Line.Visible = msoTrue '系列を表示する
    Selection.Format.Line.ForeColor.RGB = RGB(255, 0, 0) '線の色を指定する
    Selection.Format.Line.Transparency = 0 '塗りつぶしの透明度
    '点線の書式を指定します
    '(msoLineSolid=実線、msoLineSquareDot=点線 (角)、msoLineRoundDot=点線 (丸)、
    'msoLineDash=破線、msoLineDashDot=一点鎖線)
    Selection.Format.Line.DashStyle = msoLineSysDot
    Selection.Format.Line.Weight = 1.5 '線の太さ

End With

End Sub