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

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

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

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

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

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

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

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

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

      パチパチパチ! 提出者42名全員が満点(4点満点)!! 皆さん、すごいねー。 [bigsmile] 
でも、提出しなかった人が3名いた。また、課題1を提出していない人が6名いた。繰り返して言うが、課題1は期日までに提出しないと、本来配点されるべき2点が配点されなくなる。くれぐれも注意して欲しい。

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

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

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

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

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

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

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

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

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

区画No.	種名	個体数	日付(<日付については後ほど手入力>)

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

並び替えたデータの集計【必修項目[smile]

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

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

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

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

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

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

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

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

日経BP

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

NB_1540H.jpg NPC_0601H.jpg PB_0002H.jpg NSW_0151H.jpg

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

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

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

データのグラフ化 [smile]

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

グラフにすれば、データの持つ傾向が一目で分かる!

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

グラフの作成1:まずは単純なグラフから

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

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

項目軸名の変更

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

セルの操作には関数を使おう!

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

  1. 種名だけが入ることになる、新しい列を、合計値の左横に挿入。
  2. 新しくつくったセルのうちどれか1つをクリックする。
    • このセルの下には、種名のみ(「計」という文字を除く)だけを入れたい。そのためには、1つ左の「xxxx 計」という文字列から、「計」という文字を除けば良い。そこで、セルにsubstitute関数を入力
      もし、使い方が分からなかったら、Excelのヘルプを表示させ、substituteを検索する。そうすると、使い方が表示される(以下、Excelヘルプから引用)
      substitute(文字列, 検索文字列, 置換文字列, 置換対象)
       文字列   置き換える文字を含む文字列を指定します。
            目的の文字列が入力されたセル参照を指定することもできます。
       検索文字列   置き換える文字列を指定します。
       置換文字列   検索文字列 と置き換える文字列を指定します。
      そこで、たとえば「オオバコ 計」(A1のセル)の右横に新しい種名を入れたいのなら、
      =substitute(A1, "計", "")
      と入力。このとき、検索したり置換したりする文字列は、"で囲んでおく。
  3. 「計」を除いた文字列が表示されるので、他のセルにコピー・ペーストする。

グラフの作成2:系列3つを一挙に指定

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

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

#05_13.gif

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

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

第5回授業の課題

課題1.アンケート

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

課題2(復習)

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

予習課題:(評価に関わる提出物は無し)

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

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

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

性別結核悪性新生物糖尿病高血圧性疾患心疾患脳血管疾患肺炎慢性気管支炎及び肺気腫喘息胃潰瘍及び十二指腸潰瘍肝疾患腎不全老衰不慮の事故交通事故自殺死亡者数総計
2005157919660371312145839796365757310852515651897110079463668324591701522236584970
2006151719805272682095828116134856572809312901924109099714687223329625821419581370
2004155519309666942066774656154751306816415761920107058806617223667735521955557097
200675213126263823715902136692050670245514881479535811444208921494127908502503080
2003161518691267092016779896327450614865718462104107038686628823969756523396551746
200571712933864903690891466919049931246016331593542311065196771527230138317498826
1999211417581765272376739796645249903845128422250114548309660025551918922402534778
2002158418403366312108749866222947033828918722089105328428620924283817121677535305
19942290146896527628247886855510469396777331019561296283747333240821059314058476080
2000187617914064892163721566312746722823723002161110688029601725162907221656525903
19932424142222497231178830955279457976241346319651327984257324233971092013540476462
2001171518139363682102727276314645756824420862121109118202609424993869821085528768
200372212263161703581815566879344328257618551615503410135171611474533488713463205
200477512726259433640821606750844228244917071489518010311179541452631968292471505
19998211147396287427477100725374409127462559180351319395162291452839229011447253
1998197717230664242360711346552942663747127482131111677731629324984955222349512128
19952267159623710730276971869587424198018405222741157678006684282291077214231501276
19922514139674475831748638455431423285952330819171339481237613236061143513516465544
1997195516707662952537697766579042314749730272127113777714638425157982415901497796
20027331205356004351377532680284038825441899165149589757164731436035728272447074
20007801163445814390074585694024021626402173170850119231151961432237858595435750
19912449134475463334108364655740398435955338219701311376417676228791140912477450344
20017761192655779375575565687103954927001928176549379488160511450336808290441563
19902745130395449133998177457627385965664341219331325676328054221991148112316443718
19962064164824639426136897766479384727195330820871119874886372254851017014853488605
19988181116156113435671986722903728926842400174049668907150811394139129406424356
19959111033997118519569488769653721029593201204054428387148091709443757189420863
19977871083376075434770398729073659027002634174952228901150501372941577593415606
1994804967745596511480711647293641524352545172264109002161311203342766865399853
1993825934855267524391988635153534122992747171566448732157911132042736976402070
19892676127211439934867790156938334705277330219951313873247877218481146412939427114
19967941063596444463269252738873249927282687183153198708145061369941737285407606
19882954122015456938927864060832324855230359619921332069838910213581092714290428094
1992833922435065551489162626273194622202621166467688822157481107143937377391099
1991876892525001567385232627083021422272559171767047919155241027643457398379453
199091987018497958478370464317295982170253516826444794316133992343477772376587
19872959119161426140907256658744279304741348520671305362398658201241022515281408094
19863112113589433545557207261832267394825365822971328260999129204801019616499406918
198985185414481257857893063714254931954250616706581752915904920141658186361480
198891883455507863667928067863245701987256116966461724117490885438318505364920
1987106380402487366447134364882210831873255218076233638816616813135378550343078
1986105878065480971347050967457205171908270019306250595817681813033929168343702