【VBA】マスタファイルから、一人ひとりに合わせて異なるファイルを作成する

本日も引き続き、業務の整理。今日改善したファイルは、前回紹介したファイルと少し似ている。でも、今回はPDFへの出力ではなく、ファイルそのものを渡す、という業務。 【VBA】一気にPDFに出力できたらラクなのに・・・を解決  

みんなが注意深ければ不要な作業をあえてする

これは、作業の効率化とは対極にあるような業務かもしれない。元々、前任者はもっと簡単に、ある意味効率的に捌いていた。

タスク概要

職位によって、いくつかのグループに分け、年に1回ファイルを作成。新年度開始と同時に数種類のファイルを全スタッフに宛てたメールで配布し、各スタッフは自分の職位に合ったファイルに記録をつけ、期中何度か設定されている提出日にそのファイルを提出する、というもの。

問題点

引継ぎの際に、前任者からは「たまに使うべきファイルを間違えていたり、番号を間違えて入力していたり、未入力項目があったり、細かいミスがあって、差し戻しが多いから、提出期限は余裕を持って設定した方がいいよ」と言われた。そんな親切なアドバイスを、こともあろうに私は、「大げさだな。自分の情報をうっかり忘れるとか、あっても数件でしょ」と軽く見ていた。 しかし実際経験してみたら、毎年の恒例行事（？）にも関わらず、あまりのミスの多さに驚かされた。確認するのが私の仕事なので、確認したくないとは言わないが、それにしてもちょいミスばかり。

• 自分の使うべきファイルを間違える（一見すると内容が同じに見えるので、間違うのも理解できる。）
• 必要事項の入力漏れが想像以上に多い（嫌な感じに、こっちで対応可能な入力漏れ）

最初と最後、どっちでラクをするのがよりラクなのか

前任者のやり方は最初が非常にラク。何も言われずに仕事を渡されたら同じことをしただろう。しかし、前任者が年度の初めに送ったファイルの回収からこの業務に関わってみて、このやり方にやや疑問を感じた。だって、めちゃくちゃ面倒くさい。 そこで、次の配布時には、一人ひとりに宛てて必要事項を全部入力済みのファイルを送ることにした。 結果、初歩的なミスはなくなり、本題の内容確認にのみ集中することができるようになった。この業務においては、最初を少し工夫することで全体の稼働時間を減らすことを実現した。

  スタッフ《全員》が対象で、使用するファイルが《人により異なる》という内容であったため、うまく行ったが、申請書のような特定の人だけが対象のものだったり、反対に全員同じ内容の項目でいい場合には、当たり前だが、こんな手とり足とりの対応は非効率で不要だと思う。

改善方法

以下が私が試した改善策。前任者のフローと比べるとより面倒くさくなっている感じがするかもしれない。

手始めにファイルを統一する

「別々のファイルを渡す」ということをしなくてはいけないのに、《統一する》というのは変かもしれない。ただ、それぞれのファイルの構成があまりにも似ていたので、元を一つにした方が効率的になると思ったのだ。 職位によって設問が異なる程度だったので、前回の通知書の応用で、データシートと設問シートを用意。

• データシート：職位を含むスタッフの情報¹
• 設問シート：職位と設問番号の表
• 回答用シート：前回の通知書同様、VLOOKUPで各設問を変更

これでいくつもファイルを管理する必要はなくなった。ファイルが複数あると、事前の年度を直す作業もファイル分しなくちゃいけないけど、一つだともちろん一回で済む。 従来は、データシートや設問シートがなく、回答用シートがあるだけだったのだが、同じファイルに管理者用のシートが追加された。この受信者には見せたくないシート群をどう処理するかが次の課題。

管理者用のシートを隠すには？

一番簡単な方法はシートの《非表示》機能を使う方法。この方法は、個人的に好きじゃない（！）。ファイルサイズが不要に大きくなるし、《非表示》になっていることが何かのタイミングで引き継がれなかったときにゴミが埋まったファイルが出来上がってしまう。 【Excel】難しい数式や機能を覚えるよりも先に学ぶべきこと   そこで、《シートを隠す》のではなく、《必要なシートだけ新しいファイルにコピーする》という方法を取る。 マクロを使わずに作業をした場合、以下のような手順となる。

1. 前回の通知書の要領で、スタッフ情報により変更となる部分をVLOOKUPなどの数式を用いて作り込む。
2. 特定のスタッフを選択し、その情報に合わせて回答用シートを変更させる。
3. 2.の状態で、回答用シートだけを選択し、新規ファイルにコピー。
4. リンクを切って、ファイルを保存。

別ファイルに移動させて、リンクを切るため、出来上がったファイルは、VLOOKUPなどの管理者用のシート参照の数式がない状態となる。 これまでちょこちょこ使ってきた小さなマクロの組み合わせでこんなことが出来る。今年の改善では、Power Queryを利用したデータの更新作業の簡略化をしたわけだけど、以前作ったコードを見て、過去の自分の発想に一瞬追いつけず、軽く敗北感。

マスタファイルのシート構成例

実際のファイルをかなり単純化している。

• 赤字：数式で対応する部分。
• 青字：マクロで対応する部分。

  この例ではほぼ記入なしだが、一番最初のシートには、シート構成・作業手順・注意事項などを記載。また、手順の説明でボタンを設置。このボタンにマクロを登録している。内容を複数シートに分けてもいいかと思うが、個人的な趣味で１枚にしている。 データシートを参照するために、マクロを走らせている最中にフォームの上には番号が表示される（黄色ハイライト部分）。マクロの終わりで消去するため、完成したファイルには何も表示されない。この方法が可能なのは、別ファイルにコピーしてリンクを切る方法を取るため。同じファイルだと、この仮入力データを消した途端、他にも影響が出てしまう。

マスタファイルから異なるファイルを作成するマクロ

それでは、今回のコード。元々、この業務を違う人にやってもらう日が来るかもしれないという考えで、コメントは英語で書いていた。書き直すのが面倒くさかったので、簡単な部分はそのままにしている。

シート構成によりワークフローを簡単にすることが可能

前回の通知書のシート構成と同じく、今回もデータシートを作り、フォームから参照している。いろんな場所にデータを散らすよりもこのようにしておく方が応用がきくのでオススメ²。 前述のようにこの業務には続きがあり、出来上がったファイルを各スタッフへ送信しなくてはいけない。この各スタッフへのメール送信は、Wordの差し込み印刷機能を使って行っている。その際にも、Databaseシートの表が活躍。 話が逸れてしまうが、私はWordの差し込み印刷機能が好きで、前回の通知書のフローでも、「差し込み印刷機能使えばいいのに」という提案もしてみている。しかし、「ファイルが分かれるのは好ましくない」と却下されてしまった。あんなにエクセルファイルをバッラバラにされていたのを放置しておきながら、何を今さらファイルの分割が良ろしくないだなんて、どの口が言ってるんだ、と思いはしたけど、バラバラのファイルに苦しめられたのは確かなので、反論はしなかった。 【Excel】他人から渡された使いにくいファイルたちとの付き合い方：その１ 【Excel】他人から渡された使いにくいファイルたちとの付き合い方：その２   閑話休題。日常的にクロス集計の表などに慣れ親しんでいると、最初はデータシートの作り方などがよくわからないかもしれない。そんなときには、初めから仕上げようとせずに、出来る範囲で取り組んで、少しずつ改善していくのがいいと思う。繰り返すうちにデータシートとフォームシートに分ける方法のコツが掴めてくる。

最初の苦労も本当の最初だけ

当該業務の作業手順を大きく変更したのだが、個人対応の大半はマクロがやってくれるので、想定よりも作業は簡単。しかも、最初の年こそコードを書いたりしたが、次の年からはその必要がなくなるので、事前準備の大変さはほとんど無くなった。 また、当初抱えていた問題点も解決。使用するファイルの間違いや、基本情報の入力漏れはゼロ。提出されたファイルの内容の分析などに集中出来ている。 このフロー改善は成功³。