編集中!テキストファイルの加工とエクセルの使い方 †
学生部普遍教育課からのおしらせ †
Flu_annaunce.pdf
一斉配信メールを常に受信できるようにしてください。
新型インフルエンザが発生拡大しています!
千葉大学では今後の対応について、統合メールアドレス宛に一斉配信して情報を提供します。
学生の皆さんは、常にその情報を受信できる環境(携帯への転送設定等)にしておいてください。
携帯電話への転送設定の方法 †
- 統合メールにアクセス: https://cuacmsrv.chiba-u.jp/
- メニューから「オプション」を選択

- 「携帯転送」を選択

- 転送先携帯電話のメールアドレスを入力。

とりあえず、500文字x2ページぐらいにしておけば、おおよその情報は得られるだろう。
第4回授業の獲得目標:
†
- ウェブページ経由で送信されるデータの種類を知り、セキュリティに関心を持つ
- 大量データ一括処理には検索・置換が有効であることを理解する
- 正規表現とはどのようなものかを理解し、テキストエディタによる正規表現検索、置換の便利さを実感する
- テキストエディタとエクセルの間でのデータの共有方法を習得する
- エクセルによる簡単な表計算の方法を習得する
ウェブページ経由で送信されるデータ:
†
前回授業の課題では、制限ページのPROJECTページで1人につき必ず1回はコメントを投稿するというのが課題の内容だった。期日までにコメントを投稿しなかった人は4名おり、評点から1点減点してある(成績閲覧ページで#03_1が0点になっている)。(なお、こういう集計作業も、テキストエディタによる正規表現検索・置換とエクセルの合わせ技でやったものだ。ほんの数分でできてしまう。)
ところで、あのコメント投稿ページには、ニックネームでのコメント投稿も可能だったはずなのに、一体どうやってコメント投稿の有無を判断できたか、わかるだろうか?
答えは、ログファイル
皆さんインターネットを毎日のように使っているが、実は、いつ、どのパソコンを経由して、どのページが閲覧されたかということは、ホームページを公開しているサーバに記録が残ることを知っていただろうか。
今回の課題では、Pukiwikiを使って、ウェブサーバにコメントを送ってもらった。このとき、ウェブサーバのある場所に、
access_log
というファイルができる。このファイルの中身はテキストデータ(今、勉強中の文字データのこと)で、次のような情報が書かれている。
192.29.8.70 - xxxxx [30/Apr/2009:15:41:26 +0900] "POST /joho/index.php HTTP/1.1"
200 18644 http://bean.bio.chiba-u.jp/joho/index.php?joho21%2FXXXXXX "Mozilla/5.0
(Windows; U; Windows NT 5.1; ja; rv:1.9.0.8) Gecko/2009032609 Firefox/3.0.8"
192.29.8.69 - xxxxx [30/Apr/2009:15:42:18 +0900] "POST /joho/index.php HTTP/1.1"
200 6124 http://bean.bio.chiba-u.jp/joho/index.php?joho21%2FXXXXXX "Mozilla/4.0
(compatible; MSIE 6.0; Windows NT 5.1; SV1; InfoPath.1)"
192.29.8.69 - xxxxx [30/Apr/2009:15:43:17 +0900] "POST /joho/index.php HTTP/1.1"
302 - http://bean.bio.chiba-u.jp/joho/index.php?cmd=edit&page=joho21%2FXXXXXX
"Mozilla/4.0 (compatible; MSIE 6.0; Windows NT 5.1; SV1; InfoPath.1)"
192.29.8.70 - xxxxx [30/Apr/2009:15:43:39 +0900] "POST /joho/index.php HTTP/1.1"
302 - http://bean.bio.chiba-u.jp/joho/index.php?cmd=edit&page=joho21%2FXXXXXX
"Mozilla/5.0 (Windows; U; Windows NT 5.1; ja; rv:1.9.0.8) Gecko/2009032609 Firefox/3.0.8"
192.29.8.1 - keito [30/Apr/2009:15:52:38 +0900] "POST /joho/index.php HTTP/1.1"
200 18516 http://bean.bio.chiba-u.jp/joho/index.php?joho21%2FXXXXXX "Mozilla/5.0
(Windows; U; Windows NT 5.1; ja; rv:1.9.0.8) Gecko/2009032609 Firefox/3.0.8"
192.29.8.50 - xxxxx [30/Apr/2009:15:52:42 +0900] "POST /joho/index.php HTTP/1.1"
200 6124 http://bean.bio.chiba-u.jp/joho/index.php?joho21%2FXXXXXX "Mozilla/5.0
(Windows; U; Windows NT 5.1; ja; rv:1.9.0.8) Gecko/2009032609 Firefox/3.0.8"
192.29.8.8 - xxxxx [30/Apr/2009:15:52:45 +0900] "POST /joho/index.php HTTP/1.1"
200 6124 http://bean.bio.chiba-u.jp/joho/index.php?joho21%2FXXXXXX "Mozilla/5.0
(Windows; U; Windows NT 5.1; ja; rv:1.9.0.8) Gecko/2009032609 Firefox/3.0.8"
192.29.8.50 - xxxxx [30/Apr/2009:15:52:47 +0900] "POST /joho/index.php HTTP/1.1"
200 6124 http://bean.bio.chiba-u.jp/joho/index.php?joho21%2FXXXXXX "Mozilla/5.0
(Windows; U; Windows NT 5.1; ja; rv:1.9.0.8) Gecko/2009032609 Firefox/3.0.8"
(IPアドレス等のデータは適当に変更してある)
1つ1つを詳しく見て行くと
- 最初の数字: 192.29.8.69等は IPアドレスという、ネットワークに接続されているコンピュータにつけられた固有の番号。この番号を使うことで、どのコンピュータからウェブサーバにアクセスがあったか分かる。
- 09s40xx: アカウント: 皆さんがページへのアクセスに使ったアカウント
- [30/Apr/2009:15:41:26 +0900] : アクセス日時
- POST: 行った操作。この場合はデータを投稿したことを示している。
- "Mozilla/5.0 (Windows; U; Windows NT: 使ったブラウザやコンピュータの種類
つまり、ログファイルを見れば、いつどのコンピュータから、ウェブサーバにアクセスがあったのか分かってしまうのだ(なので、この授業では出席をとっていない)。もしかすると、ウェブページの閲覧だけなら、誰がどこから見ているか分からないと思っているかもしれないが、
ウェブページを閲覧したり、投稿するだけでこれだけの情報がウェブサーバに残ってしまう
ということは知っておくべきだろう。
大量データ一括処理には検索・置換が有効!!
†
前回授業では、K2Editorというテキストエディタを使って、テキストデータの検索・置換を体験してもらった。「検索・置換」がよく分からなかったという人もいたので、もう一度簡単におさらいしておこう。
- まず、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.
このテキストの中のHumptyが実はスペルミスで、Lumptyだったとする。そこで、全てLumptyに修正したい。
こういうときには、迷わず、検索・置換。
- メニューバーの「検索」から「置換」を選んで、検索ウィンドウを開き、検索語と置換語を入力
検索語: Humpty
置換語: Luympty

(大文字小文字の区別、ファイルの先頭からの横のボックスをチェックすることも忘れずに。正規表現をチェックしてあっても、この場合は影響は無い)
- 「全置換」のボタンをクリックすると、対象としているテキストの中から、「Humpty」が全て検索され、「Lumpty」に置換される。
こんな簡単な操作だが、大切なのは、
非常に大きなデータファイルであっても、検索・置換は一瞬にして終わる
ということだ。もし検索・置換をやらなかったとしたら、
1行ずつ目で検索語を探し、
見つかった検索語を消して、置換語を1つずつペーストする
なんていう面倒なことをしなくてはならない。
大量データの処理に、検索・置換は非常に有効!!であることは理解しておこう。
検索・置換はコンピュータの持つ最も基本的で便利な機能であり、Wordにも、Excelにも標準装備されている。
正規表現と友達になろう
†
上の例では、 HumptyをLumptyに置き換えるという、ごく簡単な操作を行った。では、もっと複雑な処理をするにはどうすれば良いか?たとえば、
テキストを手入力するときに、半角スペースをいくつも連続で入力してしまったようだ。
2つ以上半角スペースが連続しているところは、全て半角スペース1つにしたい。
そんなとき、例えば
1回目
検索語: <半角スペース4つ>
置換語: <半角スペース1つ>
2回目
検索語: <半角スペース3つ>
置換語: <半角スペース1つ>
3回目
検索語: <半角スペース2つ>
置換語: <半角スペース1つ>
と何度も検索・置換を繰り返すのも1つの方法だろう。しかし、もっと便利な方法もある。それが、正規表現を使った検索・置換だ。
正規表現はIT用語辞典によると、
文字列のパターンを表現する表記法。文字列の検索・置換を行なうときに利用される。
検索や置換で文字そのものを検索・置換するのでは無く、パターンを検索・置換するものということだ。(「正規」という言葉に特に意味があるわけでは無いので、気にしなくてもよい)
正規表現による検索・置換はテキストエディタを利用する醍醐味であると言える。
習得できれば、大学生活中も、社会に出てからも、様々な場面で役立つこと間違いない!、なぜなら、
手作業で一つ一つ繰り返さなければならない作業が、検索パターンと置換パターンを考えるという知的作業に一変する
この授業で是非とも習得したいものだ。
魔法の呪文:正規表現
†
ここに今、赤(ダイヤ)と黒(クラブ)のトランプが何枚かあるとする。ここから赤だけを抜き出したい。もしこのトランプを渡されたら、一枚一枚目で確かめて、赤だけを抜き出していくはず。10枚程度ならいいけれど、100枚もあったら、けっこう面倒な作業だろう。
ここに示したカードの図は、素材屋「ポケットサイズ」から拝借しました。お礼申し上げます

もし、魔法の呪文(「赤のみ」)というものがあって、黒のカードを一瞬にして全て消してしまえるならば、赤のカードのみを簡単に取り出すことができるのだから、とても便利だろう。。。。
これをコンピュータで考えてみよう。今、手元に
84A9JA49K66
というテキストデータがある。ここで「半角英数文字」は黒いカードに、 「全角文字」赤いカードに対応すると考えると、先ほど図で示した例と同じになる。「半角英数文字と全角文字が混在するテキストデータから、全角文字だけを抜き出したい」。カーソルを動かしながら、いちいち目で見て判断して、半角文字を1つずつ消していっても良いが、この操作は、正規表現を使えば、一瞬にしてできる。

このテキストをK2Editorにコピー・ペーストして、正規表現を用いた次のような検索・置換を行ってみよう
検索文字列 [0-9A-Z]+
置換文字列 <無し>
置換文字列に<無し>と書いてあるときは、何も入力しなくて良い。「正規表現」と「ファイルの先頭から」のチェックボックスをチェックして、実際に置換してみると、
9JA96
という全角文字(赤のカードに対応)だけが抜き出されたはずだ。「魔法の呪文」が使えた!もう一つの例についてもやってみよう。
検索文字列 [8989JKJK]+
置換文字列 <無し>
で6以下のカードだけが抜き出せたはずだ。(注:検索文字に [8989JQKJQK]+ を指定すれば、Qが入っている文字列にも対応できる)
他の呪文も見てみよう: K2Editorのヘルプファイル †
正規表現には、上に示した以外にも様々な命令がある。K2Editorを立ち上げて、メニューバーからヘルプを表示させ、画面をスクロールして検索・置換に関する説明を見ると、正規表現について様々なことが書いてある。ヘルプにある説明はかなり分かりやすく書かれているのだが、これまで全く正規表現に触れたことの無い人が、この説明を理解するのは不可能だろう。前回の予習課題は、ヘルプファイルに書かれているような言葉の意味を考えて貰ったものだ。そこで、ヘルプファイルから、重要な点をいくつか抜き出して、例をあげて説明する。
注) 例年、「マッチする」という言葉が分かりにくいという質問が出るので、下に正規表現を使った検索語がマッチする場合と、マッチしない場合の例を挙げておく。
正規表現を使った検索語: an+d (意味:aで始まり、nが1回以上、任意の数繰り返し、dで終わる文字列)
上の検索語にマッチする文字列の例: and, annd, annnd, annnnnnnnnnnnnnnnnd
上の検索語にマッチしない文字列の例: ad, aid, acid, answered
- よくつかう正規表現
. <梶田注:半角ドット1つ> (改行以外の) すべての文字にマッチする
例: 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」などはマッチしない。
[...] 文字の集合。〔〕の中では、範囲をあらわす「-」や否定をあらわす「^」も指定できる。
例: [abc]
文字a,b,cのどれかに一致する文字
上に挙げた「+」と組み合わせて使われることが多い
例: [abc]+
「abc」 「abacabcb」 「cccaaabbb」などがマッチ
例: [a-z]+ 半角英文字小文字のみからなる文字列
例: [A-Za-z0-9]+ 半角英数字のみからなる文字列
[の次ぎに^をいれると、「文字集合以外」 をあらわすことができる。
例: [^a-z]+ 半角英数字以外の文字からできている文字列
文字aからz(半角小文字)以外の文字にマッチ。
\t タブ <<エクセルとの合わせ技に必須!>>
\n 改行 <<データ整形には必須>>
\ 退避修飾 (エスケープ) 正規表現記号の有効/無効の制御
\^, \$, \*, \+, \?, \\ のように「^」「$」などメタ文字としての意味を打ち消して
単なる文字(リテラル)として指定したいときに使用。
<梶田注:日本語キーボードでは半角の¥マークです>
※:上の解説はK2Editorのヘルプファイルとマック版JeditXのヘルプファイルを参考にして作成した。MS Wordのワイルドカード検索も正規表現の文法にかなり近いものを使っている。
では、ここで、正規表現を使った検索・置換の演習問題をやってみよう。 †
- 半角英文字の連続文字列で最後がptyで終わる文字列
- 半角英文字の連続文字列で最後がallで終わる文字列
- 1行に含まれる全ての文字列
正規表現を使った検索・置換の練習1 †
- 1. 上のテキスト中、 "pty" で終わる単語を全て"Pumpty"に置き換える。
- 2. 上のテキスト中、 "all" で終わる単語を全て"ball"に置き換える。
- 3. 上のテキスト中、大文字で始まる単語を削除する
- 4. 上のテキスト中、2文字以上連続する半角スペースを1つに置き換える
正規表現を使った検索・置換の練習2
†
※一緒に操作すること。今回復習課題に出す操作の多くが含まれている。なお、ポイントは
テキストデータにおける"タブ"はExcelの"カラム(列)の区切りに相当する
ということ。タブで区切られたテキストデータは、エクセルにコピー・ペーストすることで、カラム(列)で区分されたデータとして扱う事が出来る。
というテキストをコピーして、エクセルにペーストすると、
- K2Editorを開いて、新規ページに上のデータをペースト
- データの並びをじーっと見る
リンゴ ミカン ナシ
- 下のようなパターンが見えてくる
リンゴ<複数の半角空白の連続>ミカン<複数の半角空白の連続>ナシ
<複数の半角空白の連続>をタブに置き換えればいいということだから、
- 正規表現検索・置換を使って
(正規表現チェック)
検索文字 [ ]+
置換文字 \t
- 全体を選択してコピーする。
ここまででK2Editorでの操作は終わり。次はエクセルでの操作
- エクセルで新しいページを開き、左上角のセルをクリックして、
- ワークシートの中の左上のセルをクリックして、ペースト。【テキストファイルのデータをエクセルに移動できた】
テキストファイルとエクセルの間でのデータのやりとり
†
いまやった操作は、テキストデータをK2Editorで編集して、エクセルの表にしたというもの。みんなができていたけれど、実はわりと高度な技。千葉大生100人に聞いて、10人知っているかどうか。。。
これほど世の中でパソコンが普及していても、大半の人は、テキストファイルを編集したことが無いみたい。だから、タブ区切りテキストとエクセルのセルの関係も知らない人が大勢いる。
今後、皆さんは学習や研究の過程でいろんなデータを扱うことになるが、
テキストデータにおけるタブはエクセルのセル間の区切りに対応する
ということを知っているだけで、
大量のデータの一括処理処理やデータの書式統一がとても楽になる ![[bigsmile]](image/face/bigsmile.png)
一つ一つコピー・ペーストしたり、目で探しだすのもいいけれど、テキストエディタによる一括整形でラクしたいものだ。
Excel(エクセル)の操作の基本:表計算
†
今日から2回ほど、表計算ソフトの代表ともいえるExcel(エクセル)の操作を習得する。表計算ソフトは、コンピュータに作らせた表の上に数値を記入して計算させるもの。手計算では、表の中の一つの数値を変更したら他の数値も全てもう一度計算し直さなければならない場合も、表計算ソフトを使うと、他の数値は自動的に再計算される。このアイデアは、ハーバード大学の学生であったDaniel Bricklinが1979年が思いつき、VISICALCというソフトウェアとして実現したものだ。その後、表計算ソフトは、DOS/V機(日本ではPC-98)におけるLOTUS-1-2-3やWindowsにおけるMicrosoft Excelとして発展してきた。表計算ソフトは、ハードウェアの普及を促進したということで、パーソナルコンピュータにおけるキラーソフト(キラーアプリ)と呼ばれている。
Microsoft Excelの各部の名称 †

値・数式の入力と計算 †
- セルを一つクリックして、 =1+2 という式を入力

セルの参照 †
- セルA1に5, セルB1に8を入力
- セルC1をクリックして半角=を入力し、セルA1、せるB1をクリック
- (あるいは、セルC1をクリックして、=A1+B1と入力しても同じ)

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

5 8
1 2
3 4
5 6
7 8
絶対参照 †
- 下の例のようにエクセルに果物の個数を入力して合計を求める
- りんごの個数の全体に対する比率を =B2/B5 で計算。書式をパーセントにする
- りんごの比率の入ったセルをコピーして、他の項目の比率のセルにペーストしてもうまく行かない
- これは、通常のコピー・ペーストでは、セルが相対的な位置関係で参照されるため
- リンゴの比率を計算するときに、分母のセル指定を $B$6 と$記号をつけて参照することで、絶対参照できる
関数の利用 †
- 合計を入力したいセルをクリック(例ではC6)
- =sum( と入力し、マウスで合計したい数値の範囲をドラッグし、最後に ) を入力してリターン
- (合計を入力したいところにカーソルを持っていって、ツールバーのΣをクリックしてリターンでもよい)
→
今つかったsum関数は、エクセルを利用する上で最も頻繁に用いられる関数。使い方をよく覚えておこう。
第4回授業の課題 †
- 提出期限:5月12日火曜正午
- http://bean.bio.chiba-u.jp/joho/index.php?joho21に、「自分のID」/04 という新しいページを作成し、下の囲みの中にあるアンケートをコピー・ペーストして、「回答:」の後に答えを書き込むこと。
- 手順
- 個人ページのトップページ(上の方のページタイトルに、「joho21/自分のID」のみが書かれているページ)に入り、画面の上の方にある〔 新規 〕をクリック
- ページ名を尋ねる入力スペースが表示されるので、半角英数字で、ドット・スラッシュ・0・4を下のように入力
./04
課題1.アンケート調査 †
*第4回授業・基本課題
**氏名:
**課題への回答
-今日の授業の進み方は?(はやい、丁度いい、おそい)
--回答:
-今日の授業の難しさはどう感じましたか(簡単 丁度いい 難しい):
--回答:
-難しいと答えた人は、特にどの点が難しかったですか?:
--回答:
-今日の授業は(分かった 半分ぐらいは分かった 分からなかった):
--回答:
-分からないと答えた人は、特にどの点が分からなかったですか?:
--回答:
-今日の講義で分からなかった用語があったら挙げてください:
--回答:
-課題2解答欄:
--問1の答え:
--問2の答え:
--問3の答え:
--問4の答え:
課題2. 復習課題: 千葉大学の年間収支を計算 †
- 下の囲みの中のテキストは、公開されている千葉大学平成19年度決算報告書のPDFから、テキストデータを抜き出したものです。千葉大学の1年間の予算状況が概観できます。
平成19年度 決算報告書 国立大学法人千葉大学 (単位:百万円)
区分 予算額 決算額
収入
運営費交付金 19,045 19,045
施設整備費補助金 1,642 1,642
船舶建造費補助金 - -
施設整備資金貸付金償還時補助金 - -
補助金等収入 247 450
国立大学財務・経営センター施設費交付金 86 86
自己収入 26,282 27,539
産学連携等研究収入及び寄附金収入等 3,035 4,017
引当金取崩 - 36
長期借入金収入 1,865 1,865
貸付回収金 - -
承継剰余金 - 1
旧法人承継積立金 - -
目的積立取崩 359 814
計 52,561 #1
支出
業務費 37,866 38,174
一般管理費 6,375 6,693
施設整備費 3,594 3,594
船舶建造費 - -
補助金等 247 442
産学連携等研究経費及び寄附金事業費等 3,035 3,615
貸付金 - -
長期借入金償還金 1,444 1,466
国立大学法人財務・経営センター施設費納付金 - -
計 52,561 #2
収入-支出 0 #3
上のデータは作業を簡単にするために、自己収入の内訳と業務費の内訳は省略してあります。
それぞれ、以下の通りです。
自己収入内訳
授業料、入学料及び検定料収入 8,499 8,508
附属病院収入 17,548 18,570
財産処分収入 - -
雑収入 234 461
業務費内訳
教育研究経費 21,646 21,093
診療経費 16,220 17,081