Microsoft Word doc

Transcription

1 ピボットテーブル(Pivot Table)の 使 い 方 データを 表 の 形 で 集 計 分 析 するための 強 力 なツール fact finding から 営 業 日 報 作 りにまで 使 える 汎 用 性 マイクロデータを 扱 う 場 合 Excel のなかでも 特 に 使 える 機 能 0. ピボットテーブルで 扱 うデータの 構 造 0.1 データの 並 びとフィールド 名 ピボットテーブルウィザードは 1 系 統 のデータはワークシートの 列 方 向 に(つまり A 列 は 品 目 B 列 は 売 上 げ 個 数...といった 感 じ) 並 んでいることを 大 前 提 としている これは Excel の 他 の 機 能 と 全 く 同 じである ピボットテーブルに 特 有 の 制 限 は データ 範 囲 の 一 番 上 の 行 ( 先 頭 行 )には データ 系 列 の 名 前 を 表 す 文 字 列 を 入 れる 必 要 があることである この 名 前 を フィールド 名 と 呼 ぶ フィールド 名 1 件 分 のデータ 生 徒 番 号 データの 並 び ピボットテーブルを 作 成 するために 使 うウィザードは フィールド 名 でデータ 系 列 を 管 理 するため 名 前 が 入 っていない 場 合 はそもそもウィザードに 処 理 を 断 られる 複 数 の 系 列 に 同 じ 名 前 が 付 いているのも 当 然 ダメ 長 すぎる 名 前 は 画 面 表 示 上 末 尾 が 切 れてしまうことがある などの 制 限 もあり 名 前 をうまくつける 1 のは 作 業 効 率 を 上 げるためにはかなり 重 要 フィールド 名 1 フィールド 名 2 フィールド 名 3 フィールド 名 4 データ データ データ 1 名 前 を 上 手 く 付 けることが 重 要 なのは 別 にこれに 限 った 話 ではない プログラミングにお ける 関 数 名 もそうだし 新 しい 概 念 にインパクトのある 名 前 をつけることはとても 大 事 1

2 例 題 1. score.xls のデータから 表 頭 に 出 身 校 表 側 に 性 別 を 配 置 して 英 語 の 平 均 点 を 計 算 したクロス 集 計 表 を 作 成 してみる クロス 集 計 表 をつくるためには ピボットテーブルウ ィザードを 利 用 する ピボットテーブルウィザードの 起 動 ピボットテーブルを 作 成 する 場 合 は [データ(D)]-[ピボットテーブルとピボットグラフレポ ート(P)]から ピボットテーブルウィザードを 起 動 する Excel2000 以 降 の 場 合 は あまり 使 ってないサブメニューが 表 示 されなくなっているので 必 要 に 応 じてメニュー 展 開 ボタン( 下 向 きの 三 角 が2つ 重 なったようなアイコン)を 押 すこ と データソースの 指 定 ウィザードを 起 動 すると 最 初 にデータをどこから 持 ってくるかを 指 定 する 画 面 がでてく る 2

3 外 部 データベースから 持 ってくるというのも 実 務 的 には 重 要 なことが 多 いが( 例 えば 売 上 げ 管 理 データベースにアクセスできる 権 限 があれば 直 接 必 要 なデータを 呼 び 出 して Excel で 分 析 できる) データベース 自 体 に 関 する 知 識 が 必 要 なのでこの 授 業 では 扱 わない 初 期 のデータソースは Excel のリスト/データベース となっているので このままそれを 使 う データ 範 囲 の 指 定 Excel のリスト データベースを 選 択 した 場 合 は データが 入 っている 範 囲 を 範 囲 指 定 で 指 定 する 指 定 方 法 はグラフなどのデータ 指 定 と 同 じ ただし フィールド 名 が 先 頭 行 にな るように 指 定 すること ピボットテーブル 作 成 位 置 の 指 定 ピボットテーブルを 新 規 に 作 成 したワークシート 上 に 作 るか 既 存 のワークシート 上 に 作 るかを 指 定 する 既 存 のワークシート 上 に 作 る 場 合 は 左 上 の 位 置 をセル 番 地 で 指 定 する 既 存 のワークシート 上 に 作 る 場 合 は 右 方 向 と 下 方 向 が 空 いている 場 所 を 指 定 すること 時 々フィールドの 指 定 違 いなどで 妙 に 大 きな 集 計 表 ができてしまうことがあるが 右 と 下 が 空 いていればデータを 壊 す 心 配 がない 集 計 用 フィールドの 指 定 データとして 選 択 した 範 囲 に 入 っているフィールドのうち どのフィールドを 集 計 の 表 頭 表 側 データとして 使 うかを 指 定 する 要 するにクロス 表 の 内 容 を 指 定 する 一 番 重 要 な 画 面 である 操 作 方 法 は ドラッグ&ドロップが 基 本 フィールド 名 をドラッグして 目 的 の 部 分 にドロップすれば 指 定 できる 列 のフィールド とは 表 頭 に 使 うフィールド 名 を 3

4 ドロップする 部 分 行 のフォールド とは 表 側 に 使 うフィールド 名 をドロップする 部 分 そして データアイテム とは 集 計 する 時 に 計 算 に 用 いるフィールド 名 をドロップする 部 分 である ページのフィールド は フィールドの 内 容 によって 集 計 表 を 複 数 ページに 分 割 したい 場 合 に 分 割 に 使 うフィールド 名 をドラッグ&ドロップする ドロップすべきフィールド 名 のリストは 下 図 のようなリストとして 提 示 されている こ こからドラッグ&ドロップすればよい 今 回 は 表 頭 が 出 身 校 表 側 が 性 別 集 計 するのは 国 語 の 平 均 点 であるから それぞれ 出 身 校 を 列 のフィールド 性 別 を 行 のフィールド 国 語 を デ ータアイテム にドロップする すると 4

5 合 計 を 計 算 している のような 表 ができる 確 かに 表 頭 と 表 側 は 正 しいようだが 中 のデータはどうみても 平 均 点 には 見 えない よくよく 見 ると 左 肩 のセルに 合 計 / 国 語 と 書 いてある つまり 平 均 ではなく 合 計 点 を 計 算 しているのである 集 計 方 法 の 変 更 この 場 合 欲 しいのは 平 均 点 であるから 合 計 点 のままでは 困 る このように 集 計 方 法 が 希 望 するものと Excel の Wizard が 判 断 したものと 異 なる 場 合 2 上 図 赤 丸 で 囲 んだ 集 計 方 法 の 書 いてあるセルをダブルクリックすることによってメニューが 開 く 可 能 な 集 計 方 法 は 合 計 データの 個 数 平 均 最 大 値 最 小 値 積 数 値 の 個 数 標 本 標 準 偏 差 標 準 偏 差 標 本 分 散 分 散 などである ここでは 平 均 を 計 算 したいので 平 均 を 選 んで OK を 押 すと 今 度 は 正 しく 平 均 / 国 語 になっており データもいかにも 平 均 値 らしいデータになって 2 既 に 皆 さん 予 想 がつくように このケースでも Excel の Wizard は 結 構 間 抜 けな 判 断 をす る あまり 信 じないように 5

6 いる 集 計 内 容 の 変 更 発 見 的 なデータ 処 理 を 行 う 場 合 は いろいろなデータ 間 の 相 関 を 見 たい そのためにはデ ータの 組 み 合 わせを 変 えつつクロス 表 を 山 ほど 作 る 必 要 がある このとき ウィザードを 最 初 から 動 かすのは 冗 長 であるしデータ 範 囲 の 指 定 を 間 違 えたりすると 面 倒 であるから f フィールド 名 の 指 定 にすぐ 戻 れると 便 利 ピボットテーブルで 作 成 したクロス 表 をクリックすると フィールド 名 のリストが 再 表 示 される 集 計 に 不 要 になったフィールドは 集 計 表 からドラッグしてフィールド 名 のリス トにドロップすれば 取 り 除 くことができる データアイテムは 左 肩 のセルをドラッグして フィールド 名 リストにドロップすることで 取 り 除 ける 複 数 の 集 計 を 同 時 に 行 う 同 じ 表 頭 と 表 側 で 複 数 のデータを 同 時 に 集 計 することができる たとえば 平 均 や 標 準 偏 差 のような 特 性 値 だけ 計 算 した 場 合 データの 個 数 が 少 ないと 実 際 問 題 としてあまり 意 味 が ない しかし 表 にデータの 個 数 が 入 っていないとそのことが 直 観 的 に 分 かりづらく 1000 個 データがある 場 合 の 平 均 と 1 件 しかないデータの 平 均 を 同 様 に 扱 ってしまう 危 険 が ある このようなケースでは データアイテム に 2 回 同 じデータをドロップすることによって 平 均 と 件 数 を 同 じ 表 に 入 れることができる は 例 として 複 数 科 目 の 平 均 点 を 同 時 に 計 算 しているが 実 際 のデータ 解 析 のときには 平 均 や 標 準 偏 差 のような 特 性 値 とデータの 個 数 を 同 時 に 計 算 するのが 一 番 役 に 立 つ 個 数 が 少 ない 場 合 は 特 性 値 自 体 が 当 てにならないが 特 性 値 だけ 計 算 した 場 合 直 観 的 にそのことが 分 かりづらくなる のような 集 計 表 があった 場 合 国 語 のデータ 件 数 を 表 に 追 加 するためには フィールド 名 リストから 国 語 をもう 一 度 データアイテム 部 分 すなわち 上 図 の の 範 囲 に ドロップすればよい 上 の 表 に 国 語 をもう 一 度 ドロップすると 6

7 のような 表 ができる このままだと2 回 目 にドロップしたデータが 合 計 / 国 語 になっ ているため 集 計 方 法 を 変 更 する 必 要 がある この 場 合 は 合 計 / 国 語 のセル( 上 図 赤 囲 み)を 右 ボタンクリックして 出 てくるダイアログ から フィールドの 設 定 を 選 ぶことによって の 設 定 ダイアログが 再 度 呼 び 出 せるので これを 用 いて 適 宜 集 計 方 法 を 変 更 すればよい ここでは データの 個 数 を 選 ぶことによって 7

8 のように 平 均 と 個 数 の 両 方 が 入 ったクロス 集 計 表 を 作 成 することができる 練 習 問 題 1. 例 題 と 同 じ score.xls を 用 いて まず 国 語 算 数 理 科 社 会 英 語 の 合 計 点 を 計 算 し 例 題 と 同 じように 出 身 校 および 性 別 ごとの 平 均 点 とデータの 個 数 が 入 ったクロス 集 計 表 を 作 成 しなさい 練 習 問 題 2. Excel のピボットテーブルレポートは 連 続 データを 区 間 に 区 切 ってカテゴリ 化 する 機 能 を 持 っていないため 点 数 のようなデータを 直 接 ピボットテーブルの 表 頭 や 表 側 のフィール ドに 指 定 すると 大 変 なことになる このことを 表 頭 に 英 語 の 得 点 表 側 に 国 語 の 得 点 を 入 れて 人 数 を 求 めるピボットテーブルを 作 って 確 認 しなさい 練 習 問 題 3 連 続 データを 一 定 区 間 に 区 切 ってカテゴリ 化 する 方 法 はいくつか 考 えられる たとえば IF 関 数 を 用 いてカテゴリ 化 する 除 算 と INT 関 数 を 組 み 合 わせて 階 段 状 のデータに 変 換 して カテゴリ 化 するなどの 方 法 がすぐ 思 いつく 練 習 問 題 2 では 素 点 をそのまま 使 ったが 10 点 階 級 のカテゴリデータに 変 換 して 英 語 と 国 語 の 得 点 階 級 ごとの 人 数 を 求 めなさい Tips. 元 データの 確 認 Excel で 作 成 したピボットテーブルは 元 のデータとの 関 係 を 知 っているため 集 計 表 の 特 定 のセルに 入 っているデータが 実 際 にはどんなデータであるかを 表 示 することが 可 能 である たとえば 例 題 1 の 最 終 的 な 結 果 表 で A 校 出 身 の 男 性 のデータを 見 たければ A 校 男 性 の データ( 平 均 でもデータの 個 数 でもよい)をダブルクリックすれば 新 しいワークシートが 挿 入 され そこに 元 のデータが 全 て 表 示 される この 機 能 は 特 異 な 外 れデータが 出 たと きに データの 中 身 をすぐ 確 認 できて 便 利 Tips. 多 重 クロス 集 計 を 行 う 表 頭 あるいは 表 側 で 複 数 のカテゴリを 組 み 合 わせて 集 計 を 行 う 場 合 は 複 数 のフィールド 8

9 名 を 表 頭 あるいは 表 側 部 分 にドロップすればよい このような 操 作 を 行 うと 下 図 のような 多 重 クロス 表 が 作 成 できる ただし あまりやりすぎると 見 づらい 表 ができるので 注 意 単 にデータを 分 割 したいだけで 別 の 表 になっていてもかまわないなら ページのフィー ルド を 使 って 複 数 の 表 に 分 割 した 方 が 見 通 しはよくなる 練 習 問 題 4. pivot.xls の 成 績 データはある 高 校 での 成 績 データである 内 申 平 均 点 は この 高 校 の 学 区 内 にある 3 中 学 校 (A, B, C)での 成 績 ( 相 対 評 価 )を 平 均 したものとなっている 科 目 毎 の 成 績 は 高 校 で 実 施 した 試 験 の 結 果 ( 絶 対 評 価 )を 示 している 4-1 出 身 中 学 ごとに 内 申 平 均 点 の 分 布 を 横 軸 を 内 心 平 均 点 縦 軸 を 人 数 としたグラフに 図 示 しなさい(ヒント ピボットテーブルで 作 成 した 表 を 加 工 する 場 合 は 形 式 を 選 択 して 貼 り 付 け 機 能 で 値 だけ 複 写 すると 扱 いやすい) 科 目 の 平 均 得 点 の 分 布 を 中 学 毎 に 分 けて 同 一 のグラフに 図 示 しなさい ただし 平 均 得 点 分 布 は 1 点 階 級 で 描 くこと 4-3 高 校 で 実 施 した 試 験 結 果 が 正 しく 生 徒 の 学 力 を 評 価 できていると 仮 定 した 場 合 学 区 内 の 3 中 学 の 学 力 水 準 には 差 があると 考 えられるか また 差 があると 考 えられる 場 合 にはど のように 差 があるのかを 示 しなさい 9