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

今までにもVBAからSQLを使用してSelect文で集計は行ってきた。

今回始めて表の統合とクロス集計をSQLで行ったので備忘録。

お題目

お題はこのようなデータを使用します。
※データの元ネタはなんちゃって個人情報

• シートは「男性」「女性」「集計」の３つ。
• 「男性」「女性」シートは「姓」～「都道府県」までの7列構成。
• 2つのシートは全く同じ構成。
• 「集計」シートは空白です。

最終目的としてこのような資料をSQLで作成します。

手順としてはこのようになります。

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

まず今回は2つのシートの指定列を統合します。

「男性」「女性」シートの血液型・都道府県の2列を統合します。

コード

まずは今回の作業範囲までのコードです。

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"
    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 [女性$]"
    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

ADODBの使用準備

当blogでは何回と紹介しているADODBです。
まずこの機能を使用するには参照設定が必要です。

VBEの上部メニューから「ツール」→「参照設定」と選択。
表示されたライブラリの一覧から「Microsoft ActiveX Data Objects X.X Library」にチェックを入れる。

以上で使用準備は完了です。

UNIONクエリ

コード内①の部分に関しては詳細は割愛。
1点だけ気をつけないといけないのは「FullPath = ThisWorkbook.FullName」の部分です。
1度も保存していないブックでは正常にフルパスを取得することが出来ず、マクロが正常に動きません。

さて今回の本題であるUNIONクエリです。

Select文 UNION [ALL] Select文

構文は2つのSelect文の間に「UNION」と記載するだけです。 大変簡単なUNIONクエリですが、何点か注意点があります。

• 統合する2つのSelect文の列数を同一にしておく
• 統合する2つのSelect文の列のデータ型を同一にしておく

列数が同一でない場合下記のようエラーが発生します。

データ型が同一出ない場合は、エラーは発生しませんが本来数値型であるはずのデータが文字列と認識されるなど、データ型の不一致が発生します。

ALLオプション

UNIONクエリは普通に使用すると、重複するデータが削除されます。 重複の判定はSelect文に含まれている列で判定されます。

今回の例で言えば元シートの7列全てが判定対象ではなく、UNIONクエリに含まれている「血液型」「都道府県」で判定されます。

では統合する2つのシートの全てのデータを表示する場合にはどうすれば良いのか。

ALLオプションです。

使用方法は「UNION」の後に「ALL」と記載するだけです。

実行結果

この図では判断が付きませんが、元シートでは男性が2505件、女性が2494件ですので正しく統合されていると判断できます。

まとめ

今回は1番の作業を行いました。

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

単純に2つのシートを統合するのは2つのシートを呼び出すSelect文をUNIONクエリで繋ぐだけで簡単にできました。
ちなみに3つのシートを統合したい場合は同じようにUNIONクエリで追加すれば統合することが出来ます。

次回はSQLでクロス集計についてまとめます。