この記事では、Left Outer Joinを使ってExcelの3つの表を結合しデータを取得する方法についてご説明します。
【動画】Left Outer Joinを使ってExcelの3つの表を結合しデータを取得する実際の動き
本題に入る前に、まずは次の動画をご覧ください。
左側の表「社員情報」と右側の表「所属部署」「役職」を、SQLのLeft Outer Joinを使って結合させています。
結合された表の所属部署(H列)は、結合前の左側の表「社員情報」の所属部署IDの値に合致する、右側の「所属部署」のIDの名称を出力させています。
また、結合された表の所属部署(I列)は、結合前の左側の表「社員情報」の役職IDの値に合致する、右側の「役職」のIDの名称を出力させています。
マクロ作成の流れ
Excelファイルの例
今回は次のExcelファイルを用意しました。
左側の表「社員情報」と、右側の表「所属部署」「役職」の3つの表が用意されています。
この3つの表を結合させて1つの表に出力します。
結合された表は下の画像の通りです。
左側の表「社員情報」と、右側の表「所属部署」「役職」の3つの表が結合されて1つの表に出力されています。
コードの例
Excelのマクロのコード(例)
Option Explicit
Sub getData()
Dim adodbCon As ADODB.Connection 'ADODB.Connectionオブジェクトのインスタンス用変数
Dim rs As ADODB.Recordset 'レコードセット用変数
Dim sqlStr As String 'SQL文用変数
Dim synTblNM As String '社員情報の表のセル範囲
Dim szkTblNM As String '所属部署の表のセル範囲
Dim yskTblNM As String '役職の表のセル範囲
Dim rsFCnt As Integer 'レコードセットのフィールドの数用カウンタ
'シート名を取得する
Const sheetNM As String = "work"
'社員情報の表の先端位置のセル
Const synBgnPos As String = "B3"
'社員情報の表の最終位置のセル
Const synLstPos As String = "I18"
'所属部署の表の先端位置のセル
Const szkBgnPos As String = "L3"
'所属部署の表の最終位置のセル
Const szkLstPos As String = "M9"
'役職の表の先端位置のセル
Const yskBgnPos As String = "L13"
'役職の表の最終位置のセル
Const yskLstPos As String = "M18"
'取得したデータを貼り付けるセルの行
Const pstRowPos As Integer = 20
'取得したデータを貼り付けるセルの列
Const pstClmPos As Integer = 2
'社員情報の表のセル範囲を取得
synTblNM = "[" & sheetNM & "$" & synBgnPos & ":" & synLstPos & "]"
'所属部署の表のセル範囲を取得
szkTblNM = "[" & sheetNM & "$" & szkBgnPos & ":" & szkLstPos & "]"
'役職の表のセル範囲を取得
yskTblNM = "[" & sheetNM & "$" & yskBgnPos & ":" & yskLstPos & "]"
'インスタンスの生成
Set adodbCon = New ADODB.Connection
With adodbCon
'接続情報の取得(自分自身のExcelファイルに接続する)
.Provider = "Microsoft.ACE.OLEDB.12.0"
.ConnectionString = "Data Source = " & ThisWorkbook.FullName & _
";Extended Properties =Excel 12.0;"
'データソースへの接続を開く
.Open
End With
'Recordsetオブジェクトのインスタンスを生成する
Set rs = New ADODB.Recordset
'左の表と右の表を結合させるLeft Joinを使ってデータを取得するSelect文を用意する
sqlStr = "select"
sqlStr = sqlStr & " a.項番 as 項番"
sqlStr = sqlStr & " ,a.名前 as 名前"
sqlStr = sqlStr & " ,a.社員番号 as 社員番号"
sqlStr = sqlStr & " ,a.年齢 as 年齢"
sqlStr = sqlStr & " ,a.出身 as 出身"
sqlStr = sqlStr & " ,a.入社年 as 入社年"
sqlStr = sqlStr & " ,b.名称 as 所属部署ID"
sqlStr = sqlStr & " ,c.名称 as 役職ID"
sqlStr = sqlStr & " from"
sqlStr = sqlStr & " ("
sqlStr = sqlStr & " ("
sqlStr = sqlStr & " " & synTblNM & " as a"
sqlStr = sqlStr & " LEFT OUTER JOIN"
sqlStr = sqlStr & " " & szkTblNM & " as b"
sqlStr = sqlStr & " ON a.所属部署ID = b.ID"
sqlStr = sqlStr & " )"
sqlStr = sqlStr & " LEFT OUTER JOIN"
sqlStr = sqlStr & " " & yskTblNM & " as c"
sqlStr = sqlStr & " ON a.役職ID = c.ID"
sqlStr = sqlStr & " )"
'Recordsetを開く
rs.Open sqlStr, adodbCon, adOpenStatic
'表の列数分だけ処理をループする
For rsFCnt = 0 To rs.Fields.Count - 1
'表の列名をセルに出力する
Worksheets(sheetNM).Cells(pstRowPos, pstClmPos + rsFCnt).Value = rs.Fields.Item(rsFCnt).Name
Next rsFCnt
'先ほど貼り付けた列名の下の行にデータを貼り付ける
Worksheets(sheetNM).Cells(pstRowPos, pstClmPos).Offset(1, 0).CopyFromRecordset rs
'後処理
'メモリを解放する
rs.Close
adodbCon.Close
Set adodbCon = Nothing
Set rs = Nothing
End Sub
コードの解説
注目すべきコード①
最初に見て頂きたいのは17行目から38行目です。
'社員情報の表の先端位置のセル
Const synBgnPos As String = "B3"
'社員情報の表の最終位置のセル
Const synLstPos As String = "I18"
'所属部署の表の先端位置のセル
Const szkBgnPos As String = "L3"
'所属部署の表の最終位置のセル
Const szkLstPos As String = "M9"
'役職の表の先端位置のセル
Const yskBgnPos As String = "L13"
'役職の表の最終位置のセル
Const yskLstPos As String = "M18"
'取得したデータを貼り付けるセルの行
Const pstRowPos As Integer = 20
'取得したデータを貼り付けるセルの列
Const pstClmPos As Integer = 2
以上のコードは、左と右の表の各位置情報をマクロが参照するのに必要な値を設定しているコードです。
例えば、左側の表と右側の表の先頭行と先頭列や、最後の行と最後の列の位置を特定したり、結合後の表をどのセルに貼り付けるのかなどの情報を以上のコードで設定しておきます。
以上のコードだけでは単に数値しか見えないので以下の画像で図示しておきます。
注目すべきコード②
次に見て頂きたいのは41行目から47行目です。
'社員情報の表のセル範囲を取得
synTblNM = "[" & sheetNM & "$" & synBgnPos & ":" & synLstPos & "]"
'所属部署の表のセル範囲を取得
szkTblNM = "[" & sheetNM & "$" & szkBgnPos & ":" & szkLstPos & "]"
'役職の表のセル範囲を取得
yskTblNM = "[" & sheetNM & "$" & yskBgnPos & ":" & yskLstPos & "]"
以上のコードは、「社員情報」「所属部署」「役職」の表のセル範囲を変数「synTblNM」「szkTblNM」「yskTblNM」に格納しているコードです。
変数「synTblNM」「szkTblNM」「yskTblNM」はSQL文のfrom句に指定するために使います。
コードの書き方は「シート名と表の範囲のセル」と記述します。
なお、コードだけでは分かりにくいと思うので、マクロ実行時の実際の値をお見せします。
[work$B3:I18] [work$L3:M9] [work$L13:M18]
注目すべきコード③
次に見て頂きたいのは50行目から62行目です。
'インスタンスの生成
Set adodbCon = New ADODB.Connection
With adodbCon
'接続情報の取得(自分自身のExcelファイルに接続する)
.Provider = "Microsoft.ACE.OLEDB.12.0"
.ConnectionString = "Data Source = " & ThisWorkbook.FullName & _
";Extended Properties =Excel 12.0;"
'データソースへの接続を開く
.Open
End With
以上のコードは、マクロが自分自身のExcelファイルにADO接続するためのコードになります。
50行目でADO接続するために必要なインスタンスを生成し、そのインスタンスに対してADO接続するために55行目から57行目で接続情報を設定します。
なお、56行目では自分自身のExcelファイルをフルパスで指定しています。
自分自身のExcelファイルのフルパスはThisWorkbook.FullNameプロパティから取得することができます。
注目すべきコード④
次に見て頂きたいのは56行目から68行目です。
'Recordsetオブジェクトのインスタンスを生成する
Set rs = New ADODB.Recordset
'左の表と右の表を結合させるLeft Joinを使ってデータを取得するSelect文を用意する
sqlStr = "select"
sqlStr = sqlStr & " a.項番 as 項番"
sqlStr = sqlStr & " ,a.名前 as 名前"
sqlStr = sqlStr & " ,a.社員番号 as 社員番号"
sqlStr = sqlStr & " ,a.年齢 as 年齢"
sqlStr = sqlStr & " ,a.出身 as 出身"
sqlStr = sqlStr & " ,a.入社年 as 入社年"
sqlStr = sqlStr & " ,b.名称 as 所属部署ID"
sqlStr = sqlStr & " ,c.名称 as 役職ID"
sqlStr = sqlStr & " from"
sqlStr = sqlStr & " ("
sqlStr = sqlStr & " ("
sqlStr = sqlStr & " " & synTblNM & " as a"
sqlStr = sqlStr & " LEFT OUTER JOIN"
sqlStr = sqlStr & " " & szkTblNM & " as b"
sqlStr = sqlStr & " ON a.所属部署ID = b.ID"
sqlStr = sqlStr & " )"
sqlStr = sqlStr & " LEFT OUTER JOIN"
sqlStr = sqlStr & " " & yskTblNM & " as c"
sqlStr = sqlStr & " ON a.役職ID = c.ID"
sqlStr = sqlStr & " )"
以上のコードは、左側の表「社員情報」と右側の表「所属部署」「役職」を、SQLのLeft Outer Joinを使って結合させるためのSQL文を用意しているコードです。
上記のSelect文で気を付ける点はfrom句の設定です。
表に対してデータを検索・抽出する場合は、fromに対して「シート名と表の範囲のセル」を指定する必要があります。
なお、コードだけではSelect文が分かりにくいと思うので、実際に実行するSQLを以下にお見せします。
select
a.項番 as 項番,
a.名前 as 名前,
a.社員番号 as 社員番号,
a.年齢 as 年齢,
a.出身 as 出身,
a.入社年 as 入社年,
b.名称 as 所属部署ID,
c.名称 as 役職ID
from
(
(
[work$B3:I18] as a
LEFT OUTER JOIN
[work$L3:M9] as b
ON a.所属部署ID = b.ID
)
LEFT OUTER JOIN
[work$L13:M18] as c
ON a.役職ID = c.ID
)
今回の表の場合、表「社員情報」と表「所属部署」を結合するのに参照するキー列は、表「社員情報」側は所属部署IDで、表「所属部署」側はIDになります。
ONの後に「a.所属部署ID = b.ID」と記述し、所属部署IDとIDが同じものを条件に結合しています。
また、表「社員情報」と表「役職」を結合するのに参照するキー列は、表「社員情報」側は役職IDで、表「役職」側はIDになります。
ONの後に「a.役職ID = c.ID」と記述し、役職IDとIDが同じものを条件に結合しています。
注目すべきコード⑤
次に見て頂きたいのは91行目です。
'Recordsetを開く
rs.Open sqlStr, adodbCon, adOpenStatic
以上のコードは、recordsetのOpenメソッドにSQL文を引数に指定して実行してデータを取得するコードです。
なお、recordsetのOpenメソッドを実行するには事前にRecordsetオブジェクトのインスタンスを生成する必要があります。(生成していないとエラーになる)
今回は65行目でRecordsetオブジェクトのインスタンスを生成しています。
'Recordsetオブジェクトのインスタンスを生成する
Set rs = New ADODB.Recordset
注目すべきコード⑤
次に見て頂きたいのは94行目から102行目です。
'表の列数分だけ処理をループする
For rsFCnt = 0 To rs.Fields.Count - 1
'表の列名をセルに出力する
Worksheets(sheetNM).Cells(pstRowPos, pstClmPos + rsFCnt).Value = rs.Fields.Item(rsFCnt).Name
Next rsFCnt
'先ほど貼り付けた列名の下の行にデータを貼り付ける
Worksheets(sheetNM).Cells(pstRowPos, pstClmPos).Offset(1, 0).CopyFromRecordset rs
以上のコードは、左側の表「社員情報」と右側の表「所属部署」「役職」を、SQLのLeft Outer Joinを使って結合させたデータをセルに出力する処理とそのヘッダ(列名)をセルに出力する処理を行っているコードです。
94行目から99行目では、表の列の数だけ繰り返しヘッダ(列名)をセルに出力しています。
97行目でのFields.Item.Nameプロパティからヘッダ(列名)を取得することができます。
ヘッダ(列名)をセルに出力し終わったら102行目で、ヘッダ(列名)の1つ下のセル位置に結合させたデータをセルに貼り付けています。
動作確認
マクロを実行した実行結果は、本記事内のExcelファイルの例の内容をご覧ください。
【注意】参照設定が必要です
一つ注意点があるのですが、先ほどのコードを動かすには参照設定が必要です。
参照設定の一覧(下の画像を参考)から次の項目(ライブラリ)にチェックを付けて「OK」ボタンをクリックします。
- Microsoft ActiveX Data Objects 2.8 Library(msado28.tlb)
なぜ必要かというと、Excelのマクロのコードの5行目の「ADODB.Connection」と6行目の「ADODB.Recordset」というオブジェクトが「msado28.tlb」というファイルを参照するからです。
Dim adodbCon As ADODB.Connection 'ADODB.Connectionオブジェクトのインスタンス用変数
Dim rs As ADODB.Recordset 'レコードセット用変数
この参照設定をしないと下の画像のエラーが出ますので必ず行う必要があります。
ここでは「msado28.tlb」とは何者かについては記事の本題から逸れてしまうので詳細は割愛しますが、マクロで「ADODB.Connection」「ADODB.Recordset」というオブジェクトを使う場合は参照設定しないと動かない、程度に思って頂ければと思います。
最後に
本記事では、Left Outer Joinを使ってExcelの3つの表を結合しデータを取得する方法についてご説明しました。
3つの表を、データベースのテーブルのようにSQLのLeft Outer Joinを使って結合させることができます。
結合させるのにキーとなる列が必要ですが、表の集計の一つにSQLのLeft Outer Joinを使う方法があることを覚えておくと便利かもしれないので参考にしてみてくださいね。
Excelのスキル向上やExcelの基礎知識をしっかりと学びたいなら
Excelのスキルを習得したい、Excelの基礎知識をもっと理解したい、そう考えているなら「無期限サポート付きExcel講座【すごい改善】」がおすすめです。
Excelのスキルの基礎を身につけるなら【すごい改善】で無期限サポート付きがあるので、これで「Excelのスキルや基礎」を学ぶのにおすすめですよ。
→ 受講後、何度でも無期限でメールで質問できるアフターサポートがついているExcelマスター講座はこちら
