関数を教えてください

質問

エクセル初心者の質問です。

Sheet1に住所録があります。
ひとつのセルに住所・名称・TELが入っている状態です。

Sheet2で、ひとつのセルに検索したいキーワードを入力し、
一致するセルを全て抽出できるようにしたいのですが、
その方法（関数）を教えてください。

関数でなくても何か良い方法があれば教えてください。
よろしくおねがいします。

回答No.3

tom04 · ベストアンサー

No．2です！
データは1列ではないという事ですね？
あ行・か行・・・と列が10列あるという前提での方法になります。

↓の画像のようにSheet1に作業用の表を設けます。
Sheet1のL2セルに
=IF($L$1="","",IF(ISNUMBER(FIND($L$1,A2)),ROW(A1),""))
という数式を入れ、オートフィルで列方向（10列）と行方向にずぃ～～～！っとコピーします。
この表のデータを元に、Sheet2に表示させます。

Sheet2のA2セルに
=IF(COUNT(Sheet1!L$2:L$100)<ROW(A1),"",INDEX(Sheet1!A$2:A$100,SMALL(Sheet1!L$2:L$100,ROW(A1))))
という数式を入れ、列方向と行方向にオートフィルでコピーすると
画像のような感じになります。
尚、数式はSheet1の100行目まで対応できるようにしています。

今回は各列ごとに表示させるようにしてみましたが
もし、1列（1行）に表示させたい場合は
作業用の数式が変わってきます。

以上、この程度でお役に立ちますかね？m(__)m

回答No.2

tom04

こんばんは！
一例です。
↓の画像で左側のSheet1に作業用の列を設けています。
Sheet1の作業列B2セルに
=IF(Sheet2!$A$1="","",IF(ISNUMBER(FIND(Sheet2!$A$1,A2)),ROW(A1),""))
という数式を入れオートフィルでずぃ～～～！っと下へコピーします。

そして、Sheet2のA2セルに
=IF(COUNT(Sheet1!$B$2:$B$1000)<ROW(A1),"",INDEX(Sheet1!$A$2:$A$1000,SMALL(Sheet1!$B$2:$B$1000,ROW(A1))))
という数式を入れ、オートフィルで下へコピーすると
画像のような感じになります。
これで、Sheet2のA1セルに検索したい文字を入力すれば、
その文字が含まれているセルが表示されるはずです。

尚、数式は1000行目まで対応できるようにしていますが、
データ量によって範囲指定の領域はアレンジしてみてください。

以上、参考になれば幸いです。m(__)m

お礼

回答ありがとうございます。

早速やってみました。
見事に検索結果が抽出されました。

そこで・・本当に素人の質問ですみません。
Sheet1のデータがA列、C列、E列・・にある場合は
（A列は「あ」行、C列は「か」業と縦にデータが入っています）
どのように範囲指定すればいいでしょうか？

よろしくお願いいたします。

回答No.1

KURUMITO

例えばシート1では1行目に項目名があり、2行目からデータがあるとします。
また、検索するセルはＡ列にあるとします。
シート2ではＡ1セルに検索の文字列を入力することにします。
そこでシート1には作業列として例えばＥ2セルには次の式を入力して下方にオートフィルドラッグします。

=IF(COUNTIF(A2,"*"&Sheet2!$A$1&"*")>0,MAX(E$1:E1)+1,"")

シート2ではＡ2セルに次の式を入力し右横方向にオートフィルドラッグしたのちに下方向にもオートフィルドラッグします。

=IF(COLUMN(A2)>4,"",IF(INDEX(Sheet1!$A:$D,ROW(A2),COLUMN(A2))="","",INDEX(Sheet1!$A:$D,ROW(A2),COLUMN(A2))))

これでシート2のＡ1セルに検索の文字列を入力すれば、それに該当するデータがシート2に表示されます。