Тормозит эксель при фильтрации
Трудно судить без информации о решаемых задачах, но скорее всего надо
перепроектировать таблицы.
По своему опыту.
Дано:
______________________________________________________________
Полная система бухгалтеского учета для малых предприятий.
Все разделы учета и отчетности для Украины.
Данные собраны за 3 полных года активно работающего предприятия.
______________________________________________________________
Сейчас предриятие не является моим клиентом, но когда мы проектировали и
все ответы не по существу!
перепроектировать таблицы - не вариант, т.к. у меня идёт выгрузка из внутреннего ПО.
удалять формулы - ну а нахрен я их писал?
прикреплять макросы всё равно придётся (хотя я до последнего и боролся с ними) и я это и без поста могу понять.
вопрос в том, из-за чего больше толстеет книга: из-за формулы или из-за значения в ячейке (может, программе пофиг)? и, если из-за формулы, то её сложность важна? скажем:
Не надо быль столь категоричным. Их на Украине много миллионов.
Вот чась данных листа с формулой. Так их хранит Ecxel 2007
Видно что формулы ряда не повторяются. Т.е. введены в несколько ячеек, а хранятся
в одном месте. В других просто ссылаются на это.
Но если формулы ссылаются на "скользящие" диапазоны, то, судя по данным файла,
хранится каждая формула.
И все же, думаю, перепроектированием можно добиться значительного уменьшения размера.
Иногда слышал от клиентов: "Да у нас уже все оптимизировано. Профи работали. "
При ближайшем рассмотрении открывались резервы для дальнейшей оптимизации структур.
Поймите меня правильно, я не занимаюсь саморекламой и не ставлю под сомнение
чью-либо квалификацию. Просто часто, возвращаясь к собственным решениям через
"Вешает" РС именно количество ячеек с функциями, а также нелаконичность функций.
Создайте один файл с функцией ВПР в 100 ячейках и второй файл с тойже функцией, но в 30.000 ячейках. Разница времени пересчёта будет очевидна.
А ещё попробуйте решить туже задачу поиска значений не с ВПР, а с помощью только функции ЕСЛИ!
Не надо быль столь категоричным. Их на Украине много миллионов.
"Вешает" РС именно количество ячеек с функциями, а также нелаконичность функций.
Создайте один файл с функцией ВПР в 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 ячейку или несколько он просто зависал на неопределенное время. Это означает, что как только мы скопировали одну или несколько ячеек, курсор превращается в часики или вертящийся кружок и крутится от нескольких секунд до нескольких минут. А порой и вообще уходит в "нирвану" насовсем, загружая при этом процессор чуть ли не на полную. При этом какой-то зависимости от количества скопированных ячеек или сложности их форматирования и данных не наблюдается. Да и другие действия с ячейками никак не проявляют проблему, можно спокойно вставлять и удалять строки, объекты, диаграммы, редактировать ячейки и формулы.
Проблема была замечена на версиях 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 .
Просто чтобы закрепить ответ (ы):
Щелкните правой кнопкой мыши на имени вашего листа, выберите "Просмотр кода" и вставьте код ниже. После вставки щелкните значок Excel под "Файл" в левом верхнем углу или введите Alt-F11, чтобы вернуться к представлению электронной таблицы.
Это включит автообновление. Не забудьте сохранить файл в формате с поддержкой макросов lie .xlsm.
И Крис использовал этот код (который я только что сделал в 2010 году):
Я прошу настоящих экспертов и гуру прочесть код, потому что я уверен, что это материал высшего качества. Возможно, необъяснимый подсчет понижения в этом ответе может быть обращен вспять, когда люди увидят, что хорошо сделано ниже.
Даникотра использовал упрощенный пример. На самом деле, вы можете сделать это в более общем плане. Предположим, с ActiveSheet для следующего (или некоторого другого объекта листа):
Сохраните диапазон автофильтра. Она имеет .Автофильтр.Фильтры.Подсчитать столбцы и (.Автофильтр.Спектр.Граф /.Автофильтр.Фильтры.Количество) строк, сохраненных в rngAutofilter
Соберите в массив myAutofilters каждое из 4 свойств каждого из.Автофильтр.Фильтры.Подсчитайте элементы автофильтра, стараясь при этом избежать ошибок, определенных приложением.На или.Оператор ложный. (myAutofilters будет reDim'd на количество строк и столбцов в шаге 1)
Выключите фильтр, но сохраните раскрывающиеся списки с помощью.ShowAllData
Теперь автофильтр будет восстановлен в том же диапазоне, в котором он находился до начала работы вашего кода, но с обновленным автофильтром для изменений данных.
Читайте также: