Домашняя страница Undo Do New Save Карта сайта Обратная связь Поиск по форуму
МИР MS EXCEL - Гость.xls

Вход

Регистрация

Напомнить пароль

 

= Мир MS Excel/Измерить скорость работы формулы - Мир MS Excel

Старая форма входа
  • Страница 1 из 1
  • 1
Модератор форума: китин, _Boroda_, DrMini  
Мир MS Excel » Вопросы и решения » Вопросы по Excel » Измерить скорость работы формулы (Формулы/Formulas)
Измерить скорость работы формулы
Russel Дата: Понедельник, 02.03.2015, 11:17 | Сообщение № 1
Группа: Друзья
Ранг: Старожил
Сообщений: 1394
Репутация: 320 ±
Замечаний: 0% ±

Excel 2010
Доброе утро, коллеги!

Делаю на работе отчет по большому массиву данных. Выдал на-гора несколько вариантов формул, получающих один и тот же результат.
Ничего особенного, простое суммирование по нескольким параметрам, пример одной из формул:
Код
=СУММПРОИЗВ((СМЕЩ('ОПР'!$A$1;ПОИСКПОЗ(КОНМЕСЯЦА($R$2;-1)+1;'ОПР'!$X:$X;0)-1;;1600)<=$R$2)*(СМЕЩ('ОПР'!$B$1;ПОИСКПОЗ(КОНМЕСЯЦА($R$2;-1)+1;'ОПР'!$X:$X;0)-1;;1600)=A18)*(СМЕЩ('ОПР'!$R$1;ПОИСКПОЗ(КОНМЕСЯЦА($R$2;-1)+1;'ОПР'!$X:$X;0)-1;;1600)))

Все формулы работают правильно, но, ввиду того, что объем данных достаточно большой, а рабочие ПК недостаточно молодые, все формулы очень сильно затормаживают файл - приходится либо отключать автопересчет, либо выносить лист с тяжелыми формулами в отдельный файл, что не очень удобно.

Внимание, вопрос: (с) как можно измерить скорость работы той или иной формулы (макрос, или внутренний функционал, - секундомером вручную не предлагать :D )

Факультативно: в СУММПРОИЗВ() лучше использовать ссылки на весь столбец или как в примере выше - смещать диапазон?

ИМХО, вопрос общего характера, поэтому файл не прикладываю.
Всем заранее спасибо! yes

QIWI 9173973973
 
Ответить
СообщениеДоброе утро, коллеги!

Делаю на работе отчет по большому массиву данных. Выдал на-гора несколько вариантов формул, получающих один и тот же результат.
Ничего особенного, простое суммирование по нескольким параметрам, пример одной из формул:
Код
=СУММПРОИЗВ((СМЕЩ('ОПР'!$A$1;ПОИСКПОЗ(КОНМЕСЯЦА($R$2;-1)+1;'ОПР'!$X:$X;0)-1;;1600)<=$R$2)*(СМЕЩ('ОПР'!$B$1;ПОИСКПОЗ(КОНМЕСЯЦА($R$2;-1)+1;'ОПР'!$X:$X;0)-1;;1600)=A18)*(СМЕЩ('ОПР'!$R$1;ПОИСКПОЗ(КОНМЕСЯЦА($R$2;-1)+1;'ОПР'!$X:$X;0)-1;;1600)))

Все формулы работают правильно, но, ввиду того, что объем данных достаточно большой, а рабочие ПК недостаточно молодые, все формулы очень сильно затормаживают файл - приходится либо отключать автопересчет, либо выносить лист с тяжелыми формулами в отдельный файл, что не очень удобно.

Внимание, вопрос: (с) как можно измерить скорость работы той или иной формулы (макрос, или внутренний функционал, - секундомером вручную не предлагать :D )

Факультативно: в СУММПРОИЗВ() лучше использовать ссылки на весь столбец или как в примере выше - смещать диапазон?

ИМХО, вопрос общего характера, поэтому файл не прикладываю.
Всем заранее спасибо! yes
Автор - Russel
Дата добавления - 02.03.2015 в 11:17
Nic70y Дата: Понедельник, 02.03.2015, 11:26 | Сообщение № 2
Группа: Друзья
Ранг: Экселист
Сообщений: 9133
Репутация: 2416 ±
Замечаний: 0% ±

Excel 2010
http://www.excelworld.ru/forum/2-13314-1#112948
Цитата Russel, 02.03.2015 в 11:17, в сообщении № 1
СМЕЩ
мне не нравится - она летучая (думаю есть более лучший способ - например доп. столбцы)
(это все ИМХО конечно)

ЮMoney 41001841029809
 
Ответить
Сообщениеhttp://www.excelworld.ru/forum/2-13314-1#112948
Цитата Russel, 02.03.2015 в 11:17, в сообщении № 1
СМЕЩ
мне не нравится - она летучая (думаю есть более лучший способ - например доп. столбцы)
(это все ИМХО конечно)
Автор - Nic70y
Дата добавления - 02.03.2015 в 11:26
Russel Дата: Понедельник, 02.03.2015, 11:36 | Сообщение № 3
Группа: Друзья
Ранг: Старожил
Сообщений: 1394
Репутация: 320 ±
Замечаний: 0% ±

Excel 2010
Nic70y, спасибо за макрос! hands
СУММПРОИЗВ() тоже летучая, разве нет?
Субъективно, по ощущениям, внутри СУММПРОИЗВ() конструкция типа (СМЕЩ('ОПР'!$A$1;ПОИСКПОЗ(КОНМЕСЯЦА($R$2;-1)+1;'ОПР'!$X:$X;0)-1;;1600)<=$R$2) работает быстрее, чем ('ОПР'!$X:$X<=$R$2)

QIWI 9173973973
 
Ответить
СообщениеNic70y, спасибо за макрос! hands
СУММПРОИЗВ() тоже летучая, разве нет?
Субъективно, по ощущениям, внутри СУММПРОИЗВ() конструкция типа (СМЕЩ('ОПР'!$A$1;ПОИСКПОЗ(КОНМЕСЯЦА($R$2;-1)+1;'ОПР'!$X:$X;0)-1;;1600)<=$R$2) работает быстрее, чем ('ОПР'!$X:$X<=$R$2)
Автор - Russel
Дата добавления - 02.03.2015 в 11:36
Nic70y Дата: Понедельник, 02.03.2015, 11:56 | Сообщение № 4
Группа: Друзья
Ранг: Экселист
Сообщений: 9133
Репутация: 2416 ±
Замечаний: 0% ±

Excel 2010
Цитата Russel, 02.03.2015 в 11:36, в сообщении № 3
спасибо за макрос
это не мне - Сергею.
Цитата Russel, 02.03.2015 в 11:36, в сообщении № 3
СУММПРОИЗВ() тоже летучая, разве нет?
нет.
летучие, это те которые пересчитываются при любых изменениях на листе, даже если просто открыть файл и ни чего не делать (а в файле будут летучие формулы) они пересчитаются и при закрытии файла
будет запрос о сохранении изменений.

ЮMoney 41001841029809
 
Ответить
Сообщение
Цитата Russel, 02.03.2015 в 11:36, в сообщении № 3
спасибо за макрос
это не мне - Сергею.
Цитата Russel, 02.03.2015 в 11:36, в сообщении № 3
СУММПРОИЗВ() тоже летучая, разве нет?
нет.
летучие, это те которые пересчитываются при любых изменениях на листе, даже если просто открыть файл и ни чего не делать (а в файле будут летучие формулы) они пересчитаются и при закрытии файла
будет запрос о сохранении изменений.
Автор - Nic70y
Дата добавления - 02.03.2015 в 11:56
Russel Дата: Понедельник, 02.03.2015, 12:02 | Сообщение № 5
Группа: Друзья
Ранг: Старожил
Сообщений: 1394
Репутация: 320 ±
Замечаний: 0% ±

Excel 2010
Nic70y, пошел освежать память на предмет волатильных функций.
Еще раз спасибо!

QIWI 9173973973
 
Ответить
СообщениеNic70y, пошел освежать память на предмет волатильных функций.
Еще раз спасибо!
Автор - Russel
Дата добавления - 02.03.2015 в 12:02
Russel Дата: Вторник, 03.03.2015, 17:38 | Сообщение № 6
Группа: Друзья
Ранг: Старожил
Сообщений: 1394
Репутация: 320 ±
Замечаний: 0% ±

Excel 2010
_Boroda_, я думал над этим и даже поначалу собирался, но передумал - маленькая часть таблицы нормально работает. Так как необходимо суммирование данных по 4 критериям (диапазон дат, объект, продукт) из таблицы более 40 000 строк и 20 столбцов, шустрее будет только макрос, что неприемлемо, к сожалению. В идеале использовать бы сводную, но утвержденная форма отчета все портит, плюс данные собираются из нескольких разноформатных таблиц.

QIWI 9173973973
 
Ответить
Сообщение_Boroda_, я думал над этим и даже поначалу собирался, но передумал - маленькая часть таблицы нормально работает. Так как необходимо суммирование данных по 4 критериям (диапазон дат, объект, продукт) из таблицы более 40 000 строк и 20 столбцов, шустрее будет только макрос, что неприемлемо, к сожалению. В идеале использовать бы сводную, но утвержденная форма отчета все портит, плюс данные собираются из нескольких разноформатных таблиц.
Автор - Russel
Дата добавления - 03.03.2015 в 17:38
_Boroda_ Дата: Вторник, 03.03.2015, 21:01 | Сообщение № 7
Группа: Админы
Ранг: Местный житель
Сообщений: 16907
Репутация: 6615 ±
Замечаний: ±

2003; 2007; 2010; 2013 RUS
Цитата Russel, 03.03.2015 в 17:38, в сообщении № 7
поначалу собирался, но передумал - маленькая часть таблицы нормально работает.
Зря. Величина файла-примера не играет никакой роли - Вы же предупредили, что 40000 строк.

Цитата Russel, 03.03.2015 в 17:38, в сообщении № 7
Так как необходимо суммирование данных по 4 критериям ... шустрее будет только макрос
А вот в этом я очень не уверен. Вот прям очень-очень. Макрос, конечно, будет шустрее, но и написать формулу, работающую быстрее СУММПРОИЗВ с кучей СМЕЩей, я думаю, вполне реально.

Цитата Russel, 03.03.2015 в 17:38, в сообщении № 7
В идеале использовать бы сводную, но утвержденная форма отчета все портит
. Здесь тоже можно попытаться что-нибудь придумать.

Цитата Russel, 03.03.2015 в 17:38, в сообщении № 7
данные собираются из нескольких разноформатных таблиц
По Вашей формуле из первого столбца этого что-то не заметно. Или данные сначала собираются, а потом из них делается отчет?

В общем, конечно, дело хозяйское, но я бы посмотрел. Глядишь, что-нибудь в голову и пришло бы.

Скажи мне, кудесник, любимец ба’гов...
Платная помощь:
Boroda_Excel@mail.ru
Яндекс-деньги: 41001632713405 | Webmoney: R289877159277; Z102172301748; E177867141995
 
Ответить
Сообщение
Цитата Russel, 03.03.2015 в 17:38, в сообщении № 7
поначалу собирался, но передумал - маленькая часть таблицы нормально работает.
Зря. Величина файла-примера не играет никакой роли - Вы же предупредили, что 40000 строк.

Цитата Russel, 03.03.2015 в 17:38, в сообщении № 7
Так как необходимо суммирование данных по 4 критериям ... шустрее будет только макрос
А вот в этом я очень не уверен. Вот прям очень-очень. Макрос, конечно, будет шустрее, но и написать формулу, работающую быстрее СУММПРОИЗВ с кучей СМЕЩей, я думаю, вполне реально.

Цитата Russel, 03.03.2015 в 17:38, в сообщении № 7
В идеале использовать бы сводную, но утвержденная форма отчета все портит
. Здесь тоже можно попытаться что-нибудь придумать.

Цитата Russel, 03.03.2015 в 17:38, в сообщении № 7
данные собираются из нескольких разноформатных таблиц
По Вашей формуле из первого столбца этого что-то не заметно. Или данные сначала собираются, а потом из них делается отчет?

В общем, конечно, дело хозяйское, но я бы посмотрел. Глядишь, что-нибудь в голову и пришло бы.
Автор - _Boroda_
Дата добавления - 03.03.2015 в 21:01
Serge_007 Дата: Вторник, 03.03.2015, 21:07 | Сообщение № 8
Группа: Админы
Ранг: Местный житель
Сообщений: 16475
Репутация: 2749 ±
Замечаний: ±

Excel 2016
Цитата Russel, 02.03.2015 в 11:17, в сообщении № 1
как можно измерить скорость работы той или иной формулы
У меня есть надстройка для измерения времени выполнения формул

ЮMoney:41001419691823 | WMR:126292472390
 
Ответить
Сообщение
Цитата Russel, 02.03.2015 в 11:17, в сообщении № 1
как можно измерить скорость работы той или иной формулы
У меня есть надстройка для измерения времени выполнения формул
Автор - Serge_007
Дата добавления - 03.03.2015 в 21:07
Michael_S Дата: Среда, 04.03.2015, 06:33 | Сообщение № 9
Группа: Друзья
Ранг: Старожил
Сообщений: 2012
Репутация: 373 ±
Замечаний: 0% ±

Excel2016
Цитата Russel, 02.03.2015 в 11:17, в сообщении № 1
Факультативно: в СУММПРОИЗВ() лучше использовать ссылки на весь столбец или как в примере выше - смещать диапазон?
весь столбец - это миллион (64 000) ячеек, и все они будут проверяться... как думаете, это быстрее, чем часть?
 
Ответить
Сообщение
Цитата Russel, 02.03.2015 в 11:17, в сообщении № 1
Факультативно: в СУММПРОИЗВ() лучше использовать ссылки на весь столбец или как в примере выше - смещать диапазон?
весь столбец - это миллион (64 000) ячеек, и все они будут проверяться... как думаете, это быстрее, чем часть?
Автор - Michael_S
Дата добавления - 04.03.2015 в 06:33
Russel Дата: Среда, 04.03.2015, 11:48 | Сообщение № 10
Группа: Друзья
Ранг: Старожил
Сообщений: 1394
Репутация: 320 ±
Замечаний: 0% ±

Excel 2010
Прогнал макрос на тестовом файле (прилагается) с разным объемом информации. Результаты:
1. 100 строк - суммпроизв(смещ()) медленнее чем суммеслимн() в 6 раз 0,1875 и 0,03125 сек соответственно;
2. 2000 строк - в 3,5 раза - 0,265625 и 0,078125 сек соответственно;
3. 40000 строк - в 1,12 раза - 5,015625 и 4,453125 сек соответственно.
К сообщению приложен файл: 4975369.xlsx (53.5 Kb)

QIWI 9173973973
 
Ответить
СообщениеПрогнал макрос на тестовом файле (прилагается) с разным объемом информации. Результаты:
1. 100 строк - суммпроизв(смещ()) медленнее чем суммеслимн() в 6 раз 0,1875 и 0,03125 сек соответственно;
2. 2000 строк - в 3,5 раза - 0,265625 и 0,078125 сек соответственно;
3. 40000 строк - в 1,12 раза - 5,015625 и 4,453125 сек соответственно.
Автор - Russel
Дата добавления - 04.03.2015 в 11:48
Мир MS Excel » Вопросы и решения » Вопросы по Excel » Измерить скорость работы формулы (Формулы/Formulas)
  • Страница 1 из 1
  • 1
Поиск:

Яндекс.Метрика Яндекс цитирования
© 2010-2025 · Дизайн: MichaelCH · Хостинг от uCoz · При использовании материалов сайта, ссылка на www.excelworld.ru обязательна!