エクセル 条件範囲データと特定のセルの部分一致

質問

こちらどなたかご存知の方教えていただけますでしょうか。

A列 (３文字） 　 　 　
1. ABC　　
2. DEF 　
3. AXE 　　　　　　　
4. MNE 　　　
5. BEH 　　　
6. LXE 　　　　　　　　

B列
1. AA
2. BB
3. CC
4. DD
5. EE
6. FF

C列
1. NXE/MME
2. LEC/CCE/BNE/ZEX
3. DEF/CCE/BBE
4. MME/ABC/EFB
5. EEA/BBC/EEA
6. XXA/DEF/AXE/XEA

D列
1. X
2. X
3. CC
4. DD
5. X
6. FF

A列、Ｂ列がともにデータベース、C列のスラッシュで区切られた３文字が毎回集計に必要な情報になります。このＣ列の各行のいずれかの３文字がA列のデータベースに一致したら同行のB列の文字をD列に表示させたいのです。

例えば、１行目C列のNXE/MMEはA列のデータとは一致しないのでD列にはX（該当なし）を表示、C列３行目の文字郡のなかにはDEFがあり、A列のデータの中にも含まれているのでD列には同行B列のCCを表示させる。

一般的な関数、D列にB列の情報が指定できなくても0, 1など数字で有り無しがわかればよかったのですが、countif関数でも部分一致はできずエラーとなってしまいました。vlookup, index matchでも部分一致ができないのですが、何か方法ありますでしょうか？

回答No.5

某HN クロメート(Chromate)（@CoalTar） · ベストアンサー

ありなしで良いの？
=IF(COUNT(MATCH(MID(C1,{1,5,9,13},3),A:A,0)),B1,"X")

=INDEX(B:B,-LOOKUP(1,-MATCH(MID(C1,{1,5,9,13},3),A:A,0)))
だと思っていたから 答えと合わなくて困惑してしまった

お礼

できればデータベースにあるB列の情報を返したかったのですが、OXでも関数がシンプルだったのでベストアンサーとさせていただきました。ありがとうございます！

回答No.7

tom04

こんばんは！
VBAでの一例です。

画面左下にある操作したいSheet見出し上で右クリック　→　コードの表示　→　VBE画面が出ますので
↓のコードをコピー＆ペーストしてマクロを実行してみてください。
（Alt＋F8キー　→　マクロ　→　マクロ実行です）

Sub test() 'この行から
Dim i, j As Long
For i = 1 To Cells(Rows.Count, 3).End(xlUp).Row
For j = 1 To Cells(Rows.Count, 1).End(xlUp).Row
If Cells(i, 3) Like "*" & Cells(j, 1) & "*" Then
Cells(i, 4) = Cells(i, 2)
End If
If Cells(i, 4) = "" Then
Cells(i, 4) = "該当なし"
End If
Next j
Next i
End Sub 'この行まで

こんな感じではどうでしょうか？m(_ _)m

お礼

回答いただき、ありがとうございました。今後の参考にさせていただきます。

回答No.6

kagakusuki

以下は、A列のデーターに重複するものが無いとして、Ｃ列の1つのセルに入力されている3文字ずつの組は、1つのセルに5組までしか入力されていない場合に関して対応する方法です。
　尚、各セル内の何れかの３文字の内、A列のデータベースに一致するものが複数存在する場合には、同行のB列の文字列を"/"で区切って表示させています。

　まず、D1セルに次の関数を入力して下さい。

=IF(AND($C1<>"",SUMPRODUCT(COUNTIF($C1,"*"&$A$1:INDEX($A:$A,MATCH("゛",$A:$A,-1))&"*")*1)=0),"×","")&REPLACE(IF(OR(MID($C1,1,3)="",COUNTIF($A:$A,MID($C1,1,3))=0),"","/"&VLOOKUP(MID($C1,1,3),$A:$B,2,FALSE))&IF(OR(MID($C1,5,3)="",COUNTIF($A:$A,MID($C1,5,3))=0),"","/"&VLOOKUP(MID($C1,5,3),$A:$B,2,FALSE))&IF(OR(MID($C1,9,3)="",COUNTIF($A:$A,MID($C1,9,3))=0),"","/"&VLOOKUP(MID($C1,9,3),$A:$B,2,FALSE))&IF(OR(MID($C1,13,3)="",COUNTIF($A:$A,MID($C1,13,3))=0),"","/"&VLOOKUP(MID($C1,13,3),$A:$B,2,FALSE))&IF(OR(MID($C1,17,3)="",COUNTIF($A:$A,MID($C1,17,3))=0),"","/"&VLOOKUP(MID($C1,17,3),$A:$B,2,FALSE)),1,1,)

　次に、D1セルをコピーして、D2以下に貼り付けて下さい。

お礼

回答いただき、ありがとうございました。今後の参考にさせていただきます。

回答No.4

mu2011

補足をお願いします。
・Ｄ列の期待値は、D3=BB、D4=AA、D6=BB or CCになりそうだ如何でしょうか。
・又、D6のように複数存在する場合はどうするのでしょうか。

回答No.3

imogasi

この程度になるとＶＢＡを使わないと、関数では出来ないか、式が複雑になりすぎるように予想する。
ＶＢＡの経験はあるのか。
もう少し関数でも出来るように基データを簡単に出来ないか考えるべきでは、と思う。特にＣ列データ。
Ｃ列データが１語ずつ縦に並んでおれば、普通の問題になりそう。
質問文も同行２語で該当有りのときの処理をどうするかがもれているのでは。
データ例の書き方も
Ａ－Ｄ列　Ｄ列は望む結果
ABC AA NXE/MME
DEF BB LEC/CCE/BNE/ZEX
AXE CC DEF/CCE/BBE BB
MNE DD MME/ABC/EFB AA
BEH EE EEA/BBC/EEA
LXE FF XXA/DEF/AXE/XEA CC
のように書き、Ｄ列の出し方を文章で説明するほうが判りやすいのでは。
実例内容も文字記号（ＮＸＥのような）のようなものでなく。実際の意味を連想させる例を挙げたほうが良い。
参考
Sub test02()
d = Range("C65536").End(xlUp).Row
'MsgBox d
k = 1
For i = 1 To d
y = Cells(i, "C")
x = Split(y, "/")
For j = 0 To UBound(x)
With Worksheets("Sheet1")
Set r = .Range("A:A").Find(x(j))
If r Is Nothing Then
MsgBox x(j) & "見つかりません"
Else
MsgBox r.Row
.Cells(i, "D") = .Cells(r.Row, "B")
End If
End With
Next j
Next i
End Sub
上記は
・該当無しのＸが入ってない、
・第６行のＤＥＦ，ＡＸＥの両方街頭あるが上書きしているので、ＣＣになっている
など不十分だが参考に。しかしＶＢＡの初心者には、直ちには難しいと思う。

お礼

ありがとうございます。似たようなマクロで試したのですが、フリーズしてしまったので関数で探していた次第です。参考にさせていただきます・

回答No.2

aokii

Ｄ列にだけ一つの式を入れると長くなるので、式が解りやすいように、Ｅ列からＬ列までをワークエリアにして、以下の方法でいかがでしょう。

Ｄ列=IF(SUM(E1:H1)=0,"X",B1)
Ｅ列=IF(ISNA(VLOOKUP(I1,$A$1:$B$6,1,FALSE)),0,1)
Ｆ列=Ｅ列からドラッグコピー
Ｇ列=Ｅ列からドラッグコピー
Ｈ列=Ｅ列からドラッグコピー
Ｉ列=MID($C1,1,3)
Ｊ列=Ｉ列からドラッグコピー
Ｋ列=Ｉ列からドラッグコピー
Ｌ列=Ｉ列からドラッグコピー

お礼

ありがとうございます。今後の参考にさせていただきます。

回答No.1

MASUKUBO

D1セルには次の式を入力して下方にオートフィルドラッグします。

=IF(IF(LEN(C1)>=3,COUNTIF(A:A,MID(C1,1,3)),0)+IF(LEN(C1)>=7,COUNTIF(A:A,MID(C1,5,3)),0)+IF(LEN(C1)>=11,COUNTIF(A:A,MID(C1,9,3)),0)+IF(LEN(C1)>=15,COUNTIF(A:A,MID(C1,12,3)),0)>0,B1,"")

または

=IF(IF(LEN(C1)>=3,COUNTIF(A:A,MID(C1,1,3)),0)+IF(LEN(C1)>=7,COUNTIF(A:A,MID(C1,5,3)),0)+IF(LEN(C1)>=11,COUNTIF(A:A,MID(C1,9,3)),0)+IF(LEN(C1)>=15,COUNTIF(A:A,MID(C1,12,3)),0)>0,B1,"X")

お礼

ありがとうございます。今後の参考にさせていただきます。