エクセルの表で条件（日付別）にあてはまる行を 別シートに順番に抽出されるような関数を教えてください

エクセルの表で、条件（日付別）にあてはまる行を 別シートに順番に抽出されるような関数を教えてください。

シート名「マスター」のA列にある日付データを参照して、予めシート名が日付別に準備された各シートに、その行から必要部分を抜き出して上から順に抽出されるようにしたいです。
ちなみにマスターシートは300行ぐらい、日付名の各シートは90シート（3か月分）、各シートには最大30行程度です。

類似の質問もあるなかで恐縮ですが、よろしくおねがいします。
（以下イメージです。）

【Sheet1】
A 　　　　　　 B 　 　　　　　 C 　　　　　　D　　　　　　E　　　・・・・
11月1日　山田太郎　　岡山県　　　　倉敷市　　　090-XXXX－XXXX　　
11月2日　川本次郎　　香川県　　　　高松市　　　090-XXXX－XXXX
11月2日　海田三郎　　広島県　　　　広島市　　　090-XXXX－XXXX
11月3日　海田三郎　　広島県　　　　広島市　　　090-XXXX－XXXX
・
・
↓

シート
【11月1日】

山田太郎　　岡山県　　　　倉敷市　　　

シート
【11月2日】
川本次郎　　香川県　　　　高松市　　　
海田三郎　　広島県　　　　広島市　　　

シート
【11月3日】
海田三郎　　広島県　　　　広島市

No.5 ベストアンサー 回答者： mike_g 回答日時： 2015/11/03 10:31

[No.2お礼]へのコメント、

》 コピーしたセルがすべて　#NAME?　と表示されます
単にエラーでなく、キチンと「#NAME?」と記されたことに質問者としてのマナーを弁えておられるなぁ、と感心いたしました。
「#VALUE!」でなかったことで、私が付けた【お断り】の意味もチャンと理解される Excel 使いに慣れたお方と推察いたしました。
なぜ「#NAME?」エラーが出たかの理由が知りたければその旨をお知らせいただければ、不得手ですが若干の講釈を垂れます

前置きが長くなりましたが、貴方の場合は次のようにするのが、式がより簡単になります。(Excel の古いバージョンでも動作します)
１．セル A3 を選択
２．次の[条件付き書式]を設定
＿＿数式が⇒⇒⇒ =ISERROR(A3)
＿＿フォント色⇒ 白
３．セル A3 に次式を入力して、此れを右方および下方にズズーッ
__とドラッグ＆ペースト
＿＿=INDEX(Sheet1!B$1:B$1000,SMALL(IF(Sheet1!$A$1:$A$1000=$B$1,ROW(A$1:A$1000),""),ROW(A1)))

【お断り】上式は配列数式です。

この回答へのお礼

ありがとうございます。・・できました！配列数式についても学ばせていただきました。今後活用していきたいと思います。当方たしかに古いExcel使っています。よければ#NAME?と#VALUE!の違いをご教授いただければと思います。よろしくおねがいします。

お礼日時：2015/11/03 13:27

No.11 回答者： hallo-2007 回答日時： 2015/11/03 15:42

＞A1セルをリストから選択式にすれば、より簡単になりそうです

参考までに
年と月　を指定して　日付にするDate関数もあります。
添付の図は
スクロールバー　を使って　年月を変更する方法です。
ネットで、スクロールバー（フォームコントロール）など検索してみてください。
B3セルには　=Date(B2,C2,1)が入っています。

No.10 回答者： mike_g 回答日時： 2015/11/03 15:24

[No.9]に対する添付図です。

No.9 回答者： mike_g 回答日時： 2015/11/03 15:14

[No.5お礼]へのコメント、

》 #NAME?と#VALUE!の違いを…

先ず #NAME? からです。
次の実験をしてみてください。
１．セル C2 に式 ="heidi" を入力するつもりで、故意にダブル
＿＿クオーテーション(以後、ダブクオと略)の囲みを忘れて
＿＿ =heidi と入力するとどうなりますか？
＿＿確認が終わったら、最初の予定どおり式 ="heidi" を再入力。
２．次に、セル A1 に式 ="heidi"&heidi を入力
＿＿この結果を添付図上段に示しています。
３．範囲 C1:C2 を選択して、[挿入]→[名前]→[上端行]の
＿＿チェックだけにチェックを入れて[OK]をツン
４．この結果を添付図下段に示しています。
もうお分かりですよね？
式 =heidi とか ="heidi"&heidi の中のダブクオなしの文字を、Excel が「そんな名前あるの？」と“優しく”問いかけるエラーメッセージだったのです。

次に #VALUE! ね。ちなみに VALUE の日本語は「値」です。
何処かの空きセルに式 718+C1 を入力すると、その戻り値は #VALUE! となりますね。
Excel は文字と数値の四則演算が出来ないので「そんな値、あり得へんよッ！」と“激しく”諌めているのです。

次はオマケです。
式 =718/0 は「そんな割り算、でけへんよッ！」で #DIV/0!
「割り算する」の英語は DIVide です。

肝腎なことを忘れるところでした。
貴方の Excel では =IFERROR()関数に対応していないので、「そんな名前？」となっていた次第。

饒舌御免

この回答へのお礼

得心しました。#DIV/0まで教えていただき、ありがとうございました。またIFERROR()関数に対応していないバージョンだという指摘もありがとうございます。そのあたりはまったく想定外でした。
ひさしぶりに質問させていただきましたが、不心得な私にもかかわらず皆様のお時間をいただき、そのアドバイスでいろいろな学びがありました。今後ともよろしくおねがいします。

お礼日時：2015/11/03 19:45

No.8 回答者： tom04 回答日時： 2015/11/03 14:13

No.6・7です。

エラーが出たようですが、
No.6のコードだと
＞.Range("F:F").Delete
＞.AutoFilterMode = False
の2行を
＞.AutoFilterMode = False
＞.Range("F:F").Delete
に

No.7だと
＞.Columns(lastCol + 1).Delete
＞.AutoFilterMode = False
の2行を
＞.AutoFilterMode = False
＞.Columns(lastCol + 1).Delete
に入れ替えてみたらどうなるでしょうか？

※　詳しい検証はしていませんので
ダメだったらごめんなさいね。m(_ _)m

この回答へのお礼

ありがとうございます。
mike_gさんの方向で進めていこうと考えています。
ただ、tom04さんのVBAですが、今後のためにも、独学ではどのように体系的に学べばよいか、もしよろしければ参考図書（？）など学びの方向をご教授いただけますか。よろしくおねがいします。

お礼日時：2015/11/03 19:49

No.7 回答者： tom04 回答日時： 2015/11/03 11:13

No.6です。

投稿後気になったので・・・

前回のコードはA～E列限定だったのですが、質問文を拝見すると
もっと列数があるのでしょうか？
そうであれば前回のコードは消去し、↓のコードに変更してください。
尚、元データSheetの1行目は項目行ですべての列に項目名が入っているという前提です。

Sub Sample2() 'この行から//
Dim k As Long, lastRow As Long, lastCol As Long
Dim wS As Worksheet
Application.ScreenUpdating = False
With Worksheets("Sheet1")
lastCol = .Cells(1, Columns.Count).End(xlToLeft).Column
.Columns(lastCol + 1).Insert
lastRow = .Cells(Rows.Count, "A").End(xlUp).Row
Range(.Cells(2, lastCol + 1), .Cells(lastRow, lastCol + 1)).Formula = "=TEXT(A2,""m月d日"")"
For k = 2 To Worksheets.Count
Set wS = Worksheets(k)
wS.Cells.Clear
.Range("A1").AutoFilter field:=lastCol + 1, Criteria1:=wS.Name
If .Cells(Rows.Count, "A").End(xlUp).Row > 1 Then
Range(.Cells(1, "A"), .Cells(lastRow, lastCol)).SpecialCells(xlCellTypeVisible).Copy wS.Range("A1")
wS.Columns.AutoFit
End If
Next k
.Columns(lastCol + 1).Delete
.AutoFilterMode = False
End With
Application.ScreenUpdating = True
MsgBox "完了"
End Sub 'この行まで//

どうも失礼しました。m(_ _)m

No.6 回答者： tom04 回答日時： 2015/11/03 10:59

こんにちは！

VBAになりますが、一例です。
元データはSheet見出しの一番左側に配置してあり、
Sheet見出しの2番目以降に　11月1日　11月2日・・・11月30日　というシート名になっているSheetが配置してあるとします。

Alt＋F11キー　→　メニュー　→　挿入　→　標準モジュール　→　VBE画面のカーソルが点滅しているところに
↓のコードをコピー＆ペースト　→　Excel画面に戻り（VBE画面を閉じて）マクロを実行してみてください。
（Alt＋F8キー　→　マクロ　→　マクロ実行です）

Sub Sample1() 'この行から//
Dim k As Long, lastRow As Long
Dim wS As Worksheet
Application.ScreenUpdating = False
With Worksheets("Sheet1")
.Range("F:F").Insert
lastRow = .Cells(Rows.Count, "A").End(xlUp).Row
Range(.Cells(2, "F"), .Cells(lastRow, "F")).Formula = "=TEXT(A2,""m月d日"")"
For k = 2 To Worksheets.Count
Set wS = Worksheets(k)
wS.Cells.Clear
.Range("A1").AutoFilter field:=6, Criteria1:=wS.Name
If .Cells(Rows.Count, "A").End(xlUp).Row > 1 Then
Range(.Cells(1, "A"), .Cells(lastRow, "E")).SpecialCells(xlCellTypeVisible).Copy wS.Range("A1")
wS.Columns.AutoFit
End If
Next k
.Range("F:F").Delete
.AutoFilterMode = False
End With
Application.ScreenUpdating = True
MsgBox "完了"
End Sub 'この行まで//

※　関数でないので、データ変更があるたびにマクロを実行する必要があります。

※　一旦マクロを実行すると元に戻せませんので
別ブックにコピーしてマクロを試してみてください。m(_ _)m

この回答へのお礼

VBAからの考察ありがとうございます。
オートフィルタ解除のあたりでエラーになり、オートフィルタがオンになっている状態では列の挿入や削除ができません、、、旨のデバッグ画面で終了してしまいましたが、シートには抽出＆転記されたただしい結果が出ていました。

お礼日時：2015/11/03 13:43

No.4 回答者： hallo-2007 回答日時： 2015/11/03 10:15

No1です。

記入ミスがありましたので
B2セルに =MATCH(A1-1,Sheet1!A:A)
指定した日付のデータの下の行番号が表示されます。
C2セルに =COUNTIF(Sheet1!A:A,A1)
指定した日付のデータの数が表示されます。

は
B1セルに =MATCH(A1-1,Sheet1!A:A)
指定した日付のデータの下の行番号が表示されます。
C1セルに =COUNTIF(Sheet1!A:A,A1)
指定した日付のデータの数が表示されます。
に置き換えて確認してみてください。

＞いつでも一覧性が求められてまして・・・
はい、他の方の解答も同じですが、私の場合
A1セルに日付を入れるとその日付の一覧に変わります。
90枚ものシートの一覧を作成して、表示するのにシートを右へ左へ移動する作業と
セルに日付を入れる作業とどちらが便利でしょうか。

この回答へのお礼

そうですね、シート移動との比較になれば、やってみるとこちらのほうがよいことがわかりました。A1セルをリストから選択式にすれば、より簡単になりそうです。ありがとうございました。

お礼日時：2015/11/03 13:16

No.3 回答者： 銀鱗 回答日時： 2015/11/03 00:53

シート名からSheet1にあるデータを抽出したいと言うことだろうか。

ならば、CELL関数でシート名を得られるので、これを使ってVLOOKUP関数などを使えば良いと思う。
（この場合、VLOOKUP関数で指定する”範囲”は範囲名を付けて範囲名を指定するようにするとよい。絶対参照になる）

=CELL("filename")
これでCELL関数が使われたシート名を得ることができる。
C:\Users\ユーザー名\Documents\Excel\[xxxxxxxx.xls]11月2日
のような「ファイルパス」「ファイル名」「シート名」の値が返ってくるので、
FIND関数やSEARCH関数を使い ” ] ” の位置が先頭から何文字目にあるかを調べ、その右側の文字列を抽出すればシート名になる。
LEN関数やRIGHT関数などを組み合わせて行う。

あとは普通にVLOOKUP関数を使うだけ。
エラー関係の関数を用いてエラーへの対応も行っておくとよいでしょう。


CELL関数やFIND関数などを具体的にどのように組み合わせるかは試行錯誤してみてください。
難しいことではないと思います。
（使い方を理解してはじめて問題を解決できると自分は判断しています）
どうしても分からないときは、どのように関数を組んだのかを示すと、
何をどう間違えているのかをアドバイスできます。

この回答へのお礼

CELL関数によってシート名は抽出できました。ですがVLOOKUP関数ですと、対象レコードは抽出されても歯抜けになってしまい、上から詰められないのではと思うのですが。どのように解決できますでしょうか。よろしくおねがいします。

お礼日時：2015/11/03 09:39

No.2 回答者： mike_g 回答日時： 2015/11/02 22:39

日付別シートにおいて、

A3: =IFERROR(INDEX(Sheet1!B$1:B$1000,SMALL(IF(Sheet1!$A$1:$A$1000=$B$1,ROW(A$1:A$1000),""),ROW(A1))),"")
此れを右方および下方にズズーッとドラッグ＆ペースト

【お断り】上式は配列数式です。

この回答へのお礼

まさにこのようにしたいです。しかし同じようにしてみると、コピーしたセルがすべて　#NAME?　と表示されます。日付の書式設定かと思い調べましたがわかりません。引き続きアドバイスお願いできますでしょうか。

お礼日時：2015/11/03 09:22