Оптимизировать для каждого цикла в сводной таблице

415
SeattleITguy

Я с трудом пытаюсь оптимизировать For Eachцикл VBA, который должен анализировать 10-15 тыс. Строк в сводной таблице, основанной на таблице Excel, и все в одной книге. В настоящее время это занимает около 2 минут, что я хотел бы улучшить, поскольку я использую это на собраниях.

Я искал и нашел несколько интересных предложений, таких как отключение обновления экрана, ручные вычисления и " dim" мои переменные как другие типы данных, но я не получил никакой разницы в скорости. Я предполагаю, что For Eachцикл просто не предназначен для этой цели.

Читая этот пост Супер пользователя, похоже, что я мог бы поместить соответствующие значения в «Словарь» и это было бы очень быстро. Тот факт, что у меня есть два элемента для фильтрации, делает его немного сложнее для меня.

Я «учусь на собственном опыте» и не претендую на звание программиста VBA, так что любая помощь очень ценится! 

Dim pvtTable As PivotTable Dim pvtField1, pvtField2 As PivotField Dim pvtItem1, pvtItem2 As PivotItem  Set ws = ActiveSheet Set pvtTable = ws.PivotTables("PTReport") Set pvtField1 = pvtTable.PivotFields("callNummer") Set pvtField2 = pvtTable.PivotFields("Destination")  Application.ScreenUpdating = False  For Each pvtItem1 In pvtField1.PivotItems If InStr(UCase(pvtItem1), "STORE") > 0 Then pvtItem1.Visible = True Else pvtItem1.Visible = False End If Next  For Each pvtItem2 In pvtField2.PivotItems If InStr(UCase(pvtItem2), "221") > 0 Then pvtItem2.Visible = True Else pvtItem2.Visible = False End If Next  Application.ScreenUpdating = True

Если я могу что-то уточнить или предоставить, более подробную информацию, пожалуйста, дайте мне знать.

1
При обработке большого количества данных в Excel рекомендуется отключить вычисления в начале макроса и включить их в конце. Это может ускорить макрос в 10 раз или более. LPChip 6 лет назад 0
Спасибо за ваш ответ! У меня есть код `Application.ScreenUpdating = False` в начале макроса (отредактировал его в OP - извините за путаницу), но, похоже, мне это не помогает. SeattleITguy 6 лет назад 0

2 ответа на вопрос

1
Scott

Я подозреваю, что проблема в 20-30K InStr(UCase())- это проблема. Я предлагаю вам определить пару вспомогательных столбцов. Например, если ваши данные «callNummer» и «Destination» находятся в столбцах  Aи  B, установите

  • Y1 → =IFERROR(SEARCH("store", A1), 0)
  • Z1 → =IFERROR(SEARCH("221", B1), 0)

а затем изменить код VBA для проверки и являются . Таким образом, проверка строки в строке выполняется всякий раз, когда изменяются данные «callNummer» и «Destination», и для подпрограммы VBA требуется гораздо меньше работы. И, конечно же, вы можете скрыть вспомогательные столбцы, когда все заработает.YnZn> 0

Я не уверен, как именно перевести ваши заявления в эту структуру.pvtItemN.Visible = …

Но даже если вы не можете заставить это работать, вы можете изменить InStr(UCase(pvtItem2), "221")тест на  InStr(pvtItem2, "221"). Если все, что вам нужно, это число, то нет смысла преобразовывать алфавитное содержимое ячейки в верхний регистр.

Спасибо Скотт, что сделал свое дело! Теперь фильтр работает за секунды, еще раз спасибо за то, что открыли мне глаза. Я спрятал лист, где данные, так что это сработало отлично! SeattleITguy 6 лет назад 0
1
Rich Holton

Перед первым циклом for ... next добавьте следующий код:

Dim saveCalc as xlCalculation  With Application .ScreenUpdating = False saveCalc = .Calculation .Calculation = xlCalculationManual End With

Затем, после второго цикла for ... next, добавьте следующий код:

With Application .ScreenUpdating = True .Calculation = saveCalc End With
Спасибо, Рич! Помогло сгладить и снять немного времени SeattleITguy 6 лет назад 0

Похожие вопросы