授業/H26/情報処理/06 のバックアップ(No.2)

バックアップ一覧
差分を表示
現在との差分を表示
ソースを表示
授業/H26/情報処理/06 へ行く。
- 1 (2014-05-22 (木) 11:03:43)
- 2 (2014-05-22 (木) 14:18:35)
- 3 (2014-05-22 (木) 16:20:27)

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

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

↑

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

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

↑

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

↑

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

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

↑

発展演習：　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
```
のような形に一括置換できる。どの要素がどの正規表現に対応しているか、じっくり見て、考えてみよう。

↑

前回復習：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

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

↑

演習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による正規表現を用いた一括検索・置換 †

データをK2Editorにコピー
データをじーっとみると、
```
種名＜スペース＞数字
```
という並びが見えてくる。どうやらスペースには、半角と全角のスペースが混在しているようだ

K2Editorで正規表現検索・置換で全置換

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

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

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

↑

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

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

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

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

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

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

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

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

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

↑

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

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

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

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

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

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

手順：

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

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

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

↑

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

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

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

手順：

全てを選択し（左上角の<>をクリック）
「データ」リボンから、「小計」
集計したいのは「種名」の「個体数」の「合計」なので、以下の３項目をチェック。（例では「現在の集計行と置き換える」、「集計行をデータの下に挿入する」をチェックしてあるが、この例ではしてなくても良い）
```
グループの基準：　種名
集計の方法： 合計
集計するフィールド：個体数
```
「OK」をクリックすると集計データが表示される。
集計見出しの２をクリックすれば、集計結果だけが表示される。

これで、「オランダミミナグサが23個体で最も多い」と分かった。
万一失敗しても、集計データはすぐに消せる。

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

↑

データのグラフ化 †

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

グラフを使うことで、データの持つ傾向が一目で分かる！

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

それぞれ、

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

↑

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

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

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

これで完了。

↑

項目軸名の変更 †

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

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

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

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

↑

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

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

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

手順：

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

↑

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

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

↑

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

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

コピー・ペースト

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

↑

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

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

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

Prt Sc
SysRq
というキーを押してみよう。これで画面がキャプチャされたが、見た目には何も起こらない。
次に、画像を扱うウィンドウズの基本ソフトである、ペイントを立ち上げる

ペイントの画面が表示されたら　Ctrl-Vで、ペーストしてみよう。そうすると、先ほどキャプチャされた画面が現れる。
範囲選択ツールなどを使って大きさを整えたり、色を変えたりしてみよう。
終わったら、保存。写真ならJPEG､色数の少ない図ならgif。
- 画像の形式の違いは、http://www5e.biglobe.ne.jp/~komichan/webdesign/mame_gazouyoryo.html　を参照のこと。