【EXCEL】VLOOKUPとSUMIFS関数を活用して、軽減税率に対応した請求書作成TIPS

エクセル
この記事は約4分で読めます。
TIPS難易度
2.0

Contents


軽減税率に対応した請求書作成

今回のTIPSでは、軽減税率に対応する請求書作成方法を紹介致します。
商品ごとに税率が変わるため、その都度計算すると面倒ですよね。
今回紹介するTIPSを活用すると、自動で税率を区別することが可能です。

今回作成するゴールイメージ

今回のTIPSで完成する請求書イメージ

今回使用する主な関数

関数名
  • VLOOKUP
  • SUMIFS

作成手順について

早速、軽減税率に対応した請求書を作成していきましょう。
作成手順は大きく分けて4つです。

1、商品マスターの作成

取り扱っている商品の情報をまとめた専用テーブルを作成します。
専用テーブルで作成した商品マスターの情報をVLOOKUP関数を使用して引用します。
商品マスターを作成する際は、一番左の列に商品IDなどの、商品を区別するための識別用データを設定しましょう。
今回の例では、商品ID・カテゴリ・商品名・税区分・税率・単価・単位の7要素で構成しています。

請求書作成に必要となる商品マスター

2、VLOOKUP関数で商品IDから商品情報を引用

請求額を構成する内訳を明記するために、商品・単価・数量・税区分などの情報をまとめる必要があります。
まずは、商品IDを起点に商品マスターから情報を出力できる状態を作りましょう。
ここでは、商品IDを検索値とし、VLOOKUP関数を使うことで必要なデータを出力します。

請求書の商品詳細/内訳表
別シートの商品IDを起点に情報を引用するための商品マスター

=VLOOKUP($A11,商品マスター!$A$2:$G$11,3,FALSE)と入力することで、シート名:商品マスターのA2:G11データの中からA11で指定した商品IDと同じデータを検索し、該当の行から3列目のデータを出力するという意味になります。
同様に単価は、6列目のデータを出力する用に=VLOOKUP($A11,商品マスター!$A$2:$G$11,6,FALSE)と設定します。

3、税区分を識別して、消費税8%と10%をそれぞれ自動出力

軽減税率の対応などで請求書の消費税表示においては、8%と10%の表示が必要となりました。軽減税率対象の①酒類・外食を除く飲食料品 ② 週2回以上発行される新聞(定期購読契約に基づくもの)とされています。今回の例で表現すると、「りんご」や「みかん」などの食品は8%の軽減税率対象、ダンボール80サイズは10%の税率対象といったように区別する必要があります。

消費税8%対象の小計を表示
消費税10%対象の小計を表示

=SUMIFS(G11:G19,D11:D19,8%)*8%と入力することで、G11:G19に表示されている金額(税抜)合計金額に対して、D11:D19の税区分を照らし合わせて、8%と表示されている金額(税抜)のみを合計し、最終的に税率8%を掛けるという表示なっています。

※軽減税率についての詳細は国税庁を参照:https://www.nta.go.jp/taxes/shiraberu/zeimokubetsu/shohi/keigenzeiritsu/pdf/01.pdf

4、完成

以上のステップで概ね完成です。今回のTIPSでは、VLOOKUPとSUMIFSを活用した軽減税率に対応した請求書作成テンプレートを作成しましたが、今回の組み合わせはさまざまな方法に応用することが可能です。
商品マスターのような大元となるデータベースを作成し、データベースへのアクセス方法としてVLOOKUPを活用していきましょう。

最後に

いかがでしたか。
エクセルの基本機能を活用することで簡単に軽減税率に対応した請求書作成ができましたね。
今回紹介した方法を活用することで、様々な業務改善に役立てることできます。
条件分岐が発生する資料作成にはうってつけです!

関連記事の紹介

【EXCEL】スパークラインを活用して直感的に理解出来る推移表を秒速で作成するTIPS

【EXCEL】FORECAST関数を活用して、将来の売上予測を自動計算するTIPS

【EXCEL】UNIQUE関数を使って、重複しないデータを抽出し売上管理を楽にする方法

関連書類の紹介

4時間のエクセル仕事は20秒で終わる

マクロを仕事で使うための、ポイントのみを押さえた省力的で効率的な学び方。これが、本書のコンセプトです。マクロを完璧にマスターする必要はありません。専門用語は暗記しなくても大丈夫。時間もミスも大幅に削減できるマクロの再利用法を解説。

Excel 最強の教科書[完全版]

誰でもすぐに活用できる業務直結のノウハウを一冊に凝縮!Excelの実践スキルは、一生役立つ武器になる。