Почему тормозит Excel, 12 способов улучшить работу с файлами!
Добрый день уважаемый читатель!
Эту статью я хочу рассмотреть тему эффективности Excel и узнать почему тормозит Excel, а также предоставить 12 способов улучшить работу файлов Excel! Конечно, если вы используете небольшие таблицы или вам не интересно расширение границ использования Excel, тогда статья будет не интересна, а вот если всё наоборот, приступим….
Когда же возникает такие случаи, когда файлы Excel «тормозят» и их работоспособность стремится к малой величине. Возможно, присланный вам файл очень большой и тяжёлый и поэтому долго сохраняется или он маленький, но всё то же самое… Давайте выясним причины таких проблем и основные способы их победить.
Проблемы с «тормознутостью» MS Office это довольно распространённое явление в работе Excel. Проблемная работа Excel может возникать по многим причинам: излишнее условное форматирование, чрезмерное использование функций Excel, созданные из больших массивов данных сводные таблицы, дополнительные и ненужные строки и столбцы в таблицах, и многое другое.
А теперь перейдем от теории к действию и рассмотрим 12 способов, которые позволит вам, избавится от «тормозов» Excel и начать комфортно работать:
1. Уменьшаем размеры картинок
Самым первым способом уменьшения размера вашего файла, это поработать над картинками в нём. Но способ действителен, если изображения вы всё же использовали.
Когда вы добавляли картинку через меню на панели управления «Вставка» в блоке «Иллюстрации» нажимали «Рисунок», тогда в файл вшивался рисунок с полным объёмом. А вот теперь необходимо внести изменения, установите курсор на картинку, появится новая вкладка «Работа с рисунками» с под вкладкой «Формат». В блоке «Изменения» кликаете на кнопку «Сжать рисунки».
В диалоговом окне «Сжатие рисунков» указываете подходящий вам вариант уменьшения размера и нажимаете «Ок». 
Не стоит забывать о том, что картинки могут скрываться и в примечаниях.
2. Заменим макросами большие формулы
В случаях, когда таблица большая, на десятки тысяч строк, и вмещает в себя много сложных формул, которые ссылаются на разнообразные книги и листы приводят к тому, что тормозит Excel. Для улучшения стоит применять созданные макрорекордерных макросов вместо написанных формул, так как они превратят формулы в скопированные как значения тексты, цифры и значения. Очень поможет в случаях множества действий с файлом, когда производится частое пересчитывание данных всего файла.
3. Убрать форматирование таблиц Excel
В случаях, когда к диапазону данных применено форматирование в виде специальной таблицы, а сами таблицы достигают большого размера, это может привести к значительным сложностям в работе Excel.
Определить как именно отформатированная таблица можно просто, установив курсор на абсолютно любую ячейку, появится дополнительная вкладка в панели управления «Работа с таблицами». Выбираете вкладку «Конструктор», ищете раздел «Сервис», нажимаете кнопку «Преобразовать в диапазон» и подтверждаете, что вам нужно преобразовать вашу таблицу в обычную. 
4. Удаляем ненужные столбцы и строки
Это достаточно распространённая проблема при работе в Excel. При обыкновении пользователь случайно перемещается с помощью горячего сочетания клавиш Ctrl + стрелка вправо или вниз, нажатых случайно, и переносится в конец листа, да так и сохраняет рабочую книгу, при этом значительно ее утяжеляет. Особенно это происходит, когда в конец книги добавляется случайный символ, знак или заливка.
Проверить такой вариант возможно по характерному признаку, это очень маленький ползунок. 
5. Заменить формат рабочей книги на *.xlsb
В случаях, когда ваша работа связана с огромными таблицами и их размер достигает больших объёмов, то следует сохранять рабочие книги в формате *.xlsb. Это расширение хранится как двоичный формат, своеобразный специальный формат для создания и хранения ваших «баз данных» имеющих в основе электронные таблицы.
Когда сохранение будут произведены в этом формате, любой большой файл уменьшится сразу же в 2-3 раза, кстати, скорость вычислений также увеличится на несколько порядков, что будет вас радовать.
6. Убираем чрезмерное условное форматирование
Несмотря на всю полезность условного форматирования, его чрезмерное использование может и привести и к проблемам, ваша рабочая книга может подтормаживать. Это может произойти, даже без вашего ведома, просто в процессе работы вы копируете ячейки вместе с форматами и форматированием, а это не проходит бесследно, они накапливаются незаметно и тормозят работу файла.
Для удаления всего лишнего, для начала, выбираете необходимый диапазон, а можно и весь рабочий лист. Следующим шагом в панели управления, во вкладке «Главная» в разделе «Стили» выбираете пункт с выпадающим меню «Условное форматирование», дальше нужен пункт «Удалить правила» и выбираете нужный подпункт удаления. 
7. Удаляем ненужные данных внутри структуры файла Excel
Я могу поспорить, что далеко не каждый пользователь знает, что файлы Excel представляют собой своеобразный архив и такая структура файла начала свою жизнь с 2007 выпуска. А это означает, что теперь файлы Excel можно открыть архиватором, таким как WinRar. А вот внутри файла и могут находиться файлы, которые, в некоторых случаях значительно тормозят Excel.
Для проведения процедуры по уменьшению размера, перестрахуйтесь, и сделайте резервное копирование вашего файла. Затем откройте файл с помощью контекстного меню, открываем файл, нажав на мышке правую кнопку и выбрав пункт «Открыть с помощью» и с доступных программ выбираете программу архиватор. Или, второй вариант, открываете архиватор и в меню «Файл» кликаете на пункт «Открыть внутри». Результатом любого из вариантов будет открытие файла Excel, как архива с файлами и папками.
В архиве, скорее всего в папке «xl», удалите папки «printerSettings» и «drawings». 
После всех мучений запускаем снова файл и на все системные возмущение соглашаемся кнопкой «Ок», файл восстановиться и запустится.
Обращаю ваше внимание, что если файл содержит рисованные объекты типа кнопок или фигур то удалять всю папку «drawings» не стоит, вместе с ней испарятся и фигурки. Достаточно в папке удалить vmlDrawing.vml который вмещает разнообразную информацию и может достигать больших размеров.
8. Неправильно настроенная печать на принтер
Когда вы не установили принтер по умолчанию и система не может определить настройки текущего принтера, необходимо чётко обозначить, куда будет происходить отправка на печать. Если принтер невозможно опознать, замените драйвер устройства.
Иногда случается так, что стирании настроек принтера из пункта 7, может привести к тому, что неправильные настройки принтера тормозят весь файл.
9. Сменить версию Excel на более позднюю
Ни что не стоит на месте, даже наша планета, так и Excel развивается, ведь совершенству нет предела. Меняется и развивается программный продукт, оптимизируется программный код, что позволяет, иногда значительно, повысить продуктивность работы с таблицами и расчёты формул, быстродействие может увеличиться до 20% в более новых версиях, например в 2016 относительно 2007.
Также частенько встречается ситуация когда Excel версии 2007 не может работать с текущим файлом, а вот в более поздних версиях никаких проблем нет и всё работает отлично.
Усовершенствуйте версию, переходите на более продуктивные продукты MS Office, желательно от 2013 версии и выше.
10. Сносим файл «Personal.*»
Основное назначения сего файла, это хранение информации о предыдущих работах в Excel. Он помогает записывать и запоминать макрос, хранить формат данных для ваших всех книг. Проще говоря он нужен для того что бы из раза в раз не делать те же самые действия по форматированию файла, а при первоначальном открытии все персональные настройки сохраняются в специальный файл и воспроизводятся при открытии.
Проблемы могут возникать, если начнет, тормозит файл персональных настроек. Для устранения проблемы, воспользуйтесь принципом, нет файла, нет проблем. Найдите С:\. \Application Data\Microsoft\Excel\XLSTART или С:\. \Microsoft Office\Office12 и где то в недрах этих папок ищете файл «Personal.*» и сносите его.
11. Производим оптимальную настройку сводных таблиц
Когда вы в сводной таблице ссылаетесь на огромные диапазоны от десятка тысяч строк, она начинает все результаты своих расчётов, которые становятся колоссальными, хранить в себе. И это становится причиной общего торможения при работе с файлом. Для устранения этой причины, установите курсор на таблице, вызовите контекстное меню правой кнопкой мыши, потом выберите пункт «Параметры сводной таблицы» в диалоговом окне на вкладке «Данные» снимите галочку в пункте «Сохранять исходные данные вместе с файлом».
Этими действиями вы можете уменьшить файл практически в два раза.
12. Убираем ненужные объекты
Частенько когда вы переносите или копируете информацию из других рабочих файлов или сайтов, в структуре таблиц могут содержаться разнообразные скрытые объекты типа фигур, рисунков и прочее.
Для удаления таких ненужных объектов необходимо запустить макрос, кликните горячую комбинацию клавиш Alt + F11 для вызова редактора и вставьте код:
Excel зависает и тормозит. Как исправить? Решение
В этой статье расскажу, как я решил проблему с Excel у себя на работе раз и навсегда. А теперь постоянно делюсь этим решением с коллегами, а они в свою очередь подтверждают эффективность данных действий. Изначально проблема заключалась в том, что сотрудники каждый день по несколько раз жаловались на то, что Excel зависает при работе: долго открывает файлы, сохраняет, долго пересчитывает формулы и обновляет ячейки, а также наглухо зависает при перемещении по ячейкам, даже при простом клике, окно белеет и вылезает надпись «Программа не отвечает». Зависания Excel от 1 минуты до 30 минут были частой проблемой и головной проблемой для сисадминов, когда я только перешёл на новую работу, поэтому сразу решил заняться этой проблемой. Делалось это для того, чтобы
Поэтому, если вам поможет моё решение, отпишитесь в комментариях. Итак, я приведу несколько советов в порядке важности, использую которые вы раз и навсегда решите проблема с зависаниями Excel. Если у вас тоже Excel тормозит и зависает при работе с таблицами, воспользуйтесь моими советами в этой статье
Так почему же Excel зависает?
Причин на самом деле много, здесь я перечислю пункты по решению основных проблем по мере своей эффективности по убыванию. Бывают и очень узкие проблемы, но во-первых, эти случаи единичны, а во-вторых, они требуют тщательного изучения и исследования. Поэтому ловите 7 способов решения проблемы с Excel, если он тормозит и зависает.
1. Отключить аппаратное ускорение прорисовки (бесплатно)
Пожалуй, это один из самых простых и бесплатных способов ускорить работу Excel, но про который знает не так уж и много айтишников, не говоря уже про простых пользователей. Итак, если у вас Excel зависает и тормозит, сделайте так:
После этого у вас Excel будет работать быстрее и меньше тормозить. Особенно это даёт результат на компьютерах и ноутбуках со встроенной видеокартой, потому что аппаратное ускорение на таких видеокартах более чем сомнительно. Особенно меньше будет подвисать в моменты, когда в большом файле пересчитываются формулы по каждому «чиху» (перемещение по ячейкам, попытки копировать, вызвать контекстное меню, вставить, редактировать) или перерисовываются ячейки при изменении размеров окна.
2. Перейти на SSD (надо купить SSD)
Это универсальный способ для того, чтобы заставить операционную систему и программы быстрее открываться, работать, меньше тормозить. Если до этого Excel и Word у вас открывались за 5-20 секунд, то после перехода на твердотельные накопители это будет происходить за 1-2 секунды. Также Windows будет грузиться не 2-10 минут, а 10-15 секунд. Согласитесь, это очень большой прирост производительности. Вкупе с предыдущим пунктом — это самые действенные способы. Все нижеследующие способы менее эффективные, но тоже позволяют избавиться от тормозов Excel.
3. Почистить папку TEMP, Temporary (бесплатно)
Временные файлы — это на самом деле большая проблема на компьютерах, где Windows установлен довольно давно и ни разу эта папка не чистилась. Папка временных файлов у каждого пользователя своя, её легко открыть введя в адресной строке %TEMP% и нажав Enter. Если в этой папке свыше 500-1000 файлов и огромное количество папок с перешифрованными названиями, а также весь список файлов и папок грузится очень долго, значит этот пункт вам очень сильно поможет. Дело в том, что все программы и службы в своей работе используют папку с временными файлами как некоторый отстойник, куда можно ненадолго распаковать файлы, закинуть копии, автосохоранять различные ситуации, закачивать обновления, скрипты. Но не все эти файлы также благополучно удаляются, как создаются. Естественно с течением времени она так набухает, что все программы, использующие эту папку начинают подтормаживать. Excel не исключение, он пытается туда сохранять промежуточные версии документов, пока вы не нажмёте Сохранить, и прочие данные. Соответственно как долго у вас эта папка открывается, также долго Excel может пытаться открыть или восстановить некоторые файлы.
Для решения этой проблемы надо почистить папку со временными файлами. Сделать это можно как вручную, выделив и нажать Delete. Но надо быть аккуратным, не удалить файлы, которые сейчас используются. Но я использую программу Ccleaner. Есть и другие аналоги, однако Ccleaner меня более чем устраивает, главное — не обращать внимание на рекламу и предложения перейти на полную PRO-версию. Скачать последнюю версию можно здесь https://www.ccleaner.com/ru-ru/ccleaner/builds. Выбирайте сокращенную версию, если она там имеется. Одно замечание — можете убирать галочку с «Файлы Cookie» в каждом браузере при стандартной очистке, чтобы у вас не слетала авторизация и не приходилось заново заходить в личные кабинеты и почту. Далее жмёте «Анализ», затем «Очистка». Если у вас удалилось больше 2 Гб, значит у вас очень захламлённый компьютер, данный пункт вам подошёл. Удаляйте временные файлы раз в месяц, полгода. Каждый день и неделю не надо, это уже перебор.
4. Почистить папку %appdata%/Microsoft/Excel от временных файлов (бесплатно)
Кроме папки TEMP, Excel также сохраняет временные копии в %appdata% (которая roaming)/Microsoft/Excel. Также как и в предыдущем пункте, здесь может много файлов лишь с одним отличием — все они принадлежат Excel, ничего лишнего не удалите. Но если вдруг в будущем вам потребуется восстановить какой-то файл, то у вас это не получится. Поэтому опять-таки будьте аккуратны, удаляйте вручную, здесь Ccleaner не поможет.
5. Удалите лишние стили (форматирование), которые наслаиваются при копировании из разных файлов (долго, бесплатно)
Если вы сделали всё из предыдущих пунктов и очистили форматирование, но ничего не помогло, то вам надо скопировать все данные таблицы в новый файл и работать далее с новым файлов.
6. Купите лицензию MS Office или последнюю версию офисного пакета (дорого, проблема может быть не в этом)
Вы не поверите, но бывает, что Excel зависает, если у вас пиратская версия, поэтому однозначно купите лицензию. Хоть Майкрософт Офис стоит дорого, но он стоит каждой копейки, ведь благодаря ему вы можете зарабатывать. А лицензионная версия часто работает без сбоев из-за ошибок активации, а также ошибок, связанных с патчами, кряками и активаторами. Просто купите, для дома версия стоит недорого.
И в том случае, если вы ещё пользуетесь версиями Microsoft Office 2007 или даже 2003, то пришла пора поставить новую версию. Некоторые функции в старых версиях отсутствует, также потихоньку сокращается совместимость файлов. Так, например, файлы, запароленные в свежей версии Excel, допустим 2016 или 2019, могут просто не открыться в версии 2007. Я уж молчу, что в 2003 изначально не было поддержки файлов xlsx и docx, над оставить патч, а в 2007 нет поддержки файлов формата odf и ods, также нет экспорта в PDF напрямую, возможности работы с pdf и многих других функций.
7. Отключите лишние надстройки
Чуть не забыл про этот пункт. Так как Excel очень мощная программа, то другие программы интегрируются в неё с помощью разных надстроек. Есть надстройки OneDrive, КонсультантПлюс, Foxit, антивирусов, конвертеров файлов и прочие. Пользуетесь вы ими редко, либо не пользуетесь совсем, а из-за них Excel зависает частенько. Поэтому лишние надстройки надо удалять. Делается это так:
В принципе всё. Пользуйтесь на здоровье, работайте с Excel без тормозов, лагов и зависаний, редактируйте таблицы и не испытывайте неудобств. Если статья помогла и оказалась полезна, буду благодарен за комментарии и замечания или дополнения!
Производительность Excel: повышение производительности вычислений
Область применения: Excel | Excel 2013 | Excel 2016 | VBA
«Большая сетка» (Big Grid), состоящая из 1 миллиона строк и 16 000 столбцов, в Office Excel 2016 вместе с увеличением многих других предельных значений значительно увеличивает максимальный размер создаваемых листов по сравнению с более ранними версиями Excel. Отдельный лист в Excel может содержать в 1 000 раз больше ячеек, чем было в более ранних версиях.
В более ранних версиях Excel многие пользователи создавали книги, вычисления в которых выполнялись медленно, при этом обычно вычисления больших листов выполнялись более медленно, чем вычисления меньших листов. С появлением в Excel 2007 «большой сетки» вопросы производительности стали действительно важными. Медленное выполнение вычислений и таких задач обработки данных, как сортировка и фильтрация, привело к тому, что пользователям стало сложнее концентрироваться на выполняемой задаче, а снижение концентрации приводит к увеличению количества ошибок.
В последних версиях Excel появилось несколько новых функций, которые помогают обрабатывать такой увеличенный объем данных. Например, можно одновременно использовать несколько процессоров для выполнения вычислений и общих операций с набором данных, таких как обновление, сортировка, открытие книг. Многопоточное вычисление может значительно сократить время вычислений листа. Однако наиболее важным фактором, влияющим на скорость вычисления в Excel, по-прежнему остается способ создания и построения листа.
Можно модифицировать большинство медленно вычисляющихся листов таким образом, что вычисления будут выполняться в десятки, сотни и даже тысячи раз быстрее. Определяя, измеряя и устраняя помехи для выполнения вычислений на листах, вы можете ускорить вычисление.
Важность скорости вычисления
Низкая скорость выполнения вычислений влияет на производительность и увеличивает количество ошибок, сделанных пользователем. Производительность пользователя и способность сосредоточиться на задаче ухудшаются по мере увеличения времени отклика.
В Excel предлагается два основных режима вычисления, позволяющих пользователю управлять режимом выполнения вычислений:
**Автоматическое вычисление ** — формулы пересчитываются автоматически при внесении изменения.
Вычисление вручную — формулы пересчитываются только в случае запроса пользователя (например, при нажатии пользователем клавиши F9).
Когда время вычисления составляет менее десятой доли секунды, пользователи чувствуют, что система отвечает мгновенно. Они могут пользоваться автоматическим вычислением, даже когда вводят данные.
От одной десятой секунды до одной секунды — пользователи могут успешно сохранять концентрацию на выполняемом действии, хотя они замечают задержку отклика.
По мере увеличения времени вычисления (обычно от 1 до 10 секунд) пользователи должны переключаться на вычисление вручную при вводе данных. Количество ошибок пользователей и степень раздражения начинают увеличиваться, особенно при выполнении повторяющихся задач, и пользователям становится сложно удерживать концентрацию внимания.
Когда время вычисления превышает 10 секунд, пользователи проявляют нетерпение и обычно в процессе ожидания переключаются на другие задачи. Это может вызывать проблемы, когда вычисление является одной из последовательно выполняющихся задач, и пользователь может спутать последовательность действий.
Общие сведения о методах вычисления в Excel
Чтобы повысить производительность вычислений в Excel, необходимо понимать доступные методы вычисления и способы управления ими.
Полное вычисление и зависимости пересчета
Модуль пересчета в Excel стремится свести к минимуму время вычисления, постоянно отслеживая предыдущие значения и зависимости для каждой формулы (ячейки, на которые ссылается формула) и любые изменения, сделанные с момента последнего вычисления. Во время следующего пересчета в Excel пересчитывается только следующее:
Ячейки, формулы, значения или имена, которые изменились или помечены, как требующие пересчета.
Ячейки, зависимые от других ячеек, формул, имен или значений, которые нужно повторно вычислять.
Переменные функции и видимые условные форматы.
В Excel продолжается вычисление ячеек, которые зависят от ранее вычисленных ячеек, даже если значение ранее вычисленной ячейки не меняется при вычислении.
Поскольку между вычислениями в большинстве случаев вы меняете только часть вводимых данных или несколько формул, этот эффективный пересчет обычно занимает только часть времени, которое потребовалось бы для полного вычисления всех формул.
В режиме вычисления вручную вы можете запустить этот модуль пересчета, нажав клавишу F9. Можно вызвать полное вычисление всех формул, нажав клавиши CTRL+ALT+F9, или полностью перестроить зависимости и выполнить полное вычисление, нажав клавиши SHIFT+CTRL+ALT+F9.
Процесс вычисления
В Excel формулы, которые ссылаются на другие ячейки, могут помещаться до или после ячеек, на которые они ссылаются (опережающие ссылки или обратные ссылки). Это возможно, потому что в Excel вычисление ячеек не выполняется в фиксированном порядке или по строке или столбцу. Вместо этого в Excel динамически определяется последовательность вычислений на основе списка всех формул для вычисления (цепочка вычислений) и сведений о зависимостях, относящихся к каждой формуле.
В Excel можно выделить четкие этапы вычисления:
Построение исходной цепочки вычислений и определение места начала вычисления. Этот этап выполняется, когда книга загружается в память.
Отслеживание зависимостей, пометка ячеек как нерассчитанных и обновление цепочки вычислений. Этот этап выполняется при каждой записи или каждом изменении даже в режиме вычисления вручную. Обычно это выполняется настолько быстро, что пользователь не замечает этот этап, но в сложных случаях отклик может быть медленным.
Расчет всех формул. В ходе процесса вычисления в Excel переупорядочивается и переструктурируется цепочка вычислений в целях оптимизации будущих пересчетов.
Обновление видимых частей окон Excel.
Третий этап выполняется при каждом вычислении или пересчете. В Excel каждая формула в цепочке вычислений должна рассчитываться по очереди. Однако, если формула зависит от одной или нескольких формул, которые еще не были рассчитаны, эта формула перемещается вниз по цепочке, чтобы ее расчет выполнялся еще раз позднее. Это означает, что одна формула может рассчитываться несколько раз за один пересчет.
Когда книга рассчитывается во второй раз, вычисления часто выполняются значительно быстрее, чем в первый раз. Это происходит по нескольким причинам:
Excel обычно пересчитывает только те ячейки, которые изменились, и зависимые от них элементы.
Excel сохраняет и повторно использует самые новые последовательности вычислений, что помогает сохранить большинство времени, затрачиваемого на определение последовательности вычислений.
Когда используются многоядерные компьютеры, в Excel предпринимаются попытки оптимизировать способ распределения вычислений по ядрам на основе результатов предыдущего вычисления.
В сеансе Excel в кэше Windows и Excel недавно использовались данные и программы для более быстрого доступа.
Выполнение вычислений в рабочих книгах, листах и диапазонах
Управлять тем, что именно рассчитывается, можно с помощью различных методов вычислений в Excel.
Пересчет всех открытых книг
Во время каждого пересчета и полного вычисления рассчитываются все книги, которые открыты в этот момент, решаются любые зависимости внутри книг и листов и между ними, а также сбрасываются все ранее не рассчитанные (грязные) ячейки.
Расчет выбранных листов
Можно пересчитать только выбранные листы, используя сочетания клавиш SHIFT+F9. Это не решает зависимости между листами и не сбрасывает грязные ячейки как рассчитанные.
Расчет диапазона ячеек
В Excel для расчета диапазона ячеек можно использовать методы Visual Basic для приложений (VBA) Range.CalculateRowMajorOrder и Range.Calculate:
Range.CalculateRowMajorOrder выполняет расчет диапазона слева направо и сверху вниз, при этом все зависимости игнорируются.
Range.Calculate выполняет расчет диапазона, решая все зависимости в диапазоне.
Так как метод CalculateRowMajorOrder не решает никакие зависимости в рассчитываемом диапазоне, он обычно значительно быстрее, чем Range.Calculate. Однако его следует применять осторожно, так как результаты могут отличаться от результатов, полученных с помощью Range.Calculate.
Range.Calculate является одним из самых полезных инструментов в Excel для оптимизации производительности, поскольку позволяет сравнить скорость вычисления различных формул.
Переменные функции
Переменная функция всегда пересчитывается при каждом пересчете, даже если кажется, что никаких изменений предыдущих значений не произошло. Использование большого количества переменных функций замедляет каждый пересчет, но не оказывает никакого влияния на полное вычисление. Пользовательскую функцию можно сделать переменной, включив метод Application.Volatile в код функции.
Некоторые встроенные функции в Excel являются явно переменными: СЛЧИС(), ТДАТА(), СЕГОДНЯ(). Для других функций менее очевидно, что они переменные: СМЕЩ(), ЯЧЕЙКА(), ДВВСЫЛ(), ИНФОРМ().
Некоторые функции, которые ранее были указаны в документации как переменные, фактически не являются переменными: ИНДЕКС(), ЧСТРОК(), ЧИСЛСТОЛБ(), ОБЛАСТИ().
Действия для пересчета
Действия для пересчета вызывают повторное выполнение вычисления. К ним относятся следующие действия:
Условия расчета формулы и имени
Вычисление формулы или части формулы выполняется немедленно даже в режиме вычисления вручную, когда пользователь выполняет одно из следующих действий:
Формула помечается как невычисляемая, когда она ссылается на ячейку или формулу (зависит от ячейки или формулы), для которой выполняется одно из следующих условий:
Формула, помеченная как невычисляемая, рассчитывается при расчете или перерасчете листа, книги или экземпляра Excel, содержащего эту формулу.
Условия, вызывающие расчет определенного имени, отличаются от условий расчета для формулы в ячейке:
Таблицы данных
Таблицы данных Excel (вкладка Данные > группа Работа с данными > Анализ «что если» > Таблица данных) не следует путать с функцией таблицы (вкладка Главная > группа Стили > Форматировать как таблицу или вкладка Вставка > группа Таблицы > Таблица). В таблицах данных Excel выполняются множественные пересчеты книги, каждый управляется различными значениями в таблице. В Excel книга сначала рассчитывается обычным образом. Затем для каждой пары значений строки и столбца подставляются значения, выполняется однопотоковый пересчет и результаты сохраняются в таблице данных.
При пересчете таблицы данных всегда используется только один процессор.
Таблицы данных удобны для вычисления нескольких вариантов, а также просмотра и сравнения результатов этих вариантов. Используйте параметр вычисления Автоматически, кроме таблиц данных, чтобы остановить в Excel автоматический запуск нескольких вычислений при каждом расчете, но выполнить расчет всех зависимых формул, кроме таблиц.
Параметры управления вычислением
В Excel содержится ряд параметров, позволяющих управлять способом вычисления. Наиболее часто используемые параметры можно изменить в Excel, используя группу Вычисление на вкладке Формулы на ленте.
Рис. 1. Группа «Вычисление» на вкладке «Формулы»
Чтобы просмотреть дополнительные параметры вычислений Excel, на вкладке Файл выберите пункт Параметры. В диалоговом окне Параметры Excel выберите вкладку Формулы.
Рис. 2. Параметры вычислений на вкладке «Формулы» в параметрах Excel
Многие параметры вычисления (автоматически, автоматически, кроме таблиц данных, вручную, Пересчитывать книгу перед сохранением) и настройки итераций (Включить итеративные вычисления, Предельное число итераций, Относительная погрешность) работают на уровне приложения, а не на уровне книги (они одинаковы для всех открытых книг).
Чтобы просмотреть дополнительные параметры вычисления, на вкладке Файл выберите пункт Параметры. В диалоговом окне Параметры Excel выберите пункт Дополнительно. В разделе Формулы задайте параметры вычисления.
Рис. 3. Дополнительные параметры вычислений
Когда запускается Excel или когда это приложение выполняется без открытых книг, исходный режим вычисления и настройки итераций задаются из первой открываемой книги, которая не является шаблоном и надстройкой. Это означает, что настройки вычисления в книгах, открываемых позже, игнорируются, хотя, конечно, пользователь может вручную изменить настройки в Excel в любое время. При сохранении книги в ней сохраняются текущие настройки вычисления.
Автоматический расчет
Режим автоматического расчета означает, что в Excel автоматически пересчитываются все открытые книги при каждом изменении и при открытии книги. Обычно, когда книга открывается в автоматическом режиме и в Excel выполняется пересчет, пользователь не видит этого пересчета, поскольку ничего не изменилось с момента сохранения книги.
Пересчет можно заметить, когда книга открывается в более поздней версии Excel, чем та версия, которая использовалась последний раз при расчете этой книги (например, Excel 2016 в сравнении с Excel 2013). Поскольку модули вычисления в Excel другие, в Excel выполняется полное вычисление при открытии книги, которая была сохранена с использованием более ранней версии Excel.
Расчет вручную
Режим расчета вручную означает, что в Excel выполняется пересчет всех открытых книг только при нажатии пользователем клавиш F9 или CTRL+ALT+F9 или при сохранении книги. При работе с книгами, пересчет которых занимает больше доли секунды, необходимо задать вычисление в режиме расчета вручную, чтобы избежать задержки во время внесения изменений.
Когда требуется пересчет книги в ручном режиме, в Excel отображается команда Вычислить в строке состояния. Команда Вычислить отображается в строке состояния и в том случае, если книга содержит циклические ссылки и установлен флажок итераций.
Настройки итераций
Если в книге намеренно заданы циклические ссылки, настройки итераций позволяют управлять максимальным числом пересчетов книги (итераций) и критериями сходимости (максимальное изменение: когда остановиться). Снимите флажок итераций, чтобы в случае случайных циклических ссылок в Excel отобразилось предупреждение и не предпринималась попытка разрешения таких ссылок.
Свойство книги ForceFullCalculation
Если для этого свойства книги задано значение True, интеллектуальный пересчет Excel отключается и при каждом пересчете пересчитываются все формулы в открытых книгах. Для некоторых сложных книг время, затрачиваемое на построение и обслуживание деревьев зависимостей, необходимых для интеллектуального пересчета, превышает время, сэкономленное при интеллектуальном пересчете.
Если открытие книги длится слишком долго или внесение небольших изменений в течение длительного времени даже при ручном режиме вычислений занимает много времени, возможно, стоит попробовать свойство ForceFullCalculation.
В строке состояния будет отображаться команда Вычислить, если для свойства книги ForceFullCalculation задано значение True.
Вы можете управлять этим параметром с помощью VBE (ALT+F11), выбрав ЭтаКнига в обозревателе проектов (CTRL+R) и отображая окно свойств (F4).
Рис. 4. Настройка свойства Workbook.ForceFullCalculation
Увеличение скорости вычислений в книгах
Выполните указанные ниже действия и способы, чтобы рабочие книги рассчитывались быстрее.
Скорость процессора и использование нескольких ядер
Для большинство версий Excel более быстрый процессор, конечно, позволяет быстрее выполнять вычисления Excel. Модули многопотоковых вычислений, внедренные в Excel 2007, позволяют Excel прекрасно использовать многопроцессорные системы, что приводит к значительному повышению производительности при работе с большинством книг.
Для большинства больших книг повышение производительности вычислений с разных процессоров осуществляется почти линейно с числом физических процессоров. Однако технология Hyper-Threading физических процессоров обеспечивает лишь небольшое увеличение производительности.
Подкачка в файл подкачки виртуальной памяти выполняется медленно. Необходимо иметь достаточно свободного места на ОЗУ для операционной системы, Excel и книг. Если в процессе вычисления операции с жестким диском происходят не только время от времени и выполняются определенные пользователем функции, запускающие действия с диском, потребуется больший объем ОЗУ.
Как уже упоминалось, последние версии Excel могут эффективно использовать большие объемы памяти, а в 32-разрядной версии Excel 2007 и Excel 2010 можно обрабатывать одну книгу или комбинацию книг, используя до 2 ГБ памяти.
32-разрядные версии Excel 2013 и Excel 2016, использующие функцию обработки больших адресов (LAA), могут использовать до 3 или 4 ГБ памяти в зависимости от установленной версии Windows. 64-разрядная версия Excel может обрабатывать книги еще большего размера. Дополнительные сведения см. в разделе, посвященном большим наборам данных, LAA и 64-разрядной версии Excel статьи Производительность Excel: улучшения производительности и ограничений.
Общие рекомендации по величине памяти: для эффективных вычислений нужно обеспечить достаточно места на ОЗУ для работы с самым большим набором книг, которые должны быть открыты одновременно, плюс 1 или 2 ГБ для Excel и операционной системы, плюс дополнительное место на ОЗУ для других выполняющихся приложений.
Измерение времени вычисления
Чтобы расчет книг выполнялся быстрее, необходимо иметь возможность точно измерить время вычисления. Требуется таймер, более быстрый и более точный, чем функция Time в VBA. Функция MICROTIMER(), приведенная в следующем примере кода, использует API Windows для обращения к системному таймеру с высоким разрешением. Он может измерять временные интервалы, длительность которых составляет небольшое количество микросекунд. Имейте в виду, что поскольку Windows является многозадачной операционной системой и повторный расчет чего-либо может выполняться быстрее, чем в первый раз, полученные значения времени обычно не повторяются. Для получения более точного значения замеряйте время выполнения задач вычислений несколько раз и выбирайте среднее значение из полученных результатов.
Дополнительные сведения о том, как редактор Visual Basic может значительно повлиять на производительность пользовательских функций на языке VBA, см. в разделе «Более быстрые пользовательские функции VBA» статьи Производительность Excel: советы по оптимизации производительности.
Для измерения времени вычисления необходимо вызвать соответствующий метод вычисления. Эти подпрограммы позволяют найти время расчета для диапазона, время пересчета для листа или всех открытых книг, время полного вычисления для всех открытых книг.
Скопируйте все эти подпрограммы и функции в стандартный модуль VBA. Чтобы открыть редактор VBA, нажмите клавиши ALT+F11. В меню Вставка выберите пункт Модуль и скопируйте код в модуль.
Чтобы выполнить подпрограммы в Excel, нажмите клавиши ALT+F8. Выберите нужную подпрограмму и щелкните Выполнить.
Рис. 5. Окно макросов Excel с таймерами вычислений
Поиск и определение приоритетов помех при вычислениях
На большинстве листов, на которых вычисления осуществляются медленно, имеется только несколько проблемных областей или помех, обработка которых занимает большую часть времени вычисления. Если неизвестно, где находятся эти помехи, воспользуйтесь для их поиска детализированным подходом, описанным в этом разделе. Если известно, где они находятся, необходимо измерить время вычисления, которое тратится на каждую помеху, с тем чтобы задать приоритеты в своей работе и удалить их.
Детализированный подход для поиска помех
Детализированный подход начинается с определения времени вычисления книги, вычисления каждого листа и блоков формул на листах, на которых вычисления осуществляются медленно. Выполняйте каждый шаг по порядку и записывайте время вычислений.
Поиск помех с помощью детализированного подхода
Убедитесь, что открыта только одна книга и никакие другие задачи не выполняются.
Задайте режим вычисления вручную.
Сделайте резервную копию книги.
Откройте книгу, которая содержит макросы таймеров вычисления, или добавьте их в книгу.
Проверьте используемый диапазон, нажав клавиши CTRL+END в каждом листе по очереди.
Это действие покажет ячейку, которая была использована последней. Если ячейка находится за пределами ожидаемого диапазона, подумайте об удалении лишних столбцов и строк и сохранении книги. Дополнительные сведения см. в разделе «Минимизация используемого диапазона» статьи Производительность Excel: советы по оптимизации производительности.
Запустите макрос FullCalcTimer.
Обычно больше всего времени занимает вычисление всех формул в книге.
Запустите макрос RecalcTimer.
Пересчет сразу же после полного вычисления обычно занимает меньше всего времени.
Рассчитайте изменчивость книги как отношение времени пересчета к времени полного вычисления.
Это измерит степень, в которой переменные формулы и расчет цепочки вычислений являются помехами.
Активируйте каждый лист и по очереди запустите макрос SheetTimer.
Поскольку вы только что выполнили пересчет книги, вы получите время пересчета для каждого листа. Это должно позволить вам определить, с какими из листов связана проблема.
Запустите макрос RangeTimer на выбранных блоках формул.
Для каждого проблемного листа поделите столбцы или строки на небольшое количество блоков.
Выбирайте каждый блок по очереди и запускайте макрос RangeTimer по этому блоку.
При необходимости выполните дальнейшую детализацию, подразделив каждый блок на еще меньшие блоки.
Определите приоритеты для помех.
Ускорение выполнения вычислений и уменьшение количества помех
Затраты времени на выполнение вычислений обусловливаются не количеством формул или размером книг. Основными факторами здесь являются количество ссылок на ячейки и операции вычислений, а также эффективность используемых функций.
Большинство листов формируются посредством копирования формул, содержащих смесь абсолютных и относительных ссылок. Поэтому на них обычно имеется большое количество формул, содержащих повторяющиеся или дублируемые вычисления и ссылки.
Избегайте использования сложных мегаформул и формул массивов. В общем случае лучше иметь больше строк и столбцов и меньше сложных вычислений. Это позволяет и более эффективно проводить пересчет формул, и выполнять многопоточные вычисления в Excel, что представляет благоприятные возможности для оптимизации вычислений. Этот подход также проще для понимания и отладки. Ниже приводятся несколько правил, помогающих ускорить проведение вычислений в книге.
Первое правило: удаляйте дублируемые, повторяющиеся и не являющиеся обязательными вычисления
Выявите дублируемые, повторяющиеся и не являющиеся обязательными вычисления и прикиньте, сколько примерно ссылок на ячейки и вычислений будет задействовано в Excel, чтобы рассчитать результат при наличии этой помехи. Подумайте, как можно получить тот же результат при меньшем количестве ссылок и вычислений.
Обычно этот подход включает два или несколько из указанных ниже шагов:
Уменьшите количество ссылок в каждой ячейке.
Переместите повторяющиеся вычисления в одну или несколько вспомогательных ячеек, а затем задайте ссылки на вспомогательные ячейки из основных формул.
Используйте дополнительные строки и столбцы для вычисления и сохранения промежуточных результатов, чтобы их можно было повторно использовать в других формулах.
Второе правило: используйте по возможности наиболее эффективные функции
Когда найдена помеха, затрагивающая функцию или формулы массивов, определите, есть ли более эффективный способ для достижения такого же результата. Пример:
Поиск, проводимый по отсортированным данным, может оказаться в десятки и сотни раз более эффективным, чем поиск по не отсортированным данным.
Пользовательские функции VBA обычно работают более медленно, чем встроенные функции в Excel (хотя грамотно написанные функции VBA могут работать достаточно быстро).
Минимизируйте количество используемых ячеек в таких функциях, как СУММ и СУММЕСЛИ. Время вычисления пропорционально числу используемых ячеек (неиспользуемые ячейки игнорируются).
Рассмотрите возможность замены медленных формул массивов пользовательскими функциями.
Третье правило: часто используйте интеллектуальный пересчет и многопоточные вычисления
Чем больше вы будете пользоваться функцией интеллектуального пересчета в Excel, тем меньше процедур обработки нужно будет выполнять каждый раз, когда в Excel выполняется пересчет, поэтому:
Старайтесь по возможности не использовать такие функции, как ДВССЫЛ и СМЕЩ, если они не обеспечивают значительно большую эффективность, чем альтернативные функции. (При умелом использовании функция СМЕЩ часто обеспечивает быстрый результат).
Минимизируйте размер диапазонов, используемых в формулах массивов и функциях.
Разбейте формулы массивов и мегаформулы на отдельные вспомогательные столбцы и строки.
Избегайте использования однопотоковых функций:
Избегайте итеративного использования таблиц данных и циклических ссылок: оба этих способа всегда применяют однопотоковые вычисления.
Четвертое правило: рассчитывайте время и проверяйте результат для каждого изменения
Внося некоторые изменения, вы можете с удивлением заметить, что получили не тот результат, который ожидали, или что вычисления выполняются более медленно. Поэтому рекомендуется рассчитывать время и проверять результат для каждого изменения, следуя приведенной ниже инструкции.
Рассчитайте время для формулы, которую нужно изменить, используя макрос RangeTimer.
Рассчитайте время для измененной формулы, используя макрос RangeTimer.
Убедитесь, что измененная формула дает правильный ответ.
Примеры использования правил
В следующих разделах приводятся примеры использования указанных правил для ускорения вычислений.
Суммы с начала периода
Например, требуется рассчитать суммы с начала периода для столбца, содержащего 2 000 чисел. Предположим, что в столбце A содержатся числа, а в столбцах B и C должны располагаться суммарные значения с начала периода.
Соответствующую формулу можно записать с помощью эффективной функции SUM (СУММ).
Рис. 6. Пример формул СУММ за период
Скопируйте формулу вниз до ячейки B2000.
Каково теперь общее число ссылок на ячейки, складываемые функцией СУММ? Ячейка B1 ссылается на одну ячейку, а ячейка B2000 — на 2 000 ячеек. Среднее значение составляет 1 000 ссылок на ячейку, что в сумме дает общее число ссылок, равное 2 миллионам. Выбор 2 000 формул и использование макроса RangeTimer показывает, что 2 000 формул в столбце B вычисляются за 80 миллисекунд. Большая часть этих вычислений дублируется много раз: СУММ добавляет A1 к A2 в каждой формуле из диапазона B2:B2000.
Такое дублирование можно исключить, если записать данные формулы следующим образом.
Скопируйте эту формулу вниз до ячейки C2000.
Каково теперь общее число ссылок на складываемые ячейки? В каждой формуле, за исключением первой формулы, используется две ссылки на ячейки. Таким образом, общее их число равняется 1999*2+1=3999. Это в 500 раз меньше ссылок на ячейки.
RangeTimer показывает, что 2 000 формул в столбце C вычисляются за 3,7 миллисекунды (напомним, что формулы в столбце B вычисляются за 80 миллисекунд). Этому изменению соответствует коэффициент улучшения, равный лишь 80/3,7=22, а не 500, поскольку имеются лишь небольшие дополнительные временные затраты в расчете на одну формулу.
Обработка ошибок
Если имеется формула, предполагающая интенсивное проведение вычислений, в которой требуется, чтобы в случае возникновения ошибки результат отображался как нуль (часто возникающая ситуация при выполнении поиска с точным соответствием), то данное требование можно записать с помощью нескольких способов.
Его можно записать с помощью одной формулы, вычисления по которой осуществляются медленно:
B1=IF(ISERROR(time expensive formula),0,time expensive formula)
Его можно записать с помощью двух формул, вычисления по которым осуществляются быстро:
A1=time expensive formula
Можно также использовать функцию IFERROR (ЕСЛИОШИБКА), которая разрабатывалась как простая и быстрая, включающая одну формулу:
B1=IFERROR(time expensive formula,0)
Динамический подсчет уникальных элементов
Рис. 7. Пример списка данных для подсчета уникальных элементов
Если имеется список, включающий 11 000 строк данных в столбце A, элементы которого часто изменяются, и требуется воспользоваться формулой, которая бы динамически рассчитывала число уникальных элементов (без учета пустых строк), имеется несколько возможных решений, указанных ниже.
Формулы массива (используйте клавиши CTRL+SHIFT+ВВОД); RangeTimer показывает, что это занимает 13,8 секунды.
С помощью функции SUMPRODUCT (СУММПРОИЗВ) вычисления обычно проводятся быстрее, чем при использовании аналогичной формулы массива. Вычисление этой формулы занимает 10,0 секунд, что дает коэффициент улучшения, равный 13,8/10,0=1,38, что несколько лучше, но все же в небольшой степени.
Добавление столбца формул. Если посмотреть на предыдущий пример данных, то можно увидеть, что он был отсортирован (Excel затрачивает 0,5 секунды на сортировку 11 000 строк). Этим можно воспользоваться посредством добавления столбца формул, с помощью которых проверяется, являются ли данные в этой строке такими же, что и в предыдущей строке. Если они отличаются, формула возвращает 1. В противном случае она возвращает значение 0.
Добавьте эту формулу в ячейку B2.
Скопируйте формулу, а затем добавьте формулу сложения значений в столбце B.
Полный цикл расчета всех этих формул занимает 0,027 секунды. При этом коэффициент улучшения составляет 13,8/0,027=511.
Заключение
Excel позволяет эффективно управлять очень большими по своему объему листами и обеспечивает значительное увеличение скорости проведения расчетов по сравнению с более ранними версиями. При создании больших листов их можно достаточно легко сформировать таким образом, при котором расчеты выполняются сравнительно медленно. Использование листов, на которых вычисления осуществляются медленно, увеличивает количество ошибок, поскольку пользователям трудно концентрировать внимание в течение всего цикла расчетов.
Использование эффективного набора методик обеспечивает 10-кратное и даже 100-кратное ускорение проведения расчетов на большинстве листов, на которых в обычных условиях вычисления осуществляются медленно. Вы также можете применять эти методики при разработке и создании листов, благодаря чему на них повышается скорость проведения вычислений.
См. также
Поддержка и обратная связь
Есть вопросы или отзывы, касающиеся Office VBA или этой статьи? Руководство по другим способам получения поддержки и отправки отзывов см. в статье Поддержка Office VBA и обратная связь.
