基準点から観測点を±0.015超えた場合に条件付け書式で誤差の列が赤色に表示されるように作成したい

Question

基準点から観測点を±0.015超えた場合に条件付け書式で誤差の列が赤色に表示されるように作成したのですが、同じ条件を引っ張ってきているにも関わらず基準点が2を超えたところから±0.015も含むような判定が出てしまいます。どうすれば改善出来ますでしょうか。

.

詳しい方、ご教授お願い致します。

<モデレーター注>

質問件名(タイトル)の一部を質問内容へ転記させて頂きました。

Answer 1

こんにちは。

手元の環境（ Windows11 と Excel2021 の組み合わせ）で確認してみましたが、結果は提示されている表の状態と全く一緒になりました。

図１は、その結果です。

「条件付き書式」のルールについては、質問文で提示されている図の通りに設定しています。

尚、「誤差」の列には下記のような数式を入れ、それを下方向（行方向）に必要なだけコピーしています。

・E8セルの数式

=D8-C8

・G8セルの数式

=F8-C8

・図１

.

こういった予想外の結果が出る場合、計算結果のごく僅かな誤差が原因の場合がよくあります。

図２は「誤差」の列の小数部の表示桁数を 16桁まで拡大した状態です。（「セルの書式設定」の「表示形式」タブで「数値」を選択し「小数点以下の桁数」を 16に設定。）

・図２

.

なぜ 16桁なのかというと、Excel では、計算結果として表示できる最大有効桁数は整数部と小数部を合わせて 15桁に制限されているので、今回の場合は小数部を 16桁で表示させることで計算結果の誤差の有無が分かるからです。

・Excel の仕様と制限

https://support.microsoft.com/ja-jp/office/excel-%E3%81%AE%E4%BB%95%E6%A7%98%E3%81%A8%E5%88%B6%E9%99%90-1672b34d-7043-467e-8e27-269d656771c3?ui=ja-jp&rs=ja-jp&ad=jp#ID0EBACAAA=Excel_2007

ただ、こういったことが出来るということは　Excel 内部では計算結果として 16桁の数値を保持しているとも考えられますし、誤差が生じる場合も、更に小さな誤差を何らかの方法で 16桁に丸めている可能性も考えられます。（あくまでも結果から考えうる推測に過ぎませんが・・・。）

前置きが長くなりましたが、図２を見ていただくと、実際には E列の 11行目から 15行目までの計算結果に僅かな誤差が生じているのがお分かりいただけるかと思います。

なぜ、こういった誤差が生じるのかは、ネットで「 Excel 計算誤差」といったワードで検索してみてください。多くの情報が得られるかと思います。

内部で誤差が生じているのに、図１のように表示（見た目）では誤差が無いように見えるのは、通常は 2つの数値の差分の計算結果が小数部の桁数の多い方に合わせて表示されるためかと思われます。

今回の場合で言えば、「基準点」と「観測点」の差分を表示する「誤差」の列の計算結果の小数部は 3桁になるはずですので、誤差の有無に関わらず、小数点以下 4桁目の数値を四捨五入して小数部 3桁にして表示しているため誤差が無いように見えるのではないかと思います。

ただし、図２のように内部的には小数部 16桁を保持していますので、あくまで見た目だけの四捨五入であって、丸め（四捨五入後に小数点以下 4桁目以降は削除）は行われていないようです。

なので、図１のように、E列の 13行目以降に表示されている数値が「条件付き書式」で 0.015 より大きい数値として認識され、赤文字で表示されてしまいます。

尚、11行目と 12行目の数値は誤差は有っても 0.015 より小さいか等しい数値（且つ -0.015 より大きいか等しい数値）に該当しますので、赤文字表示にはなりません。

以上が、図１、図２から読み取れる状況です。

こういった状況を改善したい場合、「誤差」列の数式を計算結果の誤差を無視できるような数式に変更すれば良いことになります。

以下は、いずれも、今回のように誤差がごく小さな数値である場合の対処方法例になります。

1. ROUND 関数を使う方法。
   E8セルと G8セルの数式を下記のように変更し、それぞれの数式を下方向（行方向）に必要なだけコピーします。
   ・数式１：E8セルに入れる数式です。
   =ROUND(ROUND(D8,3)-ROUND(C8,3),3)

＜数式１の動作概要＞
この数式では、左から 2つ目の ROUND 関数で、D8セルの値の小数点以下 4桁目の数値を四捨五入して小数点以下 3桁の数値に丸めた（ 4桁目以降は削除した）数値と、左から 3つ目の ROUND 関数で、C8セルの値の小数点以下 4桁目の数値を四捨五入して小数点以下 3桁の数値に丸めた（ 4桁目以降は削除した）数値との差を求めますが、この時点では計算誤差が生じる可能性が有りますので、その結果を左端の ROUND 関数で、小数点以下 4桁目の数値を四捨五入して小数点以下 3桁の数値に丸めた（ 4桁目以降は削除した）数値にして表示しています。

D8セルの値と C8セルの値についても ROUND 関数を使って丸めているのは、どちらかのセル（あるいは両方のセル）の値が何らかの数式の計算結果として表示されている場合を考えているためです。

※ ROUND 関数
https://support.microsoft.com/ja-jp/office/round-%E9%96%A2%E6%95%B0-c018c5d8-40fb-4053-90b1-b3e7f61a213c

・数式２：G8セルに入れる数式です。
=ROUND(ROUND(F8,3)-ROUND(C8,3),3)

＜数式２の動作概要＞
数式１と同様のため省略します。 2. TEXT 関数を使う方法。
E8セルと G8セルの数式を下記のように変更し、それぞれの数式を下方向（行方向）に必要なだけコピーします。
・数式３：E8セルに入れる数式です。
=TEXT(TEXT(D8,"0.000")-TEXT(C8,"0.000"),"0.000")*1

＜数式３の動作概要＞
この数式では、左から 2つ目の TEXT 関数で、D8セルの値を見た目通りの小数部 3桁の文字列に変更し、左から 3つ目の TEXT 関数で、C8セルの値を見た目通りの小数部 3桁の文字列に変更した後、その差を求めますが、この時点では計算誤差が生じる可能性が有りますので、その結果を左端の TEXT 関数で見た目通りの小数部 3桁の文字列に変更し、これを *1 とすることで数値に変換し表示しています。

D8セルの値と C8セルの値についても TEXT 関数を使っているのは、どちらかのセル（あるいは両方のセル）の値が何らかの数式の計算結果として表示されている場合を考えているためです。

尚、文字列同士の演算であっても、その文字列が数値と見なすことが出来る数字の並びであった場合には問題なく演算結果を求めることが出来ますので、このような数式の書き方でもエラーとはなりません。

※ TEXT 関数
https://support.microsoft.com/ja-jp/office/text-%E9%96%A2%E6%95%B0-20d5ac4d-7b94-49fd-bb38-93d29371225c

・数式４：G8セルに入れる数式です。
=TEXT(TEXT(F8,"0.000")-TEXT(C8,"0.000"),"0.000")*1

＜数式４の動作概要＞  

数式３と同様のため省略します。

どちらの数式を使った場合でも、結果は図３のようになります。

（図３では数式１と数式２を使っています。）

・図３

.

.

以上です。

Windows11 と Excel2021 の組み合わせで動作確認しています。

よろしければ、ご自身の環境でお試しになってみてください。

ご参考になれば幸いです。

＜文章の一部修正＞

意味合い的に少し変だったので、下記の文章を修正しました。（中段あたりに書いている文章です。）

・修正前

なので、図１のように、「誤差」列の 13行目以降に・・・

・修正後

なので、図１のように、E列の 13行目以降に・・・

失礼いたしました。

その他、長い文章のため読みやすさを改善したく、数か所に改行を追加しています。

Answer 2

こんにちは

私はアデイェミです、そして私はあなたの質問を喜んでお手伝いします。

Excelの条件付き書式機能に問題があるようです。観測点が基準点から ±0.015 以上ずれている場合、セルを赤で強調表示したいが、基準点が 2 を超えると条件が正しく機能しない。

この問題を解決するために試すことができるいくつかの手順を次に示します。

1. 数式を確認する: 条件付き書式ルールで使用している数式が正しいことを確認します。「=ABS(A1-B1)>0.015」のような数式を使用している場合は、正しいセルを参照していることを確認してください
2. 絶対参照を使用します。複数のセルにルールを適用する場合は、数式で絶対参照を使用する必要があります。たとえば、列 A の各セルをセル B1 の 1 つの参照点と比較する場合、数式は '=ABS($A 1-$B$1)>0.015' になります。

3.重複するルールを管理する:Excelは、各セルに1つの条件付き書式ルールのみを適用します。同じセルに影響を与える可能性のある他のルールがある場合は、それらを適切に管理する必要があります。これを行うには、Excel ツールバーの [条件付き書式] > [ルールの管理] に移動します

4. エラーを確認する: 数式でいずれかのセルでエラーが発生した場合は、条件付き書式ルールが正しく機能しない可能性があります。Excel のエラー チェック機能を使用して、ワークシートのエラーをチェックできます。

これがお役に立てば幸いです。

コミュニティに還元する。この返信で問題が解決したかどうかを示して、この問題を抱えている次の人を支援します。下の「はい」または「いいえ」をクリックします。

敬具 アデイェミ

この回答は自動翻訳されています。文法や表現の誤りが発生した場合はご容赦ください。

Answer 3

こんにちは。

手元の環境（ Windows11 と Excel2021 の組み合わせ）で確認してみましたが、結果は提示されている表の状態と全く一緒になりました。

図１は、その結果です。

「条件付き書式」のルールについては、質問文で提示されている図の通りに設定しています。

尚、「誤差」の列には下記のような数式を入れ、それを下方向（行方向）に必要なだけコピーしています。

・E8セルの数式

=D8-C8

・G8セルの数式

=F8-C8

・図１

.

こういった予想外の結果が出る場合、計算結果のごく僅かな誤差が原因の場合がよくあります。

図２は「誤差」の列の小数部の表示桁数を 16桁まで拡大した状態です。（「セルの書式設定」の「表示形式」タブで「数値」を選択し「小数点以下の桁数」を 16に設定。）

・図２

.

なぜ 16桁なのかというと、Excel では、計算結果として表示できる最大有効桁数は整数部と小数部を合わせて 15桁に制限されているので、今回の場合は小数部を 16桁で表示させることで計算結果の誤差の有無が分かるからです。

・Excel の仕様と制限

https://support.microsoft.com/ja-jp/office/excel-%E3%81%AE%E4%BB%95%E6%A7%98%E3%81%A8%E5%88%B6%E9%99%90-1672b34d-7043-467e-8e27-269d656771c3?ui=ja-jp&rs=ja-jp&ad=jp#ID0EBACAAA=Excel_2007

ただ、こういったことが出来るということは　Excel 内部では計算結果として 16桁の数値を保持しているとも考えられますし、誤差が生じる場合も、更に小さな誤差を何らかの方法で 16桁に丸めている可能性も考えられます。（あくまでも結果から考えうる推測に過ぎませんが・・・。）

前置きが長くなりましたが、図２を見ていただくと、実際には E列の 11行目から 15行目までの計算結果に僅かな誤差が生じているのがお分かりいただけるかと思います。

なぜ、こういった誤差が生じるのかは、ネットで「 Excel 計算誤差」といったワードで検索してみてください。多くの情報が得られるかと思います。

内部で誤差が生じているのに、図１のように表示（見た目）では誤差が無いように見えるのは、通常は 2つの数値の差分の計算結果が小数部の桁数の多い方に合わせて表示されるためかと思われます。

今回の場合で言えば、「基準点」と「観測点」の差分を表示する「誤差」の列の計算結果の小数部は 3桁になるはずですので、誤差の有無に関わらず、小数点以下 4桁目の数値を四捨五入して小数部 3桁にして表示しているため誤差が無いように見えるのではないかと思います。

ただし、図２のように内部的には小数部 16桁を保持していますので、あくまで見た目だけの四捨五入であって、丸め（四捨五入後に小数点以下 4桁目以降は削除）は行われていないようです。

なので、図１のように、E列の 13行目以降に表示されている数値が「条件付き書式」で 0.015 より大きい数値として認識され、赤文字で表示されてしまいます。

尚、11行目と 12行目の数値は誤差は有っても 0.015 より小さいか等しい数値（且つ -0.015 より大きいか等しい数値）に該当しますので、赤文字表示にはなりません。

以上が、図１、図２から読み取れる状況です。

こういった状況を改善したい場合、「誤差」列の数式を計算結果の誤差を無視できるような数式に変更すれば良いことになります。

以下は、いずれも、今回のように誤差がごく小さな数値である場合の対処方法例になります。

1. ROUND 関数を使う方法。
   E8セルと G8セルの数式を下記のように変更し、それぞれの数式を下方向（行方向）に必要なだけコピーします。
   ・数式１：E8セルに入れる数式です。
   =ROUND(ROUND(D8,3)-ROUND(C8,3),3)

＜数式１の動作概要＞
この数式では、左から 2つ目の ROUND 関数で、D8セルの値の小数点以下 4桁目の数値を四捨五入して小数点以下 3桁の数値に丸めた（ 4桁目以降は削除した）数値と、左から 3つ目の ROUND 関数で、C8セルの値の小数点以下 4桁目の数値を四捨五入して小数点以下 3桁の数値に丸めた（ 4桁目以降は削除した）数値との差を求めますが、この時点では計算誤差が生じる可能性が有りますので、その結果を左端の ROUND 関数で、小数点以下 4桁目の数値を四捨五入して小数点以下 3桁の数値に丸めた（ 4桁目以降は削除した）数値にして表示しています。

D8セルの値と C8セルの値についても ROUND 関数を使って丸めているのは、どちらかのセル（あるいは両方のセル）の値が何らかの数式の計算結果として表示されている場合を考えているためです。

※ ROUND 関数
https://support.microsoft.com/ja-jp/office/round-%E9%96%A2%E6%95%B0-c018c5d8-40fb-4053-90b1-b3e7f61a213c

・数式２：G8セルに入れる数式です。
=ROUND(ROUND(F8,3)-ROUND(C8,3),3)

＜数式２の動作概要＞
数式１と同様のため省略します。 2. TEXT 関数を使う方法。
E8セルと G8セルの数式を下記のように変更し、それぞれの数式を下方向（行方向）に必要なだけコピーします。
・数式３：E8セルに入れる数式です。
=TEXT(TEXT(D8,"0.000")-TEXT(C8,"0.000"),"0.000")*1

＜数式３の動作概要＞
この数式では、左から 2つ目の TEXT 関数で、D8セルの値を見た目通りの小数部 3桁の文字列に変更し、左から 3つ目の TEXT 関数で、C8セルの値を見た目通りの小数部 3桁の文字列に変更した後、その差を求めますが、この時点では計算誤差が生じる可能性が有りますので、その結果を左端の TEXT 関数で見た目通りの小数部 3桁の文字列に変更し、これを *1 とすることで数値に変換し表示しています。

D8セルの値と C8セルの値についても TEXT 関数を使っているのは、どちらかのセル（あるいは両方のセル）の値が何らかの数式の計算結果として表示されている場合を考えているためです。

尚、文字列同士の演算であっても、その文字列が数値と見なすことが出来る数字の並びであった場合には問題なく演算結果を求めることが出来ますので、このような数式の書き方でもエラーとはなりません。

※ TEXT 関数
https://support.microsoft.com/ja-jp/office/text-%E9%96%A2%E6%95%B0-20d5ac4d-7b94-49fd-bb38-93d29371225c

・数式４：G8セルに入れる数式です。
=TEXT(TEXT(F8,"0.000")-TEXT(C8,"0.000"),"0.000")*1

＜数式４の動作概要＞  

数式３と同様のため省略します。

どちらの数式を使った場合でも、結果は図３のようになります。

（図３では数式１と数式２を使っています。）

・図３

.

.

以上です。

Windows11 と Excel2021 の組み合わせで動作確認しています。

よろしければ、ご自身の環境でお試しになってみてください。

ご参考になれば幸いです。

＜文章の一部修正＞

意味合い的に少し変だったので、下記の文章を修正しました。（中段あたりに書いている文章です。）

・修正前

なので、図１のように、「誤差」列の 13行目以降に・・・

・修正後

なので、図１のように、E列の 13行目以降に・・・

失礼いたしました。

その他、長い文章のため読みやすさを改善したく、数か所に改行を追加しています。

試してみたところ、私の方でも欲しかった結果が表示されました。

Excelに計算誤差があるとは知らず、しかも難しい計算ならまだしも小学生でも出来るようなこんな単純な計算で、原因が全く分からなかったため、助かりました。

説明も分かりやすくかなり勉強になりました。

本当にありがとうございました。