Хитрости »

Основные понятия (27) Сводные таблицы и анализ данных (10) Графики и диаграммы (5) Работа с VB проектом (12) Power BI и Power Query (23)

Условное форматирование (5) Списки и диапазоны (5) Макросы(VBA процедуры) (68) Разное (43) Баги и глюки Excel (5)

---

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

Довольно часто у пользователей после продолжительной работы в файле возникает проблема: при изменении условия фильтрации(Данные(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). Если это поможет - стоит задуматься: а так ли нужна была структура?

---

И отдельно буду публиковать подборку советов из комментариев к статье:
• перенос текста на строки тоже может быть причиной тормозов. Поэтому лучше его не выставлять там, где он не нужен(например, для ячеек с текстом, который помещается в ячейке и так). Где искать: вкладка Главная -Переносить текст или правая кнопка мыши -Формат ячеек -вкладка Выравнивание -Переносить текст.
• фильтр тормозит внутри "умной таблицы"(Вставка(Insert) -Таблица(Table)). Если тормоза проявляются внутри умных таблиц, или при работе с ними, или в формулах, ссылающихся на них - то имеет смысл проверить работу файла без этих таблиц. Для этого необходимо все умные таблицы преобразовать в обычные: правая кнопка мыши по любой ячейке умной таблицы -Таблица(Table) -Преобразовать в диапазон(Convert to Range).
  Рекомендую проделывать это все на копии файла, т.к. восстанавливать некоторые возможности умных таблиц после из удаления может быть не так просто

---

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

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

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

---

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

---

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

Access apple watch Multex Power Query и Power BI VBA управление кодами Бесплатные надстройки Дата и время Записки ИП Надстройки Печать Политика Конфиденциальности Почта Программы Работа с приложениями Разработка приложений Росстат Тренинги и вебинары Финансовые Форматирование Функции Excel акции MulTEx ссылки статистика