【ExcelVBA】表の列の値をキーに別の表データを同じに並び替える②

10分

SHARE

この記事では、表の列の値をキーに別の表データを同じに並び替える方法についてご説明します。

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

独自の規則の並び方をしているデータをキーにソートする

単純に数値や文字(列)、アルファベットなどをソートする場合は、Excelの並び替え機能で並び替えることができます。

ですが、独自の規則に基づいて並んでいるデータの並びを、別の表のデータも同じ通りに並び替えたい場合にExcelの並び替え機能だけでは正しく並び替えることができません。

例えば、以下はとある学級の生徒の点数データですが、独自の規則性に基づいて並んでおり、左側の表の生徒一覧と右側の表の生徒一覧とでは生徒名の並びが異なります。

今回のマクロでは、左側の表にある生徒名の並びの通りと同じに、右側の表を並び替えることを実現します。

並び替えたイメージは下の通りです。

目次へ戻る

【動画】表の列の値をキーに別の表データを同じに並び替える実際の動き

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


左の表と右の表では、生徒名の並びが異なっています。

マクロを実行すると、左の表にある生徒名の並びに合わせて右の表が並び替わります。

また、右側の表の生徒が左側の表に存在しない場合は表の末に並べて表示しています。

マクロ作成の流れ

STEP.1
ADOを使用してExcelファイルに接続する
ADOを使用してExcelファイルに接続します。
STEP.2
左と右の表を結合し、左と右の表のどちらにも存在する生徒データを抽出するためのSQL文を用意する
左と右の表を結合し、左と右の表のどちらにも存在する生徒データを抽出するためのSQL文を用意します。
STEP.3
OpenメソッドにSTEP.2のSQL文を指定して実行する
OpenメソッドにSTEP.2のSQL文を指定して実行します。
STEP.4
左と右の表を結合し、左の表の生徒データのうち、右の表に存在しない生徒データを抽出するためのSQL文を用意する
左と右の表を結合し、左の表の生徒データのうち、右の表に存在しない生徒データを抽出するためのSQL文を用意します。
STEP.5
OpenメソッドにSTEP.2のSQL文を指定して実行する
OpenメソッドにSTEP.4のSQL文を指定して実行します。
STEP.6
STEP.3で実行したSQL文の結果をシートに出力する
STEP.3で実行したSQL文の結果をシートに出力します。
STEP.7
STEP.5で実行したSQL文の結果を、STEP.6で貼り付けた抽出結果の1つ下の行にシートに出力する
STEP.5で実行したSQL文の結果を、STEP.6で貼り付けた抽出結果の1つ下の行にシートに出力します。
目次へ戻る

Excelファイルの例

今回は先ほどお見せしたExcelファイルを使います。

左側の表には生徒の1月から6月の点数データが、右側の表には生徒の7月から12月の点数データが入力されています。

左側の表と右側の表は生徒の並びが異なります。

左側の表と右側の表は生徒の並びを合わせたいため、左側の生徒の並びに合わせて右側の表の並びを並び替えます。

生徒名の並びは独自の規則に基づいて並んでいるので、Excelの並び替え機能では正しく並び替えができないのでマクロで正しく並び替えます。

目次へ戻る

コードの例

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

Option Explicit

Sub test()

    Dim adodbCon    As ADODB.Connection     'ADODB.Connectionオブジェクトのインスタンス用変数
    Dim rs          As ADODB.Recordset      'レコードセット用変数
    Dim rs2         As ADODB.Recordset      'レコードセット用変数
    Dim sqlStr      As String               'SQL文用変数
    Dim lTblNM      As String               '表のセル範囲
    Dim rTblNM      As String               '表のセル範囲
    
    'シート名を取得する
    Const sheetNM As String = "work"
     
    '扱いたい表の先端位置のセル
    Const lBgnPos As String = "B2"
    
    '扱いたい表の最終位置のセル
    Const lLstPos As String = "H22"
     
    '扱いたい表の先端位置のセル
    Const rBgnPos As String = "K2"
    
    '扱いたい表の最終位置のセル
    Const rLstPos As String = "Q22"
    
    '右表の最上行
    Const rbgnRPos As Integer = 3
    
    '列名を取得する表のセル範囲を取得
    lTblNM = "[" & sheetNM & "$" & lBgnPos & ":" & lLstPos & "]"
    
    '列名を取得する表のセル範囲を取得
    rTblNM = "[" & sheetNM & "$" & rBgnPos & ":" & rLstPos & "]"
        
    'インスタンスの生成
    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
    
    'Recordsetオブジェクトのインスタンスを生成する
    Set rs2 = New ADODB.Recordset
    
    '左の表と右の表を結合させるLeft Joinを使ってデータを取得するSelect文を用意する
    sqlStr = "select b.* from"
    sqlStr = sqlStr & " " & lTblNM & " AS a"
    sqlStr = sqlStr & " Left Outer Join"
    sqlStr = sqlStr & " " & rTblNM & " As b"
    sqlStr = sqlStr & " ON  a.名前 = b.名前"
    sqlStr = sqlStr & " where b.名前 <> ''"
        
    'Recordsetを開く
    rs.Open sqlStr, adodbCon, adOpenStatic
    
    '右側の表の生徒名が左側の表に存在しないデータ行を取得するSelect文を用意する
    sqlStr = "select * from"
    sqlStr = sqlStr & " " & rTblNM
    sqlStr = sqlStr & " where not exists"
    sqlStr = sqlStr & " ("
    sqlStr = sqlStr & " select"
    sqlStr = sqlStr & " 名前"
    sqlStr = sqlStr & " from"
    sqlStr = sqlStr & " " & lTblNM
    sqlStr = sqlStr & " where"
    sqlStr = sqlStr & " " & lTblNM & ".名前"
    sqlStr = sqlStr & " ="
    sqlStr = sqlStr & " " & rTblNM & ".名前"
    sqlStr = sqlStr & " )"
    sqlStr = sqlStr & " order by"
    sqlStr = sqlStr & " " & rTblNM & ".名前"
    sqlStr = sqlStr & " asc"
    
    'Recordsetを開く
    rs2.Open sqlStr, adodbCon, adOpenStatic
    
    '取得したデータを右表に貼り付ける
    Worksheets(sheetNM).Range("K" & rbgnRPos).CopyFromRecordset rs
    
    '取得したデータを右表に、先ほど貼り付けたデータの最後行の次行に貼り付ける
    Worksheets(sheetNM).Range("K" & rbgnRPos + CInt(rs.RecordCount)).CopyFromRecordset rs2
        
    '後処理
    
    'メモリを解放する
    rs.Close
    rs2.Close
    adodbCon.Close
    Set adodbCon = Nothing
    Set rs = Nothing
    
End Sub

コードの解説

注目すべきコード①

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

    '扱いたい表の先端位置のセル
    Const lBgnPos As String = "B2"
    
    '扱いたい表の最終位置のセル
    Const lLstPos As String = "H22"
     
    '扱いたい表の先端位置のセル
    Const rBgnPos As String = "K2"
    
    '扱いたい表の最終位置のセル
    Const rLstPos As String = "Q22"
    
    '右表の最上行
    Const rbgnRPos As Integer = 3

以上のコードは、左と右の表が持つ各位置情報をマクロが参照するのに必要な値を設定しているコードです。

例えば、左側の生徒が右側の表には何行目の何列目に存在するのかを特定したり、生徒の各データが何列までを対象にするのか、などの情報を以上のコードで設定しておきます。

以上のコードだけでは単に数値しか見えないので以下の画像で図示しておきます。

注目すべきコード②

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

    '列名を取得する表のセル範囲を取得
    lTblNM = "[" & sheetNM & "$" & lBgnPos & ":" & lLstPos & "]"
    
    '列名を取得する表のセル範囲を取得
    rTblNM = "[" & sheetNM & "$" & rBgnPos & ":" & rLstPos & "]"

以上のコードは、左の表と右の表のセル範囲を変数「lTblNM」「rTblNM」に格納しているコードです。

変数「lTblNM」「rTblNM」はSQL文のfrom句に指定するために使います。

コードの書き方は「シート名と表の範囲のセル」と記述します。

なお、コードだけでは分かりにくいと思うので、マクロ実行時の実際の値をお見せします。

[work$B2:H22]
[work$K2:Q22]

注目すべきコード③

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

    'インスタンスの生成
    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接続するためのコードになります。

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

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

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

注目すべきコード④

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

    '左の表と右の表を結合させるLeft Joinを使ってデータを取得するSelect文を用意する
    sqlStr = "select b.* from"
    sqlStr = sqlStr & " " & lTblNM & " AS a"
    sqlStr = sqlStr & " Left Outer Join"
    sqlStr = sqlStr & " " & rTblNM & " As b"
    sqlStr = sqlStr & " ON  a.名前 = b.名前"
    sqlStr = sqlStr & " where b.名前 <> ''"
        
    'Recordsetを開く
    rs.Open sqlStr, adodbCon, adOpenStatic

以上のコードは、左側の表と右側の表を、SQLのLeft Outer Joinを使って結合させるためのSQL文を用意しているコードです。

また、そのSQL文をrecordsetのOpenメソッドの引数に指定して実行しているコードです。

上記のSelect文で気を付ける点はfrom句の設定です。

表に対してデータを検索・抽出する場合は、fromに対して「シート名と表の範囲のセル」を指定する必要があります。

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

select
    b.*
from
    [work$B2:H22] AS a
    Left Outer Join
        [work$K2:Q22] As b
    ON  a.名前 = b.名前
where
    b.名前 <> ''

今回の表の場合、左の表と右の表を結合するのに参照するキー列は、左の表右の表共に名前になります。

ONの後に「a.名前 = b.名前」と記述し、名前が同じデータを条件にして結合しています。

また、今回は右の表のデータを右の表に出力したいので、右側の表データだけを取得するのにselectの後に「b.*」と記述しています。

「b.*」と記述することで右側の表データだけを取得することができます。(aは左側の表データのため不要)

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

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

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

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

注目すべきコード⑤

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

    '右側の表の生徒名が左側の表に存在しないデータ行を取得するSelect文を用意する
    sqlStr = "select * from"
    sqlStr = sqlStr & " " & rTblNM
    sqlStr = sqlStr & " where not exists"
    sqlStr = sqlStr & " ("
    sqlStr = sqlStr & " select"
    sqlStr = sqlStr & " 名前"
    sqlStr = sqlStr & " from"
    sqlStr = sqlStr & " " & lTblNM
    sqlStr = sqlStr & " where"
    sqlStr = sqlStr & " " & lTblNM & ".名前"
    sqlStr = sqlStr & " ="
    sqlStr = sqlStr & " " & rTblNM & ".名前"
    sqlStr = sqlStr & " )"
    sqlStr = sqlStr & " order by"
    sqlStr = sqlStr & " " & rTblNM & ".名前"
    sqlStr = sqlStr & " asc"
    
    'Recordsetを開く
    rs2.Open sqlStr, adodbCon, adOpenStatic

以上のコードは、左と右の表を結合し、左の表の生徒データのうち、右の表に存在しない生徒データを抽出するためのSQL文を用意しているコードです。

また、そのSQL文をrecordsetのOpenメソッドの引数に指定して実行しているコードです。

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

select
    *
from
    [work$K2:Q22]
where
    not exists(
        select
            名前
        from
            [work$B2:H22]
        where
            [work$B2:H22].名前 = [work$K2:Q22].名前
    )
order by
    [work$K2:Q22].名前 asc

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

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

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

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

注目すべきコード⑥

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

    '取得したデータを右表に貼り付ける
    Worksheets(sheetNM).Range("K" & rbgnRPos).CopyFromRecordset rs
    
    '取得したデータを右表に、先ほど貼り付けたデータの最後行の次行に貼り付ける
    Worksheets(sheetNM).Range("K" & rbgnRPos + CInt(rs.RecordCount)).CopyFromRecordset rs2

以上のコードは、SQL文を実行後に取得したデータを貼り付けるコードです。

90行目では、左と右の表を結合し、左と右の表のどちらにも存在する生徒データをセルに貼り付けています。

93行目では、左の表の生徒名が右の表に存在しない生徒データを、90行目で実行した後に貼り付けたデータの一つ下の行に貼り付けています。

以上のコードが実行されると、右の表の並びが、左の生徒名の並びと同じ並びで出力されます。

目次へ戻る

動作確認

マクロを実行した実行結果は、本記事内の独自の規則の並び方をしているデータをキーにソートするの内容をご覧ください。

目次へ戻る

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

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

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

  1. 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を使って表を結合させることで、表の列の値をキーに別の表データを同じに並び替えることができます。

SQL文を使って並び替える方法はあまり使う機会がないかもしれませんが、方法の一つとして知っておくと便利かもしれないので参考にしてみてくださいね。

プログラミングのスキルを習得するなら

プログラミングのスキルを習得したい、今のスキルをもっと高めたい、そう考えているなら「プログラミングスクール」がおすすめです。

プログラミングのスキルの基礎を身につけるなら「TechAcademy」で1週間の無料体験があるので、これで「プログラミングの基礎」を学ぶのにおすすめですよ。

→　TechAcademyの「1週間 無料体験」はこちら



目次へ戻る

前の記事

【ExcelVBA】Left Outer Joinを使ってEx…

次の記事

【ExcelVBA】自分自身のExcelのファイルを複製(コピ…