excelで検索用シートを作りたい。

質問

お世話になります。
excelでわからない事があり、困っています。

検索用シート　　　　　　データシート

　　　Ａ　　Ｂ　　　　　 Ａ　　 　Ｂ　　　Ｃ
1　部署名　(空欄)　　1　部署名　担当者　書類名
2　担当者　(空欄) 2　総務部　鈴木　　新人用の書類
3　書類名　(空欄) 3　人事部　田中　　部長の書類

上図の様に検索用シートとデータ用シートが同じBook内にあります。
検索用シートのB列は空欄になっており、例えば(1-B)に検索したい部署名の一部、又は全てを入れると、(2-B)及び(3-B)にその部署名に対応した、担当者、書類名が表示

される。
また、検索結果が複数ある場合には、上のものから順々に検索用シートの空欄に表示される。
部署名での検索以外にも、同様に担当者、書類名でも検索できる。
この様な仕様にしたいのですが、どのすれば良いでしょうか？
どうぞよろしくお願い致します。

回答No.3

hallo-2007 · ベストアンサー

No1です。
>検索用シートの形式は変更せずにおきたいのですが
関数の配置は自由にできますよ。
総務部を検索した際に対象が1個だけなのでしょうか。
>検索結果が複数ある場合には、上のものから順々に検索用シートの空欄に表示される。
先の回答は複数あっても一覧表で表示する方法です。
もし、ひとつでよければ
B2セルには　=VLOOKUP(B1,データ!A:C,2,FALSE)
B3セルには　=VLOOKUP(B1,データ!A:C,3,FALSE)
で十分です。
>部署名での検索以外にも、同様に担当者、書類名でも検索できる。
これも対象がひとつであれば、INDEX関数とMATCH関数で検索する方法もあります。
http://oshiete1.goo.ne.jp/qa4424865.html
ここで詳しく説明しておきましたの参考にしてください。
先にご紹介した印刷時にオートフィルターのVBAで印刷しない方法で絞込みでも十分ではないでしょうか？
自由な検索と言われるとオートフィルターの操作を覚えてください　が良いと思います。
VBAで操作するにも、どういう検索を行いたいのか設定して検索とか操作の機能を追加していくことになるので、作業が少なくなる効果はあまり期待できないと思います。
最も頻繁に使用する検索に絞り込んだ方が便利ではないでしょうか？

お礼

ありがとうございました。
教えて頂いた方法でなんとかなりました。

回答No.2

imogasi

VBAだけでやる方法を考えたいが、質問者はそれを望んでいないのだろう。だから私としては始めての、折衷案のタイプの回答を挙げてみる。色々検証して見てください。
質問者の望むシートの体裁を出来るだけ尊重したため、こうなったの部分はある。
マクロが出てくるが、マクロの記録なので恐れることは無い。
ーーー
Sheet1　原本シート
A列ーーーB列ーーーC列　　
部署名 担当者 書類名
総務部 鈴木 新人用の書類
人事部 田中 部長の書類
業務部 井上 日報
事務部 市川 手引き
Sheet2
A1:A3
A列
部署名
担当者
書類名
J1:L2
部署名 担当者 書類名
田中 ＜－A2に田中を入れた場合の例
J2は　=IF(B1="","",B1)
K2は　=IF(B2="","",B2)
L2は　=IF(B3="","",B3)
縦のものA1:A3を横にしただけ（フィルタオプション利用のためこうせざるを得ない）
ーー
Sheet2の
D１：F１に
D列　　E列　　　F列
部署名 担当者 書類名
項目見出しを用意＜ーSheet1からコピー
（フィルタオプション利用のためこうせざるを得ない）
ーーー
Sheet2（ここが肝心）で、データーフィルターフィルタオプションの設定を操作し、マクロの記録をとる
すると
Sub Macro2()

Sheets("Sheet1").Range("A1:C100").AdvancedFilter Action:=xlFilterCopy, _
CriteriaRange:=Range("J1:L2"), CopyToRange:=Range("D1:F100"), Unique:=False
End Sub
Sheets("Sheet1").Range("A1:C100").の100と
CopyToRange:=Range("D1:F100"), の100は行を増やしすため変更した結果です
＾－－－
Sheet2にコマンドボタンを１つ貼り付け。
クリックイベントに
Private Sub CommandButton1_Click()
Macro2
End Sub
またはmacro2をボタンに登録するのも同じ。＜－初めてならWEBで「マクロ　ボタン　登録」で照会のこと。
ーー
Sheet2のA1:A3に検索データ（条件）をいれ、ボタンをクリック。
D２：F○に結果データが出る。
結果例
部署名 事務部 部署名 担当者 書類名
担当者 山田 事務部 山田 事務引継ぎ
書類名
ーー
データでは事務部は２人いるが、山田の方を引いてきている。
２条件が効いているということ。

補足

回答ありがとうございます。
参考になりました。
しかし、検索用シートの形はできる事なら崩したく無いので、他に何か方法は無いでしょうか？
VBAは初心者ですが、それだけでやる方法でも構いません。

回答No.1

hallo-2007

作業列使用した方法ですが
　　データシート
　Ａ　 　Ｂ　　　Ｃ　　Ｄ
1　　　部署名　担当者　書類名
2　　　総務部　鈴木　　新人用の書類
3　　　人事部　田中　　部長の書類

検索用シート
　　　Ａ　　Ｂ
1　部署名　(総務部)　
2　担当者　書類名
3
と仮定して、データシートにＡ列を挿入　=IF(B2=検索!B$1,ROW(),"")
といれて下までコピィすれば、総務部の行にその行番号が表示されます。
検索シートの
A3セルに
=IF(ISERROR(SMALL(データ!$A:$A,ROW(A1))),"",INDEX(データ!C:C,SMALL(データ!$A:$A,ROW(A1))))
といれて。右へコピィ、下へコピィします。
検索シートのB1と一致するデータの一覧になります。
作業列のIF文変更すれば、一部合致でも可能でしょう。
データシートの作業列は目障りなら非表示（或いは文字色を白）にしてください。

補足

回答ありがとうございます。
検索用シートの形式は変更せずにおきたいのですが、VBAを使用いたものでも構わないので、他に方法は無いでしょうか？？