photo credit: marktmcn Clifftop gathering via photopin (license)
みなさん、こんにちは！
タカハシ(@ntakahashi0505)です。
エクセルVBAでAccessデータベースを操作する方法についてのシリーズです。
前回はコチラの記事。
ADODBレコードセットのレコードを並び替えをする方法についてお伝えしました。
さて、今回ですが、少し別のことをお伝えしたいと思います。
例えば、Accessデータベース内の特定のテーブルをエクセルシートに呼び出すときに、集計を加えながら抽出したいときありますよね。
エクセル関数で言えばSUMIFです。
今回は、エクセルVBAでAccessデータベースのレコードをグループで集計して抽出する方法です。SQL文のGROUP BY句と集計関数を使いますよ。
では、行ってみましょう。
今回のお題：レコードを「エリア単位」で集計したい
今回のお題について説明します。
Accessデータベース「test.accdb」内に、以下のようなテーブル「データ」があります。
フィールドの構成は
となっています。
元のテーブルではレコードは「都道府県単位」になっていますが、エクセルシート「Sheet5」に「エリア単位」でレコードを抽出したいのです。
エリア単位の、推計人口と面積です。
どのようにすればよいでしょうか？
一つの方法として考えられるのは、SELECT文で「データ」のレコードを全てシートに書き出して、それをSUMIFなどを使って集計する方法が考えられます。
ただ、この方法では、手数が多くてちょっと面倒ですね。
テーブルの全てのレコードを出力するプログラム
実はSQLをちょっと修正するだけで一発で抽出する方法がありますので、説明をしていきますね。
まず、ベースとなるプログラムはこちらを使います。「テーブル」の全てのレコードをSheet5に出力するプログラムです。
Sub SelectDB()
Dim strFileName As String
strFileName = "test.accdb"
Dim adoCn As Object 'ADOコネクションオブジェクト
Set adoCn = CreateObject("ADODB.Connection") 'ADOコネクションオブジェクトを作成
adoCn.Open "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" & ThisWorkbook.Path & "\" & strFileName & ";" 'Accessファイルに接続
Dim adoRs As Object 'ADOレコードセットオブジェクト
Set adoRs = CreateObject("ADODB.Recordset") 'ADOレコードセットオブジェクトを作成
Dim strSQL As String
strSQL = "SELECT * FROM データ"
adoRs.Open strSQL, adoCn 'SQLを実行して対象をRecordSetへ
Worksheets(1).Range("A1").CopyFromRecordset adoRs
adoRs.Close 'レコードセットのクローズ
adoCn.Close 'コネクションのクローズ
Set adoRs = Nothing
Set adoCn = Nothing 'オブジェクトの破棄
End Sub
詳しくはこちらの記事をご覧ください。
レコードをグループ化して集計するGROUP BY句の使い方
データベースから集計をしてデータを抽出には、SQLのSELECT文にGROUP BY句を使います。
書き方ですが
と書きます。
GROUP BYの後に記述したフィールドに関しては、そのフィールドの値が同じであるレコードがグループ化されます。複数フィールドを指定した場合は、その組み合わせでグループ化されます。
従って、レコード数＝グループ数にまとめられるわけです。
では、超簡単な例として、前述のプログラム内の14行目、SQL文を以下に書き換えて実行してみましょう。
strSQL = "SELECT エリア FROM データ GROUP BY エリア"
ほら、エリアごとにまとめられたでしょ？
GROUP BYでよく出くわすエラー
では、抽出するフィールドとしては、エリアの他に、推計人口、面積が欲しいので
strSQL = "SELECT エリア,推計人口,面積 FROM データ GROUP BY エリア"
として、実行してみます。
おや…実行時エラー「クエリに、集計関数の一部として指定された式’推計人口’が含まれていません。」と出てしまいました。
日本語的にはちょっと意味がわかりづらいですね。
これは何かと言いますと、GROUP BY句によってレコード数は9（=エリアの数）にグループ化されたのですが、一方で抽出するフィールド「推計人口」や「面積」は当然47個のデータが存在しているわけです。VBAからすると
どうやってまとめたらいいの？
てなことになっちゃうわけですね。
ということで、SELECTの後のフィールド（かつGROUP BYの後に列挙していないフィールド）については、どのようにまとめるかという指示を出してあげる必要があります。
どうするかというと、関数を使います。
SQLで使える集計関数
SQLには集計用の関数がいくつか用意されています。
| 集計関数 | 内容 |
|---|---|
| SUM | 合計を求める |
| AVG | 平均を求める |
| MAX | 最大値を求める |
| MIN | 最小値を求める |
| COUNT | 個数（行数）を求める |
見慣れたようなものばかりですね。これらはSELECT句の中でフィールドに対して使用します。
例えば、今回はエリア別の推計人口と面積の「合計」を出してみようということであれば
strSQL = "SELECT エリア,SUM(推計人口),SUM(面積) FROM データ GROUP BY エリア"
と書きます。
これで実行してみますと
このように推計人口、面積がそれぞれ合計値として表示されます。
また、別の例として、エリア内の都道府県の数、推計人口の最大値および平均値、面積の最大値および平均値を求めたいのであれば
strSQL = "SELECT エリア,COUNT(エリア),MAX(推計人口),AVG(推計人口),MAX(面積),AVG(面積) FROM データ GROUP BY エリア"
とジャンジャン書けばOKです。実行しますと
となります。
まとめ
エクセルVBAでAccessデータベースのレコードをグループで集計して抽出するGROUP BY句と集計関数の使い方についてお伝えしました。
GROUP BY句と集計関数…便利ですよね！！
特定の抽出条件を指定するWHERE句
複数のテーブルを組み合わせるJOIN句
レコードの並び替えを行うORDER BY句
などと組み合わせれば、変幻自在のデータ抽出が可能になります。
さて、次回ですがGROUP BYついでに、GROUP BYの結果に条件付けしてレコードを抽出するHAVING句についてもお伝えできればと思います。
どうぞお楽しみに！
連載目次：エクセルVBAでエクセルからAccessデータベースを操作する
エクセルと相性の良いデータベースとして第一候補として挙がるAccess。エクセルをインターフェース、AccessをデータベースとしてエクセルVBAを使って連携する方法とその様々なテクニックについてお伝えしていきます。- なぜエクセルとAccessデータベースを連携するのが良いのか、またその際の注意点
- エクセルVBAでAccessデータベースと接続する最もシンプルなプログラム
- 【エクセルVBA＆Access連携】SQL文でデータを抽出する最も簡単なプログラム
- 【エクセルVBAでAccess連携】SQLのSELECT,FROM,WHEREによる様々なデータ抽出方法
- 【エクセルVBAでAccess連携】SQLのJOINを使って複数のテーブルを組み合わせてデータを取り出す
- 【エクセルVBAでAccess連携】データベースのテーブルにレコードを追加するシンプルな方法
- エクセルVBAでAccessデータベースに複数のレコードを追加する方法とその実行速度について
- 【エクセルVBAでAccess連携】データベースに特定条件のデータが存在するかどうかを判定する
- エクセルVBAからAccessデータベースのレコードを呼び出して修正して上書き更新する方法
- エクセルVBAでAccessデータベースの複数のレコードをまとめて上書き更新する方法
- エクセルVBAでAccessデータベースの複数のレコードを上書き更新する場合の実行速度について
- エクセルVBAでAccessのデータをORDER BYで並び替えをして取り出す
- エクセルVBAでADODBレコードセットをSortメソッドで並び替えする方法
- エクセルVBAでAccessデータをグループで集計して抽出するGROUP BY句と集計関数の使い方
- エクセルVBAでAccessデータを集合関数による条件で抽出するHAVING句の使い方
- エクセルVBAでAccessデータベースからFormat関数によるSQL文で特定の日付で抽出
- エクセルVBAでのAccessデータベース操作にトランザクション処理を入れる
