情 報 処 理 技 法 (リテラシ)II Excelでのデータ 集 計
Excelでのデータ 集 計 の 基 本 データの 入 力 1 行 に1 試 行 分 のデータを 書 く アンケートの 結 果 なども1 人 分 のデータを1 行 に する 入 力 している 時 間 がもったいないのでデータを 用 意 しました サンプルデータをダウンロード
単 純 集 計 とクロス 集 計 単 純 集 計 一 つの 指 標 に 関 して 度 数 を 示 したもの 度 数 分 布 表 度 数 と 項 目 ごとの 割 合 を 示 す クロス 集 計 二 つ 以 上 の 指 標 に 関 して 度 数 を 示 したもの クロス 集 計 表 ( 相 関 表 ) 指 標 ( 例 えば 性 別 や 年 代 )ごとに 度 数 を 数 え 上 げる
サンプルデータについて 単 純 集 計 の 例 50 回 振 ったサイコロの 出 目 クロス 集 計 の 例 10 人 の 小 学 生 のテストの 結 果
ピボットテーブルの 利 用 集 計 機 能 としてExcelには ピボットテーブル 機 能 がある 活 用 する 事 で 事 象 を 多 角 的 にとらえることが 出 来 る
ピボットテーブルの 利 用 ピボットテーブルとは ピボットテーブル レポートは データを 組 み 合 わせて 比 較 する 対 話 型 のツールです 行 と 列 を 入 れ 替 えることで 元 データの 集 計 方 法 を 変 えてみたり 関 心 のある 領 域 の 詳 細 を 表 示 した りできます (Excel2011のヘルプより 引 用 ) データを 行 列 の 形 で 集 計 して 分 かり 易 くしたも の 大 まかに 言 えばExcelを 使 って 半 自 動 で クロス 集 計 表 を 作 るツール
サイコロの 出 目 を 集 計 する 1 完 成 図 出 目 ごとの 度 数 を 集 計 し た 表 を 作 成 する
サイコロの 出 目 を 集 計 する 2 ピボットテーブルにした いデータのある 部 分 をア クティブにする データタブ から ピ ボットテーブル 手 動 ピ ボットテーブルの 作 成 を 選 ぶ 新 規 ワークシートに ピボ ットテーブルを 作 成 する
サイコロの 出 目 を 集 計 する 3 作 成 されたシートに 切 り 替 えてピボットテーブル を 編 集 する フィールドリストから 回 数 と 出 目 をチ ェック
サイコロの 出 目 を 集 計 する 4 列 ラベルに 出 目 を Σ 値 に 合 計 / 回 数 となるよう にドラッグ(または 状 態 を 確 認 ) 合 計 / 回 数 の 値 フィード の 設 定 (iアイコン)を 開 く 選 択 したフィールドのデ ータ を データの 個 数 に 変 更 OK ボタンを 押 す 完 成
小 学 生 の 成 績 を 集 計 する 1 完 成 図 男 女 別 に4 教 科 の 平 均 値 を 表 にする
小 学 生 の 成 績 を 集 計 する 2 新 規 シートに 手 動 でピボ ットテーブルを 作 成 する ところまでは 同 じ 性 別 国 語 社 会 までを チェック 行 ラベル: 性 別 を 配 置 Σ 値 : 国 語 から 社 会 までを 配 置
小 学 生 の 成 績 を 集 計 する 3 国 語 から 社 会 までの 値 フィールドの 設 定 で 平 均 を 選 ぶ 全 て 設 定 し 終 えたら 完 成
データの 特 徴 を 把 握 する データの 特 徴 の 捉 え 方 一 般 的 にデータを 取 得 した 時 点 では 全 体 像 は 把 握 しづらい 調 査 結 果 の 例 を 元 に 実 習
50 人 の 成 績 データ 以 下 のデータを 用 いて 実 習 54 48 44 48 74 46 92 65 42 18 59 54 48 52 78 52 28 74 46 22 49 58 53 56 84 55 34 76 50 24 54 64 56 62 38 61 36 80 55 31 58 54 64 69 44 65 42 88 60 36 データファイルに 収 録 されています
表 としてまとめる 表 としてまとめるメリット 表 や 図 にすることは 直 感 的 な 理 解 のために 重 要 データの 入 力 ミスを 発 見 するためにも 役 立 つ 度 数 分 布 表 という 表 を 作 成 する
ピボットテーブルで 度 数 分 布 表 ピボットテーブルで 得 点 順 にデータを 並 べる まずはそれぞれの 値 がいくつずつあるかという 表 を 作 ってみよう
ここまでの 完 成 図
作 り 方 1 データを 指 定 して デー タ リボンから 手 動 ピ ボットテーブルの 作 成 を 選 ぶ 別 シートに 作 成 ピボットテーブルビルダ ーで 行 ラベル と Σ 値 に 得 点 を 設 定
作 り 方 2 Σ 値 の 得 点 を 合 計 から データの 個 数 に 変 更
これでは 数 値 の 種 類 が 多 すぎる
度 数 分 布 表 の 設 定 階 級 の 幅 の 決 定 階 級 とはデータが 含 まれる 等 間 隔 の 区 間 等 幅 に 設 定 した 階 級 の 間 に 値 がいくつずつあるか を 数 える 階 級 の 幅 は 分 かり 易 くデータを 数 え 易 いものにす る 区 切 りの 良 い 数 (5,10,15,20,100など)が 良 い
階 級 の 幅 の 設 定 50 100 200 500 1,000 2,000 5,000 10,000 7 8 9 10 11 12 13 14
級 間 の 決 定 スタージェスの 方 法 によると 今 回 のデータから は 級 間 の 数 は7 92-18=74 74/7 10.57 級 間 は10として 設 定 することとする 11-20, 21-30 81-90, 91-100 とすれば 数 え 易 そう
ピボットテーブルのグループ 化 機 能 行 ラベルの 列 を 選 ぶ ピボットテーブルツー ル の オプション か ら グループの 選 択 を 選 ぶ 先 頭 の 値 末 尾 の 値 単 位 をそれぞれ 設 定
完 成 図
データの 視 覚 化
ヒストグラムの 作 成 度 数 分 布 表 を 元 に 発 展 させる 棒 グラフ 状 にデータを 表 記 する ヒストグラム 量 的 データは 連 続 していることを 示 すために 棒 と 棒 の 間 に 隙 間 を 空 けないようにする( 質 的 な 違 い のある 項 目 を 示 す 棒 グラフとは 少 しちがう)
完 成 図
ヒストグラムの 作 成 1 ピボットテーブルの 値 を もとにグラフを 作 成 値 を 選 択 挿 入 グラフ から 集 合 縦 棒 を 選 択
ヒストグラムの 作 成 2 グラフレイアウト の 減 税 の 選 択 項 目 から 系 列 1 を 選 択
ヒストグラムの 作 成 3 書 式 選 択 ボタンをクリ ック データ 系 列 の 書 式 設 定 の オプション から 要 素 の 間 隔 を 0% に 設 定
ヒストグラムの 作 成 4 棒 の 見 栄 えの 設 定 塗 りつぶし の 色 は 白 線 の 色 は 黒
ヒストグラムの 作 成 5 細 かいところを 調 整 して 完 成
実 習
サンプルデータ 2での 実 習 設 問 身 長 データシート を 元 に 度 数 分 布 表 とヒスト グラムを 書 きなさい グループ 化 の 区 切 りは145-149のように5cm 刻 みで 行 うこと
完 成 図
データの 視 覚 化 の 目 的 データを 見 やすくまとめる 表 や 図 にすると 直 感 的 な 理 解 が 得 られる 調 査 や 実 験 を 行 った 後 は 必 ず 行 うべき 作 業 入 力 間 違 いなども 発 見 し 易 くなる はずれ 値 など
分 布 の 性 質 を 数 値 で 表 す
分 布 の 数 値 化 の 必 要 性 視 覚 化 全 体 の 様 子 をとらえるのには 良 い 他 人 に 伝 えるには 困 難 他 の 方 法 も 必 要 になる 数 値 化
データの 数 値 化
代 表 値 今 回 のサンプルデータの 値 を 一 つの 数 値 で 表 すに はどうしたら 良 いか? 分 布 の 特 徴 を1つの 数 値 で 表 すための 値 平 均 値 中 央 値 最 頻 値 全 て 分 布 を 代 表 する 値 足 り 得 る
代 表 値 の 種 類 1 平 均 値 記 号 では M とか X 算 出 方 法 は 結 果 を 全 て 合 計 して 人 数 で 割 ったもの 中 央 値 メディアンともいう 記 号 では Md や Me 小 さい 順 に 並 べて 中 央 に 来 た 数 偶 数 の 場 合 は 中 央 2つの 値 の 平 均 値 最 頻 値 モードともいう 記 号 では Mo 最 も 度 数 が 多 い 級 間 の 中 心 点 の 値
代 表 値 の 比 較 平 均 値 中 央 値 最 頻 値 には 特 徴 がある 性 質 をよく 知 った 上 で 使 わないといけない
平 均 値 分 布 のすべての 得 点 を 使 って 求 める 平 均 値 からのすべての 得 点 の 差 を 合 計 すると0に なる 極 端 な 値 に 左 右 されやすい 数 学 的 に 操 作 しやすい よく 出 てくるし 馴 染 みもあるのでとっつきやす いでしょう
中 央 値 分 布 の 特 定 の 値 に 左 右 されづらい 歪 曲 した 分 布 でも 利 用 できる 平 均 値 ほどには 分 布 の 性 質 が 反 映 されない 今 回 の 実 習 では 扱 わない
最 頻 値 直 感 的 にわかりやすい 極 端 な 値 には 左 右 されづらい 数 理 処 理 には 向 かない 質 的 データに 利 用 されることが 多 い 今 回 の 実 習 では 扱 わない
散 布 度 (ばらつき 具 合 の 指 標 )について
代 表 値 の 選 択 今 回 の 実 習 では 代 表 値 として 平 均 値 を 用 いる 数 学 的 に 最 も 高 度 な 代 表 値 分 布 の 全 ての 値 を 使 って 求 める
分 布 の 形 今 回 は 例 として 正 規 分 布 しているデータを 用 いる 左 右 対 称 中 央 部 分 が 最 も 度 数 が 多 く 極 端 な 値 は 度 数 が 少 ない 釣 り 鐘 型 のカーブを 描 く
正 規 分 布 のグラフ
平 均 値 が 等 しい 正 規 分 布 の 比 較 平 均 値 が 等 しいからといっても 同 じ 正 規 分 布 で はない
平 均 値 が 等 しい 正 規 分 布 の 比 較 下 の 二 つは 何 が 違 うのか
ばらつき 具 合 の 違 いの 表 現 なだらかな 分 布 データがばらついている 尖 った 分 布 データがばらついていない
ばらつき 具 合 と 分 布 の 形 データのばらつきが 分 布 の 形 を 決 める ばらつき 具 合 を 他 人 に 伝 えるためには? 数 値 で 表 現 する 散 布 度 という 指 標 を 用 いる 代 表 値 と 散 布 度 で 分 布 の 特 徴 を 表 現 でき る
最 も 簡 単 な 散 布 度 範 囲 最 大 値 と 最 小 値 から 求 める 値 極 端 な 値 に 弱 い 必 ず100 点 を 取 る 人 と 必 ず0 点 を 取 る 人 がテストを 受 け たら? 2つの 値 しか 使 っていないので 情 報 量 が 少 ない 平 均 値 のように 全 てのデータ を 使 いたい
ばらつき 具 合 の 表 現 ばらつきとは 何 からの ばらつきか? 代 表 値 ( 平 均 値 )からどれだけ 離 れているか それぞれの 値 ( 素 点 )が 平 均 値 からどれだけ 離 れ ているかを 計 算 して 集 計 すれば 良 い
偏 差 それぞれの 値 が 代 表 値 から どれだけ 離 れているかを 偏 差 という 偏 差 = 個 々の 値 平 均 値 基 本 統 計 量 に 書 き 入 れ ること 絶 対 参 照 を 使 う 事
偏 差 の 合 計 偏 差 を 全 て 求 めたら 合 計 す ること 0 になるはず 0 になるのは 数 学 的 に 証 明 されているので 安 心 するこ と( 証 明 はここでは 触 れな い)
偏 差 二 乗 和 なぜ 偏 差 の 合 計 が0になる か 正 の 数 と 負 の 数 が 等 しいか ら 全 てを 正 の 数 にすればいい 二 乗 すれば 正 の 数 になる ( 単 位 も 二 乗 になる) 全 て 計 算 したら 合 計 する
偏 差 二 乗 和 偏 差 二 乗 和 (Sum of Squares = SS) 偏 差 二 乗 和 偏 差 自 乗 和 偏 差 平 方 和 どれも 同 じものを 指 す 最 も 基 本 となる 散 布 度 の 指 標 ただし 単 純 に 加 算 しているので データ 数 が 増 え ると 値 が 大 きくなる データ 数 で 調 整 をする 必 要 がある
分 散 偏 差 二 乗 和 をデータ 数 で 調 整 した 値 分 散 = 偏 差 二 乗 和 データ 数 調 整 してあるので ばらつき 具 合 の 評 価 が 比 較 しやすい ただし 単 位 は 二 乗 のまま 算 出 する 事 データ 数 を 求 めるには =count 関 数 を 使 うと 良 い
標 準 偏 差 分 散 の 単 位 を 元 の 単 位 に 戻 したもの 分 散 の 平 方 根 (ルート)を 取 る 標 準 偏 差 = 分 散 平 方 根 を 求 める 関 数 は =sqrt 関 数 または 値 を0.5 乗 する
サンプルデータの 標 準 偏 差 5.00 2.24 このテストの 結 果 は 平 均 5.0 点 で 標 準 的 なばらつき 具 合 は 平 均 点 を 中 心 にし て2.24 点 である といえる
散 布 度 についてのまとめ 代 表 値 と 散 布 度 でデータのおおまかな 性 質 を 捉 えられる 代 表 値 平 均 値 中 央 値 最 頻 値 散 布 度 偏 差 二 乗 和 分 散 標 準 偏 差
今 週 はここまで よく 頑 張 りました