こんにちはゲストさん。会員登録(無料)して質問・回答してみよう!

解決済みの質問

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

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

シートが検索用シート、データ用シートと分かれていまして
=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

投稿日時 - 2007-06-18 15:40:49

QNo.3095185

すぐに回答ほしいです

質問者が選んだベストアンサー

#01です。こういうことですか?
データ用シートのD1の値でデータ用シートのJ列を検索して、部分一致する文字列がある行を「検索用シート」の4行目以下に表示する。

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

まずは検索用シートのA4セルに以下の式を貼り付けて右方向、および下方向にコピーしてみてください。
=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!エラーとなります。この問題を回避するためにはA4に貼り付ける式は
=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」で表示されます。これも回避したいならA4の式は以下になります
=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))),"")
となります。

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

投稿日時 - 2007-06-19 18:43:05

お礼

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

投稿日時 - 2007-06-20 15:16:19

ANo.2

このQ&Aは役に立ちましたか?

1人が「このQ&Aが役に立った」と投票しています

回答(3)

ANo.3

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

=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))

投稿日時 - 2007-06-19 18:45:10

お礼

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

投稿日時 - 2007-06-20 15:21:42

ANo.1

ご質問の内容はよく分かりません。
>その行を全て表示させるということですが
検索したい文字列を含む行を複数表示したいように読み取れますが、データ用シートをみると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と合致するデータを引っ張ってくるはずですよ

投稿日時 - 2007-06-18 17:09:39

補足

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

投稿日時 - 2007-06-19 14:21:15

あなたにオススメの質問