( 第 11 回 )2016/06/27 表 計 算 を 利 用 した 集 計 作 業 この 回 では Excel を 用 いた 集 計 作 業 を 行 う 集 計 は 企 業 などで 事 実 を 記 録 したデータを 目 的 に 従 って 整 理 する 作 業 である Excel では 集 計 に 特 化 した 機 能 として テーブル ピポットテーブル という 機 能 がある 1. 課 題 の 確 認 売 上 データを 利 用 して 必 要 な 情 報 を 抽 出 したり 集 計 したりする 利 用 するソフトウェア:Microsoft Excel 2016( 前 バージョンでも 同 等 の 機 能 がある) 1.1. 演 習 の 内 容 あらかじめ 整 理 されたデータではなく ただ 時 系 列 に 事 実 を 記 録 したデータを 用 いる 場 合 集 計 作 業 が 必 要 となる このようなデータとしては アンケートデータ 売 上 データ 家 計 簿 などである これらを 日 々 記 録 して あるタイミングで 記 録 全 体 を 俯 瞰 し 分 析 するために 集 計 作 業 が 行 われる 2. テーブルの 作 成 ダウンロードしたデータには 食 品 を 販 売 している 店 舗 が いつ 誰 が 誰 に 何 を 販 売 したか が 131 件 記 録 されている 既 に 表 形 式 にレイアウトされており 見 出 しも 設 定 されている このデータを Excel の 機 能 テーブル を 用 いて 分 析 する 作 業 を 行 う 2.1. データの 書 式 設 定 ここでは テーブルを 設 定 する 前 に 表 内 のデータ 書 式 を 設 定 する Excel では 数 値 データや 日 付 デー タに 書 式 を 設 定 することで 後 の 計 算 や 集 計 において 書 式 に 応 じた 機 能 が 利 用 できるためである ここでは 日 付 のデータに 短 い 日 付 形 式 売 上 金 額 のデータに 会 計 の 書 式 を 設 定 する ただし Excel では 日 付 と 認 識 できるデータを 入 力 すると 自 動 的 に 日 付 の 書 式 が 設 定 される そのため ダウンロードしたデータには 既 に 日 付 の 書 式 設 定 が 施 されている 例 えば 日 付 の 表 示 形 式 を 2014/6/23 から 2014 年 6 月 23 日 に 変 更 したい 場 合 日 付 のデータを 全 て 選 択 し [ホーム]タブの[ 数 値 ]に あるプルダウンメニューから 長 い 日 付 形 式 をクリックする 日 付 のデータを 選 択 日 付 の 書 式 設 定 を 変 更 すると 日 付 の 表 示 形 式 が 変 更 される 情 リテ 11-1
2016 年 度 情 報 リテラシー 変 更 された 状 態 同 様 に 価 格 のセルを 書 式 設 定 する 場 合 は 金 額 のセルをすべて 選 択 し [ 書 式 ]のプルダウンメニューか ら[ 会 計 ]を 選 択 する すると が 追 加 され 金 額 としての 書 式 が 設 定 される 金 額 に と, が 追 加 [ 会 計 ]を 設 定 2.2. テーブルの 設 定 ここでは 書 式 設 定 された 表 をテーブルとして Excel に 認 識 させる まず テーブルに 設 定 したい 範 囲 を 見 出 しも 含 めてマウスのクリック&ドラッグで 選 択 する( 自 動 で 範 囲 を 認 識 させることが 可 能 な 場 合 も ある) 選 択 したのち [ホーム]タブにある[テーブルとして 書 式 設 定 ]をクリックする するとプルダウンメ ニューが 表 示 されるので 任 意 のテーブルデザインを 選 択 する 1テーブルを 全 選 択 2 任 意 のデザインをクリック テーブルを 全 選 択 し [テーブルとして 書 式 設 定 ] 情 リテ 11-2
( 第 11 回 )2016/06/27 するとダイアログが 表 示 され テーブル 範 囲 の 確 認 が 行 われる 選 択 範 囲 に 問 題 がなく 見 出 しデータの 設 定 も 間 違 いがなければ[OK]ボタンをクリックする 見 出 しが 含 まれていれば [ 先 頭 行 をテ ーブルの 見 出 しとして ]にチェックを 入 れ る 範 囲 を 確 認 する [テーブルとして 書 式 設 定 ]ダイアログ 以 上 の 操 作 で 選 択 した 範 囲 がテーブルとして 認 識 される 見 出 しにプルダウンが 設 定 される テーブル 全 体 にデザインが 施 される テーブル 設 定 の 結 果 2.3. テーブルの 操 作 テーブル 設 定 された 表 は 並 べ 替 え フィルタなどの 操 作 ができるようになる 例 えば 担 当 者 を 川 原 だけにしたい 場 合 は 下 記 のような 操 作 を 行 う 担 当 者 の 見 出 しに 設 定 されているプルダウンをクリックし 表 示 されたメニューから[(すべて 選 択 )]のチェックを 外 す そして[ 川 原 ]のチェックを 入 れて[OK]ボタンをクリックする フィルタを 設 定 すると プルダウンメニューに 漏 斗 のアイコンが 表 示 される フィルタを 解 除 する 場 合 は プルダウンメニューをクリックし [(すべて 選 択 )]にチェックを 入 れる 情 リテ 11-3
2016 年 度 情 報 リテラシー 1プルダウンをクリック 並 べ 替 えもできる 2[(すべて 選 択 )] のチェックを 外 す セルにあるすべての データが 表 示 されて いる 3チェックを 付 ける 漏 斗 のアイコンは フィルタが 設 定 されていることを 示 してい る フィルタの 設 定 テーブルのフィルタは セルの 書 式 設 定 に 基 づいた 設 定 もできるよ うになっている 例 えば 日 付 の 書 式 設 定 が 施 された 箇 所 のフィルタ メニューは 年 月 ごとにフィルタを 設 定 することが 可 能 になっ ている 月 ごとのフィルタが 可 能 になっている 2.4. 列 の 追 加 数 式 の 処 理 テーブルが 設 定 された 範 囲 では 列 を 追 加 すると 自 動 的 にテーブルの 範 囲 の 一 部 として 設 定 される ま た 数 式 をテーブルの 範 囲 内 で 作 成 すると 自 動 的 に 列 全 体 に 数 式 が 作 成 される 例 えば 売 上 金 額 の 見 出 しセルの 右 に 8 万 以 上 を 入 力 する すると 自 動 的 にテーブルの 範 囲 が 拡 張 され 書 式 設 定 が 施 される 自 動 で 書 式 設 定 列 の 追 加 情 リテ 11-4
( 第 11 回 )2016/06/27 次 に 8 万 以 上 に 数 式 を 追 加 する 先 頭 のセルに 下 記 の 数 式 を 入 力 すると 自 動 的 に 列 全 体 に 数 式 が 入 力 される ここで[G3]セルをマウスのクリックで 参 照 した 際 に セル 単 位 ではなく 列 単 位 での 参 照 とな っていることを 確 認 する 列 単 位 の 参 照 になる 自 動 で 複 製 される 数 式 の 設 定 ここで 数 式 の 調 整 を 行 った 場 合 も その 都 度 自 動 的 に 数 式 の 更 新 が 行 われる このように テーブルの 機 能 を 用 いることで データの 形 式 によっては 作 業 を 大 幅 に 効 率 化 できる 3. ピポットテーブル ここでは データを 集 計 してクロス 表 を 作 成 する ピポットテーブル と 呼 ばれる 機 能 を 利 用 する これ を 用 いることで クロス 表 の 要 素 を 適 宜 変 更 しながらデータの 状 態 を 分 析 することができる 3.1. ピポットテーブルの 作 成 ピポットテーブルを 作 成 するには 対 象 となるデータがテーブルとして 設 定 されている 必 要 がある( 今 回 は 既 にテーブルが 作 成 されているが 表 選 択 [ 挿 入 ] [テーブル] [ピポットテーブル]をク リックすることで テーブルの 設 定 を 含 めて 一 度 に 行 うこともできる) まず 作 成 したテーブルの 中 をクリックし カーソルを 配 置 する すると 上 部 メニューに[テーブルツ ール]が 表 示 される ここで[テーブルツール]の[デザイン]をクリックし メニューを 表 示 する そして[ツール]にある [ピポットテーブルで 集 計 ]をクリックする 2[ピポットテーブルで 集 計 ]をクリック 1[デザイン]をクリック [テーブルツール]の[デザイン] すると[ピポットテーブルの 作 成 ]ダイアログが 表 示 される ここでは 集 計 の 対 象 となるテーブルの 範 囲 ( 今 回 は 先 に 作 成 したテーブル)とピポットテーブルを 作 成 する 場 所 を 設 定 する 情 リテ 11-5
2016 年 度 情 報 リテラシー 集 計 の 対 象 を 設 定 テーブルが 対 象 の 場 合 は テ ーブル 名 が 表 示 されている 作 成 する 場 所 を 指 定 する 通 常 は[ 新 規 ]を 選 ぶ [ピポットテーブルの 作 成 ]ダイアログ 設 定 を 確 認 し [OK]ボタンをクリックすると 新 しいシートにピポットテーブルが 作 成 される クロス 表 が 表 示 される 場 所 テーブルの 見 出 し 一 覧 フィールド( 見 出 し)を 追 加 する 場 所 [ピポットテーブル]の 画 面 ピポットテーブルでは テーブルの 見 出 しに 従 って[フィールド]が 表 示 されており 適 宜 必 要 な 要 素 を 選 択 しながら 作 成 することになる 例 えば 担 当 者 ごとの 販 売 回 数 (No.)を 集 計 したい 場 合 [フィールド]から 担 当 者 を[ 行 ] にドラッグし No. を[ 値 ]にドラッグする 仮 に 売 上 金 額 の 集 計 をしたい 場 合 は 売 上 金 額 を [ 値 ]にドラッグする すると 選 択 されたフィールドに 従 ってシート 上 にピポットテーブルが 表 示 される 情 リテ 11-6
( 第 11 回 )2016/06/27 結 果 が 表 示 される No. を[ 値 ]にドラッグ 担 当 者 を[ 行 ]にドラッグ [フィールド]の 追 加 3.2. 集 計 方 法 の 変 更 ここで[ 値 ]にある No. が 数 値 であるために 自 動 で 合 計 と 設 定 されてしまっているので 合 計 /No. をクリックし プルダウンメニューから[ 値 フィールドの 設 定 ]をクリックし 表 示 されたダイア ログで[データの 個 数 ]を 選 択 する メニューから[ 値 フィール ドの 設 定 ]をクリック [データの 個 数 ]を 選 択 し [OK]をクリック 集 計 方 法 の 変 更 以 上 の 設 定 で [ 担 当 者 ]ごとで No. の 個 数 がカウ ントされた 表 が 作 成 される ここで[ 値 ]に 売 上 金 額 が 利 用 された 場 合 [ 合 計 ][ 平 均 ][ 最 大 ]などを 利 用 して 計 算 を 行 う 表 を 作 成 することも 可 能 である フィルタも 利 用 できる [No.]の 個 数 がカウン トされる 情 リテ 11-7
2016 年 度 情 報 リテラシー 3.3. クロス 表 の 作 成 次 に 担 当 者 ごとの 商 品 別 販 売 回 数 をクロス 表 で 集 計 する クロス 表 を 作 成 するには [ 行 ][ 列 ]にク ロス 集 計 したい 要 素 のフィールドを 追 加 する よって[ 列 ]に 商 品 名 のフィールドを 追 加 すればよい クロス 表 が 作 成 される 商 品 名 を[ 列 ]にドラッグ クロス 表 の 作 成 ( 商 品 名 の 追 加 ) 次 に 日 付 を 用 いたクロス 表 を 作 成 する テーブルで 日 付 フィールドには 日 時 を 示 す 書 式 設 定 が 施 されているため 少 し 操 作 が 異 なってくる まず [ 列 ]に 登 録 されている 商 品 名 のプルダウンメニューを 表 示 させ [フィールドの 削 除 ]をク リックし 商 品 名 の 表 示 を 解 除 する 次 に 日 付 を[ 列 ]にドラッグして 担 当 者 と 日 付 のクロス 表 を 作 成 する 日 毎 に 集 計 されてしまう Excel2013 以 前 日 付 を 登 録 した 結 果 Excel2013 以 前 2016 では 自 動 で 月 が 作 成 され 集 計 される 日 付 を 登 録 すると 日 ごとに 集 計 が 行 われてしまうが これを 月 ごとの 表 示 に 切 り 替 える まず ど の 場 所 でも 構 わないので 日 付 の 表 示 を 右 クリックする そして 表 示 されたメニューから[グループ 化 ] をクリックする すると[グループ 化 ]ダイアログが 表 示 されるので [ 単 位 ]を[ 月 ]に 設 定 する 情 リテ 11-8
( 第 11 回 )2016/06/27 1データを 右 クリック 3[ 月 ]を 選 択 2[グループ 化 ]をクリック フィールドのグループ 化 すると 日 付 の 表 示 が 月 ごとにグループ 化 され 月 単 位 での 集 計 が 行 われる 集 計 結 果 また 複 数 の 要 素 を 組 み 合 わせたクロス 表 を 作 成 することもできる 例 えば 商 品 ごとの 月 ごと 販 売 回 数 を 集 計 したい 場 合 は [ 列 ]に 商 品 名 をドラッグし 日 付 の 上 に 商 品 名 が 表 示 されるように 設 定 する(2016 の 場 合 は 月 が 作 成 されているので 月 の 上 にドラッグ) 日 付 のうえに 商 品 名 がある 複 数 の 要 素 によるクロス 表 の 作 成 情 リテ 11-9
2016 年 度 情 報 リテラシー 課 題 講 義 で 作 成 したファイルに 下 記 の 作 業 を 行 え 商 品 の 販 売 記 録 テーブルを 用 いて 新 たにピポットテーブルを 作 成 せよ 講 義 で 作 成 したピポットテーブルは 変 更 しない 作 成 したピポットテーブルにおいて 商 品 ごとの 売 上 金 額 合 計 を 月 別 に 表 示 するクロス 表 を 作 成 せよ さらに 商 品 ごとに 担 当 者 も 表 示 されるように[ 行 ]に 要 素 を 追 加 せよ 完 成 イメージ 上 記 の 作 業 を 施 した 演 習 ファイルを 下 記 の 要 領 で 提 出 すること 提 出 期 限 は 講 義 内 で 指 定 する 必 ず 件 名 に 組 - 学 籍 番 号 情 報 リテラシー 第 11 回 課 題 を 記 載 すること 例 )3 組 学 籍 番 号 p16991 の 場 合 3-991 情 報 リテラシー 第 11 回 課 題 情 リテ 11-10