Excel 検索して返した値を条件で印を付けたい

質問

以前、「Excelで同じ商品IDが登録されたデータの中から一致する値を返したい。」という質問を投稿しました。
htt◯s://okwave.jp/qa/q10160134.html
↑◯をpに変換して下さい。
そこから派生なのですが、重複するIDが登録されたデータの中から一致した値を条件に掛けて、検索して返した値>5%の場合◯印、検索して返した値>=10%の場合△印を付けるにはどうすればよいでしょうか？

Excelシートの内容構成は添付画像の通りです
IF関数にCOUNTIFS関数とSUMIFS関数をネストして、条件一致がゼロなら空欄、条件一致がカウント１以上（重複）の場合は”重複”と表示し、条件一致がカウント１ならSUMIFS関数で値を取り出すといった内容です。

以下の条件の場合、となりの列に印を入れたいです。
・H3:12 物質A、B、Cのうち、5%を超えている場合は"◯"
・I3:I12 H列の"◯"が無い場合、物質AA、物質BBのうち、10%以上の場合は"△"

条件書式で数式で色付けすることは出来るのですが、関数などで印を付けようとすると数値が空欄でも印が入ってしまいます。

詳しい方居ましたら、ご教授下さい。
よろしくお願いします。

回答No.3

kkkkkm · ベストアンサー

回答No.1の訂正です。

> カウント１以上（重複）の場合は”重複”と表示

というのを見落としてました。
物質A、B、Cや物質AA、物質BBに「重複」と表示されることがあるんですね。

数値以外は0と認識するように変更しました。
重複がある場合は、「重複」と表示されたセル以外で指定値を超えているかを判断します。
重複が無い場合は、それぞれの範囲のセルのいずれかが指定値を超えているかを判断します。

H3に
=IF(OR(IFERROR(VALUE(C3),0)>5,IFERROR(VALUE(D3),0)>5,IFERROR(VALUE(E3),0)>5),"○","")

I3に
=IF(AND(H3<>"○",OR(IFERROR(VALUE(F3),0)>10,IFERROR(VALUE(G3),0)>10)),"△","")
として試してみてください。

補足

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

=IF(COUNTIFS($K$3:$K$29,$A12,$N$3:$N$29,"A")=0,"",IF(COUNTIFS($K$3:$K$29,$A12,$N$3:$N$29,"A")>1,"重複",SUMIFS($O$3:$O$29,$K$3:$K$29,$A12,$N$3:$N$29,"A")))

のところを

=IF(IF(COUNTIFS($K$3:$K$29,$A12,$N$3:$N$29,"A")>1,"重複",SUMIFS($O$3:$O$29,$K$3:$K$29,$A12,$N$3:$N$29,"A"))

に変え、カウント０の場合空欄にするIFをなくしてカウント１を超えたら”重複”と表示するようにし、カウント０でも０と表示。

Excelの詳細設定で値０を非表示。
H列の印をつけるセルに

=IF(OR($C3>5,$D3>5,$E3>5),"◯",IF(OR($F3>=10,$G3>=10),"△",""))

としたら物質A~Cが5%を超えたら◯、超えていなかったら物質AA~BBが10%以上なら△と表示することができました。

この方法でもあっていますでしょうか。
いろいろ試していてこの方法に行き着きました。

回答No.7

kkkkkm

回答No.4の補足です。

ちなみに

nadeko666さんが数式を変更して空欄を0にするようにしたのは

回答No.2のheisukewadaさんの回答にある「空白だと不具合がある」という意見を気にしての事だとしたら、そのような事は無いと思
いますので空欄になる元の式のままでいいと思います。

気になるようでしたら「空白だと不具合がある」とはどのような事態なのか当人に聞いてみるといいですね(わたしも知りたいです)

半角スペースとかが入る余地は元の式ではないと思いますし、私には事態が思いつきません。

回答No.6

heisukewada

何度もすみません。
H3
=IF(OR(ISNUMBER(C3), ISNUMBER(D3), ISNUMBER(E3)), IF(OR(C3>5, D3>5, E3>5), "◯", ""), "")
I3
=IF(AND(OR(ISNUMBER(F3),ISNUMBER(G3)),H3<>"◯"),IF(OR(F3>=10,G3>=10),"△",""),"")

これで、どうでしょうか？

回答No.5

heisukewada

確認をしたときに
Sheet1を　results
Sheet2を　data
として、シート results　には、
{=IFERROR(INDEX(data!$A$3:$E$29,MATCH(results!$A3&$B3&"A",data!$A$3:$A$29&data!$C$3:$C$29&data!$D$3:$D$29,0),MATCH("含有率(%)",data!$A$2:$E$2,0)),"")}
を使いました。
”重複”を、使う必要もないかと思いましたので、
文字列と、数値を、比較することはできないので、”重複”が、A~Cに含まれた場合、含有率が、5よりも小さい数字であっても、全て”◯”になりませんか？
0　を、非表示にすることも、今度は、nullと、区別がつかなくなるので、いい方法だとは思えません。
スマートな方法は、思いつかないままですが、ご一考ください。

回答No.4

kkkkkm

> Excelの詳細設定で値０を非表示。

この設定をすると、その後(たとえばA1に何もない場合かどうか判断する場合)
=IF(A1="","何もなし","何かある")
とした時にA1が0の場合、見た目A1に何もない状態なのに「何かある」になってしまいますから、できたらさけたほうがいいと思います。

> =IF(OR($C3>5,$D3>5,$E3>5),"◯",IF(OR($F3>=10,$G3>=10),"△",""))

ひとつのセルで表示するのでしたらこの考え方でいいと思います。

ただ、「重複」があった場合、他の値が指定値以下でも○になりますから

=IF(OR(IFERROR(VALUE($C3),0)>5,IFERROR(VALUE($D3),0)>5,IFERROR(VALUE($E3),0)>5),"◯",IF(OR(IFERROR(VALUE($F3),0)>=10,IFERROR(VALUE($G3),0)>=10),"△",""))

のように数値以外は0として判断するようにしておいた方がいいと思います。

回答No.2

heisukewada

ORでくくると空白で処理が不具合を起こしてしまいます。
コテコテですが、H3に
=IFERROR(IF(OR(C3*1>5,D3*1>5,E3*1>5),"◯",""),"")
同様に、I３（アイさん）は、
=IFERROR(IF(AND(H3<>"◯",OR(F3*1>=10,G3*1>=10)),"△",""),"")

もっとスマートな方法があると思いますが、思いつかないので、とりあえず、これでなんとかなるかと思います。

補足

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

ORでくくると空白で処理が不具合を起こしてしまいます。
コテコテですが、H3に
=IFERROR(IF(OR(C3*1>5,D3*1>5,E3*1>5),"◯",""),"")
＞＞空白で不具合回避のためにセルに×1という計算後、＞５という条件にしているということでしょうか。

COUNTIFの内容を少し変え、カウント０なら空欄にするところを、カウント１を超える場合”重複”にし、カウント０でも０表示にして、Excelの設定で０を非表示、
H列に、
=IF(OR($C3>5,$D3>5,$E3>5),"◯",IF(OR($F3>=10,$G3>=10),"△",""))

にしたら印を付けることができました。

回答No.1

kkkkkm

> ・H3:12 物質A、B、Cのうち、5%を超えている場合は"◯"

H3に
=IF(OR(C3>5,D3>5,E3>5),"○","")
として下にコピー

> ・I3:I12 H列の"◯"が無い場合、物質AA、物質BBのうち、10%以上の場合は"△"

I3に
=IF(AND(H3<>"○",OR(F3>10,G3>10)),"△","")
として下にコピー

で試してみてください。

OKWAVE内でしたらURLはそのままで大丈夫ですよ。