[[20170603163449]]　『条件付き書式の表示が設定どうりにならない』（ftr）　ページの最後に飛ぶ

『条件付き書式の表示が設定どうりにならない』（ftr）

入力セルに数値を入力し、隣の対象セルに数式で基準値を入力したセルとの差の2倍の値を表示する。
その値が0.40より大きい場合はフォントを赤で表示する設定。

状態は以下のような表示
基準値は14.05
入力セルに14.25を入力で0.40のフォント色変更なしで表示でOK
入力セルに13.85を入力で0.40のフォント色赤で表示でNG
尚、0.40をより大きい場合はフォント色赤の表示でOKでした。
設定はセルの値が"次の値より大きい"を選択しています。

試しに14.05を14.00に置き換えてみたところ13.80と14.20の入力で0.40の表示でフォント色変更なしの表示でOKでした。
設定の問題なのでしょうか。
解決方法を教えてください。

＜使用 Excel：Excel2007、使用 OS：Windows10 ＞

演算誤差によるものでしょう。
　
http://qiita.com/Q11Q/items/84b380b20ef51eca929c
にある雑誌記事がよくまとまっていると思います。
このテーマの定番記事と言って良いでしょう。
参考にしてください。
　
# 以前こちらの掲示板で、リンクが消えた件を指摘したことがある、
# その後、Quitaでも記事になったもの。
# 【悲報】とは上手く言ったものですな。
　

（γ） 2017/06/03(土) 17:48

 まず「入力セル」とか「隣の対象セル」といった表現はやめましょう。
 A1とかB1とか、具体的なセル番地を書くと何か不都合なことでもあるんですか？

 ＞数式で基準値を入力したセルとの差の2倍の値を表示する。

 これはどんな数式ですか？
 それがわからないと答えようがないと思いますけど。

 確認だけ。
（笑） 2017/06/03(土) 18:43

 原因は、小数演算誤差と呼ばれるもの。

 全部　0.4　と思っているんでしょうが、
 計算結果の小数は、手入力した0.4と微妙に異なります。

 試しに各値を「0.5の50乗」で割ってみると

 (1) C2セル =ABS(B2-$A$2)*2
 (2) D2セル =C2/0.5^50

 <結果図>
  行  ___A___  ___B___  ____C____  _________D_________
   1  基準値            計算結果   0.5の50乗で割ってみる
   2   14.05    14.25        0.4   450,359,962,737,048　　小
   3            13.85        0.4   450,359,962,737,052　　大
   4           手入力        0.4   450,359,962,737,050　　中

 解決方法の一つは、計算結果を下2桁へ丸めてしまう。
 (注：計算結果が必ず下2桁になるのが分かっている場合です）

 C2セル =ROUND(ABS(B2-$A$2)*2,2)

（半平太） 2017/06/03(土) 19:51

質問者さんへ。
　
浮動小数点誤差の話をこの例で実感するには、こんな方法もあります。
　
A1: 14.05
B1: 13.85
C1: = (A1-B1)*2
としてください。
　
C1セルの書式を変更して小数点桁数を増やしてみてください。
（小数点桁数を増やす既定のアイコンをクリックすればよいでしょう）
　
すると
0.4000000000000020
となっていることがわかると思います。
このように2進数で扱うことによる誤差がつきものなのですね。
　
すでに適切な解決策が提示されていますが、
この際、まとまったものに目を通して置くのも有益かと思います。
　
（γ） 2017/06/03(土) 21:57

わかりづらい説明に回答いただきありがとうございました。
回答の内容をみて試してみました。
私の使用状況では、ROUND関数を使い桁数を丸めることで解決できました。
ありがとうございました。
（ftr） 2017/06/04(日) 08:48

コメント返信：

[ 一覧(最新更新順) ]

[[20170603163449]] 『条件付き書式の表示が設定どうりにならない』（ftr） ページの最後に飛ぶ

[[20170603163449]]　『条件付き書式の表示が設定どうりにならない』（ftr）　ページの最後に飛ぶ