(license)みなさん、こんにちは!タカハシ(@ntakahashi0505)です。エクセルVBAでAccessデータベースを操作する方法についてシリーズでお伝えしています。BY句と集計関数の使い方エクセルVBAでAccessデータベースを操作する方法についてのシリーズです。今回はデータベースのレコードをグループで集計して抽出する方法です。BY句と集計関数を使いますよ。BY句の使い方をお伝えしました。BY句と合わせて使われることの多いHAVING句について紹介します。BYで集計して抽出したレコードを条件づけて絞り込む際に使うのですが、集合関数と組み合わせてテーブルの状態を判定する方法についてもお伝えします。お願いいたします!目次お題:エリア単位で集計しつつ条件を満たすエリアのみ抽出するデータベースからレコードを抽出するプログラムHAVING句で集計関数を使った条件付けによるレコード抽出HAVING句でグループ化した結果を絞り込むHAVING句を使ってテーブルの状態を判定するまとめ連載目次:エクセルVBAでエクセルからAccessデータベースを操作するお題:エリア単位で集計しつつ条件を満たすエリアのみ抽出するまずは今回のお題の確認からです。Accessデータベース「test.accdb」に、テーブル「データ」があります。フィールドの構成は|ID|エリア|都道府県|都道府県庁|推計人口|面積|人口密度|国勢調査人口|市の数|区の数|町の数|村の数|です。BY句を使って、エリア単位で抽出する方法についてお伝えしました。SUMやCOUNTなどの集合関数と組み合わせることで、エリアごとの推計人口と面積の合計や、エリアごとの都道府県数なども自在に抽出することができます。今回ですがその抽出したエリアのうち面積が一定以上のエリアのデータのみ抽出する都道府県数が一定数以上のエリアのデータのみ抽出するなどといった、グループ集計結果の絞り込みをしていきたいと思います。データベースからレコードを抽出するプログラム続いて、ベースとなるプログラムを紹介しておきます。SubAccessデータベース「test.accdb」とコネクションを開いて、SQL文を使ってデータを抽出するものです。14行目でSQL文を指定しますが、現在は全てのレコードを抽出する形になっていますね。このSQL文を抽出したい内容に指定していけばよいということになります。このプログラムについては以下の記事もご覧ください。【エクセルVBA&Access連携】SQL文でデータを抽出する最も簡単なプログラムエクセルVBAでAccessデータベースを操作する方法についてシリーズでお伝えしています。今回はデータベース言語SQLとADODB.Recordsetオブジェクトでデータベースからデータを抽出します。条件を満たすレコードを抽出というと、WHERE句を思い出すかも知れません。SQL文はこんな書き方でしたね。【エクセルVBAでAccess連携】SQLのSELECT,FROM,WHEREによる様々なデータ抽出方法エクセルVBAでAccessデータベースを操作するシリーズ。今回はAccessデータベースのテーブルから色々なパターンでデータを抽出するSELECT、WHEREの使い方についてお伝えします。SUM(面積)>=30000"こんなふうに書きたくなります。これを実行するとこのように「WHERE句(SUM(面積)>=30000)で集計関数を使用することはできません。」という実行時エラーが発生してしまいます。WHERE句では集計関数を使えません。集計関数を使って条件付けをしたい場合はHAVING句を使うのです。条件文は、集合関数と比較演算子を使うことになりますね。SUM(面積)>=30000"とします。結果としてはとなります。COUNT(都道府県)>=5"とすればよいですね。BY句とセットで使わないといけないというわけではありません。集計関数を使って条件付けをしたい場合に使うことができるので、例えばこんな使い方もできるのです。COUNT(都道府県)=47"どんなSQL文だかわかりますか?テーブル内のフィールド「都道府県」の数をカウントしてそれが47であれば、「都道府県の数は47ある」という一つのフィールドを持つ一つのレコードが表示せよ、ということです。テーブル内のフィールド「都道府県」の数が47でなければ、抽出レコードがない、つまり何も出力されません。別の例として、このような使い方もあります。SUM(推計人口)>=125000000"テーブル内のフィールド「推計人口」の合計を判定して、1億2500万人以上であれば「日本の推計人口は1億2500万人以上」というフィールドを持つレコードが出力されます。このように、HAVING句と集計関数を使ってテーブルの状態を判断することができます。以下記事にあるように、抽出したレコードが空かどうかを、ADODBレコードセットオブジェクトのEOF、BOFプロパティを使って判定することで、テーブルの状態をみて分岐をするようなプログラムを作成することができます。【エクセルVBAでAccess連携】データベースに特定条件のデータが存在するかどうかを判定するエクセルVBAでAccessデータベースを操作する方法についてシリーズでお伝えしています。今回はエクセルVBAでAccessデータベースに特定のデータが存在するかどうかを判定するプログラムを作ります。tonari-it.com2016.10.05まとめエクセルVBAでAccessデータを集合関数による条件で抽出するHAVING句の使い方についてお伝えしました。BY句とセットで使うことが多いですが、それ以外にも集合関数を使ってテーブルの状態をスパっと判断したいというときにも使うことができます。次回はSQL文内にFormat関数を使って日付を条件に色々な抽出を便利にする往訪をお送りできればと思います。エクセルVBAでAccessデータベースからFormat関数によるSQL文で特定の日付で抽出エクセルVBAでAccessデータベースを操作する方法についてお伝えしています。SQL文にFormat関数を使って特定の日付のレコードを抽出する方法。応用で特定の年月や曜日でグルーピングもできます。tonari-it.com2016.12.03どうぞお楽しみに!連載目次:エクセルVBAでエクセルからAccessデータベースを操作するエクセルと相性の良いデータベースとして第一候補として挙がるAccess。エクセルをインターフェース、AccessをデータベースとしてエクセルVBAを使って連携する方法とその様々なテクニックについてお伝えしていきます。プランノーツ代表、コミュニティ「ノンプロ研」主宰。ITで日本の『働く』の価値を上げる!」をテーマに、VBA&GASの開発、講師、執筆などをしております。→詳しいプロフィールはコチラ★ご依頼・ご相談はお気軽にどうぞ!→お問い合わせはコチラ★フォロー頂ければ嬉しいです。「コミュニティ+講座」形式で継続性&教え合える環境も提供。!ノンプログラマーがプログラミングスキルを身に着ける支援ををするコミュニティ。セミナー・もくもく会・Facebookグループのサポートで「自らで学び続ける力をつける」支援、「教え合うことで学びの価値を上げる」場の提供をしています。関連記事Excel・VBA活用術エクセルのゴールシークを使って目標から逆算して毎月の必要なアクションを知る過去データから回帰分析で未来の予測をすることができます。今回はエクセルのゴールシークという機能を使うことで、ゴールである目標値から逆算をして前提となる数値を求める方法についてお伝えします。複数ループエクセルVBAのDictionaryオブジェクトを使って書かれた重複排除のコードと、複数のループを使った重複排除のコードの処理速度を比較してみました。お手元にリストの重複処理などで「遅いなあ...」と感じるコードがあるなら、参考にしてみてください。2018.05.17Excel・VBA活用術エクセルで営業日判定を実現するWORKDAY関数の使い方エクセルで土日祝日を判定したい!といった場合に、祝日の判定は標準のエクセル機能では不可能です。今回紹介するWORKDAY関数を使用すれば、祝日の判定を実現することが可能です。【エクセルVBA】ChangeイベントとEnableEventsプロパティで部分一致検索をする方法WorksheetオブジェクトのChangeイベントと、ApplicationオブジェクトのEnableEventsプロパティを使って、セルに得意先名の一部を入力したら、マスタシートから正式名称を取得して入力セルに返す、というコードをご紹介しています。面倒かつ間違い易い入力作業は、出来るだけ簡略化したいですね。【エクセルVBA入門】シートのデータがある最終行番号を求めるステートメントを徹底解説エクセルVBAを使ってバラバラの経費精算書データを集約するシリーズです。今回は、実行のたびにデータをシートに追加できるようにします。最終行番号を求めるステートメントについて徹底的に解説をしていきます。Scriptで業務効率化!両方知って触れる欲張りハンズオン」でお話しましたので、そのレポートをお送りします。「コミュニティ+講座」形式で継続性&教え合える環境も提供。詳細・お申込はこちら隣ITを応援する当ブログを「応援したい!」「役に立ったよ!」というお気持ちを、コチラからお支払いただくことができます。日々更新の活力倍増いたします。※PayPalアカウントが必要です。※金額は変更可能です。Platform23HTML・CSS32iPhone・アプリ29Monaca・スマホアプリ開発7PC全般・Windows86PowerPoint・VBA28Python87RPA1VBScript15Vue.js・JavaScript13WEBサービス・クラウド68WordPress・ブログ88Word・VBA42コミュニティ・イベント109セミナーのお知らせ11チャットワーク72データ分析・アクセス解析40仕事・生産性・働き方73動画配信8商業出版21起業・フリーランス72電子書籍・Kindle3運営会社プランノーツは"IT×働き方改革"をお手伝いする会社です。「コミュニティ+講座」形式で継続性&教え合える環境も提供。詳細・お申込はこちら隣ITを応援する当ブログを「応援したい!」「役に立ったよ!」というお気持ちを、コチラからお支払いただくことができます。日々更新の活力倍増いたします。※PayPalアカウントが必要です。※金額は変更可能です。Platform23HTML・CSS32iPhone・アプリ29Monaca・スマホアプリ開発7PC全般・Windows86PowerPoint・VBA28Python87RPA1VBScript15Vue.js・JavaScript13WEBサービス・クラウド68WordPress・ブログ88Word・VBA42コミュニティ・イベント109セミナーのお知らせ11チャットワーク72データ分析・アクセス解析40仕事・生産性・働き方73動画配信8商業出版21起業・フリーランス72電子書籍・Kindle3運営会社プランノーツは"IT×働き方改革"をお手伝いする会社です。Scriptが「V8ランタイム」をサポート!モダンなECMAScript構文が使えるようになった2020.02.072020.02.08GASでコピーしたGoogleスライドのプレゼンテーションを取得する方法2020.02.052020.02.05Facebookページいつも隣にitのお仕事タイトルとURLをコピーしました。