Страницы: 1
RSS
Как оптимизировать перегруженный файл?, Может есть волшебная формула или еще какой фокус, что поможет мне.
 

Добрый день!

Имеется в арсенале файл, в котором из листа «выгрузка» (таблица выгрузки из 1С на 150 тыс. строк и 30 столбцов + 10 столбцов формул) по 10 разным листам вытягиваются данные формулой СУММЕСЛИМН (таких формул немногим меньше 10 тыс. штук, определит простым поиском) и общее количество листов в файле 25).

Да, файл тормозит. Пересчет книги занимает 3-4 минуты. Отключить автоматический пересчет книги конечно дает эффект, но пересчитывать приходится после корректировки 10-20 ячеек.

Понимаю, что создал монстра и возможно было-бы лучше сделать это не в Excel. Но исходя из формата Excel 2016 как можно оптимизировать этого монстра?

Первый уровень оптимизации я сделал – вынес в другой файл 10 листов с формулами СУММЕСЛИМН. То, что осталось – тормозит буквально 1-2 секунды. И после изменения «исходников» для СУММЕЛИМН я открываю дополнительный файл и переключаюсь на почту или еще что.

Буду рад за любой ответ, и за ссылку, и за критику, и за волшеную формулу ☺.

Файл не привожу, т.к. заменить все данные с сохранением функционирующих формул не получится.

P.S. Файл очищен от лишних данных, ссылок, диапазонов (в т.ч. скрытых), объектов, макросов, листов и т.д.

Изменено: Илья - 17.02.2021 15:03:28
 
Цитата
Илья написал:
как можно оптимизировать этого монстра?
все расчеты загнать в Power Query или Power Pivot. Обработка данных будет в 10-ки раз быстрее
 
Илья, верниесь в своё сообщение и привендите его в порядок. Зачем такой огромный шрифт?
 
Цитата
Юрий М написал:
Зачем такой огромный шрифт?
для тебя, только ради тебя   :D

Цитата
Илья написал:
Файл не привожу, т.к. заменить все данные с сохранением функционирующих формул не получится.
а не надо весь, покажите формулы, и чуток данных, которые можете заменить. Хотя количество указанное 10 тыс. скорее говорить о том что количество рачетов одинаково, но. вдруг можно что сделать.
По вопросам из тем форума, личку не читаю.
 
Цитата
Юрий М написал:
Илья , верниесь в своё сообщение и привендите его в порядок. Зачем такой огромный шрифт?
Исключительно по ошибке: после двукратного написания текста в форме сайта и потери всего текста (видимо с безопасностью намудрили) - набрал в Word и в форму отправки просто скопировал.  
 
Тема: Как ускорить то, не видя что.....

Winrar или 7zip в помощь для предоставления файла примера.
Изменено: Marat Ta - 17.02.2021 15:23:32
 
Цитата
БМВ написал:
покажите формулы, и чуток данных, которые можете заменить
1. Формула: Использую вот такие формулы:

=+СУММЕСЛИМН('[_Предприятие_Расшифровка_БП-2021_.xlsb]СВОД'!J:J;'[_Предприятие_Расшифровка_БП-2021_.xlsb]СВОД'!$AB:$AB;20;'[_Предприятие_Расшифровка_БП-2021_.xlsb]СВОД'!$AD:$AD;"Подразделение";'[_Предприятие_Расшифровка_БП-2021_.xlsb]СВОД'!$AC:$AC;$A8)

+СУММЕСЛИМН('[_Предприятие_Расшифровка_БП-2021_.xlsb]СВОД'!J:J;'[_Предприятие_Расшифровка_БП-2021_.xlsb]СВОД'!$AB:$AB;20;'[_Предприятие_Расшифровка_БП-2021_.xlsb]СВОД'!$AD:$AD;"Общ.";'[_Предприятие_Расшифровка_БП-2021_.xlsb]СВОД'!$AC:$AC;$A8)/СУММ(ВА_ГА!C$3:C$4)*ВА_ГА!C$4

2. Приложил размытый скриншот "Промежуточной таблицы" для общего понимания, где используются эти формулы - белые (без заливки) ячейки с янв. 21по 23 год.

3. Лист выгрузка - текстовая таблица на 150 тыс. строк и 30 столбуов, к ней добавил 10 столбцов простейших формул (ВПР, работа с текстом, /1000 и т.д.) по каждой строке.


Цитата
Marat Ta написал:
Как ускорить то, не видя что.....
Согласен, глупо получается. Но заменить такой объем данных на "обезличенные"  не представляю как. Выкладывать файл как есть... лучше закидайте меня чем там обычно закидывают неугодных - так делать нехорошо по отношению к работодателю. Но посмотрев на формулу может кто-то скажет: "... эй, зачем так сложно? используй вот эту связку формул ХХХХХХХ - она работает в разы быстрее..."  
Изменено: Илья - 17.02.2021 15:46:49
 
Сделать требуемый отчет в 1С для специалиста несложно.
Двойная работа - выгружать данные в Excel и обрабатывать там данные.
Имея Мерседес ездить на ....
----
По теме - да сделайте замену нескольких букв на "" -  получится абракадабра в ваших названиях.... никому не будет интересно воровать ваши данные....
Изменено: Marat Ta - 17.02.2021 15:51:40
 
Цитата
Marat Ta написал:
Сделать требуемый отчет в 1С для специалиста несложно.
Согласен. Есть причины это не делать (1. У меня нет ресурсов для запроса на конкретный отчет. 2. Можно почитать и разобраться, как этот отчет делается, но нам не дадут права на это, только стандартные отчеты. 3. Также данные могут меняться от ситуации. Например: "...с понедельника необходимо учитывать еще одну аналитику ..." и добавят 31-й столбец данных с дополнительно аналитикой и отчет нужен новый. 4. Запрос на отчет  у нас - это не 1 месяц, а данные нужно формировать сейчас, что с п. 3. делает его не целесообразным.)
Цитата
Marat Ta написал:
Двойная работа - выгружать данные в Excel и обрабатывать там данные.
Если Excel  - не инструмент обработки данных, то что это?

Изменено: Илья - 17.02.2021 16:05:03
 
Сразу же: указывайте ограниченные диапазоны для суммеслимн, в версиях до 2013 (или позже?) функцию ещё не научили понимать, где конец данных, и она лопатит весь столбец
 
Цитата
vikttur написал:
Сразу же:указывайте ограниченные диапазоны для суммеслимн, в версиях до 2013 (или позже?) функцию ещё не научили понимать, где конец данных, и она лопатит весь столбец
Спасибо. Не знал.
На будущее: если ниже необходимого диапазона строки пустые - их тоже перебирает?  
 
А можно без бездумного цитирования?

Цитата
Илья написал: таблица на 150 тыс. строк и 30 столбуов
Даже простые формулы могут подтормадивать на таких диапазонах... Пора менять на макросы
 
vikttur, Постараюсь.  :)

Формулы менять на макросы? Впервые слышу о таком. Можно пару ссылок для ознакомления с направлением?  
 
Илья,  Вы вводите формулы в ячейки, чтобы увидеть результат вычислений. А макрос скрыто делает вычисления и показывает готовый результат в ячейке. Формул нет на листе, но конечный результат есть в ячейках
Почитайте комментарии в коде ниже

Код
Sub test()
    'вставим какие-то цифры на лист Excel
    Range("A1") = 1
    Range("B1") = 2

    'это ваша формула
    Range("C1").Formula = "=A1+B1" 'результат 3, но внутри ячейки формула. Если таких формул на листе миллионы, то файл будет тормозить
    
    'макрос
    Range("C1") = Range("A1") + Range("B1") 'результат 3 - результат в ячейке есть, а формул нет
End Sub
Изменено: New - 17.02.2021 16:45:49
 
О каких ссылках Вы говорите? Есть формула, которая что-то вычисляет. Но ведь это же самое вычисление может выполнить макрос )
 
Без файла примера - рассматривать задачу и гадать что и как в теории сбудется или нет... пустая трата времени

https://www.planetaexcel.ru/forum/index.php?PAGE_NAME=read&FID=1&TID=137311&...

Вот вам пример темы, подсчет в которой занимал часы ...  
Изменено: Marat Ta - 18.02.2021 05:57:54
Страницы: 1
Наверх