![](http://oshiete.xgoo.jp/images/v2/pc/qa/question_title.png?c9bd177)
エクセルの表で、条件(日付別)にあてはまる行を 別シートに順番に抽出されるような関数を教えてください。
シート名「マスター」の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ベストアンサー
- 回答日時:
[No.2お礼]へのコメント、
》 コピーしたセルがすべて #NAME? と表示されます
単にエラーでなく、キチンと「#NAME?」と記されたことに質問者としてのマナーを弁えておられるなぁ、と感心いたしました。
「#VALUE!」でなかったことで、私が付けた【お断り】の意味もチャンと理解される Excel 使いに慣れたお方と推察いたしました。
なぜ「#NAME?」エラーが出たかの理由が知りたければその旨をお知らせいただければ、不得手ですが若干の講釈を垂れます
前置きが長くなりましたが、貴方の場合は次のようにするのが、式がより簡単になります。(Excel の古いバージョンでも動作します)
1.セル A3 を選択
2.次の[条件付き書式]を設定
__数式が⇒⇒⇒ =ISERROR(A3)
__フォント色⇒ 白
3.セル 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!の違いをご教授いただければと思います。よろしくおねがいします。
No.11
- 回答日時:
>A1セルをリストから選択式にすれば、より簡単になりそうです
参考までに
年と月 を指定して 日付にするDate関数もあります。
添付の図は
スクロールバー を使って 年月を変更する方法です。
ネットで、スクロールバー(フォームコントロール)など検索してみてください。
B3セルには =Date(B2,C2,1)が入っています。
![「エクセルの表で条件(日付別)にあてはまる」の回答画像11](http://oshiete.xgoo.jp/_/bucket/oshietegoo/images/media/0/526660_56385700afb3a/M.jpg)
No.9
- 回答日時:
[No.5お礼]へのコメント、
》 #NAME?と#VALUE!の違いを…
先ず #NAME? からです。
次の実験をしてみてください。
1.セル C2 に式 ="heidi" を入力するつもりで、故意にダブル
__クオーテーション(以後、ダブクオと略)の囲みを忘れて
__ =heidi と入力するとどうなりますか?
__確認が終わったら、最初の予定どおり式 ="heidi" を再入力。
2.次に、セル A1 に式 ="heidi"&heidi を入力
__この結果を添付図上段に示しています。
3.範囲 C1:C2 を選択して、[挿入]→[名前]→[上端行]の
__チェックだけにチェックを入れて[OK]をツン
4.この結果を添付図下段に示しています。
もうお分かりですよね?
式 =heidi とか ="heidi"&heidi の中のダブクオなしの文字を、Excel が「そんな名前あるの?」と“優しく”問いかけるエラーメッセージだったのです。
次に #VALUE! ね。ちなみに VALUE の日本語は「値」です。
何処かの空きセルに式 718+C1 を入力すると、その戻り値は #VALUE! となりますね。
Excel は文字と数値の四則演算が出来ないので「そんな値、あり得へんよッ!」と“激しく”諌めているのです。
次はオマケです。
式 =718/0 は「そんな割り算、でけへんよッ!」で #DIV/0!
「割り算する」の英語は DIVide です。
肝腎なことを忘れるところでした。
貴方の Excel では =IFERROR()関数に対応していないので、「そんな名前?」となっていた次第。
饒舌御免
得心しました。#DIV/0まで教えていただき、ありがとうございました。またIFERROR()関数に対応していないバージョンだという指摘もありがとうございます。そのあたりはまったく想定外でした。
ひさしぶりに質問させていただきましたが、不心得な私にもかかわらず皆様のお時間をいただき、そのアドバイスでいろいろな学びがありました。今後ともよろしくおねがいします。
No.8
- 回答日時:
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ですが、今後のためにも、独学ではどのように体系的に学べばよいか、もしよろしければ参考図書(?)など学びの方向をご教授いただけますか。よろしくおねがいします。
No.7
- 回答日時:
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
- 回答日時:
こんにちは!
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からの考察ありがとうございます。
オートフィルタ解除のあたりでエラーになり、オートフィルタがオンになっている状態では列の挿入や削除ができません、、、旨のデバッグ画面で終了してしまいましたが、シートには抽出&転記されたただしい結果が出ていました。
No.4
- 回答日時:
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セルをリストから選択式にすれば、より簡単になりそうです。ありがとうございました。
No.3
- 回答日時:
シート名からSheet1にあるデータを抽出したいと言うことだろうか。
ならば、CELL関数でシート名を得られるので、これを使ってVLOOKUP関数などを使えば良いと思う。
(この場合、VLOOKUP関数で指定する”範囲”は範囲名を付けて範囲名を指定するようにするとよい。絶対参照になる)
=CELL("filename")
これでCELL関数が使われたシート名を得ることができる。
C:\Users\ユーザー名\Documents\Excel\[xxxxxxxx.xls]11月2日
のような「ファイルパス」「ファイル名」「シート名」の値が返ってくるので、
FIND関数やSEARCH関数を使い ” ] ” の位置が先頭から何文字目にあるかを調べ、その右側の文字列を抽出すればシート名になる。
LEN関数やRIGHT関数などを組み合わせて行う。
あとは普通にVLOOKUP関数を使うだけ。
エラー関係の関数を用いてエラーへの対応も行っておくとよいでしょう。
CELL関数やFIND関数などを具体的にどのように組み合わせるかは試行錯誤してみてください。
難しいことではないと思います。
(使い方を理解してはじめて問題を解決できると自分は判断しています)
どうしても分からないときは、どのように関数を組んだのかを示すと、
何をどう間違えているのかをアドバイスできます。
CELL関数によってシート名は抽出できました。ですがVLOOKUP関数ですと、対象レコードは抽出されても歯抜けになってしまい、上から詰められないのではと思うのですが。どのように解決できますでしょうか。よろしくおねがいします。
No.2
- 回答日時:
日付別シートにおいて、
A3: =IFERROR(INDEX(Sheet1!B$1:B$1000,SMALL(IF(Sheet1!$A$1:$A$1000=$B$1,ROW(A$1:A$1000),""),ROW(A1))),"")
此れを右方および下方にズズーッとドラッグ&ペースト
【お断り】上式は配列数式です。
![「エクセルの表で条件(日付別)にあてはまる」の回答画像2](http://oshiete.xgoo.jp/_/bucket/oshietegoo/images/media/7/298588_56376746018c9/M.jpg)
まさにこのようにしたいです。しかし同じようにしてみると、コピーしたセルがすべて #NAME? と表示されます。日付の書式設定かと思い調べましたがわかりません。引き続きアドバイスお願いできますでしょうか。
お探しのQ&Aが見つからない時は、教えて!gooで質問しましょう!
似たような質問が見つかりました
- Excel(エクセル) Excelマクロ 差分抽出の方法が知りたいです。 2 2023/03/07 13:25
- Excel(エクセル) Excelマクロの差分抽出のコードを教えていただきたいです。 2 2023/03/14 11:40
- 転職 長く続けられる好条件の求人でしょうか? 3 2023/07/12 18:45
- その他(国内) 3時!夜中なのか早朝なのか微妙な時間、5時に出るならシャワー浴びてお茶淹れて神棚、仏壇 2 2022/05/20 03:58
- Excel(エクセル) エクセルの参照について教えてください 1 2022/12/08 16:06
- 野球 オールスターゲーム セ・リーグ 1 2022/06/07 23:46
- 野球 高校野球の話 1 2022/08/18 09:54
- Visual Basic(VBA) エクセルについて教えてください。 3 2023/06/28 09:11
- 政治 岸田内閣は国民を代表しているでしょうか? 11 2022/05/08 05:56
- その他(音楽・ダンス・舞台芸能) どの時代のミュージックステーションが一番豪華ですか? 86年 10月24日チェッカーズ、早見優、ジャ 2 2022/12/28 19:26
このQ&Aを見た人はこんなQ&Aも見ています
-
性格の違いは生まれた順番で決まる?長男長女・中間子・末っ子・一人っ子の性格の傾向
同じ環境で生まれ育っても、生まれ順で性格は違うものなのだろうか。家庭教育研究家の田宮由美さんに教えてもらった。
-
エクセルで日付を別のシートに引用したい
Excel(エクセル)
-
【Excel関数】別シートに日付順でデータを抽出する方法
Visual Basic(VBA)
-
指定期間内のデータを別シートへ抽出
Excel(エクセル)
-
-
4
Excelで違うシートから日付を引っ張ってきたい
Excel(エクセル)
-
5
複数のシートの日付データを、別のシートで並び替え
その他(Microsoft Office)
-
6
エクセルで、日付を入力すると、別のセルに文字を自動的に表示させるようにするには。
Excel(エクセル)
-
7
入力後に日付順になるように自動で並び替えしたいのです。
Excel(エクセル)
-
8
VBA 別シートの同じ日付の欄に値を貼付け
Excel(エクセル)
-
9
エクセルで入力→日付を自動判別して、その日付用のセルに転記したい
Excel(エクセル)
-
10
エクセル指定日付の数値を別シートに反映させる方法
Excel(エクセル)
-
11
エクセルで日付順で抽出
Excel(エクセル)
-
12
excel vbaで日付一致の行にデータ転記
Excel(エクセル)
-
13
エクセル 違う表から同じ日付のデータを検索
Excel(エクセル)
-
14
Excel 表から条件にあう行を別シートに抽出したい
Excel(エクセル)
-
15
Excel複数シートから日付と文字を取り出して日付順に並べたいです
Excel(エクセル)
-
16
エクセルで表示形式の時刻の「0:00」を表示しないようにするには?
Excel(エクセル)
-
17
Excelで日付変更ごとに、自動的にデータを転記
Excel(エクセル)
-
18
エクセル 指定した月のデータを自動的に反映させられる方法
Excel(エクセル)
-
19
エクセルで複数シートを別のシートに一覧表として自動で反映されるようにしたいです。
Excel(エクセル)
-
20
1年分のデータから特定の月分のデータを抽出→表示
Excel(エクセル)
関連するカテゴリからQ&Aを探す
おすすめ情報
このQ&Aを見た人がよく見るQ&A
デイリーランキングこのカテゴリの人気デイリーQ&Aランキング
-
エクセルで1月0日と表示される!!
-
Excelの「0」だけ非表示、小数...
-
エクセルで条件に一致したセル...
-
日付が未入力の際はゼロか、空...
-
Rangeメソッドは失敗しました。...
-
VBAで変数に関数式の結果をセッ...
-
エクセルで、加筆修正したセル...
-
(Excel)あるセルに文字を入力...
-
エクセルのルビがついたセルを...
-
Excelシートの保護時にデータの...
-
[EXCELマクロ] シートタブに自...
-
複数シートの同じセル内容を1シ...
-
Excelでスクロールすると文字が...
-
Excelにて、カタカナだけのセル...
-
excelでハイパーリンクになって...
-
エクセルの文字
-
現在時刻をリアルタイムで更新...
-
エクセル A8と同じシート名のG...
-
Excelであるファイルのセルにあ...
-
式の説明をお願いします。
マンスリーランキングこのカテゴリの人気マンスリーQ&Aランキング
-
エクセルで条件に一致したセル...
-
Excelの「0」だけ非表示、小数...
-
日付が未入力の際はゼロか、空...
-
(Excel)あるセルに文字を入力...
-
エクセルで1月0日と表示される!!
-
別シートのセルを絶対参照にする
-
複数シートの同じセル内容を1シ...
-
Rangeメソッドは失敗しました。...
-
Excelシートの保護時にデータの...
-
エクセルで複写のように自動入...
-
Excelで複数シートの選択セルを...
-
シート参照で変数を使いたい(EX...
-
ExcelでTODAY関数を更新させな...
-
エクセル ハイパーリンクで画像...
-
エクセルで、加筆修正したセル...
-
エクセルのセルに、マウスで選...
-
EXCEL関数でシート名が変わる可...
-
VBAで、セル(Range)のオブジ...
-
Excelのファイル容量が減らない...
-
excelでハイパーリンクになって...
おすすめ情報