【応用編】ExcelVBAでピボットテーブルと連動したグラフを自動で作成する

お知らせ記事には広告が含まれておりますがExcelのスキルUPに繋がる様コンテンツ自体は手を抜かずに作成しております

VBAでピボットテーブルと連動したグラフを作成する

本記事はピボットテーブルの作り方を紹介した以下記事から続く内容となっております。(本記事単体でも理解できます)

リンク先の記事を読んだ後に本記事をご覧いただくとピボットテーブルからグラフという一連の作業が理解できます。

基本編【基本編】VBA×ピボットテーブルの作り方|レポートにフィールドを追加する


ピボットテーブルからピボットグラフを用意してみます。

以下リストの様な3つの課題を持っている人には特におすすめの記事になっています。

  • ピボットテーブルからグラフを自動で作りたいが用意する方法が分からない
  • グラフを用意するだけではなくて見た目をカスタマイズしたい
  • エラー対策も考えておきたい(ピボットテーブルが無い時はグラフを作らない等)
EnjoyExcel
EnjoyExcel

余談ですが通常のテーブルから直接ピボットグラフを作る事も可能なんですよ。

今回は順を追って説明したいのでピボットテーブルからピボットグラフを用意する事にします。

前回の記事では基本編としてセルに配置された文字列をもとにしたピボットテーブルの生成方法を見ていただきました。

今回の記事では応用編としてピボットテーブルからピボットグラフを用意する一連の流れを見ていただきます。

冒頭で提示した課題について3つのポイントをもとに解説していきます。

  1. ピボットテーブルの有無を確認したうえでグラフを用意する
  2. ピボットグラフを用意する
  3. グラフのタイトルや軸ラベルをカスタマイズする

関連書籍

VBAを使ってピボットグラフまで紹介している書籍は数が少ないです。

こちらの書籍ではピボットテーブルやグラフについて分かりやすく説明されていましたので紹介します。

事例形式でマクロが用意されているので同じような事をやりたいと思っている方にはピッタリの書籍です。

表紙の質感が変わっているので是非書籍で購入をおすすめしたい一品です。

おすすめ書籍

本が苦手な方電子書籍はこちらになります。

ピボットグラフのアイキャッチ画像

ピボットグラフとは

個人的にはピボットグラフについては以下の様に考えています。

Q
ピボットグラフとは?
A

対象のピボットテーブルにおける集計結果をフィルタ等を駆使して複数の角度から表現できるグラフ

続いてMicrosoftが用意した「ピボットグラフ」に関するドキュメントを引用しつつ紹介します。

ピボットグラフは、関連するピボットテーブルのデータをグラフィカルに表現します。

ピボットグラフ もインタラクティブです。

ピボットグラフを作成すると、ピボットグラフ [フィルター] ウィンドウが表示されます。

この [フィルター] ウィンドウを使って、ピボットグラフの基礎となるデータの並べ替えとフィルタリングを行うことができます。

関連するピボットテーブルのレイアウトやデータに加えた変更は、即座にピボットグラフのレイアウトやデータに反映されます

その逆も同様です。

ピボットグラフには、標準のグラフと同じように、データ系列、カテゴリ、データ マーカー、軸が表示されます。

グラフの種類や、タイトル、凡例の位置、データ ラベル、グラフの位置などの他のオプションを変更することもできます。

上のピボットテーブルの例に基づくピボットグラフを次に示します。

Microsoft:ピボットテーブルおよびピボットグラフの概要

ピボットテーブルやグラフを見た事がある人はイメージできるかもしれませんが少し難しいですよね。

理解する為のポイントとしては「標準グラフ」との違いを理解するのが良いと感じています。

「ピボットグラフ」と「標準グラフ」との違いを理解する

引用内に用意されたリンクのドキュメントではピボットグラフではないグラフを「標準グラフ」と呼んでいます。

その流れで「ピボットグラフ」と「標準グラフ」の違いを説明しています。大きくわけて4つのポイントがあります。

  • 行/列の方向
  • グラフの種類 
  • ソース データ 
  • 書式設定

通常のグラフとの差を認識する事でピボットグラフへの理解を進めていただく事をおすすめします。

代わって動画で認識したい方はこちらです。リンク先の記事ではピボットグラフの作り方を動画で紹介しています。

応用編:VBAでピボットグラフを作成する

前回記事(基礎編)からの流れで今回は応用編としてVBAでピボットグラフを用意する方法を解説していきます。

事例

以下のような事例を用意しました。

A列からE列にピボットテーブルの元になるデータを配置しています。

A~E列のデータを使いG列からS列に配置されたピボットテーブルを用意しています。ここまでが前回記事の内容です。

本記事ではG~S列に用意されたピボットテーブルに少しかかる様な配置でピボットグラフを用意しました。

ピボットテーブルの列ラベル「海外」、「国内」の折り畳みボタンが見える様に配置しています。

今回は「海外」、「国内」の折り畳みボタンをグラフで表現出来なかったので手動で触れるようにした次第です。

事例の紹介

作業の始点と終点

G列からS列の情報が用意されているところから作業をスタートします。ここが作業の始点です。

画像と同じ配置でピボットグラフを用意するところまでをVBAで用意していきます。ここが作業の終点です。

コード

Public Sub グラフ()

Dim ws As Worksheet
Set ws = ActiveSheet

If 判定(ws) = True Then
    ws.PivotTables("表").PivotFields("内外区分").ShowDetail = True
Else
    MsgBox "該当のピボットテーブルが無いです", vbInformation, "お知らせ"
    Exit Sub
End If

'グラフを用意する
With ws
    .Range("A1").Show
    .Range("G5").Select
    '積み上げ縦棒グラフを指定しつつグラフのスタイルや大きさ、位置も指定する
    .Shapes.AddChart2(298, xlColumnStacked, 230, 150, 630, 354.375).Select
End With

'Chatオブジェクトを変数ctに格納する
Dim ct As Chart
Set ct = ActiveChart

With ct
    .Parent.Name = "グラフ" 'グラフに名前を付ける
    .HasTitle = True  'タイトルの枠を出す
    .ChartTitle.Text = "メーカー別_売上" 'タイトルを付ける
    
    '縦軸のラベルを用意
    With .Axes(xlValue, 1)
        .HasTitle = True
        .AxisTitle.Text = "台"
    End With
    
    '縦軸のラベルを縦読み(垂直)にする
    .Axes(xlValue).AxisTitle.Orientation = xlVertical
        
    '横軸のラベルを用意
    With .Axes(xlCategory, 1)
        .HasTitle = True
        .AxisTitle.Text = "年/月"
    End With
    
    'ピボが全展開された状態でフォントと文字の色を決める(黒と12)
    With .ChartArea.Format.TextFrame2.TextRange.Font
        .Fill.ForeColor.RGB = RGB(0, 0, 0)
        .Size = 12
    End With
    
    '国内、海外それぞれの要素が1つ以上存在する際はグラフを折りたたむ
    With ws.PivotTables("表").PivotFields("内外区分")
        If WorksheetFunction.CountIf(ws.Range("C:C"), "国内") > 1 Then
            .PivotItems("国内").ShowDetail = False
        End If
        If WorksheetFunction.CountIf(ws.Range("C:C"), "海外") > 1 Then
            .PivotItems("海外").ShowDetail = False
        End If
    End With
    
    'グラフの要素を折りたたむとグラフの数値が無くなるので入れ直す
    .ChartStyle = 298
    
    '最終的な外観
    .ChartStyle = 305
    
End With
'「海外」、「国内」の折り畳みを開く
ws.PivotTables("表").PivotFields("内外区分").ShowDetail = True

End Sub
'*******************************************************
'ピボットテーブル用の有無を確認する為のFunctionプロシージャ
Private Function 判定(ByVal s As Worksheet)

Dim pvt_table As PivotTable
判定 = False

For Each pvt_table In s.PivotTables
    If pvt_table.Name = "表" Then
        判定 = True
    End If
Next

End Function
'*******************************************************

解説

冒頭で提示した様に3つのポイントをもとに解説していきます。

  1. ピボットテーブルの有無を確認したうえでグラフを用意する
  2. ピボットグラフを用意する
  3. グラフのタイトルや軸ラベルをカスタマイズする

1_ピボットテーブルの有無を確認したうえでグラフを用意する

一般的にコードを書く際は目的のオブジェクトが用意されている事を前提で書いています。

という事はワークシート上にピボットテーブルが無い時にプロシージャを実行するとエラーになってしまいます。

よってエラーを回避する仕組みをプロシージャに盛り込みます。(これはエラー対策です)

具体的にどうするのかと申しますと「ピボットテーブルの存在確認をしてからピボットグラフを作る」事にします。

ピボットテーブルには「表」という名前を付けています。(前回記事参照ください)

「表」がワークシート内に存在した時は後のプロシージャを実行し存在しない時はプロシージャを抜けるという仕様です。

Functionプロシージャを使用します

さらに具体的にどうするのかを説明していきます。Functionプロシージャにピボットテーブルの存在確認を任せます。

メインのプロシージャである「グラフ」からプロシージャ「判定」に処理を任せます。

ピボットテーブル「表」が存在したら「True」、存在しない時は「False」を返してもらう様にコードを準備しました。

「True」が返ってきたら処理を止めずその後の処理をこなし「False」であれば「Exit Sub」でプロシージャを抜けます。

Functionプロシージャ
ワンポイント

Functionプロシージャは大きく分けるとサブルーチンとも言えます。

サブルーチンとコード内で使用しているFor Each ~ Nextステートメントについては以下リンク記事をご確認ください。

サブルーチンについて【マトリクスで理解する】プロシージャを分割したマクロを用意する方法

For Each ~ NextステートメントVBA|最後のシートまで処理を繰り返す|変数をうまく使いこなそう

EnjoyExcel
EnjoyExcel

Functionプロシージャを使う事が難しい際は「プロシージャを分けない」という対策をとりましょう。

少々コードが増えますがメインプロシージャ内にコードを書く様にしてください。

2_ピボットグラフを用意する

本記事でキーになるコードです。ShapesオブジェクトのAddChart2メソッドでグラフを用意します。

オブジェクトブラウザーの画像を用意してみました。

AddChart2メソッド
WorksheetオブジェクトのShapesプロパティからShapesオブジェクトを取得しAddChart2メソッドにつなげます。
構文

構文とパラメータ(引数)の引用です。パラメータは7つです。

必須のものは無いので指定しなくてもグラフは作成されます。その時は「集合棒線グラフ」が生成される事になります。

AddChart2メソッド

AddChart2 (Style、 XlChartType、 Left、 Top、 Width、 Height、 NewLayout)

名前必須 / オプションデータ型説明
Style省略可能バリアント型グラフのスタイルです。 XlChartType で指定されたグラフの種類の既定のスタイルを取得するには、”-1″ を使用します。
XlChartType省略可能バリアント型グラフの種類です。
Left省略可能バリアント型アンカーを基準としたグラフの左端の 位置をポイント単位で指定します。
Top省略可能バリアント型アンカーを基準にして、グラフの上端の位置をポイント単位で示します。
Widthオプションバリアント型グラフのポイント単位の幅です。
Heightオプションバリアント型グラフのポイント単位の高さです。
NewLayout省略可能バリアント型NewLayout が True の場合、チャートは新しい動的書式設定規則を使用して挿入されます (タイトルはオン、複数の系列がある場合は凡例がオン)。
私個人の使用感から申し上げますと「NewLayout」以外は指定する事をおすすめします。
Microsoft:Shapes.AddChart2 メソッド (Excel)

グラフの大きさや配置については画面の大きさなど不確定要素がありますので調整をお願いします。

XlChartTypeは引用内のリンクで確認できます。Styleについては以下ワンポイントをご覧ください。

ワンポイント

グラフのStyleについては公式のドキュメントを見つけられませんでした。よって私が使っている方法を紹介します。

ピボットグラフを選択するとExcelの上部に「デザイン」タブが表示される様になります。

タブ選択後に「グラフスタイル」という場所を見てください。

シート上のグラフが複数のデザインにカスタマイズされた状態を見る事ができますね。

このグラフの中から自身が気に入ったものを選択していただくとワークシート上のグラフがカスタマイズされます。

この一連の作業の内容を「マクロの記録」で取得します。そうすると選択したStyleの番号を取得する事ができます。

その番号をAddChart2メソッドのパラメータにする事で最初から指定のデザインでグラフを作成する事ができます。

今回私は298番と305番を使っています。Styleは掛け合わせで使えますのでご自身で好きなスタイルを探してみて下さい。

グラフのStyleについて
EnjoyExcel
EnjoyExcel

マクロの記録は分からない要素を調べる目的で使う際は非常に便利な機能です。

便利だからと言ってコーディング全般をマクロの記録に頼る事は止めてくださいね。

3_グラフのタイトルや軸ラベルをカスタマイズする

グラフの要素をカスタマイズしています。それぞれはそんなに難しい事はしてないのですが8項目あり数が多いです。

よってタイムラインを使って説明します。

各要素をカスタマイズする
  • 1
    グラフに名前を付ける

    プロシージャ内でグラフに名前を付けた方が取り回しが楽になります。

    よってグラフに名前を付けます。「グラフ」という名前です。

  • 2
    タイトルを用意する

    タイトルを表示させる枠を用意してからラベルにタイトルを入力します。

  • 3
    縦軸のラベルを用意する

    軸ラベルを表示させる枠を用意してからラベルに文字を入力します。

    その後縦書きの様式に変化させる事で見易さを向上させています。

  • 4
    横軸のラベルを用意する

    軸ラベルを表示させる枠を用意してからラベルに文字を入力します。

  • 5
    グラフ全体の文字色と大きさを決める

    グラフ内の文字を「黒」に統一します。

    続いて文字の大きさは「12」に統一します。

  • 6
    グラフを折りたたんだ状態でもデータラベルを表示させる

    グラフを折りたたんだ状態でも数値などのデータラベルが表示させる様指示をします。

  • 7
    最終的に外観を整える

    グラフのスタイルを再度指示する(整える)事でアウトプット時の外観を決めます。

  • 8
    需要に応じてグラフのアウトプット時の見え方を決める

    各要素のフィルタの開閉などを指示してアウトプット時のグラフの見え方を決めます。

ワンポイント

6番の「グラフを折りたたんだ状態でもデータラベルを表示させる」について少し加筆します。

内外区分の列に「国内」、「海外」の文字列がある際はグラフを畳んでいます。

理由はグラフがたたまれた状態のデータラベルには文字の表示が反映されてないので表示させる必要がある為です。

一連の作業の中で条件分岐を使っています。条件分岐についてはこちらの記事をご覧ください。

条件分岐とMsgBoxVBA-11マクロ内でMsgBoxとIFステートメントを組み合わせる

EnjoyExcel
EnjoyExcel

条件分岐はFunctionプロシージャの中でも使われています。

理解していただく事で条件によって処理を分けられるようになりますよ。

アウトプット

ピボットテーブルにかかるようにピボットグラフを配置しました。

本記事と前回記事は便宜上同じシートに元データ、ピボットテーブル、ピボットグラフを配置しています。

本来はそれぞれの要素は別シートで展開する事をおすすめします。

※状況次第ではピボットテーブルとピボットグラフは同じシートの方が使いやすい事もあります

ご自身でアウトプットを用意しながら使いやすい配置を探してみてください。

アウトプットはこんな感じです

グラフ内に表示されたフィルターを開閉する事でグラフの表示方法を変えることができます。(詳細は別途記事で用意)

加えてピボットテーブルの列方向にある「海外」、「国内」のフィルターを開閉してもグラフを変化させることができます。

ピボットグラフにもデメリットはある

ピボットテーブルを触らなくても「海外」、「国内」のフィルターを開閉させるには別途ボタンを用意する必要があります。

他にもフィルターをかけるとバーの配色が変わってしまう等いくつか良くない点もありますがリカバリー可能です。

これらはより応用を効かせたコードが必要になります。需要次第で別途記事を用意する予定です。

まとめ

ピボットテーブルからピボットグラフを作る工程をVBAで対応しました。

前回記事のピボットテーブルをVBAで用意するところから見ていただくと一連の流れを理解する事ができます。

ピボットテーブル&グラフはオブジェクトとプロパティ、メソッドの関係が分かっていないと理解するのは難しいです。

出来るだけコードの構成や意味を考えながらコードを理解する事に心がけてください。

「考えても分からない方」はこちらの記事をご覧ください。オブジェクトとプロパティの関係性について書いてあります。

EnjoyExcel

タイトルとURLをコピーしました