ExcelVBAとピボットテーブル：第８回　外部データの利用

カテゴリー名：　［ExcelVBAとピボットテーブル］

2017/09/10

当シリーズでは、Excelのピボットテーブルをプログラミングのノウハウで生成するよう試みます。

　今回は、外部データの利用を取り上げます。

　ピボットキャッシュを設定するときに xlExternal を指定し、
Excelのワークブックや Accessデータベースをソースデータとして利用します。

　Accessがインストールされていなくても大丈夫です。

　当Webページで紹介するマクロファイルやVBScriptのファイル一式は、 vovPIVOT08.zip という圧縮ファイルに同梱しておきます。

《このページの目次》

　　　　

1.　阻害データの概要

　今回の素材データは、「第１回」から「第７回」にかけて用いた
pt_source.xls (ＩＤ、性別、身長、体重) および
pt_source02.xls (日付、商品、売上、仕入原価)です。

　同じ内容の Accessデータベース pt_source.accdb, pt_source02.accdb もあります。

　その他、pt_source03.xls, pt_source03.accdb というのも用います。その中身については後述。

　今回は素材ファイルが多いので、zip圧縮ファイルの中の data というフォルダに、ソースデータファイルをまとめて入れてあります。

2.　外部データ利用の基本形

　これまでは pt_source.xls, pt_source02.xls をワークブックとしてオープンし、その上でソースデータとして参照してきました。

　今回は、オープンせずに利用します。

　データベースを操作するためのプログラミング言語 SQL が出てきますが、ピボットテーブルとの関連で用いる SQL命令文は、初歩的なものがほとんどです。

　　　　

(1)　Excelファイルにデータベースとしてアクセスするための準備

　まず、「第２回」で作成した次の集計表を作ることから始めます。

性別 平均身長 平均体重
男性 166.7 66.4
女性 160.1 55.5
記載なし 164.2 67.4
全体 163.5 61.2

　当然ですが、ピボットフィールドを設定する箇所は、「第２回」のものと同じです。

　異なるのはピボットキャッシュを設定する部分ですが、
事前準備として、データベースアクセスのための下の設定を行います。

　　　　

　具体的には次のとおり。

cnn = "ODBC;DSN=Excel Files;DBQ=" & srcPath
tblName = "SourceDataRange"
sql = "SELECT * FROM [" & tblName & "];"

　変数 srcPath には予め pt_source.xls のフルパスを代入しておきます。

　pt_source.xls の第１ワークシートにソースデータが書かれている訳ですが、データがある領域には SourceDataRange という名前が付けてあります。つまり、「名前付き範囲」になっています。

　この場合、その SourceDataRange というのがデータベースのテーブル名になります。

　もし「名前付き範囲」になっていないなら、ワークシート名にドル記号を付けてテーブル名にします。

tblName = "Sheet1$"

　今回のケースでは、上のように書いても同じ結果になります。

　SQL命令文は、「該当のテーブルから全てのフィールドを選択する」という意味です。

　SELECT * のアスタリスクは、「全てのフィールド」(ＩＤ, 性別, 身長, 体重)を意味する簡易表現です。

目次に戻る

(2)　ピボットキャッシュの設定

　ピボットキャッシュを設定する部分は下のとおり。

Set ptCache = ActiveWorkbook.PivotCaches.Create(SourceType:=xlExternal)
ptCache.Connection = cnn
ptCache.CommandText = sql

　この後のピボットテーブル設定、ピボットフィールド設定は、「第２回」と同じです。

　上のようにピボットキャッシュを設定すると、内部的に SQL命令文が実行されて、その結果(SELECTにより選択されたデータ群)がソースデータとなります。

　　　　

　参考まで、ピボットテーブルを設定するまでの VBAコードを掲げます。

 1Sub Macro1()
 2    Dim pName As String, srcPath As String
 3    Dim ptCache As PivotCache, ptObj As PivotTable
 4    Dim cnn As String, tblName As String, sql As String
 5
 6    pName = ThisWorkbook.Path  ' 本ワークブックのフォルダ名
 7    srcPath = pName & "\pt_source.xls"  ' ソースデータのパス
 8    cnn = "ODBC;DSN=Excel Files;DBQ=" & srcPath
 9    tblName = "SourceDataRange"
10    sql = "SELECT * FROM [" & tblName & "];"
11
12    WorkSheets(1).Activate  ' 第１シートをアクティブに
13    Set ptCache = ActiveWorkbook.PivotCaches.Create(SourceType:=xlExternal)
14    ptCache.Connection = cnn
15    ptCache.CommandText = sql
16    Set ptObj = ptCache.CreatePivotTable( _
17        TableDestination:="R1C1", TableName:="ピボット01")
18    （以下、ピボットフィールド設定部分を省略）
19End Sub

目次に戻る

(3)　Accessデータベースファイルの取扱い

　Accessデータベースのファイルをソースデータとして利用するときは、前述の VBAコードのうち、srcPath と cnn に関する２行だけを書き換えます。

srcPath = pName & "\pt_source.accdb"  ' ソースデータのパス
cnn = "ODBC;DSN=MS Access Database;DBQ=" & srcPath

　上記のように記述すると、accdbファイルを読み取ってピボットテーブルが作成されます。

　mdbファイルであっても cnn の書き方は同じです。

　VBAマクロおよび VBScript, JScript の全体像については、 外部データの利用・基本形を参照して下さい。

目次に戻る

3.　複数のテーブルを扱う(フィールド構成が同じ場合)

　ここでは pt_source02.xls を外部データとして利用します。

　日付、商品、売上、仕入原価の四つの列から構成されており、
東京支店、名古屋支店、大阪支店の三つのワークシート(テーブル)があります。

　どのワークシートも同じ列の構成です。

　　　　

(1)　SQL命令文(UNION ALL)による結合

　三つのワークシートが、どれも同じ列の構成である場合、それらを結合するのは簡単です。

　データベースの用語に置き換えていうなら、フィールド構成が同じテーブルを単純に結合するときは UNION ALL を使うことができます。

　テーブル１の下にテーブル２をくっつけ、更にその下にテーブル３をくっつけるといった形です。

　ただし、単純に結合したのでは既存のフィールド(日付、商品、売上、仕入原価)だけのテーブルになってしまい、「支店」の情報が失われてしまいます。

　そこで、支店名がフィールドとして追加されるようにします。

　具体的には次のようなSQL命令文です。

SELECT '東京支店' AS 支店, * FROM [東京支店$]
UNION ALL
SELECT '名古屋支店' AS 支店, * FROM [名古屋支店$]
UNION ALL
SELECT '大阪支店' AS 支店, * FROM [大阪支店$]

　このSQLを実行すると、下のようなソースデータが生成されます。4,000行以上のデータになります。

支店 日付 商品 売上 仕入原価
東京支店 2016/1/1 調味料 35 21
東京支店 2016/1/1 飲料 80 57
(中略) 　 　 　 　
東京支店 2016/12/31 魚介類 100 74
名古屋支店 2016/1/1 調味料 57 35
名古屋支店 2016/1/1 飲料 182 124
(中略) 　 　 　 　
大阪支店 2016/12/31 乳製品 201 164
大阪支店 2016/12/31 魚介類 124 98

　このようなソースデータだと、支店と日付、支店と商品、日付と商品といったクロス集計が可能です。

　「第６回」で取り上げた xlConsolidation の場合に比べると、かなり自由がききます。

目次に戻る

(2)　テーブル結合を素材にした集計

　UNION ALL により三つのテーブルを結合し、それをソースデータにして集計します。

　売上の合計値について、支店別×商品別のクロス集計を行います。

　集計表を掲げた後に、VBAコードを掲載します。

商品 東京支店 名古屋支店 大阪支店 総計
調味料 21,433 21,406 21,446 64,285
飲料 44,910 43,034 43,248 131,192
乳製品 64,302 66,630 67,431 198,363
魚介類 87,600 84,616 92,100 264,316
総計 218,245 215,686 224,225 658,156

　　　　

 1Option Explicit
 2Sub Macro1()
 3    Dim pName As String, srcPath As String
 4    Dim ptCache As PivotCache, ptObj As PivotTable
 5    Dim cnn As String, sql As String
 6    Dim vObj, i As Integer
 7
 8    pName = ThisWorkbook.Path  ' 本ワークブックのフォルダ名
 9    srcPath = pName & "\pt_source02.xls"  ' ソースデータのパス
10    cnn = "ODBC;DSN=Excel Files;DBQ=" & srcPath  ' 接続用文字列
11    sql = _
12        "SELECT '東京支店' AS 支店, * FROM [東京支店$]" & _
13        vbNewLine & "UNION ALL" & vbNewLine & _
14        "SELECT '名古屋支店' AS 支店, * FROM [名古屋支店$]" & _
15        vbNewLine & "UNION ALL" & vbNewLine & _
16        "SELECT '大阪支店' AS 支店, * FROM [大阪支店$]"
17
18    WorkSheets(1).Activate  ' 第１シートをアクティブに
19    Set ptCache = ActiveWorkbook.PivotCaches.Create(SourceType:=xlExternal)
20    ptCache.Connection = cnn
21    ptCache.CommandText = sql
22    Set ptObj = ptCache.CreatePivotTable( _
23        TableDestination:="R1C1", TableName:="ピボット01")
24    （以下、ピボットフィールド設定部分　省略）
25End Sub

　VBAマクロおよび VBScript, JScript の全体像については、 同一フィールド構成のテーブル結合と集計を参照して下さい。

目次に戻る

(3)　AccessファイルとExcelファイルの扱い方の違い

　上記の VBAコードは、Excelファイルをデータベースとして扱うものです。

　Accessの accdbファイルを扱うときは、srcPath, cnn を下のように変更します。

srcPath = pName & "\pt_source02.accdb"
cnn = "ODBC;DSN=MS Access Database;DBQ=" & srcPath

　そのほか、SQL命令文も少しだけ変更する必要があります。

　　　　

　Excelファイルにおいてワークシートをテーブルとみなす場合、ワークシート名にドル記号を付けてテーブル名にします。

　先の VBAコードに出てくる [東京支店$] がそれです。

　accdbファイルを操作するときは、このドル記号を削除します。

　accdbファイルの場合は、[東京支店] にするか、あるいは、角括弧を外して単に 東京支店 としても大丈夫です。

　　　　

　ドル記号は、SQL命令文において特別の意味を持っています。

　そのため [東京支店$] のように角括弧で囲んでいます。囲まないとエラーになる。

　角括弧で囲まれた中身は、SQL命令文でいうところの特殊な意味が打ち消されて、通常の文字とみなされます。つまり、テーブル名の一部(単なる文字)とみなされます。

　なお、Excelファイルの場合であっても、名前付き範囲(一定のセル群に名前が付いているもの)であれば、その名前をテーブル名にできます。その場合はドル記号を付ける必要はありません。

目次に戻る

(4)　QueryTableによるソースデータの確認

　ピボットテーブルの設定において、SQL命令の実行結果がソースデータになるといっても、ほんとに意図したようなソースデータが内部的に生成されているかどうか不安です。

　そこで、同じ cnn, sql を用いて、SQLの実行結果を Excelファイルに出力してみます。

　Excelの QueryTable を使えばそれが可能です。

　QueryTable を利用するための VBAマクロを掲げておきます。

 1Option Explicit
 2Sub Macro1()
 3    Dim pName As String, srcPath As String
 4    Dim qt As QueryTable
 5    Dim cnn As String, sql As String
 6
 7    pName = ThisWorkbook.Path  ' 本ワークブックのフォルダ名
 8    srcPath = pName & "\pt_source02.xls"  ' ソースデータのパス
 9    cnn = "ODBC;DSN=Excel Files;DBQ=" & srcPath  ' 接続用文字列
10    sql = _
11        "SELECT '東京支店' AS 支店, * FROM [東京支店$]" & _
12        vbNewLine & "UNION ALL" & vbNewLine & _
13        "SELECT '名古屋支店' AS 支店, * FROM [名古屋支店$]" & _
14        vbNewLine & "UNION ALL" & vbNewLine & _
15        "SELECT '大阪支店' AS 支店, * FROM [大阪支店$]"
16
17    WorkSheets(1).Activate
18    ActiveSheet.UsedRange.Clear  ' シート内容を全消去
19    Set qt = ActiveSheet.QueryTables.Add(cnn, Range("A1"), sql)
20    qt.SaveData = False  ' 「クエリテーブルをワークブックと一緒に保存」しない
21    qt.RefreshPeriod = 0  ' 「定期的更新」の時間(0で無効)
22    qt.BackgroundQuery = False  ' バックグラウンド処理をしない
23    qt.Refresh  ' クエリテーブルを更新
24End Sub

　上のマクロを実行すると、アクティブシートにSQLの実行結果が書き出されます。

　支店、日付、商品、売上、仕入原価の五つの列が 4,000行以上書き出されるとおもいます。

　QueryTableについては、本論のピボットテーブルから大きく脱線するので省略。

　クエリテーブルを活用する VBScript, JScript は、zip圧縮ファイルに同梱してあるので、そちらを参照して下さい。

目次に戻る

4.　複数のテーブルを扱う(フィールド構成が異なる場合)

　フィールドの構成が異なるテーブルを結合して、集計を行います。

　いわゆるリレーショナルデータベースを扱う要領で結合します。

(1)　素材データ

　400人に意見を尋ねた結果がデータになっています(乱数で作った仮想データ)。

　回答してもらったのは性別(男性、女性)および意見(賛成、反対、保留)です。

　データは、二つのワークシートに分かれています。

　第１シートは「性別データ」というシート名で、
ＩＤ、性別の２列からなっています。

　第２シートは「意見データ」というシート名で、
ＩＤ、意見の２列からなっています。

　ＩＤが同じであれば同一人物です。

　素材データが書かれているのは pt_source03.xls および pt_source03.accdb です。

目次に戻る

(2)　ＩＤを手がかりにした結合と集計

　「ＩＤが同じなら同一人物」というのを手がかりにして、二つのワークシート(テーブル)を結合します。

　結合した結果、「ＩＤ、性別、意見」の三つの列からなるソースデータが作られます。

　その結合を行うための SQL命令文は次のとおり。

SELECT 性別データ.ＩＤ, 性別データ.性別, 意見データ.意見
    FROM 性別データ, 意見データ
    WHERE 性別データ.ＩＤ = 意見データ.ＩＤ;

　「性別データ」と「意見データ」の二つのテーブルから
ＩＤ、性別、意見というフィールドを取り出しますが、
〈二つのテーブルのＩＤが同じであるなら〉という条件付きです。

　どちらのテーブルでもＩＤには 1〜400 の番号が書かれていて(ただし順番が不揃い)、同じＩＤが両テーブルに必ずあります。結果、400個のデータがSELECTされます。

　SQL命令文を３行にしたのは見やすくするためです。プログラミング的には１行でもかまいません。

　あと、Accessデータベースなら上の SQL命令文でいいのですが、
Excelファイルをデータベースとして扱う場合は、テーブル名を
[性別データ$] および [意見データ$] と記述します。少々煩わしいですが。

目次に戻る

(3)　集計表とVBAマクロ

　今回 作成する集計表は次のとおり。

　 賛成 　 反対 　 保留 　 全体の 人数 全体の 構成比
性別 人数 構成比 人数 構成比 人数 構成比 　 　
男性 75 35.7% 58 27.6% 77 36.7% 210 100.0%
女性 67 35.3% 58 30.5% 65 34.2% 190 100.0%
総計 142 35.5% 116 29.0% 142 35.5% 400 100.0%

　　　　

　VBAマクロは、UNION ALL の結合のときと、SQL命令文が違うだけで基本的には同じです。もちろん、ピボットフィールドを設定する箇所は違いますが。

　以下、省略なしで VBAマクロを掲げます。

 1Option Explicit
 2Sub Macro1()
 3    Dim pName As String, srcPath As String
 4    Dim ptCache As PivotCache, ptObj As PivotTable
 5    Dim cnn As String, sql As String
 6    Dim vObj, i As Integer
 7
 8    pName = ThisWorkbook.Path  ' 本ワークブックのフォルダ名
 9    srcPath = pName & "\pt_source03.xls"  ' ソースデータのパス
10    cnn = "ODBC;DSN=Excel Files;DBQ=" & srcPath  ' 接続用文字列
11    sql = "SELECT [性別データ$].ＩＤ, [性別データ$].性別, " & _
12        "[意見データ$].意見" & vbNewLine & _
13        "FROM [性別データ$], [意見データ$]" & vbNewLine & _
14        "WHERE [性別データ$].ＩＤ = [意見データ$].ＩＤ;"
15
16    WorkSheets(1).Activate  ' 第１シートをアクティブに
17    Set ptCache = ActiveWorkbook.PivotCaches.Create(SourceType:=xlExternal)
18    ptCache.Connection = cnn
19    ptCache.CommandText = sql
20    Set ptObj = ptCache.CreatePivotTable( _
21        TableDestination:="R1C1", TableName:="ピボット01")
22
23    With ptObj.PivotFields("性別")
24        .Orientation = xlRowField
25        .LabelRange.Value = "性別"
26        i = 0
27        For Each vObj In Array("男性", "女性")
28            i = i + 1
29            .PivotItems(vObj).Position = i
30        Next
31    End With
32    With ptObj.PivotFields("意見")
33        .Orientation = xlColumnField
34        i = 0
35        For Each vObj In Array("賛成", "反対", "保留")
36            i = i + 1
37            .PivotItems(vObj).Position = i
38        Next
39        .LabelRange.Value = "意見"
40    End With
41    With ptObj.PivotFields("ＩＤ")
42        .Orientation = xlDataField
43        .Function = xlCount
44        .Caption = "人数"
45    End With
46    With ptObj.PivotFields("ＩＤ")
47        .Orientation = xlDataField
48        .Function = xlCount
49            .Caption = "構成比"
50            .Calculation = xlPercentOfRow  ' 「行」における構成比
51            .NumberFormat = "0.0%"
52    End With
53End Sub

異なるフィールド構成のテーブル結合と集計の方に、 VBScript, JScript の全体像も掲載しています。

〜　以上　〜