Excel 文字列検索

質問

A　　　　　　B　　　　　　C
1　　田中実　　　　田中　　　　田中実
2　　石川武　　　　　　　　　　田中康雄
3　　中田英人
4　　石崎甚平　　　石川　　　　石川武
5　　石川啓子　　　　　　　　　石川啓子
6　　中田康雄

B列にA列の文字列に部分一致する文字列を入力すると
C列にその検索結果を表示する様にしたい。
C1=IF(OR($B$2="",$B$2<1),"",VLOOKUP("*"&B2&"*",$A$2:$A$1500,1,FALSE))だと田中や石川のように名前がかぶると
1個しか表示されない。B2にも田中が該当する人を表示させたい。
説明が下手かもしれませんが、知恵を貸してください。よろしくお願いします。

回答No.2

KURUMITO · ベストアンサー

作業列を作って対応することになるでしょう。また、田中や石川を検索しそれらをリストアップさせるのでしたら田中のためにＢ，Ｃ，Ｄの3列を設けることが田中がたくさんいる場合にも対応できることになり、関数を複雑にならずに済みます。一つの姓当りに3列を追加する形で表を作成し、作業列は後で非表示にすればよいでしょう。
例えばＢ1セルに田中と入力します。
Ｃ1セルには次の式を入力します。
=IF($A1="","",IF(COUNTIF($A1,B$1&"*")=0,"",COUNTIF($A$1:$A1,B$1&"*")))
Ｄ1セルには次の式を入力します。
=IF(COUNTIF(C:C,ROW(A1))=0,"",INDIRECT("A"&MATCH(ROW(A1),C:C,0)))
Ｃ1セルおよびＤ1セルを選択してから下方にオートフィルドラッグします。
これでＤ列には田中の姓をもった氏名が並んで表示されます。
同じようにＥ1セルに石川と入力します。次にＣ1およびＤ1セルを選択してコピーし、Ｆ1セルに貼り付けます。Ｆ1セルとＧ1セルを選択して下方にオートフィルドラッグすればＧ列には石川の姓を持つ氏名を並べることができます。
同様の操作で列を追加することでいろいろな姓の氏名をリストアップすることができます。
最後にＣ列やＦ列などを非表示すればよいでしょう。

お礼

完璧にできました！ありがとうございます！
本当に嬉しいです！！

補足

A　　　　　　B　　　　　　C　　　　　D
1　　担当者　　　　客先
2　　秋山　　　　　あ商店　　　　秋山　　　あ商店
3　　秋山　　　　　い商店　　　　　　　　　い商店
4　　田中　　　　　う商店　　　　　　　　　か商店
5　　田中　　　　　お商店　　　
6　　秋山　　　　　か商店　　
7　　星川　　　　　き商店
8　　星川　　　　　く商店
9　　星川　　　　　け商店
10　 田中　　　　　こ商店
11　　　　　　　（Bはもっと複雑な名前）

C1に担当者名を入力すればD列に客先を表示なおかつ
C1に客先名を入力すればB列を検索して表示できる。
同一セルで2つの動作をすることができますか？

回答No.4

Wendy02

こんにちは。

このようにしてみたらいかがですか？

C2;秋山

D2 に以下の数式を貼り付けてください。オートフィルでコピーしてください。

=IF(COUNTIF($A$1:$A$10,$C$2&"*")>=ROW(A1),INDEX($A$1:$B$10,SMALL(INDEX(COUNTIF(OFFSET($A$1,ROW($A$1:$A$10)-1,0),$C$2&"*")*ROW($A$1:$A$10),,),COUNTIF($A$1:$A$10,"<>"&$C$2&"*")+ROW(A1)),2),"")

範囲の変更は、数式内を置換で行ってください。手作業ですとミスが起こる可能性が多いです。

$A$1:$A$10
$A$1:$B$10

の二種類です。

なお、INDEX 関数で、最後の数式で、　ROW(A1)),2),"")　の2 が、2列目です。1でしたら、元の質問の回答です。あまり多いようでしたら、フィルタ・オプションに切り替えたほうが良いです。

回答No.3

hallo-2007

作業列を使用するか、配列関数になります。作業列案ですが
　　　　A　　　　　　B　　　　　　C
1　　田中実　　　　田中　　　　
2　　石川武　　　　　　　　　　
3　　中田英人
4　　石崎甚平　　　
5　　石川啓子　　　　　　　　　
6　　中田康雄
とあったとして、C列に　=IF(COUNTIF(A1,"*"&B$1&"*"),ROW(),"")
下へコピィしておけば、田中を含む氏名の行にその行番号が表示されます。
D列に　=IF(ISERROR(SMALL(C:C,ROW(A1))),"",INDEX(A:A,SMALL(C:C,ROW(A1))))
として下へコピィしてみてください。
作業列が見苦しければ非表示にしておきます。

お礼

ありがとうございます。KURUMITOさんのやりかたでできました。
また補足があれば是非ともお願いします！！

回答No.1

135ok

関数ではなく、フィルタで対応できます。
まず、表を下記のようにします。
Ａ　　　　　Ｂ
氏名　　　　氏名
田中実　　　石川
石川武　　　中田
中田英人
石崎甚平
石川啓子
中田康雄

そして、データ→フィルタ→フィルタ　オプションの設定を選択
抽出先を「指定した範囲」にチェック
リスト範囲　　Ａ列の文字が入っている範囲すべて
検索条件範囲　Ｂ列の文字が入っている範囲すべて
抽出範囲　　　Ｃ１

上記の設定で、添付のようになります。

補足

その方法も良いかもしれませんが、やはり関数を用いてセルにB列に文字列を
入力して、その結果をC列に反映させたいです。
関数にこだわる理由は、A列のデータが1500件もありますからフィルタで
操作するのは面倒。手動マクロを設定すればよいかもしれませんが、
もっと簡単に、入力でババッと検索したいです。