Excel 統計関数: LOGEST

概要

この記事では、Microsoft Office Excel 2003 およびそれ以降のバージョンの Excel の LOGEST 関数について説明します。 関数の使用方法を示し、Excel 2003 以降のバージョンの Excel の関数の結果と、以前のバージョンの Excel の関数の結果を比較します。

LOGEST は、関連する関数 LINEST を呼び出すことによって評価されます。 Excel 2003 以降のバージョンの Excel の LINEST に対する広範な変更が要約され、LOGEST への影響が示されています。

Microsoft Excel 2004 for Macintosh Information

Microsoft Excel 2004 for Macintosh の統計関数は、Excel 2003 以降のバージョンの Excel と同じアルゴリズムを使用して更新されました。 Excel 2003 およびそれ以降のバージョンの Excel の関数がどのように変更されたかを説明する、関数のしくみを説明するこの記事の情報は、Excel 2004 for Macintosh にも適用されます。

詳細情報

LOGEST(known_y、known_x、定数、統計) 関数は、指数曲線が適合する回帰を実行するために使用されます。 最小二乗条件が使用され、LOGEST はその条件の下で最適な適合を見つけようとします。 Known_yは従属変数のデータを表し、known_xは 1 つ以上の独立変数のデータを表します。 2 番目の引数は省略可能です。 省略した場合、値 (1、2、3、...) を含むknown_yと同じサイズの配列であると見なされます。

p 予測変数があると仮定すると、LOGEST は次の形式の数式に適合します。

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

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

LOGEST の最後の引数は、回帰係数のさまざまな平方和、r-2 乗、f 統計量、標準誤差など、追加の統計が必要な場合は TRUE に設定されます。 この場合、LOGEST は配列数式として入力する必要があります。 最後の引数は省略可能です。 省略すると、FALSE として解釈されます。 配列の次元は、独立変数の数と 3 番目の引数が TRUE に設定されている場合は 1 を加算した列数で 5 行です (3 番目の引数が TRUE に設定されていない場合は 0 を加算します)。

3 番目の引数 "constant" が TRUE に設定されている場合は、回帰モデルの前の数式の定数 b をモデルに含めます。 FALSE に設定されている場合、乗算定数 b は、強制的に 1 に等しくすることで回帰モデルから除外されます。 3 番目の引数は省略可能です。 省略すると、TRUE と解釈されます。

この記事では、known_yが y データの列であり、known_xが x データの 1 つ以上の列になるように、データが列に配置されていることを前提としています。 これらの各列のディメンションまたは長さは等しい必要があります。 データが列に配置されていない場合、次の観測値はすべて同じように当てはまりますが、最も頻繁に使用されるケースについて説明する方が簡単です。 また、LOGEST の最後の引数が常に TRUE であり、常に詳細な出力に関心があるとします。 この仮定は、以前のバージョンの Excel で数値の問題を明らかにするのに役立ちます。 この引数に FALSE を使用すると、数値の問題が引き続き発生します。

この記事では、次の例を使用して、LOGEST と LINEST の関係を示し、以前のバージョンの Excel の LINEST に関する問題を指摘して、LOGEST の問題に変換します。 LOGEST のコードは Excel 2003 およびそれ以降のバージョンの Excel では書き換えされていませんが、LINEST のコードに広範な変更と機能強化が加えられています。 LOGEST は、効果的に LINEST を呼び出し、LINEST を実行し、LINEST 出力を変更して、ユーザーに提示します。 したがって、LINEST の実行に関する問題について理解しておく必要があります。LINEST の詳細については、次の記事番号をクリックして、Microsoft サポート技術情報の記事を表示してください。

828533 Excel 2003 以降のバージョンの Excel の LINEST 関数の説明

LINEST ヘルプ ファイルは、Excel 2003 以降のバージョンの Excel 用にも変更されており、詳細については役立ちます。この記事では、以前のバージョンの Microsoft Excel の数値の問題に焦点を当てているため、この記事には LOGEST の実用的な例はあまり含まれていません。 LOGEST ヘルプ ファイルには、2 つの便利な例が含まれています。

構文

LOGEST(known_y's, known_x's, constant, statistics)

引数 (known_y と known_x) は、関連する次元を持つ配列またはセル範囲である必要があります。 known_yの列が m 行ごとに 1 列である場合、known_xのは、c が 1 以上の m 行の c 列である必要があります。 C は予測変数の数です。m はデータ ポイントの数です。 known_yが 1 行にレイアウトされている場合は、同様のリレーションシップを保持する必要があります。その場合、known_xは r 行に存在する必要があります。 (R は 1 以上である必要があります)。定数と統計は、TRUE または FALSE に設定する必要がある論理引数です。 Excel では、これらの引数を 0 または 1 に設定する必要があり、Excel によってそれぞれ FALSE または TRUE として解釈されます。 LOGEST の最後の 3 つの引数はすべて省略可能です。 2 番目の引数を省略すると、LOGEST はエントリ {1,2, 3, ...} を含む単一の予測変数を想定します。 3 番目の引数を省略すると、TRUE と解釈されます。 4 番目の引数を省略すると、FALSE として解釈されます。

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

使用例

次の重要な概念を示すために、2 つの Excel ワークシートの例が用意されています。

• LOGEST と LINEST の対話方法
• LOGEST または LINEST の 3 番目の引数が FALSE に設定されているか、省略されている場合の、Microsoft Excel 2002 および以前のバージョンの Excel の問題。
• LOGEST 内の共線known_xまたは Excel 2002 以前の LINEST が原因の問題。

この一覧の 2 番目と 3 番目の概念の詳細については、次の記事番号をクリックして、Microsoft サポート技術情報の記事を表示します。

828533 Excel 2003 以降のバージョンの Excel の LINEST 関数の説明

3 番目の引数が FALSE に設定された LOGEST を示すには、次の手順に従います。

1. 空白の Excel ワークシートを作成し、このセクションの後半でテーブルをコピーします。

2. 空白の Excel ワークシートのセル A1 をクリックします。 次に、ワークシート内のセル A1:H17 が表に入力されるようにエントリを貼り付けます。

3. 新しい Excel ワークシートにテーブルを貼り付けた後、[ 貼り付けオプション] をクリックし、[ 一致する変換先の書式設定] をクリックします。

4. 貼り付けた範囲がまだ選択されている間は、実行している Excel のバージョンに応じて、次のいずれかの手順を使用します。

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

LOGEST のデータはセル A4:B6 にあります。 Excel 2002 および以前のバージョンの Excel および Excel 2003 およびそれ以降のバージョンの Excel の詳細な結果は、それぞれセル D4:E8 とセル G4:H8 に表示されます。 セル A11:B13 は同じknown_xを示しますが、セル A4:A6 のknown_yは、Excel LN 関数を使用して自然対数を取ることによって変換されています。 その後、この変換されたデータに対して LINEST が呼び出され、結果がセル G11:H15 に表示されます。 セル G12:H15 の結果は、セル G5:H8 の LOGEST の結果と同じであることに注意してください。 セル G11:H11 の LINEST 係数は指数によって変換されます。 つまり、セル内の LINEST 係数は、基本的に Excel EXP 関数を使用してセル G4:H4 の LOGEST 係数を計算することによって変換されます。 この関係を確認するには、セル G17:H17 の EXP 関数を使用します。 LOGEST と LINEST の対話方法を要約するには、次の一連の手順に従います。

1. LOGEST(known_y、known_x、定数、TRUE) を呼び出します。
2. LOGEST は LINEST(LN of known_y、known_x の定数、TRUE) を呼び出します。
3. LOGEST は、LINEST へのこの呼び出しから結果テーブルを受け取ります。
4. LOGEST は、LINEST 結果テーブルの最初の行の LINEST 係数を指数化によって変更します。 たとえば、各 LINEST 係数 m を EXP(m) で置き換えます。
5. LOGEST は、この変更された LINEST 結果テーブルを LOGEST 結果テーブルとして返します。

LOGEST が適切な結果を返す場合、LINEST は手順 3 で適切な結果を生成する必要があります。 セル D13:D15 を調べます。 セル D13 には r-2 乗値が含まれており、セル D14 には f 統計が含まれており、セル D15 には LINEST 回帰の平方和が含まれます。

Excel 2002 以前のバージョンの Excel では、3 番目の引数を FALSE に設定して LINEST が呼び出されると、正しくない数式を使用するため、正しくない 2 乗の回帰合計が常に計算されます。 この問題は、Excel 2003 以降のバージョンの Excel で修正されました。 出力テーブルの最初の 2 行の値は、この問題の影響を受けないことを確認してください。 Excel 2002 以前のバージョンの Excel では、LINEST の記事では、LINEST 出力テーブルの最後の 3 行に適切な値を生成する回避策について説明します。 以前のバージョンの Excel を使用していて、3 番目の引数を LOGEST に FALSE に設定する場合は、前の手順の手順 2 と 3 を明示的に実行してから、LINEST の記事の回避策を使用して LINEST 出力テーブルの最後の 3 行を変更することをお勧めします。

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

LOGEST 共線性を示すには、次の手順に従います。

1. 空白の Excel ワークシートを作成し、次の表をコピーします。

2. 空白の Excel ワークシートのセル A1 をクリックします。 次に、ワークシートのセル A1:N27 が表に入力されるようにエントリを貼り付けます。

3. 新しい Excel ワークシートにテーブルを貼り付けた後、[ 貼り付けオプション] をクリックし、[ 一致する変換先の書式設定] をクリックします。

4. 貼り付けた範囲がまだ選択されている間は、実行している Excel のバージョンに応じて、次のいずれかの手順を使用します。

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

データはセル A1:C6 に含まれます。 セル D2:D6 のエントリは、データの一部ではありません。 これらのエントリは、次の段落の図に使用されます。 LOGEST への 2 つの異なる呼び出しの結果は、Excel 2002 と以前のバージョンの Excel のセル E8:G20、Excel 2003 およびセル I8:K20 の Excel のそれ以降のバージョンに対して表示されます。

最初のモデル (行 8 から 13) では、列 B と C を予測変数として使用し、3 番目の引数が TRUE に設定されている定数をモデル化するように Excel に要求します。 次に、セル D2:D6 のように見える追加の予測列が効果的に挿入されます。 行 2 から 6 の列 C のエントリは、列 B と D の対応するエントリの合計と完全に等しいことに注意してください。そのため、列 C は列 B の倍数の合計であり、LOGEST の 3 番目の引数が省略されたか、"通常" の場合は TRUE であるため、列 C は列 B の倍数の合計であり、Excel の 1 の追加列が挿入されるため、共線性が発生します。 この共線性によって数値の問題が発生し、Excel 2002 以前のバージョンの Excel では結果を計算できず、LOGEST 出力テーブルに #NUM! が入力されます。

どのバージョンの Excel でも、15 行目から 20 行目の 2 番目のモデルを処理できます。 このモデルでは共線性は発生せず、ユーザーは定数をモデル化するように Excel に要求します。 この例は、次の 2 つの理由でここに含まれています。

• この例は、実用的なケースの中で最も一般的です。共線性は存在せず、LOGEST の 3 番目の引数は TRUE または省略されます。 以前のバージョンの Excel を使用している場合、最も一般的な実用的なケースでは数値の問題が発生する可能性はありません。
• 次に、この例を使用して、2 つのモデルの Excel 2003 以降のバージョンの Excel の動作を比較します。 ほとんどの主要な統計パッケージは、共線性を分析し、モデルから他の倍数の合計である列を削除し、「列 C は他の予測列に線形に依存しており、分析から削除されました」のようなメッセージで警告します。

Excel 2003 以降のバージョンの Excel では、メッセージやテキスト文字列ではなく、LOGEST 出力テーブルに情報を受け取ります。 1 であり、標準誤差が "ゼロ" の回帰係数は、モデルから削除された列の係数に対応します。 (例については、セル I9:I10 のエントリを参照してください)。この場合、LOGEST は列 C を削除します (セル I9、J9、K9 の係数は列 C、B、Excel の定数列にそれぞれ対応します)。 共線性が発生すると、関連する列のいずれかが削除される可能性があります。

行 16 から 20 の 2 番目のモデルでは、共線性は発生せず、どの列も削除されません。 予測される y 値は両方のモデルで同じです。これは、他のモデル (1 番目と 2 番目のモデル) の倍数の合計である冗長列を削除しても、結果として得られるモデルの適合度が低下しないためです。 このような列は、最適な二乗適合を見つけるために追加された値を表さないため、正確に削除されます。 また、Excel 2003 以降の Excel のセル I8:K20 の出力では、出力テーブルの最後の 3 行は同じであり、セル I16:J17 とセル J9:K10 のエントリが一致します。 これは、列 C がモデルに含まれていても、LOGEST が実行される前に列 C が削除されたとき (I16:J20 の出力) と同じ結果が得られる (I9:K13 での出力) ことが判明した場合に、同じ結果が得られたことを示しています。 この出力は、共線性の存在下で基本的な原則を満たします。

共線性は、回帰係数を解決するために、まったく異なるアプローチである QR 分解を使用して、Excel 2003 の LINEST およびそれ以降のバージョンの Excel で識別されます。 LINEST の記事では、小さな例の QR 分解アルゴリズムのチュートリアルについて説明します。

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

LOGEST の結果は、Excel 2002 および以前のバージョンの Excel では、正確ではない LINEST の結果によって悪影響を受けます。

LINEST では、LINEST の 3 番目の引数が FALSE に設定されている場合、合計平方和に対して正しくない数式が使用されました。 この数式により、正しくない 2 乗の回帰合計の値が得られます。 また、回帰の平方和 (r 2 乗と f 統計量) に依存する値は正しくありません。 (以前のバージョンの Excel を使用している場合は、LINEST の記事の回避策を参照してください)。そのため、LOGEST のユーザーは、3 番目の引数を FALSE に設定して LOGEST を呼び出すときに、この回避策を使用する必要があります。

3 番目の引数の値に関係なく、LINEST は共線性の問題に対処しないアプローチを使用して計算されました。 共線性は、丸め誤差、適切でない回帰係数の標準誤差、および適切でない自由度を引き起こしました。 場合によっては、LINEST 出力テーブルに #NUM! が入力されたことで、丸めエラーが十分に深刻でした。 通常、LINEST は、次の条件が満たされている場合に許容される結果を提供します。

• ユーザーは、予測列が共線 (またはほぼ共線) ではないことを確信しています。
• LINEST の 3 番目の引数は TRUE であるか、省略されます。

そのため、一般に、予測列が共線 (またはほぼ共線) ではない場合や、LOGEST の 3 番目の引数が TRUE または省略された場合、LOGEST は許容される結果を提供します。

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

LINEST の次の機能強化が行われました。

• LINEST の 3 番目の引数が FALSE に設定されている平方和の合計の数式が修正されました。
• QR 分解メソッドは、回帰係数を決定するために使用されます。

QR 分解には、次の 2 つの利点があります。

• 数値の安定性が向上します (一般的には丸め誤差が小さくなります)。
• 共線性の問題の分析。

Excel 2002 およびこの記事で説明されている以前のバージョンの Excel に関するすべての問題は、Excel 2003 以降のバージョンの Excel で修正されています。

結論

EXCEL 2003 以降のバージョンの Excel では LINEST が大幅に改善されたため、LOGEST のパフォーマンスが向上しました。 以前のバージョンの Excel を使用している場合は、LOGEST を使用する前に、予測列が共線ではないことを確認してください。 また、LOGEST の 3 番目の引数が FALSE に設定されている場合は、LINEST 記事に記載されている回避策を使用するように注意してください。 この記事と LINEST の記事のこの情報は、Excel 2002 以前のバージョンの Excel のユーザーにとっては驚くべきことかもしれませんが、共線性はごく一部のケースで問題になります。 3 番目の引数を FALSE に設定した LOGEST の呼び出しは、実際には比較的まれな場合もあります。 以前のバージョンの Excel では、共線性がなく、LOGEST の 3 番目の引数が TRUE または省略された場合に、許容される LOGEST 結果が得られます。