マンガでよめる痔のこと・薬のこと

ROUND関数を使用した数値の合計と使用しない合計値を一致させる方法

単価8円98銭の品物に関して、各担当者ごとの売上高をまとめています。

A   8個   71.84 → 72円
B  21個  188.58 → 189円
C  33個  296.34 → 296円
D  17個  152.66 → 153円
E  11個   98.78 → 99円

売上高に関してはROUND関数で整数値にしているのですが、
担当者ごとの売上高を合計したとき(1)と、
個数の合計から売上高を出したとき(2)で、数値が一致しません。

(1)72+189+296+153+99 = 809円

(2)8+21+33+17+11 = 90個
   90個 * 8.98円 = 808.2 ≒ 808円

その後に他の集計でデータを使用する際には、
(2)の合計値を基準に使用するので、
1円の差異を調整するために、毎回担当者の数値を変更しています。
(上記例の場合は、ROUND関数を使用した際に一番差の大きい、
 担当者Bの売上高を「188円」に入力し直しています。)

実際の担当者はもっと多く、単価ももっと幅のある価格が複数あるので、
上記は一例ではあるのですが、このようなときに、
毎回毎回両方の合計値が一致してるかどうか確かめるのが煩わしく、
一致していなかった場合、どの担当者の数値を変更するのが適切か、
いちいち探し出すのも面倒なため、
予め作業列等に関数を入力しておき、
毎回このような作業をすることを無くしたいと考えています。


別の集計で使用するために、セルの見え方による数値の変更ではなく、
数値自体が変更になるようにしたいのですが、

・(2)の合計値と差異が出た場合にLARGE関数やRANK関数を使って探す。
・ROUNDDOWN関数とRONDUP関数を使った数値も作業列に表示しておく。

ぐらいしか、良い方法が思いつきません…。

作業列が増えても、なるべく手作業で入力し直したり、
目で探したりする手間を無くしたいのですが、
関数を使用して一発で解決できる策はないでしょうか?
分かりにくい説明で申し訳ございませんが、ご教授お願い致します。

A 回答 (5件)

経理上こんなことをして良いのか、全く存じませんがご参考まで。


ツール/ゴールシークを用いて、3.9捨4.0入→3.8捨3.9入→...という様に条件を振って、
個数計×単価を四捨五入した値と、四捨五入後の合計値の差が0になるところを探します。
ゴールシークが成功したところで、D列を値に変換します。
画像を添付いたしますので、ご覧下さい。
「ROUND関数を使用した数値の合計を一致」の回答画像5
    • good
    • 0

前のお二人の御意見も納得した上で、経理上こんなケースも起こり得るので、あえて書かせて頂きました。

    • good
    • 0

小生ならば、


単純にROUND関数との差を一列(非表示)置き、総ての差のセルのMAXの判別後に初めて、189円なり188円の数値を表示する様にと思うのですが、マクロで処理した方が入力する式が少ない上に変更箇所のセルに色付けしたり、何よりデータ量が多い時は処理が速い筈です。(ロト6の出目計算させた時、セル上での物よりマクロ上の物の方がはるかに速かったと記憶してます。)
    • good
    • 0

アルゴリズムが間違っています。



整数化した数の合計と
実数のまま合計し、最終的に
整数化した数とで計算結果が
一致することは稀だと思います。

(1)で実数のまま合計して、
その結果を整数化してください。

因みに、(1)を実数のまま合計すると、
808.2円になりますね。
    • good
    • 0

(1)と(2)の結果を同じにしたいなら、(2)の合計値を算出すると数式を以下のようにしてください。



=SUMPRODUCT(ROUND(個数のセル範囲*8.98,0))
    • good
    • 0

お探しのQ&Aが見つからない時は、教えて!gooで質問しましょう!

このQ&Aを見た人が検索しているワード

このQ&Aと関連する良く見られている質問

QSUMの合計金額が合わない、金額のずれを直すには?

EXCEL 2007 を使用しています

電気の請求額一覧を次の数式を入れて作成しました。
12社分の個別金額を出し→各社H.E+F請求額を→オートSUMで12社分合計を出しました

下記の様な詳細金額を含めた請求額を1つの表の中で12社分出しております

(例.1社分の請求額の詳細)
A.今月メーター数 B.先月メーター C.A-B=KWh D.KWh小計 E. あ+い のD計 F.請求額D*27円 G.消費税F×0.05 H.E+F請求額
-----------------------------------------------------------------------------------------------------------------
(例)1社分のメーター詳細
あ-1=A.今月メーター数 5,115 B.先月メーター 4,496 C.A-B=KWh619 あ-1+あ-2の合計KWh=814
あ-2=A.今月メーター数80,429 B.先月メーター80,234 C.A-B=KWh195

い-1=A.今月メーター数13,627 B.先月メーター13,112 C.A-B=KWh515 い-1+い-2の合計KWh=747
い-2=A.今月メーター数68,843 B.先月メーター68,611 C.A-B=KWh232
↓↓↓
あ+い のD. KWh小計=1,561 あ+い のF.請求額小計=42,147あ+い のF.請求額消費税=2,107
----------------------------------------------------------------------------------------
↓↓↓
1社分請求額 (1社分のメーター=あ+い)= H.E+F→ 44,254円
----------------------------------------------------------------------------------------

この作成した「詳細金額請求書」の各企業請求額を元に「単純な合計金額一覧」を別のExcelに作りました

単純に企業名と企業別請求金額だけを入力し、12社分 オートSUMで合計金額を出したのですが、
このExcel合計金額と「詳細金額請求書」の12社分合計金額が1円差が有り、困っております

勿論、2つのExcelとも各企業12社分の請求額はぴったり同じなのですが、オートSUMの合計金額
の箇所だけが1円差が出るのです?
今までこの様なことが無かったのでどうしていいか分かりません?

電卓で合計金額を計算したところ、「詳細金額請求書」の方の合計金額が1円違っている様です

どう質問していいのか分からず、細かく書きわかりづらい説明をしましたが この数字が合わせるにはどうしたら良いのか教えて下さい!!

EXCEL 2007 を使用しています

電気の請求額一覧を次の数式を入れて作成しました。
12社分の個別金額を出し→各社H.E+F請求額を→オートSUMで12社分合計を出しました

下記の様な詳細金額を含めた請求額を1つの表の中で12社分出しております

(例.1社分の請求額の詳細)
A.今月メーター数 B.先月メーター C.A-B=KWh D.KWh小計 E. あ+い のD計 F.請求額D*27円 G.消費税F×0.05 H.E+F請求額
------------------------------------------------------------------------------------------------------...続きを読む

Aベストアンサー

「消費税マジック」です。

計算値(小数点は切り捨てせず、そのまま計算)
A 10.5円
B 5.25円
C 4.2円
D 8.4円
計 28.35円

表示値(小数点は切り捨てせず、整数部分のみ表示)
A 10円
B 5円
C 4円
D 8円
計 28円 ←ここ注目。10+5+4+8は28ではなく27の筈

単純に入力した値(見た目上の、1円単位までしか入力せず)
A 10円
B 5円
C 4円
D 8円
計 27円 ←ここ注目。オートSUMの計算結果は、当然、10+5+4+8で27

電卓で計算した値(見た目上の、1円単位までしか電卓に打たず)
A 10円
B 5円
C 4円
D 8円
計 27円 ←ここ注目。当然、10+5+4+8で27

個々の請求額を求めたら、合計を計算する前に「INT関数やROUND関数で小数点を消してから」合計して下さい。

修正後の計算値(ROUND関数を使用)
A 10.5円→11円
B 5.25円→5円
C 4.2円→4円
D 8.4円→8円
計 28円(11+5+4+8=28)

修正後の計算値(INT関数を使用)
A 10.5円→10円
B 5.25円→5円
C 4.2円→4円
D 8.4円→8円
計 27円(10+5+4+8=27)

小数点以下を「切り捨てる」か「四捨五入する」か「切り上げる」か、で、合計が数円~数十円の(最大で「明細の件数-1円」の、20件あれば19円の)差異を含む事になります。

この誤差は「300円しか持って無い時に、税別96円の物を3つ同時に買おうとすると、96×3+96×3×0.05=302円で2円足りないが、1個づつ別々に会計すると、96+96×0.05=100円×3回=300円で、ぴったり買える」という、不思議な現象を起こします。

その為「お会計はまとめてお願いします。バラでのお会計はご遠慮下さい」ってレジの所に貼ってあるお店もあります。

「消費税マジック」です。

計算値(小数点は切り捨てせず、そのまま計算)
A 10.5円
B 5.25円
C 4.2円
D 8.4円
計 28.35円

表示値(小数点は切り捨てせず、整数部分のみ表示)
A 10円
B 5円
C 4円
D 8円
計 28円 ←ここ注目。10+5+4+8は28ではなく27の筈

単純に入力した値(見た目上の、1円単位までしか入力せず)
A 10円
B 5円
C 4円
D 8円
計 27円 ←ここ注目。オートSUMの計算結果は、当然、10+5+4+8で27

電卓で計算した値(見た目上の、1円単位までし...続きを読む

QオートSUMの答えがどうやっても合わないのですが・・・・

エクセル2000を使用しています。
縦計算と横計算程度のそれほど難しくないワークシートを作りましたが、ある列の縦計算(その列の数値の合計)がどうしても合いません。電卓で確認しましたが、どう計算してもエクセルの答えが誤っているのです。

・その列の合計されるべき列(R11~R31)には(=$N$6*Q11~Q31)が入っている。(N6及びQ11~31は実数が入る)
・その合計の答えが入るべきセル(Q32)には(=SUM(R11:R31))が入っている。
・隠れている行や見えない行は無い。(行番号で確認)
・エクセル上の答えが実際の数値(電卓による)より2少ない

どう考えてもコンピュータが間違っているとしか思えません。こんな事ってあるんですか?
解決法があればおしえて下さい!!

Aベストアンサー

こんにちは。

見えている部分だけ合計してもだめです。
表示されている桁だけでなく、小数点も計算されているのでしょう。

=ROUND($N$6*Q11,0)

のようにして、合計したらどうなりますか?

QEXCEL SUM関数で合計値が違う場合

教えてください。
EXCELのSUM関数で合計値が違う場合があるのですが、
何が原因で起こるのでしょうか?
また、どうすれば直るのでしょうか?

Aベストアンサー

小数点以下0桁で書式設定(整数)にすると四捨五入されて表示されることが大きな原因と思います。
A1:0.8 -> 表示は 1
A2:0.6 -> 表示は 1
sum(a1:a2) -> 計算は 0.8+0.6=1.4 -> 表示は 1

Excelの設定(オプションー計算方法)で、表示桁数で計算するをチェックすると sum(a1:a2) は 2 になりますが、他所で影響が出るかもしれないので、お勧めしません。


人気Q&Aランキング