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

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

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

前々回はシートの統合、前回グループ化を行いました。

www.excellovers.com
www.excellovers.com

今回はいよいよ本題のクロス集計を行います。

前回のおさらい

前回はSQLで血液型・都道府県でグループ化を行い、人数を集計しました。

今回はこのデータを基にSQLでクロス集計を行います。

コード

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" 'IMEX(Import Export Mode):１で読み込みモード
    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_クロス集計 = "transform count(*) as 人数"
    mySQL_クロス集計 = mySQL_クロス集計 & " select 都道府県 from (" & mySQL & ")"
    mySQL_クロス集計 = mySQL_クロス集計 & " group by 都道府県"
    mySQL_クロス集計 = mySQL_クロス集計 & " order by 都道府県 desc"
    mySQL_クロス集計 = mySQL_クロス集計 & " pivot 血液型"

    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

今回も①②④⑤は前回と内容が全く同じです。
変わるのは③のみです。

VBAのみでこのように集計方法を変更するとなると、別の機能を使用する必要が出来てきます。
ところがSQLを使用した方法ではSQL文を変更するだけで集計方法を変更することが出来ます。

Transform ～ Pivot句

SQLでクロス集計を行うにはTransform Pivot句を使用します。

Transform 集計関数 Select文 [Group by句] Pivot [in 列名1,列名2…]

集計関数とはSUM関数やCOUNT関数などの関数を意味します。

SQL文とピボットテーブルの対比

単純集計

まずは前回ご紹介した単純集計表版のピボットテーブルとSQL文を比べてみます。

select 血液型,都道府県,count(*) as 人数 from テーブル group by 血液型,都道府県

ピボットテーブルで行に指定している項目がSQL文ではGroup by句。
値に指定している項目がCOUNT関数になります。

SQL文ではGroup by句に指定した項目をSELECT文の列にも同じように指定します。
今回の例ではGroup by句に「血液型,都道府県」と指定していますので、列にも同じように指定しています。
(※列に指定しないことも可能です。)

クロス集計

次はクロス集計版です。

transform count(*) as 人数 select 都道府県 from テーブル group by 都道府県 order by 都道府県 desc pivot 血液型

ピボットテーブルでクロス集計を行うために、血液型を行から列に移動させました。

これをSQL文で表現するにはGroup by句の血液型をPivot句に移動させます。
そしてCOUNT関数をTransform句に移動させます。

まとめ

SQL文でクロス集計を行う方法をご紹介しました。
グループ化のSQL文から構文を少し変形させるだけでクロス集計表を作成することが出来ました。

ただ今の時点ではピボットテーブルと比べると以下の点で機能劣化しています。

• 列、行方向の合計がない
• ダブルクリックしてもデータ展開されない

この辺りはSQL文を更に変形させるか、VBA側の処理で対応することが可能です。 また機会があればご紹介したといと思います。

ADODBを参照設定しなければいけないという手間はありますが

• データを簡単・柔軟に集計でき
• 集計の内容がコードを見るだけで理解できる

という大きなメリットがあります。

基本のSelect文が使えるようになるだけでも、大きなメリットが感じられと思いますので是非チャレンジしてみてください。

Excelピボットテーブル データ集計・分析の「引き出し」が増える本

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