![](http://oshiete.xgoo.jp/images/v2/pc/qa/question_title.png?8acaa2e)
A B C D E F G H I
1 カナ 漢字 住所 備考 (空白) カナ 漢字 住所 備考
2 カナ 漢字 住所 備考 (空白) カナ 漢字 住所 備考
というような住所録があります。
別シートのA1セルに検索したいキーワードを入力し、
該当するセル全てを表示できるようにしたいのです。
ただ、検索結果はA1~D1、F1~I1、
というようにセットで表示できるように。
このようなことができるような方法(関数?)はありませんでしょうか?
エクセル初心者ですので、詳しく教えていただけると助かります。
よろしくお願いします。
No.5ベストアンサー
- 回答日時:
こんばんは!
外していたらごめんなさい。
↓の画像で説明させていただきます。
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
![「エクセル 行ごと検索結果を表示したい」の回答画像5](http://oshiete.xgoo.jp/_/bucket/oshietegoo/images/media/9/667667_5497e45073120/M.jpg)
No.4
- 回答日時:
私はExcel2003を使用していますので2003での話を
させていただきます。
2007を使用していれば、聞き流してください。(同じかわからないので)
オートフィルタを使ってみては?
並べ替えをしておけば、まとめて別シートへのコピーもできるので。
データ→フィルタ→オートフィルタを指定して検索したいキーワードを
指定すれば表示できると思います。
No.3
- 回答日時:
検索する値がa列にあると仮定して
住所録のシートのj列に
=IF(ISERROR(FIND(別シート!A$1,A1)),"",ROW())
を入れて、下方向にコピー。
別シートのa1セルに検索したキーワードを入れて、
b列の5行目に=INDEX(Sheet1!A:A,SMALL(住所録!J:J,ROW(A1)),1)
を入れて、下方向にコピー。
c列に=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)
を入れて、下方向にコピー。
c列の式はf~iまで&でvlookupの式をつなげればよい。
以上。
あいまい検索でなければ、単純にvlookupをつなげればよいでのわ?
データは昇順にしよう。
No.2
- 回答日時:
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)
データ1: =INDEX(Sheet1!A:I,B2,B3)
データ2: =INDEX(Sheet1!A:I,B2,B3+1)
データ3: =INDEX(Sheet1!A:I,B2,B3+2)
データ4: =INDEX(Sheet1!A:I,B2,B3+3)
お探しのQ&Aが見つからない時は、教えて!gooで質問しましょう!
似たような質問が見つかりました
- Excel(エクセル) ある範囲のセルに何を入力すると、別のセルに○を表示させる 3 2022/04/05 15:51
- Excel(エクセル) エクセルの数式で教えてください。 1 2023/02/08 09:05
- Excel(エクセル) Excelでの検索結果を含む行だけを表示させたい 5 2023/03/10 17:08
- 国産車 カーナビで名称登録をしても検索できません!住所もわからない場合は何か良い方法がありますか?名称登録の 1 2022/04/25 18:59
- Excel(エクセル) 下記エクセルの式がなぜこうなるのか理由が知りたいです。 6 2022/08/20 00:43
- Excel(エクセル) エクセルの数式で教えてください。 5 2023/02/10 15:11
- Excel(エクセル) エクセルで、 A1セルに「A」という値、 B1セルに「B」という値が入っています。 どちらも表示形式 5 2023/02/22 23:05
- Visual Basic(VBA) VBA 「,」・空白・カタカナ等の複数条件のマクロ 2 2023/08/23 11:57
- Visual Basic(VBA) エクセルVBAについて 2 2023/01/31 16:21
- Excel(エクセル) ユーザー定義について質問です。 2 2023/06/28 13:21
関連するカテゴリからQ&Aを探す
おすすめ情報
デイリーランキングこのカテゴリの人気デイリーQ&Aランキング
-
Microsoft365搭載Windows11PCへ...
-
Officeを開くたびの「再起動メ...
-
一太郎でカーソルを次の行の先...
-
英数字のみ全角から半角に変換
-
現在、PC2台でMicrosoft 365 Pe...
-
エクセルやワードを無料で使え...
-
Microsoft Formsの「個人情報や...
-
パソコン買い換え Office移行 ...
-
エクセルで英文字に入れた下線...
-
大学のレポート A4で1枚レポー...
-
excelの画面のグリッド線の消滅。
-
エクセルの貼り付け「リンクさ...
-
outlookで宛先が異なるメールを...
-
Excel 日付を比較したら、同じ...
-
office365って抵抗感ないですか?
-
会社PCのメールが更新されない
-
Microsoftにofficeアプリについ...
-
Office 2021 Professional Plus...
-
Windows 11で、IME言語バー(IM...
-
Office2021を別のPCにインスト...
マンスリーランキングこのカテゴリの人気マンスリーQ&Aランキング
-
【スプレドシート】IMPORTRANGE...
-
英数字のみ全角から半角に変換
-
「生産性ソフトウェア」とは何...
-
会社PCのメールが更新されない
-
【関数】○年○ヶ月と表示された...
-
WEBの記事を印刷する際にA...
-
エクセルでXLOOKUP関数...
-
Microsoft familyに追加されま...
-
会社のOutlookにてメールを予約...
-
Microsoft Formsの「個人情報や...
-
Microsoft365の一部を解約したい
-
マクロ自動コピペ 貼り付ける場...
-
Outlook で宛先が複数の場合の人数
-
outlookのメールが固まってしま...
-
【Excel VBA】PDFを作成して,...
-
大学のレポート A4で1枚レポー...
-
office365って抵抗感ないですか?
-
Microsoftにofficeアプリについ...
-
Excel テーブル内の空白行の削除
-
マイクロソフト 一時使用コード...
おすすめ情報