Ускорение работы — часть вторая

Давно ничего не писал, был занят на новой работе. Попытаюсь теперь реабилитироваться, период адаптации прошел, самые срочные вопросы решены, так что можно приняться за то, что я люблю больше всего - за Excel. Итак, сегодняшняя тема - ускорение работы.

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

Итак, для начала надо знать, что Excel пересчитывает все формулы на листе (а точнее, в книге) при изменении листа. Когда таблицы несложные, это незаметно. Но вот у меня сейчас этих таблиц достаточно много и во многих есть формулы массива, плюс подстановочные таблицы используют некоторые сложные функции. Немного изменишь формулу в одной ячейке и на тебе, Excel на пару секунд отключается из этого мира. Какой из этого следует вывод? Надо или отключить этот пересчет или сделать формулы попроще.

Чтобы отключить пересчет нужно зайти на вкладку "Формулы", там есть группа "Вычисление", нужно задать установить в Параметрах вычислений режим "Вручную". Разумеется, это надо делать только на период, пока таблица конструируется, т.е. когда формулы изменяются. Потом уже можно переключиться в автоматический режим, когда вы уже вовсю пользуетесь своими таблицами.

Насчет формул попроще у меня следующие методы:

1. Стараюсь пореже использовать формулы массива (тема, которую я раскрыл в своем самоучителе). Они тяжеловесы и жрут много памяти. Поэтому я постарался их оставить только там, где без них никак, заменив простыми, хотя и двух-трехэтажными формулами.
2. С удивлением обнаружил, что условное форматирование тоже активный потребитель памяти. Оставил только на таблицах проверки данных (это один из приемов, все хочу его расписать, но никак не могу воткнуть в какую-нибудь общую тему).
3. Есть таблицы, которые вытаскиваются из каких-либо отчетов. Т.е., получается много ссылок ,которые подгружают данные при каждом открытии. Чтобы сильно не грузиться, взял старые отчеты, объединил в одном файле и тем самым убрал много формул. Убрал путем замены формул просто данными (Спецвставка - значения).Есть риск, что старые отчеты все-таки будут где-нибудь изменены, а у меня тогда будут устаревшие данные. Но на этот случай подготовил через сводные таблицы контрольные операции. Их можно запускать раз в месяц на предмет соответствия данных, а пока что пойдет. В сумме должна быть экономия времени нехилая.
4. Постарался не задействовать в формуле слишком большие диапазоны. Например, для сводных таблиц я раньше указывал целые столбцы, но теперь приходится указывать размеры адекватные (если объем таблицы данных не меняется, то точные).
5. Использую более простые формулы. Навскидку: вместо ВПР иногда применяю ИНДЕКС. Причина проста - ИНДЕКС просто вытаскивает данные, а ВПР обрабатывает весь массив.

Эксель Практик

«Глаза боятся, а руки делают»

 P.S. Понравилась статья? Подпишитесь на рассылку в правой части страницы (Бесплатный курс "Топ-10 инструментов Excel") и будьте в курсе новых событий.