この記事では、SQLのUNION ALLで複数のシートデータを統合し対象のデータを取得する方法についてご説明します。
【動画】SQLのUNION ALLで複数のシートデータを統合し対象のデータを取得する実際の動き
本題に入る前に、まずは次の動画をご覧ください。
マクロが自分自身のExcelファイルに接続して3つのシートにある表をSQLのUNION ALLで統合し、SELECT文で対象のデータを検索・抽出しています。
マクロ作成の流れ
SQLのUNION ALLで複数のCSVファイルのデータを統合し対象のデータを取得するSelect文を生成します。
Select文が実行されると、Excelのシートにデータが貼り付けられます。
Excelファイルの例
今回は次のExcelファイルを用意しました。
取得先のデータは「1月」「2月」「3月」の3シートの表にあるデータになります。
以上の3シートの表をUNION ALLで結合させ、検索対象の値を条件にデータをSELECT文で取得します。
コードの例
Option Explicit
Private Sub btn_exec_Click()
Dim adodbCon As ADODB.Connection 'Connection用変数
Dim rs As ADODB.Recordset 'レコードセット用変数
Dim sqlStr As String 'SQL文用変数
Dim ws As Worksheet 'シート用変数
Dim cnt As Integer 'カウンタ
Dim fieldType As Long '列の型
Dim field As ADODB.field '列
'データを出力するセルをクリアする
Worksheets("top").Range("D2:J1000").ClearContents
'Connectionインスタンスの生成
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
For Each ws In Worksheets
If ws.Name <> "top" Then
'シート名が「top」以外の場合
'データの型を調べるため、1件だけデータを取得する
sqlStr = "SELECT TOP 1 * FROM " & " [" & ws.Name & "$]"
Exit For
End If
Next
'Recordsetオブジェクトのインスタンスを生成する
Set rs = New ADODB.Recordset
'カーソルタイプにキーセットカーソル使用
rs.CursorType = adOpenDynamic
'SQL文を実行する
Set rs = adodbCon.Execute(sqlStr)
'ここからデータを取得するSQL文を作成する
'UNIONした結果に対してSelect文を実行するために以下の「select distinct * from」を用意する
sqlStr = "select distinct * from ("
For Each ws In Worksheets
If ws.Name <> "top" Then
'シート名が「top」以外の場合
If cnt > 0 Then
'2件目のSELECT文の前にUnion ALLを付与する
sqlStr = sqlStr & " Union ALL"
End If
sqlStr = sqlStr & " select"
sqlStr = sqlStr & " *"
sqlStr = sqlStr & " from "
sqlStr = sqlStr & " [" & ws.Name & "$]"
cnt = cnt + 1
End If
If fieldType = 0 Then
'フィールドの型を格納する変数がブランクの場合
'フィールドの型を取得します。
For Each field In rs.Fields
If ws.Range("searchFld").Value = field.Name Then
'検索項目に入力された列名と、表の列名が一致した場合
'列の型を取得する
fieldType = field.Type
Exit For
End If
Next field
End If
Next ws
'UNIONした結果に対してSelect文を実行するための閉じ括弧
sqlStr = sqlStr & ")"
'WHERE句(「検索対象の値」)
sqlStr = sqlStr & " where "
sqlStr = sqlStr & Worksheets("top").Range("searchFld").Value
Select Case fieldType
Case adVarWChar
'「検索項目」に入力された項目のデータが文字列の場合
'WHERE句(「検索対象の値」)
sqlStr = sqlStr & " = '" & Worksheets("top").Range("searchVal").Value & "'"
Case adDouble
'「検索項目」に入力された項目のデータが数値の場合
If isNumeric(Worksheets("top").Range("searchVal").Value) Then
'「検索対象の値」に入力された値が数値の場合
'WHERE句(「検索対象の値」)
sqlStr = sqlStr & " = " & CLng(Worksheets("top").Range("searchVal").Value)
Else
'「検索対象の値」に入力された値が文字列の場合
MsgBox "検索項目には" & field.Name & "が入力されているので、" & Chr(10) & "「検索対象の値」のセルには文字列の値ではなく数値型の値を入力して再実行してください。" & Chr(10) & _
"処理を終了します。"
'後処理
adodbCon.Close
Set rs = Nothing
Set adodbCon = Nothing
'処理を終了する
Exit Sub
End If
Case Else
End Select
'recordsetを閉じる
rs.Close
'SELECT文を実行してRecordsetを開く
rs.Open sqlStr, adodbCon, adOpenStatic
If rs.RecordCount = 0 Then
'対象データの件数が0件の場合
MsgBox "データがありませんでした。"
End If
'データをシート「top」に貼り付ける
Worksheets("top").Range("D2").CopyFromRecordset rs
'後処理
'recordsetを閉じる
rs.Close
'Connectionインスタンスを閉じる
adodbCon.Close
Set rs = Nothing
Set adodbCon = Nothing
End Sub
注目すべきコード①
最初に見て頂きたいのは17行目から29行目です。
'Connectionインスタンスの生成
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ファイルに接続するための接続情報を取得して接続するコードです。
コードの詳細
17行目のコードでは、Connectionインスタンスを生成します。
このインスタンスがないとマクロが自分自身のExcelファイルに接続することができないので必ず生成しておきます。
22行目から24行目で接続情報を取得し、27行目でOpenメソッドを実行してマクロが自分自身のExcelファイルに接続します。
注目すべきコード②
次に見て頂きたいのは32行目から41行目です。
For Each ws In Worksheets
If ws.Name <> "top" Then
'シート名が「top」以外の場合
'データの型を調べるため、1件だけデータを取得する
sqlStr = "SELECT TOP 1 * FROM " & " [" & ws.Name & "$]"
Exit For
End If
Next
'Recordsetオブジェクトのインスタンスを生成する
Set rs = New ADODB.Recordset
'カーソルタイプにキーセットカーソル使用
rs.CursorType = adOpenDynamic
'SQL文を実行する
Set rs = adodbCon.Execute(sqlStr)
コードの説明
以上のコードは、シートにある表の列名とその列の型を取得するために一度SELECT文を実行している処理のコードです。
列の型を取得するには、表に対して一度SELECT文を実行する必要があります。(SELECT文を実行せずに、列の型だけを直接取得することはできないため)
SELECT文を実行すると、recordsetにデータが取得されて、その中のTypeプロパティから列の型を取得することができます。
Typeプロパティから列の型を取得する処理は、「注目すべきコード④」でご説明します。
コードの詳細
31行目のコードは、Excelファイルの中のシート全てを参照するためのFor文で、33行目のIFでシートが「top」でないかを判定します。
なぜExcelファイルの中のシート全てを参照し、シートが「top」でないかを判定するのかというと、「top」以外のシートにデータのある表が存在しているからです。
データのある表だけを参照したいので、「top」のシートは対象外としています。
(※今回のサンプルではあくまで「top」以外は全てデータ用のシートとしているため)
38行目のコードは、データのある表のシート名をSELECT文のFROM句に指定してSELECT文を実行しています。
データのある表のシート名をSELECT文のFROM句に指定してSELECT文を実行することで、列の型を取得することができるようになります。
なぜSELECT文に「TOP 1」と記述しているのかというと、SELECT文の実行完了に時間をかけたくないからです。
(※もしシートに何万件もデータがあったら無駄に時間がかかってしまいます)
「TOP 1」だと1件だけのデータ抽出になるので無駄に処理時間をかけなくて済みます。
あくまで列名が欲しくてSELECTを実行しているのでとりあえず今回は「TOP 1」を記述しています。
47行目のコードではRecordsetオブジェクトのインスタンスを生成し、50行目でカーソルタイプにキーセットカーソル使用します。
53行目のコードでは、38行目のSELECT文を実行します。
注目すべきコード③
次に見て頂きたいのは60行目から77行目です。
For Each ws In Worksheets
If ws.Name <> "top" Then
'シート名が「top」以外の場合
If cnt > 0 Then
'2件目のSELECT文の前にUnion ALLを付与する
sqlStr = sqlStr & " Union ALL"
End If
sqlStr = sqlStr & " select"
sqlStr = sqlStr & " *"
sqlStr = sqlStr & " from "
sqlStr = sqlStr & " [" & ws.Name & "$]"
cnt = cnt + 1
End If
コードの説明
以上のコードは、SQLのUNION ALLで複数のシートデータを統合し対象のデータを取得するためのSELECT文を組み立てているコードです。
コードの詳細
60行目のコードは、Excelファイルの中のシート全てを参照するためのFor文で、62行目のIFでシートが「top」でないかを判定します。
「top」はデータの表があるシートではないので、シートが「top」の場合はUNION ALLで表を結合する処理は対象外とし、「top」以外の場合は65行目のコードに進みます。
65行目のコードは、1つ目のSELECT文の次から、2つ目以降のSELECT文にUnion ALLを付与するかどうか、カウント数を判定します。
cntが0の場合は1つ目のSELECT文なので、Union ALLは付与せず2つ目からのSELECT文の前にUnion ALLを付与します。
Union ALLを付与し終わったSELECT文は次の通りです。
select distinct
*
from
(
select
*
from
[1月$]
Union ALL
select
*
from
[2月$]
Union ALL
select
*
from
[3月$]
注目すべきコード④
次に見て頂きたいのは82行目から102行目です。
If fieldType = 0 Then
'フィールドの型を格納する変数がブランクの場合
'フィールドの型を取得します。
For Each field In rs.Fields
If ws.Range("searchFld").Value = field.Name Then
'検索項目に入力された列名と、表の列名が一致した場合
'列の型を取得する
fieldType = field.Type
Exit For
End If
Next field
End If
コードの説明
以上のコードは、「検索項目」に入力された値が、シートの表にあるデータではどの型なのかを取得する処理のコードです。
例えば今回のサンプルでは、A列とB列の「月」「名前」は文字列型で、C列の「国語」からG列の「英語」までは数値型です。
もし「検索項目」に「名前」を入力した場合は94行目のTypeプロパティから「文字列型」を指す「202」の値を取得することができます。
これは、「名前」の列が「文字列型」であることを意味します。
「検索項目」に「国語」を入力した場合は94行目のTypeプロパティから「数値型」を指す「5」の値を取得することができます。
これは、「国語」の列が「数値型」であることを意味します。
コードの詳細
82行目のコードは、fieldTypeの値が0であるかを判定しているIF文のコードです。
列の型が取得できたらfieldTypeには列の型を格納しますが(94行目)、0だと列の型が取得はまだ取得されていないことを表します。
列の型を取得するには、「検索項目」に入力された値と、表の列全てを突き合わせて(87行目のFor文内で行う)、もし合致したら94行目で列の型をfieldTypeに格納します。
列の型が1度取得できたら(94行目)、96行目で87行目のFor文のループ処理を抜けます。
注目すべきコード⑤
次に見て頂きたいのは110行目から152行目です。
'WHERE句(「検索対象の値」)
sqlStr = sqlStr & " where "
sqlStr = sqlStr & Worksheets("top").Range("searchFld").Value
Select Case fieldType
Case adVarWChar
'「検索項目」に入力された項目のデータが文字列の場合
'WHERE句(「検索対象の値」)
sqlStr = sqlStr & " = '" & Worksheets("top").Range("searchVal").Value & "'"
Case adDouble
'「検索項目」に入力された項目のデータが数値の場合
If isNumeric(Worksheets("top").Range("searchVal").Value) Then
'「検索対象の値」に入力された値が数値の場合
'WHERE句(「検索対象の値」)
sqlStr = sqlStr & " = " & CLng(Worksheets("top").Range("searchVal").Value)
Else
'「検索対象の値」に入力された値が文字列の場合
MsgBox "検索項目には" & field.Name & "が入力されているので、" & Chr(10) & "「検索対象の値」のセルには文字列の値ではなく数値型の値を入力して再実行してください。" & Chr(10) & _
"処理を終了します。"
'後処理
adodbCon.Close
Set rs = Nothing
Set adodbCon = Nothing
'処理を終了する
Exit Sub
End If
Case Else
End Select
コードの説明
以上のコードは、SELECT文のWHERE句を組み立てる処理のコードです。
コードの詳細
110行目のコードはWhere句の「where」を、111行目のコードは検索対象の列名を指定しています。
113行目のコードでは、「注目すべきコード④」で取得した「field.Type」の値(変数fieldTypeに格納されている)を判定するSelect Case文を用意しています。
115行目のコードでは、変数fieldTypeの値がadVarWChar(文字列型)の場合かどうかを判定します。
もし合致している場合は、120行目で検索条件に「検索対象の値」のセルに入力された値を指定します。
(※文字列なので、「検索対象の値」を「’」(シングルクォーテーション)で囲みます)
122行目のコードでは、変数fieldTypeの値がadDouble(数値型(倍精度浮動小数点値))の場合かどうかを判定します。
もし合致している場合は、126行目で「検索対象の値」のセルに入力された値が数値なのか判定します。
もし数値の場合は、131行目で検索条件に「検索対象の値」のセルに入力された値を指定します。
数値以外の場合は、fieldTypeの値が数値型なので、数値以外を条件に指定することはできません。
そのままSELECT文を実行するとエラーになってしまうので、処理を強制終了します。(数値型の列に対して文字列型を指定する条件はエラーになる)
処理を強制終了するのに、まずは処理を強制終了する旨のメッセージを137行目138行目で出力させて、146行目で処理を終了させます。
注目すべきコード⑥
次に見て頂きたいのは155行目です。
'recordsetを閉じる
rs.Close
コードの説明
以上のコードは、recordsetを閉じる処理のコードです。
ここでなぜRecordsetを閉じているのかというと、ここまで組み立ててきたUNION ALLを使ったSELECT文に必要な列の型を取得するのにrecordsetを参照しており、SELECT文の組み立てが終わったのでそのSELECT文を実行したいからです。
ここまででRecordsetが開きっぱなしだったのですが、組み立てたSELECT文を実行するのに再度Recordsetを開く必要があるのでここでRecordsetを閉じます。
注目すべきコード⑦
次に見て頂きたいのは158行目から169行目です。
'SELECT文を実行してRecordsetを開く
rs.Open sqlStr, adodbCon, adOpenStatic
If rs.RecordCount = 0 Then
'対象データの件数が0件の場合
MsgBox "データがありませんでした。"
End If
'データをシート「top」に貼り付ける
Worksheets("top").Range("D2").CopyFromRecordset rs
コードの説明
以上のコードは、Recordsetを開いてSELECT文を実行し、取得したデータを「top」のシートに出力する処理のコードです。
もし0件だった場合は「データがありませんでした。」とメッセージを出力します。
コードの詳細
158行目のコードでは、SELECT文をOpenメソッドの引数に指定して実行しています。
実行後は169行目でSELECT文で取得したデータをシートに出力します。
160行目のコードでは、データ件数が0件かどうか判定し、もしデータ件数が0件の場合は164行目で「データがありませんでした。」とメッセージを出力します。
動作確認
マクロ実行前
今回は以下のExcelファイルを用意しました。
「top」のシートの「検索項目」のセルには「名前」、「検索対象の値」のセルには「生徒1」が入力されています。
マクロ実行後
マクロを実行すると、下の通りに「名前」の列の値に「生徒1」が入っているデータが「top」のシートに出力されました。
【注意】参照設定が必要です
一つ注意点があるのですが、先ほどのコードを動かすには参照設定が必要です。
参照設定の一覧(下の画像を参考)から次の項目(ライブラリ)にチェックを付けて「OK」ボタンをクリックします。
- Microsoft ActiveX Data Objects 2.8 Library(msado28.tlb)
なぜ必要かというと、Excelのマクロのコードの5行目の「ADODB.Connection」と6行目の「ADODB.Recordset」、11行目の「ADODB.field」というオブジェクトが「msado28.tlb」というファイルを参照するからです。
Dim adodbCon As ADODB.Connection 'Connection用変数
Dim rs As ADODB.Recordset 'レコードセット用変数
Dim field As ADODB.field '列
この参照設定をしないと下の画像のエラーが出ますので必ず行う必要があります。
ここでは「msado28.tlb」とは何者かについては記事の本題から逸れてしまうので詳細は割愛しますが、マクロで「ADODB.Connection」「ADODB.Recordset」「ADODB.field」というオブジェクトを使う場合は参照設定しないと動かない、程度に思って頂ければと思います。
最後に
本記事では、SQLのUNION ALLで複数のシートデータを統合し対象のデータを取得する方法についてご説明しました。
Excelにある複数のシートの表からデータを抽出したい時は本記事を参考にしてみてくださいね。
プログラミングのスキルを習得するなら
プログラミングのスキルを習得したい、今のスキルをもっと高めたい、そう考えているなら「プログラミングスクール」がおすすめです。
プログラミングのスキルの基礎を身につけるなら「TechAcademy」で1週間の無料体験があるので、これで「プログラミングの基礎」を学ぶのにおすすめですよ。
