2006年度経済情報処理
(*1) return valueの訳としては返値（かえりち）も使われる。世の中には一つの文書の中で両方を使っているケースもあるようだが、ここでは「返り血」と聞き違えてギョッとする人がいると困るので戻り値に統一しておく。
(*2)紀伊國屋BookWebや Amazonなどのデータベースで、Excel 関数 を検索してみればいかに解説書が沢山出ているかわかる。ヘルプがわかりやすければ解説書をあえて買う必要があるわけがない。口の悪い人に言わせると、Excelのヘルプは火星語で書いてあるそうだが、これは火星人に失礼だろう。
例題1. 消費税の計算で1円未満の端数が出た場合の切り捨て処理を行う
消費税が5%のとき、150円のカップラーメンを3個購入した。消費税額と、消費税込みの支払い総額は幾らになるかを求めてみよう。(*3)
Step 1. データを入力する
まず、計算に必要な基礎データと説明用の文字列を入力する。Excelは単に 5% と入力すると、自動的に0.05と解釈してくれるので便利。
Step 2. 計算式をとりあえず入力する
ここまで学んだ計算式の作り方を思い出して、
を入力すればよい。どうしても思い出せない人はカンペ参照。計算式が入ると、こんな感じになる。しかし、このままだと消費税額に1円未満の端数がついてしまうので、切り捨て処理を行いたい。
Step 3. 切り捨て処理を INT 関数で行う
切り捨て処理のような操作は、ここまでに出てきた四則演算の範囲ではできない(*4)。Excelでは、このような処理を行うためにいくつかの関数があるが、ここでは一番単純な INT 関数を用いる。INT関数は、数値の引数を一つとって、戻り値としては引数を超えない最大の整数を戻す関数である。INT関数の引数は
のいずれでもよい。もちろん、参照先のセルに入っている値や計算式の結果が数値でなければエラーとなる。この例題では、セル D6 の消費税額の1円未満を切り捨てたいので、セル D6 を以下のように書き換える
すると、小数点以下が切り捨てられて下のような計算結果となる。
(*3)現在は税込価格での表示が原則であり、税込価格に含まれる消費税相当額は「税込価格×5/105」と考えるのが原則。税抜価格から消費税額を求めて表示するという扱いは適切な操作ではない。詳細は国税庁web（http://www.nta.go.jp/）などを確認のこと。
(*4)セルの表示形式の設定や
(小数点表示桁下げボタン）を使って小数点以下を表示しないようにすると、表示上は小数点以下が消えてスッキリするが、データとして小数点以下の値がなくなるわけではないので注意。たとえば、0.3, 0.3, 0.4が入っているセルの表示桁数を変更して、表示上は全部0にしたとしても、この3つのセルの合計を計算するとちゃんと1になる。INT関数で処理した場合とは全く異なる。
ヘルプの読み方練習として、メニューにある「ヘルプ(H)」から「Microsoft Excelのヘルプ（H）」を選び、検索文字列として「INT」を入れて検索するとINT関数のヘルプが読める。以下にヘルプを引用してみる。
INT
指定した数値を超えない最大の整数を返します。
書式
INT(数値）
数値 切り捨てて整数にする実数を指定します。
まず、最初に関数名が入っていて、次に概要が書いてある。「指定した数値を超えない最大の整数」ということは、正の数に対しては小数点以下を取り除いた数値が戻り値になるが、負の値についてはちょっと異なる。たとえば -0.3を超えない最大の整数は 0ではなく -1であるから、INT(-0.3)は0ではなく -1 となる。このへんは「切り捨て」という言葉のイメージにだまされてしまうとはまる可能性がある。
練習問題1. INT関数の引数として以下の数値を用いたとき、関数の戻り値はいくつになるか。まず自分で予想してから、実際に Excelを使って実験してみなさい。
練習問題2. 例題1で INT関数と同じように利用できる Excel 関数は他にもいくつかある。これらを使って以下の問に答えなさい。
(1) Excelのヘルプを「切り捨て」をキーワードにして検索し、INT関数以外の関数を 3つ以上見つけ、使い方についてヘルプまず自力で読んでみなさい。
(2) INT関数、ROUNDDOWN関数、FLOOR関数、TRUNC関数を使って、以下の数値例を整数に切り捨て、処理の違いを確認しなさい。Excelのヘルプには時々理解に苦しむことが書いてあるので、必要に応じて「答えが分かっている」データを使って挙動を確認する習慣を身につけるのが吉。
-4.1, -0.5, 0, 0.5, 4.1
Excelで関数を挿入するには？ → Excel 編 Sec. 31
例題2. 消費税の計算を2種類以上の商品でもできるように拡張する
例題1で使った計算表は、カップラーメン1種類での消費税計算だったが、実際にはレシート単位で消費税は計算する。健康を気遣って野菜ジュース（135円、2本）とリンゴ（90円、5個）を一緒に買った時はどうすればいいのだろうか。
Step 1. 野菜ジュースとリンゴのデータを入れる行を計算表に挿入する
行の挿入を使って、カップラーメンの行の下に2行空行を入れる
Step 2. データと小計の計算式を入力する
Step 3. セル D7に税抜き合計額を計算する関数を入力する
セル D7 では、セル範囲 D4:D6 の合計を計算したい。既にこのような計算はオートサム機能を用いて何度もやっているが、ここでは関数を使っていることを意識して手で入力してみる。ここで用いる関数は SUM関数。手順は以下の通り。
この操作が終わると、以下のようになる。
ここでの例は高々3つのセルに入っている値を加えるだけであったから、単純に足し算を行っても大した問題ではない。しかし、SUM関数のようにセル範囲を引数として与えることができる関数の強みは、セル範囲で表せるならば、何万個セルが中にあったとしても全く問題なく扱える点にある。
Tips: Excelで範囲を指定するには？
ヘルプの読み方練習として、今度は SUM関数のヘルプを見てみよう。
SUM
セル範囲に含まれる数値をすべて合計します。
書式
SUM(数値 1,数値 2,...)
数値 1 , 数値 2,... 集計を計算する数値を指定します。引数は 1 〜 30 個まで指定できます。
解説
引数として指定した数値、論理値、数値を表す文字列が計算の対象となります。詳細は、1 番目と 2 番目の使用例を参照してください。
引数が配列またはセル範囲の参照である場合、その中に含まれている数値だけが計算の対象となります。空白セル、論理値、文字列、エラー値は無視されます。詳細は、3 番目の使用例を参照してください。
エラー値または数値に変換できない文字列を引数に指定すると、エラーになります。
今度はINT関数と違ってちょっと混乱した記述になっている。概要説明には「セル範囲に含まれる数値をすべて合計します」と書いてあるのに、書式には「SUM(数値1, 数値2, …)」と引数としては数値を与えるように書いてあり、さらに「引数は 1〜30個まで指定できます」と書いてある。これを素直に読むと、SUM関数では個々のセルを引数として、30個のセルまでしか合計できないように見える。
しかし、早まってはいけない。解説まで読むと「引数が配列またはセル範囲の参照である場合、その中に含まれている数値だけが計算の対象となります」と書いてある。つまり、引数にはセル範囲も使うことができるのである。Excelのヘルプにはこういうわかりにくい表現が沢山あるので、最後まで一通り読むことが重要である。
練習問題3. SUM関数を使った合計の計算
下の表はたぬき市とキツネ市の老齢人口を表している。2つの市の各年での老齢人口の合計を計算しなさい。
| 市＼年 | 2002 | 2003 | 2004 | 2005 |
|---|---|---|---|---|
| たぬき市 | 2000 | 3000 | 4000 | 4500 |
| キツネ市 | 5000 | 6000 | 7000 | 8000 |
| 合計 |
ヒント： オートサム機能を使ってうまくいかない場合は、入力された計算式の中身をきちんと見てみると分かるかもしれない
練習問題4. Excelには平均を求める関数がある。この関数を使って、練習問題3のデータから、各年のたぬき市、キツネ市の老齢人口の平均を求めなさい。結果は合計行の下に平均行を作って記入すること
ヒント: 平均を求める関数は、ヘルプで「平均」をキーワードに検索すればすぐわかる。使い方は合計を求める以外はSUM関数と全く同じである
（*5） より正確に言えば、「○○は△△である」といった平叙文の中で「成立するかしないかが明確に決まるもの」を命題と考えている。たとえば、「クジラはほ乳類である」という記述は真であると判断可能であるので命題と言えるが、「聖徳太子は偉人である」という記述は「偉人である」という部分が「明確に決まる」訳ではないので一般的には命題と言えない。
Excelは表計算ソフトなので、論理式を一番使うシーンは数値の大小あるいは等しい、等しくないの比較である。このような比較に使う記号の一覧は、ヘルプを「比較演算子」で検索すれば出てくるが、主なものは以下の通りである。
| 比較内容 | Excelでの書き方 | 数学での記号 |
|---|---|---|
| AはBと等しい | = | ＝ |
| AはBと等しくない | <> | ≠ |
| AはB以上 | >= | ≧ |
| AはBより大きい | > | ＞ |
| AはB以下 | <= | ≦ |
| AはB未満 | < | ＜ |
=は、計算式の始まりを表す記号としても使うのでちょっと混乱しやすいが、計算式の最初の文字として入力されている場合は計算式を表す記号、計算式の途中に入力されている場合はその前後が等しいことを意味する比較演算子と解釈される。たとえば =A1=B1 のような計算式は「A1とB1が等しければ TRUE、等しくなければ FALSE」となる。
例題3. 数値の大小関係を比較する論理式
Step 1. まず、数値データを下図のように入力してみる
Step 2. 数値の比較を含む論理式を入力する
数値の比較を行うために、Excel では普通の計算式と同様に等号や不等号を使う。ただし、≦や≠のようにキーボードから入力できない記号（*6）については、2つの記号を組み合わせて代用する。ここでは、
という 6つの論理式を入力してみると、以下のようになるはず。
（*6）日本語IMEを使って入力すればこれらの数学記号は入力できてしまうが、Excelが英数字だけあれば事足りるアメリカ生まれのソフトだということを忘れてはいけない。いくら日本語化されているといっても、こういう基本的な部分については「アメリカ人が普通に使っているキーボード入力環境」が大前提となっている。当然のことながら、無理矢理 =A2≠B2 等と入力してもエラーが出るだけ。
練習問題5. 「A列に入っているデータはB列に入っているデータより大きい」という計算式を C列に入れなさい
※計算式を入れる前に、比較の結果が TRUEとなるか FALSEとなるか予想してから入力しなさい
※セル A4には、計算式を入れなさい
| A | B | C | |
|---|---|---|---|
| 1 | 5 | 10 | |
| 2 | 3 | 4 | |
| 3 | 0.1 | 0.1 | |
| 4 | =4.2-4.1 | 0.1 |
Tips: Excelでの数値表現と計算精度
Excelでは、文字列の大小関係も比較することができる。この場合の比較ルールは数値とは異なり、辞書と同じように文字列の先頭の文字から順番に比較していく。
例題4. 文字列の比較
Step1. まずサンプルデータを入力する
3行目と4行目の違いは、4行目のB列に入っている文字列が、最後に空白が1つついている"kame "であるだけ
Step2. 文字列の比較を行う論理式を入力する
Excelでは、文字列の比較に使う比較演算子は数値の比較に使うものと同じ。入力してみると以下のような結果になる。
「等しい」が成立するのは完全に文字列が等しいときだけ。空白も比較対象になるので、単語の前後に余計な空白がついていたりすると比較結果が変わる。
s123とs22の比較結果は、s123よりs22の方が大きい。これは注意が必要。s+数値みたいな気分で番号をつけた場合には、順序が思っていたものと逆になる。このようなケースを避けるためには、6行目の例のように0を入れて桁をそろえておくとよい。
Excelには論理式を組み合わせて使うために、AND, OR, NOTという関数が用意されている。これらの関数を用いて論理式を組み合わせると、複雑な論理を記述する論理式を作ることができる。
例題5. 複数の論理式を組み合わせて、複雑な論理式を作る
(1) セル A1 に入っているデータは、18以上 23未満である
18以上23未満という条件は、数学の記号を使えば 18≦A1＜23 と書けるが、Excelの計算式としてはこのような書き方はできない（*8）。そこで、単純な比較の組み合わせとして条件を整理し直すと、この条件は
18≦A1 かつ A1＜23
と書ける。接続詞「かつ」が入っているので、 AND関数を用いて
AND(18<=A1, A1<23)
と書くことで求める論理式を作ることができる。AND関数は、引数として与えた論理式が全て TRUEの時だけ TRUEを戻り値として戻すので、上の例だと、18<=A1とA1<23が同時に成り立った時だけ TRUEを戻すことになる。
(2) セル A1 に入っているデータは、18以上 23未満ではない
例題(1)の条件を否定しているので、素直に書けば NOT関数を使って
NOT(AND(18<=A1, A1<23))
と書ける。より直接的に考えれば、A1<18 または A1 >= 23 となるので、これを OR関数を使って書くと
OR(A1<18, A1>=23)
とも書ける(*9)
（*7） 普通の日本語では、「A または B」と書いてあったら、AとBのどちらか一方と考えるのが普通。たとえば、「ランチセットには食後にコーヒーまたは紅茶がつきます」と書いてあるメニューを見て「えーっと。食後はコーヒーと紅茶両方で」と言う人は相当な変人である。しかしながら、論理式として「かつ（OR）」を使う際は、AとBの両方が成立していても構わない。このへんは普通の日本語の感覚と論理式を作る上での数学の言葉との間にずれがあるので注意。
（*8） 正確に言えば、=18<=A1<23 をExcelの計算式として入力することは自体は可能で、特にエラーは出ない。ただし、通常の計算順序のルールに従って処理されるだけなので、期待した結果とは一致しないところが大きな問題。<= と <の演算順位は同じなので、まず前の演算を行ってから後の演算を行うことになる。たとえば、セルA1に 20が入っているケースでは((18<=20)<23)として Excelは計算する。前のカッコ内は TRUEとなるので、(TRUE<23)。TRUEという論理値と 23という数値を比較しても意味がある答えにはならない。ちなみに Excel 2003で =TRUE<23 を計算すると FALSEとなり、20は18以上23未満ではないことになった。エラーは出ないのに意味がない計算をするので非常にたちが悪い。
（*9）この2つの論理式が同じ意味であることは、「ド・モルガンの法則」と呼ばれる論理計算の規則から簡単に確かめられる。この法則は、論理式を書くときは知っている方がいいが、別に知らなくても構わないので詳述はしない。興味がある人は適宜検索すれば説明しているページが山ほど見つかるはず。
練習問題6. セル A1 の値をチェックする以下の条件をあらわす Excelの論理式を作りなさい
練習問題7. 練習問題6で作った論理式が正しいかどうかをチェックするために使う数値を考えなさい
練習問題8. セル A1の値をチェックする以下の条件を表す Excelの論理式を作りなさい
練習問題9. 練習問題8で作った論理式が正しいかどうかをチェックするために使う数値を考えなさい
IF関数はIF(論理式, TRUEの時の処理, FALSEの時の処理） という書式で使い、第1引数として与えた論理式が TRUEならば、第2引数として与えた計算式を計算した結果を IF関数の戻り値とし、FALSEならば第3引数として与えた計算式を計算した結果を戻り値とする。
例:
IF(TRUE, "真", "偽") → "真" 第1引数として TRUEを与えているので、第2引数の文字列を IF関数の値として戻す
IF(FALSE, 1+1, 2+3) → 5 第1引数として FALSEを与えているので、第3引数として与えた計算式の値を IF関数の値として戻す
もちろん、上に挙げた例は最初から TRUE あるいは FALSEが決まっているので、実質的には意味がない例となっている。
例題6. 試験の点数が60点以上だったら「合格」、60点未満だったら「落第」と表示する
Step1. データを入力する
Step2. 計算式を入力する
セル B2 → =IF(A2>=60,"合格","落第")
以下、セル B11 まで計算式をコピーして入力すると、以下のようになる。
練習問題10. 例題の点数例を用いて、以下の処理をIF関数で行いなさい
例題7 試験が60点以上なら「合格」、40点未満なら「不合格」、40点以上60点未満なら「追試」と表示する
例題6との違いは、合否だけではなく追試も表示するため、3通りの場合分けが必要になったこと。一つの IF関数では、第1引数として与えた論理式が TRUEか FALSEかの区別、つまり、2通りの場合分けしかできないので、3つ以上の場合分けを行うためには、IF関数を組み合わせる組み合わせる必要がある。具体的には、
IF(論理式1, IF(論理式2, 計算式1, 計算式2), 計算式3)
IF(論理式1,計算式1,IF(論理式2, 計算式2, 計算式3))
のように、IF関数の中にさらにIF関数を入れることによって扱える場合分けの数を増やすことができる。Excelでは
=IF(論理式1,IF(論理式2,IF(論理式3,IF(論理式4,IF(論理式5,IF(論理式6,IF(論理式7IF(論理式8,…
のように、一つのIF関数の中に他のIF関数を7レベルまで入れられる。しかし、入れられるからといってあまり複雑なIF関数を作ると正しいかどうかの検証が面倒になる。見通しがいい範囲で処理を分割する方が実用的。
この例題では場合分けが3つであるから、IF関数の中に、もう1つのIF関数を入れる程度で処理できそうである。2つのIF関数を重ねて使った場合の書き方は
のような構造になる。上の形式だと
IF(論理式1,IF(論理式2, 計算式1, 計算式2), 計算式3)
| 論理式1 | 論理式2 | 処理結果 |
|---|---|---|
| TRUE | TRUE | 計算式1 |
| TRUE | FALSE | 計算式2 |
| FALSE | TRUE/FALSE | 計算式3 |
となる。3行目の論理式2が TRUE/FALSEと書いてあるのは、論理式1が FALSEの時には内側の IF関数は実行されないため。論理式2は内側のIF関数の引数であるから、内側のIF関数が実行されない場合には TRUEでも FALSEでも結果には影響を与えない。
また、下の形式だと、
IF(論理式1,計算式1, IF(論理式2, 計算式2, 計算式3))
| 論理式1 | 論理式2 | 処理結果 |
|---|---|---|
| TRUE | TRUE/FALSE | 計算式1 |
| FALSE | TRUE | 計算式2 |
| FALSE | FALSE | 計算式3 |
となる。この場合は、論理式1が TRUEであれば、論理式2の内容にかかわらず計算式1が実行される。
さて、具体的な例に戻ろう。合格、追試、不合格のそれぞれを表す論理式は、点数がセル A1に入っている場合、それぞれ
となる。この3つの場合分けを1つのセルに詰め込むためには上で説明した二重のIF関数を使うことになるが、ここでは「先に合格者を確定したい」という気分で下の方の形式、つまり IF(論理式1, 計算式1, IF(論理式2, 計算式2, 計算式3)) の方を使うことにし、計算式1が「合格」、計算式2が「追試」、計算式3が「不合格」であるとしておこう。
「合格」と判定されるためには、計算式1が実行される必要がある。上の処理表を見ると、計算式1が実行されるのは論理式1が TRUEであるだけでOKだから、論理式1として適切なのは、合格を判定するための A1>=60 である。
「追試」と判定されるためには、計算式2が実行される必要がある。追試の条件は AND(A1<60,A1>=40) であったから、計算式2が実行されるときには、この条件が成立するように論理式2を決める必要がある。論理式2が実行される場合、論理式1は必ず FALSEであるから、論理式1を否定した論理式、NOT(A1>=60) = A1<60 は必ず TRUEである。この条件は「追試」の判定条件の前半であるから、論理式2では、「追試」の判定条件の後半がを入れてやればよい。そこで、論理式2としては A1>=40 が適切である。
最後に「不合格」と判定される条件をチェックしておく。計算式3が実行されるのは、論理式1も論理式2も FALSEのときであるが、論理式2が実行される段階で既に論理式1がFALSEであることは保証されているので、論理式2が FALSEのとき「不合格」でいいかどうかをチェックすればよい。論理式2は、A1>=40 であったから、これが FALSEとなるのは得点が 40点未満のケースであり、正しく「不合格」の条件と一致している。
以上の条件を整理すると、求める計算式は
=IF(A1>=60, "合格", IF(A1>=40, "追試", "不合格))
となる。
ここまでの話を図示すると下のようになる。これはフローチャートと呼ばれる図法に従って書いたものであり、菱形が TRUE/FALSEの条件判断(*10)を表している。複雑な論理をIF関数で処理する際には、いきなり書かずに、このような形でロジックを整理した方が条件漏れや勘違いが減ってよい。
(*10)一般的には、フローチャートでの条件判断は Yes/Noという表現を使うが、ここでは IF関数の論理式にあわせて TRUE/FALSEという表現にしてある
練習問題11
例題7を、=IF(論理式1,IF(論理式2,計算式1, 計算式2),計算式3) という形式の IF関数を使って解いてみなさい
練習問題12. 以下の場合分けをIF関数を用いて行いなさい
テストの点数データ（下表）を用いて、「評価」欄に成績を A, B, C, Dの4段階評価で表示しなさい。ただし、Aは80点以上、Bは 70以上80点未満、Cは60点以上70点未満、Dは60点未満とする。
| 点数 | 評価 |
| 82 | |
| 77 | |
| 68 | |
| 59 | |
| 74 |
練習問題13. tfr.xls は 1960年から 2004年までの都道府県別合計特殊出生率データ（人口動態調査）である
(*11) このような目的には COUNTIFという専用の関数がある。普通はそちらを使うが、今回は IFと SUMのおさらいということで。
練習問題14. score.xls は仮想的な試験の結果である
練習問題15. 買い物をするためにショッピングリボ払いを利用した
支払い条件は以下の通り
このショッピングリボ払いを使って、7月にセル B2 に入力してあるだけの買い物をした場合に、すべて支払い終わった時点での支払い総額を毎月の支払い額ごとに計算しなさい。
©2006, Hiroshi Santa OGAWA
このページにアダルトコンテンツ、XXXコンテンツ類は一切含まれていません。暴力反対.