Среднее значение диапазона чисел

[Vaseninbox]

Здравствуйте!
Имеется UDF,которая считает среднее значение N-последних ячеек в столбце, находящемся на 1 сдвиг влево от текущей ячейки.

Код: Выделить всё

Public Function Mooving(N As Integer) As Integer

Dim Srednee As Integer
Dim i As Integer

Mooving = 0
i = 1

For i = 1 To N Step 1
    Mooving = (Mooving + ActiveCell.Offset(-N + i, -1).Value) 'сумма значений
Next

Mooving = Mooving / N 'среднее значение

End Function

При попытке растянуть эту формулу маркером на весь размер столбца, по которому нужно провести расчёт, значение в каждой ячейке дублируется и равно первому рассчитанному значению (диапазон B9:B18). Выходом может быть решение в столбце D, где каждый раз функция вбивалась вручную заново в каждой ячейке.
Но как поправить функцию,чтобы она автоматом рассчитывалась для всех значений столбца,стоящего слева от нужной ячейки?
Заранее спасибо!

[pashulka]

Vaseninbox, Без обид, но использование ActiveCell в пользовательской функции это плохая идея, даже хуже, чем ненужный цикл и отказ от применения стандартной функции рабочего листа СРЗНАЧ() в т.ч. и программно.

Что касается решения, то Вы можете либо явно указывать ячейку, относительно которой будет производиться смещение, т.е.

Код: Выделить всё

Public Function Mooving(Cell As Range, N As Long) As Long
      ...
      Mooving = Mooving + Cell.Offset(-N + i).Value
      ...
End Function

либо заменить ActiveCell на Application.Caller(1) или Application.ThisCell (MS Excel XP и старше), только учтите, что оба свойства в UDF возвращают ячейку из которой вызвана функция.

[Vaseninbox]

Спасибо за рабочие решения! Дело в том,что давно не брался за VBA и забыл некоторые тонкости...
А как можно было бы в данной ситуации миновать цикла и реализовать решение с помощью "программного" СРЗНАЧ() или СУММ()/N?
Спасибо!

[SAS888]

Так устроит?

Код: Выделить всё

Function Mooving(N As Integer) As Integer
    Mooving = Application.Average(Application.ThisCell.Offset(-N + 1, -1).Resize(N))
End Function

1. Можно еще добавить проверку: не выходит ли за пределы листа диапазон ячеек для подсчета среднего значения, а также, все ли ячейки диапазона содержат числовые значения. (если, конечно, Вам это нужно).
2. Вы уверены, что результат функции Вам нужен типа Integer? Если так, то лучше добавить требуемое округление до целого.
3 В данном решении, при изменении значений ячеек в диапазоне расчета, функция пересчитана НЕ БУДЕТ. Ибо в параметрах функции нет ссылки на влияющие ячейки.

[pashulka]

Тоже самое, только без округления (которое, при необходимости, может добавить любой желающий) и без применения VBA

B2=СРЗНАЧ(СМЕЩ(A2;-2;;3))
B2=СРЗНАЧ(СМЕЩ(B2;-2;-1;3))

Т.к. стандартная функция рабочего листа СМЕЩ() является Volatile, то проблем с пересчётом формулы, где она применяется, не будет. К слову сказать, если в тело пользовательской функции добавить Application.Volatile True или просто Application.Volatile, то такая UDF автоматически станет Volatile (более подробную информацию можно получить в справке)

[Vaseninbox]

Да,действительно,можно было обойтись и без макроса...
Однако, все советы нашли свое применение в других работах. Большое спасибо!

[Vaseninbox]

Уважаемые профессионалы. Как без применения VBA реализовать алгоритм вычисления теперь уже взвешенной скользящей средней (СС)??

Для простой СС с периодом,определённым в ячейке $S$2 отлично работает формула: "=СРЗНАЧ(СМЕЩ(Q14;0;0;-$S$2;1))".

Как унифицировать формулу для вычисления взвешенной СС, соответствующей формуле в ячейке "B6" в примере, с периодом, заданным в ячейке "A1" (в данном примере это значение =5).

Заранее большое спасибо!

[pashulka]

=СУММПРОИЗВ(СМЕЩ(A6;;;-$A$1);СТРОКА(ДВССЫЛ("1:"&$A$1)))/СУММПРОИЗВ(СТРОКА(ДВССЫЛ("1:"&$A$1)))

{=СУММ(СМЕЩ(A6;;;-$A$1)*СТРОКА(ДВССЫЛ("1:"&$A$1)))/СУММ(СТРОКА(ДВССЫЛ("1:"&$A$1)))}

После ввода формулы массива (разумеется, без фигурных скобок) необходимо нажать клавиши CTRL + SHIFT + ENTER

[Vaseninbox]

Спасибо большое, pashulka. Обе формулы прекрасно работают!!