エクセル 複数セルに対しての範囲内外かの一括判定

添付画像のG列に表示してある値 (これは条件付書式で色付けしています)に対し、
この G3～G9の値の中で、1個でも±60％以上、値が外れていた場合に、
表の欄外に、 一括して、この表の中の値は NGだというのがわかるようにしたいのです。
添付画像では I3セルにOK か NG と表記させる イメージです。

実際にはG列の項目は数百以上ある場合もあるので、
G列の値をいちいち全部見なくても、このI3のセルを見ればわかるというようにしたいです。

=IF(COUNTIFS(G5:G7,"<=-0.6",G5:G7,">=0.6"),"OK","NG")
セルの複数範囲での判定方法がわからなかったので countifsを使ってみましたが、
全てNGとなってしまいました。根本的に他の数式で出来るのであれば、
それでも構いません。

No.1 ベストアンサー 回答者： 銀鱗 回答日時： 2018/08/14 15:38

=IF(COUNTIFS(G5:G7,"<=-0.6",G5:G7,">=0.6"),"OK","NG")

　↓
=IF(COUNTIFS(G5:G7,"<0.6",G5:G7,">-0.6")=COUNT(G5:G7),"OK","NG")
としたら？

OKなセルの数を数えて、それが範囲のセルの数と同じなら”OK”、違えば”NG”でいいでしょ。

・・・
質問文に書かれた数式は、
　-0.6以下で、且つ、0.6以上のセルの数
を数えていますから。

そのまま利用するなら、
逆に
　-0.6より大きく、且つ、0.6未満のセルの数
を数えて、それが範囲のセルの数と等しければOK、違えばNGと判定させるように修正…ですよねえ。

この回答へのお礼

回答ありがとうございました。
修正案が中々思いつかずに、四苦八苦していました。
上記数式で、うまくいきましたので、大変助かりました。

お礼日時：2018/08/20 19:06

No.7 回答者： tom04 回答日時： 2018/08/20 21:23

何度もごめんなさい。

投稿後気づきました。
No.6の数式の「OK」と「NG」が逆でしたね。

=IF(COUNTIF(G3:G1000,">=0.6")+COUNTIF(G3:G1000,"<=-0.6"),"NG","OK")

に変更してください。

どうも失礼しました。m(_ _)m

この回答へのお礼

すみません。せっかくいろいろと追記していただいたのに、しばらく確認できていませんでした。
一旦 内容参考にさせて頂きます。
回答ありがとうございました。

お礼日時：2018/10/13 12:58

No.6 回答者： tom04 回答日時： 2018/08/20 20:12

No.2・5です。

もう一度確認してみました。
G列の数式は関係なかったようです。
SUMPRODUCT関数内のABS関数の範囲を1000行まで範囲指定したいたためのエラーだと思います。
（空白セルまたは数式によって空白に見えるセルをABSの範囲にしているためのエラーのような気がします。

素直に
=IF(COUNTIF(G3:G1000,">=0.6")+COUNTIF(G3:G1000,"<=-0.6"),"OK","NG")

としてみてはどうでしょうか？m(_ _)m

No.5 回答者： tom04 回答日時： 2018/08/17 16:30

No.２です。

＞任意の測定値の欄が空欄の時に判定欄が#VALUE!と返ってくるのが・・・

G列の数式がどのようになっているのかこちらでは判らないのですが、
G列にエラーセルが存在している場合があるのでしょうかね？

そうであればG列にエラーが出ないような数式にすれば対処できると思うのですが、
具体的な数式が判らないので、まずはこの程度で・・・m(_ _)m

この回答へのお礼

ちなみにG列のズレ量の数式は下記のようになっており、
これは、過去に別の人から引き継いだものとなるのですが、
測定値が、公差(上限/下限)に対してどれだけ外れているのかを
％で表示したものです。
=IF(E3="","",IF($C3<>0,IF($D3<>0,F3/(($C3-$D3)/2),F3/(($C3-$D3))),F3/(($C3-$D3))))

エラーが出たのは、
=IF(SUMPRODUCT((ABS(G3:G1000)>=0.6)*1),"NG","OK")
上記数式を入れたとき、
E3セルが空欄だった時に #VALUE! と表示されました。
E3セル以外(E4以下) に数値が入っていても、I3セルの判定は
E3セルのみの処置となるようです。

お礼日時：2018/08/20 18:46

No.4 回答者： mike_g 回答日時： 2018/08/14 16:21

式 =ABS(G3) を入力したセル H3 を下方にオートフィルしておけば、

I3: =IF(COUNTIF(H3:H9,">=1.6"),"NG","OK")
で如何かと。

この回答へのお礼

列を追加しなければならないこと以外では、他の方から回答頂いた数式となんら変わらず使用できました。
別のときに参考にさせて頂きます。
回答ありがとうございました。

お礼日時：2018/08/17 11:13

No.3 回答者： moon-and-star 回答日時： 2018/08/14 16:20

素直に･･･

=IF(AND(G3:G7>=-0.6,G3:G7<=0.6),"OK","NG")

この回答へのお礼

一番上のセルのみの範囲外では OK/NGを判定してくれましたが、それ以降の下のセルでは、範囲外の場合でも 判定が変わらない結果となりました。
複数セルの範囲に対しての判定だと、上記数式だと難しいのでしょうか。
今回は、他の方に教えて頂いた数式で対応できましたので、そちらで使用させていただきます。回答ありがとうございました。

お礼日時：2018/08/17 11:08

No.2 回答者： tom04 回答日時： 2018/08/14 16:11

こんにちは！

一つでも±60％以上のデータがあれば「NG」、なければ「OK」となれば良いのですよね？

=IF(SUMPRODUCT((ABS(G3:G1000)>=0.6)*1),"NG","OK")

ではどうでしょうか？m(_ _)m

この回答へのお礼

回答ありがとうございました。
自分は、関数の組み合わせでうまくいかなかったので、
上記数式は参考にさせて頂きます。

任意の測定値の欄が空欄の時に判定欄が#VALUE!と返ってくるのが
難点ですね。
=IF(E3="","",IF(SUMPRODUCT(～
という感じにして最初のセル(E3)が空欄の時は非表示にするくらいしか
対応ないでしょうか。

お礼日時：2018/08/17 12:26