サイト内検索

アイディア 基本

VBAコード習得のきっかけは標準モジュール活用から

集計業務へVBAを使う初歩的な使い方で、かつ意外と便利な方法をお伝えします。ちょっとしたデータ整形でも、対象とする個々のデータに応じて、実に様々なコードを扱う必要があります。そこで、便利な使い方として標準モジュールを活用する方法があります。

今回は、わたしがまだVBAを使い集計業務を始めた頃、実際の集計業務にどんな感じでVBAを取り入れ、集計していたかをお話ししたいと思います。

その頃は、当然そんなに長いVBAコードは書けませんので、もっぱら短いVBAコードからなるプロシージャを数十個、1つの標準モジュールに集めて集計業務の一部に取り入れていきました。

VBA入門者であれば、はじめは短いVBAコードから集計業務に取り入れて、早くVBA集計業務の改善効果を実感したほうが、その後のモチベーションにもなります。今回ご紹介する方法もその1つの集計業務のやり方としてお勧めです。

Excelファイルの準備

VBAを使い集計業務を始めた頃の様子をお伝えしやすいように、サンプルのExcelファイルを用意しましたので、まずは下のExcelファイルを用意してくださいね。なお、Excelのファイル形式は、97-2003形式になります。

Excelワークシート3枚のシート名確認
Excelファイルのシート説明

今回の3枚のワークシートは、前回まで集計作業のサンプルにも使っていたSampleSheet01~03です。赤枠のシート名をそれぞれ「テンプレート」、「A部」、「B部」に変えてあります。

集計業務でよくある話ですが、ある資料を取りまとめ集計・作成する際に、その元ネタになるデータを各部署の取りまとめ担当や、データの所有者個人から集めてから合算集計しますよね。

同時に、各方面から集まったデータを合算集計しやすいように、 取りまとめ側が回答用テンプレートを 事前に 作成しそれを配布することもあります。

今回は、そんな集計業務を想定した例でお話ししますね。ここで、あなたは全体の集計を取りまとめる責任者とします。そして、全体集計をする前に、A部とB部に集計用テンプレートを配布し、データの確認と修正をお願いしたとします。

あなたは、後日返ってきたデータを集計しやすいように、テンプレートを準備しましたが、あいにくA部とB部の担当マネージャーは、ちょっと癖のある人でした。

A部のマネージャーは、必ず集計結果に小計行をつけて返し、またB部のマネージャーは、必ず「棚卸資産」の集計列を挿入して返してきます。

普通ならここで一言といきたいところですが、あなたにはVBA集計スキルがあるのでいつもニッコリ受け取ります。

ということで、今あなたの手元には、各部から帰ってきた小計行付きの「A部」シートと「棚卸資産」列付きの「B部」シートがあります。このまま余分な行と列があっては、最終的な資料を集計するための合算集計がやりずらいですよね。

そこで、ここからVBAを使ってデータの整形作業に入ります。

標準モジュールの準備

準備したExcelファイルに、標準モジュールを追加します。また、VBE画面は、「Alt」+「F11」キーで表示しておいてください。

そのモジュールに、下記2つのプロシージャをコピペしてください。

 

 

次図は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を使い続けるきっかけにもなる「標準モジュール」の使い方を紹介しました。日々手作業の集計業務の中に、少しずつ取り入れることで、徐々に自動化できる部分を増やしてみませんか。

[スポンサーリンク]



サイト内検索

-アイディア, 基本
-

Copyright© 集計用VBAサンプルコードをやさしく学ぼう , 2020 All Rights Reserved.