【VBA】エクセルをSQLで読む　コーディング編

Excel VBAでSQLを使いたいと思ったことはありませんか？入出力の機能をVBAで、データの加工をSQLで行うと圧倒的に短いコードで実現することができます。
ExcelでSQL 準備編からの続きです。
（2024.9.29 比較対象として、生成AIで作ったSQLなしバージョンも追記しました。）

【VBA】エクセルをSQLで読む　テストデータ準備編

Excel VBAでSQLを使いたいと思ったことはありませんか？入出力の機能をVBAで、データの加工をSQLで行うと圧倒的に短いコードで実現できます。そしてソースコードが短ければバグが入り込むリスクも少なく済みます。

gizmiration.com

2024.06.01

エクセルでSQL 目指す姿

このデータ数ならエクセル関数でも、SQLを使わないVBAでも可能です。しかしこれが一万行あったら話は別です。一発の計算で済むSQLの反応はそれらとは比較になりません。
またSQLを使わないVBAに比べてコードの量が少なく済みます。コード量が少ないイコールバグが入り込む余地も少ないといえます。

エクセルでSQL ソースコード

シート「result」を作って下記のコードを書きました。resultシートに集計結果が作成されます。

Option Explicit
Sub makeresult()
    Const adopenKeyset = 1
    Const adLockReadOnly = 1

    Dim cn As Object
    Dim rs As Object

    Set cn = CreateObject("ADODB.Connection")
    Set rs = CreateObject("ADODB.Recordset")
    cn.Provider = "Microsoft.ACE.OLEDB.12.0"
    cn.Properties("Extended Properties") = "Excel 12.0;HDR=YES;IMEX=1"
    cn.Open ThisWorkbook.FullName
    
    Dim strSQL As String
    strSQL = ""
    strSQL = strSQL & "SELECT "
    strSQL = strSQL & "       A.[伝票番号]"
    strSQL = strSQL & "      ,A.[品番]"
    strSQL = strSQL & "      ,B.[品名]"
    strSQL = strSQL & "      ,A.[個数] * B.[単価]"
    strSQL = strSQL & "  FROM [売り上げ$] A"
    strSQL = strSQL & "  LEFT JOIN [品名マスター$] B ON A.品番 = B.品番 "
    
    rs.Open strSQL, cn, adopenKeyset, adLockReadOnly
    Cells(1, "A").CopyFromRecordset rs
    
    cn.Close
    Set rs = Nothing
    Set cn = Nothing
End Sub

「売上」シートの内容に品名を紐付けてC列に表示し、D列には 単価×売れた個数 を表示しています。

SQLで紐付けているので、「品番が一致していれば単価と個数を掛け算、データの終わりまで処理」というコードを作る必要はありません。

まとめ　SQLはレスポンスが良い

vlookup関数やVBAのloopだと、例えば一万行くらいのデータの場合にレスポンスが悪くなり実用に耐えられませんが、SQLならデータ数あまり影響されずに動きます。

【VBA】エクセルをSQLで読む　テストデータ準備編

Excel VBAでSQLを使いたいと思ったことはありませんか？入出力の機能をVBAで、データの加工をSQLで行うと圧倒的に短いコードで実現できます。そしてソースコードが短ければバグが入り込むリスクも少なく済みます。

gizmiration.com

2024.06.01

おまけ（SQLを使用しないバージョン）

試しに生成AIで、SQLを使用しないバージョンを作ってコードの量を比べてみました。
SQLバージョンが31行、こちらが40行、差は3割くらいでした。

ソースコードの量も処理速度も、今回の例では圧倒的というほどの違いではありませんでしたが、データ数と処理の複雑度が上がるにつれ、この差は顕著になっていきます。

Option Explicit
Sub MergeSalesDataWithAmount()
    Dim wsMaster As Worksheet
    Dim wsSales As Worksheet
    Dim wsOutput As Worksheet
    Dim lastRowMaster As Long
    Dim lastRowSales As Long
    Dim i As Long
    Dim j As Long
    Dim found As Range

    ' シートの設定
    Set wsMaster = ThisWorkbook.Sheets("品名マスタ")
    Set wsSales = ThisWorkbook.Sheets("売り上げデータ")
    Set wsOutput = ThisWorkbook.Sheets.Add(After:=ThisWorkbook.Sheets(ThisWorkbook.Sheets.Count))
    wsOutput.Name = "売り上げ結果"

    ' 最終行の取得
    lastRowMaster = wsMaster.Cells(wsMaster.Rows.Count, 1).End(xlUp).Row
    lastRowSales = wsSales.Cells(wsSales.Rows.Count, 1).End(xlUp).Row

    ' 売り上げデータのループ
    For i = 2 To lastRowSales
        ' 品番を検索
        Set found = wsMaster.Columns(1).Find(What:=wsSales.Cells(i, 2).Value, LookIn:=xlValues, LookAt:=xlWhole)
        If Not found Is Nothing Then
            ' 出力シートにデータを転記
            j = wsOutput.Cells(wsOutput.Rows.Count, 1).End(xlUp).Row + 1
            wsOutput.Cells(j, 1).Value = wsSales.Cells(i, 1).Value ' 日付
            wsOutput.Cells(j, 2).Value = wsSales.Cells(i, 2).Value ' 品番
            wsOutput.Cells(j, 3).Value = found.Offset(0, 1).Value ' 品名
            wsOutput.Cells(j, 4).Value = found.Offset(0, 2).Value * wsSales.Cells(i, 3).Value ' 売上金額
        End If
    Next i

    MsgBox "データの紐づけと売上金額の計算が完了しました。", vbInformation
End Sub

これを生成したプロンプト。

次の売り上げデータに品名マスタの品名と数量を紐づけ、かつ単価×数量で売上金額を計算するVBAを作ってください。出力する項目は、日付、品番、品名、売上金額の順で、タイトルは不要です。

<品名マスタ>
品番, 品名, 単価
12345-67890,トイレットペーパー,300
23456-78901,歯ブラシ,200
34567-89012,洗剤,500
45678-90123,シャンプー,700
56789-01234,リンス,700
67890-12345,ボディソープ,600
78901-23456,洗濯洗剤,800
89012-34567,食器用洗剤,300
90123-45678,スポンジ,100
01234-56789,雑巾,150
11234-66789,掃除機,20000
22345-77890,エアコン,30000
33456-88901,冷蔵庫,50000
44567-99012,洗濯機,40000
55678-00123,炊飯器,13000
66789-11234,電子レンジ,8000
77890-22345,トースター,5000
88901-33456,ミキサー,3500
99012-44567,フライパン,2500
00123-55678,包丁,3000

<売り上げデータ>
日付, 品番, 数量
1001,12345-67890,10
1001,23456-78901,15
1001,34567-89012,7
1001,45678-90123,12
1001,56789-01234,9
1002,67890-12345,14
1002,78901-23456,11
1002,89012-34567,13
1002,90123-45678,8
1002,01234-56789,10
1003,11234-66789,5
1003,22345-77890,4
1003,33456-88901,3
1003,44567-99012,2
1003,55678-00123,6
1004,66789-11234,7
1004,77890-22345,8
1004,88901-33456,9
1004,99012-44567,10
1004,00123-55678,11
1005,12345-67890,12
1005,23456-78901,13
1005,34567-89012,14
1005,45678-90123,15
1005,56789-01234,16
1006,67890-12345,17
1006,78901-23456,18
1006,89012-34567,19
1006,90123-45678,20
1006,01234-56789,21
1007,11234-66789,22
1007,22345-77890,23
1007,33456-88901,24
1007,44567-99012,25
1007,55678-00123,26
1008,66789-11234,27
1008,77890-22345,28
1008,88901-33456,29
1008,99012-44567,30
1008,00123-55678,31
1009,12345-67890,32
1009,23456-78901,33
1009,34567-89012,34
1009,45678-90123,35
1009,56789-01234,36
1010,67890-12345,37
1010,78901-23456,38
1010,89012-34567,39
1010,90123-45678,40
1010,01234-56789,41