授業/H21/情報処理/05

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

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

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

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

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

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

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

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

　　　　　　パチパチパチ！　提出者39名全員が満点！！ 皆さん、すごいねー。 　TAの白井君もビックリ！！

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

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

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

  
  この辺りの操作はもう理解していて暇だと思うので、今回は、千葉大学平成19年度決算報告PDFをつかって、PDFで報告されている決算報告を検算してみよう。
  操作手順は、次の通り

1. 千葉大学平成19年度決算報告PDFの決算報告の内容をマウスで選択し、コピー。
2. K2Editorを立ち上げて、新しいページに貼り付ける
   そして、貼り付けた内容をじーっと観察。そうすると、

   施設整備費補助金1,642 1,642 -

   ふむふむ。数字と数字の間は半角スペースで区切られているが、項目名の次の数字の前にはスペースが無い。
   では、一体どうすればいいか？
   もちろん、手作業で１つ１つタブを入れてもいいけれど、頭をつかって少しでも楽をしたい。
   たぶん、数字の前の文字は、金、費が多いので、これをタブに置き換えれば、少しは手間が省けるはず
   （※Tips：　「数字以外」と「数字」を、「タブ」と「その数字」に置き換えれば、簡単に解決できる。それには、次回以降やる、後方参照というやり方を使う）
   
3. K2Editorの置換ウィンドウを開いて次ぎの検索・置換を繰り返してみよう

    1 　検索文字：   （注：半角空白１つだけ指定）
      　置換文字： ¥t　（注：半角の￥マークと半角のt）
    2  検索文字： 金
      　置換文字： ¥t　（注：半角の￥マークと半角のt）
    3  検索文字： 費
      　置換文字： ¥t　（注：半角の￥マークと半角のt）

4. 置換の終わったテキストを全て選択して、コピー
5. エクセルを立ち上げて、貼り付け
6. 数字が縦にそろっていないのが一目で分かるので、

    カット＆ペーストで形式を整える

   このとき、自己収入内訳の４項目と業務費の内訳の２項目は削除する。
7. 差額を入れたいセルをクリックして、「 = 」 を入力し、引かれる方の値が入ったセルをクリックし、「 - ] を入力、続けて引く方の値の入ったセルをクリック
8. 最後の問の答えを入力したいセルをクリックして、「 =100* 」 を入力し、「授業料、入学料及び検定料収入決算」のセルをクリックし、「 / 」を入力し、最後に収入決算総額入ったセルをクリック。

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

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

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

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

では、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で正規表現検索・置換で全置換

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

• 手順：

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

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

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

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

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

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

• 手順：

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

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

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

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

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

データのグラフ化 †

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

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

それぞれ、

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

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

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

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

• 手順：（別の方法：　系列３つを別々に指定することで、データの意味を理解）

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

第5回授業の課題 †

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

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

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

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

     ./05

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

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

課題２. 復習 †

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

    学籍番号.xls

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

    情報処理05（学籍番号）

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

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

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

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

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