お知恵を貸して頂きたいです。 エクセル関数

質問

資料を作っているのですが、どうしていいかわかりません。
ご存知の方がいらっしゃいましたら、アドバイス頂ければと思います。

【パターン1　=Good】
A類　　1　　○
A類　　2　　○
A類　　1
B類　　2
C類　　1
B類　　1
B類　　1
C類　　2

【パターン２　=NG】
A類　　1　　○
A類　　2　　○
A類　　1
B類　　2　　○
C類　　1
B類　　1
B類　　1
C類　　2

【パターン３　=NG】
A類　　1　　
A類　　2　　
A類　　1
B類　　2　　
C類　　1
B類　　1
B類　　1
C類　　2

【パターン４　=NG】
A類　　1　　
A類　　2　　
A類　　1　　○
B類　　2　　○
C類　　1
B類　　1
B類　　1
C類　　2

【パターン４　=NG】
A類　　1　　
A類　　2　　
A類　　1　　
B類　　2　　○
C類　　1
B類　　1
B類　　1
C類　　2

【パターン６　=NG】
A類　　1　　○
A類　　2　　○
A類　　1
B類　　2　
C類　　1　　○
B類　　1
B類　　1　
C類　　2　　○

パターン１の様に、
ある１つ類　且つ、１と２　それぞれに「○」が１つ以上ついている時のみ、OKなのですが、

パターン２の様に
異なる類にまで「○」がついていると、NGになります。
異なる類は１つ以上あればNGです。

パターン３の様に
どれにも全く「○」が付いていないのも、NGです。

パターン４の様に
１か２だけで複数の類に「○」
も、NGです。

パターン５の様に
ある種の１か２のどちらか１つだけ「○」も、NGです。

パターン６の様に
ある類の１と２に「○」
プラス
他のある類(←１種類以上)の１と２に「○」
も、NGです。

つまり、
パターン１(１つの類で１と２に必ず「○」がある)のみGOODで、

・「○」をもつ(類列の）類名が複数ある時
・１と２が揃わない時
は、NGになってしまうパターンなのですが…

これを関数で示す事は出来るのでしょうか？
（ひょっとすると、パターン全ての関数が必要無いかもしれません。）

宜しくお願い致します。

回答No.7

kagakusuki

表の行数が何行あるのか不明なため、行数が何行になっても指定範囲を変更するつ様の無い関数とします。

　今仮に、類が入力されているのがA列であり、1や2が入力されているのがB列であり、〇が入力されているのがC列であるものとします。

　質問者様が御使用になられるExcelのバージョンがExcel2007以降の場合には次の様な関数となります。

=IF(IF(COUNTIF($C:$C,"○"),AND(COUNTIFS($C:$C,"○",$A:$A,"<>"&INDEX($A:$A,MATCH("○",$C:$C,0)))=0,COUNTIFS($B:$B,1,$C:$C,"○",$A:$A,INDEX($A:$A,MATCH("○",$C:$C,0)))*COUNTIFS($B:$B,2,$C:$C,"○",$A:$A,INDEX($A:$A,MATCH("○",$C:$C,0)))),FALSE),"GOOD","NG")

　又、御使用になられるExcelのバージョンがExcel2007よりも前のバージョンである場合には次の様な関数となります。

=IF(IF(COUNTIF($C:$C,"○"),AND(SUMPRODUCT((INDEX($C:$C,MATCH("○",$C:$C,0)):INDEX($C:$C,MATCH("○"&CHAR(1),$C:$C))="○")*(INDEX($A:$A,MATCH("○",$C:$C,0)):INDEX($A:$A,MATCH("○"&CHAR(1),$C:$C))<>INDEX($A:$A,MATCH("○",$C:$C,0))))=0,SUMPRODUCT((INDEX($C:$C,MATCH("○",$C:$C,0)):INDEX($C:$C,MATCH("○"&CHAR(1),$C:$C))="○")*(INDEX($A:$A,MATCH("○",$C:$C,0)):INDEX($A:$A,MATCH("○"&CHAR(1),$C:$C))=INDEX($A:$A,MATCH("○",$C:$C,0)))*(INDEX($B:$B,MATCH("○",$C:$C,0)):INDEX($B:$B,MATCH("○"&CHAR(1),$C:$C))=1))*SUMPRODUCT((INDEX($C:$C,MATCH("○",$C:$C,0)):INDEX($C:$C,MATCH("○"&CHAR(1),$C:$C))="○")*(INDEX($A:$A,MATCH("○",$C:$C,0)):INDEX($A:$A,MATCH("○"&CHAR(1),$C:$C))=INDEX($A:$A,MATCH("○",$C:$C,0)))*(INDEX($B:$B,MATCH("○",$C:$C,0)):INDEX($B:$B,MATCH("○"&CHAR(1),$C:$C))=2))),FALSE),"GOOD","NG")

回答No.6

MarcoRossiItaly

No.2です。

＞難しい関数を教えて頂き感謝します。

確かに組み上がった数式は、長くて難しいですね。ただ一応、使っている関数は全て、使用頻度の高いものではあります。よかったら、それぞれの関数名をインターネットで検索してみてください。

＞……同類で、２に「○」が１つ
　　　同類で、１に「○」が２だとNGと出てしまう様です。

ごめんなさい、1つ「以上」という条件を見逃してしまいました。それでは、次式ならいかがでしょうか。

=if(countifs(b1:b8,1,c1:c8,"○")*countifs(b1:b8,2,c1:c8,"○")*((countif(c1:c8,"○")=countifs(a1:a8,"A類",c1:c8,"○"))+(countif(c1:c8,"○")=countifs(a1:a8,"B類",c1:c8,"○"))+(countif(c1:c8,"○")=countifs(a1:a8,"C類",c1:c8,"○"))),"Good","NG")　　　　　……Excel2007以後
あるいは
=if(sumproduct((b1:b8=1)*(c1:c8="○"))*sumproduct((b1:b8=2)*(c1:c8="○"))*((countif(c1:c8,"○")=sumproduct((a1:a8="A類")*(c1:c8="○")))+(countif(c1:c8,"○")=sumproduct((a1:a8="B類")*(c1:c8="○")))+(countif(c1:c8,"○")=sumproduct((a1:a8="C類")*(c1:c8="○")))),"Good","NG")　　　　　……Excel2003以前

今回の条件では、これ以上短い1本の数式にするのは難しそうです。もっと短いほうがいい場合は、上式の各部分を、作業列に分けて入力してください。

回答No.5

tom04

No.4です！
たびたびごめんなさい。

前回の数式では仮に1だけが3つ以上あっても「GOOD」と表示されてしまいます。

F2セルの数式を↓に変更してください。

=IF(AND(SUMPRODUCT((B1:B1000=1)*(C1:C1000="○")),SUMPRODUCT((B1:B1000=2)*(C1:C1000="○")),SUM(D:D)=SUMIF(C:C,"○",B:B)),"GOOD","NG")

これで何とかご希望に近い形にならないでしょうか？

検証せずに投稿してごめんなさいね。m(_ _)m

回答No.4

tom04

こんばんは！
一例です。

↓の画像のように作業用の列を設けます。
作業列D2セルに

=IF(C2="","",IF(A2=INDEX($A$2:A2,MIN(IF($C$2:C2<>"",ROW($A$1:A1)))),B2,""))

これは配列数式になってしまいますので、Shift＋Ctrl＋Enterで確定！
この画面からコピー＆ペーストする場合はD2セルに貼り付け後、数式バー内で一度クリック！
編集可能になりますので、Shift＋Ctrlキーを押しながらEnterキーで確定！
数式の前後に｛　｝マークが入り配列数式になります。
このD2セルをオートフィルでずぃ～～～！っと下へしっかりコピー！

最後に結果のF2セル（配列数式ではありません）に
=IF(AND(SUM(D:D)>2,SUM(D:D)=SUMIF(C:C,"○",B:B)),"GOOD","NG")
という数式を入れると画像のような感じになります。

こんな感じではどうでしょうか？
的外れならごめんなさいね。m(_ _)m

回答No.3

jacob-wk9

B類１など、同じ項目が複数回出てくるのでしょうか。
アンケートの集計とか、どういう案件なのかわかると、もう少しイメージしやすいのですが。
また、YESの事例ももう少し多いほうが、イメージしやすいです。

初期エクセル関数から追加されたものを勉強していませんので、単純関数で回りくどいやり方になりますことをご了承ください。
画像と合わせて要件あってるかご評価ください。
C１行～８行　「=IF(B1="○",1,0)　」（B１～B8）
B１０行～B１５行「=SUMIF($A$1:$A$8,A10,$C$1:$C$8)」　（A１０～A１５）
D１０行～D１５行「=IF(B10>0,1,0)」　（B１０～B１５）
D１６「=SUM(D10:D15)」
D１７「=IF(D16=2,0,1)」　２値化した評価点合計が２の場合のみOKにする準備
F１０、F１３、F15「=IF(AND(D10>0, D11>0),1,0)」　　（D１０とD１１、D１２とD１３、D１４とD１５を評価）
F17「=IF(SUM(F10:F15)=1,1,0)-D17」　（１および２がセットになったものがAかBかCのいずれか１類のみ存在し、
かつ余計なところに丸がついていない）

回答No.2

MarcoRossiItaly

=if((countif(c1:c8,"○")=2)*(sumif(c1:c8,"○",b1:b8)=3),if(index(a1:c8,match("○",c1:c8,0),1)=index(a1:c8,match("○",c1:c8,1),1),"Good","NG"),"NG")

お礼

難しい関数を教えて頂き感謝します。
ありがとうございます。

補足

有難うございます。
エクセルで操作してみたのですが、
同類、なおかつ、必ず１と２がそれぞれ１つ以上はGOODとなるのですが、
同類で、２に「○」が１つ
同類で、１に「○」が２だとNGと出てしまう様です。

回答No.1

Cupper-2

条件は

　A類…１と２で○が二つ
　B類…×
　C類…×

　A類…×
　B類…１と２で○が二つ
　C類…×

　A類…×
　B類…×
　C類…１と２で○が二つ

でGood。
他はNoGood。
ってことかい？

意外と条件を並べるだけで自身で解決できたりします。
難しい条件だな…と思ったら、まずは整理してみましょう。
整理している間に良いアイディアが浮かぶかも知れません。

まあ質問の例では条件の成立には３つのケースあることが分かります。
これで何とかなるんじゃない？
あとは、１つのパターンで条件を作って、それを３つ組み合わせたら解決すると思うんだ。

お礼

ありがとうございます。
関数がある事を知らなかったので、
今本を買ってきて色々作っている所で、
関数自体、全く意味がちんぷんかんぷんな状態での質問です。

条件は、
類はABCの３つだけではなく、
延々と出て来ますので、無数になります。
ABCも仮称です。