エクセルで、○円○銭の端数を切り捨てた時の請求書が合いません！

質問

エクセルで３月の請求書を作成しました。
シート１～シート９までに３月の売上を日々入力して、最後のシート10に品名毎の売上を算出した売上票を作成しました。
この売上票を元に請求書を作ろうとしたのですが、作成後に微妙な誤差に気づきました。と言うのは、品名毎の単価が全て整数ではなく、例えば21円76銭というような単価設定をしていて、尚かつ合計金額の端数を切り捨てるため、売上票と請求書が合わないんです。
一例ですが、21円76銭を３日に別れて６袋売ったとします。
１日目　21.76円×３袋＝65.28円→切り捨てて65円。
２日目　21.76円×１袋＝21.76円→切り捨てて21円。
３日目　21.76円×２袋＝43.52円→切り捨てて43円。
の、合計129円が正しい請求金額です。
シート10の売上票では、シート１～シート９までのSUMIF関数・SUMPRODUCT関数を使って拾い上げた合計袋数に、単価をかけています。
よって、21.76円×６袋＝130.56円→切り捨てて130円となります。
このようなズレが何ヵ所表れ、最後には数百円の誤差が出ました。
参考までですが、シート１～シート10全てにＡ列に品名、Ｂ列に重量(kg)、Ｃ列に袋数、Ｄ列に数量、Ｅ列に単価、Ｆ列に金額としています。
ちなみにシート10(請求書)のＦ列(金額)の計算式は、(２行目なら)=TRUNC(D2*E2)として、端数を切り捨てています。
シート10のＦ列の計算式を変えれば解決すると思うのですが、複雑すぎて計算式が浮かびません。
ご指導、お願い致します。

回答No.5

Wendy02 · ベストアンサー

こんばんは。

たとえば、1～3日の計算を出すなら、

21.76　3
21.76　1
21.76　2

=SUMPRODUCT(TRUNC(D1:D3*E1:E3))

それぞれをいったん、端数を切り捨てしてから合計すればよいのですから、こういう計算式でよいと思います。

お礼

お返事遅くなりました。
とても参考になりました。
ありがとうございました。

回答No.6

emsuja

合計をどのように求めるかはともかくとして
１日目　21.76円×３袋＝お買い上げ額 65.28円 ご請求額 65円

の様に請求明細を書かないと、支払い側が困ると思うのですが・・・
そのれは問題ないのでしょうか？
上記方法なら 通常の SUM 関数で合計は求められますよね。

勤務先でも、端数処理を伝票の行単位、伝票単位、請求合計単位等色々な処理の請求所があって、経理担当が納品書の額と請求総額が合わないと言って困ってるのを時々目にします。

お礼

請求明細の件は、私なりに説明しやすく省略したので心配はありません。
ありがとうございました。

回答No.4

saru2002

Accessならその方法なんかもいいですが
Excelなら単純に切捨ての関数rounddownを使われてはいかがでしょう？

例：rounddown(1010*0.05,-1)=50　←実際は50.5ですが-1の為少数第一位が切り捨てられてる。

1010*0.05→式でも数値でもOKです。
-1　→　－の場合少数点の方を判定。1の場合、整数の１の桁を判定。

お礼

お返事遅くなってすみません。
参考にさせていただきました。
ありがとうございました。

回答No.3

suo2k

例の場合は１２９円が正しいのですよね？
袋の数に単価をかけずに、売り上げ合計は別途F列の数値を合計するのではダメでしょうか。

お礼

お礼が遅くなってすみません。
ありがとうございました。

回答No.2

a-saitoh

この場合、1ヶ月の合計個数×単価で月の売り上げを計算してはダメですね。

商品Aの○月1日の売り上げ+商品Aの○月2日の売り上げ＋・・・　で計算するように式を変えないといけないでしょう。

シート１～９はどういう風に分かれているのでしょうか？1日1シートではないのですか?

「参考までですが、シート１～シート10全てにＡ列に品名、Ｂ列に重量(kg)、Ｃ列に袋数、Ｄ列に数量、Ｅ列に単価、Ｆ列に金額としています。」
ということで、どこにも日付データがないのでこれではどうしようもないと思います。1日ごとにシートを変えるか、どこかの列に何日の売り上げかのデータを入れないとだめでしょう。

えーと、1日に同じ商品が複数回に分けて出るということがなければ、F列の金額の方をsumifで足せば良いのかな？

お礼

お礼が遅くなってすみませんでした。
ありがとうございました。
参考にさせて頂きました。

回答No.1

xicun

F列の関数にRounddownを使われたら如何でしょう？
それで解決するかと思うのですが…

お礼

ありがとうございました。
お礼が遅くなってすみませんでした。