YouTubeチャンネルを開設しました!

Excelで予測!気温が○度だとアイスクリームは○円売れる?【回帰分析】

Excelで予測!気温が○度だとアイスクリームは○円売れる?【回帰分析】

スポンサードリンク



商売をしていると。ある「A」という数値の変化に対して、「B」という数値がどのように変化するかを予測したい、ということがあります。

そこで、「気温が○度だとアイスクリームは○円売れる?」を例に、Excelの回帰分析を使った予測についてお話をします。

気温が○度だとアイスクリームは○円売れる

商売をしていると。ある「A」という数値の変化に対して、「B」という数値がどのように変化するかを予測したい、ということがあります。

たとえば、自社が「アイスクリーム」を売っているとして。「気温が上がるとアイスクリームが売れる気がするのだけれど、気温が25度だとしたらどれくらい売れるのだろう? 26度だったら…?」と、考えているようなケースです。

この場合、ある数値Aが「気温」で、数値Bが「アイスクリームの売上高」です。気温の変化に対して、アイスクリームの売上高の変化を予測したい。

というわけで。予測をするためのデータとして、次のデータを集めました ↓

  • 過去3年間月別の1世帯あたりアイスクリーム支出額(総務省・家計調査より)
  • 過去3年間月別の東京の平均気温(気象庁・過去の気象データ検索より)

ここでは、1世帯あたりの「アイスクリームの支出額」のデータを使っていますが。本来は、自社の「アイスクリームの売上高」の実績データを使うべきところです。便宜的に、ということでご了承願います。

また、自社は東京でアイスクリームを売ってるという前提で、東京の平均気温を見ることとします。

これらデータをExcelで並べてみたものがこちらです ↓

平均気温・アイスクリーム支出額 データ

上記のデータを見ながら、気温の変化に対して、アイスクリームの売上高(データとしては1世帯あたりの支出額)の変化をExcelで予測してみましょう。

ちなみに。似たようなケースでの予測はいろいろと想定できます。たとえば、

  • 会員加入期間が伸びると、客単価はどれだけ伸びるのか?
  • 競合店の販売価格が下がると、自店の同一商品の販売数量はどれだけ下がるのか?
  • ある広告を増やしたときの費用に対して、売上高はどれだけ伸びるのか?

なども同じように予測ができますので、いろいろな場面で役立つことでしょう。

本記事では、Excel 2016 を使っての説明になります。Excel 2013、2010でも同様に回帰分析は使えます。

 

スポンサードサーチ

Excelの「回帰分析」で予測する

冒頭のアイスクリームの件、Excelで予測をしていきます。具体的には、Excelの「回帰分析」という機能を使います。

一見難しそうですが、ポイントを絞ってのお話ですからご安心を。

手始めに散布図を描いてみる

本題の「回帰分析」についてお話をする前に。さきほどの「気温」と「アイスクリームの支出額」のデータを使って「散布図」を描いてみましょう。

散布図の描き方はカンタンです。

まずは、散布図のもとになるデータを選択します。今回のケースでは、数字が入力されている「B2」のセルから「C37」のセルまでを選択します。

そのうえで、Excelの上部にあるリボン(メニュー)から「挿入」を選択。そのあと、下図の箇所にある「散布図」を選択します ↓

散布図の作成

すると、次のような「散布図」が描けるはずです ↓

散布図

上図は、縦軸が「アイスクリームの支出額」で、横軸が「気温」です。3年分(36ヶ月分)のデータとして 36個の点がプロットされています。

これを見ると、「気温」が上がるにつれて、「アイスクリームの支出額」が伸びているようすがわかるでしょう。言い換えると、「赤色の点線」のまわりに 36個の点が集まっている、ということです。

なお、赤色の点線は、目安としてわたしが引いた線になります。言わば、なんとなく引いた線ですが、「なんとなく」ではなく、「明確」に線を引くことができれば予測に役立ちそうです。

そこで、このあとの「回帰分析」のお話へと続きます。

Excelで回帰分析をしてみる

回帰分析とはなんぞや? というお話は他へゆずるとして。まずはさっそく、Excelを使って回帰分析をしてしまいましょう。

あっという間に終わります。

「データ」タブのなかにある「分析」グループから、「データ分析」を選択しましょう ↓

分析タブ
もしも、「データ分析」がメニューに無い場合

「データ分析」のメニューが無い場合。最初の1回めだけ、アドインの設定が必要になります。難しくはありません。

はじめに、Excelで「オプション」の画面を開きましょう。

「ファイル」タブを選択したあと、画面左下の「オプション」を選択する、のもよいですが。キーボードで、「Alt」「 T」「O」の順に押すだけのショートカットもおすすめです。

オプション画面が開いたら、左端のメニューのなかから「アドイン」を選択します ↓

Excelのオプション

続いて、Excelアドインの「設定」ボタンを押します ↓

Excelのオプション(アドイン)

すると、アドインの設定画面が開くので、「分析ツール」にチェックを入れて、「OK」ボタンを押しましょう ↓

アドイン設定の画面

これで、はじめの設定はおしまい。回帰分析の機能が使えるようになります。

「データ分析」を選択すると、データ分析のウィンドウが開くので「回帰分析」を選択して、「OK」ボタンを押します ↓

分析ツール メニュー

回帰分析の設定画面が表示されるので、次のように設定します ↓

回帰分析 設定画面

「入力Y範囲」は、「アイスクリームの支出額」データを選択します。このとき、データ1行目の見出しにあたる「C1」セルも選択します ↓

回帰分析 データの選択

同じように、「入力X範囲」の「気温」も、「B1」のセルを含めて選択します。

そのうえで、「ラベル」のチェックボックスにチェックを入れましょう。これにより、データ1行目の「B1」「C1」セルを、数値ではなく見出しとして扱います。

さいごに、「残差」のチェックボックスにもチェックを入れます。残差とはなんなのか? は、のちほどお話をします。

回帰分析の結果の見方

前述の回帰分析の結果が、次のように表示されます ↓

回帰分析の結果 概要

うげっ、なんじゃこりゃ。という感じではありますが。いまいま見るべきところは限られます。上図のうち、赤色の枠で囲ったところです。

まずは、下の赤枠から見ていきます。「切片 126.5326」「気温 38.14244」というところです。

これは、アイスクリーム支出額について、ベース(基礎)として「126.5326円」であること。また、気温が1度上がるごとに「38.14244円」の支出額が増えることを示しています。

これを算式にまとめると、 ↓

  • アイスクリームの支出額(円) = 38.14244 × 気温 + 126.5326

したがって、たとえば「気温 25度」「気温 26度」ならば、アイスクリーム支出額の予測はこうなります ↓

  • 気温 25度 38.14244 × 25 + 126.5326 = 約 1,080(円)
  • 気温 26度 38.14244 × 26 + 126.5326 = 約 1,118(円)

よって、気温 25度ならば、アイスクリーム支出額は 1,080円くらいになるはず。1度上がって気温 26度ならば、支出額は 1,118円くらいまで上がるはず。というのが回帰分析の結果です。

ちなみに。「アイスクリームの支出額(円) = 38.14244 × 気温 + 126.5326」という算式が、散布図のところで見た「赤色の点線」にあたります。38.14244が線の「傾き」で、126.5326が「切片」ですね。

それから、回帰分析の結果のなかからもうひとつ。「重決定R2」という数値がありました。具体的には「0.798186」という数値、その説明です。

結論を言うと、「気温の変化」で「アイスクリーム支出額の変化」の 79.8186%を説明できる、ということをあらわしています。

言い換えると、残りの 20%弱は、なにか別の要素がアイスクリーム支出額に影響している、ということです。

この「重決定R2」は、「0〜1」の値をとり、「1」に近いほど説明力が高いと言えます。R2の値がいくつ以上だとよいのか? の明確な基準はありませんが、「79.8186%」は高めだと言ってよいでしょう。

残差の見方

回帰分析の結果について、さいごにもうひとつ。回帰分析の結果をずっと見ていくと、下のほうに「残差出力」があります ↓

残差出力

残差は、データの件数分だけあります。3年分(36ヶ月分)のデータでしたから残差は 36件。

なお、A列の「観測値」の1行目が元データの1件目(2016年1月分)に、「観測値」の2行目が元データの2件目(2016年2月分)に、という具合に対応しています。

B列の「予測値」は、各観測値の気温を、さきほど見た算式「38.14244 × 気温 + 126.5326」にあてはめたときの計算結果です。

C列の「残差」は、実際の「アイスクリーム支出額」と予測値の「アイスクリーム支出額」との差になります。実際のほうが予測値よりも大きければ「プラス」、実際のほうが小さければ「マイナス」です。

では、この「残差」からなにを読み取るか? 

たとえば、もっともプラスが大きな残差である、「観測値 20」を見てみましょう。その残差は「236.507」です。

「観測値 20」は元のデータで言うと、20ヶ月目の「2017年8月」にあたります。その2017年8月の「気温」は 26.4度でした。これを例の算式にあてはめると、

  • 38.14244 × 26.4 + 126.5326 = 約 1,133(円)

予測値の「1133.493026」に一致します。この値に対して、「2017年8月」の実際のアイスクリーム支出額は「1,370円」でした。よって、残差は「1,370円 − 1,133円 =236.507円」となるわけです。

つまり、「2017年8月」は、「気温の割にはアイスクリーム支出額が多かった」ということになります。そこから、「気温以外になにかアイスクリーム支出額を伸ばした理由があるのではないか?」と考えるきっかけができます。

本来は、アイスクリーム支出額ではなくて、アイスクリームの売上高であったことに置き換えてみると。たとえば、一時的なアイスクリームブームがあったとか、販促キャンペーンをした、などが売上高を伸ばした理由として考えられます。

それら理由のなかから、「再現性が高い理由」が見つかれば。今後、気温とは関係なく、売上高を伸ばすことができる策を打ち出せるかもしれません。

逆に、もっともマイナスが大きい残差についても、理由を検討することは有効です。理由がわかれば、売上高が落ち込むことへの予防策をたてることができます。

回帰分析の結果は、「残差」もあわせて見るようにするとよいでしょう。

 

スポンサードサーチ

まとめ

商売をしていると。ある「A」という数値の変化に対して、「B」という数値がどのように変化するかを予測したい、ということがあります。

そこで、「気温が○度だとアイスクリームは○円売れる?」を例に、Excelの回帰分析を使った予測についてお話をしてきました、

回帰分析は他の場面にも使える手法です。ぜひ、使い方と結果の見方を押さえておきましょう。

この記事のシェアはこちらから!