準備問題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. 賃金計算
労働基準法では法定労働時間を超えた場合には賃金率（時給）に割り増しが必要となる。具体的には1日に8時間以上働いた場合は、8時間を超えた部分については25%以上の割り増しが必要となる。
data05.xlsx の 残業手当 シートには、タイムカードで記録した始業時刻と終業時刻が記録されている。このデータを使って、
という操作を行ってみる。
Step 1. 就業時間の計算
終業時刻−始業時刻 で就業時間が計算できる。ただし、この終業時間だと1時間の休憩時間が必要なので、
就業時間 = 終業時刻−始業時刻−1時間
で計算できる。このデータでは、時間と分が分けて入力されているので、差を取るとき分の分は1/60にして時間単位にしてから計算しないと話が屋個敷くなることに注意。
8:05〜20:19まで働いて、12時間14分。休憩時間が1時間あるので就業時間は11時間14分。14分を60で割って時間単位にすると、0.233333333333となるので上の「就業時間」の欄は11.2333333となっている。
Tips. Excelでの時刻と日付の扱い
Step 2. 所定内賃金の計算
日の所定労働時間（この場合は8時間）以内の労働時間については、通常の時給（データ例では900円になっている）が適用される。労働時間全体で8時間を超えている場合は、8時間までは通常の時給、8時間を超えた分は割増賃金として計算する。つまり、所定内賃金を計算するためには、
という計算を行う必要がある。このような場合分けは IF関数で行えばよい。
セル G6 ← =IF(F6<=8, G$1*F6, G$1*8)
この式の意味は、「F6セルの値（つまり、就業時間）が8時間以下ならばF6の値に時給を乗じて賃金を計算し、8時間を超えている場合は8時間分までを通常の時給で計算する」ということになる。
Step 3. 所定外賃金の計算
所定外賃金は所定労働時間（ここでは1日8時間）を超えた労働時間に適用される。割増率は法定での最低で25%としておこう。
これをExcelの計算式にすると、
セル H6 ← =IF(F6<=8, 0, 125%*G$1*(F6-8))
のようになる。
Step 4. 所定内賃金＋所定外賃金で日給を計算する
これは単に足し算をおこなうだけ。
練習問題3.
例題2の計算式では、所定労働時間と賃金割増率はそれぞれ 8時間、25%という定数で計算式に入れてあった。これをそれぞれG2, G3セルに入っているデータを参照するように変更しなさい。
練習問題4
例題2の計算式では22時〜5時の深夜時間帯の割増（さらに最低25%追加）の部分を計算していない。始業時刻、終業時刻のデータを使ってこの部分を計算する方法を考えてみなさい。
練習問題5
例題2と同じデータを、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関数とフローチャート
練習問題6
BMIは25以上で「肥満」、18.5未満で「低体重」、その中間が「普通体重」とされている。準備問題で計算したBMIのデータとIF関数を使って、それぞれの人が「低体重」、「普通体重」、「肥満」のいずれであるか表示しなさい。
練習問題7
A, B, Cの三科目を受験し、合格するためには以下の2つの条件のいずれかを満たしている必要がある。data05.xlsx ブックの 試験得点 シートのデータを使って、合否を判定する計算式を作りなさい。
練習問題8
ある試験の評価は、素点が60点未満で「不可」、60〜69点で「可」、70〜79点で「良」、80〜89点で「優」、90〜100点で「秀」であるとする。data05.xlsxブックの成績評価シートのデータを使って、成績評価を行う計算式を作成しなさい。
練習問題9
例題2で扱ったメタボリック検診の条件は、以下のように整理することができる。この条件を用いたIF関数で例題2と同じ処理を行ってみなさい。
（（性別=男性） かつ （ウェスト >=85cm）） または （（性別=女性） かつ （ウェスト >= 90cm）） であれば「メタボ？」
ヒント： Excelでは、「かつ」を表すためには AND関数、「または」を表すためには OR関数を用いる
©2012, Hiroshi Santa OGAWA
このページにアダルトコンテンツ、XXXコンテンツ類は一切含まれていません。暴力反対.