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

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

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

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

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

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

おさらい1:レポートの課題の解答の手順

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

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

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

  1. 千葉大学平成19年度決算報告PDFの決算報告の内容をマウスで選択し、コピー。
  2. K2Editorを立ち上げて、新しいページに貼り付ける
    そして、貼り付けた内容をじーっと観察。そうすると、
    施設整備費補助金1,642 1,642 -
    ふむふむ。数字と数字の間は半角スペースで区切られているが、項目名の次の数字の前にはスペースが無い。
    では、一体どうすればいいか?
    もちろん、手作業で1つ1つタブを入れてもいいけれど、頭をつかって少しでも楽をしたい。
    たぶん、数字の前の文字は、金、費が多いので、これをタブに置き換えれば、少しは手間が省けるはず
    (※Tips: 「数字以外」と「数字」を、「タブ」と「その数字」に置き換えれば、簡単に解決できる。それには、次回以降やる、後方参照というやり方を使う)
  3. K2Editorの置換ウィンドウを開いて次ぎの検索・置換を繰り返してみよう
     1  検索文字:   (注:半角空白1つだけ指定)
        置換文字: ¥t (注:半角の¥マークと半角のt)
     2  検索文字: 金
        置換文字: ¥t (注:半角の¥マークと半角のt)
     3  検索文字: 費
        置換文字: ¥t (注:半角の¥マークと半角のt)
  4. 置換の終わったテキストを全て選択して、コピー
  5. エクセルを立ち上げて、貼り付け
  6. 数字が縦にそろっていないのが一目で分かるので、
     カット&ペーストで形式を整える
    このとき、自己収入内訳の4項目と業務費の内訳の2項目は削除する。
  7. 差額を入れたいセルをクリックして、「 = 」 を入力し、引かれる方の値が入ったセルをクリックし、「 - ] を入力、続けて引く方の値の入ったセルをクリック
  8. 最後の問の答えを入力したいセルをクリックして、「 =100* 」 を入力し、「授業料、入学料及び検定料収入決算」のセルをクリックし、「 / 」を入力し、最後に収入決算総額入ったセルをクリック。

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

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

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

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

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

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

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

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

区画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個体で最も多い」と分かった。

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

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

データのグラフ化 [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. どこか下か右の方の空白セル(データの入っていないセル)をクリック
  2. ツールバーのグラフアイコンをクリック:グラフウィザードが表示される(あるいは、メニューバーの「挿入/グラフ」)
  3. グラフウィザード1/4: グラフの種類では「縦棒グラフ」をクリック。「次へ」
  4. グラフウィザード2/4: グラフに使うデータを指定する。
    • 範囲のところに何か文字列が入っていたら消しておく 左上隅の空白から、「区画」と「種名」を含めて、「ヤセウツボ」の右下隅までのデータをマウスで選択
      &ref(): File not found: "#2009_05_1.gif" at page "授業/H22/情報処理/05";
  5. 完了をクリックすればグラフが表示される
  1. どこか下か右の方の空白セル(データの入っていないセル)をクリック
  2. ツールバーのグラフアイコンをクリック:グラフウィザードが表示される(あるいは、メニューバーの「挿入/グラフ」)
  3. グラフウィザード1/4: グラフの種類では「縦棒グラフ」をクリック。「次へ」
  4. グラフウィザード2/4: グラフに使うデータを指定する。
    • データ範囲のところに何か文字列が入っていたら消しておく #05_11.gif
    • 系列タブをクリックして、「追加」ボタンをクリック
      • 名前のフィールドをクリックしたら、シートのうち「区画1」のセルをクリック
      • 値のフィールドをクリックして、入っている文字を全て消したら、区画1のデータ部分(数字)を全て選択
    • さらに、区画2のデータを表示させるため、「追加」をクリックして、上の作業を繰り返す
    • 同様に、区画3についても同じ作業
    • 「項目軸ラベルに使用」の入力フィールドをクリックし、表の「オランダガラシ」から「ヤセウツボ」までを選択
      これでよければ完了。
      系列のうち、「名前」、「値」、「軸ラベル」の指定方法が理解できただろうか?全てのデータをグラフ化したいときは最初の方法でいいが、沢山の系列のうち一部を抜き出してグラフ化したいときには、上のようにすると、グラフに使う系列データを選ぶことができる
      #05_12.gif
  5. グラフウィザード3/4: グラフオプション:
    必要に応じて、次の画面でグラフの見栄えを設定する。
    完了をクリックすればグラフが表示される。
    #05_13.gif

第5回授業の課題

課題1.アンケート調査

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

課題2. 復習

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

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

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