Excelをデータベースのように使用した場合 データの抽出

質問

Ｅｘｃｅｌをデータベースのようにして使用している場合について教えて欲しいことがあります。

　・Sheetを２つ作成したとします。
　・Sheet1はたくさんのデータが入っているシートです。
　・Sheet1の名前を仮に『Data』とします。
　・Sheet2はSheet1から必要なデータを抽出してきて並べるシートです。
　・Sheet2の名前を仮に『Report』とします。
　・『Data』のA列には1～200までの数字が入力されています。
　・その数字は　ランダムである上に　重複していることもあります。
　・例えばA1には「1」が入力されていて　A2には「2」が入力されています。
　・しかし　その後A3にもA4にもA5にも「2」が入力されています。
　・同様にA6～A15までは「3」が入力されているのです。
　・このようにA列の数字は　同じ数字が何度も重複しており　その重複には規則性はありません。
　・『Report』シート上で『Data』シートからデータを抽出したいのですが　A列に「1」と入力されているデータだけを取り出すのなら「VLOOKUP」でもできますよね・・・・
　・しかし　A列に「2」と入力されているデータを「VLOOKUP」関数で選んでも　その先頭行のデータしか読み込んできません。
　・『Data』シートのA列に入力している数字から　必要な数字が入力されている行のデータだけを　全て『Report』シートに並べようとした場合　手作業で行うとすると・・・
『Data』でA列が「1」のデータだけを選び，それを『Report』シートに貼り付ける。次は「2」で実行する。・・・・・という作業を200回繰り返すことになりますが
簡単に行うにはどのようにすればいいのでしょうか？

マクロについては　あまり詳しくないので
できれば関数を使いたいのですが　無理でしょうか？
マクロを使うのであれば　分かりやすく教えていただきたいのですが・・・

勝手なお願いで申し訳ありませんが　よろしくお願いします。

回答No.4

imogasi · ベストアンサー

#3です。
Sheet1に足した連番（D列）は１をスタートにして、２，３，４・・と振っています。
M6から１をまず探すにはROW()ーー>６ですから、Row()-5に書き換えて最初が１になるように数を調整してください。すると
Sheet2の
M列６行目　6-5=1 -->Sheet1のT列で、1をMATCH関数で探す
M列７行目　7-5=2　-->〃　2を探す
M列８行目　8-5=3　-->〃　3を探す
・・
となります。

お礼

お礼が遅くなって申し訳ありません。
ありがとうございます。

ついでに・・・　あと一つ教えていただけませんか？

データシートのデータはA6～R275までです。
Sheet2には
M6からデータを書き込むようにしたいのです。

つまり　Sheet1のA列～R列までを　Sheet2のM列～AD列に書き込みたいのです。

このような場合は　式のどの部分を書き換えればいいのでしょうか？

よろしくお願いします。

回答No.3

imogasi

質問が、冗長すぎる。回答者は初心者ばかりではない。くだくだ書かなくても、「A列に重複データがあります」だけでわかる。
本来
エクセルは
操作
関数
VBA
などの解決法があるが、本筋は「操作」だ。それを輪売れないこと。
データーフィルターフィルタオプションの設定などが適当ではないかな。
データベースクエリのご紹介もあるが、アクセスなどの経験がないと難しいのでは。
関数だけで抜き出すご回答も有るが、私の自称imogasi方式を書いてみる。
＃２のご回答の式の意味が理解できれば、下記は読まなくても良い。
ーーーー
Sheet1
A2:B2　A,B列データ　　D列作業列
A列　　B列　　　　　　D列
1 a 1
1 b 2
2 c
3 d
1 e 3
3 f
2 g
1 h 4
4 i
2 j
ーーー
D２の式は
=IF(A2=Sheet2!$A$1,MAX($D$1:D1)+1,"")
下方向に式を複写
ーー
Sheet2
A1に１、や２．３の抜き出す条件の値を入れる。
A2に　=INDEX(Sheet1!$A$2:$B$100,MATCH(ROW()-1,Sheet1!$D$2:$D$100,0),COLUMN())
右へ式を複写。
あ２：ｂ２を下方向に式を複写。
結果
1
1 a
1 b
1 e
1 h
１を２に変えると即座に
2 c
2 g
2 j
#N/A #N/A
このエラーを出さない方法は、Googleでimogasi方式を照会すればでてくる私の回答の中に載っている。長くなるので略。
Sheet1のD列のMAXの値より大なら空白にするIF関数を前にかぶせる。

お礼

ご回答ありがとうございます。

すみません。長々とした質問で・・・
こちらがシロウトなもので
こんな表現しかできませんでした。

結局　このimogasi方式というのを　使わせていただくことにしました。
しかし　関数の意味が理解できていないので（スミマセン　初心者で）
ちょっと　うまくできない部分がありました。

データシートのA6～R275までがデータなので
作業列はT6～T275までつくりました。
T6には　=IF(A6=Sheet2!$A$1,MAX($T$5:T5)+1,"")　でうまくいくと思います。

しかし
Sheet2のJ5に抜き出す条件の値を入れるようにして
M6からデータを書き込むようにするには

M6に　=INDEX(Sheet1!$A$6:$R$275,MATCH(ROW()-1,Sheet1!$T$6:$T$275,0),COLUMN())　では　うまくいきません。

どこがダメなのでしょうか?

回答No.2

mitarashi

ここ１，２ヶ月の間にこのサイトで見た回答を参考にさせていただいております。オリジナルのURLを記録してなかったので、再現してみました。
シート名、データベースのデータ範囲はご自分の環境に合わせて修正願います。
データベースのシート
.......A.......B.......C.......D
..1.................1.....あ.......a
..2.......2.......2.....い.......b
..3................6.....う.......c
..4.................1.....え.......d
..5.......5.......2.....お.......e
..6................3.....か.......f
..7................4.....き.......g

A1の式=IF(B1=Sheet1!$A$1,ROW(),"")、以下下方に複写
データはB列以降に置く

検索するシート
............A...............B...............C
..1...............2.............い...............b
..2..............................お...............e
..3........................#NUM!.......#NUM!
..4........................#NUM!.......#NUM!
..5........................#NUM!.......#NUM!

A1：検索する値
B1の式=INDEX(Sheet2!$B$1:$Z$7,SMALL(Sheet2!$A$1:$A$7,ROW(A1)),COLUMN(B1))
以下、列方向、行方向に複写
#NUMが気に入らない場合は（普通は嫌ですね．．）条件付書式で白色フォントにする等してください。
式にエラー処理を盛り込みたければ、ご自分でなさって下さい。

お礼

ご回答ありがとうございます。

何とか　希望どおりにできそうです。
ありがとうございました。

回答No.1

mitarashi

下記は検討されたのでしょうか？A列の値だけで抽出なら、関数で可能でしょうが、より複雑な条件になるとこれらの出番になると思います。
１．フィルタオプション
http://www.eurus.dti.ne.jp/~yoneyama/Excel/filter3.htm
２．データベースクエリ
http://www.eurus.dti.ne.jp/~yoneyama/Excel/filter2.htm
（Officeインストール時にMS Queryをインストールしてある、または、追加でインストールする必要あり）

お礼

ご回答ありがとうございます。

フィルタオプションを使えば　確かに抽出ができました。
しかし　A列の数字でデータを抽出して表に表す場合
何度も　実行させるためには　マクロが必要ですね。

個人的には　VLOOKUP関数のように
特定のセルに数値を入力すると　瞬時に表示してくれるのを期待していたのですが・・・

ありがとうございました。