Excelが大好きだ!

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


スポンサードリンク

ExcelでADODBで外部結合をしたときに集計結果が倍で表示された原因

ADODBを使ってExcelをデータベースのように扱うのは、SQL文を書くだけで集計出るのでとても便利ですね。

そんなデータベースで集計の指示を出すSQL文で今回始めて

Excel VBAでデータベースの結合処理を行ってみました。


やろうとしたこと

今回ミスったことを簡単に紹介。

①元データ。
f:id:ExcelLover:20200124105458j:plain

②マスターデータ。(突っ込みたい気持ちはわかるが今の段階ではそっとしておいてください。)
f:id:ExcelLover:20200124105509j:plain

①のデータに対して②マスターデータを関連付けて

・コード

・品名

・金額

を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 元データ.コード,マスター.品名

という風になる。


結合結果

上記コードの実行結果はこのようになる。
f:id:ExcelLover:20200124105523j:plain

left outer join(左外部結合という) はテーブル1の全データとそれに関連付けられたデータが表示される。

Cに果物名が表示されていないのはマスターテーブルにデータが存在していないから。


今回のネタはAの金額。

元データでは「100」だった金額が「200」になっている。

正直ロジックについては解説するだけの知識を今の所有していないのだが、

原因はマスターテーブルに「A」の項目が重複しているために発生している。

ADODBでデータベースのようにExcelを扱えるようにはなりますが、Excelはデータベースのように強い制限をかけられるわけではないので、使用する際にはデータチェックが必要ですね。

まとめ

今までSQL分は単一テーブルでの処理しか行っていなかったのであまり気になることはなかったのですが、今回のように結合処理を行う際には、それぞれのテーブルの整合性や重複の確認など、事前の準備が必要なこと痛感しました。

痛い目見つつ勉強していきます。