エクセルVBA セル式の中にROW()を取り込みたい

早速ですが、加重移動平均を求めたいのですが
=SUMPRODUCT(OFFSET(E6,0,0,-$J$4,1),OFFSET(I6,0,0,$J$4,1))/SUM(OFFSET(I6,0,0,$J$4,1))
式として成り立ってます。Ｅ列が基本データ　$J$4は　集める期間　20項とか
I列が　連番です。連番を利用すれば事足りるのですが、オリジナルはシートが大きくなり、連番の列を増やしたくない、で、連番の代わりに　ROW()が代わりに使えないか？　という質問です
ＮＧですが
SUMPRODUCT(OFFSET(E5,0,0,-$J$4,1),OFFSET(row(),0,0,$J$4,1))/SUM(OFFSET(row(),0,0,$J$4,1))
こんな感じで使えれば・・・
お手数ですが

No.1 回答者： moto_koukousei 回答日時： 2015/11/14 21:45

質問の状況（excelのシートの状況）がわかりません。

質問文の式から想像して、一つの方式を例示します。
ただ、Excelの状況は別に造り直しました。
1) B列に上から下へ変動するデータを入れてある（適当に）
2) C2に｢１｣を入れ、C列下に100まで連続する数値を入れてある（固定）
3) 2行目に、(D列から適当に）加重移動平均する区間の数を入れる（適当に）
4) D3に次の式を入れる
｢　=SUMPRODUCT(OFFSET($B4,1-D$2,0,D$2,1),OFFSET($C$2,0,0,D$2,1)*2/(D$2+1)/D$2)　｣
5) D3を、右方向や下方向にコピードラッグしてしまう。
　（式がコピーされたセルに、加重移動平均が表示される）
イメージは添付画像のようなものです。
　
2行目の　D2、E2、G2などのセルの値を変更すると、加重移動平均は再計算されます。　B列の変動データを書き直しても同じです。

この回答へのお礼

お手数おかけします。質問の意図は
=Row()　が　成立するのに　
＝SUMPRODUCT(OFFSET(E5,0,0,-$J$4,1),OFFSET(row(),0,0,$J$4,1))/SUM(OFFSET(row(),0,0,$J$4,1))
が　なぜ　ＮＧか？

式はＥ列がデータ、Ｉ列は連番、$J$4は平均を取る期間（通常変化させません、利用目的の適合期間を求めるためにシュミレーションとして可変することは有ります）です。

加重移動平均は　明示頂いたＨＰの様に使われます。

お礼日時：2015/11/15 09:34

No.2 回答者： moto_koukousei 回答日時： 2015/11/14 22:03

間違いました。

4) D3に次の式を入れる　⇒　4) D4に次の式を入れる
5) D3を、　⇒　5) D4を、

このように配列入力でなく、SUMPRODUCTを使うのは、私は初めての経験です。加重移動平均というやり方も、私は知りませんでした。
http://www.fx-soken.co.jp/tech/t_130.html
計算はできるけれど、これで計算してどう使えるのかが、私にはわからない。
またこのような計算をする式を実用的に使えるものか、私にはわからない。
このシートの計算結果をグラフにはできるようです。

この回答へのお礼

単純移動平均は
早い話、小売店の日々の売り上げをグラフにしても、曜日のデータばらつき（金土日が売り上が多い）が影響して、傾向が見えません。そこで、7項移動平均が使われます。火曜の朝に日から月（1週前の）まで合計し7で割る。次は月（昨日の）から日まで同じように・・・・これでグラフを書けば、すべてのデータに土日のデータが混ざって、曜日の特徴が消えます。
欠点は、「データ変化が鈍くなる」と言うことです。
毎日10万売上る店が有ります。グラフは10万で横直線です。
競合店が出来て、売り上げが5万に成りました。グラフのデータが5万に成るのは　1週間後です。
それまで、日々コンスタントに7142円減少します（7142*7＝49994）
加重移動平均だと12500円減少します。
明示頂いたＨＰの時は、転換点が早く表示されます。
この様な使われ方です。

投稿感謝

お礼日時：2015/11/15 09:35

No.3 ベストアンサー 回答者： moto_koukousei 回答日時： 2015/11/17 00:09

＞=Row()　が　成立するのに　

>＝SUMPRODUCT(OFFSET(E5,0,0,-$J$4,1),OFFSET(row(),0,0,$J$4,1))/SUM(OFFSET(row(),0,0,$J$4,1))　が　なぜ　ＮＧか？

OFFSETの関数の使い方が違うからです。
https://support.office.com/ja-jp/article/OFFSET- …
OFFSET(基準とする参照位置, 参照位置からの行偏位, 参照位置からの列偏位, [指定する高さ], [指定する幅])
これが使い方なので、ROW()では適切なものになりません。
加重するウエートをセルに明示して確定させておくことが便利だと私は思います。とにかく、そこをシート上に示したくない、セルを使いたくないのであれば、次のような式も考えられると思います。
E4 = SUMPRODUCT(OFFSET($B4,1-E$2,0,E$2,1),ROW(OFFSET($A$1,0,0,E$2,1)))*2/(E$2+1)/E$2
上記では、
E2に加重平均するための期間（例えば、７や２０など）を数値で入力
B4からB789などの下方行に、変動するデータ
E4の数式を下方向にドラッグコピーすれば、E2＝20ならば、
E90には、B71:B90の過去を1,2,3,4………20のウエートで加重平均した結果が表示されると思います。
上記の式の$A$1は、ただ行番号を配列にするための指定開始位置なので、$F$1にしても結果は同じです。

この回答へのお礼

なるほど、了解です。お世話様でした。お付き合いいただき感謝

お礼日時：2015/11/17 08:14