関数を使って検索エンジンを作りたい
- エクセルで検索エンジンのようなものを作成しているがうまくいかない。検索用シートとデータ用シートがある。検索ワードを入力し、該当する行を表示させたいがうまくできない。
- 現在はVLOOKUP関数を使って該当する行の内容を表示させようとしているが、同じ検索ワードが複数あると全て同じ内容になってしまう。どのようにすれば個々の行の内容を表示させることができるか。
- 関数初心者なので詳しい手順を教えてほしい。
- ベストアンサー
関数を使って検索エンジンを作りたい
はじめまして☆ エクセルで検索エンジンのようなものを作成しているのですがなかなかうまくいきません。 ご存知の方、教えていただければと思います。 シートが検索用シート、データ用シートと分かれていまして =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
- shionair
- お礼率42% (3/7)
- オフィス系ソフト
- 回答数3
- ありがとう数3
- みんなの回答 (3)
- 専門家の回答
質問者が選んだベストアンサー
#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))),"") となります。 いきなり最終形を書いてしまうと理解しにくいと思い、段階的に式を記述しましたが、これでも理解できないということなら最初に書いたようにオートフィルタのご利用をお薦めします。
その他の回答 (2)
- zap35
- ベストアンサー率44% (1383/3079)
#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
- zap35
- ベストアンサー率44% (1383/3079)
ご質問の内容はよく分かりません。 >その行を全て表示させるということですが 検索したい文字列を含む行を複数表示したいように読み取れますが、データ用シートをみると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
- IF COUNT AND 関数で、
IF COUNT AND 関数で、 Sheet1をデータ用名簿リストにして出欠簿を作成しています。 部門が3つに分かれているので Sheet2~4に、部門ごとに抽出されるようにし、さらに、出席の人だけ表示されるようにするのが目的です。 =IF(COUNT(Sheet1!$K:$K)<ROW(C1),"",INDEX(Sheet1!$C:$C,SMALL(Sheet1!$K:$K,ROW(C1)))) という関数で部門ごとの抽出は成功したのですが、下記関数だと出席者のみの表示がうまくいきません。 Sheet1には =IF(J1=Sheet2!$A$1,ROW(A1),"") をKに入力し、対応する部門が絞れるようにしています。 =IF(COUNT((Sheet1!$K:$K)<ROW(C1)*(Sheet1!$F:$F)=D),"",INDEX(Sheet1!$C:$C,SMALL(Sheet1!$K:$K,ROW(C1)))) なお、(Sheet1!$F:$F)=D)は、Sheet1の出席者の欄で、ここにD(出るの略)と記入しています。 使用ソフトはKingsoftです。
- ベストアンサー
- オフィス系ソフト
- SUMPRODUCT関数について
excel2010 SUMPRODUCT関数で、式の内容が理解できず教えてください。 sheet1に、A22からAM1047までデータがあります。B,E列を参照し、 sheet2のA、Cに設定された内容で抽出するという内容になっています。 具体例を下記に示します。 sheet1の構成 A1~AM21までは題目が記載されています。抽出したいデータ対象ではありません。 A列は見出しの内容でほとんど空欄です。 A427セルに本体、以降空欄が続きA490セルにヘッド、また空欄が続きA544セルに 見出しの内容といった感じです。 B22~B30セルにX001という識別コード(同じものが9) B30~B39セルにX002という識別コード(同じものが9) 以降、識別コードがB1047セルまで入ります。 E22~E30までは、a,b,c,d,e,f,g,h,iという検索内容が入ります。 以降同じ検索内容が続きます。 H22~H1047まで数字のデータが入ります。 上記以外の列は無関係なので説明省略。 下記のイメージです。 A1からAM20までにも何かしら内容が入っていますが、関係ないので説明省略。 A21の様に記載しているのはセルアドレス、その直下は入っている内容です。 A21 B21 E21 H21 名称 コード 検索 3月2日 A22 B22 E22 H22 準備 X001 a 0 X001 b 3 X001 c 2 X001 d 5 X001 e 2 X001 f 0 X001 g 3 X001 h 0 X001 i 0 X002 a 4 X002 b 7 X002 c 0 X002 d 0 X002 e 1 X002 f 3 X002 g 0 X002 h 0 X002 i 3 … A427 B427 E427 H427 本体 X701 a 0 X701 b 0 X701 c 1 X701 d 0 X701 e 2 X701 f 3 X701 g 0 X701 h 0 X701 i 1 … A490 ヘッド X792 a 4 X792 b 7 X792 c 2 X792 d 0 X792 e 1 X792 f 3 X792 g 0 X792 h 0 X792 i 3 sheet2は A1 C1 D1 コード 検索 X001 c 2 X002 c 0 X003 c 0 X004 c 0 X005 c 0 X006 c 0 X007 c 0 X008 c 0 X009 c 0 … X701 c 1 X792 c 2 の様にコードと検索条件でD列にsheet1からデータをひっぱってきたいのです。 下記はsheet2のD27セルに設定した内容です。 =IF(SUMPRODUCT((Sheet1!$B$22:$B$1047=$A27)*(Sheet1!$E$22:$E$1047=$C27)), INDEX(Sheet1!H$22:H$1047,SUMPRODUCT((Sheet1!$B$22:$B$1047=$A27)*(Sheet1!$E$22:$E$1047=$C27)*(ROW(sheet1!$A$1:$A$1026)))),"-") webや過去のokwebのsumproduct関数について調べた上で上記式にたどり着き、この内容で、sheet1の内容をひっぱってくるので良いのですが、 最後の*(ROW(sheet1!$A$1:$A$1026))の部分が理解できません。 疑問の内容は、下記2点 (1)単独でROW(sheet1!$A$1:$A$1026)を実施すると1になります。 しかしながら、 =IF(SUMPRODUCT((Sheet1!$B$22:$B$1047=$A27)*(Sheet1!$E$22:$E$1047=$C27)), INDEX(Sheet1!H$22:H$1047,SUMPRODUCT((Sheet1!$B$22:$B$1047=$A27)*(Sheet1!$E$22:$E$1047=$C27)*1)),"-") としてフィルハンドコピーすると、全ての行が27行目の値になってしまいます。 何故1ではだめなのでしょう? (2)データの範囲はA22からAM1047なので範囲を下記の様に同じにしてみました。 *(ROW(sheet1!$A$1:$A$1026))→*(ROW(sheet1!$A$22:$A$1047)) とすると全く正しく抽出されません。0になってしまいます。 *(ROW(sheet1!$A$1:$A$1026))が*(ROW(sheet1!$A$1:$A$1025)) の様に設定すると#N/Aとなり、抽出する設定条件で必要なのですが、 この*(ROW(sheet1!$A$1:$A$1026))の意味を教えていただきたく。
- ベストアンサー
- Excel(エクセル)
- ROW関数について
先日質問をしたのですが、理解が足りないようなので質問させて頂きます。 ROW関数はセルを入力したそのセルの行番号を返すものだと思うのですが、例えば =INDEX(シート1!C2:シート1!HR2,(ROW()-3)*3+1,) このような数式をワークシート2のD3のセルに入れると、ROW()は3と解釈されるので、 ・範囲はData!のワークシートのC2からHR2まで、 ・(3-3)*1+1となるので、その範囲内の1番であるData!D2セルを参照するのだと思うのですが、何か間違っているでしょうか? この際に、ROW()に参照されるセルは、もしかしてシート1のものなのでしょうか? 宜しくお願いしますm(_ _)m
- ベストアンサー
- オフィス系ソフト
- 検索値を表示させる関数
教えてください。 製品の構成シートですが、元データがばらばらでまとめるのに困ってます。 シートB AW AY AZ BB BC BE 4 種類 使用数1 種類 使用数2 種類 使用数3 5 船 2 電車 1 飛行機 2 6 車 3 船 0 電車 1 7 電車 0 飛行機 0 船 2 8 飛行機 1 車 1 車 3 シートA A B C D E F 2 種類 場所 合計使用数 使用数1 使用数2 使用数3 3 車 道路 6 3 1 3 4 電車 線路 2 0 1 1 5 飛行機 空 3 1 0 2 6 船 海 4 2 0 2 シートBでのAY、BB,BEでの使用数をシートAの列D,E、Fに表示させたい のですが、どのような関数をシートAのD3、E4、F5のセルに入れれば表示 しますか? もう一つありますが、シートAからの検索です。 シートC D E F 1 コード 名称 略名 2 (1) 青 b 3 (2) 赤 j 4 (3) 黄 f 5 (7) 黒 q シートD B K 1 コード 略名 2 (7) q 3 (3) f 4 (1) b シートCの略名をシートDに検索し、表示させたいのですが、 シートDのK2セルにはVLOOKUPをどのようにつかえばよいのですか? 3000点位の検索があります。
- 締切済み
- オフィス系ソフト
- 1行名簿の複数行化について悩んでます(続)
いろいろ教えていただいたのですが、やっぱりよくわからず、助けを求めて再掲示です。 ほんとバカですいません(泣 質問内容を少し変えました。 <質問内容> 「原本」シートの1行データ内容を、 別シート「印刷用」に4行表示する関数を組んでいただけませんか。 (※下方向にドラッグしてコピーしても構成が変わらないように) セル番号(全て原本シートのセル番号)で説明させていただきます。 「原本」シートの1行顧客データを、 「印刷用」シートに以下のように、 「原本」シートのセル番号の値を参照していき、 4行データ化したいのです。 「原本」シート +-------------------------------+ ¦B2¦C2¦D2¦E2¦F2¦G2¦H2¦I2¦J2¦K2¦L2¦M2¦N2¦ +-------------------------------+ ↓ 「印刷用」シート(一部セル結合あります) +----------------+ ¦B2¦E2¦C2¦H2 ¦F2¦N2 ¦ + -- ---- + ¦ ¦D2¦ ¦G2 ¦ ¦ ¦ + ---- + ¦ ¦ ¦ ¦I2¦J2¦ ¦ ¦ + -- ---- + ¦ ¦K2¦ ¦L2¦M2¦ ¦ ¦ +----------------+ ※2件目以降のデータは全て行番号のみ1づつカウントアップします 非常に手間なような気がしてきましたが、 どなたかよろしくお願いいたします。
- ベストアンサー
- オフィス系ソフト
- INDEX関数SMALL関数を使って空白行を詰める
シート1にINDEX関数SMALL関数を使って空白行を詰める表を作成したのですが詰めた表をシート2に表示することはできないのでしょうか。 =IF(COUNTIF($A4:$A4,0)=0,MAX(D$3:$D3)+1,"") =IFERROR(INDEX(A:A,MATCH(ROW()-3,$D:$D, )),"")この表をシート2に表示する方法
- 締切済み
- Excel(エクセル)
- excelで検索結果の表示
J8にD列の語句を入力すると、検索結果の所に同じ行のデータを表示させるような仕組みを考えているのですが、どうもよくわかりません。D列の語句はvlookup関数で出すことはわかりましたが、その両隣の行のデータを出すのがまだわかりません。OFFSET関数でいけるのかな?とも思ったのですが… 詳しい方おられましたらよろしくお願いします。
- ベストアンサー
- その他MS Office製品
- Find関数、2つ目を検索
こんばんは 各セルに以下の様に入力してあるとします。(英字は列、数字は行) Find関数とRow関数もしくはColumn関数を使い、2番目に出現する"男"の行あるいは列番号を取得したい場合は、どのように検索すればよろしいのでしょうか? よろしくお願いします。 ABCD 1 男 女 女 男 2 女 3 男 4 男
- ベストアンサー
- その他MS Office製品
- VBAの検索で回答をいただいたのですが・・・
Excel2010VBAの検索で、シート1のE列(2行目から)の「日時」とシート2のE列(2行目から)の日時が一致した場合、シート2のF列(2行目から)からJ列(2行目から)、またはJ列にデータがない場合は、F列(2行目から)からI列(2行目から)にデータを入力するというプログラムを高速化する方法を回答者様から教えていただきました。 シートの内容としては シート1は、A「年」、B「月」、C「日」、D「時刻」、E「日時」(文字列)、F「データ1」、G「データ2」、H「データ3」、I「データ4」、J「データ5」、(1行目はタイトル) シート2も基本的にはシート1と同じです。 教えていただいたプログラムは以下の通りで、これを元にシート3(シート1と同じ配列)のE列の日時とシート2のE列の日時が一致した行のシート3のF~J列(データ1~データ5)、J列のデータがない場合、F~I列(データ1~データ4)のデータをシート2のK~O列(データ1~データ5)に入力するというプログラムを作りたかったのですが、自分にとってはこのプログラムの内容が理解できないため、どこを修正していいか分かりません。 どなたか解説していただけませんか? Sub xxx3() Dim myDic As Object Dim S1_v, S2_v Dim i As Long, n As Long, j As Long 'With Workbooks("ブック.xlsm").Worksheets("シート1") With Sheets("Sheet1") j = .Range("E" & Rows.Count).End(xlUp).Row S1_v = .Range("E1").Resize(j, 6).Value '対象範囲を配列に End With 'With Workbooks("ブック.xlsm").Worksheets("シート2") With Sheets("Sheet2") j = .Range("E" & Rows.Count).End(xlUp).Row S2_v = .Range("E1").Resize(j, 6).Value '対象範囲を配列に End With Set myDic = CreateObject("Scripting.Dictionary") For i = 2 To UBound(S1_v) myDic.Add S1_v(i, 1), i 'keyに追加、itemにi Next i For i = 2 To UBound(S2_v) If myDic.exists(S2_v(i, 1)) Then j = myDic.Item(S2_v(i, 1)) S2_v(i, 2) = S1_v(j, 2) S2_v(i, 3) = S1_v(j, 3) S2_v(i, 4) = S1_v(j, 4) S2_v(i, 5) = S1_v(j, 5) S2_v(i, 6) = S1_v(j, 6) Else 'マッチしなかったときの処理 End If Next 'With Workbooks("ブック.xlsm").Worksheets("シート2") With Sheets("Sheet2") j = .Range("E" & Rows.Count).End(xlUp).Row .Range("E1").Resize(j, 6).Value = S2_v End With Set myDic = Nothing Erase S1_v, S2_v End Sub 回答よろしくお願いします。
- ベストアンサー
- その他MS Office製品
- 関数の質問
エクセル2003を使用しています。 下記のシート1に不規則な空白行があり、それをシート2に空白行を詰めて 表示させたいのですがわかりません。 関数でよろしくおねがいいいたします。 シート1 A B C D E F G 1 1 1 1 1 1 1 1 2 3 2 2 2 2 2 2 2 4 3 3 3 3 3 3 3 5 6 7 4 4 4 4 4 4 4 8 9 5 5 5 5 5 5 5 10 11 12 13 6 6 6 6 6 6 6 14 7 7 7 7 7 7 7 15 8 8 8 8 8 8 8 16 9 9 9 9 9 9 9 17 18 シート2 A B C D E F G 1 1 1 1 1 1 1 1 2 2 2 2 2 2 2 2 3 3 3 3 3 3 3 3 4 4 4 4 4 4 4 4 5 5 5 5 5 5 5 5 6 6 6 6 6 6 6 6 7 7 7 7 7 7 7 7 8 8 8 8 8 8 8 8 9 9 9 9 9 9 9 9 10 11 12 13 14 15 16 17 18 ・ ・ ・ ・
- ベストアンサー
- オフィス系ソフト
お礼
ありがとうございます!! 早速チャレンジしてみます。 大変な内容でしたのに…本当に感謝です☆彡 ありがとうございました(^O^)/