中間課題(3) 成績処理

キーワード

• SUMPRODUCT関数
• RANDBETWEEN関数
• IFS関数

今回やること

• 関数を組み合わせて、成績処理をやってみる。

使用ファイル

• 成績処理.xlsx

3つのシートの役割

2つ目「テスト結果」シート

• 3回分のテスト結果を入力する。
• 3回分のテスト結果を重み付きで計算し100点満点で結果を出す。

3つ目「欠席回数」シート

• 欠席回数を入力する。

1つ目「成績表」シート

• 「テスト結果」シートと「欠席回数」シートの結果を集計して、成績(A〜D)を出力する。

2枚目「テスト結果」シートの作成

3回分のテスト結果(B2〜D11)を入力する。

• 0〜100の値を入力する。のは面倒なので、RANDBETWEEN関数を使う。

RANDBETWEEN(最小値, 最大値)

• 最小値と最大値の間の乱数を返す。

• B2に以下の式を入力する。

  • RANDBETWEEN(0, 100)

• ファンクションキー F9を押す度に、乱数を発生し直す。

課題 1

• 100点満点じゃないテストにも対応できるようにB2の式を変更する。
• 何点満点かは、B15にある。
• ついでに最小値が0だと寂しいので、最小値を40に変更する。
• B15を50に変更して確認する。

課題 2

• B2を残りの点数の入力欄(B2:D11)にコピペして、キチンと点数が出るように、B2の式を変更する。
• D15を200に変更して確認する。

E列に重み付きの合計を計算する。

• E2にそれぞれのテストの点数(B2〜D2)に重み(B14〜D14)をかけて合計する。
• 式としては、B2 * B14 + C2 * C14 + D2 * D14 だが、面倒い。
  • テストが10回とかあると大変。
• そこでSUMPRODUCT関数を使う。

SUMPRODUCT(範囲1, 範囲2)

• 範囲1から1つ、範囲2から1つ、それぞれ順番にセルの値を取り出して、かけて合計する。
• SUMは合計(足し算)、PRODUCTはかけ算を意味する。

• E2に以下の式を入力する。
  • SUMPRODUCT(B2: D2, B14:D14)

課題 3

• E2を残りの点数の入力欄(E3〜E11)にコピペして、キチンと点数が出るように、E2の式を変更する。

F列に100点満点での点数を計算する。

• まず、3つのテストで満点を取った場合の点数をE15に計算させる。

課題 4

• E15の計算は、コピペで実現できる。
• どのセルをコピペすればよいか？

課題 5

• E15の値を利用して、F2にE2の点数を100点満点に換算した点数を計算させる。
• F2の計算結果を整数として表示するようにする。

• F2をF3〜F11とF15にコピペして表を完成させる。
  • F15はちゃんと100にならなければどこかおかしい。
• ついでにF12も整数表示されるようにする。

3枚目「欠席回数」シートの作成

B2〜B11に欠席回数を入力していく。

課題 6

• 入力していくのが面倒なので、B2に、欠席回数が多くても寂しいので、0〜6が表示されるようにする。

課題 7

• B2をB3〜B11にコピペしてキチンと値が出るように、B2の式を変更する。

1枚目「成績表」シートの作成

• B列の出席率、D列のテスト結果をもとに、C列に評価対象となるかどうかを計算させる。
• E列に成績を表示する式は既に入力済み。
  • E列の式の意味は授業で解説。

B列に出席率を計算させる。

課題 8

• B2に出席率を計算させる式を入力する。
  • 欠席数は「欠席回数」シートのB2
  • 授業回数は、H2
• B3〜B11にコピペしてキチンと計算結果が出るか確認する。

C列に評価対象かどうかを計算させる。

課題 9

• C2にL3の出席率以上であれば「1」、そうで無ければ「0」と表示するようにする。
• 1と0は文字列ではなく、数値で。
  • " " (ダブルクォーテーション)でくくる必要は無い。

D列にテスト結果を表示する。

課題 10

• D2にテスト結果を表示させる式を入力する。
  • テスト結果は「テスト結果」シートのF2
• ただし、評価対象で無い場合は、テスト結果を0にする。
  • IF関数は使わない。
• D3〜D11にコピペしてキチンと値が出るか確認する。

• 以上で完成（のハズ)
• ファンクションキー F9を叩いて、動作確認する。

E列を見てみる。

• E列でIFS関数を使っている。

IFS(条件1, 条件1を満たしている場合, 条件2, 条件2を満たしている場合, ..., TRUE, 全ての条件を満たさなかった場合)

• 条件1を満たしていたら、条件1を満たしている場合の動作を行う。
• 条件1を満たしていなかったら、条件2をチェックして満たしていたら、条件2を満たしている場合の動作を行う。
• 条件2を満たしていなかったら・・・と、先頭から順に条件をチェックして、満たしていたらその条件の直後にある動作を行う。
• 最後に全て満たしていなかった場合の条件をTRUEとして、その動作を書いて終わる。

• この関数をつかって、成績の点数の条件をチェックして、成績を表示している。

おしまい

• 保存して、メールで送ってください。

提出期限

• Teamsに掲載します。