エクセルで売上の増加量、温度変化、移動距離と時間の関係などを確認するときは、データの傾向を数値で表せる傾きが役立ちます。
傾きを求める代表的な方法には、SLOPE関数で回帰直線の傾きを計算する方法と、散布図に近似曲線を追加してグラフ上で確認する方法があります。
どちらも同じデータの傾向を扱いますが、数値をセルで利用したいのか、報告資料で視覚的に示したいのかによって使い分けることが大切です。
この記事で確認するポイント
・SLOPE関数で傾きを算出する数式
・X値とY値の指定順序
・散布図と近似曲線による傾きの表示
・傾きが計算できないときの確認項目
ここでは、1行目に見出しがあり、2行目以降に数値データが入力されている表を使って解説します。
エクセルで傾きを求めるSLOPE関数
それではまず、SLOPE関数を使って傾きを直接求める方法について解説していきます。
| A列 | B列 | C列 |
|---|---|---|
| 月 | 広告費 | 売上 |
| 4月 | 10 | 120 |
| 5月 | 15 | 145 |
| 6月 | 20 | 170 |
SLOPE関数を使えば、複数のX値とY値から最も当てはまりのよい直線の傾きを一度に求められます。
SLOPE関数の入力形式
まずはSLOPE関数の基本的な書式について解説していきます。
=SLOPE(既知のY値,既知のX値)
既知のY値には、結果として変化する数値の範囲を指定します。
既知のX値には、原因や条件として扱う数値の範囲を指定します。
たとえば広告費が増えることで売上が変化する関係を調べる場合、売上がY値、広告費がX値です。
この順序を逆にすると計算結果も逆の関係になるため、Y値を先、X値を後に指定する点が重要になります。
セルE2に傾きを表示するなら、次の数式を入力します。
=SLOPE(C2:C4,B2:B4)
この例では、広告費が1増えたときに売上が平均してどの程度変化するかを示す値が返ります。
傾きの数値が示す意味
続いては、計算された傾きの読み方を確認していきます。
傾きが正の数なら、X値が増えるほどY値も増える傾向です。
傾きが負の数なら、X値が増えるほどY値は減る傾向になります。
傾きが0に近い場合は、X値の変化とY値の間に直線的な関係があまり見られない可能性があります。
先ほどのデータでは、広告費が5増えるごとに売上が25増えているため、傾きは5になります。
傾きが5の場合
X値が1増えると、Y値は平均して5増える傾向です。
ただし、傾きは将来の結果を保証する数値ではありません。
入力済みデータに見られる平均的な変化の度合いとして理解すると、分析の目的に合いやすくなります。
セル参照で数式を作成する注意点
続いては、SLOPE関数の範囲指定で注意したい点を確認していきます。
Y値のデータ数とX値のデータ数は必ず同じにします。
たとえばC2からC10をY値にしたなら、X値もB2からB10のように9件分を選びます。
片方だけ見出し行を含めたり、途中に空白セルが多く含まれたりすると、意図した結果を得られないことがあります。
文字列として保存された数値が混ざっている場合も、計算前に数値へ変換しておくと安心です。
同じ行にあるX値とY値を対にして扱うことが、正しい傾きを求める基本になります。
【操作のポイント】1行目が見出しなら、数式の範囲は2行目から指定します。
傾きの計算に使うX値とY値
続いては、傾きの計算で混乱しやすいX値とY値の決め方について確認していきます。
| 項目 | X値 | Y値 |
|---|---|---|
| 学習時間と点数 | 学習時間 | テスト点数 |
| 時間と距離 | 時間 | 距離 |
| 広告費と売上 | 広告費 | 売上 |
説明する値と結果の値
それではまず、X値とY値の役割について解説していきます。
X値は、変化の基準や説明側に置く値です。
Y値は、X値に応じて変化すると考える結果側の値です。
時間の経過に伴う気温の変化を見る場合は、時間をX値、気温をY値に設定します。
売上分析で広告費の影響を見たい場合は、広告費をX値、売上をY値に設定する考え方です。
この区別ができると、傾きが何を表すのかを文章で説明しやすくなります。
単位をそろえる考え方
続いては、傾きを読み間違えないための単位について確認していきます。
傾きには、Y値の単位をX値の単位で割った意味があります。
たとえば時間を分、距離をメートルで入力した場合、傾きは1分あたり何メートル進むかを表します。
広告費を千円、売上を千円で入力した場合、傾きは広告費1千円あたりの売上変化として読み取れます。
距離がY値、時間がX値の場合
傾き＝距離の変化量÷時間の変化量
入力単位を変更すれば傾きの数値も変わるため、グラフや報告書では単位の記載が欠かせません。
データの並び替えと欠損値
続いては、表データを整える際の注意点を確認していきます。
SLOPE関数は、必ずしもX値を小さい順に並べ替えなくても計算できます。
ただし、散布図を作成して内容を確認するなら、時系列データは日付や時刻の順番に整えておくと分かりやすくなります。
空白、エラー値、別の単位の数値が混在する行は、事前に確認しましょう。
特に月別データで一部の月だけ入力漏れがあると、比較の前提が変わるかもしれません。
【操作のポイント】分析用の表では、見出しを1行目に置き、同じ行に対応するX値とY値を入力します。
傾きを求める数式の入力手順
続いては、実際にセルへSLOPE関数を入力して傾きを表示する手順について確認していきます。
| A列 | B列 | C列 | E列 |
|---|---|---|---|
| 月 | 広告費 | 売上 | 傾き |
| 4月 | 10 | 120 | =SLOPE(C2:C4,B2:B4) |
| 5月 | 15 | 145 | 5 |
数式を入力するセルの選択
それではまず、結果を表示するセルを選ぶ操作について解説していきます。
元データと重ならない空きセルを選択します。
この例では、E1に傾き、E2に計算結果を置くと表を見返しやすくなります。
E2をクリックし、半角でイコール記号を入力すると数式入力を開始できます。
続けてSLOPEと入力すると、候補にSLOPE関数が表示されます。
候補をクリックするか、Tabキーで選択すると関数名を入力できます。
関数名を直接すべて入力しても問題ありません。
範囲を指定する順番
続いては、既知のY値と既知のX値を指定する順番について確認していきます。
売上がC列、広告費がB列に入力されている場合、最初にC2からC4を選択します。
次に区切りのカンマを入力し、B2からB4を選択します。
=SLOPE(C2:C4,B2:B4)
Enterキーを押すと、計算結果がセルに表示されます。
関数の引数画面を利用する場合も、上側の既知のY値にC列、下側の既知のX値にB列を入力します。
列の位置ではなく、データが果たす役割で指定順を決めることが大切です。
オートフィルを使う場合
続いては、複数のグループごとに傾きを求める場合のオートフィルについて確認していきます。
たとえば商品別にB列とC列のデータが横方向へ並ぶ表では、最初の数式を作った後にフィルハンドルを右へドラッグできます。
フィルハンドルは、選択セルの右下に表示される小さな四角です。
ただし、全体の傾きを1つだけ求める表では、数式を下方向へコピーする必要はありません。
コピーする場合は、参照範囲が意図どおり移動しているか数式バーで確認しましょう。
【操作のポイント】傾きを1つだけ表示するなら、計算結果用のセルは表の右側か下側に分けて置きます。
散布図と近似曲線による傾き表示
続いては、散布図と近似曲線を利用して傾きを視覚的に確認する方法について解説していきます。
| 広告費 | 売上 | 散布図上の位置 |
|---|---|---|
| 10 | 120 | 左下 |
| 15 | 145 | 中央 |
| 20 | 170 | 右上 |
散布図の作成
それではまず、X値とY値から散布図を作成する操作について解説していきます。
広告費と売上のように数値同士の関係を見る場合は、折れ線グラフよりも散布図が適しています。
B1からC4までを選択し、挿入タブから散布図を選びます。
散布図では、横軸に最初の列である広告費、縦軸に次の列である売上が配置されます。
データが左下から右上に並んでいれば、正の傾きがあると視覚的に判断できます。
近似曲線の追加
続いては、散布図に近似曲線を追加する操作について確認していきます。
作成した散布図内の点をクリックすると、データ系列が選択されます。
選択後に右クリックし、近似曲線の追加を選択します。
近似曲線の書式設定では、通常は線形を選びます。
線形近似は、データを直線で表したい場合に使う方法です。
グラフ上で数式を確認したいときは、グラフに数式を表示する項目へチェックを入れます。
表示される式のXの係数が傾きです。
近似曲線の式が y = 5x + 70 の場合
傾きは5です。
切片は70です。
グラフと関数の使い分け
続いては、SLOPE関数と近似曲線の使い分けについて確認していきます。
SLOPE関数は、計算結果を別の数式や判定式に利用したい場合に便利です。
たとえば傾きが一定の基準を超えた月だけを抽出する分析にもつなげられます。
散布図は、データのばらつきや外れ値を見つけたい場合に向いています。
直線から大きく離れた点があれば、入力ミス、特殊な要因、季節変動などを確認するきっかけになります。
数値の計算にはSLOPE関数、説明や共有には散布図という使い分けが実務では分かりやすいでしょう。
【操作のポイント】近似曲線の数式を表示すると、傾きと切片をグラフ上で同時に確認できます。
傾きと切片を読み取る方法
続いては、回帰直線の傾きと切片をどのように読み取るかについて確認していきます。
| 近似式 | 傾き | 切片 |
|---|---|---|
| y = 5x + 70 | 5 | 70 |
| y = -2x + 100 | -2 | 100 |
傾きと切片の役割
それではまず、近似式に含まれる2つの数値について解説していきます。
一次関数の形では、Yは傾きにXを掛けた値へ切片を加えたものです。
Y＝傾き×X＋切片
傾きはXが1増えたときのYの変化量を表します。
切片はXが0のときに予測されるYの値です。
y = 5x + 70なら、Xが1増えるとYは5増え、Xが0ならYは70と読み取れます。
傾きだけでなく切片も見ることで、直線全体の意味を把握しやすくなります。
INTERCEPT関数との組み合わせ
続いては、切片をセルで求めるINTERCEPT関数について確認していきます。
SLOPE関数と同様に、INTERCEPT関数でもY値を先、X値を後に指定します。
=INTERCEPT(C2:C4,B2:B4)
この関数を使うと、近似直線の切片を計算できます。
傾きをE2、切片をE3に求めておけば、別セルで予測値を計算することも可能です。
=E2*B5+E3
上の式では、B5のX値に対する予測Y値を計算します。
予測値を扱うときの注意点
続いては、傾きを利用して予測値を出す際の注意点を確認していきます。
近似直線による予測は、過去データの傾向をもとにした目安です。
大幅な価格改定、天候、販促施策、市場環境の変化があれば、実際の値は予測から離れることがあります。
元データの範囲から大きく外れたX値を入れる場合は、特に慎重な判断が必要です。
予測値は意思決定の補助情報として使い、実績や前提条件とあわせて確認する姿勢が重要です。
【操作のポイント】傾きと切片を別セルへ表示すると、予測式の参照先が分かりやすくなります。
SLOPE関数でエラーになる原因
続いては、SLOPE関数でエラーや予想外の結果が表示される主な原因について確認していきます。
| 表示例 | 主な原因 | 確認内容 |
|---|---|---|
| #DIV/0! | X値がすべて同じ | X値に変化があるか |
| #N/A | 件数の不一致 | 範囲の行数 |
| 予想外の値 | 文字列や外れ値 | 入力形式 |
X値に変化がない場合
それではまず、#DIV/0!が表示される場合について解説していきます。
X値がすべて同じ数値の場合、傾きを定義できません。
たとえば広告費がすべて10で固定されているなら、広告費が増減したときの売上変化はデータから判断できないためです。
これはエクセルの故障ではなく、計算に必要な条件が不足している状態です。
X値には少なくとも2種類以上の異なる数値が必要になります。
範囲の件数が異なる場合
続いては、Y値とX値の範囲サイズが異なる場合について確認していきます。
Y値がC2からC10なのに、X値がB2からB9となっていると、対応するデータの組み合わせが不足します。
見出しを含めた範囲と含めない範囲が混ざることもよくある原因です。
数式バーをクリックし、各範囲の開始行と終了行を見比べましょう。
テーブル機能を使っている場合は、列見出しを含まないデータ部分を選択することも大切です。
文字列と外れ値の確認
続いては、計算結果が実感と合わない場合の確認方法について解説していきます。
数値の左上に緑色の三角形が表示されている場合は、数値が文字列として保存されている可能性があります。
セルを選択し、警告アイコンから数値に変換を選ぶと修正できる場合があります。
また、桁を1つ誤って入力した値などの外れ値は、傾きを大きく変えてしまいます。
散布図を表示すると、他の点から極端に離れたデータを見つけやすくなります。
数式だけで結果を確定せず、元表と散布図を両方確認することが信頼できる分析につながります。
【操作のポイント】エラーが出たときは、X値の変化、データ件数、数値形式の順に確認します。
まとめ エクセルで傾きを求める方法
エクセルで傾きを求めるには、SLOPE関数へ既知のY値と既知のX値を正しい順番で指定します。
基本となる数式は、=SLOPE(既知のY値,既知のX値)です。
広告費と売上の関係であれば、売上をY値、広告費をX値として選択します。
結果の数値は、X値が1増えたときにY値が平均してどれだけ変化するかを示します。
散布図へ線形の近似曲線を追加すれば、傾きの方向、データのばらつき、外れ値も視覚的に確認できます。
近似式に表示されるXの係数が傾きであり、定数部分は切片です。
範囲の行数が異なる、X値がすべて同じ、文字列が含まれるといった状態では、計算エラーや不自然な結果につながるため注意しましょう。
SLOPE関数による数値計算と散布図による可視化を組み合わせることで、データの変化をより分かりやすく分析できます。
