Excel 統計関数: 例を含むチュートリアル、パート 3

Excel には、平均値からより複雑な統計分布関数や傾向線関数までの計算を実行する幅広い統計関数が用意されています。

この記事では、傾向線関数を計算するための Excel の統計関数について詳しく説明します。

一部の統計関数は Excel の最新バージョンで導入されたため、古いバージョンでは使用できないことに注意してください。

推定読書時間: 12 分

近似曲線関数

Forecast

Excel の Forecast 関数は、指定された x 値と y 値のセットに適合する線形近似曲線上の将来の点を予測します。

構文

= FORECAST( x, known_y's, known_x's )

科目

• x: 新しい y 値を予測する数値の x 値。
• known_y's: 既知の y 値の配列
• known_x's: 既知の x 値の配列

の配列の長さに注意してください。 known_x と同じでなければなりません known_y との分散 known_x ゼロである必要はありません。

例

次のスプレッドシートの関数は、 FORECAST Excel を使用して、一連の既知の x 値と y 値 (セル F2:F7 および G2:G7 に保存) を通じて最適な直線に沿った追加の点を予測します。

スプレッドシートのセル F7 に示されているように、x=7 での予想される y 値を計算する関数は次のとおりです。 :=FORECAST( 7, G2:G7, F2:F7 )

これにより結果が得られます 32.666667 .

Intercept

Excel の予測関数の中には、 Intercept。 Excel の Intercept 関数は、指定された x 値と y 値のセットにわたる線形回帰直線の切片 (y 軸の交点の値) を計算します。

構文

= INTERCEPT( known_y's, known_x's )

科目

• known_y's: 既知の y 値の配列
• known_x's: 既知の x 値の配列

の配列の長さに注意してください。 known_x と同じでなければなりません known_y との分散 known_x ゼロである必要はありません。

例

次のスプレッドシートは関数の例を示しています。 Intercept を通る線形回帰直線が通る点を計算するために使用される Excel の known_x と known_y (セル F2:F7 および G2:G7 にリストされている) は y 軸と交差します。

Le known_x と known_y スプレッドシートのグラフにプロットされます。

スプレッドシートのセル F9 に示されているように、Intercept 関数の式は次のとおりです。 :=INTERCEPT( G2:G7, F2:F7 )

結果が得られます 2.4 .

Slope

もう 1 つの非常に興味深い予測関数は、Slope (Slope) Excel は、指定された x 値と y 値のセットを通じて線形回帰直線の傾きを計算します。

関数の構文は次のとおりです。

構文

= SLOPE( known_y's, known_x's )

科目

• known_y's: 既知の y 値の配列
• known_x's: 既知の x 値の配列

の配列の長さに注意してください。 known_x と同じでなければなりません known_y との分散 known_x ゼロである必要はありません。

例

次のスプレッドシートは関数の例を示しています。 Slope 直線回帰直線の傾きを計算するために使用される Excel の (傾き) known_x そしてその known_y、セル F2:F7 および G2:G7。

Le known_x と known_y スプレッドシートのグラフにプロットされます。

スプレッドシートのセル F9 に示されているように、Intercept 関数の式は次のとおりです。 :=SLOPE( G2:G7, F2:F7 )

結果が得られます 4.628571429.

Trend

非常に興味深い Excel の予測関数は次のとおりです。 傾向 Excel (トレンド) は、指定された y 値のセットと (オプションで) 指定された x 値のセットを通じて線形傾向線を計算します。

次に、関数は線形傾向線を延長して、新しい x 値の追加セットに対する追加の y 値を計算します。

関数の構文は次のとおりです。

構文

= TREND( known_y's, [known_x's], [new_x's], [const] )

科目

• known_y's: 既知の y 値の配列
• [known_x's]: 既知の x 値の 1 つ以上の配列。これはオプションの引数で、指定した場合は、次のセットと同じ長さでなければなりません。 known_y's。省略した場合、[ のセットknown_x's] は値 {1, 2, 3, …} をとります。
• [新しい_x]: オプションの引数。対応する新しい y 値を計算する新しい x 値のセットを表す 1 つ以上の数値配列を指定します。 [ の各配列new_x さん] は [ の配列と一致する必要がありますknown_x's]。引数 [new_x さん] が省略された場合は、[ と等しくなるように設定されます。known_x's]。
• [費用]: 線形方程式の定数「b」かどうかを指定するオプションの論理引数 y = m x + b 、強制的にゼロに等しくする必要があります。自分 [コスト] が TRUE の場合 (またはこの引数が省略された場合)、定数 b は通常どおりに扱われます。
• 自分 [コスト] が FALSE の場合、定数 b は 0 に設定され、直線の方程式は次のようになります。 y = MX .

例

次のスプレッドシートでは、Excel トレンド関数を使用して、直線 y = 2x + 10 上にある一連の x 値と y 値を拡張しています。既知の x 値と y 値は、セル A2 ～ B5 に格納されています。スプレッドシートのグラフにも表示されます。

指定された点が直線 y = 2x + 10 に沿って正確に適合することは必須ではないことに注意してください (ただし、この例では適合しています)。 Excel のトレンド関数は、指定した値のセットに最も適合する線を見つけます。

トレンド関数は、最小二乗法を使用して最適な線を見つけ、それを使用して、指定された新しい x 値に対する新しい y 値を計算します。

この例では、[new_x さん] はセル A8 ～ A10 に保存されており、Excel のトレンド関数をセル B8 ～ B10 で使用して、対応する新しい y 値を見つけています。数式バーに示されているように、数式は次のとおりです。 := トレンド( B2:B5、A2:A5、A8:A10 )

数式バーのトレンド関数が中かっこ { } で囲まれていることがわかります。これは、関数が次のように入力されたことを示します。 配列数式 .

Growth

Excel の予測関数の中には、 Growth。 機能 Growth Excel は、指定された y 値のセットと (オプション) 1 つ以上の x 値のセットを通じて指数関数的な増加曲線を計算します。次に、関数は曲線を拡張して、新しい x 値の追加セットに対する追加の y 値を計算します。

関数の構文は次のとおりです。

構文

= GROWTH( known_y's, [known_x's], [new_x's], [const] )

科目

• known_y's: 既知の y 値の配列
• [known_x's]: 既知の x 値の 1 つ以上の配列。これはオプションの引数で、指定した場合は、次のセットと同じ長さでなければなりません。 known_y's。省略した場合、[ のセットknown_x's] は値 {1, 2, 3, …} をとります。
• [新しい_x]: 新しい x 値のセット。関数はこれに対応する新しい y 値を計算します。省略した場合は、[ の集合とみなされます。new_x さん] は [ と同じです。known_x's] そして関数は、計算された指数関数的成長曲線上にある y 値を返します。
• [費用]: 線形方程式の定数「b」かどうかを指定するオプションの論理引数 y = b * m^x , は強制的に 1 に等しくする必要があります。コスト] が TRUE の場合 (またはこの引数が省略された場合)、定数 b は通常どおりに扱われます。自分 [コスト] が FALSE の場合、定数 b は 1 に設定され、直線の方程式は次のようになります。 y = mx .

例

次のスプレッドシートでは、Excel の成長関数を使用して、指数関数的な成長曲線 y = 5 * 2^x 上にある一連の x 値と y 値を拡張しています。これらはスプレッドシートのセル A2 ～ B5 に保存され、スプレッドシートのグラフにも表示されます。

Growth 関数は、指定された既知の x 値と y 値に最もよく適合する指数関数的な成長曲線を計算します。この単純な例では、最もよく適合する曲線は指数曲線 y = 5 * 2^x です。

Excel が指数関数的成長曲線の方程式を計算すると、それを使用してセル A8 ～ A10 に指定された新しい x 値に対する新しい y 値を計算できます。

この例では、[new_x's] はセル A8 ～ A10 に格納されており、関数 Growth Excel のデータがセル B8 ～ B10 に挿入されました。数式バーに示されているように、この式は次のとおりです。=Growth( B2:B5、A2:A5、A8:A10 )

数式バーの Growth 関数が中かっこ { } で囲まれていることがわかります。これは、関数が次のように入力されたことを示します。 配列数式 .

上の例の点は曲線 y = 5 * 2^x に沿って正確に適合していますが、これは必須ではないことに注意してください。関数 Growth Excel は、指定した値のセットに対して最適な曲線を見つけます。

金融機能

効果

機能 Effect Excel は、指定された名目金利と指定された年間複利期間数に対する実効年利率を返します。

実効年利率

実効年利率は、金利の資本化を組み込んだ金利の尺度であり、さまざまな資本化条件の金融ローンを比較するためによく使用されます。

実効年利率は、次の式を使用して計算されます。

どこ nominal_rate は名目金利 e npery 年間の複利期間の数です。

関数の構文は次のとおりです。

構文

= EFFECT( nominal_rate, npery )

科目

• nominal_rate: 名目金利 (0 から 1 までの数値でなければなりません)
• npery: 年間の複利期間の数 (正の整数である必要があります)。

例

次のスプレッドシートは、Excel Effect 関数の 3 つの例を示しています。

関数の結果が Effect 0 進数で表示されるか、XNUMX% が表示されます。これらの問題は両方とも、関数を含むセルの書式設定が原因である可能性があります。 Effect.

したがって、小数点以下の桁数を含むパーセンテージでセルを書式設定することで、この問題を解決できます。

これをする：

1. パーセンテージとして書式設定するセルを選択します。
2. 次のいずれかの方法を使用して、「セルの書式設定」ダイアログボックスを開きます。
   • 選択したセルまたは範囲を右クリックし、オプションを選択します セルの書式設定… コンテキストメニューから;
   • タブの数値グループにあるダイアログ ボックス ランチャーをクリックします。 ホーム エクセルリボン;
   • キーボード ショートカットを使用する CTRL-1 (つまり、CTRL キーを選択し、それを押したまま「1」キーを選択します)。
3. 「セルの書式設定」ダイアログボックスで:
   • カードを確認してください 数 ダイアログボックスの上部にある が選択されています。
   • 選択 パーセンテージ リストから カテゴリ ダイアログボックスの左側にある .これにより、チェックボックスの右側に追加のオプションが表示され、表示する小数点以下の桁数を選択できます。
   • 表示する小数点以下の桁数を選択したら、 をクリックします。 OK .

Nominal

機能 Nominal Excel は、指定された実効金利と指定された年間複利期間数に対する名目金利を返します。

関数の構文は次のとおりです。

構文

= NOMINAL( effect_rate, npery )

科目

• effect_rate: 実効金利 (0 ～ 1 の数値)。
• npery: 年間の複利期間の数 (正の整数である必要があります)。

例

次のスプレッドシートの関数は、 Nominal Excel の を使用して、条件の異なる 3 つのローンの名目金利を計算します。

Ercole Palmeri