Excel の統計関数 TREND

文書翻訳 文書翻訳
文書番号: 828801 - 対象製品
すべて展開する | すべて折りたたむ

目次

概要

この資料では、Microsoft Office Excel 2003 およびそれ以降のバージョンの Excel の TREND 関数の概要と使用方法について説明します。また、Excel 2003 およびそれ以降のバージョンの Excel の TREND 関数の計算結果と Excel 2003 より前のバージョンの Excel の TREND 関数の計算結果を比較します。

TREND では、関連する関数 LINEST を呼び出して計算を行います。Excel 2003 およびそれ以降のバージョンの Excel の LINEST に対する大きな変更点と、それらが TREND に及ぼす影響について説明します。

Microsoft Excel 2004 for Macintosh に関する情報

Microsoft Excel 2004 for Macintosh の統計関数は、Excel 2003 およびそれ以降のバージョンの Excel と同じアルゴリズムを使用して更新されました。この資料に記載されている、Excel 2003 およびそれ以降のバージョンの Excel の関数の機能および関数に対する変更点についての情報は、Excel 2004 for Macintosh にも適用されます。

詳細

TREND(既知の y, 既知の x, 新しい x, 定数) 関数は、線形回帰を実行するために使用されます。最小二乗法を使用して、TREND は、指定された条件のもとで最適な解の探索を試みます。既知の y は "従属変数" のデータを表し、既知の x は 1 つまたは複数の "独立変数" のデータを表します。TREND のヘルプ ファイルには、特殊な場合に 2 番目または 3 番目の引数を省略できることが記載されています。

最後の引数の "定数" を TRUE に設定した場合は、回帰モデルの切片に対応する係数をその回帰モデルに適用できます。この引数を FALSE に設定した場合、切片項は含まれず、適合させる回帰が必ず原点を通るように指定できます。この引数は省略可能です。省略された場合は TRUE として解釈されます。

この資料では以降の説明をわかりやすくするために、既知の y がデータ y の列、既知の x がデータ x の 1 つまたは複数の列となるように、データが列方向に並ぶと仮定します。これらの列の次元 (長さ) はそれぞれ同じにする必要があります。新しい x も列方向に配置すると仮定すると、新しい x の列数は、既知の x の列数と同じであることが必要です。データが列方向に並んでいない場合にも、この資料の計算例はすべて同様の結果になりますが、ここで説明する (最もよく使用される) 事例の説明がより簡単になるためです。

(本質的には Excel の LINEST 関数の呼び出しによって) 最適な回帰モデルが計算されると、TREND は、新しい x に関連付けられた予測値を返します。

この資料では、TREND と LINEST との関連性と、Microsoft Excel 2002 およびそれ以前のバージョンの Excel の LINEST の問題について例を使用して説明します。この問題は、TREND の問題と言い換えることもできます。TREND のコードは Excel 2003 およびそれ以降のバージョンの Excel で更新されていませんが、LINEST のコードには大きな変更 (と改善) が加えられました。

TREND は実際には LINEST を呼び出して実行しています。各行の新しい x に関連付けられた y の予測値の計算で LINEST の出力に含まれる回帰係数を使用して、この y の予測値の列を表示します。したがって、LINEST の実行時の問題について知っておくことが必要です。

この資料の補足として、LINEST に関する次の資料を強くお勧めします。この資料には、Excel 2002 およびそれ以前のバージョンの Excel の LINEST についてのいくつかの例とドキュメントの問題に関する説明が記載されています。

関連情報を参照するには、以下の「サポート技術情報」 (Microsoft Knowledge Base) をクリックしてください。
828533 Excel 2003 および Excel 2004 for Mac の LINEST 関数について


ここでは Excel 2002 およびそれ以前のバージョンの Excel で発生する数値エラーの問題を中心に説明するため、この資料には TREND の使用方法の実例は一部しか掲載していません。TREND のヘルプ ファイルには、有用な例が記載されています。

構文

TREND(known_y's, known_x's, new_x's, constant)
引数のうち、既知の y (known_y's)、既知の x (known_x's)、および新しい x (new_x's) は、それぞれの大きさに関連性を持つ配列またはセル範囲であることが必要です。既知の y が m 行× 1 列ならば、既知の x は m 行× c 列になり、c は 1 以上になります。この c は予測変数の数、m は観測点の数です。この場合、新しい x は、r 行× c 列で、r は 1 以上であることが必要です (データが列方向ではなく行方向に並んでいる場合にも、次元に関して同様の関係を保つ必要があります)。定数 (constant) は論理値の引数で、TRUE または FALSE に設定する必要があります (0 または 1 に設定しても、Excel ではそれぞれ FALSE または TRUE として解釈されます)。TREND の引数のうち、後ろの 3 つの引数はいずれも省略可能です。2 番目の引数、3 番目の引数、またはその両方を省略するオプションについては、TREND のヘルプ ファイルを参照してください。4 番目の引数を省略すると、TRUE として解釈されます。

TREND の最も一般的な使用方法では、TREND(A1:A100, B1:F100, B101:F108, TRUE) などのように、データを含む 2 つの範囲のセルが使用されます。通常は複数の予測変数が存在するため、この例の 2 番目の引数には複数の列が含まれます。この例では、100 の計算対象があり、対象ごとに 1 つの従属変数の値 (既知の y) と 5 つの独立変数の値 (既知の x) があります。TREND を使用して y の予測値を計算する推定用の対象値が 8 つ追加されています。

使用例

Excel ワークシートの例を使用して、次の基本的な概念を説明します。
  • TREND と LINEST の間のデータの流れ
  • Excel 2002 およびそれ以前のバージョンの Excel の TREND (または LINEST) で共線性を持つ既知の x によって発生する問題
2 つ目の項目で言及されている LINEST の問題の詳細な説明は、LINEST に関する資料に記載されています。

TREND の共線性を確認するには、空の Excel ワークシートを作成します。次の表をコピーし、空の Excel ワークシートのセル A1 をクリックしてから貼り付けると、次の表の各項目がワークシートのセル A1:K35 に入力されます。
元に戻す全体を表示する
y x
1 1 2 1
2 3 4 1
3 4 5 1
4 6 7 1
5 7 8 1
新しい x 9 11
12 14
TREND (列 B、C を使用した場合) Excel 2002 以前の値 Excel 2003 以降の値
=TREND(A2:A6,B2:C6,B7:C8,TRUE) #NUM! 6.15789473684211
=TREND(A2:A6,B2:C6,B7:C8,TRUE) #NUM! 8.13157894736842
TREND (列 B のみを使用した場合)
=TREND(A2:A6,B2:B6,B7:B8,TRUE) 6.1578947368421 6.15789473684211
=TREND(A2:A6,B2:B6,B7:B8,TRUE) 8.13157894736842 8.13157894736842
適合値 (Excel 2003 以降の LINEST の計算結果)
列 B、C を使用した場合 列 B を使用した場合
= K24*1 + J24*B7 + I24*C7 =J31*1+I31*B7
=K24*1 + J24*B8 + I24*C8 =J31*1 +I31*B8
LINEST (列 B、C を使用した場合) Excel 2002 以前の値 Excel 2003 以降の値
=LINEST(A2:A6,B2:C6,TRUE,TRUE) =LINEST(A2:A6,B2:C6,TRUE,TRUE) =LINEST(A2:A6,B2:C6,TRUE,TRUE) #NUM! #NUM! #NUM! 0 0.657894736842105 0.236842105263158
=LINEST(A2:A6,B2:C6,TRUE,TRUE) =LINEST(A2:A6,B2:C6,TRUE,TRUE) =LINEST(A2:A6,B2:C6,TRUE,TRUE) #NUM! #NUM! #NUM! 0 0.043859649122807 0.206652964726136
=LINEST(A2:A6,B2:C6,TRUE,TRUE) =LINEST(A2:A6,B2:C6,TRUE,TRUE) =LINEST(A2:A6,B2:C6,TRUE,TRUE) #NUM! #NUM! #NUM! 0.986842105263158 0.209426954145848 #N/A
=LINEST(A2:A6,B2:C6,TRUE,TRUE) =LINEST(A2:A6,B2:C6,TRUE,TRUE) =LINEST(A2:A6,B2:C6,TRUE,TRUE) #NUM! #NUM! #NUM! 225 3 #N/A
=LINEST(A2:A6,B2:C6,TRUE,TRUE) =LINEST(A2:A6,B2:C6,TRUE,TRUE) =LINEST(A2:A6,B2:C6,TRUE,TRUE) #NUM! #NUM! #NUM! 9.86842105263158 0.131578947368421 #N/A
LINEST (列 B のみを使用した場合)
=LINEST(A2:A6,B2:B6,TRUE,TRUE) =LINEST(A2:A6,B2:B6,TRUE,TRUE) 0.657894736842105 0.236842105263159 0.657894736842105 0.236842105263158
=LINEST(A2:A6,B2:B6,TRUE,TRUE) =LINEST(A2:A6,B2:B6,TRUE,TRUE) 0.0438596491228071 0.206652964726136 0.043859649122807 0.206652964726136
=LINEST(A2:A6,B2:B6,TRUE,TRUE) =LINEST(A2:A6,B2:B6,TRUE,TRUE) 0.986842105263158 0.209426954145848 0.986842105263158 0.209426954145848
=LINEST(A2:A6,B2:B6,TRUE,TRUE) =LINEST(A2:A6,B2:B6,TRUE,TRUE) 224.999999999999 3 225 3
=LINEST(A2:A6,B2:B6,TRUE,TRUE) =LINEST(A2:A6,B2:B6,TRUE,TRUE) 9.86842105263158 0.131578947368421 9.86842105263158 0.131578947368421

この表を新しい Excel ワークシートに貼り付けたら、[貼り付けのオプション] をクリックし、[貼り付け先の書式に合わせる] をクリックします。貼り付けた範囲を選択した状態のまま、実行している Excel のバージョンに応じて、次のいずれかの方法を使用します。
  • Microsoft Office Excel 2007 では、[ホーム] タブをクリックし、[セル] の [書式] をクリックして、[列の幅の自動調整] をクリックます。
  • Excel 2003 では、[書式] メニューの [列] をポイントし、[選択範囲に合わせる] をクリックます。
TREND 用のデータはセル A1:C8 にあります (セル D2:D6 の項目はデータの一部ではありませんが、この資料の後半の説明で使用します)。以前のバージョンの Excel と、Excel 2003 以降のバージョンの Excel のそれぞれに対応した 2 種類のモデルの TREND の計算結果は、それぞれセル E10:E16 および I10:I16 に示されています。セル A10:A16 の計算結果は、現在使用しているバージョンの Excel に対応しています。TREND が LINEST を呼び出して LINEST の計算結果を使用する方法を説明する際に、この資料では Excel 2003 およびそれ以降のバージョンの Excel の計算結果に注目していきます。

TREND と LINEST の間のデータの流れを、以下に順番に示します。
  1. ユーザーが TREND(既知の y, 既知の x, 新しい x, 定数) を呼び出します。
  2. TREND から LINEST(既知の y, 既知の x, 定数, TRUE) が呼び出されます。
  3. この LINEST の呼び出しで、回帰係数が取得されます。これらの係数値は、LINEST が出力する表の最初の行に返されます。
  4. LINEST から出力されたこれらの係数と新しい x の行の値に基づいて、新しい x の行ごとに y の予測値が計算されます。
  5. 手順 4. で計算された値が、新しい x の行に対応する TREND の出力の該当するセルに返されます。
TREND で適切な計算結果を返す必要があるならば、LINEST が手順 3. で適切な結果を生成する必要があります。この際に、共線性を持つ予測変数の列によって問題が発生します。

予測変数の列 (既知の x) は、少なくとも 1 つの列 c が、他の c1、c2、などの列の倍数の和として表現できる場合に共線性を持ちます。列 c に含まれる情報は c1、c2、などの他の列から構築できるため、列 c は一般に冗長であると言われます。共線性が存在する場合の基本原則は、元のデータに冗長な列を追加したり削除したりしても、結果が変わってはならないということです。Excel 2002 およびそれ以前のバージョンの Excel の LINEST では共線性を検出していなかったため、この原則が守られていない場合がありました。少なくとも 1 つの列 c が他の c1、c2 などの列の倍数の和とほぼ等しいとして表現できる場合、予測変数の列は、ほぼ共線性を持つと言えます。この場合の "ほぼ等しい" とは、c1、c2、などの他の列の加重合計の対応する項目からの c の項目の偏差の平方和が非常に小さいことを意味します。"非常に小さい" とは、たとえば 10^(-12) よりも小さいような場合です。

10 〜 12 行目の最初のモデルでは、列 B と列 C を予測変数として使用し、Excel に定数付きのモデルを計算するように要求します (最後の引数を TRUE に設定します)。実際には Excel によって、セル D2:D6 のような追加の予測変数の列が挿入されます。列 C の 2 〜 6 行目の項目が、列 B と列 D の対応する項目の和とちょうど同じであることがすぐにわかります。つまり、列 C が次の列の倍数の和になっているため、共線性が存在します。
  • 列 B
  • LINEST の 3 番目の引数 (TREND の 4 番目の引数と同じ) が省略されたか TRUE が指定された (これが一般的です) ために Excel によって挿入された 1 の値を持つ追加の列
この結果、Excel 2002 およびそれ以前のバージョンの Excel では結果を計算できず、TREND が出力する表には #NUM! が設定される数値エラーが発生します。

14 〜 16 行目の 2 つ目のモデルは、いずれのバージョンの Excel でも正常に処理できる内容です。共線性が存在しておらず、この場合は Excel に定数付きのモデルを計算するように要求できます。このモデルをこの表に掲載したのは、次の 2 つの理由からです。

まず、実際の状況では、共線性が存在しない場合が最も一般的だと考えられるためです。このような場合には、すべてのバージョンの Excel で正常に処理されます。実際の状況では、一般的に数値エラーがほとんど発生しないことを認識すれば、以前のバージョンの Excel を使用しているユーザーの不安が解消されます。

次に、この例は Excel 2003 およびそれ以降のバージョンの Excel の動作を 2 つのモデルで比較するために使用しています。多くの主要な統計計算用パッケージでは、共線性を分析し、他の列の倍数の和になる列をそのモデルから削除して、"列 C は他の予測変数の列に依存した共線性を持つため、分析対象から除外されました" という警告メッセージを表示します。

Excel 2003 およびそれ以降のバージョンの Excel では、このようなメッセージは警告やテキスト文字列ではなく、LINEST が出力する表に書き込まれます。TREND にはこのメッセージをユーザーに表示する機構がありません。LINEST が出力する表では、値が 0 でその標準誤差が 0 の回帰係数が、モデルから削除された列の係数に相当します。LINEST が出力する表は、TREND の出力の 10 〜 16 行目に対応する 23 〜 35 行目に含まれています。セル I24:I25 の項目は、削除された冗長性のある予測変数の列を示しています。この場合には、LINEST で列 C の削除が選択されています (セル I24、J24、K24 の係数は、それぞれ、列 C、B、および Excel の定数の列に対応する値です)。共線性が存在する場合、関連している列はいずれも削除の可能性があり、その選択は任意です。

30 〜 35 行目の 2 つ目のモデルでは、共線性が存在せず、列は削除されていません。y の予測値は両方のモデルで同じであることがわかります。これは、他の列の倍数の和となる冗長な列を削除しても、計算されたモデルの適合度は減少しないためです。最小二乗法で最適な適合を見つけるための試行では、このような冗長な列には値が設定されないため、これらの列は確実に除外されます。

また、セル I23:K35 にある Excel 2003 およびそれ以降のバージョンの Excel の LINEST の出力を見ると、(列 C が含まれる場合と含まれない場合で) 出力された表の最後の 3 行が同じで、セル I31:J32 の項目とセル J24:K25 の項目が一致していることがわかります。これは、列 C がモデルに含まれていて冗長性がある場合 (セル I24:K28 の出力) と、LINEST が実行される前に列 C が削除されていた場合 (セル I31:J35 の出力) で同じ結果が得られることを示しています。これは、共線性が存在する場合の基本原則を満たしています。

セル A18:C21 では、Excel 2003 およびそれ以降のバージョンの Excel 用のデータを使用して、TREND が LINEST の出力を利用して適切な y の予測値を計算する方法を示しています。セル A20:A21 とセル C20:C21 の数式を見ると、2 つのそれぞれのモデル (列 B、C を予測変数として使用する場合と B のみを予測変数として使用する場合) で、LINEST の係数がセル B7:C8 の新しい x のデータとどのように組み合わされているかがわかります。

Excel 2003 およびそれ以降のバージョンの Excel では、回帰係数の計算にまったく異なる手法が使用されているため、共線性が LINEST で識別されます。この手法は、QR 分解法 (QR Decomposition) と呼ばれています。LINEST の資料には、QR 分解法のアルゴリズムのチュートリアルが簡単な例で説明されています。

以前のバージョンの Excel の計算結果について

Excel 2002 およびそれ以前のバージョンの Excel では、LINEST の不正確な計算結果によって、TREND の計算結果が悪影響を受けます。

LINEST では、共線性の問題について留意しない方式を使用して計算されていたため、共線性の存在によって、丸め誤差が発生したり、回帰係数の標準誤差や自由度が不適切になったりしていました。丸めが深刻な問題となり、LINEST が出力する表に #NUM! が設定される場合もありました。

実際のほとんどの状況では、共線性を持つ (またはほぼ共線性を持つ) 予測変数の列が存在しないことが明らかな場合、LINEST は通常適切な計算結果を返します。したがって、このような場合には、TREND も同様に問題なく使用できます。

Excel 2003 およびそれ以降のバージョンの Excel の計算結果について

LINEST の強化点には、回帰係数の判定手法を QR 分解法に変更したことが含まれます。QR 分解法には、以下の利点があります。
  • 数値の安定性の向上 (通常は丸め誤差が減少します)
  • 共線性の問題の分析
この資料で説明した、Excel 2002 およびそれ以前のバージョンの Excel に存在するすべての問題は、Excel 2003 およびそれ以降のバージョンの Excel で修正されました。

まとめ

Excel 2003 およびそれ以降のバージョンの Excel で LINEST が大幅に強化されたため、TREND のパフォーマンスが強化されました。以前のバージョンの Excel を使用している場合は、予測変数の列が共線性を持たないことを確認してから、TREND を使用してください。

Excel 2002 およびそれ以前のバージョンの Excel のユーザーは、この資料や LINEST に関する資料の多くの説明を見て最初は不安を感じるかもしれません。しかし、共線性は、非常にまれな状況で発生する問題です。共線性が存在しない場合には、以前のバージョンの Excel で適切な TREND の計算結果が得られます。

さいわいなことに、LINEST の強化点は、分析ツールの線形回帰分析ツール (これが LINEST と呼ばれるものです) の他、類似する 2 つの Excel 関数の LOGEST と GROWTH にも好ましい影響を与えます。

プロパティ

文書番号: 828801 - 最終更新日: 2007年3月27日 - リビジョン: 4.0
この資料は以下の製品について記述したものです。
  • Microsoft Office Excel 2007
  • Microsoft Office Excel 2003
  • Microsoft Excel 2004 for Mac
キーワード:?
kbformula kbexpertisebeginner kbinfo KB828801
"Microsoft Knowledge Baseに含まれている情報は、いかなる保証もない現状ベースで提供されるものです。Microsoft Corporation及びその関連会社は、市場性および特定の目的への適合性を含めて、明示的にも黙示的にも、一切の保証をいたしません。さらに、Microsoft Corporation及びその関連会社は、本文書に含まれている情報の使用及び使用結果につき、正確性、真実性等、いかなる表明・保証も行ないません。Microsoft Corporation、その関連会社及びこれらの権限ある代理人による口頭または書面による一切の情報提供またはアドバイスは、保証を意味するものではなく、かつ上記免責条項の範囲を狭めるものではありません。Microsoft Corporation、その関連会社 及びこれらの者の供給者は、直接的、間接的、偶発的、結果的損害、逸失利益、懲罰的損害、または特別損害を含む全ての損害に対して、状況のいかんを問わず一切責任を負いません。(Microsoft Corporation、その関連会社 またはこれらの者の供給者がかかる損害の発生可能性を了知している場合を含みます。) 結果的損害または偶発的損害に対する責任の免除または制限を認めていない地域においては、上記制限が適用されない場合があります。なお、本文書においては、文書の体裁上の都合により製品名の表記において商標登録表示、その他の商標表示を省略している場合がありますので、予めご了解ください。"

フィードバック

 

Contact us for more help

Contact us for more help
Connect with Answer Desk for expert help.
Get more support from smallbusiness.support.microsoft.com