Пользовательская функция в Excel с параметром – интервалом, можно ли так?

[Доярка]

Всех с наступающим! Несмотря на то, что до Нового Года остаётся совсем мало времени, один вопрос не даёт мне покоя.
Как известно, в Excel существует возможность создавать пользовательские функции на языке VBA, которые можно использовать в формулах рабочего листа. Если последний аргумент такой функции описать с ключевым словом ParamArray, то он может быть вызван сколь угодно много раз. Это с одной стороны хорошо, но с другой - иногда не совсем удобно. Например, пусть требуется из одного и того же заданного скаляра вычесть каждый элемент заданного интервала, и в итоге найти сумму квадратов этих разностей. Встроенная функция СУММКВРАЗН здесь не подойдёт, поскольку она работает лишь для двух интервалов одинаковых размеров.
Самый простой способ: построить такой же интервал, что приводит к ненужному разбазариванию ячеек листа, в которых хранятся лишь промежуточные значения, ненужные при получении конечного результата. Ладно, если исходный интервал имеет малые размеры, а если достаточно большие?
Если пожелаете открыть прилагаемый файл Excel формата 2003, то предварительно в параметрах безопасности макросов необходимо выбрать режим: в 2003 «Безопасность - Средняя», а в 2007 «Отключить все макросы с уведомлением». При открытии содержащийся макрос необходимо включить.
Первый способ, вручную без макроса: пусть интервал расположен в A2:D4 (т.е. размеры 4*3), скаляр в F2. Тогда в H2 пишу формулу =($F$2-A2)^2 и заполняю ей интервал тоже размеров 4*3, т.е. H2:K4. И наконец в I6 пишу формулу =СУММ(H2:K4) где и размещён ответ.
Другой способ: в редакторе VBA создаю в текущем файле новый модуль, в него вставляю вот такую функцию:
Function SUMSQ(Scalar, ParamArray Interval())
SUMSQ = 0
For i = LBound(Interval) To UBound(Interval)
SUMSQ = SUMSQ + (Scalar - Interval(i)) ^ 2
Next i
End Function
Тогда в I8 вставляю эту функцию из категории «Определённые пользователем» (аж с 13 аргументами!), получается весьма громоздкая формула:
=SUMSQ(F2;A2;B2;C2;D2;A3;B3;C3;D3;A4;B4;C4;D4)
Результаты, конечно, совпали. Но... формула напоминает небезызвестный анекдот про автора опуса «Индеец Джо едет на коне» (много страниц из фраз «цок-цок-цок...»)
Что хотелось бы: как-нибудь изменить эту функцию так, чтобы первый аргумент по-прежнему содержал ссылку на скаляр, а второй и последний – сразу ссылку на весь интервал! Например, =ИМЯ_ФУНКЦИИ(F2;A2:D4)
Перелопачено много документации, но как это сделать, увы, не знаю...

И ещё вопрос. Если такая возможность всё же имеется, можно ли сделать так, чтобы функция (конечно, какая-нибудь другая) возвращала бы массив? Т.е. при вводе формулы с функцией в единичную ячейку отображался бы первый элемент массива, а потом выделяется нужный интервал для массива, нажимается F2 и затем Ctrl+Shift+Enter? Наподобие работают, например, ряд матричных функций (МУМНОЖ, МОБР), статистических (ТЕНДЕНЦИЯ, РОСТ) и др.

[okshef]

=СУММ((A2:D4-$F$2)^2)
Вводится как формула массива

P.S. ParamArray (http://msdn.microsoft.com/ru-ru/library/ct363x9h.aspx)

[Доярка]

Насчёт формулы массива - спасибо за подсказку! Можно и так. Но всё равно тот пример демонстрационный.
Вопрос же в принципиальном плане остаётся открытым.
С наступающим! В моём регионе уже около 18 часов, скоро солнце сядет, а там через 6 часов и Новый Год! Так что скоро сойдё в оффлайн.

[okshef]

Я не силен в макросах, но, по-моему, нужно использовать 2 переменные для обозначения массива и делать через вложенный цикл (по строкам и столбцам)

ИМХО, формула массива в данном случае вполне достаточна.

или как-то так
SUMSQ=0
Set range=Ваш_массив
skalar=[F2].value 'здесь могу ошибиться
for each c in range
SUMSQ = SUMSQ + (Scalar - c.Value) ^ 2
Next

[Доярка]

1. Если переменная есть массив, то у неё дожны быть свойства типа rows и columns, т.е. вложеные циклы должны идти по обоим индексам. Вся документация, что я видела, говорит только о ParamArray - с его использованием и привела пример абсурдной формулы.
2. В данном случае формула массива - вполне. Но возможны более сложные случаи.

или как-то так
Код: »
Явно можно описать диапазоны и в коде. Например,
Scalar=Cells(2,6).Value (буква F 6-я по счёту) и т.д.
Но снижается гибкость. Хотелось бы, чтобы какой-то один аргумент функции был массивом сразу весь, у которого можно определить число строк и столбцов. Мало ли какие операции необходимо проделать, например, только со строками, а оператор вида For Each... Next работает просто с каждым элементом объекта.

[okshef]

Передавайте массив ячеек как Range
Function SUMSQ(Scalar, Interval As Range)
SUMSQ = 0
For Each c In Interval
SUMSQ = SUMSQ + (Scalar - c) ^ 2
Next
End Function
Проверил - работает!

[Доярка]

Отлично!!! Есть!!!
Дальше – больше. Если аргумент Interval описан как Range, есть ли у него свойства типа Rows и Columns (т.е. сколько у него строк и столбцов)?
С наступившим Новым Годом и больше хороших деяний во благо продвинутых юзеров – и не только!

[okshef]

Доярка, вы же знаете ответ на свой вопрос. Зачем спрашиваете?
Конечно есть.

[Доярка]

Что имею?
Во-первых, если у меня в Excel 2007 в модуле VBA сидит не процедура (Sub), а функция (Function), то через меню «Вид -> Макросы -> Макросы» уже доступа к ней нет. Хорошо, что помню комбинацию Alt+F11 для быстрого вызова редактора VBA, которая и здесь имеет силу!
Во-вторых, при попытке в том коде вычислить значение типа Interval.Rows при повторном вычислении ячейки с этой вставленной функцией выдаётся сообщение об ошибке «#ЗНАЧ!» - т.е. просто не знаю, как это значение грамотно узнать.

Возьмём такую вот задачу. Пусть в некотором интервале размещена матрица. Требуется создать функцию, которая вычислила бы сумму элементов, находящихся в строке с указанным номером (2-ой аргумент) этой матрицы (1-ый аргумент). Причём если номер строки задан ошибочно, то и выводилось бы сообщение о какой-нибудь ошибке.
Как делаю с помощью формул рабочего листа? Пусть в этом же примере номер строки явно указан в ячейке C11. В A12 пишу формулу
=ИНДЕКС($A$2:$D$4;$C$11;СТОЛБЕЦ())
и растягиваю по D12. В E12 формула
=СУММ(A12:D12)
Коряво, не правда ли?

[okshef]

Доярка, во-первых, давайте подходить к вопросу согласно общих правил форума: одна проблема - одна тема, поэтому, если хотите, создайте новую тему и приложите файл.
Во-вторых, в рамках исходной темы приложите файл с кодом, в котором вы хотите вычислить Interval.rows

[Доярка]

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

[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. Вот и интересуюсь, как бы это сделать? Конечно, размеры выделения определял бы сам пользователь – точно так же, как при использовании встроенных функций, возвращающих матрицы.