神奈川大学 経済学部
2010年度経済情報処理II

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

第9課の目標

↑経済情報処理(2010)ホームページに戻る

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

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

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

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

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

給与の収入金額 給与所得控除額
1,625,000円まで 650,000円
1,625,001円から 1,800,000円まで 収入×0.4

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

そこで、給与の収入金額が年間180万円までのケースについて、給与所得額を計算してみることにする。data05.xlsx の 例題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に入力した計算式を B3B181にコピーする

できあがりはこんな感じ

練習問題1

  1. data05.xlsx の 例題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. メタボリック診断

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

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

データが男性か女性かを判断するときには、論理演算とよばれる計算が使える。論理演算では、条件を式とすることによって条件が成立していれば真(TRUE)、成立していなければ偽(FALSE)を演算結果として返す。あるセルのデータが、文字列と等しいことを確認するためには、 = という演算子が使える。

セル F2 ← =B2="女性"

女性の前後についている"(ダブルクォーテーション)は、これでくくられているデータが文字列を表していることをExcelに伝えるためのもの。

この計算式は、性別をチェックして、男性ならばFALSE(偽)、女性ならばTRUE(真)という値(論理値)を返す。個人番号1の人は男性なので、FALSE と表示されれば OK。

Step2. ウェストサイズが85cm以上か、90cm以上かをチェックする計算式を作る

Step1と同様に、ウェストサイズが85cm以上、90cm以上であるかどうかをチェックする計算式を作る。左辺が右辺以上(同じか大きい)時には >= という演算子が使える。

セル G2 ← =E2>=85
セル H2 ← =E2>=90

ここまで入力するとこんな感じ

Step3. 女性の時の処理を書いてみる

Step1と2の計算式では、単に性別とウェストサイズを論理値として計算するだけだったので求めている処理を行えていない。必要な処理は以下の3つになる。

  1. 「女性」 かつ 「ウェストサイズが90cm以上」 ならば 「メタボ」
  2. 「男性」 かつ 「ウェストサイズが85cm以上」 ならば 「メタボ」
  3. 上記2つのケース以外 ならば 「非メタボ」

Excelで「かつ」(論理積)を使う場合は、演算子ではなく関数 AND( ) を使う。AND関数は引数として複数の論理式をとり、全ての論理式が TRUEならば TRUEを、一つでも FALSEがあれば FALSEを返す関数である。

「女性 かつ ウェスト90cm以上」は、AND( )を使って書くと次のようになる。
セルI2 ← =AND(F2,H2)

個人番号4の人は女性で、ウェストが90cmあるからTRUEになればOK。コピーするとこんな感じになって,確かになっている。この条件で、既に女性の場合の条件は全てカバーしているので、I列がTRUEであればその人は女性でメタボである。

Step4. 男性のときの処理を書いてみる

Step3と同様に J列に男性の場合の計算式を入れてみる。この場合、「女性か?」という条件をそのまま使うわけにはいかないので、女性という条件を否定(NOT)して使うことになる。Excelでは論理否定はNOT( )関数を用いるので、「男性 かつ ウェスト 85cm以上」は次のようになる。

セルJ2 ← =AND(NOT(F2),G2)

Step5. 女性のメタボと男性のメタボをまとめてみる

女性でメタボなケースと、男性でメタボなケースのいずれかが成立していれば、その人はメタボであると判断してよい。このような場合をExcelではOR()関数を用いて表すことができる。

セルK2 ← =OR(I2, J2)

このような計算式を入力すると、I2セル または J2セルが TRUEのときに、計算結果がTRUEとなる。つまり、(女性 かつ メタボ) または (男性 かつ メタボ)のケースで TRUEとなるので、これが求める処理結果となる。

Step 6. 表示を「メタボ?」「OK」に書き換えてみる

Step5までの処理では、メタボかどうかの判別結果が論理値で表示されていてあまり分かり易くない。そこで、IF()関数を使ってK列の結果を書きなおしてみよう。

セルL2 ← =IF(K2,"メタボ?", "OK"))

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

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

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

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

Tips: IF関数とフローチャート

練習問題3

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

練習問題4

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

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

練習問題5

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

練習問題6

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

((性別=男性) かつ (ウェスト >=85cm)) または ((性別=女性) かつ (ウェスト >= 90cm)) であれば「メタボ?」

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

確認問題
data05.xlsx ブックの 確認 シートに入っているデータを用いて、60点以上、80点未満の人の数を数えなさい(ヒント: 条件を満たす場合に 1、満たさない場合に 0となるような 計算式を作れば、SUM関数を使って人数が数えられる)

↑経済情報処理(2010)ホームページに戻る