Страница 1 из 1
Среднее значение диапазона чисел
Добавлено: 15 апр 2012, 16:34
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, где каждый раз функция вбивалась вручную заново в каждой ячейке.
Но как поправить функцию,чтобы она автоматом рассчитывалась для всех значений столбца,стоящего слева от нужной ячейки?
Заранее спасибо!
Re: Среднее значение диапазона чисел
Добавлено: 15 апр 2012, 18:58
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 возвращают ячейку из которой вызвана функция.
Re: Среднее значение диапазона чисел
Добавлено: 15 апр 2012, 21:37
Vaseninbox
Спасибо за рабочие решения! Дело в том,что давно не брался за VBA и забыл некоторые тонкости...
А как можно было бы в данной ситуации миновать цикла и реализовать решение с помощью "программного" СРЗНАЧ() или СУММ()/N?
Спасибо!
Re: Среднее значение диапазона чисел
Добавлено: 16 апр 2012, 08:13
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 В данном решении, при изменении значений ячеек в диапазоне расчета, функция пересчитана НЕ БУДЕТ. Ибо в параметрах функции нет ссылки на влияющие ячейки.
Re: Среднее значение диапазона чисел
Добавлено: 16 апр 2012, 10:44
pashulka
Тоже самое, только без округления (которое, при необходимости, может добавить любой желающий) и без применения VBA
B2=СРЗНАЧ(СМЕЩ(A2;-2;;3))
B2=СРЗНАЧ(СМЕЩ(B2;-2;-1;3))
Т.к. стандартная функция рабочего листа СМЕЩ() является Volatile, то проблем с пересчётом формулы, где она применяется, не будет. К слову сказать, если в тело пользовательской функции добавить Application.Volatile True или просто Application.Volatile, то такая UDF автоматически станет Volatile (более подробную информацию можно получить в справке)
Re: Среднее значение диапазона чисел
Добавлено: 23 апр 2012, 18:27
Vaseninbox
Да,действительно,можно было обойтись и без макроса...
Однако, все советы нашли свое применение в других работах. Большое спасибо!
Re: Среднее значение диапазона чисел
Добавлено: 04 май 2012, 21:00
Vaseninbox
Уважаемые профессионалы. Как без применения VBA реализовать алгоритм вычисления теперь уже взвешенной скользящей средней (СС)??
Для простой СС с периодом,определённым в ячейке $S$2 отлично работает формула: "=СРЗНАЧ(СМЕЩ(Q14;0;0;-$S$2;1))".
Как унифицировать формулу для вычисления взвешенной СС, соответствующей формуле в ячейке "B6" в примере, с периодом, заданным в ячейке "A1" (в данном примере это значение =5).
Заранее большое спасибо!
Re: Среднее значение диапазона чисел
Добавлено: 05 май 2012, 18:44
pashulka
=СУММПРОИЗВ(СМЕЩ(A6;;;-$A$1);СТРОКА(ДВССЫЛ("1:"&$A$1)))/СУММПРОИЗВ(СТРОКА(ДВССЫЛ("1:"&$A$1)))
{=СУММ(СМЕЩ(A6;;;-$A$1)*СТРОКА(ДВССЫЛ("1:"&$A$1)))/СУММ(СТРОКА(ДВССЫЛ("1:"&$A$1)))}
После ввода формулы массива (разумеется, без фигурных скобок) необходимо нажать клавиши CTRL + SHIFT + ENTER
Re: Среднее значение диапазона чисел
Добавлено: 06 май 2012, 09:20
Vaseninbox
Спасибо большое, pashulka. Обе формулы прекрасно работают!!