授業/H22/情報処理/05

Excelによるデータ集計とグラフ作成 †

　Excelは本当に便利なソフト。前回やったような簡単な計算だけでなく、データの整形、並べ替え、データの集計の他、グラフ作りや、本格的なデータ解析など、本当にいろんなことができる。授業で全てを紹介する時間は無いので、生物学では必ず必要になる、並べ替え、集計、グラフ作成の手法を修得して貰う。

授業中にわからないことがあったら、ここに書き込んでください

第5回授業の獲得目標： †

1. 正規表現検索・置換とエクセルの操作に習熟する
2. エクセルによるデータの並び替え方法を習得する
3. エクセルによるデータの集計方法を習得する
4. エクセルによるグラフ作成方法を習得する

正規表現検索・置換とエクセルの操作に習熟 †

アンケート調査の結果をみると、K2Editorの操作と正規表現やエクセルの操作が難しいという意見がちらほら。こういう技術を習得するには、とにかく使って慣れるしかないので、今日もいくつか練習、練習。今後の講義でも、１回の授業で最低１回は、正規表現検索・置換を（ムリヤリでも）利用するつもり。

前回レポートの課題・覚えておくべきこと †

前回のレポート課題を採点してみると、

　　　　　　パチパチパチ！　提出者37名のうち、ほとんどの人が満点（4点満点）！！ 皆さん、すごいねー。 　

こんなにできの良い皆さんには釈迦に説法かもしれないけれど、一応、操作をおさらいしておこう。

• 習得したかったことは、ウェブページに貼られたテキストデータ（あるいは、PDFファイルからコピーしたテキストデータ）を再利用して、自分のエクセルファイルで集計するということ。ポイントは、
  

  テキストデータを"タブ"で区切れば、エクセルの表に簡単に移せる

  ということ。これだけは、絶対に忘れないでおこう。
  

演習1：　ウェブに貼ってある解析用データをエクセルに移動して、今日やる集計の準備 †

これからの学生生活で、皆さんはきっといろんなデータを集計する機会に出会うはず。例えば、名簿、部費の管理、アンケート調査の結果、そしてもちろん、実験データ。こういうデータは必ずしも、いつも最初からエクセルに入力されている訳ではない。たとえば、メールで受信した解析データをエクセルで集計したい場合もあるはず。そんなときにもやっぱり、

データの区切りをタブに置換して、エクセルで集計！

では、下の囲みの中のようなデータを解析してみよう。これは、千葉大の西千葉キャンパスの草地に３つの区画をつくり、それぞれの区画での植物の出現個体数をまとめたものだ。

区画No.1
カラスノエンドウ   5
セイヨウタンポポ   2
オランダミミナグサ 10
オオバコ　1
スズメノテッポウ  8
キュウリグサ　 5

区画No.2
キュウリグサ  1
スズメノテッポウ　　3
オランダミミナグサ  1

区画No.3
カラスノエンドウ  10
オランダミミナグサ 12
ヘラオオバコ  5
ヤセウツボ  3
キュウリグサ  8
スズメノテッポウ  5

このデータでは、区画ごとに、種名と出現個体数が書かれている。これをエクセルで

区画No.　　種名　　出現個体数	日付（＜日付については後ほど手入力＞）
1　　　　　　　オオバコ     1        2010/05/20
. . . . .  . . . . .  . . . . .  . . . . . 

という形で集計したい。

手順をちょっと自分で考えて、やってみよう。
この操作さえ覚えれば、調査データをその場で携帯メールに打ち込み、集計する人に送信しちゃうなんていうことができるので、とても便利。

演習1：　作業手順 ・K2Editorによる正規表現を用いた一括検索・置換 †

1. データをK2Editorにコピー
   データをじーっとみると、

   種名＜スペース＞数字

   という並びが見えてくる。どうやらスペースには、半角と全角のスペースが混在しているようだ
2. K2Editorで正規表現検索・置換で全置換

   検索文字列： [ 　]+（注：半角と全角のスペース1文字以上の繰り返し）
   置換文字列：¥t（注：半角￥マークと半角t）

3. 置換できたもの全てをコピーしてエクセルの新しいシートに貼り付け

自分で考えてここまで出来ただろうか？

演習1：　作業手順 ・エクセルによるデータ整形 †

これでテキストファイルからエクセルへのデータの移動はできたが、まだ、

区画No.	種名	個体数	日付（＜日付については後ほど手入力＞）

という形にはなっていない。

この後で行う集計作業では、データ行の上には''見出し行''を作り、それぞれの列の見出しを書いておく

ことが必要だ。そこで、エクセルの上で、行の挿入、コピー・ペースト等を使って、下の様な形に整形する。（操作方法は前方スクリーンで示す）。

1. 見出し作成：　ワークシートの最左端の"１"という四角をクリックして、1行選択。メニューバーの「挿入／行」　で最上部に１行挿入。
2. それぞれのセルに、見出しを入力（上の囲みの中をコピー・ペーストしてもよい）
   

ついでに、上の図のように、日付も入力しておこう。１つ入力したら、あとは、コピー・ペースト。

これで集計の準備はできた。

サンプルデータの並び替え【必修項目】 †

'データの'並び替え''（ソートともいう）は、大量データを扱う上で、絶対に知っておかなければならない方法なので、必ず習得して欲しい。

ぱっと見で分からなくても、並べ替えたり、集計したりすると、何かが見えてくる

では、サンプルデータを見てみよう。ここで、

全部のデータを合わせたとき、どの種の個体数が最も多いか

を知りたいと思ったのだが、この表では、同じ種がばらばらに出てくるのでわかりにくい。そこで、「種名」で並べ替えてみる

• 手順：

1. 全てを選択し（左上角の<>をクリック）
2. メニューから「データ／並べ替え」
   • 並べ替えウィンドウの中、「範囲の先頭行」で「タイトル行」を指定し（ラジオボタンをクリック）
   • 優先されるキーで「種名」、昇り順
     
3. 「OK」をクリックすると、種名順に並びかわる。
   

これで、同じ種名のものが近くにまとまったので、全部のデータを合わせたとき、どの種の個体数が最も多いかかがわかる。

並び替えでは２つの並べ替えキーを設定することもできる。先の並び替えの２番目のキーに「個体数」を指定して並べ替えてみよう。

並び替えたデータの集計【必修項目】 †

上の並び替えで、どうやらオランダミミナグサの個体数がもっとも多いとわかるのだが、実際に何個体現れたのかを知ろうと思うと、いちいち計算しなければならくて面倒。そういうときにエクセルの「集計」機能を使って、自動的に計算させる。

　　　　重要事項：集計する前にグループの基準になる項目で必ず並べ替えておくこと

• 手順：

1. 全てを選択し（左上角の<>をクリック）
2. メニューバーから「データ／集計」
3. 集計したいのは「種名」の「個体数」の「合計」なので、以下の３項目をチェック。（例では「現在の集計行と置き換える」、「集計行をデータの下に挿入する」をチェックしてあるが、この例ではしてなくても良い）

   グループの基準：　種名
   集計の方法： 合計
   集計するフィールド：個体数

   
4. 「OK」をクリックすると集計データが表示される。
   
5. 集計見出しの２をクリックすれば、集計結果だけが表示される。
   
   これで、「オランダミミナグサが23個体で最も多い」と分かった。

   万一失敗しても、集計データはすぐに消せる。

   集計データを削除するには、上の集計ウィンドウで「削除」をクリック。 元データは削除されないので心配無用。

コラム：　図書館資料の有効利用 †

授業料の免除をうけている人以外は、千葉大学に年間、535,800円の学費を支払っている。月あたりの額を計算すると（つまり12で割ると）、44,650円。なかなか大きな金額だ（さらに、入学金が282,000円必要）。1年生の前期では標準的には12科目ぐらいの授業を履修しているので、1科目あたりの単価を計算すると、22,325円。1講義あたりで計算すると、1,488円。これを高いと見るか、安いと見るかはひとそれぞれだが、大学にお金を払って授業を受けている限り、支払った分だけの教育は受けたいと思うのが当然だろう。

ところで、大学に支払っている授業料は、授業のためだけではなく、図書館で閲覧できる本などにも使われている。これらの資源も、ぜひ有効に利用したいところだ。そこで、いくつか、もしかすると皆さんが気づいていない、お得な図書館資料を紹介しよう。

日経BP †

日経BPという出版社を知っているだろうか？書店に並ぶ、下のような雑誌を発行している出版社だ。

　　　

雑誌の出版社は違うが、日経サイエンスは、理学部の皆さんで読んだことのある人がいるかもしれない。
　　

このページをみると、日経で出している雑誌の一覧がある：　http://bizboard.nikkeibp.co.jp/daigaku/
これらの雑誌、書店で購入すると、一冊600円から1,400円ぐらいするのだが、千葉大学の皆さんは、バックナンバーを無料で読めるって知っていただろうか？学内パソコンからアクセスして、PDFで読むので、少し使いにくいかもしれないが、質の良い情報が無料で入手できるんだから、利用しない手は無い。

千葉大学図書館で利用できる、その他のオンラインコンテンツ †

• NetLibrary
  洋書24冊、和書519冊の購読タイトル全文が閲覧できる。上のリンクをクリックしてNetLibraryにアクセスし、「利用可能なコレクション」を一覧すれば、最近出版された書籍を含め、いろんな本が読めることが分かる。生物学科の皆さんが興味を持ちそうなのは、例えば、
  • 進化の生態学: 生物間相互作用が織りなす多様性　種生物学研究 ; 第31号　著者： 横山潤; 堂囿いくみ.　出版： 東京 文一総合出版 Bun-ichi sogo shuppan Co., Ltd., 2008.
    
  • オープンソースで学ぶバイオインフォマティクス 出版： 東京 東京電機大学出版局 - Tokyo Denki University Press, 2008.
    
  • しごとの日本語: メールの書き方編 = Japanese for business　　著者： 奥村真希.; 釜淵優子.　出版： 東京 アルク – ALC PRESS INC., 2008.
    

• パソコンスキルアップ講座
  • Excel　：　授業で習う内容も、より発展的な使い方も、かなり詳しく説明されている。
  • Word
  • セキュリティ
    他、多数。

データのグラフ化 †

エクセルで集計はできたのだが、数字をみて傾向をつかむのはなかなか難しい。そこで、データをグラフにして表現する。グラフにすることによって、データの大小の程度や全体の傾向が一目で分かるようになる。

データをグラフで表現する場合に、どのグラフを使えば良いかを考える。データの持つ傾向をみるためにいろいろなグラフを試すのはいいが、人にグラフを見せるときには、そのグラフで何を言いたいのかを十分に検討して、それに適したグラフを選ぼう。データで言いたいことに適さないグラフを使うと、言いたいことが伝わらないだけでなく、余計な誤解を与えることになりかねない。エクセルで使える代表的なグラフは次のようなもの：

それぞれ、

• 縦棒・横棒グラフは、対象間で数値の大小を表現するのに適している
• 折れ線グラフは、時系列に沿った数値の変化を表現するのに適している。
• 円グラフは、対象ごとに全体に占める割合を表現するのに適している。
• 散布図は、２つの変数間の相関を見るのに適している。

グラフの作成１：まずは単純なグラフから †

では、上の集計データから、種ごとの個体数の合計値をグラフで表してみよう。

1. まず、どんなグラフを作るか考える。ここでは、種ごとの合計値の違いを表したいのだから、棒グラフを試すことにする。
2. どこか下か右の方の空白セル（データの入っていないセル）をクリック
3. メニューバーの「挿入／グラフ」：グラフウィザードが表示される
4. グラフウィザード1/4：　グラフの種類を選ぶ。
   ここでは、「縦棒グラフ」をクリックし、グラフの形式はなにも変えずに（つまり、一番左上に書かれている形式）、「次へ」
5. グラフウィザード2/4：　グラフに使うデータを指定する。
   

   ＜範囲のところに何か文字列が入っていたら消しておく＞

   「種名」と「個体数」の見出し行を含めて、「ヤセウツボ」までのデータをマウスで選択
   「系列」は「列」を指定
   グラフのプレビューが表示される
   
   これでよければ完了。
6. グラフウィザード3/4:　グラフオプション：
   必要に応じて、次の画面でグラフの見栄えを設定する。
   完了をクリックすればグラフが表示される。
   
   

項目軸名の変更 †

上のようにしてグラフはできたのだが、それぞれの項目軸には、「オオバコ 計」というように「計」という文字が余分に入っている。ここで、グラフに表示されるのは種名のみにしたい（つまり「計」を消したい）。
こういう時は、エクセルの関数を使って、項目名から「計」を除いたデータを新に作成して、グラフ作成に利用してみよう。

エクセルには様々な便利な'関数'がある。前回使った sum関数もその１つ。今回のように、ある文字列を消したり、別のものに置き換えたいときには、文字列の置換に使える関数： SUBSTITUTE() を利用する

1. 種名だけが入ることになる、新しい列を、合計値の左横に挿入。
2. 新しくつくったセルのうちどれか１つをクリックする。
   • このセルの下には、種名のみ（「計」という文字を除く）だけを入れたい。そのためには、１つ左の「xxxx　計」という文字列から、「計」という文字を除けば良い。そこで、セルにsubstitute関数を入力
     もし、使い方が分からなかったら、Excelのヘルプを表示させ、substituteを検索する。そうすると、使い方が表示される（以下、Excelヘルプから引用）

     substitute(文字列, 検索文字列, 置換文字列, 置換対象)
     　文字列   置き換える文字を含む文字列を指定します。
      　　　　　目的の文字列が入力されたセル参照を指定することもできます。
     　検索文字列   置き換える文字列を指定します。
     　置換文字列   検索文字列 と置き換える文字列を指定します。

     そこで、たとえば「オオバコ 計」（A1のセル）の右横に新しい種名を入れたいのなら、

     =substitute(A1, "計", "")

     と入力。このとき、検索したり置換したりする文字列は、"で囲んでおく。
3. 「計」を除いた文字列が表示されるので、他のセルにコピー・ペーストする。

グラフの作成２：系列３つを一挙に指定 †

先ほどのグラフは１つの系列（データの並び）だけを指定したグラフだったので、すごく簡単な操作で自動的にグラフができた。今度はそれぞれの区画毎に現れる種数をグラフで表示してみよう。この場合、系列は３つになる。また、共通している項目が全てに必要になるため、もとの表を次のように整形する（出現しない種名についても0というデータを追加する）。

• 手順：

1. 上のデータをコピーして、エクセルにペースト。うまくペーストできないときは、いちど、K2Editorにペーストしてから、再度コピーペースト。
2. 表全体を選択したら、ツールバーのグラフアイコンをクリック：グラフウィザードが表示される（あるいは、メニューバーの「挿入／グラフ」）
3. グラフウィザード1/4：　グラフの種類では「縦棒グラフ」をクリック。「次へ」
4. グラフウィザード2/4：　グラフに使うデータを指定する。
   
   • 範囲のところに何か文字列が入っていたら消しておく 左上隅の空白から、「区画」と「種名」を含めて、「ヤセウツボ」の右下隅までのデータをマウスで選択
     
5. 完了をクリックすればグラフが表示される

ウィルス対策ソフトの紹介 †

最初の授業で行ったアンケートで、パソコンを持っているのにウィルス対策ソフトを使っていない人が数名。
自分のパソコンがウィルスに感染すると、自分が困るのはもちろんのこと、データを交換した他のコンピュータ（USBメモリやメールなどで）にも大変な迷惑をかけることになる。
ウィルス対策ソフトは必ずインストールしておこう。フリーのソフトでは、Avastが高機能で、ウィルス対策データの更新も頻繁。ただし、１年に１度、ウェブ上で登録更新して（住所やメールアドレスを記入）、ソフトウェアに登録キーを入力する必要がある。
http://www.avast.com/ja-jp/free-antivirus-download

第5回授業の課題 †

• 提出期限：5月26日水曜正午　（下記２つ全て）

課題１．アンケート †

http://bean.bio.chiba-u.jp/moodle/　にアクセスし、第5回授業課題1・アンケートに記入して提出しなさい。
注意：　アンケートだけれど、これは課題です（ネットワークを用いたコミュニケーション、および、授業の理解度のフィードバックが評価対象）。前回の課題では、期日までに提出していない人が2名おり、その人達は第4回授業課題1の点数が0点になっています。

課題2（復習） †

http://bean.bio.chiba-u.jp/moodle/　にアクセスし、第5回課題２小テストから提出しなさい。

予習課題：（評価に関わる提出物は無し） †

プロジェクト相談で出し合ったアイデアを検討して、１班５名ぐらいを目安に、班分けを行い、次回授業時間(5月21日木曜）までにリーダー１名とプロジェクトのタイトルを決めて下さい。また、リーダーはプロジェクト相談にプロジェクト名とメンバー名を記入してください。

おまけ：時間があったら解説 †

• 英語サイトですが、グラフの作り方については、Almost Everything You Wanted to Know About Making Tables and Figuresというページが参考になります。

補足：　課題のエクセルファイルが開けない人のためのデータ †