Excelエクセルで消費税を計算する方法/Rounddown関数の使い方

tax IT

見積もりや帳簿などで金額を扱う以上、消費税を計算する必要が出てくる場面は多くあります。特に決まったExcelのフォーマットで記入する場合は、計算ミスを避ける上でも自動的に計算されることは必須事項といえます。
下で「なぜこうなるのか」を延々と説明しますが、最終形はこういう式になります。

消費税(8%)の求め方
=Rounddown(税抜き金額*8%,0)
Advertisements

消費税・税込金額の求め方

zei1
まずはこの表で、数値を集計するようにしてみたいと思います。
zei2
「金額」に対して「税率」が%で入力されていれば、そのふたつを掛け合わせることで消費税を求めることができます。
まずは=B2*C2と入力し、金額と税率を掛け合わせてみましょう。
*は掛け算の意味で、英数字入力モードでEnterのふたつ左下側のキーをShift押しながら打鍵するか、テンキーの*を押せば打てます。
zei3
税抜き金額(150)×税率(8%)が求められました。
この式は実はある理由で修正の必要がありますが、ひとまず話を先に進めます。
zei4
作った式をオートフィルで下にコピーすると、自動的に対応する行も変化します(3行目では=B3*C3、4行目では=B4*C4となる)。
zei5
税抜き金額と消費税を合計すれば、税込金額が求められます。この場合=B2+D2のようになります。
+は*のひとつ左隣のキーをShift押しながら打鍵するか、やはりテンキーにあります。
zei6
総額が求められましたので、これもオートフィルで下にコピーします。
zei7
あとは計ですが、これはSum関数で上にあるものを集計します。
この場合=Sum(B2:B4)のようになります。もし間に行を挿入した場合は、範囲が自動で拡張されます(2~4列に行を挿入すると、=Sum(B2:B5)となる)。
zei8
税抜き金額の総計が求められました。
zei9
今度はこの式を右にオートフィルします。アルファベットがB→C→Dと列ごとに変わり、それぞれの列の総計が求められます。
消費税率の総計は求めてもしょうがないので削除します。
zei10
罫線付けたり色を付けたりして、表の見栄えを整えます。

税率を直接式に入れる場合

zei11
これ書いてる時点では軽減税率とかあるので税率欄作りましたが、商売や時期によっては税率を分ける必要もなかろうと思います。
というわけで、今度は「税率」抜きで直接税込金額を求めてみます。
zei12
税率が8%として、=B2*108%などで求められます。
zei13
総額はこれをオートフィルで下までコピーしてもいいですし、Sum関数で税込金額を合計してもよいです。
zei14
一応消費税も書くと、税率が8%なら=B2*8%のようにします。
zei15
消費税も求めることができました。で、ここからが本題です。

消費税の小数を切り捨てる方法

セルの書式設定で小数点以下切り捨ては禁止

zei16
実はここまで作った表には、小数も表示されるという問題があります。
zei17
「小数を表示しない」方法としては書式設定が一般的ですが、これは消費税計算の場合基本的に禁止です。その理由を下記で説明します。
zei18
右クリックセルの書式設定をクリックし、
zei19
通常分類が「標準」になっているので、これを「数値」「通貨」にして、「小数点以下の桁数」を0にしてみます。
zei20
これを適用すると、一見良さそうですが、
zei21
金額をちょっと上げてみると、
zei22
計算結果がおかしなことになります。
zei23
税込金額も同様。
zei24
書式「数値」は見た目を変えているだけで、データ的には小数も生きており、これを四捨五入して表示しています。
なので合計すると、小数も含めた計算結果となります。消費税計算は切り捨てが普通な日本で、この計算方法はほぼ使いません。
zei26
よってこの場合は、「%で計算する時は結果を切り捨てる」処理をする必要があります。

ここで関数を使います。Rounddown関数は、数字の指定した桁を切り捨てることができる関数です。
桁数を指定する以外は、SumやMaxなどと同様、対象を()で閉じる使い方です。

指定した桁数で金額を切り捨て
Rounddown(対象,桁数)
※桁数が0=小数点以下切り捨て、1=小数点1位未満を切り捨て、-1=1の位を切り捨て
zei29
先ほど入力した消費税の右に=rounddown(をくっつけ、小数点以下を切り捨てしたいので最後に0をつけると、=Rounddown(B2*8%,0)
税抜き金額×8%小数点0位切り捨て)という式になります。
zei30
式をオートフィルで下に延長します。
zei31
税込金額にも同様にRounddown関数を付けてみると、ちゃんと切り捨てで総額が計算されることが確認できました。
Advertisements

税込金額から消費税と税抜き金額を算出

消費税(8%)の求め方
Rounddown(税込金額*8/108,0)
gyakuzei1
あまり需要はないかもしれませんが、税込金額だけわかっている状態で、消費税を割り出すという計算もやってみたいと思います。
gyakuzei2
考え方としては、まず税率8%とした場合、税込金額108円は税抜き金額100円+消費税8円となります。
gyakuzei3
税抜き金額(100%)+消費税(8%)=税込金額(108%)なので、税込金額の100/108が税抜き金額、8/108が消費税になります。
まずは8を108で割ってみましょう。
gyakuzei4
出た数字に税込金額をかけると、税込金額の8/108が求められます。
gyakuzei5
よって式としては=8/108*108となります。税率/(100+税率)×税込金額です(税率は%でなく数字)。結果は8円です。
gyakuzei6
あとは税込金額から消費税を引けば税抜き金額となるわけです。
gyakuzei7
で、これを色んな金額でやっていくと、当然小数も出てくるので……。
gyakuzei8
Rounddown関数を噛ませます。式としては=Rounddown(A14*8/108,0)のようになります。A14が税込金額です。
gyakuzei9
切り捨てた消費税が求められました。
gyakuzei10
税込金額から税抜き金額を引きます。
gyakuzei11
これで、税込金額から消費税と税抜き金額が求められました。
gyakuzei12
さらに税率も不定の場合、話はさらにややこしくなります。
gyakuzei13
先ほどの式で使った、税率8%の場合の108という数字は、つまり100+(100×8%)です。この8%のあたりにC列を代入すればいけそうです。
gyakuzei14
というものを書くと、=Rounddown(E2*(100*C2/(100+100*C2)),0)のような式となります。
=Rounddown(税込金額*(100*税率/(100+100*税率)),0)です。
gyakuzei15
100*C2のC2は税率なので、つまり100*8%、イコール8を意味します。税率が10%なら10になります。
gyakuzei16
ということは後半の(100+100*C2)は108となり、税率が8%なら=Rounddown(E2*(8/108)),0)という意味の式になります。
gyakuzei17
税抜き金額を同様に税込金額-消費税で求めて完成です。
やりたいことから方法を探すエクセルExcel操作・関数・VBA(マクロ)逆引きまとめ
逆引き(やりたいことから探す)Excel記事まとめ
関数・演算子・メソッド・プロパティ名から探すExcel/VBA(マクロ)使い方・組み合わせ方まとめ
こちらはExcelやメソッドの諸機能を、機能の名称から探せるまとめ記事です。

コメント