![](http://oshiete.xgoo.jp/images/v2/pc/qa/question_title.png?e8efa67)
はじめまして☆
エクセルで検索エンジンのようなものを作成しているのですがなかなかうまくいきません。
ご存知の方、教えていただければと思います。
シートが検索用シート、データ用シートと分かれていまして
=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ベストアンサー
- 回答日時:
#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))),"")
となります。
いきなり最終形を書いてしまうと理解しにくいと思い、段階的に式を記述しましたが、これでも理解できないということなら最初に書いたようにオートフィルタのご利用をお薦めします。
ありがとうございます!!
早速チャレンジしてみます。
大変な内容でしたのに…本当に感謝です☆彡
ありがとうございました(^O^)/
No.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))
とってもお礼をしたいのですが、ポイントなどはどうやってつけるのかよく分かりません。
まだ、試していませんが、本当に親切に教えていただいたので感謝です♪
ポイントのつけ方などご存知でしたら、教えて下さい。
たくさんポイント差し上げたいですw
No.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と合致するデータを引っ張ってくるはずですよ
この回答への補足
文章下手ですいません(^_^;)
Jの列には、カナに変換させる関数が入っています。
A列にも入っていますが、それは、VLOOKUPを使用するためです。
A列から引っ張ればいいのですが、先に上記の関数を作ってしまったので…。
該当する行を表示させるにはどうしたらいいのでしょう?
キーワード1で検索した後、その行を表示させたいのです。
ただし、キーワード1には同じ言葉が入っていることがあります。
(ワード2、ワード3には別の内容なのですが…)
お探しのQ&Aが見つからない時は、教えて!gooで質問しましょう!
似たような質問が見つかりました
- Excel(エクセル) VLOOKUP が機能しない、その原因は何 ? 8 2022/10/19 12:06
- Visual Basic(VBA) 顧客ごとに違う点検案内を作成するマクロ 4 2022/09/16 05:34
- Visual Basic(VBA) VBA 検索と入力 Excel ブック ぶぶぶ シート ししし 列V 検索対象の列です 最終行は、お 6 2023/05/17 01:40
- Visual Basic(VBA) 検索のユーザーフォームの表示について 1 2023/03/27 23:31
- Visual Basic(VBA) VBA 改行コードの取り方 1 2022/03/22 14:14
- Excel(エクセル) VLOOKUPで、検索先が3シートに分かれていて、番号の大小で検索するとエラーになります。 6 2023/07/14 19:09
- Visual Basic(VBA) ユーザーフォーム「frm_基本❶」を立ち上げると新規で入力する行数を右下のNoとして表示しています。 1 2023/03/16 19:02
- Excel(エクセル) Excelでの検索結果を含む行だけを表示させたい 5 2023/03/10 17:08
- Visual Basic(VBA) ファイル全てを .xlsm に変更したところ、プログラムが途中で落ちてしまっています 17 2022/12/07 12:03
- Excel(エクセル) PHPプログラムをエクセルに張り付けると検索ボックスがでてくる! 3 2022/05/08 07:10
関連するカテゴリからQ&Aを探す
おすすめ情報
デイリーランキングこのカテゴリの人気デイリーQ&Aランキング
-
IFとIFS関数
-
Excelを無料で使うには? パソ...
-
セルの数を求めたい
-
エクセルの数式バーのフォント...
-
Excelの表示についての質問
-
再質問です。マクロの修正箇所...
-
データチェックを行うエクセル...
-
Excelに詳しい方! B列が「日...
-
西暦や和暦の表示をyyyymmdd表...
-
Excelで50個のセルに同じ文字を...
-
Excelの数式について教えてくだ...
-
Excel VBAで全ての矢印を赤色に...
-
エクセル関数を使って
-
祝日と土曜、日曜の合計をカウ...
-
xlsxファイルを保存する際にPDF...
-
Excelについて
-
エクセルVBA、別ブックへ転記す...
-
エクセルで「ページレイアウト...
-
2列に入っているデータを1列...
-
【ExcelVBA】名前を付けて保存→...
マンスリーランキングこのカテゴリの人気マンスリーQ&Aランキング
-
半角カタカナをヘボン式ローマ...
-
(マクロ)vlookupの元データを同...
-
エクセルで上位バイトのセルと...
-
exselの質問です
-
Excel 大小比較演算子による「...
-
Excel VBについての質問です。
-
エクセルの問題です。絶対値の...
-
非表示列の再表示に失敗
-
職場の人から聞かれており、こ...
-
Excel関数-文字列で自動作成さ...
-
Excelデータをコピペして、ペー...
-
ユーザー定義関数をアドイン登...
-
【マクロ】for next構文について
-
エクセルの日付を編集する
-
【マクロ】VLOOKUPにて参照元に...
-
exselで最小数で並び替える関数
-
libre 表計算ソフトの計算がう...
-
エクセルで表
-
エクセルの表で1年間の曜日を...
-
西暦和暦
おすすめ情報