わえなび ワード&エクセル問題集

当サイトはWordとExcelの実力を鍛える最高水準の特訓問題集です。リンクを張るのは自由です。みんなで勉強しましょう!

【Excel】ROUNDの四捨五入の計算結果が合わないのはExcelのせいではない

ExcelのROUND関数を用いて四捨五入をしたときに、四捨五入の仕方によっては計算が合わないように見えることがあります。しかし、Excelで計算したものを電卓で計算しなおして「計算が合わない」などと文句を言うのは愚かというべきです。計算が合わないのは、四捨五入の性質として発生する誤差(小学校の算数のレベル)であって、Excelに限って起こる現象ではありません。電卓で計算しなおして正しくなることは絶対にありません。計算が合わない事象の99.999999%は、Excelを使う人間のほうに原因があります。

逆に、「合計が合わないのが正しい」という場合もあります。また、端数処理の方法は1通りではなく、そのルールはExcelが決めることではありません。

そこで、今回は、四捨五入の性質を踏まえて、Excelで端数処理をするときの注意点について出題します。

 

 

目次

問題文のあとに簡単な操作方法を解説していますが、静止画では、わかりにくいと思いますので、最後に動画を載せています。ぜひご覧ください。

1.計算が合わないのはExcelのせいではない

問題

ある飲食店ではキャンペーン価格として4つのメニューについて3%引きをしました。キャンペーン期間中の売上数量をもとに、売上合計金額を計算したところ、30395円となるはずなのに30380円となってしまいました。

B列の単価とD列の数量の入力、合計の数式はいずれも正しいです。

(1)金額について、キャンペーンを行う前に決めておくことは何か、述べなさい。
(2)C列の3%引きの計算にROUND関数は必要か、述べなさい。
(3)ROUND関数を用いる場合、桁数を1にした場合の注意点を述べなさい。
(4)C列の値引き金額を整数とする場合の注意点を述べなさい。
(5)チャーハン400円の3%引きは388円である。このように小数部分が無いことが明らかなメニューについては、ROUND関数を使わなくてもよいか。

f:id:waenavi:20191202195540j:plain

 

解説

(1)端数処理のルールを決めること

3%引き、つまり97%を掛ける計算はあきらかに小数部分が発生します。お金の計算では端数をどのように処理をするかが極めて重要です。

f:id:waenavi:20191202195828j:plain

 

1円未満であっても、掛け算をすると誤差が大きくなり、最終的に大きな差となります。とくに、掛け算や割り算、パーセンテージの計算では事前に端数が発生した場合の処理方法についてルールを考えておく必要があります。

f:id:waenavi:20191202195833j:plain

 

端数が発生した場合、法律で禁止されていない限り、会社や個人で処理の仕方を決めることができます。

f:id:waenavi:20191202195838j:plain

 

(2)四捨五入とは限らない

商品単位で端数処理をする場合、四捨五入、切り上げ、切り捨て等が考えられますが、どのように処理をするかはExcelが決めることではありません。四捨五入をすることに決定した場合に限り、ROUND関数が使えます。

f:id:waenavi:20191202195908j:plain

 

四捨五入ではないのに勝手にROUNDを使ってはいけません。

f:id:waenavi:20191202195911j:plain

 

(3)小数部分を隠してはいけない

ROUNDで四捨五入をする場合、通常は小数点以下を四捨五入するので、桁数は0です(参考:【Excel】ROUND関数の桁数は「小数点の位置」を0として考えると分かりやすい)。

f:id:waenavi:20191202200452j:plain

f:id:waenavi:20191202200455j:plain

 

ROUNDで桁数を0にすれば、金額が全て整数になり、合計金額が30395円となります。小数部分が適切に処理されていなかったことによって誤差が発生していたことが分かります。

f:id:waenavi:20191202200458j:plain

 

桁数を1にします。

f:id:waenavi:20191202200501j:plain

 

桁数を1にしても小数にはなりません。それは、表示形式がカンマ桁区切りになっているからです。

f:id:waenavi:20191202200504j:plain

 

小数点以下表示桁数を増やします。

f:id:waenavi:20191202200507j:plain

 

小数部分があるにもかかわらず、表示形式で表示させないのは間違いです。これはExcelのミスではなく表を作る人のミスです。桁数を1にして小数第1位まで求めた場合は、必ずその小数部分を表示します。

f:id:waenavi:20191202200510j:plain

 

この場合、掛け算した結果も小数第1位までの端数が発生しますから、小数第1位まで表示しなければなりません。

f:id:waenavi:20191202200514j:plain

 

その結果、合計金額も端数になります。

f:id:waenavi:20191202200517j:plain

f:id:waenavi:20191202200520j:plain

 

(4)端数処理の注意書きをしよう

ROUNDで桁数を0にします。

f:id:waenavi:20191202200712j:plain

 

整数の表示にします。

f:id:waenavi:20191202200715j:plain

 

ところで、1180円の97%は1144.6円であり、3%引きが1145円であるとする記載は間違いです。

f:id:waenavi:20191202200717j:plain

 

このように計算上間違っている記載をそのまま放置してはいけません。

f:id:waenavi:20191202200720j:plain

 

Excelの画面上では小数点以下を四捨五入したことが分かりますが、この表をプリントアウトした時には分からないので、「3%引き」という記述だけでは端数処理の内容が伝わりません。

f:id:waenavi:20191202200724j:plain

 

ROUND関数を用いて端数処理をした場合は、その内容を記述します。

f:id:waenavi:20191202200727j:plain

 

(5)四捨五入は全部に適用する

400円の97%は、388円であり小数部分はありません。したがって、ROUND関数があっても無くても同じです。

f:id:waenavi:20191202200830j:plain

 

しかし、他のメニューについて四捨五入の処理をしているのに、1つだけROUNDを使わないのは間違いです。四捨五入をする必要が無くても必ずROUND関数を入れます。

f:id:waenavi:20191202200834j:plain

 

また、次回のキャンペーンのときに再利用をすることができます。

f:id:waenavi:20191202200837j:plain

 

2.構成比率の端数処理

問題

3つの商品A、B、Cの売上数量を入力し、合計を求めた。売上構成比率を求めなさい。ただし、パーセンテージの表示形式で小数第1位までとなるように、ROUND関数で四捨五入しなさい。

f:id:waenavi:20191202200904j:plain

 

解説

売上構成比率は各商品の数量を合計で割ります。合計は絶対参照です(参考:構成比率は全体の何%?の前に、大きい数で割る感覚を鍛えなさい)。

f:id:waenavi:20191203001610j:plain

 

小数になります。

f:id:waenavi:20191203001613j:plain

 

パーセンテージにした時に小数第1位まで求めるということは、小数の段階では小数第3位まで求めるということです。

f:id:waenavi:20191203001616j:plain

 

ROUND関数で四捨五入をします。桁数は3です。

f:id:waenavi:20191203001620j:plain

 

このパーセンテージを合計すると100.1%となります。

f:id:waenavi:20191203001623j:plain

 

ところで、3.4と3.4をたすと、6.8となり四捨五入をすると7になりますが、それそれを四捨五入をしてから足すと3+3=6になります。

f:id:waenavi:20191203001626j:plain

 

足し算をしてから四捨五入をするのと、それぞれを四捨五入をしてから足すのでは答えが異なりますが、これは小数部分の影響によるものです。

f:id:waenavi:20191203001628j:plain

 

小数第3位まで求めると、小数第4位以降の数字が無くなります。

f:id:waenavi:20191203001631j:plain

 

0.1%未満の部分を合計すると0.1%を超えるため、100ではなく100.1%となります。

f:id:waenavi:20191203001634j:plain

 

このように構成比率を四捨五入した結果を合計すると100にならないことがあります。

f:id:waenavi:20191203001639j:plain

 

構成比率の合計が100にならないのは、四捨五入の性質上当たり前のことであって、Excelの計算は正しいです。

f:id:waenavi:20191203001643j:plain

 

構成比率の合計が合わないのは世界共通の常識なので、合計が100になっていないことの注意書きをするだけでよいです。

f:id:waenavi:20191203001646j:plain

 

もし、無理やり100%にする場合は割合の大きいもので調整します。0.1%を引きます。この場合も調整したことが明らかに分かるように注意書きをしなければなりません。 

f:id:waenavi:20191203001649j:plain

 

3.按分の端数処理

問題

3つの部署X、Y、Zの人数に応じて、合計金額が100万円になるように按分しなさい。また、ROUND関数を用いて整数値にしなさい。

f:id:waenavi:20191202200930j:plain

 

解説

全体の数量が分かっていて、ある一定の割合で按分をするには、全体の数量に構成比率を掛けます(参考:【Excel】按分の計算方法と按分比率を補正する計算式の練習問題)。

f:id:waenavi:20191203002415j:plain

 

まず、構成比率を求めます。小数になります。

f:id:waenavi:20191203002417j:plain

f:id:waenavi:20191203002420j:plain

 

これに100万円を掛けます。絶対参照です。

f:id:waenavi:20191203002423j:plain

 

構成比率が小数なので、金額も小数です。

f:id:waenavi:20191203002426j:plain

 

ROUND関数で四捨五入をします。桁数は0です。

f:id:waenavi:20191203002430j:plain

 

この金額を合計すると1円足りません。

f:id:waenavi:20191203002432j:plain

 

構成比率の合計に誤差が出るということは、それに全体の数量を掛けた按分の数量も合計が出るのは当然のことです。

f:id:waenavi:20191203002436j:plain

 

このように、按分した数量の合計が元の数量と一致しないのは、四捨五入の性質上当たり前のことであって、Excelの計算は正しいです。

f:id:waenavi:20191203002438j:plain

 

この1円をどうするかはExcelには決められないので、自分で決める必要があります。

f:id:waenavi:20191203002441j:plain

 

割合の多い部署で調整するのであれば1円を足します。ただし、調整した理由を記述しておく必要があります。

f:id:waenavi:20191203002444j:plain

 

4.端数調整のルールはExcelが決めることではない

問題

商品代金2,050円を7%値引きして請求金額を求めようとしている。値引金額を四捨五入するのと、請求金額を四捨五入するのは同じと言えるか。

f:id:waenavi:20191203002524j:plain

 

解説

値引金額は商品代金の7%です。

f:id:waenavi:20191203084537j:plain

 

小数になるのでROUNDで四捨五入をします。桁数は0です。

f:id:waenavi:20191203084539j:plain

 

144円となります。引き算をすると1906円となります。

f:id:waenavi:20191203084543j:plain

f:id:waenavi:20191203084545j:plain

 

今度はさきに請求金額を求めます。7%の値引きをするということは残りは93%です。

f:id:waenavi:20191203084547j:plain

 

小数になるのでROUNDで四捨五入をします。桁数は0です。

f:id:waenavi:20191203084550j:plain

 

1907円となります。引き算をすると値引金額は143円となります。

f:id:waenavi:20191203084552j:plain

f:id:waenavi:20191203084555j:plain

 

商品代金からみて値引金額は7%、請求金額は93%です。

f:id:waenavi:20191203084557j:plain

 

値引き額を144円にすると全体の7.02%にあたるため四捨五入をすると7%になります。しかし、143円にしても6.97%なので四捨五入をすると7%になってしまいます。

f:id:waenavi:20191203084600j:plain

 

このようにパーセンテージの計算で四捨五入をすると答えが1つに決まらないことがあります。

f:id:waenavi:20191203084603j:plain

 

これは四捨五入の性質上当然発生することであり、Excelの計算は正しいです。

f:id:waenavi:20191203084607j:plain

 

7%の四捨五入は144円、93%の四捨五入は1907円なので、合計2051円となり計算が合いません。

f:id:waenavi:20191203084609j:plain

 

両方四捨五入をするとさらに誤差は大きくなります。一方を四捨五入した時はもう一方を四捨五入してはいけません。

f:id:waenavi:20191203084612j:plain

 

いずれを四捨五入にするかについてはExcelが決めることではないので、事前にルールを決めておく必要があります。

f:id:waenavi:20191203084616j:plain

 

5.端数処理は1通りではない

問題

(1)各商品の単価をそれぞれの割引率で割引して、合計金額を求めなさい。また、カンマ桁区切りにしなさい。

f:id:waenavi:20191203084635j:plain

 

(2)計算が合わないので四捨五入をすることにした。ROUND関数を用いて修正しなさい。

f:id:waenavi:20191203084647j:plain

 

解説

割引率で割引をするときは、1から引いた比率を掛けます。さらに、数量を掛けます。2763.5円と3537.6円になります。

f:id:waenavi:20191203085253j:plain

 

合計すると6300.1円となります。カンマ桁区切りにします。2763円と3538円を足すと6301円のはずですが、6300円になります。

f:id:waenavi:20191203085256j:plain

f:id:waenavi:20191203085258j:plain

 

そこで、合計を四捨五入します。桁数を0にします。

f:id:waenavi:20191203085301j:plain

 

答えは変わりません。

f:id:waenavi:20191203085304j:plain

 

合計金額を四捨五入をしても計算が合わないことがあります。この場合は、注意書きをするなどの工夫が必要となります。

f:id:waenavi:20191203085306j:plain

 

別解その1

次に、足し算をする前の金額を四捨五入します。

f:id:waenavi:20191203085439j:plain

 

桁数を0にします。これで6301円になります。

f:id:waenavi:20191203085441j:plain

 

この場合も四捨五入によって、小数を整数にしているので注意書きが必要です。

f:id:waenavi:20191203085444j:plain

 

小数を足した後の合計を四捨五入するのと、四捨五入をしてから合計をするのは異なります。

f:id:waenavi:20191203085448j:plain

 

別解その2

単価に1-割引率を掛けます。単価が小数なのでこのような誤差が発生するのです。

f:id:waenavi:20191203090143j:plain

 

そこで、この単価を四捨五入します。桁数を0にします。

f:id:waenavi:20191203090146j:plain

f:id:waenavi:20191203090148j:plain

 

割引後の単価に数量を掛けます。

f:id:waenavi:20191203090150j:plain

 

単価が整数なので四捨五入をしなくても整数になります。その合計も整数になります。

f:id:waenavi:20191203090153j:plain

 

もともと6301円ですから、合計金額が変わっています。

f:id:waenavi:20191203090156j:plain

 

単価の小数部分を無くしてから、数量を掛けているので、その分誤差が大きくなります。これも正しい計算です。

f:id:waenavi:20191203090159j:plain

 

いっぱんに、商品1個単位で端数が発生する場合に、その端数を調整する方法には3通りあります。商品1個単位で調整する方法と、商品の種類ごとまたは分類ごとで調整する方法と、全体の合計で調整する方法です。

f:id:waenavi:20191203090203j:plain

 

いずれの方法で調整してもかまいませんが、調整方法はExcelが決めることではないので、事前にルールを決めておく必要があります。

f:id:waenavi:20191203090205j:plain

 

6.Excelの浮動小数点演算の誤差

102.03-100.18=184.9999999999999のように浮動小数点演算を原因とする微小な誤差が発生することがあります。これはExcelに限らずコンピュータ全般で発生する誤差です。その場合も「数式の検証」を用いて原因を見つけることは可能です。

 


解説は以上です。


7.動画版はこちら(無料)

この記事は、わえなび実力養成講座「ファンダメンタルExcel」Program 12-12、12-13、12-14 のYoutube動画を書き起こして、加筆修正したものです。

 

 


Copyright(C)2018-2019 waenavi, All rights reserved. [www.waenavi.com 定礎 平成三十年八月]