ネットあいち産業情報　IT特集　仕事に役立つエクセルマクロ　第３回　

トップ > IT特集 > 仕事に役立つエクセルマクロ　第３回　～各種ブックの統合とバッチ処理～

仕事に役立つエクセルマクロ　第３回　
～各種ブックの統合とバッチ処理～

阿部　晋也

記事更新日.09.03.10

愛知工業大学　経営情報科学部　非常勤講師

システム開発・楽曲制作・教育研修・DVD制作
Orange-Piece（オレンジピース）

複数の処理をまとまった単位で実行することを「バッチ処理」と呼んでいます。Excelのマクロを使うことができれば、ボタンひとつで簡単に集計を済ませることが可能になります。
特に“毎日”といった頻度でExcelを使った集計を手作業で行っている場合は、マクロの導入を検討したほうがよいでしょう。小規模の集計処理であれば、半日ぐらいあれば、マクロを構築することができます。
（ここから「回答用ワークシート」がダウンロードできます）
（ここから「集計用マークシート（完成前）」がダウンロードできます）
ここから完成後のマクロがダウンロードできます。

１．多少、より道

いつも難しい話題ばかりでもつまらないので、マクロを使った場合の作業工数を考えたとき、いったいどのくらいで元が取れるのか考えてみました。マクロを作ったことで、10分かかっていた作業がボタン１つ実行できるようになり、これが3分でできるようになったとすると、次のような表で考えることができます。

	マクロ未使用時	マクロ使用時
１日目	10分／日	240分（マクロ制作に4時間）
２日目以降	10分／日	3分／日

▼作業工数の変化

すると、だいたい１ヶ月ぐらい経って、元がとれるという結果になりました。最初の制作に時間がかかるかもしれませんが、使い続けることで“作っておいてよかった”ということになります。このことから、

長く使い続けることができるマクロを構築しよう

ということが大切なポイントであることがわかります。何が何でもマクロにすればよいというわけではなく、業務フローの中でどの部分をマクロにすると効率化が進み、さらにできるだけ簡単にマクロを作成できないのかという点を検討する必要がありますね。　これは通常のシステム開発にも当てはまります…話が長くなりそうなので、本題に戻ることにしましょう。

２．今回のシナリオ

●作業フローの確認
「会社の報告書をExcelで記録していて、メールに添付して提出する」、また、「アンケートの質問をExcelに書いておき、回答を記入してもらって、回収する」など、エクセルの用途は様々です。
　この手の業務では、最近Microsoft Office InfoPath 2007となるソフトウェアがありますから、情報収集するための入力画面を作成して、回収することはシステム構築として容易になっているかもしれません。
　しかし、普段使っているソフトウェアから離れることはユーザーにとって負担になることも少なくありません。社員全員がITに精通していて、どのようなソフトウェアの操作でもすばやく覚えられるとは限らないからです。
　その点、Excelはいまや中学校からでも教えていることもあり、高い普及率を保っているのが現状です。できれば、普段から使い慣れている入力画面で業務に組み入れ、効率化を図っていきたいものです。
　今回のシナリオは、複数のブックの集計です。社員１人ずつが１つのブック（ワークシート）に情報を記入し、メールで管理者に報告します。

（下の画像はクリックすると拡大します）

管理者は、締め切りを金曜日21:00として、メールで添付されたExcelのファイルをPCのフォルダに保存します。フォルダの中のExcelファイルをマクロで集計した上で、集計シートに書き出すといった操作を行います。

●ブックとワークシートの違い
ここで用語の意味を明確にしておきます。それはブックとワークシートです。ワークシートは、大きな方眼紙に相当し、巨大なセルの集合です。Excelをインストールした直後では、ワークシートが３つ開いた状態になります。

▼ワークシート

これら複数のワークシートを１つのまとめたものが「ブック」です。一般的に“Excelファイル”と呼ぶものは、このブックを示します。

▼ブックの考え方

今回作成したマクロには、外部に保存したブックを呼び出す記述が存在していますので、ある程度学習を進めていくと、かなり応用が広がると思います。

●回答・集計用ワークシートの作成
今回のシナリオでは１つのブックの中に１つのワークシートしかない状態にします。もちろん、複数のワークシートをもつこともできますが、操作が複雑になるので、１つのワークシートにしておきました。項目数は、練習レベルなので氏名と３問の質問だけにしました。

▼集計の対象となるワークシート

このワークシートからそれぞれの質問の回答数を集計して、集計結果をシートに書き出します。集計結果を表示する画面は次のようなイメージを想定しています。

▼集計結果のワークシート（「集計」ボタン押下時）

３．計画

●計画を立てる前に
マクロ（VBA）は、ITが進化していても魔法のようにパッとできるものではありません。１回目の記事で登場したように操作手順を確認したり、また２回目の記事のように自動化する作業の中で規則性をみつけたりする情報収集が大切です。このような思考回路はデジタルの話ではなく、かなりアナログな話です。
紙と鉛筆を出してきて、時には情報を書き出したり、自分で操作を順番に並べてみたり、いろいろな条件を想像する訓練が必要です。
また、Visual Basic for Application（VBA）の知識が必要になります。しかし、こちらはすぐには覚えられませんので、試行錯誤の連続になることでしょう。ただ、救いがあり、

インターネットユーザには同じようなことで悩んでいる人がいるかもしれない

ということがあります。最近のインターネットでは質問掲示板を開設しているところがあります。たとえば、「moug モーグ」や「答えてねっと」などです。これらの過去の記事を参考にすれば、数日悩んでいた疑問が１日で解決できるようになるかもしれません。

●アプローチ
さて、本記事はどのような手順でマクロを作成するのか、考えてみましょう。まず、「今回のシナリオ」から注意しておきたいところを列挙することです。

▼作業フローから…

▼アンケート票から…

▼集計結果のワークシートから…

このような疑問はほんの一例です。実際にはもっといろいろことを想定しないといけないのですが、逆に神経質になりすぎてしまってはマクロが増えてしまうだけです。マクロを組む前にできるだけ疑問点を解消しておき、マクロの制作時間も考慮した上で、制作範囲の策定作業に入ります。

●制作範囲の策定
１回目や２回目ではあまり意識しませんでしたが、マクロを組むときには、全体像を記録しておくべきです。要するに闇雲にマクロを組み立てるのではなく、道筋を立ててから制作にとりかかるのが本来のやり方です。今回は時間がありませんので、流れを先に示してしまいます。大筋のマクロの流れは下図の通りです。プログラムを追うときの参考にしてください。
（下図をクリックすると拡大します）

いや、しかし巨大なプログラムを考えてしまったものです。これは、回答ブックと集計ブックを比較する処理をかなり原始的に行っているからです。この方法では、質問数が100近くになるとおそらく数分はExcelが「ガーガー」いっているように忙しく動くことになると思います。質問数が100近くになり、早く計算させたいのであれば、データベースシステムの導入を検討したほうがいいかもしれませんね。
この本連載では、２回目と同様に、ボタンを作成していただき、コピーして貼り付ければ、とりあえず動くという状態にしておきます。ファイルもダウンロードできるようになっていますから、動作を試す分には問題ないかと思います。

４．VBAを使う

●ボタンの作成、コードの貼り付け

ボタンの作成の操作は２回目の記事と同じなので、リンク先を参考にしていただければ、幸いです。また、貼り付けるデータもダウンロードできるようにしましたので、まずは下記、リンク先を開いて確認してください。
ここからコードをダウンロードする

実際にコード貼り付けると次のようなイメージになります。

▼「集計」ボタンを押下するから①まで（下をクリックすると拡大します）

▼①～②の区間（下をクリックすると拡大します）

▼②以降

●ほかのブックを開いて参照する

実際の講習のように全部のコードを解説したい気持ちはあるのですが、かなり時間がかかってしまいそうなので、特に今後に役立ちそうな文法だけを取り上げておきましょう。まず、VBAでセルを選択する方法ですが、これは次の２種類をよく使います。
・ Range（セル番地）　　　
例：ワークシートのセルB3を示す
Range("B3")
・ Cells（行番号,列番号）
例：ワークシートの３列目、１行目を示す
Cells(3,1)

これに対して、ワークブック（“ブック”のこと）やワークシートを示す情報を追加すると次のようになります。
WorkBooks("ブック名").Worksheets("ワークシート名").Range("B3")
WorkBooks("ブック名").Worksheets("ワークシート名").Cells(3,1)

この文法を知っていれば、たとえ他のExcelファイルであっても自由に呼ぶことができます。ただし、ワークブックを開くという処理が必要になります。

Dim totalWB As Workbook ’ワークブック用の作業領域をtotalWBとする
Set totalWB = Workbooks.Open(XXX)　 'その作業領域にXXXワークブックを指定する

このように作業領域（変数）を指定しておけば、わざわざWorkBooks("ブック名")と記述しなくても、作業領域名.～という形で記述できるようになるので、作業が楽になります。
しかし、今回のマクロで登場した回答ブックや集計ブックのように、１つのマクロで複数のブックを扱うときには、１点注意が必要です。それは、ブックをアクティブにする必要があるということです。Open～でワークブックを開いたときには自動的にアクティブになりますが、残念なことに他のブックをOpen～すると以前のワークブックは非アクティブになってしまうので、今回のマクロの随所に、

totalWB.Activate
answerWB.Activate

という記述が登場します。これは、「totalWBを選択したよ（集計ブックを開いたよ！）」「answerWBを選択したよ（回答ブックを開いたよ！）」というようなイメージです。マクロ（VBA）は正直ですから、「ブックを開かないと参照できませんよ」というようなエラーになってしまうことがあります。

５．終わりに

さて、無事に動かして試していただけたでしょうか？第３回はかなり専門的でしたが、業務ではよく扱うトピックかと思うので、これをうまく改造すれば、何かに役立てるのではないかと思います。
また、これをきっかけにマクロを発展させたいという方は、ぜひ２回目の記事と３回目の記事の機能をあわせることにチャレンジしてください。セミナーの受講票を収集して、フォルダに保存し、受講人数を３回目の記事を参考に集計する。それから受講票を印刷するマクロを２回目の記事を使って、自動化することも可能になります。
　ぜひ、あきらめずにチャレンジしてみてください。