小 売 店 舗 密 度 レポート Excel での 作 成 について ( 以 下 の 説 明 は Excel のバージョンの 違 いによって 少 し 異 なる 場 合 がある ここでは J 棟 PC 教 室 のPC に 入 っている EXCEL2013 での 操 作 が 書 いてある ) 1 http://www.nanzan-u.ac.jp/~minamik/report-minamik.xls にアクセスしてこのExcel ファイルを ダウンロードして まず 自 分 のUSB などに 保 存 する 保 存 したそのファイルを 開 いて 作 業 する 2 ファイルの(Sheet1)を 開 らける このとき リボンの[データ]に 分 析 グループ[データ 分 析 ]が 表 示 されているか 確 認 する( 下 の 図 の 赤 丸 や 赤 い 矢 印 ) 表 示 されていない 場 合 は (すでに 表 示 さ れている 場 合 は 以 下 の 説 明 は 飛 ばしてください 3ページの3に 進 む ) 表 示 されていない 場 合 は このままでは 分 析 ツールを 使 えない ので アドインを 組 み 込 む 必 要 があ ります 以 下 は その 方 法 です リボンの[ファイル]をクリックし ます リボンの[ファイル]をクリックします 1
左 メニューの[オプション]をクリックします Excel のオプションダイアログが 開 きます 左 パネルの[アドイン]をクリックし Microsoft Office のアドインの 表 示 と 管 理 を 行 います の 最 下 部 の[ 設 定 ]をクリックします アドインダイアログが 開 くので[ 分 析 ツール]のチェックをON し[OK]ボタンをクリックし 完 了 です リボンの[データ]に 分 析 グループ[データ 分 析 ]が 追 加 できた 2
3(Sheet1)を 開 らける [データ 分 析 ]をクリックする [データ 分 析 ]の[ 基 本 統 計 量 ]を 選 んでOK を 押 す 入 力 範 囲 の 空 欄 の 右 にあるボタンを 押 して データシートのうえでセルB1 からG18 までの 範 囲 を 選 択 し もういちどボタンを 押 して 下 のように 空 欄 が $B$1:$G$18 と 記 入 されているようにする さらに 先 頭 行 をラベル にチェック 統 計 情 報 にもチェックを 入 れて OK 押 す 4 出 力 結 果 は 以 下 のとおり DOR PD FAD DOD PCP CVP 平 均 8.435353 平 均 3.497647 平 均 1.045118 平 均 25.26918 平 均 0.235647 平 均 0.048706 標 準 誤 差 0.703661 標 準 誤 差 2.51715 標 準 誤 差 0.056479 標 準 誤 差 6.606858 標 準 誤 差 0.041512 標 準 誤 差 0.010073 中 央 値 (メジアン) 7.637 中 央 値 (メジアン)0.54 中 央 値 (メジアン) 0.953 中 央 値 (メジアン) 16.392 中 央 値 (メジアン) 0.286 中 央 値 (メジアン) 0.041 最 頻 値 (モード) #N/A 最 頻 値 (モード) 0.25 最 頻 値 (モード) #N/A 最 頻 値 (モード) #N/A 最 頻 値 (モード)0.018 最 頻 値 (モード) #N/A 標 準 偏 差 2.901267 標 準 偏 差 10.37848 標 準 偏 差 0.23287 標 準 偏 差 27.24077 標 準 偏 差 0.171156 標 準 偏 差 0.041531 分 散 8.417353 分 散 107.7128 分 散 0.054228 分 散 742.0598 分 散 0.029294 分 散 0.001725 尖 度 2.870163 尖 度 16.51238 尖 度 -0.48888 尖 度 4.123849 尖 度 -1.283 尖 度 2.30068 歪 度 1.781607 歪 度 4.041123 歪 度 0.814669 歪 度 2.208477 歪 度 -0.0441 歪 度 1.629112 範 囲 10.386 範 囲 43.51 範 囲 0.74 範 囲 96.037 範 囲 0.525 範 囲 0.15 最 小 5.843 最 小 0.02 最 小 0.796 最 小 0.775 最 小 0.01 最 小 0.007 最 大 16.229 最 大 43.53 最 大 1.536 最 大 96.812 最 大 0.535 最 大 0.157 合 計 143.401 合 計 59.46 合 計 17.767 合 計 429.576 合 計 4.006 合 計 0.828 標 本 数 17 標 本 数 17 標 本 数 17 標 本 数 17 標 本 数 17 標 本 数 17 3
これには 不 要 な 統 計 量 が 含 まれているので 次 の4つ( 平 均 標 準 偏 差 最 大 最 小 )だけでいい DOR PD FAD DOD PCP CVP 平 均?????????????????? 標 準 偏 差?????????????????? 最 小 値?????????????????? 最 大 値?????????????????? 5(Sheet1)のデータシートにもどって 同 じく [データ 分 析 ]をクリック 今 度 は 相 関 でOK 同 じく 入 力 範 囲 の 空 欄 を $B$1:$G$18 として 先 頭 行 をラベル にチェック OK 出 力 結 果 は 次 のとおり 小 数 点 以 下 の 桁 数 が 多 すぎるので DOR PD FAD DOD PCP CVP DOR 1 PD -0.16073 1 FAD -0.41268 0.395802 1 DOD -0.23782-0.26397 0.236045 1 PCP -0.20442-0.21084 0.26846 0.113329 1 CVP 0.331826-0.01295 0.340539 0.327126 0.552531 1 表 示 桁 数 を 減 らすには [ホーム]メニューのなかの 数 値 のグループのなかに.00.0 がある 表 示 桁 数 を 減 らしたいセルを 選 択 して.00.0 のボタンを 押 していくと ケタが 減 っていく 6 また(Sheet1)データシートにもどって y 座 標 をB 列 として x 座 標 をC 列 かD 列 かE 列 か F 列 かG 列 か のどれでもいいので 選 んで 散 布 図 を 描 く たとえば D 列 をx 座 標 と して 散 布 図 を 描 く 場 合 は B 列 とD 列 とを 同 時 に 選 択 しておく ( 先 頭 行 を 含 めて) 4
互 いに 離 れている2 つの 列 を 同 時 に 選 択 するには B 列 を 選 んだときに[Ctrl]キー(キーボードの コントロールキー)を 指 で 押 しっぱなしにしておいて そのままD 列 を 選 択 する そして [ 挿 入 ]メニューのなかの 散 布 図 ( 下 の 説 明 の 赤 い 丸 )をクリックすると 5 種 類 ( 赤 い 四 角 ) の 散 布 図 が 出 てくる そのうち 左 上 のいちばん 普 通 の( 赤 い 丸 の)をクリックすると 以 下 の 図 が 出 る しかし この 散 布 図 はy 座 標 がC 列 で x 座 標 がB 列 となり 逆 になってしまっている (Excel では 左 列 をx 軸 右 列 をy 軸 にして 自 動 的 に 描 いてしまう ) ただしく y 座 標 がB 列 で x 座 標 がC 列 となるように 座 標 を 逆 にするためには 次 のよ うにする 散 布 図 のなかのどの 点 でもいいので 点 のところにポインターを 置 いて そこで 右 クリックする そこの データの 選 択 を 選 ぶ 5
編 集 (E)をクリックすると 系 列 x がB 列 系 列 y がC 列 ではなく 系 列 x がC 列 系 列 y がB 列 にする ここの 文 字 B を2つともC に 書 き 換 える ここの 文 字 C を2つともB に 書 き 換 える 系 列 の 編 集 ができたらOK を 押 して データソースの 選 択 に 戻 ったら OK を 押 す 以 下 のような 散 布 図 がいちおう 作 成 されるが シンガポール のプロット PD と DOR の 散 布 図 の 場 合 1 個 だけ(シンガポール)が 極 端 に 他 と 離 れている( 異 常 値 )ので これだけ 取 り 除 いて 散 布 図 を 描 くとすると 散 布 図 のヨコ 軸 の 線 上 のどこかで 右 クリックをすると 以 下 のように 軸 の 書 式 設 定 が 出 てくる 6
最 大 値 が 50 になっているのを 4 と 入 力 して 変 更 させる Enter を 押 す 同 様 に 他 にも FAD と DOR の 散 布 図 など 同 じように 作 成 する 20.000 FAD 20.000 FAD FAD 線 形 (FAD) 15.000 15.000 10.000 5.000 0.000 0.000 0.500 1.000 1.500 2.000 FAD 10.000 5.000 0.000 0.000 0.500 1.000 1.500 2.000 各 々の 点 (プロット)をなぞるように 通 過 する 直 線 を 引 いてもよい それ( 近 似 直 線 )を 引 くには 散 布 図 のなかのどの 点 でもいいので 点 のところにポインターを 置 いて そこで 右 クリックする 近 似 曲 線 の 追 加 をクリック 7
ほかに タイトル( 見 出 し)をクリックして DOR( 縦 軸 )とPD( 横 軸 )の 散 布 図 というふうに タイトルを 分 かりやすく 書 き 換 えるのもよい Excel のシート 上 で このように 散 布 図 ができれば それ らをコピーしてきて Word で 作 成 するレポート 課 題 の 本 文 中 にペース トする 7 回 帰 分 析 また(Sheet1)というデータシートにもどって 例 えば H2 のセルを 選 択 して ( 入 力 モードをひらがなではなく 直 接 入 力 にして) =ln(b2) と 入 力 する そして Enter キーを 押 す ( ln は エル エヌ ) 8
これは B2 のセルの 値 6.68 を 対 数 に 変 換 したものが 1.899 ということ log 6.68 = 1.899 H2 のセルを 選 択 して セルの 外 枠 の 右 下 の 角 にポインターを 重 ねると ポインターが 黒 い 十 文 字 の かたちになる そのときにマウスを 左 クリックしたままH 列 を 下 方 向 にドラッグすると 自 動 計 算 コピー される さらに 横 にも 自 動 計 算 コピーする 1 行 目 には 変 数 名 を 記 入 しておく B1 からG1 のみだしをコピーアンドペーストしてくればいい 以 下 の 回 帰 分 析 では H1 からM18 までのデータを 使 っていく 8 回 帰 分 析 [データ]タブをクリック 右 にある[データ 分 析 ]をクリック 回 帰 分 析 を 選 ぶ 下 のほうに 見 ていくと 回 帰 分 析 がある 9
範 囲 の 空 欄 の 右 のマークをクリックして データシート 上 で セルH1 からH18 まで を 選 択 して またマークをクリックすると 入 力 Y 範 囲 の 空 欄 に $H$1:$H$18 と 入 力 される 同 様 に 入 力 X 範 囲 の 空 欄 に $I$1:$M$18 と 入 力 する ラベルに をしてOK を 回 帰 分 析 を 実 行 するには OK をクリックする 新 規 ワークシートに 回 帰 分 析 の 結 果 が 出 力 される レポートでは 出 力 結 果 の 一 部 分 をつかう 下 の??? にあたる 数 値 だけを 読 み 取 っておく 係 数 標 準 誤 差 t 値 P - 値 切 片???????????? P D???????????? F A D???????????? D O D???????????? P C P???????????? C V P???????????? 10
----------------------------------------------------------- レポートのみほん ----------------------------------------------------------- 流 通 論 A レポート 課 題 2016/5/27 2015BB999 南 山 花 子 基 本 統 計 量 DOR PD FAD DOD PCP CVP 平 均?????????????????? 標 準 偏 差?????????????????? 最 小 値?????????????????? 最 大 値?????????????????? 相 関 行 列 DOR PD FAD DOD PCP CVP DOR 1 PD??? 1 FAD?????? 1 DOD????????? 1 PCP???????????? 1 CVP??????????????? 1 散 布 図 18.0 DOR 16.0 14.0 12.0 10.0 8.0 6.0 4.0 2.0 回 帰 分 析 0.0 0.0 0.5 1.0 1.5 2.0 FAD 係 数 標 準 誤 差 t 値 P- 値 切 片???????????? PD???????????? FAD???????????? DOD???????????? PCP???????????? CVP???????????? 回 帰 分 析 の 結 果 の 読 み 取 り PD のP- 値???は,???<???<???,だから,PD については???% 水 準 で 有 意 である したがって, 人 口 密 度 が 大 きい 国 ほど 小 売 店 舗 密 度 は???になる FAD のP- 値 は(ほぼ)???だから,FAD については???% 水 準 で 有 意 である 11
DOD のP- 値???は,???<???<???,だから,DOD については???% 水 準 で 有 意 である したがって,DOD が 大 きい 国 ほど 小 売 店 舗 密 度 は???になる PCP のP- 値 は(ほぼ)???だから,PCP については???% 水 準 で 有 意 である CVP のP- 値???は,???<???,だから,PCP については???% 水 準 で 有 意 である したがって,CVP が 大 きい 国 ほど 小 売 店 舗 密 度 は???になる 回 帰 式 ( 推 定 式 ) PD,FAD,DOD,PCP,CVP,の 各 説 明 変 数 と, 各 々についての 回 帰 係 数 の 推 定 値 (および 切 片 の 値 の 推 定 値 )とを 用 いて, 小 売 店 舗 密 度 ( 被 説 明 変 数 )DOR についての 回 帰 式 がどのように 書 けるか 示 しな さい (また, 回 帰 係 数 の 値 の 下 に 括 弧 をつけて,そのt 値 を 書 き 加 えること また,0.1% 水 準 で 有 意 な 説 明 変 数 には,その 説 明 変 数 の 右 上 付 きで,***を 付 ける 1% 水 準 には**,5% 水 準 には*,10% 水 準 には を 付 ける ) ( 授 業 での 配 布 プリントの35 枚 目 のスライドの 推 定 式 の 書 き 方 を 参 照 のこと ただし 回 帰 係 数 の 値 は あなたが 実 際 に 出 した 数 値 と 違 っているはずである ) ------------------------------------------------------ レポートのみほんは 以 上 ---------------------------------------------------- レポート 課 題 締 切 : 5 月 27 日 ( 金 )の 授 業 終 了 時 1 http://www.nanzan-u.ac.jp/~minamik/report-minamik.xls のファイルをダウンロードして, 自 分 の USB などに 保 存 する 保 存 したそのファイルを 開 いて 作 業 する 2 各 国 の 小 売 店 舗 密 度, 住 戸 あたり 床 面 積 等 のデータ を 使 って, 見 本 のような 内 容 と 同 じ 作 業 をし たものをまとめる 基 本 的 には, 見 本 の 各 々の 項 目 (データ, 基 本 統 計 量, 相 関 行 列, 散 布 図, 回 帰 分 析 ) について,Excelでの 作 業 結 果 を カット&ペースト して, 図 表 を 作 成 する 回 帰 分 析 結 果 の 読 み 取 り は 回 帰 分 析 結 果 の 表 (P- 値 の 大 きさ)から, 各 変 数 の 推 定 係 数 の 有 意 性 について 読 み 取 って 書 く 書 き 方 の 細 かい 体 裁 には 取 り 決 めは 特 にもうけないので, 各 自 の 自 由 裁 量 にまかせます 3 A4 かA 3 判 用 紙 印 刷, 複 数 の 枚 数 の 場 合 にはホチキス 止 めが 必 要, 表 紙 は 不 要 締 め 切 り 日 までの 授 業 時 間 内 に 南 川 に 手 渡 し 教 務 や 事 務 では 受 け 取 らないので 注 意 メール 添 付 ファイルでの 提 出 は 不 可 12