[Доярка]

Проблема одна и та же. Чтобы пользовательская функция могла полноценно работать с аргументом, описанным как массив. Ну и в идеале сама выдавала бы массив.

[okshef]

Функция, вычисляющая сумму элементов строки, указанной в ячейке С11 матрицы [A2:D4]
=СУММ(ИНДЕКС(A2:D4;C11;0))

5 вариантов пользовательских функций:

читать дальше »

Код:

Function SumRow(arr As Range, nRow As Long) As Double
    SumRow = Application.Sum(Application.Index(arr, nRow, 0))
End Function
----------------------
Function SumRow1(arr As Range, nRow As Long) As Double
    SumRow1 = Application.Sum(arr.Rows(nRow))
End Function
----------------------
Function SumRow2(arr As Range, nRow As Long) As Double
Dim a As Variant, s As Double, i As Long
a = arr.Value
    For i = 1 To UBound(a, 2)
        s = s + a(nRow, i)
    Next i
SumRow2 = s
End Function
---------------------
Function SumRow3(arr As Range, nRow As Long) As Double
Dim a As Variant, s As Double
    For Each a In arr.Rows(nRow).Value
        s = s + a
    Next 'a
SumRow3 = s
End Function
----------------------
Function SumRow4(arr As Range, nRow As Long) As Double
Dim a As Variant
    For Each a In arr.Rows(nRow).Value
        If IsNumeric(a) Then SumRow4 = SumRow4 + a
    Next a
End Function

Спасибо коллегам PlanetaExcel

[Доярка]

Спасибо огромное!!! Ваши коллеги – настоящие спецы! Разбираться буду позже, сейчас ухожу по делам.
Ну и теперь (строго в рамках темы – в ней вопрос уже был задан!) как бы соорудить такую пользовательскую функцию, которая выводила бы массив, состоящий из всей строки (номер строки указан в аргументе) матрицы? Т.е. для полноценной вставки функции выделялся бы интервал и нажималось бы Ctrl+Shift+Enter?

[okshef]

Доярка, более чем странный вопрос, тем более вы сами это делаете:
В первую ячейку введите формулу

=ИНДЕКС(массив;ячейка_с_номером_строки;СТОЛБЕЦ(A1))

и протягиваете ее столько, сколько нужно.
Соответственно, в третей ячейке получится

=ИНДЕКС(массив;ячейка_с_номером_строки;СТОЛБЕЦ(C1))

ВводИте как угодно, хоть формулой массива, хоть простой.

[Доярка]

Я Вас понимаю в том отношении, что многие поставленные задачи могут быть успешно решены с помощью формул рабочего листа. Многие, но не все же – для этого и существует язык VBA.
Хорошо, возьмём такую задачу. Пусть на рабочем листе расположена матрица размером m*n. Требуется найти все коэффициенты попарной корреляции её столбцов. Всего таких коэффициентов должно быть n*(n-1)/2.
В прилагаемом заархивированном файле «Correlation.xlsm» приводится её решение двумя способами: тупо с помощью формул, и через макрос CorrelationCoeffs. Хорошо, что может измениться:
- размеры исходной матрицы;
- расположение исходной матрицы;
- расположение строки и, в которую заносятся результаты расчётов, и её начальной ячейки.
И что, в таком случае каждый раз менять код с указанием этих параметров?
Хотелось бы, чтобы поставленную задачу решала уже функция с единственным аргументом – исходной матрицей, и пусть по умолчанию ряды данных располагаются в столбцах. И выводила бы она уже массив из n*(n-1)/2 элементов. При вставке такой функции как скаляра выводилось бы первое значение массива, а при выделении в строке диапазона нужной длины - все его элементы отображались бы при нажатии Ctrl+Shift+Enter. Вот и интересуюсь, как бы это сделать? Конечно, размеры выделения определял бы сам пользователь – точно так же, как при использовании встроенных функций, возвращающих матрицы.