近似式のエクセルでの出し方は？関数や手順も！（エクセル近似式：グラフ：近似曲線：TREND関数など）

近似式のエクセルでの出し方は？関数や手順も！（エクセル近似式：グラフ：近似曲線：TREND関数など）

Excelで実測データを扱うと、値の傾向を数式として表したい場面が出てきます。

売上予測や品質管理、実験結果の整理では、データのばらつきから全体の流れをつかむことが重要です。

そこで役立つのが、グラフの近似曲線とTREND関数を使った近似式の作成です。

本記事では、散布図から近似式を表示する方法、関数で計算する方法、精度を確認するポイントまでを順番に解説します。

Excelで近似式を出す基本手順

それではまず、Excelで近似式を表示する基本手順について解説していきます。

近似式に向くデータの並べ方

近似式を作る前に、元となるデータを見やすく整える必要があります。

一般的には、横軸にしたい説明変数を左列へ、縦軸にしたい目的変数を右列へ入力します。

たとえば広告費と売上の関係を見るなら、A列に広告費、B列に売上を入力する形が分かりやすいでしょう。

日付と売上高のような時系列データでも近似はできますが、月ごとの変動や季節性が強い場合は、単純な直線だけで判断しないことが大切です。

空白セル、文字列、単位が混ざった数値があると、グラフや関数の結果が意図と異なる場合があります。

単位は見出し行に記載し、計算対象のセルには数値だけを入力すると管理しやすくなります。

散布図を作成する操作

近似式を確認するなら、まず散布図を作る方法がおすすめです。

散布図は横軸と縦軸の数値関係をそのまま示せるため、直線的な傾向か、曲線的な傾向かを視覚的に確認できます。

データ範囲を選択したら、挿入タブから散布図を選びます。

折れ線グラフでも値の推移は見られますが、横軸を数値として正しく扱いたい場合は散布図が適しています。

グラフを作成した後は、横軸と縦軸が逆になっていないかを確認してください。

横軸が連番ではなく、入力した数値の間隔で表示されていれば設定は概ね適切です。

近似曲線と数式を表示する設定

散布図上の点を選択し、右クリックから近似曲線の追加を選びます。

表示される設定画面で線形を選択すると、一次式による近似曲線がグラフ上に追加されます。

さらにグラフに数式を表示する項目と、グラフにR二乗値を表示する項目へチェックを入れましょう。

これにより、y＝ax＋bの形の数式と、データへの当てはまりを判断するR二乗値を同時に確認できます。

数式だけでなくR二乗値も表示することが、近似式を実務で使う際の基本です。

近似曲線の種類と選び方

続いては、近似曲線の種類と選び方を確認していきます。

線形近似の特徴

線形近似は、y＝ax＋bの形で表す最も基本的な方法です。

xが一定量増えたときに、yもほぼ一定量ずつ増減する関係に向いています。

たとえば作業時間と加工費、距離と燃料費、数量と変動費などは、ある範囲では直線で近似しやすいでしょう。

式のaは傾きであり、xが1増えるごとにyがどの程度変化するかを表します。

bは切片で、xが0のときの推定値です。

ただし、xが0という条件が現実には存在しない場合、切片そのものへ強い意味を持たせすぎないほうが安全です。

多項式近似と曲線的な変化

データが途中から急に増加したり、山なりになったりする場合は、多項式近似を検討します。

二次式ならy＝ax²＋bx＋cとなり、直線では表せない曲がり方を反映できます。

Excelでは近似曲線の設定から多項式を選び、次数を指定します。

次数を高くするとデータ点には近づきやすくなりますが、将来予測には不向きになることがあります。

見た目に合う曲線と、予測に使える式は同じとは限りません。

少ないデータに高次数の式を当てはめると、偶然の上下動まで表現してしまうため注意が必要です。

指数、対数、移動平均の使い分け

増加率が大きくなる傾向なら指数近似、初期に大きく変化して次第に緩やかになるなら対数近似が候補です。

指数近似は、yが正の値で推移するデータに適しており、成長率や減衰率の分析で使われます。

対数近似は、習熟曲線や広告出稿の初期効果など、伸びが徐々に小さくなる現象で利用しやすい方法です。

一方、日々の売上のように短期変動をならして流れを見たいだけなら、移動平均が便利です。

移動平均は厳密な関数式を出す目的よりも、変動の中にあるトレンドを見つける目的に適しています。

近似の種類	向いている傾向	主な利用場面
線形	一定の割合で増減	コスト、数量、距離
多項式	山形、谷形、曲線変化	実験値、温度特性
指数	増加率や減少率が大きい	成長、減衰、複利
対数	初期変化が大きく後半が緩やか	習熟、反応、広告効果
移動平均	短期的な上下をならしたい	売上、アクセス、在庫

TREND関数による予測値の算出

続いては、TREND関数による予測値の算出方法を確認していきます。

TREND関数の基本構文

TREND関数は、既知のデータから線形回帰による予測値を返す関数です。

グラフ上に数式を表示するだけでなく、セル内で将来値や任意条件の推定値を求めたい場合に役立ちます。

基本的な構文は、TREND（既知のyの範囲、既知のxの範囲、新しいxの範囲、定数）です。

最後の定数は省略できますが、通常は省略して問題ありません。

既知のyには結果の列、既知のxには原因や条件の列を指定します。

新しいxには、予測したい値が入ったセルまたは範囲を指定してください。

複数の予測値をまとめて出す方法

予測したいxの値が複数ある場合、新しいxとして複数セルの範囲を指定できます。

Excelのバージョンによっては、TREND関数の結果が下方向へ自動的に展開されます。

たとえばA7からA12へ将来の月番号や条件値を並べ、B7にTREND関数を入力すると、対応する予測値を一覧にできます。

結果が展開されない環境では、必要なセル範囲を選択してから数式を確定する方法が必要になる場合もあります。

元データ範囲と予測対象範囲を分けて管理することで、更新時のミスを減らせます。

予測値には実測値と異なる書式を設定しておくと、表を見た人にも推定値だと伝わりやすくなるでしょう。

LINEST関数との違い

TREND関数は予測値を求めるための関数であり、LINEST関数は回帰式の係数や統計情報を取得するための関数です。

傾きと切片だけを別々に確認したい場合は、SLOPE関数とINTERCEPT関数も使えます。

たとえば予測値を見積書へ反映するだけならTREND関数で十分なケースが多いでしょう。

一方で、分析資料として傾き、切片、決定係数などを示したいときはLINEST関数やグラフの近似曲線が便利です。

目的が予測なのか、分析結果の説明なのかを先に決めると、使う機能を選びやすくなります。

グラフの近似式をセル計算へ活用する方法

続いては、グラフの近似式をセル計算へ活用する方法を確認していきます。

表示された係数を数式に入力する方法

グラフに表示された近似式は、セルへ直接入力して計算にも使えます。

たとえばy＝2.35x＋10.8と表示された場合、xの値がA2にあるなら、別セルへ2.35＊A2＋10.8という考え方で入力します。

ただし、グラフに表示される係数は桁数が丸められていることがあります。

そのため、表示式をそのまま転記すると、TREND関数やLINEST関数の結果とわずかに違う場合があります。

特に単価計算や品質判定など、細かな差が問題になる用途では注意しましょう。

係数を正確に取得する関数

より正確な近似式をセルで作りたい場合は、SLOPE関数とINTERCEPT関数を利用します。

SLOPE関数は直線の傾き、INTERCEPT関数は切片を求めます。

傾きを別セル、切片を別セルに出しておけば、参照式を使って多くの予測値を計算できます。

係数を変更する必要がある場合も、参照先のデータ範囲を調整するだけで済むため、再利用しやすい方法です。

グラフ表示用の数式と、実務計算用の数式は分けると考えると整理しやすくなります。

予測値をグラフへ追加する方法

実測値と予測値を比較したい場合は、予測値の列をグラフに新しい系列として追加します。

実測値はマーカー付き、予測値は線のみといった形式にすると、違いがひと目で分かります。

予測範囲を過去データより先まで広げれば、近似式による将来予測を視覚化できます。

ただし、過去の範囲を大きく超えた予測は外挿と呼ばれ、誤差が大きくなりやすい点に注意してください。

特に需要予測では、価格改定、季節要因、競合状況など、数式に含まれない条件も結果へ影響します。

R二乗値と近似精度の確認ポイント

続いては、R二乗値と近似精度の確認ポイントを確認していきます。

R二乗値の見方

R二乗値は決定係数とも呼ばれ、近似式がデータの変動をどの程度説明できているかを示す指標です。

値は0から1の範囲で表示され、1に近いほどデータへの当てはまりが良いと考えられます。

ただし、R二乗値が高いからといって、必ずしも将来予測が正確とは限りません。

過去データに偶然合っているだけの可能性や、重要な外部要因が抜けている可能性もあります。

R二乗値は判断材料の一つであり、絶対的な合格基準ではありません。

残差から外れ値を確認する方法

残差とは、実測値と近似式による予測値との差です。

残差を計算してグラフ化すると、近似式が特定の範囲で偏っていないかを確認できます。

一部の点だけが大きく離れている場合は、入力ミス、測定条件の違い、一時的な要因などを調べる必要があります。

外れ値を削除する前には、なぜその値が出たのかを記録として残すことが大切です。

都合の悪い値を除外して式をきれいに見せると、分析結果の信頼性を損ねるおそれがあります。

精度を高めるデータの扱い方

近似精度を高めるには、データ数を増やすだけでなく、条件をそろえることも重要です。

測定方法、集計単位、対象期間が混ざっていると、関係性が不明確になります。

売上データなら、キャンペーン期間や休日の影響を区別しておくと分析しやすくなります。

製造データなら、材料ロット、設備条件、担当工程などを補助情報として残すと原因を追いやすいでしょう。

近似式の精度は関数操作だけでなく、元データの品質で大きく変わります。

近似式作成時のエラーと注意点

続いては、近似式作成時のエラーと注意点を確認していきます。

数値が文字列になっているケース

セルに見た目は数字でも、文字列として保存されていると計算やグラフが正しく動かないことがあります。

左寄せ表示、エラー表示、セル左上の警告マークなどがあれば確認しましょう。

全角数字、先頭のアポストロフィ、不要な空白なども原因になります。

データの形式を数値へ統一し、桁区切りや小数点の扱いも確認してください。

CSVから貼り付けたデータでは、この問題が起きやすい傾向があります。

近似曲線が追加できないケース

グラフの種類によっては、希望する近似曲線を追加できない場合があります。

横軸と縦軸の数値関係を分析するなら、散布図を選ぶのが基本です。

また、データが1点しかない、空白やエラー値が多いといった状態では、近似結果を適切に出せません。

指数近似や対数近似では、0以下の値が含まれると利用できない場合もあります。

エラーが出たときは関数式より先に、データ範囲と値の種類を見直すことが近道です。

予測結果を過信しない考え方

近似式は、既存データから一定の規則性を見つける方法です。

そのため、制度変更、市場環境の変化、設備故障のような突発要因までは自動で反映できません。

特に長期予測では、近似式の結果を単一の答えとして扱わず、強気、中立、慎重といった複数シナリオを用意すると実務に役立ちます。

予測値の近くに許容幅を示すことで、数値が持つ不確実性も共有しやすくなるでしょう。

近似式をExcelで出す方法のまとめ

Excelで近似式を出すには、まずxとyの関係が分かる形でデータを整理し、散布図へ近似曲線を追加する方法が分かりやすい手順です。

グラフ上で数式とR二乗値を表示すれば、傾向と当てはまりを視覚的に確認できます。

セル内で予測値を計算したい場合はTREND関数、傾きや切片を使って式を組み立てたい場合はSLOPE関数とINTERCEPT関数が便利です。

線形、多項式、指数、対数などの種類は、データの形と利用目的に合わせて選んでください。

近似式は正確な未来を保証するものではなく、データの傾向を判断しやすくするための道具です。

R二乗値、残差、外れ値、元データの条件を確認しながら使うことで、Excelの分析結果をより実務的に活用できるでしょう。