まえがき この度は Excel でできる統計 データ分析講座 をご受講いただき まことにありがとうございます 人間万事塞翁が馬 ということわざがあります 世の中で起きることは 良いことも悪いことも なかなか予測できないものです しかし将来を予測する場合でも 過去の傾向や現状を基にすれば 手がかりが有るかもしれません 人間の経験からくる勘に基づく判断は無視できません ただここで重要なのは 過去の傾向や現状を 客観的に探ることも必要です そして 数値による裏づけを基に将来の予測をしたり 次なるアクションをとったりすることができれば 経験や勘だけによる判断から より確証ある裏づけに基づく判断に変わります この講座は Excel を使ってビジネスデータを活用し 意思決定に活かすことがテーマです あくまで Excel はツールです 東京から大阪へ出張に行くことを例に挙げれば 移動手段が新幹線 飛行機 夜行バスなど様々あり それぞれ所要時間や料金などにおいてメリットやデメリットがある中で 何時にどの交通手段で移動をするのかを決めます これと同じく Excel はただの手段であり Excel の操作を覚える段階で苦労を強いられることは望んでいません 自己責任 や 危機管理 という言葉を耳にする機会が増えて久しくなりました 会社の経営で考えれば 経営者や所属長の勘や経験だけを頼りに予測や判断をした時 うまくいっている時は良いのですが その人の欠勤ばかりでなく 異動や退職をした場合 残された人は従来と同じように判断ができるでしょうか またその勘を頼りにした判断の方法を 的確に後任者や部下へ継承することができるのでしょうか また従業員の退職による従業員固有のノウハウの流出は 会社としては避けたいものです 情報共有しやすいツールである Excel を使って 誰が作業をしても同じような解釈ができるよう すべての人がスキルを持てば 従業員全体が高いレベルで知識の均整化が図られます 会社の経営資源という面でも 大変意義があると言えます このことを意識しながら 講座の内容を習得して下さい そして演習問題にもチャレンジして下さい もし不明な点があれば どしどしご質問をお寄せ下さい 最後になりましたが 本書執筆は 家族など大切な人をはじめ 多くの方の協力が有り完成しました 共著者の鈴木義郎君をはじめ 協力なさった皆さんに厚く御礼を申し上げます またこれまでに公開セミナー 企業研修や通信講座 共著書等を通じて寄せられたご質問も 執筆のヒントになっています 本当にありがとうございました 2014 年 6 月監修 著者 : 米谷学
データの抽出に役立つ機能 ここから E x cel の演習に入ります データを扱うときのポイントの1 つである データの抽出に役立つ機能能 フィルタ機能 や V L OOKUP 関数 を理解しましょう 特に VLOOO KUP 関数は利用価値の高い関数です この章のねらい Excel の演演習を通じて フィルタ機能能を理解しましょう Excel の演演習を通じて VLOOKUP 関数を理解しましょう データの抽出に役立つフィルタ機能 Excel による主なデータの抽出方法を考えてみます ここでは 次の2つを採り上げます 1 フィルタ機能 特定の値や条件のデータのみを抽出 ( 表示 ) させる 2 VLOOKUP 関数 特定の値に紐付けされた情報を呼び出すまずはフィルタ機能から説明します フィルタ機能とは 指定する条件に該当するデータのみをシートに表示させる機能です このとき 表示されなかった情報は削除されるのではなく ただ 非表示 になるだけです なおこの非表示になったデータは無視され 表示されたデータのみを 他のワークシートなどにコピー / 貼り付け( ペースト ) させることが可能です 次の表は 136 名の顧客の居住地 職業 性別 年齢 過去のキャンペーン実施時に来店したかどうかをまとめたものです( 抜粋 ) 第 Ⅰ講演習用データ.xlsx ファイル 01_ フィルタ 1 シート Copyright 2014 Man abu Yoneya All rights reserved. 著者 販売者者に無断で 転載 複写 転売はご遠慮下さい
この表から Excel のフィルタ機能で 居住地は 京都 年齢は 30 歳以下 のみを抽出すると 次のようになります それでは このようにデータを抽出する操作に入ります 本来フィルタ機能は 表の範囲のうちいずれかの任意のセルを 1 か所指定していれば良く 表の範囲外を指定すると 次のようにエラーメッセージが表示され フィルタ機能を実行できません 今回 B 列はナンバリングの列なので 本来 処理の対象にする必要はありませんが 操作の便宜上 表の範囲のうち 任意のセルを選択した状態で 操作を始めます ( 但しこのとき表の周囲には スペースを含め 選択した範囲のデータとは無関係な文字等は入力されていないこと ) しょうじゅんなお特に顧客 ID が初期状態で昇順 ( 少ない値から多い値の順 ) で並んでいる場合は良いのですが それ以外のルールに基づいてナンバリングされている場合などは Excel で並べ替え操作をした後でも 元の状態に復元できるよう 別途ナンバリング列を設けることをお勧めします なおここでは 京都在住の 30 歳以下のデータのみを抽出する操作を行います 1 データ タブの 並べ替えとフィルター グループから フィルター ボタンをクリックします フィルタのマークが範囲選択された各列に表示されました
複数の項目の情報を基に 予測をする ( 重回帰分析 ) 分析の背景 仮説次の表は ある小売チェーン店 20 社の総売場面積 店舗数と合計売上高を示した表です 20 社のデータで見られる売上高は 総売場面積と店舗数に応じて増減するだろう という仮説の下 No. 1~ 20 のデータを基に 総売場面積と店舗数により新たな 21 社目の売上高を予測する式を求めます また売上高に より影響を及ぼしている要因 ( 総売場面積か店舗数 ) はどれかを併せて分析します ここでは 予測をしたいまたは注目している項目に対して 複数の数値項目の関連を基に分析を行います このように複数の数値項目を一度に分析し 関連や傾向を探ることを 多変量解析と呼び 回帰分析は多変量解析の中で代表的な分析手法の1つです そして総売場面積や店舗数の数値を基に 売上高を予測し要因分析を行うことが 回帰分析の目的です まず予測式を求めるためめに回帰分析を実行します 入力 X 範囲 にあたる列が2つ以上有る場合 散布図で予測式を求めることができません Excel の分析ツール 回帰分析 を利用します 回帰分析の実行 第 Ⅱ講演習用データ.xlsx ファイル 5.3.2 回帰分析 シート要領は 最高気温とアイスクリームの売上個数の事例と同じですが X 範囲と Y 範囲の指定を間違えないようにしましょう 入力 X 範囲 (X) には 総売場面積 と 店舗数 のデータ列である C2 セルから D222 セルを マウスでドラッグして範囲選択をします 設定が済んだら OK をクリックします Copyright 2014 Man abu Yoneya All rights reserved. 著者 販売者者に無断で 転載 複写 転売はご遠慮下さい
回帰分析を実行した結果は次のようになりました 予測式を求める上記の回帰分析実行結果より 予測式を作ります 注目する点は 切片と回帰係数の部分です 売上高 = 2,149 + 48.283 総売場面積 + 15.203 店舗数 要因分析をする 第 Ⅱ講演習用データ.xls ファイル 5.3.4影響度 シートさて 回帰分析の実行結果から 総売場面積と店舗数のうち どちらの項目がどの程度 総売上高に影響を及ぼしているかを探ることができます それには 影響度を求めます Copyright 2014 Man abu Yoneya All rights reserved. 著者 販売者者に無断で 転載 複写 転売はご遠慮下さい
影響度 : には t 値の絶対値の大きい順に影響を及ぼしている と判断します 切片の t 値は この分析には無関係なので 無視します 統計学的には 偏回帰係数 という指標を使って 影響度を求めますが Excel ではサポートしていません また t 値を使う方が 精度が良いとされていますので ここでは偏回帰係数の説明は省略します 絶対値とは 正の値 (0 より大きい値 ) の場合はそのままの値 負の値 (0 より小さい値 ) の場合は マイナスの記号を取り払った時の値を表します Excel では ABS 関数が絶対値を求める関数です それでは 説明変数の中で t 値を比較してみましょう 大小の比較をグラフで表すには 棒グラフで表します 数式 0-1 回帰分析実行結果から t 値を抜粋 図 0-1 影響度 影響度 18 16 14 12 10 8 6 4 2 0 総売場面積 店舗数 総売場面積の方が より売上高に影響を及ぼしていることがわかります t 値と P 値の統計学上の原則 目的変数への影響度は t 値の絶対値の大きさで判断することを説明しました t 値は回帰係数 ( 隣の ) 標準誤差で求めています また P 値 (Excel では P- 値 と表記 ) とは 回帰係数が 0 である つまり説明変数の回帰係数には意味が無いという仮説 ( 帰無仮説 ) を基に 0 を中心とした t 分布上の両側確率を求めたものです t 値を基に Excel の関数を使って T.DIST.2T 関数 (Excel2007 までの場合は TDIST 関数 ) で P 値を求めることもできます 付録 無相関の検定 を参照 ) 先人の慣例により 有意水準は 5% とすることが多く このとき P 値が 0.05 未満だとその説明変数の回帰係数は有意であると判断することが多いです 実際の重回帰分析において しかし説明変数が 2 つ以上の回帰分析では 説明変数を組み合わせることにより 目的変数を説明するということに意味があり また説明変数の組み合わせ方によって t 値や P 値は変化します つまり個々の説明変数が有意かどうかよりも 説明変数がどのような組み合わせならば回帰式として最適かを考えることを優先しなくてはなりません
有意水準を 5% としたとき P 値が 0.05 以上となった説明変数のことを 有意ではない と表しますが その有意ではない説明変数が回帰式に無くても良いということではありません