仕事術

【教員×Excel】成績処理を自動化②「絶対参照」と「COUNTIF」

みなさんこんにちは。

今回の内容は、前回紹介した『【教員×Excel】成績処理を自動化①関数「IF」の活用』の応用編です。

【教員×Excel】成績処理を自動化①関数「IF」の活用 みなさんこんにちは。 教員をしていると避けられない仕事の中に、「成績処理」がありますよね。 ◯◯点だからA評価A・B・Aだ...

応用編といっても、使用する関数は基本的なものだけですので、安心して活用してみてください。

こんな人に読んでほしい!
  • 前回の記事を読んで、Excelでの成績処理に興味をもった。
  • 成績の基準となる数値を一括で変更したい。
  • 「A」・「B」・「C」の人数を確認したい。

基準となる数値を一括で変更する

成績をつけると、こんなことを思う時がありますよね。

  • 「A」の数が多すぎるな。
  • 「C」の基準を少し下げようかな。

例えば、80点以上を「A」としていたものを、95点以上に変更したいとします。
(数式は、前回の記事のものを使用します)

【変更前】=IF(B3>=80,”A”,IF(B3>=30,”B”,”C”))

【変更後】=IF(B3>=95,”A”,IF(B3>=30,”B”,”C”))

変更自体は簡単ですが、全てのセルに入力されている数式を変更する必要があります。またコピーして、必要なところに貼り付けをしなくてはいけないので面倒ですし、ミスが起こる可能性もあります。

基準となる数値を入力するセルを作る

成績を一括で変更するためには、まず基準となる数値を入力するセルを作ります。

そして、基準となる数値を入力します。

今回は上の画像のように、緑色のところに作成していますが、
どこのセルに作っても問題ありません。

数式に、セル番地を入力する。

初めに、「伊達由加」さんの「知識・技能」の数式を変更します。

緑色のセル(E3)に、先ほど入力した基準を当てはめていきます。

『E3』に入力する数式

【もともとの数式】=IF(B3>=80,”A”,IF(B3>=30,”B”,”C”))

【新しい数式】=IF(B3>=B14,”A”,IF(B3>=B15,”B”,”C”))

数式の解説

①もし、B3の値がB14の値以上なら、Aと表示する。そうでなければ、次の数式を実行する。

②もし、B3の値がB15の値以上なら、Bと表示する。そうでなければ、Cと表示する。

これで、基準を変更するだけで、「A」・「B」・「C」の評価が、自動的に変更されます。

「絶対参照」を活用する

数式ができれば、あとは全てのセルにコピーして貼り付けるだけです。

しかし、ここで問題が起きます。

数式をコピーして貼り付けると、参照すべきセル番地(B14やB15)がズレてしまうのです。

コピーして貼り付けた場合

E3・・・=IF(B3>=B14,”A”,IF(B3>=B15,”B”,”C”))
E4・・・=IF(B3>=B15,”A”,IF(B3>=C16,”B”,”C”))
E5・・・=IF(B3>=B16,”A”,IF(B3>=C17,”B”,”C”))

これは、「相対参照」という設定がデフォルトになっていることが原因です。

相対参照とは、参照先が数式に連動して変化する参照方式です。
数式をコピーすると、コピー先のセルの位置に応じて参照先のセルが自動的に変化します。

自動で変化することが効果的な場面も多くあるのですが、今回のように、どこに数式をコピーしても「B14」や「B15」を参照してほしい場合は、その設定を変更しなくてはいけません。

そこで使用する設定が「絶対参照」です。
絶対参照にすると、参照するセルが常に固定されます。

絶対参照にするには、参照したいセル番地に「$」マークを付けるだけです。

「相対参照」と「絶対参照」の数式

「相対参照」・・・=IF(B3>=B14,”A”,IF(B3>=B15,”B”,”C”))

「絶対参照」・・・=IF(B3>=B$14,”A”,IF(B3>=B$15,”B”,”C”))

絶対参照にしたいセル番地をクリックして「F4キー」を押すと、自動的に絶対参照に変更されます。

絶対参照にしておけば、どこに数式をコピーしても、参照すべき「B14」や「B15」が変わることはありません。

各評価の人数を把握する。

「A」・「B」・「C」の人数を把握する

成績をつける時、どうしても気にしてしまうのが「人数」ですよね。

あまりに「A」が多すぎてもいけませんし、少なすぎてもいけません。

ここでは、つけた成績の人数を常時表示してくれるように数式を入力します。

まずは、「知識・技能」が「A」の人数です。

緑色のセルに、「知識・技能」が「A」の生徒の人数を表示する数式を入力します。
(これまでのデータに、列を追加しています。)

『G14』に入力する数式

=COUNTIF(G3:G12,F14)

数式の解説

G3〜G12の中から、F14の値(A)と同じものの数を表示する。

このように数式を全てのセルに入力すれば、完成です。
人数がひと目でわかります。

ただし、コピーして貼り付ける場合は、相対参照・絶対参照に注意してください。

まとめ

今回は、前回紹介した『【教員×Excel】成績処理を自動化①関数「IF」の活用』の応用編として、

  • 基準の一括変更
  • 人数の確認

を行うための数式を紹介しました。

どれも簡単な数式ですので、理解できれば、さまざまな内容に応用することができます。

今回の記事を参考にしていただき、ぜひ、自らの仕事効率アップに役立てていただければと思います。

今回も最後まで読んでいただき、ありがとうございました。
これからも、教員のみなさんにとって有意義な内容をお伝えできればと思います。