グラフで特定の横軸の色を変更し基準線を引くマクロ

最終更新日：2019-11-02

グラフで特定の横軸の色を変更し基準線を引くマクロ

特定の横軸のみ色を変更する、つまり、基準値や下限・上限等に線を引きたい場合のマクロVBAになります。
手動でやろえとすると結構面倒なので、いざやろうとするとなかなか出来なかったりします。

グラフの元データに線を引きたい数値の系列をシートに追加しておくと、もう少し楽にできますが、
ここでは、シートには手を加えずに基準線を引いています。

手動でやる方法については、以下に掲載しています。

グラフで特定の横軸の色を変更し基準線を引く

特定の横軸のみ色を変更する、つまり、基準値や下限・上限等に線を引く場合の手順です基準値や、下限・上限等に線を引きたい場合になります。結構面倒なので、いざやろうとすると、なかなか出来なかったりしますので、自分自身の覚書を兼ねて掲載しておきます。

今回は、これをマクロVBAで行います。

あらかじめ棒グラフがあるものとして、そこに赤の規準線（ここでは平均値）を引くマクロVBAになります。
上記サンプルでは２系列となっていますが、系列数は問いません、自動で行います。

グラフで特定の横軸の色を変更する（基準線を引く）VBA


  
    
      Sub sample()

　　Dim TitleHas As Boolean

　　Dim TitleFormula As String

　　Dim 基準値 As Double

　　Dim vTemp, v1, v2

　　Dim i As Long

　　
　　'シートの先頭グラフ

　　Dim obj As Chart

　　Set obj = ActiveSheet.ChartObjects(1).Chart

　　With obj

　　　　'基準値決定：平均値を求める

　　　　For i = 1 To .SeriesCollection.Count

　　　　　　'シート名に,カンマは含んでいない前提です

　　　　　　vTemp = Split(.SeriesCollection(i).Formula, ",")

　　　　　　vTemp = vTemp(UBound(vTemp) - 1)

　　　　　　v1 = v1 + WorksheetFunction.Sum(Range(vTemp))

　　　　　　v2 = v2 + WorksheetFunction.Count(Range(vTemp))

　　　　Next

      　　　　If v2 <> 0 Then 基準値 = v1 / v2

　　　　

　　　　'系列追加でタイトルが消える事への対応

　　　　TitleHas = .HasTitle

　　　　If TitleHas Then TitleFormula = .ChartTitle.Formula

　　　　

　　　　'基準線を新規系列として追加

　　　　With .SeriesCollection.NewSeries

　　　　　　.Name = "=""規準""" '特に必要はない

　　　　　　'.Values = "={基準値,基準値}"

　　　　　　.Values = "={" & Join(Array(基準値, 基準値), ",") & "}"

　　　　　　.ChartType = xlLine '折れ線

　　　　　　.AxisGroup = 2 '第2軸

　　　　　　.Format.Line.ForeColor.RGB = vbRed 'RGB(255, 0, 0)

　　　　End With

　　　　

　　　　'系列追加で消えたタイトルの復元

　　　　.HasTitle = TitleHas

　　　　If TitleHas Then .ChartTitle.Formula = TitleFormula

　　　　

　　　　'第2縦軸のScaleを第1縦軸に合わせる

　　　　.Axes(xlValue, 2).MinimumScale = .Axes(xlValue, 1).MinimumScale

　　　　.Axes(xlValue, 2).MaximumScale = .Axes(xlValue, 1).MaximumScale

　　　　.Axes(xlValue, 2).Delete '第2軸を消す

　　　　

　　　　'グラフ内左右余白を消す：基準線を第2横軸に設定

　　　　.HasAxis(xlCategory, 2) = True '第2横軸表示

      　　　　.Axes(xlCategory, 2).AxisBetweenCategories = False '軸位置を目盛

　　　　.Axes(xlCategory, 2).TickLabelPosition = xlNone '目盛ラベルなし

　　　　

　　　　'基準線の凡例を消す

　　　　On Error Resume Next '凡例が無いときの対応

　　　　.Legend.LegendEntries(.SeriesCollection.Count).Delete

　　End With

End Sub

基準線を引くマクロVBAの解説

以下、基準線を引くマクロVBAの中から、要点・注意点のみ解説します。

ActiveSheet.ChartObjects(1)

シートの先頭グラフになります。
複数のグラフがある場合は適宜変更してください。

基準値決定：平均値を求める

グラフの元データから平均値を求めています。
簡易的に,カンマで区切ってデータ範囲を取得していますので、
あまり無いとは思いますが、'シート名に,カンマを含んでいると正しく取得できません。
また、クラフのデータ範囲として固定数値を直接指定しているとエラーとなります。

.SeriesCollection(i).Formulaの値は、
=SERIES(Sheet1!$B$1,Sheet1!$A$2:$A$13,Sheet1!$B$2:$B$13,1)
このような文字列になりますので、カンマ区切りで後ろから2番目がデータ範囲になります。
この文字列(Sheet1!$B$2:$B$13)を取り出して、Rangeの引数に入れてRangeオブジェクトにしています。

あくまで、基準値を求める一つのサンプルになります。
あらかじめ決められた基準値なら、変数（基準値）にその値を入れてください。
シート上で数式により計算していれば、そのセル値を変数（基準値）に入れてください。

基準線を新規系列として追加

.SeriesCollection.NewSeries
これで系列を追加しています。

Valuesには、
.Values = "={基準値,基準値}"
このように設定しますので、この文字列を作成してValuesに入れています。

そして、折れ線に設定し、第2軸に設定しています。
色の指定は、vb定数でもRGB関数でも有効な指定なら何でも構いません。

系列追加でタイトルが消える事への対応と復元

系列を追加すると、タイトルが消えてしまいますので、
系列追加前に情報を退避しておき、系列追加後に戻しています。
タイトルには、数式を設定できますので、Formulaを使っています。

第2縦軸のScaleを第1縦軸に合わせる

基準線を第2縦軸に設定するので、第2縦軸の最大値と最小値を第1縦軸と同じにしています。
そして、第2縦軸の表示は必要ないので消しています。

, 2)や, 1)は、縦軸の数値です。
第1縦軸は、xlPrimary=1
第2縦軸は、xlSecondary=2
上記VBAでは、掲載コードの見やすさを考慮して数値リテラルで指定しています。

グラフ内左右余白を消す：基準線を第2横軸に設定

基準線を横幅いっぱいに広げるために、
・第2横軸に設定
・軸位置を目盛
・目盛ラベルなし

基準線の凡例を消す

凡例に追加した基準線の系列が表示されてしまうので、これを消しています。
もちろん、凡例に表示されていた方が良ければ消さずに残してください。

ほぼ全て自動になっていますので、大抵の場合は修正せずに使えると思います。
手動で一つずつ設定するよりは間違いなく早いはずです。
特に複数グラフで設定したい場合には有効だと思いますので、VBAでできる事だけは覚えておくと良いでしょう。

同じテーマ「マクロVBAサンプル集」の記事

図を確認しながら消していく（Shape）
オートシェイプを他ブックの同じ位置に貼り付ける（Shapes,DrawingObjects）
全シートの画面キャプチャを取得する（keybd_event）
写真の取込方法について（Pictures.Insert,Shapes.AddPicture）
写真をサムネイルに変換して取り込む（Shapes.AddPicture）
円グラフの色設定（Chart,SeriesCollection）
棒グラフ・折れ線グラフのサンプルマクロ
 人口ピラミッドのグラフをマクロで作成
グラフで特定の横軸の色を変更し基準線を引くマクロ
グラフのデータ範囲を自動拡張するマクロ
 画像のトリミング（PictureFormat,Crop）

アクセスランキング・・・ランキング一覧を見る

1.最終行の取得（End,Rows.Count）｜VBA入門
2.RangeとCellsの使い方｜VBA入門
3.変数宣言のDimとデータ型｜VBA入門
4.繰り返し処理（For Next)｜VBA入門
5.セルのコピー&値の貼り付け（PasteSpecial）｜VBA入門
6.マクロって何？VBAって何？｜VBA入門
7.並べ替え（Sort）｜VBA入門
8.Excelショートカットキー一覧｜Excelリファレンス
9.エクセルVBAでのシート指定方法｜VBA技術解説
10.Range以外の指定方法（Cells,Rows,Columns）｜VBA入門

ホーム
マクロVBA応用編
マクロVBAサンプル集
グラフで特定の横軸の色を変更し基準線を引くマクロ

このサイトがお役に立ちましたら「シェア」「Bookmark」をお願いいたします。

記述には細心の注意をしたつもりですが、
間違いやご指摘がありましたら、「お問い合わせ」からお知らせいただけると幸いです。
掲載のVBAコードは動作を保証するものではなく、あくまでVBA学習のサンプルとして掲載しています。
掲載のVBAコードは自己責任でご使用ください。万一データ破損等の損害が発生しても責任は負いません。

VBAサンプル集
グラフで特定の横軸の色を変更し基準線を引くマクロ