エクセル ある文字列が含まれるセルを検索する方法

質問

初めて問い合わせします。大量のデータを検証しなければいけません。
関数などで方法を教えて頂ければ幸いです。
１セルに対し、１セルを検索する方法はわかっております（countifやifを使って）
でも、セル単位ではなく文字列単位での検索方法を知りたいと思います。
わかりづらいかもしれませんが、現状をお知らせします。

シート１「登録データ一覧」（５万件）
Ａ列１行目　ア社
Ａ列２行目　イ社
Ａ列３行目　ウ社
Ａ列４行目　エ社
Ａ列５行目　☆ーア社
Ａ列６行目　ウー☆社

シート２「検索したい社名一覧」（５００社）
Ａ列１行目　ア社
Ａ列２行目　イ社
Ａ列３行目　ウ社

＜出したい結果＞
(1)検索したい会社が、シート１上で何社登録があるか
ア社→【答え：２社】
イ社→【答え：１社】
ウ社→【答え：２社】

(2)登録データ一覧上、検索したい会社が存在するか
ア社→【答え：○】
イ社→【答え：○】
ウ社→【答え：○】
エ社→【答え：×】
☆ーア社→【答え：○】
ウー☆社→【答え：○】

よろしくお願いします。

回答No.3

mshr1962 · ベストアンサー

1)検索したい会社が、シート１上で何社登録があるか
=COUNTIF(範囲,"*ア*社*")
となれば上記の回答になります。

(2)登録データ一覧上、検索したい会社が存在するか
{=IF(SUM(COUNTIF(セル,{"*ア*社*","*イ*社*","*ウ*社*"})),"○","×")}
と配列数式にすれば結果は出せます。
※配列数式は{}無しの状態を入力して、Ctrl+Shift+Enterで設定できます。

つまり　"ア社"　を　"*ア*社*"　に変換できるようにすればOKなのですが
中間に*を追加できる関数がないのでユーザー定義する必要があります。

Function MOJIPLUS(MOJI)
Dim MOJIP(1 To 500)
For Each Mc In MOJI
Mx = Mx + 1
ML = Len(Mc)
For I = ML To 1 Step -1
MOJIP(Mx) = Mid(Mc, I, 1) & "*" & MOJIP(Mx)
Next I
MOJIP(Mx) = Replace("*" & MOJIP(Mx), "**", "*")
Next Mc
MOJIPLUS = MOJIP
End Function

上記で500セルまでの範囲のセルに対応できますがその分、メモリを食うので検索データ数の増減はご自身でご検討ください。

(1)=COUNTIF(範囲,MOJIPLUS(検索文字))
(2){=IF(SUM(COUNTIF(セル,MOJIPLUS(検索文字の範囲))),"○","×")}
で計算できるはずです。

お礼

ご回答ありがとうございました。
今回は、これだといくつか問題が発生してしまった為、counifに*を入れて検索し、目で調べる方法で処理しました。
しかし、このユーザー定義のおかげで、他の作業がとても楽になりました。ありがとうございました。

回答No.7

zap35

＃０６です
本当の目的はシート２に「エクセル」という文字列があり、シート１の会社名から「エクセル」という文字列を含む会社の数を検索したいということでしょうか。これなら関数で可能です。

B1の式は以下の通りです
=COUNTIF(Sheet1!A:A,"*"&A1&"*")
（COUNTIF関数はワイルドカードが使用できます）

質問では「ウ」と「社」の両方の文字列を含む会社の数を検索したいとおっしゃっているように読み取れました。もしシートに「エクセル社」と文字列があって、「エクセル」と「社」の両方を含む会社の数を検索しようとすると、これは実現が困難です。

それは「エクセル，社」「エク，セル社」「エ，クセル社」のように文字列のどこを区切りににして検索すれば良いのかが機械的に判定できないからです。

お礼

ご回答ありがとうございました。
説明の仕方があまりうまくなく、混乱させてしまったこと、お詫び致します。
わかりやすい説明をありがとうございました。
今後ともよろしくお願い致します。

回答No.6

zap35

補足要求です
＞ウ社→【答え：２社】
とのことですが、これはシート１の「ウ社」と「ウー☆社」が該当するのでしょうか？

すると具体的な例で書くと、シート１に「宇宙株式会社」「宇宙有限会社」「宇宙電気株式会社」「小宇宙社」がある場合は
　「宇社」→【答え：４社】
になるのですか？　何か変ですね。

また「小宇宙社」はシート２に「宇社」と「小社」があった場合どちらでカウントするのが正しいのでしょうか。

集計の条件をもう少し補足していただけませんか

補足

ご回答ありがとうございました。
確かにおっしゃるとおりの内容ではおかしいのですが、検索したい500社が正式社名となっており、zap35さんの例のような件数は少ないと考えています。

たとえば「エクセル」という会社の検索をしたい場合、「エクセル」はヒットしても「エクセル　経理部」がヒットしない方が困る件が多数出た為、今回の問い合わせとなりました。
※「エクセルインターナショナル」がヒットしてしまってもいたしかたない状況と考えています。

100%正しいデータを作りたいのですが、私では力不足ゆえ、こういった方法を探すこととなりました。

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

回答No.5

noname#91219

考えていましたが、☆の部分が確定していない場合は（１）は関数だけでくむのはかなり難しいと思います。
（「有限」・「株式」などある程度決まっていればよいかもしれませんが）
No3の方の通りマクロで実行してみてはどうでしょうか？
（２）については
=IF(COUNTIF(Sheet1!$A$1:$A$50000,Sheet2!A●)>0,"○","×")
で良いと思います。検索と言うことは一つのセルに入力した後結果が出るようにするのでしょうか？その場合は●を入力欄に合わせます。

お礼

早速のご回答、ありがとうございました。
おっしゃる通り、マクロ実行しかないのかもしれません。
初めての単語もある為、少し時間をかけて考えてみようと思います。
お時間割いていただきありがとうございました。

回答No.4

hirorin00

はじめまして

勝手に話を変えちゃいけないとは思うのですが、察するところ「☆」は「株式会社」表すのではないかと…

シート１「登録データ一覧」（５万件）
Ａ列１行目　マルビシ
Ａ列２行目　上田屋
Ａ列３行目　たつなみ
Ａ列４行目　Koide
Ａ列５行目　株式会社マルビシ
Ａ列６行目　たつなみ株式会社

シート２「検索したい社名一覧」（５００社）
Ａ列１行目　マルビシ
Ａ列２行目　上田屋
Ａ列３行目　たつなみ

…でよろしければ…

シート１のB1に
=IF(ISERROR(FIND("株式会社",A1)),A1,IF(FIND("株式会社",A1)=1,MID(A1,5,100),LEFT(A1,LEN(A1)-4)))
これをB50000迄コピー（B1セルの右下にカーソルを合わせカーソルが十字に変わったらダブルクリック）する。

シート２のB1に
=COUNTIF(Sheet1!$B$1:$B$50000,Sheet2!A1)
これをB500までコピーB1セルの右下にカーソルを合わせカーソルが十字に変わったらダブルクリック）する。

シート１のC1に
=IF(COUNTIF(Sheet2!$A$1:$A$500,Sheet1!B1),"○","×")
これをC50000迄コピー（C1セルの右下にカーソルを合わせカーソルが十字に変わったらダブルクリック）する。

これでいかがでしょうか？
勝手に変えてしまいましてごめんなさい。

お礼

ご回答ありがとうございました。
しかし、「株式会社」などは元々入力しないように整理しているデータベースなので、伺いたかった事ではありません。
お時間割いていただきありがとうございました。

回答No.2

noname#91219

すみません、ちゃんと読んでませんでした。ちょっと待ってください。

回答No.1

noname#91219

(1)検索したい会社が、シート１上で何社登録があるか
ア社→【答え：２社】
イ社→【答え：１社】
ウ社→【答え：２社】

を出すには、COUNTIFが使えると思います。
たとえばシート３上に
=COUNTIF(Sheet1!$A$1:$A$50000,Sheet2!A1)
これをA500までコピーすればよいと思います。
条件複写なので、検索範囲が崩れると困るので$でAを囲んでおきます。
ちなみに「COUNTIF（範囲,検索条件）」は、範囲内で条件に一致するのは何件あるかを表示します。