Хитрости »
Основные понятия (23)
Сводные таблицы и анализ данных (9)
Графики и диаграммы (5)
Работа с VB проектом (12)
Power BI и Power Query (14)
Условное форматирование (5)
Списки и диапазоны (5)
Макросы(VBA процедуры) (63)
Разное (38)
Баги и глюки Excel (2)

Файл долго фильтрует строки/тормозит при фильтрации

Довольно часто у пользователей после продолжительной работы в файле возникает проблема: при изменении условия фильтрации(Данные(Data) -Фильтр(Filter)) файл задумывается на минуту, а порой и не на одну. В этой статье я решил рассмотреть несколько причин подобного поведения файла и как их устранить. При этом описанные методы так же справедливы и для случаев, когда файл тормозит не только при фильтрации, но и при других изменениях на листе:

  • Большое количество формул и функций на листах
    Самая частая причина - наличие большого количества формул на листе. Это могут быть и ВПР(VLOOKUP) с большим диапазоном данных и СУММПРОИЗВ(SUMPRODUCT), и любые другие в разных сочетаниях. Так же значительно тормозить файл могут формулы массива, даже если они не сложные. Добавить тормозов в этом случае могут еще и так называемые "летучие" функции, среди которых чаще других в файлах используются: ДВССЫЛ(INDIRECT), СМЕЩ(OFFSET), ЯЧЕЙКА(CELL). Почему летучие и чем они плохи? Обычно функции пересчитываются только в том случае, если изменена сама функция или любой аргумент этой функции(иными словами только если изменены ячейки, которые влияют на результат функции). Летучие же функции пересчитываются при любом изменении в книге(в том числе сортировка и фильтрация), независимо от того, повлияет ли это на расчет самой функции.
    Поэтому если файл напичкан формулами и тормозит, то первое, что надо сделать это найти те формулы, расчеты которых больше не нужны и преобразовать их в значения: Как удалить в ячейке формулу, оставив значения?. Оптимальный вариант: сделать копию файла, заменить все формулы значениями и просматривать данные в нем. Так же можно заменить ту часть формул, расчеты которых не нужны для выполнения текущей задачи.
    Если этот вариант не подходит, то необходимо убедиться, а нет ли летучих функций? Это можно сделать элементарно при помощи поиска: Ctrl+F, найти(find what) -Имя функции, кнопка Параметры(Options>>) -снять галку с Ячейка целиком(Match case). В этом случае надо попробовать оптимизировать расчеты, отказавшись от таких функций. Как правило в большинстве случаев их можно заменить другими функциями, но это потребует знания самих функций.
    Так же следует помнить, что функции и формулы могут быть не просто на листе, а так же содержаться в именованных диапазонах: Именованные диапазоны. Как пример таких формул: Динамические именованные диапазоны. Поэтому так же следует проверить наличие именованных диапазонов и удалить все неиспользуемые. С осторожностью удаляйте такие имена - как правило, если плохо в этом ориентируетесь удалять их можно только на копиях файлов и только после того, как все формулы в книге замените значениями


  • Наличие условного форматирования на листе
    Если в листе есть много условий условного форматирования, это вполне может приводить к медленной работе файла не только при изменения условий фильтрации, но и вообще при любом действии на листе - изменение данных в ячейках, сортировка, пересчет формул и т.д. Происходит это потому, что условное форматирование накладывает формат поверх реального и определяется на основании условий, которые могут определяться расположением и видимостью строк на листе. Поэтому следует убедиться в наличии/отсутствии условного форматирования и понять - а так ли оно нужно в этом файле? Если не знаете где искать условное форматирование: Основные понятия условного форматирования и как его создать
  • Разбиение на печатные страницы
    Очень сильно может замедлить работу файла разбиение листа на печатные страницы, особенно если рабочий диапазон(UsedRange) листа довольно большой. Почему этот режим тормозит? Потому что при каждом изменении на листе Excel будет просматривать каждую строку и столбец и обращаясь при этом к настройкам печати(принтера), перерисовывая границы печати на листе.
    Кто установил эти границы, если я сам этого не делал? По умолчанию после вывода листа на печать, независимо от настроек книги или принтера, Excel отображает линии разбиения листа Excel на печатные листы и не убирает их.
    Убрать печатные страницы можно через параметры:
    Excel 2003:
    Сервис -Параметры -вкладка Вид -Авторазбиение на страницы
    Excel 2007:
    Кнопка Офис -Параметры Excel(Excel options) -вкладка Дополнительно(Advanced) -Показывать разбиение на страницы(Show page breaks)
    Excel 2010-2013:
    Файл -Параметры(Options) -вкладка Дополнительно(Advanced) -Показывать разбиение на страницы(Show page breaks)
  • Структура/Группировка данных
    Как ни странно, но на фильтр очень сильно влияет наличие структуры/группировки на листе. Устанавливается группировка через вкладку Данные(Data) -Группировать(Group) или Данные(Data) -Промежуточные итоги(Subtotal).
    При этом тормоза в файле могу проявиться не сразу, а после добавления определенного количества строк или столбцов.
    Делается группировка обычно для возможности компактно расположить на листе данные, относящиеся к определенной группе или категории. И это действительно удобно, но не стоит увлекаться. Если файл стал тормозить - попробуйте создать его копию и удалить всю структуру и группировки: Данные(Data) -Разгруппировать(Ungroup) -Удалить структуру(Clear Outline). Если это поможет - стоит задуматься: а так ли нужна была структура?

Ну и конечно, все эти варианты могут сойтись в одном файле. В таком случае в файле достаточно будет меньшее количество каждого компонента для получения коктейля "Глубинный тормоз" :)

Так же советую ознакомиться со статьей: Как уменьшить размер файла. Она тоже может помочь с устранением тормозов.


Статья помогла? Поделись ссылкой с друзьями!
  Плейлист   Видеоуроки

Поиск по меткам

Access apple watch Multex Outlook Power Query и Power BI VBA работа в редакторе VBA управление кодами Бесплатные надстройки Дата и время Диаграммы и графики Записки Защита данных Интернет Картинки и объекты Листы и книги Макросы и VBA Надстройки Настройка Печать Поиск данных Политика Конфиденциальности Почта Программы Работа с приложениями Работа с файлами Разработка приложений Сводные таблицы Списки Тренинги и вебинары Финансовые Форматирование Формулы и функции Функции Excel Функции VBA Ячейки и диапазоны акции MulTEx анализ данных баги и глюки в Excel ссылки
Обсуждение: 2 комментария
  1. Oleg:

    Есть еще вариант для варианта БОЛЬШОЕ КОЛИЧЕСТВО ФОРМУЛ И ФУНКЦИЙ НА ЛИСТАХ:
    на время работы с фильтром включить режим вычислений "вручную" (для Excel2010 на ленте "формулы" пункт "параметры вычислений"), после можно вернуть на "автоматически".
    Только следует учесть, что в режиме вычислений "вручную" идет пересчет при сохранении файла.

  2. виталий:

    Ещё в адрес условного форматирования.
    Очень сильно тормозил документ при фильтрации данных, оказалось из-за применённого "перенос строк" в одном из столбцов таблицы.
    Выключение переноса строк исправило проблему и фильтрация по документу стала в секунды

Поделитесь своим мнением

Комментарии, не имеющие отношения к комментируемой статье, могут быть удалены без уведомления и объяснения причин. Если есть вопрос по личной проблеме - добро пожаловать на Форум


Для оформления сообщений Вы можете использовать следующие тэги:
<a href="" title=""> <abbr title=""> <acronym title=""> <b> <blockquote cite=""> <cite> <code> <del datetime=""> <em> <i> <q cite=""> <s> <strike> <strong>

Тренинги

Заказать
Юридическая информация

Использование материалов сайта

Политика Конфиденциальности

ИП Щербаков Дмитрий Валентинович
ОГРНИП: 318502700083307
ИНН: 504013350772

Наши партнеры

Перейти
Перейти

Счетчики

Рейтинг@Mail.ru Яндекс.Метрика
© 2018 Excel для всех   Войти