準備問題BMI（Body Mass Index）は 体重（kg）÷身長（m）÷身長（m）で計算できる。たとえば身長165cm、体重60kgならば BMIは60÷1.65÷1.65で約22になる。
data05.xlsx BMI シートに入っているデータを使って、それぞれの人のBMIを計算しなさい。ただし、件数が多いため、計算式のコピーを上手に使うこと。
準備問題例題1. 試験の成績と合否
data05.xlsxの例題1シートには、仮想的な試験の点数データが入っている。このデータを使って
の2つの操作を行いたい。
この操作は、得点が60点以上か60点未満かで判断して違うことを行う必要がある。このように「ある条件を満たすかどうか」で操作を変える場合、ExcelではIF関数という関数を用いる。
Step 1. まずC2 に「合格」「落第」の表示を行う
Excelの場合、計算式が入っているセルの値は計算式の値になるので、IF関数が返す値を「合格」「落第」の文字列にすればこの目的は達成できる。IF関数の書式は、
IF(条件, 条件が成立した場合の値, 条件が成立しない場合の値)
となっている。このケースでは
であるから、これをそのままExcelの計算式にすればよい。
セル C2 ← =IF(B2>=60, "合格", "落第")
最初の引数である、B2>=60は、「B2セルの内容が60以上か？」という条件を表す式になる。data05.xlsxのデータでは、B2は60なのでこの条件は成立する。条件が成立した場合は、2つめの引数がIF関数の値となるので、C2 は「合格」と表示されるはず。
Step 2. 計算式をコピーして全てのデータについて合否が判定できるようにする。
Step 3. 不合格者数を数える
Excelでこの目的を達成する方法はいくつかあるが、ここでは不合格者数を数える→落第なら1、合格なら0が入っている列を作成して、その列のデータを全部合計するという愚直な方法を採用する。
この場合、条件は「落第か？」ということになるので、Step 2で作成した「合格」「落第」のデータが使える。
セル D2 ← =IF(C2="落第", 1, 0)
最初の引数である C2="落第" は、C2セルの値が「落第」という文字列かどうかを検査している。落第なら、第2引数が関数の値として評価されるので関数の値は1となり、合格なら第3引数が評価されて0になる。
計算式をコピーしたら、セル G1 に合計するための計算式（SUM関数を使う）を入れておしまい。
（*1）「SUMIF関数使えばいいんじゃね？」 と思ったひとは、頭が固いので自己批判しましょう。条件が複雑になったらSUMIFではすぐ破綻します。「ピボット・テーブル使えばいいんじゃね？」と思った人は経済情報処理なんか受講して単位稼ぎしてないで、他の専攻科目を履修しましょう。
Tips. Excelの比較演算子
Excelの比較演算子は以下のようになっている。使える記号の制限から、数学で使う比較演算子とは違うので注意。
| 説明 | 数学での表記 | Excelでの表記 |
|---|---|---|
| AはBより大きい（Bと等しい場合は含まない） | A＞B | A>B |
| AはB以上（Bと等しい場合を含む） | A≧B | A>=B |
| AはBと等しい | A=B | A=B |
| AはB以下（Bと等しい場合を含む） | A≦B | A<=B |
| AはB未満（Bと等しい場合は含まない） | A＜B | A<B |
| AとBは等しくない | A≠B | A<>B |
練習問題1.
例題1では合格点（60点）が予め決まっていたが、都合により不合格者数が20人程度になるよう調整したい。
練習問題2
例題2. 製品のランク付け
ある農家では出荷する作物を重さを用いて、S, M, Lの3種類に分けて出荷することにしている。重さとランクと関係は以下の表の通りである。ただし、あるランクに入る作物は、最低重量≦作物重さ＜最大重量を満たすが、Lサイズは重量の上限を定めない。
| ランク | 最小重量(g) | 最大重量(g) |
| S | 0 | 50 |
| M | 50 | 100 |
| L | 100 |
方針： 重さでS, M, Lの3つに分けたいが、ExcelのIF関数では「条件が成立した」「条件が成立しなかった」の2つにしか分けられない。そのため、まず「S」「S以外」の2つに分け、さらに「S以外」を「M」と「L」に分ける。
Step 1. 「S」と「S以外」に分ける
作物の重量が0以上であることは自明であるから、ある作物がSランクとなるのは重量が50g未満の時である。セルB2に
=IF(A2<50,"S","S以外")
という計算式を入れることで、SとS以外に分けることができる。
Step 2. 「S以外」を「M」と「L」に分ける
S以外の作物は、全て50g以上ある。よって、S以外の作物のうち、100g未満のものがM、100g以上のものがLとなる。つまり
IF(A2<100, "M", "L")
となる。この計算式を、Step 1で作成した計算式の"S以外"の位置に埋め込むことで、「S以外の時には追加でこの計算式を計算する」という指示を与えることができる。できあがりはこんな感じ。
=IF(A2<50,"S", IF(A2<100, "M", "L"))
Step 3. 計算式をコピーする
計算式をコピーして表を完成させる。できあがりはこんな感じ。
練習問題3.
例題では区切りの重量（50gとか100g）を計算式の中に数値として埋め込んでいたが、こういう「後になったら訳がわからない数字」（俗称 magic number）はできる限り使わない方がよい。そこで、例題2シートに入っているランクの表のなかの数値を計算式の中で参照するように書き直しなさい。
練習問題4
出荷規格が変わって、130g以上の作物はLLランクとすることになった。この判断ができるように練習問題4シートに適切な計算式を入力しなさい。ただし、可能ならば計算式の中に具体的な数値を埋め込まず、ランクの表の中の数値を参照して書くこと。
練習問題5
ある試験の評価は、素点が60点未満で「不可」、60〜69点で「可」、70〜79点で「良」、80〜89点で「優」、90〜100点で「秀」であるとする。data05.xlsxブックの成績評価シートのデータを使って、成績評価を行う計算式を作成しなさい。
練習問題6（発展）
労働基準法では法定労働時間を超えた場合には賃金率（時給）に割り増しが必要となる。具体的には1日に8時間以上働いた場合は、8時間を超えた部分については25%以上の割り増しが必要となる。
data05.xlsx の 残業手当 シートには、タイムカードで記録した始業時刻と終業時刻が記録されている。このデータを使って、
という操作を行いなさい。
Tips. Excelでの時刻と日付の扱い
練習問題7（発展）.
練習問題6の計算式では、所定労働時間と賃金割増率はそれぞれ 8時間、25%という定数で計算式に入れてあった。これをそれぞれG2, G3セルに入っているデータを参照するように変更しなさい。
練習問題8（発展）
練習問題6の計算式では22時〜5時の深夜時間帯の割増（さらに最低25%追加）の部分を計算していない。始業時刻、終業時刻のデータを使ってこの部分を計算する方法を考えてみなさい。
練習問題9（発展）
練習問題6と同じデータを、Excelのシリアル値を使って入力したものが残業手当2 シート。このデータを使って同じような計算をしてみなさい。
(注意）書式が「時刻」や「日付」になっているセルのデータをつかって計算すると、計算式が入っているセルの書式も自動的に時刻や日付になってしまう。ここでは、「就業時間」の計算式が入っているセルが自動的に「時刻」に書式設定されてしまう。当然、就業時間としてはマトモに表示されないことになるので、書式を「標準」に再設定する必要がある。
例題3. メタボリック診断
2008年の春から実施されている健康診断におけるメタボリック判定は、ウェストサイズが男性の場合85cm以上、女性の場合90cm以上であればいくつかの検査値と組み合わせて判定することになっている。つまり、性とウェストサイズの2つの条件を組み合わせて判断を行うことになる。
Step 1. 条件を整理する
この条件を整理するとこんな感じ。性と85cm, 90cmが条件になってることがわかる。
| ウェスト85cm未満 | ウェスト85cm以上 | ウェスト90cm以上 | |
| 女性 | 非メタボ | 非メタボ | メタボ |
| 男性 | 非メタボ | メタボ | メタボ |
Step 2. 疑似コードを使って条件を IF関数の書式に落とし込んでいく
これをIF関数の書式に落とし込んでいくことを考える。まず性で判断することを考えると、
IF(女性？, ウェスト90cm以上ならメタボ, ウェスト85cm以上ならメタボ） ①
という感じになる（*2）。もちろんこのままではExcelに入力できないが、こんな感じの擬似コードで問題を整理するのは悪くない。
さらに、「ウェスト90cm以上ならメタボ」「ウェスト85cm以上ならメタボ」という部分はそれぞれIF関数を使って記述することができる。
IF(ウェスト90cm以上, "メタボ", "非メタボ") ②
IF(ウェスト85cm以上, "メタボ", "非メタボ") ③
さて、①〜③を組み合わせてみるとこんな風になる。
IF(女性？, IF(ウェスト90cm以上, "メタボ", "非メタボ"), IF(ウェスト85cm以上, "メタボ", "非メタボ"))
Step 3. データが入っているセルなどを考慮して、Excelの関数として記述する
セルF2 ← =IF(B2="女性", IF(E2>=90, "メタボ", "非メタボ"), IF(E2>=85, "メタボ", "非メタボ"))
できあがりはこんな感じ
(*2)この式は性別が「女性」と「それ以外」に分けているが、「それ以外」の方の処理は「男性」を前提とした処理になっている。女性でも男性でもないような性別の人がいるデータ群を扱う場合はもう少し考慮する必要があるが、ここではそこが本質ではないのでスルーする。
Tips: IF関数とフローチャート
練習問題10
BMIは25以上で「肥満」、18.5未満で「低体重」、その中間が「普通体重」とされている。準備問題で計算したBMIのデータとIF関数を使って、それぞれの人が「低体重」、「普通体重」、「肥満」のいずれであるか表示しなさい。
練習問題11（発展）
例題2で扱ったメタボリック検診の条件は、以下のように整理することができる。この条件を用いたIF関数で例題2と同じ処理を行ってみなさい。
（（性別=男性） かつ （ウェスト >=85cm）） または （（性別=女性） かつ （ウェスト >= 90cm）） であれば「メタボ？」
ヒント： Excelでは、「かつ」を表すためには AND関数、「または」を表すためには OR関数を用いる
練習問題12（発展）
A, B, Cの三科目を受験し、合格するためには以下の2つの条件のいずれかを満たしている必要がある。data05.xlsx ブックの 試験得点 シートのデータを使って、合否を判定する計算式を作りなさい。
©2016, Hiroshi Santa OGAWA
このページにアダルトコンテンツ、XXXコンテンツ類は一切含まれていません。暴力反対.