Excel表の一覧から、フリーワード検索した項目のみ引っ張ってくるには？

質問

仕事上で急遽データを作成しないといけなくてご相談させて頂きます。

(1)Excel「シート１」に以下のような一覧があります。

1．WEB　　　　 80人
2．営業事務　　70人
3．営業　　　　60人　
4．サービス　　55人
5．事務　　　　43人
6．製造　　　　35人
7．販売　　　　30人
8．医療事務　　20人
9．保育士　　　10人
10．土木　 　　5人
：
：

(2)Excel「シート２」にあるキーワード項目（例えば、フリーワード入力欄を作成して『事務』と入力した場合）を入力した場合に、
一覧表から、
『営業事務』、『事務』、『医療事務』を引っ張って、
Excel「シート２」に以下のようなランキング表を作成したいと考えてます。
-------------------------------
キーワード　［　事務　]

1．営業事務　　70人
2．事務　　　　43人
3．医療事務　　20人
-------------------------------

※Excel2003を使用してます。

Excel関数を使用して、どなたかお分かりになられる方がいらしたら、お教え願います。お力添えの程宜しくお願いします。

回答No.1

KURUMITO · ベストアンサー

シート1ではＡ1セルから下方に業務の種類が、Ｂ1セルから下方に人数が入力されているとします。
Ｃ1セルには次の式を入力し下方にオートフィルドラッグします。
=IF(ISERROR(FIND(Sheet2!$A$1,A1)),"",A1)
Ｄ1セルには次の式を入力し下方にオートフィルドラッグします。
=IF(C1="","",COUNTIF(C$1:C1,">*"))
シート2ではＡ1セルに検索のキーワードを例えば事務と入力します。
Ａ2セルには次の式を入力し、Ｂ2セルまでオートフィルドラッグしたのちに下方にもオートフィルドラッグします。
=IF(COUNTIF(Sheet3!$D$1:$D$100,ROW(A1))=0,"",INDEX(Sheet3!$A$1:$B$100,MATCH(ROW(A1),Sheet3!$D$1:$D$100,0),COLUMN(A1)))

お礼

ありがとうございます。
細かい方法も記載して頂き、感謝しております。

回答No.6

imogasi

自称imogasi方式で出来ます。ただし作業列を使います。
例データ　Sheet1
任はセルのデータを数値にし、表示形式で「人」を加える方が良い。
A列　　B列　　C列
事業所 人数 該当連番
WEB 80人
営業事務 70人 1
営業 60人
サービス 55人
事務 43人 2
製造 35人
販売 30人
医療事務 20人 3
保育士 10人
土木 5人
ーー
Sheet2のA1に検索語「事務」を入れます
ーー
Sheet1の
C列のC2の式は　=IF(COUNTIF(A2,"*" & Sheet2!$A$1&"*")>=1,MAX($C$1:C1)+1,"")
と入れて最後の行まで式を複写します。該当に連番を振ってます。
含むというのはワイルドカード文字＊をCOUNTIFで使えるので使います。
ーー
Sheet2に行って
A2に式　=INDEX(Sheet1!$A$1:$B$100,MATCH(ROW()-1,Sheet1!$C$1:$C$100,0),COLUMN())
と入れて、Sheet1のデータ列数だけ右方向に式を複写します。
A2:B2を範囲指定して下方向に式を複写します。Sheet1のC列の最大数
より超えて式を複写すると＃N/Aのエラーが出る。これは
=IF(Row()-1>Max(Sheet1!$c$1:$c$100),"",上記式)
出ないようにします。
Sheet2
事務
営業事務 70
事務 43
医療事務 20
#N/A #N/A　＜－－上記修正前
ーー
この手の質問は毎日あり、Googleで「imogasi方式」で照会してください。沢山の同類質問と他のタイプの回答が判ります。
ーーー
ランク関数はかまわないが、並べ替えは値化してから行わないとダメです。

回答No.5

tom04

No.4です！
何度もごめんなさい・・・

一番肝心なSheet2のA2セルの数式を書いていませんでした。

Sheet2のA2セルを
=IF(COUNT(Sheet1!$C$2:$C$11)>=ROW()-1,INDEX(Sheet1!$A$2:$A$11,SMALL(Sheet1!$C$2:$C$11,ROW()-1)),"")

としてこれもオートフィルでコピーしてみてください。
（この列もかなり下の方までコピーしても構いません）

どうもたびたび失礼しました。m(__)m

回答No.4

tom04

こんばんは！
参考になるかどうか分かりませんが・・・
↓の画像のように表を作ってみました。

Sheet1のC1セルに　=Sheet2!B1
Sheet1のC2セルに　=IF(COUNTIF(A2:A2,"*"&$C$1&"*")>0,ROW()-1,"")
としてC2セルをオートフィルで下へコピーしています。

次にSheet2の方ですが、
Sheet2のB2セルに　=IF(A2="","",VLOOKUP(A2,Sheet1!$A$2:$B$11,2,0))
と数式を入れて、下へオートフィルでコピーします。
この場合、かなり下までコピーしても構いません。

これでSheet2のB1セルに「営業」や「事務」とキーワードを入力すれば
その言葉を含んだ業種と人数が表示できると思います。

以上、お役に立てれば幸いです。m(__)m

回答No.3

KURUMITO

回答Ｎｏ1での作業でシート2のＡ2セルには回答2の式を入力してくださいとのことで、Ｎｏ1での作業でＡ2セルへの入力する式は回答2に変更してくださいとのことです。
シート1での作業が済んでいなければシート2のＡ1セルにキーワードを入力しても何の反応もありません。

回答No.2

KURUMITO

回答Ｎｏ1です。
シート2のＡ2セルへ入力する式は次のようにしてください。
=IF(COUNTIF(Sheet1!$D$1:$D$100,ROW(A1))=0,"",INDEX(Sheet1!$A$1:$B$100,MATCH(ROW(A1),Sheet1!$D$1:$D$100,0),COLUMN(A1)))

補足

ご回答ありがとうございます。
ご質問なんですが、シート2のＡ2セルに数式を入れた後の作業を教えて頂けますでしょうか。

シート2のＡ1セルに、キーワードを入れても何もならないんです。。

初心者なので申し訳ございません。。。