四捨五入のなかまの関数☆
Excelの関数で、四捨五入、切り上げ、切り捨ての関数って結構たくさんあるんですけど、使うこともまあまああって、実際にお支払いするお金や受け取るお金って小数点以下ってないじゃないですか。でも割引率とかの関係で四捨五入が出たりするので、実際の仕事だとそういうのを調整する必要があるんですよね。
Excelは小数点以下の桁数を指定して表示しますが、実際には表示以下の数字を含めてとても正確に計算するので、非表示になっている小数点以下もちゃんと計算します。そうすると見た目にずれが出たり、計算が合わなくなる時があるんですよね。それがでないようにするのがこういった関数の目的です。
たくさんある四捨五入系の関数ですけど、グループに分けるとそんなに多くないしパターンも似てるので、覚えにくくはないです。この記事で一気に覚えてしまいましょう。
これらの関数を、切り捨て系、桁数指定系、実際の数値で指定系の3種類で紹介します。
切り捨て系
切り捨ての関数は2つあって、小数点以下を切り捨てる動作をする関数です。
この2つはプラスの数では同じ動作をするんですけど、マイナスの数だと動作が違うんです。
INT関数
INT関数は指定した数以下の整数を求めます。
ちょっと言い方がイジワルっぽいですね。
=INT(数値)
1.99999以下の整数はいくつでしょうか。
=INT(1.99999)
答えは1です。つまり小数点以下がなくなりますね。
では、-1.99999以下の整数はいくつでしょうか。
-1.99999よりも「小さい」整数です。
-1は-1.99999よりも「大きい」整数ですよね。
=INT(-1.99999)
答えは‐2です。マイナスになると思ってるイメージより1つ下になるので気を付けてください。
ぴったり‐1のINT関数の結果はその数字以下の整数なので、-1も含むので、-1になります。
TRUNC関数
INT関数はマイナスだとイメージが違うので、それに対してわかりやすいようにマイナスでも、単純に小数点以下をなくしちゃう関数がほしくて、それをするのがTRUNC関数です。
=TRUNC(数値,[桁数])
数値に小数点以下を切り捨てたい数を指定します。
[桁数]は指定しなくてもいいのですが、指定すると切り捨てた後の小数点以下の桁を指定できます。実はもう少し複雑なのでROUND関数のところで解説します。
これであれば1.99999の結果は1だし、－1.99999の結果は-1となるようにイメージが合うようになります。
桁数指定系
桁数指定で指定する関数は丸めるという意味のROUND関数で四捨五入です。それだけ覚えると、切り上げはROUNDUP関数、切り捨てはROUNDDOWN関数なので名称は覚えやすいです。
ROUND関数
四捨五入する桁数を数字で指定するのが、ROUND関数です。
=ROUND(数値,[桁数])
数値は四捨五入する数値、桁数は四捨五入後の小数点以下の桁を指定できます。が、この桁数がもう少し指定できることが多いので解説します。
小数点以下を四捨五入する場合、1.234を四捨五入して1.2とする場合、「小数点以下2桁で四捨五入する」と教えられました。指定する桁数はこの考え方ではなく、四捨五入後の小数点以下の桁数なので、1.2は小数点以下1桁なので、指定は1になります。
=ROUND(1.234,1)
つまり、小数点以下がなくなるようにする桁数は0です。
ここまでのポイントは、小数点以下の四捨五入の桁数の指定は、最終的に求めたい数字の小数点以下の桁数、ということです。
さて、小数点以下はそうですが、例えば請求書を作っていると、100円以下は端数だから値引としてサービスみたいなことがあるのです。この場合は切り捨てですが、今は四捨五入を勉強しているので四捨五入のROUND関数で説明しますけど、このとき、12,345円は12,300円になるのです。このときの桁数はどうなるんですかね。
0より大きい桁の四捨五入は、四捨五入した後の0の数で指定します。だから、12,345円を12,300円にしたいときは2です。でもただ2って指定すると小数点以下2桁の意味になるので、これをマイナスにして‐2とします。
=ROUND(12,345,-2)
まとめると、
- 小数点よりも小さい桁の四捨五入は、四捨五入後になってほしい小数点以下の桁数
- 小数点以下で四捨五入して整数にしたいときは0
- 0よりも大きい桁で四捨五入は、四捨五入後になってほしい数字の0の数をマイナスに
ということで覚えるとわかりやすいと思います。
ROUNDUP関数、ROUNDDOWN関数
ROUNDUP関数では切り上げ、ROUNDDOWN関数は切り捨てというだけで、ROUND関数と同じです。
マイナスの値ではTRUNC関数で、整数にマイナスが付く数を求めるのでわかりやすいですね。
あれ？っていうことはTRUNC関数とROUNDDWON関数は同じ指定方法で同じ結果になりますね。
TRUNCは桁数指定せずに使って、ROUNDDOWN関数を桁数指定で、って使い分けるとわかりやすいかもしれません。
実際の数値で指定系
ROUND関数を桁数で指定しました。これから紹介する関数は同じ四捨五入や切り捨て、切り上げなのですが、桁数で指定するのではなく、区切る数を直接指定します。どういうことか見ていきましょう。
MROUND関数
ROUND関数に対してMROUND関数があります。同じ四捨五入なのですが、四捨五入で区切る数を直接指定します。
=MROUND(数値,倍数)
数値は四捨五入する数値です。倍数は区切る数です。関数としては、指定した数値を、指定した区切る数で丸めるのです。実際に関数の指定と、結果を見るのがわかりやすいと思います。
=MROUND(1.234,0.1)
この結果は1.2です。
=MROUND(12345,100)
この結果は12300です。
つまり四捨五入をする桁数ではなく、実際の数値で指定できる魅力があります。
この区切る数は100とか0.1だけじゃなくて、50とか0.25とかでも指定できるのも特徴です。
時間でも区切れて、出勤時間を15分単位で丸めることもできるんですよ。
次の答えは8:45:00です。
=MROUND(“8:49:00″,"0:15:0")
さとうせんせいは四捨五入はROUNDを使ってたけど、MROUND関数を知ってからこっちしか使ってないって言ってました。やっぱりわかりやすいというのが理由のようです。
ただ、ROUND関数は万能じゃないです。マイナスの値を四捨五入するときの区切る数値はマイナスにしないといけないんです。逆にプラスの値を四捨五入するするときの区切る数字はマイナスにしてはいけないです。そうしないと、#NUM!ってエラーになります。エラーにならないためにはどっちも正かどっちも負の値にしなきゃいけないです。
=MROUND(-12345,-100)
四捨五入したい数字がプラスかマイナスかわからないときって使いにくいですよね。
CEILING.MATH関数・FLOOR.MATH関数
CEILING.MATH関数は切り上げする関数、FLOOR.MATH関数は切り捨てする関数です。
シーリングライトって天井のライトのことですよね。あのceilingで天井なので上にあげる切り上げですね。フロアって床のことですよね。あのfloorで床なので下に下げる切り捨てですね。
この2つはMROUND関数と同じ倍数の指定の方法です。
これはマイナスの値を扱うときだけ気にすればいいのですが、MROUND関数と違い、倍数は正の数と負の数で指定することができます。この2つの違には意味がります。
切り上げのCEILING.MATH関数で倍数が正の場合は0に近くなります。
つまり、=CEILING.MATH(-12345,100)では‐12300になります。INT関数と同じようにイメージと違います。
切り捨てのFLOOR.MATH関数で倍数が正の場合は0により遠くします。
つまり、=FLOOR.MATH(-12345,100)では‐12400になります。INT関数と同じようにイメージと違います。
イメージに合わせるならば、最後に-1をつけて、=CEILING.MATH(-12345,100,1)、=FLOOR.MATH(-12345,100,1)にするとイメージ通りになります。これをモードというらしいです。イメージ通りのモードにするかどうかってことなんでしょうね。
こういうことがあるので、マイナスの値を使うときには注意です。ROUND関数のほうを使った方が簡単ですね。
この関数はタイムカードを作るときに大活躍します。9時始業の会社で遅刻早退を30分単位で考えるときって9:01に出勤なら9:30出勤の扱いですよね。この時は9:00より後の30分ということで処理するので上になるCEILING.MATH関数です。退勤時間でこれを考えると、逆にFLOOR.MATH関数を使えます。
この2つの関数は後ろに「.MATH」がついてとっても長くなってるんですけど、これなくてCEILINGでもFLOORでも同じ動作をするんですね。モードがなければ。
実はCEILING関数のバージョンアップがCEILING.MATH関数なんです。大きな違いはモードが使えるようになったということです。あとは計算の精度が上がっているようです。FLOOR関数のも同様にFLOOR.MATH関数がバージョンアップになったものです。
FLOOR関数、CEILING関数は将来なくなることが予告されています。使えなくなった後のExcelでは四捨五入はしなくなります。逆にFLOOR.MATH関数、CEILING.MATH関数はExcel2013から登場した関数なので、それ以前のExcelでは使えません。もうその時代のExcelを使っているケースはないと思うので心配はないですが、そういうことがあるみたいです。
これからはFLOOR.MATH関数、CEILING.MATH関数を使っていけばいいですね。
実際に使うケース
四捨五入や切り捨て、切り上げ関数を実際に使うことになる例を紹介します。次の図を見てください。
ねじって1個単位で考えると1円とかなんですけど、それを40％引きで売るとなると、1個0.6円ですね。販売額は小数点以下で表示するのがかっこわるいので表示で0桁表示にしています。実際の販売額は0.6のままですが、四捨五入されて表示は1なんですね。だから、合計は0.6が5つなので3円になってるんです。
でもこれじゃ書類として計算が合ってないですよね。
だから、割り引いた後の数字をどう扱うかルールを作って、四捨五入するならROUND関数を使って小数点以下四捨五入するのです。そうすれば計算書の数字が合うのです。
セルE3の計算式は「=ROUND(C3*(1-D3),0)」です。セルC3の定価に1からD列の割引率を引いたものをかけて、それを小数点以下で四捨五入してます。
このように計算値を合わせるときに四捨五入や切り上げ、切り捨てを使います。
1行1行の結果で四捨五入するルールにしましたが、合計するまで四捨五入しないで合計で四捨五入するルールもありますし、切り捨て、切り上げにするルールもあります。
このルールは、その表の意味にもよりますし、その会社ごとのルールや規則によって決まる場合もあります。
ルールを決めていると、このルールだとこっちは合わせたいけどこっちが合わなくなるということがよくありますので、数学でどうしても合わせられないものは合わせられないので、それを整理してどうするのかを決めるのがルール決めです。
まとめ
今回は四捨五入や切り捨て、切り上げの関数を紹介しました。
マイナスの値を扱うこともあると思うので、詳しく説明しましたが、やっぱり理解しにくいですね。
もしマイナスの値を扱う場合はROUND系の関数にする、という選択をするのがいいのかもしれないですね。
さとうせんせいは、小数点以下でマイナスの値を使わないときはINT関数、小数点以下や0より大きい区切りにするときときは、区切りの数を直接入力できるMROUND関数、CEILNG関数も今のところ使えてるのでCEILING関数、FLOOR関数も同じ理由で使っています。
マイナスの値の時はROUND系だそうです。
これらの関数は、見た目とか計算の途中でのズレをなくせるんですが、使うと本当の数字が丸まってしまっちゃうってことも覚えておきましょう。なんでもかんでも使うのではなく、ちゃんと丸めたい理由のあるときだけ使いましょう。丸める理由がないときは、表示形式で四捨五入して、実際の値は丸まらないようにします。
データ分析の種類によっては全く四捨五入を考えないケースもあるので、そのような場合は重要ではないのですが、金額を部署ごとに案分するとかって計算をしなきゃいけないケースが突然あったりするので、そんなときには突然使うことになるので、INT関数と小数点以下で丸めるのROUND系の関数の使い方は、何も見なくてもできるようにしておきましょうね。