2014年11月23日

端数処理で値が変わる!?(表示桁数で計算する) /Excel

Excelで集計をしていて、少数点以下の桁数が多いと見難いから、セルの書式設定で表示形式を変えて小数点以下を表示させないようにしたり、よくあるこだけど、その合計を計算すると、Excelで計算した結果と、表示されているセルの値を電卓などで手計算すると合わないことがあります。
何でだ!?

こんな、業務日報があって、作業時間が“分”で表示されています。作業者の時間単価を60で割って分単価を求めて、業務時間と分単価を掛けてその業務コストを集計しています。
分単価が割切れない数値になっているため、業務コストも小数点以下が続く値があります。
一番右の列は、業務コストを“ROUND関数”で小数点以下で四捨五入して整数に丸めています。
20141123_201.JPG

ここで、一番下の行の合計を見ると、小数点以下が表示されている“業務コスト”の合計は、“46,547.33”、四捨五入した方は“46,545”と“1.7円”ほど異なっている。。
ちなみに、四捨五入したセルはこの様な式が入力されている。
20141123_202.JPG

小数点以下が表示されている“業務コスト”のセルを「セルの書式設定」で「通貨」に設定します。これで、小数点以下で四捨五入されて整数で表示されます。
20141123_203.JPG

これで、“業務コスト”のセルも隣の“四捨五入”のセルも同じ値が表示されました。
がー、合計は変わりなく、依然として“1.7円”ほどの違いがあります。
20141123_204.JPG
こうなると、表示されているセルの値を電卓で足していっても、集計された合計値とは合わなくなってしまいます。
これは、書式設定で小数点以下は四捨五入されて整数での表示になっていますが、セルの中身は変わっていないからです。

表示桁数で計算する
エクセルではこういった場合の誤差を無くすためにセルの「表示桁数で計算する」という機能があります。
左上のExcelのボタンを押します。
20141123_205.JPG

そこから「Excelのオプション」を選択します。
20141123_206.JPG

「詳細設定」から「次のブックを計算するとき」で「表示桁数で計算する」にチェックを入れます。
20141123_207.JPG

すると、こんなメッセージが表示されますが「OK」して続行します。
20141123_208.JPG

すると、合計金額が「四捨五入」の合計と一致しました。
20141123_209.JPG

“分単価”も、小数点以下が続いているので、見易いように小数点以下 2桁で丸めてみます。
20141123_210.JPG

すると、合計金額も四捨五入で切上げられたため、“46,545”だったのが“46,558”に“13円”増加しました。
20141123_211.JPG

と、このように、セルの書式設定によって見かけ上の数値の小数点以下は自由に丸めることが出来ますが、実際の計算はセルの中の値で行われるため、書式設定による表示の影響は受けません。
また、「表示桁数で計算する」は非常に便利な機能にも思いますが、セルの書式設定によって計算結果が変わってしまうのは人為的なミスが起きる可能性があると思います。
と言うことで、数値を丸める場合は見せかけに頼ることなく、ハッキリとセルの中身の値そのものを丸めてしまいましょう!



バックナンバー


posted by haku1569 at 23:02| Excelデータ分析 | このブログの読者になる | 更新情報をチェックする
×

この広告は180日以上新しい記事の投稿がないブログに表示されております。