エクセルで複数文字を検索する方法を教えてください。

質問

お世話になります。

エクセルで、関数を使用して、複数文字を検索する方法を教えてください。

具体的には、エクセルシートが２つあり（sheet1の名前は「姓名」、sheet2の名前は「該当」とします）

sheet1(姓名)のA列に名前を入力して、sheet2(該当）のA列には、漢字６００個を１文字ずつ縦に
並べたとします。（漢字６００個は勿論全部違う漢字です）

そこで、sheet1(名前)のA列に入力されている名前に対して、B列に関数を入れて、
sheet2(該当)のA列に該当する漢字があった場合は「当たり」と表示して、該当する漢字が
無い場合は空欄にする関数を教えていただけないでしょうか。

EXCEL2003になります。

よろしくお願いいたします。

回答No.2

tsubuyuki · ベストアンサー

わかりづらいかもしれませんが・・

添付した図では（ハードコピーが面倒だったので）１シートにまとめちゃってますが、
Ａ列に名前が、Ｄ列に探す文字が、Ｂ列に結果が羅列されているものとして、
　　Ｂ１セル：=IF(SUMPRODUCT(COUNTIF(A1,"*"&$D$1:$D$5&"*"))>0,"あたり","")
で、以下必要分コピー（フィル）。
としてやると、「探す文字」が１文字以上含まれているものに関して「あたり」を表示します。

ただし、探す文字の範囲（この場合、$D$1:$D$5の範囲）に「空白が無いこと」が条件です。
６００くらいだとほぼ大丈夫と思いますが、件数が多いと少々レスポンスが鈍るかもしれません。

お礼

ありがとうございました！
シンプルで初心者には応用が利き易い関数でした。
=IF(SUMPRODUCT(COUNTIF(A1,"*"&該当!A$1:A$600&"*"))>0,"外字","")
で対応できました。
感謝いたします。

回答No.4

某HN クロメート(Chromate)（@CoalTar）

最初はkeithinさんと同じ方法を考えたのですが、空白処理までできなくなってしまった。
(名前の定義で対応可能ではあるけれど)
=IF(OR(ISNUMBER(MATCH(MID(A1,ROW($A$1:INDEX(A:A,LEN(A1))),1),該当!A:A,0))),"当たり","")
[Ctrl]+[Shft] ＋[Enter] で確定、配列数式。{ }で挟まれる

で、逆に考えてみた。ただし、「該当」シートのデータ範囲(A1:A5)に空白セルがないものとして
=IF(A1="","",IF(OR(ISNUMBER(FIND(該当!$A$1:$A$5,A1))),"当たり",""))
[Ctrl]+[Shft] ＋[Enter] で確定、配列数式。{ }で挟まれる

お礼

ありがとうございました。
関数初心者のため、関数の中身がよく理解できませんでした。。

回答No.3

KURUMITO

力技になりますが計算には負担がかからない方法です。
次の式をB1セルに入力して下方にドラッグコピーします。

=IF(A1="","",IF(IF(LEN(A1)>0,COUNTIF(該当!$A:$A,MID(A1,1,1)),0)+IF(LEN(A1)>1,COUNTIF(該当!$A:$A,MID(A1,2,1)),0)+IF(LEN(A1)>2,COUNTIF(該当!$A:$A,MID(A1,3,1)),0)+IF(LEN(A1)>3,COUNTIF(該当!$A:$A,MID(A1,4,1)),0)+IF(LEN(A1)>4,COUNTIF(該当!$A:$A,MID(A1,5,1)),0)+IF(LEN(A1)>5,COUNTIF(該当!$A:$A,MID(A1,6,1)),0)+IF(LEN(A1)>6,COUNTIF(該当!$A:$A,MID(A1,7,1)),0)+IF(LEN(A1)>7,COUNTIF(該当!$A:$A,MID(A1,8,1)),0)>0,"当たり",""))

名前が8文字まで対応できます。もっと増やすこともできますね。

お礼

ありがとうございました。
関数初心者のため、関数の中身がよく理解できませんでした。。

回答No.1

keithin

とりあえず手っ取り早いやり方で
Ｂ１に
=IF(OR(ISNUMBER(MATCH(MID(A1,ROW($A$1:$A$9),1),該当!A:A,0))),"あたり","")
と記入、必ずコントロールキーとシフトキーを押しながらＥｎｔｅｒで入力、以下コピー。

お礼

ありがとうございました。
関数初心者のため、関数の中身がよく理解できませんでした。。