Excel 消費税の計算において演算誤差を微小値加算方式で解決する式

消費税の計算は演算誤差があり得る

1.1が2進数にすると循環小数だから

このためVBAの関数は作ってきましたが、今回はセルに入れる数式で演算誤差を起こさない精密な関数を作りたいと思います

ところで万能税込数は20790でいいかな？

110 → 2 * 5　*　11
108 → 2^2 * 3^3
105 → 3 * 5 * 7
この3つの数の最小公倍数を求める
2^2 * 3^3　*　5 * 7 *　11
7 * 11 * 3^3 * 2 * 5 = 20,790
また110と108の最小公倍数は
2 * 55 * 54 = 5,940

2019年9月までは108と105の最小公倍数をもとめるので
3 * 35 * 36 = 3,780

さてこうなるとわかるとおり、どんどん最小公倍数は大きくなっている。しかも飛躍的に伸びることが分かる。

消費税の逆数で割り切れる数の出現間隔

これは公約数のうち2,5を除いた数を相乗すると求められる。
このため整数をnとすると
n/110は110以上では11おきに割り切れる
110の次は110 + 11 =121
n/108 は 108以上では　2 * 27 なので27おきに割り切れる
ということは110と108の逆数で割り切れる数は最小公倍数の5940の公約数から 5と2を除く297おきに出現する
5940+297=6237
また逆数が割り切れる数を多く持つのは、２と５を除く公約数の積の大きさに反比例すると予想される。
さらにこの逆数が大きいほど潜在的なエラーは発見されないことが予測できる。

消費税の計算はAIでもムリ

AIはしょせん人間が作るので、人間が理解できないものはAIも理解できない。よって必ず失敗する。
なぜ無理なのか。

誤差が許されない

AIができるできる！というのは全部ある程度誤差があってもいいところ。それ以外は誤差があるのでだめ。これをごまかすために「そんな誤差がない仕事なんて非効率。」とウソを言う。

消費税の計算時期の基準と経過措置

2019/10/1で切るというのは不可能で、この辺が理解できていない人はAIの話なんかしない方がいいと思う。
これは検収基準のような企業の経理のやり方と関連している。
この相手企業の経理の方法は予測ではだめなので、当然AIなど役立たない。
人間が教えてやらないといけない。

経過措置と地方消費税の違い

AIで解決ができる、AIが人間の仕事をどんどん奪うというバカげたことを言う人って、まずこれすら理解できていないことがわかるので信用できない。というか頭が悪すぎるので人にアドバイスをするのをやめた方がいい。あとマスコミもそういうバカに本を書かせたり新聞記者が取材するのをマジでやめた方がいいと思う。

収益認識に関する会計基準が適用される場合

　今般の「収益認識に関する会計基準」の導入に伴い、法人税法等の改正が行われたところですが、取引の事例によっては、「収益認識に関する会計基準」に沿って会計処理を行った場合の収益の計上額、法人税における所得金額の計算上益金の額に算入する金額及び消費税における課税資産の譲渡等の対価の額がそれぞれ異なることがありますので注意が必要です。
　次の事例は、「収益認識に関する会計基準」に沿って会計処理を行った場合に、会計・法人税・消費税のいずれかの処理が異なることとなる典型的なものとなります。
　引き続き、処理が異なることとなる事例について適宜公表してまいります。

※　消費税は、事業者が行う課税資産の譲渡等の取引を課税対象としており、売り手側の課税資産の譲渡等は、買い手側の課税仕入れとなります。
　このため、原則として、法令等に規定する一定の取引や、例えば、資産の譲渡における棚卸資産の引渡しの日について、売り手側は出荷基準、買い手側は検収基準を採用しているなどの場合以外は、売り手側の課税資産の譲渡等に係る対価の額や時期とこれに対応する買い手側の課税仕入れに係る対価の額や時期が異なることにはなりません。

さてこの文章を読んで「異なることがない。」と国税局が書いていると思う人はどれくらいいるんだろうか。それは誤り。その場合は十分考えられるので、「異なる場合があります」と読んだ方がいいです。つまり国税局もあまりよくわかっていない。ということはだれもわかっていないんだからAIができますというのは「完全にウソ」ということです。こういうバカが多い。Qitaではあまり見かけないけど、ビジネス雑誌とかはこんな薬を打って脳がスカスカなやつが記事を書いていて誠にレベルが低い。そもそもQitaのようなプログラミングスキルが高い(※自分は除きます)連中が言わないことをなぜいうのか謎。中国の経済が崩壊するレベルです。（その時は日本も死ぬのに期待する人が多いのも謎）

設例

	A	B	C	D	E
1	税込み	税抜き	税額
2	20790	????	=A2-B2

こうした例でB2に今日の日付で税率をきりかえて計算する関数を

場合分け

端数処理

一般的な民間企業では切り捨てが一般的です。公的機関は公平性の観点から四捨五入があり得るようです。

税率

次に税率は理論的には10%のみ、軽減税率、経過措置があります。このとき経過措置と軽減税率では地方消費税の割合が違います。

https://www.tanakakaikei.net/free1
https://www.osaki-tax.com/free6

ここで演算誤差を考慮に入れる必要があるため、微小値を足します。
https://tech.nikkeibp.co.jp/dm/article/LECTURE/20130729/294883/?P=5
もう一つ
文字列を日付形式変える関数を使います。
https://support.office.com/ja-jp/article/%E6%96%87%E5%AD%97%E5%88%97%E5%BD%A2%E5%BC%8F%E3%81%AE%E6%97%A5%E4%BB%98%E3%82%92%E6%97%A5%E4%BB%98%E5%BD%A2%E5%BC%8F%E3%81%AB%E5%A4%89%E6%8F%9B%E3%81%99%E3%82%8B-8df7663e-98e6-4295-96e4-32a67ec0a680
次に単純に今日の日付で計算するケース、検収日、検査日、検収書を取り交わした日に設定するケースがあります。
要は単純に購入した日ではなく、購入した品物が企業に来て、かつ企業でそれを検査して契約通りの商品、数量である、不良品がないかなどを検収（検査という場合もある）して初めて購入したといえるため、そこで消費税を計算するというものです。
簡単に言うと売上計上基準の違いということになります。
この説例では10%と8%とします。

税込価格を10%で割り戻したあと、切り捨てで税抜き価格を求める場合

=IF(TODAY()>=DATEVALUE("2019/10/1")*1,ROUNDDOWN(A2/1.1+0.0001,0),ROUNDDOWN(A2/1.08+0.0001,0))

高速化+マイナスを考慮

=IF(TODAY()>=DATEVALUE("2019/10/1")*1,INT(ABS(A2)/1.1+0.0001)*SIGN(A2),INT(ABS(A2)/1.08+0.0001)*SIGN(A2))

VBAで実験

以前作成した
GetTicCountClassをつかってみます
https://qiita.com/Q11Q/items/13492275186ee9c95b8d
GettickCountTimeClassで時間を計ります

'微小値加算後　INTで切り捨てる
Dim IntaxPrice As Currency
Dim exTaxPrice As Currency
Dim iCnt As Long
Dim gtTickcntClass As GetTickCountClass: Set gtTickcntClass = New GetTickCountClass 'GetTickCountClassクラスモジュールの宣言と初期化
Dim Dt
Application.ScreenUpdating = True

Dt = gtTickcntClass.GetValueOfTickCnt
Range("A2").Formula = "=IF(TODAY()>=DATEVALUE(""2019/10/1"")*1,INT(ABS(A1)/1.1+0.0001)*SIGN(A1),INT(ABS(A1)/1.08+0.0001)*SIGN(A1))"
For iCnt = 1 To 1000
IntaxPrice = Int(Rnd * 1000000)
Range("A1").Value = IntaxPrice
'exTaxPrice = Int(IntaxPrice / 1.1 + 0.0001)
Next
Debug.Print gtTickcntClass.GetValueOfTickCnt - Dt
Application.ScreenUpdating = True

End Sub

微小値加算をしてINTした結果

8406
8547
8687

Sub CountTimeCalc()
' // 微小値加算後　小数点以下をRoundDownで切り捨てる
Dim IntaxPrice As Currency
Dim exTaxPrice As Currency
Dim iCnt As Long
Dim gtTickcntClass As GetTickCountClass: Set gtTickcntClass = New GetTickCountClass 'GetTickCountClassクラスモジュールの宣言と初期化
Dim Dt
Application.ScreenUpdating = True

Dt = gtTickcntClass.GetValueOfTickCnt
Range("A2").Formula = "=IF(TODAY()>=DATEVALUE(""2019/10/1"")*1,RoundDown(ABS(A1)/1.1+0.0001,0)*SIGN(A1),RoundDown(ABS(A1)/1.08+0.0001,0)*SIGN(A1))"
For iCnt = 1 To 1000
IntaxPrice = Int(Rnd * 1000000)
Range("A1").Value = IntaxPrice
'exTaxPrice = Int(IntaxPrice / 1.1 + 0.0001)
Next
Debug.Print gtTickcntClass.GetValueOfTickCnt - Dt
Application.ScreenUpdating = True

End Sub

結果

4203
4016
5734

微小値を加算しないRondDownはもっと遅くなる

ExcelのRoundDownは微小値なんていらないんじゃないか、と思いますよね普通。
実はわたくしもこれをやってみるまで、そう思ってました。
3回しか実行していませんが、結果は驚くべきものです。
微小値を加算しない方が遅いのです。

Sub CountTimeCalc()
' // 微小値加算をしないで切り捨てる
Dim IntaxPrice As Currency
Dim exTaxPrice As Currency
Dim iCnt As Long
Dim gtTickcntClass As GetTickCountClass: Set gtTickcntClass = New GetTickCountClass 'GetTickCountClassクラスモジュールの宣言と初期化
Dim Dt
Application.ScreenUpdating = True

Dt = gtTickcntClass.GetValueOfTickCnt
Range("A2").Formula = "=IF(TODAY()>=DATEVALUE(""2019/10/1"")*1,RoundDown(ABS(A1)/1.1,0)*SIGN(A1),RoundDown(ABS(A1)/1.08,0)*SIGN(A1))"
For iCnt = 1 To 1000
IntaxPrice = Int(Rnd * 1000000)
Range("A1").Value = IntaxPrice
'exTaxPrice = Int(IntaxPrice / 1.1 + 0.0001)
Next
Debug.Print gtTickcntClass.GetValueOfTickCnt - Dt
Application.ScreenUpdating = True

End Sub

結果

9078
8296
10094

税込価格を10%で割り戻した値を、円未満四捨五入をおこなって税抜き価格、税額を求める場合

=IF(TODAY()>=DATEVALUE("2019/10/1")*1,ROUND(A2/1.1+0.0001,0),ROUND(A2/1.08+0.0001,0))

実務の設例

	A	B	C	D	E	F	G	H	I	J	K	L
1	取引日	検収日	相手業者	属性	経過5経過8軽減8通常10	品名	数量	単価税込	税込み	税抜き	税額
2	2019/9/30	2019/10/1	Qita商店	適格Invoice	通常10	りんご	10	2079	20790	？？？	=A2-B2

最低でもこれくらいのパラメーターで決まるので、どうみても計算が地獄です。なにより商品の種類と相手方の属性、取引時期、基準で変わるという非常に厄介なところで、単純に計算式をいれれば税額が求められるということがありません。これって解決方法があるんだろうか。
とりあえず今回は演算誤差のない端数切捨てということで記事をまとめています。自分のできる範囲で技術的に今できるのはこれくらいか。
また、今までなら税抜き価格を合計して8%をかけますが、それも難しい場合が出てくるでしょう。
このため消費税はその都度計算する方式に変えた方がいいのかもしれません。

今回のまとめ

微小な値を消費税が1.125などと決まった場合を想定して10000分の1に設定し、これを加算して切り捨てて端数処理します。これでRoundDownは100%正確に計算するでしょう。
それと
日付はDateValueで変換にする。実際は日付は整数なので*1はいらないけど、まあ見た目かっこいいから。
という2点です。

最後にウソの付き方

自分の主張に合うように適当に情報をぶった切って持ってくる。
AIで仕事はなくなる（その根拠はぶった切って切ったデータばかり）
プログラムは出さない（よくわかっていないから）
適当なストーリーをでっちあげる。なぜ適当なストーリーなのか、コストを度外視するから。
そういう適当さがばれるときは、疑問を持った人を罵倒するか、ごまかすか、これは国家的プロジェクトだ反日だとかレッテルを張る。
適当なストーリーをでっちあげたあげく（統計的な処理もなにもなく）未来はこうなると言い出す。

だいたいこういうのしか書いていないので、ビジネス雑誌が売れるわけがない。

自分の例で話すと
108が27おきに割り切れる数が出現するのは10万までの整数を税率をかけて端数整理をして、それを税抜きに戻して戻るかという実験をしてみつけたのですAI素晴らしい！
みたいなことを書きますね。