Excel 2016 で追加された5つの新しい統計関数を紹介してみました

Excel（エクセル）2016で追加された5つの新関数「FORECAST.ETS」、「FORECAST.ETS.CONFINT」、「FORECAST.ETS.SEASONALITY」、「FORECAST.ETS.STAT」、「FORECAST.LINEAR」の引数や使用例、活用のポイントを解説します。

 目次

FORECAST.ETS　指数平滑法を利用して今後の値を予測する

指定された［値］と［タイムライン］を元に今後の指定の目標期日における予測値を返します。

関数の引数と入力方法

目標期日：   Microsoft Excel が値を予測する期を指定します。

値：  タイムラインに対応する数値または範囲を指定します。

タイムライン：  年度や日付など、［値］が得られた期を指定します。タイムラインの日付には、ゼロ以外の一定の間隔が必要です。

季節性： 季節性の変動がある場合に、周期を指定します。1 は、Microsoft Excel が予測で季節性を自動的に検出して、季節のパターンの長さに正の整数を使用するという意味です。省略すると季節性は自動的に計算されます。0 は、季節性がないことを示します。

データ補間： 不足している値を処理するオプションの値です。0 では、不足している値がゼロで置き換わります。欠測値がある場合に1を指定すれば自動的に補間されます。

関数試用例

四半期ごとの売上高を元に2019年第1四半期〜第4四半期の売上高を予測する

{=FORECAST.ETS(B15:B18,E3:E14,B3:B14)}

上記はセルE15〜E18に配列数式として入力されている数式です。FORECAST.ETS関数を配列数式として入力すれば、複数の［目標期日］の予測ができます。ここでは、2015年から2017年までの四半期ごとの売上高を元に、2018年第1四半期〜第4四半期の売上高を予測しています。

注意：タイムラインの入力を間違えないこと。B3:B14をC3:C14に間違えることが多いです。

FORECAST.ETS.CONFINT　指数平滑法を利用して予測した値の信頼区間を求める

Excel（エクセル）2016で追加された新関数「FORECAST.ETS.CONFINT」は、予測された値が信頼区間の中にあるということが、指定された信頼レベルで正しいものと示唆されます。

関数の引数と入力方法

目標期日：Microsoft Excel が値を予測する期を指定します。

値：タイムラインに対応する数値または範囲を指定します。

タイムライン：年度や日付など、［値］が得られた期を指定します。タイムラインの日付には、ゼロ以外の一定の間隔が必要です。

信頼レベル：信頼区間の信頼レベルを指定します。 0 から 1 の数値を指定します。省略した場合は95%が指定されたものとみなされます。

季節性：季節性の変動がある場合に、周期を指定します。1 は、Microsoft Excel が予測で季節性を自動的に検出して、季節のパターンの長さに正の整数を使用するという意味です。0 は、季節性がないことを示します。省略すると季節性は自動的に計算されます。

データ補間：不足している値を処理するオプションの値です。0 では、不足している値がゼロで置き換わります。欠測値がある場合に1を指定すれば自動的に補間されます。

関数試用例

四半期ごとの売上高を元に2019年第1四半期?第4四半期の売上高を予測し、その信頼区間を求める

上記はセルF15〜F18に入力されている数式です。FORECAST.ETS.CONFINT関数を配列数式として入力すれば、複数の［目標期日］の信頼区間が求められます。配列数式を入力するには、入力の終了時に［Enter］キーではなく［Ctrl］+［Shift］+［Enter］キーを押します。

FORECAST.ETS.SEASONALITY　指数平滑法を利用して予測を行うときの季節変動の長さを求める

予測に使われる値の季節変動の長さを求めます。

関数の引数と入力方法

値：タイムラインに対応する数値または範囲を指定します。

タイムライン：年度や日付など、［値］が得られた期を指定します。タイムラインの日付には、ゼロ以外の一定の間隔が必要です。

データ補間：不足している値を処理するオプションの値です。0 では、不足している値がゼロで置き換わります。欠測値がある場合に1を指定すれば自動的に補間されます。

集計：タイムラインに同じ期がある場合、［値］を集計します。省略した場合は集計を行いません。

関数試用例

四半期ごとの売上高を元に予測を行う場合の、季節変動の長さを求める

セルD3～D14に入力されている［値］が売上高で、それに対する［タイムライン］がセルA3～A14に入力されています。季節変動の長さは4となります。

FORECAST.ETS.STAT　指数平滑法を利用して予測を行うときの各種の統計値を求める

予測を行うときに使われる数式の係数や予測の精度を表す値を求めます。

関数の引数と入力方法

値：タイムラインに対応する数値または範囲を指定します。

タイムライン：年度や日付など、［値］が得られた期を指定します。タイムラインの日付には、ゼロ以外の一定の間隔が必要です。

集計：タイムラインに同じ期がある場合、［値］を集計します。省略した場合は集計を行いません。

予測の精度

1.αパラメーター  この値が大きいほど最近のデータの重みが大きくなります。

2.βパラメーター  この値が大きいほど最近の傾向の重みが大きくなります。

3.γパラメーター  この値が大きいほど最近の季節性の重みが大きくなります。

4.MASEの値        Mean Absolute Scaled Error（平均絶対スケーリング誤差）の略です。予測の精度を表す値です。

5.SMAPEの値  Symmetric Mean Absolute Percentage Error（対称平均絶対比率誤差）の略です。誤差の割合に基づいて求めた精度です。

6.MAEの値  Mean Absolute Error（平均絶対誤差）の略で、予測値と実測値の差の絶対値の平均です。

7.RMSEの値  

Root Mean Square Error（平均平方誤差）の略で、予測値と実測値の差の二乗の平均の正の平方根です。

8.検出されたステップサイズ  予測に使われるステップ（刻み値）のサイズです。

関数試用例

四半期ごとの売上高を元に予測を行う場合の、各種統計量を求める 

FORECAST.LINEAR　回帰直線を使って予測する

既知の値を使用し、線形トレンドに沿って将来の値を予測します。

関数の引数と入力方法

予測に使うx：Xには値を予測する対象となるデータ要素を数値で指定します。

yの範囲：すでに分かっているyの値をセル範囲または配列で指定します。

xの範囲：すでに分かっているxの値をセル範囲または配列で指定します。

関数試用例

四半期ごとの売上高を元に2018年第1四半期～第4四半期の売上高を予測する

2015年から2017年までの四半期ごとの売上高を元に2018年第1四半期～第4四半期の売上高を予測しています。

まとめ

Excel 2016の新関では、さまざまな新機能に加えて11個の新しい関数が追加されました。「Office 365」のユーザーのみに利用できる機能もありますが、次の記事で紹介します。新しい統計分析をさまざまな作業で活用しましょう。

www.e-soft.net