SQLでExcelの集計を簡単・楽しく

Excelで資料作成してますかぁー！！！
Excelでデータの加工してますかぁー！！
Excel大量のデータ管理してますか！
データの集計するのにマクロをたくさん書いてしんどいですか？
後で見返した時にコードの処理が何をしてるかわからない…？

そんな時はSQLですよ！

SQLとは
ADOとは
SQLを使用するメリット

SQLとは

SQLとはStructured Query Language(構造化問い合わせ言語)の略。
Access等のリレーショナル・データベースを操作するためのプログラミング言語です。
SQL - Wikipedia

データベースに対して - データ追加 - データ削除 - 検索 - 抽出 - 集計
などの指示を簡単に出すことができます。

SQLはリレーショナルデータベースのための言語なのに、表計算ソフトであるExcelで使うことができるのか？

VBEにそのままSQLのコードを記載しても使用することは出来ません。
ADOという機能を利用します。

ADOとは

ADOとはActiveX Data Objectsの略。
アプリケーションからAccess、SQL Serverをはじめ、Oracleなどのさまざまなデータベースへ同一の指示で処理を行うことができる。
コードを書く際にデータベースごとに書き分ける必要がないというメリットがあります。

ADOを経由することでExcelをデータベースと同様に扱うことができるようになり、SQLで指示を出すことが出来るようになります。

SQLというデータベース専用の言語を使用しなくても、VBAを使用すればデータの集計等が出来るのになぜSQLをオススメするのか。

次のようなメリットがあります。

SQLを使用するメリット

ExcelでSQLを使用するメリットはズバリこれです。

頭で考えたデータ集計を思ったままにコードにできる

SQLを利用することでデータの集計を、やりたいと頭の中で思っていることをそのままコード化でき、圧倒的に簡潔になります。

※余談ですが「簡潔」とは「表現が簡単で要を得(え)、無駄がないこと」だそうです。
SQLの説明としてこれ以上ないくらいピッタリですね

簡潔になることで

コードを書くことが簡単になる
コードを読むことが簡単になる

ゆえにストレス無くコードを短時間で処理できるようになります。

どの程度わかりやすく簡潔になるのか。
VBAで書く場合とSQLで書く場合を比較してみます。

まずは簡単な事例ですが、「テスト」シートのデータから「教科」列を転記したいとします。

列の転記 VBAの場合

Sub 列の転記_VBA版()
    Worksheets("テスト").Range("B:B").Copy Worksheets("テスト").Range("F1")
End Sub

１行で済む簡単なコードで実現できました。

列の転記 SQLの場合

Sub 列の転記_SQL版()
    Dim myCON As ADODB.Connection: Set myCON = New ADODB.Connection
    myCON.Provider = "Microsoft.ACE.OLEDB.12.0"
    myCON.Properties("Extended Properties") = "Excel 12.0;HDR=YES;IMEX=1;MAXSCANROWS=16"
    myCON.Properties("Data Source") = ThisWorkbook.FullName
    myCON.Open
        
    Dim mySQL As String
    mySQL = "select 教科 from [テスト$]"
    
    Dim myRS As ADODB.Recordset: Set myRS = New ADODB.Recordset
    With myRS
        .ActiveConnection = myCON
        .CursorType = adOpenStatic
        .Source = mySQL
    End With
    myRS.Open

    Dim i As Long
    Dim データ貼付セル As Range: Set データ貼付セル = Worksheets("テスト").Range("F1")
    For i = 1 To myRS.Fields.Count
        データ貼付セル.Offset(, i - 1).Value = myRS.Fields(i - 1).Name
    Next
    データ貼付セル.Offset(1).CopyFromRecordset myRS

    myRS.Close: Set myRS = Nothing
    myCON.Close: Set myCON = Nothing
End Sub

逃げないで！
どこが簡潔なんだ？と言いたいのはわかります。
しかし今回注目して頂きたいのはこちら。

mySQL = "select 教科 from [テスト$]"

上記コードの中でSQLの部分はここです。
SQL文だけだとVBA版と同じく一行で完結しています。

さらに注目して頂きたいのは、やりたいこと”「テスト」シートから「教科」列を抽出”ということがSQL文がわからなくても見えてきますよね。

もう１つの事例として「テスト」シートの「教科」別の「点数」合計点を集計して合計点の「昇順」に表示する例を取り上げます。

教科別点数 VBAの場合

パッと頭に浮かんだ方法で実現してみました。
流石に最初の例に比べると長くなりましたが、まだ短い部類ですね。

Sub 教科別点数_VBA版()
    With Worksheets("テスト")
        .Range("B:B").Copy Worksheets("テスト").Range("F1")
        .Range("F:F").RemoveDuplicates 1, xlYes
        .Range("G1").Value = "合計"
        .Range("G2", .Range("F2").End(xlDown).Offset(, 1)).Formula = _
            "=sumifs(" & Range("C:C").Address & "," & Range("B:B").Address & "," & Range("F2").Address(False, False) & ")"
        Range("F2").CurrentRegion.Sort Range("G1"), xlAscending, Header:=xlYes
    End With
End Sub

ところがですね。
このコードだけを見て何をしているかわかりますか？
コードの頭から順を追って何をしているか理解しながらでないと最終形がわからないと思います。

当然ながらデータの集計が複雑になる程、解読しなければいけないコードの量が増えていきます。

教科別点数 SQLの場合

SQLの場合のコードは以下のとおりです。

Sub 教科別点数_SQL版()
    Dim myCON As ADODB.Connection: Set myCON = New ADODB.Connection
    myCON.Provider = "Microsoft.ACE.OLEDB.12.0"
    myCON.Properties("Extended Properties") = "Excel 12.0;HDR=YES;IMEX=1;MAXSCANROWS=16"
    myCON.Properties("Data Source") = ThisWorkbook.FullName
    myCON.Open
        
    Dim mySQL As String
    mySQL = "select 教科,sum(点数) as 合計 from [テスト$] group by 教科 order by sum(点数) asc"
    
    Dim myRS As ADODB.Recordset: Set myRS = New ADODB.Recordset
    With myRS
        .ActiveConnection = myCON
        .CursorType = adOpenStatic
        .Source = mySQL
    End With
    myRS.Open

    Dim i As Long
    Dim データ貼付セル As Range: Set データ貼付セル = Worksheets("テスト").Range("F1")
    For i = 1 To myRS.Fields.Count
        データ貼付セル.Offset(, i - 1).Value = myRS.Fields(i - 1).Name
    Next
    データ貼付セル.Offset(1).CopyFromRecordset myRS

    myRS.Close: Set myRS = Nothing
    myCON.Close: Set myCON = Nothing
End Sub

やっぱり簡潔じゃないじゃないかと思われるかも知れません。
注目して頂きたいのはここ！

mySQL = "select 教科,sum(点数) as 合計 from [テスト$] group by 教科 order by sum(点数) asc"

実は1つ目のSQLのコードと、この２つ目のコードでは上記のSQLのコード部分以外は全く同じ内容なんです。

つまりデータの集計・加工の内容はこのSQLの部分だけを変更すれば良いということになります。

また読み解く必要があるSQL文も1つ目の例と同じく、やりたいことがそのままSQL文になっています。

やりたいこと	SQL
「テスト」シート	[テスト$]
「教科」別	group by 教科
「点数」の合計点	sum(点数)
合計点の昇順	order by sum(点数)
表示する	select 教科

このようにSQL文だけでデータの集計内容を表現することが出来るようになります。

ちなみにSQL文以外の部分はSQLを使うための定形の処理になっています。

定形の処理なのでアドイン化してやるとこのぐらいに短くなります。

Sub 教科別点数_自作アドイン版()
    Dim mySQL As String
    mySQL = "select 教科,sum(点数) as 合計 from [テスト$] group by 教科 order by sum(点数) asc"
    kf.SQL文の結果をセルに転記 ThisWorkbook.FullName, mySQL, True, Worksheets("テスト").Range("F1")
End Sub