“学びたい”を刺激するメディア。

2022.02.22

column

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

データの準備から丁寧に学べる、Excelを使った回帰分析の方法
動画:「主観に頼らない「Excelを活用した売上予測」」より

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

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

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

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

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

担当の先生

米谷 学 先生

Excelで統計学を教える研修講師

目次

  • 広告宣伝費と売上高の関係を探るには?
  • データの準備と相関関係を探る
  • Excelで相関関係を探り予測の値を求める

 

 

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

 

 

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

 

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

 

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

 

問いは以下の2つです。

 

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

 

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

 


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

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

 

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

 

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

 

 

 

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

 

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

 

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

 

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

 

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

 

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

 

 

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

 

売上高(予測)= 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という数値もあくまで予測値であり、必ず確保できるものではありません。

 


Excelで相関関係を探り予測の値を求める

さて、ここからは「Excelで相関関係を探り予測の値を求める」と題し、実際にExcelを使って回帰分析のデモを行っていきます。

 

最初に、Excelで範囲指定した中に、数値以外の入力や空白のセルがある場合は、そこにはデータがないものとして扱われるという注意点を押さえておいてください。

 

呼び出されたExcelの画面には、「年月」「給料手当」「広告宣伝費」「旅費交通費」「交際費」「租税公課」「売上高:千円」という7項目がタイトルに冠された月次データの表が表示されています。

 

 

 

ここで、「売上高」とそれ以外の「相関係数」を調べるべく、CORREL関数を使った分析が行われました。適当なセルを選んで「=CORREL()」と入力し、カッコの中で任意のセルを指定します。

 

最初に入力されたのは、以下の数式でした。

 

=CORREL($G$2:$G$25,F2:F25)

 

「$」はExcelにおいてセルの固定を意味し、何度も使う範囲指定をコピペして使う場合などに役立ちます。「F4」キーのショットカットで呼び出せますので、ぜひ活用して、時短に生かしましょう。

 

上記の関数を実行した結果は「0.7760379」。F列は「租税公課」にまつわるデータを格納しているため、売上高と租税公課にはある程度の相関関係がありそうということがわかります。

 

 

 

続いて、「給料手当」「広告宣伝費」「旅費交通費」「交際費」との相関係数も調べますが、もういちいち関数を手入力する必要はありません。先ほど売上高の範囲の指定は固定していたため、セルの右下のフィルハンドルをクリックし、そのままドラッグするだけでそれぞれに合わせた範囲指定で計算を行わせることができます。

 

結果としてそれぞれの相関係数は以下の通りでした。

 

給料手当と売上高:0.0001785
広告宣伝費と売上高:0.9000385
旅費交通費と売上高:0.0511204
交際費と売上高:-0.725114

 

よって、相関係数が「0.9000385」の広告宣伝費と売上高の相関が最も強いと推測することができます。

 

さて、それでは、広告宣伝費をいくら使ったら売上高がいくらになるかの予測を回帰分析で行っていきましょう。そこで使うのが「FORECAST関数」です。
70000円の広告宣伝費を利用した場合の予測を行うと仮定して米谷先生は以下の数式を記述しました。

 

=FORECAST.LINEAR(B26,C2:C25,B2:B25)

 

B26には「70000」と予測に用いる広告宣伝費の金額が記述されています。「C2:C25」は広告宣伝費、「B2:B25」は売上高のデータです。結果として、指定のセルに入力されたのが「31921.63588」。売上高は3192万1635.88円と予測されました。

 

 

 

回帰分析と聞くと難易度が高そうに感じていた方も、意外と簡単な数式で実行できることに驚いたのではないでしょうか。まだわからないという方も、授業動画を確認しつつ実際に手を動かせば理解はきっと深まるはずです。ぜひ、シリーズの過去動画も含めて、アーカイブを参照してみてください!

 

文=宮田文机

今回取り上げたSchooの授業はこちら!
『主観に頼らない「Excelを活用した売上予測」』

 

今日の生放送

おすすめ記事

ペンシルからのプッシュ通知を設定しておくと、新着記事のお知らせなどをブラウザ上で受信できて便利です。

通知を受信しますか?