excelで検索に最適な関数を教えて下さい。

質問

sheet1にデータがあります。
　　　　Ａ　　　　　　Ｂ　　　　　　Ｃ　　　　　　　　Ｄ
１　　県名　　　　会社名　　　　住所　　　　　　電話番号
２　埼玉県　　　山田商事　　　埼玉県…　　　　　０４－…
３　東京都　　　佐藤商事　　　東京都…　　　　　０３－…
４　東京都　　　中田商事　　　東京都…　　　　　０３－…
　　　…　　　　　　…　　　　　　…　　　　　　　　…
上記のデータが約５００有ります。

表示用sheet2の
Ａ１に入力規則を活用して、県名を（例えば東京都と）入力すると
Ａ２に東京都　佐藤商事　東京都…　０３－…
Ａ３に東京都　中田商事　東京都…　０３－…
が表示させるようにしたいです。

VLOOKUPだと中田商事の行が表示されず、上手くできませんでした。
オートフィルタだとsheetが違う点と、同じ県の会社が多いために、表示が縦に長くなるので見にくいです。
同じ県の会社は多くても５０くらいなので、できれば３０行くらいまで表示すると、続きはＥに県名、Ｆに会社名として表示できれば一画面に多く表示でき助かります。

関数でできると思うのですが、どうすれば良いでしょうか？
わかる方教えてください。よろしくお願いします。

回答No.2

imogasi · ベストアンサー

諸所に質問者の行っていることに疑問アリ。
＞VLOOKUPだと中田商事の行が表示されず、上手くできませんでした。
VLOOKUPやMATCH関数の常識。当たり前。
＞オートフィルタだとsheetが違う点と
Sheet2側でフィルタオプションの設定で出来るはず。
＞同じ県の会社が多いために、表示が縦に長くなるので見にくいです
何のことだか。
該当が多いと抽出行数が増えるのは当たり前では。
一般には、データがA-D列で終わるものではない。項目が多いと列方向にスクロールが必要であって、同じく不便。
＞続きはＥに県名、Ｆに会社名として表示できれば一画面に多く表示でき助かります。
こういうのは関数では苦手（式が複雑化するとか、普通は不可能と表現する部類。
VBAでも使うことになる。
ーーーーー
この質問も中身は１条件（県名）による抜き出し問題で（毎日のように出る質問で）、Googleで「imogasi方式」で照会すれば、多数出てくる。私の答えた方式に名づけているやり方だが、他の回答者の回答も沢山出るので参考になるでしょう。
何十回と答えているので、よければそちらを参考にしてください。
ざっと思いつくやり方は
（１）作業列を使う方法
（２）作業列を使わず、関数の組み合わせで１つの式で抜き出し
　　　式の意味を理解できればこれでよい。
（３）フィルタオプションの設定
（４）VBAで
　　　　　A.（３）をVBAか
　　　　　B.データ総当り判別抜き出し
　　　　　C。Findメソッドを使う
（５）クエリを使う（Msクエリ）
ーーー
ついでだから、フィルタオプションのことを書いておく
例データ
Sheet1　A-E列
1 県名 会社名 住所 電話番号
2 埼玉県 山田商事 埼玉県… ０４－…
3 東京都 佐藤商事 東京都… ０３－…
4 東京都 中田商事 東京都… ０３－…
Sheet2
A1に「県名」と入れる。
A2に条件の「東京都」など入力。
A3:D3に　見出しの
県名 会社名 住所 電話番号
をSheet1からコピー貼り付け
操作
データーフィルターフィルタオプションの設定
指定した範囲を選択
リスト範囲　Sheet1のデータ範囲を選択　Sheet1!B1:E4
検索条件範囲　Sheet2の上演範囲を選択　Sheet2!A1:A2
抽出範囲　Sheet2の範囲を選択　　Sheet2!A3：D100（余分目に）
OK
結果　Sheet2に
県名
東京都
県名 会社名 住所 電話番号
東京都 佐藤商事 東京都… ０３－…
東京都 中田商事 東京都… ０３－…

回答No.1

soixante

オートフィルタにして、ウィンドウ枠固定ではダメでしょうか。
（ウィンドウ枠固定方法＝Sheet1のA2のセルを選択の上、Alt+W、F　です。戻すのはもう一度同じ操作。）

何らかの理由で上記ではご意向を満たしていないのならば。
関数では難しいような気がしました。
私ならばマクロを使うと思います。
あまり詳しくは無いのですが、もしよろしければ以下お試しください。
ご質問文から、以下の形式を前提にしています。
・元データシート名＝Sheet1、表示用シート名＝Sheet2
・Sheet1　はA1から、県名、会社名、住所、電話の４つのデータであること
・Sheet2　は、A1に検索したい県名が入力されるということ
--------------------------------------------------
Sub 県別表示()

Dim Ken As String
Dim p As Integer, q As Integer, r As Integer
Dim Ws1 As Worksheet, Ws2 As Worksheet
Set Ws1 = Worksheets("sheet1")
Set Ws2 = Worksheets("sheet2")
p = 2
Ken = Ws2.Cells(1, 1)
Ws2.Cells.ClearContents
Ws2.Cells(1, 1).Value = Ken
Range(Ws1.Cells(1, 1), Ws1.Cells(1, 4)).Copy Ws2.Cells(2, 1)

Do While Ws1.Cells(p, 1) <> 0
　　If Ws1.Cells(p, 1).Value = Ken Then
　　　　q = Ws2.Cells(Rows.Count, 1).End(xlUp).Row + 1
　　　　If q < 31 Then
　　　　　Range(Ws1.Cells(p, 1), Ws1.Cells(p, 4)).Copy Ws2.Cells(q, 1)
　　　　ElseIf q >= 31 Then
　　　　　Range(Ws1.Cells(1, 1), Ws1.Cells(1, 4)).Copy Ws2.Cells(2, 5)
　　　　　r = Ws2.Cells(Rows.Count, 5).End(xlUp).Row + 1
　　　　　Range(Ws1.Cells(p, 1), Ws1.Cells(p, 4)).Copy Ws2.Cells(r, 5)
　　　　End If
　　End If
　　p = p + 1
Loop
Ws2.Select
Cells(1, 1).Select

Set Ws1 = Nothing
Set Ws2 = Nothing

End Sub
----------------------------------------------------

念のため使い方は以下です。
【１】エクセル上で、Alt+F11　を押す。VisualBasic（以下VBE画面）というのが出ます。
【２】VBE画面で、Alt+I、M　と押す。真っ白な画面が出ます。
【３】その画面に上記の---線から---部分をコピペ。線含まず。
【４】VBE画面を閉じて、エクセルに戻る
【５】試しにSheet2のA1セルに何か県名を入れたうえで、Alt+F8.
【６】マクロというボックスが開きますので、「県別表示」を選択して実行。（Alt+F8　の後に　Tab、Enter　だとマウス要らず）

※これからこのファイルを開ける時には、マクロ有効にするかどうか聞かれますので、「有効」で開いてください。

※マクロ名「県別表示」が都合が悪ければ、書き換えてください。VBE画面を開き、左のウィンドウに出てくる、Module1というところに先ほどのコードがありますので、最初のSub　の後ろを書き換えればOKです。カッコは残す。

※「試してみたけど、やっぱりだめ、このマクロを削除したい」と言う場合は、VBE画面を開き、左のウィンドウに出てくるModule1　の上で、右クリック、「Module1の開放」、エクスポートしますか？と聞かれますので、「いいえ」。これでマクロを削除できます。

長文失礼しました。

補足

回答ありがとうございました！！！

希望に叶うものができました。大変助かります。

ところで質問があるのですが…
(1)３０行で改行されるのですが、今後データが増え６０行・９０行で改行するようにするのは、構文のどこの部分を変えれば良いでしょうか？

(2)＞Ａ１に入力規則を活用して、県名を（例えば東京都と）入力すると
＞Ａ２に東京都　佐藤商事　東京都…　０３－…
＞Ａ３に東京都　中田商事　東京都…　０３－…
と書いていましたが
Ａ１に入力規則を活用して、県名を（例えば東京都と）入力すると
Ａ２に佐藤商事　東京都…　０３－…
Ａ３に中田商事　東京都…　０３－…
としたいです。

(3)例えば広島・岡山・山口と数県を検索表示できればしたいです。
案としてはsheet2のＡ１広島に加え、Ｂ１に岡山・Ｃ１に山口を入力すると三県の検索結果が表示できれば…と思います。