授業/H26/情報処理/06

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

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

1. 正規表現検索・置換とエクセルの操作に習熟する
2. エクセルによるデータの並び替え方法を習得する
3. エクセルによるデータの集計方法を習得する
4. エクセルによるグラフ作成方法を習得する
5. スクリーンキャプチャを使った画面の切り取り

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

演習1: グループワーク・正規表現・後方参照 †

• 授業Moodleページから行うこと。出席確認演習。
  http://bean.bio.chiba-u.jp/moodle24

注目すべき構造と、それに対応する正規表現は、次のようになる

• 解答例1:要素をそれぞれグループで指定

  構造	行頭から英文字か空白かピリオドの連続	空白	英文字のみの連続	空白	(で始まる在位年で、)が行末
  検索文字列	^([A-Za-z ¥.]+)	空白	([A-Za-z]+)	空白	¥(
  置換文字列	¥2	空白	¥1	空白	(

• 解答例2:空白以外・改行以外というグループを指定（注意：　半角の(は、正規表現で意味を持つ記号なので、「そのままの文字」として扱うときにはバックスラッシュ（円マーク）をつけて、(と指定する。)も同様。

  構造	行頭から任意の文字列の連続	空白	空白以外の連続	空白	(で始まる在位年で、)が行末
  検索文字列	^(.*)	空白	([^ ]+)	空白	¥(
  置換文字列	¥2	空白	¥1	空白	(

• 解答例3: 在位を3番目の後方参照要素として指定しても構わない

  構造	行頭から任意の文字列の連続	空白	空白以外の連続	空白	(で始まる在位年で、)が行末
  検索文字列	^(.*)	空白	([^ ]+)	空白	¥((.*)¥)$
  置換文字列	¥2	空白	¥1	空白	(¥3)

演習2・発展演習：　DNAの塩基配列データの整形処理の解説 †

ファイルsequence_Pdic.fastaには、Pedicularisという植物のDNA塩基配列データがFASTA形式で入っていた。GenBankからダウンロードしたままの形式なので、

>gi|310769146|gb|HM596759.1| Pedicularis procera tRNA-Leu (trnL) gene
 （GenBankID, データベース、Accession No.、種名、遺伝子の種類...）

が、それぞれのデータを示すラベル情報として入っている。これを、よりシンプルに、

>種名の省略形_Accession No.
という形に整形したい。上の例だと、

この場合、区切り文字である|に注目する。

• 解答例：「|以外」を指定する。|も正規表現で意味を持つ文字なので、[^¥|]と指定する。
  • 構造： 「行頭に>があり|以外の文字列」「|」「|以外の文字列」「|」「|以外の文字列」「|」「英文字とピリオドと数字」「|」「空白英文字連続　空白 英文字連続」「以下、任意の文字列」
• 検索文字列： ^>[^¥|]+¥|[^¥|]+¥|[^¥|]+¥|([A-Za-z0-9]+)¥.[0-9]¥| ([A-Z])[a-z]+ ([a-z]+).*$
• 置換文字列: >¥2_¥3_¥1 こうすれば、

  >P_procera_HM596759

  のような形に一括置換できる。
• 問題：　では、上のサンプルデータと正規表現を比較して、どの要素がどの正規表現に対応しているか考えてみよう。

演習3・前回復習：Excelによる表計算 †

前回の課題は、以下のデータをエクセルで処理して、#がついている部分の数字に答えることだった。

平成２２年度 決算報告書 国立大学法人千葉大学 （単位：百万円）
収入 予算額 決算額 差額
運営費交付金 17,853 17,853 0
施設整備費補助金 4,012 2,116 -1,896
船舶建造費補助金 0 0 0
補助金等収入 2,858 3,184 #7
国立大学財務・経営センター施設費交付金 86 81 -5
授業料、入学料及び検定料収入 8,289 8,514 #8
附属病院収入 20,909 23,820 #9
雑収入 340 423 83
産学連携等研究収入及び寄附金収入等 4,114 4,081 -33
引当金取崩 66 112 46
長期借入金収入 1,679 1,532 -147
目的積立金取崩 0 18 18
計 #1 #2

支出
教育研究経費 23,723 22,757 -966
診療経費 22,581 24,447 1,866
施設整備費 5,777 3,729 -2,048
補助金等 2,858 3,184 326
産学連携等研究経費及び寄附金事業費等 4,114 3,834 -280
長期借入金償還金 1,153 1,157 4
国立大学財務・経営センター施設費納付金 0 0 0
計 #3 #4
収入－支出 #5 #6

• 解答例
  • 1. データを全てK2Editorにコピー
  • 2. 半角スペースを、タブに置換（注：正規表現でタブは¥t）　（正規表現のチェックボックス忘れずに）

    検索文字：　（注：半角スペース1つ）
    置換文字：　¥t

  • 3. 置換済のテキストを、エクセルにコピー・ペースト
  • 4. #のついているセルに、計算式を入れる

    例：
    #1　カーソルをもってゆき、Σをクリック
    #2 （#1をコピー・ペースト）
    #3　カーソルをもってゆき、Σをクリック
    #4 （#3をコピー・ペースト）
    #5 =B15-B25　（注：セルの番号は人によって違うかもしれない）
    #6 （#5をコピー・ペースト）
    #7 =C6-B6
    #8, 9 （#&を一度にコピー・ペースト）
    「授業料、入学料及び検定料収入」の決算額は、収入計の決算額の何パーセントにあたるか
     =C8/C15

    　　計算後、ツールバーの%をクリック

演習4・データ整形とエクセルによる集計 †

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

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

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

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

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

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

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

という形で集計したい。

手順を考えて、自分でやってみよう。
この操作さえ覚えれば、調査データをその場で携帯メールに打ち込み、集計する人に送信しちゃうなんていうことができるので、とても便利。

演習5：　作業手順 ・K2Editorによる正規表現を用いた一括検索・置換 †

1. データをK2Editorにコピー
   データをじーっとみると、

   種名＜スペース＞数字

   という並びが見えてくる。どうやらスペースには、半角と全角のスペースが混在しているようだ
2. K2Editorで正規表現検索・置換で全置換

   検索文字列： [ 　]+（注：[]の間に半角と全角のスペース1文字ずつ入れる。これでスペースの1文字以上の繰り返しを意味する）
   置換文字列：¥t（注：半角￥マークと半角t：タブのこと）

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

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

演習6：　作業手順 ・エクセルによるデータ整形 †

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

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

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

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

ことが必要だ。そこで、エクセルの上で、行の挿入、コピー・ペースト等を使って、下の様な形に整形する。（操作方法は前方スクリーンで示す。ほとんど全て、マウスの右クリックでメニューを表示させてできる。）。

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

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

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

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

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

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

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

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

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

• 手順：

1. 全てを選択し（Aと1の間のボタンをクリック）
   
2. 右端の「並べ替えとフィルター」→「ユーザー設定の並び替え」
   
   • 並べ替えウィンドウの中、「範囲の先頭行」で「タイトル行」を指定し（ラジオボタンをクリック）
   • 優先されるキーで「種名」、昇り順
     
3. 「OK」をクリックすると、種名順に並びかわる。
   

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

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

並び替えたデータの集計【必修項目】 †

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

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

• 手順：

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

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

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

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

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

データのグラフ化 †

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

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

それぞれ、

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

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

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

1. まず、どんなグラフを作るか考える。ここでは、種ごとの合計値の違いを表したいのだから、棒グラフを試すことにする。
2. グラフ化したい部分を選択
   
   
3. 「挿入」リボンから、棒グラフを選択
   
   これで完了。

関数を利用した項目軸名の変更 †

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

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

1. 種名だけが入ることになる、新しい列を、合計値の左横に挿入。
2. 新しくつくったセルのうちどれか１つをクリックする。
   • このセルの下には、種名のみ（「集計」という文字を除く）だけを入れたい。そのためには、１つ左の「xxxx　集計」という文字列から、「集計」という文字を除けば良い。そこで、セルにsubstitute関数を入力
     もし、使い方が分からなかったら、Excelのヘルプを表示させ、substituteを検索する。そうすると、使い方が表示される（以下、Excelヘルプから引用）

     substitute(文字列, 検索文字列, 置換文字列, 置換対象)
     　文字列   置き換える文字を含む文字列を指定します。
      　　　　　目的の文字列が入力されたセル参照を指定することもできます。
     　検索文字列   置き換える文字列を指定します。
     　置換文字列   検索文字列 と置き換える文字列を指定します。

     そこで、たとえば「オオバコ 集計」（A1のセル）の右横に新しい種名を入れたいのなら、

     =substitute(A1, "集計", "")

     と入力。このとき、検索したり置換したりする文字列は、"で囲んでおく。
3. 「集計」を除いた文字列が表示されるので、他のセルにコピー・ペーストする。

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

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

• 手順：

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

演習7・　フィードバックデータの整形とグラフ作成 †

• 前回のフィードバックでMS Wordの様々な機能を使えるかどうかということをお聞きした。そのデータを処理して、グラフを作って見よう。欲しいグラフの例は、授業moodleページにのっている。下に添付したエクセルファイルをダウンロードして、グラフを作って見よう。
  #6_feedback1.xlsx

演習8・発展演習：　時間のある人は次の発展演習をやってみよう。 †

• 1. 添付のエクセル書類は、東京都健康安全研究センターで公表されている環境放射線量測定結果を表にまとめたものです。このデータを使って、3月から現在までの、環境放射線量の変化をグラフにしなさい。放射線.xlsx
• 2. 千葉県のホームページには、千葉県における上水（蛇口水）の核種分析結果が掲載されています（ 4月分、3月分）。3月分と4月分のデータをエクセルの上であわせて、上水核種分析結果をグラフにしなさい。

スクリーンキャプチャを使った画面の切り取り †

これまでに、画面に表示されたテキストデータをコピーして加工し、自分のデータとして使う方法を習得した。いわゆる、

コピー・ペースト

今日の授業では、画面に表示された情報を画像として再利用する方法を学ぶ。この方法をしっていると、画面に表示されたグラフや画像を切り取って、編集し、ウェブページやレポートに貼り付けることができるようになる。

ウィンドウズの基本：　スクリーンキャプチャ †

ウィンドウズにはキーボードに、画面の情報をそのまま画像としてコピーするキーがついている。この機能を使う方法を覚えておくと、とても便利

1. まず、キーボードの一番最上段にある
   

   Prt Sc SysRq

   というキーを押してみよう。これで画面がキャプチャされたが、見た目には何も起こらない。
2. 次に、画像を扱うウィンドウズの基本ソフトである、ペイントを立ち上げる
   
   ペイントの画面が表示されたら　Ctrl-Vで、ペーストしてみよう。そうすると、先ほどキャプチャされた画面が現れる。
3. 範囲選択ツールなどを使って大きさを整えたり、色を変えたりしてみよう。
   
   
4. 終わったら、保存 。写真ならJPEG､色数の少ない図ならgif。
   • 画像の形式の違いは、http://www5e.biglobe.ne.jp/~komichan/webdesign/mame_gazouyoryo.html　を参照のこと。

切り取る範囲がもう決まっていて、保存やコピーだけをしたいのなら、Snipping Tool †

スクリーンキャプチャとペイントによる編集はとても便利なのだが、もしも、切り取り範囲がすでに決まっていて、それほど編集する必要は無く、ファイルとして保存したり、コピーだけをしたいのなら、Snipping Toolが便利。

1. スタートメニューからSnipping Toolを立ち上げる
   
   
2. 切り取りたい範囲をマウスのドラッグで選択する。
3. ファイルとして保存したり、コピー・ペーストしたりできる。
   
   

Dropboxフォルダへのファイルの保存 †

上の方法で、自分で作ったグラフを、レポート提出用のDropboxフォルダに保存しよう。今回の課題ではこの方法で画像ファイルを提出することになるので、やり方を覚えておこう。

• エクセルで作成したグラフをSnipping Toolを使って選択し、gif形式で保存する。ファイル名は

  学生証番号.gif

  にしておく
• できたファイルを、前回課題で作ったDropboxの共有フォルダに保存する
• Dropboxの共有フォルダは、TAの山本君と共有されているはず。しているかどうかは、DropBoxにログインして、確かめておこう。

第6回授業の課題 †

東京都健康安全研究センターでは、都内の降下物の放射線量測定結果を示す表（http://monitoring.tokyo-eiken.go.jp/mon_fallout_data.html）が公表されています。このデータをから、2011年3月18日から平2011年12月31までの、３種類の放射性物質の線量の変化を知りたいです。ExcelまたはRを使って、分かりやすいグラフを作成しなさい。

提出方法： エクセルまたはRでグラフを作成し、グラフエリアだけをSnipping Toolで切り取るか保存して、GIF形式（あるいはpng形式）のファイルとして、教員およびTAと共有している自分のDropBoxフォルダから提出しなさい。ファイル名は、 自分の学生証番号#6.gif としなさい。

評価： 提出していれば、基本点が得られます。 グラフでは、複数の放射性同位体元素の放射線量の変化がわかるように工夫してください。 グラフは見やすく編集してあるほど、評価は高くなります。