【EXCEL】カンマ区切りの二つのデータを分解して検査する方法

初めまして。二つのカンマ区切りデータの片方の要素が、もう一方のデータに含まれているかを
関数で確認する方法に悩んでいます。
簡単な方法がありましたらご教示お願いします。

分解して足し算の和を比較する方法も考えましたがたまたま一致してしまう恐れがあるので
一つづつ比較したいと思っています。
セル内のデータ数についてはカンマ有り、無しの文字数比較で解決できるので
純粋にセルAの各要素がセルBの全て含まれているか確認したいと思っています。

また1セルで解決したいと思っています。

例）
【セルA】10,20,30,40,50
　　　↓
　　　↓セルAの10～50(カンマ区切り)の全ての要素がセルB(カンマ区切り)に含まれているかを検査
　　　↓
【セルB】30,50,10,40,20

【セルC】検査結果を表示

よろしくおねがいします。

No.7 回答者： CoalTar 回答日時： 2016/09/17 16:30

>【セルA】10,20,30,40,50

>　　　↓
>　　　↓セルAの10～50(カンマ区切り)の全ての要素がセルB(カンマ区切り)に含まれているかを検査
> 　　　↓
>【セルB】30,50,10,40,20
仮に【セルB】30,50,10,40,20,6 としてもOK(TRUE)で良いのですか？
それとも完全一致なのですか？
MATCH関数を使って、AはBにすべて含まれると考え
=COUNT(MATCH(MID(A1,FIND("☆",SUBSTITUTE(","&A1,",","☆",ROW(A$1:INDEX(A:A,1+LEN(A1)-LEN(SUBSTITUTE(A1,",","")))))),
FIND("☆",SUBSTITUTE(A1&",",",","☆",ROW(A$1:INDEX(A:A,1+LEN(A1)-LEN(SUBSTITUTE(A1,",",""))))))
-FIND("☆",SUBSTITUTE(","&A1,",","☆",ROW(A$1:INDEX(A:A,1+LEN(A1)-LEN(SUBSTITUTE(A1,",","")))))))*1,
MID(A2,FIND("☆",SUBSTITUTE(","&A2,",","☆",ROW(A$1:INDEX(A:A,1+LEN(A2)-LEN(SUBSTITUTE(A2,",","")))))),
FIND("☆",SUBSTITUTE(A2&",",",","☆",ROW(A$1:INDEX(A:A,1+LEN(A2)-LEN(SUBSTITUTE(A2,",",""))))))
-FIND("☆",SUBSTITUTE(","&A2,",","☆",ROW(A$1:INDEX(A:A,1+LEN(A2)-LEN(SUBSTITUTE(A2,",","")))))))*1,0))
=LEN(A1)-LEN(SUBSTITUTE(A1,",",""))+1
[Ctrl]+[Shift]+[Enter]配列数式、{}で囲まれる。

完全一致ならもう少し簡単?で、小さい順にして
=AND(SMALL(MID(A1,FIND("☆",SUBSTITUTE(","&A1,",","☆",ROW(A$1:INDEX(A:A,1+LEN(A1)-LEN(SUBSTITUTE(A1,",","")))))),
FIND("☆",SUBSTITUTE(A1&",",",","☆",ROW(A$1:INDEX(A:A,1+LEN(A1)-LEN(SUBSTITUTE(A1,",",""))))))
-FIND("☆",SUBSTITUTE(","&A1,",","☆",ROW(A$1:INDEX(A:A,1+LEN(A1)-LEN(SUBSTITUTE(A1,",","")))))))*1,ROW(A$1:INDEX(A:A,1+LEN(A1)-LEN(SUBSTITUTE(A1,",","")))))
=SMALL(MID(A2,FIND("☆",SUBSTITUTE(","&A2,",","☆",ROW(A$1:INDEX(A:A,1+LEN(A2)-LEN(SUBSTITUTE(A2,",","")))))),
FIND("☆",SUBSTITUTE(A2&",",",","☆",ROW(A$1:INDEX(A:A,1+LEN(A2)-LEN(SUBSTITUTE(A2,",",""))))))
-FIND("☆",SUBSTITUTE(","&A2,",","☆",ROW(A$1:INDEX(A:A,1+LEN(A2)-LEN(SUBSTITUTE(A2,",","")))))))*1,ROW(A$1:INDEX(A:A,1+LEN(A2)-LEN(SUBSTITUTE(A2,",",""))))))
[Ctrl]+[Shift]+[Enter]配列数式、{}で囲まれる。
おそらく2003以前ではネストレベル超えていると思われ。
桁数固定とか、カンマの数が固定とか何かしら制限があれば短くなるんだが、、、。
根本的に1つのセルですべて処理するのが間違いか。

ふ～ 疲れた

この回答へのお礼

丁寧にありがとうございました。
無事にかいけつできました

お礼日時：2016/09/17 18:00

No.6 回答者： nokonoko524 回答日時： 2016/09/17 00:31

こんばんは

前提として、Aの各数値の桁が固定ならば
=IF(ISERROR(AND(FIND(LEFT(A1,2),B1,1)>0,FIND(MID(A1,4,2),B1,1)>0,FIND(MID(A1,7,2),B1,1)>0,FIND(MID(A1,10,2),B1,1)>0,FIND(RIGHT(A1,2),B1,1)>0)),FALSE(),TRUE())
TRUEならOK

この回答へのお礼

ありがとうございました。
無事に解決できました。

お礼日時：2016/09/17 18:01

No.5 回答者： d-q-t-p 回答日時： 2016/09/16 20:11

> また1セルで解決したいと思っています。

そこにこだわる意味はあるんですか？

回答した後で「分からないから解説してくれ」とか「実はバージョンが古くて動かない」
とか そういう後出しの話には対応できかねます。

=SUBSTITUTE(NPV(9,FREQUENCY(FILTERXML("<a><b>"&SUBSTITUTE(A2,",","</b><b>")&"</b></a>","//b"),FILTERXML("<a><b>"&SUBSTITUTE(A1,",","</b><b>")&"</b></a>","//b"))),1,"")="0."

この回答へのお礼

ありがとうございました。
無事に解決できました。

お礼日時：2016/09/17 18:01

No.4 回答者： jcctaira 回答日時： 2016/09/16 18:15

EXCELの関数では難しいかと思います。

やはりマクロ（ユーザ関数）を作成した方がすっきりと解決すると思います。
以下のマクロを設定し、関数を使用してみてください。

【セルC】=包含チェック(【セルA】,【セルB】)
注）カンマ区切りの内容は、全て数字として判断してチェックしています。


--------- 以下マクロコード -----
Function 包含チェック(チェックセル As String, 全値セル As String) As Boolean
　Dim I　　　　　　　As Long
　Dim J　　　　　　　As Long
　Dim チェック分割　 As Variant
　Dim 全値分割　　　 As Variant
　チェック分割 = Split(チェックセル, ",")
　全値分割 = Split(全値セル, ",")
　
　For I = 0 To UBound(チェック分割)
　　包含チェック = False
　　For J = 0 To UBound(全値分割)
　　　If Val(チェック分割(I)) = Val(全値分割(J)) Then
　　　　包含チェック = True
　　　　Exit For
　　　End If
　　Next J
　　If 包含チェック = False Then Exit Function
　Next I
End Function

この回答へのお礼

ありがとうございました。
無事に解決できました。

お礼日時：2016/09/17 18:02

No.3 回答者： CoalTar 回答日時： 2016/09/16 16:55

文章からCSVデータの比較かと思ったよ。

Comma Separated Value

4.0マクロの数式案(結局のところマクロですが)
A1セルに「10,20,30,40,50」と入力してある
A2セルに「30,50,10,40,20」と入力してある
としてA3セルを選択して[Ctrl]+[F3]名前の定義
名前 比較
参照範囲
=AND(EVALUATE(SUBSTITUTE(A1,",","+"))
=EVALUATE(SUBSTITUTE(A2,",","+")),
EVALUATE(SUBSTITUTE(A1,",","*"))
=EVALUATE(SUBSTITUTE(A2,",","*")))
A3セルに =比較

つまり、足し算だけでなく掛け算も比較
(数学で勉強した気もするけど。。。)

この回答へのお礼

ありがとうございます。
ためしてみます！

お礼日時：2016/09/16 17:01

No.2 回答者： zircon3 回答日時： 2016/09/16 16:16

No.1です。

　どこかでカンマ区切りで生成された2つのデータを2つのセルに入れるしかできないという事でしたら。。。

　セルBの文字列の先頭から最後までカンマ区切りで文字列データ（質問だと各2文字）を1つずつ抽出し、抽出した1つの文字列データがセルCに無いか文字列比較する、、、ということでしょう。
　セルBまたはセルCに値が入った際に実行されるのか、シート状に実行ボタンを設けるのか全体仕様が分からないので何とも言えませんが、マクロで実現するのが汎用性があるように思います。

参考まで。

この回答へのお礼

ありがとうございます。

検査のタイミングはセルに値が入ったときになります。

マクロを書くなどするのであれば配列、ループで検
査が可能だと思いますが、関数だけとなるとなかなか苦戦してしまいまして困ってる所です…

お礼日時：2016/09/16 16:24

No.1 回答者： zircon3 回答日時： 2016/09/16 15:34

元データがどういう形なのかわかりませんが、例えば書かれているデータがCSVファイルになっていれば、Excelで読み込むと2行5列となりますからセル間の比較で処理できます。

文字列処理でなくなるので楽です。

　ピンボケ投稿でしたらすみません。(_ _;;

この回答へのお礼

回答ありがとうございます。
残念ながらセルA、B共に既に上記のようにカンマ区切りで入ってまして、分割等せずそのまま扱う方法をもさくしております。
いい方法がございましたらよろしくお願いします。

お礼日時：2016/09/16 15:39