Страницы: 1
RSS
Включение и выключение итоговой строки в умных таблицах
 
Коллеги, добрый день,

Проконсультируйте, пожалуйста.
Имеется файл. Если коротко: он весит 17 мб, формат .xlsx, в нем очень много формул и умных таблиц.

Проблема конкретно с умными таблицами, в которых ~15 тысяч строк. Суть проблемы: если включить/выключить у таблицы указанного размера строку итогов, то это происходит ну прям очень долго, а файл в это время в состоянии not responding. Причем, что вручную, что с помощью VBA, результат один. Application.Calculation = xlManual не помогает.

Вопрос: почему это происходит (очень долгая реакция на выключение total row) и можно ли это как-нибудь вылечить (кроме как отказом от умных таблиц), потому что таких таблиц, теоретически, может быть 26, а мой макрос не может разнести каких-то жалких 600 строк между 4 таблицами даже за полчаса из-за этого.

Важное уточнение: вполне возможно, что дело не только в выключении/включении строки итогов, а вообще в любых манипуляциях с умной таблицей: далее, мой макрос должен будет полностью удалить все строки в таблице с помощью .DataBodyRange.Rows.Delete, а потом перенести новую информацию из источника, и я вполне допускаю, что на данном этапе тоже будет похожая проблема.

Очень жду советов, спасибо.
 
Framed, а зачем (почему) вы используете "умные таблицы"?
 
Михаил Витальевич С.,

1. Требование заказчика;
2. Мне проще ссылаться на диапазоны в VBA, в столбцах с формулами, и т.д.;

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

В файле, помимо всего прочего, есть 13 определенных листов, каждый из которых содержит по 2 умные таблицы. Все эти таблицы одинаковой структуры, изначально все пустые. В определенных столбцах каждой таблицы (всего таких столбцов 5 шт.) прописаны формулы. На нулевом листе (самом первом по списку) в эти столбцы подтягиваются определенные данные из другой таблицы, которая очень редко изменятся (раз в год) и сама по себе не очень большая. Формулы в аналогичных столбцах таблиц следующих листов (1-12) сперва ссылаются на предыдущую таблицу, проверяя по ключу, существовал ли он там ранее, и, в зависимости от этого, показывают определенный результат.

Если короче, суть в том, что все таблицы связаны через формулы, и это очень сильно влияет на определенные события в книге, например, удаление строк, выключение сабтоталов (причем, неважно, удаляю я строки внутри или вне таблицы, или же выключаю/включаю сабтотал в таблице, в которой вообще нет ничего - все равно файл будет виснуть), и так далее, и чем больше файл (~ больше строк в таблицах), тем медленнее эти события реализуются.

Пока решение принял следующее: просто поубирал все формулы, которые ссылаются на предыдущий объект.
Сами формулы и подобные столбцы - требование заказчика.
 
Framed, раз в любом случае работаете с таблицами, то не проще тащить при помощи PQ? Или заказчик на столько ретроград?
Вот горшок пустой, он предмет простой...
 
PooHkrd, к сожалению, так и есть. Конечные пользователи абсолютно не умеют обращаться с этим тулом, но при этом хотят иметь возможность прямо влиять на определенные вещи, к примеру, изменять порядок столбцов в шаблоне, менять их название, прописывать формулы и т.д.
Изменено: Framed - 22.09.2020 19:02:35
 
Цитата
Framed написал:
Application.Calculation = xlManual не помогает
это странно, если учесть, что корень зла - формулы. По F8 проходили код? Какие-то события может еще срабатывали? Отключение EnableEvents не помогает? Да и другие обязательные строки для оптимизации процесса тоже не помешали бы.
Даже самый простой вопрос можно превратить в огромную проблему. Достаточно не уметь формулировать вопросы...
 
Я бы ещё посмотрел есть ли на листа Условное форматирование - если есть убить на всех листах
 
Цитата
Framed написал:
проще ссылаться на диапазоны в VBA
мне кажется, что в VBA сложнее...
Цитата
Framed написал:
методом тыка
Цитата
МЕТОД ТЫКА - метод, основанный на использовании недостаточно достоверных или неполных данных для получения научных или практических выводов. Впервые был применен выдающимся немецким математиком и врачом Гансом Фридрихом Тыком (род. около 1317 г. до н.э., по другим источникам - в 523 г. н.э.), производившим вскрытие Буриданова осла для объяснения причин его смерти. Впоследствии метод и был назван по фамилии ученого.
 
Дмитрий(The_Prist) Щербаков,

Я пробовал всё.
Стандартно ставлю везде:
Application.ScreenUpdate = False
Application.EnableEvents = False
Application.Calculation = xlCalculationManual.

Условного форматирования нет вообще.

По F8 прогонял, никаких событий нет до момента отключения total row в умной таблице (это первое событие, которое приводит к тому, что, если файл большой, то он зависает минуты на 2-3).

Еще, из того, что удалось обнаружить: например, если я заполняю таблицы по порядку, начиная с нулевого листа (~15000 строк), то первая таблица заполняется за 10 секунд, следующие заполняются - 1-2 минуты. При этом, у меня реализована возможность с помощью чекбоксов выбрать, какие именно таблицы заполнять. Так вот, если разрушить эту цепочку, и заполнять не по порядку, а, скажем, первый лист, потом третий, потом пятый, то время заполнения каждой таблицы будет 10-15 секунд; т.е., я хочу сказать, что процесс пойдет гораздо быстрее.

Еще: при условии, что все таблицы заполнены информацией, то видно, что самый долгий отклик на событие у нулевого листа, а самый быстрый - у последнего листа, что, наверное, логично.

И еще: насколько я понял, любое событие на листе заставляет Excel пересчитывать все формулы. Например, даже если я удаляю пустую строку вне заполненной умной таблицы текущего листа (пускай это будет нулевой лист, самый долгий), или отключаю или включаю сабтотал у второй умной таблицы, которая вообще пустая, то файл дико виснет. Чем дальше я от нулевого листа, тем быстрее все это происходит. xlCalculationManual не помогает здесь вообще, почему, я не знаю.

Сейчас, т.к. в текущей версии я потер все формулы, каждый лист заполняется за 12-18 секунд.
Изменено: Framed - 22.09.2020 19:24:46
 
Цитата
Framed написал:
насколько я понял, любое событие на листе заставляет Excel пересчитывать все формулы.
Где-то у вас "летучая" функция.
 
Цитата
Framed написал:
любое событие на листе заставляет Excel пересчитывать все формулы
нет, если отключен пересчет.
Разбиение на печатные страницы не установлено? Фильтры? Группировка и структура? Много ли ячеек с переносом текста на строки? Сохранение файла кодом не происходит после добавления каждого итога?
Даже самый простой вопрос можно превратить в огромную проблему. Достаточно не уметь формулировать вопросы...
 
Дмитрий(The_Prist) Щербаков,

Разбиение не установлено.
Сам автофильтр стоит в каждой таблице, конечно же, но по нему ничего не отфильтровано.
Группировка есть, как по столбцам, так и по строкам, до второго уровня (по строкам скрыты некоторые ключи, о которых я подробнее напишу ниже, по столбцам - некоторые малозначимые колонки).
Переносится ~15000 строк, столбцов ~35. На всякий случай, ниже пишу детально о переносе информации.

Вообще, как переносится всё: во второй строке каждого листа, в соответствующих каждому столбцу таблицы ячейках прописан ключ - название столбца таблицы источника (эта таблица находится в этой же книге, на отдельном листе). Соответственно, один массив я заполняю единожды - это названия столбцов источника; второй массив заполняется ключами из листа, на который я хочу перенести данные. Плюс, по определенным правилам, автоматически определяется по источнику, какие листы вообще заполняются, а какие нет. Когда все исходные условия определены, я запускаю цикл по каждому листу, который должен заполняться, внутри которого каждый раз, опять-таки, с помощью циклов For... Next сравниваются ключи по двум первым массивам. Если есть совпадение, то уже в третий массив я загоняю все данные столбца (например, arrTransfer = tblSource.ListColumns(n).DataBodyRange.Value) и осуществляю .Resize. Перед тем, как цикл перейдет к обработке следующего листа, я стираю второй масив ключей, первый и третий не трогаю.
 
Цитата
Framed написал:
Группировка есть, как по столбцам, так и по строкам, до второго уровня
попробуйте убрать её полностью(можно на копии файла) и проверьте, останутся ли тормоза. Хотя бы чтобы исключить этот фактор. Тоже самое можно и с фильтрами в таблицах - уберите их полностью и проверьте.
Даже самый простой вопрос можно превратить в огромную проблему. Достаточно не уметь формулировать вопросы...
 
Framed, в названии темы ни слова про тормоза, а выглядит так, как будто вы не знаете как включить/отключить строку итогов умной таблицы
Во всех делах очень полезно периодически ставить знак вопроса к тому, что вы с давних пор считали не требующим доказательств (Бертран Рассел) ►Благодарности сюда◄
Страницы: 1
Наверх