ADODBを使ってExcelをデータベースのように扱うのは、SQL文を書くだけで集計出るのでとても便利ですね。
そんなデータベースで集計の指示を出すSQL文で今回始めて
Excel VBAでデータベースの結合処理を行ってみました。
やろうとしたこと
今回ミスったことを簡単に紹介。
①元データ。
②マスターデータ。(突っ込みたい気持ちはわかるが今の段階ではそっとしておいてください。)
①のデータに対して②マスターデータを関連付けて
・コード
・品名
・金額
を1つのデータとして表示しようと思った。
コード
Option Explicit Sub DoubleSum() Dim myCon As ADODB.Connection Set myCon = New ADODB.Connection myCon.Provider = "Microsoft.ACE.OLEDB.12.0" myCon.Properties("Extended Properties") = "Excel 12.0;IMEX=1" myCon.Open ThisWorkbook.FullName Dim myRS As ADODB.Recordset Set myRS = New ADODB.Recordset Dim mySQL As String mySQL = "select A.コード,B.品名,sum(金額) as 合計 from [" & Worksheets(1).Name & "$] as A" mySQL = mySQL & " left outer join [" & Worksheets(2).Name & "$] as B on A.コード = B.コード group by A.コード,B.品名" myRS.Open mySQL, myCon Worksheets(3).Range("A1").CopyFromRecordset myRS myRS.Close Set myRS = Nothing myCon.Close Set myCon = Nothing End Sub
vlookupのようにSQLで関連付けを行うには外部結合「Outer Join句」を使用します。
公式 |
---|
select テーブル名.カラム名,・・・ from テーブル1 left outer join テーブル2 on テーブル1.カラム名 = テーブル2.カラム名 |
公式に今回の事例を当てはめてみると
select 元データ.コード,マスター.品名,sum(金額)
from 元データ
left outer join マスターデータ
on 元データ.コード = マスターデータ.コード
group by 元データ.コード,マスター.品名
という風になる。
結合結果
上記コードの実行結果はこのようになる。
left outer join(左外部結合という) はテーブル1の全データとそれに関連付けられたデータが表示される。
Cに果物名が表示されていないのはマスターテーブルにデータが存在していないから。
今回のネタはAの金額。
元データでは「100」だった金額が「200」になっている。
正直ロジックについては解説するだけの知識を今の所有していないのだが、
原因はマスターテーブルに「A」の項目が重複しているために発生している。
ADODBでデータベースのようにExcelを扱えるようにはなりますが、Excelはデータベースのように強い制限をかけられるわけではないので、使用する際にはデータチェックが必要ですね。
まとめ
今までSQL分は単一テーブルでの処理しか行っていなかったのであまり気になることはなかったのですが、今回のように結合処理を行う際には、それぞれのテーブルの整合性や重複の確認など、事前の準備が必要なこと痛感しました。
痛い目見つつ勉強していきます。