Excel 統計関数: GROWTH

[アーティクル]
03/13/2024
適用対象:

Microsoft Office Excel 2007, Microsoft Office Excel 2003, Excel 2004 for Mac

概要

この記事では、Microsoft Office Excel 2003 以降のバージョンの Excel の GROWTH 関数について説明し、関数の使用方法を示し、Excel 2003 以降のバージョンの Excel の関数の結果と、以前のバージョンの Excel の GROWTH の結果を比較します。 GROWTH は、関連する関数 LINEST を呼び出すことによって評価されます。 Excel 2003 以降のバージョンの Excel に対する LINEST に対する広範な変更がまとめられ、GROWTH への影響が示されています。

Microsoft Excel 2004 for Macintosh 情報

Excel 2004 for Mac の統計関数は、Excel 2003 以降のバージョンの Excel の統計関数を更新するために使用されたのと同じアルゴリズムを使用して更新されました。 Excel 2003 以降のバージョンの Excel の関数のしくみや関数の変更方法を説明するこの記事の情報は、Excel 2004 for Mac にも適用されます。

詳細情報

GROWTH(known_y、known_x、new_xの定数) 関数は、指数曲線が適合する回帰分析を実行するために使用されます。最小二乗基準が使用され、GROWTH はその基準の下で最適な適合を見つけようとします。 Known_yは "従属変数" のデータを表し、known_xのは 1 つ以上の "独立変数" のデータを表します。 GROWTH ヘルプファイルでは、2 番目または 3 番目の引数を省略する可能性があるまれなケースについて説明します。

p 予測変数があると仮定すると、GROWTH は基本的に LOGEST を呼び出します。 LOGEST は、次の形式の数式に適合します。

y = b * (m1^x1) * (m2^x2) * ... * (mp^xp)

係数の値、b、m1、m2、...、mp は、y データに最適な値を与える決定されます。

最後の引数 "constant" が TRUE に設定されている場合は、回帰モデルに乗算係数 b を回帰モデルに含めます。 FALSE に設定した場合、b は基本的に 1 に設定することで除外されます。最後の引数は省略可能です。引数を省略すると、TRUE と解釈されます。

この記事の残りの部分で簡単に説明できるように、known_yが y データの列であり、known_xが x データの 1 つ以上の列になるように、データが列に配置されているとします。もちろん、これらの各列のディメンション (長さ) は等しい必要があります。 New_xは列に配置されているものと見なされ、known_xの列と同じ数の列new_x必要があります。以下のすべての観測値は、データが列に配置されていない場合も同様に当てはまりますが、この単一 (最も頻繁に使用される) ケースについて説明する方が簡単です。

最適適合回帰モデルを計算した後 (基本的に Excel の LOGEST 関数を呼び出すことによって)、GROWTH はnew_xに関連付けられている予測値を返します。

この記事では、例を使用して、GROWTH と LOGEST の関係を示し、EXCEL 2003 より前のバージョンの Excel で LOGEST の問題を指摘し、GROWTH の問題に変換します。 GROWTH は、LOGEST を効果的に呼び出し、LOGEST を実行し、LOGEST 出力の回帰係数を使用して、new_xの各行に関連付けられている予測 y 値の計算を行い、予測された y 値のこの列を表示します。そのため、LOGEST を実行する際の問題について理解しておく必要があります。 LOGEST が呼び出されると、効率的に LINEST が呼び出されます。 GROWTH と LOGEST のコードは Excel 2003 およびそれ以降のバージョンの Excel では書き換えされていませんが、LINEST コードの広範な変更 (および機能強化) が行われています。

この記事の補足として、LINEST に関する次の記事を強くお勧めします。これには、Excel 2003 より前のバージョンの Excel で LINEST に関するいくつかの例とドキュメントの問題が含まれています。

LINEST の詳細については、次の記事番号をクリックして、Microsoft サポート技術情報の記事を表示してください。

828533 Excel 2003 および Excel 2004 for Mac の LINEST 関数の説明

Excel 2003 用に改訂された LINEST ヘルプファイルも推奨されます。

LOGEST に関する次の記事では、LOGEST が LINEST と対話する方法について説明します。ここでは、これらの詳細を省略します。

詳細については、次の記事番号をクリックして、Microsoft サポート技術情報の記事を表示します。

828528 Excel 統計関数: LOGEST

この記事では、Excel 2003 より前のバージョンの Excel の数値の問題に焦点を当てているため、この記事では、GROWTH の使用に関する実用的な例はあまりありません。 GROWTH のヘルプファイルには、便利な例が含まれています。

構文

GROWTH(known_y's, known_x's, new_x's, constant)

引数、known_y、known_x、new_xは、関連する次元を持つ配列またはセル範囲である必要があります。 known_yの列が m 行ごとに 1 列である場合、known_xの列は m 行の c 列であり、c は 1 以上です。 C は予測変数の数です。m はデータポイントの数です。 New_xは、r 行ごとの c 列である必要があります。ここで、が 1 以上である必要があります。 (列ではなく行にデータをレイアウトする場合は、ディメンション内の同様のリレーションシップを保持する必要があります)。定数は、TRUE または FALSE に設定する必要がある論理引数です (または、Excel が FALSE または TRUE として解釈する 0 または 1)。 GROWTH の最後の 3 つの引数はすべて省略可能です。2 番目の引数、3 番目の引数、またはその両方を省略するオプションについては、GROWTH ヘルプファイルを参照してください。4 番目の引数を省略すると、TRUE と解釈されます。

GROWTH の最も一般的な使用方法には、GROWTH (A1:A100、B1:F100、B101:F108、TRUE) などのデータを含むセル範囲が 2 つ含まれます。通常、複数の予測変数があるため、この例の 2 番目の引数には複数の列が含まれています。この例では、100 個のサブジェクト、各サブジェクトに 1 つの従属変数値 (known_y)、サブジェクトごとに 5 つの従属変数値 (known_x) があります。 GROWTH を使用して予測された y 値を計算する 8 つの仮定の対象が追加されています。

使用例

Excel ワークシートの例は、次の主要な概念を示すために提供されています。

GROWTH と LOGEST の対話方法
Excel 2003 より前のバージョンの Excel の共線known_xが原因で、GROWTH (または LOGEST と LINEST) で発生する問題

注:

LINEST のコンテキストで 2 番目の箇条書きの項目について詳しく説明します。LINEST に関する記事で説明します。

GROWTH 関数を説明するには、空白の Excel ワークシートを作成し、次の表をコピーし、空白の Excel ワークシートでセル A1 を選択し、次の表にワークシートのセル A1:K35 が入力されるようにエントリを貼り付けます。

A	B	C	D	E	F	G	I	J	K
Y：	X：
=EXP(F2)	1	2	1		1
=EXP(F3)	3	4	1		2
=EXP(F4)	4	5	1		3
=EXP(F5)	6	7	1		4
=EXP(F6)	7	8	1		5
新しい x:	9	11
	12	14

cols B,C を使用した成長:				Excel 2002 および以前のバージョンの Excel の値:
			Excel 2003 以降のバージョンの Excel の値:
=GROWTH(A2:A6,B2:C6,B7:C8,TRUE)				#NUM!			472.432432563203
=GROWTH(A2:A6,B2:C6,B7:C8,TRUE)				#NUM!			3400.16400895377

col B のみを使用した GROWTH
=GROWTH(A2:A6,B2:B6,B7:B8,TRUE)				472.432432563203			472.432432563203
=GROWTH(A2:A6,B2:B6,B7:B8,TRUE)				3400.16400895377			3400.16400895377

LOGEST の適合値は、Excel 2003 以降のバージョンの Excel で生成されます
cols B、C の使用		Col B の使用
=EXP(LN(K24)1 + LN(J24)B7 + LN(I24)*C7)		=EXP(LN(J31)1 + LN(I31)B7)
=EXP(LN(K24)1 + LN(J24)B8 + LN(I24)*C8)		=EXP(LN(J31)1 + LN(I31)B8)

COLS B,C を使用した LOGEST:				Excel 2002 および以前のバージョンの Excel の値:			Excel 2003 以降のバージョンの Excel の値:
=LOGEST(A2:A6,B2:C6,TRUE,TRUE)	=LOGEST(A2:A6,B2:C6,TRUE,TRUE)	=LOGEST(A2:A6,B2:C6,TRUE,TRUE)		#NUM!	#NUM!	#NUM!	1	1.9307233720034	1.26724101129183
=LOGEST(A2:A6,B2:C6,TRUE,TRUE)	=LOGEST(A2:A6,B2:C6,TRUE,TRUE)	=LOGEST(A2:A6,B2:C6,TRUE,TRUE)		#NUM!	#NUM!	#NUM!	0	0.043859649122807	0.206652964726136
=LOGEST(A2:A6,B2:C6,TRUE,TRUE)	=LOGEST(A2:A6,B2:C6,TRUE,TRUE)	=LOGEST(A2:A6,B2:C6,TRUE,TRUE)		#NUM!	#NUM!	#NUM!	0.986842105263158	0.209426954145848	#N/A
=LOGEST(A2:A6,B2:C6,TRUE,TRUE)	=LOGEST(A2:A6,B2:C6,TRUE,TRUE)	=LOGEST(A2:A6,B2:C6,TRUE,TRUE)		#NUM!	#NUM!	#NUM!	225	3	#N/A
=LOGEST(A2:A6,B2:C6,TRUE,TRUE)	=LOGEST(A2:A6,B2:C6,TRUE,TRUE)	=LOGEST(A2:A6,B2:C6,TRUE,TRUE)		#NUM!	#NUM!	#NUM!	9.86842105263158	0.131578947368421	#N/A

COL B のみを使用した LOGEST
=LOGEST(A2:A6,B2:B6,TRUE,TRUE)	=LOGEST(A2:A6,B2:B6,TRUE,TRUE)			1.9307233720034	1.26724101129183		1.9307233720034	1.26724101129183
=LOGEST(A2:A6,B2:B6,TRUE,TRUE)	=LOGEST(A2:A6,B2:B6,TRUE,TRUE)			0.0438596491228071	0.206652964726136		0.043859649122807	0.206652964726136
=LOGEST(A2:A6,B2:B6,TRUE,TRUE)	=LOGEST(A2:A6,B2:B6,TRUE,TRUE)			0.986842105263158	0.209426954145848		0.986842105263158	0.209426954145848
=LOGEST(A2:A6,B2:B6,TRUE,TRUE)	=LOGEST(A2:A6,B2:B6,TRUE,TRUE)			224.999999999999	3		225	3
=LOGEST(A2:A6,B2:B6,TRUE,TRUE)	=LOGEST(A2:A6,B2:B6,TRUE,TRUE)			9.86842105263158	0.131578947368421		9.86842105263158	0.131578947368421

注:

新しい Excel ワークシートにこのテーブルを貼り付けた後、[ 貼り付けオプション ] ボタンをクリックし、[ 一致する変換先の書式設定] をクリックします。貼り付けた範囲がまだ選択されている状態で、実行している Excel のバージョンに応じて、次のいずれかの手順を使用します。

Microsoft Office Excel 2007 で、[ホーム] タブをクリックし、[セル] グループの [書式] をクリックし、[列の幅の自動調整] をクリックします。
Excel 2003 の [書式] メニューの [列] をポイントし、[オートフィット選択] をクリックします。

GROWTH のデータは、セル A1:C8 にあります。 (セル D2:D6 のエントリはデータの一部ではありませんが、次の図に使用されます)。以前のバージョンの Excel とそれ以降のバージョンの Excel の両方の 2 つの異なるモデルの GROWTH の結果は、それぞれセル E10:E16 と I10:116 に表示されます。セル A10:A16 の結果は、使用している Excel のバージョンに対応します。ここでは、GROWTH が LOGEST を呼び出す方法と、GROWTH で LOGEST 結果がどのように使用されるかを調査するときに、Excel 2003 以降のバージョンの Excel の結果に焦点を当てます。

GROWTH と LOGEST は、次の手順で対話と見なすことができます。

GROWTH(known_y、known_x、new_x、定数) を呼び出します。
GROWTH は LOGEST(known_y、known_x、定数、TRUE) を呼び出します
LOGEST へのこの呼び出しから回帰係数が取得されます。これらの係数は、LOGEST の出力テーブルの最初の行に表示されます。
new_x行ごとに、予測される y 値は、これらの LOGEST 係数とその行のnew_xの値に基づいて計算されます。
手順 4 の計算値は、そのnew_xの行に対応する GROWTH 出力の適切なセルに返されます。

GROWTH が適切な結果を返す場合、LOGEST は手順 3 で適切な結果を生成する必要があります。手順 3 の LOGEST の評価には LINEST の呼び出しが必要であるため、LINEST の動作が適切であることが不可欠です。 Excel 2003 より前のバージョンの Excel の LINEST に関する問題は、共線予測列から発生します。 (以前のバージョンの Excel では、GROWTH の最後の引数が FALSE に設定されている場合に発生する LINEST と LOGEST に関する他の問題があります。ただし、これらの問題は GROWTH の結果には影響せず、ここでは説明しません)。

予測列 (known_x) は、少なくとも 1 つの列 c を他の列、c1、c2、およびその他の列の倍数の合計として表すことができる場合、共線です。列 c は、列 c1、c2、およびその他の列から構成できるため、頻繁に冗長と呼ばれます。共線性の存在における基本的な原則は、冗長列が元のデータに含まれているか、元のデータから削除されるかによって結果が影響を受けないようにすることです。 Excel 2003 より前のバージョンの Excel の LINEST では共線性が検索されていないため、この原則は簡単に違反していました。予測列は、少なくとも 1 つの列 c が、他の列、c1、c2、およびその他の列の倍数の合計とほぼ等しい値で表すことができる場合、ほぼ共線です。この場合、"ほぼ等しい" とは、c1、c2、およびその他の列の重み付けされた合計の対応するエントリからの、c 内のエントリの二乗偏差の小さな合計を意味します。たとえば、"非常に小さい" は 10^(-12) 未満である可能性があります。

最初のモデル (行 10 から 12) では、列 B と C を予測変数として使用し、定数 (最後の引数が TRUE に設定) をモデル化するように Excel に要求します。次に、セル D2:D6 のように見える追加の予測列が効果的に挿入されます。 2 行目から 6 行目の列 C のエントリは、列 B と D の対応するエントリの合計と完全に等しいことがわかります。したがって、列 C は次の項目の倍数の合計であるため、共線性が存在します。

列 B
LOGEST への 3 番目の引数が省略されたか TRUE ("通常" の場合) のため、挿入される Excel の 1 の追加列

これにより、このような数値の問題が発生し、Excel 2003 より前のバージョンの Excel では結果を計算できません。そのため、GROWTH 出力テーブルには #NUM! が入力されます。

2 番目のモデル (行 14 から 16) は、任意のバージョンの Excel が正常に処理できるモデルです。共線性はなく、ユーザーは定数をモデル化するように Excel に再度要求します。このモデルは、次の理由でここに含まれています。

最初に、実用的なケースの中で最も一般的です。共線性が存在しません。これらのケースは、すべてのバージョンの Excel で十分に処理されます。以前のバージョンの Excel を使用している場合、最も一般的な実用的なケースでは数値の問題が発生する可能性が高くないことを知って安心する必要があります。
次に、この例を使用して、2 つのモデルの Excel 2003 以降のバージョンの Excel の動作を比較します。ほとんどの主要な統計パッケージは、共線性を分析し、モデルから他の倍数の合計である列を削除し、「列 C は他の予測列に線形に依存し、分析から削除されました」などのメッセージでユーザーに警告します。

Excel 2003 以降のバージョンの Excel では、このようなメッセージはアラートやテキスト文字列ではなく LOGEST 出力テーブルに伝達されます。 GROWTH には、このようなメッセージをユーザーに配信するためのメカニズムはありません。 LOGEST 出力テーブルでは、1 であり、標準誤差が 0 の回帰係数は、モデルから削除された列の係数に対応します。 LOGEST 出力テーブルは、行 10 から 16 の GROWTH 出力に対応する行 23 から 35 に含まれます。セル I24:I25 のエントリには、削除された冗長予測列が表示されます。この場合、LOGEST は列 C を削除することを選択しました (セル I24、J24、K24 の係数は列 C、B、Excel の定数列にそれぞれ対応します)。共線性が存在する場合は、関連する列のいずれかを削除でき、選択は任意です。

行 30 から 35 の 2 番目のモデルでは、共線性はなく、列も削除されません。予測される y 値は両方のモデルで同じであることがわかります。この問題は、他の倍数の合計である冗長列を削除しても、結果のモデルの適合度が低下しないために発生します。このような列は、最適な二乗適合を見つけるために追加された値を表さないため、正確に削除されます。また、Excel 2003 およびそれ以降のバージョンの Excel のセル I23:K35 の LOGEST 出力を調べると、出力テーブルの最後の 3 行が同じことがわかります。さらに、セル I31:J32 とセル J24:K25 のエントリが一致します。これは、列 C がモデルに含まれているときに同じ結果が得られるが、LOGEST が実行される前に列 C が削除されたときと同じ (セル I24:K28 の出力) ことが判明したことを示しています (セル I31:J35 の出力)。これは、共線性の存在の基本的な原則を満たします。

セル A18:C21 では、Microsoft は Excel 2003 以降のバージョンの Excel のデータを使用して、GROWTH が LOGEST 出力を受け取り、関連する予測 y 値を計算する方法を示します。セル A20:A21 とセル C20:C21 の数式を調べると、2 つのモデルのそれぞれに対して、LOGEST 係数とセル B7:C8 のnew_xのデータを組み合わせる方法を確認できます (列 B、C を予測変数として使用し、列 B のみを予測変数として使用)。

LOGEST は LINEST を呼び出すので、共線性は Excel 2003 の LOGEST およびそれ以降のバージョンの Excel で識別されます。 LINEST では、回帰係数の解決に別のアプローチが使用されます。この方法は QR 分解です。 LINEST 記事には、小さな例の QR 分解アルゴリズムのチュートリアルが含まれています。

以前のバージョンの Excel での結果の概要

LOGEST の結果が不正確なため、Excel 2003 より前のバージョンの Excel では、成長の結果が悪影響を受けます。これは、結果が不正確な結果が LINEST に起因するためです。

LINEST は、共線性の問題に注意を払っていないアプローチを使用して計算されました。共線性の存在は、丸め誤差、回帰係数の不適切な標準誤差、不適切な自由度を引き起こしました。 LINEST が出力テーブルを #NUM! で埋め込むという問題が十分に深刻な場合があります。実際の大多数のケースと同様に、共線 (またはほぼ共線) の予測列が存在しないと確信できる場合、LINEST は一般に許容可能な結果を提供します。したがって、GROWTH のユーザーは、共線 (またはほぼ共線) 予測列が存在しないことを確認できれば、同様に安心できます。

Excel 2003 以降のバージョンの Excel での結果の概要

LINEST の機能強化には、回帰係数を決定する QR 分解方法への切り替えが含まれます。 QR 分解には、次の利点があります。

数値の安定性の向上 (一般に、丸め誤差が小さい)
共線性の問題の分析

この記事に示されている Excel 2003 より前のバージョンの Excel に関するすべての問題は、Excel 2003 以降のバージョンの Excel で修正されています。 LINEST のこれらの機能強化は、LOGEST と GROWTH の改善に関連します。

結論

EXCEL 2003 以降のバージョンの Excel では LINEST が大幅に改善されたため、GROWTH のパフォーマンスが向上しました。 LOGEST は GROWTH によって呼び出されるため、LINEST の機能強化も LOGEST に影響します。以前のバージョンの Excel のユーザーは、GROWTH を使用する前に、予測列が共線的ではないことを確認する必要があります。

この記事と LINEST の記事で紹介されている資料の多くは、最初は Excel 2003 より前のバージョンの Excel のユーザーに警告を表示する可能性があります。ただし、共線性はごく一部のケースでのみ問題になることに注意してください。以前のバージョンの Excel では、共線性がない場合に許容される GROWTH 結果が得られます。

幸いなことに、LINEST の機能強化は、Analysis ToolPak の線形回帰ツール (このツールは LINEST を呼び出します) と、LOGEST と TREND という 2 つの関連する Excel 関数にも影響します。