Нужна помощь в оптимизации SUMIFS в VBA

Я использую этот сайт уже несколько месяцев, чтобы помочь мне начать свою карьеру программиста, и это мой первый пост здесь. Прошу прощения, если пропустил вчера этот вопрос при поиске по форуму. У меня была электронная таблица с примерно 6000 активных Sumif, ссылающихся на таблицу из 500 000+ строк. Как вы можете себе представить, это заняло некоторое время, чтобы вычислить. Я написал их в VBA, поэтому они вычисляют только тогда, когда пользователь решит это сделать. Однако при таком количестве сумифов выполнение кода занимает около 3-5 минут. Я ищу способы ускорить это для лучшего взаимодействия с конечным пользователем. Ниже я опубликую, как я выполняю сумифы. Просто для некоторого контекста это выполняет sumifs для одной и той же группы пользователей дважды, но с одним другим критерием. Дайте мне знать, если я упустил какую-либо соответствующую информацию.

For i = 1 To 12
    Range("Emp_Utility").Offset(1, i).Activate
    Do Until Cells(ActiveCell.Row, 2) = ""
        ActiveCell.Value = Application.WorksheetFunction.SumIfs(Hrs, Emp, Cells(ActiveCell.Row, 2), Y, Cells(4, ActiveCell.Column), M, Cells(3, ActiveCell.Column))
        ActiveCell.Offset(1, 0).Activate
    Loop
Next i

For a = 1 To 12
    Range("Emp_Billable").Offset(1, a).Activate
    Do Until Cells(ActiveCell.Row, 30) = ""
        ActiveCell.Value = Application.WorksheetFunction.SumIfs(Hrs, Emp, Cells(ActiveCell.Row, 2), Y, Cells(4, ActiveCell.Column), M, Cells(3, ActiveCell.Column), Bill, "No")
        ActiveCell.Offset(1, 0).Activate
    Loop
Next a

optimization excel sumifs vba
person Clouse24    schedule 03.10.2013    source источник
comment
Похоже, вам стоит заглянуть в сводные таблицы.   -  person user2140261    schedule 03.10.2013
comment
Я бы хотел. Недостаточно динамичный для того, что мне нужно. Это всего лишь один шаг из нескольких в процессе экспорта мгновенных отчетов.   -  person Clouse24    schedule 03.10.2013

Ответы (2)


arrow_upward
2
arrow_downward

Загрузите диапазоны в вариантные массивы, а затем запишите СУММЕСЛИМН в коде, не используя формулы. Если вам нужны примеры, дайте мне знать, и я проведу вас через них.

РЕДАКТИРОВАТЬ: Нет проблем. Тогда вот пример.

Sub example()

    Dim EmpUtil, EmpBillable As Variant   ' Creates variant array

    EmpUtil = Range("Emp_Utility")        'Places Range into EmpUtil Array
    EmpBillable = Range("Emp_Billable")   'Places Range into EmpBillable Array

    For x = LBound(EmpUtil) To UBound(EmpUtil)   'Cycles through EmpUtil Rows
        'Do comparisons and additions here
        'References to arrays should be something like
        ' "EmpUtil(x,3) = example" - for the 3rd column

        'for calculations on each column you cycle through each column for that row
        For y = LBound(EmpUtil, 2) To UBound(EmpUtil, 2)
            EmpUtil(x, y) = Calculation

        Next y


    Next x

    For x = LBound(EmpBillable) To UBound(EmpBillable)   'Cycles through EmpBillable Rows
        'Do comparisons and additions here


    Next x

    Range("Emp_Utility") = EmpUtil         'Places EmpUtil Array back into Range
    Range("Emp_Billable") = EmpBillable    'Places EmpBillable Array back into Range


End Sub

Это должно помочь вам начать.

person jlaverde    schedule 03.10.2013
comment
Привет jlaverde, я определенно мог бы использовать пример. Я довольно неопытен с массивами в VBA. Спасибо! - person Clouse24; 03.10.2013
comment
Ну вот, если вам нужна какая-либо другая помощь, дайте мне знать. Кроме того, не забудьте проголосовать, если это полезно, или принять ответ, если это то, что вам нужно. - person jlaverde; 03.10.2013
comment
Спасибо! это определенно выглядит более эффективным. Быстрый вопрос по выполнению расчетов. У меня 12 столбцов, был бы у меня EmpUtil(x,1) = расчет EmpUtil(x,2) = расчет ETC.... - person Clouse24; 03.10.2013
comment
Вы можете сделать их отдельно, чтобы создать еще один цикл for. отредактирую как пример - person jlaverde; 03.10.2013
comment
В этом есть смысл. И последний вопрос: использую ли я синтаксис sumif точно так же, как worksheet.function? - person Clouse24; 03.10.2013
comment
Я бы использовал операторы if then else, потому что предпочел бы точно знать, что делает мой код. Хотя, возможно, вы сможете использовать эту функцию. - person jlaverde; 03.10.2013
comment
В этом разделе я получаю несоответствие типов. Если EmpUtil(x, 1) = Emp, то Emp — это диапазон в таблице... - person Clouse24; 03.10.2013
comment
Это должно быть значение Emp.value, если оно ссылается на одну ячейку. - person jlaverde; 04.10.2013

arrow_upward
1
arrow_downward

Вы можете рассмотреть альтернативу СУММЕСЛИМН (вычисление которой может занять целую вечность), например комбинацию СУММ и СОРТИРОВКИ. См. ответ на Как выполнить SumIf с использованием VBA для массива в Excel, который рассматривает использование комбинации СУММ и СОРТИРОВКИ, что приводит к очень быстрым вычислениям, но при этом возвращает те же значения, как если бы использовался метод СУММЕСЛИМН.

person Community    schedule 02.02.2014