2009.12/2010.01 情報科学 A 資料第 11 回 Excel の応用 (2) 担当笹原 千葉 野瀬 ポイント : Excel のデータベース機能を使えるようになろう 何千件 何万件もある大量のデータを処理するためには 専用の データベースソフト を使用する しかし 数十件 ~ 数百件程度のデータならば Excel に備わっているデータベース機能でも十分に実用的に利用できる 名簿の作成 アンケートデータの分析をはじめ Excel のデータベース機能を知っていると重宝するので覚えておくとよい 今日学ぶこと : ソート ( 並べ替え ) の練習 オートフィルタ を使ったデータの抽出( 条件に合ったデータのみを取り出して一覧表示する ) 1. ソート ( 並べ替え ) の練習 Excel は 昇順 降順 というふたつの方法でソートができる 昇順 数字の小さい順 文字ならば abc 順 50 音順にデータを並べ替える方法 降順 数字の大きい順 文字ならば abc 順 50 音順の逆順にデータを並べ替える方法 ( 文字の降順はあまり使わない ) ソートの方法 (1) 並べ替えの基準となる列 ( 基準列 ) を決める (2) ソートする基準列 ( 列の中であればどの部分でもよい ) をクリックする (3) ホーム タブの 並べ替えとフィルタ から並べ替えのボタンをクリックする ( 右図 ) 同じグループは データ タブにもある (4) データが並べ替えられる 用意されている Excel のデータをもとに データのソート ( 並べ替え ) の練習をしてみよう ファイルサーバーのなかに Excel の練習用のファイルが用意してある 場所 :file_server の Kadai の中に Info-Sci フォルダがある このなかに Computer Literacy フォルダがある そのなかに Excel3 というフォルダが存在しているので このフォルダを file_server の [user-id] にコピーすること 麗澤大学 2009 年度情報科学 A 資料 No.11 1
手順 : 授業ホームページ URL: http://www.fl.reitaku-u.ac.jp/~schiba/is/ コピーした Excel3 フォルダのなかの shiken というファイルを開き 以下の練習問題をおこないなさい 次のデータを追加しなさい 学籍番号 :0992601 学科 : チャイ語氏名 : 諏訪光男ふりがな : すわみつお 筆記試験 :62 実技試験 :58 先頭が 0 ではじまる学籍番号のような数字を入力するときには 0 のまえに ( アポストロフィ ) を入力すること 成績を計算しなさい なお 成績の点数は 以下の式で計算すること ( 筆記試験 + 実技試験 ) 2 表の見だし (2009 年度 ) を大きくしたり 表の項目名 ( 学籍番号 学科 氏名 ) などの部分を見やすく調整しなさい 成績のよい順にデータを並べ替えなさい 情報科学 A という科目名 クラス番号 氏名 学籍番号 今日の日付を Excel のシートの適当な場所に記入しなさい 表とグラフを A4 判の用紙 1 枚にまとめて印刷して提出しなさい できあがった表 グラフは s2009 という名前をつけて file_server の [user-id] に保存しておきなさい 複数の列を使った複雑な並べ替え : 並べ替えとフィルタ ユーザー設定の並べ替え を選択 例 : 成績の高い順に並べ替え, 同じ成績ならば学籍番号の小さい順に並べる場合 表中の学籍番号は 0 で始まっている ( 数値ではなく テキスト ( 文字列 ) として扱われている ) ので 以下のような確認メッセージが出る この例の場合は 数値に見えるものはすべて数値として並べ替えを行う を選択する テキスト形式で並べ替える場合, 例えば 11 が 3 よりも前に来てしまうといった不都合が生じるので注意 2. 条件にあったデータを一覧表示する ( オートフィルタを使った抽出 ) Excel の一覧表にあるデータのなかから 特定の条件を満たすものだけを取り出して表にしてみよう このために Excel の フィルタ という機能を使用する 麗澤大学 2009 年度情報科学 A 資料 No.11 2
1. で使った 2009 年成績集計表のデータ (s2009 という名前で保存済み ) のなかから いくつか条件を指定して 条件に該当するデータのみを表示してみよう 条件例 : A. サモサ語学科の学生のデータのみを表示する B. 合格者のみ ( 成績が 60 点以上 ) を表示する C. 不合格者 ( 成績が 60 点未満 ) を表示する D. サモサ語学科とイギリス語学科のデータのみを表示する E. イギリス語学科 以外の 学科の学生のデータのみ表示する 手順 : 1. 一覧表の任意の場所 ( 例えば 成績 と書いてあるセル ) をクリックする 2. データ タブの 並べ替えとフィルタ から フィルタ を選択する ( ホーム タブにもある 右図参照 ) 3. 一覧表の一番上の行が以下のように変化して 三角マークがつく 4. 上の条件例 A( サモサ語学科 ) で データを抽出 ( 条件にあったデータだけを抜き出して一覧表示 ) するには 学科 の マークをクリックする 5. 右図の一覧から サモサ語 だけを選択すると サモサ語学科の学生のデータだけが表示される ( マークの表示がフィルタがかかっていることを示す記号に変わっている ) このやりかたで イギリス語学科だけ チャイ語学科だけ も同じように抽出して表示してみよう 再度全部のデータを表示するには ( すべて選択 ) のチェックを入れればよい 応用 : 条件式を入れて抽出する B. 合格者のみ ( 成績が 60 点以上 ) を表示する という条件の指定方法を知っておこう 0. まず 他のフィルタがかかっておらず 全データが表示されていることを確認しよう 1. 成績 の右の三角マークをクリックする 2. テキストフィルタ の ユーザ設定フィルタ を選択する ( 次ページ図 ) 3. オートフィルタオプション のダイアログボックスが表示される 抽出条件の指定 : で 成績 の欄に 60 という数字を 右の条件のところは 欄の右側の三角マークを押して 条件の一覧を表示し 以上 を選択する ( 次ページ図 ) 4. 合格者 ( 成績 欄が 60 点以上 ) のデータのみが表示される 練習 : 合格点が 50 点以上のとき 不合格者 (50 点未満の学生 ) のみを表示するには どのような指定をすればよいか 麗澤大学 2009 年度情報科学 A 資料 No.11 3
練習問題 : 以下の条件のデータが表示されるように オートフィルタの設定をおこないなさい C. 不合格者 ( 成績が 60 点未満 ) を表示する D. サモサ語学科とイギリス語学科のデータのみを表示する ( OR の設定に注意 ) E. イギリス語学科 以外の 学科の学生のデータのみ表示する F. サモサ語学科の不合格者 ( 成績が 60 点未満の学生 ) のみ表示する オートフィルタの解除 : オートフィルタの機能を使い終わったときは 再度 フィルタ ボタンをクリックする 3. オートフィルタの練習問題 (1) いろいろな形式のデータを Excel に読み込んで分析してみよう Kadai サーバよりコピーした Excel3 フォルダのなかに 練習用の univlist というファイルが用意してある このファイルは Excel のファイルではない 次ページの図のように 1 件のデータが 1 行で並べられ データの項目の区切りがカンマ (,) という形式のテキストファイルである このような形式のファイルのことを CSV 形式 と呼ぶ CSV とは comma separated value の略である Excel 専用のワークシートの拡張子は XLSX (2007 形式 ) ないし XLS (97-2003 形式 ) だが CSV 形式のファイルの拡張子は CSV となる univlist の正確なファイル名は univlist.csv となる このようなファイルを Excel で開き オートフィルタを使って分析してみよう 大学名に 東京 を含む大学の一覧表を作成する 手順 : Excel を起動して Office ボタン 開く を選択する 麗澤大学 2009 年度情報科学 A 資料 No.11 4
ファイルの種類 を テキストファイル にする univlist (univlist.csv) を選択して開く 列幅などを調整して この表を見やすくする オートフィルタを使って 東京 を含む大学名を抽出する 結果に daigaku という名前をつけ Excel 形式 (Microsoft Excel ブック ) で保存する 4. オートフィルタの練習問題 (2) 次のデータを使ってオートフィルタの練習をおこないなさい Kadai サーバよりコピーした Excel3 フォルダのなかに sori という Excel の練習用のファイルが用意されている sori ファイルを開き データをもとに 以下の条件に合致するデータのみを抜き出して一覧表示しなさい 昭和以降 ( 昭和と平成 ) に就任した総理大臣を抜き出しなさい この条件に該当する総理大臣の一覧を, 就任時年齢の若い順番に並べ替えなさい 1 期の在職日数が 1000 日を超える総理大臣を抜き出しなさい この条件に該当する総理大臣の一覧を 1 期の在職日数の長い順番に並べ替えなさい 結果を syusyou という名前で file_server の [user-id] に保存しておきなさい sori ファイルにある現職の鳩山首相の在職日数は, 計算式で自動的に表示してあり, 毎日更新される 計算式を確認して以下に書き出そう 計算式には today() と datevalue() という 2 種類の関数が使われている Excel のヘルプを使ってそれぞれの関数の意味を調べ, 在職日数がどうやって計算されているかを調べよう また, セルの表示形式も確認しよう 麗澤大学 2009 年度情報科学 A 資料 No.11 5
5. 応用データの集計 ( ピボットテーブル ) 時間があまったら挑戦しよう 4. で使った sori ファイルを使って 在職日数の長い順 に総理大臣を並べ替えようとすると, この表のままではうまく集計できない 表をよく見てその理由を考えよう 以下の手順を参考に, 在職日数の長い順に総理大臣を並べ替えるための ピボットテーブル を作成してみよう フィルタ を解除しておく 歴代総理大臣 で降順に並べ替えなおす 表の中のセルを選択しておいて, 挿入 タブの ピボットテーブル を選択する データの場所を Excel のリスト に, 作成するレポートの種類を ピボットテーブル にする 使用するデータの範囲に一覧表全体を指定する 行のラベルに 氏名 フィールドを, 集計する 値 のフィールドに 在職日数 を指定する 同じ氏名の総理大臣の在職日数が合計されて表示される 在職日数の長い順にソートする 結果を syusyou2 という名前で file_server の [user-id] に保存しておきなさい 麗澤大学 2009 年度情報科学 A 資料 No.11 6
集計方法の設定 : 値 の集計方法を 値フィールドの設定 から変更することで, 在職日数の合計だけでなく, 就任回数や一回の就任での平均在職日数を調べることもできる ( 以上 ) 麗澤大学 2009 年度情報科学 A 資料 No.11 7