Досить часто у користувачів після тривалої роботи в файлі виникає проблема: при зміні умови фільтрації (Дані (Data) -Фільтр (Filter)) файл замислюється на хвилину, а часом і не на одну. У цій статті я вирішив розглянути кілька причин такої поведінки файлу і як їх усунути. При цьому описані методи так само справедливі і для випадків, коли файл гальмує не тільки при фільтрації, але і при інших змінах на аркуші:
- Велика кількість формул і функцій на аркушах
Найчастіша причина - наявність великої кількості формул на аркуші. Це можуть бути і ВПР (VLOOKUP) з великим діапазоном даних і СУММПРОИЗВ (SUMPRODUCT). і будь-які інші в різних поєднаннях. Так само значно гальмувати файл можуть формули масиву, навіть якщо вони не складні. Додати гальм в цьому випадку можуть ще й так звані "летючі" функції, серед яких частіше за інших в файлах використовуються: ДВССИЛ (INDIRECT). Зміщені (OFFSET). ОСЕРЕДОК (CELL). Чому летючі і чим вони погані? Зазвичай функції перераховуються тільки в тому випадку, якщо змінена сама функція або будь-який аргумент цієї функції (іншими словами тільки якщо змінені осередки, які впливають на результат функції). Летючі ж функції перераховуються при будь-якій зміні в книзі (в тому числі сортування і фільтрація), незалежно від того, чи вплине це на розрахунок самої функції.
Тому якщо файл напханий формулами і гальмує, то перше, що треба зробити це знайти ті формули, розрахунки яких більше не потрібні і перетворити їх в значення: Як видалити в осередку формулу, залишивши значення ?. Оптимальний варіант: зробити копію файлу, замінити всі формули значеннями і переглядати дані в ньому. Так само можна замінити ту частину формул, розрахунки яких не потрібні для виконання поточного завдання.
Якщо цей варіант не підходить, то необхідно переконатися, а чи немає летючих функцій? Це можна зробити просто за допомогою пошуку: Ctrl + F. знайти (find what) -Ім'я функції, кнопка Параметри (Options >>) -зняти галку з Осередок цілком (Match case). В цьому випадку треба спробувати оптимізувати розрахунки, відмовившись від таких функцій. Як правило в більшості випадків їх можна замінити іншими функціями, але це зажадає знання самих функцій.
Так само слід пам'ятати, що функції і формули можуть бути не просто на аркуші, а так само міститися в іменованих діапазонах: Іменовані діапазони. Як приклад таких формул: Динамічні іменовані діапазони. Тому так само слід перевірити наявність іменованих діапазонів і вилучити всі невикористовувані. З обережністю видаляйте такі імена - як правило, якщо погано в цьому орієнтуєтеся видаляти їх можна тільки на копіях файлів і тільки після того, як всі формули в книзі замініть значеннями
Ну і звичайно, всі ці варіанти можуть зійтися в одному файлі. В такому випадку у файлі досить буде менша кількість кожного компонента для отримання коктейлю "Глибинний гальмо" :)
Так само раджу ознайомитися зі статтею: Як зменшити розмір файлу. Вона теж може допомогти з усуненням гальм.
