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

公開日：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）

資料等の作成で、画面キャプチャすることがあると思います。そこで、全シートの画面キャプチャを、新規シートに全て取得するプログラムです。Alt+PrntScrnで、エクセルのウインドウのみキャプチャしています。

写真の取込方法について（Pictures.Insert,Shapes.AddPicture）

・Pictures.Insertメソッド・Shapes.AddPictureメソッド・Shapesに関連する記事

写真をサムネイルに変換して取り込む（Shapes.AddPicture）

・写真をサムネイルに変換して取り込むVBA ・写真をサムネイルに変換して取り込むVBAの解説・Shapesに関連する記事

円グラフの色設定（Chart,SeriesCollection）

円グラフの色を、元の表から設定します。以下は、ウイザードでグラフを作成した状態です。A列に指定した、塗りつぶし色を、グラフに反映させます。たった、これだけです。手作業よりは、はるかに簡単ですし、応用範囲が広いと思います。

棒グラフ・折れ線グラフのサンプルマクロ

グラフはプロパティ・メソッドも多いので、自分の覚書もかねて掲載しました。この元データから、以下のグラフが作成されます。解説は、プログラム内のコメントを参考にして下さい。系列データのデータ数が増えた時に、データ範囲を変更するマクロも掲載しておきます。

人口ピラミッドのグラフをマクロで作成

人口ピラミッドのグラフ作成は、設定項目が多く、かなり面倒です。マクロでサクッと作って、細かい部分を手動で設定できれば楽です。この表から、以下のグラフを作成します。手動で設定すると、かなり多くの手順が必要になります。

グラフで特定の横軸の色を変更し基準線を引くマクロ
グラフのデータ範囲を自動拡張するマクロ

グラフのデータ範囲を自動で拡張・縮小するマクロVBAになります、グラフのデータ範囲を変更する事は度々ありますが、作業自体は大した事はないのですが、やはり面倒ですし、グラフの数が多いと、結構な手間になります。注意以下は、棒グラフ・折れ線グラフの場合になります。

画像のトリミング（PictureFormat,Crop）

エクセルで画像をトリミングするマクロになります、画像の一部を四角に切り取るVBAコードの解説です。図形で切り取る等は、自動記録のコードをほぼそのまま使えるはずですので、ここでは、基本的かつ汎用的な、一部を四角に切り取るVBAコードについて、サンプルコードを掲載して解説します。

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

1.最終行の取得（End,Rows.Count）｜VBA入門
2.セルのコピー&値の貼り付け（PasteSpecial）｜VBA入門
3.変数宣言のDimとデータ型｜VBA入門
4.繰り返し処理（For Next)｜VBA入門
5.RangeとCellsの使い方｜VBA入門
6.ブックを閉じる・保存（Close,Save,SaveAs）｜VBA入門
7.セルのクリア（Clear,ClearContents）｜VBA入門
8.メッセージボックス(MsgBox関数)｜VBA入門
9.条件分岐（Select Case）｜VBA入門
10.マクロとは？VBAとは？VBAでできること｜VBA入門

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

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

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

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