Страницы: 1
RSS
Почему эксель забирает много ресурсов, как оптимизировать?
 
Почему эксель забирает много ресурсов, как оптимизировать?
Здравствуйте форумчане!

На каждую новую строку листа в эксель поступают данные из внешнего источника. После чего, расчитывается среднее значение за 200 и 1000 строк для графика.
Формулы типа =ЕСЛИ($A9<>"";СРЗНАЧ(A9:СМЕЩ(A9;-200;0));)
Но есть ощущение что когда добавляются данные на новую строку, то эксель пересчитывает все предыдущие расчёты средних (которые уже расчитаны) привожу гифку (+ файл-пример) на которой при каждых новых данных происходит:

Может как-то можно сделать чтобы эксель не пересчитывал уже ранее расчитанные данные или можно как-то попытаться оптимизировать? Может даже если есть какие-нибудь программы в которые можно отправлять данные для расчёта и забирать потом обратно результат для того, чтобы не загружать эксель?

В эксель-примере приведены однотипные расчёты только двух средних, а их больше, и в итоге сам процесс экселя в диспетчере задач занимает 1ГБ + ещё 3-4 ГБ занимает в виртуальной памяти на диске, ещё и процессор загружается на 80%, сам файл экселя 50МБ - вцелом всё начинает подтормаживать (((

Может кто-нибудь знает как формулу доработать или как уменьшить нагрузку на эксель?
 
СМЕЩ - летучая функция, пересчитывается при любых изменениях на листе. Диапазон можно формировать по-другому:
A9:ИНДЕКС(A:A;значение)
Заменить функцию, считать макросом. Варианты есть.

Цитата
garysanders написал: сам файл экселя 50МБ
И с таким файлом макросом будет правильнее
 
vikttur, благодарю за совет, попробую использовать функцию ИНДЕКС
 
я б так записал
=IF(AND($A2<>"";ROW()>=MALastMin);AVERAGE(A2:INDEX(A:A;ROW()-MALastMin));)
Изменено: БМВ - 14.04.2019 15:27:57
По вопросам из тем форума, личку не читаю.
 
БМВ, Спасибо, сейчас попробую
 
Цитата
garysanders написал: И с таким файлом макросом будет правильнее
В модуль листа
Код
Private Sub Worksheet_Change(ByVal Target As Range)
On Error Resume Next
If Not Intersect(Target, Columns(1)) Is Nothing And Target.Count = 1 Then
    Application.ScreenUpdating = False
    Application.EnableEvents = False
    iCount = Application.WorksheetFunction.Count(Columns(1))
    If iCount >= 200 Then
        Target(, 5) = Application.WorksheetFunction.Average(Range(Target, Target.Offset(-200)))
        If iCount >= 1000 Then
            Target(, 6) = Application.WorksheetFunction.Average(Range(Target, Target.Offset(-1000)))
        End If
    End If
End If
Application.EnableEvents = True
Application.ScreenUpdating = True
End Sub
Изменено: Sanja - 14.04.2019 16:50:10
Согласие есть продукт при полном непротивлении сторон
 
Меня терзают смутные сомнения относительно очерёдности >=200 и >=1000 :)
 
Точно, очередность тут лишняя, нужны оба значения. Изменил макрос и заменил файл выше
Согласие есть продукт при полном непротивлении сторон
 
Sanja, Юрий М, Спасибо, буду мучать макрос)))
 
А зачем СМЕЩ, ИНДЕКС при фиксированном смещении? Относительной адресации недостаточно?
В строку 201 и ниже
Код
=ЕСЛИ($A201<>"";СРЗНАЧ(A1:A201);)
В строку 1001 и ниже
Код
=ЕСЛИ($A1001<>"";СРЗНАЧ(A1:A1001);)
 
Казанский, в исходном переменной было это смещение задано.
По вопросам из тем форума, личку не читаю.
 
БМВ, ТС нигде не намекнул, что предполагается изменение констант 200 и 1000. Если все же нужно будет это сделать, можно включить стиль R1C1 и поменять константы через Ctrl+H. Это не сложнее, чем в диспетчере имен их менять.
Кстати, при последовательном вычислении средних, каждый раз складывать 200 или 1000 чисел - нерационально. Так нужно вычислить только первое среднее, а для последующих - вычитать выбывающую из диапазона ячейку и добавлять новую. Например для строки 1002 (т.к. данные начинаются с А2)
Код
=ЕСЛИ($A1002<>"";СРЗНАЧ(A2:A1002);)
А для 1003 и далее
Код
=ЕСЛИ($A1003<>"";D1002+(A1003-A2)/1000;)
 
Казанский, БМВ, Благодарю за помощь.
Предполагается изменение констант.
Логическую оптимизацию я немного применил, а плане того что считать рациональнее не каждый раз всё пересчитывать, а логически их применять только к новым данным (насколько это возможно), например к моему примеру плюсовать новые данные к сумме предыдущих по типу:
"Можно создать формулу, =$A1 например в ячейку B1, и =$B1+$A2 ячейку B2, и при необходимости перетаскивать ее.", а потом вычитать путём смещения на определённое количество ячеек.

В качестве обратной связи могу сказать замена СМЕЩ на ИНДЕКС где-то в два раза снизило нагрузку на процессор.
Ещё буду пытаться максимально дробить расчёты для увеличения скорости.

Всем кому интересна тема оптимизации советую ОЧЕНЬ интересные рекомендации по оптимизации "Производительность Excel", лично сам узнал много интересного по данной теме:
https://docs.microsoft.com/ru-ru/office/vba/excel/concepts/excel-performance/excel-improving-calcuat...
Производительность Excel: повышение производительности вычислений
Производительность Excel: улучшения производительности и ограничений
Производительность Excel: советы по оптимизации препятствий производительности

Также если кому интересно, я когда-то давно (в данный момент не интересовался) вкратце рассматривал Матлаб, у него к экселю сеть специальный переходник (по-моему через активикс), так вот через этот мост можно использовать все безграничные возможности Матлаба + через Матлаб можно делать распределённые вычисления. Также можно например создать какую-нибудь функцию запихнув её в специально сделанную дллку, потом зарегистрировать её в операционной системе, потом подгрузить в оперативную память (чтобы скорость не снижалась при чтении с диска). И такими ухищрениями можно добиться почти максимальной на данном оборудовании скорости функции))) Но это для тех, кто хочет максимальную скорость из раздела "хороший пользователь, но не программист" выше - только программирование.
Надеюсь эта информация кому-нибудь может пригодится.
Страницы: 1
Наверх