関数を使って検索エンジンを作りたい

質問

はじめまして☆
エクセルで検索エンジンのようなものを作成しているのですがなかなかうまくいきません。
ご存知の方、教えていただければと思います。

シートが検索用シート、データ用シートと分かれていまして
=PHONETICで検索したい文字列をカナ変換後↓としています。
（↓これも人から教わったので、細かい内容はよくわかっていないのですが、なんとか使っています）
=INDEX($J$2:$J$5000,SMALL(IF(ISERROR(FIND(検索!$L$2,$J$2:$J$5000)),1000,ROW($J$2:$J$5000)-1),ROW()-2))

------検索用シート-----------------------------------------
　　　A　　　　　　　B　　　　　　　　　C　　　　D　E　F　G
1　　　　　
2　　　　　　検索ワード入力セル　
3
4　検索の結果を4行目以降20行目まで表示させる

------データ用シート---------------------------------------
　　　A　　B　　C　　D　　　E　　　F　　　G　　　H　　　I
1PHO関数　　　　　　ワード1　ワード2　ワード3
2
3
4　これ以降　5000行まで続く↓

やりたいことは、データシートのD1に記入されたキーワード1を検索した上で、その行を全て表示させるということですが、なかなか複雑なようで…。

現在、上記で、検索用シートで文字入力後、その検索ワードの行の内容を全てVLOOKUPで拾おうとしたのですが、検索ワードが同一のものが多数出てきた時に、全て同じ内容になってしまうことに気づきました。(T_T)

はじめからやり直さなければいけないような気がしますが、
どのようにしたら、検索して、その該当する行をそれぞれ表示させることができるでしょうか？

関数初心者なので、できましたら、詳しく教えていただければと思います。よろしくお願いいたしますm(__)m

回答No.2

zap35 · ベストアンサー

＃０１です。こういうことですか？
データ用シートのＤ１の値でデータ用シートのＪ列を検索して、部分一致する文字列がある行を「検索用シート」の４行目以下に表示する。

個人的にはデータ用シートのＪ列にオートフィルタを設定し、「○○を含む」条件で絞り込む方が簡単で理解しやすいと思いますが、関数式にしてみます

まずは検索用シートのＡ４セルに以下の式を貼り付けて右方向、および下方向にコピーしてみてください。
=INDEX(データ!D:D,LARGE(INDEX(NOT(ISERROR(FIND(データ!$D$1,データ!$J$1:$J$5000)))*ROW(データ!$J$1:$J$5000),),COUNTIF(データ!$J$1:$J$5000,"*"&データ!$D$1&"*")-ROW(D5)+1))

ただしこの式では検索条件に合致した行数以降は#NUM!エラーとなります。この問題を回避するためにはＡ４に貼り付ける式は
=IF(COUNTIF(データ!$J$1:$J$5000,"*"&データ!$D$1&"*")>ROW(A1)-1,INDEX(データ!A:A,LARGE(INDEX(NOT(ISERROR(FIND(データ!$D$1,データ!$J$1:$J$5000)))*ROW(データ!$J$1:$J$5000),),COUNTIF(データ!$J$1:$J$5000,"*"&データ!$D$1&"*")-ROW(A1)+1)),"")
です。

更にこれでもデータ用シートで「空白」のセルは「0」で表示されます。これも回避したいならＡ４の式は以下になります
=IF(COUNTIF(データ!$J$1:$J$5000,"*"&データ!$D$1&"*")>ROW(A1)-1,IF(INDEX(データ!A:A,LARGE(INDEX(NOT(ISERROR(FIND(データ!$D$1,データ!$J$1:$J$5000)))*ROW(データ!$J$1:$J$5000),),COUNTIF(データ!$J$1:$J$5000,"*"&データ!$D$1&"*")-ROW(A1)+1))="","",INDEX(データ!A:A,LARGE(INDEX(NOT(ISERROR(FIND(データ!$D$1,データ!$J$1:$J$5000)))*ROW(データ!$J$1:$J$5000),),COUNTIF(データ!$J$1:$J$5000,"*"&データ!$D$1&"*")-ROW(A1)+1))),"")
となります。

いきなり最終形を書いてしまうと理解しにくいと思い、段階的に式を記述しましたが、これでも理解できないということなら最初に書いたようにオートフィルタのご利用をお薦めします。

お礼

ありがとうございます！！
早速チャレンジしてみます。
大変な内容でしたのに…本当に感謝です☆彡　
ありがとうございました(^O^)／

回答No.3

zap35

＃０１です。最初の式が誤っていました。以下に差し替えて下さい

=INDEX(データ!D:D,LARGE(INDEX(NOT(ISERROR(FIND(データ!$D$1,データ!$J$1:$J$5000)))*ROW(データ!$J$1:$J$5000),),COUNTIF(データ!$J$1:$J$5000,"*"&データ!$D$1&"*")-ROW(A1)+1))

お礼

とってもお礼をしたいのですが、ポイントなどはどうやってつけるのかよく分かりません。
まだ、試していませんが、本当に親切に教えていただいたので感謝です♪
ポイントのつけ方などご存知でしたら、教えて下さい。
たくさんポイント差し上げたいですｗ

回答No.1

zap35

ご質問の内容はよく分かりません。
＞その行を全て表示させるということですが
検索したい文字列を含む行を複数表示したいように読み取れますが、データ用シートをみるとA1に表示される検索キーと合致する項目を横方向に並べるようにも読み取れます。でも、

=INDEX($J$2:$J$5000,SMALL(IF(ISERROR(FIND(検索!$L$2,$J$2:$J$5000)),1000,ROW($J$2:$J$5000)-1),ROW()-2))
＞細かい内容はよくわかっていないのですが

これが理解できていないのなら、何を回答してもまた繰り返しで、応用が利かないのではないでしょうか。
先頭の$J$2:$J$5000を別の列に変えれば、J列が検索シートのL2と合致するデータを引っ張ってくるはずですよ

補足

文章下手ですいません(^_^;)
Ｊの列には、カナに変換させる関数が入っています。
Ａ列にも入っていますが、それは、VLOOKUPを使用するためです。
A列から引っ張ればいいのですが、先に上記の関数を作ってしまったので…。
該当する行を表示させるにはどうしたらいいのでしょう？
キーワード1で検索した後、その行を表示させたいのです。
ただし、キーワード1には同じ言葉が入っていることがあります。
（ワード2、ワード3には別の内容なのですが…）