Страницы: 1
RSS
Быстродействие (скорость работы) в MS Excel
 
Добрый день, подскажите кто знает.

Есть документ, в нем формулы по 4 столбцах. Записей примерно 6000 и около 30 столбцов. Есть сводные таблицы на других листах, гистограммы, ссылки на сводные таблицы, вообщем полный фарш. Документ весит 4 мгб(вроде как немного)
Он как-то очень медленно работает, хочется его оптимизировать.
Просьба дать совет, если я напишу формулы как макросы в vba, этот метод будет эффективнее чем протягивать формулы для новых записей?
Сводные таблицы и форматирование ячеек\текста сильно влияет на производительность?
И есть ли какая нибудь масштабная статья по этому вопросу?

p.s. У книги1 есть запрос к бд oracle, к этой книге1 сделал запрос к таблице в  книге2. Если в книге 2 обновить все данные, в книгу1 подтянуться новые строки?
Изменено: Argo9 - 12.12.2019 11:19:38
 
нельза в данных держать формулы
6000 строк в 4-х колонках формулы - их уже 24 тысячи, это еще не смертельно, но вы уже чуаствуете, что это неудобно
т.е. держать можно, но будьте готовы к тому что они будут иногда пересчитываться и у вас через некоторое время перестанет хватать нервов на ожидание результатов пересчета "при каждом шорохе а файле"
общая рекомендация такая:
как только вы начинаете замечать пересчеты в файле - это повод задуматься, как от этого уйти, потому что дальше - будет все хуже и хуже
как только вы задумались: "может выключить автопересчет" - нужно что-то серьезно менять в файле, отключение автопересчета - это повод в самый неподходящий момент забыть что он отключен и принять решение на основании недостоверных результатов расчетов (обьявить заказчику стоимость проекта, еще хуже написать ее в письме)
Программисты - это люди, решающие проблемы, о существовании которых Вы не подозревали, методами, которых Вы не понимаете!
 
Цитата
Argo9: Документ весит 4 мгб
у меня весит иногда по 5-7 метров и вполне приемлимо работает. Другое дело, что размер далеко не всегда показатель скорости…
Цитата
Argo9: если я напишу формулы как макросы в vba, этот метод будет эффективнее чем протягивать формулы для новых записей?
если вы про UDF, то обычно, это хуже, а если заменить формулы на пересчёт внутри кода и вставку значений макросом, то я так и делаю (обычно - для "тяжёлых" функций типа ВПР)
Совет: если вы тянете, что-то по ключу (ВПР или ИНДЕКС+ПОИСКПОЗ), то всегда лучше в отдельном столбце протянуть ПОИСКПОЗ, а потом тянуть нужные столбцы просто ИНДЕКСом со сыылкой на ПОИСКПОЗ. На отсортированных данных можно указать дополнительный аргумент и данные будут тянуться вообще мгновенно (я не использую)
Цитата
Argo9: Сводные таблицы и форматирование ячеек\текста сильно влияет на производительность?
сводные обновляются при открытии файла (настраивается) или вручную, так что при обычной работе на скорость влиять не должно. Условное форматирование точно влияет — на строках более 100 тыс. уже стараюсь его не использовать (только в проверочных столбцах 1 на лист/таблицу)

Общий вывод:
  • отключите автопересчёт и пересчитывайте по кнопке, когда это нужно
  • если форма таблиц устаканена, то замените все тяжёлые функции макросами


Цитата
Ігор Гончаренко: как только вы задумались: "может выключить автопересчет" - нужно что-то серьезно менять в файле
категорически не согласен — веду 10 связанных таблиц на отдельных листах, автопересчёт отключен, потому что зачем пересчитывать 10 листов, если я просто ввожу значение в ячейку (заполняю таблицу)
Цитата
Ігор Гончаренко: отключение автопересчета - это повод в самый неподходящий момент забыть что он отключен и принять решение на основании недостоверных результатов расчетов
ну да. А ещё можно удариться об клавиатуру головой и отправить отчёт со случайным набором символов в ячейках  :D что это вообще за аргумент… Лично я уже 2 года как повесил "пересчёт" на ПБД (как и "вставить только значения") и делаю это на автомате через "Alt+2" тогда, когда мне нужно. Разумеется, в макросах для отчётов пересчёт присутствует в коде
Изменено: Jack Famous - 12.12.2019 12:09:22
Во всех делах очень полезно периодически ставить знак вопроса к тому, что вы с давних пор считали не требующим доказательств (Бертран Рассел) ►Благодарности сюда◄
 
А что скажете по поводу визуализации? (форматирования, сводных таблиц, гистограмм?)
А если формулы прописывать в запросе PQ? Это будет лучше чем код в VBA?
 
Цитата
Argo9: А что скажете по поводу визуализации? (форматирования, сводных таблиц, гистограмм?)
сводные практически не делаю, т.к. заменил на их VBA-аналоги (расчёт, компоновка, вставка, форматирование)
Гистограммы использую только "от зелёного в красное" и наоборот — на скорость по ощущениям влияет не сильно, но всё зависит от объёмов

Цитата
Argo9: если формулы прописывать в запросе PQ? Это будет лучше чем код в VBA?
  1. лично я PQ совсем не использую. Отчёты на VBA "уделывают" PQ раз в 5 на моих данных. Тут оценка некорректна, т.к. PQ я почти совсем не знаю, а в VBA уже нахватался всяких приблуд
  2. меня очень сильно бесило, что при любом изменении имени столбцов, их расположения и количества в PQ всё может поехать — знаю, что это как-то фиксится, но лично мне гораздо проще указывать номера контрольных столбцов в константах VBA или вообще завязываться на именованных диапазонах для универсальности
  3. PQ есть далеко не у всех, а у тех, у кого есть, может быть конфликт версий (сделал запрос в новой, а в старой выдаст ошибку) — такая мультиподдержка меня тоже не улыбает))) VBA стар, как мир и пока что мои решения работают даже в 2003 офисе, который всё ещё многие активно юзают

P.S.: дополнил ответ выше
Изменено: Jack Famous - 12.12.2019 12:19:24
Во всех делах очень полезно периодически ставить знак вопроса к тому, что вы с давних пор считали не требующим доказательств (Бертран Рассел) ►Благодарности сюда◄
 
Argo9,  
Цитата
Argo9 написал:
нем формулы по 4 столбцах
Что за формулы?
Цитата
Argo9 написал:
и форматирование ячеек\текста сильно влияет на производительность?
Если ичпользуется условное форматирование - да.
Цитата
Argo9 написал:
И есть ли какая нибудь масштабная статья по этому вопросу?
например https://docs.microsoft.com/en-us/office/vba/excel/concepts/excel-performance/excel-improving-calcuation-performance
Цитата
Argo9 написал:
Если в книге 2 обновить все данные, в книгу1 подтянуться новые строки?
Смотря как сделан запрос.
По вопросам из тем форума, личку не читаю.
 
Цитата
Argo9: есть ли какая нибудь масштабная статья по этому вопросу?
  • Производительность Excel: советы по оптимизации производительности
  • Производительность Excel: улучшения производительности и ограничений
  • Производительность Excel: повышение производительности вычислений
Изменено: Jack Famous - 12.12.2019 12:52:20
Во всех делах очень полезно периодически ставить знак вопроса к тому, что вы с давних пор считали не требующим доказательств (Бертран Рассел) ►Благодарности сюда◄
 
Спасибо за помощь! Буду изучать, надеюсь вопросов не останется.

Jack Famous, вам отдельное спасибо.
 
Argo9, обращайтесь  ;)
Во всех делах очень полезно периодически ставить знак вопроса к тому, что вы с давних пор считали не требующим доказательств (Бертран Рассел) ►Благодарности сюда◄
 
Цитата
Argo9 написал:
надеюсь вопросов не останется
прибавится  :D
По вопросам из тем форума, личку не читаю.
Страницы: 1
Наверх