Приветствуем форумчане, гости сайта и Администрация!
Работаю с большими объёмами данных и давно подошел к черте, где мои решения к задачам устарели и требуют новых подходов. Поэтому решил обратиться к знатокам – наверняка есть варианты проще и быстрее. Буду благодарен за любые идеи. Желательно без макросов.
Задача: Заменить формулы в массиве 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 написал: сжатый файл Excel 2,82 Мб - по правилам форума файлы более 300 кб прикреплять нельзя,
так от вас требуется правилами пример..а не весь файл с реальными данными вариант формулы уже макросом обработать и вставлять только результат.... да и не такие у вас формулы тормозящие...у меня данные на 1500 строках и 300 столбцах и формулы в 20 столбцах и 1500 строках...это основное а еще на формулах разные отчеты ... и все хорошо работает...у вас что конкретно не так?
это только четверть рабочего файла... пример так сказать и уже 9 Мб Не так то, что он медленный и большой. Если удалить столбцы с формулами, то файл около 800 кб всего. Вот и стоит задача как облегчить его хотябы до 2 Мб с сохранением того же функционала, что сейчас в нем. А так как я поставщик этого файла в другие отделы с медленными машинами и ограниченным размером почты, то и вопрос ко мне.
По каждой конкетной формуле нужно смотреть в разные стороны
Отказатья от ТРАНСП - очень грузит. Не нужно в формулах обходить отображение нулей - для этого есть глочка в параметрах листа. А подстановкой пустого текста ("") Вы закладываете возможную ошибку для будущих вычислений (текст в диапазоне чисел) Не нужно бояться макросов. Сейчас что-то оптимизируете, но при таких объемах данных и их увеличении неизбежно вернетесь к сегодняшнему вопросу. Кардинально перестроить данные. Но это так, совет навскидку. Нужно разбираться.
MATCH(z!CV$3;s!$B$1:$B$154;0) вынести в отдельную строку, рядом с заголовком, а MATCH(s!$C$1;s!$C$1:$K$1;0) в отдельный столбец и сослаться на их а не искать тысячи раз одно и тоже.
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 написал: Буду благодарен за любые идеи. Желательно без макросов.
С такой постановкой задачи могу только сказать копайте в сторону 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 написал: это добавит доп. действий для меня перед отправкой
Формулы можно макросом убирать. Должно получиться довольно быстро где-то вот так:
Код
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 написал: Должно получиться довольно быстро где-то вот так:
Спасибо! В макросах не силен: вот здесь останавлявается команда:
Скрытый текст
получаеться это макрос, который на всех листах (скрытые и не скрытые) превращает все данные в значения и сохраняет файл в месте по запросу - верно? таким образом Весь файл должен получиться в пределах 0,1-0,5 мб?
Можно еще вот так попробовать... 1. Убрать на листе z все блоки, в которых Вы рассчитываете по каждому продукту кол-во, кроме одного, где считать "на лету", при выборе е.и. из раскрывающегося списка. А "накопительные" столбцы (с L по P, где "итого") - считать с пом. более сложной формулы. Кол-во столбцов сокращается "в разы".
Смотрите изменения на обоих листах. В столбцах, где "итого" - наверно можно придумать формулу без использования =ТРАНСП(), но что-то не придумывается. М.б., если идеея понравится, кто из форумчан предложит более резвую формулу?
PS Исходил из того, что вряд ли кому требуется одновременно смотреть кол-во одного и того же товара в различных е.и. А даже если и требуется, то проще сделать отдельную таблицу на один товар, где организовать выбор этого товара из списка
До 2-х мб он врядли сократится, как не изворачивайся. Был в недавнем времени опыт сокращения файла с 18 мб до неполных 7: - убирались все дублирующие расчеты - снес все УФ - всяческие невидимые графические объекты , которые юзеры натаскали с других файлов, посносил еще годом ранее - оптимизировал кол-во пользовательских форм и соотв. код их обрабатывающий - по тем данным, по которым обновление нужно не часто, делал выгрузку макросом с внешнего источника по клику - большую часть расчетов теперь выполняется программно - ну и все в таком духе
Для примера - время пересчета формул по вставленному из буфера массиву данных в 50-60 т.строк превышает время обработки этих данных через словари с массивами + создание файла-отчета на несколько листов с отправкой его по почте примерно в два раза.
Михаил Лебедев написал: Можно еще вот так попробовать...
Благодарю!!! С этого варианта файл меньше всего выходит!
В целом от каждого, кто отвечал рекомендации помогли - как идеи, решения и направления для размышлений. Целый год отправлял 10 мб файлы по почте - не решался зайти сюда. Спасибо всем!!!
P.S. На этот сайт еще бы систему "колличество поблагодаривших" добавить отвечающему пользователю)