Excelで平均回帰分析を活用した売上予測モデル構築

第1章：平均回帰分析とは？Excelでできる売上予測の全体像

「来月の売上、どれくらいになりそう？」と聞かれて、勘と経験だけで答えるのは不安ですよね。そこで役立つのが回帰分析です。回帰分析は、過去データから「売上を説明するルール（式）」を作り、将来の売上を推定する手法。難しそうに見えますが、Excelだけでも実務レベルの売上予測モデルは作れます。

まず押さえたいのは、ここでいう「平均回帰」のイメージです。売上は毎月上下しますが、長期的に見るとある平均的な水準（トレンドや基準値）に近づこうとする動きが出ることがあります。たとえばキャンペーンで一時的に跳ねた売上も、次月以降は落ち着く（＝平均に戻る）ことが多い、という感覚に近いです。回帰分析では、この「平均的な関係」をデータから学習し、外部要因（広告費、来店数、価格、商談数など）と売上の関係を式で表現します。

全体像としては、売上予測モデルは次の流れで作ります。

1. 目的を決める（何を、いつの売上まで、どの精度で当てたいか）
2. データを整える（欠損・形式・異常値・期間の揃え）
3. 回帰モデルを作る（係数を出して回帰式を作成）
4. 予測に落とす（予測値算出、グラフ化、誤差検証）
5. 運用しながら改善（季節性や外れ値を考慮し更新）

回帰分析の最終成果物はシンプルで、基本は「売上＝a×要因1＋b×要因2＋…＋切片」の形になります。たとえば「売上は来店数と広告費でだいたい説明できそう」という仮説があれば、Excelで係数（a,b）を推定し、来月の来店数・広告費の見込みを入れて売上を予測する、という流れです。

ここで大事なのは、回帰分析は「未来を当てる魔法」ではなく、あくまで過去データから見える平均的な傾向を数式化する点です。だからこそ、データの質が甘いとそれっぽい式は作れても当たりません。逆に、データ準備と検証をきちんとやれば、上司に「根拠は？」と聞かれても数字で説明できる予測になります。

次章では、予測精度を決める最重要パートであるデータ準備（売上データの整え方、やりがちなミス、注意点）を具体的に解説していきます。

第2章：予測精度を左右する「データ準備」—売上データの整え方と注意点

回帰式そのものより、実は精度を一番左右するのがデータ準備です。Excelで回帰分析を回すのは一瞬ですが、入力データが揃っていないと「計算はできるけど、使えない予測」になりがち。ここでは、売上予測モデルに載せる前に最低限やるべき整え方を、実務目線で整理します。

1) まずは「1行＝1期間」を徹底する（粒度を揃える）

月次予測なら、データも月次で統一します。よくある事故が「売上は月次、広告費は週次、来店数は日次」のように粒度がバラバラな状態。回帰分析は同じ期間の数字同士を比較するので、集計して期間を揃えてから使いましょう。

• 月次に揃える例：日次来店数 → 月合計、週次広告費 → 月合計
• 「売上が計上された月」と「施策の効果が出た月」がズレる場合は後述のラグも検討

2) 列設計は「目的変数」と「説明変数」を分ける

回帰分析で言うところの目的変数＝売上、説明変数＝売上に影響しそうな要因です。Excel上では列をはっきり分け、表の形を固定します。

• 例：A列=年月、B列=売上、C列=広告費、D列=来店数、E列=平均単価、F列=商談数…

この時点で「売上を説明できそうな要因」を出し切るのがコツ。逆に、売上とほぼ同じ意味の数字（売上前年差、売上累計など）を説明変数に入れると、モデルが不自然に当たりやすく見えることがあります（実務では“ズルい当たり方”になりがち）。

3) 欠損値・空欄は「放置しない」

空欄が混ざると、回帰分析が正しく動かなかったり、意図せず行が除外されたりします。対応は大きく3つです。

1. 取得して埋める（最優先：元データを確認）
2. 0で埋める（「その月は実施なし」が明確な場合のみ。広告費0など）
3. 除外する（欠損が少数で、埋める根拠がない場合）

「とりあえず平均で埋める」は見た目は整いますが、平均回帰のモデルでは傾向を薄める方向に効きやすいので、理由なく多用しないのが無難です。

4) 異常値（外れ値）は「ミス」と「イベント」を切り分ける

売上が極端に跳ねた月・落ちた月は要注意。まずは入力ミス（桁違い、税込/税抜混在、二重計上）を疑い、正しいならイベント扱いにします。イベントなら、後でモデルに反映しやすいようにメモ列やフラグ列を用意すると便利です（例：キャンペーン月=1、それ以外=0）。

5) 「時系列のズレ（ラグ）」を意識する

広告費を使った月に、売上が同じ月に増えるとは限りません。BtoBなら翌月以降、ECでも数週間遅れることがあります。この場合は、説明変数を1か月ずらした列を作るのが定番です。

• 例：当月売上を、前月広告費（広告費_前年差ではなく広告費_前月）で説明する

ラグを入れるかどうかで係数の意味が変わるので、ここは「実際の業務プロセス（検討期間・リードタイム）」に合わせると納得感が出ます。

6) 期間は「最低でも12点、できれば24点以上」を目安に

月次データで6か月分だけだと、たまたまのブレに引っ張られて不安定になりがち。理想は季節性をまたぐ期間です。まずは12か月、可能なら24か月以上を確保すると、平均的な関係が見えやすくなります。

ここまで整えると、次章の「係数を出して回帰式を作る」作業が一気にスムーズになります。回帰分析は、言い換えると“データの形を整えた人が勝つ”手法。次はExcelの分析ツールや関数を使って、実際にモデル（回帰式）を組み立てていきましょう。

第3章：Excelで平均回帰モデルを作る手順—回帰式・係数の出し方（分析ツール／関数）

データの粒度や欠損・ラグまで整ったら、いよいよ「売上＝（要因の足し算）＋切片」の形に落とし込みます。Excelで係数を出す方法は大きく2つ。①分析ツール（回帰）で一気に出す、②関数で必要な値を引っ張るです。実務では「分析ツールで全体を確認→関数でシートに組み込んで運用」が鉄板です。

1) まず作るのは「説明変数X」と「目的変数Y」の範囲

第2章の列設計の通り、例として下のような表を想定します。

• A列：年月
• B列：売上（目的変数Y）
• C列：広告費（説明変数X1）
• D列：来店数（説明変数X2）
• E列：平均単価（説明変数X3）

ポイントは、XとYは同じ行で「同じ期間」を指していること。ラグを入れたなら、すでに「前月広告費」などに加工した列をXとして使います。

2) 分析ツール（回帰）で係数を出す手順

まずはExcelの回帰機能で、係数と当てはまりをまとめて確認します。

1. （未設定なら）［ファイル］→［オプション］→［アドイン］で「分析ツール」を有効化
2. ［データ］タブ→［データ分析］→［回帰］を選択
3. 入力Y範囲：売上列（例：B2:B25）
4. 入力X範囲：広告費〜平均単価の列（例：C2:E25）
5. 先頭行が見出しなら［ラベル］にチェック
6. 出力先（新しいワークシート推奨）を指定してOK

出力結果で最低限見るべきはここです。

• 係数（Coefficients）：各要因の“効き具合”。回帰式のa,b,cに相当
• 切片（Intercept）：要因が0でも発生するベースの売上（解釈には注意）
• 重決定係数（R Square）：ざっくり「売上のブレをどれだけ説明できているか」
• P値（P-value）：その要因が効いていそうかの目安（大きすぎる要因は見直し候補）

ここで作られる回帰式の形は次のイメージです。

売上 ＝ 切片 ＋（広告費×係数1）＋（来店数×係数2）＋（平均単価×係数3）

平均回帰の発想としては、「月ごとの凸凹はありつつも、要因との平均的な関係に引き寄せる形で売上を推定する」モデルになります。極端な月があっても、係数は全期間の傾向をならして決まる、というイメージです。

3) 関数で回帰係数を出す（シートに組み込む）

分析ツールは便利ですが、毎月データが増える運用だと「出力表を毎回作り直す」のは面倒です。そこで、関数で係数を引っ張る方法も押さえておくと便利です。

• LINEST関数：複数要因（重回帰）の係数をまとめて返せる
• SLOPE/INTERCEPT：1要因（単回帰）のときにシンプル

たとえば重回帰なら、LINESTを使います（Excelのバージョンによって出力形式は異なります）。基本イメージは次の通りです。

• 係数を出す：=LINEST(売上範囲, 要因範囲, TRUE, TRUE)

得られた係数と切片をセル参照できるようにしておけば、翌月以降はデータを足すだけでモデル更新が可能になります。

4) 係数が出たら「予測に使える形」に整える

モデルを“使える”状態にするコツは、係数を別エリアに固定して、予測式をセルで組むことです。たとえば係数表を作り、予測列に次のような式を入れます。

予測売上 ＝ $切片$ ＋ 広告費セル×$係数(広告費)$ ＋ 来店数セル×$係数(来店数)$ ＋ 平均単価セル×$係数(平均単価)$

こうしておくと、回帰分析が「一回きりの分析」ではなく、来月の見込み値（広告費予定、来店見込みなど）を入れれば即座に売上予測が返る“業務ツール”になります。

次章では、この回帰式を使って予測値の算出→グラフ化→誤差（残差）の見方までつなげ、どれくらい信頼できるモデルかを検証していきます。

第4章：売上予測に落とし込む—予測値の算出、可視化、精度検証（誤差・残差）

第3章で係数（回帰式）ができたら、次は「当てにいく」より先に「使えるか確かめる」フェーズです。ここでは①予測値の算出、②見える化、③誤差・残差での精度検証までを、Excelでそのまま回せる形にします。

1) 予測値（ŷ）をExcelで計算する

回帰式は基本的に「予測売上＝切片＋要因×係数…」。係数を固定セルに置いて、各月の行で予測列を計算します。

• 例：切片がH2、広告費係数がH3、来店数係数がH4、平均単価係数がH5にある場合

（予測売上のセル）
=$H$2 + C2*$H$3 + D2*$H$4 + E2*$H$5

この「予測列」を過去の全期間にコピーすると、過去データに対してモデルがどれくらい当たっていたか（当てはまり）を検証できます。さらに、来月以降は広告費・来店数などの“見込み”を入力すれば、そのまま予測に使えます。

2) 可視化：実績 vs 予測の折れ線グラフで違和感を潰す

数字だけだと見落としが出るので、まずは実績（売上）と予測（予測売上）を同じグラフに重ねます。

1. 年月列＋売上列＋予測売上列を選択
2. ［挿入］→［折れ線］でグラフ化

ここで見るべきは「当たっているか」より、外し方のクセです。例えば、ずっと予測が低めに出るなら切片や要因設計の問題、特定の時期だけ外すなら季節性やラグ不足の可能性が濃厚です（季節性の対策は第5章で扱います）。

3) 誤差を数値化：残差（e）と誤差率を列で持つ

次に、モデルの弱点を炙り出すために、最低限この2列を作ります。

• 残差（実績−予測）：ずれている“方向”が分かる
• 誤差率（残差/実績）：売上規模が違う月でも比較できる

（残差）
= B2 - 予測売上セル

（誤差率）※実績0があり得るならIFで回避
=IF(B2=0,"", 残差セル / B2)

ここで重要なのは、平均回帰モデルは「平均的に合う」設計なので、残差がプラスとマイナスにバラけているかが健全性のサインになります。逆に、残差がずっとプラス（ずっと過小予測）／ずっとマイナス（ずっと過大予測）なら、モデルが現状とズレています。

4) 精度指標：まずはMAE（平均絶対誤差）で“体感”を作る

上司や他部署に説明するとき、R²だけだと伝わりにくいことがあります。そこで「平均で何円ズレる？」が一発で分かるMAEが便利です。

（絶対誤差の列）
=ABS(残差セル)

（MAE）
=AVERAGE(絶対誤差の範囲)

「平均で±30万円ズレます」まで言えると、予測が“意思決定に使えるレベルか”を判断しやすくなります。誤差率も見たい場合は、誤差率列のABSの平均（平均絶対誤差率）を併用すると良いです。

5) 残差プロットで最終チェック：「偏り」と「外れ月」を見つける

最後に、残差を年月に対して散布（または折れ線）で表示します。理想は0付近にランダムに散る状態。もし「右肩上がりに残差が増える」なら、トレンドの取り込み不足。「特定の月だけドカンと外す」なら、イベント要因（キャンペーン等）のフラグ化や外れ値対応が必要です。

ここまでできると、モデルは「予測を出して終わり」ではなく、「外れ方から改善点が分かる」状態になります。次章では、この検証結果を踏まえて、季節性・外れ値・毎月更新など、実務で回し続けるための運用のコツを詰めていきましょう。

第5章：実務で使える運用のコツ—改善サイクルとよくある落とし穴（季節性・外れ値・更新）

回帰モデルは作って終わりではなく、「毎月使いながら育てる」のが実務の正解です。第4章で残差やMAEを見たはずなので、ここではその結果をどう改善につなげるか、そしてハマりがちな落とし穴（季節性・外れ値・更新）をまとめます。

1) 改善サイクルは「残差→仮説→変数追加/見直し」の順で回す

精度が微妙なとき、いきなり説明変数を増やすのはNG。まずは残差の“クセ”から原因を当てにいきます。

• 特定の月だけ毎年外す：季節性の可能性
• 広告費を増やした月の翌月に外す：ラグ不足の可能性
• ずっと過大/過小：直近の環境変化（価格改定、チャネル変更）で平均がズレた可能性

この「外れ方→要因の仮説」が立つと、モデル修正が“勘”ではなく“検証”になります。

2) 季節性は「月フラグ（ダミー変数）」で吸収する

売上は、ボーナス時期・年度末・セール月などでリズムが出ます。これを無視すると、平均回帰モデルは平常月の平均に引っ張られて繁忙月を外しやすいです。

Excelで現実的なのは、月ごとのダミー変数を足す方法。たとえば「12月なら1、他は0」の列を作り、回帰の説明変数Xに追加します（1月〜11月も同様。全部入れると重複しやすいので、通常は1つ基準月を除外します）。

• 例：F列=12月フラグ、G列=3月フラグ…のように追加
• 残差で「毎年12月だけプラスに大きい」なら、12月フラグは効きやすい

これで「繁忙月は平均より上振れしやすい」という構造を式に埋め込めます。

3) 外れ値は“削除”より「理由を残して扱いを分ける」

実務データの外れ値は、だいたい2種類です。

1. ミス（桁違い・二重計上）：正しい値に直す
2. 事実（大型案件・炎上・供給不足）：原因があるので消さない

問題は後者。消すと「都合のいい予測」になります。おすすめは、外れ値の月に対してイベントフラグ列（大型案件=1、在庫切れ=1など）を用意し、説明変数として入れること。これなら再現性のある“例外”として学習できます。イベントが一過性なら、予測時はそのフラグを0にすれば「通常時の売上」に戻せます。

4) 毎月更新は「データ追加→係数更新→精度ログ」の3点セット

運用で一番ありがちな失敗は、モデル更新が属人化して止まること。最低限、次の流れを型にしてください。

• データを1行追加（月次の売上・要因・フラグ）
• 係数を更新（分析ツールでもLINESTでもOK。やり方を固定）
• 精度を記録（MAE、平均絶対誤差率、直近3か月の誤差など）

ポイントは「当たった/外れた」の感想ではなく、数字でモデルの劣化を検知すること。たとえば「直近3か月の誤差率が急に悪化」したら、価格改定や集客チャネル変更など“構造変化”を疑う合図になります。

5) よくある落とし穴：変数を増やしすぎる／意味がない係数を信じる

説明変数を増やすと、過去への当てはまり（R²）は上がりがちですが、未来には弱くなることがあります。特に月次データが少ないうちは危険です。対策はシンプルで、「増やす前に残差の理由を言語化できるか」を基準にすること。

また、係数の符号が直感と逆（広告費係数がマイナス等）なら、ラグ不足や変数同士のかぶり（似た指標を複数入れている）を疑いましょう。回帰は“それっぽい数字”が出るので、最後は必ず業務プロセスと整合するかでチェックするのが安全です。

季節性・外れ値・更新ルールを押さえると、平均回帰モデルは「一回の分析」から毎月使える予測ツールに変わります。予測の価値は、当てることだけでなく、外れたときに次の改善策が見えること。残差を味方にして、運用で精度を上げていきましょう。