Как уменьшить объем excel файла и ускорить работу с таблицей

65496 11.11.2012 Скачать пример

Если в один прекрасный момент вы осознаете, что ваш основной рабочий файл в Excel разбух до нескольких десятков мегабайт и во время открытия файла можно смело успеть налить себе кофе, то попробуйте пробежаться по описанным ниже пунктам — возможно один или несколько из них укоротят вашего «переростка» до вменяемых размеров и разгонят его «тормоза» 🙂

Проблема 1. Используемый диапазон листа больше, чем нужно

Если ваша таблица занимает 5 на 5 ячеек, то это отнюдь не означает, что Excel запоминает при сохранении этого файла только 25 ячеек с данными.

Если вы в прошлом использовали какие-либо ячейки на этом листе, то они автоматически включаются в используемый диапазон (так называемый Used Range), который и запоминается при сохранении книги.

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

Лечится это, тем не менее, достаточно легко:

1. Выделите первую пустую строку под вашей таблицей
2. Нажмите сочетание клавиш Ctrl+Shift+стрелка вниз – выделятся все пустые строки до конца листа.
3. Удалите их, нажав на клавиатуре Ctrl+знак минус или выбрав на вкладке Главная – Удалить – Удалить строки с листа (Home – Delete – Delete rows).
4. Повторите то же самое со столбцами.
5. Повторите все вышеописанные процедуры на каждом листе, где при нажатии на Ctrl+End активная ячейка перемещается не на фактическую последнюю ячейку с данными а «в пустоту» ниже и/или правее.
6. Сохраните файл (обязательно, иначе изменения не вступят в силу!)

Если в вашей книге очень много таких листов, то проще, наверное, использовать короткий макрос.

Проблема 2. Используется старый формат XLS вместо новых XLSX, XLSM и XLSB

Много лет и версий подряд еще с начала девяностых в Excel был один формат файлов — XLS. Это, конечно, убирало проблемы совместимости, но, сам по себе, этот формат давно устарел и имел много неприятных недостатков (большой размер, непрозрачность внутренней структуры данных, легкую повреждаемость и т.д.)

Начиная с верии Excel 2007 Microsoft ввела новые форматы сохранения файлов, использование которых заметно облегчает жизнь и — ваши файлы: 

• XLSX — по сути является зазипованным XML. Размер файлов в таком формате по сравнению с Excel 2003 меньше, в среднем, в 5-7 раз.
• XLSM — то же самое, но с поддержкой макросов.
• XLSB — двоичный формат, т.е. по сути — что-то вроде скомпилированного XML. Обычно в 1.5-2 раза меньше, чем XLSX. Единственный минус: нет совместимости с другими приложениями кроме Excel, но зато  размер — минимален.

Вывод: всегда и везде, где можно, переходите от старого формата XLS (возможно, доставшегося вам «по наследству» от предыдущих сотрудников) к новым форматам.

Проблема 3. Избыточное форматирование

Сложное многоцветное форматирование, само-собой, негативно отражается на размере вашего файла. А условное форматирование еще и ощутимо замедляет его работу, т.к. заставляет Excel пересчитывать условия и обновлять форматирование при каждом чихе.

Оставьте только самое необходимое, не изощряйтесь. Особенно в тех таблицах, которые кроме вас никто не видит. Для удаления только форматов (без потери содержимого!) выделите ячейки и выберите в выпадающем списке Очистить — Очистить форматы (Clear — Clear Formats) на вкладке Главная (Home):

Особенно «загружают» файл отформатированные целиком строки и столбцы. Т.к. размер листа в последних версиях Excel сильно увеличен (>1 млн. строк и >16 тыс.

столбцов), то для запоминания и обрабоки подобного форматирования нужно много ресурсов.

В Excel 2013-2016, кстати, появилась надстройка Inquire, которая содержит инструмент для быстрого избавления от подобных излишеств — кнопку Удалить избыточное форматирование (Clean Excess Cell Formatting):

Она мгновенно удаляет все излишнее форматирование до конца листа, оставляя его только внутри ваших таблиц и никак не повреждая ваши данные. Причем может это сделать даже для всех листов книги сразу.

Если вы не видите у себя в интерфейсе вкладку Inquire, то ее необходимо подключить на вкладке Разработчик — Надстройки COM (Developer — COM Addins).

Проблема 4. Ненужные макросы и формы на VBA

Большие макросы на Visual Basic и особенно пользовательские формы с внедренной графикой могут весьма заметно утяжелять вашу книгу. Для удаления:

1. нажмите Alt+F11, чтобы войти в редактор Visual Basic
2. найдите окно Project Explorer’а (если его не видно, то выберите в меню View — Project Explorer)
3. удалите все модули и все формы (правой кнопкой мыши — Remove — дальше в окне с вопросом о экспорте перед удалением — No):

Также код может содержаться в модулях листов — проверьте их тоже. Также можно просто сохранить файл в формате XLSX без поддержки макросов — все макросы и формы умрут автоматически. Также можно воспользоваться инструментом Очистить книгу от макросов из надстройки PLEX.

Проблема 5. Именованные диапазоны

Если в вашем файле используются именованные диапазоны (особенно с формулами, динамические или получаемые при фильтрации), то имеет смысл от них отказаться в пользу экономии размера книги. Посмотреть список имеющихся диапазонов можно нажав Ctrl+F3 или открыв окно Диспетчера имен (Name Manager) на вкладке Формулы (Formulas):

Также вычищайте именованные диапазоны с ошибками (их можно быстро отобрать с помощью кнопки Фильтр в правом верхнем углу этого окна) — они вам точно не пригодятся.

Проблема 6. Фотографии высокого разрешения и невидимые автофигуры

Если речь идет о фотографиях, добавленных в книгу (особенно когда их много, например в каталоге продукции), то они, само-собой, увеличивают размер файла.

Советую сжимать их, уменьшая разрешение до 96-150 точек на дюйм. На экране по качеству это совершенно не чувствуется, а размер файла уменьшает в разы.

Для сжатия воспользуйтесь кнопкой Сжать рисунки (Compress pictures) на вкладке Формат (Format):

Кроме видимых картинок на листе могут содержаться и невидимые изображения (рисунки, фотографии, автофигуры). Чтобы увидеть их, выделите любую картинку и на вкладке Формат (Format) нажмите кнопку Область выделения (Selection Pane).

Для удаления вообще всех графических объектов на текущем листе можно использовать простой макрос:

Sub Delete_All_Pictures()
'макрос для удаления всех картинок на текущем листе

Dim objPic As Shape

For Each objPic In ActiveSheet.Shapes
objPic.Delete
Next objPic

End Sub

Проблема 7. Исходные данные сводных таблиц

По-умолчанию Excel сохраняет данные для расчета сводной таблицы (pivot cache) внутри файла. Можно отказаться от этой возможности, заметно сократив размер файла, но увеличив время на обновление сводной при следующем открытии книги.

Щелкните правой кнопкой мыши по сводной таблице и выберите команду Свойства таблицы (Pivot Table Properties) — вкладка Данные (Data) — снять флажок Сохранять исходные данные вместе с файлом (Save source data with file):

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

В Excel 2007-2016 кнопку Мастера сводных таблиц нужно добавлять на панель вручную — на ленте такой команды нет. Для этого щелкните по панели быстрого доступа правой кнопкой мыши и выберите Настройка панели быстрого доступа (Customize Quick Access Toolbar) и затем найдите в полном списке команд кнопку Мастер сводных таблиц (PivotTable and PivotChart Wizard):

Проблема 8. Журнал изменений (логи) в файле с общим доступом

Если в вашем файле включен общий доступ на вкладке Рецензирование — Доступ к книге (Review — Share Workbook), то внутри вашего файла Excel на специальном скрытом листе начинает сохраняться вся история изменений документа: кто, когда и как менял ячейки всех листов. По умолчанию, такой журнал сохраняет данные изменений за последние 30 дней, т.е. при активной работе с файлом, может запросто занимать несколько мегабайт.

Мораль: не используйте общий доступ без необходимости или сократите количество дней хранения данных журнала, используя вторую вкладку Подробнее (Advanced) в окне Доступ к книге. Там можно найти параметр Хранить журнал изменений в течение N дней (Keep change history for N days) или совсем отключить его:

Проблема 9. Много мусорных стилей

Про эту пакость я уже подробно писал ранее в статье о том, как победить ошибку «Слишком много форматов ячеек». Суть, если кратко, в том, что если вы разворачиваете на вкладке Главная список Стили ячеек (Home — Cell Styles) и видите там очень много непонятных и ненужных стилей, то это плохо — и для размера вашего файла Excel и для его быстродействия.

Удалить ненужные стили можно с помощью макроса или готовой команды из надстройки PLEX.

Проблема 10. Много примечаний

Примечания к ячейкам, конечно, не самый вредный момент из всех перечисленных. Но некоторые файлы могут содержать большое количество текста или даже картинок в примечаниях к ячейкам. Если  примечания не содержат полезной для вас информации, то их можно легко удалить с помощью команды на вкладке Главная — Очистить — Очистить примечания (Home — Clear — Clear Comments).

Источник: https://www.planetaexcel.ru/techniques/11/114/

Почему тормозит 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 DataMicrosoftExcelXLSTART или С:…Microsoft OfficeOffice12 и где то в недрах этих папок ищете файл «Personal.*» и сносите его.

11. Производим оптимальную настройку сводных таблиц

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

Для устранения этой причины, установите курсор на таблице, вызовите контекстное меню правой кнопкой мыши, потом выберите пункт «Параметры сводной таблицы» в диалоговом окне на вкладке «Данные» снимите галочку в пункте «Сохранять исходные данные вместе с файлом».

      Этими действиями вы можете уменьшить файл практически в два раза.

12. Убираем ненужные объекты

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

      Для удаления таких ненужных объектов необходимо запустить макрос, кликните горячую комбинацию клавиш Alt + F11 для вызова редактора и вставьте код:

sub DelObject()
For Each i In ActiveSheet.Shapes
i.Delete
Next
end sub

sub DelObject()     For Each i In ActiveSheet.Shapes        i.Delete     Next end sub

     Ну, или же просто ручками уберите ненужные вам объекты.

Для этого в панели управления выбираете на вкладке «Главная» в блоке «Редактирование» в выпадающем меню «Найти и выделить» выбираете пункт «Выделение группы ячеек» и в диалоговом окне отмечаете пункт «Объекты» нажимаете «ОК». Теперь можно всё удалить.

     Теперь вы знаете 12 способов, что нужно делать, когда тормозит Excel, но даже если и они вам не помогли, тогда попробуйте скопировать и перенести нужную информацию со старого в новый файл, существует вероятность, что файл имеет повреждения. Всегда надо страховаться.

      Я очень хочу, чтобы о таких возможностях выхода с трудных ситуаций и улучшения работоспособности ваших файлов, вы знали еще больше и могли применить их в своей работе. Если у вас есть чем дополнить меня пишите комментарии, я буду их ждать с нетерпением, ставьте лайки и делитесь полезной статьей в соц.сетях!

      Не забудьте подкинуть автору на кофе…

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

Источник: http://topexcel.ru/pochemu-tormozit-excel-12-sposobov-uluchshit-rabotu-s-fajlami/

Почему тормозит Excel? Как уменьшить размер файла Excel? Решения проблемы

Всем привет!

Сегодня расскажу о проблемах самого популярного табличного офисного редактора Microsoft Excel, и как с этим бороться…

Бывает так, что файл книги Microsoft Excel содержащий несколько листов по 1000 строк, с простыми формулами и без связанных таблиц начинает весить непомерно много  — 100 или даже 200 мегабайт, при этом открывается файл очень долго, иногда бывает даже сохраняться отказывается. Что делать в этом случае? Давайте разбираться.

Большой размер файла Micosoft Excel причина зависаний и торможений.

В этой статье я хочу предложить несколько вариантов борьбы с «тяжелыми» файлами Microsoft Excel, которыми пользуюсь сам. Так же, в х с удовольствием почитаю ваши варианты борьбы с тормозами и оптимизацией размера .xls и .xlsx файлов.

Первое. Пустые ячейки с данными.

Такая ситуация может возникнуть в следствии копирования. Это так же может произойти, если данные в документ excel вставляются листами или столбцами/строками из файлов, созданных в ИНФИН, 1С, SAP и других подобных ERP-системах.

Объясняется это тем, что они генерируют в файл «пустые» ячейки (забитые пробелами) и объекты типа Надпись. Со временем файл становится очень объемным, так как забивается этими невидимыми глазу данными.

Sub DeleteAllTextBox()

Dim oSh As Shape

For Each oSh In ActiveSheet.Shapes

oSh.Delete

Next oSh

End Sub

Отработав, через какое-то время он очистит пустые ячейки, после чего можно сохранить документ и, как говориться, почувствовать разницу!

Второе. Очищаем неиспользуемый диапазон листа

Если в вашей книге Excel есть таблица с данными, допустим 10 на 10, но это не значит что остальные ячейки листа пустые (особенно часто это происходит при копировании данных).

Проверить это просто – нажмите на клавиатуре сочетание клавиш Ctrl+End и посмотрите куда переместится активная ячейка. Если она прыгнет на фактическую последнюю ячейку с данными на листе – отлично.

А если вдруг ускачет сильно правее и/или ниже «в пустоту» – дело плохо: все эти ненужные пустые ячейки Excel тоже запоминает внутри файла.
Поправить ситуацию достаточно просто, для этого нужно:

1. 1. Выделяем первую пустую строку под вашей таблицей;
   2. Нажимаем сочетание клавиш Ctrl+Shift+стрелка вниз (после этого должны выделится все пустые строки до конца листа в книге);
      Поиск пустых строк в Microsoft Excel. Нашлось более 65000 пустых строк на листе.
   3. Удалите их, через меню ленты: Главная – Удалить – Удалить строки с листа (или горячей клавишей Ctrl+знак минус);
   4. Те же операции по удалению проделываем и со столбцами.
   5. Если ваша книга Excel содержит один лист, то пропускаем этот пункт и переходим к пункту 6, если у вас в книге много листов, то вышеприведенные операции нужно повторить на каждом листе, при этом, при нажатии комбинации клавиш Ctrl+End активная ячейка перемещается не на последнюю ячейку с заполненными данными, а в пустую ячейку, которая может быть ниже или правее.
   6. Сохраняем наш очищенный xlsx-файл. Теперь самое время проверить размер файла:
      Наш 111-мегабайтный файл Excel после очистки от пустых строк превратился в 1,23 Мб.

Как видите, достаточно несложные манипуляции могут помочь оптимизировать файлы Microsoft Excel, главное найти правильный подход.

Третье. Пересохраняем файл

Бывает и так, что скрипт приведенный выше не панацея, и после того как он отработал размер файла не уменьшился. Переходим к варианту 2: попробуйте сохранить файл в бинарном формате Excel. Смотрите, что получилось у меня, на примере все того же 111-мегабайтного файла:

Сохраняем файл в бинарный формат .xlsb.

После сохранения наш файл сильно похудел и уменьшился в размере, при том что мы не проводили никаких дополнительных чисток:

«Похудевший» 111-мегабайтный файл Excel после сохранения в бинарный формат .xlsb.

Как видно из скриншота, наш файл стал занимать объем в почти в 22 раза меньший,  просто при смене формата с .xlsx в xlsb. Открываться он стал быстрей, при этом мы не потеряли функционал файла, формулы, стили и оформление — все это сталось на месте.

Послесловие

Про Формат файла. Еще раз. Со всей внимательностью отнеситесь к формату файла. Если у вашего Excel-документа формат файла .xls, не поленитесь пересохранить его в новый формат .xlsx. И сейчас я кратко объясню почему это необходимо сделать:

Дело в том, что формат *.xls давно и безвозвратно устарел, Microsoft перетягивает поддержку этого формата от версии к версии лишь для устранения проблем совместимости со старыми документами.

Начиная с версии Excel 2007 компания-разработчик Microsoft ввела новые, современные форматы файлов.

Формат .xlsx — это структурированный, по сути заархивированный xml-файл, который при том же объеме данных способен быть в среднем 6 раз меньше устаревшего .xls.

Формат .xlsm используется для макросов.

Формат .xlsb — бинарный формат, который в 2 раза более компактен но уже по сравнению с .xlsx.

Если на работе или дома вы увидели, что коллеги или родные работают со старым форматом Excel-документов порекомендуйте им пересохранить их в новый, современный формат. Старый – это архаизм.

Заключение

На данный момент я рассказал вам о 3-х вариантах оптимизации и очистки .xls и .xlsx файлов от мусора, который увеличивает объем документа и приводит к торможению. Какой метод лучше и каким пользоваться? — В итоге решать вам.

Источник: http://pingmeup.ru/2018/08/07/pochemu-tormozit-excel-kak-umenshit-razme/

Как уменьшить размер файла Excel и заставить его работать быстрее

Каждый, кто часто имеет дело с Excel и созданными в нем документами, рано или поздно сталкивается с проблемой медленной работы файла. «Файл тормозит, что делать?»

Такой вопрос часто можно увидеть на форумах, тематических сайтах или просто в списке запросов поисковика. Чаще всего подобная проблема сопровождается гипертрофированными размерами книги Excel. Из этой статьи вы узнаете несколько самых действенных способов уменьшения размера файла и ускорения его работы.

1. Уменьшаем размер используемого диапазона листа

Наиболее часто проблема увеличения размера книги и ее торможения бывает связана с разросшимся используемым диапазоном листа.

Это тот диапазон, который Excel запоминает и с которым каждый раз работает.

Если вы используете на листе всего несколько ячеек, но когда то у вас там была таблица в 1000 строк, то даже после ее удаления Excel будет обрабатывать все эти строки, тем самым замедляя работу файла.

Чтобы проверить, есть ли на листе лишние пустые столбцы и строки нужно нажать сочетание клавиш «Ctrl+End». Вы попадете в последнюю ячейку, которую использует программа. Если она явно за пределами ваших данных, то лишние строки и столбцы стоит удалить. Для этого в столбце А встаем в ячейку ниже последней нужной нам строки и нажимаем «Ctrl+Shift+End».

Ручное выделение лишних ячеек

Выделятся все лишние строки. Удаляем их. То же самое повторяем для столбцов. Если их много – файл может зависнуть. В таком случае проще использовать небольшой макрос.

Нажмите Alt+F11 или кнопку Visual Basic на вкладке Разработчик (как ее активировать — показывализдесь), вставьте новый модуль через меню Insert — Module и скопируйте туда код макроса:

• Sub УдалениеЛишнихЯчеек()
• For Each Sht In ActiveWorkbook.Worksheets
• Sht.UsedRange
• Next Sht
• End Sub
• Чтобы запустить его — на той же вкладке (1) нажмите кнопку «Макросы» (2), выберите в списке «УдалениеЛишнихЯчеек» (3) и нажмите кнопку «Выполнить» (4).
• После проделывания всех операций обязательно сохраняем книгу.

Запуск макроса

2. Пересохраняем файл в другом формате

Если кто-то еще пользуется файлами в старом формате XLS, но уже сидит на более новом ПО (Excel 2007 и новее), то есть смысл пересохранить файл в один из новых форматов: XLSX, XLSM, XLSB.

Они более современные, лучше оптимизированы, весят меньше и работают быстрее. Самый компактный из них – XSLB.

При сохранении в этом формате размер файла существенно уменьшится (даже если пересохранить в него «новый» XLSX).

3. Удаляем лишние объекты из книги

Часто при копировании в файл данных из сторонних программ (например, 1С) вместе с ними копируются лишние объекты (фигуры, картинки и прочее), которые не всегда можно сразу заметить на листе. Чтобы проверить, есть ли в файле лишние объекты, нужно найти на ленте команду «Найти и выделить» (1) и выбрать «Область выделения» (2). Откроется список объектов листа (3).

Отображение списка объектов листа

Удалить все объекты можно выделив их и нажав клавишу Delete. Чтобы выделить все объекты снова используем команду «Найти и выделить» (1), выбираем пункт «Выделить группу ячеек» (2) и в открывшемся окне выбираем «Объекты» (3).

Вызываем окноУдаляем объекты

Если объектов много, то имеет смысл удалять их простым макросом. Код — ниже. Его нужно запустить поочередно на каждом листе.

1. Sub УдаляемОбъекты()
2. For each shp in ActiveSheet.Shapes
3. shp.delete
4. Next shp
5. End Sub

4. Уменьшаем размер сводных таблиц

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

Для отключения этой опции выберите нужную сводную таблицу, перейдите в ее Параметры (правая кнопка мыши — Параметры сводной таблицы) и на вкладке Данные (1) снимите галочку с пункта «Сохранять исходные данные вместе с файлом» (2).

Изменение параметров сводной таблицы

5. Заменяем формулы на значения

Если в файле содержится очень много формул, то нужно постараться везде, где это возможно, заменить их на значения. Постоянный пересчет сильно замедляет работу файла. И хоть его можно отключить (как — смотри здесь), рекомендуем избавляться ото всех лишних формул на листах.

6. Удаляем лишнее форматирование

Красивые документы нравятся всем, но чем более пёстрый лист у Вас получился, тем медленнее будет работать файл. А условное форматирование сказывается на быстродействии еще больше, так как основано на формулах и постоянно пересчитывается. Рекомендуем удалять все лишние форматы, оставляя только то, что действительно нужно (как очистить форматы — смотри здесь). Минимализм сейчас в моде.

Чтобы удалить лишние правила условного форматирования выбираем на вкладке «Главная» инструмент «Условное форматирование», кнопка «Управление правилами». В открывшемся диспетчере выбираем весь лист (1), выделяем лишнее правило (2) и удаляем его (3). Повторяем, пока не удалим всё лишнее.

Удаление лишних правил условного форматирования

И помните — никогда не используйте формат целиком для всего столбца или строки!

7. Удаляем лишние имена

Именованные диапазоны используют далеко не все, но обнаружить их в своем файле может каждый (например, они могли попасть туда при импорте или копировании данных). Весь список можно увидеть в диспетчере имен («Формулы» — «Диспетчер имен»). Там же можно и удалить все ненужные имена.

Диспетчер имен

Иногда в книге могут оказаться скрытые имена. Для их удаления придется воспользоваться небольшим макросом.

• Sub УдалениеСкрытыхИмен()
• For Each Nm In ActiveWorkbook.Names
• If Nm.Visible = 0 Then
•   Nm.Delete
• End If
• Next Nm
• End Sub

Заключение

Предложенные выше способы помогают решить подавляющее большинство «решаемых» проблем с быстродействием и размером книги.

Однако, помните — если ваш рабочий файл содержит множество сводных таблиц, сложных формул, связи с другими документами, диаграммы и условные форматы — то вполне может быть, что и после всех проделанных операций он будет работать медленно и весить много. Это естественно, так как вычислительные способности компьютеров и самого Excel не безграничны.

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

@ExEvFeedbackBot

Источник: https://zen.yandex.ru/media/id/59affb7afd96b11e8eadd771/59b911eca8673183c5047d3f

Производительность Excel — советы по оптимизации препятствий производительности

• 10/06/2017
• Время чтения: 36 мин

Применимо к: Excel | Excel 2013 | Office 2016 | СРЕДApplies to: Excel | Excel 2013 | Office 2016 | VBA

Следуйте этим советам для оптимизации многих часто встречающихся проблем производительности в Excel.Follow these tips for optimizing many frequently occurring performance obstructions in Excel.

Узнайте, как увеличить производительность, связанную с типами ссылок и ссылок.Learn how to improve performance related to types of references and links.

Не используйте ссылки вперед и назадDo not use forward referencing and backward referencing

Чтобы повысить четкость и избежать ошибок, разработайте формулы так, чтобы они не ссылались на пересылку (справа или ниже) на другие формулы или ячейки.

To increase clarity and avoid errors, design your formulas so that they do not refer forward (to the right or below) to other formulas or cells.

ПереСылка ссылок обычно не влияет на производительность вычислений, за исключением случаев, когда для первого вычисления книги может потребоваться больше, чем при наличии большого количества формул, которые должны иметь Вычисление отложено.

Forward referencing usually does not affect calculation performance, except in extreme cases for the first calculation of a workbook, where it might take longer to establish a sensible calculation sequence if there are many formulas that need to have their calculation deferred.

Минимизация использования циклических ссылок в итерацииMinimize use of circular references with iteration

Вычисление циклических ссылок с итерациями выполняется медленно, так как требуется несколько вычислений, и эти вычисления являются однопотоковыми.Calculating circular references with iterations is slow because multiple calculations are needed, and these calculations are single-threaded.

Часто циклические ссылки можно «раскрутить», используя «накат», чтобы итеративные вычисления больше не требовалось.Frequently you can «unroll» the circular references by using algebra so that iterative calculation is no longer needed.

Например, при расчете денежных средств и расчете процентов попробуйте вычислить движение денежных средств перед процентом, рассчитать процент, а затем рассчитать движение денежных средств, включая процент.

Excel вычисляет циклические ссылки на листе без учета зависимостей.Excel calculates circular references sheet-by-sheet without considering dependencies. Таким образом, если циклические ссылки охватывают несколько листов, скорость вычислений обычно снижается.

Therefore, you usually get slow calculation if your circular references span more than one worksheet. Попробуйте переместить все циклические ссылки на один лист или оптимизировать последовательность вычислений на листе, чтобы избежать ненужных вычислений.

Try to move the circular calculations onto a single worksheet or optimize the worksheet calculation sequence to avoid unnecessary calculations.

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

Before the iterative calculations start, Excel must recalculate the workbook to identify all the circular references and their dependents. Этот процесс эквивалентен двум или трем итерациям вычислений.

This process is equal to two or three iterations of the calculation.

После определения циклических ссылок и зависимостей на каждой итерации приложение Excel вычисляет не только все ячейки в циклической ссылке, но также и ячейки, зависящие от ячеек в цепочке циклической ссылке, и переменные ячейки с зависимостями.

After the circular references and their dependents are identified, each iteration requires Excel to calculate not only all the cells in the circular reference, but also any cells that depend on the cells in the circular reference chain, together with volatile cells and their dependents.

If you have a complex calculation that depends on cells in the circular reference, it can be faster to isolate this into a separate closed workbook and open it for recalculation after the circular calculation has converged.

Важно сократить число ячеек в циклическом вычислении и время, затрачиваемое на вычисление таких ячеек.It is important to reduce the number of cells in the circular calculation and the calculation time that is taken by these cells.

По возможности избегайте связей между книгами; они могут быть медленными, легко разорваны и не всегда просты в поиске и исправлении.Avoid inter-workbook links when it is possible; they can be slow, easily broken, and not always easy to find and fix.

Использование небольшого числа крупных книг обычно (но не всегда) более эффективно, чем использование множества небольших книг.Using fewer larger workbooks is usually, but not always, better than using many smaller workbooks.

Некоторые исключения из этого могут быть вызваны тем, что у вас есть несколько интерфейсных вычислений, которые редко пересчитываются, что имеет смысл поместить их в отдельную книгу или при недостатке ОПЕРАТИВной памяти.

Some exceptions to this might be when you have many front-end calculations that are so rarely recalculated that it makes sense to put them in a separate workbook, or when you have insufficient RAM.

Попробуйте использовать простые прямые ссылки на ячейки в закрытых книгах.Try to use simple direct cell references that work on closed workbooks. Это позволяет избежать пересчета всех связанных книг при пересчете любых книг.

Also, you can see the values Excel has read from the closed workbook, which is frequently important for debugging and auditing the workbook.

Если не удается избежать использования связанных книг, попробуйте оставить все нужные книги открытыми (при этом книги, на которые указывают связи, следует открывать после того, как открыты книги, из которых исходят связи).If you cannot avoid using linked workbooks, try to have them all open instead of closed, and open the workbooks that are linked to before you open the workbooks that are linked from.

Разбиение книги на множество листов обычно делает ее более простой в использовании, однако снижает производительность вычислений ссылок на другие листы относительно ссылок в пределах одного листа.Using many worksheets can make your workbook easier to use, but generally it is slower to calculate references to other worksheets than references within worksheets.

Минимизация используемого диапазонаMinimize the used range

Для экономии памяти и уменьшения размера файла Excel пытается хранить сведения только об используемой области листа.To save memory and reduce file size, Excel tries to store information about only the area on a worksheet that was used. Эта область называется используемый диапазон.This is called the used range.

Некоторые операции редактирования и форматирования охватывают область, значительно превышающую диапазон, который на данный момент считается используемым.Sometimes various editing and formatting operations extend the used range significantly beyond the range that you would currently consider used. Это может привести к проблемам, связанным с производительностью и размером файла.

This can cause performance obstructions and file-size obstructions.

С помощью сочетания клавиш Ctrl + End можно проверить видимый диапазон на листе.You can check the visible used range on a worksheet by using Ctrl+End. В таком случае необходимо удалить все строки и столбцы ниже и справа от текущей ячейки, а затем сохранить книгу.

Where this is excessive, you should consider deleting all the rows and columns below and to the right of your real last used cell, and then saving the workbook. Перед этим следует создать резервную копию.Create a backup copy first.

Если некоторые формулы содержат диапазоны, указывающие или ссылающиеся на удаленные области, эти диапазоны будут уменьшены в размере или получат значение #Н/Д.

If you have formulas with ranges that extend into or refer to the deleted area, these ranges will be reduced in size or changed to #N/A.

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

Для этого можно использовать в формулах большой диапазон, который выходит далеко за пределы текущей области данных.You can do this by using a large range in your formulas that extends well beyond your current data boundaries.

Тем не менее, в некоторых случаях это может привести к неэффективным вычислениям и сложностям при обслуживании, поскольку при удалении строк и столбцов диапазон может быть уменьшен без уведомления.

However, this can cause inefficient calculation under certain circumstances, and it is difficult to maintain because deleting rows and columns can decrease the range without you noticing.

Использование ссылок на структурированные таблицы (рекомендуется)Use structured table references (recommended)

Начиная с Excel 2007, вы можете использовать структурированные ссылки на таблицы, которые автоматически разворачиваются и отключают по мере увеличения или уменьшения размера указанной таблицы.Starting in Excel 2007, you can use structured table references, which automatically expand and contract as the size of the referenced table increases or decreases.

Такой подход имеет ряд преимуществ:This solution has several advantages:

• Существует меньше недостатков производительности, чем альтернативные ссылки на столбцы и динамические диапазоны.Fewer performance disadvantages exist than the alternatives of whole column referencing and dynamic ranges.
• Простота использования нескольких таблиц на одном листе.It is easy to have multiple tables of data on a single worksheet.
• Возможность расширения и сокращения внедренных в таблицу формул в соответствии с изменением размера области данных.Formulas that are embedded in the table also expand and contract with the data.

Кроме того, используйте ссылки на столбцы и строки целикомAlternatively, use whole column and row references

Альтернативный подход заключается в использовании ссылки на столбец целиком, например, $A:$A.An alternative approach is to use a whole column reference, for example $A:$A.

Эта ссылка возвращает все строки в столбце A. В этом случае все добавляемые данные всегда будут включаться в ссылку.This reference returns all the rows in Column A.

Therefore, you can add as much data as you want, and the reference will always include it.

Такой подход имеет свои преимущества и недостатки:This solution has both advantages and disadvantages:

• Многие встроенные функции Excel (SUM, SUMIF) эффективно вычисляют ссылки на столбцы целиком, поскольку они автоматически распознают последнюю использованную строку в столбце.Many Excel built-in functions (SUM, SUMIF) calculate whole column references efficiently because they automatically recognize the last used row in the column. Тем не менее, функции вычисления массивов, такие как SUMPRODUCT

Источник: https://docs.microsoft.com/ru-ru/office/vba/excel/concepts/excel-performance/excel-tips-for-optimizing-performance-obstructions