Excelで散布図や折れ線グラフを作成したにもかかわらず、近似曲線が表示されない、グラフ要素から追加できない、または追加したはずの線が見えないことがあります。
原因はグラフの種類、データの形式、系列の選択状態、軸の設定などさまざまです。
特に折れ線グラフのカテゴリ軸や文字列として入力された数値は、近似曲線の追加を妨げる代表的な要因になります。
近似曲線が表示されないときの確認ポイントです。
・散布図または対応するグラフ種類を選ぶ
・数値データが文字列になっていないか確認する
・グラフではなく系列そのものを選択する
・軸の範囲や線の書式を見直す
この記事では、近似曲線を追加できない原因から、グラフ上で見えない場合の調整方法、数式と決定係数の活用方法まで順に解説します。
近似曲線を追加できない場合のグラフ種類の確認
それではまず、近似曲線を追加できない場合に最初に見直したいグラフ種類について解説していきます。
| 月 | 広告費 | 売上 |
|---|---|---|
| 1月 | 10 | 120 |
| 2月 | 15 | 148 |
| 3月 | 20 | 171 |
| 4月 | 25 | 198 |
散布図を選ぶ理由
近似曲線は、横軸と縦軸に並ぶ数値の関係をもとに、データの傾向を線として表す機能です。
そのため、広告費と売上、気温と販売数、作業時間と生産量のように、二つの数値の関係を分析する場合は散布図を使うことが基本です。
散布図では横軸も縦軸も数値軸として扱われるため、各点の間隔を実際の数値どおりに配置できます。
一方で、通常の折れ線グラフでは横軸がカテゴリ軸になりやすく、1月、2月、3月のような項目を同じ間隔で配置します。
横軸に不規則な数値を使いたいとき、折れ線グラフのままでは近似の意味が変わってしまう可能性があります。
グラフを選択してから、［グラフのデザイン］タブの［グラフの種類の変更］をクリックし、［散布図］の［直線とマーカー］または［マーカーのみ］を選びましょう。
データ点だけを確認したい場合はマーカーのみ、推移も視覚的に追いたい場合は直線とマーカーが扱いやすい選択です。
近似曲線に対応しないグラフの種類
円グラフ、ドーナツグラフ、レーダーグラフ、積み上げグラフなどでは、近似曲線を追加できないことがあります。
これらは割合、構成比、複数系列の積み上がりを見せる目的が強く、数値間の回帰関係を求める用途には向いていません。
また、ピボットグラフでは機能に制約があり、近似曲線のコマンドが表示されない場合があります。
ピボットテーブルの集計値を分析したい場合は、集計結果を通常のセル範囲へコピーし、その範囲から通常の散布図を作成する方法が確実です。
バブルグラフでも近似曲線を使える場面はありますが、バブルの大きさは回帰計算に直接反映されません。
横軸と縦軸に使う二項目を明確にし、分析対象に合ったグラフを選ぶことが大切です。
系列を選択して追加する手順
グラフをクリックしただけでは、グラフ全体が選択されている状態になることがあります。
近似曲線はグラフ全体ではなく、特定のデータ系列に対して追加する要素です。
散布図内の点または線をもう一度クリックし、対象系列だけを選択してください。
選択できると、データ点の周囲に小さな選択ハンドルが表示されます。
その状態で右クリックし、［近似曲線の追加］を選択します。
右クリックのメニューに項目がない場合は、グラフ右上の［＋］をクリックして［近似曲線］にチェックを入れる方法もあります。
複数の系列があるグラフでは、売上の系列に追加するのか、利益の系列に追加するのかを間違えないよう注意しましょう。
【操作のポイント】グラフの余白ではなく、近似させたい点や線をクリックしてから［近似曲線の追加］を実行します。
数値データと空白セルの確認
続いては、近似曲線の計算対象となる数値データと空白セルを確認していきます。
| No. | 投入時間 | 生産数 | 確認結果 |
|---|---|---|---|
| 1 | 1 | 38 | 数値 |
| 2 | 2 | 75 | 数値 |
| 3 | 3時間 | 112 | 文字列の可能性 |
| 4 | 151 | 空白 |
文字列として保存された数値
見た目は数字でも、セル内で文字列として保存されていると、Excelは計算用の数値として正しく認識できないことがあります。
セル左上に緑色の三角形がある、数値が左寄せになっている、数式バーの先頭にアポストロフィがある場合は要確認です。
たとえば「3時間」や「10件」は数値に単位が含まれているため、そのままでは純粋な数値データになりません。
単位は見出しに記載し、データセルには3、10のような数値だけを入力する形が適切です。
文字列の数字を数値へ変換するには、警告アイコンから［数値に変換］を選ぶ方法があります。
ほかにも、空いているセルに1を入力してコピーし、対象範囲を選択して［形式を選択して貼り付け］から［乗算］を実行する方法があります。
値を1倍することで、文字列型の数字を数値型へ一括変換できる場合があります。
数値へ変換した後は、セルを選択して数式バーの内容を確認しましょう。
数値として認識されていれば、SUM関数やAVERAGE関数でも正しく計算できます。
エラー値と空白セルの扱い
近似曲線を作る範囲に#N/A、#VALUE!、#DIV/0!などのエラー値が含まれると、グラフや回帰計算が期待どおりにならないことがあります。
エラーを空白に見せるためにIFERROR関数を使っている場合も、グラフの設定によっては欠損値として扱われます。
空白セルが途中にあること自体は必ずしも問題ではありませんが、横軸と縦軸の対応が崩れないように注意が必要です。
1行目をヘッダーとしたサンプルデータで、A列に投入時間、B列に生産数を入力したとします。
空白を除いて数値の個数を確認するには、次のような数式を使えます。
=COUNT(A2:A20)
この数式は、A2からA20の中にある数値セルの個数を返します。
COUNTの結果が想定より少ない場合、空白セルや文字列として保存された数字が混在しているかもしれません。
縦軸側も=COUNT(B2:B20)で確認し、対応するデータ行に漏れがないかを見直しましょう。
データ範囲の再設定
近似曲線が追加できても、意図した場所に表示されない場合は、グラフが参照しているデータ範囲を確認します。
グラフを右クリックして［データの選択］を開くと、系列名、系列Xの値、系列Yの値を確認できます。
散布図では、横軸の値と縦軸の値の件数をそろえる必要があります。
たとえばXの値がA2:A10で9件なのに、Yの値がB2:B11で10件になっていると、系列設定が不適切になることがあります。
見出し行を値の範囲に含めないことも重要です。
1行目がヘッダーなら、系列Xの値はA2:A20、系列Yの値はB2:B20のように指定します。
範囲を修正後にグラフを作り直すと、データの混在や古い参照が解消される場合もあります。
【操作のポイント】散布図の系列設定では、横軸と縦軸の数値範囲を同じ行数にそろえ、見出し行は含めません。
近似曲線の種類と追加設定
続いては、近似曲線の種類と追加時に選ぶ設定について確認していきます。
| 近似曲線の種類 | 適した傾向 | 注意点 |
|---|---|---|
| 線形 | 一定の割合で増減 | 最初に試しやすい |
| 指数 | 急速な増加や減少 | 0以下の値に注意 |
| 多項式 | 曲線的な変化 | 次数を上げすぎない |
線形近似曲線の基本
データがほぼ一定の割合で増える、または減る傾向にあるなら、線形近似曲線から試すのが分かりやすい方法です。
線形近似曲線は、一般にy=ax+bの形で表されます。
y=ax+b
yは予測したい値、xは原因側の数値、aは傾き、bは切片です。
広告費が1増えると売上がどの程度増えるのかを見たい場合、aの値が増加量の目安になります。
グラフ上で系列を右クリックして［近似曲線の追加］を選び、［線形］をクリックすれば追加できます。
データ点が一直線に近いほど、線形近似の説明力は高くなります。
ただし、実際のデータが曲線的に変化している場合は、無理に直線で表すと傾向を取り違える可能性があります。
多項式と移動平均の使い分け
売上が季節によって上下する、ある時点から増加率が変わるといった場合には、多項式近似曲線を検討できます。
多項式は二次、三次などの次数を指定でき、曲がった傾向を表現できます。
ただし、次数を高くするほどデータ点に過度に合わせた線になり、将来予測には使いにくくなることがあります。
見た目がぴったり合うことと、予測に適していることは同じではありません。
まずは二次を試し、必要がある場合だけ三次へ進む程度が実務では扱いやすいでしょう。
一方、時系列データの細かな変動をならして方向性を見たいなら、移動平均が適しています。
移動平均は回帰式ではなく、指定した期間の平均値をつないで変動を滑らかに表示する手法です。
月ごとの売上推移など、時間の順番が意味を持つデータでは有効な選択になります。
追加オプションと予測区間
［近似曲線の書式設定］では、前方予測と後方予測の期間を入力できます。
前方予測に3を入力すると、現在の最終データ点より先の3期間まで近似曲線を延長して表示できます。
ただし、これは近似式を機械的に延長した結果であり、将来の確定値を示すものではありません。
外部環境の変化、季節性、キャンペーンの実施などを考慮しない予測には限界があります。
切片を指定する項目は、理論上xが0のときにyが必ず0になるなど、明確な根拠があるときだけ使いましょう。
根拠なく切片を0に固定すると、データ本来の傾向が歪むことがあります。
【操作のポイント】最初は線形近似を確認し、データの形に明確な曲がりがある場合だけ多項式や移動平均を検討します。
グラフ上で近似曲線が見えない場合の書式設定
続いては、追加済みの近似曲線がグラフ上で見えない場合の書式設定を確認していきます。
| 確認箇所 | 起こりやすい状態 | 調整方法 |
|---|---|---|
| 線の色 | 背景や系列と同色 | 目立つ色へ変更 |
| 線の太さ | 細すぎる | 1.5pt以上を目安 |
| 軸の範囲 | 表示範囲外 | 最小値と最大値を見直す |
近似曲線の線色と太さ
近似曲線を追加していても、データ系列の線と同じ色、または非常に薄い色になっていると存在に気付きにくいことがあります。
近似曲線をクリックして選択し、右クリックから［近似曲線の書式設定］を開きます。
［塗りつぶしと線］の［線］で［単色線］を選び、系列と異なる色に設定してください。
赤、濃い青、黒など、背景とのコントラストが高い色が見やすいでしょう。
線の幅は1.5ptから2.25pt程度にすると、印刷時にも確認しやすくなります。
データ系列の線を細くし、近似曲線を少し太くすることで、実測値と傾向線の役割が明確になります。
近似曲線は実測値そのものではなく、複数の点から計算した傾向を示す線です。
凡例名を変更する場合は、実績、近似、予測など意味が分かる名称を使うと読み手に伝わりやすくなります。
軸の最小値と最大値
縦軸の最大値が非常に大きいと、近似曲線とデータ点がグラフ下部に圧縮され、線の違いを見分けにくくなります。
反対に軸の表示範囲が不自然に狭いと、線の一部が切れているように見えることがあります。
縦軸を右クリックして［軸の書式設定］を開き、［軸のオプション］にある境界値を確認しましょう。
自動設定で問題がある場合は、最小値と最大値をデータに合わせて入力します。
たとえば売上が120から200の範囲なら、最小値を100、最大値を220程度にすると変化を読み取りやすくなります。
ただし、比較対象のグラフが複数ある場合は、都合よく軸範囲を変えると比較が誤解を招くことがあります。
同じ指標を比較するグラフでは、軸の範囲を統一する配慮も必要です。
Excel画面での近似曲線の確認
近似曲線の設定画面を開くと、線の種類、予測、グラフに数式を表示する設定をまとめて確認できます。
上のイメージ図のように、グラフのデザインから近似曲線を選び、右側の書式設定で線の表示内容を確認できます。
赤枠で示した箇所は、追加できたかどうかと、線が見えにくい状態になっていないかを判断する重要な場所です。
グラフのサイズが小さい場合も、近似曲線と系列が重なって見えることがあります。
グラフの角をドラッグして横幅を広げると、傾向線や数式の表示を読み取りやすくできます。
【操作のポイント】近似曲線が見えないときは、追加の有無だけでなく、線色、太さ、軸の範囲、グラフサイズを順に確認します。
近似式と決定係数の表示
続いては、近似式と決定係数をグラフ上に表示して分析へ活用する方法を確認していきます。
| 表示項目 | 読み取れる内容 | 用途 |
|---|---|---|
| 近似式 | xとyの関係 | 予測値の計算 |
| R二乗値 | 当てはまりの程度 | モデルの比較 |
| 前方予測 | 延長した傾向線 | 参考値の確認 |
グラフに数式を表示する設定
近似曲線を選択して［近似曲線の書式設定］を開くと、［グラフに数式を表示する］というチェック項目があります。
ここにチェックを入れると、たとえばy=5.2x+68.4のような式がグラフ内に表示されます。
この式では、xに広告費などの横軸の値を入れることで、yに相当する売上などの推定値を求められます。
数式がグラフの端に重なって見づらい場合は、数式表示をドラッグして空いている位置へ移動できます。
表示桁数が少なすぎると計算結果に差が出ることがあるため、必要に応じて数式ラベルを右クリックして表示形式を調整しましょう。
近似式は分析用の目安であり、元データのすべての事情を説明するものではありません。
決定係数の見方
［グラフにR-2乗値を表示する］にチェックを入れると、決定係数が表示されます。
R二乗値は0から1に近い数値で表され、一般に1へ近いほど近似曲線がデータのばらつきをよく説明していると考えられます。
たとえばR二乗値が0.95なら、線形モデルがデータの変動を比較的よく表している可能性があります。
一方で、R二乗値だけを見て判断することはできません。
データの件数が少ない場合や、偶然の外れ値がある場合には、高い値でも安定した関係とは限りません。
決定係数は近似の良し悪しを考える材料の一つとして扱い、データの背景や業務上の条件も併せて確認しましょう。
R二乗値が低い場合は、近似曲線の種類を変える前に、入力ミス、外れ値、異なる条件のデータが混ざっていないかを確認します。
数式をセルで利用する方法
グラフ上の近似式を使って予測値をセルに出したい場合は、傾きと切片を関数で求める方法があります。
1行目がヘッダーで、A列に広告費、B列に売上が入力されている場合を考えます。
傾きはSLOPE関数、切片はINTERCEPT関数で求められます。
=SLOPE(B2:B20,A2:A20)
=INTERCEPT(B2:B20,A2:A20)
SLOPE関数の最初の引数には縦軸側の既知のy、二番目の引数には横軸側の既知のxを指定します。
INTERCEPT関数も同じ順序で指定し、直線がy軸と交わる位置を求めます。
たとえばD2に広告費の予測条件を入力し、E2に推定売上を出す場合は、次の式を使えます。
=SLOPE($B$2:$B$20,$A$2:$A$20)*D2+INTERCEPT($B$2:$B$20,$A$2:$A$20)
D2だけを相対参照にしているため、E2の右下にあるフィルハンドルを下へドラッグすれば、D3、D4の広告費に対応する予測値をオートフィルできます。
元データの範囲は絶対参照で固定することが、数式をコピーするときの重要なポイントです。
【操作のポイント】近似式を予測へ使う場合は、SLOPE関数とINTERCEPT関数で傾きと切片を求め、元データ範囲を絶対参照にします。
近似曲線が追加できない場合の確認手順のまとめ
エクセルで近似曲線が表示されない、または追加できない場合は、まずグラフの種類を散布図に変更し、近似させたいデータ系列を選択することが基本です。
折れ線グラフや円グラフなどでは、目的や機能の制約によって近似曲線を扱えない場合があります。
次に、横軸と縦軸に使うセルが数値として認識されているか、空白、エラー、見出し行の混入がないかを確認しましょう。
見た目が数字でも文字列では正しく分析できないため、単位をセル内へ混ぜないことも大切です。
近似曲線を追加済みなのに見えない場合は、線の色、太さ、グラフの大きさ、軸の最小値と最大値を見直します。
近似式とR二乗値を表示すれば、単なる見た目の線ではなく、数値にもとづく傾向分析へつなげられます。
ただし、近似曲線は将来を保証する機能ではありません。
データの件数、外れ値、季節変動、業務上の条件を考慮しながら、傾向を読み解くための補助線として活用していきましょう。
