人気ブログランキング | 話題のタグを見る
ブログトップ

カクレ理系のやぶにらみ

tamm.exblog.jp

時間のある方はお読みください。軽い気持ちで読み始めると頭が痛くなります。

複雑な表をシンプルに(3)  作業用セルで計算式をわかりやすくする

今回は、作業用セルを設けると計算式をシンプルにすることができるという事例をお目にかけます。
 下図のように、テストの点数を入力すると、その点数に応じて評価(この場合、「優」「良」「可」「不可」の4種類)を自動的に表示させることにします。


 複雑な表をシンプルに(3)  作業用セルで計算式をわかりやすくする_c0136904_2373115.jpg
 

 この場合、やり方は少なくとも2通りあって、まずIF関数を使うやり方からご説明します。

(1)IF関数を使うやり方
 ご存知のように、IF関数は条件式の結果に応じて処理の仕方を変更するというもので、どういう処理をするかについては、数値や文字列を表示させることもできますし、計算式を指定しておくことも可能です。これによりIF関数を入れ子にして組み合わせることもできるようになっています。
 IF関数を単独で使用する場合だと条件に応じた処理の方法が2通りしか設定できないのに比べ、IF関数を入れ子にすると処理の方法が3通り、4通りと増やすことができるという利点があります。次の図をご覧ください。


 複雑な表をシンプルに(3)  作業用セルで計算式をわかりやすくする_c0136904_2384055.jpg
 


 ここでは、セルE3に次の計算式が入力されています。ちょっと長いのですがご辛抱ください。

=IF(D3<40,"不可",IF(AND(D3>=40,D3<60),"可",IF(AND(D3>=60,D3<80),"良","優")))

 この計算式は3つのIF関数が入れ子になっており、その意味は次の通りとなります。

(最初のIF関数)
点数が40点未満ならば「不可」を表示せよ。そうでない場合次のIF関数を計算せよ。

(2番目のIF関数)
点数が40点以上で60点未満ならば「可」を表示せよ。そうでない場合次のIF関数を計算せよ。

(3番目のIF関数)
点数が60点以上で80点未満の場合「良」を表示せよ。そうでない場合は「優」を表示せよ。


 「40点以上で60点未満」という指定のしかたをする場合は、このようにAND関数を使います。このほかにOR関数というのもあって、OR関数を使うと「40点以上の場合と60点未満の場合の両方」ということになり、結果的にすべての点数が含まれてしまうという違いがあります。
 IF関数を使えるかどうかでExcelでできることがまるで違ってきますから、覚えておいて損はないと思います。

(2)計算式の修正
 IF関数を使うとこのような式の記述となります。これはこれで何の問題もないのですが、たとえば、「優」「良」「可」「不可」というのは時代遅れだからをやめて、「たいへんよくできました」「よくできました」「がんばろう」「ざんねん」に変更したいということになった場合、計算式を直さなければなりません。
 この場合、手作業で計算式を修正して、あとはコピー&ペーストするというのが普通ですが、「置換」機能(Excel2007以降では「ホーム」タブの右端の「検索と選択」、Excel2003以前では「編集」メニューの「置換」)を使って、計算式の中の文字列を一括修正することもできます。(この場合、「優」という文字列を「たいへんよくできました」という文字列に置換することになります。)


 複雑な表をシンプルに(3)  作業用セルで計算式をわかりやすくする_c0136904_239566.jpg



 この図はExcel2010のものですが、Excel2003以前でもあまり変わりはありません。ポイントは「検索対象」を数式とすること、計算式が列方向に入っているので「検索方向」を列とすることです。(これらの設定は「オプション」ボタンをクリックすると表示されます)
 なお、置換対象の文字列は””で囲います。最後に「すべて置換」ボタンをクリックすると計算式の内容が修正されます。


 複雑な表をシンプルに(3)  作業用セルで計算式をわかりやすくする_c0136904_2310599.jpg




(3)VLOOKUP関数を使うやりかた
 2番目のやり方はもっと単純です。ワークシートのどこかに「点数と評価の対比表」を作成しておき、VLOOKUP関数を用いるというものです。(下図参照)


 複雑な表をシンプルに(3)  作業用セルで計算式をわかりやすくする_c0136904_23121553.jpg
 


 ここでは、セルE3に次の計算式が入力されています。

 =VLOOKUP(D13,$K$13:$L$16,2,TRUE)

 検索の型でTRUEが指定されているのは、TRUEの場合は、検索する値が検索テーブル(図のセルK13からL17の範囲)の値と完全一致しなくてもよいからです。(FALSEを選択すると、このようなケースではエラーになってしまいます。)その代わり検索テーブルの左端の列(この場合はK13からK17まで)を昇順に並べておく必要があります。

 IF関数を使うやり方は、そこですべての処理を完結させるというものですが、ここでご紹介したやり方は、「点数と評価の対比表」(検索テーブル)を別途作成しておくという処理工程と、VLOOKUP関数を使って点数に応じた評価を表示させるという処理工程とに分けて取り組むというものです。
 「点数と評価の対比表」(検索テーブル)をあらかじめ作成しておくというのは、両氏でいうところの「下ごしらえ」のようなものだと理解していただくとよいと思います。下ごしらえをしておくと、本番の調理が手際よくできるようになります。Excelで複雑な表をつくるときも同じことがいえるのです。

 このやり方だと、計算式がシンプルになるだけでなく、評価の内容を修正したいというときは、「点数と評価の対比表」(検索テーブル)を書き直すだけよいという利点があります。どうせなら、なるべく楽な方法を用いた方が使い勝手はよくなります。
 このように、処理工程を分けるというのも、以前申し上げた「細かい単位に分けて考える」という原則に沿ったやり方であるといえるでしょう。
 
(4)作業用セルとは
  Excelという表計算ソフトのは、以下の工程を繰り返すところに特徴があります。

1.データを入力する
2.それを処理(計算)する
3.その結果を表示する

 2.の工程を実行させるために、ユーザーが計算式を決めるわけです。(1)のように、IF関数を使うと1つの工程となりますが、計算式が長く複雑になるという欠点があります。ところが、2.の工程を分割してそれを作業用セルに配置すると、個々の工程がシンプルになり、見た目がわかりやすくなります。あとはそれを組み合わせればいのです。下ごしらえした材料を組み合わせて料理を完成させるというイメージで捉えていただければよいかと思います。

(5)点数が未入力のときに評価を表示させないようにするには(エラー処理)
 今回ご紹介した計算式では、点数欄が未入力の場合、評価が「不可」と表示されてしまいます。点数の未入力と0点は意味が違うのですから、これでは具合が悪いことになります。これを回避するには計算式を次のようにします。

(IF関数を使う場合)セルE3の計算式
=IF(D3="","",IF(D3<40,"不可",IF(AND(D3>=40,D3<60),"可",IF(AND(D3>=60,D3<80),"良","優"))))

セルB3の計算式
=IF(D3="","",RANK(D3,$D$3:$D$8))

セルH8の計算式
=IF(ISERROR(VLOOKUP(G8,$B$3:$C$8,2,FALSE)),"",VLOOKUP(G8,$B$3:$C$8,2,FALSE))

 ISERROR関数は、計算式の結果がエラーかどうかを判定する関数です。IF関数と組み合わせて使うとエラーとなった場合、エラー値(#N/Aなど)を表示させるのではなく、空白とすることができるようになります。


(vlookup関数を使う場合)セルE13の計算式
=IF(D13="","",VLOOKUP(D13,$K$13:$L$16,2,TRUE))

セルB13の計算式
 省略

セルH18の計算式
 省略



 複雑な表をシンプルに(3)  作業用セルで計算式をわかりやすくする_c0136904_23144468.jpg

by T_am | 2011-06-15 23:18 | Excel のあの手この手

by T_am