エクセルデータ抽出（日付）

質問

はじめまして、エクセルの関数とか殆ど分かりません。
そこで、以下内容の事をしたいのですが、どうすれば良いでしょうか？

前提
日付を入力した「元データ」があります。下記(1)～(3)を対応して「抽出データ」を作成したいです。

元データ 抽出データ
番号 日付 金額 番号 日付 金額
1 平成23年9月18日 \60,000 1 平成23年9月18日 \60,000
2 平成23年11月2日 \70,000 3 平成23年10月15日 \90,000
3 平成23年10月15日 \90,000 4 平成23年10月13日 \150,000
4 平成23年10月13日 \150,000 ⇒
5 平成23年11月29日 \200,000
6 平成23年11月1日 \80,000
　　　　　　　　　　　　 合計 \300,000

(1)今日より３ヶ月以内（月末〆）のデータのみ抽出する。
(2)抽出データの金額を計算する。
(3)抽出データを別のシートに作成する。

(1)～(3)を実行する方法を教えて下さい。

回答No.5

kagakusuki

回答番号ANo.4です。
　別の方法をもう1つ回答させて頂きます。
　今仮に、元データーの表中で、「日付」と入力されているのがSheet1のB1セルであり、
抽出結果の表中で、「日付」と入力されているのがSheet2のB1セルであるものとし、
Sheet3のA列を作業列として使用するものとします。

　まず、Sheet2のA2セルに次の数式を入力して下さい。

=IF(ROWS($D$2:$D2)>COUNTIF(Sheet1!$B:$B,"<"&DATE(YEAR(TODAY()),MONTH(TODAY())+3,DAY(TODAY()))+1)-COUNTIF(Sheet1!$B:$B,"<"&TODAY()),"",INDEX(Sheet1!A:A,SUMPRODUCT(ROW(Sheet1!$B$1:INDEX(Sheet1!$B:$B,MATCH(9^9,Sheet1!$B:$B)))*(Sheet1!$B$1:INDEX(Sheet1!$B:$B,MATCH(9^9,Sheet1!$B:$B))<DATE(YEAR(TODAY()),MONTH(TODAY())+3,DAY(TODAY()))+1)*(Sheet1!$B$1:INDEX(Sheet1!$B:$B,MATCH(9^9,Sheet1!$B:$B))>=TODAY())*(COUNTIF(OFFSET(Sheet1!$B$1,,,ROW(Sheet1!$B$1:INDEX(Sheet1!$B:$B,MATCH(9^9,Sheet1!$B:$B)))-ROW(Sheet1!$B$1)+1),"<"&DATE(YEAR(TODAY()),MONTH(TODAY())+3,DAY(TODAY()))+1)-COUNTIF(OFFSET(Sheet1!$B$1,,,ROW(Sheet1!$B$1:INDEX(Sheet1!$B:$B,MATCH(9^9,Sheet1!$B:$B)))-ROW(Sheet1!$B$1)+1),"<"&TODAY())=ROWS($D$2:$D2)))))

　次に、Sheet2のA2セルをコピーして、Sheet2のB2～C2の範囲に貼り付けて下さい。
　次に、Sheet1のA2～C2の範囲をコピーして、Excelの[形式を選択して貼り付け]機能を使用して、書式のみをSheet2のA2～C2の範囲に貼り付けて下さい。
　次に、Sheet2のA2～C2の範囲をコピーして、同じ列の3行目以下に貼り付けて下さい。

　以上で操作は完了です。
　尚、こちらの方法では作業列が不要です。
　但し、処理に要するパソコンの計算負荷が大きいため、元データの行数が多くなりますと、抽出結果が表示されるまでに時間が掛かる様になる場合があります。（時間を要すると言っても、1000行前後程度であれば、許容範囲だと思います）

回答No.4

kagakusuki

今仮に、元データーの表中で、「日付」と入力されているのがSheet1のB1セルであり、
抽出結果の表中で、「日付」と入力されているのがSheet2のB1セルであるものとし、
Sheet3のA列を作業列として使用するものとします。

　まず、Sheet3のA1セルに次の数式を入力して下さい。

=IF(ISNUMBER(INDEX(Sheet1!$B:$B,ROW())),Sheet1!$B1+COUNTIF(Sheet1!$B$1:INDEX(Sheet1!$B:$B,ROW()),INDEX(Sheet1!$B:$B,ROW()))/COUNTIF(Sheet1!$B:$B,INDEX(Sheet1!$B:$B,ROW()))/2,"")

　次に、Sheet3のA1セルをコピーして、Sheet3のA2以下に貼り付けて下さい。

　次に、Sheet2のA2セルに次の数式を入力して下さい。

=IF(ROWS(Sheet1!$2:2)>COUNTIF(Sheet1!$B:$B,"<="&DATE(YEAR(TODAY()),MONTH(TODAY())+3,DAY(TODAY())))-COUNTIF(Sheet1!$B:$B,"<"&TODAY()),"",INDEX(Sheet1!A:A,MATCH(SMALL(Sheet3!$A:$A,ROWS(Sheet1!$2:2)+COUNTIF(Sheet1!$B:$B,"<"&TODAY())),Sheet3!$A:$A,0)))

　次に、Sheet2のA2セルをコピーして、Sheet2のB2～C2の範囲に貼り付けて下さい。
　次に、Sheet1のA2～C2の範囲をコピーして、Excelの[形式を選択して貼り付け]機能を使用して、書式のみをSheet2のA2～C2の範囲に貼り付けて下さい。
　次に、Sheet2のA2～C2の範囲をコピーして、同じ列の3行目以下に貼り付けて下さい。

　以上で操作は完了です。
　尚、Sheet3の作業列は、同じ日付が複数存在している場合に対処するためのものです。
　ですから、もし、元データの日付欄に、重複する日付が絶対に存在しない場合には、Sheet3は使わずに済ませる事も出来ます。
において

Sheet3!$A:$A

と入力されている部分を、全て

Sheet1!$B:$B

に置き換えて、

=IF(ROWS(Sheet1!$2:2)>COUNTIF(Sheet1!$B:$B,"<="&DATE(YEAR(TODAY()),MONTH(TODAY())+3,DAY(TODAY())))-COUNTIF(Sheet1!$B:$B,"<"&TODAY()),"",INDEX(Sheet1!A:A,MATCH(SMALL(Sheet1!$B:$B,ROWS(Sheet1!$2:2)+COUNTIF(Sheet1!$B:$B,"<"&TODAY())),Sheet1!$B:$B,0)))

という数式とする事が出来ます。

回答No.3

KURUMITO

回答No1,2です。
上記内容の『MAX(D$1:D1)』箇所は、どの様な意味合いがあるのでしょうか。
オートフィルドラッグすると『MAX(D$1:D3)』と増えて、値が1、2、3、とカウン
トアップしていきます。
上記動作で宜しいのでしょうか。
全く正常です。別のシートに結果を表示させるときの該当するデータの表示順ということになります。

お礼

毎回回答頂き有難う御座います。

何度もすみませんが、
先程の質問内容とは異なるのですが、実際のデータに沿った形で質問させて下さい。

データ量が膨大で、実際セルが多数御座います。
内容をしっかり理解できていない為、実データ通りの計算式を教えて下さい。

『元データ』※一番上は実際の列になります。
A B 　F Q
番号 商品名 　金額 日付
1 きゅうり　\60,000 平成23年9月18日
2 とまと 　\70,000 平成23年11月2日
3 スイカ 　\90,000 平成23年10月15日
4 たまご 　\150,000 平成23年10月13日
5 茄子 　\200,000 平成23年11月29日
6 お菓子 　\80,000 平成23年11月1日

↓

『抽出データ』
A B 　F Q
番号 商品名 　金額 日付
1 きゅうり　\60,000 平成23年9月18日
3 スイカ 　\90,000 平成23年10月15日
4 たまご 　\150,000 平成23年10月13日

(1)データ抽出
　(1)今日より３ヶ月以内（月末〆）のデータのみ抽出する。
　(2)抽出データを別のシートに作成する。

(1)～(2)を実行する方法を教えて下さい。 ※『列』もそのままで計算式を教えて下さい。

(2)『抽出データ』の金額を計算する。

(3)出来ればマクロを組む。

(4)ボタンを作成。

回答No.2

KURUMITO

別のシートのA2セルに入力する式は次のような式でもよいでしょう。

=IF(OR(COLUMN(A1)>3,ROW(A1)>MAX(Sheet7!$D:$D)+1),"",IF(ROW(A1)<=MAX(Sheet7!$D:$D),INDEX(Sheet7!$A:$C,MATCH(ROW(A1),Sheet7!$D:$D,0),COLUMN(A1)),IF(ROW(A1)=MAX(Sheet7!$D:$D)+1,IF(COLUMN(A1)=2,"合計",IF(COLUMN(A1)=3,SUM(INDEX($C:$C,2):INDEX($C:$C,ROW(A1)-1)),"")))))

回答No.1

KURUMITO

シート1に元のデータがありA1セルに番号、B1セルに日付、C1セルに金額の文字列が入力されており下方にそれぞれのデータが入力されているとします。
分かり易く、計算にも負担のかからない方法としては作業列を作って対応することです。
D2セルには次の式を入力して下方にオートフィルドラッグします。

=IF(B2<TODAY(),"",IF(B2>DATE(YEAR(TODAY()),MONTH(TODAY())+3,DAY(TODAY())),"",MAX(D$1:D1)+1))

この式は今日から3か月後の日付までのデータを抽出するための式です。月末締め切りの文字が気になります。
もしも今日から3か月後の日付の月の月末までというのでしたら次の式をD2セルに入力して下方にオートフィルドラッグします。

=IF(B2<TODAY(),"",IF(B2>DATE(YEAR(TODAY()),MONTH(TODAY())+4,0),"",MAX(D$1:D1)+1))

別のシートに抽出後のデータを表示させるとしてA1セルからC2セルにはシート1と同じ項目名を入力します。
A2セルには次の式を入力して右横方向にオートフィルドラッグしたのちに下方向にもオートフィルドラッグします。

=IF(OR(COLUMN(A1)>3,ROW(A1)>MAX(Sheet1!$D:$D)+1),"",IF(ROW(A1)<=MAX(Sheet1!$D:$D),INDEX(Sheet7!$A:$C,MATCH(ROW(A1),Sheet1!$D:$D,0),COLUMN(A1)),IF(ROW(A1)=MAX(Sheet1!$D:$D)+1,IF(COLUMN(A1)=2,"合計",IF(COLUMN(A1)=3,SUM(INDEX($C:$C,2,1):INDEX($C:$C,ROW(A1)-1)),"")))))

なお、B列にはシリアル値が表示されますのでB列を選択して右クリックし、「セルの書式設定」から「表示形式」で日付からお望みの日付表示にします。

お礼

早速のご対応有難う御座いました。

初心の為、１つずつ確認させて頂きます。
今回は、下記内容になります。
=IF(B2<TODAY(),"",IF(B2>DATE(YEAR(TODAY()),MONTH(TODAY())+4,0),"",MAX(D$1:D1)+1))

上記内容の『MAX(D$1:D1)』箇所は、どの様な意味合いがあるのでしょうか。
オートフィルドラッグすると『MAX(D$1:D3)』と増えて、値が1、2、3、とカウントアップしていきます。
上記動作で宜しいのでしょうか。

初歩的な質問で申し訳御座いませんが、宜しく御願い致します。