わえなび ワード&エクセル問題集 waenavi

当サイトはWordとExcelの実力を鍛える最高水準の特訓問題集です。リンクを張るのは自由です。みんなで勉強しましょう!

Excelの関数で消費税抜き価格を10%の税込価格に変更する方法と端数処理【切り上げ・切り捨て】


2019年10月に消費税が増税となり、メーカーや小売店の皆さんは値段の付け替えなどの作業が増えて面倒です。Excelで消費税の計算をするときに最も重要な問題は、小数点以下の端数処理です。表示形式で整数値にするのは問題外ですが、例えば、8%から10%に切り替えるのに単に110/108倍をするというのも間違いです。

「一銭を笑う者は一銭に泣く」「一円を笑う者は一円に泣く」ということわざがありますが、Excelで一銭(0.01円)の端数処理ができない人は一銭に泣きます。

そこで、今回は、税込価格・税抜価格の変更方法と増税の対応方法についてまとめて解説します。

 

目次

1.消費税の端数処理について

消費税が10%に上がりますが、一部の商品は軽減税率により8%のままとなります。8%や10%のパーセンテージをかけると小数になります。小数部分を切り上げか切り捨てるか四捨五入をするかは法律で明確に定められていないので、事業者が自由に決めることができます。

しかし、多くの事業者は「切り捨て」にしています。

当然のことです!!! 切り上げた税金をお客様に負担させるとか絶対にありえません。四捨五入や切り上げの請求書を出すとか失礼にも程があります。レシートや請求書で確認すれば分かりますから、変な誤解を与えないように、1円未満は確実に切り捨てるべきです。

 

2.数値・通貨の表示形式

例えば、「12345.6」の表示形式をカンマ桁区切りにすると、「12,346」となりますが数式バーは「12345.6」のままです。Excelのセルは数値データと書式の2つの情報が保存されています。数値データが「12345.6」であっても表示形式(書式)でカンマ桁区切り(整数表示)にすると、四捨五入の表示になります。しかし、小数点以下のデータが消えたわけではありません。

f:id:waenavi:20190815230901j:plain

 

実際に10倍してみると「123456」になります。

f:id:waenavi:20190815231329j:plain

 

通貨の表示形式でも同じです。セルの表示として小数点以下の部分を消したとしても、数値データとして小数部分が残っているので全く意味がありません。

f:id:waenavi:20190815231435j:plain

 

3.税抜価格を税込価格にする方法

(1)税率が固定の場合

8%の場合は1.08倍、10%の場合は1.1倍にすればよいです。10円未満の金額に1.1を掛けると小数になります。

  • =税抜*1.08
  • =税抜*1.1

f:id:waenavi:20190815231552j:plain

 

なお、1.1倍の代わりに110%と入力しても構いません(以下、省略)。

  • A2*110%

f:id:waenavi:20190816181907j:plain

 

なお、10%増し、10%引きの計算について詳しくはこちらの記事をご覧ください。

 

前述のとおり、税込み価格の小数点以下は通常切り捨てにします。切り捨てをするにはINT関数を使います。INT()で囲みます。これは表示形式ではなく数値データそのものを切り捨てているので誤差は発生しません。

  • =INT(税抜*1.08)
  • =INT(税抜*1.1)

f:id:waenavi:20190815233300j:plain

 

(2)INTとROUNDDOWNの違い

Excelには小数点以下を切り捨てる関数として、INTとROUNDDOWNがあります。INTは桁数を指定する必要はありませんが、ROUNDDOWNは桁数として0を指定しなければなりません。

  • INT(税抜*1.1)
  • ROUNDDOWN(税抜*1.1,0)

f:id:waenavi:20190815233330j:plain

 

基本的に計算結果は同じですが、マイナスの場合に異なります。

  • INT・・・小数の場合、元の値より小さい整数にする
  • ROUNDDOWN・・・小数の場合、小数部分を無くす

f:id:waenavi:20190815233633j:plain

 

例えば、INT(-555.5) は-556になります。-555.5より小さい整数を返すからです。数学的にはINTのほうが正しいです。しかし、ROUNDDOWNは単に-555.5の小数部分を無くすだけなので-555になります。

消費税の計算でマイナスを使うと面倒なことになるのであまり良くないのですが、マイナスの消費税や税込価格を計算するときには、INTかROUNDDOWNかを注意しなければなりません。

 

(3)税率が変動する場合

税率を別のセルに入力することがあります。この場合の倍率は、絶対参照で(1+税率)とします。

  • =INT(税抜*(1+税率))
  • =ROUNDDOWN(税抜*(1+税率),0)

f:id:waenavi:20190815234118j:plain

 

税率を変えるだけで、税込価格が変わります。

f:id:waenavi:20190815234318j:plain

 

(4)8%と10%が混ざっている場合

軽減税率の制度によって8%と10%の商品が混ざることがあります。税率がそれぞれ異なる場合、相対参照で(1+税率)とします。

f:id:waenavi:20190815234409j:plain

 

(5)グループで税率が異なる場合 <やや難>

例えば、A~Dグループの商品は8%で、それ以外は10%とする場合、税率の表を用意しておきます。

f:id:waenavi:20190815234913j:plain

 

VLOOKUP関数を用いて、税率をVLOOKUP(B2,税率表,2,0)とします。

f:id:waenavi:20190815235039j:plain

f:id:waenavi:20190815235254j:plain

 

1つの式にまとめると次のようになります。

  • =INT(A2*(1+VLOOKUP(B2,$E$2:$F$10,2,0)))

f:id:waenavi:20190815235813j:plain

 

8%のグループだけを指定しておいて、エラーが出たら10%にするという方法もあります。MATCH関数などでグループを検索してエラーになったら10%とします。

  • =INT(A2*(1+IF(ISERROR(MATCH(B2,$E$2:$E$5,0)),10%,8%)))

f:id:waenavi:20190815235848j:plain

 

(6)複合参照

8%の場合と10%の場合を比較するには、税率を複合参照にします。

f:id:waenavi:20190816000251j:plain

 

8%税込価格と10%税込価格の差を求めると不規則になります。8%と10%では切り捨てている小数部分が異なりますので、引き算をして2%になるわけではありません。

f:id:waenavi:20190816000351j:plain

 

4.税込価格を税抜価格にする方法

(1)基本原則

税込価格を求めるときに(1+税率)をかけるので、税抜き価格は(1+税率)で割ります。

f:id:waenavi:20190814123141j:plain

 

8%の場合は1.08、10%の場合は1.1で割ればよいです。小数で割り算をするので、答えも原則として小数になります。

  • =税込/1.08
  • =税込/1.1

f:id:waenavi:20190816000820j:plain

 

端数処理は重要です。税込価格を求めるときに切り捨てをした場合、税抜き価格は必ず切り上げにします。絶対に切り上げです(後述)。切り上げをするにはROUNDUP()で囲み、桁数として0を指定します。

  • =ROUNDUP(税込/1.08,0)
  • =ROUNDUP(税込/1.1,0)

f:id:waenavi:20190816001253j:plain

 

(2)8%と10%が混ざっている場合

税率がそれぞれ異なる場合、相対参照で(1+税率)とします。

f:id:waenavi:20190816001640j:plain

 

5.消費税額だけを求める

(1)税抜価格から消費税額を求める

税抜価格の8%(0.08)または10%(0.1)を掛けたら、税額が出ますがこれも切り捨てです。INT関数です。

  • INT(税抜*0.1)
  • INT(税抜*10%)

f:id:waenavi:20190816010533j:plain

f:id:waenavi:20190816012614j:plain

 

税抜価格と消費税額を求めたら、足し算で税込価格が求められます。すでに消費税額で切り捨てをしていますので、足し算に切り捨ては不要です。

  • 税抜+消費税=税込

f:id:waenavi:20190816010627j:plain

 

(2)税込み価格を本体価格と消費税に分離する

10%の税込価格から税抜価格を求めるには、1.1で割って切り上げます。

f:id:waenavi:20190816010850j:plain

 

税込価格から税抜価格を引けば、消費税額になります。

  • 税込-税抜=消費税

f:id:waenavi:20190816011117j:plain

 

今度は、税込価格から直接、消費税額を求めます。税込価格の中の消費税の割合は、8%の場合は8/108、10%の場合は10/110です。この比率を税込み価格に掛けます。この時、消費税額は切り捨てです。 

  • 消費税=INT(税込*10/100)

f:id:waenavi:20190816011442j:plain

 

税込価格から税抜価格を求めるときに小数部分を切り上げますので、消費税はその分だけ減ります。したがって、切り捨てとなります。本体価格は切り上げ、消費税額は切り捨てです。

f:id:waenavi:20190816012530j:plain

 

6.税込価格にならない金額がある

(1)8%の場合

例えば、900円から920円までの税抜価格に対して、8%の消費税を加算します。985円が抜けています。

f:id:waenavi:20190816002050j:plain

 

912*1.08=984.96、913*1.08=986.04なので、切り捨てをすると985円が抜けます。税抜価格を1円ずつ増やすと、税込価格は1.08円ずつ増えるので、切り捨てたときに整数部分が飛ぶことがあるのです。

f:id:waenavi:20190816013418j:plain

 

仮に税込価格を985円とした場合、税抜価格は1.08で割って切り上げて913円となりますが、913円に消費税を加算すると1.08倍の切り捨てで986円です。このようにあり得ない価格を税込価格にした場合、いったん税抜価格にして、さらにもう一度税込価格にしても元に戻りません。

f:id:waenavi:20190816002822j:plain

 

ちなみに、1円~1000円の中に税込価格にならない金額は74個あります。+13、+14、+13、+14、・・・と規則的に増えていることが分かります(後述)。

f:id:waenavi:20190816003013j:plain

 

(2)10%の場合:11の倍数-1

今度は、900円から920円までの税抜価格に対して、10%の消費税を加算します。1000円や1011円が抜けています。

f:id:waenavi:20190816002626j:plain

 

消費税が10%の場合、税込価格が1000円にはなりません。仮に、税込価格を1000円とした場合、税抜価格は1.1で割って切り上げて910円となりますが、910円の税込は1001円です。ありえない税込価格を設定してしまうと計算が合わなくなりますので注意が必要です。

f:id:waenavi:20190816002932j:plain

 

ところで、910円は10の倍数、1001円は11の倍数です。税抜価格が10の倍数の場合、その1.1倍は必ず整数になり、税込価格は11の倍数になります。ありえない税込価格である1000円は11の倍数から1を引いた金額です。

f:id:waenavi:20190816004142j:plain

 

税抜価格が1円間隔の場合、1.1倍をすると1.1円間隔になるので、11の倍数から1を引いた金額は税込価格にはなりません。

f:id:waenavi:20190819134139j:plain

 

簡単に言えば、1を足して11で割り切れる金額は税込価格にならないということです。税込6,500円、税込12,000円などもありえません。

  • (1000+1)/11=91・・・11で割り切れる
  • (6500+1)/11=591・・・11で割り切れる
  • (12000+1)/11=1091・・・11で割り切れる

 

(3)どうする?1000円均一セール

楽天市場や通販サイトで1,000円均一セールをする店があります。8%の場合は、税抜価格を926円とすれば税込価格がちょうど1,000円となりますが、10%の場合、1,000円に相当する税抜価格がありません。「1,000円均一」の場合、消費税別の可能性が高いです。

1,000円+税などと外税の表記すれば、1,000円セールと宣伝しても法的には問題ないと思われます(モラルや規約の問題でしょう)。

 

7.8%税込価格と10%税込価格の変換

(1)8%の税込価格を10%にする

前述のように、8%の税込価格と10%の税込価格が必ずしも2%の差になるとは限らないので、直接2%増やすことはできません。税率を変える場合は必ず税抜価格に戻します。税抜きにするのは1.08で割って、ROUNDUPにします。

f:id:waenavi:20190816005551j:plain

 

10%の税込にするのは1.1倍で、INTにします。

f:id:waenavi:20190816005631j:plain

 

1つの式にする場合はINTのなかにROUNDUPを入れます。

  • =INT(ROUNDUP(A2/1.08,0)*1.1)

f:id:waenavi:20190816005740j:plain

 

(2)10%の税込価格を8%にする

税抜きにするのは1.1で割って、ROUNDUPにします。

f:id:waenavi:20190816005921j:plain

 

8%の税込にするのは1.08倍で、INTにします。

f:id:waenavi:20190816010031j:plain

 

1つの式にする場合はINTのなかにROUNDUPを入れます。

  • =INT(ROUNDUP(A2/1.1,0)*1.08)

f:id:waenavi:20190816010113j:plain

 

8.伝票合計に消費税を加算する

消費税を加算する方法には次の3つの方法があります。どの方法にするかを、あらかじめ決めておかなければなりません。通常は注文ごとの加算です。通販の場合、商品ごとに消費税を加算することがあり、注文の合計を見ると消費税が合わないことがあります。

  • 商品ごとの加算:1つ1つの商品に対して消費税を加算してから、合計する
  • 注文ごとの加算:商品は税抜価格にしておいて、1つの注文伝票の小計に消費税を加算する
  • 請求ごとの加算:1か月間の売掛金に対して消費税を加算する

注文ごとの加算や請求ごとの加算の場合は、注文伝票や請求書などの帳票を発行し、その小計に対して消費税を加算します。いったん税抜で小計を計算します。

f:id:waenavi:20190819142431j:plain

 

値引きをするときは消費税抜きで引き算をするのが無難です(後述)。

f:id:waenavi:20190819143936j:plain

 

10%の切り捨て(INT)を計算します。

f:id:waenavi:20190819142522j:plain

 

足し算をします。

f:id:waenavi:20190819141203j:plain

 

9.補足説明

(補足1)切り上げについて

税抜価格を求めるときに切り捨てにするといった解説をしているサイトがありますが、絶対に間違いです。数学的に考えれば明らかです。

税抜価格X円、税率Y%の場合、税込価格は X*(1+Y%) の切り捨てです。切り捨てた小数部分を a (ただし0<=a<1) とすると、税込価格は X*(1+Y%)-a です。これを税抜にするには(1+Y%)で割りますから、X-a/(1+Y%)となります。0<=a<1、1<1+Y%なので、0<=a/(1+Y%)<1 つまり1未満の数です。よって、この部分を切り上げることによって、Xに戻ります。

(補足2)+13、+14の繰り返し

消費税8%のとき、13円と26円は税込価格になりません。また、27円が整数になることから、13円と26円に27の倍数を加算した額も税込価格になりません。したがって、+13、+14、・・・の繰り返しになります。

  • 12円*1.08=12.96円
  • 13円*1.08=14.04円
  • 24円*1.08=25.92円
  • 25円*1.08=27円・・・27円周期になる

(補足3)税込合計から値引きをする場合は専門家に相談しよう

自店だけで使えるクーポン券は、税込合計からの値引きとして扱います。

例えば、税抜価格10,000円、消費税1,000円、税込価格11,000円の注文があったとします。値引きクーポン券によって1,000円値引きをして10,000円を受け取った場合、10000/1.1の切り上げで税抜9091円となり、消費税は909円となります。このように税込価格から値引きをすると消費税額が変更となる場合があります。8%と10%が混ざっている場合は按分する必要があり、さらに面倒なことになります。

税込合計からの値引きをするときには、事前に税理士さんや公認会計士さんとよく相談をしておくべきです。なお、共通の商品券やポイント払いの場合は値引きではなく、支払方法なので消費税額の変更はありません。

 


解説は以上です。


 


Copyright(C)2018-2021 waenavi, All rights reserved. [www.waenavi.com 定礎 平成三十年八月]
Youtube | 公式サイト | twitter | 著作権 | 運営者情報 | お問い合わせ