Excel файл много весит что делать

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

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

Если в один прекрасный момент вы осознаете, что ваш основной рабочий файл в Excel разбух до нескольких десятков мегабайт и во время открытия файла можно смело успеть налить себе кофе, то попробуйте пробежаться по описанным ниже пунктам - возможно один или несколько из них укоротят вашего "переростка" до вменяемых размеров и разгонят его "тормоза" :)

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

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

Проверить это просто – нажмите на клавиатуре сочетание клавиш Ctrl+End и посмотрите куда переместится активная ячейка. Если она прыгнет на фактическую последнюю ячейку с данными на листе – отлично. А если вдруг ускачет сильно правее и/или ниже "в пустоту" – дело плохо: все эти ненужные пустые ячейки 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).

Уменьшение размера файла Excel электронных таблиц

Excel для Microsoft 365 Excel 2021 Excel 2019 Excel 2016 Excel 2013 Еще...Меньше

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

Сохранение таблицы в двоичном формате (XSLB)

Чтобы уменьшить размер файла, можно сохранить таблицу в виде двоичной книги (XSLB), а не формата по умолчанию (XSLX). По умолчанию используется формат XML, что важно при использовании данных в сторонних программах, так как XML является открытым стандартом. Однако в большинстве целей двоичного формата достаточно и он сохраняется в файле меньшего формата.

1. Перейдите в меню > параметры >Сохранить.

2. В списке Сохранениефайлов в этом формате в списке Сохранить книги выберите Excel Двоичная книга.

Этот параметр задает двоичный формат по умолчанию. Если вы хотите сохранить по умолчанию Excel книгу (.xlsx), но сохранить текущий файл как двоичный, выберите параметр в диалоговом окке Сохранить как. 

Примечание: Если файл находится в SharePoint, возможно, вы не видите параметр Сохранить как. В этом случае вы можете сохранить копию на локальном диске, сохранить ее как двоичное, а затем заменить исходный файл новым двоичным.

1. Перейдите в > сохранить как и, если файл сохраняется впервые, выберите расположение.

2. В списке типов файлов выберите Excel Двоичная книга (XLSB).

Сохранение таблицы в двоичном формате (XSLB)

Чтобы уменьшить размер файла, можно сохранить таблицу в виде двоичной книги (XSLB), а не формата по умолчанию (XSLX). По умолчанию используется формат XML, что важно при использовании данных в сторонних программах, так как XML является открытым стандартом. Однако в большинстве целей двоичного формата достаточно и он сохраняется в файле меньшего формата.

1. Перейдите в меню > параметры >Сохранить.

2. В списке Сохранениефайлов в этом формате в списке Сохранить книги выберите Excel Двоичная книга.

Этот параметр задает двоичный формат по умолчанию.

Если вы хотите сохранить по умолчанию Excel книгу (.xlsx), но сохранить текущий файл как двоичный, выберите параметр в диалоговом окне Сохранить как:

1. Выберите Файл > Сохранить как.

2. В списке Тип файла выберите Excel двоичной книги (XLSB).

Уменьшение количества таблиц

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

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

Сохранение изображений с более низким разрешением

1. Откройте меню Файл, выберите раздел Параметры, а затем — Дополнительно.

2. В области Размер и качество изображениясделайте следующее:

   • Выберите Отменить редактирование данных. Этот параметр удаляет хранимые данные, которые используются для восстановления исходного состояния изображения после его изменения. Обратите внимание, что если удалить данные редактирования, восстановить изображение будет нельзя.

   • Убедитесь, что не выбрано сжатие изображений в файле.

   • В списке Разрешение по умолчанию выберите разрешение 150ppi или более низкое. В большинстве случаев разрешение не должно быть выше.

Сжатие рисунков

1. Выберите рисунок в документе. На ленте появится вкладка Формат рисунка.

2. На вкладке Формат рисунка в группе Настройка выберите Сжать рисунки.

3. В области Параметры сжатиясделайте следующее:

   • Чтобы сжать все рисунки в файле, снимайте снимок Применить только к этому рисунку. Если этот параметр выбран, изменения, внесенные здесь, будут влиять только на выбранный рисунок.

   • Выберите Удалить обрезанные области рисунков. Этот параметр удаляет обрезанные данные рисунка, но вы не сможете их восстановить.

4. В области Разрешениесделайте следующее:

Не сохранения кэша данных с файлом

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

1. Выберите любую ячейку в таблице.

2. На вкладке Анализ таблицы в группе Таблица выберите Параметры.

3. В диалоговом окне Параметры таблицы выберите вкладку Данные и сделайте следующее:

   • Чтобы сохранить исходные данные с файлом, с помощью сохранения исходных данных с помощью сохранения.

   • Выберите Обновить данные при открытии файла.

Уменьшение размера файлов PowerPoint презентаций

Уменьшение размера файлов документов Word

8 способов уменьшить размер файла Excel (которые действительно работают)

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

Иногда легко избавиться от лишнего жира и контролировать размер файла Excel, а иногда нет.

А поскольку Excel не является веб-инструментом, большинство из нас работает и сотрудничает, отправляя файлы Excel по электронной почте. А большим файлом Excel весом 50 МБ может быть сложно поделиться с коллегами/клиентами.

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

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

Примечание. Влияние этих методов на уменьшение размера файла Excel зависит от ваших данных. Для некоторых это может помочь уменьшить размер на 50%, а для некоторых это может привести к уменьшению размера только на 5%.

В этом руководстве рассматриваются:

Советы по уменьшению размера файла Excel

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

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

В зависимости от вашей рабочей тетради вы можете использовать один или несколько из этих советов.

Удалить ненужные листы/данные

Лучшие решения часто бывают самыми простыми.

И этот метод больше основан на здравом смысле, чем на волшебстве Excel.

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

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

Результаты теста:

Я создал файл Excel с 10 рабочими листами, каждый из которых содержит 2,6 миллиона точек данных (26 столбцов по 100 000 точек данных в каждом столбце).

Полученный размер этой книги составляет 143 МБ (сохранение и открытие занимает довольно много времени).

Теперь, когда я удалил пять листов, размер уменьшился вдвое (дух).

Как видите, данные, которые есть у вас на листе, имеют прямую корреляцию с размером файла.

Преобразование в двоичный формат (XLSB)

Знаете ли вы, что простое сохранение файла Excel в формате XLSB может уменьшить размер файла?

Да, это правда.

Все, что вам нужно сделать, это при сохранении книги использовать формат .xlsb.

Вот шаги для этого:

1. Перейдите на вкладку «Файл».
2. Нажмите «Сохранить как».
3. Щелкните Обзор.
4. В диалоговом окне "Сохранить как" измените тип файла на Двоичная книга Excel (.xlsb)

Результаты теста

У меня была рабочая книга Excel, которая занимала 14,6 МБ места.

После преобразования того же файла в формат XLSB мне удалось уменьшить его размер до 10,4 МБ (уменьшение размера примерно на 28%).

Что такое XLSB?

При сохранении файла Excel (.xlsx или .xlsm) он сохраняется в формате XML.

Например, если я изменю расширение файла XLSX на ZIP, а затем открою его, вот что я получу.

И если я перейду в папку «xl», а затем перейду в папку с рабочими листами, он покажет мне рабочие листы в этой книге (и эти рабочие листы сохраняются в виде XML-документа).

Теперь давайте сделаем то же самое с файлом XLSB.

Поэтому я сначала меняю расширение файла на ZIP, затем открываю его и нахожу лист1. Вот что вы получите:

Вы заметите, что и XLSX, и XLSB сохраняют рабочий лист в другом формате.

XLSX/XLSM сохраняет его как файл XML, а XLSB сохраняет его как двоичный файл (.bin).

Не вдаваясь в технические подробности, XML-файлы имеют большой размер по сравнению с двоичным файлом.

Таким образом, при сохранении книги Excel в формате XLSB размер файла уменьшается. В приведенном выше примере уменьшение размера составило ~30%.

XLSB против XLSX/XLSM

Когда дело доходит до использования формата XLSB, уменьшение размера является огромным преимуществом. Но вам нужно быть немного осторожным при работе с форматом XLSB.

Преимущества формата XLSB:

1. Уменьшает размер файла Excel. Это может быть полезно, когда у вас есть большие файлы, и вы хотите поделиться ими по электронной почте. Вы можете быстро уменьшить размер, сохранив его в формате XLSB.
2. Файлы XLSB обычно открываются и сохраняются быстрее, чем файлы XML. Основываясь на проведенных мной ограниченных тестах, файлы XLSB открывались и сохранялись на 25-50% быстрее. Эта разница заметна только в больших файлах Excel. С файлами меньшего размера и XLSB, и XLSX работают быстро.

Несмотря на то, что XLSB выглядит великолепно, есть несколько причин, по которым вам следует придерживаться файлов XLSX/XLSM:

1. В формате XLSB у вас нет возможности узнать, содержит ли он макрос или нет. Это делает его более рискованным, поскольку кто-то может использовать его для выполнения вредоносного кода.
2. Файлы XLSB закодированы в проприетарном формате файлов bin, а XML — это читаемый формат файлов с открытым исходным кодом. Это означает, что если вы используете сторонние инструменты с Excel, лучше использовать формат XML.
3. Power Query не может прочитать данные из файла XLSB. Поэтому, если вы используете Power Query, лучше иметь данные в форматах XLSX/XLSM.

Как правило, если размер вашего файла небольшой (менее 5 МБ), лучше придерживаться форматов XLSX/XLSM.

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

Вот хорошая статья о формате XLSB.

Удаление ненужных формул

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

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

Вот шаги для быстрого преобразования формул в значения в Excel:

1. Выберите весь лист/набор данных.
2. Нажмите клавишу F5 .
3. В диалоговом окне «Переход» нажмите «Специальный».
4. В диалоговом окне «Перейти к специальному» выберите «Формулы».
5. Нажмите OK.

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

Теперь выполните следующие действия, чтобы преобразовать эти формулы в значения:

1. Скопируйте выбранные ячейки (control + C)
2. Перейдите на вкладку «Главная».
3. В группе «Буфер обмена» нажмите «Вставить».
4. Щелкните значок «Вставить значение».

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

Результаты испытаний

Я создал файл Excel с 2,7 миллионами точек данных (1048576*26), а затем добавил три столбца с формулами (СУММ, СЧЁТ и СРЗНАЧ).

Затем я преобразовал эти формулы в значения и сохранил файл под другим именем.

Ниже приведен результат:

Файл без формул примерно на 8% меньше по размеру (и обратите внимание, что у меня было только 3 столбца формул).

Если у вас много формул, это может увеличить размер.

ПРИМЕЧАНИЕ : Изменчивые формулы могут сильно увеличить размер вашего файла. В рамках теста, когда я заменил формулу СУММ на изменчивую формулу (СМЕЩЕНИЕ), это привело к скачку размера файла (со 186 МБ до 198 МБ). Поэтому, если у вас есть изменчивые формулы, которые вы можете заменить энергонезависимыми формулами или преобразовать их в значения, это поможет вам уменьшить размер файла.

Сжатие изображений (до и после загрузки)

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

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

При работе с изображениями лучший способ уменьшить размер файла Excel — сжать изображения перед их вставкой в ​​Excel.

Хорошим онлайн-инструментом для этого является TinyPNG (или вы можете использовать Photoshop или любой другой подобный инструмент).

Это бесплатный инструмент, который может уменьшить размер изображения до 90%, сохраняя при этом достойное качество изображения.

Ниже приведен скриншот, на котором мне удалось сжать изображение размером 3,9 МБ до 331 КБ с минимальной потерей качества.

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

Вот шаги для сжатия изображения в Excel:

1. Выберите изображение, которое вы хотите сжать.
2. Перейдите на вкладку Формат обработки изображений. Это контекстная вкладка, которая появляется только при нажатии на картинку.
3. В группе «Настройка» нажмите «Сжать изображение».
4. В диалоговом окне «Сжатие изображений»:
   • Снимите флажок «Применить только к этому изображению», если вы хотите сжать все изображения в книге.
   • Выберите Электронная почта (96 пикселей на дюйм) для максимального сжатия.
5. Нажмите OK.
6. Сохраните файл.

Это сожмет все изображения и поможет уменьшить размер файла книги Excel.

Результаты испытаний:

Я использовал параметр сжатия изображения в Excel, используя одно изображение.

Ниже приведены результаты:

При использовании параметра сжатия изображения размер файла уменьшился с 445 КБ до 43 КБ (уменьшение размера файла примерно на 90 %).

Это также зависит от изображения. Изображение, которое уже сжато, может не привести к значительному уменьшению размера.

Удаление сводного кэша для экономии места

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

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

Сводной кэш — это объект, содержащий реплику источника данных.

Хотя вы его не видите, он является частью книги и подключен к сводной таблице. Когда вы вносите какие-либо изменения в сводную таблицу, она не использует источник данных, а использует сводной кэш.

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

Поскольку вы создаете реплику набора данных, она занимает некоторое пространство.

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

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

Теперь есть два способа уменьшить размер файла при работе со сводными таблицами.

1. Сохранить исходные данные и удалить сводную кэш-память
2. Сохранить сводную кэш-память и удалить исходные данные

Давайте рассмотрим обе эти техники.

Сохранить исходные данные и удалить сводную кэш-память

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

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

Ниже показана разница в размере при сохранении файла Excel со сводным кэшем и без него.

Вот шаги для НЕ сохранения сводного кэша:

1. Выберите любую ячейку в сводной таблице.
2. Перейдите на вкладку Анализ.
3. Щелкните значок «Параметры» сводной таблицы.
4. В диалоговом окне «Параметры сводной таблицы» щелкните вкладку «Данные».
5. В параметрах данных сводной таблицы снимите флажок «Сохранить исходные данные с файлом». Этот параметр гарантирует, что сводной кэш не будет сохранен при сохранении книги (и помогает уменьшить размер файла Excel).
6. Установите флажок «Обновлять данные при открытии файла». Этот параметр гарантирует, что как только вы откроете книгу, сводная таблица будет обновлена, а сводной кэш будет создан автоматически. Если вы не отметите этот параметр, вам придется обновить сводную таблицу вручную, чтобы сгенерировать сводной кэш.
7. Нажмите OK.

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

Сохранение сводного кэша и удаление исходных данных

Другой способ уменьшить размер файла Excel — удалить исходные данные сводной таблицы и оставить только сводной кэш.

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

Ниже приведены результаты сохранения файла Excel без исходных данных.

Для этого просто удалите данные (либо удалив данные, либо рабочий лист, содержащий данные).

Если вы хотите вернуть исходные данные, дважды щелкните ячейку «Общая сумма» сводной таблицы, и она мгновенно сгенерирует весь набор данных на новом рабочем листе.

Сжать файл (ZIP IT)

Это то, что вы можете сделать вне приложения Excel.

Когда вы заархивируете файл Excel, вы сразу увидите уменьшение размера примерно на 10-15%.

Затем вы можете отправить этот заархивированный файл по электронной почте, и человек, получивший его, может разархивировать файл и использовать книгу Excel.

Вот шаги для архивирования файла Excel (для Windows):

1. Щелкните правой кнопкой мыши файл Excel
2. Наведите курсор на параметр «Отправить».
3. Нажмите «Сжатая (заархивированная) папка».

Будет создан заархивированный файл для выбранной книги Excel.

Вы также заметите уменьшение размера файла (как показано ниже).

Результаты тестов

С помощью этой методики мне удалось уменьшить размер файла рабочей книги Excel на 8%.

Этот метод лучше работает с файлами размером от 20 до 50 МБ. По мере увеличения размера файла процентное уменьшение не очень велико.

Удалить форматирование данных

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

Хотя, основываясь на своих тестах, я заметил, что это не оказывает большого влияния.

Ниже приведены размеры двух книг Excel: одна с 2,6 миллионами точек данных (на одном листе), а другая с теми же данными, но с примененным к ним форматированием (цвет ячейки, граница и курсив).

Результаты тестов

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

Размер файла с данными составляет 14,6 МБ, а размер файла с данными и форматированием — 14,9 МБ.

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

Вот шаги, чтобы мгновенно удалить все форматирование, сохранив при этом данные:

1. Выберите весь набор данных (из которого вы хотите удалить форматирование).
2. Перейдите на вкладку «Главная».
3. В группе «Редактирование» нажмите «Очистить».
4. Нажмите «Очистить форматы».
5. Сохраните книгу.

Удалить условное форматирование

Как и обычное форматирование данных, условное форматирование увеличивает размер файла.

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

Тем не менее, рекомендуется удалить условное форматирование из любого набора данных, где оно вам не нужно.

Помимо размера, есть еще одна веская причина для удаления условного форматирования – СКОРОСТЬ.

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

Результаты теста

Я создал файл с 27,2 миллионами точек данных и применил к нему 5 правил условного форматирования.

Разница в размере всего несколько КБ.

  Читайте также  : Советы по ускорению медленных электронных таблиц Excel 

Советы по повышению производительности Excel

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

1.  Избегайте использования изменчивых формул. Помимо увеличения размера файла, изменчивые формулы также замедляют работу ваших книг. Примеры изменчивых функций включают RAND, TODAY, OFFSET, INDIRECT и т.  д. 9.0060
2. Если вам нужно создать сводку большого набора данных вместо использования формул, посмотрите, можно ли вместо этого использовать сводную таблицу. Сводная таблица работает быстро и интуитивно понятна по сравнению с формулами.
3. Если вы извлекаете данные из нескольких источников или объединяете данные из нескольких файлов, используйте Power Query для получения и преобразования данных. Вы можете сохранить данные как соединение Power Query, а не хранить их на листе. Вы можете легко создать сводную таблицу, используя подключения, созданные с помощью Power Query.
4. Преобразуйте свои табличные данные в таблицу Excel и используйте таблицу Excel в формулах. Это ускоряет работу вашей книги, а формулами, созданными с использованием структурированных ссылок в таблицах Excel, легче управлять (поскольку они более интуитивно понятны).
5. Избегайте ссылок на всю строку или столбец в формуле. Это поможет вашим формулам быть более эффективными.
6. Если в вашей книге много формул, отключите автоматический расчет и переключитесь на ручной расчет. При этом каждый раз, когда в рабочих листах происходит изменение, формулы не будут пересчитываться. Вы можете внести это изменение, перейдя на вкладку «Формула» и изменив «Параметры расчета».

Надеюсь, вы найдете метод и советы из этого руководства полезными.

Что еще я могу добавить в этот урок, чтобы сделать его более полезным? Пожалуйста, дайте мне знать в разделе комментариев.

Вам также могут понравиться следующие учебные пособия по Excel:

• Как восстановить несохраненные файлы Excel.
• 100+ вопросов для собеседования в Excel.
• Лучшие книги Excel (для повышения уровня ваших навыков работы с Excel).
• Более 200 сочетаний клавиш Excel.
• 10 способов очистки данных в Excel.
• Как включить автосохранение в Excel

7 простых способов уменьшить размер файла Excel • UpSlide

7 простых способов уменьшить размер файла Excel

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

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

Откройте для себя семь простых способов сжатия файлов Excel в статье ниже.

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

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

Вы можете легко определить, какие листы являются самыми тяжелыми в вашем файле Excel . Для этого сделайте копию файла Excel и выполните следующие действия:

1. Откройте проводник , где сохранен ваш Excel
2. Отображение расширений имен файлов на вкладке «Вид»
3. Измените расширение копии файла Excel на «.zip»
4. Затем откройте заархивированную папку и перейдите к шагу 9.0035 xl > листов
5. Сортировать по размеру

Обнаружение самых тяжелых листов Excel может помочь вам выбрать, с какого из них начать. Например, на «sheet26.xml» приходится почти треть глобального объема. Обычно это соответствует листу n+1 в вашем файле Excel. Итак, в приведенном выше примере мы могли бы начать с обрезки двадцать седьмого листа.

Будьте осторожны! Правило n+1 учитывает скрытые листы. Если у вас много скрытых листов, может быть сложно найти нужный. Один из способов узнать наверняка, что у вас есть нужный лист, — узнать точное имя. Вот подробное руководство по поиску имени листа.

Теперь пришло время погрузиться в наши семь советов по уменьшению размера файла Excel:

Условное форматирование пригодится, когда вам нужно выделить определенные данные. Но это может быстро сделать ваш файл Excel тяжелым . Мы предлагаем удалить условное форматирование из вашей электронной таблицы, как только вы закончите с ним. Для этого необходимо:

• Выберите несколько отформатированных ячеек на листе
• Перейдите на вкладку Главная > Условное форматирование > Очистить правила > Очистить выбранные ячейки

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

Знаете ли вы, что листы Excel могут содержать до миллиона строк? Хороший способ уменьшить размер файла Excel — избавиться от пустых строк и столбцов. Вот как это сделать:

• Выбрать все неиспользуемые строки и столбцы (сочетание клавиш = Ctrl+Shift+Right / Стрелка вниз )
• Щелкните правой кнопкой мыши > Удалить

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

• Выберите ячейки, с которыми вы работаете (вы можете использовать сочетание клавиш Ctrl+A , чтобы выбрать весь лист)
• Копировать и Специальная вставка: значения

Это также удалит гиперссылки, которые утяжеляют файл.

Откройте для себя 16 основных советов по Excel

Золотое правило — избегать изображений в Excel. Если вам нужно использовать изображения, рекомендуется всегда сжимать их. Вот как:

• Выберите изображение, которое вы хотите осветлить
• Перейдите на вкладку Формат изображения
• Нажмите Сжать
• Мы рекомендуем снять первый флажок Применить только к этому изображению для сжатия всех изображений в вашем файле Excel
• Необязательно: вы можете выбрать желаемое разрешение (мы рекомендуем отправить по электронной почте).

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

• Выбрать все отформатированные строки, столбцы и ячейки, не содержащие данных
• Главная > Редактирование > Очистить формат

Хотите изучить 6 лучших финансовых функций Excel?

Люди, которые работают с большими файлами с большим количеством таблиц и формул, знают, что такое часы (не те, что у вас на запястье!) Многочисленные часы утяжелят ваши электронные таблицы. Как только вы закончите с ними, рекомендуется удалить их из ваших файлов:

• Перейти на вкладку Формула > Окно просмотра
• Выбрать все и нажать Удалить

ВАЖНО: Мы рекомендуем выполнить этот шаг последним, так как он может нарушить формулы и не может быть отменен! Заранее сделайте копию.

Начнем с листов!

• Щелкните лист правой кнопкой мыши
• Выбрать Показать
• Повторите предыдущие шаги для каждого листа

Теперь давайте проделаем то же самое со столбцами и строками:

• Выберите весь лист ( Ctrl+A )
• Щелкните правой кнопкой мыши строки/столбцы
• Нажмите Показать

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

---

Learn more

• 
  Почему нет режима модема на айфон
• 
  Как отформатировать флешку в fat32 windows 10
• 
  Браузер на смартфон
• 
  Флешки для apple
• 
  Установка ipa на iphone без джейлбрейка
• 
  Телевизор самсунг родительский контроль
• 
  Mikrotik hap lite сброс на заводские настройки
• 
  Icq номер что это
• 
  Антивирус на нокиа люмия
• 
  Можно ли подключить bluetooth наушники к ps4
• 
  Как войти в полноэкранный режим