難解 条件に合えば〇を付ける 関数

質問

添付ご覧ください。
4グループ、その中に4項目あります。
1行ごとにみて、条件に合う場合、その行に〇を付けるという関数を作りたいのですが、できないので助けてください。

条件というのは
(1)各グループに必ず1つ以上A~Dのいずれかが含まれる。（空欄のみのグループ
があってはダメ）
(2)A~Dが１つずつ4グループに含まれる。（A、B、C、Cはダメで、A、B、C、Dとなれば〇。順番は不問）

条件を満たすのは、添付でいうと赤字でわかるようにした行です。
関数の方がありがたいですが、マクロでもいいです。

ちなみに私が試みたのは、
４グループのそれぞれで空欄がない、かつ、１行（A~P列）に４種以上の文字列がある場合は〇、という関数を作ったのですが、それでは、１グループに２種文字列があるときも〇になってしまいますのでダメでした。

回答No.10

Chiquilin · ベストアンサー

今更出すのもどうかと思いますが せっかく思いついたので……
=REPT("○",AND((LEN(SUBSTITUTE(MMULT((A2:P2>"")*(INT(COLUMN(D:S)/4)<>{1;2;3;4})*10^(MOD(COLUMN(D:S),4)),1^ROW($1:$16)),0,""))>2)*MMULT((A2:P2>"")*(INT(COLUMN(D:S)/4)={1;2;3;4}),1^ROW($1:$16))))
Ctrl + Shift + Enterで確定

久々に数式で楽しめました。ありがとうございます。

お礼

できました。すごく難しいですが、シンプルな数式にしていただいてありがとうございます。

回答No.9

bunjii

回答No.7で論理式のチェック範囲について誤りが有りました。
また、4つのセルに"A"、"B"、"C"、"D"の何れも含まれないグループ内のチェックが漏れていました。
下記数式で再度試してください。
=IF(AND((SUMPRODUCT(N((COUNTIF(E2:H2,S$2:S$5)+COUNTIF(I2:L2,S$2:S$5)+COUNTIF(M2:P2,S$2:S$5))>0))>2)*SUMPRODUCT(COUNTIF(A2:D2,S$2:S$5)),(SUMPRODUCT(N((COUNTIF(A2:D2,S$2:S$5)+COUNTIF(I2:L2,S$2:S$5)+COUNTIF(M2:P2,S$2:S$5))>0))>2)*SUMPRODUCT(COUNTIF(E2:H2,S$2:S$5)),(SUMPRODUCT(N((COUNTIF(A2:D2,S$2:S$5)+COUNTIF(E2:H2,S$2:S$5)+COUNTIF(M2:P2,S$2:S$5))>0))>2)*SUMPRODUCT(COUNTIF(I2:L2,S$2:S$5)),(SUMPRODUCT(N((COUNTIF(A2:D2,S$2:S$5)+COUNTIF(E2:H2,S$2:S$5)+COUNTIF(I2:L2,S$2:S$5))>0))>2)*SUMPRODUCT(COUNTIF(M2:P2,S$2:S$5)),SUMPRODUCT(N(COUNTIF(A2:P2,S$2:S$5)>0))>3),"○","")

結果的には回答No.6より複雑になっているかも知れません。

お礼

何度もありがとうございます。

回答No.8

bunjii

>私のやり方がおかしいかもしれませんが、正しい結果にならないです。
論理式の誤りが有るかも知れませんので、再チェックのためにデータの配置を提示してください。

当方はExcel 2013です。
貼付画像のようなデータで検証しました。

回答No.7

bunjii

回答No.6の数式改変です。
=IF(AND(SUMPRODUCT(N((COUNTIF(E2:H2,S$2:S$5)+COUNTIF(I2:L2,S$2:S$5)+COUNTIF(M2:P2,S$2:S$5))>0))>2,SUMPRODUCT(N((COUNTIF(A2:D2,S$2:S$5)+COUNTIF(I2:L2,S$2:S$5)+COUNTIF(M2:P2,S$2:S$5))>0))>2,SUMPRODUCT(N((COUNTIF(A2:D2,S$2:S$5)+COUNTIF(E2:H2,S$2:S$5)+COUNTIF(M2:P2,S$2:S$5))>0))>2,SUMPRODUCT(N((COUNTIF(A2:D2,S$2:S$5)+COUNTIF(E2:H2,S$2:S$5)+COUNTIF(M2:P2,S$2:S$5))>0))>2,SUMPRODUCT(N(COUNTIF(A2:P2,S$2:S$5)>0))>3),"○","")
前回の数式は論理式が複雑なのでシンプルな方法に変更してみました。
考え方としては4つのグループの内3つのグループに"A"、"B"、"C"、"D"の内3種類以上含まれるか否かをチェックします。
組み合わせは4種類です。
グループ2とグループ3とグループ4（グループ1を除く）
グループ1とグループ3とグループ4（グループ2を除く）
グループ1とグループ2とグループ4（グループ3を除く）
グループ1とグループ2とグループ3（グループ4を除く）
すべての組み合わせで3種以上の検出が有れば"○"とします。
他に全グループ一括でチェックし、4種類が含まれていることも論理式に含めます。

補足

私のやり方がおかしいかもしれませんが、正しい結果にならないです。

回答No.6

bunjii

回答No.2の訂正です。
回答No.3への補足に対応するには次の数式に変更する必要があります。
=IF(AND(SUM(N(MMULT((E2:H2=S$2:S$5)+(I2:L2=S$2:S$5)+(M2:P2=S$2:S$5),{1;1;1;1})>0))>2,SUM(N(MMULT((A2:D2=S$2:S$5)+(I2:L2=S$2:S$5)+(M2:P2=S$2:S$5),{1;1;1;1})>0))>2,SUM(N(MMULT((A2:D2=S$2:S$5)+(E2:H2=S$2:S$5)+(M2:P2=S$2:S$5),{1;1;1;1})>0))>2,SUM(N(MMULT((A2:D2=S$2:S$5)+(E2:H2=S$2:S$5)+(I2:L2=S$2:S$5),{1;1;1;1})>0))>2,(MMULT(N(A2:P2=S$2:S$5),{1;1;1;1;1;1;1;1;1;1;1;1;1;1;1;1})>0),SUM(MMULT(N(A2:D2=S$2:S$5),{1;1;1;1})),SUM(MMULT(N(E2:H2=S$2:S$5),{1;1;1;1})),SUM(MMULT(N(I2:L2=S$2:S$5),{1;1;1;1})),SUM(MMULT(N(M2:P2=S$2:S$5),{1;1;1;1}))),"○","")
検索値の"A"～"D"はS2:S5セルへ入力してあります。
もう少し簡略化できるかも知れませんが論理式を整理するには実証しながらデバッグしますので時間が掛かります。

回答No.5

mt2015

ANo.3です。

> ダメです。４グループにABCDがばらばらに入らないといけないです。

まぁ、そうですよね。
作業列を大量に使えばセル関数だけで出来ると思いますが面倒なのでマクロでやってみました。ABCDの全ての組み合わせ(24通り）を作って、Grがそのどれかに一致すれば○を表示しています。変数宣言もエラー処理も中途半端なサンプルですが、一応動きます。

Sub Sample()
　　Dim sData(23, 3)
　　nCount = 0
　　vData = Array("A", "B", "C", "D")

　　'全ての組み合わせを作成
　　For i = 0 To 3
　　　　For j = 0 To 3
　　　　　　For k = 0 To 3
　　　　　　　　For l = 0 To 3
　　　　　　　　　　nChk = Array(0, 0, 0, 0)
　　　　　　　　　　nChk(i) = 1
　　　　　　　　　　nChk(j) = 1
　　　　　　　　　　nChk(k) = 1
　　　　　　　　　　nChk(l) = 1
　　　　　　　　　　If WorksheetFunction.Sum(nChk) = 4 Then
　　　　　　　　　　　　sData(nCount, 0) = vData(i)
　　　　　　　　　　　　sData(nCount, 1) = vData(j)
　　　　　　　　　　　　sData(nCount, 2) = vData(k)
　　　　　　　　　　　　sData(nCount, 3) = vData(l)
　　　　　　　　　　　　nCount = nCount + 1
　　　　　　　　　　　　Exit For
　　　　　　　　　　End If
　　　　　　　　Next l
　　　　　　Next k
　　　　Next j
　　Next i

　　'全ての組み合わせをチェック
　　Columns("Q:Q").ClearContents
　　For m = 2 To 12 '2～12行目までを対象
　　　　For n = 0 To 23
　　　　　　nGR = 0
　　　　　　nGR = -1 * (WorksheetFunction.CountIf(Range("A" & m & ":D" & m), sData(n, 0)) <> 0)
　　　　　　nGR = nGR - (WorksheetFunction.CountIf(Range("E" & m & ":H" & m), sData(n, 1)) <> 0)
　　　　　　nGR = nGR - (WorksheetFunction.CountIf(Range("I" & m & ":L" & m), sData(n, 2)) <> 0)
　　　　　　nGR = nGR - (WorksheetFunction.CountIf(Range("M" & m & ":P" & m), sData(n, 3)) <> 0)
　　　　　　If nGR = 4 Then
　　　　　　　　Range("Q" & m) = "○"
　　　　　　　　Exit For
　　　　　　End If
　　　　Next n
　　Next m
End Sub

お礼

まだ試してませんが、やってみます。ありがとうございました。

回答No.4

OKWavex

＞４グループのそれぞれで空欄がない、かつ、１行（A~P列）に
＞４種以上の文字列がある場合は〇、という関数を作ったのですが、
＞それでは、１グループに２種文字列があるときも〇になってしまいます
＞のでダメでした。

条件とちがうのだからダメなのは当たり前でしょう
１行（A~P列）に４種以上の文字列がある場合ではなくA~Dが１つずつ4グループに含まれる場合に〇にすればいいだけでしょう

補足

それが分からないから質問したのです。どうすればいいですか？

回答No.3

mt2015

結構難しいですね。
補足願います。
以下のような場合は○ですか？
グループ1：A,B
グループ2：C
グループ3：D
グループ4：D

補足

ダメです。４グループにABCDがばらばらに入らないといけないです。
グループ1：A,B
グループ2：B
グループ3：C
グループ4：D
ならOKです。

回答No.2

bunjii

>４グループのそれぞれで空欄がない、かつ、１行（A~P列）に４種以上の文字列がある場合は〇、という関数を作ったのですが、それでは、１グループに２種文字列があるときも〇になってしまいますのでダメでした。
その考え方では(2)の条件のみです。
IF関数で条件式をAND関数とOR関数を使えば良いでしょう。
文字が含まれるか否かはCOUNTIF関数で良いと思います。
=IF(AND(OR(INDEX(COUNTIF(A2:D2,{"A";"B";"C";"D"}),0)),OR(INDEX(COUNTIF(E2:H2,{"A";"B";"C";"D"}),0)),OR(INDEX(COUNTIF(I2:L2,{"A";"B";"C";"D"}),0)),OR(INDEX(COUNTIF(M2:P2,{"A";"B";"C";"D"}),0)),INDEX(COUNTIF(A2:P2,{"A";"B";"C";"D"}),0)),"○","")

お礼

できました。Index関数がよく分からないですが、勉強してみます。ありがとうございました。

回答No.1

tsubu-yuki

> （略）という関数を作った
とのことですので、それをご提示ください。
その添削の方が話が早いかもしれませんよ。