【ExcelVBA】Left Outer Joinを使ってExcelの3つのシートの表を結合しデータを取得するには

9分

SHARE

この記事では、Left Outer Joinを使ってExcelの3つのシートの表を結合しデータを取得する方法についてご説明します。

ちなみに、表が全て同一シートに存在している場合はこちらの記事を参考にしてください。

【ExcelVBA】Left Outer Joinを使ってExcelの3つの表を結合しデータを取得するには

すぐにソースコードを見たい場合はこちらをクリック

【動画】Left Outer Joinを使ってExcelの3つのシートの表を結合しデータを取得する実際の動き

本題に入る前に、まずは次の動画をご覧ください。


workシートとは別に、「社員情報」「所属部署」「役職」のシートが用意され、そのシートに表が存在しています。

「社員情報」のG列に所属部署IDに対応した値が、H列には役職IDに対応する値が入力されています。

マクロを実行すると、シート「work」に「社員情報」の表の項番から役職(A列からH列)を出力しています。

なお、SQLのLeft Outer Joinを使って「社員情報」の表の所属部署IDと「所属部署」の表のIDに合致する名称をG列に、そして「社員情報」の表の役職IDと「役職」の表のIDに合致する名称をH列に出力しています。

マクロ作成の流れ

STEP.1
ADOを使用して結合させたい表が存在するExcelファイルに接続する
ADOを使用して結合させたい表が存在するExcelファイルに接続します。
STEP.2
各シートに存在する表を結合させるためのLeft Outer JoinのSQL文を用意する
各シートに存在する表を結合させるためのLeft Outer JoinのSQL文を用意します。
STEP.3
OpenメソッドにSTEP.2のSQL文を指定して実行する
OpenメソッドにSTEP.2のSQL文を指定して実行します。
STEP.4
STEP.3で実行したSQL文の結果をシートに出力する
STEP.3で実行したSQL文の結果をシートに出力します。
目次へ戻る

Excelファイルの例

今回は次のExcelファイルを用意しました。

シート「work」

シート「社員情報」

シート「所属部署」

シート「役職」

目次へ戻る

コードの例

Excelのマクロのコード(例)

Option Explicit

Sub test()

    Dim conn            As ADODB.Connection     'ADODB.Connectionオブジェクトのインスタンス用変数
    Dim rs              As ADODB.Recordset      'レコードセット用変数
    Dim sqlStr          As String               'SQL文用変数
    Dim synTbl          As String               '社員情報の表のセル範囲
    Dim szkTbl          As String               '所属部署の表のセル範囲
    Dim yskTbl          As String               '役職の表のセル範囲
    Dim rsFCnt          As Integer              'レコードセットのフィールドの数用カウンタ
    Dim rng             As range                'Rangeオブジェクト格納用変数
    
    '各シート名を取得する
    Const sheetNM As String = "work"
    Const sheetNM_EMPI As String = "社員情報"
    Const sheetNM_DPMT As String = "所属部署"
    Const sheetNM_POST As String = "役職"
    
    'インスタンスの生成
    Set conn = New ADODB.Connection

    With conn

        '接続情報の取得(自分自身のExcelファイルに接続する)
        .Provider = "Microsoft.ACE.OLEDB.12.0"
        .ConnectionString = "Data Source = " & ThisWorkbook.FullName & _
        ";Extended Properties =Excel 12.0;"

        'データソースへの接続を開く
        .Open

    End With
    
    'クライアントサイドカーソルに変更
    conn.CursorLocation = adUseClient
    
    '社員情報の表のセル範囲を取得
    synTbl = "[" & sheetNM_EMPI & "$A1:H" & Worksheets(sheetNM_EMPI).range("A1").End(xlDown).Row & "]"
    
    '所属部署の表のセル範囲を取得
    szkTbl = "[" & sheetNM_DPMT & "$A1:B" & Worksheets(sheetNM_DPMT).range("A1").End(xlDown).Row & "]"
    
    '役職の表のセル範囲を取得
    yskTbl = "[" & sheetNM_POST & "$A1:B" & Worksheets(sheetNM_POST).range("A1").End(xlDown).Row & "]"
    
    '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 所属部署"
    sqlStr = sqlStr & " ,c.名称 as 役職"
    sqlStr = sqlStr & " from"
    sqlStr = sqlStr & " ("
    sqlStr = sqlStr & " ("
    sqlStr = sqlStr & " " & synTbl & " as a"
    sqlStr = sqlStr & " LEFT OUTER JOIN"
    sqlStr = sqlStr & " " & szkTbl & " as b"
    sqlStr = sqlStr & " ON  a.所属部署ID = b.ID"
    sqlStr = sqlStr & " )"
    sqlStr = sqlStr & " LEFT OUTER JOIN"
    sqlStr = sqlStr & " " & yskTbl & " as c"
    sqlStr = sqlStr & " ON  a.役職ID = c.ID"
    sqlStr = sqlStr & " )"
    
    'Recordsetを開く
    rs.Open sqlStr, conn, adOpenStatic
    
    'データを出力するセルの範囲を取得する
    Set rng = Worksheets("work").range("A1").End(xlDown)

    'データを出力するセルをクリアする
    Worksheets("work").range("A1:H" & rng.Row).ClearContents
    
    '表の列数分だけ処理をループする
    For rsFCnt = 0 To rs.Fields.Count - 1

        '表の列名をセルに出力する
        Worksheets(sheetNM).Cells(1, 1 + rsFCnt).Value = rs.Fields.Item(rsFCnt).Name

    Next rsFCnt
    
    '先ほど貼り付けた列名の下の行にデータを貼り付ける
    Worksheets(sheetNM).Cells(1, 1).Offset(1, 0).CopyFromRecordset rs
    
    'メモリを解放する
    rs.Close
    conn.Close
    
    '後処理
    Set conn = Nothing
    Set rs = Nothing
    
End Sub

注目すべきコード①

最初に見て頂きたいのは21行目から33行目です。

    'インスタンスの生成
    Set conn = New ADODB.Connection

    With conn

        '接続情報の取得(自分自身のExcelファイルに接続する)
        .Provider = "Microsoft.ACE.OLEDB.12.0"
        .ConnectionString = "Data Source = " & ThisWorkbook.FullName & _
        ";Extended Properties =Excel 12.0;"

        'データソースへの接続を開く
        .Open

    End With

以上のコードは、マクロが自分自身のExcelファイルにADO接続するためのコードになります。

21行目でADO接続するために必要なインスタンスを生成し、そのインスタンスに対してADO接続するために26行目から28行目で接続情報を設定します。

なお、27行目では自分自身のExcelファイルをフルパスで指定しています。

自分自身のExcelファイルのフルパスはThisWorkbook.FullNameプロパティから取得することができます。

注目すべきコード②

次に見て頂きたいのは39行目から45行目です。

    '社員情報の表のセル範囲を取得
    synTbl = "[" & sheetNM_EMPI & "$A1:H" & Worksheets(sheetNM_EMPI).range("A1").End(xlDown).Row & "]"
    
    '所属部署の表のセル範囲を取得
    szkTbl = "[" & sheetNM_DPMT & "$A1:B" & Worksheets(sheetNM_DPMT).range("A1").End(xlDown).Row & "]"
    
    '役職の表のセル範囲を取得
    yskTbl = "[" & sheetNM_POST & "$A1:B" & Worksheets(sheetNM_POST).range("A1").End(xlDown).Row & "]"

以上のコードは、

「社員情報」「所属部署」「役職」のシートにある表を参照するセルの範囲を取得するコードです。

このあと実行するSELECT文で「社員情報」「所属部署」「役職」のシートにある表の値を参照するのに、ここで取得した表のセルの範囲を使います。

シートを参照するには、「シート名」に「$」の文字を結合させて、さらにそのシートにある表の範囲を指定します。

実際の値(今回のサンプル)は次の通りです。(Excelファイルの例を参考)

シート「社員情報」の表(synTbl)

シート「所属部署」の表(szkTbl)

シート「役職」の表(yskTbl)

注目すべきコード③

次に見て頂きたいのは51行目から71行目です。

    '左の表と右の表を結合させる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 所属部署"
    sqlStr = sqlStr & " ,c.名称 as 役職"
    sqlStr = sqlStr & " from"
    sqlStr = sqlStr & " ("
    sqlStr = sqlStr & " ("
    sqlStr = sqlStr & " " & synTbl & " as a"
    sqlStr = sqlStr & " LEFT OUTER JOIN"
    sqlStr = sqlStr & " " & szkTbl & " as b"
    sqlStr = sqlStr & " ON  a.所属部署ID = b.ID"
    sqlStr = sqlStr & " )"
    sqlStr = sqlStr & " LEFT OUTER JOIN"
    sqlStr = sqlStr & " " & yskTbl & " as c"
    sqlStr = sqlStr & " ON  a.役職ID = c.ID"
    sqlStr = sqlStr & " )"

以上のコードは、表「社員情報」と、表「所属部署」「役職」を、SQLのLeft Outer Joinを使って結合させるためのSQL文を用意しているコードです。

上記のSelect文のfrom句で指定している変数「synTbl」「szkTbl」「yskTbl」は注目すべきコード②で紹介した表を参照するセルの範囲です。

なお、コードだけではSelect文が分かりにくいと思うので、実際に実行するSQLを以下にお見せします。

select
    a.項番 as 項番,
    a.名前 as 名前,
    a.社員番号 as 社員番号,
    a.年齢 as 年齢,
    a.出身 as 出身,
    a.入社年 as 入社年,
    b.名称 as 所属部署,
    c.名称 as 役職
from
    (
        (
            [社員情報$A1:H16] as a
            LEFT OUTER JOIN
                [所属部署$A1:B7] as b
            ON  a.所属部署ID = b.ID
        )
        LEFT OUTER JOIN
            [役職$A1:B6] 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が同じものを条件に結合しています。

注目すべきコード⑤

次に見て頂きたいのは74行目です。

    'Recordsetを開く
    rs.Open sqlStr, conn, adOpenStatic

以上のコードは、recordsetのOpenメソッドにSQL文を引数に指定して実行してデータを取得するコードです。

なお、recordsetのOpenメソッドを実行するには事前にRecordsetオブジェクトのインスタンスを生成する必要があります。(生成していないとエラーになる)

今回は48行目でRecordsetオブジェクトのインスタンスを生成しています。

    'Recordsetオブジェクトのインスタンスを生成する
    Set rs = New ADODB.Recordset

注目すべきコード⑤

次に見て頂きたいのは83行目から91行目です。

    '表の列数分だけ処理をループする
    For rsFCnt = 0 To rs.Fields.Count - 1

        '表の列名をセルに出力する
        Worksheets(sheetNM).Cells(1, 1 + rsFCnt).Value = rs.Fields.Item(rsFCnt).Name

    Next rsFCnt
    
    '先ほど貼り付けた列名の下の行にデータを貼り付ける
    Worksheets(sheetNM).Cells(1, 1).Offset(1, 0).CopyFromRecordset rs

以上のコードは、表「社員情報」と、右側の表「所属部署」「役職」を、SQLのLeft Outer Joinを使って結合させたデータをセルに出力する処理とそのヘッダ(列名)をセルに出力する処理を行っているコードです。

83行目から88行目では、表の列の数だけ繰り返しヘッダ(列名)をセルに出力しています。

86行目のFields.Item.Nameプロパティからヘッダ(列名)を取得することができます。

ヘッダ(列名)をセルに出力し終わったら91行目で、ヘッダ(列名)の1つ下のセル位置に結合させたデータをセルに貼り付けています。

目次へ戻る

動作確認

マクロ実行前

今回はExcelファイルの例を使います。

マクロ実行後

マクロ実行後は、下の画面の通りに表が作成されます。

目次へ戻る

【注意】参照設定が必要です

一つ注意点があるのですが、先ほどのコードを動かすには参照設定が必要です。

参照設定の一覧(下の画像を参考)から次の項目(ライブラリ)にチェックを付けて「OK」ボタンをクリックします。

  1. Microsoft ActiveX Data Objects 2.8 Library(msado28.tlb)

なぜ必要かというと、Excelのマクロのコードの5行目の「ADODB.Connection」と6行目の「ADODB.Recordset」というオブジェクトが「msado28.tlb」というファイルを参照するからです。

    Dim conn            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マスター講座はこちら



目次へ戻る

前の記事

【ExcelVBA】指定した文字(列)が含まれる行がCSVファ…

次の記事

【ExcelVBA】閉じた3つのExcelファイルにある表をL…