集計業務へVBAを使う初歩的な使い方で、かつ意外と便利な方法をお伝えします。ちょっとしたデータ整形でも、対象とする個々のデータに応じて、実に様々なコードを扱う必要があります。そこで、便利な使い方として標準モジュールを活用する方法があります。
今回は、わたしがまだVBAを使い集計業務を始めた頃、実際の集計業務にどんな感じでVBAを取り入れ、集計していたかをお話ししたいと思います。
その頃は、当然そんなに長いVBAコードは書けませんので、もっぱら短いVBAコードからなるプロシージャを数十個、1つの標準モジュールに集めて集計業務の一部に取り入れていきました。
VBA入門者であれば、はじめは短いVBAコードから集計業務に取り入れて、早くVBA集計業務の改善効果を実感したほうが、その後のモチベーションにもなります。今回ご紹介する方法もその1つの集計業務のやり方としてお勧めです。
目次
Excelファイルの準備
VBAを使い集計業務を始めた頃の様子をお伝えしやすいように、サンプルのExcelファイルを用意しましたので、まずは下のExcelファイルを用意してくださいね。なお、Excelのファイル形式は、97-2003形式になります。
今回の3枚のワークシートは、前回まで集計作業のサンプルにも使っていたSampleSheet01～03です。赤枠のシート名をそれぞれ「テンプレート」、「A部」、「B部」に変えてあります。
集計業務でよくある話ですが、ある資料を取りまとめ集計・作成する際に、その元ネタになるデータを各部署の取りまとめ担当や、データの所有者個人から集めてから合算集計しますよね。
同時に、各方面から集まったデータを合算集計しやすいように、 取りまとめ側が回答用テンプレートを 事前に 作成しそれを配布することもあります。
今回は、そんな集計業務を想定した例でお話ししますね。ここで、あなたは全体の集計を取りまとめる責任者とします。そして、全体集計をする前に、A部とB部に集計用テンプレートを配布し、データの確認と修正をお願いしたとします。
あなたは、後日返ってきたデータを集計しやすいように、テンプレートを準備しましたが、あいにくA部とB部の担当マネージャーは、ちょっと癖のある人でした。
A部のマネージャーは、必ず集計結果に小計行をつけて返し、またB部のマネージャーは、必ず「棚卸資産」の集計列を挿入して返してきます。
普通ならここで一言といきたいところですが、あなたにはVBA集計スキルがあるのでいつもニッコリ受け取ります。
ということで、今あなたの手元には、各部から帰ってきた小計行付きの「A部」シートと「棚卸資産」列付きの「B部」シートがあります。このまま余分な行と列があっては、最終的な資料を集計するための合算集計がやりずらいですよね。
そこで、ここからVBAを使ってデータの整形作業に入ります。
標準モジュールの準備
準備したExcelファイルに、標準モジュールを追加します。また、VBE画面は、「Alt」+「F11」キーで表示しておいてください。
そのモジュールに、下記2つのプロシージャをコピペしてください。
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 | '小計欄を行削除する Sub Sample028() Dim r As Long '行ルーチン処理用変数 Dim lngERow As Long 'レコード最終行番号用変数 '2列目に入っているデータの最終行番号を取得 lngERow = Range("B" & Rows.Count).End(xlUp).Row '行削除なので最終行番号側からルーチン処理 For r = lngERow To 2 Step -1 '2列目のデータの最後の文字に「市場」の '2文字が含まれている場合にThen以降を実行 If Cells(r, 2) Like "?市場" Then '行削除実行 Cells(r, 2).EntireRow.Delete End If Next End Sub |
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 | '「棚卸資産」の列を削除する Sub Sample030() Dim lngECol As Long 'データ範囲最終列番号用変数 Dim c As Integer '列ルーチン処理用変数 'データ範囲最終列番号取得 lngECol = Range("IV1").End(xlToLeft).Column '列ルーチン処理 For c = lngECol To 1 Step -1 '1行目の項目名が「棚卸資産」の場合に列削除 If Cells(1, c) = "棚卸資産" Then '列削除 Cells(1, c).EntireColumn.Delete End If Next End Sub |
次図は2つのプロシージャのコピペ後になります。ここで、1点補足です。下図赤線は、Excelファイルが97-2003形式のため、 VBAコードで特定の列を削除する 記事で紹介した最終列を取得するVBAコードに対して、変更した箇所になります。
VBAによるデータ整形
次にそれぞれのVBAコードを実行すれば、「A部」の小計行は削除され、「B部」の「棚卸資産」列は削除されますよ。
では、1つずつ実行してみますね。まず「A部」の小計行を一気に削除します。ただ、実行前に1つ注意点があります。「A部」用の小計行削除のVBAコードを実行する際は、以下の手順で実行してください。
- 「A部」のシートを一度選択する
- 「Alt」＋「F11」キーでVBE画面にする
- 「小計欄を削除する」のプロシージャ(Sample028)を実行する
(実行するVBAコード上にカーソルがあること） - 「F5」キーでVBAを実行
次に、「B部」の「棚卸資産」列を削除しましょう。手順は以下の通りになります。
- 「B部」のシートを一度選択する
- 「Alt」＋「F11」キーでVBE画面にする
- 「棚卸資産の列を削除する」のプロシージャ(Sample030）を実行する
(実行するVBAコード上にカーソルがあること） - 「F5」キーでVBAを実行
どうでしょう。上手くいけば、3つのシートはシート名を除き、すべて同じになります。これで、2つの部のデータシートが整形されましたので、次の合算のための集計作業もVBAを使ってスムーズに行えます。
なおVBAによる合算データの集計作業は、別の記事でお話ししますね。
標準モジュールをエクスポートしておく
さて今回の想定した集計業務の続きです。あなたは、A部とB部から受け取ったデータを取りまとめ集計をして、上司に報告する資料を難なく仕上げました。その時、上司から今回の資料を毎週レポートとして報告するよう依頼されました。
作業は下記のことを、週1で集計作業をすることになります。
- 毎週A部とB部からシートを集める
- 集めたデータをVBAで整形作業
- 整形後データをVBAで合算集計
- 報告する資料用の表やグラフの元表をVBAでクロス集計
上のタスク一覧から、A部とB部から受け取るシートを合算集計する前に、それぞれに対応したVBAコードで 、毎週 整形した方が効率が良さそうですよね。（実際の集計業務では、各マネージャにテンプレートに沿って返すようお願いするべきですが）
そこで、あなたは毎週繰り返される各部の整形作業を楽にするために、標準モジュールのエクスポートを使うことを思いつきました。
では、実行してみましょう。まず標準モジュールの名前は、「A部B部週報用」にしました。次に右クリックメニューから、「エクスポート」選択、デスクトップに保存します。
これで次週以降もこの標準モジュールをExcelファイルにインポートして、VBAを実行するだけで一瞬にしてデータが整形されます。
今回想定した集計業務の例は、データ行列数や部の数も少なく、VBAの威力は感じられなかったかもしれません。ただこれが膨大なデータ量で、方々から様々な形のデータが集まってくる場合は、VBA集計スキルの威力を実感できますよ。
標準モジュールはプチRPA
今回の使い方は、みなさんのExcel集計業務の中でも応用できそうですか？。一連の集計業務の中で、まずはどこかの一部分、簡単なところから、例であげたような短いVBAコードから成るプロシージャを作って、少しでも集計作業が早く、正確になれば良いと思います。
わたしの場合は、資料の取りまとめ役になった頃は、集計業務に慣れるまでテンプレートを準備する余裕がなく、他部署から毎週・毎月返し送られてくるいびつなExcelシートをコピペでデータ整形・合算集計を繰り返してました。
でもそのうち、「もっと楽したいなぁ」と思うようになり、今回の例のような標準モジュールに短いVBAコードを書いては、実務に少しずつ取り入れ、気がつけば、ほぼすべての集計業務にVBAを取り入れてました。
今思うと、それら短いVBAコードのプロシージャは、わたしにとってExcel版RPAみたいなものでした。
まとめ
今回は、VBAを使い続けるきっかけにもなる「標準モジュール」の使い方を紹介しました。日々手作業の集計業務の中に、少しずつ取り入れることで、徐々に自動化できる部分を増やしてみませんか。