Поиск  Пользователи  Правила  Войти
Форумы "Планета Excel" » Вопросы по Microsoft Excel (архив)
Страницы: 1
RSS
в 2010-м Excel таблица считается намного медленнее, чем в 2003-м
 
#1
29.12.2010 21:45:03
Здравствуйте.    
Есть у меня таблица высотой, примерно, 23 тыс. строк, а шириной - около сотни столбцов.  
Мне надо найти комбинацию 2-х параметров, при которых определенная ячейка этой таблицы принимает наибольшее значение. Один из этих двух параметров изменяется равномерно от 0,01 до 10, через 0,01, а другой от 0,01 до 1, тоже через 0,01. То есть всего 100 тыс. комбинаций этих параметров.  
Я решил данную задачу с помощью макроса, который подставляет в соответствующие "параметрические" ячейки расчетной таблицы значения от 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-го, чтобы убыстрить работу макроса и расчетной таблицы, из которой он берет данные?    
На всякий случай, прилагаю файл с уменьшенным количеством строчек.  
<EM><STRONG>Файл удален</STRONG> - велик размер - [<STRONG>МОДЕРАТОРЫ</STRONG>]</EM>
 
 
 
#2
30.12.2010 01:01:11
{quote}{login=Павел++}{date=29.12.2010 09:45}{thema=в 2010-м Excel таблица считается намного медленнее, чем в 2003-м}{post}Прочел я в интернете, что спасенье мое может быть в Windows 7 и Office-2010, чтобы оба были 64 bit.{/post}{/quote}  
Информация в принципе не верная, т.к. 64-битный Excel медленнее 32-битного.  
Это касается, в том числе, 64 и 32 битных версий Excel 2010.  
Основное преимущество 64-битного не в быстродействии, а в возможности работать с книгами значительно большего размера, при наличии большей оперативной памяти, конечно.
 
 
 
#3
30.12.2010 02:11:32
Где-то я это уже видел...  
 http://programmersforum.ru/showthread.php?p=699496  
Насчёт того макроса: работать напрямую с ячейками - это наверное самый медленный способ. Нужно на массивах делать.
 
 
 
#4
30.12.2010 03:06:35
ZVI, означает ли Ваш ответ, что нет никакой возможности ускорить работу расчета? И что самый быстрый вариант расчета тот, что делалася на xp-32 и excel-2003?
 
 
 
#5
30.12.2010 03:08:48
{quote}{login=Hugo}{date=30.12.2010 02:11}{thema=}{post}Где-то я это уже видел...{/post}{/quote}  
Ну да, только что-то там никто не ответил на мой вопрос. Вот пытаю счастья на планете эксель.  
 
>Насчёт того макроса: работать напрямую с ячейками - это наверное самый медленный способ. Нужно на массивах делать.  
Не можете ли пояснить на каком-нибудь примере?
 
 
 
#6
30.12.2010 05:11:26
{quote}{login=}{date=30.12.2010 03:06}{thema=Re: }{post}Означает ли Ваш ответ, что нет никакой возможности ускорить работу расчета? И что самый быстрый вариант расчета тот, что делалася на xp-32 и excel-2003?{/post}{/quote}  
Мой комментарий был только по сравнению быстродействия 32 и 64-битных версий Excel.  
 
Обычно максимальный эффект по быстродействию достигается не заменой компьютера или версии операционки, или Excel, а оптимизацией кода.  
 
Сравните, например, медленную индивидуальную обработку ячеек с быстрой обработкой с помощью  VBA-массива:  
 
Sub Test()  
   
 Dim Rng As Range, t!, r&, a  
 Set Rng = Range("A1:A10000")  
   
 ' Поочередное заполнение ячеек 10000 раз  
 t = Timer  
 For r = 1 To Rng.Rows.Count  
   Rng.Cells(r, 1) = Rng.Cells(r, 1) + 1  
 Next  
 Debug.Print "Ячейки:", Round(Timer - t, 3) & " секунд"  
   
 ' Запись значений в массив a(), обработка массива  
 ' и копирование из массива в ячейки за одно действие  
 t = Timer  
 a = Rng.Value  ' <-- в массив  
 For r = 1 To UBound(a)  
   a(r, 1) = a(r, 1) + 1  
 Next  
 Rng.Value = a  ' <-- из массива  
 Debug.Print "Массив:", Round(Timer - t, 3) & " секунд"  
     
End Sub
 
 
 
#7
30.12.2010 10:05:25
> Считался такой макрос в XP на 2003-м Экселе около суток, что меня, конечно, не устраивало.  
 
И долго Вы так работали? О_O  
ИМХО, если что-то считается больше 5 минут, и это не однократная задача, то уже надо задуматься о смене алгоритма / программной среды / компьютера.  
 
> Мне надо найти комбинацию 2-х параметров, при которых определенная ячейка этой таблицы принимает наибольшее значение. Один из этих двух параметров изменяется равномерно от 0,01 до 10, через 0,01, а другой от 0,01 до 1, тоже через 0,01. То есть всего 100 тыс. комбинаций этих параметров.  
 
А "Поиск решения" пробовали? В случае более-менее гладких функций он быстро находит решение без тупого перебора.  
 
> Каково же было мое удивление, когда я обнаружил, что считается оно теперь в Excel-2010 в несколько раз медленнее, чем в Excel-2003.  
 
Ничего удивительного. Excel вообще не является средством для выполнения большого объема расчетов. Это инструмент для анализа и наглядного представления данных. Поэтому и развивается он в сторону "звоночков и свисточков" (конечно, нужных для целевого пользователя), а не повышения скорости расчетов.
 
 
 
#8
30.12.2010 11:36:39
{quote}{post}Не можете ли пояснить на каком-нибудь примере?{/post}{/quote}  
Небольшой пример выше уже есть. Там ускорение в 7 раз, я как-то замерял похожим тестом, только вроде ещё суммировал параллельно ячейки - получил разницу в 38 раз. На другом форуме похожую задачу ускорили с 40 минут до 5 секунд. Так что Вашу задачу наверняка можно быстрее часа сделать, всего лишь заменив перебор ячеек на перебор массивов.
 
 
 
#9
30.12.2010 12:15:50
{quote}{login=ZVI}{date=30.12.2010 01:01}{thema=}{post}Информация в принципе не верная, т.к. 64-битный Excel медленнее 32-битного. Это касается, в том числе, 64 и 32 битных версий Excel 2010.  
Основное преимущество 64-битного не в быстродействии, а в возможности работать с книгами значительно большего размера, при наличии большей оперативной памяти, конечно.{/post}{/quote}  
что-то как-то сомнительно...  
или имеете в  виду 64 битное ПО на 32 битном железе?
 
 
 
#10
30.12.2010 13:51:43
Попытался вникнуть в задачу - тяжело... Столько формул переплетается...  
Но если смотреть на первоначальную задачу:  
"Мне надо найти комбинацию 2-х параметров, при которых определенная ячейка этой таблицы принимает наибольшее значение. Один из этих двух параметров изменяется равномерно от 0,01 до 10, через 0,01, а другой от 0,01 до 1, тоже через 0,01. То есть всего 100 тыс. комбинаций этих параметров.",  
то это вроде просто цикл в цикле двух массивов, и анализ на максимальное значение. 100 тыс. комбинаций - это вроде в пару минут должно уложиться, а то и быстрее?
 
 
 
#11
30.12.2010 20:55:43
{quote}{login=Hugo}{date=30.12.2010 01:51}{thema=}{post}Попытался вникнуть в задачу - тяжело... Столько формул переплетается...  
Но если смотреть на первоначальную задачу:  
<...> - это вроде в пару минут должно уложиться, а то и быстрее?{/post}{/quote}  
Hugo, Вы же сами говорите "столько формул переплетается". Как может получиться пару минут при 23 тыс (более) строчек и порядка ста столбцах и "стольких" формулах на 100 тыс. вариантов? Похоже, не получается. По крайней мере, у меня. Буду признателен за подсказки.
 
 
 
#12
30.12.2010 21:27:02
Я не вникал в Ваш алгоритм, как-то не до этого в последние дни :), но исходя из "если смотреть на первоначальную задачу", то перебор двух массивов с обработкой полученных пар значений больше времени занять не должен. Результаты можно собирать в третий массив, который в конце выгрузить на лист для анализа.  
Но возможно задача сложнее, и такой подход не годится, тогда извините...  
С наступающим!!!
 
 
 
#13
31.12.2010 00:51:53
Так как насчет Поиска решения? Было бы интересно сравнить результаты, полученные этим методом и перебором.
 
 
 
#14
01.01.2011 01:48:13
Файл очень большой. Как я сказал, 23 тыс. строк и много десятков стобцов. Большое количество многоэтажых "если" практически в каждой ячейке. Поиск решения у меня не срабатывает. Может, его надо настроить как-то особо. Подскажите...  
ВСЕХ С НАСТУПИВШИМ 2011-М!!!
 
 
 
#15
01.01.2011 01:53:42
Впрочем, написал и подумал. Если перебор с помощью макроса занимает более суток (в 2003-м), то и поиск решения должен заниматься часы (это - уж  по крайней мере). Поэтому, наверное, и не срабатывает. Где-то должна быть регулировка времени работы этой функции. При обычных настройках "по умолчанию" поиск решения через пару минут выдает мне сообщение, что "решение найдено", но я не вижу никакого "решения" в своей таблице. Все ячейки остаются неизменными.
 
 
 
#16
27.01.2011 23:25:28
Ну вот еще раз прикладываю обрезанный файл (чтобы было не более 100 кб)с макросом.  
Может, какой добрый человек, подскажет, что в нем надо поправить, чтобы быстрее считалась таблица из 25 тыс. строк?..
 
 
 
#17
28.01.2011 00:16:29
Я думаю, вряд ли кто сделает за просто так, от избытка свободного времени. Там нужно тщательно анализировать каждую из 58 формул и переносить их выполнение в код, тогда будет быстрее. Возможно, попутно можно что-то ещё оптимизировать.  
Я бы на работе на такое вероятно запросил неделю. Оплачиваемую. И делал бы только потому, что надо и больше некому. Это если бы у нас такая задача стояла. Но я особо не вникал, может там половина этих формул и не нужна?  
Гоняйте своих ИТ, пусть зарплату отрабатывают.
 
 
 
#18
28.01.2011 00:29:49
Для начала можно формулы оптимизировать. Например, часто встречается выражение  
ЕСЛИ(а+в<c;а+в;с). Можно заменить на МИН(а+в;с). В столбце J  
есть  
=ЕСЛИ(И(AD16=0;AD17=1);ЕСЛИ(AK16<F17;ЕСЛИ(D17+$G$1<E17;D17+$G$1;E17);ЕСЛИ(AK16+$G$1<E17;AK16+$G$1;E17));"-")  
можно  
=ЕСЛИ(И(НЕ(AD16);AD17);ЕСЛИ(AK16<F17;МИН(D17+$G$1;E17);МИН(AK16+$G$1;E17));"-")
 
 
 
#19
28.01.2011 02:30:01
Господин Казанский, спасибо за подсказку.  
Замена " ЕСЛИ(а+в<c;а+в;с). Можно заменить на МИН(а+в;с) " - абсолютно понятна.  
А вот вариант вместо ЕСЛИ(И(AD16=0;AD17=1);... писать ЕСЛИ(И(НЕ(AD16);AD17);... - честно говоря, не очень понятен.
 
 
 
#20
28.01.2011 02:39:53
{quote}{login=Hugo}{date=28.01.2011 12:16}{thema=}{post}Я думаю, вряд ли кто сделает за просто так, от избытка свободного времени...{/post}{/quote}  
Hugo, мне не нужно, чтобы кто-то этот файл делал за меня.    
Просто зашел в тупик и нуждаюсь в двух-трех экспертных подсказках, типа совета г-на Казанского. Что можно было бы подправить, чтобы оно быстрее стало работать.
 
 
 
#21
28.01.2011 02:51:35
С количеством строк явная ошибка, требуется не 25 тысяч, а 99 тысяч строк.  
Приложил готовое решение как иллюстрацию работы с массивами.  
Формулы листа не оптимизировал.  
Перерисовку экрана не отключал.  
Индикацию в A3:B3 оставил, хотя без этого можно и обойтись, сэкономив пару секунд. Добавил DoEvents, что вообще-то тоже лишнее.  
 
Результат: все строки листа Excel 2003 заполняются примерно за 20 секунд.
 
 
 
#22
28.01.2011 02:57:33
При выгрузке на лист очередной порции данных из буферного массива, в статусной строке отображаются текущие строки и время
 
 
 
#23
06.02.2011 02:00:31
ZVI, забыл поблагодарить.
 
 
 
#24
06.02.2011 02:03:07
Павел, хорошо, что/если помогло :-)
 
 
 
#25
05.02.2012 08:13:38
.xlsx -- xml-структура, сжатая zip  
Павел, я думаю, вы будеде довольны моим трудоемким решением: просто сохраните ваш файл в .xlsb -- Бинарная книга. Прирост скорости примерно в 10 раз.    
Что-то по поводу ограниченной совместимости с другими программами, так что аккуратнее.
 
 
 
#26
05.02.2012 08:15:27
надо было завтро ответить, был бы юбилейный "не прошло и года" ответ :)
 
 
 
#27
10.03.2012 15:37:40
Seregas, прошу прощения за задрежку с ответом. Потерял из виду эту ветвь и своевременно не заметил Вашего поста. А сейчас фактически случайно, через Яндекс, в поиске ответа на совершенно другой вопрос, опять наткнулся на эту ветвь и обнаружил его.  
Быстро попробовал (на уже измененной версии того файла). Похоже, Вы правы. Макрос, если файл сохранить в формате xlsb, работает заметно быстрее, чем в формате xlsm. Буду дальше использовать Ваше предложение.    
Спасибо за ответ и за долготерпение!
 
 
 
Страницы: 1
Читают тему
Наверх