Excel の可変スライディング ウィンドウ

Excel の可変スライディング ウィンドウ

私は Excel 2010 を使用していますが、どのバージョンの Excel でも同じように解決できると思います。

合計したいデータがあります。基本的には、スライドする累積値を提供します。たとえば、1000 行のテーブルで、現在の行の値と前の X 行の合計を表示する列が必要です。ここで、X は別のセルで指定する数値です。たとえば、最後の 6 行の累積値、または最後の 5 行、8 行などです。

ADDRESS と INDIRECT、INDEX などを使用してさまざまな組み合わせを試しましたが、何も機能しませんでした。

以下は、ウィンドウ サイズが 2 の場合の、望ましい結果を示す例です。

  |A     |B
1 |Value |Cumulative
2 | 1    |   
3 | 2    |3
4 |15    |17
5 |10    |25
6 |11    |21
7 | 8    |19

ウィンドウサイズ3の場合

  |A     |B
1 |Value |Cumulative
2 | 1    |   
3 | 2    |
4 |15    |18
5 |10    |27
6 |11    |36
7 | 8    |29

前述したように、ウィンドウのサイズはスプレッドシートの別のセル (たとえば、「WindowSize」という名前) で指定したいと思います。

ウィンドウのサイズが有効なデータを超える場合は、空白または 0 の値が返されると便利ですが、これは必須ではありません。#Refs を無視したり、列の先頭を手動で調整してそれを考慮したりしてもかまいません。

Visual Basic に頼らずに、数式を使用してこれを実行できると信じたいのですが、本当にできるのでしょうか?

ありがとう、ヨシュ

答え1

Index メソッドは次のとおりです。

=IF(ROW(1:1)<$E$1,"",SUM(INDEX(A:A,(ROW()-$E$1)+1):INDEX(A:A,ROW())))

INDEX は、参照先のデータが変更された場合にのみ再計算される、不揮発性関数です。

両方OFFSET() と INDIRECT()揮発性関数です。揮発性関数は Excel が再計算するたびに計算します。そのため、揮発性関数が多数あると、不要な計算によって計算速度が低下します。

ここに画像の説明を入力してください

ここに画像の説明を入力してください

答え2

これはセルで動作するはずですB2:

=SUM(A2:INDIRECT(ADDRESS(ROW(A2)-($D$2-1),1,4)))

ウィンドウの値はセルにあります$D$2

答え3

オフセット機能を使う

=sum(offset(A2,0,0,D1))

ここで、D1 には行数が入ります。

関連情報