Поиск  Пользователи  Правила  Войти
Форумы "Планета Excel" » Вопросы по Microsoft Excel
Страницы: 1
RSS
Cкорость работы при больших вычислениях VBA и стандартных формул эксель, VBA и стандартные формулы
 
#1
12.02.2019 11:08:39
Добрый день!
Вопрос не по формулам, а по оптимизации.
Столкнулся на работе со следующей проблемой:
Есть файл, который содержит кучу информации:
Лист1 - персональные данные, 1200х25 ячеек;
Лист2 - выгрузки из базы. В среднем 400х325 ячеек;
Лист3 - перечень организаций. 400х5 ячеек;
Лист4 - промежуточный арифметический. Переводит данные из выгрузки в данные для финального расчёта. Каждая ячейка - формула, содержащая всякие "Если", Индексы и пр. 1200х70 ячеек;
Лист5 - вывод данных по определенной категории пользователей. Все ячейки - ВПР, индексы, если и пр. 1200х55 ячеек;
Лист6 - лист с выводом статистической информации

Вот. Всё это сейчас весит 4.5Мб, и довольно долго начинает грузить. В этом году планируется увеличение кол-ва пользователей до 2.5-3 тысяч. Боимся, что работа станет очень затруднительной.
В чём вопрос: правильно ли я понимаю, что основной вес и время отклика связаны с большим кол-вом формул, которые как-бы постоянно считаются? Если всё это перевести в VBA, и запускать обновление только тогда, когда это надо нам, избежим ли мы коллапса?

P.S. Думал сделать просто второй эксель, который будет выводить основную информацию из первого, но, пока не разбирался из-за чего, данные в новом файле не обновляются без открытия старого файла, что нивелирует эту идею. Возможно, проблема в том, что файлы лежат на сетевом диске, хотя и в одной папке.
Изменено: bertz - 12.02.2019 11:13:13
 
 
 
#2
12.02.2019 13:03:44
Цитата
bertz написал:
вес и время отклика связаны с большим кол-вом формул
Не факт, может куча каких-нибудь объектов на листе (при копировании из другого файла или отчётов различных программ бывают несколько тысяч мелких непонятных объектов).
Цитата
bertz написал:
избежим ли мы коллапса?
Если действительно висит из-за формул, то избежите. Но при этом не забывайте макросами обновлять отчёты.
Цитата
bertz написал:
Всё это сейчас весит 4.5Мб
Многовато. Видимо куча какого-то форматирования или ещё чего.
Мастерство программиста не в том, чтобы писать программы, работающие без ошибок.
А в том, чтобы писать программы, работающие при любом количестве ошибок.
 
 
 
#3
12.02.2019 22:20:58
Цитата
bertz написал:
что основной вес и время отклика связаны с большим кол-вом формул, которые как-бы постоянно считаются?
Да, проблема в большом количестве формул. Не смотря на то, что в Excel'е много ячеек на листе, во все эти ячейки нельзя записывать формулы, т.к. всё зависнет.
Значит для Вашей задачи не подходит тот способ, который Вы выбрали изначально.
Если заменить формулы макросами, то будет всё нормально.
Еще можете перевести формат "xlsx" в "xlsb" - размер файла уменьшится и будет быстрее работать файл.
Есть еще Power Query, который может быть Вам поможет для Вашей задачи.
 
 
 
#4
12.02.2019 22:44:40
bertz, частично согласен с Alemox и Karataev но чтоб сказать надо смотреть файл. указанные размеры таблиц не сильно большие для современных мощьностей, но даже супер комп можно поставить на колени написав формулы безумно и без оптимизации.  Вот https://www.planetaexcel.ru/forum/index.php?PAGE_NAME=message&FID=1&TID=113968&TITLE_SEO=113968-uskorenie-makrosa-avtomaticheskoy-vstavki-daty&MID=946931#message946931, когда очень незначительные изменения ускоряют в разы. Нет ли условного форматирования? Это тоже не разгоняет расчет. Короче. Макрос не панацея, также как и формулы не всегда спасение.
По вопросам из тем форума, личку не читаю.
 
 
 
#5
12.02.2019 22:59:18
БМВ, могу ошибаться, но мне кажется самый мощный компьютер будет тормозить, если во все ячейки листа записать формулы. То есть в самом Excel'е мне кажется проблема.
Но это только предположения.
 
 
 
#6
12.02.2019 23:09:32
Karataev, 16E9 ячеек это не мало, но ведь и не про такие объемы таблиц ТС говорит, Указанные объемы вполне приемлемы. Я ежемесячно обрабатывал статистические данные по заявкам  - 20-30к строк. примерно по 20мб файлы, формулы конечно не в 50 столбцах но в 10ке были и ничего, даже при импорте данных в шаблон.
По вопросам из тем форума, личку не читаю.
 
 
 
#7
12.02.2019 23:30:04
Но 150 000 ячеек с формулами - многовато всё же.
 
 
 
#8
12.02.2019 23:48:01
Юрий М, Ну вот поднял архивный файлик, 20мб. исходные из 1с A1:S30334
вторая таблица от A2:O30331 просто ссылка на соседний лист и потом еще 11 столбцов с несложными формулами преобразования, расчета времени по часовым поясам … с учетом ночных , стоимости исходя из категории и ночного коэф и прочего, с обращением в справочник в другом файле.
даже 30к*11 это 300к формул. Вывод , не в количестве, а в качестве дело.

P.S, И не надо на VBA одеяло перетягивать :-)
Изменено: БМВ - 12.02.2019 23:49:05
По вопросам из тем форума, личку не читаю.
 
 
 
#9
12.02.2019 23:52:30
Да я и не спорю ) Но мы же не знаем, что там за формулы. Может половина из них и "хорошие".
 
 
 
#10
12.02.2019 23:55:15
Цитата
bertz написал:
Cкорость работы при больших вычислениях VBA и стандартных формул эксель,
если вчитаться то
скорость работы больших вычислений VBA сравнивается м работой стандартных формул. Хм, понять бы еще что есть стандартные формулы :-)
По вопросам из тем форума, личку не читаю.
 
 
 
Страницы: 1
Наверх