Тормозит эксель при фильтрации

Обновлено: 04.07.2024

Трудно судить без информации о решаемых задачах, но скорее всего надо
перепроектировать таблицы.

По своему опыту.

Дано:
______________________________________________________________
Полная система бухгалтеского учета для малых предприятий.
Все разделы учета и отчетности для Украины.
Данные собраны за 3 полных года активно работающего предприятия.
______________________________________________________________

Сейчас предриятие не является моим клиентом, но когда мы проектировали и

все ответы не по существу!

перепроектировать таблицы - не вариант, т.к. у меня идёт выгрузка из внутреннего ПО.
удалять формулы - ну а нахрен я их писал?
прикреплять макросы всё равно придётся (хотя я до последнего и боролся с ними) и я это и без поста могу понять.

вопрос в том, из-за чего больше толстеет книга: из-за формулы или из-за значения в ячейке (может, программе пофиг)? и, если из-за формулы, то её сложность важна? скажем:

Не надо быль столь категоричным. Их на Украине много миллионов.

Вот чась данных листа с формулой. Так их хранит Ecxel 2007

Видно что формулы ряда не повторяются. Т.е. введены в несколько ячеек, а хранятся
в одном месте. В других просто ссылаются на это.

Но если формулы ссылаются на "скользящие" диапазоны, то, судя по данным файла,

хранится каждая формула.

И все же, думаю, перепроектированием можно добиться значительного уменьшения размера.

Иногда слышал от клиентов: "Да у нас уже все оптимизировано. Профи работали. "

При ближайшем рассмотрении открывались резервы для дальнейшей оптимизации структур.

Поймите меня правильно, я не занимаюсь саморекламой и не ставлю под сомнение

чью-либо квалификацию. Просто часто, возвращаясь к собственным решениям через

"Вешает" РС именно количество ячеек с функциями, а также нелаконичность функций.

Создайте один файл с функцией ВПР в 100 ячейках и второй файл с тойже функцией, но в 30.000 ячейках. Разница времени пересчёта будет очевидна.

А ещё попробуйте решить туже задачу поиска значений не с ВПР, а с помощью только функции ЕСЛИ!

Не надо быль столь категоричным. Их на Украине много миллионов.

"Вешает" РС именно количество ячеек с функциями, а также нелаконичность функций.

А ещё попробуйте решить туже задачу поиска значений не с ВПР, а с помощью только функции ЕСЛИ!

пыс пыс: ещё можно сделать неинтелектуальные вложения в новое железо! =) Мне очень помогло!

Пересчет существующих формул. Microsoft Excel пересчитывает те ячейки, которые зависят от других ячеек, содержащих изменившиеся значения. Этот тип пересчета позволяет избежать ненужных вычислений. Пересчет ( полный ) также производится при открытии книги.

Фоновые вычисления. Во время пересчета можно выбирать команды и выполнять ввод чисел или формул. Для выполнения команд или других действий пересчет прерывается и затем возобновляется снова. Если книга содержит большое число формул или листы содержат таблицы данных, а также автоматически вычисляемые функции, то процесс пересчета может затянуться на долгое время.

Известны случаи, когда при попытке скопировать в Excel ячейку или несколько он просто зависал на неопределенное время. Это означает, что как только мы скопировали одну или несколько ячеек, курсор превращается в часики или вертящийся кружок и крутится от нескольких секунд до нескольких минут. А порой и вообще уходит в "нирвану" насовсем, загружая при этом процессор чуть ли не на полную. При этом какой-то зависимости от количества скопированных ячеек или сложности их форматирования и данных не наблюдается. Да и другие действия с ячейками никак не проявляют проблему, можно спокойно вставлять и удалять строки, объекты, диаграммы, редактировать ячейки и формулы.
Проблема была замечена на версиях 2013 и 2016, но нет гарантий, что её нет на других

В чем проблема? Сложно сказать. На момент написания статьи одни подозрения и никакой точной информации. Поэтому отнесем это просто к программной недоработке, которая просто не учитывает особенности некоторых ПК.

К слову сказать, проблема неотображенных ярлыков и как следствие глюков с зависанием при копировании чаще всего проявляется на файлах, выгруженных из 1С. Если файл был создан самостоятельно - 90%, что проблема не проявится.
Так же есть еще один метод, который может сработать - отключить обработку анимации средствами Windows. Для этого переходим в Панель управления (Control Panel) , идем в раздел Система (System) (или Система и безопасность (System & Security) -Система (System)) . Появится еще одно окно, в котором в левой части жмем на ссылку Дополнительные параметры системы (Advanced system settings) . Откроется следующее окно. Идем в пункт Быстродействие (Performance) -Параметры (Settings) . Далее снимаем все флажки, отвечающие за анимацию. Или просто выбираем пункт Обеспечить наилучшее быстродействие (Adjust for best performance) .

Такие действия, кстати, могут повлиять на быстродействие системы в общем, даже если и до этого копировалось в Excel все нормально.

Как автоматически обновить автофильтр Excel при изменении данных?

Вариант использования: я изменяю значение одной ячейки на значение, которое было отфильтровано. Я хочу, чтобы текущая строка исчезла без необходимости делать что-либо еще.

Обмен кода с этим, похоже, тоже помогает (по крайней мере, в Excel 2010):

Я обнаружил, что когда я работал с таблицами, это не сработало. Фильтр был не на листе, а на столе. этот код добился цели

Я также использую VBA/Macro на основе события Worksheet_Change , но мой подход немного отличается . Хорошо, сначала код, а затем объяснения:

(Используйте комбинацию клавиш Alt + F11, чтобы отобразить панель разработки, и вставьте код на лист, содержащий фильтр, который вы хотите автоматически обновить.)

Поле является целочисленным смещением диапазона. В моем случае у меня только фильтр с одним столбцом, а диапазон создается одним столбцом (L), который является первым в диапазоне (поэтому я использую 1 в качестве значения).

Критерии - это строка, описывающая фильтр, применяемый к диапазону данных. В моем примере я хочу показать только те строки, где столбец L отличается от 0 (поэтому я использовал «<> 0»).

Щелкните правой кнопкой мыши на имени вашего листа, выберите "Просмотр кода" и вставьте код ниже. После вставки щелкните значок Excel под "Файл" в левом верхнем углу или введите Alt-F11, чтобы вернуться к представлению электронной таблицы.

Это включит автообновление. Не забудьте сохранить файл в формате с поддержкой макросов lie .xlsm .

Просто чтобы закрепить ответ (ы):

Это включит автообновление. Не забудьте сохранить файл в формате с поддержкой макросов lie .xlsm.

И Крис использовал этот код (который я только что сделал в 2010 году):

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

Даникотра использовал упрощенный пример. На самом деле, вы можете сделать это в более общем плане. Предположим, с ActiveSheet для следующего (или некоторого другого объекта листа):

Сохраните диапазон автофильтра. Она имеет .Автофильтр.Фильтры.Подсчитать столбцы и (.Автофильтр.Спектр.Граф /.Автофильтр.Фильтры.Количество) строк, сохраненных в rngAutofilter

Соберите в массив myAutofilters каждое из 4 свойств каждого из.Автофильтр.Фильтры.Подсчитайте элементы автофильтра, стараясь при этом избежать ошибок, определенных приложением.На или.Оператор ложный. (myAutofilters будет reDim'd на количество строк и столбцов в шаге 1)

Выключите фильтр, но сохраните раскрывающиеся списки с помощью.ShowAllData

Теперь автофильтр будет восстановлен в том же диапазоне, в котором он находился до начала работы вашего кода, но с обновленным автофильтром для изменений данных.

Читайте также: