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 точек данных в каждом столбце).

Уменьшить размер файла Excel - тест 1 оригиналВ результате размер этой книги составляет 143 МБ (и ее сохранение и открытие занимает довольно много времени).

Уменьшить размер файла Excel - результат теста 1

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

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

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

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

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

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

Вот шаги, чтобы сделать это:

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

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

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

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

Уменьшить размер файла Excel - бинарный файл xlsb

Что такое XLSB?

Когда вы сохраняете файл Excel (.xlsx или .xlsm), он сохраняется в формате XML.

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

Уменьшить размер файла Excel - папка xlsx zip

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

Уменьшить размер файла Excel - лист xlsx zip

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

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

Уменьшить размер файла Excel - лист xlsb zip folder

Вы заметите, что и 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. В диалоговом окне «Перейти к» нажмите «Специальные».Нажмите специальную кнопку после нажатия F5 - в диалоговом окне «Перейти к»
  4. В диалоговом окне «Перейти к спец.» Выберите «Формулы».Нажмите на Формулы в специальном диалоговом окне Перейти к
  5. Нажмите ОК.

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

Теперь используйте приведенные ниже шаги для преобразования этих формул в значения:

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

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

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

Я создал файл Excel с 2,7 миллионами точек данных (1048576 * 26), а затем добавил три столбца с формулами (SUM, COUNT и AVERAGE).

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

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

Уменьшить размер файла Excel - с формулами и без

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

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

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

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

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

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

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

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

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

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

Размер файла уменьшен на TinyPNG

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

Вот шаги, чтобы сжать изображение в Excel:

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

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

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

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

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

Уменьшить размер файла Excel - сжатие изображения

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

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

Удалить Pivot Cache для экономии места

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

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

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

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

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

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

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

Размер файлов Excel с помощью сводной таблицы

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

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

  1. Сохраните исходные данные и удалите сводный кэш
  2. Сохраните сводный кэш и удалите исходные данные.

Давайте посмотрим на оба эти метода.

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

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

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

Ниже представлена ​​разница в размере при сохранении файла Excel с Pivot Cache и без него.

Размер файла с и без сводного кэша

Вот шаги, чтобы НЕ сохранять сводный кэш:

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

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

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

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

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

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

Уменьшите размер файла Excel - не сохраняя исходные данные в сводной таблице

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

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

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

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

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

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

Вот шаги, чтобы сжать файл Excel (для Windows):

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

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

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

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

Уменьшение размера в Excel путем архивирования файла

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

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

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

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

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

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

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

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

Уменьшить размер файла Excel - форматирование теста 2

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

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

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

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

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

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

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

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

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

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

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

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

Разница в размере, кажется, составляет всего несколько килобайт.

Разница в размере файла Excel с условным форматированием

Также Читайте : Советы, чтобы сделать Медленные Таблицы Excel Быстро

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

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

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

Добавить комментарий

Ваш адрес email не будет опубликован. Обязательные поля помечены *

один × 3 =