エクセルで散布図や売上データを扱うと、回帰直線の切片を求めたい場面があります。
切片は、横軸の値が0になったときに縦軸の値がいくつになるかを示す数値です。
手計算では複雑になりがちな回帰分析も、ExcelのINTERCEPT関数を使えば、既知のデータ範囲から切片をすばやく算出できます。
INTERCEPT関数の基本形
=INTERCEPT(既知のyの範囲,既知のxの範囲)
第1引数に結果の数値、第2引数に原因や説明変数となる数値を指定します。
この記事では、INTERCEPT関数の書式、サンプルデータを使った入力方法、傾き関数SLOPEとの組み合わせ方、エラー時の確認ポイントまで順番に解説します。
先に結論をいうと、切片はINTERCEPT関数に縦軸のデータ範囲と横軸のデータ範囲を指定するだけで求められます。
INTERCEPT関数による回帰直線の切片計算
| 月 | 広告費 | 売上高 | 切片 |
|---|---|---|---|
| 1月 | 10 | 120 | =INTERCEPT(C2:C6,B2:B6) |
| 2月 | 20 | 155 | |
| 3月 | 30 | 188 | |
| 4月 | 40 | 226 | |
| 5月 | 50 | 258 |
それではまず、INTERCEPT関数で切片を直接計算する方法について解説していきます。
回帰直線は一般に、y=ax+bという式で表します。
このうちaは傾き、bは切片です。
広告費をx、売上高をyとして考える場合、広告費が0のときに予測される売上高が切片にあたります。
INTERCEPT関数の書式と引数
INTERCEPT関数の書式は、=INTERCEPT(既知のy,既知のx)です。
既知のyには、回帰直線で予測したい結果側の数値範囲を指定します。
既知のxには、結果に影響すると考える要因側の数値範囲を指定します。
今回の表では、売上高が結果であるためC2:C6を既知のyに指定し、広告費が要因であるためB2:B6を既知のxに指定します。
引数の順序はyが先、xが後です。
SUM関数のように範囲を並べるだけではありますが、xとyを逆にすると意味の異なる回帰式になるため注意しましょう。
入力する数式
=INTERCEPT(C2:C6,B2:B6)
C列の売上高をy、B列の広告費をxとして扱う指定です。
セル範囲の行数は必ずそろえる必要があります。
たとえばC2:C6に5件の売上データがあるなら、B2:B6にも対応する5件の広告費データを用意します。
見出しがある場合は、1行目を含めず、実際の数値が始まる2行目から指定することが基本です。
実際の数式入力と結果の読み取り
切片を表示したいセルとして、ここではD2を選択します。
D2に=INTERCEPT(C2:C6,B2:B6)と入力し、Enterキーを押してください。
サンプルデータでは、およそ86前後の数値が返されます。
これは広告費が0であると仮定したとき、回帰直線上では売上高が約86になるという意味です。
切片は実際に観測された最小値ではなく、データ全体から推定した直線上の値です。
そのため、広告費が0の月が表に含まれていなくても計算できます。
ただし、観測データの範囲から大きく離れた0を推定する場合は、実務上の妥当性を別途確認する必要があります。
たとえば広告費が10から50までしかないデータで、0のときの売上を予測することは外挿にあたる場合があります。
数式の結果だけで判断せず、商品の固定需要や季節要因も踏まえて読み取りましょう。
切片が示す数値の考え方
切片は、説明変数xが0のときの目的変数yの推定値です。
広告費と売上高の例では、広告を出さなくても発生する基礎的な売上の目安として解釈できます。
一方で、xが0という状態が現実にはあり得ない場合、切片そのものに強い実務的意味を持たせられないこともあります。
たとえば従業員数と人件費の関係では、従業員数が0でも固定費が存在するケースがあるでしょう。
切片は計算結果として重要ですが、必ずしも現実の起点をそのまま表すとは限りません。
【操作のポイント】切片だけを単独で見るのではなく、傾き、決定係数、実際のデータ分布と合わせて判断すると、回帰分析の読み違いを防げます。
サンプルデータを使った切片関数の入力手順
| A列 | B列 | C列 | D列 |
|---|---|---|---|
| 月 | 広告費 | 売上高 | 計算結果 |
| 1月 | 10 | 120 | 切片 |
| 2月 | 20 | 155 | |
| 3月 | 30 | 188 | |
| 4月 | 40 | 226 | |
| 5月 | 50 | 258 |
続いては、表を作成してからINTERCEPT関数を入力する流れを確認していきます。
ここでは1行目にヘッダーがあり、2行目以降に数値データを入力した前提で進めます。
データ範囲の準備
まず、B1に広告費、C1に売上高と見出しを入力します。
B2:B6には10、20、30、40、50を入力し、C2:C6には対応する120、155、188、226、258を入力してください。
このように、同じ行に対応関係のある数値を並べることが大切です。
広告費がB2であれば、その広告費による売上高はC2に置きます。
途中で行を並べ替えたり、一方の列だけを削除したりすると、回帰計算の前提が崩れます。
サンプルの配置例
B1 広告費 C1 売上高
B2:B6 10から50 C2:C6 120から258
空白セルが混ざると、意図しないデータの対応付けになる可能性があります。
データを追加する予定がある場合は、テーブル機能を使うか、集計範囲を余裕を持って設計すると管理しやすくなります。
数式バーからのINTERCEPT関数入力
計算結果を表示するD2をクリックします。
数式バーまたはセルに半角で=INTERCEPT(と入力します。
続けて売上高の範囲C2:C6を選択し、カンマを入力したあと、広告費の範囲B2:B6を選択します。
最後に閉じかっことを入力してEnterキーを押すと、数式が確定します。
関数の候補が表示された場合はINTERCEPTを選ぶと、引数の入力欄を確認しながら操作できます。
数式を入力した直後に#N/Aや#VALUE!が表示された場合は、範囲のサイズとセルの中身を見直してください。
数値に見える文字列や、エラー値が混在している場合も、期待どおりの計算にならないことがあります。
小数点以下の表示形式の調整
INTERCEPT関数の計算結果には、小数点以下が長く表示されることがあります。
分析用に詳細な値を残すこともできますが、報告書では小数第1位や整数に丸めた表示のほうが読みやすい場合があります。
対象セルを選択し、ホームタブの小数点以下の表示桁数を減らすボタンで表示を整えましょう。
これは表示だけを変更する操作であり、セル内部の計算精度は維持されます。
見た目を丸めても、数式バーには元の精密な計算結果が残ります。
【操作のポイント】切片をほかのセルで計算に使うときは、表示を丸めるだけにして、ROUND関数で値そのものを丸める必要があるかを用途別に判断しましょう。
傾き関数SLOPEとの組み合わせ
| 項目 | 入力する関数 | 役割 |
|---|---|---|
| 傾き | =SLOPE(C2:C6,B2:B6) | 広告費が1増えたときの変化量 |
| 切片 | =INTERCEPT(C2:C6,B2:B6) | 広告費が0のときの推定売上 |
| 予測値 | =傾き*広告費+切片 | 回帰直線上の売上 |
続いては、切片と傾きを組み合わせて回帰直線を作る方法を確認していきます。
INTERCEPT関数は切片を返す関数ですが、SLOPE関数と一緒に使うことで、回帰式をセル上に再現できます。
SLOPE関数による傾きの算出
SLOPE関数は、回帰直線y=ax+bにおけるa、つまり傾きを求める関数です。
書式は=SLOPE(既知のy,既知のx)ではなく、Excel上では=SLOPE(既知のy,既知のx)と入力します。
たとえばE2に=SLOPE(C2:C6,B2:B6)と入力すると、広告費1単位あたりの売上高の平均的な増加量を取得できます。
SLOPE関数もINTERCEPT関数と同じく、yの範囲を先に指定します。
広告費が1万円単位であれば、得られた傾きは広告費を1万円増やしたときの予測売上の増加量として読めます。
回帰直線の構成
y=ax+b
aは=SLOPE(C2:C6,B2:B6)
bは=INTERCEPT(C2:C6,B2:B6)
単位が混在していると解釈が難しくなるため、広告費を円で入力するのか万円で入力するのかを表の段階で統一しておくとよいでしょう。
回帰式による予測値の作成
傾きをE2、切片をF2に計算したとします。
広告費がB2にある場合、予測売上をG2に出す数式は=$E$2*B2+$F$2です。
この式は、傾きに広告費を掛け、切片を加えるという回帰直線の計算をそのまま表しています。
数式をG2に入力したら、フィルハンドルを下へドラッグしてG6までコピーします。
傾きと切片のセルには絶対参照の$を付けることが重要です。
B
罫線
中央揃え
| A | B | C | D | E | F | G | |
|---|---|---|---|---|---|---|---|
| 1 | 広告費 | 売上高 | 傾き | 切片 | 予測売上 | ||
| 2 | 10 | 120 | 3.46 | 86.2 | 120.8 | ||
| 3 | 20 | 155 | 155.4 |
予測値と実績値の差を確認すれば、回帰直線が各データをどの程度説明できているかを視覚的に把握できます。
散布図と近似曲線の確認
数式の結果をグラフでも確認したい場合は、広告費と売上高の範囲を選択して散布図を挿入します。
グラフ上のデータ系列を右クリックし、近似曲線の追加を選びます。
線形近似を選択し、グラフに数式を表示する設定を有効にすると、y=ax+bの形で近似式を確認できます。
グラフに表示された定数項が、INTERCEPT関数で求めた切片と対応します。
表示桁数の違いにより、グラフの式とセルの数値がわずかに異なって見えることがあります。
比較するときはセルの表示桁数を増やすか、グラフ側の数値表示形式を調整しましょう。
【操作のポイント】散布図は、直線関係が成り立ちそうかを確認するための有効な手段です。大きく外れたデータがある場合は、切片と傾きを使う前に原因を確認しましょう。
切片計算で発生しやすいエラーと対処
| 表示 | 主な原因 | 確認内容 |
|---|---|---|
| #N/A | 範囲の行数不一致 | xとyのデータ件数 |
| #DIV/0! | xの値に変化がない | 横軸データの重複 |
| #VALUE! | 数値以外の入力 | 文字列やエラー値 |
続いては、INTERCEPT関数でエラーが出たときの確認ポイントを解説していきます。
回帰直線の計算では、単純な入力ミスだけでなく、データの性質によって計算できない場合があります。
#N/Aが表示される場合
#N/Aが表示される代表的な原因は、既知のyと既知のxで指定した範囲の件数が異なることです。
たとえば=INTERCEPT(C2:C6,B2:B5)では、C列が5件、B列が4件となるため対応関係を判断できません。
この場合は両方をC2:C6、B2:B6のように同じ行数に修正します。
空白行を含める場合も、xとyの両方で同じ行を含める必要があります。
数式をコピーした際に片方だけ範囲がずれていないか、数式バーで再確認してください。
#DIV/0!が表示される場合
#DIV/0!が表示される場合、xとして指定した数値にばらつきがない可能性があります。
たとえば広告費がすべて20で、売上高だけが異なるデータでは、広告費の変化に対する傾きを定義できません。
回帰直線を求めるには、少なくともxの値に複数の異なる数値が必要です。
切片は傾きと関係して算出されるため、xの分散が0では計算できません。
確認する内容
xとして指定した列に10、20、30のような変化があるか確認します。
すべて同じ数値なら、分析対象や入力範囲を見直します。
文字列や空白を含むデータの扱い
セルに数値ではなく文字列が入力されていると、計算結果に影響する場合があります。
特に、数字の前後に空白がある、単位として円や個を直接入力している、数値が文字列として保存されているケースには注意が必要です。
セル左上に緑色の三角マークがある場合は、数値が文字列として保存されている可能性があります。
数値への変換を実行するか、VALUE関数などで数値形式に整えてから分析しましょう。
【操作のポイント】分析用の列には単位を入力せず、列見出しに広告費（万円）や売上高（千円）のように記載すると、関数計算とデータ管理を両立できます。
FORECAST関数とLINEST関数による応用
| 目的 | 利用する関数 | 内容 |
|---|---|---|
| 指定したxの予測値 | FORECAST.LINEAR | 回帰直線上のyを返す |
| 傾き | SLOPE | 回帰式のaを返す |
| 切片 | INTERCEPT | 回帰式のbを返す |
| 回帰統計量 | LINEST | 傾きや切片などを配列で返す |
続いては、切片関数と関連する回帰分析関数の使い分けを確認していきます。
目的が切片だけなのか、将来値の予測なのか、統計量まで確認したいのかによって適した関数が変わります。
FORECAST.LINEAR関数による予測値
FORECAST.LINEAR関数は、指定したxの値に対応する予測yを線形回帰に基づいて返す関数です。
広告費60のときの予測売上を求める場合は、=FORECAST.LINEAR(60,C2:C6,B2:B6)と入力します。
これは、SLOPE関数とINTERCEPT関数を使って、傾き×60+切片を計算するのと同じ考え方です。
予測値を1件だけ求めたい場合は、FORECAST.LINEAR関数が簡潔です。
一方で、回帰式をセルに明示し、傾きと切片を別々に確認したい場合はSLOPE関数とINTERCEPT関数の組み合わせが向いています。
LINEST関数による回帰統計量
LINEST関数は、線形回帰の結果をまとめて取得できる関数です。
単純な回帰では傾きと切片を返し、統計量を指定すれば決定係数などの情報も取得できます。
Microsoft 365などの動的配列に対応したExcelでは、複数セルへ結果が展開される場合があります。
より詳細にモデルの信頼性を確認したいときに便利ですが、まず切片を知りたいだけならINTERCEPT関数のほうがわかりやすいでしょう。
関数の使い分け
切片のみを求めるならINTERCEPT関数
予測値を直接求めるならFORECAST.LINEAR関数
回帰統計量まで確認するならLINEST関数
決定係数RSQ関数との併用
RSQ関数は、回帰直線がデータをどの程度説明できているかを示す決定係数を返します。
書式は=RSQ(既知のy,既知のx)です。
結果は0から1に近い値となり、1に近いほど直線的な関係をよく説明できている目安になります。
切片や傾きが算出できても、決定係数が低ければ、その直線による予測の信頼性には慎重になる必要があります。
ただし、決定係数だけで因果関係を証明できるわけではありません。
データの収集方法、外れ値、季節性、他の要因も考慮して分析を進めましょう。
【操作のポイント】INTERCEPT、SLOPE、RSQの3つを並べて表示すると、回帰式の内容と当てはまりの程度を一つの表で確認しやすくなります。
まとめ エクセルで切片を求めるINTERCEPT関数の使い方
| 確認項目 | 内容 |
|---|---|
| 基本書式 | =INTERCEPT(既知のy,既知のx) |
| 引数の順番 | 売上などの結果側を先に指定 |
| 関連関数 | SLOPE、FORECAST.LINEAR、RSQ、LINEST |
| エラー確認 | 範囲の件数、xの変化、数値形式 |
Excelで回帰直線の切片を求めるには、INTERCEPT関数を使います。
数式は=INTERCEPT(既知のy,既知のx)であり、結果となる数値の範囲を先、原因や説明変数の範囲を後に指定することが重要です。
広告費と売上高のようなデータでは、切片は広告費が0のときに予測される売上高を表します。
ただし、実際のデータに0が含まれない場合は、計算結果が外挿になることもあるため、数値の意味を慎重に判断しましょう。
SLOPE関数で傾きを求めれば、y=ax+bの回帰式をセル上で組み立てられます。
さらにFORECAST.LINEAR関数を使うと、任意のxに対する予測値も算出できます。
範囲の行数をそろえ、数値形式を整え、RSQ関数で当てはまりも確認することが、切片関数を実務で活用するポイントです。
最後の確認
切片はINTERCEPT関数
傾きはSLOPE関数
予測値はFORECAST.LINEAR関数
