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