商売をしていると。ある「Ａ」という数値の変化に対して、「Ｂ」という数値がどのように変化するかを予測したい、ということがあります。
そこで、「気温が○度だとアイスクリームは○円売れる？」を例に、Excelの回帰分析を使った予測についてお話をします。
気温が○度だとアイスクリームは○円売れる
商売をしていると。ある「Ａ」という数値の変化に対して、「Ｂ」という数値がどのように変化するかを予測したい、ということがあります。
たとえば、自社が「アイスクリーム」を売っているとして。「気温が上がるとアイスクリームが売れる気がするのだけれど、気温が25度だとしたらどれくらい売れるのだろう？ 26度だったら…？」と、考えているようなケースです。
この場合、ある数値Ａが「気温」で、数値Ｂが「アイスクリームの売上高」です。気温の変化に対して、アイスクリームの売上高の変化を予測したい。
というわけで。予測をするためのデータとして、次のデータを集めました ↓
- 過去３年間月別の１世帯あたりアイスクリーム支出額（総務省・家計調査より）
- 過去３年間月別の東京の平均気温（気象庁・過去の気象データ検索より）
ここでは、１世帯あたりの「アイスクリームの支出額」のデータを使っていますが。本来は、自社の「アイスクリームの売上高」の実績データを使うべきところです。便宜的に、ということでご了承願います。
また、自社は東京でアイスクリームを売ってるという前提で、東京の平均気温を見ることとします。
これらデータをExcelで並べてみたものがこちらです ↓
上記のデータを見ながら、気温の変化に対して、アイスクリームの売上高（データとしては１世帯あたりの支出額）の変化をExcelで予測してみましょう。
ちなみに。似たようなケースでの予測はいろいろと想定できます。たとえば、
- 会員加入期間が伸びると、客単価はどれだけ伸びるのか？
- 競合店の販売価格が下がると、自店の同一商品の販売数量はどれだけ下がるのか？
- ある広告を増やしたときの費用に対して、売上高はどれだけ伸びるのか？
なども同じように予測ができますので、いろいろな場面で役立つことでしょう。
Excelの「回帰分析」で予測する
冒頭のアイスクリームの件、Excelで予測をしていきます。具体的には、Excelの「回帰分析」という機能を使います。
一見難しそうですが、ポイントを絞ってのお話ですからご安心を。
手始めに散布図を描いてみる
本題の「回帰分析」についてお話をする前に。さきほどの「気温」と「アイスクリームの支出額」のデータを使って「散布図」を描いてみましょう。
散布図の描き方はカンタンです。
まずは、散布図のもとになるデータを選択します。今回のケースでは、数字が入力されている「Ｂ２」のセルから「Ｃ37」のセルまでを選択します。
そのうえで、Excelの上部にあるリボン（メニュー）から「挿入」を選択。そのあと、下図の箇所にある「散布図」を選択します ↓
すると、次のような「散布図」が描けるはずです ↓
上図は、縦軸が「アイスクリームの支出額」で、横軸が「気温」です。３年分（36ヶ月分）のデータとして 36個の点がプロットされています。
これを見ると、「気温」が上がるにつれて、「アイスクリームの支出額」が伸びているようすがわかるでしょう。言い換えると、「赤色の点線」のまわりに 36個の点が集まっている、ということです。
なお、赤色の点線は、目安としてわたしが引いた線になります。言わば、なんとなく引いた線ですが、「なんとなく」ではなく、「明確」に線を引くことができれば予測に役立ちそうです。
そこで、このあとの「回帰分析」のお話へと続きます。
Excelで回帰分析をしてみる
回帰分析とはなんぞや？ というお話は他へゆずるとして。まずはさっそく、Excelを使って回帰分析をしてしまいましょう。
あっという間に終わります。
「データ」タブのなかにある「分析」グループから、「データ分析」を選択しましょう ↓
「データ分析」のメニューが無い場合。最初の１回めだけ、アドインの設定が必要になります。難しくはありません。
はじめに、Excelで「オプション」の画面を開きましょう。
「ファイル」タブを選択したあと、画面左下の「オプション」を選択する、のもよいですが。キーボードで、「Alt」「 Ｔ」「Ｏ」の順に押すだけのショートカットもおすすめです。
オプション画面が開いたら、左端のメニューのなかから「アドイン」を選択します ↓
続いて、Excelアドインの「設定」ボタンを押します ↓
すると、アドインの設定画面が開くので、「分析ツール」にチェックを入れて、「OK」ボタンを押しましょう ↓
これで、はじめの設定はおしまい。回帰分析の機能が使えるようになります。
「データ分析」を選択すると、データ分析のウィンドウが開くので「回帰分析」を選択して、「OK」ボタンを押します ↓
回帰分析の設定画面が表示されるので、次のように設定します ↓
「入力Ｙ範囲」は、「アイスクリームの支出額」データを選択します。このとき、データ１行目の見出しにあたる「Ｃ１」セルも選択します ↓
同じように、「入力Ｘ範囲」の「気温」も、「Ｂ１」のセルを含めて選択します。
そのうえで、「ラベル」のチェックボックスにチェックを入れましょう。これにより、データ１行目の「Ｂ１」「Ｃ１」セルを、数値ではなく見出しとして扱います。
さいごに、「残差」のチェックボックスにもチェックを入れます。残差とはなんなのか？ は、のちほどお話をします。
回帰分析の結果の見方
前述の回帰分析の結果が、次のように表示されます ↓
うげっ、なんじゃこりゃ。という感じではありますが。いまいま見るべきところは限られます。上図のうち、赤色の枠で囲ったところです。
まずは、下の赤枠から見ていきます。「切片 126.5326」「気温 38.14244」というところです。
これは、アイスクリーム支出額について、ベース（基礎）として「126.5326円」であること。また、気温が１度上がるごとに「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円くらいになるはず。１度上がって気温 26度ならば、支出額は 1,118円くらいまで上がるはず。というのが回帰分析の結果です。
ちなみに。「アイスクリームの支出額（円） ＝ 38.14244 × 気温 ＋ 126.5326」という算式が、散布図のところで見た「赤色の点線」にあたります。38.14244が線の「傾き」で、126.5326が「切片」ですね。
それから、回帰分析の結果のなかからもうひとつ。「重決定Ｒ２」という数値がありました。具体的には「0.798186」という数値、その説明です。
結論を言うと、「気温の変化」で「アイスクリーム支出額の変化」の 79.8186%を説明できる、ということをあらわしています。
言い換えると、残りの 20%弱は、なにか別の要素がアイスクリーム支出額に影響している、ということです。
この「重決定Ｒ２」は、「０〜１」の値をとり、「１」に近いほど説明力が高いと言えます。Ｒ２の値がいくつ以上だとよいのか？ の明確な基準はありませんが、「79.8186％」は高めだと言ってよいでしょう。
残差の見方
回帰分析の結果について、さいごにもうひとつ。回帰分析の結果をずっと見ていくと、下のほうに「残差出力」があります ↓
残差は、データの件数分だけあります。３年分（36ヶ月分）のデータでしたから残差は 36件。
なお、Ａ列の「観測値」の１行目が元データの１件目（2016年１月分）に、「観測値」の２行目が元データの２件目（2016年２月分）に、という具合に対応しています。
Ｂ列の「予測値」は、各観測値の気温を、さきほど見た算式「38.14244 × 気温 ＋ 126.5326」にあてはめたときの計算結果です。
Ｃ列の「残差」は、実際の「アイスクリーム支出額」と予測値の「アイスクリーム支出額」との差になります。実際のほうが予測値よりも大きければ「プラス」、実際のほうが小さければ「マイナス」です。
では、この「残差」からなにを読み取るか？
たとえば、もっともプラスが大きな残差である、「観測値 20」を見てみましょう。その残差は「236.507」です。
「観測値 20」は元のデータで言うと、20ヶ月目の「2017年８月」にあたります。その2017年８月の「気温」は 26.4度でした。これを例の算式にあてはめると、
- 38.14244 × 26.4 ＋ 126.5326 ＝ 約 1,133（円）
予測値の「1133.493026」に一致します。この値に対して、「2017年８月」の実際のアイスクリーム支出額は「1,370円」でした。よって、残差は「1,370円 − 1,133円 ＝236.507円」となるわけです。
つまり、「2017年８月」は、「気温の割にはアイスクリーム支出額が多かった」ということになります。そこから、「気温以外になにかアイスクリーム支出額を伸ばした理由があるのではないか？」と考えるきっかけができます。
本来は、アイスクリーム支出額ではなくて、アイスクリームの売上高であったことに置き換えてみると。たとえば、一時的なアイスクリームブームがあったとか、販促キャンペーンをした、などが売上高を伸ばした理由として考えられます。
それら理由のなかから、「再現性が高い理由」が見つかれば。今後、気温とは関係なく、売上高を伸ばすことができる策を打ち出せるかもしれません。
逆に、もっともマイナスが大きい残差についても、理由を検討することは有効です。理由がわかれば、売上高が落ち込むことへの予防策をたてることができます。
回帰分析の結果は、「残差」もあわせて見るようにするとよいでしょう。
まとめ
商売をしていると。ある「Ａ」という数値の変化に対して、「Ｂ」という数値がどのように変化するかを予測したい、ということがあります。
そこで、「気温が○度だとアイスクリームは○円売れる？」を例に、Excelの回帰分析を使った予測についてお話をしてきました、
回帰分析は他の場面にも使える手法です。ぜひ、使い方と結果の見方を押さえておきましょう。
