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を表示
簡単なようでなかなかうまく抽出が出来ず悩んでいます。
良い方法がありましたらどうかご教授ください。
A 回答 (4件)
- 最新から表示
- 回答順に表示
No.2
- 回答日時:
作業用の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のデーターの末尾から貼り付けます
データー全体を選択して、並べ替えをします
キー1はA列
キー2はB列にします
これで一応出来るんですが、模試と本試の結果が混ざっていますので
も一度、全体をコピーして、「値」で貼り付けし、オートフィルターなどで、"m"を抽出して出来上がりです
No.3
- 回答日時:
●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.4
- 回答日時:
別解
=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)),"期間外","未受"))
お探しのQ&Aが見つからない時は、教えて!gooで質問しましょう!
似たような質問が見つかりました
- MySQL 下記の問合せを行うクエリを、PhpMyAdminで作成して実行せよ。 第二回模試の3科目の各得点と合 1 2023/04/25 18:02
- 高校受験 神奈川県全県模試って信用できますか? 高校受験 今この前受けた全県模試の結果を見たのですが何故か第1 1 2022/12/13 20:03
- 大学受験 東大実践模試受けたい高3です。 私は毎年駿台全国模試を学校受験していて、あいさむ会員(?)に登録済み 2 2022/07/15 20:35
- 大学受験 娘の大学受験勉強 6 2022/06/30 19:58
- 弁護士・行政書士・司法書士・社会保険労務士 記述対策について 1 2022/09/19 20:51
- 大学受験 3日後に、共通テスト模試を受けます。 2 2022/04/29 13:26
- 高校受験 神奈川県全県模試かW模擬 どっちの方が信憑性高い? 最近知ったのですが神奈川県全県模試の方がW模擬よ 1 2022/12/13 22:38
- 中学校受験 中受 模試の結果 4 2022/12/16 23:24
- 高校受験 こんばんは。今年受験を控えた中学3年生です。 西宮東高校を受験したいと考えているのですが… 三年一学 6 2022/08/08 04:14
- Excel(エクセル) Excel 効率的な名簿と得点の管理の仕方 8 2022/08/07 08:15
関連するカテゴリからQ&Aを探す
おすすめ情報
デイリーランキングこのカテゴリの人気デイリーQ&Aランキング
-
エクセル初心者です 関数の入れ...
-
【関数】先頭だけにある、半角...
-
エクセル 白黒印刷で白線を印刷...
-
Excelのチェックボックスの使い...
-
【関数】適切な文字数の数字を...
-
Excelのpivotについて質問です
-
Excel ピボットテーブルで日付...
-
LOOKUP関数を使えばいいのでし...
-
エクセル関数を教えてください
-
エクセルのセルに同じ大きさの...
-
UNIQUE関数が使えないバージョ...
-
excelの不要な行の削除ができな...
-
エクセルで「-0.0」と表示さ...
-
時間によってファイル名が変わ...
-
WPS OFFICEでの縦書きについて
-
エクセルの関数について教えて...
-
Aというブックの1というシート...
-
【マクロ】シート名を取得する...
-
VBA Private Sub Worksheet_Cha...
-
VBA、Excelのworkbook.open に...
マンスリーランキングこのカテゴリの人気マンスリーQ&Aランキング
-
Excel 2019 のピボットテーブル...
-
[関数得意な方]教えて下さい・...
-
Excelにてある膨大なデータを管...
-
[関数について]わかる方教えて...
-
Excel初心者です。 詳しい方、...
-
excelの不要な行の削除ができな...
-
エクセル関数に詳しい方教えて...
-
INDIRECTを使わず excelで複数...
-
[オートフィルタ]で抽出された...
-
エクセルの神よ、ご回答を! エ...
-
エクセル関数に詳しい方、教え...
-
各ページの1番上の表示について
-
Excelで写真のような表を作った...
-
エクセルで不等号記号(≠)が上に...
-
数学 Tan(θ)-1/Cos(θ)について...
-
Excel 2019 は、SPILL機能があ...
-
Excelで全角を半角にしたいので...
-
条件付き書式を教えてください
-
Excel フィルターを掛けた状態...
-
[オートフィルタ]の適用範囲の...
おすすめ情報