Здравствуйте!

Есть таблица. По колонкам - дни. По строкам - представлены данные: исходные и расчетные. Пусть в данном примере для понимания расчетные данные будут как кумулятивные. Суть здесь расчетов в том, что они цепные: начинают считать с первого дня по текущий.

Для решения данной проблемы создал пользовательские функции. Каждая расчетная мера имеет свою пользовательскую функцию, где в аргументе протягивается диапазон. Диапазон выбирается таким образом, что по строкам выбирается: Дата, Исходная Мера1 и Исходная Мера2. И по колонкам: с начальной даты по текущую ячейку. См вложенный файл. Потом каждая пользовательская функция вызывают главную функцию, но с двумя аргументами: диапазон и меру, которую нужно возвратить.

В общей функции для каждой ячейки идет считывание данных, и цепной расчет. Потом результат возвращается на ячейку, в зависимости какая вызвала пользовательская функция.

Для временного хранения данных по дням создал класс со свойствами.

Возьмем, для примера, Расчетную меру1. Для ячейки 02.09.2017 и он сложит Исходную Мера1 за 01.09.2017 и 02.09.2017. Для ячейки 03.09.2017 сложит туже меру за 01.09.2017, 02.09.2017 и 03.09.2017. и т. д.

Данный пример простой, считает быстро. Можно менять исходные данные и происходит перерасчет для каждой ячейки.

В рабочем примере алгоритм сложный: использовано больше строк (для исходных данных) с вложенные циклами, даже возвращается к ранним итерациям для перерасчетов. И таких табличных блоков, скажем, около 20. И тут возникает проблема: долго пересчитывается. Ведь пересчет идет для каждой ячейки, и для последующей ячейки, получается повторяет расчеты для предыдущих ячеек и плюс для текущего дня. Включая еще считывание данных с диапазона.

Не обращайте внимание, что в макросе переменные на русском языке. Специально, чтобы проще Вам было понять код.

Не уверен, что решение для данной задачи подобрано оптимально. Будто не в ту сторону иду.

1. Подскажите, как оптимально использовать пользовательские функции для данной задачи, чтобы меньше повторять расчеты и уменьшить время перерасчета?

2. Для данной задачи для временного хранения данных лучше использовать Класс или Пользовательский тип?

big-dukeferzmikk,

Посмотрите в сторону отсоединенных Recordset вместо массивов.
Ощущение, что с ними побыстрей будетRecordset знаю что это. Но что значит отсоединенных Recordset?

Наверно как вариант можно использовать пользовательскую функцию вне таблицы, где на входе диапазон (включая дату и исходные меры) и в результате меняет значения в расчетных мерах. Только при изменении значении в одной ячейки исходных мер - пересчитаются все ячейки расчетных мер.

КазанскийМожно заменить функцию на обработчик события Worksheet_Change , который будет следить за изменением ячеек в диапазоне "исходных мер" и менять ячейки "расчетных мер". Поскольку вычисления будут производиться в одном вызове процедуры, считывать данные можно один раз и не повторять расчеты для предыдущих ячеек. Как вариант, только на листе не одна такая табличка. Важно, чтобы макрос понимал в каких ячейках находятся исходные данные и для какой таблицы выводить расчетные данные.

Казанский,

ОК, хорошо! А как быть, если со временем таблицы добавляются, удаляются, сдвиг строк, то в коде динамически как это учесть?

ferzmikk2. Для данной задачи для временного хранения данных лучше использовать Класс или Пользовательский тип?

Казанскийferzmikk,
Пользовательский тип быстрее, но не дружит с Variant. Класс дружит с Variant, но медленнее, если использовать Public переменные. А если использовать Property Let/Property Get, то ваще мрак.
С другой стороны, если идет речь об эффективности вычислений, то Variant противопоказан. Только Double и Long.
Так что пользовательский тип.Добавлю. У класса есть возможность свои методы добавлять, например, для перерасчетов внутри текущего экземпляра или предыдущих экземпляров. А для пользовательских типов придется отдельные процедуры прописывать.

КазанскийА если использовать Property Let/Property Get, то ваще мрак.при отладке видим переменные без и с приставкой m. Это как бы усложняет отладку: табло насыщается дублирующей информацией. Возможно ли сделать так, чтобы переменные с приставкой m не были видны в отладчике? Или по своей логике работы они должны быть видны?