グラフ自動生成

を行うマクロを組みました。

完成図としてはこんな感じで、棒グラフの平均が赤い線として出ます。

コードはこんな感じです。

Sub test01()

 

Dim a(4) As Single
Dim a_ave As Single
Dim tmp As String
Dim ele_name As Range
Dim ele As Range
Dim ele_ave As Range
Dim chartObj As ChartObject

 

'データ
a(0) = 1
a(1) = 2
a(2) = 3
a(3) = 4
a(4) = 5

 

'シートを追加
Sheets.Add After:=Sheet1

 

'シート名変更（TMP）
ActiveSheet.Name = "TMP"

 

'TMPシートをアクティブにする
Sheets("TMP").Activate

 

'各要素名をTMPシートに記載
Range("A1").Value = "ele_1"
Range("A2").Value = "ele_2"
Range("A3").Value = "ele_3"
Range("A4").Value = "ele_4"
Range("A5").Value = "ele_5"
Set ele_name = Range("A1", "A5")

 

'各要素をTMPシートに記載
Range("B1").Value = a(0)
Range("B2").Value = a(1)
Range("B3").Value = a(2)
Range("B4").Value = a(3)
Range("B5").Value = a(4)
Set ele = Range("B1", "B5")

 

a_ave = WorksheetFunction.Sum(ele)
a_ave = a_ave / (UBound(a) + 1)

 

'平均値をTMPシートに記載
Range("C1").Value = a_ave
Range("C2").Value = a_ave
Range("C3").Value = a_ave
Range("C4").Value = a_ave
Range("C5").Value = a_ave
Set ele_ave = Range("C1", "C5")

 

Sheets("Sheet1").Activate
Cells(1, 1).Select

 

'グラフを作成
With ActiveSheet.ChartObjects.Add(10, 10, 500, 500).Chart
        .ChartType = xlColumnClustered
        With .SeriesCollection.NewSeries
            .Values = ele
            .Name = "ele"
            .XValues = ele_name

        End With
   
    '平均の系列を追加
    .SeriesCollection.Add ele_ave
           
End With
   
'追加した平均の系列の書式を編集
Set chartObj = ActiveSheet.ChartObjects(1)
chartObj.Activate
ActiveChart.FullSeriesCollection(2).Select
ActiveChart.FullSeriesCollection(2).ChartType = xlLine
ActiveChart.FullSeriesCollection(2).Name = "ele_ave"

 

'値軸の最大値も固定
ActiveChart.Axes(xlValue).Select
ActiveChart.Axes(xlValue).MaximumScale = 10

 

'平均の系列用の第二軸追加
ActiveChart.FullSeriesCollection(2).Select
ActiveChart.FullSeriesCollection(2).AxisGroup = 2

 

'第二縦軸の最大値も固定
ActiveChart.Axes(xlValue, xlSecondary).Select
ActiveChart.Axes(xlValue, xlSecondary).MaximumScale = 10

 

'第二横軸を生成
ActiveChart.SetElement (msoElementSecondaryCategoryAxisShow)

 

'第二横軸を目盛り、ラベルなしに設定
ActiveChart.Axes(xlCategory, xlSecondary).Select
Selection.MajorTickMark = xlNone
Selection.TickLabelPosition = xlNone

 

'TMPシート非表示
Sheets("TMP").Select
ActiveWindow.SelectedSheets.Visible = False

End Sub

 

以下解説です。

 

■グラフへの系列追加

まずポイントとしては、SeriesCollection.Addで平均の系列をグラフに追加する部分です。

 

>    '平均の系列を追加
>    .SeriesCollection.Add ele_ave

 

当初これを変数でやろうとして、配列や二次元配列を渡してみたり、仮想的なRangeオブジェクト（セルのA1~A5とかという範囲を持たないもの）を生成しようとしたりしたんですが、うまくいかず、結局TMPシートを使用して後で非表示にするという方法に落ち着きました。

 

 

■グラフ、グラフ要素の選択

次に、

シート上にあるグラフを選択する部分です。

 

>'追加した平均の系列の書式を編集
>Set chartObj = ActiveSheet.ChartObjects(1)

 

マクロの記録で同様のことを確認するとグラフ名（この場合だとグラフ１とかグラフ２とかに自動でなってます）でグラフを取得していますが、これは使用できませんでした。理由はよくわかりません。グラフの名前の付与が自動なので、マクロを実行しようとした段階ではまだ名前が付与されていなかったとかが考えられますが…

 

■第二横軸について

次に、

第二横軸を生成する部分です。

 

'第二横軸を生成
ActiveChart.SetElement (msoElementSecondaryCategoryAxisShow)

 

この第二横軸というのは、平均系列をAxisGroup = 2に設定した時点でできていると思っていたのですが（現に第二「縦軸」に関してはそうです）、実はデフォルトでは未作成（非表示？）になっていて、この生成の手順を抜かして第二横軸の設定をいじる以下の記載にいこうとすると、下記の処理で落ちます。

 

ActiveChart.Axes(xlCategory, xlSecondary).Select

 

要するに第二横軸が選択できないということです。

 

それ以外の部分についてはおおむねマクロの記載を確認することで知れると思います。

 

■参考文献

infoyandssblog.blogspot.jp

平均値線のひき方は上記サイト様を参考にさせていただきました。