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

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

【Excel】ROUND関数と表示形式の違い、数式の検証で計算が合わない原因を見つける練習

Excelでは表示形式で小数点以下の桁数を調整できるため、端数が四捨五入されたものと勘違いして計算してしまうことがあります。表示形式を変えても四捨五入はされないので、それを別の計算に利用すると表示上の計算が合わなくなります。

ROUND関数を習得するにはまず、表示形式によって計算が合わなくなる現象を自分で再現して、ROUND関数を使ったときになぜ計算が合うのかを考えることが大事です(参考:Excelの表示形式で「3+3=7」になるのは誤差か?操作ミスか?それとも教育の問題か?)。

そこで、今回は、ROUND関数を使う理由と、表示形式との違い、数式の検証の使い方について出題します。

目次

1.表示形式で小数部分は消えない

問題

次の図で、123を100倍したら12345になった。この原因を述べなさい。

f:id:waenavi:20191129153510j:plain

 

解説

掛け算の式を入力していますが、整数を100倍すると下2桁は0になるはずです。これは間違いです。

f:id:waenavi:20191129154124j:plain

 

セルA1に123.45、セルB1に100と入力します。

f:id:waenavi:20191129154127j:plain

 

掛け算をすると12345になります。セル範囲A1:C1を選択して、カンマ桁区切りにすると123*100=12345であるかの表示になってしまいます。

f:id:waenavi:20191129154129j:plain

f:id:waenavi:20191129154133j:plain

 

セルA1をクリックすると、セルには123と表示されていますが、数式バーには123.45と表示されます。

f:id:waenavi:20191129154136j:plain

 

カンマ桁区切りにすると小数部分が四捨五入になりますが、小数点以下の部分が消えたわけではありません(参考:Excelの表示形式で「3+3=7」になるのは誤差か?操作ミスか?それとも教育の問題か?)。

f:id:waenavi:20191129154139j:plain

 

セルを参照した時に使われる値は、セルに表示されている値ではなく、セルに保存されている値です。つまり、数式バーに表示されている値です。

f:id:waenavi:20191129154142j:plain

 

2.ROUND関数と表示形式の違い

問題

次の図で、単価123円の商品100個の金額が12,345円と表示されている。この文書は正しいものと言えるか。また、これを修正しなさい。

f:id:waenavi:20191129153547j:plain

 

解説

さきほどの問題と同じように、金額には単価*個数の式が入力されていますが、単価が小数になっています。

f:id:waenavi:20191129154145j:plain

 

このExcelを作った人は数式バーで確認することができますが、数式バーがプリントアウトされるわけではないので、他の人には分かりません。

f:id:waenavi:20191129154149j:plain

 

このように表示上、計算が合わない文書を作ることは文書の信用性にかかわります。特にお金の計算の場合は絶対に許されません。これは、Excelの計算ミスではなく、使い方の問題です。

f:id:waenavi:20191129154153j:plain

 

単価が123.45円の場合は、小数点以下表示桁数を増やして小数の表示にしなければなりません。

f:id:waenavi:20191129154156j:plain

 

小数を用いた計算の場合、原則として小数部分を隠してはいけません。カンマ桁区切りにしたときは、小数点以下表示桁数を増やして表示します。

f:id:waenavi:20191129154204j:plain

 

また、整数の表示にする場合は、端数があることを分かっていながらそれを放置して、そのセルを参照する計算をしてはいけません。

f:id:waenavi:20191129154207j:plain

 

まず、表示形式を標準にします。

f:id:waenavi:20191129154211j:plain

 

四捨五入をする列を設けます。

f:id:waenavi:20191129154217j:plain

 

[fx]関数の挿入のボタンを押します(参考:Excel関数の使い方と入れ子の基本を習得するのに最初に特訓すべきことは何か)。

f:id:waenavi:20191129154220j:plain

 

ROUNDを探します。このとき、関数の分類を「数学/三角」にすると比較的はやく見つかります。

f:id:waenavi:20191129154546j:plain

 

数値に「A4」と入力すると123.45と表示されます。

f:id:waenavi:20191129154548j:plain

 

桁数に「0」と入力します。

f:id:waenavi:20191129154552j:plain

 

「0を指定すると、最も近い整数として四捨五入されます」と書いてあります。

f:id:waenavi:20191129154555j:plain

 

計算結果として123と表示されます。

f:id:waenavi:20191129154557j:plain

 

「=ROUND(A4,0)」と入力されます。

f:id:waenavi:20191129154600j:plain

 

小数点以下表示桁数を増やすと、123.0000になります。小数点以下が無くなっていることが分かります。

f:id:waenavi:20191129154604j:plain

 

このように、四捨五入によって中途半端な部分をなくして、0にすることを「丸める」ということがあります。

f:id:waenavi:20191129154608j:plain

 

ROUND関数(ラウンド)は、数値を四捨五入する関数で、引数は2個です。round numberは、丸めた数、およその数のことです。

  • =ROUND(数値,桁数)

f:id:waenavi:20191129154613j:plain

 

対象となる数値と桁数はカンマで区切られています。四捨五入をして整数値にしたいときは桁数を0にします。

  • =ROUND(A4,0)・・・セルA4の値を整数値にする(桁数=0)

f:id:waenavi:20191129154617j:plain

 

この四捨五入をしたものと数量を掛けます。

f:id:waenavi:20191129154619j:plain

 

12300になります。B4:D4を選択します。カンマ桁区切りにしても計算結果は変わりません。

f:id:waenavi:20191129154622j:plain

 

3.数式の検証

(1)数式の検証で誤りを見つける

問題

1111と表示されているセルA1と、5と入力されているセルB1を掛け算したところ5556となった。数式の検証の画面を用いて原因を見つけ、修正しなさい。

f:id:waenavi:20191129154710j:plain

 

解説

電卓で1111*5を計算すると5555です。

f:id:waenavi:20191129160358j:plain

 

数式タブの数式の検証のボタンをクリックします。

f:id:waenavi:20191129160400j:plain

 

検証の欄に数式が表示されています。

f:id:waenavi:20191129160415j:plain

 

検証のボタンを押します。セルA1が「1111.2」に変わりました。

f:id:waenavi:20191129160404j:plain

 

もう一度、検証のボタンを押します。セルB2が「5」に変わりました。

f:id:waenavi:20191129160407j:plain

 

もう一度、検証のボタンを押します。掛け算をした結果5556になったことが分かります。

f:id:waenavi:20191129160411j:plain

 

そこで、セルA1を確認すると、1111.2がカンマ桁区切りで1,111の表示になっていることが原因であることが確認できます。

f:id:waenavi:20191129160418j:plain

 

このように、数式は正しいのに答えが間違って表示されているように見える場合は、数式の検証の画面を用いて原因を探ることができます。

f:id:waenavi:20191129160422j:plain

 

原因が分かったらこのまま放置せず、必ず修正をします。

f:id:waenavi:20191129160425j:plain

 

セルA1の小数点以下の表示桁数を増やします。

f:id:waenavi:20191129160428j:plain

 

(2)ROUND関数を用いた結果を確認する

問題

セルA1の数値の小数部分を四捨五入することによって整数値にしてから掛け算しなさい。

f:id:waenavi:20191129154737j:plain

 

解説

カンマ桁区切りにするだけでは四捨五入はされません。

f:id:waenavi:20191129160644j:plain

 

ROUND関数を使わない限り、勝手に小数部分が消えることはありません。セルの表示上、四捨五入になっているように見えても、まったく四捨五入されていません。

f:id:waenavi:20191129160647j:plain

 

ROUNDの画面を表示します。

f:id:waenavi:20191129160650j:plain

 

数値はA1、桁数は0です。

f:id:waenavi:20191129160653j:plain

 

さらに、四捨五入をした値と、セルC1をかけます。

f:id:waenavi:20191129160658j:plain

 

数式タブの数式の検証のボタンをクリックします。

f:id:waenavi:20191129160701j:plain

 

検証のボタンを押します。セルB1が「1111」に変わりました。

f:id:waenavi:20191129160704j:plain

 

もう一度、検証のボタンを押します。セルC1が「5」に変わりました。

f:id:waenavi:20191129160707j:plain

 

もう一度、検証のボタンを押します。掛け算をした結果5555になったことが分かります。

f:id:waenavi:20191129160711j:plain

 

このように四捨五入をした整数値を用いて、その後の計算をする場合は必ずROUND関数を使います。

f:id:waenavi:20191129160714j:plain

 


解説は以上です。


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

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

 


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