消 費 支 出 経 済 統 計 実 習 資 料 2011/11/14 < 家 計 に 関 する 統 計 > 1. 準 備 今 回 の 実 習 には あらかじめ 河 田 が 作 成 したファイルを 用 いる 課 題 4 経 済 統 計 の 講 義 用 HP から 家 計 調 査 の 分 析 のファイルをダウンロードしてみよ う 1 徳 山 大 学 のトップページ (http://www.tokuyama-u.ac.jp) で 在 学 生 の 皆 様 へ のメニュ ーから 教 員 紹 介 を 選 び 河 田 の 教 員 紹 介 を 開 く 2 そのページの 下 のほうにある 個 人 サイト のところをクリックすると 河 田 研 究 室 の ページにジャンプする (ここまでの 手 順 は http://www2.tokuyama-u.ac.jp/kawada とア ドレスを 直 接 入 力 してもよい) 3 経 済 統 計 をクリックし 第 13 回 11 月 14 日 ( 月 ) の 配 布 資 料 にある 家 計 調 査 の 分 析 H22 をクリックし 自 分 の 使 いやすい 場 所 に 保 存 する 2. 散 布 図 の 描 画 課 題 5 年 間 収 入 階 級 別 データの 可 処 分 所 得 を 横 軸 に 消 費 支 出 を 縦 軸 にとり 散 布 図 を 描 いてみよう 1 2-6 表 の 可 処 分 所 得 (K25:AB25)を 消 費 関 数 のシートの D2:D19 セルに 消 費 支 出 (K14:AB14)を 消 費 関 数 のシートの E2:E19 セルにそれぞれコピーする 貼 り 付 ける 際 に 形 式 を 選 択 して 貼 り 付 け を 選 び 行 列 を 入 れ 替 える にチェックをいれる 2 D2:E19 を 範 囲 指 定 し リボン 内 にグラフのグループにある 散 布 図 のボタンをクリッ クする ( 挿 入 タブをクリックすることで 表 示 される ) 3 散 布 図 グラフのフォーマッ ト( 型 式 )メニューにおい て 散 布 図 (マーカーのみ) ( 左 上 )をクリックし リ ボンの 中 のレイアウト 1( 左 端 )をクリックし タイト ルや 軸 ラベルなどが 書 き 込 めるようにする 4 右 の 図 のようにタイトル 軸 ラベルを 入 力 し 目 盛 線 凡 例 を 非 表 示 にする 年 間 収 入 階 級 別 の 所 得 と 消 費 600000 500000 400000 300000 200000 100000 0 0 200000 400000 600000 800000 1000000 可 処 分 所 得 - 1 -
3. 統 計 関 数 による 回 帰 直 線 の 導 出 Excelが 備 えている 関 数 を 用 いて 回 帰 直 線 の 導 出 を 行 ってみることにする (1) 回 帰 係 数 の 導 出 ( 関 数 SLOPE とINTERCEPT 1 ) Y=a+bX という 回 帰 モデルにおいて SLOPE は 回 帰 直 線 の 傾 き b を INTERCEPT は 切 片 a を 求 める 関 数 である どちらも 引 数 は2 個 あるが 最 初 の 引 数 が 従 属 変 数 (=の 左 側 )の 範 囲 で 2 個 目 の 引 数 が 独 立 変 数 (=の 右 側 )の 範 囲 をとる 引 数 の 順 序 に 注 意 しなければならない (2) 予 測 値 の 導 出 ( 関 数 FORECAST 2 ) 予 測 値 ^Y として 関 数 FORECAST がある FORECAST( Xi, 回 帰 のY 範 囲, 回 帰 のX 範 囲 ) として 用 いる なお この 予 測 値 は 戻 り 値 は a + bxi として 求 められた 数 値 1 個 である 関 数 SLOPEとINTERCEPTを 用 いて 求 めたa, bから a + bxi として 求 めた 値 と 等 しくなる 課 題 6 消 費 支 出 を Y 可 処 分 所 得 を X とした 回 帰 分 析 Y=a+bX をおこない 予 測 値 を 求 め 散 布 図 を 描 きいれよう 1 消 費 関 数 のシートの E21 セルに a の 推 定 値 を E22 セルに b の 推 定 値 を 求 める (D21 セルと D22 セルに a,b と 記 入 しておこう) E21 セルに =INTERCEPT(E2:E19,D2:D19) と 入 力 E22 セルに =SLOPE(E2:E19, D2:D19) と 入 力 する 2 F2 セルに =FORECAST(D2,$E$2:$E$19,$D$2:$D$19) と 入 力 し F3:F19 セルにコピ ーする (F1 セルには 予 測 値 と 記 入 しておこう) 3 グラフをアクティブにした 状 態 で リボンの 中 の データの 選 択 ボタン( 出 ていない 場 合 には 上 部 の グラフツール をクリックする) をクリックし 凡 例 項 目 ( 系 列 ) の 追 加 ボタンをクリックする そして 系 列 Xの 値 をD2:D19 系 列 Yの 値 をF2:F19 とする 4 3の 操 作 で 散 布 図 上 に 赤 色 のマーカーが 現 れたはずである これを 直 線 で 結 ぶ 赤 色 の マーカーのひとつを 右 クリックして データ 系 列 の 書 式 設 定 を 選 ぶ そこで マーカ ーのオプション をクリックし マーカーの 種 類 として なし をチェックし 線 の 色 をクリックし 線 の 色 として 線 ( 単 色 ) をチェックする グラフエリアの 外 をクリッ クすると 回 帰 直 線 が 引 けたことがわかるはずである 4. 分 析 ツールの 利 用 Excel には 統 計 分 析 を 行 うためのいくつかの 分 析 ツールが 付 属 している これらのツールを 使 えば 一 度 に 詳 細 な 分 析 結 果 を 得 ることができる 分 析 ツールを 最 初 に 使 用 する 場 合 には アド イン( 有 効 にすること)しなくてはならない 分 析 ツールのアドインは 次 のようにおこなう 1 左 上 の Office ボタンを 押 し 下 にある Excel のオプション のボタンをクリックする 2 分 析 ツール にチェックをつけ OK ボタンをクリックする すると データタブの 中 に データ 分 析 のボタンが 出 てくるので 下 のほうにある 回 帰 分 析 を 選 べばよい 1 回 帰 直 線 の 傾 きと 切 片 を 求 める 関 数 には LINEST という 関 数 がある この 関 数 は 傾 きと 切 片 以 外 に 分 析 結 果 に 関 する 多 くの 情 報 量 を 与 えてくれる 非 常 に 便 利 な 関 数 である 反 面 使 用 法 および 結 果 の 解 釈 の 仕 方 が 難 しい LINEST 関 数 の 説 明 はここでは 省 略 する 2 FORECAST 関 数 と 同 様 に 予 測 値 を 求 める 関 数 として TREND という 関 数 があるが ここでは 説 明 は 省 略 する - 2 -
5. 統 計 関 数 をもちいたエンゲル 関 数 の 導 出 課 題 7 食 料 住 居 といった 十 大 費 目 について 各 費 目 を E 消 費 支 出 を C とした 回 帰 分 析 E=a+bC をおこない エンゲル 関 数 を 求 めよう 1 2-6 表 の 消 費 支 出 (K14:AB14)を エンゲル 関 数 のシートの D2:D19 セルに 各 費 目 別 の 支 出 (K15:AB24)を エンゲル 関 数 のシートの E2:N19 セルにそれぞれコピーする 貼 り 付 ける 際 に 形 式 を 選 択 して 貼 り 付 け を 選 び 行 列 を 入 れ 替 える にチェック をいれる 2 最 初 に 食 料 のエンゲル 関 数 について 考 える エンゲル 関 数 のシートの H22 セルに a の 推 定 値 を J22 セ ル に b の 推 定 値 を そ れ ぞ れ 求 め る H22 セルに =INTERCEPT(E2:E19,D2:D19) と 入 力 J22 セルに =SLOPE(E2:E19, D2:D19) と 入 力 する 3 住 居 についてのエンゲル 関 数 も 同 様 であるが E2:E19 の 部 分 が F2:F19 になる このよ うにして 十 大 費 目 のエンゲル 関 数 の 表 を 完 成 させよう 6. ローレンツ 曲 線 の 描 画 課 題 8 2010 年 の 年 収 階 級 分 布 について ローレンツ 曲 線 を 描 いてみよう ローレンツ 曲 線 の 横 軸 は 累 積 世 帯 比 率 縦 軸 は 累 積 年 収 比 率 となるので 最 初 にそれらを 計 算 する (1) 累 積 世 帯 比 率 の 導 出 : 累 積 世 帯 比 率 は 累 積 世 帯 数 を 求 め 世 帯 数 の 合 計 で 割 ることによっ て 求 められる 1 2-6 表 の 集 計 世 帯 数 (K13:AB13)のデータを ローレンツ 曲 線 ジニ 係 数 のシートのD3: D20セルにコピーする 貼 り 付 ける 際 に 形 式 を 選 択 して 貼 り 付 け を 選 び 行 列 を 入 れ 替 える にチェックをいれる 2 世 帯 数 のデータの 合 計 をD21セルに 求 める=sum(D3:D20)とすれば 世 帯 数 の 合 計 を 計 算 できる 3 次 にE 列 に 累 積 世 帯 数 を 求 める i. E3セルに =D3 と 入 力 する ii. E4セルに =E3+D4 と 入 力 し それをE5セルからE20セルまでにコピーする これで 累 積 世 帯 数 が 計 算 される 4 最 後 にF 列 に 累 積 世 帯 比 率 を 計 算 する F3セルに =E3/D21 と 入 力 し F4キーを 押 す 3 (す ると =E3/$D$21 となるはず) これをF4:F20セルにコピーする (2) 累 積 年 収 比 率 の 計 算 :2-6 表 のデータは 各 階 級 の 平 均 年 収 である(たとえば K26セルの 158 は 年 収 200 万 未 満 の47 世 帯 の 平 均 が154 万 円 であることを 意 味 している) 最 初 に 各 階 級 の 3 これはセルを 絶 対 参 照 にする 操 作 である Excel は 通 常 そのセルとの 相 対 的 な 位 置 関 係 に 基 づく 相 対 参 照 となっており コピーした 場 合 などは セルの 中 身 が 変 化 していく しかし 絶 対 参 照 では 変 化 しない 私 の 家 の 2 軒 右 隣 りに 酒 屋 さんがあるとする これを 他 人 に 説 明 する 場 合 私 の 家 の 2 軒 右 隣 り というのが 相 対 参 照 で 酒 屋 の 住 所 ( 周 南 市 城 ヶ 丘 丁 目 番 地 )というのが 絶 対 参 照 である - 3 -
年 収 の 合 計 を 求 め 累 積 年 収 累 積 年 収 比 率 を 求 めていく 1 2-6 表 の 年 間 収 入 (K26:AB26)のデータを ローレンツ 曲 線 ジニ 係 数 のシートのG3: G20 セルにコピーする 貼 り 付 ける 際 に 形 式 を 選 択 して 貼 り 付 け を 選 び 行 列 を 入 れ 替 える にチェックをいれる 2 H 列 に 各 階 級 の 年 収 の 合 計 を 求 める 各 階 級 の 年 収 の 合 計 は 世 帯 数 平 均 年 収 で 求 められので H3セルに =D3*G3 と 入 力 し これをH4:H20セルにコピーする そして 全 階 級 の 年 収 の 合 計 をH21セルに 求 める (sum 関 数 を 用 いる) 3 次 にI 列 に 累 積 年 収 を 求 める これは 累 積 世 帯 数 の 場 合 と 同 様 であり i. I3セルに =H3 と 入 力 する ii. I4セルに =I3+H4 と 入 力 し それをI5:I20セルにコピーする これで 累 積 年 収 が 計 算 される 3 最 後 にJ 列 に 累 積 世 帯 比 率 を 計 算 する J3セルに =I3/H21 と 入 力 し F4キーを 押 す (す ると =I3/$H$21 となるはず) これをJ4:J20セルにコピーする (1) (2)により 下 図 のように 計 算 される A B C D E F G H I J 1 階 級 集 計 世 帯 数 累 積 世 帯 数 累 積 世 帯 比 率 年 間 収 入 年 収 総 額 累 積 年 収 総 額 累 積 年 収 比 率 2 0 0 3-200 47 47 0.01121 154 7238 7238 0.00253 4 200-250 95 142 0.03387 224 21280 28518 0.00997 5 250-300 128 270 0.06439 274 35072 63590 0.02223 6 300-350 203 473 0.11281 322 65366 128956 0.04507 7 350-400 227 700 0.16694 374 84898 213854 0.07475 8 400-450 318 1018 0.24279 424 134832 348686 0.12187 9 450-500 305 1323 0.31553 473 144265 492951 0.17230 10 500-550 312 1635 0.38994 524 163488 656439 0.22944 11 550-600 308 1943 0.46339 573 176484 832923 0.29112 12 600-650 288 2231 0.53208 621 178848 1011771 0.35364 13 650-700 274 2505 0.59742 673 184402 1196173 0.41809 14 700-750 230 2735 0.65228 721 165830 1362003 0.47605 15 750-800 204 2939 0.70093 773 157692 1519695 0.53117 16 800-900 383 3322 0.79227 843 322869 1842564 0.64402 17 900-1000 267 3589 0.85595 946 252582 2095146 0.73230 18 1000-1250 363 3952 0.94252 1097 398211 2493357 0.87148 19 1250-1500 139 4091 0.97567 1356 188484 2681841 0.93736 20 1500-102 4193 1.00000 1757 179214 2861055 1.00000 21 計 4193 2861055 累 積 世 帯 比 率 (F 列 )を 横 軸 に 累 積 年 収 比 率 (H 列 )を 縦 軸 にとった 散 布 図 を 描 き 線 でつないだ ものがローレンツ 曲 線 となる なお 列 の 先 頭 に 0 をいれてある 1 最 初 にグラフに 描 く 範 囲 を 範 囲 指 定 する F2:F20をドラッグし Ctrlキーを 押 しながら J2:J20をドラッグして 範 囲 指 定 する 2 グラフを 作 成 するには 挿 入 タブをクリックすることで リボン 内 にグラフのグループが 表 示 される ここでは 散 布 図 のボタンをクリックする 3 すると 散 布 図 グラフのフォーマット( 型 式 )メニューが 出 るので 左 下 の 散 布 図 ( 直 線 とマ ーカー)をクリックする - 4 -
累 積 年 収 比 率 4 この 時 点 でグラフのサンプルが 自 動 的 に 描 かれている これを 修 正 していく まずレイアウ トを 変 更 する リボンの 中 の グラフのレイアウト を 展 開 し レイアウト1( 左 上 )をク リックし タイトルや 軸 ラベルなどが 書 き 込 めるようにする そして タイトル: 年 間 収 入 のローレンツ 曲 線 ( 家 計 調 査 2010 年 ) と 記 入 する X 軸 : 累 積 世 帯 比 率 と 記 入 する Y 軸 : 累 積 年 収 比 率 と 記 入 する 5 さらにいくつかの 細 かい 修 正 を 加 えた ものが 右 図 である 右 図 のようにするに は ⅰ. 凡 例 の 消 去 ⅱ. 軸 の 書 式 設 定 において 最 大 値 の 変 更 ⅲ. 目 盛 線 の 消 去 ⅳ. プロットエリアの 書 式 設 定 に おいて 枠 線 を 単 色 にする ⅴ. タイトルのフォント 変 更 ⅵ. グラフの 大 きさ 変 更 (プロットエ リアを 正 方 形 に 近 づける) 3. ジニ 係 数 の 導 出 0.8 0.6 0.4 0.2 年 間 収 入 のローレンツ 曲 線 (2010 年 家 計 調 査 ) 1 0 0 0.2 0.4 0.6 0.8 1 累 積 世 帯 比 率 課 題 9 2010 年 の 年 収 階 級 分 布 について ジニ 係 数 を 求 めてみよう ジニ 係 数 を 計 算 するには 既 に 述 べたように 台 形 の 面 積 を 足 し 合 わせたものを 2 倍 して 正 方 形 の 面 積 1 から 引 けばよい ここで 台 形 の 面 積 は ( 上 底 + 下 底 ) 高 さ 2 という 公 式 で 求 まるが 上 底 1 つ 前 の 階 級 までの 累 積 年 収 比 率 下 底 その 階 級 までの 累 積 年 収 比 率 高 さ その 階 級 までの 累 積 世 帯 比 率 から 1 つ 前 の 階 級 までの 累 積 世 帯 比 率 を 引 いたもの となる K 列 に 台 形 の 面 積 を 求 め その 2 倍 の 合 計 を 1 から 引 くことによって ジニ 係 数 を 求 める 1 K3セルに =(J2+J3)*(F3-F2)/2 とする( 上 底 がJ2 下 底 がJ3 高 さがF3-F2である) 2 K3をK4:K20セルにコピーし K21セルに 合 計 を 求 める 3 K23セルに =1-K21*2 と 入 力 する これがジニ 係 数 である 本 日 実 習 したファイルは 河 田 まで 提 出 すること ( 講 義 時 間 中 にできなかったものもおこな い 完 成 させること) 提 出 は 1. Webclass 経 由 2. E-mail に 添 付 ファイルとして( 送 付 先 アドレスは kawada@tokuyama-u.ac.jp) のいずれでもかまわない 締 め 切 りは 11 月 28 日 ( 月 )10:40 とする なお ファイル 名 は 家 計 調 査 の 分 析 H22 E40- のように 学 籍 番 号 をつけること - 5 -