ブログトップ

カクレ理系のやぶにらみ

tamm.exblog.jp

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

<   2011年 06月 ( 20 )   > この月の画像一覧

 古い友だちが、関西電力の株主総会で、株主から「北朝鮮が原発に対してテポドンを撃ってきたらどうするか」という質問があったことを伝えるニュースを教えてくれました。

(スポーツ報知)
http://hochi.yomiuri.co.jp/topics/news/20110629-OHT1T00335.htm

 他のマスコミはこのようなやりとりがあったことなどまったく伝えていないので、貴重なニュースだと思います。以下はその記事の一部の引用です。

 「北朝鮮が原発に対してテポドンを撃ってきたらどうしますか。その対策を教えて下さい」―男性株主の質問に、原発事業の担当役員である豊松秀己常務取締役(57)が答弁に立った。「テロ対策は、侵入があれば治安機関に通報する。大規模テロには対策本部を設置し、テポドンの場合は国民保護対策本部を作って国と対応する」その上で「仮に着弾があっても、堅固な立派な格納容器と思っている」と言い放った。(以下省略)

 最後の「言い放った」という表現に記者の感情がこもっていて、あまり感心しませんが、それにしてもお粗末な回答だと思います。この記事の後半で京大原子炉実験所の小出裕章助教のコメントとして、「仮に格納容器が壊れなくても、配管1本が壊れるだけで炉心溶融(メルトダウン)が起こりえる。格納容器が大丈夫だからというのは、もともと成り立たないバカげた返答」というのが載っているように、ミサイルが直撃すれば(原子炉本体はともかく)原子炉施設が無事であるはずはありません。この常務氏は、福島第一原発の事故をみて何とも思わなかったのでしょうね。あいかわらず原子炉本体が無事であれば事故は起こらないと思っているようです。こういう人が原発事業の担当役員をしている関西電力という組織はよほど病んでいるものといえますし、福井にある原発は本当に安全なのかね、と疑問を持たないわけにはいきません。

 実をいうと、この質問は意地が悪い質問であると思っています。というのは、会社側が少しでも危険性を認めれば、「そういう危険なものを放置していてよいのか」という二の矢が用意されていたことは明らかだからです。この常務氏はそれを避けるために支離滅裂な答弁をしたのかもしれませんが、その代わり私のようなものにまでからかわれることになったのですから、お気の毒と申し上げなければならないのかもしれません。

 ミサイルの直撃を受けて無事である施設というのは日本には存在しません(たぶん)。また、そういう場合の対策を講じている民間組織も(たぶん)日本には存在しないといってよいと思います。それが悪いというのであれば、たしかにその通りですが、だからといってミサイルの標的となるような施設(この場合は原発)を存在させてはならないという結論には結びつきません。
 ミサイルが命中すれば大事故になる施設は、原発以外にもいくらでもあります。それらを運営管理する民間企業に対し、北朝鮮のミサイルが飛んできたときの対策を講じなければならないというのは無茶です。原発や工場、石油タンクなどの重要施設をすべて地下に建設すれば防ぐことはできるでしょうが、現実には不可能です。実現不可能なことをやっていないという理由で責めるのはフェアではありませんし、そもそも問題の次元が違います。防衛責任を負うのは民間企業ではなくて国なのですから。

 この常務氏の他人事のようなテロ対策を聞くと、日本というのは平和な国なんだなと思ってしまいます。千年に一度の地震など起こるはずがないと思っていたものが実際に起きたからこそ、福島第一原発の事故につながったことを忘れてはなりません。東電や政府は想定外の出来事だったといっていましたが、この言い訳に二度目はないことを自覚してもらいたいものです。万一事故が起これば、その影響は凄まじいものとなる施設を運営しているのですから、可能性がゼロではない出来事に対しては、それが起きた場合どのように対処するかという計画を普段から整えておくことが必要だというのがフクシマの教訓だと思います。

「テロ対策は、侵入があれば治安機関に通報する。」
「大規模テロには対策本部を設置」
「テポドンの場合は国民保護対策本部を作って国と対応する」

 これらの空虚な言葉の羅列を読むと情けなくなります。あなたが貰っている役員報酬の大きさはあなたが果たさなければならない責任の重さに比例しているのですよ。それを考えたことはないのですか?
 工作員が密かに上陸して何人もの日本人を拉致していった事実が明らかになっているのですから、テロリストが原発を襲うという可能性がゼロであるとは誰にもいえません。
 テロリストが襲ってくるような状況を招かないというのは国の責任ですが、実際に襲撃されたときの被害を最小限にくい止めるためにはどうしたらいいか? という設問に回答を用意するのはマグニチュード9.0という地震が襲ってきた場合の対策を考えておくというのとレベルは同じです。ミサイルが直撃すれば被害を防ぐことはできませんが、テロリストに襲われても対応如何によっては被害を最小限にくい止めることは可能だからです。
 関西電力にはテロリスト対策がマニュアルとしてちゃんと用意されているけれども、公にすることができないのでこのような答弁をしたというのならば、お詫びしなければなりませんが、実際のところはどうなのでしょうか? 何も考えていないというのではないかという気がしてならないのですが・・・・・・


付記
 私の友だちは、この常務氏のことを「社蓄」と呼んでいました。己の主人に対しては家畜のように従順でありながら、他人に対しては鬼畜のような行為を平気ですることができる人間という意味です。昔、この言葉を発明した漫画家がいましたが、定着しなかったようです。久しく社蓄という言葉を聞くことはありませんでしたが、改めて聞くと、まさにぴったりという思いがします。
by T_am | 2011-06-30 23:39 | その他
 福島第一原発の事故で立入り禁止となっている警戒区域への一時帰宅の際に、熱中症対策のため防護服を着なくてもいいということになったそうです。初日となった6月25日は雨がぱらついていたせいか、防護服を着ないことにした人は参加者740人中十人程度だったとのことです。
 放射線の防護服といってもいろいろな種類があるようで、ニュースなどで何度も放映されたあの白い防護服は、その実態は雨合羽といった方がわかりやすいと思います。何も特別なものではないので、透過力の弱いα線(プルトニウムなどからから放出されます)はともかく、β線やγ線といった放射線を遮ることはできません。
 それではなぜ原発でこれを防護服として着用させているかというと、作業服などのまま原子炉建屋の中に入ると服の繊維についた放射性物質を事務棟などの普段人がいるとこに持ち込んでしまうからでしょう。つまり、原子炉建屋の中に入るときに防護服(雨合羽)を着て、帰って来るときに表面を洗い流せば、放射性物質を持ち込む恐れはなくなります。
 また、手袋やマスクをするのは、放射性物質が体内に侵入するのを防止するためでしょう。

 警戒区域内の空間放射線量をみると、もっとも低い地点で0.5マイクロシーベルト/時(6月23日現在、文部科学省のサイトから)であり、最も高い地点では79.3マイクロシーベルト/時となっています。かなりばらつきはありますが、それでも人が住むには高い値であるといわざるを得ません。
 一時帰宅の滞在時間は上限が決まっているので、警戒区域内に入った場合に気をつけなければならないのは外部被曝よりも、むしろ放射性物質を飲み込んだり吸い込んだりして発生する内部被曝の方でしょう。そうなると、絶対に欠かしてはならない道具はマスクと手袋であるということになります。雨合羽(防護服)は、洋服に付着した放射性物質を避難先に持ち帰ることを防ぐという意味で、本当は用意した方がよいように思います。というのは、避難先には家族がいるからです。

 福島第一原発の事故から百日が経過しました。半減期が短いヨウ素131は事故発生時の千分の1以下に減っていますから、現在計測されている放射線は半減期が長いセシウムやストロンチウムなどの原子炉生成物によるものと思われます。ストロンチウムはカルシウムに性質が似ているので、間違って飲み込んでしまった場合カルシウムの代わりに骨に吸着する心配があります。また、セシウムはカリウムと似ているので、口から体内に入るとなかなか排泄されず百日以上体内にとどまることが知られています。
 内部被曝が怖いのは、放射性物質が体内にとどまっている限り、その周辺の細胞を攻撃し続けるからです。外部被曝であれば、放射線量の低いところへ逃げればいいのですが、内部被曝はそういうわけにはいきません。
 したがって、警戒区域内に一時帰宅する場合は内部被曝をいかに防ぐかということに気を配るべきでしょう。
 一時帰宅の概要をみると、参加者は中継地点で必要な装備(防護服、手袋、マスクなど)を受け取った後でバスに乗り込んで居住地に向かい、用が済んだら中継地点に戻ってくることになっています。その間食事やトイレは禁止されており(水を飲めるのは行きのバスの車内だけ。帰りの車内では禁止)、防護服を脱ぐことができるのは中継地点でスクリーニングを受けてからということになっています。
 防護服を着ない人については、スクリーニングの結果、放射線が検出された場合には、それまで着ていた服を着替えてもらうことも必要です。できればそこでシャワーを浴びてもらうのが一番だと思いますが、そこまで用意されているかどうかはわかりません。

 ニュースによれば、参加者の中には「きょうは涼しいし、放射線の事を考えると防護服を着ていた方が安心です」と話していた人もいたとのことです。こういう声を聴くと、防護服=放射線から防御するための特別な服、と思っている人が相変わらず多いことがわかります。私も、今回の事故が起こるまでは、原発で使用されている防護服は放射線から身を守るためのものだと思っていたくらいです。
 このことからも、原子力にかんする情報知識というのは、大事なことが何も知らされていないということがわかるのです。
by T_am | 2011-06-26 23:24 | その他
 5月31日、地下式原子力発電所政策推進議員連盟(会長 平沼赳夫)が発足しました。太陽光や風力などの自然エネルギーによる発電方式が主力になるとは思えず、主要な電力は原子力で賄う必要があるとのことで、地下に原発をつくることは安全性の面から意義があるのだそうです。

http://www.jiji.com/jc/zc?k=201105/2011053100809

http://sankei.jp.msn.com/politics/news/110531/stt11053117580006-n1.htm

 地下式原発は以前から検討されていたようですが、今日まで実現しなかったのは建設費が高くなるからでしょう。地下に大きな空洞をつくるのですから当然ですよね。ところが、福島第一原発の事故により、にわかに注目されるようになったわけです。
 そこで、地下式原発とはどのようなものか、すこし調べてみました。

http://www.rist.or.jp/atomica/data/dat_detail.php?Title_Key=02-02-01-06


(地下式原発の概念図)

c0136904_23174587.jpg


 概念図を見る限り、完全地下式なら平沼会長がいうように、放射性物質が外部に飛散することはなさそうですが、本当にそうなのでしょうか?
 福島第一原発では水素爆発によって1号機2号機4号機の原子炉建屋の上半分が吹き飛んでしまいました。そのため大量の放射性物質が空気中に放出されることになったわけですから、地下深くに原発の施設をつくれば万一の事故が起こっても放射性物質が飛散することだけは防ぐことができるように思われます。

 でも、それで本当にいいのでしょうか?

 以前にもこのブログで申し上げましたが、見方を変えれば、原子炉建屋が吹き飛んだおかげで爆発の衝撃波が上に逃げたために、原子炉(格納容器と圧力容器)が破壊されるのだけは免れました。もしも原子炉建屋がもっと頑丈につくられていたら、爆発の衝撃波は原子炉そのものを破壊していたかもしれません。そういうことが起こらないように、原子炉建屋の強度は上に行くほど脆く設計されているのです。仮に、原子炉が完全に破壊されてしまえば、周囲には誰も立ち入ることができなくなりますから、その先何が起ころうとも手をこまねいてみている以外にありません。そちらの方がよほど被害が大きくなる可能性が高いといえます。
 地下式原発というのは、原子炉建屋を今よりももっと強固にするのと意味は同じです。すなわち、万一水素爆発や素蒸気爆発が起こったときにはその衝撃波を逃がすことができないということですから、原子炉は完全に破壊されてしまうことになります。
 原発が地下深いところにあって、深刻な事故が起こっても絶対に放射性物質が外部に漏れ出ることはないと断言できればいいのでしょうが、議員連盟の平沼赳夫会長でさえも「安全性の面から意義がある」といういい方しかしていません。絶対安全ですとは断言していないことに注意すべきです。(そもそも、「地下式原子力発電所政策推進議員連盟」という名称自体がなんだか悪の秘密結社みたいで、胡散臭いと私には思われるのですが・・・)

 地下式原発といっても、地下深くに完全に密閉されているわけではありません。少なくとも、冷却水を循環させる経路と排気塔(換気塔)、さらには燃料や物資の搬入経路も設けなければなりません。原発内部で事故が起こったときは、これらの経路を伝わって必ず外部に放射性物質が漏れ出すことになるはずであり、特に排気系統は重要となります。

 今回の事故で、東電がなぜベントにこだわったのかといえば、内部に溜まった水素や水蒸気を逃がさなければ最悪の場合爆発が起こるからです。地下に原発があれば多少の爆発が起きても安全だと思うかもしれませんが(平沼会長はそんなことは一言もいっていません)、考えてみてください、火山の爆発だって水蒸気爆発の一種なのですよ。
 要は、爆発のエネルギーと、それを抑え込もうとする土の圧力とどちらが強いかというだけのことです。爆発のエネルギー=発生する水蒸気の量は原発の内部にどれだけの水が存在するかによって決まりますから、地下式原発の出力が大きくなればなるほど、万一の場合の爆発エネルギーは大きくなります。それを抑え込むには、地下深く穴を掘って原発の蓋となる土を分厚いものにしなければなりません。

 けれども、地下式原発の目的が原発に蓋をすることであるならば、何も地下深く穴を掘る必要はないのであって、地上に原発をつくってそれをコンクリートの窯で覆ってしまっても同じ効果があるはずです。もしくは、原子炉建屋の強度を今よりもはるかに強固なものにしてもいいわけです。地下に巨大な空洞を掘るよりも、そちらの方がはるかに低コストで建設できると思うのですが、そういう発想でつくられた原発はどこにもありません。

 なぜなのでしょうか?

 私のような素人でも考えつく理由は、「そのような発想でシステムを設計することは本末転倒であるから」というものです。普通に考えれば、もっとも壊れたら困るところの強度を最高にしておくということになります。しかし、地下式原発という考え方は、最も壊れては困るところの補強には手をつけずに外殻の強度を高めることに夢中になっているというものです。走行中にエンジンが爆発する事故が発生した自動車メーカーがあったとして、そのメーカーの対策が「従来よりもボディを強化しました。だから外部に迷惑をかけることはありません。」というものだったら、あなた、そのメーカーの自動車を買おうという気になりますか?
 「地下式原子力発電所政策推進議員連盟」という悪の秘密結社を連想させる名称の組織に属す政治家たちが主張しているのはそれと同じことのように私には思えます。その主張は問題のすり替えであって、原発が絶対に必要だというのなら、絶対に事故を起こさない原発をつくるというのが筋でしょう。
 過去に軽微な原発の事故はしょっちゅう起こってきました。また、東電の隠蔽体質というのも、今回初めて指摘されたわけではありません。「議員連盟」に名を連ねている政治家の中には、過去にそういう事態が起こったときに権力の中枢にいた人が多いのです。責任を負う者として、当時適切な手を打とうとしなかった人たちが、今になって地下式原発などという論点のずれた主張をするのは、何かやむにやまれぬ事情があるのかもしれませんね。

 参考までに、地下式原子力発電所政策推進議員連盟の主要メンバーを掲載しておきます。(Wikipediaからの引用です。)

会長
平沼赳夫(たちあがれ日本)

顧問
谷垣禎一(自民党)
安倍晋三(自民党)
山本有二(自民党)
森喜朗(自民党)
鳩山由紀夫(民主党)
渡部恒三(民主党)
羽田孜(民主党)
石井一(民主党)
亀井静香(国民新党)

事務局長
山本拓(自民党)
by T_am | 2011-06-25 23:18 | その他
 今回もクロス集計になりますが、前回のデータベース関数とデータテーブルを組み合わせたやり方に比べると原始的な手法になります。原始的なだけに自由度は高く、集計する項目を3つ以上とすることも可能です。(ただし、あまり複雑な集計をすると人間の理解を超えてしまうので、お勧めはしませんが・・・)

1.基本的な考え方(2次元の集計条件を1次元にする)
 データベースのフィールドに格納される値は原則として常に1つとなります。たとえば、「性別」というフィールドに格納される値は「男」か「女」のどちらかであり、両方が同時に格納されることはありません。逆に言えば、このような排他的な性質を持つフィールドであればこそ集計の条件とすることができると考えてよいと思います。
 排他的な2つのフィールドの組み合わせによって得られた条件も排他的となります。歓送迎会出席者名簿という単純なデータベースを例にとると、「男」で「○」という人が同時に「男」で「×」となることはありません。
 組み合わせた条件も排他的になるという性質を利用して、条件の組み合わせのひとつひとつに目印をつけてしまえば、あとはそれを数えれば個数や合計を計算することができるようになります。
 たとえば、このようなものになります。


c0136904_21596100.jpg




 ここでは、2つの条件を2桁の数値に置き換えていることに注目してください。十の位は1か2しかありません。すなわち「男」=10 であり、「女」=20 となっています。同様に一の位も規則が設けられています。
 このような規則を決めると、歓送迎会出席名簿というデータベースの各レコードには、この表の値のどれか1つが適用されることになります。こうすれば、本来2次元の平面でしか記述できない集計条件でも1次元化されるので、SUMIF関数やCOUNTIF関数を使って集計することができるようになります。
 なお、「目印」を数値化する理由は、こうしておくと手作業で入力しなくても済むからです。後述するように計算式を設定すればExcelが自動計算してくれるので、入力ミスもなくなりますし、何よりもデータの件数が増えたときに手作業では処理量に限界があるからです。


2.1次元化された条件でのクロス集計
 まず、表の完成形を掲載しておきます。図の緑色に着色された部分がこのためにつくられたものです。(実際にはF列からM列までは作業用領域ですから非表示となります。)


c0136904_2201514.jpg





(1)セルF4:H31 の範囲について
  ここには3種類の計算式が入っています。

 (セルF5の計算式)
=VLOOKUP(C5,$L$11:$M$12,2,FALSE)

 C列の値が「男」か「女」かによって、どの数値が割り当てられるのかをVLOOKUP関数を使うことで実現しています。

(セルG5の計算式)
=VLOOKUP(D5,$L$15:$M$19,2,FALSE)

 考え方は前と同様です。

(セルH5の計算式)
=SUM(F5:G5)

 前の2つのセルの値を合計すると、二つの条件の組み合わせの結果が2桁の数字に置き換わることになります。
 あとは、この計算式を下の方に向かってコピーしていけば、すべての人に対し、このような「目印」をつけることができます。

2.L11:M12 の範囲について
 ここでは、性別が男であるか女であるかによって、それぞれどの数値を割り当てるのかを定義しています。ポイントは一の位が0 となっていることです。これを無視すると集計ができなくなってしまいます。

3.L15:M19 の範囲について
 ここでは、「出欠」フィールドの値に対して、0~4の数値を割り当てることが定義されています。

4.セルJ23:M30 の範囲について
 ここではいくつかの計算式が設けられています。手で入力するのは極力1カ所にまとめてしまうという原則に従って、すべて計算式でできあがっています。なお、以下に掲載する基本的な計算式を設定すれば、あとはコピーすることでこの領域は完成します。

(セルL23の計算式)
=L11

(セルL24の計算式)
=VLOOKUP(L23,$L$11:$M$12,2,FALSE)

(セルJ25の計算式)
=L15

(セルK25の計算式)
=VLOOKUP(J25,$L$15:$M$19,2,FALSE)

(セルL25の計算式)
=COUNTIF($H$5:$H$30,L$24+$K25)

 COUNTIF関数の書式は、COUNTIF(範囲、集計条件)となっています。集計条件には値(20などのように)を直接入力する(20などのように)ことが一般的ですが、それだとひとつひとつの計算式をいちいち入力しなければなりません。関数の引数には、このように数式を当てはめることもできるので、これを応用すると、数式の答えに該当するものだけを数えてくれるので入力の煩わしさから解放されます。ここでは、この式を横方向と縦方向にコピー&ペーストしています。

(セルM30の計算式)
=SUM(L25:M29)

 集計した個数が全体の件数と一致しているかをチェックするために設けたものです。

 クロス集計表としてはこれで完成なのですが、余計な要素(10、20、0~4といった数値)が表示されています。他人に見せるときは、余計な部分はカットして、できるだけシンプルな形で見せた方が理解してもらいやすくなるので、クロス集計の結果だけを表示させる領域を別に設けることにします。

5.O23:R29の範囲について
(セルP23の計算式)
=L23
 
 先ほど作成したクロス集計表を参照しているだけです。
 なお、「計」の欄にはSUM関数が入っています。


6.まとめ
 今回行ったように、2つの項目の組み合わせによる条件設定は2桁の数値に置き換えることができます。同様に、3つの項目の組み合わせによる条件設定は3桁の数値に置き換えることができます。
 こうすることで、SUMIF関数やCOUNTIF関数によって集計することができるようになり、データベースの値を修正しても、その結果はただちにクロス集計に反映されます。
 またクロス集計表のレイアウトの自由度も高い(たとえば列を新たに挿入して、構成比の欄を設けることも可能)というアドバンテージもあります。データテーブルではこのような真似はできません。
 どちらをつかってもいいのですが、以前お話ししたように、複雑な処理でも今回のように細かく分けて考えて処理をしていけば実現可能になる、というひとつの事例であるとご理解いただければよいと思います。

 
by T_am | 2011-06-22 22:01 | Excel のあの手この手
 前回使った歓送迎会出席者名簿(下図)のような体裁のものをデータベースと呼びます。先頭行に項目名があって、2行目から順にデータが入力されているのが特徴です。


c0136904_1139711.jpg



 データベースというのは単なるデータの集合体にすぎませんから、実際には膨大なデータの中から、必要なものだけを取りだして集計表を作成するという使い方をしています。集計表を作成する際に、1つの項目(性別あるいは出欠だけ)に基づいて集計するという場合もあれば、2つの項目(性別と出欠の両方)に基づいて集計表を作成する場合もあります。後者のように、2つの項目に基づいて集計することを「クロス集計」と呼んでいます。(下図参照)どちらが役に立つかは一目瞭然でしょう。


c0136904_1141278.jpg





 Excelにはクロス集計を行うツールとしてピボットテーブルという機能が設けられています。マウス操作だけでクロス集計ができる便利な機能ですが、元データが更新されても、そのままではピボットテーブルの内容に反映されないという弱点があります。(惜しいですね。)
 そこで、今回と次回はピボットテーブルを使わずにクロス集計する方法をそれぞれ書いておくことにします。今回はデータベース関数とデータテーブルを組み合わせてクロス集計を行うという手法です。
 まず、完成状態の図を掲載しておきます。


c0136904_11423133.jpg





 クロス集計のために作成したのは、図の薄く緑色で着色した部分です。意外とシンプルであることがおわかりいただけるのではないでしょうか。
 最初の行には、データベースの「性別」フィールドに入力されている値である「男」と「女」が入力してあります。また、最初の列には、データベースの「出欠」フィールドに入力されている値である「○」「退職」「転出」「「着任」「×」の5種類が入力されています。
 一方、セルO21には次の計算式が入っています。

=DCOUNT(B4:E30,"会費",O29:P30)

 DCOUNT関数は、データベース関数の一種で、データベースの中の条件に合致したデータの個数を数えるときに使います。その書式は次の通りとなります。

データベースの範囲        B4:E30
集計するフィールド名       ”会費”
集計条件が設定されている範囲  O29:P30

 この場合、「データベースの範囲B4:E30の中で、会費というフィールドについて、O29:P30に設定されている条件に合致するデータの個数を数える」という意味になります。このとき条件設定範囲であるO29:P30の1行目はデータベース範囲のフィールド名(「性別」「と出欠」)に一致していなければなりません。また、フィールド名の指定は文字列を” ”(半角)で囲む必要があります。(他にデータベース範囲の左側から何番目の列、という番号で指定することもできます。)

 しかし、実際にはセルO30:P30は空白であり、何の条件も入力されていません。また、計算式もセルO21に入力されているほかは、「計」の列と行にSUM関数が使われているだけです。これで本当にクロス集計ができるのか疑問に思われるかもしれませんが、Excelには「データテーブル」という機能があって、今回はそれとデータベース関数を組み合わせて使います。
 データテーブルというのは、「数式 で 1 つまたは 2 つの変数を変更したときに数式の結果にどのように影響するかを示すセル範囲です。」(オンラインヘルプより) これを利用すると1回の操作で複数の結果を表示させることができます。ちなみに、ここで変数として扱うのが、集計条件が設定されている範囲として指定したセルO29:P30になります。つまり集計条件として変数となるのが「性別」と「出欠」であり、それぞれ2通り×5通りのデータがあるのですから、合計10通りの集計結果を一度に表示してくるのがデータテーブルという機能になります。なんだか便利そうでしょ。

 「計」の行と列にはそれぞれ横計と縦計と計算するためのSUM関数を入力しておきます。

(データテーブルの使い方)
(1)まず最初にデータテーブルとして使用する範囲(O21:Q26)をドラッグします。このとき、範囲の先頭セル(O21)にはデータベース関数が入力されていなければなりません。また、データテーブルの最初の行と最初の列には変数として用いる名称(「男」「女」「○」「×」など)が入力されている必要があります。

(2)Excel2007以降では、「データ」タブの「What-If分析」から「データテーブル」を選択します。Excel2003以前では、「データ」メニューから「テーブル」を選択します。


c0136904_11441999.jpg




(3)すると「データテーブル」というダイヤログボックスが表示されるので、下図のように、「行の代入セル」の欄のところにカーソルが点滅している状態で、セルO30をクリックします。欄内には $O$30 と表示されます。「行の代入セル」というのは、「データテーブルの中で変数として使用するために行方向(横方向)に設定されている値を代入するセル」という意味です。ここでは、行方向で使用する変数の値として最初の行に入力されている「男」「女」が用いられるので、DCOUNT関数で集計条件が設定されている範囲として指定した O29:P30 のうち「性別」の下のセルであるO30を指定することになります。
 同様に、列の代入セルにはP30をクリックして指定します。


c0136904_11453676.jpg




(4)「OK」をクリックします。

 

(おさらい)
 データベース関数は、データベースの中で条件に合致したデータについて集計するためのものです。ここではDCOUNT関数を使っていますが、他にも合計値を求めるDSUM関数などがあります。条件を設定するためには、フィールド名を2つ並べ、その下に条件とする値を入力します。(セルO29:P30)
 例にO30に”男”、P30に”○”と入力すると、セルO21には15という数値が表示されます。
 しかし、ここではデータテーブルで用いる変数を代入するための領域として使用するので、O30とP30は空白のままとしておきます。
 
 データテーブルで間違いやすいのは「行の代入セル」と「列の代入セル」ではないでしょうか。「行の代入セル」とは、データテーブルの中で「行方向に設定されている値を変数の代入値としてどこに代入したらいいか」を指定するためのものです。したがって、行方向に設定されている値が含まれているデータベースのフィールド名(ここでは「性別」)に基づいて指定することになります。

 イメージとして、データテーブルの中の変数の値を順番にDCOUNT関数に代入した結果をテーブルに表示させていると理解するとわかりやすいかもしれません。

 そのほかのポイントとして、
・データテーブルの先頭セルには数式が入力されていること。
・データテーブルの最初の行と最初の列には、変数の代入値として用いられる値が入力されていること。

 以上に気をつければ、それほど難しいものではありません。


 データベース関数とデータテーブルを組み合わせたクロス集計では、データベースの値を修正すると、その結果がただちにデータテーブルに反映されるというのが特徴です。ピボットデーブルではこれができません。
 また、クロス集計表のレイアウトや体裁などの集計表作成の自由度もこちらの方が高いと思います。
 最初は取っつきにくいかもしれませんが、一度慣れてしまうと便利ですから、活用されることをお勧めします。

 次回は、データベース関数もデータテーブルも使わずにクロス集計する方法について書くことにします。今回の手法に比べ原始的なだけに集計表を作成する手間は比較的かかりますが、今回同様、データベースの変更が反映されるうえに、集計する項目が3つ以上の場合でも使うことができるやり方です。(データテーブルは2つの項目まで集計可能)
 どちらが使いやすいかは、その人次第というところでしょうか。
 
by T_am | 2011-06-22 11:47 | Excel のあの手この手
 福島第一原発1号機の汚染水処理システムが相次ぐトラブルに見舞われています。本格稼働すれば汚染水の処理能力は1日に2千トンといわれていたのですが、その本格稼働が危ぶまれているようです。

 私が不思議に思ったのは、汚染水処理システムにはバックアップが備わっていないのだろうか、ということです。

 福島第一原発の原子炉が現在どのような状態になっているかというと、高温によって溶け出した核燃料が圧力容器の底に溜まっているものと考えられます(メルトダウン)。核燃料が水に直にさらされると、その一部は流れ出していき、水を汚染することになります。 ところが、冷却システムが動かないのですから、核燃料の崩壊熱を奪うために注水を続けなければなりません。そうすると、放射性物質に汚染された水が次々につくられることになります。
 汚染水処理システムを設置したのは、放射物質に汚染された水をこれ以上増やさないということが目的のはずですが、圧力容器の中に露出した核燃料が存在する限り、汚染水処理システムを動かし続けなければならないことになります。したがって、汚染水処理システムがそう簡単に止まってもらっては困るのです。

 NHKテレビの解説では、この汚染水処理システムというのは大きく分けると4つの装置から成り立っているとのことです。それぞれの装置とは、油分離装置・セシウム吸着装置・除染装置・淡水化装置であり、放射性物質に汚染された水はこれらの装置を順番に通ることによって放射性物質の濃度が低減される仕組みとなっているようです。
 疑問に思うのは、これらの装置が直列に接続されている以上、どれか1カ所でトラブルが発生すればシステム全体が止まることは避けられないというにもかかわらず、なぜバックアップを設けなかったのか? ということです。それぞれの装置を2台並列に並べ、1台を予備としておけば、もう1台がトラブルに見舞われたときに予備機に汚染水を流す(バックアップ)ことによって、システム全体が止まることは避けられるはずです。
 これらの装置を映像でみると非常に複雑な形をしており、それだけ脆弱なのではないかと心配になります。なにしろ処理しなければならない水の量が桁違いに大きいのですから、今後もトラブルやメンテナンスに追われるであろうということは、私のような素人にも容易に想像がつきます。そのたびに、システムを止めるというのであれば、汚染水の処理は遅々として進まないことが予想されます。

 東電と許可権者である政府、原子力・保安院がシステムのバックアップの必要性に気づかなかったのであれば、それは関係者が全員無能であるか、どうせ一時的なシステムなのだとタカをくくっていたかのどちらかであろうと思います。ゆえに、まことにお気の毒とは思いますが、いっそ辞表を提出されてはいかがかと愚考する次第です。
 強力な権限は責任を果たすために与えられています。にもかかわらず、責任を果たすことができないのであれば、できる人に替わるというのが当たり前でしょう。バッターを打ち取れないピッチャー、守れない野手、打たない打者がいれば、監督は選手交代を命じますし、私たちもそれが当たり前だと思っています。プロ野球の試合ではしょっちゅう行われていることが、なぜ政治やビジネスの社会ではできないのでしょうか?


付記
 政府、東電の担当者・責任者はやるべきことはちゃんとやっているというのであれば、本稿に書いたことを取り消さなければなりません。その代わり、原子力というのは人類の手に負えない代物であるという認識を再確認することになるでしょう。
 海江田大臣が全国の原子炉の安全性が確認できたので、停止中の原子炉を再稼働させたいといっていますが、原子力が人間の手に負えない存在である以上、それがどこまで信用できるのか疑問に思います。
by T_am | 2011-06-20 23:18 | その他
 電卓や算盤で計算していた時代に比べると、Excelでシミュレーションを作成しておくと、条件を変えると一瞬で結果も変わるので時代の進歩というのを感じます。今回は、シミュレーションにおいて、「この結果を導くためには条件をどのように設定しなければならないか?」をExcelに計算させるための方法について書くことにします。

(例題)
 男性会費5,000円(女性会費4,000円)で、退職者の記念品は1人5,000円相当額の品物を渡したい。最終的に次回繰越金として1万円残るようにするには、歓送迎会のコース予算を1人あたりいくらにしたらよいか?


c0136904_5362266.jpg




 この解答を求めるために、条件を手入力で変更して結果をいちいちチェックしていくのも大変です。Excelには「ゴールシーク」という機能があって、結果を表示するセルの値を設定したときに、条件をどのようにしたよいかを計算してくれます。

(Excel2007・2010の場合)
 「データ」タブの「What-If分析」を展開して、「ゴールシーク」をクリックします。

(Excel2003以前の場合)
 「ツール」メニューから「ゴールシーク」を選択します。

(図は Excel2010 のものです。Excel2003以前でも大差ありません。)
c0136904_5371574.jpg



 「数式入力セル」というのは、計算の結果が表示されているセルのことなので、ここでは「次回繰越」のセルP30をクリックします。
 「目標値」は、セルP30の結果をいくらにするか? というものです。次回繰越金を1万円にしたいので、ここでは「10,000」と入力します。
 「変化させるセル」とは、結果を導くための条件を入力しているセルのことを指します。コース単価をいくらにすればよいかを知りたいのですから、ここではセルP9をクリックします。
 
 これで「OK」をクリックすると、次の図のように、計算した結果が表示されます。

c0136904_5383182.jpg



 セルP9の値が変わっていることにご注目ください。これだと3,998円のコース単価であれば、最終的に1万円の黒字となることがわかります。これ以上の金額でコース単価を決めるとその分黒字が減っていきますし、これ以下の金額設定をすればその分黒字が増えることになります。
 あまり知られていないゴールシークですが、覚えておくとけっこう便利な機能です。


 そういえば、今回の表で設定した条件付書式について書いておくのを忘れていました。シミュレーションのような複雑な表をつくるうえでのちょっとしたコツと一緒に、次回はそのことについて書くことにしたいと思います。
by T_am | 2011-06-19 05:40 | Excel のあの手この手
Excelというアプリケーションの基本的な動作は、次の工程の連続となります。

・数値を入力する
・計算する
・結果を表示する

 人間が電卓で計算するとかなりの時間がかかる処理でも、Excelを使うと一瞬で終わってしまいます。そこで、Excelの真骨頂ともいえる使い方がシミュレーションでの利用です。シミュレーションは、前提となる数値をいろいろと変えることによって、結果がどう変化していくかを確認するために用います。
 今回は、歓送迎会の収支計算から会費をいくらに設定したらよいかをシミュレーションする表を作成してみます。

 まず、表をつくるための前提となるルールを確認します。

(歓送迎会のルール)
1.女性会費は男性マイナス千円とする。
2.退職者・転出者・着任者は会費無料。
3.記念品は退職者1人あたり5,000円とする。
4.赤字となった場合幹事が自腹を切ること。
5.欠席者の氏名を取り消し線で消すこと。

このルールに基づいて作成した表が次の図になります。

c0136904_22293230.jpg



 ここでは、出欠者名簿と収支計算のための表の2種類に分けて作成してみました。入力する欄は薄い黄色で着色してある項目で、「氏名」「性別」「出欠」(以上「名簿」)と収支計算の前提条件となる「前回からの繰越金」「男性会費」「記念品単価」(退職者へのプレゼントの予算)「コース単価」(飲食店への1人あたり支払額)となります。
 これ以外の項目は自動計算されるようになっています。つまり、作成された名簿に基づいて、「男性会費」「記念品単価」「コース単価」の須知をそれぞれ変更すると収支計算がシミュレートされ、次回繰越金(いくら残るか)が計算されます。このルールでは、予算オーバーした場合追加徴収が認められていないので、幹事が自腹を切らなければなりません。そこで、なんとしても黒字にしなければならないことになります。

 実は、このシミュレーションには表作成をわかりやすくするために、いくつかの作業用セルが設けられていますが、非表示となっています。作業用セルを表示させると次の図のようになります。(クリックすると拡大表示されます。)

c0136904_4575064.jpg

 


 以下、順番にどのような計算式が入力されているかをみていきます。

(セルE5の計算式)会費を表示させる
=IF(D5="○",VLOOKUP(C5,$L$5:$M$6,2,FALSE),0)

 出席者のうち会費を徴収できるのは「出欠」欄が”○”となっている人です。欠席者は”×”となっていますし、出席者でも「退職者」「転出者」「着任者」からは会費を徴収することができません。このように、1つの項目欄に入力する内容は「互いに排他的」(1つのセルには1つの内容しか入れられず、2つ以上入力することはできない。というもの)である必要があります。これは当たり前といえば当たり前ですが、結構大事なことなので覚えておくとよいでしょう。
 さて、会費を徴収できる人のうち男性と女性では金額が異なるのですから、性別に応じて会費を表示させるようにします。ここでは、VLOOKUP関数を使いました。その参照先である範囲L5:M6には、男性と女性の会費が表示されていますが、これは条件設定で決められた数値(セルF6とF7)を読み込むようになっています。
 また計算式をコピーするために、参照先範囲は $L$5:$M$6 というふうに絶対参照にしています。(こうするとコピーしても範囲が変更されなくなります。)
 さらに、検索の型ではFALSE を指定しています。というのも、検索する値が数値ではなく「男」か「女」かという文字列ですから、TRUEにしたり省略するとエラーになるからです。

 なお、VLOOKUP関数の代わりに、次のようにIF関数を入れ子にすることもできます。

=IF(D5="○",IF(C5="男",$P$6,IF(C5="女",$P$7,0)),0)


(セルF5の計算式)女性出席者で”○”かどうかの検証
F列からJ列は、出席者の男女別内訳を計算させるために設けている作業用セルです。収支計算に影響を与えるものではなく、シミュレーションとは直接関係ありませんから、男女別出席人数を知る必要がなければこの欄は不要です。
 ただし、Excelでつくるデータベースを集計する際に参考になる考え方がいくつも盛り込まれているので、興味のある方はおつきあいください。
 セルF5(出席者が女性かどうかを検証するセル)には次の計算式が入力されています。

=IF($C5="女",1,0)*IF($D5=F$4,1,0)

 IF関数とIF関数を掛け合わせるというのはあまり見慣れない式かもしれません。左側のIF関数では「性別が女性であれば1を、そうでなければ0を返す」ことになっています。また右側のIF関数では「出欠欄がセルF4(ここに入力されているのは”○”です)と同じであれば1を、そうでなければ0を返す」というものです。
 この2つのIF関数を組み合わせると、両方のIF関数の結果がともに1であった場合(女性で”○”)のみ結果は1となります。どちらか一方でも0であれば計算結果は0になります。
 なぜこんなことをするのかというと、女性の出席者は「○」「退職」「転出」「着任」の4種類のどれか1つになるからです。それぞれの条件に該当する人数を数えるには、その条件(たとえば、女性で○、という条件)に合致する場合、1という値を設定し、全体でその1を合計すると、その条件に該当する人数がわかることになります。(セルF31がその合計値です。)

 なお、計算式の中でセルの番地指定として「$C5」や「F$4」という書き方をしています。絶対参照がセル番地の列名と行番号の両方に「$」がつけられるのに対し、ここではどちらか片方にだけ「$」がつけられています。これを混合参照といい、計算式をコピーした祭に、変わってほしいものと変わると困るものを分けるときに用います。ちなみに、この式を右方向にコピーするとどうなるかというと、

(コピー前)
=IF($C5="女",1,0)*IF($D5=F$4,1,0)

(コピー後)
=IF($C5="女",1,0)*IF($D5=G$4,1,0)

 また、縦方向にコピーすると、

(コピー前)
=IF($C5="女",1,0)*IF($D5=F$4,1,0)

(コピー後)
=IF($C6="女",1,0)*IF($D6=F$4,1,0)

 このようになり、単純にコピー&ペーストするだけよいことがおわかりいただけるかと思います。

 また、このような考え方(1×1=1,1×0=0)を応用して作成したのが次の計算式です。

=IF(($C5="女")*($D5=F$4),1,0)

 この式の場合、条件がTRUEとなるのは、($C5="女")がTRUEであり、しかも($D5=F$4)がTRUEの場合のみです。なぜなら、TRUE×TRUE=TRUEであり、TRUE×FALSE=FALSEとなり、FALSE×FALSE=FALSEとなるからです。(というのは、TRUE=1,FALSE=0だから)

 ゆえに、この式は次の計算式と同じ意味を持ちます。
=IF(AND($C5="女",$D5=F$4),1,0)

 したがって、この3種類のうちどれを選んでも同じ結果となります。

(セルG5の計算式)女性出席者で退職者かどうかの検証
=IF($C5="女",1,0)*IF($D5=G$4,1,0)

(セルH5の計算式)女性出席者で転出者かどうかの検証
=IF($C5="女",1,0)*IF($D5=H$4,1,0)

(セルI5の計算式)女性出席者で着任者かどうかの検証
=IF($C5="女",1,0)*IF($D5=I$4,1,0)

(セルJ5の計算式)男性の場合と欠席者の場合は0となります
=SUM(F5:I5)

(セルC31の計算式)営業部の総人数
=COUNTA(C5:C30)

(セルD31の計算式)出欠欄記入済みの件数-記入漏れをチェックするため
=COUNTA(D5:D30)



 以下は、右側の集計表の計算式です。

c0136904_2231479.jpg



(セルP7の計算式)女性会費は男性マイナス千円だから
=P6-1000

(セルP12の計算式)営業部の総人数
=COUNTA(D5:D30)

(セルP13の計算式)
=COUNTIF(D5:D30,"×")欠席者の下図

(セルP14の計算式)男性出席者:出席者合計から女性出席者を引いたもの
=P16-P15

(セルP15の計算式)女性出席者の人数
=SUM(J5:J30)

(セルP16の計算式)出席者合計(総人数-欠席者)
=P12-P13

(セルP17の計算式)退職者の人数
=COUNTIF($D$5:$D$30,P17)

(セルP20の計算式)退職者・転出者・着任者の合計
=SUM(Q17:Q19)

(セルP23の計算式)前回からの繰越金
=Q5

(セルP24の計算式)会費合計
=E31

(セルP25の計算式)収入合計:前回から繰越金+会費合計
=SUM(Q23:Q24)

(セルP26の計算式)飲食費合計:コース単価×出席者人数
=Q9*Q16

(セルP27の計算式)記念品購入額:記念品単価×退職者人数
=Q8*Q17

(セルP28の計算式)支出合計:飲食費合計+記念品購入額
=SUM(Q26:Q27)

(セルP29の計算式)次回繰越金:収入-支出
=Q25-Q28


 このようにして作成した表は、条件設定を変えると収支結果(次回繰越金)が変わっていくことになります。
なお、Excelには、「次回繰越金を1万円確保したい場合、会費をいくらに設定したらいいか?」を簡単に求める機能があります。次回は、この「ゴールシーク」という機能について書いてみたいと思います。
by T_am | 2011-06-18 22:38 | Excel のあの手この手
今回は、作業用セルを設けると計算式をシンプルにすることができるという事例をお目にかけます。
 下図のように、テストの点数を入力すると、その点数に応じて評価(この場合、「優」「良」「可」「不可」の4種類)を自動的に表示させることにします。


c0136904_2373115.jpg
 

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

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


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以前では「編集」メニューの「置換」)を使って、計算式の中の文字列を一括修正することもできます。(この場合、「優」という文字列を「たいへんよくできました」という文字列に置換することになります。)


c0136904_239566.jpg



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


c0136904_2310599.jpg




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


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の計算式
 省略



c0136904_23144468.jpg

by T_am | 2011-06-15 23:18 | Excel のあの手この手
 Excelというソフトは自由度が高く、こうでなければならないというのはないので、発想を柔軟にすればいろいろなことが実現できるようになっています。多くの書籍でExcelのさまざまなテクニックが紹介されていますが、それらを全部覚えるのは難しいので、共通する考え方、目の付け所というのをここではご紹介していきたいと思っています。

 すでに述べたように、パソコンとつきあうコツは「細かい単位に分けて考える」ということです。パソコンを動かすプログラムは、全体でみると巨大で複雑なものですが、よくみると細かいパーツをうまく組み合わせてつくられています。Excelという表をつくるアプリケーションも、「自分でつくる」という点は同じですから、「細かい単位で分けて考える」とどんなに複雑な表でも割と楽につくることができるようになります。
 今回は、その中で「作業用セル」を使った表づくりをご紹介します。応用の利く手法ですから、覚えておいて損はないと思います。


例題.テストの点数に応じて生徒の氏名を1番から順番に並べ替える表を作成します

 Excelにはデータの値の大きさによって並べ替えをする機能があるので、これを使うことが一番早いのですが、その代わり点数を入力するたびに「並べ替え」の作業を行わなければなりません。以前ご紹介した「入力する場所と計算結果を表示させる場所を分ける」考え方を応用すると、データを入力するだけで順位が自動計算されて、順番に表示される表を作成することができます。

(表の完成形)
c0136904_22291766.jpg


 この表をつくるには、次の2つの作業を行う必要があります。
1)生徒の順位が何番かを計算する。(RANK関数を使います)
2)計算された生徒の順位に応じて氏名を並べ替える。(VLOOKUP関数を使います)

■生徒の順位を計算する
 先ほどの表ではB列が表表示となっています。実はここに生徒の順位を計算する式が入力されているのです。

(B列を表示させた表)
c0136904_2230598.jpg


 作業用セルの最初の行には、次の計算式が入力されています。

=RANK(D3,$D$3:$D$8)

 この関数は、セルD3の値が範囲D3:D8の中で何番目になるかを計算させるものです。これを下方向にコピーすると、残りの生徒の順位を計算するための作業用セルができあがります。(セルの番地の間に $ がついているのは、計算式をコピーしたときに参照先の番地が変わらないように絶対参照にしているからです。範囲をドラッグして指定したときにF4キーを押すと絶対参照にすることができます。)
 生徒の順位を計算させるための作業用セルを氏名の左側(B列)に設けています。作業用セルというのは作業を完成させるための中間処理を行うものです。作業用セルは印刷して他人に見せる必要はありません。できあがった表をすっきりさせるためにも通常は非表示にしておきます。
 ここで、作業用セルを氏名の左側に設けたのは、この次の工程でVLOOKUP関数を使うためです。
 

■順位によって生徒の氏名を並べ替える
 既に申し上げたように、Excelにはデータそのものを並べ替える機能があり、それを使ってもいいのですが、そうすると点数を入力するたびに「並べ替え」を行わなければなりません。それでは大変なので、点数を入力すれば後はExcelが自動的に処理してくれるようにします。そこで、vlookup関数を使うことにします。

(vlookup関数の式が表示されている表)
c0136904_22305539.jpg



 G列にはあらかじめ1~6の数値を入力しておきます。
 セルH3に次の計算式を入力し、下方向にコピーします。

=VLOOKUP(G3,$B$3:$C$8,2,FALSE)

 この式の意味は、「指定された範囲(B3からB8まで)の一番左端の列(B列)の中でセルG3に一致する値がある行を探し出し、該当する行の左端(B列)から2番目の列の値を表示させる」というものです。この場合B列が1となっている行の2番目の列(C列)には「安倍晋一」という値が入っているので、それが表示されることになります。なお、検索の型としてFALSEを指定しているのは検索する範囲の第1列の値が昇順(値が小さいものから順番に並べられている)になっていないからです。第1列値が昇順になっている場合、検索の型としてTRUEを使うことができます。この場合検索する値が完全に一致するものがなくとも、検索値未満で最大値を探し出してくれます。

検索の型    TRUE    
事前準備    検索範囲を昇順で並び替えておく
検索方法     完全一致するものがない場合直近の値を検索する


       
検索の型    FALSE
事前準備      検索範囲を並び替える必要なし
検索方法    完全一致するものを検索。ない場合はエラーとなる



 このように作業用セルを設けておくことのメリットとして、他に「計算式がシンプルになる」という場合があります。
 次回は、テストの点数を評価するという例題でこのことをご紹介したいと思います。
by T_am | 2011-06-14 22:33 | Excel のあの手この手