untitled

流 通 科 学 大 学 高 等 教 育 研 究 センター 紀 要 第 9 号,85-100 (2012) アンケート 分 析 を 例 とした Microsoft Excel の ピボットテーブルの 基 本 的 な 使 用 方 法 The fundamental directions for the pivot table of Microsoft Excel which made questionnaire analysis the example. 近 藤 敦 飯 田 真 伍 Atsushi Kondo, Shingo Iida 卒 業 生 満 足 度 調 査 のデータを 使 用 し Microsoft Excel のピボットテーブルの 基 礎 的 機 能 についての 利 用 方 法 を 説 明 した キーワード:Microsoft Excel 2007 ピボットテーブル 集 計 分 析 Ⅰ.はじめに 業 務 改 善 や 授 業 改 善 などを 目 的 として 多 数 実 施 されている 学 生 アンケー トの 集 計 や 分 析 において Microsoft Excel ( 以 降 エクセル)は 有 用 といえる 特 に 簡 単 に データ 集 計 抽 出 を 行 ためのピボットテーブル 機 能 は 有 用 と 思 われる ピボットテーブルの 使 用 方 法 については Internet 上 にも いろいろな 例 が 出 ており 書 籍 としても 発 売 されている それらの 例 は 実 用 的 な 顧 客 管 理 や 商 品 集 計 売 上 集 計 等 から 月 / 年 単 位 等 の 集 計 分 析 結 果 を 求 めるものが 多 く 著 者 らの 業 務 で 活 用 するための 使 用 方 法 の 練 習 課 題 とし ては 敷 居 が 高 いのではないかと 考 えられる このため 著 者 らも 普 段 の 業 務 ではピボットテーブル 機 能 を 使 うことはなく 難 しいことは 避 けて 自 分 のわかる 範 囲 の 関 数 を 使 ってデータ 加 工 をする 程 度 である ピボットテ ーブル 機 能 の 存 在 は 知 っていても 有 効 活 用 には 至 っていないのが 現 状 であ る 一 方 別 稿 の 卒 業 生 大 学 生 活 満 足 度 調 査 のデータ 処 理 をする 際 に ピ ボットテーブルでクロス 集 計 しようか の 一 言 から 5 分 程 度 のレクチャー を 受 け 同 稿 の 表 はピボットテーブルにより 数 日 で 作 成 した その 結 果, 流 通 科 学 大 学 高 等 教 育 研 究 センター 事 務 室 651-2188 神 戸 市 西 区 学 園 西 町 3-1 同 上 (2011 年 2 月 1 日 受 理 ) 2012Center for the Studies of Higher Education

86 近 藤 敦 飯 田 真 伍 ピボットテーブルの 高 度 な 機 能 を 利 用 せずとも 基 本 機 能 のみでも, 十 分 な データ 処 理 および 分 析 結 果 を 効 率 的 に 導 出 できることが 体 験 的 に 明 らかと なった そこで 本 稿 では 今 後 の 業 務 における 学 生 アンケートなどの 集 計 や 分 析 の 効 率 化 や 高 度 化 を 図 るため ピボットテーブルが 簡 単 に 使 えるものだ ということを 理 解 することを 主 眼 におき アンケートデータのサンプルを 使 い ピボットテーブルの 基 本 的 な 使 用 方 法 を 整 理 する ここでは 別 稿 の 卒 業 生 大 学 生 活 満 足 度 調 査 の 実 際 のデータ 処 理 を 通 して 得 られた 利 用 法 を 中 心 に 紹 介 する なお 本 稿 で 利 用 したエクセルは Microsoft Excel 2007 である 他 のバ ージョンでは 画 面 表 示 や 操 作 方 法 に 違 いが 見 られる 場 合 があるものの 基 本 機 能 はほぼ 同 様 である Ⅱ.ピボットテーブルとは ピボットテーブルの 利 用 は 非 常 に 簡 単 でありながら 非 常 に 強 力 な 集 計 分 析 ツールである pivot を 辞 書 で 調 べると 中 枢 枢 軸 要 中 軸 軸 中 心 という 意 味 があるようである pivot table は ある 表 を 中 心 ( 軸 )に 集 計 分 析 する というところだろうか ピボットテーブルを 使 う と 聞 いた( 見 た) 瞬 間 に 難 しそう また 暇 なときに と 思 うところだが 関 数 を 利 用 してデータを 加 工 する ことを 考 えると こんな 簡 単 なことで これだけの 集 計 結 果 が 抽 出 できる のかと 驚 くはずである 式 の 入 力 は 必 要 ない ドラッグ&ドロップにより 集 計 分 析 作 業 を 進 めることが 可 能 である Ⅲ.ピボットテーブルの 利 用 方 法 1.サンプルデータ 卒 業 生 満 足 度 調 査 では 2005 年 ~2010 年 までのデータが 3316 行 119 列 の 表 にまとめられている この 3316x119 の 表 をオリジナル データと 呼 ぶことにする オリジナル データより 調 査 実 施 年 度 学 生 生 活 の 満 足 度 趣 味 や スポーツ 等 によって 生 活 を 楽 しむ 力 が 身 についたか の 3 問 (3316x3)につい て 集 計 作 業 を 行 なう

アンケート 分 析 を 例 とした Microsoft Excel のピボットテーブルの 基 本 的 な 使 用 方 法 87 1 行 目 は データ 項 目 2 行 目 以 降 3317 行 は 各 項 目 のデータが 入 力 さ れている ( 図 1) 図 1.オリジナル データ ここで 使 用 するデータは オリジナルデータから 表 1の 内 容 について 作 業 を 行 なう 表 1.サンプルとして 使 用 するデータ 設 問 項 目 選 択 分 類 B-1 対 象 卒 業 年 次 ( 調 査 実 施 年 度 ) 調 査 実 施 年 度 を 数 値 記 入 基 本 データ 学 生 生 活 を 振 返 って あなたの 1 大 変 満 足 2 満 足 ア 満 足 度 はどうですか? 3どちらともいえない 満 足 度 ( 学 生 生 活 の 満 足 度 ) 4 不 満 5 大 変 不 満 カ あなたの 卒 業 後 の 進 路 につい て お 教 えください 1 就 職 2 進 学 3その 他 取 り 組 み Q-1 大 学 生 活 で 趣 味 やスポーツ 等 によって 生 活 を 楽 しむ 力 がつ いた と 実 感 できますか? ( 生 活 を 楽 しむ 力 ) 1とても 実 感 できる 2 実 感 できる 3どちらともいえない 4 実 感 できない 5 全 く 実 感 できない 身 につく 力

88 近 藤 敦 飯 田 真 伍 2.ピボットテーブルの 準 備 ピボットテープルの 対 象 データの 範 囲 を 指 定 する 学 生 生 活 の 満 足 度 と 生 活 を 楽 しむ 力 についての 集 計 をするために オリジナル データから 必 要 部 分 を 抜 き 取 り 別 の 表 としてまとめる 作 業 は 必 要 ない 逆 に 抜 き 取 ら ないことにより ピボットテーブルの 本 領 が 発 揮 されると 言 える 次 の1~4により 準 備 作 業 を 行 なう 1オリジナル データ 全 てをマウスにより 選 択 する(3316 行 x119 列 を 反 転 表 示 させる) 2メニュー 挿 入 タブより ピボットテーブルをリボン メニューを 選 択 (クリック)する ( 図 2) 図 2.ピボットテーブルの 選 択 3 ピボットテーブルの 作 成 ウィンドウが 開 く 図 3のように 既 に テーブル/ 範 囲 には オリジナル データの 範 囲 が 入 力 された 状 態 と なる OK ボタンにて 新 規 ワークシートにピボットテーブルの 雛 形 が 作 成 されます

アンケート 分 析 を 例 とした Microsoft Excel のピボットテーブルの 基 本 的 な 使 用 方 法 89 図 3. ピボットテーブルの 作 成 ウィンドウ 4ピボットテーブルが 作 成 され 準 備 完 了 ( 図 4) 図 4.ピボットテーブルの 作 成 3.ピボットテーブル 作 成 時 の 基 本 事 項 空 ピボットテーブルの 作 成 ができたところで 基 本 的 な 定 義 をここで 行 なう a.ピボットテーブルのフィールドリスト 図 4で ピボットテーブルのフィールドリスト ウィンドウが 開 いてい る このウィンドウの 中 には レポートに 追 加 するフィールドを 選 択 して

90 近 藤 敦 飯 田 真 伍 ください レポートフィルタ 列 ラベル 行 ラベル Σ 値 の 4 項 目 が 白 背 景 で 表 示 されている それぞれ レポートに 追 加 するフィールドを 選 択 してください は フ ィールドリスト レポートフィルタ は レポートフィルタ 列 ラベ ル は 列 ラベル 行 ラベル は 行 ラベル Σ 値 は Σ 値 と 表 記 した 場 合 は その 欄 に 対 しての 動 作 を 示 すこととする b.ピボットテーブルのコピー ピボットテーブルはコピーすることができる セル A3~ C20 をマウスに てドラッグし 反 転 表 紙 させ コピー&ペーストにて A30 にコピーを 行 な うと オリジナル データを 選 択 した 空 ピボットテーブルが A30~ C47 にコ ピーされる 範 囲 指 定 を 間 違 えると ピボットテーブルはコピーされないので 注 意 が 必 要 である 同 じオリジナル データに 対 して 複 数 のピボットテーブルを 作 成 する 場 合 は 基 本 構 成 ピボットテーブルを 作 成 し コピー&ペーストにて 再 利 用 すると 準 備 作 業 の 時 間 短 縮 をはかることができる 本 稿 では 一 つの 例 を 扱 うので コピーは 行 なわず 集 計 作 業 に 入 る c.ピボットテーブルの 編 集 ピボットテーブル 内 のいずれかのセル( 例 : 図 4 のセル A3~ C20)をク リックすると ピボットテーブルのフィールドリスト ウィンドウが 開 き 編 集 可 能 な 状 態 となる 図 4の 例 で A3~ C20 以 外 のセルをクリックする と ピボットテーブルのフィールドリスト ウィンドウは 表 示 されない d. 列 幅 の 固 定 ピボットテーブルの 編 集 を 始 めると 項 目 の 入 れ 替 え 等 を 行 なうたびに セルの 列 幅 が 自 動 調 整 され 思 った 以 上 に 煩 わしいので 自 動 調 整 機 能 は オフにする ピボットテーブルを 右 クリックし メニューから ピボットテーブル オ プション を 選 択 する( 図 5)

アンケート 分 析 を 例 とした Microsoft Excel のピボットテーブルの 基 本 的 な 使 用 方 法 91 図 5.ピボットテーブル オプション 下 から 2 番 目 の 更 新 時 に 列 幅 を 自 動 生 成 する のチェックをオフにす る e. 空 白 セルの 表 示 方 法 フィルタ 等 の 設 定 や 抽 出 条 件 により データが 存 在 しないことが 発 生 する その 場 合 は 特 に 設 定 をしていなければ 空 白 セルとしてひょうじ される 空 白 ではなく 例 えば 0 を 表 示 させたいという 場 合 には 図 5 の 空 白 セルに 表 示 する 値 の 欄 に 0 を 入 力 することで 空 白 セルには 0 を 表 示 させることができる Ⅳ.ピボットテーブルを 使 った 集 計 例 1. 学 生 生 活 の 満 足 度 の 経 年 変 化 について ア: 学 生 生 活 を 振 返 って あなたの 満 足 度 はどうでしたか? という 設 問 の 経 年 変 化 の 表 とグラフを 作 成 する 1ピボットテーブルを 編 集 するため ピボットテーブル 内 のセルをクリ ックし ピボットテーブルのフィールドリスト を 表 示 する 2 フィールドリスト より B-1: 対 象 卒 業 年 次 をドラッグ&ドロッ プにて 行 ラベル へ 移 動 する 実 際 には 移 動 されず 行 頭 にチェッ

92 近 藤 敦 飯 田 真 伍 クマークが 付 く ( 図 6) 図 6. 卒 業 年 度 を 行 ラベル へドラッグ&ドロップ ピボットテーブルは 図 7のように 変 化 している 図 7. 行 ラベル だけを 指 定 したピボットテーブル 3 続 いて フィールドリスト より ア: 学 生 生 活 を 振 返 って あなた の 満 足 度 はどうですか? を 列 ラベル へドラッグ&ドロップする ( 図 8)

アンケート 分 析 を 例 とした Microsoft Excel のピボットテーブルの 基 本 的 な 使 用 方 法 93 図 8. 行 と 列 に 表 示 したい 項 目 を 選 択 セル B4 の 01: 大 変 満 足 については マウスでクリックすること で 自 由 に 編 集 が 行 なえる オリジナルデータへの 影 響 はない ここ では 数 字 部 分 を 削 除 する セル A4,B3 についても 同 様 に 修 正 は 行 な える 4 集 計 結 果 の 表 示 させるために フィールドリスト より ア: 学 生 生 活 を 振 返 って あなたの 満 足 度 はどうですか? を Σ 値 へドラ ッグ&ドロップする 5 各 年 度 における 学 生 生 活 の 満 足 度 の 集 計 表 が 完 成 する( 図 9) 図 9. 各 年 度 における 学 生 生 活 の 満 足 度 2005 年 度 には ア: 学 生 生 活 を 振 返 って あなたの 満 足 度 はどうですか?

94 近 藤 敦 飯 田 真 伍 の 設 問 の 回 答 者 は 604 名 であり 内 訳 は 大 変 満 足 は 106 名 満 足 は 340 名 どちらともいえない は 124 名 不 満 は 29 名 大 変 不 満 は 4 名 未 回 答 者 1 名 の 計 604 名 である データは 2005 年 度 ~2010 年 度 まであるので 経 年 変 化 についての 分 析 を 行 なうことができる 図 9の 表 を 作 成 するために エクセルの 式 は 一 切 使 用 していない 元 と なるオリジナル データを 選 択 し ピボットテーブルにて 行 と 列 結 果 とな る 情 報 をドラッグ&ドロップしただけである 2. 学 生 生 活 の 満 足 度 の 経 年 変 化 のグラフ 化 について a. 抽 出 した 表 をグラフ 化 する ピボットテーブルの 任 意 のセルをクリックし 通 常 の 表 からグラフを 作 成 することと 同 じ 手 順 でグラフを 作 成 することができる ここでは 通 常 の 手 順 なので 詳 細 は 割 愛 する 図 10は 積 上 げ 縦 棒 グラフでの 表 示 としてい る 図 10. 積 上 げ 縦 棒 グラフ 化 グラフに 限 らず この 表 を 利 用 して 次 のデータの 作 成 を 行 なうことは 通 常 のエクセルの 利 用 と 変 わりは 無 い b. 抽 出 した 表 の 表 示 形 式 の 変 更 図 10では グラフ 化 した 元 データは 各 選 択 肢 を 選 択 した 人 数 をカウ

アンケート 分 析 を 例 とした Microsoft Excel のピボットテーブルの 基 本 的 な 使 用 方 法 95 ントした 数 値 ( 人 数 )が 表 示 されている これを 各 年 度 毎 の 構 成 比 に 変 更 する ピボットテーブル 内 の 任 意 のセルを 選 択 し 右 クリックのメニューより 値 フィールドの 設 定 を 選 択 する( 図 11) 値 フィールドの 設 定 ウ ィンドウが 開 く 同 ウィンドウの 計 算 の 種 類 タブを 選 び 計 算 の 種 類 を 標 準 から 列 方 向 の 比 率 に 変 更 する( 図 12) 図 11. 値 フィールドの 設 定 ウィンドウ 図 12. 計 算 の 種 類 タブ 図 13のように ピボットテーブルが 各 年 度 毎 に 割 合 表 示 に 変 更 される 同 時 に グラフ 化 している 場 合 は グラフの 値 も 変 更 されているのが 確 認 できる 小 数 点 の 位 置 については エクセルの 通 常 の 機 能 により 変 更 可 能 である 図 13.データの 比 率 表 示 への 変 更

96 近 藤 敦 飯 田 真 伍 3. 集 計 対 象 の 変 更 次 に 集 計 対 象 を 各 年 度 における カ:あなたの 卒 業 後 の 進 路 について お 教 えください についての 解 答 に 変 更 します 先 述 の 学 生 生 活 の 満 足 度 についての 集 計 表 を 引 き 続 き 利 用 するが 新 た に 作 成 する 場 合 は 使 用 するオリジナル データは 同 一 なので ピボット テーブルのコピーに 新 しいピボットテーブルを 準 備 すればよい 1 列 ラベル と Σ 値 に それぞれドロップした ア: 学 生 生 活 を 振 返 って あなたの 満 足 度 はどうですか? を フィールドリスト へドラッグ&ドロップにて 戻 し 列 ラベル Σ 値 欄 を 空 にする 図 7の 状 態 に 戻 る 2 先 の 例 と 同 様 に フィールドリスト より カ:あなたの 卒 業 後 の 進 路 について お 教 えください を 列 ラベル Σ 値 にドラッグ& ドロップにより 各 年 度 毎 の 卒 業 後 の 進 路 についての 結 果 をうること ができる 図 14. 各 年 度 毎 の 卒 業 後 の 進 路 同 様 の 手 順 にて どのデータとどのデータの 関 係 を 知 りたいのかをドラ ッグ&ドロップすることで 容 易 に 集 計 結 果 を 得 ることができる 4.フィルタの 適 用 図 14は カ:あなたの 卒 業 後 の 進 路 について お 教 えください の 回

アンケート 分 析 を 例 とした Microsoft Excel のピボットテーブルの 基 本 的 な 使 用 方 法 97 答 を 全 て 表 示 しているが その 中 で 就 職 と 回 答 したデータを 抽 出 する 場 合 図 14のピボットテーブル: 列 ラベルと 書 かれた 横 に 下 向 きの マー クがある これをクリックすると 図 15のように 選 択 肢 の 一 覧 が 表 示 され 各 選 択 肢 には チェックボックスが 行 頭 に 付 与 されている 図 15. 回 答 選 択 肢 のフィルタ 可 能 項 目 このチェックボックスにより 表 示 させるデータにフィルタをかけるこ とができる 01: 就 職 にのみチェックをオンにすると 図 16にように 卒 業 の 進 路 が 就 職 である 卒 業 生 数 が 表 示 される 図 16. 卒 業 の 進 路 が 就 職 である 卒 業 生 の 人 数

98 近 藤 敦 飯 田 真 伍 5.クロス 集 計 ア: 学 生 生 活 を 振 返 って あなたの 満 足 度 はどうですか? について 大 変 満 足 満 足 と 回 答 した 卒 業 生 の 進 路 について 集 計 する 図 17のように カ:あなたの 卒 業 後 の 進 路 について お 教 えください の 集 計 結 果 で レポートフィルタ に フィールドリスト から ア: 学 生 生 活 を 振 返 って あなたの 満 足 度 はどうですか? をドラッグ&ドロッ プする セル A-1 に レポートフィルタ にドロップした 内 容 が 追 加 され データに 変 化 はない( 図 18) 図 17. カ:あなたの 卒 業 後 の 進 路 について お 教 えくだ さい の 集 計 結 果 図 18. レポートフィルタ を 設 定

アンケート 分 析 を 例 とした Microsoft Excel のピボットテーブルの 基 本 的 な 使 用 方 法 99 続 いて セル A-2 の により ア: 学 生 生 活 を 振 返 って あなたの 満 足 度 はどうですか? の 選 択 肢 から 抽 出 したいものを 選 択 する( 図 19) 図 19.フィルタの 選 択 大 変 満 足 満 足 のチェックをオンの 状 態 とすると 図 20のように データに 変 化 が 現 れる 図 20.フィルタの 適 用 2005 年 度 の 卒 業 生 の 内 就 職 したのは 86.4%の 学 生 で 大 学 生 活 に 大 変 満 足 または 満 足 した 卒 業 生 の 内 で 就 職 したのは 87.9%である という 結 果 を 得 ることができる

100 近 藤 敦 飯 田 真 伍 Ⅴ.まとめ 今 回 使 用 したオリジナル データは データとしては 非 常 に 小 さなもので あるが 様 々なデータが 蓄 積 され 非 常 に 大 きなものとなった 場 合 でも 集 計 したい 事 項 は 実 は 非 常 に 小 さいものである 場 合 がある 広 大 なデータから 必 要 なものを 抽 出 し さらにまとめる 作 業 は 非 常 に 困 難 な 場 合 もあるが ピボットテーブルにより 広 大 なデータから 任 意 のデ ータに 着 目 し ドラッグ&ドロップによるフィールドリストの 変 更 抽 出 条 件 の 設 定 ( 上 記 以 外 にも 多 数 あります)により 任 意 のデータ 抽 出 が 行 える アンケート 結 果 の 集 計 分 析 が 主 な 利 用 方 法 ではないが ピボットテー ブルの 利 用 方 法 を 知 るには 売 上 表 のようなものよりも アンケートデー タのようなものを 扱 う 方 が 入 門 として 理 解 しやすいと 考 えられる ピボットテーブルについての 市 販 の 参 考 書 は 商 品 売 上 表 や 担 当 者 別 売 上 表 のような 実 用 的 な 例 が 豊 富 にあるが ピボットテーブルは 難 しいとい うイメージも 同 時 に 植 えつけているように 感 じる ピボットテーブルの 柔 軟 で 強 力 な 利 用 方 法 については 本 稿 を 読 んで 頂 き 何 ができそうなのか もっとこんな 使 い 方 はできないかを 感 じ 取 っ ていただけると 幸 いである ここでは 基 本 的 な 機 能 だけに 留 まるが 非 常 に 多 くの 機 能 をピボットテ ーブルは 有 しており 強 力 である しかしながら 当 然 限 界 も 存 在 する 全 てをピボットテーブルに 頼 るのではなく ピボットテーブルから 抽 出 し たデータを 再 利 用 し 新 たなテーブルから 答 えを 導 くという 臨 機 応 変 さも 利 用 者 は 持 つ 必 要 がある こんなに 簡 単 で 有 効 な 結 果 を 簡 単 に 得 られる 機 能 を 使 わないのはもっ たいないと 思 われる エクセルは 敷 居 が 高 いと 思 っている 方 や 学 生 の 皆 さんに 使 ってみようかと 思 っていただけると 幸 いである 最 後 に 本 稿 作 成 にあたり 高 等 教 育 研 究 センター 運 営 委 員 の 皆 様 な らびに 同 事 務 室 の 皆 様 のご 協 力 なく 作 成 することはできませんでした 心 より 感 謝 いたいます 参 考 文 献 門 脇 香 奈 子 &できるシリーズ 編 集 部 できる Excel ピボットテーブル