このページは編集中です。完成予定は2008年5月15日16:00です。 Excelによるデータ処理

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

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

テキストデータにおける"タブ"はExcelの"カラム(列)の区切りに相当する

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

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

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

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

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

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

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

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

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

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

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

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

操作1:K2Editorでの正規表現置換・検索による整形

  1. プロジェクト相談のページに入り、発言のデータ全てを選択し、コピー
  2. K2Editorを立ち上げ、ペースト
    * 梶田(07s4098) 2007-04-26 (木) 13:35:56 -- 今年度のスポーツ大会の委員を1年生から2名(男女各1)選出してください。
    .....
  3. 上のデータのパターンをじーっと眺める。ほしいデータは「誰が発言したか」と「いつ発言したか」というデータなので、
    名前	学生証番号	日付	時間	発言内容
    がそれぞれエクセルの別のカラムに入るようにしたい。正規表現検索置換で区切り場所にタブを入れれば良いが、その操作は、第4回授業でやったはず。ファイルが保存してあると思うので、エクセルで開いて下さい
    もし、保存し忘れたとか、もう一度操作をして欲しいという要望があれば、説明します。

操作2:エクセルでの整形と集計

  1. フォーマットの乱れをコピー・ペーストで修正する
    • 例えば、下のような例で書式を揃えたい場合
      &ref(): File not found: "Untitled-1.gif" at page "授業/H20/情報処理/04";
    • 移動させたい領域を選択(マウスのドラッグ)し
    • カットし、
      &ref(): File not found: "Untitled-2.gif" at page "授業/H20/情報処理/04";
    • 移動させたい領域の左上のセル一つをクリックして
      &ref(): File not found: "Untitled-3.gif" at page "授業/H20/情報処理/04";
    • ペースト
      &ref(): File not found: "Untitled-4.gif" at page "授業/H20/情報処理/04";
  1. 書式の統一:

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

    書式が統一されていなければならない
    というのが原則なのですが、このデータには
    比良間(07s4088)
    ひらま(07s4088)
    hirama(07s4088)
    hirama
    などというように、同一人物だと思われるのだけど
    ・名前の表記が異なる
    ・学生証番号が全角で書かれているもの、半角で書かれているもの、書かれてないもの
    があります。コンピュータはこういうデータは全て異なるものと解釈して数え上げてしまいます。今回はあとで名簿順に並べ替えることを考えて、名前は無視して、学生章番号だけで集計しようと思います。

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

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

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

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

  1. K2Editorで、名前と学生証番号の間にタブを入れる。次の検索・置換を行う。正規表現でやる。
    検索文字  ¥((半角¥と()
    置換文字  ¥t
  2. 学生証番号の最後の)を削除する
    検索文字  ¥)(半角¥と))
    置換文字   (指定無し)
  3. 全てをコピーしてエクセルに移動。
  4. エクセルで名前の右側に新しい列を一つ挿入し(メニューから「挿入/列」)
  5. シートの一番左上のセル(A1)をクリックして、ペースト
  6. これで一番左の列(A列)に氏名が、次の列(B列)に学生証番号が入った。
    • 学生証番号が抜けていて、すぐに直せるところは、コピーペーストで直しておこう

データの集計

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

  1. 見出し作成: ワークシートの最上部左側の1をクリックして、1行選択。メニューバーの「挿入/行」 で最上部に1行挿入される。
  2. 学籍番号の列の一番上のセルからそれぞれのセルに、見出しを入力
    名前  学生証番号  日付 曜日  時間  発言内容
    • 例:&ref(): File not found: "Untitled-9.gif" at page "授業/H20/情報処理/05";
  3. 次にデータを並べ替える。全てを選択し(左上角の<>をクリック)、メニューから「データ/並べ替え」。並べ替えウィンドウで、範囲の先頭行で「タイトル行」を指定し(ラジオボタンをクリック)、優先されるキーで「学生証番号」を選ぶ。
    • &ref(): File not found: "Untitled-10.gif" at page "授業/H20/情報処理/05";
  1. 並べ替えたのだから、誰が何回発言しているのかをは数えれば分かる。一つ一つ数えるのは面倒なので、自動的に「集計」する。まず、集計したい項目が入った列をえらぶため、最上部のA,B2つの列をマウスのドラッグ操作で選択し、メニューバーの「データ/集計」を選ぶ。
  2. 集計したいのは「学生証番号」が現れる回数(「個数」)なので
    グループの基準: 学生証番号
    集計の方法: データの個数
    集計するフィールド:学生証番号にチェック
    • &ref(): File not found: "Untitled-11.gif" at page "授業/H20/情報処理/05";
      • グループの基準:学籍番号別に発言回数を数えるのだから、グループの基準は学籍番号
      • 発言回数を数えたいのだから、集計の方法はデータの個数
      • 発言回数を数えるのだから、数える対象は、選択した2つの列の中では日付の個数
      1. 学籍番号毎に発言回数が数えられ、下の図のようになるはず

        06052402.gif

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

        06052403.gif

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

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

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

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

      06052404.gif

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

    06052405.gif

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

    06052406.gif

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

    06052407.gif

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

      06052408.gif

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

      06052409.gif

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

プロジェクトについて

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

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

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

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

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

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

ws153

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

Winshot.exe

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

第5回授業の課題

課題1.アンケート調査

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

課題2. 復習

課題3.予習

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