подскажите ф-лу для среднего значения (хитрый случай)
не сочтите за офтоп, т.к. вопрос не совсем по программированию... :o
Но может кто сталкивался по работе?
Есть ячейки A1, A3, A5. В последней ячейке значение равно нулю (вообще-то ячеек больше [см. ниже]. Здесь упрощено). Как написать формулу для подсчета средней величины для данного набора (т.е. чтобы не учитывались нулевые значения, что ессно). Диапазон указать нельзя (A1:A5), а можно только так (A1; A3; A5), потому что в промежуточных ячейках сидят другие цифры.
Пробовал через СРЗНАЧ/СРЗНАЧЕСЛИ. Эти формулы ексель "понимает" только, если указан диапазон. Если указать неск. ячеек через точку с запятой, пишет "ошибка"
формула в итоговой ячейке:
пишет "ошибка"
если формула с диапазоном, то ошибка не возникает
правда тогда считаются числа в промежуточных строках, которые не должны быть в расчете.
Но может кто сталкивался по работе?
Есть ячейки A1, A3, A5. В последней ячейке значение равно нулю (вообще-то ячеек больше [см. ниже]. Здесь упрощено). Как написать формулу для подсчета средней величины для данного набора (т.е. чтобы не учитывались нулевые значения, что ессно). Диапазон указать нельзя (A1:A5), а можно только так (A1; A3; A5), потому что в промежуточных ячейках сидят другие цифры.
Пробовал через СРЗНАЧ/СРЗНАЧЕСЛИ. Эти формулы ексель "понимает" только, если указан диапазон. Если указать неск. ячеек через точку с запятой, пишет "ошибка"
формула в итоговой ячейке:
Код:
=СРЗНАЧ(ЕСЛИ(D36:D381>0; D36;D65;D97;D128;D160;D191;D223;D255;D286;D318;D349;D381;""))
пишет "ошибка"
если формула с диапазоном, то ошибка не возникает
Код:
=СРЗНАЧ(ЕСЛИ(D36:D381>0; D36:D381;""))
правда тогда считаются числа в промежуточных строках, которые не должны быть в расчете.
Вариант I.
Используйте дополнительные ячейки, которые будут связаны, посредством ссылок, со всеми необходимыми ячейками и определяйте среднее арифметическое в этом диапазоне, например :
H1=D36
H2=D65
H3=D97
...
H12=D381
{=СРЗНАЧ(ЕСЛИ(H1:H12<>0;H1:H12))}
=СУММЕСЛИ(H1:H12;">0")/СЧЁТЕСЛИ(H1:H12;">0") 'только полож. значения
Вариант II. (максимально подходящий для данного раздела)
Используйте пользовательскую функцию, например :
И вызывайте её из ячейки следующим образом =AverageNoZero((D36;D65;D97;D128;D160;D191;D223;D255;D286;D318;D349))
На самом деле есть ещё вариант с перечислением всех несмежных ячеек, но применительно к Вашему случаю он, возможно, покажется слишком громоздким …
Используйте дополнительные ячейки, которые будут связаны, посредством ссылок, со всеми необходимыми ячейками и определяйте среднее арифметическое в этом диапазоне, например :
H1=D36
H2=D65
H3=D97
...
H12=D381
{=СРЗНАЧ(ЕСЛИ(H1:H12<>0;H1:H12))}
=СУММЕСЛИ(H1:H12;">0")/СЧЁТЕСЛИ(H1:H12;">0") 'только полож. значения
Вариант II. (максимально подходящий для данного раздела)
Используйте пользовательскую функцию, например :
Код:
Function AverageNoZero#(iDiapazon As Range)
Dim iCell As Range, iSum#, iCount&
For Each iCell In iDiapazon
If iCell.Value <> 0 Then
iSum = iSum + iCell.Value
iCount = iCount + 1
End If
Next
If iCount > 0 Then AverageNoZero = iSum / iCount
End Function
Dim iCell As Range, iSum#, iCount&
For Each iCell In iDiapazon
If iCell.Value <> 0 Then
iSum = iSum + iCell.Value
iCount = iCount + 1
End If
Next
If iCount > 0 Then AverageNoZero = iSum / iCount
End Function
И вызывайте её из ячейки следующим образом =AverageNoZero((D36;D65;D97;D128;D160;D191;D223;D255;D286;D318;D349))
На самом деле есть ещё вариант с перечислением всех несмежных ячеек, но применительно к Вашему случаю он, возможно, покажется слишком громоздким …
Большое спасибо, сделал через доп.ячейки.
С функцией, почему-то не сработало (Ексель пишет в ячейке "#имя?").
С функцией, почему-то не сработало (Ексель пишет в ячейке "#имя?").
Значение ошибки #ИМЯ? может появиться, если :
- неверно ввести имя функции, к примеру =АverageNoZero(...) у меня первый символ латиница, а здесь кириллица
- разместить функцию в другой открытой рабочей книге, и при вызове функции, забыть указать путь к этой книге
- если запретить выполнение макросов
- неверно ввести имя функции, к примеру =АverageNoZero(...) у меня первый символ латиница, а здесь кириллица
- разместить функцию в другой открытой рабочей книге, и при вызове функции, забыть указать путь к этой книге
- если запретить выполнение макросов