売上データを記録していくと自動で請求書を出力しているシステムはお金を出せば便利なソフトがたくさんありますが、「お金を出すほどでもない」とか「経営者が許可してくれない」などの理由でソフトを導入できずに請求書を一つ一つ手作りしている方も多いのではないのでしょうか。
そこで、売上明細から自動で請求書を作成できるシステムを作ってみましょう。
請求先リストを作る
まずは請求書を送る請求先のリストを作成します。
すでに請求先リストがあるという場合はそちらを利用していただいて構いません。その場合は必要な項目をこちらのデータの項目と読み替えてください。
請求書出力のために最低限必要な項目は、「請求先名」「郵便番号」「住所」です。
そのほかにももっと細かい機能を付けたい場合はこちらの項目を増やしていくといいでしょう。例えば電話番号も表示したい、「備考を請求書の最後に表示したい」など
売上明細の構造を確認する
次にお使いの売上明細を確認します。
請求先リストと請求先名が完全一致しているか
ここで重要なのが先ほど作った「請求先リスト」と売上明細の「請求先」が完全一致している必要があります。完全一致させる方法は大きく3つあると思ています。
- 「入力規則」のリストを使う
- 請求先名ではなく請求先コードをキーとして利用する
- 「文字はすべて全角」「株式会社は必ず㈱」などルールを徹底する
入力するのが自分一人であれば確実性は高いでしょうが、3.の方法は作業する人によって違ったり、勝手にルールを変えられたりすることをどうしても避けることができないので現実的ではありません。
あとは1.か2.ですがこれはどちらも完全一致が担保されていますので好みでいいと思います。
今回の例では列数を少なめに表現できるので1.の方法でいきたいと思います。
入力規則のリスト作成方法はこちらの記事を参考に作成してみてください。プルダウン(ドロップダウン)を作る Excel(エクセル)
請求書を表示(印刷)する
請求書のイメージを作ってみました。
文字が青色のところは関数(数式)が入っているセルです。黒色と白抜きの文字は値が入力されているので値は変わりません。
作り方を順番に解説していきます。
請求先を選択できるようにする
まずは請求先をプルダウンで選択できるようにしていきます。
プルダウンの使い方はこちらでも解説していますので参照してみてください。プルダウン(ドロップダウン)を作る Excel(エクセル)マクロ無しで２つのプルダウン(ドロップダウン)を連動させる(一組だけの連動の場合) Excel(エクセル)
「B6」セルの請求先名の欄を選択して「データ」タブ→「入力規則」ボタン→「データの入力規則」の順にクリックしてください。
「入力値の種類」を「リスト」に変更し、元の値を選択する
請求先名の範囲を選択し「エンター」キーを押し、最後に「OK」ボタンを押す。
これで請求先をプルダウンで変更できるようになりました。
請求先の住所を自動表示できるようにする
ここではINDEX・MATCH関数を使います。詳しい使い方が知りたい場合ははこちらの記事を参照してください。Excel(エクセル)INDEX関数とMATCH関数の組み合わせた使い方
「B4」セルにこのように記載します。
=INDEX(請求先!$E$3:$E$14,MATCH($B$6,請求先!$C$3:$C$14,0))
すると、「B6」セルに入力してある請求先の住所が表示されました。
同様に「B3」セルと「B5」セルも入力していきます。ただし「B3」セルには郵便番号の前に「〒」マークを挿入しています。
="〒"&INDEX(請求先!$D$3:$D$14,MATCH($B$6,請求先!$C$3:$C$14,0))
これで請求先名を切り替えることで住所などが切り替わる仕組みができました。
お支払期限を発行日から制御する
発行日やお支払期限はいろいろとルールがあると思いますが、ここでは発行日は出力した日、お支払期限は請求日の翌月の末になるようにしたいと思います。
「E4」セルには
=TODAY()「E5」セルには
=DATE(YEAR(E4),MONTH(E4)+2,0)と入力します。
この関数の意味は「E4」セルの日付の2か月後の前月末という意味になっています。
請求書に明細を載せる
今度は「売上明細」のデータを請求先に表示させたいと思いますが、データ行の請求先の違う行を除く必要があるので、単純にindex関数で引っ張ってくることができません。
それを実現させる方法がいくつかありますので、次回その方法を何パターンかご紹介させていただきます。売上明細から自動で請求書を作る２ Excel(エクセル)
コメント