エクセルで月ごとの複数データを別シートに抽出したい

質問

エクセル2007で、sheet1に元となるデータがあり、その中からsheet2に月毎に列でまとめて、しかも日付順に並ばせたいと思っています。

まず、下記がsheet1のデータ例です。
--------------------------------------------
[sheet1]
　　　A列　　　　　B列　　　　　　　　　　C列　　　　　　D列
1　　名前　　　　　レンタル希望　　　　血液型　　　　担当
2　　山田太郎　　2013/9/12　　　　　A型　　　　　　営業　
3　　鈴木花子　　2013/8/1　　　　　　B型　　　　　　開発
4　　山下健　　　　2013/9/10　　　　　　O型　　　　　広報
--------------------------------------------
上記のような、随時追加・修正されていくデータです。

それを、下記のようにsheet2にB列（レンタル希望日）の月で検索し、B列の日付順でA列（名前）とB列（レンタル希望日）を随時表示させていきたいです。
--------------------------------------------
[sheet2]
　　　A列　　　　　　B列　　　　　　　　　C列　　　　　　D列
1　　8月レンタル　　　　　　　　　　　　9月レンタル
2　　名前　　　　　　レンタル希望　　　名前　　　　　レンタル希望
3　　鈴木花子　　　2013/8/1　　　　　山下健　　　　2013/9/10
4　　　　　　　　　　　　　　　　　　　　　山田太郎　　　2013/9/12
--------------------------------------------

このような形でsheet1を変えたものをレンタル希望日を基点にとして
随時並ばれていて、この月はこのぐらいの希望がこの人からあるのか（あったのか）
というのがわかるような管理表を作りたいと思っています。

フィルタオプション等をいろいろ試してみましたがうまくいかず、
また、なかなかマッチするQAが見つからず困っています。。。

どうかよろしくお願いします！

回答No.5

tom04

こんばんは！
極端にデータ量が多い場合はおススメしませんが・・・

↓の画像で説明します。
上側がSheet1、下側がSheet2とします。

Sheet2のA1セル（B1セルと結合しています）とC1セル（D1セルと結合）の表示形式に手を加えます。
A1～C1セルを範囲指定　→　右クリック　→　セルの書式設定　→　表示形式タブ　→　ユーザー定義
→　G/標準　と表示されているところを消して
0月レンタル
と入力し、OK
そしてA1・C1セルには単に月の数値のみを入力します。

次にSheet2のA3セルに
=IFERROR(INDEX(Sheet1!A$2:A$1000,MATCH(SMALL(IF(MONTH(Sheet1!$B$2:$B$1000)=$A$1,Sheet1!$B$2:$B$1000),ROW(A1)),Sheet1!$B$2:$B$1000,0)),"")

これは配列数式になりますので、Shift＋Ctrl＋Enterで確定！
この画面からコピー＆ペーストする場合はA3セルを選択　→　数式バー内に貼り付け　
→　そのまま（編集可能のまま）Shift＋Ctrlキーを押しながらEnterキーで確定！
数式の前後に｛　｝マークが入り配列数式になります。
これを隣のB3セルまでオートフィルでコピー！

C3セルには
=IFERROR(INDEX(Sheet1!A$2:A$1000,MATCH(SMALL(IF(MONTH(Sheet1!$B$2:$B$1000)=$C$1,Sheet1!$B$2:$B$1000),ROW(A1)),Sheet1!$B$2:$B$1000,0)),"")
（これも配列数式です）
として隣のD3セルまでコピー！

B3・D3セルの表示形式を「日付」にして
最後にA3～D3セルを範囲指定　→　D3セルのフィルハンドルで下へコピー！
これで画像のような感じになります。

※　同一日がある場合は最初の行が重複して表示されると思います。m(_ _)m

回答No.4

KURUMITO

分かり易い方法は作業列を作って対応する方法です。
シート１のＦ２セルには次の式を入力してＩ２セルまで横にドラッグコピーしたのちに下方にもドラッグコピーします。

=IF($A2="","",INDEX($A:$D,MATCH(SMALL($B:$B,ROW(A1)),$B:$B,0),COLUMN(A1)))

これによってすべてのデータは日付について昇順で並べられます。
ところでシート２ですがお示しのような表にしますがＡ１セルに入力する８月レンタルというのは2013年８月レンタルのように入力します。Ｃ1セルには2013年9月レンタルのように入力します。左の横列方向には他の年月のレンタル文字が入力されても対応することができます。
なお、年や月の数字は必ず半角英数文字にします。
2行目はお示しのように項目名を並べます。
ところでシート2を完成するためにシート1ではさらに作業列を追加します。
Ｊ2セルには次の式を入力して下方にドラッグコピーします。

=IF(COUNTIF(Sheet2!$1:$1,YEAR(G2)&"年"&MONTH(G2)&"月レンタル"),MATCH(YEAR(G2)&"年"&MONTH(G2)&"月レンタル",Sheet2!$1:$1,0)*1000,"")

さらにＫ2セルには次の式を入力して下方にドラッグコピーします。

=IF(J2="","",J2+COUNTIF(J$2:J2,J2))

そこでシート2ですがＡ3セルには次の式を入力したのちに右側方向にドラッグコピーしたのちに下方にもドラッグコピーします。

=IF(COUNTIF(Sheet1!$K:$K,IF(MOD(COLUMN(A1),2)=1,COLUMN(A1)*1000,(COLUMN(A1)-1)*1000)+ROW(A1)),INDEX(Sheet1!$F:$G,MATCH(IF(MOD(COLUMN(A1),2)=1,COLUMN(A1)*1000,(COLUMN(A1)-1)*1000)+ROW(A1),Sheet1!$K:$K,0),MOD(COLUMN(A1)-1,2)+1),"")

どんなにデータが多くなっても計算に負担が少ない方法としてお勧めです。
作業列が目障りでしたらそれらの列を選択したのちに「非表示」を選択すればよいでしょう。

お礼

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

sheet1のデータが列も行も追加していき膨大なデータになりそうだったので、
作業用のシートを作成し、そちらで作成してみました。
ただ、”レンタル希望日”が重複したデータの場合、すべて同じ名前が表示されてしまいます。

こちら解消方法はありませんでしょうか？

回答No.3

MackyNo1

>このところで、列ラベルとΣ値の両方に「レンタル希望」を入れる方法がわかりませんでした。

フィールドリストの中の「レンタル希望]の部分にカーソルを置いて、そのまま左クリックでその下の列ラベルにドラッグすれば、列ラベルに「レンタル希望」が入ります。同様にもう一度「レンタル希望]の部分にカーソルを置いて、そのまま左クリックでΣ値の部分にドラッグしてください。

回答No.2

MackyNo1

参考までに。

関数で対応するなら、例えば以下のような数式になります。

８月のデータを表示するなら、添付画像のA1４セル、B14セルに以下の式を入力します。

A14セル：=INDEX($A:$A,SMALL(INDEX((MONTH($B$2:$B$10)<>8)*1000+ROW($A$2:$A$10),),ROW(1：1)))&""
B14セル：=IF(A14="","",SMALL(INDEX((MONTH($B$2:$B$10)<>8)*100000+$B$2:$B$10,),ROW(1:1)))

９月のセルには上記の数式の「8」を「9」に変更してください（セル参照してもよい）。

回答No.1

MackyNo1

ご希望の表示は関数でも対応できますが、配列数式を使うと数式が煩雑でメンテナンスできなくなる可能性があるだけでなくシートの動きが重くなるなどのデメリットがあります。

このようなケースでは（ただし同じ月に同じ人が２回以上のレンタル希望日が無い場合）ピボットテーブルを使用されることをお勧めします。

「挿入」「ピボットテーブル」で行ラベルに「名前」列ラベルとΣ値に「レンタル希望」をドラッグし、作成された列ラベルの日付の上で右クリックし「グループ化」で「月」を選択します。
次にデータフィールドの上で右クリックし「値フィールドの設定」で「最大値」を選択し、最後にこのフィールド全体を選択して右クリック「セルの書式設定」から日付の表示形式を設定します。
最後にピボットテーブル上で右クリックし、「ピボットテーブルオプション」の集計とフィルタタブで行と列の総計を表示しない設定にします（添付図参照）。

お礼

さっそく回答いただきありがとうございます。

>列ラベルとΣ値に「レンタル希望」をドラッグし
このところで、列ラベルとΣ値の両方に「レンタル希望」を入れる方法がわかりませんでした。

教えていただいたにも関わらず、追加の教えてになってしまいますが、
ご教示いただけると助かります！