Страницы: 1
RSS
Большой файл медленно работает
 
Приветствуем форумчане, гости сайта и Администрация!  :)

Работаю с большими объёмами данных и давно подошел к черте, где мои решения к задачам устарели и требуют новых подходов. Поэтому решил обратиться к знатокам – наверняка есть варианты проще и быстрее.
Буду благодарен за любые идеи. Желательно без макросов.  :)

Задача:
Заменить формулы в массиве CV4:PK3000 на более оптимальные для уменьшения размера файла и скорости работы. Нужен новый подход с сохранением всех данных.
Есть 2 варианта расчета через ИНДЕКС + ПОИСКПОЗ ("лист z") и через ВПР "лист z (2)". Оба работают медленно и файл тяжелый для пересылки по почте и открытием на слабых "машинах". Рабочий вариант " лист z (2)" – лучше решения не нашел.
В файл копируются данные - вставкой значений в массив R4:CE3000 с выбором значений в столбцах A:K
Изначально в файле:
• еще 5 листов с расчетами – почти не занимают место и макросы
• пустые столбцы (скрыты) на листах имеют свои данные – очищено, чтобы оставить только смысл задачи. в среднем получается 3000 строк и 500 столбцов
• данными этого файла пользуются несколько людей на разных компах с разным уровнем знания Excel (нужно в любой момент взять разные данные)
• файл отправляется по почте по 5-7 раз в день
• полный файл с заполненными значениями 12 мб и каждый месяц данные растут

На рабочей машине: Excel 2016 x64
OS Win 8.1 x64
Core i5, озу 8 Гб
сжатый файл Excel 2,82 Мб - по правилам форума файлы более 300 кб прикреплять нельзя, поэтому выкладываю на Яндекс диск.
если позволят прикреплю здесь  :)
 
vlad_d,
Цитата
vlad_d написал:
сжатый файл Excel 2,82 Мб - по правилам форума файлы более 300 кб прикреплять нельзя,
так от вас требуется правилами пример..а не весь файл с реальными данными
вариант формулы уже макросом обработать и вставлять только результат.... да и не такие у вас формулы тормозящие...у меня данные на 1500 строках и 300 столбцах и формулы в 20 столбцах и 1500 строках...это основное а еще на формулах разные отчеты ... и все хорошо работает...у вас что конкретно не так?
Изменено: Mershik - 17.01.2020 12:10:35
Не бойтесь совершенства. Вам его не достичь.
 
Оди вопрос - одна тема. А у Вас ТЗ. Можно, но в Работе
 
Цитата
Mershik написал:
у вас что конкретно не так?
это только четверть рабочего файла... пример так сказать и уже 9 Мб
Не так то, что он медленный и большой. Если удалить столбцы с формулами, то файл около 800 кб всего. Вот и стоит задача как облегчить его хотябы до 2 Мб с сохранением того же функционала, что сейчас в нем. А так как я поставщик этого файла в другие отделы с медленными машинами и ограниченным размером почты, то и вопрос ко мне.
 
Цитата
vikttur написал:
Можно, но в Работе
в какую сторону Excel смотреть (помимо макросов) тоже буду благодарен
 
По каждой конкетной формуле нужно смотреть в разные стороны :)

Отказатья от ТРАНСП - очень грузит.
Не нужно в формулах обходить отображение нулей - для этого есть глочка в параметрах листа. А подстановкой пустого текста ("") Вы закладываете возможную ошибку для будущих вычислений (текст в диапазоне чисел)
Не нужно бояться макросов. Сейчас что-то оптимизируете, но при таких объемах данных  и их увеличении неизбежно вернетесь к сегодняшнему вопросу.
Кардинально перестроить данные. Но это так, совет навскидку. Нужно разбираться.
 
CV4
=INDEX(s!$C$2:$K$154;MATCH(z!CV$3;s!$B$1:$B$154;0);MATCH(s!$C$1;s!$C$1:$K$1;0))*R4

MATCH(z!CV$3;s!$B$1:$B$154;0) вынести в отдельную строку, рядом с заголовком, а MATCH(s!$C$1;s!$C$1:$K$1;0) в отдельный столбец и сослаться на их а не искать тысячи раз одно и тоже.
Изменено: БМВ - 17.01.2020 14:06:50
По вопросам из тем форума, личку не читаю.
 
Цитата
vikttur написал:
Отказатья от ТРАНСП - очень грузит.
спасибо
вместо формулы вставил только значения. правда порядок названий часто меняется. а как еще быстро кроме "трансп" протянуть 75 позиций я не знаю... (
файл

Цитата
БМВ написал:
MATCH(z!CV$3;s!$B$1:$B$154;0) вынести в отдельную строку, рядом с заголовком, а MATCH(s!$C$1;s!$C$1:$K$1;0) в отдельный столбец и сослаться на их а не искать тысячи раз одно и тоже.
вынес отдельной строкой вверху значение первой части формулы (литры в паллете), файл существенно облегчился
со столбцом не понял, ведь значения массива R4:CM2156 каждый раз меняются
файл
Изменено: vlad_d - 20.01.2020 09:14:53
 
Цитата
vlad_d написал: вынес отдельной строкой вверху значение первой части формулы (литры в паллете),
- MATCH(s!$C$1;s!$C$1:$K$1;0) ищет номерс трроки каждый раз, эту формулу можно применить один раз для всей строки. Тоже для столбцов.
Изменено: БМВ - 20.01.2020 11:41:24
По вопросам из тем форума, личку не читаю.
 
Цитата
vlad_d написал:
удалить столбцы с формулами
как будто кроме формул удалять нечего :)  
 
Цитата
vlad_d написал:
Буду благодарен за любые идеи. Желательно без макросов.
С такой постановкой задачи могу только сказать копайте в сторону Power Query. И никаких копирований, макросов, сразу лезете в источник, тащите оттуда данные, крутите вертите по всякому и выводите в результате таблицу без формул - весить результирующий файл будет очень скромно, при этом все расчеты будут полностью автоматизированы.
Как ваши расчеты перетащить в PQ? Это либо в ветку работа, либо ознакамливайтесь с азами (благо ссылок тут на форуме уже вагон и маленькая тележка), после чего задавайте конкретные вопросы (а лучше поиском ищите сразу ответы - 95% что они уже есть).
Вот горшок пустой, он предмет простой...
 
Цитата
vlad_d написал:
в среднем получается 3000 строк и 500 столбцов
- заменяйте формулы на значения перед отправкой по почте
Цитата
vlad_d написал:
в среднем получается 3000 строк и 500 столбцов
- у Вас "шахматка". Каким образом она получается? Сотрудник(-ки) заполняет(-ют) вручную? Сколько у Вас полей с "0" получается? М.б. стоит отказаться от "шахматки вручную". Тем более, если ее заполняют несколько сотрудников. Имеет смысл обратить внимание на Power Query.

Цитата
vikttur написал:
Не нужно в формулах обходить отображение нулей - для этого есть галочка в параметрах листа. ...
... или, если не везде нужно "" вместо "0", можно выбрать формат "финансовый" , а в нем заменить вывод 0 в виде "-" на вывод 0 в виде "".
Например - вот такой: _-* # ##0,00\ _₽_-;-* # ##0,00\ _₽_-;""
Всё сложное - не нужно. Всё нужное - просто /М. Т. Калашников/
 
Цитата
БМВ написал:
- MATCH(s!$C$1;s!$C$1:$K$1;0) ищет номер строки каждый раз, эту формулу можно применить один раз для всей строки. Тоже для столбцов.
... пожалуйста покажите в файле - не понял ((

Цитата
artyrH написал:
как будто кроме формул удалять нечего
Расскажите пожалуйста подробнее, что удалили?
Увидел только, что добавили формулы: Массив R3:CE3 и удалили строки. При удалении строк это поможет только на 2-3 месяца. К концу года строк более 3000 шт. И формулы снова разрастутся, как и размер файла.

Цитата
PooHkrd написал:
С такой постановкой задачи могу только сказать копайте в сторону Power Query.
С этой надстройкой знаком очень мало. Делаю в ней самые простые вещи. На будущее переработать файл - альтернатива хорошая. Пока только формулами.

Цитата
Михаил Лебедев написал:
- заменяйте формулы на значения перед отправкой по почте
не вариант - это добавит доп. действий для меня перед отправкой:
* часть листов под паролём (которых тут нет)/часть столбцов и ячеек под паролем

Цитата
Михаил Лебедев написал:
- у Вас "шахматка". Каким образом она получается? Сотрудник(-ки) заполняет(-ют) вручную? Сколько у Вас полей с "0" получается?
1. делаю заявки в другой таблице
2. копирую значение в массив R4:CE3000 – "что отгружать"
3. автоматом в отсутствующие столбцы проставляются другие значения
4. Выбираю значения в столбцах (сейчас они пустые), по которым сотрудники знают "кому отгружать"
5. Поле с "0" только одно - под запас на новый расчет. все остальное заполнено

Цитата
vikttur написал:
Не нужно в формулах обходить отображение нулей - для этого есть галочка в параметрах листа.
скажите пожалуйста где эта галочка?

Цитата
Михаил Лебедев написал:
Например - вот такой: _-* # ##0,00\ _₽_-;-* # ##0,00\ _₽_-;""
 
Цитата
vlad_d написал: скажите пожалуйста где эта галочка?
Цитата
vikttur написал: глочка в параметрах листа
Файл-Параметры-Дополнительно-Для листа...
 
vikttur, благадарю!
 
Цитата
Михаил Лебедев написал:
- заменяйте формулы на значения перед отправкой по почте
Цитата
vlad_d написал:
это добавит доп. действий для меня перед отправкой
Формулы можно макросом убирать. Должно получиться довольно быстро где-то вот так:
Код
Option Explicit

Sub formuli()
    Dim shX As Worksheet
    
    Application.DisplayAlerts = False
    Application.ScreenUpdating = False
    Application.EnableEvents = False
    Application.Calculation = xlCalculationManual
    
    'Пересохраняю книгу под новым именем
    ThisWorkbook.SaveAs Left(ThisWorkbook.FullName, Len(ThisWorkbook.FullName) - 5) & "ДЛЯ ОТПРАВКИ" & Right(Left(ThisWorkbook.FullName, 5)), ThisWorkbook.FileFormat
    
    'Убираю формулы
    For Each shX In ThisWorkbook.Worksheets
        If shX.Name = "Лист с паролем1" Or shX.Name = "Лист с паролем2" Then
            shX.Unprotect "ПарольЛистов1и2"
            shX.UsedRange.Value = shX.UsedRange.Value
            shX.Protect "ПарольЛистов1и2"
        ElseIf shX.Name = "Лист с паролем3" Then
            shX.Unprotect "ПарольЛиста3"
            shX.UsedRange.Value = shX.UsedRange.Value
            shX.Protect "ПарольЛиста3"
        Else
            shX.UsedRange.Value = shX.UsedRange.Value
        End If
    Next shX
    
    Application.DisplayAlerts = True
    Application.ScreenUpdating = True
    Application.EnableEvents = True
    Application.Calculation = xlCalculationAutomatic
    
    ThisWorkbook.Save
End Sub
Изменено: Wiss - 21.01.2020 14:47:03
Я не волшебник, я только учусь.
 
Цитата
Wiss написал:
Должно получиться довольно быстро где-то вот так:
Спасибо!
В макросах не силен: вот здесь останавлявается команда:
Скрытый текст

получаеться это макрос, который на всех листах (скрытые и не скрытые) превращает все данные в значения и сохраняет файл в месте по запросу - верно? таким образом Весь файл должен получиться в пределах 0,1-0,5 мб?
 
Можно еще вот так попробовать...
1. Убрать на листе z все блоки, в которых Вы рассчитываете по каждому продукту кол-во, кроме одного, где считать "на лету", при выборе е.и. из раскрывающегося списка. А "накопительные" столбцы (с L по P, где "итого") - считать с пом. более сложной формулы. Кол-во столбцов сокращается "в разы".

Смотрите изменения на обоих листах.
В столбцах, где "итого" - наверно можно придумать формулу без использования =ТРАНСП(), но что-то не придумывается. М.б., если идеея понравится, кто из форумчан предложит более резвую формулу? :)

PS
Исходил из того, что вряд ли кому требуется одновременно смотреть кол-во одного и того же товара в различных е.и.
А даже если и требуется, то проще сделать отдельную таблицу на один товар, где организовать выбор этого товара из списка
Всё сложное - не нужно. Всё нужное - просто /М. Т. Калашников/
 
До 2-х мб он врядли сократится, как не изворачивайся. Был в недавнем времени опыт сокращения файла с 18 мб до неполных 7:
- убирались все дублирующие расчеты
- снес все УФ
- всяческие невидимые графические объекты , которые юзеры натаскали с других файлов, посносил еще годом ранее
- оптимизировал кол-во пользовательских форм и соотв. код их обрабатывающий
- по тем данным, по которым обновление нужно не часто, делал выгрузку макросом с внешнего источника по клику
- большую часть расчетов теперь выполняется программно
- ну и все в таком духе

Для примера - время пересчета формул по вставленному из буфера массиву данных в 50-60 т.строк превышает время обработки этих данных через словари с массивами + создание файла-отчета на несколько листов с отправкой его по почте примерно в два раза.
 
Цитата
Михаил Лебедев написал:
Можно еще вот так попробовать...
Благодарю!!!  :)  С этого варианта файл меньше всего выходит!

В целом от каждого, кто отвечал рекомендации помогли - как идеи, решения и направления для размышлений.
Целый год отправлял 10 мб файлы по почте - не решался зайти сюда.
Спасибо всем!!!  :)

P.S. На этот сайт еще бы систему "колличество поблагодаривших" добавить отвечающему пользователю)
Изменено: vlad_d - 24.01.2020 11:41:29
Страницы: 1
Наверх