元データを別シートへ、種類で振り分ける。

質問

元データとして、以下のような表があります。
　受付No.　室No.　申込日　利用日　利用料　利用時間　団体名
これを別シートの室No.ごとの表に振り分けたいのです。
できれば、元データに入力すると連動して室No.の表も更新されるようにしたいのですが、どのようにすればいいのでしょう。

元データの室No.は１～3までランダムに入力してあります。
そこでIFとVLOOKUPなどで試してみましたが、上手くいきません。
VBAでのDO～LOOPを使うのがいいような気がするのですが、VBAはまったくの初心者で参考書を読んでいるのですが、よくわかりません。

できれば関数で処理をしたいのですが、無理ならばVBAでも構いません。
振り分けるのにいい方法を教えていただけますでしょうか。

回答No.3

imogasi

関数では該当分の抜き出しは不得手です。
ＶＢＡ向きの問題です。
関数では＃１のご回答のような理解には相当、関数になれないと、難しいです。
ーーー
私はわかりやすくするため、中間作業列を使う、自称　imogasi方式　を提唱してます。（ＷＥＢで照会してもらえば、沢山過去の質問回答が出ます）
ーーー
以下も相当理解は難しいかも知れませんが、作業列を使わない方法を
上げます。
例データ　Ａ１：Ｂ２０（データは現在１０行の例）
室Ｎｏ 利用者名
1 a
2 b
3 c
3 d
2 e
5 f
2 g
2 h
3 i

同一シートのＦ２に
室Ｎｏが２のものを抜き出す例
=SMALL(IF($A$1:$A$20=2,ROW($A$1:$A$20),),20-COUNTIF($A$1:$A$20,2)+ROW(A1))
と入れてＳＨＩＦＴ＋ＣＴＲＬ＋ＥＮＴＥＲ（３つのキーを同時押し）配列数式です。
下へ複写する。
3
6
8
9
#NUM!
#NUM!
ーーー
上記の式の説明
A1:A2の値の中で＝２のセルがあれば、その行の行番号を仮想エリアに記録すると考える。
この場合、２である行の行番号３，６，８，９の４つと、＝２でない０が１６個です。
この場合の４つはCOUNTIF($A$1:$a$20,2）で求まる数です
０でないものは、２０個のうち小さい順では１６＋１＝１７番目、次ぎは１８番目、次ぎは１９番目、次ぎは２０番目に
ＳＭＡＬＬ関数で小さい物を取ると、行番号が小さいものが取れる。それは上の行から取り出すことに当たる。
次々と番目数に１を加えるのは＋ＲＯＷ（Ａ１）で実現できる。式を複写するとＡ２，Ａ３・・と番号が増えるから。

Ｂ列のデータを採るのは、=INDEX($A$1:$B$20,(上記で求まる行）,2)

あと残る問題は、５行以下に＃ＮＵＭが出ることを防ぐ必要があるが
これはＡ列で２の数COUNTIF($A$1:$A$20,2)より、行数が大になれば（ＩＦで判別）、空白を返すようにする。
Ｇ２に
=INDEX($A$1:$B$20,SMALL(IF($A$1:$A$20=2,ROW($A$1:$A$20),),20-COUNTIF($A$1:$A$20,2)+ROW(B1)),2)
と入れてＳＨＩＦＴ＋ＣＴＲＬ＋ＥＮＴＥＲ（３つのキーを同時押し）配列数式です。
下へ複写する。
結果
3 b
6 e
8 g
9 h
#NUM! #NUM!
#NUM! #NUM!
エラーを消すには
Ｈ２に
=IF(ROW(H1)>COUNTIF($A$1:$A$20,2),"",INDEX($A$1:$B$20,SMALL(IF($A$1:$A$20=2,ROW($A$1:$A$20),),20-COUNTIF($A$1:$A$20,2)+ROW(C1)),2))
と入れてＳＨＩＦＴ＋ＣＴＲＬ＋ＥＮＴＥＲ（３つのキーを同時押し）配列数式です。
下へ複写する。
ーーーー
Ｓｈｅｅｔ２へ結果を出す方法
Ｓｈｅｅｔ１を参照している部分はＳｈｅｅｔ１！を番地の前につければ良い。右最後の部分のROW(C1)のｃ１は結果を出すシートの行数なので、シート名をつけなくて良い。
ーーー
＃１のご回答や、上記でも、質問者は関数で抜き出したいですか。

回答No.2

cafe_au_lait

＞勝手にセルが結合され、結合されたセルが青い罫線で枠組みされます。

結合というのは、いわゆる「セルの結合」ではないですよね？関数によってセルが結合されることはないと思うのですが・・・。循環参照ではないかとも思うのですが、確信が持てません。

・実際のシート名やセル位置をお教えください。こちらではシートが「元データ」「室No.1」「室No.2」「室No.3」とあり、それぞれ1行目に見出し、2行目から下にデータがつづくものとしています。
・提示した式を改変したのであれば、そのまま載せてください。
・「結合」の様子をもう少し具体的に説明していただけませんか？また、「結合」したセルはどこでしょうか？

※数式の意味

新規ワークシートのA1:B8に、No.1の回答に示した元データ（見出し含む）を入力します。
次に、以下の式を入力して下にコピーします。
C2:=B2=1
D2:=C2/ROW(D1)
E2:=LARGE($D$2:$D$8,ROW(E1))
F2:=1/E2

これらを一つにまとめ、IF文でエラー回避を行ったのが元の式になります。
ちなみに、ワークシート分析の「エラーのトレース」または「数式の検証」で、計算過程が確認できます（たしかエクセル2002以降）。なお、この数式を確認するときは式中の100を10程度にした方が見やすいと思います。

回答No.1

cafe_au_lait

元データのシート

A B
受付No. 室No.
1 1
2 2
3 3
4 1
5 2
6 1
7 3

室No.1のシートの受付No.

A2:=IF(ROW(A1)>COUNTIF(元データ!$B$1:$B$100,1),"",INDEX(元データ!$A$1:$A$100,1/LARGE(INDEX((元データ!$B$1:$B$100=1)/ROW(元データ!$A$1:$A$100),),ROW(A1))))

下にコピーします。

結果

A
受付No.
1
4
6

他の項目はA行の数値を元にVLOOKUP関数なりで処理してください。
他の室No.については、$B$1:$B$100,1、$B$1:$B$100=1の部分の"1"を変えてください。

補足

すみません。コピーしてみましたが上手くいきません。
勝手にセルが結合され、結合されたセルが青い罫線で枠組みされます。

関数を勉強し始めたばかりなので、それぞれの関数（例えばROWやCOUNTIF）などの意味はわかります。
しかしこのようにつながると、いったいこの数式は何を支持しているのかわかりません。
この数式について、もう少しわかりやすく説明していただけないでしょうか？