Имя пользователя:
Пароль:  
Помощь | Регистрация | Забыли пароль?  | Правила  

Компьютерный форум OSzone.net » Сфера Microsoft » Microsoft Office (Word, Excel, Outlook и т.д.) » Разное - Пользовательская функция в Excel с параметром – интервалом, можно ли так?

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

Аватара для Доярка

Новый участник


Сообщения: 33
Благодарности: 0

Профиль | Сайт | Отправить PM | Цитировать


Вложения
Тип файла: xls DemoFunction.xls
(32.0 Kb, 5 просмотров)
Всех с наступающим! Несмотря на то, что до Нового Года остаётся совсем мало времени, один вопрос не даёт мне покоя.
Как известно, в 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? Наподобие работают, например, ряд матричных функций (МУМНОЖ, МОБР), статистических (ТЕНДЕНЦИЯ, РОСТ) и др.

Отправлено: 09:12, 31-12-2011

 

Модератор


Moderator


Сообщения: 16585
Благодарности: 3169

Профиль | Сайт | Отправить PM | Цитировать


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

P.S. ParamArray

-------
При заполнении сведений о конфигурации компьютера не забудь поставить флажок: отображать - "Да"
-------------------------------------------------------------------------------------------
Ассоциация VirusNet - помощь и обучение борьбе с вирусами. Некоторые вопросы загрузки в моем блоге


Отправлено: 10:02, 31-12-2011 | #2



Для отключения данного рекламного блока вам необходимо зарегистрироваться или войти с учетной записью социальной сети.

Если же вы забыли свой пароль на форуме, то воспользуйтесь данной ссылкой для восстановления пароля.


Аватара для Доярка

Новый участник


Сообщения: 33
Благодарности: 0

Профиль | Сайт | Отправить PM | Цитировать


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

Отправлено: 10:38, 31-12-2011 | #3


Модератор


Moderator


Сообщения: 16585
Благодарности: 3169

Профиль | Сайт | Отправить PM | Цитировать


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

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

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

-------
При заполнении сведений о конфигурации компьютера не забудь поставить флажок: отображать - "Да"
-------------------------------------------------------------------------------------------
Ассоциация VirusNet - помощь и обучение борьбе с вирусами. Некоторые вопросы загрузки в моем блоге


Последний раз редактировалось okshef, 31-12-2011 в 11:00.


Отправлено: 10:52, 31-12-2011 | #4


Аватара для Доярка

Новый участник


Сообщения: 33
Благодарности: 0

Профиль | Сайт | Отправить PM | Цитировать


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

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

Отправлено: 10:59, 31-12-2011 | #5


Модератор


Moderator


Сообщения: 16585
Благодарности: 3169

Профиль | Сайт | Отправить PM | Цитировать


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

-------
При заполнении сведений о конфигурации компьютера не забудь поставить флажок: отображать - "Да"
-------------------------------------------------------------------------------------------
Ассоциация VirusNet - помощь и обучение борьбе с вирусами. Некоторые вопросы загрузки в моем блоге

Это сообщение посчитали полезным следующие участники:

Отправлено: 11:17, 31-12-2011 | #6


Аватара для Доярка

Новый участник


Сообщения: 33
Благодарности: 0

Профиль | Сайт | Отправить PM | Цитировать


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

Отправлено: 08:05, 02-01-2012 | #7


Модератор


Moderator


Сообщения: 16585
Благодарности: 3169

Профиль | Сайт | Отправить PM | Цитировать


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

-------
При заполнении сведений о конфигурации компьютера не забудь поставить флажок: отображать - "Да"
-------------------------------------------------------------------------------------------
Ассоциация VirusNet - помощь и обучение борьбе с вирусами. Некоторые вопросы загрузки в моем блоге


Отправлено: 09:20, 02-01-2012 | #8


Аватара для Доярка

Новый участник


Сообщения: 33
Благодарности: 0

Профиль | Сайт | Отправить PM | Цитировать


Что имею?
Во-первых, если у меня в 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, 02-01-2012 в 12:50.


Отправлено: 10:46, 02-01-2012 | #9


Модератор


Moderator


Сообщения: 16585
Благодарности: 3169

Профиль | Сайт | Отправить PM | Цитировать


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

-------
При заполнении сведений о конфигурации компьютера не забудь поставить флажок: отображать - "Да"
-------------------------------------------------------------------------------------------
Ассоциация VirusNet - помощь и обучение борьбе с вирусами. Некоторые вопросы загрузки в моем блоге


Отправлено: 12:54, 02-01-2012 | #10



Компьютерный форум OSzone.net » Сфера Microsoft » Microsoft Office (Word, Excel, Outlook и т.д.) » Разное - Пользовательская функция в Excel с параметром – интервалом, можно ли так?

Участник сейчас на форуме Участник сейчас на форуме Участник вне форума Участник вне форума Автор темы Автор темы Шапка темы Сообщение прикреплено

Похожие темы
Название темы Автор Информация о форуме Ответов Последнее сообщение
есть ли в php аналог функции XIRR? (XIRR-функция Excel) zvezda_t Вебмастеру 2 25-02-2010 02:55
Разное - Запустить 3D Mark с определённым параметром. Можно такое? yurfed Программирование и базы данных 2 01-10-2008 10:34
Можно ли так поступить? sirius_99 Microsoft Windows NT/2000/2003 3 10-07-2007 15:12
Можно ли сделать так... starav Microsoft Windows NT/2000/2003 7 12-04-2005 09:45
Можно ли так. Denhell Вебмастеру 9 10-03-2005 20:24




 
Переход