Доброго времени суток, прошу помощи в оптимизации макросов.
Есть расчетный файл, в него ежедневно добавляются обновленные исходные данные, по этим данным производится расчет, результаты сохраняются в отдельный файл, исходные данные и расчеты из расчетного файла удаляются (на следующий день все тоже самое повторяется).
Данные удаляются именно очисткой содержимого ( и формата ячеек), а не путем удаления ячейки совсем.
Чистятся все строки кроме заголовка и первой строки с формулами и исходными данными.
Изначально, расчет производился по одной большой и сложной формуле, она охватывала не все случаи, усложнять формулу было с каждым разом все сложнее. Было принято решение определять какой именно случай представлен в исходных данных (каждая строка отдельно) и в зависимости от случая применять конкретную формулу для этого случая, а не одну большую универсальную.
Изначально, расчет производился по формуле, которая "растягивалась" на весь массив данных макросом:
после того как формулу разделили на несколько более простых такой вариант уже не подходил, на этом форуме добрый человек подобрал решение:
Все работает, основной расчет стал значительно точнее. НО, после того как расчет произведен, расчетный файл нужно почистить, изначально использовался макрос:
данный макрос стал в разы дольше выполнять свою задачу, обычно диапазон в 200 тысяч строк он чистил за считанные секунды, но после обновления макроса для расчета время выполнения очистки увеличилось до десятков минут.
Методом тыка макрос был изменен:
Если сначала почистить содержимое ячейки а затем её формат - процесс протекает быстрее, но все равно занимает несколько минут. Основное время затрачивается именно на очистку столбца в который макросом были подставлены формулы для расчета, проверял неоднократно. Если перевести формулы в значения, процесс протекает быстрее, но на то чтобы "зазначить" формулы, тоже тратится время.
Внимание вопрос: можно ли каким то образом ускорить очистку ячеек? главное - не удалять ячейки/строки насовсем, а очистить их содержимое. как вариант зазначить диапазон перед очисткой, или вообще приравнять каждую ячейку к 0, а потом очистить диапазон. главное чтобы это было быстро
Заранее спасибо!
Есть расчетный файл, в него ежедневно добавляются обновленные исходные данные, по этим данным производится расчет, результаты сохраняются в отдельный файл, исходные данные и расчеты из расчетного файла удаляются (на следующий день все тоже самое повторяется).
Данные удаляются именно очисткой содержимого ( и формата ячеек), а не путем удаления ячейки совсем.
Чистятся все строки кроме заголовка и первой строки с формулами и исходными данными.
Изначально, расчет производился по одной большой и сложной формуле, она охватывала не все случаи, усложнять формулу было с каждым разом все сложнее. Было принято решение определять какой именно случай представлен в исходных данных (каждая строка отдельно) и в зависимости от случая применять конкретную формулу для этого случая, а не одну большую универсальную.
Изначально, расчет производился по формуле, которая "растягивалась" на весь массив данных макросом:
| Код |
|---|
Sub Было_расчет()
Range("B2").AutoFill Destination:=Range("B2").Resize(Range("A4").End(xlDown).Row - Range("B2").Row + 1) 'растянуть формулу
Calculate
End Sub
|
| Код |
|---|
Sub Стало_расчет() 'ВПР основных формул
Dim lastRow As Long 'пременная для определения нижней границы диапазона
lastRow = Cells(Rows.Count, 1).End(xlUp).Row 'последняя строка по столбцу 1 (A)
Application.ReferenceStyle = xlR1C1 'стиль ссылок R1C1
FillFormulasRange Range(Cells(2, 2), Cells(lastRow, 2)), Range(Cells(2, 16), Cells(lastRow, 16)), Sheets("Стало").Range("T2:U6") 'подстановка формул по условию
Calculate
Application.ReferenceStyle = xlA1 'стиль ссылок А1
End Sub
Private Sub FillFormulasRange(rTarget As Range, rCondition As Range, rDictionary As Range) 'вспомогательное для ВПРа основных формул
Dim dic As Object
Set dic = GetDic(rDictionary)
Dim aTrg As Variant
Dim aCon As Variant
aCon = rCondition.Value
ReDim aTrg(1 To UBound(aCon, 1), 1 To 1)
Dim yc As Long
For yc = 1 To UBound(aCon, 1)
If dic.Exists(aCon(yc, 1)) Then
aTrg(yc, 1) = dic.Item(aCon(yc, 1))
End If
Next
rTarget.Cells(1, 1).Resize(UBound(aTrg, 1), 1).FormulaR1C1 = aTrg
End Sub
Private Function GetDic(rr As Range) As Object 'вспомогательное для ВПРа основных формул
Dim arr As Variant
arr = rr.Columns(1).Resize(, 2).FormulaR1C1
Dim dic As Object
Set dic = CreateObject("Scripting.Dictionary")
Dim ya As Long
For ya = 1 To UBound(arr, 1)
dic.Item(arr(ya, 1)) = arr(ya, 2)
Next
Set GetDic = dic
End Function
|
| Код |
|---|
Sub Было_очистка()
Rows("3:250000").Select 'выбрать строку
Selection.Clear 'очистить
End Sub
|
Методом тыка макрос был изменен:
| Код |
|---|
Sub Стало_очистка()
Rows("3:250000").Select 'выбрать строку
Selection.ClearContents 'очистить
Selection.ClearFormats 'очистить
End Sub
|
Внимание вопрос: можно ли каким то образом ускорить очистку ячеек? главное - не удалять ячейки/строки насовсем, а очистить их содержимое. как вариант зазначить диапазон перед очисткой, или вообще приравнять каждую ячейку к 0, а потом очистить диапазон. главное чтобы это было быстро
Заранее спасибо!
Изменено: - 24.04.2024 15:44:37
