エクセルVBAでAccessデータをグループで集計して抽出するGROUP BY句と集計関数の使い方

★気に入ったらシェアをお願いします!


grouping

photo credit: marktmcn Clifftop gathering via photopin (license)

みなさん、こんにちは!
タカハシ(@ntakahashi0505)です。

エクセルVBAでAccessデータベースを操作する方法についてのシリーズです。

前回はコチラの記事。

エクセルVBAでADODBレコードセットをSortメソッドで並び替えする方法
エクセルVBAでAccessデータベースを操作する方法についてシリーズでお伝えしています。ADODBレコードセットの並び順について確認しつつ、Sortメソッドを使って並び替えをする方法についてです。

ADODBレコードセットのレコードを並び替えをする方法についてお伝えしました。

さて、今回ですが、少し別のことをお伝えしたいと思います。

例えば、Accessデータベース内の特定のテーブルをエクセルシートに呼び出すときに、集計を加えながら抽出したいときありますよね。

エクセル関数で言えばSUMIFです。

今回は、エクセルVBAでAccessデータベースのレコードをグループで集計して抽出する方法です。SQL文のGROUP BY句と集計関数を使いますよ。

では、行ってみましょう。

スポンサーリンク

今回のお題:レコードを「エリア単位」で集計したい

今回のお題について説明します。

Accessデータベース「test.accdb」内に、以下のようなテーブル「データ」があります。
Accessで作成したテーブル

フィールドの構成は

|ID|エリア|都道府県|都道府県庁|推計人口|面積|人口密度|国勢調査人口|市の数|区の数|町の数|村の数|

となっています。

元のテーブルではレコードは「都道府県単位」になっていますが、エクセルシート「Sheet5」に「エリア単位」でレコードを抽出したいのです。

エリア単位の、推計人口と面積です。

どのようにすればよいでしょうか?

一つの方法として考えられるのは、SELECT文で「データ」のレコードを全てシートに書き出して、それをSUMIFなどを使って集計する方法が考えられます。

ただ、この方法では、手数が多くてちょっと面倒ですね。

テーブルの全てのレコードを出力するプログラム

実はSQLをちょっと修正するだけで一発で抽出する方法がありますので、説明をしていきますね。

まず、ベースとなるプログラムはこちらを使います。「テーブル」の全てのレコードをSheet5に出力するプログラムです。

詳しくはこちらの記事をご覧ください。

【エクセルVBA&Access連携】SQL文でデータを抽出する最も簡単なプログラム
エクセルVBAでAccessデータベースを操作する方法についてシリーズでお伝えしています。今回はデータベース言語SQLとADODB.Recordsetオブジェクトでデータベースからデータを抽出します。

レコードをグループ化して集計するGROUP BY句の使い方

データベースから集計をしてデータを抽出には、SQLのSELECT文にGROUP BY句を使います。

書き方ですが

SELECT フィールド名1,フィールド名2,… FROM テーブル名 GROUP BY フィールド名A,フィールド名B,…

と書きます。

GROUP BYの後に記述したフィールドに関しては、そのフィールドの値が同じであるレコードがグループ化されます。複数フィールドを指定した場合は、その組み合わせでグループ化されます。

従って、レコード数=グループ数にまとめられるわけです。

では、超簡単な例として、前述のプログラム内の14行目、SQL文を以下に書き換えて実行してみましょう。

ほら、エリアごとにまとめられたでしょ?

エクセルVBAでSQL文のGROUP BYを使いグループ化してデータ抽出

GROUP BYでよく出くわすエラー

では、抽出するフィールドとしては、エリアの他に、推計人口、面積が欲しいので

として、実行してみます。

実行時エラー「クエリに、集計関数の一部として指定された式が含まれていません。」

おや…実行時エラー「クエリに、集計関数の一部として指定された式’推計人口’が含まれていません。」と出てしまいました。

日本語的にはちょっと意味がわかりづらいですね。

これは何かと言いますと、GROUP BY句によってレコード数は9(=エリアの数)にグループ化されたのですが、一方で抽出するフィールド「推計人口」や「面積」は当然47個のデータが存在しているわけです。VBAからすると

どうやってまとめたらいいの?

てなことになっちゃうわけですね。

ということで、SELECTの後のフィールド(かつGROUP BYの後に列挙していないフィールド)については、どのようにまとめるかという指示を出してあげる必要があります。

どうするかというと、関数を使います。

SQLで使える集計関数

SQLには集計用の関数がいくつか用意されています。

集計関数 内容
SUM 合計を求める
AVG 平均を求める
MAX 最大値を求める
MIN 最小値を求める
COUNT 個数(行数)を求める

見慣れたようなものばかりですね。これらはSELECT句の中でフィールドに対して使用します。

例えば、今回はエリア別の推計人口と面積の「合計」を出してみようということであれば

と書きます。

これで実行してみますと

エクセルVBAでSQL文のGROUP BYを使い合計してデータ抽出

このように推計人口、面積がそれぞれ合計値として表示されます。

また、別の例として、エリア内の都道府県の数、推計人口の最大値および平均値、面積の最大値および平均値を求めたいのであれば

とジャンジャン書けばOKです。実行しますと

エクセルVBAでSQL文のGROUP BYを使い様々な集計をしてデータ抽出

となります。

まとめ

エクセルVBAでAccessデータベースのレコードをグループで集計して抽出するGROUP BY句と集計関数の使い方についてお伝えしました。

GROUP BY句と集計関数…便利ですよね!!

特定の抽出条件を指定するWHERE句

【エクセルVBAでAccess連携】SQLのSELECT,FROM,WHEREによる様々なデータ抽出方法
エクセルVBAでAccessデータベースを操作するシリーズ。今回はAccessデータベースのテーブルから色々なパターンでデータを抽出するSELECT、WHEREの使い方についてお伝えします。

複数のテーブルを組み合わせるJOIN句

【エクセルVBAでAccess連携】SQLのJOINを使って複数のテーブルを組み合わせてデータを取り出す
エクセルVBAでAccessデータベースを操作する方法についてシリーズでお伝えしています。今回はSQL文にJOINを使うことで複数のテーブルを組み合わせてデータを取り出す方法についてお伝えします。

レコードの並び替えを行うORDER BY句

エクセルVBAでAccessのデータをORDER BYで並び替えをして取り出す
エクセルVBAでAccessデータベースを操作する方法についてお伝えしています。今回はSQLのSELECT文で取得したデータの順番についての検証、並び替えて取得するORDER BY句の使い方についてです。

などと組み合わせれば、変幻自在のデータ抽出が可能になります。

さて、次回ですがGROUP BYついでに、GROUP BYの結果に条件付けしてレコードを抽出するHAVING句についてもお伝えできればと思います。

エクセルVBAでAccessデータを集合関数による条件で抽出するHAVING句の使い方
エクセルVBAでAccessデータベースからレコードを抽出する方法として、HAVING句の使い方です。GROUP BY句でグループ化したレコードを条件で絞り込む、また集合関数でテーブルの状態を判定することもできます。

どうぞお楽しみに!

連載目次:エクセルVBAでエクセルからAccessデータベースを操作する

エクセルと相性の良いデータベースとして第一候補として挙がるAccess。エクセルをインターフェース、AccessをデータベースとしてエクセルVBAを使って連携する方法とその様々なテクニックについてお伝えしていきます。
  1. なぜエクセルとAccessデータベースを連携するのが良いのか、またその際の注意点
  2. エクセルVBAでAccessデータベースと接続する最もシンプルなプログラム
  3. 【エクセルVBA&Access連携】SQL文でデータを抽出する最も簡単なプログラム
  4. 【エクセルVBAでAccess連携】SQLのSELECT,FROM,WHEREによる様々なデータ抽出方法
  5. 【エクセルVBAでAccess連携】SQLのJOINを使って複数のテーブルを組み合わせてデータを取り出す
  6. 【エクセルVBAでAccess連携】データベースのテーブルにレコードを追加するシンプルな方法
  7. エクセルVBAでAccessデータベースに複数のレコードを追加する方法とその実行速度について
  8. 【エクセルVBAでAccess連携】データベースのテーブルにレコードを追加するシンプルな方法
  9. 【エクセルVBAでAccess連携】データベースに特定条件のデータが存在するかどうかを判定する
  10. エクセルVBAからAccessデータベースのレコードを呼び出して修正して上書き更新する方法
  11. エクセルVBAでAccessデータベースの複数のレコードをまとめて上書き更新する方法
  12. エクセルVBAでAccessデータベースの複数のレコードを上書き更新する場合の実行速度について
  13. エクセルVBAでAccessのデータをORDER BYで並び替えをして取り出す
  14. エクセルVBAでADODBレコードセットをSortメソッドで並び替えする方法
  15. エクセルVBAでAccessデータをグループで集計して抽出するGROUP BY句と集計関数の使い方
  16. エクセルVBAでAccessデータを集合関数による条件で抽出するHAVING句の使い方
  17. エクセルVBAでAccessデータベースからFormat関数によるSQL文で特定の日付で抽出