請求書をExcel関数で自動作成する方法｜番号で宛先・明細を表示

請求書をExcelで自動作成するには、宛先や日付を保存する「請求一覧」と、品名・数量・単価を保存する「明細」を用意し、請求番号で必要な行を帳票へ表示します。一覧に入力した内容を、請求書へ繰り返し転記する手間を減らせます。

Excel関数で自動表示できる範囲

この作例はINDEX・MATCHなどの関数を使い、VBA・マクロは使いません。請求一覧と明細を入力した後、帳票に表示する内容を請求番号で切り替えます。

この記事の作例では、「請求書」のF4へ番号を入力すると、宛先・複数の明細・合計が切り替わります。I-261001は3明細・62,700円、I-261002は1明細・33,000円の請求書です。

無料Excelテンプレートで宛先・明細・合計を切り替える

1. ダウンロードしたファイルをExcelで開き、「請求書」シートを表示します。
2. 黄色のF4にI-261001を入力し、宛先・3つの明細・合計62,700円を確認します。
3. F4をI-261002へ変更します。宛先が架空青空工房に変わり、ロゴ制作1件・合計33,000円になることを確認してください。

入力したのに表示が変わらない場合は、Excelの計算方法が「自動」になっているか確認します。次に、F5の入力確認と、入力元の確認欄を見てください。数式セルを上書きした場合は、元のダウンロードファイルからやり直すと確認しやすくなります。

書類を作った後の入金額や未入金残も同じファイルで確認したい場合は、見積・請求・入金管理 Excelテンプレートに書類台帳・明細・入金記録・書類作成の機能が用意されています。販売版での使い方と無料作例との違いは後半をご覧ください。

書類作成と入金管理の仕様を見る

見積・請求・入金管理 Excelテンプレート

¥2,200 税込買い切り

お買い物カゴに追加 画面と機能を見る

自分の請求一覧と明細を入力する

元ファイルを保管し、コピーで入力を始めます。黄色のセルが入力欄、薄い緑のセルが数式です。作例のデータを使わない場合は、次の入力範囲だけを消し、確認欄や計算式は残してください。

請求一覧の番号は、その請求を区別するための番号です。たとえば同じ会社へ翌月も請求する場合は、新しい番号で行を追加します。I-261001のように文字を含む番号にすると、先頭の0や日付への自動変換も避けやすくなります。作例の番号入力欄は文字列形式にしています。

明細では、同じ請求番号を複数の行に使って構いません。その代わり、1つの請求の行番号は1、2、3…と重複しないように付けます。行番号は帳票の表示順です。番号3だけを入力すると、帳票の3行目に表示され、1・2行目は空欄になります。

単価には、その請求で確定した税抜金額を入力します。数量・単価は「3」「12000」のような数値、税率は「10%」と入力してください。この無料作例は全明細10％で計算します。8％と10％が混在する請求書の計算例は、税率別に集計する請求書Excelの記事で確認できます。

INDEX・MATCHで請求一覧から帳票へ転記する

無料ファイルには、ここから説明する式が入力済みです。新しく作る場合は、「請求書」「請求一覧」「明細」の3シートを作り、上の図と同じ列・行へ見出しとデータを配置します。シート名やセル位置を変える場合は、式の参照先も変更してください。

この方法では、MATCHで番号が見つかった位置を調べ、INDEXでその位置の値を取り出します。MATCHの最後の0は完全一致の指定です。式の意味はMicrosoftのMATCH関数とINDEX関数の説明でも確認できます。

1．請求一覧と明細に入力確認の式を入れる

先に、入力途中の請求や重複した番号を帳票へ出さないための確認欄を作ります。次の欄を開き、3つの式を指定範囲へコピーしてください。この後の金額・帳票の式は、確認欄の「登録済」を参照します。

=IF(COUNTA(A6:F6)=0,"",IF(A6="","番号未入力",IF(COUNTIFS($A$6:$A$15,A6)<>1,"番号重複",IF(OR(B6="",F6="",COUNT(C6:E6)<3),"一覧入力を確認","登録済"))))

=IF(COUNTA(A6:G6)=0,"",IF(A6="","請求番号未入力",IF(COUNTIFS(請求一覧!$A$6:$A$15,A6)<>1,"請求番号を確認",IF(OR(C6="",E6="",COUNT(B6,D6,F6,G6)<4),"明細入力を確認",IF(OR(B6<>INT(B6),B6<1,B6>10,D6<=0,F6<0,G6<>10%),"明細値を確認",IF(COUNTIFS($A$6:$A$35,A6,$B$6:$B$35,B6)<>1,"行番号重複","登録済"))))))

=IF(F4="","請求番号を入力",IF(COUNTIFS(請求一覧!$A$6:$A$15,F4)<>1,"一覧の番号を確認",IF(INDEX(請求一覧!$G$6:$G$15,MATCH(F4,請求一覧!$A$6:$A$15,0))<>"登録済","一覧入力を確認",IF(COUNTIFS(明細!$A$6:$A$35,F4)=0,"明細未登録",IF(COUNTIFS(明細!$A$6:$A$35,F4,明細!$J$6:$J$35,"<>登録済")>0,"明細を確認","表示準備OK")))))

2．明細に検索キーと税抜金額を作る

「明細」のI6へ次の式を入れ、I35まで下へコピーします。請求番号と行番号を#でつなぎ、I-261001#1のような検索キーにします。請求番号には区切り文字の#を使わないでください。

=IF(OR(A6="",COUNT(B6)=0),"",A6&"#"&B6)

H6へ次の式を入れ、H35まで下へコピーします。J列が「登録済」の行だけ、数量×確定単価を円単位に四捨五入します。単価の空欄は未入力、単価0円は入力済みの数値です。

=IF(J6="登録済",ROUND(D6*F6,0),"")

3．請求番号で宛先と日付を表示する

「請求書」のF4を請求番号の入力欄にします。B6へ次の式を入れると、請求一覧のB列から宛先を表示します。B6:D6は宛先用に結合しても構いません。

=IF($F$5="表示準備OK",INDEX(請求一覧!$B$6:$B$15,MATCH($F$4,請求一覧!$A$6:$A$15,0)),"")

変えるのはINDEXの取り出す列です。MATCHが探す請求番号の範囲と、F4・F5の参照は変えません。

4．請求番号と行番号で複数の明細を表示する

帳票の明細は13〜22行目に置きます。G13:G22へ1〜10を順に入力し、H13へ次の式を入れてH22まで下へコピーします。G・H列は帳票の右側に置く補助欄です。

=IF($F$5<>"表示準備OK","",IF(COUNTIFS(明細!$I$6:$I$35,$F$4&"#"&$G13)=0,"",MATCH($F$4&"#"&$G13,明細!$I$6:$I$35,0)))

F4がI-261001、G13が1なら、I-261001#1を明細のI列から探します。該当する行がなければ空欄にします。入力確認が済んでいない請求も表示しません。

A13へ次の式を入れ、A22まで下へコピーすると、明細のC列から品名を取り出せます。

=IF($H13="","",INDEX(明細!$C$6:$C$35,$H13))

B13〜F13には、A13の式のINDEX範囲だけを上の表どおりに変更して入力し、それぞれ22行目まで下へコピーします。$H13は変更しません。税率のE列はパーセント、単価と金額は桁区切りの表示形式にします。

5．小計・税額・合計を計算する

F24の小計は、帳票に表示した税抜金額を合計します。

=IF($F$5="表示準備OK",SUM(F13:F22),"")

F25は10％の税額、F26は税込合計です。税額は、請求書の税抜小計に10％を掛け、円単位に四捨五入します。F10にも合計を表示する場合は、=F26と入力してください。

=IF(ISNUMBER(F24),ROUND(F24*10%,0),"")

=IF(COUNT(F24:F25)=2,SUM(F24:F25),"")

I-261001の小計57,000円・税5,700円・合計62,700円と、I-261002の合計33,000円をもう一度確認します。番号を切り替えたとき、前の請求の明細が残っていないことも確認してください。

空欄・重複・明細の抜けを確認する

宛先や合計が空欄の場合は、まず「請求書」のF5を見ます。無料作例は入力がそろうまで合計を空欄にするため、0円と取り違えずに確認できます。

試す場合はコピーで、明細のF6の単価を消してみてください。F5は「明細を確認」、合計は空欄になります。F6へ0を入力した場合は、0円の明細を含めて合計23,100円になります。空欄と0円を区別できていることを、この2つの操作で確認できます。

明細のJ列は、選んでいる請求以外も確認してください。F5はF4で選んだ請求だけを判定します。別の番号に誤入力した行や、未登録番号の行まで点検するには、明細全体の確認欄を見る必要があります。

無料作例は請求一覧10件、明細は全請求を合わせて30行、帳票は1請求10行です。11行目が必要な請求では、入力行を増やすだけで済ませず、帳票の行、行番号の上限、参照範囲を一緒に拡張します。変更後は、末尾の明細と合計が一致するか確かめてください。

発行済みの番号は、請求一覧を並べ替えたり請求を取り消したりした後も残します。採番ルールと値で確定する手順は、請求書番号の付け方とExcelでの固定管理で、取消・欠番・月替わりを含む無料作例を使って試せます。

請求番号ごとにPDFを保存し、送付前に確認する

日付・数量・単価・宛先を確認したら、請求書のB29:B32を実際の発行者・連絡先・登録番号・振込先へ置き換えます。学習用の説明が不要ならA3の文章も消します。登録番号は、適格請求書発行事業者として登録している場合に、自分の番号を入力してください。

適格請求書として発行する場合は、発行者と登録番号、取引年月日・取引内容、税率ごとの対価と税率・消費税額、宛先などの記載を確認します。税額の端数処理は、1つの請求書につき税率ごとに1回行います。詳細は国税庁のインボイス制度の案内を確認してください。

1. F4の番号と宛先・明細・合計を照合し、F5が「表示準備OK」になっていることを確認します。
2. 請求書のA2:F32を印刷範囲として選び、A4縦の印刷プレビューで改ページや欠けを確認します。G・H列の補助欄は範囲に含めません。
3. PDFとして保存し、I-261001_20261011.pdfのように番号と日付を付けます。開き直して読める文字サイズと金額を確認してから送付します。
4. 送付時点のExcelもコピーで保管します。次の請求番号へ切り替える前に、その請求の控えを残してください。

この無料ファイルでは印刷範囲や1ページへの収まりを環境に合わせて設定してください。Excelの操作はMicrosoftの印刷手順を参照できます。入力元を後から変更すると帳票の内容も変わるため、送付済みのPDFを控えとして保管します。

書類作成と入金確認をまとめて管理する

一覧から帳票を表示する仕組みに加え、見積書・請求書の台帳や分割入金の確認も必要なら、見積・請求・入金管理 Excelテンプレートでまとめて管理できます。販売中v1.2には、書類台帳・明細・書類作成・入金記録・取引先・品目などのシートがあります。

使い始めるときは、設定と取引先・品目を登録し、書類台帳へ書類番号・種別・日付・宛先を記録します。次に同じ書類番号で明細を入力し、「書類作成」のD1で番号を選んでください。請求後は入金記録へ同じ番号で入金日・金額を追加し、台帳で残額を確認する流れです。PDFの保存と送付は自分で行います。

分割入金の記入例は、請求・入金管理表をExcelで作る手順で確認できます。書類の作成から入金後の確認まで続けて使いたい方は、次のカードから販売版の仕様と画面をご覧ください。

設定済みのテンプレートで始める

見積・請求・入金管理 Excelテンプレート

¥2,200 税込買い切り

お買い物カゴに追加 画面と機能を見る

買い切り・テンプレートの月額料金なし
お支払い完了後、Excel＋説明テキストのZIPをダウンロード

カートの商品で購入手続きへ →