PR
私が使っているPCガジェット類
作業環境で実際に使っている・気になっている周辺機器などをまとめました
※ 一部のリンクは広告(アフィリエイト)を含みます
エクセルで消費税の端数処理 税率ごとに1回だけ丸める
エクセルで消費税の端数処理をするとき、明細の行ごとにROUNDDOWNを置いていませんか？
同じ税率の中で端数の出る行が2行以上あると、それを足した税額は税率ごとにまとめて計算した額とずれてきます
ずれるのは1回あたり1円に満たない額ですが、明細が増えるほど積み上がっていきます
それに国税庁のQ&Aは商品ごとに端数処理して合計する形を認めていないので、そのまま請求書に転用すると、Q&Aが認めていない形の請求書になります
ここで扱う端数処理のルールは、インボイスを出せる登録を済ませた事業者が適格請求書を交付する場合の話です
免税事業者や未登録の事業者が出す書類には、そのまま当たりません
扱うのは公表されているQ&Aをエクセルの数式にどう落とすかまでで、税務の判断には立ち入りません
ジャベ雄先に結論だけ言うと、丸めるのは税率ごとに1回です
目次
明細ごとに丸めると合計が1円合わなくなる
何が起きるのかを先に数字で見ておきます
10%の商品が2つ・8%の商品が2つ混じった、よくある形の明細です
スクロールできます
| 品目 | 税抜金額 | 税率 | 行ごとに切り捨てた税額 |
|---|---|---|---|
| コピー用紙 | 12,345 | 10% | 1,234 |
| インクカートリッジ | 8,900 | 10% | 890 |
| 弁当(テイクアウト) | 4,567 | 8% | 365 |
| ペットボトル飲料 | 3,210 | 8% | 256 |
| 合計 | 29,022 | 2,745 |
行ごとに切り捨てた税額を足すと2,745円です
税率ごとにまとめてから1回だけ切り捨てると、答えが変わります
- 10%対象の税抜合計 21,245円 × 10% = 2,124.5 → 切り捨てて 2,124円
- 8%対象の税抜合計 7,777円 × 8% = 622.16 → 切り捨てて 622円
- 税額の合計は 2,746円
行ごとに丸めると2,745円・税率ごとに丸めると2,746円
国税庁のQ&Aが求めているのは後者の出し方です
丸める回数が減るぶん、端数で動く額そのものが小さくなります
今回ずれたのは8%側だけで、10%側は一致しています
ただ、これは10%対象で端数が出た行が12,345円の1行だけだったからで、たまたまです
差が出るのは同じ税率の中で端数の出る行が2行以上重なったときで、税率が混ざっているかどうかは関係ありません
10%だけの帳票でも、12,345円と6,789円が並べば行ごとの合計は1,912円・税率ごとなら19,134円×10%で1,913円と、やはり1円ずれます
ジャベ雄ずれる額は小さいです
ただ、この作りが認められていないほうが困ります
ルールは税率ごとに1回 国税庁Q&Aの原文
根拠は国税庁のQ&A 問57(PDF)です
適格請求書、いわゆるインボイスに書く消費税額の丸め方を決めている箇所で、【答】の本文をそのまま引きます
問57【答】より(数字は半角に直しています)
適格請求書の記載事項である消費税額等に1円未満の端数が生じる場合は、一の適格請求書につき、税率ごとに1回の端数処理を行う必要があります(消令70の10、基通1-8-15)。
なお、切上げ、切捨て、四捨五入などの端数処理の方法については、任意の方法とすることができます。
(注)一の適格請求書に記載されている個々の商品ごとに消費税額等を計算し、1円未満の端数処理を行い、その合計額を消費税額等として記載することは認められません。
読むところは2か所です
ひとつは一の適格請求書につき、税率ごとに1回という回数の指定、もうひとつが(注)の商品ごとに端数処理して合計する形は認められないという一文です
冒頭の表で行ごとにROUNDDOWNを入れていた形が、この(注)に当たります
Q&Aには計算例も付いていて、税率ごとに合計した税込金額から割り戻す形です
10%対象の税込60,000円なら10/110を掛けて約5,454円・8%対象の税込40,000円なら8/108を掛けて約2,962円です
見積書なら関係ないのでは？
国税庁が適格請求書の例として挙げているのは請求書・納品書・領収書・レシート等で、記載事項には課税資産の譲渡等を行った年月日が含まれます
記載事項から見ると、取引の前に出す見積書をそのまま適格請求書として交付する形は考えにくいところです
ただし同じQ&Aの問25(PDF)には、様式の定めが無いと書かれています
必要事項が記載されていればその名称を問わず適格請求書に該当するとのことでした
書類の名前で決まるものではないので、見積書だから端数処理は自由、とは考えないほうが安全です
実務的な線引きはひとつです
同じ仕組みで請求書まで出すつもりなら、明細行ごとに端数処理して足す作りにしないことです
ここを守れば転用のときに組み直さずに済みます
エクセルで消費税の端数処理を組む
やることは2つ
明細一覧に税率の列を持たせることと、税率ごとに集計してから1回だけ丸めることです
この形にすればQ&Aの求める並びになります
明細一覧に税率の列を足す
明細を並べているシート、つまり見積番号ごとに行がぶら下がっている一覧に、税率の列を1本足します
この記事で使う列は見積番号 / 税抜金額(または税込金額)/ 税率の3本です
10%と8%を行ごとに持たせておけば、税率を条件にして集計できます
気をつけたいのは、文字として「10%」と打たないことです
パーセント書式の数値として入れておけば、集計の条件にも掛け算にもそのまま使えます
文字列で入っていると条件に一致せず、合計がいつまでも0のままになります
そして明細の行では丸めません
金額の列は数量×単価のまま置いて、行ごとの税額の列は作りません
これが今回の前提です
税率ごとに集計して1回だけ丸める
数式に入る前に前提をひとつ
ここから先は明細一覧をCtrl+Tでテーブルにして、テーブル名を「明細一覧」にしてあるものとして書いています
普通の範囲のまま貼ると#NAME?が返るので、そこだけ先に済ませてください
その明細一覧をどう用意するか(マスタと帳票を分ける・テーブルにする・XLOOKUPで単価を引く)は、見積書を関数だけで組む記事のほうにまとめてあります
手元にまだ一覧が無いなら、先にそちらを見てもらったほうが早いかもしれません
あわせて読みたい
Excelで見積書を作るなら知っておきたい関数と機能
エクセルで見積書を作るときに要る関数と機能を、作り方の手順ではなく部品ごとに整理しました テーブル・XLOOKUP・FILTER・表示形式・入力規則・シート保護まで、マクロを使わず関数とExcel標準機能だけで組む前提でまとめています
明細を税抜で持っている場合の式です
D3に見積番号が入っている前提で、10%対象の税額を出します
=ROUNDDOWN(SUMIFS(明細一覧[税抜金額], 明細一覧[見積番号], $D$3, 明細一覧[税率], 10%) * 10%, 0)内側のSUMIFSで、この見積番号かつ税率10%の行だけを合計しています
そこに10%を掛けて、いちばん外側のROUNDDOWNで1回だけ切り捨てます
丸めが外側にひとつだけ、この形が要点です
8%対象は、条件と掛ける率を差し替えた同じ形をもう1本並べます
=ROUNDDOWN(SUMIFS(明細一覧[税抜金額], 明細一覧[見積番号], $D$3, 明細一覧[税率], 8%) * 8%, 0)見積書の合計欄に載せる消費税額は、この2本を足した額です
税率が増えても、増えるのは式の本数だけになります
請求書に転用するときは、足した1行だけにしないでください
適格請求書の記載事項は税率ごとに区分した消費税額等なので、10%分と8%分をそれぞれ書き分けます
丸める単位は帳票1枚の単位です
この式は条件を見積番号にしているので、複数の見積をまとめて1枚の請求書にするなら、条件をその請求書番号の列に差し替えてください
テーブルにしない運用なら、列を直接指定した形に読み替えても結果は同じです
下はA列に見積番号・E列に税率・F列に税抜金額が入っている場合の、10%対象の式です
=ROUNDDOWN(SUMIFS($F:$F, $A:$A, $D$3, $E:$E, 10%) * 10%, 0)SUMIFSはExcel 2007以降ならどの版でも使えるので、配布先の環境をあまり選びません
Microsoft 365なら税率ごとの小計をGROUPBYで一気に出す手もありますが、丸めるのは集計したあとの1回です
税込で持っているときは割り戻す
明細を税込で持っているなら、掛ける部分を10/110に置き換えます
=ROUNDDOWN(SUMIFS(明細一覧[税込金額], 明細一覧[見積番号], $D$3, 明細一覧[税率], 10%) * 10 / 110, 0)8%対象なら8/108で、問57の計算例もこの形です
税抜のつもりで10%を掛けると税を二重に乗せるので、明細の金額が税抜か税込かだけは先に決めておいてください
切り捨て・切り上げ・四捨五入のどれにするか
ここに正解の関数はありません
Q&Aが任意の方法とすることができますと書いているとおりで、どれを選んでも回数の条件さえ守っていれば形は成り立ちます
スクロールできます
| 丸め方 | 関数 | 10%対象 2,124.5 | 8%対象 622.16 |
|---|---|---|---|
| 切り捨て | ROUNDDOWN | 2,124 | 622 |
| 切り上げ | ROUNDUP | 2,125 | 623 |
| 四捨五入 | ROUND | 2,125 | 622 |
3つとも第2引数は0で、整数の位で丸める指定です
どれを選んでも式の形は変わらず、いちばん外側の関数名が入れ替わるだけになります
値引き行のようにマイナスの金額が混じるときは、丸める場所に気をつけてください
ROUNDDOWNはマイナスの数を0に近づける向きに切り捨てるので、行ごとに丸めると向きが変わります
税率ごとに集計したあとの1回だけなら、これは起きません
決め方は、社内でどれかに統一しておくのが現実的だと思います
取引先との取り決めがあるならそちらが優先で、会計ソフトの設定と揃えると突き合わせが楽です
ジャベ雄関数を選ぶ話より、社内でバラバラにしないほうが大事です
明細の合計と小計が1円ずれて見えるとき
結論から言うと計算そのものは合っています
ずれて見えるのは、表示のために丸めた数字を人が足しているからです
ここは単価や数量に小数がある場合の話です
単価が小数第2位まで入っている・作業時間を0.5時間で数えている、そういう明細だと「印刷した金額を電卓で足したのに小計と1円合わない」という質問が飛んできます
数量×単価がいつも整数で収まる明細なら起きないので、当てはまらない方は読み飛ばして大丈夫です
冒頭の2,745円と2,746円とは別物です
あちらは税額をどこで丸めるかの話で、こちらは明細の金額に小数が残っていることで起きます
表示形式で小数点以下を隠しても、セルの中身は小数のまま残ります
SUMは中身の数値で足すので、画面の整数を電卓で足した答えとは1円単位で食い違います
選べるのは2通りです
そのままにするなら、聞かれたときに説明できれば足ります
表示と中身をそろえるなら、明細の金額の列をROUNDDOWNで確定させます
金額の列を丸めた場合も、税額は確定した金額を税率ごとに合計してから1回だけ丸めます
問57が書いているのは消費税額等の端数処理についてなので、明細の金額そのものを丸めていいかは、最後の確認先で聞いてもらうのが確実です
エクセルのオプションにある「表示桁数で計算する」は触らないほうが安全です
ブック全体のセルの値が表示どおりの数値に置き換わるので、元の小数に戻せなくなります
まとめ 丸めるのは税率ごとに1回だけ
押さえどころは3つです
- 明細一覧に税率の列を持たせて、明細の行では丸めない
- SUMIFSで税率ごとに集計し、ROUNDDOWNはいちばん外側に1回だけ
- 切り捨て・切り上げ・四捨五入は任意なので、社内で統一する
10%だけの帳票でも話は同じです
問57の(注)は税率がいくつあるかと無関係に、商品ごとに丸めて足す形そのものを認めていません
軽減税率の行が増えても、やることは式をもう1本並べるだけです
先にこの並びにしておくと、あとで組み直さずに済みます
税額の扱いは取引の形によって変わります
この記事はQ&Aの内容を数式に落としたものなので、明細の金額そのものを丸めていいかも含め、自社の運用に落とすときは顧問税理士や所轄の税務署に確認してもらうのが確実です
ジャベ雄数式の形が決まれば、あとは丸め方をひとつに決めるだけです
最後に・・・
PythonとExcelを中心に仕事に役立つ業務ツールや自動化、スクレイピングツールの作成を受注していて、クラウドワークスでは気が付けば100件以上のお仕事を受注してきました！
会社員をやりながらの副業なので時間の捻出は相応ですが、クライアントの方々と近い立場でこちらからも提案しながら活動していますのでお悩みあれば是非ご相談ください
コメント