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

Excel では，条件に応じて式の値を変えるための関数が用意されています．条件分岐の関数の利用法や，それを応用して個数を数える方法を調べましょう．
今回の実習では，数学の成績表を対象に

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

という操作を行ってみます．これらはいずれも「点数がどの区分に当てはまるかを調べる」というステップを含むので，条件分岐の関数が欠かせません．
なお，このページでは 23.2.4 コピー & ペーストと数式の入力 作ったデータを使います．まだデータを作っていない人は sample_meibo_for_23_2_12.xlsx をダウンロードしてください．

条件分岐

式の値を条件によって変えるには IF 関数 を使います．IF 関数には「条件式，条件が成り立つときの値，条件が成り立たないときの値」の 3 つ組を渡します．
ためしにやってみましょう．セル K1 に見出しとして「追試判定」と書いてから，セル K2 に

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

と書いてみてください．

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

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

条件の組合せ

IF 関数を使うときに，複数の条件を組み合わせることもできます．それには AND 関数や OR 関数というものを使います．これらの関数の意味は，名前を見れば明らかでしょう．AND 関数に 2 つ以上の条件を渡すと，全ての条件が「かつ」で繋がれます．また OR 関数の場合，与えられた条件が「または」で繋がれます．
これを使って，追試条件を「中間試験または期末試験の一方が 60 点未満」と再設定し直し，追試になる人を調べましょう．条件が「または」なので，使うのは OR 関数の方ですね．そこで K2 を

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

と書き直してみます．

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

値の分類

次に，得点を 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	秀

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

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

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

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

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

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

個数のカウント

最後に，成績の分布表を作ってみましょう．条件を満たすセルの個数を調べるには，COUNTIF 関数を使います．
まず，6 段階評価の一覧表の右端のセル C33 に「人数」という見出しを設けましょう．続いてセル C34 に

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

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

これで実際，最初に「論外」の人数が 0 人と求まります．幸いなことに，今回は論外の人がいませんでしたね．
あとは同様にして，この式を下にコピー & ペーストするだけです．ここでも検索範囲を絶対参照にしておいたことが効いて，検索したい値のみがペーストで変わっていきます．

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