Поиск  Пользователи  Правила  Войти
Форумы "Планета Excel" » Вопросы по Microsoft Excel
Страницы: 1
RSS
Оптимизация времени выполнения цикла For
 
#1
08.02.2014 05:26:23
Доброго времени суток!
Одним из действий нужного мне макроса есть удаление всех значений цены, которые отличаются от моды более чем на 20%.
Цены расположены в произвольном кол-ве столбцов. Один столбец - один продукт. В данном случае 13 столбцов по 2000 строк.
Для этого написал такой цикл:
Код
?
1
2
3
4
5
For m = 26 To qcol
    For i = 2 To qrow
        If Sheets("Лист1").Cells(i, m).Value * 0.8 > Sheets("Лист2").Cells(2, m).Value Or Sheets("Лист1").Cells(i, m).Value * 1.2 < Sheets("Лист2").Cells(2, m).Value Then Sheets("Лист1").Cells(i, m).Value = ""
    Next i
Next m
qcol - переменная с номером последнего столбца
qrow - переменная с номером последней строки.
В ячейках на Лист2 записаны значения моды для этого продукта, разные для каждого столбца.

Цикл рабочий, однако очень долго выполняется.
Посоветуйте, пожалуйста, как можно добиться такого же результата, но быстрее. Прошу объяснять как для тупых, т.к. это мой первый макрос и с VBA я пока не сильно знаком.
Заранее спасибо.   :)
 
 
 
#2
08.02.2014 07:49:47
Может я конечно еще не проснулся, но мне кажется, что такой код наоборот удалит все цены, которые ближе 20% к значениям на втором листе.
А по поводу ускорения, я бы не делал условие через ИЛИ, потому как для этого будут расчитываться обе части формул, а только потом будет получаться результат ИЛИ. Лучше сделать первое условие (с 0,8 ), если результат какой-то, то сразу переходим к следующей ячейке, а вот если резулььтат другой, то тогда проверяем второе условие. И только тогда считается формула с 1,2.
Изменено: wowick - 08.02.2014 07:50:25
Если автоматизировать бардак, то получится автоматизированный бардак.
 
 
 
#3
08.02.2014 09:53:08
Попробуй так
Код
?
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
Sub testst()
Dim m As Long, i As Long, qcol As Long, qrow As Long, oCell As Range
Application.ScreenUpdating = False
 
m = 26
i = 2
qcol = ActiveSheet.UsedRange.Columns.Count
qrow = ActiveSheet.UsedRange.Rows.Count
 
For Each oCell In ActiveSheet.Range(Cells(i, m), Cells(qrow, qcol))
    If oCell.Value * 0.8 > Worksheets("Ëèñò2").Cells(oCell.Row, oCell.Column).Value _
        Or oCell.Value * 1.2 < Worksheets("Ëèñò2").Cells(oCell.Row, oCell.Column).Value Then
        oCell.Value = Empty    
    End If
Next
 
Application.ScreenUpdating = True
End Sub
 
 
 
#4
08.02.2014 13:07:02
Код
?
1
2
3
4
5
6
7
8
9
a=Sheets("Лист1").range(Sheets("Лист1").Cells(2, 26), Sheets("Лист1").Cells(qrow, qcol)).value
b=Sheets("Лист2").range(Sheets("Лист2").Cells(2, 26), Sheets("Лист2").Cells(2, qcol)).value
 
for m=1 to ubound(a,2)
  for i=1 to ubound(a)
    if a(i,m)*0.8 > b(1,m) or a(i,m)*1.2 < b(1,m) then a(i,m)=""
next i, m
 
Sheets("Лист1").cells(2,26).resize(ubound(a), ubound(a,2)).value=a
фрилансер Excel, VBA - контакты в профиле
"Совершенствоваться не обязательно. Выживание — дело добровольное." Э.Деминг
 
 
 
#5
08.02.2014 15:57:29
Спасибо всем огромное, за такие оперативные советы.

ikki, ваш способ оказался идеальным. Сократил время с 40 (!) мин до 3-4 секунд.
Было бы здорово, если б вы вкратце объяснили почему так получилось, а то понимания у меня нет.

Scripter, wowick, в связи с вышесказанным не вижу смысла оптимизировать логическое условие или отключать обновление экрана, это дает не такие впечатляющие результаты.

Я рад, что присоединился к такому замечательному сообществу.
Изменено: Tialas - 08.02.2014 15:58:29
 
 
 
#6
08.02.2014 17:14:36
Можно еще сократить время, если вообще избавиться от цикла :)
Хотя, если теперь расчет занимает несколько секунд, то неактуально.
 
 
 
#7
08.02.2014 17:26:54
Казанский, расскажите, пожалуйста, как, если не лениво.  :)
 
 
 
#8
08.02.2014 18:49:41
Цитата
Tialas пишет: вкратце объяснили почему так получилось
основное ускорение - за счет использования в коде локальных массивов для хранения и обработки значений ячеек.
работа с отдельными ячейками из VBA - процесс довольно медленный, что особенно заметно становится при использовании циклов на более-менее больших объемах.
в данном коде операции с ячейками сведены к загрузке диапазонов значений в массив и выгрузке результата из массива на лист.

тем не менее, это не отменяет любую другую оптимизацию.  :)
фрилансер Excel, VBA - контакты в профиле
"Совершенствоваться не обязательно. Выживание — дело добровольное." Э.Деминг
 
 
 
#9
09.02.2014 03:51:41
Цитата
Tialas пишет:
Я рад, что присоединился к такому замечательному сообществу.
Я тоже, тут недавно, выше сказанное поддерживаю
 
 
 
#10
09.02.2014 19:08:32
Tialas,
дело в том, что формулы листа в Excel работают быстрее процедур VBA. А в VBA можно сформировать формулу (текстовую строку) и получить ее результат с помощью функции Evaluate (можете поискать по форуму это слово, можно в сочетании с моим ником  ;)  )
В данном случае формула массива, которая вычисляет результат, выглядит так, при qrow = 18, qcol = 34:

Код
?
1
=ЕСЛИ(Лист1!$Z$2:$AH$18<Лист2!$Z$2:$AH$2*0,8;"";ЕСЛИ(Лист1!$Z$2:$AH$18>Лист2!$Z2:$AH$2*1,2;"";Лист1!$Z$2:$AH$18))


Формулу удобно отладить на отдельном листе, потом получить ее на "родном" языке в окне Immediate VBA
Код
?
1
2
?activecell.Formula
=IF(Лист1!$Z$2:$AH$18<Лист2!$Z$2:$AH$2*0.8,"",IF(Лист1!$Z$2:$AH$18>Лист2!$Z2:$AH$2*1.2,"",Лист1!$Z$2:$AH$18))


, потом перенести в код программы и подставить нужные адреса диапазонов. В результате получается:
Код
?
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
Sub bb()
Dim qrow, qcol, adrA, adrB, s
qrow = 18
qcol = 34
 
'адреса диапазонов с учетом стиля ссылок приложения (A1 или R1C1)
adrA = "Лист1!" & Range(Cells(2, 26), Cells(qrow, qcol)).Address(True, True, Application.ReferenceStyle)
adrB = "Лист2!" & Range(Cells(2, 26), Cells(2, qcol)).Address(True, True, Application.ReferenceStyle)
 
'формула с адресами диапазонов
s = "IF(" & adrA & "<" & adrB & "*0.8,"""",IF(" & adrA & ">" & adrB & "*1.2,""""," & adrA & "))"
 
'расчет формулы (значение - массив) и выгрузка на лист
Range(Sheets("Лист1").Cells(2, 26), Sheets("Лист1").Cells(qrow, qcol)).Value = Evaluate(s)
 
End Sub
 
 
 
#11
09.02.2014 19:38:53
офф. после таких постов особенно остро ощущаешь недостатки форума - ни в репу дать, ни тему оценить, ни пометить ответ как "лучший",..
фрилансер Excel, VBA - контакты в профиле
"Совершенствоваться не обязательно. Выживание — дело добровольное." Э.Деминг
 
 
 
#12
09.02.2014 19:42:27
http://joxi.ru/1KH3Uv3JTJB4eVpOElk
 
 
 
#13
10.02.2014 03:26:23
ikki, спасибо за объяснеие. Теперь буду сводить операции с ячейками к минимуму. Однако, оптимизации действительно нет предела. :-)

Казанский, спасибо за такой вариант. Открыл для себя функцию Evaluate. Уже использовал, но не для замены цикла.

Господа, может еще подскажете с таким вопросом.

В коде выше используется запись в ячейку пустого множества ("" или Empty). Однако, при дальнейшей работе с этой ячейкой она воспринимается формулой моды, например, как 0, вместо пустой ячейки. Мода пустые ячейки пропускает, а такие нет и выдает результат 0, если таких пустых много.
Вопрос, как сделать, чтобы ячейка была действительно пустой?

Как вариант Range.Clear так делает, однако я не знаю как применить Clear к ячейке, записанной в массив, например, как в коде ikki.


Заранее спасибо.
Изменено: Tialas - 10.02.2014 03:27:28
 
 
 
#14
10.02.2014 04:42:00
Вместо a(i, m) = "" напишите a(i, m) = Empty
 
 
 
Страницы: 1
Читают тему
Loading...