Excelで散布図に近似曲線を追加したものの、線が不自然に折れて見えたり、データの傾向に合っていないように感じたりすることがあります。
近似曲線を滑らかに見せるには、グラフの種類、系列の設定、近似式の種類、元データの並び順をまとめて確認することが大切です。
とくに対数近似、累乗近似、多項式近似はそれぞれ得意なデータの形が異なるため、見た目だけで選ぶと予測値や分析結果にずれが生じるかもしれません。
近似曲線を滑らかに表示するポイントは、散布図を使うこと、X値を昇順に整えること、データの増え方に合う近似の種類を選ぶことです。
この記事では、Excelの近似曲線を自然で見やすい形に整える方法と、対数近似・累乗近似・多項式近似の使い分けを詳しく解説していきます。
数式やグラフの設定を確認しながら、伝わる分析グラフを作成していきましょう。
エクセルの近似曲線を滑らかに表示する基本設定
それではまず、近似曲線を滑らかに表示するための基本設定について解説していきます。
| A列 時間 | B列 測定値 |
|---|---|
| 1 | 12 |
| 2 | 19 |
| 3 | 27 |
| 4 | 34 |
散布図による数値軸の設定
近似曲線を扱う際は、折れ線グラフではなく散布図を選ぶことが基本です。
折れ線グラフは横軸を項目として扱うため、1、2、10、100のように間隔が異なる数値でも、同じ間隔で配置される場合があります。
散布図は横軸と縦軸の両方を数値として処理するため、データの変化を実態に近い位置関係で表示できます。
表のA1からB5を選択してから、挿入タブの散布図を選び、「散布図（平滑線とマーカー）」または「散布図（直線とマーカー）」をクリックしましょう。
近似曲線そのものはExcelが計算して描くため、元の系列を平滑線にする必要はありません。
ただし、元データを結ぶ線も同時に見せたい場合には、平滑線とマーカーの散布図を使うと全体の印象がやわらかくなります。
散布図の横軸は数値軸です。
日付や時間をX値に使う場合も、文字列ではなくExcelが認識する日付シリアル値として入力しておくと、近似曲線の計算が安定します。
X値を昇順に並べるデータ整形
線が行ったり来たりするように見える場合は、X値の並び順を確認してください。
近似曲線は計算上の関数として描画されますが、系列の線やマーカーは表に入力された順番の影響を受けます。
A列の時間、距離、数量などのX値は、小さい順に並べるとグラフを読み取りやすくできます。
サンプルデータでは1行目を見出しにし、A2以降にX値、B2以降にY値を入力します。
表内のセルを選択し、データタブから昇順で並べ替えると、系列の接続順も整理されます。
途中に空白セル、文字列、エラー値が混ざっていると、近似曲線の対象範囲が意図せず変わる場合があります。
測定できなかった行を残す場合は、空欄の意味を統一し、グラフに含める範囲を明確にしておくことが重要です。
近似曲線追加による表示調整
散布図を作成したら、グラフ内のデータ点をクリックし、右クリックメニューから近似曲線の追加を選択します。
画面右側に近似曲線の書式設定が表示されるため、目的に合う近似の種類を選択しましょう。
近似曲線の色は元の系列と区別できる濃い色にし、線の幅を少し太くすると、分析対象がすぐに伝わります。
マーカーは小さく、近似曲線はやや太くする設定が、実測値と傾向を両立させやすい組み合わせです。
近似曲線の書式設定で、線を実線にし、透明度を下げすぎないことも見やすさにつながります。
【操作のポイント】近似曲線はデータ系列を選択してから追加します。グラフエリア全体を選んだ状態では追加先が分かりにくくなるため、マーカーを一度クリックして系列を選択しましょう。
対数近似による伸びの鈍化したデータ分析
続いては、対数近似が向いているデータの形と設定方法を確認していきます。
| A列 利用回数 | B列 習熟度 |
|---|---|
| 1 | 35 |
| 2 | 48 |
| 5 | 65 |
| 10 | 77 |
対数近似に適した変化の特徴
対数近似は、最初は大きく増えるものの、次第に増加量が小さくなるデータに適した方法です。
たとえば練習回数と作業スキル、広告表示回数と認知度、初期投資と効率改善のような関係で使われます。
Xが増えてもYの伸びが少しずつ緩やかになる場合は、対数近似を候補にできます。
対数近似の基本形は、Yがa×LN X＋bとなる式です。
対数近似の考え方
Y ＝ a×LN(X)＋b
Xが0以下になるとLN関数を計算できないため、横軸の値には正の数が必要です。
開始時点を0回として入力しているデータは、そのままでは対数近似を使えません。
分析の目的によっては、開始後1日目を1として扱うなど、意味を損なわない範囲でX値を見直す必要があります。
近似式と決定係数の表示
近似曲線の書式設定で対数近似を選んだら、「グラフに数式を表示する」と「グラフにR-2乗値を表示する」にチェックを入れます。
表示された数式は、データから推定された係数を確認するための材料です。
R-2乗値は決定係数とも呼ばれ、1に近いほど近似式がデータのばらつきを説明できている目安になります。
決定係数が高くても、業務上の意味がない近似式では分析に活用しにくいため、データの背景と合わせて判断することが必要です。
小数点以下の桁数が少ないと、表示された数式を別のセルで再計算したときに値が合わないことがあります。
近似式のラベルを選択し、表示形式で小数点以下の桁数を増やすと、検算しやすくなります。
対数近似の誤用を防ぐ確認
対数近似は、データ点を滑らかにつなぐためだけの機能ではありません。
増加が止まることや、一定の上限に必ず近づくことまで保証するものではない点に注意しましょう。
また、対数近似の結果をXの範囲外まで大きく延長すると、実際には起こりにくい予測になる場合があります。
散布図の点が右へ進むほど寝ていく形であり、測定条件にも同じような減速要因があるときに、対数近似の説得力が高まります。
一方で、途中から急に伸び始めるデータや周期的に上下するデータには、別の近似方法を検討したほうがよいでしょう。
【操作のポイント】対数近似を選ぶ前に、X値に0または負の数が含まれていないか確認します。エラーの原因を先に除くことで、設定作業がスムーズになります。
累乗近似による比率変化の表現
続いては、累乗近似が有効になる比率変化とグラフの読み方を確認していきます。
| A列 長さ | B列 面積 |
|---|---|
| 1 | 1.1 |
| 2 | 4.0 |
| 3 | 9.2 |
| 4 | 16.1 |
累乗近似の数式と利用場面
累乗近似は、Xの何乗かに比例してYが変わる関係を表すときに使います。
面積と長さ、体積と寸法、流速と圧力損失など、物理的な関係を持つデータで候補になりやすい近似です。
累乗近似の考え方
Y ＝ a×X^b
bが2に近い場合は、Xが2倍になるとYがおおむね4倍になる関係を示します。
XとYがともに正の値で、増え方が一定の差ではなく一定の倍率に近いとき、累乗近似が合う可能性があります。
グラフで右上がりが急になる形だけでなく、右へ進むほど緩やかに増える形も、指数bの値によっては累乗近似で表せます。
ゼロ値と負の値の取り扱い
累乗近似では、X値またはY値に0や負の数が含まれると、近似曲線を作成できないことがあります。
これは累乗の計算や対数変換を利用して係数を求める仕組みと関係しています。
0を含むデータを無理に累乗近似へ当てはめるのではなく、測定開始点の意味とデータの性質を確認することが優先です。
たとえば差分データに負の値がある場合は、累乗近似よりも線形近似、多項式近似、または別の統計手法が適することがあります。
元データを任意の定数でずらす処理は、近似の見た目を作れても、元の解釈を変えてしまう可能性があります。
加工する場合は、何をどの理由で変換したのかをグラフの注記や分析資料に残すと安心です。
両対数グラフによる関係の確認
累乗近似が適しているかを視覚的に確かめる方法として、横軸と縦軸を対数目盛にした両対数グラフがあります。
累乗の関係は、両対数グラフにすると直線に近い形で表れます。
軸を右クリックして軸の書式設定を開き、「対数目盛を表示する」を選ぶことで設定できます。
ただし、対数目盛は数値の間隔が通常の目盛と異なるため、グラフを見る人が理解できるよう軸ラベルや説明を添えましょう。
近似式の係数aと指数bは、単なる数値ではなく、対象の大きさや伸び方を読むための情報です。
【操作のポイント】累乗近似ではX値とY値の両方に0以下の値がないかを確認します。エラーが表示されたら、空白・文字列・符号を含めて表を見直しましょう。
多項式近似による曲線の滑らかな調整
続いては、多項式近似で曲線の形を整える方法と次数の選び方を確認していきます。
| A列 温度 | B列 反応量 |
|---|---|
| 10 | 18 |
| 20 | 32 |
| 30 | 39 |
| 40 | 31 |
多項式近似の次数選択
多項式近似は、上昇と下降、山形や谷形など、直線や単純な比率では表しにくい曲線的な変化を近似するときに役立ちます。
Excelでは次数を2から6まで選択でき、次数が大きくなるほど複雑な形に合わせられます。
2次多項式の基本形
Y ＝ a×X^2＋b×X＋c
2次式は、上に開く曲線または下に開く曲線を表す基本的な形です。
最初は2次または3次から試し、必要以上に次数を上げないことが、滑らかで説明しやすい近似曲線につながります。
次数を高くするとすべての点に近い線を作りやすくなりますが、それは必ずしも将来予測に強い式ではありません。
データ点の間を細かく蛇行する曲線は、測定誤差まで拾っている状態かもしれません。
近似曲線の書式設定画面
グラフ内の系列を右クリックし、近似曲線の追加を選ぶと、右側に近似曲線の書式設定ペインが開きます。
近似曲線のオプションから多項式近似を選択し、その下にある次数の入力欄で2、3、4のように設定します。
まず2次を選んでグラフの大まかな山や谷を確認し、説明できない偏りが残る場合だけ次数を一つ上げる流れが実務的です。
次数を変更するたびに、曲線がデータ点の周辺でどのように動くかを確認してください。
グラフ上の数式とR-2乗値も表示すると、見た目と数値の両面から比較できます。
ただしR-2乗値だけを基準にすると高次数が有利になりやすいため、利用目的とデータ数を優先して選ぶことが必要です。
過剰適合を避けるグラフの読み方
多項式近似で注意したい点は、過剰適合です。
過剰適合とは、限られたデータに細かく合わせすぎた結果、新しいデータでは外れやすくなる状態を指します。
曲線が不自然に何度も上下する場合は、滑らかに見えても分析としては信頼しにくい可能性があります。
測定点が6個しかないデータに6次の多項式を使うと、すべての点を通る式に近づけることはできますが、偶然のばらつきまで表現してしまいます。
予測が目的なら、作成に使わなかったデータで近似式を確認する方法も有効です。
【操作のポイント】多項式近似は次数を上げるほど正確に見えやすくなります。グラフの端で急に跳ね上がる線が出た場合は、次数を下げて比較しましょう。
近似式の数式化と予測値の算出
続いては、グラフに表示した近似式をセルで利用し、予測値を求める方法を確認していきます。
| A列 X値 | B列 実測値 | C列 予測値 |
|---|---|---|
| 1 | 12 | 13.2 |
| 2 | 19 | 18.7 |
| 3 | 27 | 24.2 |
表示された近似式のセル入力
グラフに表示された近似式を使えば、任意のX値に対するYの予測値をセルで計算できます。
たとえばグラフの式がY＝5.5X＋7.7なら、C2セルには次のような式を入力します。
=5.5*A2+7.7
サンプルデータでは1行目が見出しであるため、最初の計算式はC2に入力します。
入力後にC2セル右下のフィルハンドルを下方向へドラッグすると、C3以降へ数式をコピーできます。
予測式の係数は、グラフに表示された小数点以下の桁数では足りない場合があるため、近似式ラベルの表示桁数を増やしてから転記します。
対数近似と累乗近似のセル計算
対数近似で表示された式がY＝12.3LN X＋8.1の場合は、ExcelのLN関数を利用します。
=12.3*LN(A2)+8.1
累乗近似で表示された式がY＝2.4X^1.8の場合は、べき乗演算子を使って計算できます。
=2.4*A2^1.8
数式を入力する際、Xをそのまま書くのではなく、A2のようにX値が入ったセル参照へ置き換えます。
セル参照で予測式を作ると、A列の値を変更するだけで予測値が自動更新されます。
ただし、対数近似の数式に0以下の値を渡すとエラーになるため、入力規則やIF関数で事前に確認する設計も有効です。
実測値と予測値の差分確認
近似式がどの程度データに合っているかを確認するには、実測値と予測値の差分を別列に計算します。
D2セルに=B2-C2と入力すると、実測値から予測値を引いた残差を確認できます。
残差が特定の範囲だけ大きくなっている場合は、近似の種類が合っていない、外れ値がある、別の要因が影響しているといった可能性を考えられます。
残差がランダムに散らばる状態は、近似式が一定の偏りを持ちにくい一つの目安です。
近似曲線の線だけで判断せず、表の予測値と差分も並べて確認すると、分析の根拠が明確になります。
【操作のポイント】数式を下へコピーする前に、係数の小数点以下の桁数を確認します。丸めた係数で計算すると、グラフ上の曲線とセルの予測値に差が出ることがあります。
まとめ エクセルの近似曲線を滑らかに表示する方法
エクセルの近似曲線を滑らかに表示するには、まず横軸と縦軸を数値として扱える散布図を使い、X値を昇順に整えることが基本です。
データが初期に大きく伸びてから緩やかになる場合は対数近似、比率やべき乗の関係を持つ場合は累乗近似、山や谷のある曲線的な変化には多項式近似を検討します。
多項式近似は次数を上げすぎず、データの背景を説明できる最小限の形を選ぶことが重要です。
近似式とR-2乗値をグラフに表示し、必要に応じてセルへ数式を入力すれば、予測値と実測値の差分まで確認できます。
見た目の滑らかさだけを追うのではなく、近似曲線が示す変化に業務上・分析上の意味があるかを確かめながら、Excelグラフを活用していきましょう。
