Excel VBA ADOを使って学ぶデータ集計の自動化!VBAとSQLAで実現する会計データの月次集計(テクニック)

 

 

Excel VBA ADOを使って学ぶデータ集計の自動化!VBAとSQLAで実現する会計データの月次集計(テクニック)

 

 

 ●はじめに

今回紹介するプログラムは、Excel上に保存されたデータをSQLクエリで操作し、結果を別のワークシートに出力するものです。特に、1月から12月の各月の金額を事業所と勘定科目ごとに集計し、resultDataワークシートに表示します。この自動化は、Microsoft ActiveX Data Objects (ADO) ライブラリを活用して実現します。このコードを利用することで、予算管理の追跡、売上の分析と予測、コストの削減、パフォーマンスのベンチマーキングなど、業務の効率化と戦略的意思決定を大きく支援します。会社の業務に応じたカスタマイズにより、さらに多様なシナリオに対応可能で、データ集計だけでなく分析や意思決定においても重要な役割を果たします。具体的な説明につきましては、下記を参照して下さい。

【EXCELでSQLを利用するの場合の応用方法と業務へのメリット】
1.予算管理と実績の追跡: 各部署やプロジェクトの予算と実績を月次で追跡し、逸脱がある場合に即時に対応できるようにします。これにより、財務の健全性を維持しやすくなります。
2.売上分析と予測: 月次の売上データを分析し、季節性やトレンドを把握することで、将来の売上予測の精度を高めます。マーケティング戦略や在庫管理にも役立てることができます。
3.コスト削減: 各事業所や部門のコストを詳細に分析し、無駄な支出を特定します。コスト削減の機会を見つけ出し、より効率的な運営を目指します。
4.パフォーマンスのベンチマーキング: 各事業所や部署のパフォーマンスを比較し、ベストプラクティスの共有や改善点の特定に役立てます。組織全体のパフォーマンス向上を目指します。

●【EXCEL VBAエクセルでSQLを実行(ADO)データベース構築・クエリー実行・大量のデータを処理】については、下記を参照して下さい】

EXCEL VBA エクセルでSQLを実行(ADO)データベース構築・クエリー実行・大量のデータを処理・入門者

●【Microsoft ActiveX Data Objects (ADO)、下記を参照して下さい】(Microsoft社 様)】
https://learn.microsoft.com/ja-jp/sql/ado/microsoft-activex-data-objects-ado?view=sql-server-ver16

 

 

 

EXCEL VBAで管理会計を楽にするADOを使ってEXCELデータを会計データを月別集計表を作成する。

 

 

 ●プログラム説明 (サンプル①)

今回紹介するプログラムは、Excel上に保存されたデータをSQLクエリを使って操作し、結果をExcelの別のワークシートに出力します。具体的には、ある期間(1月から12月)の各月の金額を事業所と勘定科目ごとに集計し、その結果をresultDataワークシートに表示します。このプロセスは、Microsoft ActiveX Data Objects (ADO) ライブラリを使用して行われます。

【プログラムの流れ】
1.必要なライブラリ(Microsoft ActiveX Data Objects Library)を参照設定に追加します。
2.ソースデータと結果を表示するワークシートを指定します。
3.ADO接続とレコードセットオブジェクトを初期化します。
4.Excelファイルをデータソースとする接続文字列を設定し、データベースに接続します。
5.SQLクエリを定義し、特定の期間の金額を事業所と勘定科目ごとに集計します。
6.SQLクエリを実行し、結果をレコードセットに格納します。
7.レコードセットの内容をresultDataワークシートに出力します。
8.最終列と最終行を取得し、罫線と色塗りのフォーマットを適用します。
9.レコードセットと接続を閉じ、オブジェクトを解放します。
10.結果ワークシートをアクティブにします。

【プログラム実行条件・注意事項】
1.Microsoft ActiveX Data Objects Libraryを参照設定に追加する必要があります。
2.このコードは、Excelファイルが閉じられていない状態で実行する必要があります。
3.Excelファイルのパスや名前が変わると、接続文字列もそれに応じて変更する必要があります。
4.セキュリティ設定によっては、マクロを有効にする必要があります。
5.[SourceData]と[resultData]のワークシートが事前にワークブック内に存在する必要があります。

●【Microsoft ActiveX Data Objects (ADO) LibraryをExcel VBAプロジェクトに追加する方法は以下の手順で行います。】
これにより、ADOを使用してデータベース接続や操作を行うコードをVBA内で記述できるようになります。

①Excelを開き、VBAエディターを起動します。
Excelで対象のワークブックを開いた状態で、Alt + F11 キーを押してVBAエディターを起動します。
②参照設定を開く
VBAエディターのメニューバーから「ツール(T)」>「参照設定(R)…」を選択します。

③Microsoft ActiveX Data Objects Libraryを探す。
「参照設定」のダイアログボックスが表示されたら、スクロールして「Microsoft ActiveX Data Objects x.x Library」という項目を探します。
ここでの「x.x」はバージョン番号で、使用しているOfficeのバージョンによって異なります
(例: Microsoft ActiveX Data Objects 6.1 Library ,Microsoft ActiveX Data Objects 2.8 Library など)。
④Libraryを選択して追加
 見つけた「Microsoft ActiveX Data Objects x.x Library」の横にあるチェックボックスをクリックして選択(チェックを入れる)状態にします。

⑤OKボタンをクリック

「OK」ボタンをクリックして、参照設定ダイアログを閉じます。これで、ADO LibraryがVBAプロジェクトに追加されました。
⑥コードの記述を開始
追加したLibraryを使って、VBA内でデータベース接続やSQLクエリの実行などのコードを記述できるようになります。

★【サンプルプログラム】
下記のリンク先よりサンプルプログラムをダウンロードする事ができます。
● sql_totalling01(サンプルプログラム)

 

 

 

 

 

●実行前~実行後 ※このコードを実行すると、SourceDataワークシートから指定された条件に基づいてデータを集計し、その結果をresultDataワークシートに出力します。出力されたデータは、事業所と勘定科目ごとに、各月の金額の合計が表示されました。
(画面クリックして拡大)

 

 

 

EXCEL VBAでデータ分析の効率化!Excel内でSQLクエリを実行する方法!一歩進んだExcel集計!VBAとSQLのパワフルな組み合わせで集計作業を自動化!

 

 

 ●プログラム説明 (サンプル②)

下記のこのコードは上記のサンプル①の応用になります。Excel VBAを使用して、特定の条件に基づいてExcelシート内のデータを集計し、結果を別のシートに表示します。Microsoft ActiveX Data Objects (ADO) ライブラリを使用して、Excelファイル自体に対してSQLクエリを実行します。集計は「事業所」ごとに年度別に「修繕費」、「通信費」、「雑費」の合計額を計算し、結果を整形して表示することに焦点を当てています。最終的な表示では、セルの罫線の追加、特定の列の背景色の設定などの視覚的なフォーマットを施しています。

【プログラムの流れ】
1.必要な変数とオブジェクトの宣言。
2.ソースデータと結果データのワークシートを設定。
3.ADO接続オブジェクトを初期化し、Excelファイルに対する接続文字列を設定。
4.SQLクエリを構築し、ADOレコードセットオブジェクトにクエリ結果を格納。
5.クエリ結果のフィールド名を結果シートの1行目に表示。
6.クエリ結果を2行目から結果シートに出力。
7.結果シートに対して視覚的なフォーマット(罫線、背景色)を適用。
8.レコードセットと接続のクローズおよびオブジェクトの解放。
9.結果シートをアクティブにする

【このコードを日常事務で利用する場面】(例)
1.月次報告の自動化: 月末に各部門から提出される経費報告を自動集計。
2.年度予算の追跡: 年度予算に対する実績の追跡と分析。
3.販売データの分析: 販売データを事業所や製品カテゴリー別に分析。
4.経費のカテゴリー別集計: 企業の経費を種類ごとに集計し、コスト削減の機会を特定。
5.人事データの管理: 従業員の入退社に伴う人事データの集計と分析。

 

【プログラム実行条件・注意事項】
1.Microsoft ActiveX Data Objects Libraryへの参照設定が必要です。
2.クエリを実行するExcelファイルは開いている状態でなければなりません。
3.Excelのバージョンによっては接続文字列を変更する必要があるかもしれません。
4.シート名やフィールド名に変更がある場合は、コード内の対応する部分を更新する必要があります。
5.大量のデータを処理する場合、パフォーマンスに影響が出る可能性があります。

★【サンプルプログラム】
下記のリンク先よりサンプルプログラムをダウンロードする事ができます。
● sql_totalling02(サンプルプログラム)

 

 

 

 ●実行前~実行後 ※プログラム実行後、結果シートには「事業所」と「年」ごとにグループ化された「修繕費」、「通信費」、「雑費」の合計値が表示されます。結果は視覚的に整形され、指定された列には背景色が適用され、全セルに罫線が引かれています。
(画面クリックして拡大)

 

 

最後まで、ご覧いただきまして誠に有難うございました。
また、VBAに関するテクニックや便利な手法などをこのサイトに掲載していきますので、定期的に参照していただけると幸いです。