学生の成績表を作成するためのGoogleスプレッドシートの数式と関数

Google スプレッドシートの数式と関数を使用すると、スプレッドシートの値から結果を自動的に計算できます。数式は等号 ( ) で始め、次に、、 、 など=の計算式または関数を入力します。この例では、点数をアルファベットの成績に変換し、賞を割り当て、各学生を合格または不合格としてマークする学生の成績表を作成します。AVERAGEVLOOKUPIF

以前の「Googleドキュメント」スプレッドシートの説明は、廃止されたインターフェースに関するものです。現在のスプレッドシートアプリはGoogleスプレッドシートで、数式はセルに直接入力できます。数式ヘルプボックスには、入力中に関数の構文や候補が表示されます。最新のワークフローについては、Googleの数式と関数の追加に関するガイドをご覧ください。

成績表を設定する

空白のシートの1行目に、「学生」、「スコア」、「成績」、「賞」、「ステータス」という見出しを作成します。A列に学生の名前を、B列に数値スコアを入力します。数式を上書きすることなくスコアを確認または変更できるように、ソースデータと計算結果を別々の列に保存してください。

簡単な計算をしたい場合は、=AVERAGE(B2:B6)空のセルに平均スコアを入力すると良いでしょう。数式とは、セルに入力された式全体のことです。関数とは、AVERAGEその式の一部として使用できる組み込みの操作のことです。Google スプレッドシートでは、編集時に参照されているセルが強調表示され、数式の下に関数のヘルプが表示される場合があります。

VLOOKUP関数を使って点数を成績に変換する

H1:I6 に 2 列のしきい値表を作成します。H1に「最低スコア」 、I1 に「成績」と入力し、しきい値を昇順で入力します。0 / F、60 / D、70 / C、80 / B、90 / A。これらは成績評価のカットオフ値の例です。ご自身のコースで使用するルールに置き換えてください。この表は近似マッチングを使用するため、数値のしきい値は小さい順から大きい順に並べてください。

C2をクリックして以下を入力してください：

=VLOOKUP(B2,$H$2:$I$6,2,TRUE)

VLOOKUP選択した範囲の最初の列で、B2 の値を検索します。数値 2 は、2 番目の列から値を返すように指示し、TRUE近似一致を要求します。上記のしきい値では、88 は B を返し、67 は D を返します。ドル記号は検索範囲を絶対値にするため、数式をコピーしても範囲は移動しません。Google は近似一致の場合、昇順を推奨しています。ソートされていないテーブルでは、間違った成績が返される可能性があります。公式のVLOOKUP 構文とガイダンスを参照してください。

数式を列全体に記入してください

C2セルに期待通りの成績が表示されていることを確認したら、C2セルを選択し、右下隅にある小さな青い塗りつぶしハンドルをドラッグして、残りの生徒の行まで移動させます。行参照はB2からB3、B4…と変化しますが、$H$2:$I$6C2セルは固定されたままです。最後の行を確認して、コピーした数式がすべての生徒に適用されていることを確認してください。

シートの行数が少ない場合は、数式をコピー＆ペーストするだけでも問題ありません。行数が多い場合は、下方向にコピーする方が速いです。検索範囲は各しきい値に対応できる十分な幅を確保し、スコアはテキストではなく数値であることを確認してください。

賞の授与と合否判定を計算します。

賞については、2つ目のルックアップテーブルを使用できます。K2:K6 に成績を、L2:L6 に対応する賞のラベルまたは賞金額を入力します。D2 には を入力します=VLOOKUP(C2,$K$2:$L$6,2,FALSE)。ここではFALSE、成績の完全一致が求められます。これは、A、B、C、D、F などのカテゴリに適しています。戻り値が金額の場合は、D 列を通貨形式に設定します。ラベルの場合は、同じ数式でテキストが返されます。各成績に対応する賞が付与されるように、各成績ごとに行を追加します。

60点未満のスコアを不合格とするには、E2セルに次の数式を入力してください。

=IF(B2<60,"Fail","Pass")

IF条件が真かどうかをチェックします。この式は、スコアが60未満の場合は「不合格」、それ以外の場合は「合格」を返します。コースで異なる合格基準を使用している場合は、60を変更してください。IF関数の公式リファレンスには、条件、真の結果、偽の結果が説明されています。

C2と同様に、D2とE2にも入力してください。賞の検索では、固定範囲はそのままですが、グレードの参照は行ごとに変わります。ステータスの検索では、スコアの参照は行ごとに変わります。

よくある公式の問題を確認する

• 間違ったグレードが表示されます。最後の VLOOKUP 引数が の場合、H2:H6 のしきい値が数値で昇順になっていることを確認してくださいTRUE。
• つまり#N/A、正確な検索では該当する成績が見つからない場合や、スコアが最低基準値を下回っている場合があるということです。スペル、スペース、そしてすべての成績が表彰表に該当する行として登録されているかどうかを確認してください。
• コピーされた数式はテーブルを変更します。検索範囲にドル記号を追加します。例: $H$2:$I$6。
• 関数名や構文がわからない場合は、入力を開始して=ヘルプボックスを使用するか、Google の最新のGoogle スプレッドシート関数一覧を参照してください。

しきい値、参照範囲、合格基準が採点方針と一致すると、スコアを更新するたびにシートが成績とステータスを再計算します。共同作業者がどのルールが数式に適用されているかを確認できるように、採点基準のコピーをデータの横に保管してください。