Хитрости »

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

Данный вопрос возникает периодически на различных форумах. Решил написать некоторые рекомендации по уменьшению веса файла. Применив их Вы сможете понять – действительно ли Ваши данные настолько раздувают файл или же в файле имеется много лишнего.
Почему я это пишу. Бывают ситуации, когда в файле всего один лист, данных на нем на 1000 строк и 20 столбцов. Никаких формул, только значения. Но размер файла непомерно велик – скажем 10 Мб. Этого недопустимо. Или есть различные формулы, но Вы все равно считаете, что размер файла не соответствует тому, что должно бы быть. Приступим. Следующие действия необходимо проделать на каждом листе книги:


  • Первое, что необходимо вспомнить – а не в общем ли доступе Ваш файл? Если в Общем, то есть вероятность, что Ваш файл раздувается именно из-за этого. Дело в том, что при установке общего доступа к файлу, по умолчанию ведется журнал изменений, в который записываются все действия, произведенные в книге за период, указанный в параметрах. Как избавиться или изменить период:
    Excel 2007-2010: вкладка РецензированиеДоступ к книге;
    Excel 2003: СервисДоступ к книге.
    Переходим на вкладку Подробнее. Для начала активируйте пункт “Не хранить журнал изменений”. Сохраните файл. Затем установите кол-во дней, в течении которых хотите хранить журнал. По умолчанию – 30, но Вы можете сделать меньше, если файл сильно разбухает.
  • Перейдя на лист, нажмите Ctrl+End. Активируется последняя ячейка листа. Если она расположена ниже или правее последних данных таблицы(или просто данных) – то удалите все строки и столбцы после последних данных таблицы. Удалите полностью строки. Сделать это быстро можно так. Нажали Ctrl+End, попали на последнюю ячейку. Выделили эту строку, нажали Ctrl+Shift+стрелка Вверх. Выделились все строки вместе с последней строкой Ваших данных. Удерживая Shift жмете стрелку Вниз. Тоже самое и со столбцами.
  • Посмотрите лист на предмет форматирования. Необходимо избегать форматирования ЦЕЛИКОМ столбцов либо строк. Это приводит к раздуванию файла. Все форматирование, выходящее за границы таблицы необходимо убрать. А еще лучше – вообще избегать излишнего форматирования, особенно если книгой никто, кроме Вас не пользуется. Вместо Заливки ячеек – Белым цветом ставьте “Нет заливки”.Чтобы убрать все форматирование из ячеек: выделяете необходимый диапазон и:
    для Excel 2003: ПравкаОчиститьФормат.
    В Excel 2007-2010: вкладка ГлавнаяОчиститьОчистить форматы
    Очистка форматов
  • Удаляем объекты:
    В Excel 2003: меню Правка- ПерейтиВыделитьОбъекты.
    в Excel 2007-2010: вкладка Главная-Найти и выделить-Выделение группы ячеек-
    Объекты.
    Нажмите Delete. Все объекты на листе будут удалены. Правда есть небольшой шанс, что у Вас есть скрытые объекты. Тогда надо идти в редактор VBA(Alt+F11)-Ctrl+R. Отображаете окно свойств(F4). Находите объект ЭтаКнига(ThisWorkbook) и в окне свойств свойство – DisplayDrawingObjects и ставите там значение – -4104-xlDisplayShapes. После этого переходите опять на лист и повторяете операции по выделению и удалению объектов, описанные выше. Нахождение на листе объектов не означает, что Вы их видите. Как правило такие объекты попадают на лист в результате копирования из других файлов, содержащих объекты. Объекты в некоторых случаях переносятся с нулевой длиной и шириной. Как следствие – объект не видно, но файл увеличивается в размерах. И при каждом копировании он начинает увеличиваться в размерах в геометрической прогрессии. Мало того, файл начинает дико тормозить даже при выделении ячеек. Выделили ячейку, хотите выделить другую – файл задумался на пару секунд. Не очень удобно…
  • Еще очень хорошо помогает удаление всех формул и связей. Удаление не в прямом смысле: заменить все формулы значениями, которые они вернули. Это можно сделать без макросов: выделяем все ячейки с данными на листе-Копируем-правая кнопка мыши-Специальная вставка-Значения. Но это не очень удобно, если листов много, поэтому я заготовил для этого еще и макрос, который проделает эту операцию на всех листах активной книги:

    Данный макрос необходимо вставить в стандартный модуль и выполнить(нажав F5 в редакторе VBA или непосредственно с листа: нажать Alt+F8-выбрать макрос All_Cells_In_All_Sheets_To_Value-Выполнить)
  • Так же у Вас в книге могут содержаться имена, которые тоже порой довольно неплохо прибавляют вес файлу. Посмотреть их можно, нажав сочетание клавиш Ctrl+F3на листе. Но может оказаться так, что имена будут скрытыми, и тогда Вы просто так их не увидите. Отобразить их можно при помощи следующего кода:

    Данный код используется как и предыдущий. Он отображает все имена на листе и в книге. После выполнения макроса необоходимо повторно нажать сочетание клавиш Ctrl+F3 на листе. Теперь Вы можете удалить ненужные Вам имена. Так же все имена можно сразу удалить при помощи следующего кода:

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

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

Обсуждение: оставлено 41 коммент.
  1. Зайдите в раздел “Что умеет Excel”. Там есть статья: Как собрать данные с нескольких листов или книг?

  2. Наташа:

    Отличная статья! Спасибо! Файл с 10МБ стал весить 640Кб!

  3. Наталья:

    Спасибо. Не добавлялись и не скрывались столбцы – изменила настройки DisplayDrawingObjects. Все стало нормально. А до этого ничего не помогало! Спасибо! Не пришлось удалять нужные столбцы из файла.

  4. Александр:

    Огромнейшее спасибо! Все ясно и понятно. До этого мучился 2 дня -потерял кучу времени, но толком ничего хорошего не вычитал, а здесь собрано все основное ЛЕКАРСТВО от вздутия файлов . В результате уменьшил с 65 МБ до 500 КБ. Затратил на все 20 мин.

  5. Аслан:

    в общем встретился я с такой же проблемой, начал расти файл после добавления нового листа и внесения данных с предыдущего. ну на работе сводка обновляется три раза в день и в день добавляется три новых листа с изменяющейся информацией. в общем файл начал неимоверно расти, нашел простой способ решения проблемы. у нас в организации офис2010 и формат файла *.xlsx я открыл файл и сохранил как документ офис 97-2003 и он стал *.xls, уменьшился в два раза, а потом новый файл сохранил как документ офис 2010 с форматом *.xlsx и файл с 8МБ стал в шесть раз меньше, меньше возни в общем, попробуйте.

  6. олег:

    когда файлы с размером в 150-350 МБ (к-во строк 100-700 тис.), при этом там только данные (ни формул, ни сводных – ничего) единственное что помогает это сохранить в формате двоичной книги (расширение .xlsb) – сжимает в/до 10 раз.

  7. Amidvo:

    Есть более грубый способ, но он помогает, когда ничего не помогает. Мне от юзера попался файл ~2Mb, перепичканный textbox`ами. Выделение этих объектов в нём ни разу не удалось довести до конца. Excel выедал более 500Mb памяти и столько же page-файла и в итоге вис.
    В этом случае надо перестать открывать файл Экселем и открыть его любой программой, умеющей открывать архивы (речь идёт о файлах xlsx, xlsb и т.п., для файлов Excel 2003 и ранее этот способ не годится) – Winrar, 7zip и т.д. Откройте их, например, в Total Commander комбинацией клавиш CTRL-PGDOWN. Вы увидите файл как содержимое архива. Найдите папку “xl”, в ней найдите папку “drawings”, в ней будут находится один иди несколько “файлов” с расширением xml. Удалите их. Вместе с их удалением пропадёт и папка “drawings”. Выйдите из файла и попробуйте открыть его в Excel. Он должен обнаружить в файле ошибку. Дайте ему её исправить и сохраните файл с другим именем. Всё.
    В моём случае 2Mb файл уменьшился до 900к, но это не главное. Он стал открываться мгновенно вместо 5 минут.

    • Наталья:

      Amidvo,большое спасибо!
      Приходится регулярно обрабатывать вроде бы небольшие (300-400к), но очень тормознутые файлы, присланные тупыми коллегами. Тоже виснут при попытке выделить объекты, и вообще работать с ними невозможно. Приходилось медленно и печально переносить значения в новую книгу и “дорабатывать напильником”.
      Ваш способ помог. Из 385к — 36к и всё летает!
      Спасибо ещё раз — Вы мне сэкономили кучу времени и километры нервов!

  8. Андрей:

    У меня запуск макроса All_Cells_In_All_Sheets_To_Value() вызывает ошибку “Run-Time Error ‘7’: Out Of Memory”. В открытой книге (Excel 2013 64-bit) один лист, 32 гига оперативки (доступно 30 гиг), файл подкачки не лимитирован. На листе примерно 50 млн ячеек с формулами, и это сокращенный вариант. Эту проблему можно решить? Указанный e-mail настоящий.

    PS. Если на листе мало формул, макрос работает великолепно, и именно так как мне нужно.

    • Вопрос именно в нехватке памяти. Слишком много ячеек к обработке. В данном случае вариант только частями данные ячеек обрабатывать(строк по 100)

      • Андрей:

        Спасибо, что так бысто ответили. Но по 100 строк это будет мучительно долго, потому как обработать надо до 80 000 строк. Забыл указать, ругается на строку wsSh.UsedRange.Value = wsSh.UsedRange.Value. Решил проблему таким макросом (не ругается и за раз скармливаю по 20 000 строк – так быстрее, все вычисления в памяти):

        Sub Formulas_to_values()
        Selection.Value = Selection.Value
        End Sub

        По теме статьи о размерах конечного файла. Использование этих макросов и стандартной функции Excel “Специальная вставка-Значения” имеет принципиальную разницу. Если формулы на листе в качестве результата вычисления возвращают “” (пусто), то при копировании и последующей вставке значений, ячейки заполняются строкой нулевой длины, а при использовании макроса – такие формулы просто удаляются и ячейка остается абсолютно пустой. А когда ячеек со строкой нулевой длины более 300 млн, то размер файла просто громадный. Например: один и тот же файл содержит ~400 млн ячеек с формулами, которые при невыполнении условия возвращают “”. Размеры этого файла при сохранении:
        с формулами ~ 1,2 Гб
        после “Специальная вставка-Значения” ~ 690 Мб
        после применения макроса около 4 Мб. Почувствуйте разницу:)

Поделитесь своим мнением

Для оформления сообщений Вы можете использовать следующие тэги:
<a href="" title=""> <abbr title=""> <acronym title=""> <b> <blockquote cite=""> <cite> <code class="" title="" data-url=""> <del datetime=""> <em> <i> <q cite=""> <strike> <strong> <pre class="" title="" data-url=""> <span class="" title="" data-url="">

Много работаете в Excel, но понимаете, что используете его не на полную?
Пройдите один из тренингов и работайте в Excel эффективно!Заказать

Вы постоянно выполняете однотипные действия в Excel или Word?
Вам надоела рутина?
Закажите создание макроса(программы) - быстро, качественно, недорого!Заказать

Реклама
Логин
Счетчики
Анализ сайта
Рейтинг@Mail.ru
Яндекс.Метрика
© 2014 Excel для всех  Войти