23.2.12 条件分岐の利用とデータの検索・カウント

Excel では,条件に応じて式の値を変えるための関数が用意されています.条件分岐の関数の利用法や,それを応用して個数を数える方法を調べましょう.

今回の実習では,数学の成績表を対象に

  • 特定の点数以下の人を追試に判定する
  • 成績表の数値を 5 段階評価で表してみる
  • 成績の分布表を作る

という操作を行ってみます.これらはいずれも「点数がどの区分に当てはまるかを調べる」というステップを含むので,条件分岐の関数が欠かせません.

なお,このページでは hwb23.2.4 コピー & ペーストと数式の入力 作ったデータを使います.まだデータを作っていない人は sample_meibo_for_23_2_12.xlsx をダウンロードしてください.

条件分岐

式の値を条件によって変えるには IF 関数 を使います.IF 関数には「条件式,条件が成り立つときの値,条件が成り立たないときの値」の 3 つ組を渡します.

ためしにやってみましょう.セル K1 に見出しとして「追試判定」と書いてから,セル K2 に

=IF(J2<100,"追試","")

と書いてみてください.

23_2_12_a

これは「セル J2 の値 100 より小さければ追試と表示し,そうでなければ何も表示しない」という意味です.Excel では「文字列はダブルクォーテーションで囲む」という約束があるので,”追試” という書き方をしています.また追試でない場合は何も表示させないので,最後に “” があります.実際にやってみると,K2 には式が入りますが,値は何も表示されません.

23_2_12_b

ですが,この式を下の方までコピー & ペーストすると「追試」という文字列がいくつか現れます.これで確かに IF 関数が働いていることが見て取れます.

23_2_12_c

条件の組合せ

IF 関数を使うときに,複数の条件を組み合わせることもできます.それには AND 関数OR 関数というものを使います.これらの関数の意味は,名前を見れば明らかでしょう.AND 関数に 2 つ以上の条件を渡すと,全ての条件が「かつ」で繋がれます.また OR 関数の場合,与えられた条件が「または」で繋がれます.

これを使って,追試条件を「中間試験または期末試験の一方が 60 点未満」と再設定し直し,追試になる人を調べましょう.条件が「または」なので,使うのは OR 関数の方ですね.そこで K2 を

=IF(OR(G2<60,H2<60),"追試","")

と書き直してみます.

23_2_12_d

そして,この式を全ての行にコピーします.すると先ほどとは違う追試パターンが表示されるはずです.

23_2_12_e

値の分類

次に,得点を 5 段階評価に変換してみましょう.今回は「180 点以上が秀」「160 点以上が優」「130 点以上 160 点未満が良」「100 点以上 130 点未満が可」「50 点以上 130 点未満が不可」「50 点未満が論外」という秀・優・良・可・不可・論外の 6 段階で成績をつけてみます.

ここまでの知識だけで取り組むなら「IF 関数の中で IF 関数を使う」という方法があります.たとえばセル L2 に

IF(G2<50,"論外","不可")

と書いてみると,50 点未満なら「論外」,50 点以上なら「不可」と表示されます.しかしもちろん,このままでは正しくなく,50 点以上の場合を「不可」とそれ以外とに分けないといけません.そこで今の式の “不可” の部分にさらに IF を書きます.

=IF(G2<50,"論外",IF(G2<100,"不可","可"))

こうすると,今度は 50 点以上のときに限って後ろの IF が呼び出され「100 点未満なら不可,それ以外なら可」が表示されます.これを何度も繰り返せば,いちおう全部の分類を書き下すことができます.

でもこの方法を使うと,(評価の段数 – 1) 回だけ IF 関数を使わなければならず,式を書くのが大変です.そのために VLOOKUP 関数を使ってみます.VLOOKUP 関数を使うと,表の中から「ある列の値で検索をかけ,一番近い値のところでヒットし,そしてヒットしたものと同じ行にあるデータを引っ張りだす」という操作ができます.

VLOOKUP 関数ではまず,評価一覧の表が必要です.そこでたとえば A34:B40 の範囲に,次の表を書きこんでください.

得点 評価
0 論外
50 不可
100
130
160
180

23_2_12_f

そしてセル L1 に「評価」と書いて見出しを作ってから,セル L2 に

=VLOOKUP(J2,$A$35:$B$40,2)

と書き込みます.VLOOKUP 関数に渡した J2, $A$35:$B$40, 2 という値は,それぞれ次の意味です.

  • セル J2 は,表の中から検索したい値
  • $A$34:$A$39 は検索する表の範囲
  • ヒットした行の左から 2 番目の列の値を返す

23_2_12_g

cautionVLOOKUP 関数の仕様には,少し気を付ける点があります.

  • 値の検索に使われるのは,常に一番左の列です.一番左にない列で検索したいときは,予めその列を一番左に移動させる必要があります.
  • 検索対象となる表の左端の列は,上から順に小さい値が並ぶようにしておかないといけません.
  • ここでの VLOOKUP 関数の使い方は近似検索と呼ばれます.近似検索モードでは表の一番左の列を上から順に読んでいき,与えられた値を初めて超えた行がヒットします.しばしば VLOOKUP は「近い値を調べる」と言われることがありますが,そう思ってしまうと実際の挙動とイメージとがズレてしまいます.

これで検索ができるので,あとは式をコピー & ペーストするだけです.その際,検索対象の表を絶対参照で指定したことに気を付けましょう.このおかげで,コピー & ペーストができます.

23_2_12_h

このように,6 段階評価をすることができました.

個数のカウント

最後に,成績の分布表を作ってみましょう.条件を満たすセルの個数を調べるには,COUNTIF 関数を使います.

まず,6 段階評価の一覧表の右端のセル C33 に「人数」という見出しを設けましょう.続いてセル C34 に

=COUNTIF($L$2:$L$31,B34)

と書き込みましょう.この式は「$L$2:$L$31 の範囲から,B34 と同じ値のセルの個数を数える」意味です.

23_2_12_i

これで実際,最初に「論外」の人数が 0 人と求まります.幸いなことに,今回は論外の人がいませんでしたね.

あとは同様にして,この式を下にコピー & ペーストするだけです.ここでも検索範囲を絶対参照にしておいたことが効いて,検索したい値のみがペーストで変わっていきます.

23_2_12_j

ちなみに,条件を複数にした COUNTIFS 関数というものもあります.これは「与えられた長方形の表を対象に,複数の列に対して条件を課して,全てに当てはまる行の個数を調べること」を念頭とした関数です.