【教員×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”))

『E3』に入力する数式

①もし、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」の活用』の応用編として、「基準の一括変更」を行うための数式を紹介しました。

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

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