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

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関数を使った数値も作業列に表示しておく。

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

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

No.5 ベストアンサー 回答者： mitarashi 回答日時： 2011/01/04 19:45

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

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

No.4 回答者： awasan0419 回答日時： 2011/01/04 18:49

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

No.3 回答者： awasan0419 回答日時： 2011/01/04 18:44

小生ならば、

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

No.2 回答者： Visar 回答日時： 2011/01/04 17:46

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

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

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

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

No.1 回答者： MackyNo1 回答日時： 2011/01/04 17:39

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

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