業務中でハマったところなので備忘録を。
四捨五入、切捨て、切上げ、剰余演算を計算フィールドで表現する
今回の目的は四捨五入、切捨て、切上げ、剰余演算を計算フィールドで実現すること。
一見簡単そうに見えますが、他の記事でも嘆いているとおり、Dynamicsの計算フィールドはそれほど機能が多くありません。
関数自体も多くないのですが、さらに計算系の関数については皆無に等しく、出来るのは四則演算くらいです。
Excelのように、ROUNDUP、ROUNDDOWN、TRUNC、MODが使用できないので、なかなかもどかしい想いをします。
Microsoftさん、お願いしますよ。。。
工夫すれば計算フィールドで実現可能
じゃあ、計算フィールドで、切捨てや剰余が出来ないかといったらそうでもありません。
少し計算ロジックを工夫することで、四捨五入、切捨て、切上げ、剰余演算が可能なので、その方法を紹介していきたいと思います。
今回は、金額(通貨型の整数)と人数(整数)を入力し、一人当たりの分配金を四捨五入、切捨て、切上げのケース(いずれも整数)でそれぞれ作成します。
一人当たりの分配金が切捨てだった時の金額の余りも算出します。
例えば、
金額:¥1,000,000
人数:7(人)
だったときに、¥1,000,000 ÷ 7=¥142,857.143…になるので、
一人当たりの分配金(四捨五入)→¥142,857
一人当たりの分配金(切捨て)→¥142,857
一人当たりの分配金(切上げ)→¥142,858
になります。
切捨て時の余りの金額は 1,000,000 mod 7 = 1なので、¥1になります。
まず用意するのは、金額フィールドを小数点なしの通貨型で定義して、
人数フィールド、こちらは整数で作成。
なお、金額・人数ともにマイナスを考慮すると、切上げのロジックが変わってしまうため、今回は0以上とすることを前提とします。
あとは、計算フィールドを駆使して、四捨五入・切捨て・切上げ・剰余をそれぞれ実現していきます。
四捨五入
四捨五入はいたってシンプルです。
単純に割り算すればOKです。
一人当たり分配金(四捨五入) = 金額 / 人数
計算値は表示精度の1つ前の位を四捨五入にする仕様になっています。
(つまり、整数であれば小数点第一位を四捨五入)
切捨て
切捨ての場合は、ExcelでいうROUNDDOWNやTRUNCがありませんが、計算式をちょっとひと工夫すれば切捨ての算出ができます。
その計算式がこちらです。
一人当たり分配金(切捨て) = (金額 / 人数) – 0.5
※金額が¥0の場合は、そのまま¥0で算出する
ここでのポイントはやはり0.5の減算。
0.5を引くことによって、本来四捨五入で繰り上がる数を繰り下げになるよう調整をしているわけです。
ただし、¥0の場合に0.5を引いてしまうと、-0.5となり、四捨五入で-¥1となるので、ここだけ場合分けしています。
今回のケースは整数(精度0)で表示しているため、0.5を引いていますが、表示精度によってこの値を調整します。
小数第一位まで表示するのであれば、0.05を引くことになります。
切上げ
続いて、切上げのケースも切捨てと同じような考え方ですが、計算式はこのようになります。
一人当たり分配金(切上げ) = (金額 / 人数) + 0.4999
なぜ、0.5ではなく、0.4999で入力しているかというと、境界値である0.5で加算した場合、割り切れたときに繰り上げ処理が発生するからです。
例えば、金額¥1,000,000の人数が10人だった場合、一人当の分配金は割り切れるので、¥100,000になります。
ここで、0.5を足してしまうと、100,000.5に対して四捨五入をされてしまうので、意図しない繰り上げが行われてしまいます。
そのため、0.5を超えないギリギリの値を加算して、四捨五入で繰り上げないように調整しているわけです。
ちなみに、これ以上9を増やしすぎると、0.5とみなされてしまうため、ギリギリ0.5に達しない値が0.4999となります。
剰余演算
最後に金額の余りの計算。
割り算した時の余りですね。
MOD関数が使えれば一発なんですが、残念ながら使用できません。
ここは、切捨ての計算ができているのでそれを利用します。
切捨ての計算がいわゆる、割り算の商ですから、商から逆算して余りを求めます。
数式でいうと、A÷B=C 余り Dという式であれば、余りのDを求めるには、D=A-B*cということになります。
なので、余りの算出はその式を当てはめればいいわけです。
ということで、数式はこう。
余り = 金額 – (金額 / 一人当たり分配金(切捨て))
検証結果
まずは四捨五入で切捨てが起こるケース
次に、四捨五入で切上げが起こるケース
最後に金額が割り切れるケース
この通り、四捨五入、切上げ、切捨て、剰余ともにバッチリです!
コメント