Excelで縦計が合いません。

質問

私はExcel2010を使っています。
実際のひな形とは違いますが、同じような事を再現したのが添付した画像になります。
セルの番号が切れてしまって見にくいかも知れませんが、Ｅ列に「金額」Ｆ列に
「消費税」、Ｇ列に「小計」を入れています。
Ｅ7の金額「▲224円」に対しての消費税ですが、これは先方から届いた物を手入力しています。
他の消費税のセルには「=E6*0.08」などの数式が入っていますので自動で計算されています。
消費税の列の合計が「1,519」になっていますが、電卓で計算すると「1,518」になります。
消費税の合計のセルには「=SUM(E6:E13)」の数式を入れてみたり、「=INT(SUM(E6:E13))」も試してみましたが結果は同じでした。
消費税を計算するセルに「=ROUNDDOWN(E6*0.08,0)」も試してみましたが、同じ結果でした。
説明が下手で申し訳ありませんが、どなたかお力をお貸し下さい。
よろしくお願いします。

回答No.11

Green_Tree · ベストアンサー

とりあえず、面倒な説明は後にして以下やってみてください。
・F6に「=ROUND(E6*0.08,0)」を入力してF7～F12まで数式コピー（オートフィルででも）
・F7が-18なので、-17を手入力
・F14に「=SUM(F6:F12)」
※手動計算になっているかもしれないので、計算式を再度入力しなおし。

さて、F14の合計額はどうなりましたか。1518になりました？

まず前置きとして
・書式設定で表示形式を数字にしたことにより、設定桁数以下は自動的に四捨五入されます。（F6が69.84なのに70と表示されているのはそのため）
・理解されているようですが、表示上見えないだけで小数点以下も計算に含まれます。（F6は70ではなく実際は69.84として計算されています）
・消費税は小数点以下も計算され合計は1518.32で、F7を-17に変更で1519.24になり表示形式で四捨五入され1519と表示されます。

これを表示通りの合計値にしたいのなら、まずは消費税全ての小数点以下を丸める必要があります。（端数処理）
そこでROUND関数を使うようになるのですが、小数点以下を四捨五入、切り捨て、切り上げ、どの処理にするのかを決めなければなりません。

ですが、消費税が金額*0.08で四捨五入となっているところに、-224*0.08＝-17.92→四捨五入で-18のところを切り捨ての-17としており矛盾してます。
「先方」からの指示とのことですが、先方はそもそも消費税の小数点以下の処理をどのように指定しているのでしょう。

「基本は四捨五入で、指示したところは手入力する」なら、ROUND関数。
「基本は切り捨てで、指示したところは手入力する」なら、ROUNDDOWN関数。
「基本は四捨五入だが、マイナス金額の場合は切り捨てにする」なら、IF関数でROUNDとROUNDDOWNの組み込みとかで。

先方に消費税の端数処理方法を確認してから、どの関数を使うのか決めてください。

尚、金額及び小計の合計が合っていません。
計算式がおかしいか、もしくは手動計算になっていて金額修正をしても自動反映されないのかもしれません。
計算式及び設定を確認しなおしてください。

お礼

お忙しい中色々と教えて頂きましてありがとうございました。
教えて頂いたやり方で出来ました。
本当に助かりました。

回答No.10

HohoPapa

>電卓で計算する時も小数点もいれれば
>問題の無い結果になるのですが、
>あくまでも表に表示された数字の合計が
>電卓でも表の合計でも同じにしたいのです。（小数の表示無し）

個々のセルの表示設定で小数点以下を見えなくしていることから、
見える値（見かけ上の値）は、
小数点以下第一位を四捨五入した値です。

Excel上でそのような計算をしたい
（つまり個々のセル値を四捨五入してから合計したい）
ということであれば

F14の計算式を
=SUM(INDEX(ROUND(F6:F13,0),0))
とすることで実現できます。

なお、
消費税額を小数点以下まで計算し、
１円未満をそのままにしておくことが
正しいかどうか疑問ではあります。

お礼

お忙しい中的確なご回答を頂きありがとうございました。
教えて頂いた計算式で問題無くできました。
助かりました。

回答No.9

bunjii

>あくまでも表に表示された数字の合計が電卓でも表の合計でも同じにしたいのです。（小数の表示無し）
消費税の計算方法が正しいか否かを度外視して提示の数表通りにF6が70、F8が684、F9が264、・・・・・のように表示されて然も小数点以下の端数が無いようにすれば電卓で計算した値と一致することになります。
F6=ROUND(E6*0.08,0)
F6セルをF8～F12へコピペすれば良いでしょう。
ROUND関数は指定の桁以下を四捨五入で丸める計算です。
指定する桁を0にすると小数点以下1位を四捨五入します。
Excelでは表示方法で端数を見せない方法がありますので集計を正しく行うには端数をどのように処理するかを決めて矛盾が起こらないように数表を作成しなければなりません。

お礼

お忙しい中ありがとうございました。
また困った時にはよろしくお願い致します。

回答No.8

okok456

電卓で「▲224」の消費税は「▲１８」になりませんか？

消費税が「=E6*0.08」はExcelの基本設定なら四捨五入で
　F7も「=E7*0.08」で　「▲18」　になり合計は「1,518」なります。
「▲17」になった原因を確認されてはいかがでしょう。

「=ROUNDDOWN(E6*0.08,0)」だと切り捨てになり
F6が「６９」にF7は「17」になり合計が「1,517」になります。

余談
金額の合計「18,979」で消費税を出すと切り捨てでも「1,518」になりますね。

お礼

お忙しい中ありがとうございました。
助かりました。

回答No.7

山田 太郎（@f_a_007）

【補足】仕入消費税は、画一の計算式で入力すべきではありませんよ。

仕入消費税は、あくまでも相手先伝票に記載されているそれを円単位で入力すべきです。ですから、通常は、《相手先計算タイプ》をテーブルに登録して、それで計算させます。そこまで仕組むのが面倒であれば、手入力訂正します。

回答No.6

山田 太郎（@f_a_007）

＞Excelで縦計が合いません。

そんなことは絶対にありません。そもそも消費税には銭単位のそれは発生しません。ですから、円単位で計算する限り100％合います。

回答No.5

FEX2053

excelで、小数点以下の計算をすると、合計値が電卓と合わなくなるのは「仕様」です。excelの内部処理が2進数である以上、これはもうどうしようもないです。関数などで、すべての計算を整数化しないかぎり、合わせる方法はありませんのであきらめて下さい。

詳細な説明はこちら
https://eip.econ.kanagawa-u.ac.jp/eip/excel-calc-error.html

回答No.4

bunjii

>Ｅ7の金額「▲224円」に対しての消費税ですが、これは先方から届いた物を手入力しています。
F7セルには手入力で-17としたのですね。

>他の消費税のセルには「=E6*0.08」などの数式が入っていますので自動で計算されています。
消費税は8%ですが1円未満を切り捨てることになっています。
従って、F7以外の計算式は次のようにすべきです。
=ROUNDDOWN(E6*0.08,0)

>消費税の列の合計が「1,519」になっていますが、電卓で計算すると「1,518」になります。
F6とF8～F12には端数が付いているため合計が1519.24になっているようです。
電卓での計算は消費税の算出も検算をしたでしょうか？
F6は1円未満を切り捨てると69になると思います。

>消費税を計算するセルに「=ROUNDDOWN(E6*0.08,0)」も試してみましたが、同じ結果でした。
同じ結果にならないですよ。
消費税の合計は1,517円となりました。

補足

お忙しいところ本当にありがとうございます。
ご指摘のように電卓で計算する時も小数点もいれれば問題の無い結果になるのですが、あくまでも表に表示された数字の合計が電卓でも表の合計でも同じにしたいのです。（小数の表示無し）
面倒臭い話しで申し訳ありません。

回答No.3

marukajiri

画像の合計の計算結果がまず間違っていますので、まず計算式のチェックをしてみてください。

次に消費税の欄ですが、自動計算して小数点第１位を四捨五入するのであれば、その計算が間違いなくできているかチェックするために、わざと小数点第１位まで表示させてみるのです。一番上の「70」なら「70.0」となればいいのです。

四捨五入は、「=ROUND(E6*0.08,0)」のような式でいいと思います。「●●.0」という表示にすべてなっていれば、正しい計算がされているということですので、確認ができた時点で小数点以下は表示しないようにしていいと思います。

気になったのは▲の手入力部分です。消費税の計算は自動計算の四捨五入では「▲224」→「▲18」になるはずです。確認してみて下さい。尚、実際の打ち込みの際は「－」マイナス記号を使うはずです。

補足

お忙しいところありがとうございます。
「▲224」→「▲18」ですが、これは相手から指示があったものですので、数式で計算しないで手入力にしています。
また、「-」で打ち込んだ時に「▲」になるように設定しています。
基本的には表示された消費税の合計を縦計算するようにします。
当然、見えていない小数もExcelが計算するので単純に電卓で計算した結果と違ってくるのは私自身理解しています。
ただ、あくまでも表示された数字の縦計算が合っていないといけないとの事なので悩んでしまっています。

回答No.2

mt2015

商習慣的に消費税の1円未満は切り捨てですので、貴方の設定した「=ROUNDDOWN(E6*0.08,0)」が正しいと思います。
しかし、実際には「=E6*0.08」と言う式が入っているうえに、書式が小数点以下の表示桁数0と設定されているので以下の様になっています。

金額873の消費税→69.84(表示上は70）

金額8556の消費税→684.48(表示上は684）
金額3300の消費税→264
金額2329の消費税→186.32(表示上は186）
金額2240の消費税→179.2(表示上は179）
金額1905の消費税→152.4(表示上は152）

で、金額-224の消費税→-17.92　ですが、-17と手入力をしていますよね。
したがって合計すると
69.84+(-17）+684.48+264+186.32+179.2+152.4＝1519.24≒1519
と、なっています。

「1,519」が正しくないから合わないと言う結論になるでは？

補足

お忙しいところありがとうございます。
おっしゃる通りで「1,519」と計算されてしまうので、実際に電卓で計算した結果の「1,518」と異なってしまいます。
電卓と同じ結果になるような方法がないものかと考えています。

回答No.1

山田 太郎（@f_a_007）

【アドバイス】提示されている例題の見直しを！

添付図を見てもらえれば分かりますが、《四捨五入》《切り捨て》
《丸目なし》のいずれとも一致しません。そこで、同じように、３タイプの計算結果を例示されることをお勧めします。

＊Excelは一度も利用したことがないので、付け刃です。ですから、私が間違っているかもしれませんが・・・。なお、通常は、相手先の設定に合わせて計算します。で、仕入の場合は、大半は切り捨てです。