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)データベース構築・クエリー実行・大量のデータを処理】については、下記を参照して下さい】
●【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(サンプルプログラム)
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 80 81 82 83 84 85 86 87 88 89 90 91 92 93 94 95 96 |
' ' Option Explicit ' ' Sub sql_totalling01() 'EXCELデータベース ' 参照設定が必要です (Microsoft ActiveX Data Objects Library) Dim cn As ADODB.Connection Dim rs As ADODB.Recordset Dim strSQL As String Dim sourceWs As Worksheet, resultWs As Worksheet Dim field As ADODB.field Dim i As Integer Dim lastCol As Long Dim lastRow As Long ' ワークシートの指定 Set sourceWs = ThisWorkbook.Worksheets("SourceData") 'ソースデータが登録 Set resultWs = ThisWorkbook.Worksheets("resultData") 'クエリー結果が登録 resultWs.Cells.Clear ' オブジェクトの宣言 Set cn = New ADODB.Connection Set rs = New ADODB.Recordset ' データソースとしてExcelファイルを指定する接続文字列 cn.ConnectionString = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" & ThisWorkbook.FullName & ";Extended Properties=""Excel 12.0 Xml;HDR=YES"";" ' データベースに接続 cn.Open strSQL = "SELECT [事業所],[勘定科目], " & _ "SUM(IIF(MONTH(日付) = 4, 金額, 0)) AS '4月', " & _ "SUM(IIF(MONTH(日付) = 5, 金額, 0)) AS '5月', " & _ "SUM(IIF(MONTH(日付) = 6, 金額, 0)) AS '6月', " & _ "SUM(IIF(MONTH(日付) = 7, 金額, 0)) AS '7月', " & _ "SUM(IIF(MONTH(日付) = 8, 金額, 0)) AS '8月', " & _ "SUM(IIF(MONTH(日付) = 9, 金額, 0)) AS '9月', " & _ "SUM(IIF(MONTH(日付) = 10, 金額, 0)) AS '10月', " & _ "SUM(IIF(MONTH(日付) = 11, 金額, 0)) AS '11月', " & _ "SUM(IIF(MONTH(日付) = 12, 金額, 0)) AS '12月', " & _ "SUM(IIF(MONTH(日付) = 1, 金額, 0)) AS '1月', " & _ "SUM(IIF(MONTH(日付) = 2, 金額, 0)) AS '2月', " & _ "SUM(IIF(MONTH(日付) = 3, 金額, 0)) AS '3月' " & _ "FROM [" & sourceWs.Name & "$] GROUP BY [事業所],[勘定科目]" ' SQLクエリを実行し、結果をレコードセットに格納 rs.Open strSQL, cn ' 表題(フィールド名)を1行目に表示 i = 1 For Each field In rs.Fields resultWs.Cells(1, i).Value = field.Name i = i + 1 Next field ' 結果を「resultData」ワークシートに出力(2行目から) resultWs.Range("A2").CopyFromRecordset rs ' 最終列と最終行を取得 lastCol = resultWs.Cells(1, resultWs.Columns.Count).End(xlToLeft).Column lastRow = resultWs.Cells(resultWs.Rows.Count, 1).End(xlUp).Row ' 罫線を引く With resultWs.Range(resultWs.Cells(1, 1), resultWs.Cells(lastRow, lastCol)).Borders .LineStyle = xlContinuous .Color = vbBlack .Weight = xlThin End With ' A列とB列を塗りつぶす With resultWs.Range(resultWs.Cells(1, 1), resultWs.Cells(lastRow, 1)) .Interior.Color = RGB(173, 216, 230) ' 薄い青 End With With resultWs.Range(resultWs.Cells(1, 2), resultWs.Cells(lastRow, 2)) .Interior.Color = RGB(224, 255, 255) ' かなり薄い青 End With ' 1行目のC列から最終列まで薄い緑で塗りつぶす With resultWs.Range(resultWs.Cells(1, 3), resultWs.Cells(1, lastCol)) .Interior.Color = RGB(144, 238, 144) ' 薄い緑 End With ' レコードセットと接続を閉じる rs.Close cn.Close ' オブジェクトの解放 Set rs = Nothing Set cn = Nothing resultWs.Activate End Sub ' |
(画面クリックして拡大)
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(サンプルプログラム)
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 80 81 82 83 84 85 86 87 88 89 90 91 92 93 94 95 96 97 98 99 100 101 102 103 104 105 106 107 108 109 110 111 112 113 114 115 116 117 118 119 120 |
' ' Option Explicit ' 厳格な変数宣言を強制 Sub sql_totalling02() ' メインのサブプロシージャ ' 参照設定が必要です (Microsoft ActiveX Data Objects Library) Dim cn As ADODB.Connection ' データベース接続用のオブジェクトを宣言 Dim rs As ADODB.Recordset ' データの集合を扱うオブジェクトを宣言 Dim strSQL As String ' SQLクエリ文を格納する文字列変数を宣言 Dim sourceWs As Worksheet, resultWs As Worksheet ' ソースデータと結果を表示するワークシートオブジェクトを宣言 Dim field As ADODB.field ' データフィールドを参照するオブジェクトを宣言 Dim i As Integer ' ループカウンタとして使用する整数型の変数を宣言 Dim lastRow As Long, lastCol As Integer ' 最終行と最終列を格納する変数を宣言 ' ワークシートの指定 Set sourceWs = ThisWorkbook.Worksheets("SourceData") ' ソースデータが含まれるワークシートを設定 Set resultWs = ThisWorkbook.Worksheets("resultData") ' 結果を表示するワークシートを設定 ' オブジェクトの宣言 Set cn = New ADODB.Connection ' データベース接続オブジェクトを初期化 Set rs = New ADODB.Recordset ' レコードセットオブジェクトを初期化 resultWs.Cells.ClearContents ' 結果シートの内容をクリア ' データソースとしてExcelファイルを指定する接続文字列 cn.ConnectionString = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" & ThisWorkbook.FullName & ";Extended Properties=""Excel 12.0 Xml;HDR=YES"";" ' Excelファイルへの接続文字列を設定 ' データベースに接続 cn.Open ' 接続オブジェクトを開く strSQL = "SELECT [事業所], YEAR([日付]) AS [年], " & _ "SUM(IIF([勘定科目]='修繕費', 金額, 0)) AS [修繕費], " & _ "SUM(IIF([勘定科目]='通信費', 金額, 0)) AS [通信費], " & _ "SUM(IIF([勘定科目]='雑費', 金額, 0)) AS [雑費] " & _ "FROM [" & sourceWs.Name & "$] " & _ "GROUP BY [事業所], YEAR([日付])" ' 実行するSQLクエリを設定。事業所と年ごとに、修繕費、通信費、雑費の合計を計算 ' SQLクエリを実行し、結果をレコードセットに格納 rs.Open strSQL, cn ' SQLクエリを実行して結果をレコードセットに格納 ' 表題(フィールド名)を1行目に表示 i = 1 For Each field In rs.Fields resultWs.Cells(1, i).Value = field.Name i = i + 1 Next field ' クエリ結果のフィールド名を結果シートの1行目に表示 ' 結果を「resultData」ワークシートに出力(2行目から) resultWs.Range("A2").CopyFromRecordset rs ' クエリ結果を結果シートに出力 ' レコードセットと接続を閉じる rs.Close cn.Close ' レコードセットとデータベース接続を閉じる ' オブジェクトの解放 Set rs = Nothing Set cn = Nothing ' レコードセットと接続オブジェクトのメモリ解放 ' 最終行と最終列を取得 With resultWs lastRow = .Cells(.Rows.Count, "A").End(xlUp).Row lastCol = .Cells(1, .Columns.Count).End(xlToLeft).Column ' 結果シートの最終行と最終列を取得 ' 罫線を引く With .Range(.Cells(1, 1), .Cells(lastRow, lastCol)).Borders .LineStyle = xlContinuous .Color = vbBlack .Weight = xlThin End With ' 結果範囲に罫線を設定 ' 1行目のC列から最終列まで背景色を薄い黄緑色に塗りつぶす With .Range(.Cells(1, "C"), .Cells(1, lastCol)).Interior .Color = RGB(198, 239, 206) End With ' 1行目のC列から最終列の背景色を設定 ' A列目の最終行まで背景色を薄い水色に塗りつぶす With .Range(.Cells(1, "A"), .Cells(lastRow, 1)).Interior .Color = RGB(183, 222, 232) End With ' A列の背景色を設定 ' B列目の最終行まで背景色をもっと薄い水色に塗りつぶす With .Range(.Cells(1, "B"), .Cells(lastRow, 2)).Interior .Color = RGB(221, 235, 247) End With ' B列の背景色を設定 End With ' 結果シートに対してフォーマット設定を適用 resultWs.Activate ' 結果シートをアクティブにする End Sub ' ' |
(画面クリックして拡大)
また、VBAに関するテクニックや便利な手法などをこのサイトに掲載していきますので、定期的に参照していただけると幸いです。