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

↑

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

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

正規表現・後方参照の復習：　前回課題3の解説: [smile] †

George Washington (1789-1797)
John Adams (1797-1801)
Thomas Jefferson (1801-1809)
James Madison (1809-1817)
James Monroe (1817-1825)
John Quincy Adams (1825-1829)
Andrew Jackson (1829-1837)
Martin Van Buren (1837-1841)
William Henry Harrison (1841)
John Tyler (1841-1845)
James K. Polk (1845-1849)
Zachary Taylor (1849-1850)
Millard Fillmore (1850-1853)
Franklin Pierce (1853-1857)
James Buchanan (1857-1861)
Abraham Lincoln (1861-1865)
Andrew Johnson (1865-1869)
Ulysses S. Grant (1869-1877)
Rutherford B. Hayes (1877-1881)
James A. Garfield (1881)
Chester Arthur (1881-1885)
Grover Cleveland (1885-1889)
Benjamin Harrison (1889-1893)
Grover Cleveland (1893-1897)
William McKinley (1897-1901)
Theodore Roosevelt (1901-1909)
William Howard Taft (1909-1913)
Woodrow Wilson (1913-1921)
Warren G. Harding (1921-1923)
Calvin Coolidge (1923-1929)
Herbert Hoover (1929-1933)
Franklin D. Roosevelt (1933-1945)
Harry S Truman (1945-1953)
Dwight D. Eisenhower (1953-1961)
John F. Kennedy (1961-1963)
Lyndon B. Johnson (1963-1969)
Richard Nixon (1969-1974)
Gerald Ford (1974-1977)
Jimmy Carter (1977-1981)
Ronald Reagan (1981-1989)
George Bush (1989-1993)
Bill Clinton (1993-2001)
George W. Bush (2001-2009)
Barack Obama (2009-present)

という、

ファーストネーム　ミドルネームかイニシャル（あれば）　ラストネーム　在位年

になっているリストを、

ラストネーム　 ファーストネーム　　ミドルネームかイニシャル（あれば）　在位年

に並べ替えるというものだった。Moodleの問題文では、１行毎に空白行が入っているが、それは無視しても構わない。
注目すべき構造と、それに対応する正規表現は、次のようになる

↑

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

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

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

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

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

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

↑

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

↑

Excel（エクセル）の操作の基本：表計算 [smile] †

今日から２回ほど、表計算ソフトの代表ともいえるExcel（エクセル）の操作を習得する。表計算ソフトは、コンピュータに作らせた表の上に数値を記入して計算させるもの。手計算では、表の中の一つの数値を変更したら他の数値も全てもう一度計算し直さなければならない場合も、表計算ソフトを使うと、他の数値は自動的に再計算される。このアイデアは、ハーバード大学の学生であったDaniel Bricklinが1979年が思いつき、VISICALCというソフトウェアとして実現したものだ。その後、表計算ソフトは、DOS/V機（日本ではPC-98）におけるLOTUS-1-2-3やWindowsにおけるMicrosoft Excelとして発展してきた。表計算ソフトは、ハードウェアの普及を促進したということで、パーソナルコンピュータにおけるキラーソフト（キラーアプリ）と呼ばれている。
以下、基本操作の解説を書いておいたが、ほとんどの人は高校の授業で習得済みだと思う。「全く不安の無い人」がいなければ、基本操作の部分は割愛する。

↑

Microsoft Excelの各部の名称 †

excelmeisho.gif

↑

値・数式の入力と計算 †

  1. セルを一つクリックして、 =1+2 という式を入力 sushiki.gif
↑

セルの参照 †

  1. セルA1に5, セルB1に8を入力
  2. セルC1をクリックして半角=を入力し、セルA1、せるB1をクリック
    • （あるいは、セルC1をクリックして、=A1+B1と入力しても同じ）

      sansho.gif

      5	8
↑

式（参照）のコピー・ペースト †

  1. 式を入力したセル（例ではC1）をクリックしてコピー(ctrl+c)：選択されたセルが波線で囲まれる
  2. 同じ法則（例では「左側の２つのセルの値を足すこと」）を適用したいセルをマウスドラッグで選択
  3. ペースト(ctrl+v)すると、選択範囲に全て式がコピーされる

    sanshocopy.gif

    5	8
1	2
3	4
5	6
7	8
↑

絶対参照 †

  1. 下の例のようにエクセルに果物の個数を入力して合計を求める
    • soutaierr1.gif
  2. りんごの個数の全体に対する比率を =B2/B5 で計算。書式をパーセントにする（あるいは、パーセントの計算式を入れる。関数を使っても良い）。
  3. りんごの比率の入ったセルをコピーして、他の項目の比率のセルにペーストしてもうまく行かない
    • soutaierr2.gif
  4. これは、通常のコピー・ペーストでは、セルが相対的な位置関係で参照されるため
  5. リンゴの比率を計算するときに、分母のセル指定を $B$6 と$記号をつけて参照することで、絶対参照できる
    • zettai1.gif　→　zettai2.gif 
      	個数	比率
りんご	10	
みかん	15	
ばなな	25	
合計	50
↑

関数の利用 †

  1. 合計を入力したいセルをクリック（例ではC6）
  2. =sum(　と入力し、マウスで合計したい数値の範囲をドラッグし、最後に ) を入力してリターン
↑

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

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

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

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

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

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

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

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

区画No.　　種名　　出現個体数日付（＜日付については後ほど手入力＞）
1　　　　　　　オオバコ12010/05/20
. . .. . . . . . .. . . . .. . . . .

という形で集計したい。

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

↑

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

  1. データをK2Editorにコピー
    データをじーっとみると、 
    種名＜スペース＞数字
    という並びが見えてくる。どうやらスペースには、半角と全角のスペースが混在しているようだ
  2. K2Editorで正規表現検索・置換で全置換 
    検索文字列： [ 　]+（注：[]の間に半角と全角のスペース1文字ずつ入れる。これでスペースの1文字以上の繰り返しを意味する）
置換文字列：¥t（注：半角￥マークと半角t：タブのこと）
  3. 置換できたもの全てをコピーしてエクセルの新しいシートに貼り付け

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

↑

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

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

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

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

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

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

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

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

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

↑

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

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

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

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

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

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

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

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

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

↑

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

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

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

  1. 全てを選択し（左上角の<>をクリック）
  2. 「データ」リボンから、「小計」
    fig3.JPG
  3. 集計したいのは「種名」の「個体数」の「合計」なので、以下の３項目をチェック。（例では「現在の集計行と置き換える」、「集計行をデータの下に挿入する」をチェックしてあるが、この例ではしてなくても良い） 
    グループの基準：　種名
集計の方法： 合計
集計するフィールド：個体数
    fig4.JPG
  4. 「OK」をクリックすると集計データが表示される。
  5. 集計見出しの２をクリックすれば、集計結果だけが表示される。
    #05_7.gif
    これで、「オランダミミナグサが23個体で最も多い」と分かった。

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

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

データのグラフ化 [smile] †

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

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

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

↑

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

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

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

項目軸名の変更 †

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

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

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

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

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

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

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

#05_13.gif

↑

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

↑

ウィルス対策ソフトの紹介 †

最初の授業で行ったアンケートで、パソコンを持っているのにウィルス対策ソフトを使っていない人が数名。
自分のパソコンがウィルスに感染すると、自分が困るのはもちろんのこと、データを交換した他のコンピュータ（USBメモリやメールなどで）にも大変な迷惑をかけることになる。
ウィルス対策ソフトは必ずインストールしておこう。フリーのソフトでは、Avastが高機能で、ウィルス対策データの更新も頻繁。ただし、１年に１度、ウェブ上で登録更新して（住所やメールアドレスを記入）、ソフトウェアに登録キーを入力する必要がある。
http://www.avast.com/ja-jp/free-antivirus-download

↑

第6回授業の課題 †