Почему я это пишу. Бывают ситуации, когда в файле всего один лист, данных на нем на 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. После этого переходите опять на лист и повторяете операции по выделению и удалению объектов, описанные выше. Нахождение на листе объектов не означает, что Вы их видите. Как правило такие объекты попадают на лист в результате копирования из других файлов, содержащих объекты. Объекты в некоторых случаях переносятся с нулевой длиной и шириной. Как следствие – объект не видно, но файл увеличивается в размерах. И при каждом копировании он начинает увеличиваться в размерах в геометрической прогрессии. Мало того, файл начинает дико тормозить даже при выделении ячеек. Выделили ячейку, хотите выделить другую – файл задумался на пару секунд. Не очень удобно… - Еще очень хорошо помогает удаление всех формул и связей. Удаление не в прямом смысле: заменить все формулы значениями, которые они вернули. Это можно сделать без макросов: выделяем все ячейки с данными на листе-Копируем-правая кнопка мыши-Специальная вставка-Значения. Но это не очень удобно, если листов много, поэтому я заготовил для этого еще и макрос, который проделает эту операцию на всех листах активной книги:
123456Sub All_Cells_In_All_Sheets_To_Value()Dim wsSh As ObjectFor Each wsSh In SheetswsSh.UsedRange.Value = wsSh.UsedRange.ValueNext wsShEnd Sub
Данный макрос необходимо вставить в стандартный модуль и выполнить(нажав F5 в редакторе VBA или непосредственно с листа: нажать Alt+F8-выбрать макрос All_Cells_In_All_Sheets_To_Value-Выполнить) - Так же у Вас в книге могут содержаться имена, которые тоже порой довольно неплохо прибавляют вес файлу. Посмотреть их можно, нажав сочетание клавиш Ctrl+F3на листе. Но может оказаться так, что имена будут скрытыми, и тогда Вы просто так их не увидите. Отобразить их можно при помощи следующего кода:
1234567891011Sub All_Names_Visible()Dim objName As Object, wsSh As ObjectFor Each objName In ActiveWorkbook.NamesobjName.Visible = TrueNext objNameFor Each wsSh In SheetsFor Each objName In wsSh.NamesobjName.Visible = TrueNext objNameNext wsShEnd Sub
Данный код используется как и предыдущий. Он отображает все имена на листе и в книге. После выполнения макроса необоходимо повторно нажать сочетание клавиш Ctrl+F3 на листе. Теперь Вы можете удалить ненужные Вам имена. Так же все имена можно сразу удалить при помощи следующего кода:
123456789101112Sub Delete_All_Names()Dim objName As Object, wsSh As ObjectOn Error Resume NextFor Each objName In ActiveWorkbook.NamesobjName.DeleteNext objNameFor Each wsSh In SheetsFor Each objName In wsSh.NamesobjName.DeleteNext objNameNext wsShEnd Sub
Только применяя данный код Вы должны быть уверены, что Вам действительно не нужны все имена в книге. Т.к. выполнив данный макрос Вы уже не сможете вернуть удаленные имена. - Еще в книге могут содержаться скрытые листы, на которых может содержаться ненужная Вам информация и с которыми можно проделать операции, описанные выше. Как их обнаружить можно узнать, прочитав эти статьи:
Как сделать лист скрытым?
Как сделать лист очень скрытым
После всех этих действий необходимо сохранить файл, чтобы изменения вступили в силу. Только после этого Вы сможете проверить – изменился размер файла или нет. Я бы советовал сохранять файл как копию, если не уверены, что удалили действительно ненужное.
Ну вот, вроде, и все. Надеюсь данная статья помогла Вам.