エクセルのマクロで請求書を管理！出し忘れを防ぐには

日々行なっている作業がきちんと行われているかどうかは、毎朝一番初めの仕事としてチェックするなど習慣化しておくことが大事ですが、その場合、きちんと一覧表になっていて一目で確認できることが重要です。

しかし、その一覧表を作成すること自体が面倒だと、毎日の作業として長続きせず、最悪の場合請求書の出し忘れが起きることもあるかもしれません。

そこでExcelを使って簡単にそのような一覧表が作れるような仕組みを作成しておくのですが、その入力も出来るだけ簡単に少ない操作で確実に操作できることが重要です。
そのためには様々な自動化の手法を使って効率よくチェック表を作成していく方法がありますが、Excelの自動化と言うとどうしてもマクロやVBAといったプログラム的な知識が必要になってくるように感じます。

しかしExcelには手で作業した動作を単純に記録してくれて、その作業を何度も繰り返すことができる、マクロの記録機能があります。

これを使うことによって、プログラムに一切触れずに自動化することができます。
今回はその［マクロの記録］の方法を使って、とても簡単に「納品」「請求」「入金」といった日々の作業のチェックが管理できるような仕組みを作成していきます。

サンプルファイル

サンプルファイルはコチラ

作成するチェック表

サンプルファイルをご覧ください。

A列からは作業の入力欄です。
それぞれの案件に対して、「納品」「請求」「入金」の作業が行われたら、
セルB4に案件名、セルC4に作業名を入力し、
その後にこれから作成する「登録」のボタンをクリックしたら、
その内容がE列からの一覧表にどんどん蓄積できるようにします。

その蓄積された内容に対し、I列から、案件の一覧表に対して、「納品」「請求」「入金」の欄がありますので、そこにその案件が行われた日付を表示するようにします。

入力しやすくする入力規則の設定

セルB4に案件名、セルC4に作業名を入力するのですが、
これも一回一回キーボードから入力すると面倒です。
選択肢の中から選べるようになっていると簡単に入力できるので、その設定をします。
案件名のセルB4をクリックして、［データ］タブの中の入力規則をクリックします。

入力規則のダイアログボックスが表示されるので、設定タブの中の入力値の種類の中から［リスト］を選択します。

元の値のボックスに選択肢の範囲を指定するのですが、今は案件が3件、I2からI4の範囲に入っていますが、これから案件が増えることも見越してI2からI100の範囲で設定し、OKボタンをクリックします。

するとセルB4に下向き三角が表示され、案件を選択できるようになります。

セルC4には、J1からL1までのセル範囲の「納品」「請求」「入金」の中から選択することになるので、同様にリストの設定でJ1からL1までのセル範囲を設定します。

あらかじめ設定しておくもの

日付は、本日の日付を設定するので、
セルA4に「=TODAY()」の関数を入力しておきましょう。

マクロの記録機能

今回の作業を、仮に手作業で行う場合は次のような作業を記録するようなことになります。マクロの記録の前に操作せずに文面で作業を整理しておきましょう。

まず蓄積するデータに、現在のデータを追加していくのですが、データが追加されていくにつれ、記録すべきセルはE列からのどんどん下の行にずれていきます。
このように一回一回違うセルに貼り付けるという動作は、今回行う［マクロの記録］機能は苦手なのです。
必ず同じ動作で記録できるように、まずはE2からG2の範囲に下方向へ移動する［セル挿入］を行います。
その後、A4からC4までのセル範囲をコピーし、セルE2からG2までの範囲に貼り付けます。この貼り付けはただ貼り付けてしまうとセルA2の日付は計算式なので、明日になれば明日の日付に変わってしまいます。

そこで貼り付ける方法は、計算の結果のみである［値の貼り付け］で貼り付けていきます。
書式はA4からC4までの範囲のものをそのまま使う貼り付けの方法を使うので、［値と元の書式］を使います。

最後に、セルB4とC4の入力内容をDeleteボタンで消して、次の入力に備えるために、セルB4を選択して終了となります。
この流れで作業を記録すれば、毎回同じセルに対して同じ動作をすればよくなります。

ではこの流れで［マクロの記録］をしてみましょう。
記録の前に、案件に「A産業電話工事」と作業に「入金」を選んでおきましょう。

マクロの記録は［表示］タブの中の［マクロ］の中の［マクロの記録］で開始します。

マクロの名前を設定しなければいけないので、今回は「登録」とし、OKボタンをクリックします。

ここからの操作はそのまま記録されるので慎重に行きましょう。
セルE2からG2までの範囲を選択して、右クリックして［挿入］を選択します。

［下方向にシフト］を選んでOKボタンをクリックします。

A4からC4までのセル範囲を選択して、右クリックして［コピー］を選択します。

セルE2からG2までの範囲を選択して、［ホーム］タブの貼り付けの下向き三角をクリックして［値と元の書式］をクリックします。

セルB4とC4を選択してDeleteキーを押し、入力内容をクリアします。

セルB4を選択します。

ここで［表示］タブの［マクロ］の中の［記録終了］をクリックします。

これでマクロが記録されました。
今度は挿入タブの中の図形からテキストボックスを選択し、下の図のようにセルC1くらいにテキストボックスを作成し、中の文字に「登録」と入力します。
テキストボックスには任意の書式を設定してください。

このテキストボックスの周りの線にマウスのポインターをぴったり合わせて、右クリックし、［マクロの登録］をクリックします。

一覧の中に「登録」のマクロがあるのでこれをクリックしてOKボタンをクリックします。

これによって、テキストボックスをクリックすると登録マクロが動くようになりました。

集計表の作成

I列以降に案件の集計をしたチェック表を作成していますが、J2以降のセルにE列からの表を基にした登録日を表示するような計算式を作成します。

登録日は一覧表の中で、その案件、その作業に当たる日付を求めるのですが、VLOOKUP関数やINDEX関数などの行列関数で求めると、いくつかの関数を組み合わせて求めなければならないので、今回は、その案件その作業のデータの日付の最大値として計算してみたいと思います。
使う関数は比較的新しい関数で、「MAXIFS関数」です。

MAXIFS関数の書式は次の通りです。
=MAXIFS(最大値を求める範囲,条件の範囲1,条件1,条件の範囲2,条件2…)

今回、最大値を求める範囲はE列の日付です。日付の最大値というと計算的に違和感がありますが、日付の最大値を計算すると、最新の日付が求められます。
条件は二つあり、案件の範囲はF列でその中からI列に対する案件の条件で絞り込みます。
合わせて作業の範囲はG列でその中からJ1からL1までの項目に対する条件で絞り込みます。
セルJ2に計算式を作成しますが、その計算式はJ列からL列までコピーして使えるようにします。
そのためには計算式内の、E列、F列、G列はコピーしても動かないように$をつけて絶対参照にします。

また、セルI2の指定は、行方向では動くのですが、列方向にコピーしても絶対にI列を参照するのでIの前に$を、セルJ1の指定は、別方向では動くのですが、行方向にコピーしても必ず1行目を参照するので、1の前に$を付けます。

最終的に次のような計算式になります。
=MAXIFS($E:$E,$F:$F,$I2,$G:$G,J$1)

もしも上記の計算式で「#NAME?」エラーになる場合は、
ExcelのバージョンがMAXIFS関数に対応していないバージョンかもしれません。
MAXIFS関数はExcel2016の一部のエディションで先行して使えるようになりましたが、正式にはExcel2019またはOffice365で採用された新しい関数です。

もしそのような場合は、次の計算式で対応可能です。
この計算式は10000行までしか対応できないので、計算式内の10000の数字を適宜変更して使ってください。
=SUMPRODUCT(MAX(($F$2:$F$10000=$I2)*($G$2:$G$10000=J$1)*$E$2:$E$10000))

ではこの計算式をセルJ2に入力し、今後案件がどんどん増えることを想定して、J2からL100までの範囲にコピーしておきましょう。
日付が表示されるはずなのが、4万いくつという数字が表示されています。
これはExcelの日付は、実は1900年の1月1日からの日数で数えられているただの日付シリアル値と呼ばれる数字で、その数字が表示されているということになります。
また、その作業が発生していないセルや、案件がない行には0と表示しています。

今回は正の数字のみ日付の表示にし、それ以外は表示しないという設定が必要です。
そのような日付表示にするためには、Ctrl+1のショートカットキーでセルの書式設定を表示し、ユーザー定義で「m月d日;;」と設定します。「;;」を入れるのがポイントです。

これで仕組みは完成です。

ここで、案件に「Cコーポレーション電気工事」の作業に「納品」を設定し、登録ボタンをクリックしてセルJ3に納品に日付が入ります。
マクロの記録をしたExcelブックはExcelのブックとして保存するとせっかく記録したマクロが保存されません。マクロの記録をした場合は、そのブックはマクロブックとして保存しましょう。

まとめ

このようにマクロの記録は、実際の手作業を想定して、毎回同じ作業でもきちんとデータが蓄積されていくように考えるのがコツです。その方法さえわかってしまえば、その作業はマクロの記録をすることができるので、簡単に自動化ができるようになります。