データの準備から丁寧に学べる、Excelを使った回帰分析の方法- Schoo PENCIL

2022.02.22

データの準備から丁寧に学べる、Excelを使った回帰分析の方法

未来は、予測できるでしょうか？

──それを可能にするのが、統計学の力です。皆さんも「回帰分析」という言葉を一度は聞いたことがあるのでは？

『「統計学を仕事に活かす」-文系もExcelで実用できる統計-』は、「7日間集中講義！Excel統計学入門（オーム社）」「ビジネスマンのためのデータ分析＆活用術（フォレスト出版）」などの著者、米谷学先生を講師としてお招きしたSchooの授業シリーズ。

第3回『主観に頼らない「Excelを活用した売上予測」』では「予測」をテーマに、「売上要因をデータ化して予測を立てる単回帰分析」「予測との向き合い方」の2つについてレクチャーがなされました。

本記事では、授業の一部を抜粋してテキストベースでお届けします！

広告宣伝費と売上高の関係を探るには？

△【画像クリックでSchoo授業動画へ】（会員登録で1時間無料視聴できます）

授業の冒頭では第2回と同様、リアルタイム受講生への問いが用意されていました。広告宣伝費と売上高との関係を探る問題です。

月ごとの広告宣伝費（→の前）と売上高（→の後）の関係は、以下の通り。

8月：8万円→400万円
9月：9万円→700万円
10月：10万円→1,000万円
11月：11万円→1,300万円

問いは以下の2つです。

①9万円使った場合の売上高は？
②16万円使った場合の売上高は？

正解は、ぜひ実際の授業動画で確認してみてください。この問題は正解を出すためというよりも、統計学の予測に対する考え方の基礎をレクチャーするための下準備のようなものです。

データの準備と相関関係を探る

それでは授業1つ目のセクション「データの準備と相関関係を探る」に進みましょう。売上予測までの第一歩として「予測の目的の明確化・共通理解」がまず挙げられます。何のために予測するのか、何のために知りたいのかを押さえることがその後の検討の方向性を決定づけます。次に押さえたいのが「予測したい項目」を明確化し共有すること。ここからが具体的に本授業に関わる部分です。

・「散布図」を描き、「相関係数」を求めて、予測したい項目の増減の要因を探る

この部分については、第2回の授業で詳しく取り上げられました。例えば先ほどの例で取り上げた広告宣伝費と売上高に何らかの相関があるのであれば、それを予測に利用することが可能になります。

△【画像クリックでSchoo授業動画へ】（会員登録で1時間無料視聴できます）

続いてのステップは以下。

・相関関係があることを利用して「回帰分析」で予測の式を求める

「実際は予測はやりっぱなしじゃいけない」と米谷先生。要因や予測手法の見直しも含め、日常業務を通じての絶え間ない改善・検証が求められるということです。

なお、分析の対象となる2変数は、以下のような項目から抽出することになります。

・注目している項目と連動しているほかの項目
・結果にあたる項目と原因にあたる項目
・後に起こることと先に起こること

用意された例では、売上高の予測に生かすという目的のもと、給料手当、広告宣伝費、旅費交通費などさまざまな変数から最も相関係数が強いと見込まれる一つを選び、実際の予測に生かすまでの手順が解説されました。

△【画像クリックでSchoo授業動画へ】（会員登録で1時間無料視聴できます）

売上高を予測する式の例は、たとえば以下のような一次関数で表されます。

売上高（予測）＝ y ＝ 0.2444x ＋ 14810

※……xは広告宣伝費、yの単位は千円

この場合の0.2444はxが1増えたときにyが増加する量を、14810はxが0のときのyの値を意味します。例えば広告宣伝費を70000円かけたときの売上高は、「0.2444*70000 ＋ 14810 ＝ 31918千円（3191万8,000円）」と予測されます。ただし、この計算式はあくまで取得されたデータの範囲内にのみ当てはまるものであり、たとえば広告費が0円の場合「0.2444*0 ＋ 14810　＝ 14810千円（1481万円）」の売上が立つと予想されるわけではないということは押さえておきましょう。データの範囲外の予測の事を統計学では「外挿」と呼びます。また、0.2444という数値もあくまで予測値であり、必ず確保できるものではありません。