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

SQLでシートの統合とクロス集計を行う今回のシリーズ。

「男性」「女性」2つのシートを統合する
統合したデータを元にクロス集計を行う
完成したデータをシートに貼付け

前回はシートの統合を行いました。

www.excellovers.com

今回はクロス集計について書く予定でしたが、2の箇所をもう少し掘り下げて、サブクエリとグループ化について書きたいと思います。

前回のおさらい
コード
SQLを使用するメリット
サブクエリ
- SQL文を分割して書くメリット
Group by句
- Group by句の注意点
まとめ

前回のおさらい

前回は2つのシート統合して、必要な2つの列を抽出しました。
f:id:ExcelLover:20220130174843p:plain

今回はクロス集計の前にサブクエリとグループ化を行います。

コード

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
    Dim mySQL As String
    'UNION ALLで２つの表の全てのデータを含む。UNIONとすると重複分がカットされる
    mySQL = "select 血液型,都道府県 from [男性$] UNION ALL select 血液型,都道府県 from [女性$]"
    
'③グループ化
    Dim mySQL_グループ化 As String
    mySQL_グループ化 = mySQL_グループ化 & " select 血液型,都道府県,count(*) as 人数 from (" & mySQL & ")"
    mySQL_グループ化 = mySQL_グループ化 & " group by 血液型,都道府県"
    
    myRS.Open mySQL_グループ化, myCON

'④「集計」シートへ転記
    Dim myWS As Worksheet: Set myWS = Worksheets("集計")
    myWS.Cells.Clear
    Dim i As Long
    'CopyFromRecordsetはヘッダー部分は含まないため別の方法で転記
    For i = 1 To myRS.Fields.Count
        myWS.Cells(1, i).Value = myRS.Fields(i - 1).Name
    Next
    myWS.Range("A2").CopyFromRecordset myRS
    
'⑤クロージング
    myRS.Close
    Set myRS = Nothing
    myCON.Close
    Set myCON = Nothing
    myWS.Activate
End Sub

SQLを使用するメリット

上記コードの内①②④⑤については前回と全く同じ内容です。

変わっているのは③の部分が追加されているだけです。

Excelのデータ集計でSQLを使用するメリットは

Excelの複数の機能を使わない
SQLの構文を少し変えるだけ
他の部分のコードを変える必要がない
集計するデータを変えることが出来る

点にあると思います。

サブクエリ

サブクエリとはSQL文の中に別の完結したSQL文を挿入することを言います。
Excel関数をネストしているようなイメージです。

例えばExcelでSQLのSelect文の構文は以下です。

select 列名 from テーブル名(≒シート名)

既に存在しているシートのデータを基にしてデータを集計する構文です。

サブクエリをテーブルに使用する場合の構文は以下です。

select 列名 from (select 列名 from テーブル名(≒シート名))

何らかの集計を行った後のデータをそのまま次の集計の元データとして利用することが出来ます。

今回の例では前回作成した「男性」「女性」シートを統合したデータをそのまま、次のグループ化の集計に利用しています。
※下記1行目のSQL文が2行目の末尾にそのまま使用されている

mySQL = "select 血液型,都道府県 from [男性$] UNION ALL select 血液型,都道府県 from [女性$]"

mySQL_グループ化 = mySQL_グループ化 & " select 血液型,都道府県,count(*) as 人数 from (" & mySQL & ")"
mySQL_グループ化 = mySQL_グループ化 & " group by 血液型,都道府県"

今回、書いていて思ったのですが、上記SQL文は変数を分けずに一度に書くことも可能です。
最初はそのように書こうとしていました。

ところが別けて書いてみると色々とメリットを感じられました。

SQL文を分割して書くメリット

SQL文のテストを段階的に行うことが出来る
SQL文を読む時に問題のない箇所を読まずに済む

いづれも段階的に実行・解析を行うことが可能になることで、思考する時に問題のない箇所を排除することが出来ます。

上記のコードではまずテーブルの結合を行っています。
この箇所だけで問題なく動作することが確認できれば、2行目以降ではその部分を検証する必要がありません。

この問題のない箇所を変数で表現することで、次の箇所を検証する際に意識しないで済むようになります。

VBAでのSub・Functionの切り分けを行うような感覚でしょうか。

Group by句

select 列名[,集計関数(列名)] from テーブル名 group by 列名

Group by句はGroup by句で指定した列を基準にしてデータのグループ化と集計を行うものです。
Excelでのピボットテーブルの単純集計表のイメージです。

mySQL = "select 血液型,都道府県 from [男性$] UNION ALL select 血液型,都道府県 from [女性$]"

mySQL_グループ化 = mySQL_グループ化 & " select 血液型,都道府県,count(*) as 人数 from (" & mySQL & ")"
mySQL_グループ化 = mySQL_グループ化 & " group by 血液型,都道府県"