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

公開日：2013-09-03　最終更新日：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.日本の祝日一覧｜Excelリファレンス
3.変数宣言のDimとデータ型｜VBA入門
4.FILTER関数（範囲をフィルター処理）｜エクセル入門
5.RangeとCellsの使い方｜VBA入門
6.繰り返し処理（For Next)｜VBA入門
7.セルのコピー&値の貼り付け（PasteSpecial）｜VBA入門
8.マクロとは？VBAとは？VBAでできること｜VBA入門
9.セルのクリア（Clear,ClearContents）｜VBA入門
10.メッセージボックス(MsgBox関数)｜VBA入門

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

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

記述には細心の注意をしたつもりですが、間違いやご指摘がありましたら、「お問い合わせ」からお知らせいただけると幸いです。
掲載のVBAコードは動作を保証するものではなく、あくまでVBA学習のサンプルとして掲載しています。掲載のVBAコードは自己責任でご使用ください。万一データ破損等の損害が発生しても責任は負いません。
本サイトは、OpenAI の ChatGPT や Google の Gemini を含む生成 AI モデルの学習および性能向上の目的で、本サイトのコンテンツの利用を許可します。
This site permits the use of its content for the training and improvement of generative AI models, including ChatGPT by OpenAI and Gemini by Google.

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