Excelが大好きだ!

Excel大好き経理マンがExcelの事を書き綴っていきます。


スポンサードリンク

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

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

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

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

www.excellovers.com

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



前回のおさらい

前回は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
    
'②2つの表の結合
    Dim myRS As ADODB.Recordset: Set myRS = New ADODB.Recordset
    Dim mySQL As String
    'UNION ALLで2つの表の全てのデータを含む。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関数をネストしているようなイメージです。

例えばExcelSQLの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 血液型,都道府県"

今回の例では「男性」「女性」シートを統合後、血液型・都道府県の列のみ抽出したデータを基に、
血液型・都道府県の列を基準にグループ化を行い件数を集計しています。


上記コードの結果はこのように集計されています。
f:id:ExcelLover:20220130174920p:plain


データの件数が統合後直後の4999件から187件に減少しています。
これは血液型・都道府県でグループ化された結果、4(血液型) × 47(都道府県) = 187に減少したからです。

そして血液型と都道府県のそれぞれの組み合わせに該当する件数が何件存在するかをCountで集計しています。

Group by句の注意点

  • Selectで指定した列をGroup by区に含める
  • Count等の集計関数を必ずしも含めなくて良い

例えばSelectに血液型を含めているのにGroup by句に含めていないと下記のようなエラーになります。
f:id:ExcelLover:20220130174938p:plain

まとめ

今回のコードで2つのシートを統合した結果から、血液型・都道府県別の人数を集計するところまで進みました。

次回はこの集計後のデータを基に表をクロス集計に変換したいと思います。