LINEST 関数
適用対象: Microsoft Excel 2010, Excel Web App, SharePoint Online for enterprises, SharePoint Online for professionals and small businesses
内容
"最小二乗法" を使って指定したデータに最もよく適合する直線を算出し、この直線を記述する配列を返すことによって直線の補正項を計算します。LINEST 関数を他の関数と共に使用して、多項式近似、対数近似、指数近似、べき級数をはじめとする、不明なパラメーター内で線形近似を示す他の種類のモデルの統計を計算することもできます。この関数は値の配列を返すため、配列数式として入力する必要があります。方法については、この記事の「使用例」の後に示します。
y = mx + b
または
y = m1x1 + m2x2 + ... + b
これは、x の値が複数の範囲にある場合に適用されます (ここで、従属変数 y は独立変数 x の関数です)。m の値はそれぞれの x の値に対応する係数であり、b は定数です。y、x、および m はベクトル (1 次元配列) とすることもできます。LINEST 関数が返す配列は、{mn,mn-1,...,m1,b} となります。また、回帰直線の補正項も追加情報として返されます。
書式
LINEST(既知の y, [既知の x], [定数], [補正])
書式
- 既知の y 必ず指定します。既にわかっている y の値の系列であり、y = mx + b という関係になります。
- "既知の y" の配列が 1 つの列に入力されている場合、"既知の x" の各列はそれぞれ異なる変数であると見なされます。
- "既知の y" の配列が 1 つの行に入力されている場合、"既知の x" の各行はそれぞれ異なる変数であると見なされます。
- 既知の x 省略可能です。既にわかっている x の値の系列であり、y = mx + b という関係になります。
- "既知の x" の配列には、1 つまたは複数の変数の系列を指定できます。変数の系列が 1 つである場合、"既知の y" と "既知の x" は、両者の次元が同じであれば、どのような形の範囲であってもかまいません。変数の系列が複数である場合、"既知の y" はベクトル (高さが 1 行、または幅が 1 列のセル範囲) である必要があります。
- "既知の x" を省略すると、"既知の y" と同じサイズの {1,2,3,...} という配列を指定したと見なされます。
- 定数 省略可能です。定数 b を 0 にするかどうかを論理値で指定します。
- 補正 省略可能です。回帰直線の補正項を追加情報として返すかどうかを論理値で指定します。
| 補正項 | 説明 |
|---|---|
| se1,se2,...,sen | 係数 m1,m2,...,mn に対する標準誤差の値です。 |
| seb | 定数 b に対する標準誤差の値です ("定数" が FALSE の場合、seb = #N/A となります)。 |
| r2 | 確実度の係数です。予測される y の値と実際の y の値を比較して、0 から 1 の範囲の数値を計算します。この数値が 1 である場合、標本に完全な相関関係が存在することになります。予測される y の値と実際の y の値の間に差異はありません。逆にこの係数の値が 0 である場合、回帰直線の演算式は y の値の予測にはほとんど役立ちません。r2 の計算方法については、「解説」を参照してください。 |
| sey | 予測される y の値に対する標準誤差です。 |
| F | F 補正項または F 観測値。F 補正項を利用すると、独立変数と従属変数の間で観察された関係が偶然によるものかどうかを判定できます。 |
| df | 自由度です。自由度を利用すると、統計表の中で F の臨界値を見つけるのに役立ちます。統計表の中で見つけた値と、LINEST 関数が返す F 補正項を比較すると、モデルの信頼性の度合いを決めることができます。df の計算方法については、後の「解説」を参照してください。下記の「使用例 4」は、F と df の使い方を示しています。 |
| ssreg | 回帰の平方和です。 |
| ssresid | 残余の平方和です。ssreg と ssresid の計算方法については、後の「解説」を参照してください。 |
解説
傾き (m):
直線の傾き (m) は、直線上の 2 点の座標が (x1,y1)、(x2,y2) で表されるとき、(y2 - y1)/(x2 - x1) で計算できます。
y 切片 (b):
直線の y 切片 (b) とは、直線が y 軸と交わるときの y の値です。
直線の方程式は y = mx + b で表されます。m と b の値がわかれば、y または x の値をこの方程式に代入して、直線上の任意の点の座標を計算できます。この計算に、TREND 関数を使用することもできます。
傾き:
INDEX(LINEST(既知の y,既知の x),1)
y 切片:
INDEX(LINEST(既知の y,既知の x),2)
- LINEST 関数で計算した直線の精度は、指定したデータのばらつきによって決まります。データの分布がより直線に近ければ、LINEST 関数のモデルの精度はそれだけ向上します。LINEST 関数では、データに最もよく合う直線を見つけるために最小二乗法を使用しています。独立変数 x の値が 1 つしかわからないときは、次の数式を使って m と b の値が計算されます。
ここで、x は標本平均 "AVERAGE(既知の x)"、y は標本平均 "AVERAGE(既知の y)" です。
- 直線/指数曲線回帰関数である LINEST と LOGEST は、データに適合する直線または指数曲線を近似計算します。ただし、データを直線で近似するか指数曲線で近似するかは、データに合わせて選択する必要があります。直線の場合は TREND(既知の y,既知の x)、指数曲線の場合は GROWTH(既知の y,既知の x) を使って計算できます。これらの関数は、引数として "新しい x" を指定しなくても、直線または指数曲線上で実際のデータに対応する、y の予測値の配列を返します。これにより、予測値と実際の値を比較できます。両者の値をひとめで比較できるようにグラフを作成することもできます。
- 回帰分析では、直線上の各点ごとに、予測される y の値と実際の y の値との平方差が計算されます。このようにして計算した平方差の合計を "残余の平方和" (ssresid) と呼びます。次に、"総平方和" (sstotal) が計算されます。"定数" に TRUE を指定するか省略すると、総平方和は、実際の y の値と y の平均値の平方差の合計となります。"定数" に FALSE を指定すると、総平方和は、(個々の y の値から y の平均値を引いたものではなく) 実際の y の値の平方和となります。回帰の平方和 ssreg は、ssreg = sstotal - ssresid として計算されます。総平方和と比較し、残余の平方和が小さければ小さいほど、確実度の係数である r2 の値が大きくなり、回帰分析で得られた方程式が変数間の関係をより正確に表していることになります。ここで、r2 = ssreg/sstotal です。
- Y と X が列になっていると仮定します。いくつかの X 列は、その他の X 列から得られる予測値に影響しないことがあります。言い換えれば、いくつかの X 列を削除しても、同様に正確な Y の予測値が得られます。その場合は、これらの冗長な X 列を回帰モデルから除外する必要があります。冗長な X 列はどれも、冗長でない X 列の倍数の和として表されるため、この現象は "共線性" と呼ばれます。LINEST 関数は共線性をチェックし、冗長な X 列があれば、それらを識別するときにすべて回帰モデルから削除します。削除された X 列は、LINEST 関数の出力で係数 0 と se 0 を持つことから識別できます。1 つ以上の列が冗長として削除されると、df 値が影響を受けます。df 値は、予測に実際に使用される X 列の数に依存するためです。df 値の計算の詳細については、下記の「使用例 4」を参照してください。冗長な X 列が削除されたために df 値が変化すると、sey および F の値も影響を受けます。実際に共線性が起きることは比較的まれです。共線性が起きやすい一例として、実験の各対象者が特定のグループのメンバーであるかどうかを 0 と 1 だけで表す X 列が挙げられます。"定数" に TRUE を指定するか省略すると、LINEST 関数では、すべて 1 の値を持つ X 列が 1 つ追加されます。各対象者を男なら 1、それ以外なら 0 で表した列があるとします。また、女なら 1、それ以外なら 0 で表した別の列があるとします。後者の列は冗長です。この理由は、前者の列の各項から、LINEST 関数で追加された列の各項 (すべて 1) を減算することで得られるためです。
- 共線性が原因でモデルから削除される X 列がない場合、df 値は次のように計算されます。"既知の x" の列が k 個あり、"定数" に TRUE を指定するか省略した場合は、df = n - k - 1 となります。"定数" に FALSE を指定した場合は、df = n - k となります。どちらの場合も、共線性が原因で X 列が 1 つ削除されるごとに、df 値は 1 ずつ増加します。
- 計算結果が配列となる数式は、配列数式として入力する必要があります。
メモ Excel Web App で配列数式を作成することはできません。
- "既知の x" などで引数に配列定数を指定するとき、同じ行の値を区切るには半角のコンマ (,) を使い、各行を区切るには半角のセミコロン (;) を使います。区切り記号は、地域設定によって異なる場合があります。
- 回帰方程式によって予測計算された y の値が、方程式を決定するときに使用した y の値の範囲では、適切な値にならない場合があります。
- LINEST 関数の基になるアルゴリズムは、SLOPE 関数や INTERCEPT 関数の基になるアルゴリズムとは異なります。そのため、データが不定で共線性がある場合に、結果が異なることがあります。たとえば、"既知の y" 引数のデータ要素が 0、"既知の x" 引数のデータ要素が 1 の場合、次のような動作になります。
- LINEST 関数では値 0 が返ります。"LINEST" のアルゴリズムでは、共線性があるデータに対して適切な結果を返すようになっており、この場合は少なくとも 1 つの答えが見つかります。
- SLOPE 関数と INTERCEPT 関数ではエラー値 #DIV/0! が返ります。SLOPE 関数と INTERCEPT 関数のアルゴリズムは唯一の答えを見つけるようになっており、この場合は複数の答えがあり得ます。
- LOGEST を使用して他の種類の回帰分析の統計を計算することに加えて、LINEST を使用すると、x 変数と y 変数の関数を LINEST の x 系列と y 系列として入力することによって、他の一連の回帰分析を計算できます。例として、次の数式を参照してください。
=LINEST(yvalues, xvalues^COLUMN($A:$C))
この数式では、単一列の x 値と y 値を使用して、次の形式の立方体 (3 次多項式) の近似値を計算できます。
y = m1*x + m2*x^2 + m3*x^3 + b
この式を調整することによって、他の種類の回帰分析を計算できますが、出力値およびその他の統計値の調整が必要になる場合もあります。
使用例
使用例 1: 傾きと y 切片
使用例を新規のワークシートにコピーすると、計算結果を確認できます。
その方法は?
- Ctrl キーを押しらながら C キーを押します。
- 空のブックまたはワークシートを作成します。
- ワークシートでセル A1 を選択し、Ctrl キーを押しながら V キーを押します。Excel Web App を使用している場合は、使用例のセルごとにコピーと貼り付けを繰り返します。
重要 Excel デスクトップ アプリケーションでは、この使用例の数式を配列数式として入力する必要があります。使用例を新規のワークシートにコピーした後、A7:B7 のセル範囲 (数式が入力されているセルが左上になる) を選択します。F2 キーを押し、Ctrl キーと Shift キーを押しながら Enter キーを押します。この数式が配列数式として入力されていない場合、単一の値 2 のみが計算結果として返されます。
数式が配列として入力されると、傾き (2) と y 切片 (1) が返されます。
使用例 2: 変数の線形回帰
使用例を新規のワークシートにコピーすると、計算結果を確認できます。
その方法は?
- Ctrl キーを押しらながら C キーを押します。
- 空のブックまたはワークシートを作成します。
- ワークシートでセル A1 を選択し、Ctrl キーを押しながら V キーを押します。Excel Web App を使用している場合は、使用例のセルごとにコピーと貼り付けを繰り返します。
一般に、SUM({m,b}*{x,1}) = mx + b となり、指定した x の値に対する y の値を予測計算できます。この計算に、TREND 関数を使用することもできます。
使用例 3: 多変数の線形回帰
ある不動産会社がビジネス街にある小規模のオフィス ビル群の買収を考えているとします。
この不動産会社は、多変数の線形回帰分析を使用して、指定した地域のオフィス ビルの価格を次の変数に基づいて評価することができます。
この使用例では、各独立変数 (x1, x2, x3, x4) と指定地域のオフィス ビルの評価額である従属変数 (y) との間に直線的な関係が成立するものと見なします。
この不動産会社は、1,500 にのぼる物件の中から 11 のオフィス ビルを選択し、次のようなデータを取得しました。"半分の入口" とは通用口を意味します。
使用例を新規のワークシートにコピーすると、計算結果を確認できます。
その方法は?
- Ctrl キーを押しらながら C キーを押します。
- 空のブックまたはワークシートを作成します。
- ワークシートでセル A1 を選択し、Ctrl キーを押しながら V キーを押します。Excel Web App を使用している場合は、使用例のセルごとにコピーと貼り付けを繰り返します。
|
重要 Excel デスクトップ アプリケーションでは、この使用例の数式を配列数式として入力する必要があります。使用例を新規のワークシートにコピーした後、A14:E18 のセル範囲 (数式が入力されているセルが左上になる) を選択します。F2 キーを押し、Ctrl キーと Shift キーを押しながら Enter キーを押します。この数式が配列数式として入力されていない場合、単一の値 -234.2371645 のみが計算結果として返されます。
数式が配列として入力されると、次の回帰直線の補正項が返されます。このキーを使用すると目的の統計量を確認できます。
多変数の回帰方程式 y = m1*x1 + m2*x2 + m3*x3 + m4*x4 + b は、14 行目に表示されている値を使って次のように計算されます。
y = 27.64*x1 + 12,530*x2 + 2,553*x3 - 234.24*x4 + 52,318
床面積が 2,500 平方フィートの 3 つのオフィスを備えた、入口が 2 つある築 25 年のオフィス ビルがあるとします。その評価額は次の方程式で計算できます。
y = 27.64*2500 + 12530*3 + 2553*2 - 234.24*25 + 52318 = $158,261
または、次の表をこの例のために作成したワークシートのセル A21 にコピーすることもできます。
| 床面積 (x1) | オフィスの数 (x2) | 入口の数 (x3) | 築年数 (x4) | 評価額 (y) |
|---|---|---|---|---|
| 2500 | 3 | 2 | 25 | =D14*A22 + C14*B22 + B14*C22 + A14*D22 + E14 |
使用例 4: F 補正項と r2 補正項の使用
使用例 3 では、確実度係数 r2 が 0.99675 という値になっており (LINEST の計算結果のセル A17 を参照)、独立変数と評価額との間に強い相関関係があることを示しています。F 補正項を利用すると、このように高い r2 の値が偶然の結果であるかどうかを調べることができます。
実際には変数間に相関関係など存在せず、選択した 11 のオフィス ビルがたまたま特異な例であり、強い相関関係を示す統計分析をもたらしたと仮定します。このように、相関関係が存在すると誤って結論づける確率を "アルファ" と称します。
LINEST 関数の計算結果の F および df の値を使用して、より高い F 値が偶然に発生する可能性を評価できます。発行されている F 確率分布表の臨界値と F 値を比較するか、Excel の FDIST 関数を使用して、より高い F 値が偶然に発生する確率を計算できます。F 確率分布には、自由度 v1 および v2 があります。データ要素の数が n のとき、定数に TRUE を指定するか省略すると、v1 = n - df - 1 および v2 = df となります (定数に FALSE を指定すると、v1 = n - df および v2 = df となります)。Excel の関数 FDIST(F,v1,v2) を使用して、より高い F 値が偶然に発生する確率を計算できます。使用例 4 では、df = 6 (セル B18) および F = 459.753674 (セル A18) です。
α値 0.05、v1 = 11 - 6 - 1 = 4、および v2 = 6 とすると、F の臨界値は 4.53 です。459.753674 という F 値は 4.53 を大幅に超えているので、このように大きな F 値が偶然に発生する可能性は非常に低くなります。α = 0.05 のとき、F 値が臨界値 4.53 を超えると、既知の y と既知の x の間に関連がないという仮定は成り立ちません。Excel の FDIST 関数を使用して、この高い F 値が偶然に発生する確率を計算できます。FDIST(459.753674, 4, 6) = 1.37E-7 となり、これは非常に低い確率です。F 確率分布表の臨界値と比較するか、Excel の FDIST 関数を使用すると、オフィス ビルの評価額を予測するうえで回帰方程式が有効かどうかを判断できます。ここで、v1 および v2 には、前の段落で計算した正しい値を使用することが重要です。
使用例 5: t 補正項の計算
もう 1 つの仮説検定を使うと、直線の傾きを表すそれぞれの係数が「使用例 3」のオフィス ビルの評価額の予測に有効であるかどうかを調べることができます。たとえば、築年数の係数が統計的に有意であるかどうかを調べるには、-234.24 (築後年数の係数) を 13.268 (セル A15 に表示されている築後年数の係数についての標準誤差の予測値) で除算します。次の数式により、t の観測値を計算できます。
t = m4 ÷ se4 = -234.24 ÷ 13.268 = -17.7
t の絶対値が十分に大きい場合は、「使用例 3」のオフィス ビルの評価額を予測するうえで、直線の傾きを表す係数が有効であると判断できます。次に、t の観測値の絶対値の一覧を示します。
統計学の教科書の一覧表を参照すると、自由度 6、アルファ 0.05 として、t の臨界値 (両側) は 2.447 であることがわかります。この臨界値は、Excel の TINV 関数を使用して TINV(0.05,6) = 2.447 として計算することもできます。t の絶対値は 17.7 で臨界値の 2.447 よりも大きいため、オフィス ビルの評価額を予測するとき、築後年数が重要な変数であることがわかります。その他の各独立変数についても、同様の方法で統計的な有意性を調べることができます。次に、それぞれの独立変数に対する t の観測値の一覧を示します。
これらの値の絶対値はすべて 2.447 よりも大きくなるため、回帰方程式のすべての変数が、オフィス ビルの評価額を予測するうえで有効であることを確認できます。
- linest関数のページへのリンク
