エクセル 行ごと検索結果を表示したい

質問

Ａ　　Ｂ　　　Ｃ　　　　Ｄ　　　　Ｅ　　Ｆ　　　Ｇ　　　Ｈ　　　Ｉ
１　カナ　漢字　住所　備考　（空白）　カナ　漢字　住所　備考
２　カナ　漢字　住所　備考　（空白）　カナ　漢字　住所　備考

というような住所録があります。

別シートのＡ１セルに検索したいキーワードを入力し、
該当するセル全てを表示できるようにしたいのです。

ただ、検索結果はＡ１～Ｄ１、Ｆ１～Ｉ１、
というようにセットで表示できるように。

このようなことができるような方法（関数？）はありませんでしょうか？

エクセル初心者ですので、詳しく教えていただけると助かります。
よろしくお願いします。

回答No.5

tom04 · ベストアンサー

こんばんは！
外していたらごめんなさい。
↓の画像で説明させていただきます。
Sheet1に作業用の列を設けています。
作業列K2セルに
=IF(OR(ISNUMBER(FIND(Sheet2!$A$1,A2:D2))),ROW(A1),"")

L2セルに
=IF(OR(ISNUMBER(FIND(Sheet2!$A$1,F2:I2))),ROW(A1),"")

これはどちらも配列数式になってしまいますので、
この画面からコピー＆ペーストしただけでは正確な数値が表示されないと思います。
各セルに貼り付け後、F2キーを押す、又は貼り付けセルをダブルクリック、又は数式バー内で一度クリックします。
編集可能になりますので、Shift＋Ctrl＋Enterキーで確定してください。
数式の前後に｛　｝マークが入りは配列数式になります。
そして、このK2・L2セルを範囲指定し、L2セルのフィルハンドルで下へずぃ～～～！っとコピーします。

そして、Sheet2のB2セルに
=IF(COUNT(Sheet1!$K$2:$K$100)<ROW(A1),"",INDEX(Sheet1!A$2:A$100,SMALL(Sheet1!$K$2:$K$100,ROW(A1))))
（これは配列数式ではありません）としてE2セルまでコピーします
B2～E2を範囲指定してE2セルのフィルハンドルで下へコピーします。

同様にSheet2のG2セルに
=IF(COUNT(Sheet1!$L$2:$L$100)<ROW(A1),"",INDEX(Sheet1!F$2:F$100,SMALL(Sheet1!$L$2:$L$100,ROW(A1))))
という数式を入れ、J2セルまでオートフィルでコピーした後に、下へコピーします。

これで画像のような感じになります。
元データに空白がある場合は「0」が表示されますので、
当方使用のExcel2003の場合ですが、
メニュー　→　ツール　→　オプション　→　「表示タブ」で「ゼロ値」のチェックを外しています。

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

以上、長々と書いてしまいました。
参考になれば良いのですが、
的外れなら読み流してくださいね。m(__)m

回答No.4

mar00

私はExcel2003を使用していますので2003での話を
させていただきます。
2007を使用していれば、聞き流してください。(同じかわからないので)
オートフィルタを使ってみては?
並べ替えをしておけば、まとめて別シートへのコピーもできるので。
データ→フィルタ→オートフィルタを指定して検索したいキーワードを
指定すれば表示できると思います。

回答No.3

junichihirobe

検索する値がa列にあると仮定して

住所録のシートのj列に
=IF(ISERROR(FIND(別シート!A$1,A1)),"",ROW())
を入れて、下方向にコピー。

別シートのa1セルに検索したキーワードを入れて、
ｂ列の５行目に=INDEX(Sheet1!A:A,SMALL(住所録!J:J,ROW(A1)),1)
を入れて、下方向にコピー。

ｃ列に=VLOOKUP($B5,住所録!$A$1:$J$11,1,0)&VLOOKUP($B5,住所録!$A$1:$J$11,2,0)&VLOOKUP($B5,住所録!$A$1:$J$11,3,0)&VLOOKUP($B5,住所録!$A$1:$J$11,4,0)
を入れて、下方向にコピー。

ｃ列の式はｆ～iまで＆でvlookupの式をつなげればよい。

以上。

あいまい検索でなければ、単純にvlookupをつなげればよいでのわ？
データは昇順にしよう。

回答No.2

keithin

A1にデータ
A2に　行
A3に　列　と記入

B1に　目的の値を記入
B2に　=IF(COUNTIF(Sheet1!A:I,B1)<>1,"NA",SUMPRODUCT((Sheet1!A1:I99=B1)*ROW(Sheet1!A1:I99)))
B3に　=IF(COUNTIF(Sheet1!A:I,B1)<>1,"NA",FLOOR(SUMPRODUCT((Sheet1!A1:I99=B1)*COLUMN(Sheet1!A1:I99))-1,5)+1)

データ１：　=INDEX(Sheet1!A:I,B2,B3)
データ２：　=INDEX(Sheet1!A:I,B2,B3+1)
データ３：　=INDEX(Sheet1!A:I,B2,B3+2)
データ４：　=INDEX(Sheet1!A:I,B2,B3+3)

回答No.1

aokii

vlookup関数をお使い下さい。