データクレンジングやデータ加工の時に、大胆なデータハンドリングが求められませんか？そんな時によく使われるのが、統計解析ソフトだと思います。代表的なのが「R」や「SAS」だと思います。Rはまだ無償で使えますが、SASとかの専用の統計解析ソフトだと、高額だったりします。またRであっても相手に同様の環境が整っていないと、その解析結果を共有できないですよね。

そこで私が編み出した、独自のSQL駆動エンジンを作りました。それは、Excelだけがあれば十分です。またPowerQueryのようにテーブルを設計する必要もありません。それは、あたかも統計解析ソフトSASのData Stepのように自在にデータハンドリング、データクレンジングが実現可能です。今から、その核となるコードを紹介していきます。

Sub sql_nn_01(bk As Workbook, sh As Worksheet, sql_code, bk_name)
'
'

'ブックとシートをアクティベート
bk.Activate
sh.Activate

'SQLの実行結果を出力するシートをリセットする
num = sh.UsedRange.Rows.Count
sh.Rows("1:" & (num + 3)).Delete shift:=xlUp

'SQL参照元のファイル名と同じファイルを閉じる
For Each bb In Workbooks
If bb.Name <> ThisWorkbook.Name And bb.Name <> bk.Name Then
If bb.Name = bk_name Then
bb.Close SaveChanges:=False
End If
End If
Next
DoEvents
'SQLを実行する核となる部分
    With sh.ListObjects.Add(SourceType:=0, Source:=Array(Array( _
        "ODBC;DSN=Excel Files;DBQ=" & ThisWorkbook.Path & "\" & bk_name & ";DefaultDir=" & ThisWorkbook.Path & ";DriverId=1046;MaxBu" _
        ), Array("fferSize=2048;PageTimeout=5;")), Destination:=Range("$A$1")). _
        QueryTable
'        .CommandType = 0
        .CommandText = sql_code
        .RowNumbers = False
        .FillAdjacentFormulas = False
        .PreserveFormatting = True
        .RefreshOnFileOpen = False
        .BackgroundQuery = False
        .RefreshStyle = xlInsertDeleteCells
        .SavePassword = True
        .SaveData = True
        .AdjustColumnWidth = True
        .RefreshPeriod = 0
        .PreserveColumnInfo = True
        .ListObject.DisplayName = "Query_Name"
        .Refresh BackgroundQuery:=False
    End With
DoEvents
'ListObjectを全て削除する
For Each ob In sh.ListObjects
ob.Unlist
Next

'SQL参照元のファイル名と同じファイルを閉じる（２回目）
For Each bb In Workbooks
If bb.Name <> ThisWorkbook.Name And bb.Name <> bk.Name Then
If bb.Name = bk_name Then
bb.Close SaveChanges:=False
End If
End If
Next

End Sub

上記のコードにより、Excel単体でSQLを実行することができます。特にこのコードは、他のExcelファイルをSQLの参照先と見なして、コードを実行することができます。さらに特徴的なのは、同一シート内に複数の表がある場合でも、別々に表をSQLで処理できるという、とても便利なことができます。関数の引数については、次の通りです。

上記のコードは、もともとMicrosoft QueryをExcel内で実行する時のマクロ記録が土台となっています。ただ、記録したマクロをそのまま実行してもエラーになってしまうので、使える形に編集とチューニングを重ねたのが、上記コードになっています。そして今では、私がデータクレンジングやハンドリングを行う際の、とても強力な相棒となっています。

さて、今度はこのSQL駆動エンジンの使用例を解説していきます。まずは下記のコードをご覧ください。

Sub test_code()

fil_name = "test_file.xlsx"
fil_path = ThisWorkbook.Path & "\" & fil_name
Call pre_delete(fil_path)

Dim bk As Workbook
Set bk = Workbooks.Add
Call data_entry(bk)
    bk.SaveAs Filename:=fil_path, FileFormat:= _
        xlOpenXMLWorkbook, CreateBackup:=False

Call sql_part(bk)

bk.Close SaveChanges:=False
End Sub
Sub pre_delete(fil_path)

Dim sys
Set sys = CreateObject("scripting.filesystemobject")
If sys.fileexists(fil_path) Then
Dim fil
Set fil = sys.getfile(fil_path)
fil.Delete
End If

End Sub
Sub data_entry(bk As Workbook)
Dim sh As Worksheet

Set sh = bk.Worksheets(1)
sh.Name = "raw_data"

data_set = Array( _
Array("地名", "属性", "数値"), _
Array("中央区", "A", "2"), _
Array("北区", "B", "31"), _
Array("南区", "B", "74"), _
Array("東区", "A", "2748"), _
Array("中央区", "C", "4"), _
Array("南区", "B", "98"), _
Array("北区", "A", "563"), _
Array("西区", "C", "85"), _
Array("東区", "A", "74"), _
Array("北区", "D", "8"), _
Array("中央区", "C", "437"))

i = 1
j = 1
For Each dd In data_set
j = 1
For Each dd1 In dd
sh.Cells(i, j).Value = dd1
j = j + 1
Next
i = i + 1
Next

Set sh = bk.Worksheets.Add(after:=bk.Worksheets(1))
sh.Name = "raw_data2"

data_set = Array( _
Array("地名", "地域番号"), _
Array("中央区", "N002"), _
Array("北区", "N003"), _
Array("南区", "N003"), _
Array("東区", "N002"), _
Array("西区", "N001"))

i = 1
j = 1
For Each dd In data_set
j = 1
For Each dd1 In dd
sh.Cells(i, j).Value = dd1
j = j + 1
Next
i = i + 1
Next

End Sub
Sub sql_part(bk As Workbook)

Dim sh As Worksheet

'元データから、属性が「A」のものだけ抽出
sql_code = Array( _
"select 地名, 属性, 数値" & Chr(13) & "" & Chr(10), _
"from `" & bk.Worksheets(1).Name & "$`" & Chr(13) & "" & Chr(10), _
"where 属性='A'")
Set sh = bk.Worksheets.Add(after:=bk.Worksheets(bk.Worksheets.Count))
sh.Name = "only_A"
Call sql_nn_01(bk, sh, sql_code, bk.Name)

'元データから、数値が「100以上」のものだけ抽出
sql_code = Array( _
"select 地名, 属性, 数値" & Chr(13) & "" & Chr(10), _
"from `" & bk.Worksheets(1).Name & "$`" & Chr(13) & "" & Chr(10), _
"where 数値>=100")
Set sh = bk.Worksheets.Add(after:=bk.Worksheets(bk.Worksheets.Count))
sh.Name = "over_100"
Call sql_nn_01(bk, sh, sql_code, bk.Name)

'元データの地名を地域番号に置き換える
sql_code = Array( _
"select b.地域番号, a.属性, a.数値" & Chr(13) & "" & Chr(10), _
"from `" & bk.Worksheets(1).Name & "$` as a left outer join `" & bk.Worksheets(2).Name & "$` as b" & Chr(13) & "" & Chr(10), _
"on a.地名=b.地名")
Set sh = bk.Worksheets.Add(after:=bk.Worksheets(bk.Worksheets.Count))
sh.Name = "local_num"
Call sql_nn_01(bk, sh, sql_code, bk.Name)

'新しいデータシートを参照するので、一度保存
bk.Save

'地名ごとのデータ数を計算
sql_code = Array( _
"select 地名, count(属性) as `データ数`" & Chr(13) & "" & Chr(10), _
"from `" & bk.Worksheets(1).Name & "$`" & Chr(13) & "" & Chr(10), _
"group by 地名")
Set sh = bk.Worksheets.Add(after:=bk.Worksheets(bk.Worksheets.Count))
sh.Name = "data_count"
Call sql_nn_01(bk, sh, sql_code, bk.Name)

'地名ごとのスコア（数値の合計）を計算
sql_code = Array( _
"select 地名, sum(数値) as `スコア`" & Chr(13) & "" & Chr(10), _
"from `" & bk.Worksheets(1).Name & "$`" & Chr(13) & "" & Chr(10), _
"group by 地名")
Set sh = bk.Worksheets.Add(after:=bk.Worksheets(bk.Worksheets.Count))
sh.Name = "score_sum"
Call sql_nn_01(bk, sh, sql_code, bk.Name)

'地域番号ごとのデータ数を計算
sql_code = Array( _
"select 地域番号, count(属性) as `データ数`" & Chr(13) & "" & Chr(10), _
"from `local_num$`" & Chr(13) & "" & Chr(10), _
"group by 地域番号")
Set sh = bk.Worksheets.Add(after:=bk.Worksheets(bk.Worksheets.Count))
sh.Name = "local_count"
Call sql_nn_01(bk, sh, sql_code, bk.Name)

'地域番号ごとのスコア（数値の合計）を計算
sql_code = Array( _
"select 地域番号, sum(数値) as `スコア`" & Chr(13) & "" & Chr(10), _
"from `local_num$`" & Chr(13) & "" & Chr(10), _
"group by 地域番号")
Set sh = bk.Worksheets.Add(after:=bk.Worksheets(bk.Worksheets.Count))
sh.Name = "local_sum"
Call sql_nn_01(bk, sh, sql_code, bk.Name)

'地名と属性のクロス集計
sql_code = Array( _
"transform sum(数値) " & Chr(13) & "" & Chr(10), _
"select 地名" & Chr(13) & "" & Chr(10), _
"from `" & bk.Worksheets(1).Name & "$`" & Chr(13) & "" & Chr(10), _
"group by 地名" & Chr(13) & "" & Chr(10), _
"pivot 属性")
Set sh = bk.Worksheets.Add(after:=bk.Worksheets(bk.Worksheets.Count))
sh.Name = "cross_01"
Call sql_nn_01(bk, sh, sql_code, bk.Name)

'地域番号と属性のクロス集計
sql_code = Array( _
"transform sum(数値) " & Chr(13) & "" & Chr(10), _
"select 地域番号" & Chr(13) & "" & Chr(10), _
"from `local_num$`" & Chr(13) & "" & Chr(10), _
"group by 地域番号" & Chr(13) & "" & Chr(10), _
"pivot 属性")
Set sh = bk.Worksheets.Add(after:=bk.Worksheets(bk.Worksheets.Count))
sh.Name = "cross_02"
Call sql_nn_01(bk, sh, sql_code, bk.Name)

bk.Save
End Sub

マクロ有効ファイルを作成して保存し、上記コードをVBAの標準モジュールに貼り付けます。そして先に出てきたSQL駆動エンジンのコードを別のモジュールに貼り付けると、SQLを実行できる状態になります。これを動かすには、一つ目のモジュールの「test_code()」を実行します。すると、「data_entry」関数で生成されたデータが、「sql_part」で指定されたSQLコードに従って処理されていきます。そして、マクロ有効ファイルと同じフォルダに「test_file.xlsx」としてSQL処理結果が保存されます。

さて、それではSQL駆動エンジンの使い方を、上記コードを元に説明していきます。まずは「sql_part」という部分を見れください。すると、以下のようなパターンがあります。

上記のようにしてSQL実行結果の出力先を「bk」と「sh」で指定します。「bk_name」にはSQLの参照先Excelファイルの名前を入力します。そしてSQLコードは「sql_code」に格納します。格納の際は、文字変数の配列とします(Array関数を使って、文字列配列を作っています)。また、SQLコードを改行する際は、「Chr(13) & “” & Chr(10)」を文字列の末尾に付加し、カンマで区切って、配列の新しい文字列要素を続けて記載していきます。SQLのfrom句にはテーブル名として参照先Excelファイルの「シート名$」と入力します。ここで大切なのが、シート名の末尾に「$」と入力することです。なぜこのような文字を末尾に付加するのか理由は分かりませんが、統計解析ソフトSASで Excelファイルを取り込む時も同じような記載方法だったことは確かです。またここで面白い記載方法があります。一つのシートの中の一部分だけをテーブルとして認識させる方法です。それは以下のように記載します。

このように記載することで、一つのシートの中に複数の表があったとしても、問題なく別々の処理を行うことができます。おそらく、これはRやSAS、Pythonなどでは難しいことだと思います。そもそもがExcelだからこそできる柔軟な対応だと思います。
あとは通常のSQLのルール(Accessと同じ)に従ってコードを書いていけば、SQLコードの完成です。最後に前述のSQL駆動エンジン「sql_nn_01」を実行すれば、結果がシートに出力されます。今のところ、SQL文で使用できたのはSelect文とTransform文の二つだけでしたが、これだけで十分に有効活用できます。なぜなら、シートに出力したSQL実行結果は、また新しいテーブルとして以降のSQLの実行に使えるからです。このため、複雑なデータハンドリングやクレンジングも、複数回SQL文を続けて実行することで対応可能です。それだけでなく、間に通常のExcelのVBAコードを挟めば、SQLではやりにくい処理も混ぜることができます。例えば、横向きや縦向きに数値を積算していくことや、型の異なるデータが一つの列に混在している元データを整備してからSQLを実行するなどです。

最後に、上記テクニックを応用して作った私の家計簿データ加工・分析ツールの紹介動画を貼っておきます！ぜひ一度ご覧ください！

以上が私の渾身のデータハンドリングテクニックが詰まったコードでした。そんなに長くなくて拍子抜けされている方もいらっしゃるかと思いますが、苦しい状況の中で、もがきながら編み出したものです。ぜひコードをExcel VBAに貼り付けて実行してみてください！説明が不足している箇所などは、Twitterのリプライ欄で可能な範囲で回答させていただきます。皆さんがさらにこのコードを役立てていただければ嬉しいです。