OFFSET は AVERAGE がデータを滑らかにする範囲を特定します。 OFFSET関数は、AVERAGEが作用する範囲を返します。 OFFSETの属性は、

OFFSET(reference,rows,columns,,)

この例では、OFFSETが計算する範囲はG20から始まり、行または列のオフセットが0です。 言い換えれば、OFFSETが計算する範囲の左上隅は、ちょうどG20です。 これは、行や列によってシフトされたりオフセットされたりすることはありません。 計算された範囲は1行分高くなり、現在のセルG26とさらに2つ左（これは-3です）を含むことになります。 つまり、この計算範囲は G20:E20 になります。

G26のその数式を右にコピーすると、20行目のその上のデータセルと左の前の2つのセルの平均が計算されることがわかります。 数式内のその-3をセル参照に置き換えるとどうなるでしょうか。

下図では、セルG26の数式が、セルE24の数値で指定されるように変更されています

AVERAGE(OFFSET(G20,0,0,1,-$E$24))

$E$24は緑の網掛けのセルになっています。 セルE24に入力する数値は、全体の平均化に使用するセルの幅を決定します。

E24に値を入力すると、データの平滑化に使用する範囲を変更します。

この時点で、平滑化の平均をどのくらい広くするかの選択をユーザーに与えるためにスピナーまたはドロップダウン メニューを使うことができます。 選択した結果はセル E24 に入ります。

Download the smoothing Excel Chart Data with Fixed or Dynamic Smoothing

この配置には 1 つの問題があります。 ユーザーがE24に左側のセルより大きな数値を入力した場合、OFFSETは空白のセルとラベルを含みます。 この場合、誤った平均値が表示されます。 E24が大きすぎてOFFSETがシートから外れてしまうと、AVERAGEはエラーを発生します。 どうしたらよいのでしょうか？ ISERRORのようなエラーを止めるだけでなく、不正解を止めるソリューションが必要です。

この次の図では、セルG26の数式が調整されています。 25行目には、左側に有効範囲の限界となる一連の数値があります。 セルG26の新しい数式は25行目を調べ、セルE24の数値が25行目の限界値より大きい場合、NA()が生成されます。 G26 のこの新しいエラー修正式は、

=IF($E$24>G25,NA(),AVERAGE(OFFSET(G20,0,0,1,-$E$24)))

Excel チャートがセルで NA() を参照しても、何もプロットされません。

エラーを防ぐために動的平滑化式を修正する

データの平滑化の問題点と回避方法

これで、ユーザーが好きな平滑化期間を試せるようになりました。 しかし、(常に「しかし」または「しかし、この場合」があるように思われますが)データの平滑化には問題があります。 平滑化するとボラティリティがなくなりますが、平滑化期間の約半分まで方向性の変化、変曲点、新しいトレンドが見えないということです。

両方の利点があるのは、加重平均平滑化関数を使用することです。 この方法では、短期的なデータに重点を置いて、トレンドを確認することができますが、古いデータに基づいて平滑化し、ボラティリティの一部を排除することができます。 別の記事「加重平均を使用してExcelのチャートデータを平滑化する」の良いケースのようです。

移動平均または動的平滑化でExcelのチャートデータを平滑化するサンプルファイルをダウンロードする

関連記事

• Smoothing Excel Chart Data with Moving Average
• Smoothing Excel Chart Data Using a Weighted Average

関連ページ