概要：なぜ文字列の一部で集計する必要があるのか
Excel VBAを用いた業務自動化において、データの集計は避けて通れないタスクです。しかし、実務で扱うデータは必ずしも「集計に適した形」で格納されているわけではありません。特に多いのが、「商品コードの先頭3桁がカテゴリを表している」「メールアドレスのドメイン部分で集計したい」「社員番号の所属コードを抽出して分析したい」といった、文字列の一部をキーにした集計要件です。
通常のExcel関数（LEFT関数やMID関数）で作業用列を作成し、そこからピボットテーブルを作成する手法は一般的ですが、データ量が数万件を超えるとExcelの動作は重くなり、メンテナンス性も低下します。ここで強力な武器となるのが、VBAからADO（ActiveX Data Objects）を経由してSQLを直接発行する手法です。SQLの文字列操作関数を駆使すれば、作業用列を一切作ることなく、高速かつスマートに目的の集計結果を得ることが可能です。
詳細解説：SQLにおける文字列抽出とグループ化のロジック
SQLで文字列の一部を取り出して集計を行う場合、主に「SUBSTRING関数（またはLEFT/MID関数）」と「GROUP BY句」を組み合わせます。
SQLの標準的な構成要素は以下の通りです。
1. SUBSTRING(カラム名, 開始位置, 文字数)：指定した位置から特定の文字数だけを抽出します。
2. GROUP BY：抽出した文字列をキーにしてデータをまとめます。
3. SUM/COUNT：集計対象の数値を計算します。
Excel VBAからADOを使用する場合、接続先はカレントブック（現在のExcelファイル自体）に設定することで、まるでデータベースを操作するかのようにシート上のデータをSQLで扱えます。この手法の最大のメリットは「データの非破壊性」です。元の表に加工を加えることなく、クエリの結果だけを別のシートに出力できるため、誤操作によるデータ破損のリスクを最小限に抑えることができます。
サンプルコード：VBAで実行する文字列集計SQL
以下のサンプルコードは、A列に「A001-001」「A001-002」「B002-001」のようなコードが含まれているデータテーブルから、ハイフンより前の「カテゴリコード」をキーにして、B列の売上金額を合計するものです。
Sub ExecuteStringAggregationSQL()
Dim cn As Object
Dim rs As Object
Dim strQuery As String
Dim strPath As String
' カレントブックへの接続
strPath = ThisWorkbook.FullName
Set cn = CreateObject("ADODB.Connection")
Set rs = CreateObject("ADODB.Recordset")
' Excel 2007以降の接続文字列
cn.Open "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" & strPath & _
";Extended Properties=""Excel 12.0 Xml;HDR=YES;"""
' SQLの組み立て
' LEFT(コード, INSTR(コード, '-') - 1) でハイフン前を抽出
' ※Jet/ACE SQLではINSTR関数が利用可能
strQuery = "SELECT LEFT([商品コード], INSTR([商品コード], '-') - 1) AS カテゴリ, " & _
"SUM([売上金額]) AS 合計金額 " & _
"FROM [Sheet1$A1:B1000] " & _
"GROUP BY LEFT([商品コード], INSTR([商品コード], '-') - 1)"
' クエリ実行
Set rs = cn.Execute(strQuery)
' 結果の出力（新規シートへ）
With Worksheets.Add
.Range("A1").CopyFromRecordset rs
End With
' クリーンアップ
rs.Close
cn.Close
Set rs = Nothing
Set cn = Nothing
End Sub
このコードのポイントは、INSTR関数を使用して「ハイフンが何文字目にあるか」を動的に判定している点です。これにより、コードの長さが可変であっても正確に抽出が可能です。
実務アドバイス：パフォーマンスとエラー回避の勘所
実務でこの技術を使いこなすためには、いくつか注意すべき点があります。
第一に「インデックスの不在」です。SQLを用いた集計は高速ですが、Excelのシートを直接読み込む場合、データベースのようなインデックスは効きません。数百万行を超えるような巨大なデータセットを扱う場合は、VBAで配列（Array）に読み込んでから処理するほうが早いケースもあります。SQLを使用するのは「数万〜数十万行程度」のデータが最も効率的です。
第二に「データ型の不一致」です。Excelは1列の中に文字列と数値が混在していても許容しますが、SQLは厳格です。集計対象の「売上金額」列に数値以外の文字列が含まれていると、エラーやデータ欠損の原因になります。事前にVBAのRange.TextToColumnsメソッド等で型を整えておくか、SQL内でCDbl関数などを用いて明示的にキャストする習慣をつけましょう。
第三に「HDR設定」です。HDR=YESは「1行目をヘッダーとして扱う」という意味です。データ範囲の指定は[Sheet1$A1:B1000]のように具体的に記述するのがトラブルを避けるコツです。
まとめ：VBAとSQLの融合が業務を劇的に変える
文字列の一部をキーにした集計は、データ分析の現場で最も頻繁に発生する「前処理」の一つです。これを手作業や複雑なExcel関数で行うことは、時間的にも精神的にも大きなコストを伴います。
今回紹介した「VBA × SQL」のアプローチを習得すれば、以下のメリットを享受できます。
1. 処理の自動化：ボタン一つで集計が完了し、ミスがゼロになる。
2. メンテナンス性の向上：ロジックがコード化されているため、変更が必要な際も修正が容易。
3. 高い拡張性：今回学んだ文字列抽出技術を応用すれば、日付データからの月別集計や、氏名からの姓抽出など、あらゆる加工に応用が可能です。
Excel VBAを単なるマクロ記録ツールとして使う段階は卒業しましょう。SQLという強力なデータベース言語をVBAのエンジンに組み込むことで、あなたは「Excelをデータベースとして使いこなすプロフェッショナル」へと飛躍できます。まずは手元のデータで、今回紹介したコードを試してみてください。SQLが描く「抽出と集計」の論理美に触れたとき、あなたの業務効率は一段上のステージへと昇華されるはずです。
最後に、SQL文を書く際は、まずメモ帳などでクエリを書き、それをデバッグウィンドウやMsgBoxで出力して、意図通りの文字列になっているか確認する癖をつけてください。一見難しそうに見えるSQLも、分解してみれば非常にシンプルな論理の積み重ねです。学習を継続し、ぜひ自社の業務を自動化の力で劇的に変革してください。
