Excel数式 偶捨奇入(銀行型まるめ)間違いを教えて下さい

A1セルの値（8.825）の少数点以下3桁を五捨五入し、小数点以下2桁（8.82）にしたいのですが・・・
どうしても8.83となってしまいます。

=IF(MOD(ABS(A1)*10^2,1)=0.5,EVEN(ABS(A1)*10^2-0.5)/10^2*SIGN(A1),ROUND(A1,2))

上記数式の間違いのご指摘、正しい数式をご教授願えませんでしょうか。

宜しくお願い致します。

質問者からの補足コメント

• 皆様からのご回答ならびにご指摘をうけまして、
  補足させていただきます。
  
  元の値につきましては負の数となる可能性も御座います。
  絶対値について考え、小数点以下3桁目が5の際に、
  最も近い偶数にまるめたいと考えております。
  
  説明不足で申し訳御座いません。

    補足日時：2016/02/24 14:36

No.8 ベストアンサー 回答者： CoalTar 回答日時： 2016/02/24 14:38

No.4です。

d-q-t-pさんの案より
=IF(MOD(A1*10^3,10)=5,ROUND(A1/2,2)*2,ROUND(A1,2))
でもよさそう。小数点誤差までは検証してないけど、
たぶん、d-q-t-pさんは小数点誤差も考えていられる。

>検証してみたところ、やはり一部で結果が奇数となってしまうようです・・・
その一部の希望とかなわない数値を示していただかないと改善できませ～ん

この回答へのお礼

再度ご回答いただきありがとうございます。

大変申し訳御座いません。
当方の勘違いで、CoalTar様の式で問題御座いませんでした。

本当にすみませんでした。

お礼日時：2016/02/24 15:10

No.7 回答者： shorun 回答日時： 2016/02/24 14:34

>どうしても8.83となってしまいます

質問の式は間違っていないとおもいます。A1セルの内容を確認してください。
A1セルの数値は、8.825と純粋に8.825が入力されていますか?
もしかして、A1セルの書式は(数値、少数3位)の設定で
見かけは8.825　でも、8.8256を入力している、または
A1=(セル1)*(セル2)等の計算式になっていると
見かけA1=8.825でも内容はA1=8.8256なので
MOD(ABS(A1)*10^2,1)=0.5　とならず　0.56などとなるために、ROUNDされて8.83になる。

この回答へのお礼

ご回答ありがとうございます。

元の値を式で求めていたので、直接数値を入力してみたところ
同じ結果になってしまうようです。

お礼日時：2016/02/24 15:09

No.6 回答者： d-q-t-p 回答日時： 2016/02/24 13:12

桁の指定が間違っているように思います。

「10^2」は「100」ですよ。
それにしても 今見返すと当時の数式の無駄が目に付きますね……

=IF(MOD(ROUND(A1*1000,2),10)=5,ROUND(A1/2,2)*2,ROUND(A1,2))

これでどうですか？

この回答へのお礼

ご回答ありがとうございます。

この式で試したところ正の数も負の数も問題なさそうです。

式も短くて理想的です。

有難う御座いました！

お礼日時：2016/02/24 15:08

No.5 回答者： mike_g 回答日時： 2016/02/24 12:14

[NO.2お礼]へのコメント、

》 A列の値がマイナス値の場合うまくいかないようです
負数の場合を全く考慮してないので、仰るとおりだと思います。
しかしながら、「うまくいかない」だけでは不愉快です。どのように「うまくいかない」かを明記するのが筋というものです。

それはソレとして、他の方の提示式を見るにつけ、私のは如何にも冗長に見えてお恥ずかしい。私の式は無視してください。

ところで、数値を丸めた結果の一般的な概念は、
切捨て→小さい方に
切上げ→大きい方に
ですよねぇ。

この概念を踏襲するなら、“負数”の場合は
切捨て→数値としては小さい方に→絶対値が大きい方に
切上げ→数値としては大きい方に→絶対値が小さい方に
でなければなりません。
つまり、
-2.4→小数点以下切捨て→-3.0
-2.6→小数点以下切上げ→-2.0
ということですが、貴方の理解もこれでよろしいのですね？
ひょっとして、「こんな筈じゃなかった！」ではないでしょうね？

この回答へのお礼

再度ご回答いただき有難う御座います。

A列が正の数の際→（0.165を0.16に）（0.175を0.18に）
A列が負の数の際→（-0.165を-0.16に）（-0.175を-0.18に）
というのが理想の形ということになります。

自分でも十分理解していないことに加え、説明不足により不愉快な思いをさせてしまい、申し訳御座いませんでした。

お礼日時：2016/02/24 14:32

No.4 回答者： CoalTar 回答日時： 2016/02/24 11:24

検証不足かもだけど

=IF(MOD(ABS(A1)*10^3,10)=5,ROUND(ROUND(A1*2,2)/4,2)*2,ROUND(A1,2))

この回答へのお礼

ご回答有難う御座います。

検証してみたところ、やはり一部で結果が奇数となってしまうようです・・・

お礼日時：2016/02/24 14:26

No.3 回答者： enunokokoro 回答日時： 2016/02/24 10:53

希望していることは、VBAのRound関数と同じA1にしたいのですよね。

http://ameblo.jp/kanjizaibosatsu/entry-111153258 …
https://support.microsoft.com/ja-jp/kb/418216

質問の数式は、こちら↓を参考にしたのですね。
http://excel-ubara.com/excel3/EXCEL011.html

=IF(MOD(ABS(A1)*10^2,1)=0.5,EVEN(ABS(A1)*10^2-0.5)/10^2*SIGN(A1),ROUND(A1,2))
この数式だと、小数点以下の桁数によっては誤差が多くなるみたいです。

少し探してみたところ、銀行型丸めをするときに小数点以下の丸めをする
場合なら、こちら↓のほうがよいかも。
http://www.geocities.jp/chiquilin_site/data/1103 …

=IF(MOD(ROUND(A1*10^(2+1),10),10)=5,EVEN(TRUNC(A1,2)*10^2)/10^2,ROUND(A1,2))

=IF(MOD(ROUND(A1*10^(2+1),10),10)=5,EVEN(ABS(A1)*10^2-0.5)/10^2*SIGN(A1),ROUND(A1,2))

データ数を多くして検証をしていませんが、参考にはなると思います。

この回答へのお礼

ご回答ありがとうございます。

一つ目の数式ですとA1の値を0.145にした際結果が0.16になってしまう
二つ目の数式ですとA1の値を0.365にした際結果が0.38になってしまう

等の問題が出てしまいました・・・

丁寧にお答えいただき、感謝いたします。

お礼日時：2016/02/24 14:22

No.2 回答者： mike_g 回答日時： 2016/02/23 18:52

A3セルの値(8.825)の少数点以下3桁(この数値をB1セルに入力)を五捨五入し、小数点以下2桁(8.82)にしたい、のであれば、添付図を参照しながら、…

セル B2 に次式を入力(冗長に過ぎるかも)してみてください。
=(IF(AND(MOD(ROUND(A2*10^B$1,3),10)=5,MOD(INT(ROUND(A2*10^B$1,3)/10),2)=0),ROUNDDOWN(ROUND(A2*10^B$1,3),-1),ROUND(ROUND(A2*10^B$1,3),-1)))/10^B$1

なお、
https://ja.wikipedia.org/wiki/%E7%AB%AF%E6%95%B0 …
のページの中ほどの「最近接偶数への丸め」の記事が参考になろうかと。
私自身は「JIS丸め」なる言葉を昔々耳にしたことがあります。

この回答へのお礼

大変丁寧にご回答いただき、ありがとうございます。

参照ページを拝見したところ、
「最近接偶数へのまとめ」というものだと思います。

ご教授いただいた数式で検証したところ、
A列の値がマイナス値の場合うまくいかないようです・・・

何か良い方法はありますでしょうか？

お礼日時：2016/02/24 09:35

No.1 回答者： angkor_h 回答日時： 2016/02/23 17:41

「五捨五入」とか「銀行型まるめ」とかはわかりませんが、

「偶捨奇入」の意味が、偶数ならば切り捨て、でなければ(奇数ならば)切り捨て、
ということであれば、
8.825⇒8.83はその結果だと思いますが…

この回答へのお礼

ご回答いただきありがとうございます。
説明が不十分で申し訳御座いませんでした。

少数点以下第二位が偶数の場合は五捨、
奇数の場合は五入をしたいということを申し上げたかったのです。

お礼日時：2016/02/24 08:39