第9課 場合分けを含む計算式（IF関数）

↑経済情報処理（2008）ホームページに戻る

準備問題
BMI（Body Mass Index）は　体重（kg）÷身長（m）÷身長（m）で計算できる。たとえば身長165cm、体重60kgならば BMIは約22になる。
data05.xls BMI シートに入っているデータを使って、それぞれの人のBMIを計算しなさい。ただし、件数が多いため、計算式のコピーを上手に使うこと。

1. 単純な場合分けを含む計算式

例題1. バイト代と給与所得金額と扶養控除（データは data05.xls ）

わが国の所得税制では、バイト代が給与として支払われている場合は、給与の収入金額から給与所得控除と呼ばれる金額を差し引いたものが「給与所得」として扱われる。給与等の収入金額が180万以下の場合は、以下の表に示したように給与所得控除額が決まる。

給与所得控除額（給与の収入金額が660万円以下の場合）

学生が親の扶養家族となっている場合には、「所得が年間38万円未満」という条件が必要であるから、給与の収入金額−給与所得控除額　で計算される給与所得の金額が38万未満であることが必要である。これを超えると扶養控除の対象から外れてしまうため、親の税金がドンと増えることになる。

そこで、給与の収入金額が年間180万円までのケースについて、給与所得額を計算してみることにする。data05.xls の　例題1 シートには、給与の収入金額が1万円単位で1万円から180万円まで入力してある。

給与所得控除額は、給与の収入金額が1,625,000円以下の場合と、1,625,001円以上で計算方法が違う。整理するとこんな感じ。このように場合分けがある計算が必要なときに、Excelでは IF関数を使う。

IF関数の一般的な形式は以下の通り

IF(条件, 処理T, 処理F)

Step1. 計算式を入力する

セルB2 ← =IF(A2<=1625000, 650000, A2*0.4)

※A2<=1625000 が条件、650000は条件が成立したときに実行される処理T（このように、計算をしないで単に定数にするのもアリ）、A2*0.4は条件が成立しなかったときに実行される処理Fに相当する。

Step2. 計算式をコピーする

セル B2に入力した計算式を B3 〜 B181にコピーする

できあがりはこんな感じ

練習問題1

1. data05.xls の　例題1シートに入っているデータと、例題1の結果を使って給与所得額を計算しなさい。ただし、給与所得額は、給与の収入金額−給与所得控除額で計算される。
2. 給与所得額が負の値になるのは変なので、IF関数を使って負の値は0に、正の値はそのままの値になるよう変換しなさい。
3. 給与所得額が38万円未満であれば「控除OK」、それ以上であれば「控除NG」と表示するようにIF関数を使った計算式を作りなさい。（解答例）

練習問題2

1. BMIは25以上で「肥満」とされている。準備問題で計算したBMIのデータとIF関数を使って、肥満ならば「肥満」、そうでなければ「OK」と表示する計算式が入った列をつくりなさい。
2. BMIは18.5未満で「低体重」とされている。準備問題で計算したBMIのデータとIF関数を使って、肥満ならば「肥満」、そうでなければ「OK」と表示する計算式が入った列をつくりなさい（解答例）。

2. 複数の条件を組み合わせた場合分けが必要な計算式

例題2. メタボリック診断

今年の春から実施されている健康診断におけるメタボリック判定は、ウェストサイズが男性の場合85cm以上、女性の場合90cm以上であればいくつかの検査値と組み合わせて判定することになっている。つまり、性とウェストサイズの2つの条件を組み合わせて判断を行うことになる。

Step1 まず、男女別に処理することを考える（data05.xlsの例題2 シートに入っているデータを使う）

セル F2　← =IF(B2=1,"男性","女性")

このIF関数だと、性別をチェックして、男性ならば「男性」、女性ならば「女性」と表示するようになる。個人番号1の人は男性なので、「男性」と表示されれば OK。

Step2. 男性の時の処理を書き換えてみる

Step1の計算式では、単に性別を表示するだけだったので求めている処理を行えていない。そこで、まず「男性」と表示している部分に、男性用のウェストサイズチェックを行うIF関数を入れてみる。具体的にはこんなかんじ

セルF2 ← =IF(B2=1,IF(E2>=85,"メタボ？","OK"),"女性")

個人番号1の人は男性で、ウェストが93cmあるから「メタボ？」と表示されればOK。ついでに、男性で非メタボな人でもうまく行くか確認するため、セル F3 にコピーしてみよう。

※この書き換えを行う際に、セル F2に入っている内容を全部消して書き直したりしないこと。セルF2 をアクティブセルにしてから、F2 キーを押すと編集モードになるので、カーソルを移動して必要な部分だけ書き換える。手直しの度に全部書き換えるのは時間の無駄。

Step3. 女性の時の処理を書き換えてみる

更に、セル F2 の計算式を書き換えて、単に「女性」と表示していただけの部分にウェストサイズチェックを入れてみる。

セルF2 ← =IF(B2=1,IF(E2>=85,"メタボ？","OK"),IF(E2>=90,"メタボ？","OK"))

だいぶ計算式がややこしくなってきたので、入力ミスでエラーが出るかもしれない。もしエラーが出たら、ダブルクォート " の対応関係や、(と)の数が合っているかなどを確認しよう。

※エラーが出たからといって、計算式を消してしまわないこと。入力ミスでエラーがでていることは自明なのだから、消してもう一度入力しても同じことになる。F2 キーを押して、入力済みの計算式を編集して修正すること。どうしても修正箇所が分からなければ、計算式を残した状態で先生なりアシスタントなりに聞く。消してしまってから「何が間違っていたのでしょう」と質問されても訳が分かりません。

Step4. コピーして、結果をチェックする

計算式を全体にコピーして、結果を確認する。確認ポイントは少なくとも以下の4人

1. 個人番号1: 男性でメタボ？
2. 個人番号2: 男性で OK
3. 個人番号3: 女性で OK
4. 個人番号4: 女性でメタボ？

うまくいっていれば、できあがりはこんな感じ。

練習問題3

BMIは25以上で「肥満」、18.5未満で「低体重」、その中間が「普通体重」とされている。準備問題で計算したBMIのデータとIF関数を使って、それぞれの人が「低体重」、「普通体重」、「肥満」のいずれであるか表示しなさい（解答例）。

練習問題4

A, B, Cの三科目を受験し、合格するためには以下の2つの条件のいずれかを満たしている必要がある。data05.xls ブックの 試験得点 シートのデータを使って、合否を判定する計算式を作りなさい。

1. A, B, Cそれぞれの科目の合格点は60点である。3科目全てに合格すれば、試験全体も合格。
2. A, B, Cそれぞれの科目の合格点は60点である。いずれか2科目合格していて、合計点が230点を超えていれば試験合格（解答例）。

練習問題5

ある試験の評価は、素点が60点未満で「不可」、60〜69点で「可」、70〜79点で「良」、80〜89点で「優」、90〜100点で「秀」であるとする。data05.xlsブックの成績評価シートのデータを使って、成績評価を行う計算式を作成しなさい（解答例）。

練習問題6

例題2で扱ったメタボリック検診の条件は、以下のように整理することができる。この条件を用いたIF関数で例題2と同じ処理を行ってみなさい。

（（性別=男性） かつ （ウェスト >=85cm））　または　（（性別=女性） かつ （ウェスト >= 90cm））　であれば「メタボ？」（解答例）

ヒント：　Excelでは、「かつ」を表すためには AND関数、「または」を表すためには OR関数を用いる

↑経済情報処理（2008）ホームページに戻る

©2008, Hiroshi Santa OGAWA
このページにアダルトコンテンツ、XXXコンテンツ類は一切含まれていません。暴力反対.