エクセル請求書テンプレート作成ー5「請求書記載の効率化」

合計金額を請求金額のセルに転写

作成した請求書のテンプレートに実際の案件を入力する際、時間と労力を節約するために関数・四則演算を使って作業の効率化を図ります。取引内容、単価、数量、金額を列記した明細の欄は誤記や計算ミスの発生しやすい場所でもあり、これをできる限り避けることにも役立ちます。

下図は完成した請求書のサンプルです。このサンプルの作成手順をステップバイステップでたどっていきます。※下の画像をクリックで拡大表示
請求書の見本画像

VLOOKUP関数で取引内容から単価を自動表示

取引内容の詳細をドロップダウン(プルダウン)リストで選び、選んだ値に対応した単価をVLOOKUP関数で呼び出します。

この実装プロセスは見積書作成の記事を参照して下さい。

ドロップダウンリストとVLOOKUP関数の連動

作業の手順は、「別表を用意し、セル範囲に名前を定義」→「取引内容(詳細)をリスト化する」→「単価のセルにVLOOKUP関数を挿入」という流れになります。

請求書発行日の入力時に支払期限が表示される

支払期限は事業所によって定められた支払サイトに準じるか、相手先の意向に従うか、いずれにしても事前の打ち合わせで決めますが、一般的に多いのは月末締めの翌月末(または翌々月末払い)だと思います。

当サイトでダウンロードできる請求書ファイルでは発行日の日付の翌月末の日付を「お支払い期限」に自動表示させる数式を入れています。実装手順は以下の記事へ。

EOMONTHで翌月末の支払期限を求める

発行日より~営業日後の日付を支払期限とする関数の実装手順は以下の記事を参照して下さい。。

WORKDAYで土日祝日を除いた~日後の支払期限を求める
WORKDAY.INTLで土日以外の休日と祝日を除いた~日後の期日を求める

単価入力時に数量が空欄の場合は単価の数値が金額に表示される

取引内容の詳細(A9)から単価(E9)を導き出し、数量(D9)を入力すると金額(G9)に「単価×数量」の値が計算されるよう数式「D9*E9」を入れます。
請求書の明細のセル構成
詳細(A9)から単価(E9)が呼び出された時点で、数量が未入力だと金額(G9)のセルには「」が入りますが、ここでは数量が空欄であっても「」と認識されて金額(G9)欄に単価額が入るようにしたいと思います。
単価が入った時の金額セルの表示
論理式のIF関数の引数を以下のように設定します。
IF関数の引数の説明
数式は「=IF(D9>1,D9*E9,E9)」。明細表の最後の行「G19」まで書式なしのフィルコピーをします。 ※詳細・単価のセルが未入力で、数量のセルに数値を入れると金額のセルにはエラーが出ます。これが気になるなら「=IFERROR(IF(D9>1,D9*E9,E9),””)」として下さい。

請求書の集計と仕上げ

請求書に記載する中で最も重要な情報「請求金額」を間違いなく提示するために、明細の集計はミス無く、正確に行われなければなりません。そこで、セルに数式を仕込んでExcelに計算をやってもらいます。

消費税額を計算

消費税額のセルに「G21*8%(※消費税率8%:2018年1月現在)と入れれば、小計「G21」から計算された税額が表示されます。端数を切り捨てにするならINT関数にネスト、四捨五入にするならROUND関数を使って処理します。「=INT(G21*8%)」「=ROUND(G21*8%,0)

オートSUMで小計を自動集計

明細の金額の「小計」はオートSUMで簡単に自動集計できます。
小計の金額が入るセル「G21」を選択して、「数式」タブ(または「ホーム」タブ)にあるオートSUMのボタンをクリック、明細表の金額のセル範囲「G9:G19」をドラッグで選択し、Enterを押すだけです。
オートSUMで小計の自動集計
数式バーを確認すると、「=SUM(G9:G19)」と入っています。

合計とご請求金額

「合計」のセルには「(小計+消費税額)-(源泉徴収)」の単純な式が入ります。
=(G21+G22)-G23」または「=SUM(G21:G22)-G23
小計~合計のセル構成

最後に、「ご請求金額」のセルに「=G24」と入れて、「合計」金額を転写します。
合計金額を請求金額のセルに転写
請求書テンプレートの無料ダウンロード

タイトルとURLをコピーしました