Как оптимизировать?

[А)-(дрей]

Я именно так и понял и в предыдущем посте писал, что пробовал выносить объявление циклов перед началом цикла - это не помогает. Щас вот опять попробовал - "0" везде

[Hugo121]

Я пока в код не вникал, но смотрю, что у Вас массивы вообще и не используются. Используется одно текущее значение сразу после занесения, ну и ещё одно соседнее:

M7(i) = Application.Max(Range(Cells(i + 1, 2), Cells(i + 1, 5)))
...
        M8(i) = IIf(M7(i) = M7(i + 1), 1, 0)
    Cells(i + 1, 102) = M8(i)

Так зачем вообще массивы? Вполне можно было так написать:

Cells(i + 1, 102) = IIf(Application.Max(Range(Cells(i + 1, 2), Cells(i + 1, 5))) = Application.Max(Range(Cells(i + 2, 2), Cells(i + 2, 5))),1, 0)

И кстати появились единички при таком коде (старое не стирал, просто отключил):

    For i = 1 To Data
    '======================================================================================================
'    Dim M6()
'    ReDim M6(1 To Data)
'    M6(i) = i
    Cells(i + 1, 6) = i
    Cells(i + 1, 100) = i 'M6(i)
  
'    Dim M7()
'    ReDim M7(1 To Data + 1)
'    M7(i) = Application.Max(Range(Cells(i + 1, 2), Cells(i + 1, 5)))
    Cells(i + 1, 7) = "=MAX(RC[-5]:RC[-2])"
'    Cells(i + 1, 101) = M7(i)
    Cells(i + 1, 101) = Application.Max(Range(Cells(i + 1, 2), Cells(i + 1, 5)))
    
'    Dim M8()
'    ReDim M8(1 To Data)
    Cells(i + 1, 8) = "=IF(RC[-1]=R[1]C[-1],1,0)"
'    M8(i) = IIf(M7(i) = M7(i + 1), 1, 0)
'    Cells(i + 1, 102) = M8(i)
    
    Cells(i + 1, 102) = IIf(Application.Max(Range(Cells(i + 1, 2), Cells(i + 1, 5))) = Application.Max(Range(Cells(i + 2, 2), Cells(i + 2, 5))), 1, 0)

Как видите, массивы отключены полностью.
Вообще конечно на массивах было бы быстрее и лучше, но это нужно переделывать алгоритм - заносить сразу всё анализируемое в массивы, анализировать в памяти и класть результат в другой массив, потом этот массив выгрузить на лист.
Расскажите словами, что нужно из чего получить - вычитывать это из кода лениво...

[Hugo121]

Вот например так - получаем три последних столбца.
Для сравнения выгружаю в [cz2 : db2].
В цикле сравнение с last из-за того, что иначе ошибка в конце цикла, когда значение i + 1 выходит за предел массива.
Код отрабатывает за 0,06 секунды.

Sub AnalizMassiv()
'Dim tm: tm = Timer
    Dim a, b, iLastRow As Long, i As Long, last As Long
    Dim x As Double, y As Double

    With Лист1
        iLastRow = .Cells(.Rows.Count, 2).End(xlUp).Row
        a = .Range("a2:e" & iLastRow).Value

        ReDim b(1 To UBound(a), 1 To 3)

        last = UBound(a)
        For i = 1 To last
            x = Application.Max(a(i, 2), a(i, 3), a(i, 4), a(i, 5))
            b(i, 1) = i
            b(i, 2) = x
            If i < last Then
                y = Application.Max(a(i + 1, 2), a(i + 1, 3), a(i + 1, 4), a(i + 1, 5))
                If x = y Then b(i, 3) = 1
            End If
        Next

        .[cz2:db2].Resize(last) = b
    End With
    'Debug.Print (Timer - tm) * 1000
End Sub

[А)-(дрей]

Спасибо за ответ, щас попробую объяснить - на первом этапе расчет листа проиведен формулами (без использования vba), на втором этапе (когда я начал изучать vba) был написан данный код, который не раздувает файл данными и расчитывает именно столько строк - сколько нужно, но опять не задача - это расчет не внутри кода, а на рабочих листах, поэтому собственно и понадобилась оптимизация уже ранее написанного, а это и есть третий этап. Хорошо конечно когда ты наделен сразу определенными знаниями и можно обойтись без первых двух этапов - но не в моем "пока что" случае.
Весь этот пост и затеян для того, чтобы понять суть расчетов формулами массивов и отобраченнии действительных значений результатов вычеслений по окончании кода на рабочий лист.
Вы не много не правы по поводу не использовании мной массивов - на данном этапе "да", этого пока не сильно то и заметно. Все дело в том, что сами поймите, тяжело переделывать то, что изначально написанно не так - поэтому стратегия моей работы над переделкой этого кода следующая: хотелось бы понять, как правильно программно и синтаксически оформить значения каждого столбца расчитанного через цикл в значения массивов (для того чтобы не запутаться на этом этапе - решил давать таким массивам индекс равный номеру столбца, рассчитанного формулой через цикл: например значения Cells(i + 1, 6) должны соответствовать массиву M6, Cells(i + 1, 7) массиву M7 и т.д.). Далее что касается в коде срок типа Cells(i + 1, 100) = M6(i) - это временные строки, делаю пока их лишь для того, чтобы на лесте визуально сравнивать значения массивов со значениями формул с циклами (опять же, только ради того, чтобы не запутаться), после переделки кода - такие строки сразу же уберу. Ну и следовательно сами строки цикла формул пока не убираю - это чтобы было пока с чем работать, когда увижу, что созданные циклы им идентичны - строки типа Cells(i + 1, 7) = "=MAX(RC[-5]:RC[-2])" - с кода так же будут удалены.
Вот теперь далее, получив массивы М6 и М7 (которые образуются от работы со значениями в ячеках), следующий массив М8 уже призван работать со значениями массива М7. И так далее, пока не перелопачу все нужные массивы.
Работа конечно предстоит муторная и от вас я только хочу в нужный момент помочь мне с синтаксисами или полезным замечанием.
Я старался объяснить, как мог.

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

[Hugo121]

Примерно понятно.
Непонятно только, что именно нужно получить? Если все те данные, которые высчитаны формулами - тогда я пас, такую рутину прописывать не буду.
Но если Вы поймёте, как получен массив b с единичками в моём коде - тогда Вы сами можете создать ещё сколько нужно массивов, которые заполните нужными данными на основе анализа массива а, и которые в конце можно аналогично выгрузить на лист.
Или, если нужно например подсчитать общее количество единичек, или получить список моментов, где стоят эти единички - тогда и это можно сделать внутри кода, и выгрузить на лист (или например в файл отчёта) уже только именно нужные данные.

[А)-(дрей]

А получить нужно столбец данных с названием X1 (не путать с адресом ячейки), подсвеченный красным цветом. Да понятно что никто этим не будет заниматься, хотя мне в принципе не трудно кинуть клич "накидать код за wmr" - а результат то какой? кодов то мне еще много писать, все покупать? нет, я хочу разобраться... Вот не помню, у кого подпись такую видел - что то вроде "дайте человеку рыбу и накормите его на день, научите его ловить рыбу - и на кормите на всю жизнь" -правильные слова, я и хочу "научиться ловить рыбу".
То что вы пытались до меня довести постом выше я понимаю и где когда внутри, а где выгружать тоже догадываюсь... просто спотыкаюсь пока еще, как ребенок.
Спасибо вам за то, что возитесь со мной

[Hugo121]

Да, запутанно там всё переплетается, это вероятно только Вы сами можете правильно распутать...
Вот смотрите - Вы получили массив "a" с данными, которые построчно перебираете в цикле.
Взяли первую строку (a(i, 2), a(i, 3), a(i, 4), a(i, 5)) и начинайте через If..Then получать искомое.
Можно временно заносить промежуточные результаты в переменные, их сравнивать, потом их обнулять перед следующим шагом цикла.
Значения массива a(i-1,n) и a(i+1,n) - это строка выше и ниже, только нужно предусмотреть, что бы не вылезти за пределы массива в начале и конце, иначе будет ошибка. Или может просто изначально массив взять чуть больше.
Мне кажется, в макросе всё это прописать даже проще, чем формулами, т.к. можно пошагово всё отследить, прокомментировать для себя каждую строку, добавить ещё переменных и ветвлений, если мало простора для мысли

[А)-(дрей]

Вроде как разобрался, расчет запихал внутрь кода - скорость возросла многократно. Во вложении выкладываю что получилось. У меня только один вопрос остался, в коде есть два момента:

Dim M14()
    ReDim M14(1 To Data)
    For i = 1 To Data
    On Error Resume Next
    'Cells(i + 1, 14) = "=IF(RC[-2]+RC[-1]=2,1,IF(R[1]C=1,RC[-2],0))"
    M14(i) = IIf(M12(i) + M13(i) = 2, 1, IIf(M14(i + 1) = 1, M12(i), 0))
    Next
    'Повторные пересчеты массивов, т.к. значения M14(i + 1)еще не расчитаны
    For i = 1 To Data
    On Error Resume Next ' обход ошибки расчета диапазонов массивов
    M14(i) = IIf(M12(i) + M13(i) = 2, 1, IIf(M14(i + 1) = 1, M12(i), 0))
    Next
    For i = 1 To Data
    On Error Resume Next ' обход ошибки расчета диапазонов массивов
    M14(i) = IIf(M12(i) + M13(i) = 2, 1, IIf(M14(i + 1) = 1, M12(i), 0))
    'задаем последнему элементу массива расчетное значение минуя ошибку
    M14(Data) = IIf(M12(i) + M13(i) = 2, 1, 0)
    'Cells(i + 1, 108) = M14(i)
    Next

и еще один

Dim M44()
    ReDim M44(1 To Data)
    M44(Data) = 0
    For i = 1 To Data
    'Cells(i + 1, 44) = "=IF(RC[-4]=0,R[1]C,RC[-5])"
    M44(i) = IIf(M40(i) = 0, M44(i + 1), M39(i))
    Next
    For i = 1 To Data
    M44(i) = IIf(M40(i) = 0, M44(i + 1), M39(i))
    Next
    For i = 1 To Data
    M44(i) = IIf(M40(i) = 0, M44(i + 1), M39(i))
    Next
    For i = 1 To Data
    M44(i) = IIf(M40(i) = 0, M44(i + 1), M39(i))
    Next
    For i = 1 To Data
    M44(i) = IIf(M40(i) = 0, M44(i + 1), M39(i))
    Next
    For i = 1 To Data
    M44(i) = IIf(M40(i) = 0, M44(i + 1), M39(i))
    Next
    For i = 1 To Data
    M44(i) = IIf(M40(i) = 0, M44(i + 1), M39(i))
    Next
    For i = 1 To Data
    M44(i) = IIf(M40(i) = 0, M44(i + 1), M39(i))
    Next
    For i = 1 To Data
    M44(i) = IIf(M40(i) = 0, M44(i + 1), M39(i))
    Next
    For i = 1 To Data
    M44(i) = IIf(M40(i) = 0, M44(i + 1), M39(i))
    'Cells(i + 1, 138) = M44(i)
    Next

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

[Hugo121]

Я сейчас тоже детально не въеду (поздно уже), но вот тут:

    For i = 1 To Data
    On Error Resume Next
    'Cells(i + 1, 14) = "=IF(RC[-2]+RC[-1]=2,1,IF(R[1]C=1,RC[-2],0))"
    M14(i) = IIf(M12(i) + M13(i) = 2, 1, IIf(M14(i + 1) = 1, M12(i), 0))
    Next

Можно ведь идти снизу:

    Dim M14()
    ReDim M14(1 To Data)
    
    For i = Data To 2 Step -1
       'Cells(i + 1, 14) = "=IF(RC[-2]+RC[-1]=2,1,IF(R[1]C=1,RC[-2],0))"
    M14(i - 1) = IIf(M12(i - 1) + M13(i - 1) = 2, 1, IIf(M14(i) = 1, M12(i - 1), 0))
    Next

И всё уже рассчитано, и нет ошибки. И с М44() и М48() так же.
Если идти до 1, то будет ошибка в M14(i - 1), M12(i - 1), M13(i - 1) - нет таких элементов. Поэтому я в том своём коде условие вводил и при 1 это действие не делал. Ну тут можно по разному поступить, поизучайте, как лучше.
И кстати On Error Resume Next не стоит 100 раз задавать, раз Вы не сбрасываете On Error GoTo 0. Хватит одного раза в начале.

[А)-(дрей]

А я вот не знал как идти снизу тут бы это очень пригодилось - я попробую, попозже, уже поздно сегодня. Кстати про On Error Resume Next я тоже запомнил. Спасибо.