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

ExcelマクロVBAの実用サンプル、エクセルVBA集と解説
最終更新日:2019-11-02

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


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


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

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

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

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


マクロ VBA グラフ 基準線

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

グラフで特定の横軸の色を変更する(基準線を引く)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を使っています。

マクロ VBA グラフ 基準線

第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)


新着記事NEW ・・・新着記事一覧を見る

VBAでのCSVの扱い方まとめ|VBAサンプル集(11月9日)
VBAにおける変数のメモリアドレスについて|VBA技術解説(11月8日)
空文字列の扱い方と処理速度について(""とvbNullString)|VBA技術解説(1月7日)
Errオブジェクトとユーザー定義エラー|VBA入門(11月5日)
シングルクォートの削除とコピー(PrefixCharacter)|VBA技術解説(11月4日)
ユーザー定義型の制限とクラスとの使い分け|VBA技術解説(11月3日)
クリップボードに2次元配列を作成してシートに貼り付ける|VBA技術解説(11月1日)
VBAクラスを使ったイベント作成(Event,RaiseEvent,WithEvents)|VBA技術解説(10月31日)
VBAクラスのAttributeについて(既定メンバーとFor Each)|VBA技術解説(10月19日)
VBAの用語について:ステートメントとは|VBA技術解説(10月16日)


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

1.最終行の取得(End,Rows.Count)|VBA入門
2.セルのコピー&値の貼り付け(PasteSpecial)|VBA入門
3.RangeとCellsの使い方|ExcelマクロVBA入門
4.Range以外の指定方法(Cells,Rows,Columns)|VBA入門
5.変数宣言のDimとデータ型|ExcelマクロVBA入門
6.繰り返し処理(For Next)|ExcelマクロVBA入門
7.マクロって何?VBAって何?|ExcelマクロVBA入門
8.ひらがな⇔カタカナの変換|エクセル基本操作
9.空白セルを正しく判定する方法(IsEmpty,IsError,HasFormula)|VBA技術解説
10.セルに文字を入れるとは(Range,Value)|VBA入門



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

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


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




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