VBAでSQLを使用してシートの統合とクロス集計を行う(行合計・総合計編)

VBAでSQLを使用したクロス集計表を作成する当シリーズ。

www.excellovers.com

前回で終了予定でしたが、前回の最後にちょろっと言っていた箇所が対応できたので予定外のシリーズ追加です。

では前回何を言っていたかというと

列、行方向の合計がない

これの対応をご紹介したいと思います。

ROLLUP演算子

SQLには合計行を出すためにROLLUP演算子というものが用意されています。

が、残念ながらADODBでは使用することができません。

UNION演算子

ではどうするかというとUNION演算子を利用します。

UNION演算子は2つのSelect文を繋いで１つのデータを作るというものです。

このUNION演算子を利用して表部分・行合計・合計行を別々に作成した後に結合するようにします。

元データ・完成データ

前回までのデータとは違うデータを使用します。
左の担当者別・商品別の売上データから、右の商品別・担当者別のクロス集計を作成します。

前回までの表と異なるのは、当然ながら行合計と合計行があることです。

これをSQL文で表現します。

表構成

このように３つのデータを作成して、それをUNION演算子で結合した結果をクロス集計します。

コード

Sub SQL_クロス集計_合計行追加()
'--ADODBの使用設定
    'Microsoft ActiveX Data Objects X.X Libraryの参照設定を行っておく
    Dim myCon As ADODB.Connection: Set myCon = New ADODB.Connection
    Dim FullPath As String: FullPath = ThisWorkbook.FullName
    myCon.Provider = "Microsoft.ACE.OLEDB.12.0"
    myCon.Properties("Extended Properties") = "Excel 12.0;IMEX=1"
    myCon.Open FullPath
    Dim myRS As ADODB.Recordset: Set myRS = New ADODB.Recordset
    
'SQL文作成
'--①データ部分
    Dim mySQL As String
    mySQL = "select 商品名,担当,sum(売上) as 売上 from [売上$] group by 商品名,担当"
    
'--②行合計
    Dim mySQL_行合計 As String
    mySQL_行合計 = "select 商品名,'行合計',sum(売上) as 売上 from [売上$] group by 商品名"
       
'--③データ部分・行合計結合
    Dim mySQL_結合  As String
    mySQL_結合 = mySQL & " union all " & mySQL_行合計
    
'--④合計行
    Dim mySQL_合計行
    mySQL_合計行 = "select '合計行',担当,sum(売上) as 売上 from (" & mySQL_結合 & ") group by 担当"

'--⑤データ部分・行合計・合計行結合
    mySQL_結合 = mySQL & " union all " & mySQL_行合計 & " union all " & mySQL_合計行
    
'--⑥クロス集計
    Dim mySQL_総合計 As String
    mySQL_総合計 = "transform sum(売上) as 売上計 select 商品名 from (" & mySQL_結合 & ") group by 商品名 pivot 担当"
    myRS.Open mySQL_総合計, myCon, adOpenStatic
    
'--データ転記
    Dim i As Long
    For i = 1 To myRS.Fields.Count
        Worksheets("売上").Cells(1, i + 6).Value = myRS.Fields(i - 1).Name
    Next
    Worksheets("売上").Range("G2").CopyFromRecordset myRS
    
'--クロージング
    myRS.Close: Set myRS = Nothing
    myCon.Close: Set myCon = Nothing
End Sub

ずいぶん長いコードのように見えます。
が、ほぼSQL文の作成だけで、後はADODBの準備と、データの貼り付けだけです。

SQL結果推移

ここからはSQLの結果を逐一追っていくことでデータの集計具合を追いやすくします。

データ部分

まずは①のデータ部分です。
下記SQLを実行すると右の表が出来上がります。

左の表を商品名・担当者をキーにしてグループ化されています。

select 商品名,担当,sum(売上) as 売上 from [売上$] group by 商品名,担当

行合計

次に②の行合計です。 ここではグループ化の対象列を商品名だけにして、商品名別の売上を集計しています。

2列目の「Expr1001」と内容の「行合計」というものがあります。
SQLでは実際のデータの列だけでなく、固定値を指定することもできます。 その場合は値を「'シングルクォーテーション」で括ります。

UNION演算子は結合する2つのデータの列数を揃えておく必要があります。
そのために集計上は不要ですが固定値を指定して列数を揃えています。

select 商品名,'行合計',sum(売上) as 売上 from [売上$] group by 商品名

データ部分・行合計結合

③でデータ部分と行合計を結合します。

mySQL & " union all " & mySQL_行合計

合計行

④で合計行を作成します。
集計する元データは左の元データではなく、③で作成したUNION演算子で結合したデータを使用します。

select '合計行',担当,sum(売上) as 売上 from (" & mySQL_結合 & ") group by 担当

データ部分・行合計・合計行結合

③と④で作成したSQL文を結合してデータ部分・行合計・合計行結合を１つのデータにまとめています。
ここまでで必要なデータは全て揃いました。

mySQL & " union all " & mySQL_行合計 & " union all " & mySQL_合計行

クロス集計

後は前回の要領で出来上がった元データをクロス集計表のSQLに変換します。

transform sum(売上) as 売上計 select 商品名 from (" & mySQL_結合 & ") group by 商品名 pivot 担当

まとめ

私はまだ頭の中でこのようなSQL文を構築することが来ません。
なので実際に作成する際もこのように順を踏んで組み立ていっています。

出来上がってみればそれほど複雑なSQL文ではありません。
慣れてくれば普段使いも出来そうです。

ただ紹介しておいてなんですが、Excelの場合は元データ部(コード①)のクロス集計だけSQLで行って、後はVBAで数式を追加する方法もあります。

もっと言えばVBAでピボットテーブルを作成する方法もあります。

今の自分の感覚ではSQLが色々としっくりきます。

• コードを考える時にSQL以外の不要なことを考えなくていい
• 出来上がったコードの見通しがいい

色々な表現の仕方があるということを知った上で、自分にあった方法で書いてみてください。

SQL 第2版 ゼロからはじめるデータベース操作