このページは編集中です。完成予定は2008年5月15日16:00です。　Excelによるデータ処理 †

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

↑

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

↑

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

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

↑

おさらい１：レポートの課題の解答の手順 †

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

　　　　　　パチパチパチ！　提出者34名全員が満点です！！ 皆さん、すごいねー。 [bigsmile]　TAの五十畑君も脱帽です。
　　　　　　（一人だけ、.を入力し忘れて 15691%としている人がいましたが、おまけしておきました）

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

  1. 第4回授業のページの課題２の囲みの中の内容をマウスで選択し、コピー。
  2. K2Editorを立ち上げて、新しいページに貼り付ける
  3. K2Editorの置換ウィンドウを開いて 
       　検索文字：   （注：半角空白１つだけ指定）
   　置換文字： ¥t　（注：半角の￥マークと半角のt）

    と指定し、正規表現のところの□とファイルの先頭からという□をチェックして「全て置換」

    ↓この操作により、K2Editorに貼り付けたテキストで、半角の空白が全てタブに置換される

  4. 置換の終わったテキストを全て選択して、コピー
  5. エクセルを立ち上げて、貼り付け
  6. 合計値を求めるべき#1のセルをクリックして、上の方にあるΣ記号をクリックする。これで#1のセルの上に並んでいる数値の合計値が出る。同様の操作を、#2のセルについても繰り返す。
  7. 差額を求める#3のセルをクリックして、「 = 」 を入力し、引かれる方の値が入ったセルをクリックし、「 - ] を入力、続けて引く方の値の入ったセルをクリック
  8. 最後の問の答えを入力したセルをクリックして、「 =100* 」 を入力し、「授業料、入学料及び検定料収入決算」のセルをクリックし、「 / 」を入力し、最後に収入決算総額入ったセルをクリック。

    これで、千葉大学の１年間の総収入のうち、皆さんの授業料、入学料、検定料が占める割合は15.7%ぐらいだと分かりました。ついでに同様の操作で、業務費のうち教育研究経費に占める割合をみてみると、42.5%ということが分かる。

↑

おさらい２：メールで受け取った解析用データをエクセルに移動して、集計の準備 †

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

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

では、https://cuacmsrv.chiba-u.jp/am_bin/am_main.cgi/login　にアクセスして、メールで送信してあるサンプルデータを受信してみよう。下の囲みの中のようなデータが届いているはずだ。

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

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

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

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

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

という形で集計したい。

手順をちょっと自分で考えて、やってみよう。

  1. 受信したデータをK2Editorにコピー
    データをじーっとみると、 
    種名＜半角スペース２個＞数字
    という並びが見えてくる。
  2. K2Editorで正規表現検索・置換で全置換 
    検索文字列： +（注：半角スペースと半角+の２文字）
置換文字列：¥t（注：半角￥マークと半角t）
  3. 置換できたもの全てをコピーしてエクセルの新しいシートに貼り付け

自分で考えてここまで出来ただろうか？
#05_1.gif

↑

並び替えや集計の準備：エクセルによるデータ整形 †

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

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

という形にはなっていない。特に、データ行の上には見出し行を作り、それぞれの列の見出しを書いておく必要がある。そこで、エクセルの上で、行の挿入、コピー・ペースト等を使って、下の様な形に整形する。（操作方法は前方スクリーンで示す）。

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

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

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

↑

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

Excelはデータを表の形式で簡単に集計できる素晴らしいソフト。前回紹介した計算機能だけでは無く、データを並び替えたり、おなじものをまとめて計算したりする機能もついている。このうち、'データの'並び替え''（ソートともいう）は、大量データを扱う上で、絶対に知っておかなければならない方法なので、必ず習得して欲しい。

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

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

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

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

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

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

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

↑

並び替えたデータの集計 [smile] †

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

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

  1. 全てを選択し（左上角の<>をクリック）
  2. メニューバーから「データ／集計」
  3. 集計したいのは「種名」の「個体数」の「合計」なので、以下の３項目をチェック。（例では「現在の集計行と置き換える」、「集計行をデータの下に挿入する」をチェックしてあるが、この例ではしてなくても良い） 
    グループの基準：　種名
集計の方法： 合計
集計するフィールド：個体数
    #05_5.gif
  4. 「OK」をクリックすると集計データが表示される。
  5. 集計見出しの２をクリックすれば、集計結果だけが表示される。
    #05_7.gif
    これで、「オランダミミナグサが23個体で最も多い」と分かった。
↑

データのグラフ化 [smile] †

エクセルで集計はできたのだが、数字をみて傾向をつかむのはなかなか難しい。そこで、データをグラフにして表現する。グラフにすることによって、データの大小の程度や全体の傾向が一目で分かるようになる。
データをグラフで表現する場合に、どのグラフを使えば良いかを考える。データの持つ傾向をみるためにいろいろなグラフを試すのはいいが、人にグラフを見せるときには、そのグラフで何を言いたいのかを十分に検討して、それに適したグラフを選ぼう。データで言いたいことに適さないグラフを使うと、言いたいことが伝わらないだけでなく、余計な誤解を与えることになりかねない。エクセルで使える代表的なグラフは次のようなもの：
#05_8.gif
それぞれ、

↑

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

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

  1. まず、どんなグラフを作るか考える。ここでは、種ごとの合計値の違いを表したいのだから、棒グラフを試すことにする。
  2. どこか下か右の方の空白セル（データの入っていないセル）をクリック
  3. メニューバーの「挿入／グラフ」：グラフウィザードが表示される
  4. グラフウィザード1/4：　グラフの種類を選ぶ。
    ここでは、「縦棒グラフ」をクリックし、グラフの形式はなにも変えずに（つまり、一番左上に書かれている形式）、「次へ」
  5. グラフウィザード2/4：　グラフに使うデータを指定する。
    ＜範囲のところに何か文字列が入っていたら消しておく＞
    「種名」と「個体数」の見出し行を含めて、「ヤセウツボ」までのデータをマウスで選択
    「系列」は「列」を指定
    グラフのプレビューが表示される
    #05_9.gif
    これでよければ完了。
  6. グラフウィザード3/4:　グラフオプション：
    必要に応じて、次の画面でグラフの見栄えを設定する。
    完了をクリックすればグラフが表示される。
    #05_10.gif
↑

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

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

区画＼種名オランダミミナグサカラスノエンドウキュウリグサスズメノテッポウセイヨウタンポポオオバコヘラオオバコヤセウツボ
1105582100
210130000
31210850053
  1. どこか下か右の方の空白セル（データの入っていないセル）をクリック
  2. メニューバーの「挿入／グラフ」：グラフウィザードが表示される
  3. グラフウィザード1/4：　グラフの種類では「縦棒グラフ」をクリック。「次へ」
  4. グラフウィザード2/4：　グラフに使うデータを指定する。
    • 範囲のところに何か文字列が入っていたら消しておく
    • 「系列」タブをクリック
    • 「追加」をクリック
      • 名前:「区画1」と入力
      • 値：何か文字が入っていたら消して、「=」を入力し、 「種名」と「個体数」の見出し行を含めて、「ヤセウツボ」までのデータをマウスで選択
        #05_11.gif
    • さらに、区画2のデータを表示させるため、「追加」をクリックして、上の作業を繰り返す
    • 同様に、区画3についても同じ作業
    • 「項目軸ラベルに使用」の入力フィールドをクリックし、表の「オランダガラシ」から「ヤセウツボ」までを選択
      これでよければ完了。
      #05_12.gif
  5. グラフウィザード3/4:　グラフオプション：
    必要に応じて、次の画面でグラフの見栄えを設定する。
    完了をクリックすればグラフが表示される。
    #05_13.gif
↑

第5回授業の課題 †

↑

課題１．アンケート調査 †

  1. http://bean.bio.chiba-u.jp/joho20/ に、「自分のID」/05　という新しいページを作成し、下の囲みの中にあるアンケートをコピー・ペーストして、「回答：」の後に答えを書き込むこと。
↑

課題への回答 †

-今日の授業の進み方は？（はやい、丁度いい、おそい）
--回答：
-今日の授業の難しさはどう感じましたか（簡単　丁度いい　難しい）：
--回答：
-難しいと答えた人は、特にどの点が難しかったですか？：
--回答：
-今日の授業は（分かった　半分ぐらいは分かった　分からなかった）：
--回答：
-分からないと答えた人は、特にどの点が分からなかったですか？：
--回答：
-今日の講義で分からなかった用語があったら挙げてください：
--回答：
-高校までに習った英語、数学、国語、理科、社会のうち、どの教科が社会に出てから最も重要だと思いますか？
--回答：
↑

課題２. 復習 †

↑

課題３．予習 †

↑

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