Excelの丸めで合計がずれるとき|表示桁数とROUNDの順序を確認

Excelの明細を画面で足すと0なのに、合計欄は1になる。明細をROUNDしてから合計すると、元の値の合計をROUNDした場合と1ずれる。このような差は、表示だけを変えたのか、計算に使う値を丸めたのか、どの段階で丸めたのかを分けると確認できます。

まず元データを残し、同じ行・同じ単位を使って3経路を比較します。合計を合わせるために特定の明細へ1を足す前に、採用する集計ルールを確認してください。以下は正の数6件を、Excel 2024で実測した例です。

2026年11月1日、Windows版Excel 2024(Excel2024Retail、16.0.20527.20112、64ビット)で2ケースを実測しました。計算方法は自動、[表示桁数で計算する]はオフであることを確認し、この設定は変更していません。B列は書式0.00、C~F列は0です。6件のID・元値・数式・保存値・表示形式と、xlsxを開き直した後の表示46項目を照合しました。以下の結果は、この版・入力値・設定に限った確認です。

最初に表示形式とROUNDの役割を分ける

操作確認すること
小数点以下を表示しない見た目の桁数を変える操作。セルに格納された値がどうなっているかは別に確認する。
明細ごとにROUNDする各明細の丸め後の値を合計する。元の小数をそのまま足す計算とは結果が異なることがある。
元の値を合計してからROUNDする小数を含む合計に対して1回丸める。明細ごとの丸めとは別の計算。

Microsoftの数値の丸め表示の説明では、通常の計算は画面の表示値ではなく格納された値を使います。また、ROUND関数は、指定した桁数へ丸めた数値を返します。この例ではROUND(数値,0)を使い、整数に丸めます。

確認用の表を作る

空の作業用シートで、A1~F1に次の見出しを入力します。B列には数値を直接入力し、C列にはB列を参照する式、D列には明細を丸める式を入れます。A列のIDは、同じ行を比較しているか確認する目印です。

列見出し・役割
AID
B元の値
C表示だけ0桁
D明細ROUND
E合計後ROUND(合計行だけ)
F差=明細ROUND合計-合計後ROUND(合計行だけ)

B2~B4へ0.4を1つずつ、A2~A4へR04-01・R04-02・R04-03を入力します。C2へ=B2、D2へ=ROUND(B2,0)を入れ、C4・D4までコピーします。B列は小数点以下2桁、C~F列は0桁の数値表示にします。元の値は上書きしません。

5行目を合計行にし、次の式を入力します。C5のSUMは、画面上に表示された数字を読み取って足す式ではありません。C列に格納されている計算結果を合計します。

B5 =SUM(B2:B4)
C5 =SUM(C2:C4)
D5 =SUM(D2:D4)
E5 =ROUND(SUM(B2:B4),0)
F5 =D5-E5

上の先頭のB5などは入力先です。セルには右側の=で始まる式だけを入れてください。

ケースA:0.4が3件ある場合

算術上は0.4×3=1.2です。実測ではC5の保存値は1.2000000000000002で、以下では約1.2と表記します。各明細をROUNDで整数へ丸めると0が3件になり、その合計D5は0。元の合計を丸めたE5は1でした。

確認対象格納・計算される値設定した表示での見た目
C2~C4(表示だけ)各0.4各0
C5(表示列の合計)約1.21
D5(明細ROUNDの合計)00
E5(合計後ROUND)11
F5(D5-E5)−1−1

C列の明細の見た目を手計算すると0+0+0=0ですが、C5のSUMは表示前の値0.4+0.4+0.4を計算し、0桁表示では1になりました。C2の数式バーには参照式=B2が表示されます。値を目で確認する場合は、作業用コピーでC列の小数点以下の表示桁数を増やし、元のB列と見比べてください。

C5の保存値には、算術上の1.2に対してごく小さな差がありました。Excelの小数計算では2進数での表現に伴う差が生じることがあり、Microsoftも浮動小数点演算の説明を公開しています。この保存値の差と、明細か合計かというROUNDの位置で生じた整数1の差は、分けて扱います。

ケースB:0.5が3件ある場合

次にA7~A9へR05-01・R05-02・R05-03、B7~B9へ0.5を1つずつ入れます。C7へ=B7、D7へ=ROUND(B7,0)を入力し、9行目までコピーします。10行目には、5行目と同じ形で参照範囲を7~9行目にした合計式を入れます。

B10 =SUM(B7:B9)
C10 =SUM(C7:C9)
D10 =SUM(D7:D9)
E10 =ROUND(SUM(B7:B9),0)
F10 =D10-E10

実測ではC10の保存値は1.5、0桁表示は2、明細ROUNDの合計D10は3、合計後ROUNDのE10は2、差F10は+1でした。C列の明細の見た目は1+1+1=3ですが、SUMは表示前の0.5を合計します。丸める順序による差は、常に少ない側へ出るわけではありません。

確認対象値設定した表示での見た目
C7~C9(表示だけ)各0.5各1
C10(表示列の合計)1.52
D10(明細ROUNDの合計)33
E10(合計後ROUND)22
F10(D10-E10)11

差が出たときに確認する順番

1.同じ元値を足しているか確認する。対象期間、行範囲、単位、重複・欠落をそろえます。片方だけ行が抜けている場合は、丸めの問題とは分けて確認します。

2.表示と値を分けて確認する。小数点以下が見えないだけなのか、ROUNDの式や整数の直接入力で値を変えているのかを調べます。表示された整数しか受け取っていない場合、元の小数を推測で復元しません。

3.丸める単位と位置をそろえる。明細ごと、部署ごと、全体合計の後など、どの段階で値を確定する運用かを確認します。方法が違えば、同じ元データでも結果が異なり得ます。

4.採用した方法と差を残す。たとえば「元値の合計約1.2を整数へ丸めて1。明細を先に丸めた合計は0で、差は−1」と記録します。単に「Excelの誤差」として処理せず、比較した式が分かる形にします。

確認した運用対応
明細ごとの整数を確定値として合計する明細ROUNDの列を使い、その列を合計する。元の小数は別に残す。
元の精度で合計し、総額・総量を最後に丸める元値のSUMを参照して最後にROUNDする。途中の表示値から再計算しない。
どちらのルールか未確認どちらかへ勝手に合わせず、計算方法と差を示して確認する。

保存後の確認と、この例の対象外

作業用をxlsxで保存して開き直し、6件のID・元の値・参照範囲・表示形式を確認します。ケースAはD5=0・E5=1・F5=−1、ケースBはD10=3・E10=2・F10=1を照合します。C列は表示を0桁にしたまま、元の小数を参照しているかも確認してください。

この記事の例は正の小数とROUNDの0桁指定を対象にします。負数、切上げ・切捨て、税や契約ごとの端数規則、極小の浮動小数点残差への一般的な対処、15桁を超える値、文字列の数値、非表示行の集計は別の確認が必要です。

丸めの前に率の分母や集計単位が違っている場合は、部署別の率を分子・分母からまとめる方法で計算対象をそろえます。割合の表示を足すと99.9%などになる例は、複数回答アンケートの割合と分母の確認でも扱っています。

この記事を書いた人

メーカーで設計開発とマーケティング企画に携わってきた個人が運営しています。報告書の作成、Excelでの集計、会議の運営など、仕事の文章と数字の扱いで迷いやすい言葉の違いを、判断の手順と例文で整理しています。

Smart-HowToをフォローする
Excel・ツール・AI
Smart-HowToをフォローする
Smart HowTo
タイトルとURLをコピーしました