2007年度経済情報処理

第5回　場合分けを含む計算式

第5回の目標

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

準備確認問題

1. 条件付書式

Excelでは、ある条件を満たすセルだけ書式を変える(たとえば、文字を赤くする）ことができる。

例題1. 試験の結果が悪かった学生のチェック

下の表は、ある試験での成績を表している。得点が50点未満の学生は点数のセルを黄色く塗りつぶして目立つようにしたい

条件を満たすセルに色を設定するには？　→ Excel編 Q12

A B
1 学籍番号 得点
2 20075925 61
3 20079382 53
4 20072583 53
5 20077073 42
6 20078632 41
7 20078184 54
8 20079037 48
9 20071559 52
10 20071463 60
11 20072869 50
12 20071362 35
13 20075554 27

できあがりはこんな感じ

練習問題1.

  1. 例題1.で、50点未満ではなく48点未満を黄色く塗りつぶすようにしなさい
  2. 例題1. で、55点以上のセルは緑色に塗りつぶすよう条件を追加しなさい

練習問題2

例題1.で、50点未満の人の学籍番号も黄色く塗りつぶすようにしなさい

※ヒント：　「セルの値が」ではなく「数式が」を使う

2. 数値の大小比較

Excelは表計算ソフトなので、数値の大小あるいは等しい、等しくないの比較を行うことはよくある。このような比較に使う記号の一覧は、ヘルプを「比較演算子」で検索すれば出てくるが、主なものは以下の通りである。

表1. Excelでの比較演算子

比較内容 Excelでの書き方 数学での記号
AはBと等しい = ＝
AはBと等しくない <> ≠
AはB以上 >= ≧
AはBより大きい > ＞
AはB以下 <= ≦
AはB未満 < ＜

=は、計算式の始まりを表す記号としても使うのでちょっと混乱しやすいが、計算式の最初の文字として入力されている場合は計算式を表す記号、計算式の途中に入力されている場合はその前後が等しいことを意味する比較演算子と解釈される。

例題2. 数値の大小比較

下の表は、ある試験での成績を表している。学生に対するフィードバックを適切に行うために、「平均点未満の学生」には警告を出すことにしたい。

A B C
1 平均点
2
3 学籍番号 得点 平均点未満？
4 20075925 61
5 20079382 53
6 20072583 53
7 20077073 42
8 20078632 41
9 20078184 54
10 20079037 48
11 20071559 52
12 20071463 60
13 20072869 50
14 20071362 35
15 20075554 27

Step 1. 平均点を計算する

セル B1に、セルB4〜B15の平均点を求める関数をを挿入すれば OK。

合計、平均を求めるには？　→ Excel編 Q20

Step 2. 最初の学生の得点が平均点未満かどうか比較する

表1に挙げた比較演算子のうち、ここでは最初の人の得点（セルB4）が平均点（セルB1）「未満」であることを比較したいのであるから、

セル C4 ← =B4<B1

と入力してやればいい。この計算式を入力すると、画面上には以下のように表示される

セル C4には FALSE （偽）と表示されている。これは、入力した比較が「成立しなかった」という意味。セル B4の値は 61、 セルB1 は48.だから、セルB4はセルB1未満ではない。

Step 3. 他の学生の得点も平均点と比較する

単にセルC4にいれた計算式をコピーすればよい。ただし、そのままだと平均点の入っているB1セルへの参照もコピーに伴い書き換えられてしまうので、コピー前に絶対参照に書き換えておくこと。

絶対参照を使うには？　→ Excel編 Q21 Column

できあがりはこんな感じ

TRUE（真）となっている場所は、得点が平均点未満になっていることに注意。これは入力した比較が「成立した」という意味。

練習問題3.　例題2のデータをつかって以下の比較計算式を追加してみなさい

  1. D列に「平均点以下」かどうかを比較する式を入力してみなさい
  2. E列に「平均点以上」かどうかを比較する式を入力してみなさい
  3. F列に「平均点と等しくない」かどうかを比較する式を入力してみなさい

3. 条件付書式再び（数式指定編）

※練習問題2を自力で解けた人は読まなくても可

例題3. 例題2で作ったデータを使って、得点が平均点以下だった人の学籍番号を黄色にしてみる。

Step 1. セルA4に条件付書式を設定してみる

セルA4をアクティブセルにしてから、[書式]-[条件付書式]を選択する

Step 2. 条件の設定方法を「数式が」に変更する

変更すると、条件付書式の設定ダイアログが少し変わる

Step 3. 条件を設定する

数式指定の条件付書式では、数式として指定した内容が真（TRUE）なら設定した書式で表示される。既にC列に、平均点未満かどうかを表す TRUE（真）/FALSE（偽）の計算結果は出ているので、これをそのまま入力すればよい。

※今回は話の流れ上、C4に既に平均値未満かどうかの真偽が入っていたが、入っていない場合は直接ここに =B4<$B$1 と比較式を書いてもOK

Step 4. 条件をコピーする

残りのセルに一々同じ作業で設定するのは面倒。そこでコピーするのだが、普通にコピーすると値までコピーされてしまうので、全員同じ学籍番号になってしまう。これはまずい。このようなときには「形式を指定して貼付」で書式だけ貼り付ける。

まず、コピー元のセルA4をアクティブセルにして、通常のコピー操作を行う
次に、コピー先の範囲A5:A15 を選択する
[編集]-[形式を選択して貼り付け]を選ぶと、以下のようなダイアログが表示される。ここでは書式だけ貼り付けたいので、書式のラジオボタンを押して貼付を実行する

できあがりはこんな感じ

練習問題4. 例題3のデータを使って以下の処理を行ってみなさい

得点が平均点以上だった人の学籍番号が緑色になるように、書式に条件を追加してみなさい

練習問題5. 例題3のデータを使って以下の処理を行ってみなさい

  1. 得点が偶数の場合は得点のセルを赤く塗りつぶすように条件付書式を設定してみなさい
  2. 得点が奇数であり、50点以上の場合は学籍番号を赤の太字にするよう条件付書式を設定してみなさい（ヒント：　AND関数を使う）
  3. 得点が平均点以上であるか、または、学籍番号が奇数の人の学籍番号を青の大きなフォントになるように条件付書式を設定してみなさい（ヒント：　OR関数を使う）

4. 比較した結果で計算のやりかたを変える

例題4. 所得税額の計算

わが国の所得税は、累進課税と呼ばれる方式で計算され、課税所得が多い人ほど税率が上がるようになっている。平成19年4月1日現在の税率は以下の表の通り。この表を使って、課税される所得が0から300万まで変わったときにどのように所得税額が変わるかのグラフを作成したい。

課税される所得金額 税率 控除額
195万円以下 5% 0
195万円超〜330万円以下 10% 97500円
330万円超〜695万円以下 20% 427000円
695万円超〜900万円以下 23% 636000円
900万円超〜1800万円以下 33% 1536000円
1800万円超 40% 2796000円

この表での「税率」は、限界税率に相当するものなので単純に課税所得に税率を掛けると税金取りすぎになってしまう。そのために使う補正値が「控除額」。たとえば、「課税される所得金額」が700万円の場合は、

700万円×23%−63万6千円=97万4千円

が所得税額になる。

Step 1. 課税される所得金額の表を作る

とりあえず 0〜300万までを考えているので、1万刻みに0〜300万までのデータを作成しておく。

連続データを入力するには　→ Excel編 Q1

Step 2. 税率を「課税される所得金額」から計算する

上に挙げた税率表を見ると、0〜300万円の範囲では、195万円が税率5%と10%の区切りになっている。これは、「195万円以下かそうでないか」という数値の比較結果を使って結果を変える方法があれがOK。Excelではこういうとき IF関数を使う

セルB2 ← =IF(A2<=195,5%,10%)

IF関数は、
IF(条件, 条件が真（TRUE）の時に実行する計算式, 条件が偽（FALSE）の時に実行する計算式）
という構造になっている。上の例だと、A2<=195 が条件になるので、この条件が成立すれば IF関数の値は 5%、成立しなければ 10%となる。

Step 3 計算式を残りの B列にコピーする

A列の値が195を超えるあたりの様子はこんな感じ。確かに、A列の値が195超で税率が5%から10%に上がっている。

練習問題6. 例題4のデータを使って以下の操作を行いなさい

  1. 例題と同様に控除額を求める計算式を入力しなさい
  2. 課税される所得金額、所得税率、控除額のデータから、D列に所得税額を計算しなさい

5. 少し複雑な条件で比較を行なう

例題4では0〜300万円までの課税される所得を対象としていたため、IF関数の中に出てくる比較は「課税される所得が195万円以下であるか」の1つだけで済んだ。しかし、所得税の税率表は課税される所得に応じて6段階に分かれているため、課税される所得に応じて6種類の税率や控除額が得られる計算式を作らないと実用的ではない。

例題5. 所得税の計算（課税される所得が0〜500万円）

Step 1. オートフィルを使って、例題1で作成した税額表の「課税される所得金額」を500万円まで増やす

Step 2. 区切りが195万円と330万円の2箇所あるので、次のような計算式をセルB2に入れる

セルB2 ← =IF(A2<=195,5%,IF(A2<=330,10%,20%))

Step 3. セルB2の計算式を、B列で必要なだけコピーする

できあがりの 330万円付近はこんな感じ

セルB2 に入れた =IF(A2<=195,5%,IF(A2<=330,10%,20%))　という計算式は、一見ヤヤコシイが丁寧にみていけば実はたいしたことはない。IF関数の構造は前の例題でやった通りだが、ここでの新しいポイントは、IF関数の中にまたIF関数が入っていること。

=IF(A2<=195,5%,IF(A2<=330,10%,20%))

を分解すると次のようになる。

=IF(A2<=195,5%,※)

※は IF(A2<=330,10%,20%)

つまり、最初の条件 A2<=195 が成立すれば 5%で終わり。
成立しなかった場合、例題4では単純に10%であったが、今回はIF(A2<=330,10%,20%)を実行するので、A2<=330 なら 10%、そうでなければ 20%になる

練習問題7. 例題5のデータを使って以下の作業を行いなさい

  1. 課税される所得金額が0〜2000万円までの範囲で所得税率を計算しなさい
  2. 税率と同様に控除額も計算しなさい
  3. 税率と控除額、さらに課税される所得金額から、所得税額を計算しなさい
  4. 課税される所得金額（0〜2000万）を横軸横軸、所得税額を縦軸に取って折れ線グラフを作成しなさい
まとめの問題
仮想的な試験の点数を入れてあるブック、score2.xls　を使って以下の作業を行いなさい
1. 各学生の5科目平均得点を計算しなさい
2. 5科目平均得点を使って、以下の基準でランク付けしなさい
得点範囲 成績ランク
0≦得点＜60 0
60≦得点<70 1
70≦得点<80 2
80≦得点 3

3. 5科目の平均得点が60点未満の学生については、警告するために学籍番号のセルを黄色で塗りつぶしなさい
4. 5科目の平均得点が80点以上の学生については、表彰するために学籍番号のセルを緑色で塗りつぶしなさい

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

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