Поиск  Пользователи  Правила  Войти
Форумы "Планета Excel" » Вопросы по Microsoft Excel (архив)
Страницы: 1 2 След.
RSS
Фоновое выполнение макроса эксель: возможно ли такое?
 
#1
25.05.2011 22:16:00
Добрый день.  
 
Ситуация:  
Нужно перебрать 5 в 10 степени вариантов (около 10 миллионов). На каждый вариант уходит 2 секунды. То есть на всё про всё уйдет более 200 дней круглосуточной работы компа.  
 
Насколько я смог додуматься, так это запустить несколько копий эксель и запустить в каждой по макросу. Но всё равно сильно это не спасет (ну, 4 копии эксель сократят время до 50 дней...)  
 
Вопросы такие:  
Возможно ли фоновое выполнение макроса?  
Если возможно - ускорит ли это его работу существенно?  
 
С уважением,  
 
Антон.
 
 
 
#2
25.05.2011 22:23:51
"Варианты" на листе? Обрабатывайте их в массиве.
 
 
 
#3
25.05.2011 22:32:52
Оптимизировал как мог.  
 
Вот он макрос:  
 
Sub Calculating()  
 
'  
' Calculating Макрос  
'  
 
'  
   Application.ScreenUpdating = False  
   Application.Calculation = xlCalculationManual  
     
   Dim var1, var2, var3, var4, var5, var6, var7, var8, var9, var10, counter, i As Integer  
     
   counter = 0  
     
   For var1 = 2 To 6 Step 1  
   For var2 = 2 To 6 Step 1  
   For var3 = 2 To 6 Step 1  
   For var4 = 2 To 6 Step 1  
   For var5 = 2 To 6 Step 1  
   For var6 = 2 To 6 Step 1  
   For var7 = 2 To 6 Step 1  
   For var8 = 2 To 6 Step 1  
   For var9 = 2 To 6 Step 1  
   For var10 = 2 To 6 Step 1  
     
   
   Windows("VALUES").Activate  
   Worksheets("Лист1").Activate  
   Range("DS2").Select  
     
   Selection.FormulaR1C1 = var1  
   ActiveCell.Offset(rowoffset:=0, columnoffset:=1).Activate  
   Selection.FormulaR1C1 = var2  
   ActiveCell.Offset(rowoffset:=0, columnoffset:=1).Activate  
   Selection.FormulaR1C1 = var3  
   ActiveCell.Offset(rowoffset:=0, columnoffset:=1).Activate  
   Selection.FormulaR1C1 = var4  
   ActiveCell.Offset(rowoffset:=0, columnoffset:=1).Activate  
   Selection.FormulaR1C1 = var5  
   ActiveCell.Offset(rowoffset:=0, columnoffset:=1).Activate  
   Selection.FormulaR1C1 = var6  
   ActiveCell.Offset(rowoffset:=0, columnoffset:=1).Activate  
   Selection.FormulaR1C1 = var7  
   ActiveCell.Offset(rowoffset:=0, columnoffset:=1).Activate  
   Selection.FormulaR1C1 = var8  
   ActiveCell.Offset(rowoffset:=0, columnoffset:=1).Activate  
   Selection.FormulaR1C1 = var9  
   ActiveCell.Offset(rowoffset:=0, columnoffset:=1).Activate  
   Selection.FormulaR1C1 = var10  
 
' и вот тут уходит наверное полсекунды-секунда (большой объем пересчитывает каждый круг):  
   Application.Calculate  
 
 
   Windows("_1001_").Activate  
   Worksheets("Лист1").Activate  
 
 
' и на этом цикле уходит время около секунды, вот и получается две секунды:  
   For i = -20 To 20 Step 1  
   Range("P17").Select  
   ActiveCell.FormulaR1C1 = i  
 
   Worksheets("Лист1").UsedRange.Columns("N:X").Calculate  
 
   Range("U261").Select  
   Selection.Copy  
   Range("X23").Select  
   ActiveCell.Offset(rowoffset:=i + 20, columnoffset:=0).Activate  
   Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _  
       :=False, Transpose:=False  
   Application.CutCopyMode = False  
     
   Next i  
     
   Worksheets("Лист1").Calculate  
   Range("X21:X22").Select  
   Selection.Copy  
   Worksheets("Sorted").Activate  
   Range("L1").Select  
   ActiveCell.Offset(rowoffset:=counter, columnoffset:=0).Activate  
   Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _  
       :=False, Transpose:=True  
   Application.CutCopyMode = False  
     
   Range("A1").Select  
   ActiveCell.Offset(rowoffset:=counter, columnoffset:=0).Activate  
     
   Selection.FormulaR1C1 = var1  
   ActiveCell.Offset(rowoffset:=0, columnoffset:=1).Activate  
   Selection.FormulaR1C1 = var2  
   ActiveCell.Offset(rowoffset:=0, columnoffset:=1).Activate  
   Selection.FormulaR1C1 = var3  
   ActiveCell.Offset(rowoffset:=0, columnoffset:=1).Activate  
   Selection.FormulaR1C1 = var4  
   ActiveCell.Offset(rowoffset:=0, columnoffset:=1).Activate  
   Selection.FormulaR1C1 = var5  
   ActiveCell.Offset(rowoffset:=0, columnoffset:=1).Activate  
   Selection.FormulaR1C1 = var6  
   ActiveCell.Offset(rowoffset:=0, columnoffset:=1).Activate  
   Selection.FormulaR1C1 = var7  
   ActiveCell.Offset(rowoffset:=0, columnoffset:=1).Activate  
   Selection.FormulaR1C1 = var8  
   ActiveCell.Offset(rowoffset:=0, columnoffset:=1).Activate  
   Selection.FormulaR1C1 = var9  
   ActiveCell.Offset(rowoffset:=0, columnoffset:=1).Activate  
   Selection.FormulaR1C1 = var10  
 
   Range("O1").Select  
   ActiveCell.Offset(rowoffset:=counter, columnoffset:=0).Activate  
   Selection.FormulaR1C1 = MyTime  
 
   counter = counter + 1  
     
     
   Next var10  
   Next var9  
   Next var8  
   Next var7  
   Next var6  
   Next var5  
   Next var4  
   Next var3  
   Next var2  
   Next var1  
     
     
Application.Calculate  
Application.Calculation = xlCalculationAutomatic  
Application.ScreenUpdating = True  
     
 
End Sub
 
 
 
#4
25.05.2011 22:43:16
извините, а вчерашний Hard и Вы - не одно и то же лицо?
фрилансер Excel, VBA - контакты в профиле
"Совершенствоваться не обязательно. Выживание — дело добровольное." Э.Деминг
 
 
 
#5
25.05.2011 22:44:04
Нет, а почему спрашиваете? Аналогичный вопрос был?
 
 
 
#6
25.05.2011 22:45:32
Глянув на все это, могу сказать только  
"Неправильно ты, дядя Федор, бутерброд ешь!"(С)  
Но и по макросу понять, как есть правильно, весма сложно!
 
 
 
#7
25.05.2011 22:49:09
Гм. Может разъяснения помогут:  
 
Файл VALUES содержит десятки тысяч строк, умножаемые на собственно 10 переменных каждый круг. Соответственно создается один столбец с итоговыми значениями.  
 
Эти значения использует второй файл, в котором присутствует цикл от -20 до 20.  
После прохождения цикла он записывает эти значения.  
 
И всё заново.....
 
 
 
#8
25.05.2011 22:51:11
во-первых, могу страшно обрадовать: 200 дней Вам ждать не придётся.  
на листе Excel просто нету 10 миллионов строк.  
во-вторых, множество команд Activate и Select тоже притормаживают работу. к ячейкам можно обращаться без их выделения.  
и, наверное, самое главное - логику вычислений на листе, ВОЗМОЖНО, будет оптимальнее реализовать в самом макросе.  
 
но... всё равно...  
а вам реально нужно столько строк результатов?  
прямо любопытство распирает - что это за расчеты такие...
фрилансер Excel, VBA - контакты в профиле
"Совершенствоваться не обязательно. Выживание — дело добровольное." Э.Деминг
 
 
 
#9
25.05.2011 22:52:03
Есть мнение, что лучше всего поможет  
http://www.planetaexcel.ru/forum.php?thread_id=8735-:)
 
 
 
#10
25.05.2011 22:58:56
{quote}{login=ikki}{date=25.05.2011 10:51}{thema=}{post}во-первых, могу страшно обрадовать: 200 дней Вам ждать не придётся.  
на листе Excel просто нету 10 миллионов строк.  
во-вторых, множество команд Activate и Select тоже притормаживают работу. к ячейкам можно обращаться без их выделения.  
и, наверное, самое главное - логику вычислений на листе, ВОЗМОЖНО, будет оптимальнее реализовать в самом макросе.  
 
но... всё равно...  
а вам реально нужно столько строк результатов?  
прямо любопытство распирает - что это за расчеты такие...{/post}{/quote}  
 
Ну, миллион-то строк (степень(2;20)) есть. :)  
Насчет Activate и Select - посмотрю, может хоть как-то ускорит.  
Насчет "логики вычислений" что-то не совсем понял... Имелось ввиду перенести часть вычисления, которые обрабатываются в книге, в макрос?  
 
Столько результатов - вообще можно (нужно) и больше, но тысячелетие я ждать не готов. )))
 
 
 
#11
25.05.2011 23:00:24
{quote}{login=RAN}{date=25.05.2011 10:52}{thema=}{post}Есть мнение, что лучше всего поможет  
http://www.planetaexcel.ru/forum.php?thread_id=8735-:){/post}{/quote}  
 
Уже читал, вашей иронии не понял.
 
 
 
#12
25.05.2011 23:10:01
Я так понял выход один - использовать массивы... Я о них сразу не подумал.  
 
Спасибо всем за помощь!
 
 
 
#13
25.05.2011 23:13:08
{quote}{login=Антон}{date=25.05.2011 10:58}{thema=Re: }{post}  
Столько результатов - вообще можно (нужно) и больше, но тысячелетие я ждать не готов. ))){/post}{/quote}  
да я ж Вам верю!!!  
мне чисто любопытно - зачем?  
тем более - с учетом того, что, по Вашим словам, "десятки тысяч строк" умножаются каждый раз на различные комбинации значений 10 переменных...  
 
для чего нужен перечень результатов вычислений для всех комбинаций, если  
1) правила расчета известны;  
2) "десятки тысяч" есть;  
3) соответсвенно, для любой комбинации значений можно за 2 секунды посчитать результат  
- мне вот это интересно :)  
 
впрочем, секрет - значит секрет.  
уважаю.
фрилансер Excel, VBA - контакты в профиле
"Совершенствоваться не обязательно. Выживание — дело добровольное." Э.Деминг
 
 
 
#14
25.05.2011 23:14:19
Очень похоже, что всё можно провернуть в массиве в памяти. По времени не знаю, но точно минимум в 40 раз быстрее. Думаю даже раз в 100 быстрее должно быть.  
Но алгоритм из кода трудно уловить...
 
 
 
#15
25.05.2011 23:18:11
{quote}{login=ikki}{date=25.05.2011 11:13}{thema=Re: Re: }  
да я ж Вам верю!!!  
мне чисто любопытно - зачем?  
тем более - с учетом того, что, по Вашим словам, "десятки тысяч строк" умножаются каждый раз на различные комбинации значений 10 переменных...  
 
для чего нужен перечень результатов вычислений для всех комбинаций, если  
1) правила расчета известны;  
2) "десятки тысяч" есть;  
3) соответсвенно, для любой комбинации значений можно за 2 секунды посчитать результат  
- мне вот это интересно :)  
 
впрочем, секрет - значит секрет.  
уважаю.{/post}{/quote}  
 
Да, всё верно: "десятки тысяч строк" умножаются каждый раз на различные комбинации значений 10 переменных...    
 
Что следует из пункта 1) и 2) - не понял. То, что можно посчитать результат? - Так вот на этот-то один цикл расчета результат и записи его в ячейку и уходит две секунды.  
 
Благодарю за уважение. :)
 
 
 
#16
25.05.2011 23:19:55
{quote}{login=Hugo}{date=25.05.2011 11:14}{thema=}{post}Очень похоже, что всё можно провернуть в массиве в памяти. По времени не знаю, но точно минимум в 40 раз быстрее. Думаю даже раз в 100 быстрее должно быть.  
Но алгоритм из кода трудно уловить...{/post}{/quote}  
 
Вот это (провернуть в массиве памяти), наверное, я и имел ввиду, говоря о фоновом режиме. Я тоже догадываюсь, что можно эти все расчеты провести быстрее, без самого модуля программы эксель, то есть где-нибудь в памяти. И это будет гораздо быстрее. Но как это сделать я не представляю.
 
 
 
#17
25.05.2011 23:26:41
давайте я ещё проще объясню (пока другие форумчане пыхтят и готовят вам реактивный макрос)...  
 
вот мне периодически нужно умножать пять разных целых чисел друг на друга.  
каких - я заранее не знаю.  
но, скажем, каждое - от 1 до 100.  
 
я могу поступить так: заранее составить таблицу результатов умножения всех возможных комбинаций чисел и сохранить её. можно даже напечатать.  
и каждый раз, когда я буду знать, какие числа мне надо перемножить, я открою таблицу - и найду результат. и считать не надо.  
 
а можно - просто знать, КАК перемножать числа и иметь инструмент для их перемножения.  
и справочник будет не нужен.  
 
насколько я пока понял, аналогия с Вашей задачей есть, возможно, и неполная :)
фрилансер Excel, VBA - контакты в профиле
"Совершенствоваться не обязательно. Выживание — дело добровольное." Э.Деминг
 
 
 
#18
25.05.2011 23:33:23
{quote}{login=ikki}{date=25.05.2011 11:26}{thema=}{post}  
 
вот мне периодически нужно умножать пять разных целых чисел друг на друга.  
каких - я заранее не знаю.  
{/post}{/quote}  
 
Видимо, моя ошибка, я не уточнил. Мне периодически не нужно ничего умножать. Задача состоит в том, чтобы один раз перемножить (перебрать) все возможные варианты и выявить наиболее оптимальный результат, который будет соответствовать сочетанию именно тех десяти переменных...  
 
Если вы все так будете любезны завтра помочь, как и сегодня, я завтра прикреплю эти два файла (в усеченном варианте), и может тогда станет еще яснее... :)
 
 
 
#19
25.05.2011 23:37:21
{quote}{login=}{date=25.05.2011 11:33}{thema=Re: }{post}  
Если вы все так будете любезны завтра помочь, как и сегодня, я завтра прикреплю эти два файла (в усеченном варианте), и может тогда станет еще яснее... :){/post}{/quote}  
договорились :)  
 
пс. а надстройка "поиск решения" Вам помочь не может, да?..
фрилансер Excel, VBA - контакты в профиле
"Совершенствоваться не обязательно. Выживание — дело добровольное." Э.Деминг
 
 
 
#20
25.05.2011 23:39:10
{quote}{login=ikki}{date=25.05.2011 11:37}{thema=Re: Re: }{post}  
 
пс. а надстройка "поиск решения" Вам помочь не может, да?..{/post}{/quote}  
 
Извиняюсь, но такое она не потянет... :)
 
 
 
#21
26.05.2011 18:57:34
Добрые люди, а в особенности ikki. :)  
 
Подготовил я макрос (внутри файла) и сами файлы в крайне усеченном виде, но работающие по тому же принципу. Посмотрите, пожалуйста, что можно сделать, как ускорить процесс пересчета.  
 
Напомню, суть проблемы:  
В файле VALUES значений крайне много, десятки тысяч строк, каждый круг макроса эксель пересчитывает всё заново, плюс цикл от -10 до 10 во втором файле (_1001_) и в итоге на один круг работы макроса уходит 2 секунды. Как ускорить?
 
 
 
#22
26.05.2011 19:07:00
упс :)  
а у меня ведь Ex 2002...
фрилансер Excel, VBA - контакты в профиле
"Совершенствоваться не обязательно. Выживание — дело добровольное." Э.Деминг
 
 
 
#23
26.05.2011 19:39:48
точнее - файлы-то я, с помощью ковертера (в который раз спасибо Hugo!), открыл.  
но вот макрос ругается.  
ошибки компиляции.  
попробую разобраться, конечно...
фрилансер Excel, VBA - контакты в профиле
"Совершенствоваться не обязательно. Выживание — дело добровольное." Э.Деминг
 
 
 
#24
26.05.2011 20:08:35
В первом (очень первом :)) приближении:  
Sub Calculating()  
'Application.ScreenUpdating = False  
'Application.Calculation = xlCalculationManual  
Dim var1&, var2&, var3&, var4&, var5&, var6&, var7&, var8&, var9&, var10&, counter&, i&  
Dim t()  
With Workbooks("_1001_.xlsx")  
   For var1 = 2 To 6  
       For var2 = 2 To 6  
           For var3 = 2 To 6  
               For var4 = 2 To 6  
                   For var5 = 2 To 6  
                       For var6 = 2 To 6  
                           For var7 = 2 To 6  
                               For var8 = 2 To 6  
                                   For var9 = 2 To 6  
                                       For var10 = 2 To 6  
                                           t = Array(var1, var2, var3, var4, var5, var6, var7, var8, var9, var10)  
                                           [m3:v3].Value = t
                                           With .Sheets("Лист1")  
                                               For i = -10 To 10  
                                                   .[b2] = i
                                                   .[k5].Offset(i + 10).Value = .[h21].Value
                                               Next i  
                                           End With  
                                           .Sheets("Sorted").[l1].Offset(counter).Value = .Sheets("Лист1").[k3].Value
                                           .Sheets("Sorted").[m1].Offset(counter).Value = .Sheets("Лист1").[k4].Value
                                           .Sheets("Sorted").[a1:j1].Offset(counter).Value = t
                                           counter = counter + 1  
                                           If counter > 1000 Then MsgBox ">1000 - Exit Sub": Exit Sub  
                                       Next var10  
                                   Next var9  
                               Next var8  
                           Next var7  
                       Next var6  
                   Next var5  
               Next var4  
           Next var3  
       Next var2  
   Next var1  
End With  
'Application.Calculation = xlCalculationAutomatic  
'Application.ScreenUpdating = True  
End Sub  
 
Вот здесь: .[k5].Offset(i + 10).Value = .[h21].Value
почему только h21 используем? М.б. только ее и надо считать?  
Кол-во итераций ограничил 1000 для примера.
 
 
 
#25
26.05.2011 20:11:24
... и надо ли записывать все миллионы комбинаций? Конечная цель в чем?
 
 
 
#26
26.05.2011 23:08:56
пока вот так.  
формулы практически не менял, убрал только те расчеты, которые вообще не зависят от изменяемых переменных.  
 
в макросе - убрал активацию-выделение книг, листов и ячеек, заменил Copy и Paste на присваивание значений.  
подкорректировал объявление переменных.  
 
добавил строчку Array(...) - внаглую спёр приём у nilem'а :)  
 
в итоге на данных примера на небольшом кол-ве циклов (3125) получился выигрыш по времени примерно в 15-16 раз.  
думаю, что на "десятках тысяч строк" такого и близко не будет :(  
попробуйте?  
 
честно говоря, поднадоело :(  
возможно, завтра, с новыми силами, попробую загнать ячейки в массивы и считать всё там.  
 
пс. в 2003-м Excel'e не работало Windows(...).Activate, остальное всё то же самое.
фрилансер Excel, VBA - контакты в профиле
"Совершенствоваться не обязательно. Выживание — дело добровольное." Э.Деминг
 
 
 
#27
26.05.2011 23:27:27
{quote}{login=ikki}{date=26.05.2011 11:08}{thema=}{post}пс. в 2003-м Excel'e не работало Windows(...).Activate, остальное всё то же самое.{/post}{/quote}  
Скорее всего открыты в разных приложениях.
 
 
 
#28
26.05.2011 23:34:09
Юрий, да нет вроде,.. заменил на Workbooks - заработало.  
глубже копать не стал.  
не вижу никакого смысла в коллекции Windows, если у каждой книги только одно окно.
фрилансер Excel, VBA - контакты в профиле
"Совершенствоваться не обязательно. Выживание — дело добровольное." Э.Деминг
 
 
 
#29
26.05.2011 23:38:37
Я среагировал на "в 2003-м Excel'e не работало...". Просто сталкивался с этим - из другого приложения не видит :-) А вообще работает.
 
 
 
#30
27.05.2011 00:30:35
{quote}{login=ikki}{date=26.05.2011 11:08}{thema=}{post}пока вот так.  
формулы практически не менял, убрал только те расчеты, которые вообще не зависят от изменяемых переменных.  
 
в макросе - убрал активацию-выделение книг, листов и ячеек, заменил Copy и Paste на присваивание значений.  
подкорректировал объявление переменных.  
 
добавил строчку Array(...) - внаглую спёр приём у nilem'а :)  
 
в итоге на данных примера на небольшом кол-ве циклов (3125) получился выигрыш по времени примерно в 15-16 раз.  
думаю, что на "десятках тысяч строк" такого и близко не будет :(  
попробуйте?  
 
честно говоря, поднадоело :(  
возможно, завтра, с новыми силами, попробую загнать ячейки в массивы и считать всё там.  
 
пс. в 2003-м Excel'e не работало Windows(...).Activate, остальное всё то же самое.{/post}{/quote}  
 
Большущее спасибо, ikki, Юрий М, nilem! Извиняюсь за задержку с ответом, просто только что домой прилетел, день забит до отказа, возможности добраться до компа не было. Завтра попробую и результат обязательно сообщу!
 
 
 
Страницы: 1 2 След.
Читают тему
Наверх