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

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

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

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

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

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

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

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

EnjoyExcel

今回は前記事からのつながりをもって説明したいです。

よってピボットテーブルからピボットグラフを用意する事にします。

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

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

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

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

VBAでグラフを作るのは難しいです。

理由は作り方が沢山あるので関連オブジェクトを理解してないとコーディング時に混乱してしまうからです。

以下リンク先の記事では情報を整理してどのオブジェクトを使ってグラフを用意するべきなのかを解説しています。

グラフの作り方を理解するマクロでグラフを新規作成｜関連オブジェクトをもとに3種類の方法を紹介

マクロでグラフを新規作成｜関連オブジェクトをもとに3種類の方法を紹介

マクロでグラフを新規作成する方法を解説します。VBAを使ったグラフの作成は「複雑で分かりにくい」様です。理由としては作成方法が3種類あり情報が整理できていない事が挙げられます。情報を整理して簡単にグラフを作成出来るように解説していきます。

ピボットグラフとは

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

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_ピボットテーブルの有無を確認する

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

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

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

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

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

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

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

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

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

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

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

ワンポイント

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

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

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

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

EnjoyExcel

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

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

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

本記事でキーになるコードです。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)

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