授業/H20/情報処理/04

テキストファイルの加工とエクセルの使い方 †

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

1. ウェブページ経由で送信されるデータの種類を知り、セキュリティに関心を持つ
2. 正規表現とはどのようなものかを理解し、テキストエディタによる正規表現検索、置換の便利さを実感する
3. テキストエディタとエクセルの間でのデータの共有方法を習得する
4. エクセルによる簡単な表計算方法を習得する

ウェブページ経由で送信されるデータ： †

前回授業の課題では、制限ページのPROJECTページで１人につき必ず１回はコメントを投稿するというのが課題の内容だった。期日までにコメントを投稿しなかった人は4名おり、評点から1点減点してある。

ところで、あのコメント投稿ページには、ニックネームでのコメント投稿も可能だったはずなのに、一体どうやってコメント投稿の有無を判断できたか、わかるだろうか？

答えは、ログファイル

皆さんインターネットを毎日のように使っているが、実は、いつ、どのパソコンを経由して、どのページが閲覧されたかということは、ホームページを公開しているサーバに記録が残ることを知っていただろうか。

アクセスログの例：

172.29.8.17 - 08s40xx [01/May/2008:16:07:33 +0900] "GET 
/joho/skin/pukiwiki.css.php?charset=Shift_JIS&media=print HTTP/1.1" 200 8135 
"http://bean.bio.chiba-u.jp/joho/index.php?cmd=edit&page=joho20%2F08s40xx%2F03"
 "Mozilla/5.0 (Windows; U; Windows NT 5.1; ja; rv:1.8.1.12) Gecko/20080201 Firefox/2.0.0.12"
172.29.8.36 - 08s40xx [01/May/2008:16:07:35 +0900] "POST /joho/index.php HTTP/1.1" 20
0 6126 "http://bean.bio.chiba-u.jp/joho/index.php?joho20%2FPROJECT" "Mozilla/5.0 (Windows
; U; Windows NT 5.1; ja; rv:1.8.1.12) Gecko/20080201 Firefox/2.0.0.12"
172.29.8.36 - 08s40xx [01/May/2008:16:07:35 +0900] "GET /joho/skin/pukiwiki.css.php?cha
rset=Shift_JIS HTTP/1.1" 200 8512 "http://bean.bio.chiba-u.jp/joho/index.php" "Mozilla/5.0 (
Windows; U; Windows NT 5.1; ja; rv:1.8.1.12) Gecko/20080201 Firefox/2.0.0.12"

簡単な解説：

• 最初の数字： 172.29.8.17　　IPアドレス。どのコンピュータからウェブサーバにアクセスがあったか分かる
• 08s40xx: アカウント：　制限ページへのアクセスなので、アカウントのデータが残る
• その他：アクセス日時、行った操作（閲覧しただけか、データを投稿したか）、使ったコンピュータの種類とブラウザの種類

つまり、ログファイルを見れば、皆さんが授業に出席しているかどうかは分かるということ（なので、この授業では出席をとっていない）。それに、ウェブページを閲覧したり、投稿するだけでこれだけの情報はどこかに残ってしまうことは知っておくべきだろう。

では、ログファイルの簡単な解析結果を見てみよう。→PROJECTページ

ページに載っているのは、ログファイルのデータと、発言データを比較して、投稿を行ったのが本人かどうかを示したもの投稿者の実名は示していない）。こういうデータの処理も授業で習う

テキストエディタによる正規表現検索・置換
テキストエディタとエクセルの合わせ技
データベースの利用

を習得すれば、簡単にできるようになる。

テキストファイルとエクセルの間でのデータのやりとり †

前回授業で、テキストファイルをK2Editorで編集して、エクセルの表を作った。みんなできたけど、あれって、実はわりと高度な技。ライセン前を行く千葉大生100人に聞いて、10人知っているかどうか。。。

　　　　　　テキストデータにおけるタブはエクセルのセル間の区切りに対応する

これほど世の中でパソコンが普及していても、大半の人は、テキストファイルを編集したことが無いみたい。だから、タブ区切りテキストとエクセルのセルの関係も知らない人が大勢いる。

今後、皆さんは学習や研究の過程でいろんなデータを扱うことになるが、上のことを理解しておくだけで、

　　　　　　大量のデータの一括処理処理やデータの書式統一がとても楽になる

一つ一つコピー・ペーストしたり、目で探しだすのもいいけれど、テキストエディタによる一括整形でラクしたいものだ。

正規表現と友達になろう †

これまでの授業で何度か正規表現ということばを聞いたはず。正規表現はIT用語辞典によると、

文字列のパターンを表現する表記法。文字列の検索・置換を行なうときに利用される。

検索や置換で文字そのものを検索・置換するのでは無く、パターンを検索・置換するものということだ。（「正規」という言葉に特に意味があるわけでは無いので、気にしなくてもよい）

ここで簡単な例を使って、正規表現ができることを示してみよう。
正規表現による検索・置換はテキストエディタを利用する醍醐味。習得できれば、大学生活中も、社会に出てからも、様々な場面で役立つこと間違いなし！手作業で一つ一つ繰り返さなければならない作業が、検索パターンと置換パターンを考えるという知的作業になるので、この授業で是非とも習得したいものだ。

魔法の呪文：正規表現 †

ここに今、赤（ダイヤ）と黒（クラブ）のトランプが何枚かあるとする。ここから赤だけを抜き出したい。もしこのトランプを渡されたら、一枚一枚目で確かめて、赤だけを抜き出していくはず。10枚程度ならいいけれど、100枚もあったら、けっこう面倒な作業だろう。
ここに示したカードの図は、素材屋「ポケットサイズ」から拝借しました。お礼申し上げます
　　　　　　　　　　
でも、ある魔法の呪文「赤のみ」を使えば、黒のカードを一瞬にして全て消してしまえるとするとどうだろう？呪文を唱えるだけで、渡されたカードから赤のカードのみを取り出すことができる。

まさに、そういう働きをするのが正規表現を用いた検索・置換だ。
　　　　　　　　　　
実際にK2Editorを使ってやってみよう。今、手元に

84A９ＪＡ4９K6６

というテキストデータがある。ここで「半角英数文字」は黒いカードに、 「全角文字」赤いカードに対応すると考えると、先ほど図で示した例と同じになる。このテキストをK2Editorにコピー・ペーストして、正規表現を用いた次のような検索・置換を行ってみよう

検索文字列 [0-9A-Z]+
置換文字列　＜無し＞

置換文字列に＜無し＞と書いてあるときは、何も入力しなくて良い。「正規表現」と「ファイルの先頭から」のチェックボックスをチェックして、実際に置換してみると、

９ＪＡ９６

という全角文字（赤のカードに対応）だけが抜き出されたはずだ。「魔法の呪文」が使えた！もう一つの例についてもやってみよう。

検索文字列 [89８９JKＪＫ]+
置換文字列　＜無し＞

で6以下のカードだけが抜き出せたはずだ。

他の呪文も見てみよう: K2Editorのヘルプファイル †

正規表現には、上に示した以外にも様々な命令がある。K2Editorを立ち上げて、メニューバーからヘルプを表示させ、画面をスクロールして検索・置換に関する説明を見ると、正規表現について様々なことが書いてある。ヘルプにある説明はかなり分かりやすく書かれているのだが、これまで全く正規表現に触れたことの無い人が、この説明を理解するのは不可能だろう。そこで、ヘルプファイルから、重要な点をいくつか抜き出して、例をあげて説明する。

.  ＜梶田注：半角ドット１つ＞　(改行以外の) すべての文字にマッチする
  例: a.d 
  aで始まり、つぎになにか1文字つづき、dで終わる文字列。
 「and」「add」「a漢d」などがマッチする。
*         パターンに0回以上連続でマッチします。
  例: an*d    
  aで始まり0回以上のnの繰り返しを含みdで終わる文字列。
  「and」「andd」などがマッチする。
+       パターンに1回以上連続でマッチします
  例:    an+d    
  aで始まり1回以上のnの繰り返しを含みdで終わる文字列。
  「and」「annd」はマッチするが「advance」「add」などはマッチしない。
\t           タブ	＜＜エクセルとの合わせ技に必須！＞＞
\n           改行        ＜＜データ整形には必須＞＞
\       退避修飾 (エスケープ)  正規表現記号の有効/無効の制御
  \^, \$, \*, \+, \?, \\ のように「^」「$」などメタ文字としての意味を打ち消して
   単なる文字（リテラル）として指定したいときに使用。
   ＜梶田注:日本語キーボードでは半角の\マークです＞

※：上の解説はK2Editorのヘルプファイルとマック版JeditXのヘルプファイルを参考にして作成した。MS Wordのワイルドカード検索も正規表現の文法にかなり近いものを使っている。

質問：　１行に含まれる全ての文字列を表す正規表現は？

　では、ここで、正規表現を使った検索・置換の演習問題をやってみよう。

正規表現を使った検索・置換の練習1 †

• 次の囲みの中のテキストデータを、K2Editorの新しいページにコピー・ペースト

    Humpty Dumpty sat on a wall.
    Humpty Dumpty had a great fall.
    All the king's horses and all the king's men
    Couldn't put Humpty together again. 

• 1. 上のテキスト中、 "pty" で終わる単語を全て"Pumpty"に置き換える。
• 2. 上のテキスト中、 "all" で終わる単語を全て"ball"に置き換える。
• 3. 上のテキスト中、大文字で始まる単語を

正規表現を使った検索・置換の練習2 †

※一緒に操作すること。今回復習課題に出す操作の多くが含まれている。なお、ポイントは
　　　　　　　　テキストデータにおける"タブ"はExcelの"カラム（列）の区切りに相当する

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

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

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

  • ※エクセルできれいな表として貼り付けられなかった場合、一度、K2Editorにペーストしてから、再度コピーし、エクセルにペーストしてみよう。
• 練習：　下の囲みの中のデータは複数の半角空白で区切られたテキスト。コピーしてそのままエクセルにペーストしても、きれいな表にはならない。そこで、正規表現を使って、複数の空白をタブ１つに置換してみよう。

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

  • 問題：複数の半角空白の連続を表す正規表現は何か？置換文字に指定する文字は何か？
• 方法：

1. K2Editorを開いて、新規ページに上のデータをペースト
2. データの並びをじーっと見る

   リンゴ     ミカン       ナシ

3. 下のようなパターンが見えてくる

    リンゴ＜複数の半角空白の連続＞ミカン＜複数の半角空白の連続＞ナシ

   ＜複数の半角空白の連続＞をタブに置き換えればいいということだから、

4. 正規表現検索・置換を使って

   （正規表現チェック）
   検索文字　[ ]+
   置換文字　\t

5. 全体を選択してコピーする。

   　　ここまででK2Editorでの操作は終わり。次はエクセルでの操作　

6. エクセルで新しいページを開き、左上角のセルをクリックして、
7. ワークシートの中の左上のセルをクリックして、ペースト。【テキストファイルのデータをエクセルに移動できた】

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

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

Microsoft Excelの各部の名称 †

値・数式の入力と計算 †

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

セルの参照 †

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

     

     5	8

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

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

   

   5	8
   1	2
   3	4
   5	6
   7	8

絶対参照 †

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

     	個数	比率
     りんご	10	
     みかん	15	
     ばなな	25	
     合計	50	

関数の利用 †

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

• （合計を入力したいところにカーソルを持っていって、ツールバーのΣをクリックしてリターンでもよい）

  　→　

第4回授業の課題 †

• 提出期限：5月12日月曜正午
• http://bean.bio.chiba-u.jp/joho/index.php?joho20に、「自分のID」/04　という新しいページを作成し、下の囲みの中にあるアンケートをコピー・ペーストして、「回答：」の後に答えを書き込むこと。
• 手順
  1. 個人ページのトップページ（上の方のページタイトルに、「joho20/自分のID」のみが書かれているページ）に入り、画面の上の方にある〔 新規 〕をクリック
  2. ページ名を尋ねる入力スペースが表示されるので、半角英数字で、ドット・スラッシュ・0・4を下のように入力

     ./04

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

*第4回授業・基本課題 
**氏名：
**課題への回答
-今日の授業の進み方は？（はやい、丁度いい、おそい）
--回答：
-今日の授業の難しさはどう感じましたか（簡単　丁度いい　難しい）：
--回答：
-難しいと答えた人は、特にどの点が難しかったですか？：
--回答：
-今日の授業は（分かった　半分ぐらいは分かった　分からなかった）：
--回答：
-分からないと答えた人は、特にどの点が分からなかったですか？：
--回答：
-今日の講義で分からなかった用語があったら挙げてください：
--回答：
-課題2解答欄:
--問1への答え:
--問2への答え:
--問3への答え:
--問4への答え:

課題２. 復習課題： 千葉大学の年間収支決算を計算 †

• 下の囲みの中のテキストは、公開されている千葉大学平成18年度決算報告書のPDFから、テキストデータを抜き出したものです。

  国立大学法人千葉大学  
  平成18年度決算報告書  
  （単位：百万円）  
  収入　　　 予算額 決算額
  運営費交付金 17,733 17,733
  施設整備費補助金 2,162 2,174
  船舶建造費補助金 0 0
  施設整備資金貸付金償還時補助金 0 0
  補助金等収入 195 243
  国立大学財務・経営センター施設費交付金 86 86
  自己収入 24,623 26,016
  産学連携等研究収入及び寄附金収入等 2,492 3,208
  長期借入金収入 4,084 4,084
  貸付回収金 0 0
  承継剰余金 0 45
  旧法人承継積立金 0 0
  目的積立金取崩 0 155
  計 51,375 #1
  支出  
  業務費 34,623 35,762
  一般管理費 6,276 5,992
  施設整備費 6,332 6,343
  船舶建造費 0 0
  補助金等 195 238
  産学連携等研究経費及び寄附金事業費等 2,492 3,078
  貸付金 0 0
  長期借入金償還金 1,457 1,428
  国立大学法人財務・経営センター施設費納付金 0 0
  計 51,375 #2
  収入－支出 0 #3
   
  上のデータは作業を簡単にするために、自己収入の内訳と業務費の内訳は省略してあります。
  それぞれ、以下の通りです。
  自己収入内訳
  　授業料、入学料及び検定料収入 8,468 8,433
  　附属病院収入 15,958 17,264
  　財産処分収入 0 0
  　雑収入 197 319
  
  業務費内訳
  　教育研究経費 20,122 19,856
  　診療経費 14,501 15,907

• 問1: 表中、#1に入る金額を答えなさい。
• 問2: 表中、#2に入る金額を答えなさい。
• 問3: 表中、#3に入る金額を答えなさい。
• 問4 自己収入のうち、「授業料、入学料及び検定料収入決算」は、収入決算総額の何パーセントを占めるか答えなさい
• 提出方法：上で作成した「自分のID」/04というページの回答欄にそれぞれの答えを書き込みなさい。