合計が合わないのは表示形式で端数処理しているから/Excel

Excelアイキャッチ_合計合わない Excel

合計が合わない!?

例えばこちら、簡単な数字で合計を求めている。
今回はSUM関数を使っているが、四則演算で求めても結果は同じ。

どちらも合計が合っていない。

合計が合わない

実はこれ、見えない小数点以下の数字が存在している。

表示形式を標準に戻す

あらゆる表示形式の設定は「標準」にすることで元のデータに戻すことができる。
セルを選択し、[ホーム]タブ-数値グループの上にある書式のボックスから「標準」を選択。

表示形式ボタン

元の数値が表示された。

元の数値

表示形式を使い小数点以下を非表示にする

先ほどまでの数字は表示形式を使って整数となるように設定されていた。

セルを選択し、[ホーム]タブ-数値グループの「小数点以下の表示桁数を減らす」ボタンをクリック。
クリックごとに1桁ずつ減っていく。

桁数を減らすボタン

Excelでは、見えなくなる数字は自動的に四捨五入されるようになっている。
それぞれのセルで四捨五入され整数表示になるので、合計が合わないということが起こってしまう。

表示形式設定前後

計算式を使うときは、表示形式に関係なく元のデータで計算されるということを理解しておかなければならない。

つまり、正しい結果にするには表示形式ではなく、関数を使って元の数字そのものを四捨五入(または切り捨て、切り上げ)後の数字に変更しておく必要がある。

ROUND関数/四捨五入

=ROUND(数値,桁数)
数値:四捨五入したい数値や数式、セルを指定
桁数:どこまで表示させるのかを指定

「桁数」は下の赤く囲まれた数字で指定

端数処理の桁数とは

丸ごと覚える必要はなく、「0」にすると一の位、「1」にすると小数第一位と、この2つだけ覚えればOK
あとは小数第二位、第三位…となるたびに増え、逆に十の位、百の位と上がっていくとマイナス数字が増えていく。
0から-1,-2…と数えていけば問題ない。

問題
数値「72694.518」を桁数「1・0・-3・-5」で処理するとそれぞれいくつになるでしょう。
答え
=ROUND(72694.518,1)・・・72,694.5
=ROUND(72694.518,0)・・・72,695
=ROUND(72694.518,-3)・・・73,000
=ROUND(72694.518,-5)・・・100,000

今回のような場合は、上の2つのセルにROUND関数を使う。合計はその結果から計算されるので「2」となる。

ROUNDを使う

実際は計算式の結果に対して四捨五入を使うシーンが多い。

:【A1】から【A10】のセルの平均を四捨五入し整数で表示
=ROUND(AVERAGE(A1:A10),0)

前回紹介していなかったROUNDUP関数もここで紹介。

ROUNDUP関数/切り上げ

=ROUNDUP(数値,桁数)
数値:切り上げたい数値や数式、セルを指定
桁数:どこまで表示させるのかを指定

「桁数」は下の赤く囲まれた数字で指定

端数処理の桁数とは

丸ごと覚える必要はなく、「0」にすると一の位、「1」にすると小数第一位と、この2つだけ覚えればOK
あとは小数第二位、第三位…となるたびに増え、逆に十の位、百の位と上がっていくとマイナス数字が増えていく。
0から-1,-2…と数えていけば問題ない。

問題
数値「72694.518」を桁数「1・0・-1・-4」で処理するとそれぞれいくつになるでしょう。
答え
=ROUNDUP(72694.518,1)・・・72,694.6
=ROUNDUP(72694.518,0)・・・72,695
=ROUNDUP(72694.518,-1)・・・72,700
=ROUNDUP(72694.518,-4)・・・80,000

実際は計算式の結果に対して四捨五入を使うシーンが多い。

:【A1】の数値に15%の工賃を上乗せしたものを切り上げて千の位で表示させたい
=ROUNDUP(A1*1.15,-3)

「ROUNDDOWN・INT・TRUNC「切り捨て関数」比較/Excel」はこちらから。

タイトルとURLをコピーしました