通貨表示にして整数に見えている列を足したら、合計欄が電卓の答えと1ずれていた。引き継いだファイルを調べると、Power Queryで処理した列に小数値が混ざっていました。社内の別の人が作ったExcelにも、同じ状態がありました。Excelの関数やピボットに慣れた中級者でも、表示と中身が別物だと意識していないと、この事故は見つけにくいです。
先に結論
- 表示形式は見た目だけを変える。中身は変わらない
- 中身も整数にするなら、ROUND・ROUNDUP・ROUNDDOWNで確定させる
- 小数2桁の表示は、ずれに気づくための手段。ずれを消す手段ではない
- 明細ごとに丸めるか、合算してから丸めるかは、運用ルール次第
表示は整数でも、合計は小数のまま足される
1.4を3行に入れ、表示形式を#,##0にします。表示は1・1・1で、SUMの結果は4と表示されました。実際の値は4.2です。目で足すと3なので、1ずれます。通貨表示(¥#,##0)でも同じでした。=SUM(A1:A3)=4はFALSEで、差は0.199999999999999でした。
一方、1.4・2.4・3.4・4.6・5.5の5行は、表示を足しても合計の表示も17で一致しました(実際の値は17.3)。ずれるかどうかは、小数部の並びで決まります。

ROUNDで中身を確定させる
明細の行に=ROUND(B2,0)を入れて合算すると、表示と合計が一致します。1.4を3行足した場合、ROUND・ROUNDUP・ROUNDDOWNで合計はそれぞれ3・6・3でした。ROUNDの仕様はROUND 関数(Microsoft サポート)にあります。五捨六入の式は別の記事にまとめています。→【Excel関数】五捨六入の式|0円ガードの落とし穴
明細ごとに丸めるか、合算してから丸めるか
同じ1.4×3で、明細ごとにROUNDして合算すると3、合算(4.2)してからROUNDすると4でした。どちらが正しいかは、請求書や給与の計算ルールで決まります。この記事では断定しません。個人ごとに丸めて整数を合算するルールなら、明細ごとのROUNDです。丸める位置で結果が変わる話は、時間の丸めでも同じです。→【Excel関数】時間の丸めは順序で結果が変わる理由と対処
率の集計は、平均ではなく合計の比で出す話を別の記事で扱っています。→【Excel関数】率の平均はAVERAGEでなくSUM/SUM
小数2桁の表示は「気づくため」、ROUNDは「消すため」
表示形式を#,##0.00にすると、1.40・2.40のように中身が見え、ずれに気づけます。ただし値は変わらないので、ずれは消えません。気づくための表示と、消すためのROUNDは、役割が違うので分けて使います。
「表示桁数を桁数どおりにする」は元の値が戻らない
オプションの「表示桁数を桁数どおりにする」をオンにすると、合計は3に揃います。ただし、セルの1.4が1に書き換わります。オフに戻しても、1.4には戻りませんでした。元の値が失われるため、ブック全体に効く設定として使うのは慎重にしたほうが安全です。
Power Queryで読み込むと小数が見える
Power Queryで読み込んだ直後の列は、表示形式が標準で、1.4はそのまま1.4と表示されました。列に#,##0を設定して更新しても、設定は保持されます(表示は1、SUMは4.2)。整数に見えているのは、シート側の設定によるものです。
まとめ
- 表示と中身は別。合計がずれたら、小数2桁の表示で中身を確認する
- ずれを消すならROUND系で確定させる。丸める位置はルール次第
- 確認した環境はMicrosoft 365(Excel 16.0、日本語)。Excel 2021未満とMacは未検証


コメント