ほんのちょっとのExcel操作と関数とVBAの知識だけで請求書発行ツールを作る

Excelは、関数を覚えた方がいいのですか、マクロを覚えた方がいいのですか？とよく聞かれるのですが、私は、Excelは機能も関数もVBAもすべて覚えたほうがいいと言います。
それぞれをほんの少しだけ知ってるだけでビックリするようなツールを作ることができます。
今回はその紹介をします。

請求書の発行の流れ

今回は次のような請求データがあったとします。

それに対して、年と月、取引先を指定して、該当する年月の取引先の請求書を作成します。

そのためには、データ一覧表から、該当のデータを抜き出す必要があります。
該当のデータを抜き出すのは、Excelではフィルター機能を使います。
しかし一般的なフィルターでは、セルで指定した、年と月、取引先のパラメータで抜き出すことができませんので、いちいちフィルターの設定を変える必要があります。
そこで、使える機能が、フィルターオプションです。
フィルターオプションは、データタブの中の、並べ替えとフィルターの中の詳細設定をクリックすることで、始めることができます。
このフィルターオプションでは、検索の条件として、条件の範囲をセル上に設定する必要があります。
今回は指定月の指定した会社名なので、条件としては、指定年月の1日以降、指定年月の末日以前の指定会社名ということになり、次のような設定を行います。

フィルターオプションの結果は必要な項目だけ抜き出すことができます。
請求書に記載する内容は、取引日と取引金額だけなので、取り出したものを出力する範囲として、次のようなセルを用意します。

今回、人が入力、指定するのは年、月、会社名ですので、次のような入力範囲を用意します。

さらに、指定する会社名は実際のデータから抜き出し、ひとつひとつループすると良いと考えられます。
元データの会社名の列を1列コピーし、重複の削除で、同じ会社名を1つに絞り込んで会社名一覧を作成する範囲もあるとよいです。

作成した請求書範囲は、印刷範囲を指定し、その範囲のみ1ページに印刷されるようにします。

ここまでの構成をシートに作成すると、次のようになります。

会社名一覧の作成

こういうツールは、データの処理される順番に作成します。
はじめは、会社名の一覧を作成します。
A列のデータをそのままP列にコピーして、データタブの重複の削除で一覧を作成する流れをマクロに記録します。
マクロ名は「会社名一覧作成」にしましょう。

マクロに記録したら、セルQ1に次の計算式を入力します。

=COUNTA(P:P)-1

これは、作成する会社数＝請求書数になります。この数の分、請求書作成の処理を繰り返せばいいのです。

入力欄の作成

I1からJ3に入力欄を作成します。
セルK1には1と入力しておき、この番号が会社の指定する番号とします。
こうすることで、後でVBAで繰り返し処理する時に楽になるのです。
セルJ3には次の計算式を入力します。

=INDEX(P:P,K3+1)

こうすることで、番号に対応する会社名を出すことができます。

条件範囲の作成

フィルター用の条件の範囲をセルE1からG2の範囲に作成します。
セルE2には次の計算式を入力します。

=TEXT(DATE(J1,J2,1),”>=yyyy/mm/dd”)

セルF2には次の計算式を入力します。

=TEXT(EOMONTH(DATE(J1,J2,1),0),”<=yyyy/mm/dd”)

セルG2には次の計算式を入力します。

=J3

抽出範囲の作成

条件範囲の下のE5、F5のセルにフィルターの結果を通出する範囲を設定します。
条件範囲は、フィルターオプションで処理をすると、その下をすべて消してしますので、その下に何も入力されていないところにしましょう。

フィルターオプション

実際にフィルターをするアクションをマクロに登録します。
マクロ名を「請求データ抽出」としましょう。

元データをクリックし、データタブの詳細設定をクリックしましょう。

次のように設定し、OKボタンをクリックします。

請求書の作成

セルI5からN5までの範囲に請求書を作成します。

セルI1には、=J3と入っています。
セルI9には、=L24と入っていて、ユーザー設定の表示形式が[DBNum3]”金” #,##0 “円”と設定されています。
セルI10には=TEXT(DATE(J1,J2,1),”yyyy年m月分としてご請求いたします”)と入っています。
セルI13には、=E6と入っていて、表示形式はm月d日の表示形式になっています。これが、I23までにコピーされています。
セルL13には、=F6と入っていて、カンマ区切りの表示形式になっています。これが、L23までにコピーされています。
L24は、オートSUMで合計を求めています。
この範囲をページレイアウトタブで印刷範囲の設定でこの範囲だけ印刷されるようにします。

請求書の印刷・PDF作成

請求書の印刷、またはPDFの作成をマクロに登録します。
マクロ名は「請求書発行」とすればよいでしょう。
ホームタブの印刷で印刷、または、エクスポートでPDFに出力します。

PDFにする際のファイル名は、今回の場合は「【請求書】株式会社東北（2019年5月）」とします。
このファイル名であれば後で検索する時に楽です。

PDFのファイル名を変更する

もし、PDFを発行する場合は、ファイル名をセルの値から自動的に設定できるようにします。

表示タブのマクロから、マクロの表示をして、「請求書発行」をクリックして、編集しましょう。
このようにファイル名になっている部分があります。

赤文字の部分を追加し、黄色の部分のようにファイル名の部分を書き換えます。

セルの年、月、会社名の文字を取り込んでファイル名をPdfFileNameという形で作って、それを保存するファイル名としました。

マクロを組み合わせる

ここまでで、
「会社名一覧作成」
「請求データ抽出」
「請求書発行」
の3つのマクロができました。
この3つを組み合わせるマクロを作成します。

「会社名一覧作成」ははじめの一回だけ動作すればいいですね。
「請求データ抽出」「請求書発行」はセルK1の値を入れ替えて繰り返し動作させる必要があります。
では、マクロの記録で、表示タブのマクロから、順に「会社名一覧作成」、「請求データ抽出」、「請求書発行」を実行しましょう。
マクロ名は「請求書処理」にします。

繰り返し処理

繰り返す処理はVBAでないと設定できません。
なので、ここでVBAを操作します。
表示タブのマクロから、マクロの表示をして、「請求書処理」をクリックして、編集しましょう。

それぞれのマクロが順番に実行するように記録されています。
まず、マクロの指定にブック名が入っています。
本来は、きちんとブックをマクロブックとして保存してからマクロの記録をすればよかったのですが、ファイル名を指定しなかったので、！マークまでのファイル名を削除します。

赤い部分の文字を追加します。

「会社名一覧作成」を1回実行しています。
「請求データ抽出」「請求書発行」をセルQ1に回数分繰り返し、その繰り返し数をセルK3に入力して処理しています。
これで、指定年月の請求書が一度に出来上がります。

ボタンに登録

マクロの記録を呼び出すのは面倒なので「請求書処理」のマクロをワンクリックで呼び出せるようにします。テキストボックスをセルK1くらいに準備して、右クリックで「請求書処理」のマクロの登録をします。

これで完成です。

最後に

今回は、Excelの重複の削除機能・フィルターオプション機能、様々な計算式と関数、マクロの記録、VBAと全部をほんの少しずつ使って一つの実務で使えるツールを作成しました。
これはVBAですべて作成することもできますが、それはあまりにも大変でスキルも必要です。開発工数もそれなりにかかるでしょう。
この手法であれば、初心者には無理だとしても、ある程度Excelを使いこなして、VBAも入門くらいまでは済んでいる方にとっては実現可能な自動化の手段です。
今回、非常に簡単な請求書の作成だったのですが、シートがたくさんあったりもっと複雑なデータ処理だったり、他のブックを操作するようなことであれば、もっとVBAの力が必要なのですが、逆に処理の流れを見直してシンプルにすることでここまでわかりやすくすることもできます。
このように業務をわかりやすくすることも一つのスキルです。
マクロの記録で、印刷したり、フィルターオプションだったりでは、ワンアクションをマクロに登録するという一見意味のないことをｓたようにも見えますが、そのワンアクションをマクロ名というわかりやすい名前に変換できるので、今回のような整理ができるということもポイントです。
ワンアクションのマクロの登録をして、それをボタンに登録しておけばいちいちリボンを探さなくてよくなるので、初心者に使わせるときに使いやすいシートにすることもできます。
ここからVBAやExcel関数、Excelの機能をより深く知るきっかけになるといいですね。
最後にですが、「請求書処理」のEnd Subの前に「MsgBox “処理終了”」と入れておくと処理が終了した時に分かりやすいです。

今回の作業データはこちらからダウンロードできます。

OneDrive

Sign in to your OneDrive cloud storage and Office Online.

1drv.ms