モジュールME-16 ME-16 ピボットテーブルによる データ 分 析 岡 山 県 情 報 教 育 センター
ピボットテーブルによるデータ 分 析 EXCELのリスト(シート 上 の 表 形 式 のデータ)から 縦 横 の 項 目 を 設 定 して 新 しい 表 ( 集 計 表 )を 作 ることができます この 表 を ピボットテーブル と 呼 びますが 集 計 を 自 動 で 行 う ことができるので 学 校 では 科 目 などの 選 択 人 数 を 確 認 したりするのに 便 利 で うまく 利 用 すれ ば 進 級 時 のクラス 編 成 の 資 料 を 作 成 するのにも 役 に 立 ちます 1 ピボットテーブルウィザード 例 としてクラス 編 成 の 資 料 作 成 にピボットテーブルを 応 用 してみる ( 学 校 ごとにクラス 編 成 の 方 法 は 異 なるので 実 際 には 各 校 の 現 状 にあわせて 行 うこと) 1 演 習 _ピボットテーブル.xls を 読 み 込 む 2 準 備 しているデータを 確 認 する 名 簿 ( 現 在 の 組 番 氏 名 せい めい) クラス 編 成 に 用 いる 項 目 今 回 は 項 目 1~5 までに A~C の 段 階 の データを 準 備 した 項 目 1 は 地 区 として これだけは 10 種 類 にしておいた 実 際 にはこれに リーダーシップ などを 当 てはめれば よい 計 算 新 組 新 番 号 編 成 作 業 と 後 の 処 理 に 用 いる この 例 では 計 算 列 の 計 算 式 を 次 のように 設 定 している Mll: =COUNTIF(I11:L11, =A )*5+COUNTIF(Ill:Lll, =B )*3+COUNTIF(I11:Lll, =C )*1 つまり 項 目 2~5で A を 5 点 B を3 点 C を1 点 として 合 計 点 を 目 安 として 算 出 している 全 クラス( 例 では3クラス)を 計 算 の 列 が 降 順 になるように 並 べ 香 えて 新 組 に 仮 のクラスを 入 力 する このときに1,2,3,1,2,3, と 入 力 すると 1 組 に 得 点 の 高 いものが 集 まるので 例 えば1 2,3, 3,1,2,2,3,1 というように 同 じ 並 びが 続 かないように 仮 クラスを 設 定 してゆく 3 表 の 構 成 を 確 認 したら 表 内 部 のどれか(どれでもよい) 一 つのセルを 選 択 する 複 数 セルを 選 択 してはいけない 4メニューから データ 一 ピボットテ ーブルとピボットグラフレポート をク リックする EXCEL97 と EXCEL2000 とで 異 なる 左 図 は EXCEL2000 の 例 EXCEL97 では ピボットテーブルレポート を クリックする ピボットグラフ は 扱 えない 1
4 左 図 のようにピボットテーブルウイザードが 起 動 し まず データの 場 所 とレポートの 種 類 を 確 認 する 単 一 セルを 選 択 していた 場 合 は そのセルがある 領 域 ( 空 白 セルで 囲 まれた 範 囲 )が 自 動 的 に 設 定 される 外 部 データソース(Access などの 外 部 データベース) などからピボットテーブルを 作 成 するように 設 定 する こともできる この 例 では 何 も 変 更 せず そのまま 次 へ をクリックする 5 範 囲 設 定 の 画 面 が 現 れる 単 一 セルを 選 択 していたので 範 囲 が 自 動 選 択 され 点 滅 線 で 示 される ここで 範 囲 を 変 更 することもできる 次 へ をクリックする 6ピボットテーブルの 作 成 先 の 設 定 を 行 う 新 規 ワークシート を 選 択 すると 新 しいワーク シートが 作 成 され その 上 に 表 示 される 既 存 のワークシート を 選 択 すると セルの 位 置 を 指 定 し その 場 所 に 表 示 させることができる レイアウト をクリックする EXCEL97 では 直 接 レイアウト の 画 面 になり 作 成 先 はその 後 で 設 定 する 7レイアウトの 設 定 を 行 う 画 面 の 右 に 表 示 されている 項 目 をドラッグし て 左 のレイアウト 画 面 にドロップする ここでは 左 図 のように 新 組 を 行 の 位 置 へ 項 目 1 を 列 の 位 置 へ 性 別 を 列 の 項 目 1 の 右 へ 氏 名 を データ へ それぞれドロップする ドロップした 項 目 1 をダブルクリックする 右 図 のような 画 面 が 出 るので 名 前 を 1 に 変 更 集 計 を なし に 設 定 同 様 に 性 別 でも 集 計 を なし にしておく 2
8ワークシートが 挿 入 され ピボットテーブルが 作 成 される 見 やすいように 列 の 幅 を 変 更 する 新 組 が2 項 目 1 が8 性 別 が1 の 該 当 者 が2 名 である ことを 示 す 項 目 1 の 内 容 それぞれが 性 別 の1 2を 含 んでいる 内 容 が 5 のときは 性 別 の 1 のデータが 存 在 していないので 非 表 示 されている 設 定 したレイアウトどおり 縦 に 新 組 横 に 項 目 1 性 別 が 並 んでいるのが 確 認 できる シートの 名 称 を 資 料 としておく 2 複 写 によるピボットテーブル 作 成 と 再 設 定 1ピボットテーブルをコピーする 表 示 されているピボットテーブル 内 部 で 右 クリック 選 択 テーブル 全 体 をクリック して ピボットテーブル 全 体 を 選 択 する テーブルの 左 上 隅 例 ではA2セルの 上 部 分 にマウスカーソルを 合 わせるとカーソルが 下 向 きの 矢 印 に 変 わる このときクリックすればテーブル 全 体 を 選 択 できる メニューから 編 集 コピー を 行 う 貼 り 付 けたい 場 所 をクリックして 選 択 し メニューから 編 集 貼 り 付 け を 行 う ここではA1セルに 貼 り 付 ける 2 新 しく 貼 り 付 けたピボットテーブルを 選 択 肢 右 クリック ウィザード を 選 択 する EXCEL2000 では 表 示 されるダイアログから レイアウト を 選 ぶ EXCEL97 では 直 接 レイアウト 画 面 になる 3レイアウトの 再 設 定 を 行 って 項 目 1 を 削 除 し 項 目 2 を 加 える 項 目 1 をドラッグして テーブルから 離 れたところでドロップする 項 目 1 を 1 という 名 称 にしているので 実 際 には 1 を 削 除 する テーブル 内 部 の 項 目 2 をドラッグし テーブルの 性 別 の 左 にドロップする ドロップした 項 目 2 をダブルクリッ クして 名 称 を 2 集 計 を なし に 設 定 する 4 再 設 定 を 行 ったピボットテーブルをコピーし A20,J11,J20 に 貼 り 付 ける 5それぞれのピボットテーブルを 再 設 定 して 項 目 1~5の 全 てを 確 認 できるようにする 6 列 の 表 示 を 見 やすいように 設 定 する 3
7 次 のようになる 3 ピボットテーブルの 応 用 作 成 した 表 から クラス 編 成 上 の 偏 りを 見 つけて 修 正 を 行 う 1 各 テーブルを 調 べて クラスによる 偏 りをみる 項 目 2 のテーブル( 右 図 )を 見 ると 1,2 組 はAが4 人 なのに3 組 では2 人 3 組 のCの 人 数 は2 組 の6 人 に 対 して8 人 と 多 い となっているので 3 組 のCの 誰 かを2 組 へ 2 組 のAの 誰 かを3 組 へ 移 動 させればバランスが 取 れることになる ここで 男 女 (1,2)をみると3 組 のCは 男 子 (1)の 方 が5 人 と 多 いので 男 子 を 移 動 させれ ば 良 いだろうと 考 えられる 2テーブルデータの 詳 細 を 表 示 して 移 動 の 候 補 者 を 捜 す テーブルの 該 当 部 分 をダブルクリックすると その 集 計 値 の 元 データが 新 しいワークシートに 表 示 される ここでは3 組 のC 男 子 の5 人 を 示 す 5 をダブルクリックする 該 当 の5 名 のデータが 表 示 される 新 しいワークシートが 挿 入 される 同 様 に2 組 のA 男 子 の 2 を 詳 細 表 示 させる 4
3シートのデータを 複 写 して 並 べて 見 比 べ 候 補 者 を 絞 り 込 む 今 回 は 新 3 組 の 西 和 則 と 新 2 組 の 芳 明 充 洋 を 入 れ 替 えてみる 4 名 簿 シートを 選 択 し データを 変 更 する 現 3 組 (B 列 参 照 )の 西 和 則 の 新 組 (N 列 を 2 に 変 更 する 現 1 組 の 芳 明 充 洋 の 新 組 を 3 に 変 更 する 5ピボットテーブルを 更 新 する シート 資 料 を 選 択 し メニューから データ データの 更 新 をクリックする 再 集 計 が 行 われ 各 テーブルの 値 が 変 更 される 書 式 ( 列 幅 )も 再 表 示 で 変 更 されるので 設 定 をやりなおす 6それぞれの 項 目 で 同 様 の 変 更 を 行 い それを 繰 り 返 して 全 体 のバランスを 整 える 7できた 編 成 を 新 クラス せい めい で 並 び 替 えを 行 い 新 しい 名 簿 を 作 成 する これを 印 刷 して 関 係 者 で 詳 細 を 評 価 し 生 徒 間 の 関 係 などから 最 終 調 整 を 行 って 編 成 資 料 の 一 つとする 再 集 計 を 繰 り 返 すときには それぞれのテーブルの 再 設 定 時 にウィザードから オプション で 表 のオートフォーマット のチェックを 外 しておくとよい 5
Microsoft,Windows,Windows NT, Microsoft Office,Microsoft Outlook,Internet Explorer,MSN は 米 国 Microsoft Corporation の 米 国 およびその 他 の 国 における 登 録 商 標 または 商 標 です Netscape, Netscape Navigator, Netscape ONE, Netscape の N ロゴおよび 操 舵 輪 のロゴは 米 国 およびその 他 の 諸 国 の Netscape Communications Corporation 社 の 登 録 商 標 です 一 太 郎 ATOK は 株 式 会 社 ジャストシステムの 登 録 商 標 です その 他 本 書 に 掲 載 したプログラム 名 システム 名 CPU などは 一 般 に 各 社 の 登 録 商 標 です 本 文 中 では TM などのマークは 明 記 していません 本 文 中 では Microsoft Internet Explorer のことを Internet Explorer 5.0 または IE5.0 Internet Explorer と 記 述 しています 平 成 13 年 11 月 1 日 初 版 発 行 発 行 岡 山 県 情 報 教 育 センター 703-8288 岡 山 市 赤 坂 本 町 3 番 15 号 電 話 (086)272-1405( 総 務 課 ) 272-4608( 研 修 課 ) 交 通 機 関 市 内 電 車 岡 山 駅 前 ( 東 山 行 ) 乗 車 東 山 ( 終 点 ) 下 車, 南 へ 徒 歩 7 分 市 内 バス 岡 山 駅 前 ( 東 山 行 ) 乗 車 東 高 前 下 車, 西 へ 徒 歩 2 分 禁 無 断 転 載 2001 岡 山 県 情 報 教 育 センター