授業/H20/情報処理/05

Excelによるデータ処理 †

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

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

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

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

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

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

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

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

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

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

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

  操作手順は次の通り：【この辺りの操作はもう理解していて暇だという人は、千葉大学平成18年度決算報告PDFをつかって同じことができるかどうか、挑戦してみよう】

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. 置換できたもの全てをコピーしてエクセルの新しいシートに貼り付け

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

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

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

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

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

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

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

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

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

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

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

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

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

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

• 手順：

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

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

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

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

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

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

• 操作：

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

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

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

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

データのグラフ化 †

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

それぞれ、

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

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

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

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

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

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

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

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

• 操作：

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

第5回授業の課題 †

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

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

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

• 手順
  1. 画面の上の方にある〔 新規 〕をクリック
  2. ページ名を尋ねる入力スペースが表示されるので、半角英数字で、ドット・スラッシュ・0・5を下のように入力

     ./05

  3. 下の囲みの中をコピー・ペーストし、回答や答えを書き込む

     *第5回授業・基本課題 
     **氏名：
     **課題への回答
     -今日の授業の進み方は？（はやい、丁度いい、おそい）
     --回答：
     -今日の授業の難しさはどう感じましたか（簡単　丁度いい　難しい）：
     --回答：
     -難しいと答えた人は、特にどの点が難しかったですか？：
     --回答：
     -今日の授業は（分かった　半分ぐらいは分かった　分からなかった）：
     --回答：
     -分からないと答えた人は、特にどの点が分からなかったですか？：
     --回答：
     -今日の講義で分からなかった用語があったら挙げてください：
     --回答：
     --授業に関する要望・質問があったらなんでもどうぞ：
     --回答：
     -高校までに習った英語、数学、国語、社会、理科のうち、社会に出てから
     最も役立つのはどれだと思ってますか？
     --回答（理由もあれば嬉しいです）：
     --課題２の答え：
     ---問1・性別：
     ---問1・年：
     ---問2：男性：
     ---問2：女性：

課題２. 復習 †

• 右に添付した表は、厚生労働省統計表データベースシステムで公開されている、「１Ｃ　上巻　死亡　第５．１２表＿ 死因年次推移分類別にみた性別死亡数・率（人口１０万対）」の表から、1986年以降の男女別死亡数のみ抜粋したものです(2008_情報#05課題.xls)。この表を使って次の操作を行い、答えを解答欄に書いてPukiwikiページで提出するとともに、作成したグラフを保存したファイルをメールに添付して提出しなさい。
  • 問1：表の中で、心疾患による死亡数が最も多いのは、どちらの性別の何年のデータですか？（ヒント：どれか１つの列をキーにして並べ替え）
  • 問2：男女それぞれについて、肺炎に死亡数の総計を集計して答えなさい。（ヒント：どれか２つのキーで並べ替えてから集計）
  • 問3：肺炎による男女それぞれの死亡者数の1986年から2006年までの推移をグラフにし、

    学籍番号.xls

    というファイル名で保存して、メールの添付書類として に送信しなさい（ヒント：２つのキーで並べ替え。グラフに使う系列は２つ。グラフの種類にも注意）。なお、メールの件名は

    情報処理05（学籍番号）

    とすること。グラフの見やすさやエクセルの使用法について、どれだけチャレンジしているかも評価の対象となります。

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

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