Поиск  Пользователи  Правила  Войти
Форумы "Планета Excel" » Вопросы по Microsoft Excel
Страницы: 1 2 След.
RSS
Размер и жинеспособность файла
 
#1
21.03.2025 17:40:26
Всем привет.
Есть экселька (уже сохранена в xlsb)
Весит при этом 56 метров и работать отказывается, естетсвенно.
Пример с 1 строкой во вложении.
По факту количество строк порядка 40 000
Кол-во столбцов +- как в примере. (бывает на 20 больше примерно)
+ 13 подключений PQ с большим количеством строк - все это на вкладках внутри файла.
Вопрос: что сделать, чтобы это заработало?)
Изменено: yasuki - 21.03.2025 17:41:26
 
 
 
#2
21.03.2025 18:29:34
Я бы отказался от почти 6 млн! 8-0  формул (~40000 строк Х 150 столбцов, для 1го листа!) в пользу макросов
Согласие есть продукт при полном непротивлении сторон
 
 
 
#3
21.03.2025 18:59:10
Цитата
Sanja написал:
Я бы отказался от почти 6 млн!   формул
ну не совсем так.

например столбцы C-H поиск одного и тогоже и вывод из разных столбцов. Я обычно жертвую одним доаолнительным в котором ичщу строку а в нужных столбцах вывожу значение из нужного строка из найденной строки если не ошибка.
аналогично по подобным
Далее Сумма  - если уже не нашли  A4 зачем мучать сто раз сумму и не важно что она выдала 0. Но тут надо больше смотреть на то чего больше, присутствия по условию или отсутствия.

В любом случае, нужно смотреть реальные данные и оптимизировать. Хотя количество формул и правда большое.
По вопросам из тем форума, личку не читаю.
 
 
 
#4
21.03.2025 19:58:26
БМВ, Там (столбцы C-H) вроде ещё и ссылки на целые столбцы, типа "А:АА", что тоже может тормозить...
 
 
 
#5
21.03.2025 20:11:30
Цитата
Сергей Лисицын написал:
Там (столбцы C-H) вроде ещё и ссылки на целые столбцы, типа "А:АА", что тоже может тормозить...
Представленные функции работают с используемой(занятой данными) областью. То что указан целый столбец - не влияет на скорость.
По вопросам из тем форума, личку не читаю.
 
 
 
#6
22.03.2025 18:57:32
Согласен, жестко с количеством формул . количеством строк и столбцов.
но при таком пуле SKU - реально не понимаю как сделать иначе. В другой организации был куб и когда все формулы были вида cubvlaue все работало на ура. И подобный файл весил около 15 метров.
вопоос остается открытым - как быть? (
 
 
 
#7
22.03.2025 19:08:42
Ну как вариант - не собирать всё это в этом файле, а собирать модель из источников этого добра.
Из этого файла оставить список материалов, дат, и требуемых показателей.
И вообще отказаться от Экселя, есть ведь другие инструменты для таких задач. Например Qlik тянет практически всё что угодно.
 
 
 
#8
23.03.2025 07:03:44
Цитата
yasuki написал: вопрос остается открытым - как быть?
Цитата
Sanja написал: в пользу макросов
Не вариант? 40К строк не так много
Согласие есть продукт при полном непротивлении сторон
 
 
 
#9
23.03.2025 10:14:32
Цитата
написал:
Цитата Sanja  написал: в пользу макросовНе вариант? 40К строк не так много

сам не справлюсь с макросом(
 
 
 
#10
23.03.2025 11:28:00
Так что от нас то хотите?
Вам сразу написали
Цитата
В любом случае, нужно смотреть реальные данные и оптимизировать.
Согласие есть продукт при полном непротивлении сторон
 
 
 
#11
23.03.2025 20:04:25
БМВ,
Цитата
написал:
Представленные функции работают с используемой (занятой данными) областью.
Интересно!
А как и где можно узнать, какие функции игнорируют пустые диапазоны, а какие нет?
 
 
 
#12
23.03.2025 22:01:03
Сергей Лисицын,  Да практически все должны работать с UsedRange , но проверено что COUNTIFS  берет все что скормили.  Но все это опытным путем.
По вопросам из тем форума, личку не читаю.
 
 
 
#13
24.03.2025 14:15:32
Цитата
написал:
Сергей Лисицын ,  Да практически все должны работать с UsedRange , но проверено что COUNTIFS  берет все что скормили.  Но все это опытным путем.
а если там ссылка на умную таблицу? разве так не на range работает?
vlookup и xlookup тоже по идее только на range.

Цитата
написал:
Вам сразу написалиЦитатаВ любом случае, нужно смотреть реальные данные и оптимизировать.
нууу... ввиду политик конфиденциальности, я не могу шарить реальные данные.
самостоятельно макросы, конечно же, не изучу быстро.

насколько может помочь удаление полностью из книги запросов PQ и тянуть данные лукапами и суммпроизв (вместо суммесли) из отдельно лежащего файла?
а, и чем заменить суммеслимн, чтобы работало на закрытом файле? суммесли через суммпроивз, а МН - непонятно
 
 
 
#14
24.03.2025 14:24:36
Цитата
yasuki написал:
суммесли через суммпроивз, а МН - непонятно
- так СУММПРОИЗВ() ведь. Но не стандартно, а перемножением условий.
Ну т.е. как применили в роли суммесли, только добавьте условий их умножая.
Изменено: Hugo - 24.03.2025 14:42:25
 
 
 
#15
24.03.2025 14:27:57
Цитата
yasuki написал:
а если там ссылка на умную таблицу?
тут обратная сторона медали, работа с ним по опыту удобнее, но замедляется. Ведь нужно диапазон вычислять.

Цитата
yasuki написал:
насколько может помочь удаление полностью из книги запросов PQ
скорее усугубит.

Цитата
yasuki написал:
я не могу шарить реальные данные.
ну тут мы бессильны . либо надо сомтреть что и как ( на реальных или измененных данных) или только догадыватся где собака зарыта.
По вопросам из тем форума, личку не читаю.
 
 
 
#16
24.03.2025 14:35:26
Цитата
написал:
ну тут мы бессильны . либо надо сомтреть что и как ( на реальных или измененных данных) или только догадыватся где собака зарыта.
Попробую уточнить насколько критично предоставить старые данные, но, чтобы табличка была релевантна.
думаю только к концу недели будет инфа.
тему пока не закрывайте, плз ;)
 
 
 
#17
24.03.2025 14:40:53
yasuki, сделайте дома подобие, данные можно придумать.
 
 
 
#18
24.03.2025 15:27:52
Цитата
БМВ написал:
Представленные функции работают с используемой(занятой данными) областью.
Вот как раз и не все))) Ко мне на работе периодически подходят с вопросом, подобным вопросу автора темы. И очень часто помогает (или, по крайней мере, значительно уменьшает тормоза) замена ВПР(ххх;А:С;3;0) на ВПР(ххх;А$1:С$99999;3;0), где 99999 - минимальное число, заведомо большее количества строк, необходимых для поиска в ВПР. То же касается и ПОИСКПОЗ.
Если таких ВПР немного, ну, до 100 штук, то более-менее нормально все, а вот если количество в тысячах, то так делать точно не нужно
Скажи мне, кудесник, любимец ба’гов...
 
 
 
#19
24.03.2025 16:18:32
Цитата
Сергей Лисицын написал:
как и где можно узнать, какие функции игнорируют пустые диапазоны, а какие нет?
В первую очередь, читать здесь и соседние разделы.
Изменено: sokol92 - 24.03.2025 16:19:44
Владимир
 
 
 
#20
24.03.2025 16:29:26
_Boroda_,  Саша, надо уже конкретно сравнивать на разных сборках, но мои и не только мои замеры показывали что эффект от лишнего досих пор присутсвует именно у COUNTIFS .

вот смотри это время 10000 расчетов
=VLOOKUP("123",A:A,1,)       460,9375
=VLOOKUP(123,A:A,1,)         437,5
=VLOOKUP("123",A1:A100,1,)   457,0313
=VLOOKUP(123,A1:A100,1,)     410,1563

для точности еще раз и в другой последовательности
=VLOOKUP(123,A1:A100,1,)     464,8438
=VLOOKUP("123",A1:A100,1,)   574,2188
=VLOOKUP(123,A:A,1,)         523,4375
=VLOOKUP("123",A:A,1,)       511,7188


123 находится  в строке 8 остальной диапазон просто пуст.

на всякий случай на виртуалке не сильно накрученной в фоне крутится куча процессов, по этому разброс будет всегда, но не так он заметен.
По вопросам из тем форума, личку не читаю.
 
 
 
#21
24.03.2025 16:52:01
А ты возьми реальную большую таблицу, столбцов в 30 и строк тысяч на 50. И вторую такую же. В первой пересортируй столбец А и в столбце К выводи ВПР-ом значение из столбца L второй таблицы. У тебя получится 50 000 ВПР. И вот это уже замерь
Скажи мне, кудесник, любимец ба’гов...
 
 
 
#22
24.03.2025 17:17:44
_Boroda_,  Саша, ну сделал не так как ты хотел , просто рандом от 1 до 200 в столбце А на 30000

=VLOOKUP("123",A:B,2,)       9984,375
=VLOOKUP(123,A:B,2,)         683,5938
=VLOOKUP("123",A1:B40000,2,)               10054,69
=VLOOKUP(123,A1:B40000,2,)   746,0938

разницы в том ,указан или не указан ограниченный диапазон практически нет.

для верности

=VLOOKUP("300",A:B,2,)       10300,78
=VLOOKUP(300,A:B,2,)         10683,59
=VLOOKUP("300",A1:B40000,2,)               10210,94
=VLOOKUP(300,A1:B40000,2,)   10808,59
300 расположено в единственном числе и в сааамом конце строка 29960.
По вопросам из тем форума, личку не читаю.
 
 
 
#23
24.03.2025 17:31:20
Значит, влияет наполненность таблицы, ее величина, возможные внутренние ссылки на вычисленные ВПР-ом значения, еще что-нибудь.
Но вот я тебе говорю - как только ко мне приходят с вопросом про тормоза файла, который по идее тормозить не сильно должен, первое, что я делаю - смотрю, чтобы в ВПР не было диапазона на весь столбец. Исправляю и почти всегда помогает. Если не помогает, то в файле еще что-нибудь сидит, типа 100 000 стилей, скрытых имен или автофигур нулевого размера (встречались такие файлы, да)))
Скажи мне, кудесник, любимец ба’гов...
 
 
 
#24
25.03.2025 09:15:22
Даже если я сделаю везде диапазон, а не весь столбец, крайне маловероятно, что это поможет. 40к строк...
тем более данные меняются. Во всех запросах PQ (ну в 80%) кол-во строк может быть меньше или больше.
 
 
 
#25
25.03.2025 09:19:15
Цитата
yasuki написал:
кол-во строк может быть меньше или больше.
Вы внимательно читали?
Цитата
_Boroda_ написал:
где 99999 - минимальное число, заведомо большее количества строк, необходимых для поиска в ВПР
Тем более, что PQ дает умные таблицы, там вообще не нужно считать строки

Цитата
yasuki написал:
Даже если я сделаю везде диапазон, а не весь столбец, крайне маловероятно, что это поможет
Хорошо, уговорили, не делайте. Я, конечно, расстроюсь, но ничего, как-нибудь переживу)))
Скажи мне, кудесник, любимец ба’гов...
 
 
 
#26
25.03.2025 11:20:53
Цитата
написал:
Хорошо, уговорили, не делайте. Я, конечно, расстроюсь, но ничего, как-нибудь переживу)))
буду тестить сегодня вечером. Уговорили! Надеюсь поможет
и еще момент: скрыть/показать нули - влияет на скорость?
Изменено: yasuki - 25.03.2025 11:22:51
 
 
 
#27
25.03.2025 11:26:08
вы б #3  тоже посмотрели. Ну явно много лишних расчетов ,которые заведомо  лишнии на основании предыдущего.
Изменено: БМВ - 25.03.2025 11:29:57
По вопросам из тем форума, личку не читаю.
 
 
 
#28
25.03.2025 11:33:14
Цитата
написал:
вы б #3  тоже посмотрели. Ну явно много лишних расчетов ,которые заведомо  лишнии на основании предыдущего
я думаю до колонки H будут только значения в итоге. 40к SKU будут редко меняться. Можно потом и руками добавить.  
 
 
 
#29
25.03.2025 11:48:48
ну то есть у вас основная проблема в SUMIFS, которых безумно много. Посмотреть бы еще на данные 'C:\Users\yasukki\Desktop\[Freestock.xlsb]Zopen

точнее, если данные хоть как то сгруппированы там, то возможно диапазоны нужно делать динамическими, предварительно один раз просчитав их для каждого дня, но что-то подсказывает что нужно сперва сделать сводную а потом выбирать от туда значения сумм по условиям и координате столбца например.
По вопросам из тем форума, личку не читаю.
 
 
 
#30
25.03.2025 16:59:11
Цитата
написал:
'C:\Users\yasukki\Desktop\[Freestock.xlsb]Zopen
Ну, кстати, возможно еще проблема в том, что я к уже готовой после запроса PQ табличке добавляю различные расчеты. т.е. уже в умной таблице.
пример добавил.
Сам файо Zopen - до колонки H, а потом вычисления
Изменено: yasuki - 25.03.2025 17:00:01
 
 
 
Страницы: 1 2 След.
Читают тему
Наверх