この記事では、SQLのLeft Outer Joinを使ってExcelの2つの表を結合しデータを取得する方法についてご説明します。
【動画】recordsetを使ってExcelの表の列名を取得する実際の動き
本題に入る前に、まずは次の動画をご覧ください。
左側の表「社員一覧」と右側の表「所属部署」を、SQLのLeft Outer Joinを使って結合させています。
結合された表の所属部署(H列)は、結合前の左側の表「社員一覧」の所属部署IDの値に合致する、右側の「所属部署」のIDの名称を出力させています。
マクロ作成の流れ
Excelファイルの例
今回は次のExcelファイルを用意しました。
表「社員一覧」と表「所属部署」の2つの表が用意されています。
この2つの表を結合させて1つの表に出力します。
結合された表は下の画像の通りです。
左側の表「社員一覧」と右側の表「所属部署」が結合されて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 rsFCnt As Integer 'レコードセットのフィールドの数用カウンタ
'シート名を取得する
Const sheetNM As String = "work"
'社員情報の表の先端位置のセル
Const synBgnPos As String = "B3"
'社員情報の表の最終位置のセル
Const synLstPos As String = "H18"
'所属部署の表の先端位置のセル
Const szkBgnPos As String = "J3"
'所属部署の表の最終位置のセル
Const szkLstPos As String = "K9"
'取得したデータを貼り付けるセルの行
Const pstRowPos As Integer = 20
'取得したデータを貼り付けるセルの列
Const pstClmPos As Integer = 2
'社員情報の表のセル範囲を取得
synTblNM = "[" & sheetNM & "$" & synBgnPos & ":" & synLstPos & "]"
'所属部署の表のセル範囲を取得
szkTblNM = "[" & sheetNM & "$" & szkBgnPos & ":" & szkLstPos & "]"
'インスタンスの生成
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 Outer 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 所属部署"
sqlStr = sqlStr & " from"
sqlStr = sqlStr & " " & synTblNM & " as a"
sqlStr = sqlStr & " LEFT OUTER JOIN"
sqlStr = sqlStr & " " & szkTblNM & " as b"
sqlStr = sqlStr & " ON a.所属部署ID = b.ID"
'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
コードの解説
注目すべきコード①
最初に見て頂きたいのは16行目から31行目です。
'社員情報の表の先端位置のセル
Const synBgnPos As String = "B3"
'社員情報の表の最終位置のセル
Const synLstPos As String = "H18"
'所属部署の表の先端位置のセル
Const szkBgnPos As String = "J3"
'所属部署の表の最終位置のセル
Const szkLstPos As String = "K9"
'取得したデータを貼り付けるセルの行
Const pstRowPos As Integer = 20
'取得したデータを貼り付けるセルの列
Const pstClmPos As Integer = 2
以上のコードは、左と右の表の各位置情報をマクロが参照するのに必要な値を設定しているコードです。
例えば、左側の表と右側の表の先頭行と先頭列や、最後の行と最後の列の位置を特定したり、結合後の表をどのセルに貼り付けるのかなどの情報を以上のコードで設定しておきます。
以上のコードだけでは単に数値しか見えないので以下の画像で図示しておきます。
注目すべきコード②
次に見て頂きたいのは16行目から31行目です。
'社員情報の表のセル範囲を取得
synTblNM = "[" & sheetNM & "$" & synBgnPos & ":" & synLstPos & "]"
'所属部署の表のセル範囲を取得
szkTblNM = "[" & sheetNM & "$" & szkBgnPos & ":" & szkLstPos & "]"
以上のコードは、社員一覧と所属部署の表のセル範囲を変数「synTblNM」「szkTblNM」に格納しているコードです。
変数「synTblNM」「szkTblNM」はSQL文のfrom句に指定するために使います。
コードの書き方は「シート名と表の範囲のセル」と記述します。
なお、コードだけでは分かりにくいと思うので、マクロ実行時の実際の値をお見せします。
"[work$B3:H18]" "[work$J3:K9]"
注目すべきコード③
次に見て頂きたいのは40行目から52行目です。
'インスタンスの生成
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接続するためのコードになります。
40行目でADO接続するために必要なインスタンスを生成し、そのインスタンスに対してADO接続するために45行目から47行目で接続情報を設定します。
なお、46行目では自分自身のExcelファイルをフルパスで指定しています。
自分自身のExcelファイルのフルパスはThisWorkbook.FullNameプロパティから取得することができます。
注目すべきコード④
次に見て頂きたいのは58行目から70行目です。
'左の表と右の表を結合させるLeft Outer 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 所属部署"
sqlStr = sqlStr & " from"
sqlStr = sqlStr & " " & synTblNM & " as a"
sqlStr = sqlStr & " LEFT OUTER JOIN"
sqlStr = sqlStr & " " & szkTblNM & " as b"
sqlStr = sqlStr & " ON a.所属部署ID = b.ID"
以上のコードは、左側の表「社員一覧」と右側の表「所属部署」を、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 所属部署
from
[work$B3:H18] as a
LEFT OUTER JOIN
[work$J3:K9] as b
ON a.所属部署ID = b.ID
今回の表の場合、その2つの表を結合するのに参照するキー列は、表「社員一覧」側は所属部署IDで、表「所属部署」側はIDになります。
ONの後に「a.所属部署ID = b.ID」と記述し、所属部署IDとIDが同じものを条件に結合しています。
注目すべきコード⑤
次に見て頂きたいのは73行目です。
'Recordsetを開く
rs.Open sqlStr, adodbCon, adOpenStatic
以上のコードは、recordsetのOpenメソッドにSQL文を引数に指定して実行してデータを取得するコードです。
なお、recordsetのOpenメソッドを実行するには事前にRecordsetオブジェクトのインスタンスを生成する必要があります。(生成していないとエラーになる)
今回は55行目でRecordsetオブジェクトのインスタンスを生成しています。
'Recordsetオブジェクトのインスタンスを生成する
Set rs = New ADODB.Recordset
注目すべきコード⑥
次に見て頂きたいのは76行目から84行目です。
'表の列数分だけ処理をループする
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を使って結合させたデータをセルに出力する処理とそのヘッダ(列名)をセルに出力する処理を行っているコードです。
76行目から81行目では、表の列の数だけ繰り返しヘッダ(列名)をセルに出力ています。
79行目でのFields.Item.Nameプロパティからヘッダ(列名)を取得することができます。
ヘッダ(列名)をセルに出力し終わったら84行目で、ヘッダ(列名)の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」というオブジェクトを使う場合は参照設定しないと動かない、程度に思って頂ければと思います。
最後に
本記事では、SQLのLeft Outer Joinを使ってExcelの2つの表を結合しデータを取得する方法についてご説明しました。
2つの表を、データベースのテーブルのようにSQLのLeft Outer Joinを使って結合させることができます。
結合させるのにキーとなる列が必要ですが、表の集計の一つにSQLのLeft Outer Joinを使う方法があることを覚えておくと便利かもしれないので参考にしてみてくださいね。
プログラミングのスキルを習得するなら
プログラミングのスキルを習得したい、今のスキルをもっと高めたい、そう考えているなら「プログラミングスクール」がおすすめです。
プログラミングのスキルの基礎を身につけるなら「TechAcademy」で1週間の無料体験があるので、これで「プログラミングの基礎」を学ぶのにおすすめですよ。
