Вычисление макросом в место формул

[staniiislav]

Доброго времени суток, Ув. Форумчане
В очередной раз пришел просить помощи и оригинальных идей.
Вопрос состоит в том, что написал небольшой макрос, для уменьшения размера файла:

Sub vichislenie()

    Dim i As Long
'On Error Resume Next

    Application.ScreenUpdating = False
    Application.Calculation = xlManual
 With ActiveSheet
    '.Unprotect pass

                For i = 6 To 431
                    Cells(i, 3).Value = Cells(i - 1, 20)
                    Cells(i, 9).Value = Cells(i, 7) * Cells(i, 8)
                    Cells(i, 11).Value = Cells(i, 7) * Cells(i, 10)
                    Cells(i, 13).Value = Cells(i, 7) * Cells(i, 12)
                    Cells(i, 15).Value = Cells(i, 7) * Cells(i, 14)
                    Cells(i, 17).Value = Cells(i, 7) * Cells(i, 16)
                    Cells(i, 18).Value = Cells(i, 10) + Cells(i, 12) + Cells(i, 14) + Cells(i, 16)
                    Cells(i, 19).Value = Cells(i, 7) * Cells(i, 18)
                    Cells(i, 20).Value = Cells(i, 3) + Cells(i, 4) - Cells(i, 8) - Cells(i, 18)
                    Cells(i, 21).Value = Cells(i - 1, 24)
                    Cells(i, 23).Value = Cells(i, 4) * Cells(i, 22)
                    Cells(i, 24).Value = Cells(i, 21) + Cells(i, 23) - Cells(i, 92)
                    Cells(i, 25).Value = Cells(i - 1, 46)
                    Cells(i, 40).Value = Cells(i, 28) * Cells(i, 34)
                    Cells(i, 41).Value = Cells(i, 29) * Cells(i, 35)
                    Cells(i, 42).Value = Cells(i, 30) * Cells(i, 36)
                    Cells(i, 43).Value = Cells(i, 31) * Cells(i, 37)
                    Cells(i, 44).Value = Cells(i, 32) * Cells(i, 38)
                    Cells(i, 46).Value = Cells(i, 25) + Cells(i, 45) - Cells(i, 89)
                    Cells(i, 47).Value = Cells(i - 1, 49)
                    Cells(i, 49).Value = Cells(i, 47) + Cells(i, 48) - Cells(i, 90)
                    Cells(i, 50).Value = Cells(i - 1, 58)
                    Cells(i, 58).Value = Cells(i, 50) + Cells(i, 57) - Cells(i, 91)
                    Cells(i, 59).Value = Cells(i - 1, 61)
                    Cells(i, 61).Value = Cells(i, 59) + Cells(i, 60) - Cells(i, 93)
                    Cells(i, 62).Value = Cells(i - 1, 64)
                    Cells(i, 64).Value = Cells(i, 62) + Cells(i, 63) - Cells(i, 94)
                    Cells(i, 65).Value = Cells(i - 1, 67)
                    Cells(i, 67).Value = Cells(i, 65) + Cells(i, 66) - Cells(i, 95)
                    Cells(i, 68).Value = Cells(i - 1, 70)
                    Cells(i, 70).Value = Cells(i, 68) + Cells(i, 69) - Cells(i, 96)
                    Cells(i, 71).Value = Cells(i, 25)
                    Cells(i, 72).Value = Cells(i, 47)
                    Cells(i, 73).Value = Cells(i, 50)
                    Cells(i, 74).Value = Cells(i, 21)
                    Cells(i, 75).Value = Cells(i, 59)
                    Cells(i, 76).Value = Cells(i, 62)
                    Cells(i, 77).Value = Cells(i, 65)
                    Cells(i, 78).Value = Cells(i, 68)
                    Cells(i, 80).Value = Cells(i, 45)
                    Cells(i, 81).Value = Cells(i, 48)
                    Cells(i, 82).Value = Cells(i, 57)
                    Cells(i, 83).Value = Cells(i, 23)
                    Cells(i, 84).Value = Cells(i, 60)
                    Cells(i, 85).Value = Cells(i, 63)
                    Cells(i, 86).Value = Cells(i, 66)
                    Cells(i, 87).Value = Cells(i, 69)
                    Cells(i, 98).Value = Cells(i, 46)
                    Cells(i, 99).Value = Cells(i, 49)
                    Cells(i, 100).Value = Cells(i, 58)
                    Cells(i, 101).Value = Cells(i, 24)
                    Cells(i, 102).Value = Cells(i, 61)
                    Cells(i, 103).Value = Cells(i, 64)
                    Cells(i, 104).Value = Cells(i, 67)
                    Cells(i, 105).Value = Cells(i, 70)
                    Cells(i, 116).Value = Cells(i, 89) - Cells(i, 107)
                    Cells(i, 117).Value = Cells(i, 90) - Cells(i, 108)
                    Cells(i, 118).Value = Cells(i, 91) - Cells(i, 109)
                    Cells(i, 119).Value = Cells(i, 92) - Cells(i, 110)
                    Cells(i, 120).Value = Cells(i, 93) - Cells(i, 111)
                    Cells(i, 121).Value = Cells(i, 94) - Cells(i, 112)
                    Cells(i, 122).Value = Cells(i, 95) - Cells(i, 113)
                    Cells(i, 123).Value = Cells(i, 96) - Cells(i, 114)
                                                        
                    
                Next i
    '.Protect pass, DrawingObjects:=True, Contents:=True, Scenarios:=True, _
    '        AllowFiltering:=True, UserInterfaceOnly:=True
    '        .EnableOutlining = True
 End With

    Application.ScreenUpdating = True
    Application.Calculation = xlCalculationAutomatic
   
End Sub

[staniiislav]

в целом вроде быстро работает, но может кто подскажет как его еще быстрее сделать, или как правильнее его записать? В оригинале файла, еще есть столбцы с ВПР, Еслиошибка, сумм, суммпроизв.
Спасибо за внимание.
С Ув. Станислав

и забыл сказать, этот макрос хочу поставить на изменение событий листа

[EducatedFool]

с таким макросом тормозов будет значительно больше, нежели с формулами

Какая разница, макрос пересчитывает значения, или формулы?
Формулы быстрее всё вычислят, либо макрос нужно делать намного сложнее, чем то, что вы сделали (оптимизировать процесс расчётов)

Зачем в таблице много столбцов с идентичными данными?

Сколько весит файл?
Формул-то немного, на размер файла не должно заметно влиять...
Может, убрать из ячеек лишнее форматирование?
Есть много способов уменьшения размера файла...

[Казанский]

Общий принцип - загрузить значения диапазона в массив, работать с элементами массива, выгрузить обратно в диапазон. Получается примерно в 40 раз быстрее.

[staniiislav]

Какая разница, макрос пересчитывает значения, или формулы?

только для уменьшения размера файла и скорости его открытия (открывается около 30-50 сек)

Формулы быстрее всё вычислят, либо макрос нужно делать намного сложнее, чем то, что вы сделали (оптимизировать процесс расчётов)

если есть готовый пример, на который можно ориентироваться, выложите пожалуйста

Зачем в таблице много столбцов с идентичными данными?

файлик планирование бюджета, именно в таком виде начальство хочет видеть план и начальству удобно просматривать необходимые показатели

Сколько весит файл?

27,6 мб, *.xlsm (33 листа вычислительных и еще 9 вспомогательных из которых берутся данные)

Формул-то немного, на размер файла не должно заметно влиять...

я тоже так думал, сделал свод только из значений, в который выгружается данные из бюджета и получил более 14 000 строк и 125 столбцов, размер составил 10,5 мб, *.xlsm. А на втором листе через суммпроизв по 4 критерия собираю данные из 14 000 строк

Может, убрать из ячеек лишнее форматирование?

условного форматирования вообще нет, формат ячеек ставлю только # ##0,0;-# ##0,0;;, 5 столбцов с 5-431 строку залиты цветом и заголовок выделен цветом

Есть много способов уменьшения размера файла...

из того что я перечислил можно что нибудь убрать?

EducatedFool

Спасибо за ответ.

Общий принцип - загрузить значения диапазона в массив, работать с элементами массива, выгрузить обратно в диапазон. Получается примерно в 40 раз быстрее.

Если у Вас есть пример, как мне можно улучшить мой "детский" макрос, выложите пожалуйста, может немного увеличу свою познания

Казанский

Спасибо за ответ.

[Hugo121]

Попробуйте так:

Sub vichislenie()

    Dim i As Long
    Dim a()
    'On Error Resume Next

    Application.ScreenUpdating = False
    Application.Calculation = xlManual
 With ActiveSheet
    '.Unprotect pass
a = .Range(.Cells(1, 1), .Cells(431, 114)).Value

                For i = 6 To 431
                    a(i, 3) = a(i - 1, 20)
                    a(i, 9) = a(i, 7) * a(i, 8)
                    a(i, 11) = a(i, 7) * a(i, 10)
                    a(i, 13) = a(i, 7) * a(i, 12)
                    a(i, 15) = a(i, 7) * a(i, 14)
                    a(i, 17) = a(i, 7) * a(i, 16)
                    a(i, 18) = a(i, 10) + a(i, 12) + a(i, 14) + a(i, 16)
                    a(i, 19) = a(i, 7) * a(i, 18)
                    a(i, 20) = a(i, 3) + a(i, 4) - a(i, 8) - a(i, 18)
                    a(i, 21) = a(i - 1, 24)
                    a(i, 23) = a(i, 4) * a(i, 22)
                    a(i, 24) = a(i, 21) + a(i, 23) - a(i, 92)
                    a(i, 25) = a(i - 1, 46)
                    a(i, 40) = a(i, 28) * a(i, 34)
                    a(i, 41) = a(i, 29) * a(i, 35)
                    a(i, 42) = a(i, 30) * a(i, 36)
                    a(i, 43) = a(i, 31) * a(i, 37)
                    a(i, 44) = a(i, 32) * a(i, 38)
                    a(i, 46) = a(i, 25) + a(i, 45) - a(i, 89)
                    a(i, 47) = a(i - 1, 49)
                    a(i, 49) = a(i, 47) + a(i, 48) - a(i, 90)
                    a(i, 50) = a(i - 1, 58)
                    a(i, 58) = a(i, 50) + a(i, 57) - a(i, 91)
                    a(i, 59) = a(i - 1, 61)
                    a(i, 61) = a(i, 59) + a(i, 60) - a(i, 93)
                    a(i, 62) = a(i - 1, 64)
                    a(i, 64) = a(i, 62) + a(i, 63) - a(i, 94)
                    a(i, 65) = a(i - 1, 67)
                    a(i, 67) = a(i, 65) + a(i, 66) - a(i, 95)
                    a(i, 68) = a(i - 1, 70)
                    a(i, 70) = a(i, 68) + a(i, 69) - a(i, 96)
                    a(i, 71) = a(i, 25)
                    a(i, 72) = a(i, 47)
                    a(i, 73) = a(i, 50)
                    a(i, 74) = a(i, 21)
                    a(i, 75) = a(i, 59)
                    a(i, 76) = a(i, 62)
                    a(i, 77) = a(i, 65)
                    a(i, 78) = a(i, 68)
                    a(i, 80) = a(i, 45)
                    a(i, 81) = a(i, 48)
                    a(i, 82) = a(i, 57)
                    a(i, 83) = a(i, 23)
                    a(i, 84) = a(i, 60)
                    a(i, 85) = a(i, 63)
                    a(i, 86) = a(i, 66)
                    a(i, 87) = a(i, 69)
                    a(i, 98) = a(i, 46)
                    a(i, 99) = a(i, 49)
                    a(i, 100) = a(i, 58)
                    a(i, 101) = a(i, 24)
                    a(i, 102) = a(i, 61)
                    a(i, 103) = a(i, 64)
                    a(i, 104) = a(i, 67)
                    a(i, 105) = a(i, 70)
                    a(i, 116) = a(i, 89) - a(i, 107)
                    a(i, 117) = a(i, 90) - a(i, 108)
                    a(i, 118) = a(i, 91) - a(i, 109)
                    a(i, 119) = a(i, 92) - a(i, 110)
                    a(i, 120) = a(i, 93) - a(i, 111)
                    a(i, 121) = a(i, 94) - a(i, 112)
                    a(i, 122) = a(i, 95) - a(i, 113)
                    a(i, 123) = a(i, 96) - a(i, 114)
                                                                         
                Next i
                
    .Range(.Cells(1, 1), .Cells(431, 114)).Value = a
    
    '.Protect pass, DrawingObjects:=True, Contents:=True, Scenarios:=True, _
    '        AllowFiltering:=True, UserInterfaceOnly:=True
    '        .EnableOutlining = True
 End With

    Application.ScreenUpdating = True
    Application.Calculation = xlCalculationAutomatic
   
End Sub

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

[RAN.]

А зачем все каждый раз пересчитывать?

Private Sub vichislenie(n&, allrow&)
    Dim i&
    For i = n To allrow
        Cells(i, 3).Value = Cells(i - 1, 20)
        Cells(i, 9).Value = Cells(i, 7) * Cells(i, 8)
        Cells(i, 11).Value = Cells(i, 7) * Cells(i, 10)
        Cells(i, 13).Value = Cells(i, 7) * Cells(i, 12)
        '..................................................................

    Next i
 End Sub
Private Sub Worksheet_Change(ByVal Target As Range)
    Dim n&, allrow&
    If Target.Row < 6 Then Exit Sub
    With Application
        .EnableEvents = False
        .ScreenUpdating = False
        .Calculation = xlManual
            n = Target.Row: allrow = Target.Row + Target.Rows.Count - 1
            Call vichislenie(n, allrow)
        .EnableEvents = True
        .ScreenUpdating = True
        .Calculation = xlCalculationAutomatic
    End With
End Sub

[Казанский]

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

Sub vichislenie()

    Dim i As Long, v()
'On Error Resume Next

'    Application.ScreenUpdating = False 'при работе с массивом не нужно
'    Application.Calculation = xlManual 'при работе с массивом не нужно
 With Range(Cells(1, 1), Cells(431, 123))
    '.Unprotect pass
        v = .Value 'значения диапазона в массив
                For i = 6 To 431
                    v(i, 3) = v(i - 1, 20)
                    v(i, 9) = v(i, 7) * v(i, 8)
                    v(i, 11) = v(i, 7) * v(i, 10)
                    v(i, 13) = v(i, 7) * v(i, 12)
                    v(i, 15) = v(i, 7) * v(i, 14)
                    v(i, 17) = v(i, 7) * v(i, 16)
                    v(i, 18) = v(i, 10) + v(i, 12) + v(i, 14) + v(i, 16)
                    v(i, 19) = v(i, 7) * v(i, 18)
                    v(i, 20) = v(i, 3) + v(i, 4) - v(i, 8) - v(i, 18)
                    v(i, 21) = v(i - 1, 24)
                    v(i, 23) = v(i, 4) * v(i, 22)
                    v(i, 24) = v(i, 21) + v(i, 23) - v(i, 92)
                    v(i, 25) = v(i - 1, 46)
                    v(i, 40) = v(i, 28) * v(i, 34)
                    v(i, 41) = v(i, 29) * v(i, 35)
                    v(i, 42) = v(i, 30) * v(i, 36)
                    v(i, 43) = v(i, 31) * v(i, 37)
                    v(i, 44) = v(i, 32) * v(i, 38)
                    v(i, 46) = v(i, 25) + v(i, 45) - v(i, 89)
                    v(i, 47) = v(i - 1, 49)
                    v(i, 49) = v(i, 47) + v(i, 48) - v(i, 90)
                    v(i, 50) = v(i - 1, 58)
                    v(i, 58) = v(i, 50) + v(i, 57) - v(i, 91)
                    v(i, 59) = v(i - 1, 61)
                    v(i, 61) = v(i, 59) + v(i, 60) - v(i, 93)
                    v(i, 62) = v(i - 1, 64)
                    v(i, 64) = v(i, 62) + v(i, 63) - v(i, 94)
                    v(i, 65) = v(i - 1, 67)
                    v(i, 67) = v(i, 65) + v(i, 66) - v(i, 95)
                    v(i, 68) = v(i - 1, 70)
                    v(i, 70) = v(i, 68) + v(i, 69) - v(i, 96)
                    v(i, 71) = v(i, 25)
                    v(i, 72) = v(i, 47)
                    v(i, 73) = v(i, 50)
                    v(i, 74) = v(i, 21)
                    v(i, 75) = v(i, 59)
                    v(i, 76) = v(i, 62)
                    v(i, 77) = v(i, 65)
                    v(i, 78) = v(i, 68)
                    v(i, 80) = v(i, 45)
                    v(i, 81) = v(i, 48)
                    v(i, 82) = v(i, 57)
                    v(i, 83) = v(i, 23)
                    v(i, 84) = v(i, 60)
                    v(i, 85) = v(i, 63)
                    v(i, 86) = v(i, 66)
                    v(i, 87) = v(i, 69)
                    v(i, 98) = v(i, 46)
                    v(i, 99) = v(i, 49)
                    v(i, 100) = v(i, 58)
                    v(i, 101) = v(i, 24)
                    v(i, 102) = v(i, 61)
                    v(i, 103) = v(i, 64)
                    v(i, 104) = v(i, 67)
                    v(i, 105) = v(i, 70)
                    v(i, 116) = v(i, 89) - v(i, 107)
                    v(i, 117) = v(i, 90) - v(i, 108)
                    v(i, 118) = v(i, 91) - v(i, 109)
                    v(i, 119) = v(i, 92) - v(i, 110)
                    v(i, 120) = v(i, 93) - v(i, 111)
                    v(i, 121) = v(i, 94) - v(i, 112)
                    v(i, 122) = v(i, 95) - v(i, 113)
                    v(i, 123) = v(i, 96) - v(i, 114)
                                                        
                    
                Next i
    '.Protect pass, DrawingObjects:=True, Contents:=True, Scenarios:=True, _
    '        AllowFiltering:=True, UserInterfaceOnly:=True
    '        .EnableOutlining = True
 
        .Value = v 'массив в диапазон
 End With

    Application.ScreenUpdating = True
    Application.Calculation = xlCalculationAutomatic
   
End Sub

[staniiislav]

Hugo121

У меня между указанными столбцами есть формулы, получается каждый отдельный столбец нужно делать отдельным массивом?
Спасибо!

RAN.

Спасибо Вам огромное, но нужно чтобы пересчитывало все массивы,т.к. от определенной ячейки может быть зависимость еще на n-е кол. ячеек.
И кстати в это месте выдало ошибку

Call vichislenie(n, allrow)

переименовал процедуру на функцию, но все равно не посчитало (((

Казанский

Я примерно понял за какие вы массивы говорили и примерно понял алгоритм выполнения данных массивов, но если между столбцами формулы типа ВПР или СУММПРОИЗВ, получается на каждый вычисляемый столбец нужно делать свой массив? Если да, то получается время выполнения макросса уменьшится не на много, по сравнению с моим примером?

[Hugo121]

Уменьшится, и скорость работы даже заметно не упадёт от бОльшего количества массивов, просто код будет чуть сложнее.
Делаете массив a из столбца А, массив c из столбца С.
Затем a(i,1)= a(i,1)+c(i,1)
Затем выгружаете массивы назад по местам.
Столбец B остаётся с формулами.