ノン・プログラマー、仕事のためのマクロ・VBA(10)定型グラフは、VBAで自動生成できると超便利!

投稿日 2023年4月10日
著者 root

せるワザ

定期的に更新する資料、たとえば売上や経費管理などの報告書は、グラフで金額増減を説明しますよね。そのグラフ、どうやって作っていますか？

もちろん、毎回最初からグラフ・ウィザードをよび出して、設定しなおしてもよいのですが、作成担当者が異動したら、まったく同じグラフの報告書を作りつづけられますか?

異動前には、ちゃんとExcelグラフ・ウィザード操作方法を、引継ぎ書にまとめてプリントアウトして、手渡して...、って、それ、昭和の働き方ですか?

毎回同じものを再生産する作業は、マクロ・VBAがもっとも得意とする機能です。

グラフ作成を自動化するメソッドには、それほどむずかしい記述はありませんので、すこし時間をさいて、記述方法をおぼえてみましょう。

1つのマクロを実行するだけで、毎回おなじ色調、同一サイズのグラフが再生産されるので、ついでに、ほかの報告書作成作業も、どんどん自動化してみたくなりますよ。

Officeテンプレート(ライフマネープラン)の2軸グラフを、ウィザードを使って、自分でも作ってみましょう

今回も、サンプルとして使用するのはコレ↓

マイクロソフト社がExcel形式で無料公開しているライフマネープランシート(1年間)です。

Microsoft officeトップ < officeテンプレート< ライフマネープランシート

このシートは家計管理のための資料ですが、「支出」を「経費」に、「貯蓄残高」を「月間利益」におきかえたら、営業会議などでもよく見かけるグラフと表ですよね。

サイズや色づかい、メモリ単位やデータラベル有無などを、上記テンプレートのグラフそっくりにデザインすることも可能ですが、ここではExcelウィザードが自動生成するサイズ、色調で、同じ数値推移を表現するグラフを作成します。

(1)まず、使用する元データは1年間12ヶ月分の支出と貯蓄残高の実績数値なので、テンプレートのExcel表から、必要な2要素だけを事前に抽出します(※［グラフのデータ範囲］で、適切に範囲選択ができるならば、別表に抽出する必要はありません)。

その後、グラフにしたいセル範囲を指定→［挿入］→［おすすめグラフ］をクリックし、各種デザインの中から［集合縦棒］を選んで［OK］をクリックします。

この1操作だけでは、折線と縦棒の2軸グラフとはなりません。しかも、金額メモリが「貯蓄残高」最高額に合わされた1軸なので、支出グラフがとても小さくみえます。

(2)「支出」と「貯蓄残高」単位がことなる場合、2要素を別軸のグラフとする必要があります。

「貯蓄残高」グラフを右クリックして［データ系列の書式設定］メニューから［第2軸］をクリックし、［OK］します。

「支出」軸が左側、「貯蓄残高」軸が右側に切り分けられた2軸グラフにかわりました。

でもまだ、2つの要素とも棒グラフなので、月別推移がよくわからないグラフですね。

(3)最後に、再度「貯蓄残高」グラフを右クリックして、［グラフの種類の変更］→「貯蓄残高」＝折れ線を選択して、［OK］をクリックします。

3つのステップをへて、ようやくOfficeテンプレートと同じ内容の2軸グラフが完成しました。

グラフ・デザインを決めるVBAの記述方法を覚えておけば、プログラムが2軸グラフを1発で作成してくれます

おためしで、数回だけ2軸グラフをつくるのなら、ウィザード3ステップを、1回ずつやりなおしてもよいかもしれません。

しかし、「毎月の営業会議資料作成」などくりかえすのであれば、グラフ作成は自動化したい作業ですね。

「マクロの記録」で、ウィザード動作を保存することもできますが、それほどむずかしい記述方法ではありませんので、一度VBAにグラフを作らせるプログラムを書いてみることをおすすめします。

今回作成したサンプル・コードはこちらです。

VBAメソッドで、Excelに指示していることは、ウィザード3ステップとかわりません。

(1)集合縦棒をつくります。

    Range("A1:M3").Select
    ActiveSheet.Shapes.AddChart2.Select
    ActiveChart.ChartType = xlColumnClustered

ChartTypeが「グラフの種類」で、今回は「xlColumnClustered」=集合縦棒を選択しましたが、ほかに代表的なグラフの種類としては、「xlPie」=円、「xlXYScatter」=散布図などを指定できます。

(2)元データの「貯蓄残高」をあらわすグラフを「第2軸」とします。さらに、(3)この「貯蓄残高」グラフの種類を、縦棒から折れ線に変更します。

       With Worksheets("Sheet1").ChartObjects(1).Chart
        .HasAxis(xlValue, xlSecondary) = True
        With .SeriesCollection("貯蓄残高")
        .ChartType = xlLine
        .AxisGroup = xlSecondary
        End With
        End With

グラフの軸を設定するには、HasAxisプロパティを使います。カッコ()のなかに2つのインデックスを指定しますが、今回は「貯蓄残高」が”HasAxis(数値軸, 第2軸)” だという指定です。

さらにこの第2軸プロパティを

.ChartType = xlLine

にて、縦棒から折れ線に変更しているのです。

そのほか、グラフタイトルや2軸それぞれの軸ラベルを自動挿入しています。

さらに、指定やお好みのグラフ配色、グラフ・サイズ、その他の決まりごとを追記しておけば、何度でも同じサイズ、同じ配色、同じ内容で、元データの数値だけが更新されたグラフが再生産できるようになります。毎回、ウィザードから手づくりするより、作業時間も短縮されますね。