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
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ランキング
-
【スプレドシート】IMPORTRANGE...
-
英数字のみ全角から半角に変換
-
会社PCのメールが更新されない
-
マイクロソフト 一時使用コード...
-
Outlook で宛先が複数の場合の人数
-
【スプレッドシート】指定の日...
-
Microsoft Formsの「個人情報や...
-
Microsoft Edgeの「ニュースと...
-
VLOOKUP関数について
-
【Excel VBA】PDFを作成して,...
-
エクセルでXLOOKUP関数...
-
outlookのメールが固まってしま...
-
teams設定教えて下さい。 ①ビデ...
-
Microsoft365で写真をアルバム...
-
Microsoft Officeに似たキング...
-
会社におけるOfficeライセンス...
-
【スプレッドシート】白色のセ...
-
Excel VBA 日程表からスケジュ...
-
VBAファイルの保存先について
-
エクセル、ワード、ネット検索...
マンスリーランキングこのカテゴリの人気マンスリーQ&Aランキング
-
【スプレドシート】IMPORTRANGE...
-
マイクロソフト 一時使用コード...
-
英数字のみ全角から半角に変換
-
Office2021を別のPCにインスト...
-
Microsoft Formsの「個人情報や...
-
officeビジネス型のワードやエ...
-
会社PCのメールが更新されない
-
【スプレッドシート】指定の日...
-
Microsoft Officeを2台目のPCに...
-
何このステータスバー
-
2つのシートの一致する行のセ...
-
会社のTeamsのことで相談です。...
-
エクセルにリンクされるのをし...
-
Windows 11で、IME言語バー(IM...
-
office2010とoffice365の共存で...
-
Microsoftのパソコンです。 エ...
-
エクセルでXLOOKUP関数...
-
Excel関数について質問ですm(__)m
-
VBA
-
自分の専門分野の仕事。初見で...
おすすめ情報