VBA - Эффективен для каждого цикла для больших диапазонов

frosty4321

1 июля 2021 в 16:44

Я новичок в VBA и пытаюсь понять, как использовать цикл for each в большом диапазоне (500 КБ).

Я хочу рассчитать несколько сценариев в таблице в зависимости от вероятности, которая имеет следующий формат:

Group Type   | Group 1 | Group 2 | Group 3.

Probability  | 20%     | 30%     | 70%

Scenario 1   | 0       | 1       | 0 

Scenario 2   | 0       | 0       | 1   
....

Scenario 500k| 0       | 1       | 1

К сожалению, моя петля For each работает только в небольшом диапазоне до 10k - кто-нибудь из вас знает, как я могу использовать ее лучше?

Sub ScenarioCalculation()
 
Dim propability As Double, random As Double, row As Long, col As Long

Application.ScreenUpdating = False

For col = 4 To 23
    For row = 25 To 100
        propability = Cells(12, col + 1).Value
        random = 0# + Rnd * 1#
        If random < propability Then
            Cells(row + 1, col + 1).Value = 1
            Else
            Cells(row + 1, col + 1).Value = 0
        End If
    Next row
Next col
 
End Sub

Источник

Поделиться Скопировать ссылку Скопировано

m0skit0

1 июля 2021 в 16:46

Не используйте Excel для более чем 10 тыс. строк, это не база данных.

Wizhi

1 июля 2021 в 17:14

Не для каждого используемого вами цикла и почему значения цикла ограничены строкой 25 To 100? Я не очень понимаю вашу настоящую проблему (медленный ли код), то есть что не работает с кодом?

Naresh

1 июля 2021 в 17:44

Кажется, вы случайным образом генерируете 0 и 1 в заданном диапазоне. Если это так, вы можете использовать формулу Excel =RANDBETWEEN(0,1), которая случайным образом даст вам ноль или единицу, а затем скопируете ее в диапазоне, или вы можете ввести формулу с контролем + ввод во всем выбранном диапазоне сразу. Я думаю, что это будет намного быстрее, чем перебирать каждую ячейку в vba. Да, но он будет изменчивым, поэтому вам придется копировать его как значения.

Tim Williams

1 июля 2021 в 17:48

@Naresh - 1 или 0 зависит от значения ячейки в строке 12

Naresh

1 июля 2021 в 17:54

@TimWilliams .. Я не уверен. Но я думаю, что если мы сравниваем два значения; один известный и другой случайный; тогда также вероятность того, что одно больше или меньше другого, должна быть 50%, как 0 или 1. Если оба значения неизвестны, то это 50%.. Я думаю :)

Naresh

1 июля 2021 в 18:10

@TimWilliams после сравнения с вашим ответом .. да, вы правы. 0,6 (или 60%) всегда имеют высокую вероятность согласно вашему ответу. Спасибо.

Ответы (2)

Tim Williams

1 июля 2021 в 17:41

Операции по ячейкам выполняются сравнительно медленно. Вы можете использовать массивы для повышения производительности.

Это должно быть быстрее:

Sub ScenarioCalculation()
    
    Const NUM_SCENARIOS As Long = 10000 ' for example
    Dim propability As Double, random As Double, row As Long, col As Long, arr
    Dim rng As Range, ws As Worksheet
    
    Set ws = ActiveSheet 'for example
    
    Application.ScreenUpdating = False
    
    For col = 4 To 23
        propability = ws.Cells(12, col + 1).Value   'only need to read this once...
        Set rng = ws.Cells(25, col + 1).Resize(NUM_SCENARIOS)
        arr = rng.Value                             'create array from range
        'populate the array
        For row = 1 To UBound(arr, 1)
            random = 0# + Rnd * 1#
            arr(row, 1) = IIf(random < propability, 1, 0)
        Next row
        
        rng.Value = arr 'populate the range from the array
    Next col
 
End Sub

Поделиться Скопировать ссылку Скопировано

Johanness

1 июля 2021 в 18:03

Это намного быстрее, чем мое решение, и оно также работает для большого количества строк. (только что проверил: 1 с против 50 с на 100 тыс.). Узнал что-то новое

Naresh

1 июля 2021 в 18:06

Работает намного быстрее даже для 500 тыс. строк

Johanness

1 июля 2021 в 17:29

создайте длинную переменную и используйте диапазоны:

Dim i As Long
Dim rng As Range
Dim probability as variant
Dim col as integer

Set rng = Range("B2")


For col = 0 to 20
  probability=rng.offset(0,1).value
  For i = 1 To 500000
    random = 0# + Rnd * 1#
    rng.offset(0,2 + col).value = iif(random<probability,1,0)
    Set rng = rng.Offset(1, 0)
  Next
Next

Таким образом вы используете внутреннюю способность Excel использовать большое количество ячеек и работать с каждой строкой отдельно. И, как показал @Tim Williams: если вы заключите это в

On Error Goto EndThisSub
  Application.ScreenUpdating = False
  ....
EndThisSub:
  Application.ScreenUpdating = True

это будет намного быстрее.

Поделиться Скопировать ссылку Скопировано