オートフィルタの機能を別方法で出来ないでしょうか？

質問

A B C D
コード 会社名 電話番号 FAX番号
1
2
3
のデータをオートフィルタ オプションの抽出条件の中の「含む」の
使用でA～Dをそれぞれ検索しておりますが、VLOOKUPのように、セルに入力できて、さらにヒットする行を複数表示の抽出は出来ないでしょうか？
希望は別シートです。
宜しくお願い致します。

回答No.4

imogasi

質問の意味がこういうことですか
Sheet1で
A列　　　B列（関数の結果）
asdf 　1
wers
was 　2
sdfgas 　3
aqwer
zxc
sdfgasd　　4
Å列のA2:A8に上記データがあるとします。空き列に（このケースでは
B列B2に）
=IF(ISERROR(FIND("as",A2)),"",MAX($B$1:B1)+1)
と入れてB8まで式を複写します。結果は上記B列の通り。
これは「as」を含む行を抜き出すつもりです。
セルA1に条件asのようなものを入れるなら、asを＄A$1のようにすると
一般性を失わなくできます。
該当行に上から連番を振ったことになります。
この１，２，３・・を元に、Sheet2の第１行でSheet1のB列の１、２、３・・を探してその行Sheet1のデータをSheet2に持ってきます。
関数は、imogasi方式と称して沢山回答してますので、検索してください。INDEX関数とMATCH関数を使います。
４列（４項目）有るとワーク列を４つ必要なのが欠点ですが。
項目が４つ、コード、会社、電話、FAX有る場合は、列を指定する
数を指定すれば、上記の連番を振る式を１つにできそうです。

お礼

ご回答有難うございます。

回答No.3

Wendy02

こんばんは。

#1 さんのご指摘の場合、ワークシートで使う場合は、その後、MS-Query から、SQLを書き換えて、パラメータクエリにする方法もありますが、ちょっと、難しいと思います。

やはり伝統的(20年前ぐらいから)には、フィルタ・オプションがあります。(注意：オートフィルタではありません/時々勘違いする人がいます。)

1.イベント・マクロを貼り付ける

左下のSheet2 のタブを右クリック

[コードの表示]をクリック

以下を貼り付けます。

Private Sub Worksheet_Change(ByVal Target As Range)
Const DB As String = "A1"　'データの範囲の左上端
Const CRT As String = "G1"　'抽出条件の先頭(シートに条件を入れる場所)
Const EXT As String = "J1:M1"　'出力先の列(一行/4列－1～4列まで可)

If Target.Address <> Range(CRT).Offset(1).Address Then Exit Sub
Application.EnableEvents = False

Range(DB).CurrentRegion.AdvancedFilter _
　　　　 Action:=xlFilterCopy, _
　　　　 CriteriaRange:=Range(CRT).CurrentRegion, _
　　　　 CopyToRange:=Range(EXT), _
　　　　 Unique:=False
Application.EnableEvents = True
End Sub

貼り付けて、場所等に問題ないのでしたら、Alt + Q で、画面を閉じます。
なん、データの範囲等は、シート名は入れないでください。

2.抽出条件の代入

G1 に、(A列項目行)　「コード」と入れる
G2 に、抽出条件を入れる

抽出条件の中の「含む」
場合は、

TOKYO の場合の、Kを含む：「 *K* 」 と、アスタリスク(*)のワイルドカードをつければ、含むになります。また、Tから始まる：「T*」ということになります。試してみてください。やったことがない人は、一度は、試してみる価値はあると思います。

お礼

ご回答有難うございます。

回答No.2

Nouble

任意の1コードに対し、該当する会社名・電話番号・FAX番号などは
当然1葉なのですよね？

何かコードを特定するとその他の情報は芋づる式に紐付けされているので
検索時のキーとしてはコードのみで構わないのですよね？

INDEX構文や他にも数々のパターンが考えられるのですが

一例としてこういうのでは如何でしょうか？

お示し頂いた表が此処では仮にシート名「シート1」のA1:D3に
検索キーが検索値を表示させたいシートのA1に
あるものとします

検索値を表示させたいしーとのB1に
=IF(OR(OFFSET(シート1!$A$1,0,COLUMN(B1)-1,1,1)="",ISNA(VLOOKUP($A1,シート1!$A$1:$A$3,1,0))),"",VLOOKUP($A1,OFFSET(シート1!$A$1,0,0,ROW($D$3),COLUMN(B1)),COLUMN(B1),0))
と入力
必要量だけ右にコピーしてくだ…

としても良いのですが
そもそもoffset構文自体に参照能力があるので2度手間ですね　(~ ~;)ヾ

なので此処は式を
=IF(ISERROR(MATCH($A1,シート1!$A$1:$A$3,0)),"",OFFSET(シート1!$A$1,MATCH($F7,シート1!$A$1:$A$3,0)-1,COLUMN(B1)-1,1,1))
とした方が良いのかも知れません
上記と同様に必要量だけ右にコピーしてください

もし1コード1葉な表ではなく
検索はそれぞれに対し行わなければならない場合には
また補足にでもその旨申して頂ければ
新たな構文をお作り致しますので
忌憚なくお申し付けくださいね

お礼

ご回答有難うございます。

回答No.1

mshr1962

「データ」「フィルタ」「フィルタオプションの設定」を使うhttp://www.geocities.jp/c4731625/sousa/Pg000076.htm

「データ」「外部データの取り込み」「新しいデータベースクエリ」で
「excel file*」を指定する。
持って来るシート・範囲を　[>] で右の窓へ移動させてOK
条件を設定してOK
並べ替えの項目を設定してOK
貼り付けるセル（範囲の左上）を選択してOKで内容を抽出します。
※同ブック内の場合は保存してから行ってください。

お礼

ご回答有難うございます。
早速、クエリを勉強してやってみます。
他にもあれば教えてください。
宜しくお願い致します。