開発日記
VBA
業務改善
Excel VBAで帳票作成を自動化するには？システムの仕組みを具体的に解説
ぴーす君
「帳票を手作業で作るのは、もう限界！」
これまで5年間、Excelや業務改善に関するご相談をいただく中で、何度も耳にしてきた言葉があります。
「帳票を手作業で作るのが、もう限界です！」
元となるデータはある。
CSVやテキストファイルとして出力することもできる。
しかし、そのデータをそのまま使えるわけではありません。
必要な項目を抜き出して、並び替えて、集計して、決められた様式に転記して……。
場合によっては、同じ作業を何十件、何百件と繰り返すことになります。
そこで、よくあるご相談がこちらです。
「CSV（またはテキストなど）のデータがあるので、これを使って何とかできませんか？」
できます。
そして、こうしたケースはExcel VBAによる帳票作成の自動化と相性の良い業務の一つです。
では、実際にVBAで帳票作成を自動化すると、どのような仕組みになるのでしょうか。
今回は、一般的な仕組みから、Work Pieceがシステムを作る際に工夫しているポイントまで、できるだけ具体的にご紹介します。
「必要な情報はある。でも、帳票を作るまでが大変」
詳しくお話を伺うと、単純に「Excelへ転記するのが大変」という話ではありません。
必要な情報が複数のファイルに分散していることが、大きな負担になっている場合があります。
例えば、
Aのファイルには顧客情報が入っている
Bのファイルには売上情報が入っている
Cのファイルには商品情報が入っている
Dのファイルには担当者情報が入っている
というように、それぞれのファイルに必要な情報が分かれています。
そこで、それぞれのデータを確認し、必要な情報を組み合わせ、帳票の形に組み上げていきます。
さらに、業務によっては、
「データの中には存在しない情報を、担当者が判断して設定する」
といった作業が発生することもあります。
つまり、
データを集める
↓
必要な情報を探す
↓
複数のデータを組み合わせる
↓
足りない情報を設定する
↓
計算・加工する
↓
帳票に仕上げる
という、いくつもの作業を人がつないでいるわけです。
これを毎回、手作業で行っていると考えると……。
「もう限界です」と言いたくなるのも、無理はありません。
実際に、これまでこうした業務を手作業で続けてこられた方の苦労を想像すると、こちらまで「本当にお疲れさまでした……」と言いたくなります。
では、こうした業務をVBAでシステム化すると、何が変わるのでしょうか。
Excelでツール化すると、何が行われるの？
では、実際にExcel VBAを使ってツール化した場合、どのような処理が行われるのでしょうか。
大きく分けると、基本的な流れは次の3つです。
① データの取り込み
↓
② データの加工
↓
③ データの出力
一見するとシンプルですが、先ほどのように複数のファイルに分散した情報を扱う場合、この3つの中にさまざまな処理が入ってきます。
① データの取り込み
まずは、帳票作成に必要なデータをExcelに取り込みます。
例えば、
CSVファイル
テキストファイル
Excelファイル
複数のExcelファイル
などです。
ツールを使う人は、それぞれのファイルを指定するだけ。
あとはVBAが必要なデータを読み込みます。
必要な項目がない、想定していたデータと違う、といったエラーチェックもこの時に行い、後続の処理で異常が発生しないようにします。
② データの加工
データを取り込んだだけでは、まだ帳票は作れません。
取り込んだ複数のデータを組み合わせ、帳票に必要な情報を一つのデータにまとめていきます。
例えば、
「顧客コード」をもとに顧客情報を取得する。
「商品コード」をもとに商品情報を取得する。
「担当者コード」をもとに担当者情報を取得する。
といった処理です。
実際の業務では、取引データと商品マスタ、顧客マスタなど、それぞれ別々に管理されているデータを組み合わせるケースがあります。
例えば、取引データには「商品コード」しか入っていなくても、商品マスタと照合することで「商品名」や「単価」などの情報を取得できます。
同じように、顧客コードから顧客名を取得するなど、複数のデータを照合することで、帳票作成に必要な情報を揃えていきます。
このように、元となる複数のデータをExcel VBAで照合・組み合わせし、帳票を作成するための加工データを作ります。
さらに、
必要なデータだけを抽出する
データを並び替える
数値を計算する
合計・小計を算出する
条件によって表示内容を変える
など、業務に合わせた処理を行います。
つまり、これまで人が、
「このファイルを見て……次にこっちのファイルを確認して……この情報と組み合わせて……」
と行っていた作業を、VBAが決められたルールに従って処理していきます。
③ データの出力
加工したデータが完成したら、最後に帳票として出力します。
ここでは、あらかじめ用意したExcelの帳票テンプレートに、加工したデータを配置していきます。
例えば、今回のような納品書であれば、
納品先の情報
納品日
商品コード
商品名
数量
単価
金額
合計金額
担当者
など、必要な情報をそれぞれ決められた場所に配置します。
このテンプレートに、②で作成した加工データをVBAで自動的に配置することで、納品書を作成します。
テンプレートもExcelファイルなので、住所や項目名、列幅は適宜修正することができます。（※出力する項目自体変更する場合や出力する列の位置を変更する場合はプログラムの修正が必要となります。）
また、業務に応じて、
顧客ごとに帳票を作成する
複数ページの帳票を作成する
Excelファイルとして保存する
PDFとして出力する
指定したフォルダに保存する
などの処理を組み込むこともできます。
つまり、これまで人が一件ずつデータを確認し、帳票へ転記していた作業を、「実行」するだけで必要な帳票をまとめて作成することが可能になります。
まとめ
ここまで、Excel VBAを使った帳票作成の基本的な流れをご紹介しました。
① データの取り込み
必要なファイルを指定し、データを取り込む。
② データの加工
複数のデータを照合・組み合わせし、帳票作成に必要なデータを作る。
③ データの出力
加工したデータを、あらかじめ用意した帳票の様式に配置し、帳票として出力する。
実際のツールでは、この一連の処理を利用者が迷わず操作できるよう、次のような画面にまとめています。
このように、利用者が行う操作はできるだけシンプルにし、複雑なデータの取り込みや加工、帳票への出力はVBAが処理するという仕組みにします。
帳票作成の自動化で大切なのは、単に「Excelにデータを貼り付ける」ことではありません。
「どのデータを使い、どのように組み合わせ、最終的にどのような帳票を作るのか」
という業務全体の流れを整理したうえで、その作業に合わせて仕組みを作ることが重要です。
もし現在、
「複数のファイルを確認しながら帳票を作っている」
「毎回同じようなデータ加工をしている」
「帳票を一件ずつ作成している」
といった業務があるのであれば、Excel VBAによる自動化ができるかもしれません。
「この作業は自動化できる？」という段階からでも、お気軽にご相談ください。