Хитрости »
Основные понятия (21)Здесь собраны статьи, в которых разъясняются базовые понятия работы в Excel и VBA, а так же проблемы, с которыми сталкивается большинство начинающих
Сводные таблицы и анализ данных (4) Раздел поможет изучить сводные таблицы и научиться их использовать "на полную"
Графики и диаграммы (4) Раздел поможет научиться создавать диаграммы и графики в Excel, в том числе нестандартные
Работа с VB проектом (9) С помощью статей раздела вы научитесь создавать процедуры программно и выполнять различные операции с объектами самого VBA
Условное форматирование (5) Этот раздел поможет поближе познакомиться с Условным форматированием на примерах различных ситуаций
Списки и диапазоны (5) Статьи, посвященные работе не только с выпадающими списками, но и с диапазонами и хитростями их применения в рабочих файлах
Макросы(VBA процедуры) (55) Статьи раздела направлены на изучение VBA с детальным разбором кодов. Множество статей с примерами кодов под всевозможные ситуации с комментариями и пояснениями
Разное (33) Собраны статьи, которые не подходят ни под одну из представленных выше категорий или входят сразу в несколько. Но эти статье не менее полезные!

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

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


  • Первое, что необходимо вспомнить - а не в общем ли доступе файл? Если в Общем, то есть вероятность, что файл раздувается именно из-за этого. Дело в том, что при установке общего доступа к файлу, по умолчанию ведется журнал изменений, в который записываются все действия, произведенные в книге за период, указанный в параметрах. Как избавиться или изменить период:
    Excel 2007 и выше: вкладка Рецензирование(Review) - Доступ к книге(Shared workbook);
    Excel 2003: Сервис - Доступ к книге.
    Переходим на вкладку Подробнее(Advanced) и выбираем Не хранить журнал изменений(don't keep change history).
    Еще лучше - снять общий доступ с книги, сохранить. Если общий доступ все еще нужен, то открываем книгу и опять даем общий доступ, но теперь устанавливаем кол-во дней, в течении которых надо хранить журнал. По умолчанию - 30, но можно сделать меньше, если файл сильно разбухает за озвученный период. Далее отключаем для пункта Включить в личное представление(Include in personal view) представления параметров печати(Print settings) и фильтров(Filter settings). После этого сохранить файл.
    Подробнее про общий доступ можно прочитать в статье: Ведение журнала сделанных в книге изменений
  • Убедитесь, что лист не содержит ячеек без данных, но занимающих пространство. Перейдя на лист, нажмите 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 на листе. Теперь Вы можете удалить ненужные Вам имена. Так же все имена можно сразу удалить при помощи следующего кода:

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

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


Статья помогла? Сделай твит, поделись ссылкой с друзьями!

Поиск по меткам

Access Multex Outlook VBA работа в редакторе VBA управление кодами Бесплатные надстройки Дата и время Диаграммы и графики Записки Защита данных Интернет Картинки и объекты Листы и книги Макросы и VBA Настройка Поиск данных Почта Программы Работа с приложениями Работа с файлами Разработка приложений Сводные таблицы Списки Тренинги и вебинары Финансовые Форматирование Формулы и функции Функции Excel Функции VBA Ячейки и диапазоны вебинар ссылки тренинг
Обсуждение: оставлено 50 коммент.
  1. Ирик:

    Проделал все манипуляции, и файл вместо 32 МБ стал весить... 156 МБ
    Помогло простое копирование (копипаст) ТОЛЬКО таблицы в новый файл

    • Если честно, звучит как-то сомнительно...Вы же исключительно удаляли все: объекты, строки, имена, форматирование. Либо объекты не удалялись, либо где-то была допущена ошибка, либо файл поврежден(что тоже не так уж редко происходит).
      Кстати, иногда помогает сохранение в формат 97-2003 Excel и обратно в 2007.

  2. Светлана:

    Огромное спасибо.!!!!

  3. Айдар:

    Файл увеличился после объектов до 16МБ, но удаление объектов не помогло уменьшить файл.
    Помог только последний коммент: сконвертировать в другой формат эксель, вынесите это в статью повыше.

    • Айдар,
      1. то, что в Вашем конкретном случае помогло именно изменение формата файла не значит, что это надо выносить в статью повыше.
      2. Раз уж просите добавить в статью - то уж добавляйте в какой формат сохранили и какой формат у файла был до этого. Если xlsb - то это не средство, а кажущееся решение проблемы. xlsb сам по себе закрытый формат данных и файлы этого формата всегда имеют меньший размер и более быстродейственны.
      Если xls - то в данном случае, возможно, не были опробованы другие методы(в частности удаление пустых строк и столбцов).
      Известны случаи, когда сохранение из xlsx(xlsm) в xls помогало избавиться от некоторых проблем увеличения размера файлов. Но причины не очень ясны, поэтому в статье сей момент отсутствует, т.к. неизвестно, какие могут быть последствия подобных манипуляций.

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

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


Для оформления сообщений Вы можете использовать следующие тэги:
<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="">

Тренинги

Необходимо автоматизировать процессы в MS Office?
Доверьте это профессионалам и работа всегда будет сделана качественно и в срок!Заказать

Логин
Счетчики
Анализ сайта

Яндекс.Метрика
© 2016 Excel для всех  Войти