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

Обновлено: 21.11.2024

И все в них ужасно.

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

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

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

К счастью, в самом Excel есть множество способов исправить это.

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

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

1. Удалите «пустое место» на листах

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

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

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

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

Пример «используемого диапазона» в Excel

Если вы нажмете Ctrl + End на любом листе в файле Excel, вы увидите, что такое «последняя использованная ячейка». Если этот ярлык уводит вас на много строк (или столбцов) за пределы ваших данных, это означает, что все эти ячейки увеличивают размер файла без какой-либо причины.

Вот пример. У меня есть данные, которые идут до столбца F и строки 11, поэтому я должен ожидать, что используемый диапазон идет до ячейки F11 (даже если в этой конкретной ячейке ничего нет).

Но когда я нажимаю Ctrl + End, происходит следующее:

(Примечание: чтобы получить этот результат, я просто ввел значение в ячейку K20, а затем удалил его.)

Как исправить «используемый диапазон» в Excel

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

  1. Выберите первую пустую строку (или столбец). Это легко сделать с помощью сочетаний клавиш, нажав Shift + Пробел для текущей строки или Ctrl + Пробел для текущего столбца.
  2. Нажмите Ctrl + Shift + Стрелка вниз (или стрелка вправо), чтобы перейти в самый низ или вправо листа Excel.
  3. Нажмите правой кнопкой мыши и выберите "Удалить". НЕ НАЖИМАЙТЕ просто кнопку Удалить — она не будет иметь такого же эффекта, все, что она сделает, — это очистит содержимое ячеек!

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

2. Найдите и удалите ненужные скрытые листы

Если вы получили файл Excel от кого-то еще, они могли скрыть некоторые дополнительные листы как часть файла, но скрыли их по какой-то причине.

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

3. Сохраняйте файлы в двоичном формате (.xlsb)

Если у вас есть файл Excel с большим количеством необработанных данных и формул, сохраните его в двоичном формате Excel (.xlsb).

Все макросы и VBA по-прежнему будут сохранены в этом формате, поэтому вам не нужно беспокоиться о потере функциональности.

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

Иногда вы можете уменьшить размер файлов до 20 % от их исходного размера!

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

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

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

Я могу вспомнить несколько моих бывших начальников, которые возненавидели бы меня, если бы я удалил какое-либо форматирование из файлов, которые мы дали нашим клиентам, но если это внутренний рабочий файл, то не форматируйте его, просто чтобы он выглядел красиво. для тебя. Будьте осторожны, используя для этого параметры «Очистить форматы» — вы также удалите любые форматы даты или валюты из данных, которые могут сбивать с толку (например, 1 января 2016 года изменится на «42370»). Форматирование, такое как границы, выделение и уникальные шрифты, являются хорошими примерами для удаления.

5. Дважды проверьте диапазоны условного форматирования

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

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

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

Хотите больше ярлыков форматирования? Ознакомьтесь с этим обзором ярлыков Excel, чтобы стать молниеносным при форматировании в Excel.

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

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

1. Отключить автоматический расчет формулы

Если расчет по формуле замедляет вашу работу, отключите его.

Большое количество пользователей Excel даже не знает, что этот параметр доступен. Вы можете найти его на вкладке "Формулы" на ленте Excel ближе к концу:

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

Теперь вы можете заметить странное поведение при выборе «Вручную». Если вы отредактируете ячейку, содержащую формулу, и нажмете Enter, эта формула будет вычисляться как обычно. Если вы затем скопируете эту формулу в другие ячейки, предыдущий результат будет отображаться (временно!), пока вы не нажмете клавишу F9. Не беспокойтесь об этом, просто продолжайте работать как обычно, и как только вы нажмете клавишу F9 (или вернете параметр «Расчет» на «Автоматически»), таблица полностью обновится.

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

Последнее замечание по этому поводу: всегда устанавливайте обратно автоматический расчет, когда закончите работу с файлом или собираетесь отправить файл кому-то другому.

У вас есть проблемы с медленными файлами Excel? Откажитесь от работы с неуклюжими и неэффективными книгами Excel и узнайте, как исправить медленный файл Excel.

2. Используйте окно наблюдения, чтобы всегда проверять определенные ячейки

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

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

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

Если вы хотите заполнить поле Имя, выполните следующие действия:

  1. Выберите «Диспетчер имен» в меню «Формула».
  2. Выберите «Создать» и введите любую ссылку в поле «Имя».
  3. Убедитесь, что поле "Относится к:" указывает на ячейку, которую вы используете в окне просмотра.
  4. Нажмите "ОК" и закройте диспетчер имен.

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

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

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

Эти семь функций: RAND(), NOW(), TODAY(), OFFSET(), CELL(), INDIRECT() и INFO(). Microsoft перечисляет их здесь, на странице справки.

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

2. Используйте сводные таблицы или таблицы Excel

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

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

3. Избегайте ссылок на целые строки или столбцы

Я особенно виноват в этом – использую функцию СУММЕСЛИ() или ВПР(), которая просит Excel искать весь столбец, даже если мои данные состоят всего из нескольких сотен строк. Поэтому вместо:

Вы должны иметь:

Таким образом, Excel будет искать только указанный вами диапазон данных, а не все 1 048 576 строк.

4. Избегайте повторных вычислений

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

Давайте рассмотрим очень простой пример:

Допустим, у вас есть формула, содержащая "=$D$5+$E$5", и вы копируете эту формулу в сто ячеек. В этом случае общее количество ссылок на ячейки равно двум сотням, несмотря на то, что уникальных ячеек всего две.

Но если в ячейке F5 есть формула =D5+E5, и вы обновите эти сто ячеек, чтобы они ссылались на ячейку F5, то вы уменьшите количество ссылок на ячейки вдвое. Excel должен вычислить D5+E5 только один раз, а не сто раз.

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

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

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

Та же идея применима и к Microsoft Excel.

Если вы используете ВПР(), ИНДЕКС(), ПОИСКПОЗ(), СУММЕСЛИ() или любую другую функцию Excel, которая ищет определенное значение, ваш лист будет вычисляться намного быстрее, если данные, которые вы ищете, уже отсортировано.

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

Благодаря способу хранения файлов .xls/.xlsx их можно рассматривать как файлы .zip и без проблем переименовывать.

Теперь я не большой поклонник этого процесса, но, похоже, он работает!

Вот процесс:

  1. Перейдите к файлу в проводнике Windows и измените имя файла с .xls/.xlsx на .zip.
  2. Разархивируйте файл так же, как и любой другой ZIP-файл. Вы должны получить несколько папок и XML-файл:
  3. Заархивируйте только что извлеченные файлы в новый ZIP-файл.
  4. Измените имя файла на исходное расширение .xls/.xlsx.

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

    Более того, ваши файлы будут открываться быстрее, а формулы будут быстрее вычисляться!

    Помог ли вам какой-либо из приведенных выше советов или у вас все еще есть проблемы с уменьшением размера файла? Дайте нам знать в комментариях!

    81 комментарий

    Здравствуйте, спасибо за советы выше. Метод, который я нашел наиболее полезным, - это сохранить лист Excel как текст (с разделителями табуляции), закрыть его. Откройте новый лист Excel, перейдите в «Данные», «Получить внешние данные», «Из текста» и импортируйте текстовый файл обратно в Excel.Мне потребовалось меньше минуты, чтобы вернуть данные в формате Excel.

    Привет, Линн, это действительно интересно. Насколько приблизительно уменьшится размер файла в процентах после импорта данных обратно в Excel?

    Я попробовал это (предложение Линн Сэнди), и это сработало отлично. Превратил файл размером 18 928 КБ в 97 КБ.
    Внимание! По умолчанию для ячеек используется «общий» формат, что может привести к непредвиденным последствиям, таким как потеря знаков в длинном десятичном разряде или изменение формата даты. Вы можете отредактировать это при конвертации, если это необходимо. Я уже испробовал большинство средств, перечисленных в статье, но безрезультатно.

    Я обнаружил, что это дает значительные преимущества: переименуйте исходный файл, создайте новый файл Excel, скопируйте содержимое переименованного исходного файла, затем вставьте (как значения, а не обычный элемент управления V) скопированные данные в новый файл. В одном из моих файлов размер файла увеличился с 14,3 МБ до 5,3 МБ, а в другом — с 9,5 МБ до 2,4 МБ.

    Это действительно интересно, Грегг, спасибо, что поделились!

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

    Да! Грегг Лондон — победитель.

    Благодаря его методу размер моего файла увеличился с 2,2 МБ до 23 КБ, как раз то, что мне было нужно.

    Просто зайдите в PIVOT и возьмите данные оттуда, выбрав любое поле, дважды щелкните по числам, вы получите открытую копию листа и вставьте ее в любой другой лист Excel, который будет уменьшен, как файл размером 9 МБ, в несколько КБ.

    Как исправить «используемый диапазон» в Excel: щелкните правой кнопкой мыши, удалите

    Привет, Крис,
    У меня есть 3 листа, содержащие запросы, заказы, платежные реквизиты клиентов за месяцы, которые состоят из 1000 строк и 30–40 столбцов. У меня есть эти данные в таблицах. Размер файла 11 мб из-за сводных таблиц и прочих формул. Как я могу уменьшить размер файла, не затрагивая формулы. Я попытался сохранить файл в двоичном формате, но файл уменьшился до 9 МБ. какие-нибудь хитрости, которые могут помочь??

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

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

    Дайте мне знать, как у вас дела!

    Очень удобно! Сохранение в двоичном формате уменьшило размер моего файла с 8991 КБ до 2771 КБ. Спасибо!

    Если я нажму Ctrl и End, я попаду в ячейку T1048432. Если я удалю строки снизу моих данных (строка 84) до строки 1 048 432 и снова нажму Ctrl и End, я все равно окажусь в ячейке T1048432!

    Как мне избавиться от рядов и рядов небытия?

    Здравствуйте, Эндрю! Попробуйте сохранить файл после удаления соответствующих данных. Это должно решить вашу проблему!

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

    Очень полезный комментарий, спасибо, Клэр!

    Выполнение этого действия на одной вкладке уменьшило размер моего файла с ~30 МБ до примерно 4 МБ.

    По какой-то причине это не работает для меня... У меня есть Office 365 (Microsoft Office 2016) на 64-разрядном рабочем столе. У меня есть приложение размером 44 МБ, которое я пытаюсь уменьшить. Я выбрал нижнюю часть листа, как описано здесь. Сначала я удалил, потом очистил содержимое, потом все очистил… Диапазон пустых ячеек продолжает увеличиваться. Он начался со строки 3130, а сейчас я нахожусь в строке 9184. Раньше у меня был макрос, который «очищал диапазон» (диапазон, с которым я работал… я делал это только для того, чтобы убедиться, что ВСЕ пустые ячейки были очищены в чтобы уменьшить размер моего приложения) и подпрограмму для удаления всех картинок и возврата «курсора» в ячейку «А1». -> По какой-то причине я иду не в том направлении. Кажется, что если я «касаюсь» ячейки (даже чтобы очистить ее), я делаю свое приложение больше. Что я могу сделать?

    Здравствуйте, Кэтрин! Мне интересно, не увеличивает ли ваш скрипт макроса случайно некоторый размер файла, потому что простое «прикосновение» к ячейке не должно увеличивать размер файла. Если хотите, можете отправить код макроса VBA по электронной почте [email protected], и я посмотрю, есть ли ошибочный фрагмент кода, который не работает должным образом?

    Нажатие клавиш CTRL-END и удаление неиспользуемых строк/столбцов с последующим сохранением — это старый метод, который я использовал много лет, но на моем текущем листе он не работает. Также не будет щелкать правой кнопкой мыши / очищать содержимое. Никакого условного форматирования, никаких формул, идущих в конец столбцов и т. д. Размер листа составляет 31 МБ в формате .xlsb, и он наносит вред моему компьютеру. Только отключив calc и используя F9, он будет работать. Он работал нормально в течение нескольких недель, а затем за одну ночь после того, как не было значительных дополнений, он стал медленным, как патока.

    Известно, что рабочие книги 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.

    Вот как это сделать:

    Результаты тестирования

    У меня была книга 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:

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

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

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

    Результаты тестирования

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

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

    Ниже результат:

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

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

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

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

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

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

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

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

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

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

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

    Вот как сжать изображение в Excel:

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

    Результаты тестирования:

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

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

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

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

    Удалите сводную кэш-память, чтобы сэкономить место

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

    Вот как НЕ сохранять сводную кэш-память:

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

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

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

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

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

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

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

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

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

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

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

    Вот как заархивировать файл Excel (для Windows):

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

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

    Результаты тестирования

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

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

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

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

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

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

    Результаты тестирования

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

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

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

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

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

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

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

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

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

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

    Результаты тестирования

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

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

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

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

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

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

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

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

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

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

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

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

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

    1. Откройте Проводник, в котором сохранен ваш Excel
    2. Отображать расширения имен файлов на вкладке «Вид».
    3. Измените расширение копии файла Excel на «.zip»

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

    А теперь советы Флоренс!

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

    Вот семь советов, как уменьшить размер файла Excel прямо сейчас:

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

    Нам в UpSlide нравится работать с легкими файлами. Вот почему мы создали функцию, которая работает за вас: Smart Clean! Он применяет все вышеперечисленные советы в мгновение ока. Один щелчок на вкладке UpSlide в Excel, и ваш файл станет легким как перышко: эта функция может уменьшить размер вашего файла Excel до 99% от его первоначального веса!

    Хотите узнать больше о возможностях UpSlide и о том, как мы помогаем вам сэкономить время при работе с Microsoft Office?

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

    Сохраните электронную таблицу в двоичном формате (.xslb)

    Вы можете уменьшить размер файла, сохранив электронную таблицу в виде двоичной книги (.xslb) вместо формата по умолчанию (.xslx). Формат по умолчанию основан на XML, что важно, если вы используете свои данные в сторонних программах, поскольку XML является открытым стандартом. Однако для большинства целей достаточно двоичного формата, и он сохраняется в файл меньшего размера.

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

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

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

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

    Перейдите в меню "Файл" > "Сохранить как" и, если вы впервые сохраняете файл, выберите папку.

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

    Сохраните электронную таблицу в двоичном формате (.xslb)

    Вы можете уменьшить размер файла, сохранив электронную таблицу в виде двоичного файла рабочей книги (.xslb) вместо формата по умолчанию (.xslx). Формат по умолчанию основан на XML, что важно, если вы используете свои данные в сторонних программах, поскольку XML является открытым стандартом. Однако для большинства целей достаточно двоичного формата, и он сохраняется в файл меньшего размера.

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

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

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

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

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

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

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

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

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

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

    Выберите «Файл» > «Параметры» > «Дополнительно».

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

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

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

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

    Сжать изображения

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

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

    В разделе "Параметры сжатия" выполните следующие действия:

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

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

    В разделе "Решение" выполните следующие действия:

    Выберите Использовать разрешение по умолчанию.

    Не сохранять сводную кэш-память вместе с файлом

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

    Выберите любую ячейку в сводной таблице.

    На вкладке "Анализ сводной таблицы" в группе "Сводная таблица" выберите "Параметры".

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

    Читайте также: