Версия для печати

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

52217
Написать комментарий К комментариям
Что умеет Excel

Данный вопрос возникает периодически на различных форумах. Решил написать некоторые рекомендации по уменьшению веса файла. Применив их Вы сможете понять — действительно ли Ваши данные настолько раздувают файл или же в файле имеется много лишнего.
Почему я это пишу. Бывают ситуации, когда в файле всего один лист, данных на нем на 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. После этого переходите опять на лист и повторяете операции по выделению и удалению объектов, описанные выше. Нахождение на листе объектов не означает, что Вы их видите. Как правило такие объекты попадают на лист в результате копирования из других файлов, содержащих объекты. Объекты в некоторых случаях переносятся с нулевой длиной и шириной. Как следствие — объект не видно, но файл увеличивается в размерах. И при каждом копировании он начинает увеличиваться в размерах в геометрической прогрессии. Мало того, файл начинает дико тормозить даже при выделении ячеек. Выделили ячейку, хотите выделить другую — файл задумался на пару секунд. Не очень удобно…
  • Еще очень хорошо помогает удаление всех формул и связей. Удаление не в прямом смысле: заменить все формулы значениями, которые они вернули. Это можно сделать без макросов: выделяем все ячейки с данными на листе-Копируем-правая кнопка мыши-Специальная вставка-Значения. Но это не очень удобно, если листов много, поэтому я заготовил для этого еще и макрос, который проделает эту операцию на всех листах активной книги:
    Sub All_Cells_In_All_Sheets_To_Value()
        Dim wsSh As Object
        For Each wsSh In Sheets
            wsSh.UsedRange.Value = wsSh.UsedRange.Value
        Next wsSh
    End Sub
    Sub All_Cells_In_All_Sheets_To_Value()
        Dim wsSh As Object
        For Each wsSh In Sheets
            wsSh.UsedRange.Value = wsSh.UsedRange.Value
        Next wsSh
    End Sub

    Данный макрос необходимо вставить в стандартный модуль и выполнить(нажав F5 в редакторе VBA или непосредственно с листа: нажать Alt+F8-выбрать макрос All_Cells_In_All_Sheets_To_Value-Выполнить)

  • Так же у Вас в книге могут содержаться имена, которые тоже порой довольно неплохо прибавляют вес файлу. Посмотреть их можно, нажав сочетание клавиш Ctrl+F3на листе. Но может оказаться так, что имена будут скрытыми, и тогда Вы просто так их не увидите. Отобразить их можно при помощи следующего кода:
    Sub All_Names_Visible()
        Dim objName As Object, wsSh As Object
        For Each objName In ActiveWorkbook.Names
            objName.Visible = True
        Next objName
        For Each wsSh In Sheets
            For Each objName In wsSh.Names
                objName.Visible = True
            Next objName
        Next wsSh
    End Sub
    Sub All_Names_Visible()
        Dim objName As Object, wsSh As Object
        For Each objName In ActiveWorkbook.Names
            objName.Visible = True
        Next objName
        For Each wsSh In Sheets
            For Each objName In wsSh.Names
                objName.Visible = True
            Next objName
        Next wsSh
    End Sub

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

    Sub Delete_All_Names()
        Dim objName As Object, wsSh As Object
        On Error Resume Next
        For Each objName In ActiveWorkbook.Names
            objName.Delete
        Next objName
        For Each wsSh In Sheets
            For Each objName In wsSh.Names
                objName.Delete
            Next objName
        Next wsSh
    End Sub
    Sub Delete_All_Names()
        Dim objName As Object, wsSh As Object
        On Error Resume Next
        For Each objName In ActiveWorkbook.Names
            objName.Delete
        Next objName
        For Each wsSh In Sheets
            For Each objName In wsSh.Names
                objName.Delete
            Next objName
        Next wsSh
    End Sub

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

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

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



Поддержать автора сайта
  1. Марина
    18 Июнь 2012 в 12:48 | #1

    Огромное спасибо! все просто и понятно. уменьшила файл с 10 Мб до 68 Кб, удалив объекты, а до этого голову ломала :)

  2. Юлия
    4 Октябрь 2012 в 12:14 | #2

    Вообще ни на мегабайт не уменьшился документ!Может я что-то не так делаю… Объекты невидимые удалила, а видимые — все нужны!

  3. 4 Октябрь 2012 в 12:27 | #3

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

  4. сирый
    13 Декабрь 2012 в 14:49 | #4

    ещё помогает сохранение в *.xml

  5. Галина
    8 Февраль 2013 в 13:51 | #5

    а если тут «Главная-Найти и выделить-Выделение группы ячеек-Объекты» слово объекты нельзя выделить. это значит что их нет?

  6. Рамиля
    28 Март 2013 в 09:20 | #6

    Почему в моем файле после всех действий по уменьшению файла последняя непустая ячейка находится далеко за пределами последней записи? К сожалению, не могу прикрепить файл…

  7. 28 Март 2013 в 10:23 | #7

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

  8. Станислава
    18 Апрель 2013 в 11:19 | #8

    Дмитрий(Админ),
    Диапазон с листа из большого файла (под 80Мб) скопирован в новую книгу (xlsx). Получилось 28Мб.
    FX12979 — последняя ячейка листа.
    Ко всему листу применена команда «очистить форматы». Весь лист скопирован и вставлен значениями. Поиск и выделение объектов — «Не найдено ни одного объекта». Сохранено и закрыто. Результат: 26Мб. Почему?

  9. 18 Апрель 2013 в 11:25 | #9

    Ответ — потому что. Это у Вас есть этот файл. У меня нет. Поэтому вопрос мягко говоря некорректный. Я могу озвучить часть причин, но не могу дать реальный ответ. В нем могут быть данные, имена, связи и прочее. А если учесть, что последняя ячейка на строке 12979 столбца 180 — данных там предостаточно, чтобы файл имел приличный весь. Если в ячейках еще и длинный текст — чему удивляться?

  10. Станислава
    18 Апрель 2013 в 11:38 | #10

    Дмитрий(Админ),
    спасибо, Вы навели меня на мысль: скопировала данные в блокнот, результат — 22Мб. Выходит, просто действительно много данных (и этолько за первые три месяца, что же будет к концу года?..). Дмитрий, может быть подскажете, можно ли из разных диапазонов в разных книгах (одинакового содержания) импортировать всё значениями в одну таблицу в другой книге?

  11. 18 Апрель 2013 в 11:49 | #11

    Зайдите в раздел «Что умеет Excel». Там есть статья: Как собрать данные с нескольких листов или книг?

  12. Наташа
    7 Ноябрь 2013 в 13:09 | #12

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

  13. Наталья
    12 Февраль 2014 в 14:14 | #13

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

  14. Александр
    31 Март 2014 в 23:20 | #14

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

  15. Аслан
    10 Апрель 2014 в 22:09 | #15

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

Страницы комментариев

Комментарии, не имеющие отношения к комментируемой статье, будут удаляться без уведомления и объяснения причин. Если есть вопрос по проблеме в Excel- добро пожаловаться на Форум

Комментарий будет добавлен после проверки администратором.