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 番目の列の値を返す

VLOOKUP 関数の仕様には、少し気を付ける点があります。

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

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

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

個数のカウント #

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

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

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

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

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

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

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