【教員×Excel】成績処理を自動化②「絶対参照」と「COUNTIF」
みなさんこんにちは。
今回の内容は、前回紹介した『【教員×Excel】成績処理を自動化①関数「IF」の活用』の応用編です。
応用編といっても、使用する関数は基本的なものだけですので、安心して活用してみてください。
◆基準となる数値を一括で変更する
成績をつけると、こんなことを思う時がありますよね。
「A」の数が多すぎるな。
「C」の基準を少し下げようかな。
例えば、８０点以上を「A」としていたものを、９５点以上に変更したいとします。
（数式は、前回の記事のものを使用します）
【変更前】=IF(B3>=80,”A”,IF(B3>=30,”B”,”C”))
【変更後】=IF(B3>=95,”A”,IF(B3>=30,”B”,”C”))
変更自体は簡単ですが、全てのセルに入力されている数式を変更する必要があります。またコピーして、必要なところに貼り付けをしなくてはいけないので面倒ですし、ミスが起こる可能性もあります。
▼基準となる数値を入力するセルを作る
成績を一括で変更するためには、まず基準となる数値を入力するセルを作ります。
そして、基準となる数値を入力します。
今回は上の画像のように、緑色のところに作成していますが、
どこのセルに作っても問題ありません。
▼数式に、セル番地を入力する。
初めに、「伊達由加」さんの「知識・技能」の数式を変更します。
緑色のセル（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」の評価が、自動的に変更されます。
▼「絶対参照」を活用する
数式ができれば、あとは全てのセルにコピーして貼り付けるだけです。
しかし、ここで問題が起きます。
数式をコピーして貼り付けると、参照すべきセル番地（B１４やB１５）がズレてしまうのです。
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」や「B１５」を参照してほしい場合は、その設定を変更しなくてはいけません。
そこで使用する設定が「絶対参照」です。
絶対参照にすると、参照するセルが常に固定されます。
絶対参照にするには、参照したいセル番地に「＄」マークを付けるだけです。
「相対参照」・・・=IF(B3>=B14,”A”,IF(B3>=B15,”B”,”C”))
「絶対参照」・・・=IF(B3>=＄B＄14,”A”,IF(B3>=＄B＄15,”B”,”C”))
絶対参照にしたいセル番地をクリックして「F４キー」を押すと、自動的に絶対参照に変更されます。
絶対参照にしておけば、どこに数式をコピーしても、参照すべき「B14」や「B15」が変わることはありません。
◆まとめ
今回は、前回紹介した『【教員×Excel】成績処理を自動化①関数「IF」の活用』の応用編として、「基準の一括変更」を行うための数式を紹介しました。
どれも簡単な数式ですので、理解できれば、さまざまな内容に応用することができます。
今回の記事を参考にしていただき、ぜひ、自らの仕事効率アップに役立てていただければと思います。