授業/H19/情報処理/05

Excelによるデータ処理 †

【トピックス】WinShotを用いたスクリーンキャプチャ †

　WinShotはパソコン画面の保存・印刷等を行うことができる、スクリーンキャプチャソフトです。ウェブページからの画像の取り込みや、モニタに表示されている情報をそのまま画像としてコピーしたり、ファイルとして保存できるので、とても便利です。レポートに画面の一部だけを貼り付けたいときなど、役に立つこと間違いありません。下のURLからダウンロードして、インストールして下さい。
http://www.woodybells.com/winshot.html

画面の真ん中より少ししたのところに、

LHA書庫版（別途解凍ソフトが必要です）
WinShot Version 1.53 (712KB)

というリンクがありますので、こちらをダウンロードしてください。

ダウンロードすると、デスクトップに

ws153

というフォルダができます。この中に入っている

Winshot.exe

をクリックすると、タスクトレイにWinShotのアイコンが表示されます。これを右クリックすることで、スクリーンの一部を画像として切り取れます。

授業での手順の解説の仕方について †

　受講生から次のような要望がありました。授業方法の改善につながる、こういう建設的な意見はどんどん出してください。

• 話を聞く速度はちょうどいいのですが、手も同時に動かすというアクロバットは無理です！先生がデモを見せながら話している間に、生徒たちがそれと並行して同じ作業をするので、一時的に先生から注意がそれてしまう。手順通りにできればいいのですが、間違えたとき先生のほうに注意を戻してもすでに話が先に進んでいるのでちんぷんかんぷんです。先生が作業手順を見せてから三分くらい時間をください。具体的には、先生がデモを見せてからいきなり「はい！じゃあ、ここまでできてない人？」なんて言わないで、「それでは今言ったことを踏まえて実際にやってみてください」といった感じでお願いします。

  コメント: 手順を説明して、３分ぐらいやってみる時間をおくという方法は、ときどき試しにやってみるつもりですが、本格的な導入については検討させて下さい。昨年度から、受講生からの要望を取り入れて、１つ１つの操作の解説を２回繰り返すことにしています。一連の方法を全て説明した後、３分待って確認するのでは、もしかするとどこかのステップでついて来られなかったら、そのままおいてけぼりになってしまうかもしれないという心配があります。メインは今まで通り説明を２回繰り返す方法を続け、状況に応じて、３分待って確認確認する方法もやってみようと思います。

  ところで、私からも皆さんにお願いです。

  分からないときは気軽に手をあげて下さい

  手が上がった人数が少なければ、TAの脇田君に任せて解説を続けますが、多い場合は同じ説明を何度も繰り返します。

Excel: 操作のおさらいと発展的な使い方 †

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

前回のおさらい：　テキストデータ→正規表現検索・置換でタブに置き換え→エクセルで解析 †

おさらい１：　タブ区切りテキストとエクセルの関係 †

　前回の課題2では、ウェブページに貼り付けてあるテキストを、エディタで整形して、エクセルに移動して解析しました。ポイントは、

ということです。タブで区切られたテキストデータは、エクセルにコピー・ペーストすることで、カラム（列）で区分されたデータとして扱う事が出来ます。

• をコピーして、エクセルにペーストすると、
• 練習1：　下の囲みの中のデータはタブで区切られたテキストです。コピーして、エクセルにペースト（貼り付け）して見ましょう

  リンゴ	ミカン	ナシ
  スイカ	モモ	ブドウ
  サクランボ	イチゴ	バナナ

おさらい２：　前回の課題2（集計データのテキストをタブ区切りにして、エクセルで集計） †

　前回の課題は、まだ正規表現での検索・置換に慣れていない人には難しかったようです。復習をかねて、ここで一緒に操作を練習してみましょう。

• 練習2：
  1. 第4回授業のページの課題２の囲みの中、

        国立大学法人千葉大学 　から
         　...
        一般管理費 5,645 5,866 221

     までのところをマウスでドラッグして選択し、コピー。

     

  2. K2Editorを立ち上げて、新しいページに貼り付ける
  3. K2Editorの置換ウィンドウを開いて

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

     と指定し、正規表現のところの□とファイルの先頭からという□をチェックして「全て置換」

     

     ↓この操作により、K2Editorに貼り付けたテキストで、半角の空白が全てタブに置換される

     

  4. 置換の終わったテキストを全て選択（マウスで選択するか、ctrl+A）して、コピー
  5. エクセルを立ち上げて、貼り付け
  6. 合計値を求めるべき#1のセルをクリックして、上の方にあるΣ記号をクリックする。これで#1のセルの上に並んでいる数値の合計値が出る。同様の操作を、#2-#4のセルについても繰り返す。

     ~

  7. 差額を求める#5のセルをクリックして、= を入力し、引かれる方の値が入ったセルをクリック、- を入力、引く方

        の値の入ったセルをクリック

     

  8. 問2も同様に計算する。（割り算の場合は / を入力）

     

エクセルによる集計とグラフを用いたデータ解析【p65-83参照】 †

　Excelは数値データを表の上で簡単に集計できる素晴らしい道具です。また、いろんな関数を使って計算したり、あるいは、表の上で集計したデータを、グラフとして視覚的に表現することができます。グラフにすると単なる数字の並びが、なにかを意味しているということに気づくことができたり、また、他の人にわからせることができます。

プロジェクト相談での発言回数の集計とグラフ化 †

　Pukiwikiでのプロジェクト相談では、一人１回は必ず発言（コメントを投稿）するようにお願いしてありました。いろんな人が発言しており、全部で100件ぐらいの発言データが蓄積されています。でも、みんながみんな、同じ回数発言している訳ではなく、発言回数には偏りがある見たいです。このデータを解析して、

・全員が発言しているかどうか
・発言回数には何らかの傾向があるか、

実際のデータを解析してみましょう。

プロジェクト相談のページに書かれているデータは、テキストデータです。解析にはエクセルを使います。もうお分かりですね。最初にやることは先ほどの復習と同じで、

テキストをタブ区切りにして、エクセルで集計

です。操作の流れは、次のようになります。

• 操作１：ウェブページからテキストデータをコピー・ペーストで得て、テキストエディタで正規表現置換によりタブ区切り整形
• 操作２：タブ区切りテキストデータをエクセルに移動して、ワークシートの上で並び替え、集計
• 操作３：集計済みデータをグラフにする

操作１：K2Editorでの正規表現置換・検索による整形 †

1. プロジェクト相談のページに入り、発言のデータ全てを選択し、コピー
2. K2Editorを立ち上げ、ペースト

   * 梶田（07s4098) 2007-04-26 (木) 13:35:56 -- 今年度のスポーツ大会の委員を１年生から２名（男女各１）選出してください。
   .....

3. 上のデータのパターンをじーっと眺める。ほしいデータは「誰が発言したか」と「いつ発言したか」というデータなので、

   名前	学生証番号	日付	時間	発言内容

   がそれぞれエクセルの別のカラムに入るようにしたい。正規表現検索置換で区切り場所にタブを入れれば良いが、その操作は、第4回授業でやったはず。ファイルが保存してあると思うので、エクセルで開いて下さい。

   もし、保存し忘れたとか、もう一度操作をして欲しいという要望があれば、説明します。

操作２：エクセルでの整形と集計 †

• エクセル：データの整形：

  データの整形については、前回やりました。整形というのは、同じ種類のデータは同じカラム（列）に入るように、セルを移動（カット＆ペースト）することでした。

• データをざーっと眺めると、書式が乱れているところがある。原因は
  • 名前を入れずにコメントを投稿したため、カラム（列）が一つ左にずれているところ
  • 新規投稿で New の文字が表示されていたため、カラムが１つ右にずれているところ。

1. フォーマットの乱れをコピー・ペーストで修正する
   • 例えば、下のような例で書式を揃えたい場合
     
   • 移動させたい領域を選択（マウスのドラッグ）し
     
   • カットし、
     
   • 移動させたい領域の左上のセル一つをクリックして
     
   • ペースト
     

• エクセル：　データの集計に必要な整形：半角・全角の変換、名前と学生証番号の区分

  では、いよいよ集計です。もしもエクセルを使わずに集計するとすると、

  クラスの名簿を用意して、一人一人発言しているかどうか、また何回発言しているかを数える

  なんていうことをしなくてはなりません。どれだけ面倒なことか、想像できますよね。
  エクセルを使うと、こういう面倒な数え上げの作業を、「集計」や「並べ替え」で行うことができます。

  いちいち数えるのは面倒！エクセルを使って楽しよう！

1. 書式の統一：

   集計のときに何に注目して集計するか、データを見てみると、困ったことに気がつきます。コンピュータに数えさせる場合は、

   書式が統一されていなければならない

   というのが原則なのですが、このデータには

   比良間(07s4088)
   ひらま(０７ｓ４０８８）
   hirama（０７ｓ４０８８）
   hirama

   などというように、同一人物だと思われるのだけど

   ・名前の表記が異なる
   ・学生証番号が全角で書かれているもの、半角で書かれているもの、書かれてないもの

   があります。コンピュータはこういうデータは全て異なるものと解釈して数え上げてしまいます。今回はあとで名簿順に並べ替えることを考えて、名前は無視して、学生章番号だけで集計しようと思います。

やること1. 学生証番号（英数字と記号）を全て半角文字にする †

1. 名前＋学生証番号の列を選択し（列Aの一番上、Aのところをクリック）、コピー
2. Wordを立ち上げペースト　（注：他のソフトでもできますが、今回はワードを使ってみます）
3. 全てを選択し、メニューから「書式／文字種の変換／半角に変換する」
4. 英数字と記号が全て半角に変換されるので、全てを選択して、コピー、K2Editorの新しい書類にペースト

やること2. 名前と学生証番号を別のカラムに分ける †

名前と学生証番号の間には、半角の(が入っています。この文字をタブに置換すればよいですね。ついでに、最後の)を消しておきましょう。

1. K2Editorで、名前と学生証番号の間にタブを入れる。次の検索・置換を行う。正規表現でやる。

   検索文字  ¥(（半角￥と(）
   置換文字  ¥t

2. 学生証番号の最後の)を削除する

   検索文字  ¥)（半角￥と)）
   置換文字  　（指定無し）

3. 全てをコピーしてエクセルに移動。
4. エクセルで名前の右側に新しい列を一つ挿入し（メニューから「挿入／列」）
5. シートの一番左上のセル(A1)をクリックして、ペースト
6. これで一番左の列（A列）に氏名が、次の列（B列）に学生証番号が入った。
   • 学生証番号が抜けていて、すぐに直せるところは、コピーペーストで直しておこう

データの集計 †

では、エクセルを使って、いよいよ集計作業をします。集計には、データの一番上に見出し行が必要です。

1. 見出し作成：　ワークシートの最上部左側の１をクリックして、1行選択。メニューバーの「挿入／行」　で最上部に１行挿入される。
2. 学籍番号の列の一番上のセルからそれぞれのセルに、見出しを入力

   名前　　学生証番号　　日付　曜日　　時間　　発言内容

   • 例：
3. 次にデータを並べ替える。全てを選択し（左上角の<>をクリック）、メニューから「データ／並べ替え」。並べ替えウィンドウで、範囲の先頭行で「タイトル行」を指定し（ラジオボタンをクリック）、優先されるキーで「学生証番号」を選ぶ。
   • 

1. 並べ替えたのだから、誰が何回発言しているのかをは数えれば分かる。一つ一つ数えるのは面倒なので、自動的に「集計」する。まず、集計したい項目が入った列をえらぶため、最上部のA,B２つの列をマウスのドラッグ操作で選択し、メニューバーの「データ／集計」を選ぶ。
2. 集計したいのは「学生証番号」が現れる回数（「個数」）なので

   グループの基準：　学生証番号
   集計の方法： データの個数
   集計するフィールド：学生証番号にチェック

   • 

     • グループの基準：学籍番号別に発言回数を数えるのだから、グループの基準は学籍番号
     • 発言回数を数えたいのだから、集計の方法はデータの個数
     • 発言回数を数えるのだから、数える対象は、選択した２つの列の中では日付の個数

     1. 学籍番号毎に発言回数が数えられ、下の図のようになるはず

        

     2. 集計見出しの２をクリックすれば、集計結果だけが表示される。

        

集計データのグラフ化【復習課題操作】 †

1. どういうグラフを作るか考える。ここでは、学籍番号を横軸に、発言回数を縦軸にした棒グラフをつくれば、発言回数の違いがあきらかになるだろう、と考える。このとき、ワークシートを見てみると、一番左のセルには学籍番号自体では無く、「学籍番号＋データの個数」となっている。これを学籍番号だけに変えたい。どうすれば良いか？

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

   エクセルには'関数'という便利な機能がある。ここでは、「セルに入っている文字列の一部だけを抜き脱す」関数：LEFT()を利用する

   1. 関数を使う練習もかねて、新しい学籍番号だけの列をもう一つ作ることにする。
   2. 最上段の「時間」というセルの右のセルに「学籍番号」と入力する。
   3. このセルの下には、学籍番号だけを入れたい。そのためには、左端の「z6s4xxxデータ個数」という文字列から、左から数えて７文字だけ持ってくれば良い。ある文字から部分的に文字を切り出す関数があるので、それを使う。「学籍番号」と入力したセルの１つしたにカーソルを移動させ、次のように入力

      =LEFT(A3,7)

      

      • あるいは、メニューバーの「挿入／関数」から文字列関数のLEFTを選び、必要項目をクリック
   4. このセルをコピーし、下の集計データの空いているセルにペーストする
2. ではいよいよグラフを作る。使いたいデータは集計表のうち、いま作った「学籍番号」と、その人が何回発言しているかが書かれた「日付」というセル
3. グラフを書きたい場所（どこか右の方の空白セル）をクリックしてから、メニューバーの「挿入／グラフ」を選ぶ。グラフの種類を選ぶウィンドウが表示されるので、ここでは、「横棒グラフ」（上から２つめ）をクリックし、グラフの形式はなにも変えずに（つまり、一番左上に書かれている形式）、「次へ」
4. 「学籍番号」ごとの発言回数をグラフにしたいので、グラフに入れたい学籍番号の範囲をドラッグして範囲指定。まだ「次へ」はクリックしちゃダメ。

   

5. グラフ指定ウィザード(2/4)のウィンドウで、上の方にある「系列」タブをクリック
6. 系列1の値のところに、集計した発言回数をいれたい。マウスで発言回数のセルをドラッグして、範囲指定。うまくできれば、プレビューが表示されるので、「次へ」

   

7. 次の画面でグラフタイトルやX, Y軸の説明を入力し、「完了」

   

   1. できあがったグラフを見てみると、学籍番号がとびとびにしか表示されていない。これは、軸ラベルの増分が1になっていないため。そこで、グラフ画面上の学籍番号のところをダブルクリックして、軸の書式設定ウィンドウを表示させ、増分を１にしてOK。

      

   2. 学籍番号が表示された。でも文字がつまっていて見にくいので、もう一度軸の書式設定ウィンドウを開いて、フォントサイズを9ポイントぐらいに変更。そうすると、なぜか学籍番号がついていないラベルがある。こんなときは、棒グラフの上をクリックすると、グラフを作成するのに使ったセルの範囲が線で囲まれて表示される。

      

   3. この例では、学籍番号の指定が一つずれていた。そこで、セルの範囲指定を、線をドラッグして変更すれば、正常に表示される。

プロジェクトについて †

• 進め方相談

第5回授業の課題 †

• 提出期限：5月21日月曜正午　（下記３つ全て）

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

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

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

     ./05

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

     *第5回授業・基本課題 
     **氏名：
     **課題への回答
     -今日の授業の進み方は？（はやい、丁度いい、おそい）
     --回答：
     -今日の授業の難しさはどう感じましたか（簡単すぎ　簡単　丁度いい　難しい　難しすぎ）：
     --回答：
     -難しいと答えた人は、特にどの点が難しかったですか？：
     --回答：
     -今日の授業は（よく分かった　分かった　分からなかった）：
     --回答：
     -分からないと答えた人は、特にどの点が分からなかったですか？：
     --回答：
     -今日の講義で理解できなかった用語があったら挙げてください：
     --回答：
     -次週以降の講義では、1. Word, 2. Power Point, 3. データベース, 4. 統計解析ソフト, 5. プログラミング演習　
     を行う予定です。これまでに学んできた内容の復習もやりながら進めるので、優先順位をつけたいと思います。
     上の番号を興味のある順番に並べてください。また、優先順位についてコメントがあれば記入してください。
     （回答例： 3 5 4 1 2）
     --回答：

課題２. 復習 †

• 授業で作成したデータファイルを使って、別形式のグラフを作成して下さい
• 上で作った「自分のID」/05というページに添付しておいてください。ファイル名は「学籍番号_05.xls」とします。
• 評価基準：
  • グラフの見やすさ
  • エクセルの使用法について、どれだけチャレンジしているか
• 復習内容
  • Excelでのグラフ作成

課題３．予習 †

• 上で作成したグラフのスクリーンショットをWinShotで切り取ってJPEG形式のファイルとして保存し、「自分のID」/05に添付しなさい。

  添付の方法：
   ・画面上部のメニューから「添付」をクリック
   ・「選択」ボタンをクリックして、添付すべきファイルを探す
   ・アップロード

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

• 下のデータは、土谷先生の授業でやった葉の厚さの計測データです。
• 土谷先生によると、グラフを作るときには、

  1. デフォルトでは「系列1」の文字はグラフの右側に書かれるが、左側にもってくること
  2. 数値の桁揃えを注意すること
  3. 余分なグリッド線は消すこと

  とのことでした。英語サイトですが、グラフの作り方については、Almost Everything You Wanted to Know About Making Tables and Figuresというページが参考になります。
• 葉の厚さの計測データ：タブ区切り形式

  観測者	常緑・落葉	種名	葉の厚さ	寿命
  E	常	アオキ	0.17	6.00
  F	常	アオキ	0.17	3.00
  E	常	キャラボク	0.50	3.00
  F	常	キャラボク	0.50	3.00
  A	常	キンモクセイ	0.23	2.00
  B	常	キンモクセイ	0.30	3.00
  C	常	キンモクセイ	0.33	5.00
  D	常	キンモクセイ	0.30	2.00
  A	常	クスノキ	0.20	1.00
  B	常	クスノキ	0.30	1.00
  C	常	クスノキ	0.33	1.00
  D	常	クスノキ	0.30	1.00
  E	常	サザンカ	0.20	3.00
  F	常	サザンカ	0.20	3.00
  C	常	サンゴジュ	0.35	4.00
  E	常	サンゴジュ	0.17	5.00
  F	常	サンゴジュ	0.17	5.00
  A	常	シラカシ	0.20	1.00
  A	常	タイサンボク	0.30	1.20
  B	常	タイサンボク	0.33	5.00
  C	常	タイサンボク	0.33	5.00
  E	常	ツバキ	0.17	3.00
  F	常	ツバキ	0.17	3.00
  C	常	トウネズミモチ	0.33	3.00
  D	常	トウネズミモチ	0.30	3.00
  A	常	ビワ	0.25	1.00
  A	常	マテバジイ	0.50	3.10
  B	常	マテバジイ	0.25	3.00
  C	常	マテバジイ	0.25	3.00
  D	常	マテバジイ	0.25	3.00
  B	常	モチノキ	0.20	3.00
  C	常	モチノキ	0.20	4.00
  D	常	モチノキ	0.20	3.00
  E	常	モチノキ	0.25	3.00
  F	常	モチノキ	0.25	3.00
  B	常	ヤマモモ	0.30	4.00
  C	常	ヤマモモ	0.33	3.00
  D	常	ヤマモモ	0.30	4.00
  A	落	イチョウ	0.25	1.00
  B	落	イチョウ	0.17	1.00
  C	落	イチョウ	0.17	1.00
  E	落	イチョウ	0.09	1.00
  F	落	イチョウ	0.09	1.00
  A	落	ケヤキ	0.20	1.00
  B	落	ケヤキ	0.15	1.00
  C	落	ケヤキ	0.15	1.00
  D	落	ケヤキ	0.15	1.00
  B	落	サクラ	0.20	1.00
  C	落	サクラ	0.20	1.00
  D	落	サクラ	0.20	1.00
  E	落	サクラ	0.13	1.00
  F	落	サクラ	0.14	1.00
  E	落	サルスベリ	0.14	1.00
  F	落	サルスベリ	0.13	1.00
  B	落	トウカエデ	0.15	1.00
  C	落	トウカエデ	0.15	1.00
  D	落	トウカエデ	0.15	1.00
  C	落	ハクモクレン	0.11	1.00
  A	落	ムクゲ	0.20	1.00