エクセル関数を用いて税込金額を入力したら消費税と税抜金額を計算する方法

2019-08-09 2020-01-11

けいぞくま

エクセルを用いて表計算をする際、

数量・単価・消費税率から税込金額を算出する

というのはわりと行います。

数量×単価×(1+消費税率)＝税込金額　という計算式です。

一方、それとは逆で、税込金額を元に税抜金額・消費税額を算出するというのは、あまり行うことはありません。とはいえ全く無いかというとそうではなく、たまーに発生します。

税込金額から税抜金額・消費税額を算出する例

税込金額で請求書が来た場合（税抜金額が書いていない）
税込金額で管理していたが、税抜金額を出す必要が出てきた場合
税込金額と税抜金額が混在して管理しており、明示的に分けて管理できるようにしたい場合

そこで今回は、エクセル関数を用いて、税込金額を入力したら消費税と税抜金額を計算する方法をご紹介します。税込金額から逆計算で税抜金額を算出するため、どうしても端数が発生しますが、その点も考慮したサンプルをご紹介しますので、仕事で使われるような場合は参考になるかと思います。

この記事の目次

1 税込金額から税抜金額・消費税額を算出する際の考え方
2 税込金額から税抜金額・消費税額を算出するサンプル
3 まとめ

税込金額から税抜金額・消費税額を算出する際の考え方

数量、単価、消費税率の3つから税込金額を求める場合は、次の計算式を使います。

数量×単価 = 税抜金額
税抜金額 × (1+ 消費税率) = 税込金額

税込金額から税抜金額を逆計算する場合の計算式は、次の通りになります。

税抜金額 = 税込金額 / (1+ 消費税率)
消費税額 = 税込金額 - 税抜金額

計算式にすると単純なのですが、これをエクセルで算出しようとした際は次の点の考慮が必要になります。

エクセル関数を用いて税抜金額を算出する際に考慮が必要な点

税抜金額を算出した際の小数点以下の端数の扱いをどうしているのか

切捨て？切り上げ？四捨五入？

例えば、税込金額が10,800円で消費税率が8%の場合、税抜金額は10,800円÷1.08=10,000円となります。計算結果が割り切れるため迷いようがありません。

一方、税込金額が9,900円で消費税率が8%の場合はどうでしょうか？税抜金額は9,900円÷1.08= 9166.6666・・・となります。計算結果が割り切れない数字となっています。そのため、

税抜金額：9,166円（消費税額：734円） ※端数を切り捨て
※9,900円 - 9,166円で消費税額を算出
※9,166×1.08をすると税込金額は9,899.28円となるので、小数点以下を切り上げしているようなイメージ

とするのか

税抜金額：9,167円（消費税額：733円）※端数を切り上げ
※9,900円 - 9,167円で消費税額を算出
※9,167×1.08をすると税込金額は=9,900.36円となるので、小数点以下を切捨て（もしくは四捨五入）しているようなイメージ

とするのか、どちらを採用するのかを決める必要があります。

端数なのでどうでも良いと思いがちですが、お金のことだからこそ、この点がどちらを前提に算出しているのかを決めて押さえておく必要があります。

この決めによって、端数分が

税抜金額のほうに入るのか
消費税額のほうに入るのか

が変わります。

計算する上で決めるべき点（改めて）

税抜金額を算出した結果が割り切れない場合、小数点以下の扱いをどうするか。（切捨て？切り上げ？四捨五入？）

切捨て・切り上げ・四捨五入のどれを使っても間違いではありませんので、実態と照らし合わせて決めが必要になります。

税込金額から税抜金額・消費税額を算出するサンプル

では次からは、エクセル関数を用いて税込金額から税抜金額・消費税額を算出するサンプルをご紹介します。使用する関数は、切り上げ・切り捨て・四捨五入を行うROWNDUP関数・ROUNDDOWN関数・ROUND関数です。

次のように、税込金額と消費税率を指定すると税抜金額が計算されるようなシートを作ります。

税込金額を元に税抜金額と消費税額を算出したExcelの結果 — エクセルで計算した結果のイメージ

サンプル上の税抜金額について

税込金額と消費税率を元に税抜金額を算出しますが、切り捨て・切り上げ・四捨五入によって数値が変わります。どれを採用するかは決め次第ですが、どれを選んでもいいようにここでは3種類の計算結果を一覧に表示させています。

税込金額と消費税率から税抜金額（小数点あり）の算出

A列に税込金額、B列に消費税率を入力すると、 C列に計算結果の税抜金額が表示されるような計算式を入れています。B列は消費税率の切り替わりを考慮してサンプル上は選択できるようにしています。
※消費税率が固定でよいのであれば、B列自体を消して、C列の計算式内で消費税率を考慮すれば問題ありません。

計算の結果が分かりやすいように、C列には小数点ありで表示させています。
※セルの書式設定で、小数点第2位まで表示させています。

計算式は、税込金額/(1+消費税率)です。

2行目のように計算結果が割り切れる場合は特に気にしなくてよいですが、3行目以降のように少数点以下の端数が発生するような事があります。

この端数をどうするのか、を次に説明します。

税抜金額（切り捨て・切り上げ・四捨五入）の算出

D～F列ではそれぞれ、C列の税抜金額（小数点あり）を切り捨て・切り上げ・四捨五入した結果を表示しています。この処理においてROWNDUP関数・ROUNDDOWN関数・ROUND関数を使用します。

各関数の意味は次の通りです。

ROUNDUP関数

ROUNDUP(数値, 桁数)

数値：切り上げの対象となる実数値
桁数：数値を切り上げた結果の桁数

＜内容＞
指定した数値を、指定した桁数で切り上げする。

＜補足＞
桁数に正の数を指定すると、数値の小数点以下について、指定した桁数の右側が切り上げられる。
（例）=ROUNDUP(123.01, 1)　→　123.1
桁数に 0 を指定すると、数値は最も近い整数として切り上げられる
（例）=ROUNDUP(123.01, 0)　→　124
桁数に負の数を指定すると、数値の小数点の左側 (整数部分) が切り上げられる。
（例）=ROUNDUP(123.01, -1)　→　130

ROUNDDOWN関数

ROUNDDOWN(数値, 桁数)

数値：切り捨ての対象となる実数値
桁数：数値を切り捨てた結果の桁数

＜内容＞
指定した数値を、指定した桁数で切り捨てする。

＜補足＞
桁数に正の数を指定すると、数値の小数点以下について、指定した桁数の右側が切り捨てられる
（例）=ROUNDDOWN(123.06, 1)　→　123.0
桁数に 0 を指定すると、数値は最も近い整数として捨てられる
（例）=ROUNDDOWN(123.61, 0)　→　123
桁数に負の数を指定すると、数値の小数点の左側 (整数部分) が捨てられる
（例）=ROUNDDOWN(126.01, -1)　→　120

ROUND関数

ROUND(数値, 桁数)

数値：四捨五入の対象となる実数値
桁数：数値を四捨五入した結果の桁数

＜内容＞
指定した数値を、指定した桁数で四捨五入する。

＜補足＞
桁数に正の数を指定すると、数値の小数点以下について、指定した桁数の右側が四捨五入される
（例）=ROUND(123.06, 1)　→　123.1
桁数に 0 を指定すると、数値は最も近い整数として四捨五入される
（例）=ROUND(123.61, 0)　→　124
桁数に負の数を指定すると、数値の小数点の左側 (整数部分) が四捨五入される
（例）=ROUND(126.01, -1)　→　130

どの関数を使用した場合も、計算結果は整数で表示したいため、関数の第二引数には0を指定します。