Есть работающий макрос, но при увеличении строк листа "Состав узлов" до 22 000 думать начинает до 15 минут, но работает исправно. Можно ли как-то уменьшить время ожидания результата. Укороченный пример прилагается. Если что-то можно поколдовать в коде, то скину ссылку на файл оригинал, он весит 650 Kb.
Есть работающий макрос, но при увеличении строк листа "Состав узлов" до 22 000 думать начинает до 15 минут, но работает исправно. Можно ли как-то уменьшить время ожидания результата. Укороченный пример прилагается. Если что-то можно поколдовать в коде, то скину ссылку на файл оригинал, он весит 650 Kb.ZamoK
Set r = Sheets("Состав узлов").Columns(1).Find(t, LookIn:=xlValues, lookat:=xlWhole)
[/vba] Можно попробывать при запуске файла перебрать весь лист "Состав узлов" и создать Dictionary по данным Давайте, попробую с оригиналом Для большей оптимизации немного опишите логику работы [moder]Я не понял - кто что кому куда кидать собирается? Вы оба Правила форума хорошо читали? Что там говорится о переходе в личку и ссылках на посторонние ресурсы? И вообще - объясните мне, непонятливому, чем принципиально отличается работа с маленьким и большим файлами?[/moder]
А как еще проверить скорость работы макроса?
Думаю проблема в поиске [vba]
Код
Set r = Sheets("Состав узлов").Columns(1).Find(t, LookIn:=xlValues, lookat:=xlWhole)
[/vba] Можно попробывать при запуске файла перебрать весь лист "Состав узлов" и создать Dictionary по данным Давайте, попробую с оригиналом Для большей оптимизации немного опишите логику работы [moder]Я не понял - кто что кому куда кидать собирается? Вы оба Правила форума хорошо читали? Что там говорится о переходе в личку и ссылках на посторонние ресурсы? И вообще - объясните мне, непонятливому, чем принципиально отличается работа с маленьким и большим файлами?[/moder]
Модератор: Я не понял - кто что кому куда кидать собирается? Вы оба Правила форума хорошо читали? Что там говорится о переходе в личку и ссылках на посторонние ресурсы? И вообще - объясните мне, непонятливому, чем принципиально отличается работа с маленьким и большим файлами?
Да, читали. Думаю, что во все правила подразумевают исключения (если они обоснованы), на то они и правила.
чем принципиально отличается работа с маленьким и большим файлами?
Тем, что файл образец работает мгновенно, а рабочий документ думает 15 минут, это и есть суть вопроса и причина для не грубого отступления от правил. Тем более ещё ни кто не нарушил правила! И нарушать не собирается, если это не будет неизбежно.
[moder]Первый ответ - правилам на форуме можно представить аналогию в жизни - закон. Например, УК. Интересно, Вашу первую отмазку прокурору кто-нибудь пытался когда-то втюхать? Если да, что что прокурор ему на это ответил? Второй - кто мешает в маленьком файле просто протянуть данные тыщ на 50 строк?. В общем, Правила писал не я, все вопросы к автору. А я (и остальные модераторы) слежу за выполнением. Хотите нарушить, есть 2 пути - нарушать в надежде, что прокатит или связаться с владельцем и попросить о разовом исключении.
Модератор: Я не понял - кто что кому куда кидать собирается? Вы оба Правила форума хорошо читали? Что там говорится о переходе в личку и ссылках на посторонние ресурсы? И вообще - объясните мне, непонятливому, чем принципиально отличается работа с маленьким и большим файлами?
Да, читали. Думаю, что во все правила подразумевают исключения (если они обоснованы), на то они и правила.
чем принципиально отличается работа с маленьким и большим файлами?
Тем, что файл образец работает мгновенно, а рабочий документ думает 15 минут, это и есть суть вопроса и причина для не грубого отступления от правил. Тем более ещё ни кто не нарушил правила! И нарушать не собирается, если это не будет неизбежно.
[moder]Первый ответ - правилам на форуме можно представить аналогию в жизни - закон. Например, УК. Интересно, Вашу первую отмазку прокурору кто-нибудь пытался когда-то втюхать? Если да, что что прокурор ему на это ответил? Второй - кто мешает в маленьком файле просто протянуть данные тыщ на 50 строк?. В общем, Правила писал не я, все вопросы к автору. А я (и остальные модераторы) слежу за выполнением. Хотите нарушить, есть 2 пути - нарушать в надежде, что прокатит или связаться с владельцем и попросить о разовом исключении.ZamoK
Я не Гуру, но стремлюсь!
Сообщение отредактировал _Boroda_ - Вторник, 21.07.2015, 13:14
miver, что мешает Вам заполнить образец файла тестовыми данными на несколько тысяч? Код не должен меняться при изменении кол-ва данный. ZamoK, правила распространяются на всех одинаково, БЕЗ исключений.
miver, что мешает Вам заполнить образец файла тестовыми данными на несколько тысяч? Код не должен меняться при изменении кол-ва данный. ZamoK, правила распространяются на всех одинаково, БЕЗ исключений.Manyasha
рекурсивный алгоритм. перебор дерева состава. имхо, сильно зависит от реальных данных. откуда я знаю - может, в реале уровень вложенности узлов в разы или десятки раз больше?
кстати, по коду непонятно - зачем там каждый раз в процедуру передается ссылка на словарь? конечно, это не создание локальной копии объекта, но и ссылку передать - тоже время надо, да и стек вызовов быстрее переполнится. я бы сделал этот объект глобальным - как массив rez() но, скорее всего, основную часть времени занимает, действительно, поиск на листе и, возможно, многократная обработка одних и тех же узлов. это надо проверять - собирать статистику, искать узкое место, отнимающее основную часть времени и т.п.
без реального файла - оптимизировать почти нечего. имхо. и еще одно имхо: думаю, что такая работа - хорошая кандидатура для одноименной ветки форума.
"протянуть" ?.. дерево?.. без комментариев.
рекурсивный алгоритм. перебор дерева состава. имхо, сильно зависит от реальных данных. откуда я знаю - может, в реале уровень вложенности узлов в разы или десятки раз больше?
кстати, по коду непонятно - зачем там каждый раз в процедуру передается ссылка на словарь? конечно, это не создание локальной копии объекта, но и ссылку передать - тоже время надо, да и стек вызовов быстрее переполнится. я бы сделал этот объект глобальным - как массив rez() но, скорее всего, основную часть времени занимает, действительно, поиск на листе и, возможно, многократная обработка одних и тех же узлов. это надо проверять - собирать статистику, искать узкое место, отнимающее основную часть времени и т.п.
без реального файла - оптимизировать почти нечего. имхо. и еще одно имхо: думаю, что такая работа - хорошая кандидатура для одноименной ветки форума.
Логика работы: С листа "Перечень" В3-В60 номера изделий, которые нужно разузловать подетально, ссумировать повторяищеся входящие в состав детали. Результат в виде списка на лист "Итог". Подробно: 1) Берём номер из ячейки В3 лист "Перечень" 2) Ищем соответствие на листе "Состав узлов" в столбце А, копирует перечисленный состав из столбцов CDE до первой пустой ячейки на лист "Итог". 3) Затем находим из вновь добавленных позиций номера начинающиеся на 3 и 6 (это узлы и их нужно разобрать по деталям) 4) Берём найденый номер на 3(6) далее пункт 2,3,4 пока не закончатся в добавленных позициях номера на 3 и 6. 4.1) Если номера на листе "Состав узлов" в столбце А нет, то выдаём сообщение ... при нажатии ОК продолжаем 2,3,4 5) Затем следующая ячейка с листа "Перечень" - В4, и повторяются п.2,3,4,5 до пустой ячейки.
Количество деталей соответственно ссумируется при повторении и умножается на требуемое кол-во "Перечень" - $D
Вложенность узлов может достигать до 12 уровней Деталей в одном узле около 30-35
Логика работы: С листа "Перечень" В3-В60 номера изделий, которые нужно разузловать подетально, ссумировать повторяищеся входящие в состав детали. Результат в виде списка на лист "Итог". Подробно: 1) Берём номер из ячейки В3 лист "Перечень" 2) Ищем соответствие на листе "Состав узлов" в столбце А, копирует перечисленный состав из столбцов CDE до первой пустой ячейки на лист "Итог". 3) Затем находим из вновь добавленных позиций номера начинающиеся на 3 и 6 (это узлы и их нужно разобрать по деталям) 4) Берём найденый номер на 3(6) далее пункт 2,3,4 пока не закончатся в добавленных позициях номера на 3 и 6. 4.1) Если номера на листе "Состав узлов" в столбце А нет, то выдаём сообщение ... при нажатии ОК продолжаем 2,3,4 5) Затем следующая ячейка с листа "Перечень" - В4, и повторяются п.2,3,4,5 до пустой ячейки.
Количество деталей соответственно ссумируется при повторении и умножается на требуемое кол-во "Перечень" - $D
Вложенность узлов может достигать до 12 уровней Деталей в одном узле около 30-35ZamoK
Я не Гуру, но стремлюсь!
Сообщение отредактировал ZamoK - Вторник, 21.07.2015, 16:31
Sub ertert(d As Dictionary, ByVal t As String, ByVal k As Long) Dim x, j&, s$, r As Range, ro As Long ro = dic(t) If ro = 0 Then MsgBox "Не разузлован: " & t, 48, "ВНИМАНИЕ": Exit Sub Set r = Sheets("Состав узлов").Cells(ro, 1) x = r.CurrentRegion.Value For j = 1 To UBound(x) s = CStr(x(j, 3)) If d.Exists(s) Then rez(d.Item(s), 4) = rez(d.Item(s), 4) + x(j, 5) * k Else n = n + 1: d.Item(s) = n rez(n, 1) = n rez(n, 2) = s rez(n, 3) = x(j, 4) rez(n, 4) = x(j, 5) * k End If If Left(s, 1) = 3 Or Left(s, 1) = 6 Then ertert d, s, x(j, 5) * k End If Next j End Sub
[/vba]
Ну тогда вот:
[vba]
Код
Sub ertert(d As Dictionary, ByVal t As String, ByVal k As Long) Dim x, j&, s$, r As Range, ro As Long ro = dic(t) If ro = 0 Then MsgBox "Не разузлован: " & t, 48, "ВНИМАНИЕ": Exit Sub Set r = Sheets("Состав узлов").Cells(ro, 1) x = r.CurrentRegion.Value For j = 1 To UBound(x) s = CStr(x(j, 3)) If d.Exists(s) Then rez(d.Item(s), 4) = rez(d.Item(s), 4) + x(j, 5) * k Else n = n + 1: d.Item(s) = n rez(n, 1) = n rez(n, 2) = s rez(n, 3) = x(j, 4) rez(n, 4) = x(j, 5) * k End If If Left(s, 1) = 3 Or Left(s, 1) = 6 Then ertert d, s, x(j, 5) * k End If Next j End Sub
With Worksheets("Итог") With .Range("A3:D3") .CurrentRegion.Offset(2).ClearContents .Resize(n).Value = rez() .Parent.Activate End With With ActiveWorkbook.Worksheets("Итог").Sort .SortFields.Clear .SortFields.Add Key:=Range("B3:B" & n), SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:=xlSortNormal .SetRange Range("b2:D" & n) .Header = xlYes .MatchCase = False .Orientation = xlTopToBottom .SortMethod = xlPinYin .Apply End With End With
[/vba]
Вот: [vba]
Код
With Worksheets("Итог") With .Range("A3:D3") .CurrentRegion.Offset(2).ClearContents .Resize(n).Value = rez() .Parent.Activate End With With ActiveWorkbook.Worksheets("Итог").Sort .SortFields.Clear .SortFields.Add Key:=Range("B3:B" & n), SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:=xlSortNormal .SetRange Range("b2:D" & n) .Header = xlYes .MatchCase = False .Orientation = xlTopToBottom .SortMethod = xlPinYin .Apply End With End With
Вход
Регистрация
Напомнить пароль
Автосумма
Часто используемые
ВПР
ЕСЛИ
СУММЕСЛИ
Финансовые
Логические
Текстовые
Даты и времени 
Ссылки и массивы 
Математические
Другие функции
Проверка свойств и значений
Баз данных
Математические
Обработки массивов
Обработки строк
Преобразования типов данных 
Загрузки данных
Работы с файлами
Обработки системных параметров
Обработки цвета
Работы с датами и временем
Преобразования в системы счисления
Финансовые
можно ли как-то сразу на листе "Итог" сортировку прикрутить по столбцу В
