Excel 期間を条件にしたデータベースの検索、抽出について（VBAマクロまたは関数使用）

質問

Excelで塾生の模試の成績と学校での実際の試験結果との比較を行いたいと思っています。
bookの構成は下記のとおりです

sheet1は模試の成績のリストで、A列に塾生の名前,B列に模試の実施日,C列に模試の成績が記入されたリストがあります。（A列の名前順でソート）
（sheet1例）
A列, B列, C列
稲垣, 3/3, 50
稲垣, 3/30, 60
香取, 4/12, 40
木村, 1/20, 75
木村, 4/15, 60
木村, 5/20, 80
中居, 5/25, 100
中居, 5/30， 95
中居, 8/30, 50

sheet2は実際の試験結果のリストで、A列に学生の名前,B列に試験の実施日,C列に試験の成績が記入されたリストがあります。。（A列の名前順でソート）
（sheet2例）
A列, B列,C 列
石田, 4/1, 60
稲垣, 4/1, 80
稲垣, 4/2, 95
稲垣, 5/1, 60
香取, 5/1, 40
木村, 2/1, 75
木村, 4/1, 80
木村, 5/1, 60
田中, 4/1, 80
中居, 6/1, 100
中居, 7/2, 50
森, 7/2, 80

模試実施日と試験日との関係も分析したいため、模試から2週間以内のテスト結果を抽出し、比較したいと思っています。
その他の条件としては、
(1)抽出結果（試験の得点）はsheet1のD列に出力したい。
(2)試験の実施日が模試の実施日より2週間以上後の場合は“期間外”と表示したい。
(2)最後に模試を受けた後にまだ試験を受けていない場合は“未受”と表示したい。
（抽出結果例）
稲垣の3/3の模試場合、2週間以内に試験を受けていないためD列には“期間外”と表示
稲垣の3/30の模試の場合、D列には4/1の試験結果の“80”を表示（模試後2週間以内に2回以上試験を受けている場合は、より模試実施日に近いデータを選択し抽出）
木村の5/20の模試の場合、模試後に試験を受けていないためD列には“未受験”と表示
中居は6/1の試験の2週前以内に5/25，5/30と2回模試を受けているが、この場合両方の模試結果のD列に6/1の試験結果である100を表示

簡単なようでなかなかうまく抽出が出来ず悩んでいます。
良い方法がありましたらどうかご教授ください。

回答No.4

noname#204879

別解
=IF(MAX((Sheet2!A$1:A$100=A1)*(Sheet2!B$1:B$100>=B1)*(Sheet2!B$1:B$100<=B1+14)),SUMPRODUCT((Sheet2!A$1:A$100=A1)*(Sheet2!B$1:B$100=LARGE((Sheet2!A$1:A$100=A1)*(Sheet2!B$1:B$100>=B1)*(Sheet2!B$1:B$100<=B1+14)*(Sheet2!B$1:B$100),SUM((Sheet2!A$1:A$100=A1)*(Sheet2!B$1:B$100>=B1)*(Sheet2!B$1:B$100<=B1+14)))),Sheet2!C$1:C$100),IF(MAX((Sheet2!A$1:A$100=A1)*(Sheet2!B$1:B$100>B1+14)),"期間外","未受"))

お礼

ご回答ありがとうございました。
当方の応用が利かず、今回は別の方法を採用しました。
お手数をお掛けしました。

回答No.3

n_na_tto

●E,F列作業列を使う方法
＿＿＿A＿＿_B＿＿C＿＿＿_D＿_E＿＿F_
1＿稲垣＿_3/3＿_50＿期間外＿_2＿4/1_
2＿稲垣＿3/30＿_60＿＿＿80＿_2＿4/1_
3＿香取＿4/12＿_40＿期間外＿_5＿5/1_
4＿木村＿1/20＿_75＿＿＿75＿_6＿2/1_
5＿木村＿4/15＿_60＿期間外＿_8＿5/1_
6＿木村＿5/20＿_80＿＿未受＿_0＿＿＿
7＿中居＿5/25＿100＿＿_100＿10＿6/1_
8＿中居＿5/30＿_95＿＿_100＿10＿6/1_
9＿中居＿8/30＿_50＿＿未受＿_0＿＿＿

E1[直後の試験日がある行番号]
=MIN(IF((Sheet2!A$1:A$100=A1)*(Sheet2!B$1:B$100>B1),ROW(A$1:A$100),""))
Ctrl+Shift+Enter同時押し
下方向・↓
F1[直後の試験日]
=IF(E1=0,"",INDEX(Sheet2!B$1:B$100,E1))
下方向・↓
D1[判定]
=IF(F1="","未受",IF(F1>B1+14,"期間外",INDEX(Sheet2!C$1:C$100,E1)))
下方向・↓

●作業列なし
(激重。どちらのシートも100行が限界？)
D1[いきなり判定]
=IF(SUMPRODUCT((Sheet2!A$1:A$100=A1)*(Sheet2!B$1:B$100>B1))=0,"未受",IF(SUMPRODUCT((Sheet2!A$1:A$100=A1)*(Sheet2!B$1:B$100>B1)*(Sheet2!B$1:B$100<=B1+14))=0,"期間外",INDEX(Sheet2!C$1:C$100,MIN(IF((Sheet2!A$1:A$100=A1)*(Sheet2!B$1:B$100>B1)*(Sheet2!B$1:B$100<=B1+14),ROW(A$1:A$100),"")))))
Ctrl+Shift+Enter同時押し
下方向・↓

お礼

ご回答ありがとうございました。
当方の応用が利かず、今回は別の方法を採用しました。
お手数をお掛けしました。

回答No.2

ASIMOV

作業用のsheetを用意します（sheet3とします）
sheet1の内容をsheet3にコピーします
sheet3の
D列に
　=IF(A2=A1,IF(E2<>"m",IF((DATE(YEAR(B2),MONTH(B2),DAY(B2))-DATE(YEAR(B1),MONTH(B1),DAY(B1)))<14,C2,"期間外"),"未受"),"")
E列に
　"m"を入れます
（sheet1のデーターのある行の分のみ）

次に
sheet2のデーターを、コピーしたsheet3のデーターの末尾から貼り付けます
データー全体を選択して、並べ替えをします
キー１はA列
キー２はB列にします

これで一応出来るんですが、模試と本試の結果が混ざっていますので
も一度、全体をコピーして、「値」で貼り付けし、オートフィルターなどで、"m"を抽出して出来上がりです

お礼

ありがとうございます。今回はこの方法を応用させていただきました。

回答No.1

n-jun

ＶＢＡなら、試しに双方を総当たりでやってみるとか。。。
結構複雑そう。。。