Excelグラフに「平均値を表す水平線」を追加することで、データの傾向を視覚的に比較しやすくなります。VBAを使えば、元データの平均値を動的に算出し、自動で平均ラインを描画する処理を実装できます。
本記事では、既存のグラフに平均値を表す系列をVBAで追加する方法をご紹介いたします。
目次
サンプルコード:平均値ラインを追加するVBAマクロ
Sub AddAverageLineToChart()
Dim targetChart As Chart
Dim baseSeries As Series
Dim averageValue As Double
Dim dataCount As Long
Dim avgArray() As Variant
Dim i As Long
' グラフを取得(先頭のグラフ)
Set targetChart = ActiveSheet.ChartObjects(1).Chart
' もとになる系列(1番目)を取得
Set baseSeries = targetChart.SeriesCollection(1)
' 平均値を算出
averageValue = WorksheetFunction.Average(baseSeries.Values)
' 同じ長さの配列に平均値を代入
dataCount = UBound(baseSeries.Values)
ReDim avgArray(1 To dataCount)
For i = 1 To dataCount
avgArray(i) = averageValue
Next i
' 平均値の系列を追加
With targetChart.SeriesCollection.NewSeries
.Values = avgArray
.Name = "Average Line"
.MarkerStyle = xlNone
.Border.Color = RGB(255, 0, 0) ' 赤いライン
.Format.Line.DashStyle = msoLineDash
End With
End Sub
処理のポイント解説
1. SeriesCollection(1)
で基準データを取得
Set baseSeries = targetChart.SeriesCollection(1)
元データの系列から値の配列と件数を取得します。
2. 平均値を求める
averageValue = WorksheetFunction.Average(baseSeries.Values)
Excelの AVERAGE
関数と同じ結果をVBAでも算出できます。
3. 同じ長さの平均値配列を生成
ReDim avgArray(1 To dataCount)
For i = 1 To dataCount
avgArray(i) = averageValue
Next i
このようにすることで、元データと同じ長さの水平線を描画できます。
4. 新しい系列として追加
With targetChart.SeriesCollection.NewSeries
.Values = avgArray
.Name = "Average Line"
End With
既存のグラフに、平均値のラインを重ねて表示します。
表示カスタマイズのヒント
.Border.Color = RGB(255, 0, 0)
:線の色を赤に変更.Format.Line.DashStyle = msoLineDash
:点線スタイルに変更.MarkerStyle = xlNone
:点を非表示に
目的や見た目に応じて、調整が可能です。
よくある利用シーン
- 販売実績グラフに平均売上ラインを追加したい
- 実測値と目標値の差を見たい
- 異常値を視覚的に見つけたい
まとめ
Excel VBAを活用することで、任意のグラフに平均値のラインを自動で追加することが可能です。目視で確認するよりも、数値の傾向や異常の発見がしやすくなり、データ分析の効率が格段に向上します。
レポートやダッシュボードの精度を高めたい方は、ぜひ本処理の導入をご検討ください。