Форум программистов
 

Восстановите пароль или Зарегистрируйтесь на форуме, о проблемах и с заказом рекламы пишите сюда - alarforum@yandex.ru, проверяйте папку спам!

Вернуться   Форум программистов > Microsoft Office и VBA программирование > Microsoft Office Excel
Регистрация

Восстановить пароль
Повторная активизация e-mail

Купить рекламу на форуме - 42 тыс руб за месяц

Ответ
 
Опции темы Поиск в этой теме
Старый 20.12.2010, 04:07   #1
Павел+
 
Регистрация: 20.12.2010
Сообщений: 3
По умолчанию Скорость исполнения макроса в Excel-2010 намного ниже, чем в Excel-2003

Здравствуйте. Есть у меня таблица высотой, примерно, 23 тыс. строк, а шириной - около сотни столбцов.
Мне надо найти комбинацию 2-х параметров, при которых определенная ячейка этой таблицы принимает наибольшее значение. Каждый из этих двух параметров изменяется равномерно от 0,01 до 10, через 0,01. То есть всего возможно 100 тыс. комбинаций этих параметров.
Я решил данную задачу с помощью макроса (это мой первый в жизни макрос на VB), который подставляет в "параметрические" ячейки расчетной таблицы значения от 0,01 до 10, предварительно записанные мною на отдельном листе книги, и переносит получившееся значение максимизируемой ячейки расчетной таблицы на третий лист. На этот же третий лист заодно переносятся получающиеся при данных параметрах значения еще нескольких ячеек расчетной таблицы (мне нужно это для полноты информации).
Считался такой макрос в XP на 2003-м Экселе около суток, что меня, конечно, не устраивало.
Прочел я в интернете, что спасенье мое может быть в Windows 7 и Office-2010, чтобы оба были 64 bit. Ну и купил и установил их, оба 64-b.
Каково же было мое удивление, когда я обнаружил, что считается оно теперь в Excel-2010 в несколько раз медленнее, чем в Excel-2003.
Перенос из 2003 в 2010 я делал и с помощью встроенного механизма преобразования из xls в xlsx, и даже просто фактически создал файл в 2010-м заново, копируя из старого только формулы, через строку формул (а не ячейками). Сохранил в 2010-м в формате *.xlsm.
Могу сообщить, что никакой разницы в быстродействии в зависимости от способа переноса из 2003-го в 2010-й не обнаружилось. При любом способе переноса в 2010-м считается все из рук вон медленно. Даже более того: обратное действие - сохранине файла-2010 в формате *.xls и последующее тестирование его в 2003-м офисе показали все тот же результат. В 2003-м excel'е все считается намного быстрее, чем 2010-м.
Вопрос. Известен ли такой глюк сообществу? Может, есть какие-нибудь рекомендации по настройке 2010-го, чтобы убыстрить работу макроса и расчетной таблицы, из которой он берет данные?
Павел+ вне форума Ответить с цитированием
Старый 20.12.2010, 06:17   #2
EducatedFool
Программист VBA
СуперМодератор
 
Аватар для EducatedFool
 
Регистрация: 13.07.2008
Сообщений: 6,855
По умолчанию

Цитата:
Каждый из этих двух параметров изменяется равномерно от 0,01 до 10, через 0,01. То есть всего возможно 100 тыс. комбинаций этих параметров
Я насчитал всего лишь 10 тыс. комбинаций.

Цитата:
Может, есть какие-нибудь рекомендации по настройке 2010-го, чтобы убыстрить работу макроса и расчетной таблицы, из которой он берет данные?
Не увидев таблицы, и не зная, как и что вы там рассчитываете, сложно посоветовать что-то конкретное.
Не надо все 23 тысячи строк - хватит строк 20-30.

Вариантов решения как минимум четыре:
1) Немного оптимизировать ваш макрос, ускорив его в десяток раз
2) Переписать макрос "с нуля", чтобы он по минимум обращался к ячейкам листа
(это ускорит обработку в тысячи раз)
3) Подсчитать всё формулами - без помощи макросов
4) Использовать надстройку "Поиск решения"

Выкладывайте свой файл - посмотрим, что можно сделать.
EducatedFool вне форума Ответить с цитированием
Старый 20.12.2010, 12:31   #3
IgorGO
МегаМодератор
СуперМодератор
 
Аватар для IgorGO
 
Регистрация: 05.02.2008
Сообщений: 9,487
По умолчанию

у меня вообще получется от 0.01 до 10 с шагом 0.01 - тысяча шагов. комбинаций из двух параметров будет 1 миллион.
Программисты - это люди, решающие проблемы, о существовании которых Вы не подозревали, методами, которых Вы не понимаете
IgorGO вне форума Ответить с цитированием
Старый 20.12.2010, 14:38   #4
Павел+
 
Регистрация: 20.12.2010
Сообщений: 3
По умолчанию

Господа, чтобы не спорить, прикладываю "обрезанный" файл.
В реальном файле строчек на самом деле сейчас 23155.
Может, возникнут какие идеи, как его убыстрить...
Я, по неопытности своей, думаю, что дело тут не столько в макросе, сколько в самой расчетной таблице. Она в 2010-м почему-то в принципе долго пересчитывается (ну в смысле намного дольше, чем она же в 2003-м).
Вложения
Тип файла: zip Файл с макросом для Форума.zip (112.6 Кб, 24 просмотров)
Павел+ вне форума Ответить с цитированием
Старый 20.12.2010, 15:23   #5
EducatedFool
Программист VBA
СуперМодератор
 
Аватар для EducatedFool
 
Регистрация: 13.07.2008
Сообщений: 6,855
По умолчанию

Цитата:
Может, возникнут какие идеи, как его убыстрить...
Ускорить-то его можно, но вот нужно ли...

Объясните на своём примере, что и как должно вычисляться.
(чтобы не вникать в загадочные расчёты макроса)
EducatedFool вне форума Ответить с цитированием
Старый 29.12.2010, 03:28   #6
Павел+
 
Регистрация: 20.12.2010
Сообщений: 3
По умолчанию

Прошу прощения за задержку с ответом, был в отъезде.

Цель расчета - перебрать все варианты из 2-х параметров (один меняетя от 0,01 до 10,00 через 0,01, а другой - от 0,01 до 1,00 через 0,01, итого 100 тыс. комбинаций) и записать результаты расчетов на отдельном листе.

Вот как это устроено.

Лист "Param" Задает два установочных параметра, при которых происходит расчет. (Расчет выполняется на листе "1.+ (2010) SR-15M").
Колонка 1 листа "Param" задает первый параметр, остальные колонки задают второй параметр. То есть сначала устанавливается первый параметр из колонки 1 (например, ячейка R1C1), а потом перебираются все ячейки первой строки слева направо, пока не дойдут до ячейки в колонке номер 101 (R1C101), после этого переходят на вторую стоку и опять меняют параметр из колонки 1, после чего идут вдоль второй строки до колонки 101. И так далее. Эту процедуру исполняет макрос.

На листе "1.+ (2010) SR-15M" происходит основной расчет.
1) В строках 1 и 2 заданы константы, используемые в расчете и сюда же переносятся установочные параметры расчета с листа "Param". Это две цифры в колонке 33, помеченные цветным шрифтом, их подставляет макрос, перебирающий варианты.
2) В колонках с заголовками "4"-"7" - исходные данные.
3) В колонках с заголовками "30"-"38" (помечены голубой заливкой) - происходит обработка этих исходных данных и определение дополнительных вычисляемых параметров (колонки "37" и "38")
4) В колонках с заголовками "10"-"18" происходит обработка исходных данных с учетом констант и устновочных параметров (строки 1 и 2) и полученных значений вычисляемых параметров (колонки "37" и "38"). Результатом этой обработки является выбор по критериям некоторых ячеек исходных данных в некоторых строках с исходными данными (из колонок "4"-"7").
5) В колонках с заголовками "19"-"29" происходит обработка выбранных в предыдущем пункте исходных данных и подсчет результатов.
6)Значения некоторых результирующих ячеек переносятся в колонки с заголовками "64" - "121", а потом с помощью макроса переносятся на лист "Results-15M" в соответствующую строку этого листа, вкупе с парметрами, при которых были получены эти результаты.
7) Таким образом, на листе "Results-15M" записываются избранные результаты расчета каждого варианта сочетания параметров. В последствии, когда макрос заканчивает работу, я с помощью фильтрации и т.п. выбираю лучший для меня набор параметров, давших наилучшие результаты.

Полагаю, что из моего путанного объяснения Вы все равно ничего не поймете. Может быть, просто еще раз поглядев те формулы, которые я использую, и тот макрос, который я написал, Вы подскажете, почему в 2010-ом экселе происходят такие тормоза...
Павел+ вне форума Ответить с цитированием
Ответ


Купить рекламу на форуме - 42 тыс руб за месяц

Опции темы Поиск в этой теме
Поиск в этой теме:

Расширенный поиск


Похожие темы
Тема Автор Раздел Ответов Последнее сообщение
Оптимизация времени исполнения макроса basil0 Microsoft Office Excel 12 06.12.2010 10:20
Выбор запускаемого макроса по нажатию кнопки на ленте в Excel 2010 motorway Microsoft Office Excel 2 16.06.2010 16:35
В Excel 2010 макрос медленнее в 3 раза чем в 2007 Владимир. Microsoft Office Excel 12 22.05.2010 18:24
Как запретить запуск программы на VBA Excel 2003 в Excel 2007 kovalevskivf Microsoft Office Excel 2 15.05.2009 16:47
Скорость макросов в Excel 2007 June Microsoft Office Excel 3 14.02.2008 12:06