На производстве постоянно приходится решать задачу совмещения данных из очень старой базы (1997 года) с современными средствами отображения информации, в частности со всеми любимым и всем понятным Excel. База организована так, что все параметры на одно время собраны в одну байтовую строку длиной до 4 мегабайт. Структура одной «большой записи» содержит дату, время и бинарный набор, для выборки данных из которого задействована еще одна специальная таблица. Когда нужно выбирать набор значений из одной строки, вся строка считывается один раз по сети в память компьютера пользователя, а потом отдельные значения разносятся в нужные клеточки листов Excel. Все это достаточно просто решается помещением в клетку вызова пользовательской функции, определяющей из каких байт «большой записи» выбрать данные.
Гораздо хуже получается, когда нужно вычислить сумму значений по одному параметру, но из многих записей. Держать в ОЗУ копии всех «больших записей», из которых необходимо выбирать данные не позволяет ограниченность доступного ОЗУ, поэтому приходится считывать все необходимые «большие записи» последовательно. Но если для каждой клеточки листа Excel, где требуется накопительная сумма, перечитывать по сети все записи, время обновления листа затягивается до неприличных минут. Было бы здорово, если можно было бы сначала собрать все запросы от всех клеточек листа, определить нужный набор «больших записей», потом однократно прочитав их, выполнить вычисление накоплений и только после этого выдать результаты в соответствующие клеточки.
Чтобы было проще понять суть разберем пример. Создадим пользовательскую функцию
GetNak(byval nPar as long, byval begDt as date, byval endDt as date) as double
nPar – номер параметра, который надо просуммировать
begDt – дата начала периода
endDt – конец периода
Вызвать такую функцию из клетки листа может любой пользователь Excel
=GetNak(3, 23/12/2024, 01/01/2024)
В теле функции адрес вызывающей клетки определяется с помощью Application.ThisCell. Этот адрес проверяется на наличие в списке обрабатываемых отложенных адресов. Список может быть выполнен в виде массива структур
Type S_NAKOP
Val As Double
oldVal As Double
NomPar as long
dBeg as date
dEnd as date
rgPar As Range
End Type
arOtloj(0 to 256) as S_NAKOP
Надо организовать цикл сравнения адресов rgPar c адресом клетки, определяемой Application.ThisCell и при равенстве, выдать текущее значение Val.
Если эта клетка еще не обрабатывалась, то надо добавить ее в массив, запомнить номер параметра NomPar, начало и конец периода накопления, присвоить полям Val и oldVal значение ноль и вернуть системе значение ноль.
Таким образом, при первом проходе расчета, получим список всех параметров и даты периодов накопления.
Событие Worksheet.Calculate вызывается системой ПОСЛЕ обработки всех клеток листа, то есть как раз тогда, когда можно начинать загрузку «больших записей» из базы, ведь теперь можно будет накапливать значения для всего массива.
Очистим значение Val для всех элементов массива, но не будем трогать oldVal. Это поле понадобится для определения конца обновления клеток.
Выполним цикл чтения «больших записей» и расчет накопительных параметров в поле Val. Здесь используется цикл в цикле, он может быть достаточно длинным, но гораздо быстрее, чем если бы считывали по сети каждый параметр отдельно.
Осталось выполнить последний цикл и сравнить oldVal и Val. Если они не равны, необходимо перенести значение Val в oldVal и выполнить Range.Dirty – метод, которым можно активизировать функцию, вызываемую из клетки листа.
Таким образом удалось сохранить наглядность и простоту Excel для основного контингента пользователей, не знакомых с программированием, но владеющих простыми приемами вызова функций из клетки и, одновременно, оптимизировать работу программы в условиях загруженной сети и больших объемов обрабатываемых данных.
В заключении скажу, что было бы гораздо красивее решить эту проблему с помощью команд SQL, где одним Select можно отобрать что угодно. Однако наработки, сделанные за четверть века никто бросать не дает, а уж о финансировании полной замены программного обеспечения мы можем только мечтать. Думаю, что многие организации в таком же положении, поэтому делюсь своими наработками и жду от Вас предложений или вопросов, по улучшению работы с Excel.
Для тех, кто заинтересуется, вот ссылки на описание функций
https://learn.microsoft.com/ru-ru/office/vba/api/excel.application.thiscell
https://learn.microsoft.com/ru-ru/office/vba/api/excel.worksheet.calculate(even)
https://learn.microsoft.com/ru-ru/office/vba/api/excel.range.calculate
Спасибо, что дочитали до конца.
