Как очистить файл Excel от мусора?

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

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

Проблема 1. Используемый диапазон листа больше, чем нужно

Если ваша таблица занимает 5 на 5 ячеек, то это отнюдь не означает, что Excel запоминает при сохранении этого файла только 25 ячеек с данными. Если вы в прошлом использовали какие-либо ячейки на этом листе, то они автоматически включаются в используемый диапазон (так называемый Used Range), который и запоминается при сохранении книги. Проблема в том, что при очистке используемых ячеек Excel далеко не всегда автоматически исключает их из используемого диапазона, т.е. начинает запоминать в файле больше данных, чем реально имеется.

Проверить это просто – нажмите на клавиатуре сочетание клавиш Ctrl+End и посмотрите куда переместится активная ячейка. Если она прыгнет на фактическую последнюю ячейку с данными на листе – отлично. А если вдруг ускачет сильно правее и/или ниже «в пустоту» – дело плохо: все эти ненужные пустые ячейки Excel тоже запоминает внутри файла.

Лечится это, тем не менее, достаточно легко:

Выделите первую пустую строку под вашей таблицей
Нажмите сочетание клавиш Ctrl+Shift+стрелка вниз – выделятся все пустые строки до конца листа.
Удалите их, нажав на клавиатуре Ctrl+знак минус или выбрав на вкладке Главная – Удалить – Удалить строки с листа (Home – Delete – Delete rows) .
Повторите то же самое со столбцами.
Повторите все вышеописанные процедуры на каждом листе, где при нажатии на Ctrl+End активная ячейка перемещается не на фактическую последнюю ячейку с данными а «в пустоту» ниже и/или правее.
Сохраните файл (обязательно, иначе изменения не вступят в силу!)

Если в вашей книге очень много таких листов, то проще, наверное, использовать короткий макрос.

Проблема 2. Используется старый формат XLS вместо новых XLSX, XLSM и XLSB

Много лет и версий подряд еще с начала девяностых в Excel был один формат файлов — XLS. Это, конечно, убирало проблемы совместимости, но, сам по себе, этот формат давно устарел и имел много неприятных недостатков (большой размер, непрозрачность внутренней структуры данных, легкую повреждаемость и т.д.)

Начиная с верии Excel 2007 Microsoft ввела новые форматы сохранения файлов, использование которых заметно облегчает жизнь и — ваши файлы:

XLSX — по сути является зазипованным XML. Размер файлов в таком формате по сравнению с Excel 2003 меньше, в среднем, в 5-7 раз.
XLSM — то же самое, но с поддержкой макросов.
XLSB — двоичный формат, т.е. по сути — что-то вроде скомпилированного XML. Обычно в 1.5-2 раза меньше, чем XLSX. Единственный минус: нет совместимости с другими приложениями кроме Excel, но зато размер — минимален.

Вывод: всегда и везде, где можно, переходите от старого формата XLS (возможно, доставшегося вам «по наследству» от предыдущих сотрудников) к новым форматам.

Проблема 3. Избыточное форматирование

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

Оставьте только самое необходимое, не изощряйтесь. Особенно в тех таблицах, которые кроме вас никто не видит. Для удаления только форматов (без потери содержимого!) выделите ячейки и выберите в выпадающем списке Очистить — Очистить форматы (Clear — Clear Formats) на вкладке Главная (Home) :

Особенно «загружают» файл отформатированные целиком строки и столбцы. Т.к. размер листа в последних версиях Excel сильно увеличен (>1 млн. строк и >16 тыс. столбцов), то для запоминания и обрабоки подобного форматирования нужно много ресурсов. В Excel 2013-2016, кстати, появилась надстройка Inquire, которая содержит инструмент для быстрого избавления от подобных излишеств — кнопку Удалить избыточное форматирование (Clean Excess Cell Formatting) :

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

Если вы не видите у себя в интерфейсе вкладку Inquire, то ее необходимо подключить на вкладке Разработчик — Надстройки COM (Developer — COM Addins) .

Проблема 4. Ненужные макросы и формы на VBA

Большие макросы на Visual Basic и особенно пользовательские формы с внедренной графикой могут весьма заметно утяжелять вашу книгу. Для удаления:

нажмите Alt+F11, чтобы войти в редактор Visual Basic
найдите окно Project Explorer’а (если его не видно, то выберите в меню View — Project Explorer)
удалите все модули и все формы (правой кнопкой мыши — Remove — дальше в окне с вопросом о экспорте перед удалением — No):

Также код может содержаться в модулях листов — проверьте их тоже. Также можно просто сохранить файл в формате XLSX без поддержки макросов — все макросы и формы умрут автоматически. Также можно воспользоваться инструментом Очистить книгу от макросов из надстройки PLEX.

Проблема 5. Именованные диапазоны

Если в вашем файле используются именованные диапазоны (особенно с формулами, динамические или получаемые при фильтрации), то имеет смысл от них отказаться в пользу экономии размера книги. Посмотреть список имеющихся диапазонов можно нажав Ctrl+F3 или открыв окно Диспетчера имен (Name Manager) на вкладке Формулы (Formulas) :

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

Проблема 6. Фотографии высокого разрешения и невидимые автофигуры

Если речь идет о фотографиях, добавленных в книгу (особенно когда их много, например в каталоге продукции), то они, само-собой, увеличивают размер файла. Советую сжимать их, уменьшая разрешение до 96-150 точек на дюйм. На экране по качеству это совершенно не чувствуется, а размер файла уменьшает в разы. Для сжатия воспользуйтесь кнопкой Сжать рисунки (Compress pictures) на вкладке Формат (Format) :

Кроме видимых картинок на листе могут содержаться и невидимые изображения (рисунки, фотографии, автофигуры). Чтобы увидеть их, выделите любую картинку и на вкладке Формат (Format) нажмите кнопку Область выделения (Selection Pane) .

Для удаления вообще всех графических объектов на текущем листе можно использовать простой макрос:

Проблема 7. Исходные данные сводных таблиц

По-умолчанию Excel сохраняет данные для расчета сводной таблицы (pivot cache) внутри файла. Можно отказаться от этой возможности, заметно сократив размер файла, но увеличив время на обновление сводной при следующем открытии книги. Щелкните правой кнопкой мыши по сводной таблице и выберите команду Свойства таблицы (Pivot Table Properties) — вкладка Данные (Data) — снять флажок Сохранять исходные данные вместе с файлом (Save source data with file):

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

В Excel 2007-2016 кнопку Мастера сводных таблиц нужно добавлять на панель вручную — на ленте такой команды нет. Для этого щелкните по панели быстрого доступа правой кнопкой мыши и выберите Настройка панели быстрого доступа (Customize Quick Access Toolbar) и затем найдите в полном списке команд кнопку Мастер сводных таблиц (PivotTable and PivotChart Wizard) :

Проблема 8. Журнал изменений (логи) в файле с общим доступом

Если в вашем файле включен общий доступ на вкладке Рецензирование — Доступ к книге (Review — Share Workbook) , то внутри вашего файла Excel на специальном скрытом листе начинает сохраняться вся история изменений документа: кто, когда и как менял ячейки всех листов. По умолчанию, такой журнал сохраняет данные изменений за последние 30 дней, т.е. при активной работе с файлом, может запросто занимать несколько мегабайт.

Мораль: не используйте общий доступ без необходимости или сократите количество дней хранения данных журнала, используя вторую вкладку Подробнее (Advanced) в окне Доступ к книге. Там можно найти параметр Хранить журнал изменений в течение N дней (Keep change history for N days) или совсем отключить его:

Проблема 9. Много мусорных стилей

Про эту пакость я уже подробно писал ранее в статье о том, как победить ошибку «Слишком много форматов ячеек». Суть, если кратко, в том, что если вы разворачиваете на вкладке Главная список Стили ячеек (Home — Cell Styles) и видите там очень много непонятных и ненужных стилей, то это плохо — и для размера вашего файла Excel и для его быстродействия.

Удалить ненужные стили можно с помощью макроса или готовой команды из надстройки PLEX.

Проблема 10. Много примечаний

Примечания к ячейкам, конечно, не самый вредный момент из всех перечисленных. Но некоторые файлы могут содержать большое количество текста или даже картинок в примечаниях к ячейкам. Если примечания не содержат полезной для вас информации, то их можно легко удалить с помощью команды на вкладке Главная — Очистить — Очистить примечания (Home — Clear — Clear Comments) .

Очистка книги Excel, чтобы она использовала меньше памяти

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

Симптомы

После обновления Microsoft Office 2013/2016/Office 365 вы испытываете один или несколько следующих симптомов:

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

Вы больше не можете открывать столько Excel книг в том же экземпляре, что и до обновления до Excel 2013/2016.

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

При работе с Excel таблицой вы получаете следующее сообщение об ошибке:

Причина

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

Дополнительные сведения об изменениях, внесенных в Excel 2013 г., см. в 32-битной версии Excel 2013г. .

Решение

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

Многие Excel книг имеют несколько проблем, которые могут привести к проблемам. После устранения этих проблем ваша книга будет работать более плавно.

Соображения форматирования

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

Метод 1. Устранение чрезмерного форматирования

Чрезмерное форматирование в книге Excel может привести к росту файла и может привести к плохой производительности. Форматирование будет считаться чрезмерным, если вы отформатировали целые столбцы или строки с цветом или границами. Эта проблема также возникает, когда форматирование требует копирования или импорта данных с веб-страниц или баз данных. Чтобы исключить избыточное форматирование, используйте надстройку для очистки формата, доступную в clean excess cell formatting on a worksheet.

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

Метод 2. Удаление неиспользованых стилей

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

Доступно множество утилит, которые удаляют неиспользованы стили. До тех пор, пока вы используете XML-Excel книги (то есть .xlsx файл или. xlsm-файл), вы можете использовать средство для чистки стиля. Этот инструмент можно найти здесь.

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

Метод 3. Удаление фигур

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

Диаграммы
Рисование фигур
Комментарии
Искусство клипа
SmartArt
Изображения
WordArt

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

Чтобы проверить фигуры, выполните следующие действия:

На домашней ленте нажмите кнопку Найти и Выбрать, а затем нажмите кнопку Выбор области.
Щелкните Фигуры на этом листе. Фигуры отображаются в списке.
Удалите нежелательные фигуры. (Значок глаза указывает, видна ли фигура.)
Повторите шаги от 1 до 3 для каждого таблицы.

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

Метод 4. Удаление условного форматирования

Условное форматирование может привести к росту файла. Это происходит, когда условное форматирование в файле повреждено. Можно удалить условный форматирование, чтобы проверить, не проблема ли с коррупцией в формате. Чтобы удалить условное форматирование, выполните следующие действия:

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

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

Проблема остается?

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

Расчетные соображения

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

Метод 1. Откройте книгу в последней версии Excel

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

Если файл продолжает медленно открываться после Excel полностью пересчитать файл и сохранить файл, переходить к методу 2.

Метод 2. Формулы

Изучите книгу и изучите типы формул, которые вы используете. Некоторые формулы принимают много памяти. К ним относятся следующие формулы массива:

LOOKUP
СМЕЩ
СМЕЩЕНИЯ
INDEX
MATCH

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

Формулы, ссылаясь на целые столбцы, могут привести к низкой производительности .xlsx файлов. Размер сетки увеличился с 65 536 строк до 1 048 576 строк и с 256 столбцов (IV) до 16 384 столбцов (XFD). Популярным способом создания формул, хотя и не является наилучшей практикой, было ссылаться на целые столбцы. Если вы ссылались только на один столбец в старой версии, вы были в том числе только 65 536 ячеек. В новой версии вы ссылаетесь на более 1 млн столбцов.

Предположим, что у вас есть следующий VLOOKUP:

В Excel 2003 и более ранних версиях этот VLOOKUP ссылался на целую строку, которая включала только 655 560 ячеек (10 столбцов x 65 536 строк). Однако с новой, более крупной сеткой эта же формула ссылается почти на 10,5 млн ячеек (10 столбцов x 1 048 576 строк = 10 485 760).

Это исправлено в версии Office 2016/365 версии 1708 16.0.8431.2079 и более поздней версии. Сведения об обновлении Office см. в Office Install Office.

Для более ранних версий Office может потребоваться реконструировать формулы, ссылаясь только на те ячейки, которые необходимы для формул.

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

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

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

Метод 3. Вычисление по книгам

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

Вы пытаетесь открыть файл по сети.
Excel пытается рассчитать большие объемы данных.

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

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

Метод 4. Летучие функции

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

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

Метод 5. Формулы массива

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

Дополнительные сведения о работе массивов см. в Excel 2010 performance: Советыоптимизации препятствий производительности.

Если вы продолжаете испытывать проблему после обновления формул массива, переходить к методу 6.

Метод 6. Определенные имена

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

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

Если Excel продолжает сбой и висят после удаления ненужных определенных имен, переходить к методу 7.

Метод 7. Ссылки и гиперссылки

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

Продолжайте двигать

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

Дополнительные сведения

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

Excel тормозит: данные, форматирование, формулы, макросы, сводные

Большие файлы Excel часто добавляют хлопот пользователям из-за увеличения времени их обработки. Иногда проблемы становятся настолько острыми, что с файлом невозможно работать. Несколько лет назад я предложил два варианта, как бороться с мусорными объектами, которые могут появляться при импорте данных из 1С, SAP и других программ: Excel тормозит. Что делать? и Excel «тормозит». Что делать? Дубль 2. Заметки пользуются большой популярностью, при этом в комментариях задают вопросы, напрямую не связанные с паразитными объектами. А около года назад, когда я читал книгу Майкл Гирвин. Ctrl+Shift+Enter. Освоение формул массива в Excel, то обратил внимание на то, как много внимания автор уделяет скорости работы формул, постоянно сравнивая время выполнения обычных формул, формул баз данных и формул массива. Так и родилась идея этой заметки – показать, как различные элементы книги Excel влияют на ее размер. [1]

Рис. 1. Зависимость размера книги от числа листов в ней; 255 пустых листов увеличивают размер книги до 131 КВ

Скачать заметку в формате Word или pdf, примеры в формате Excel

Начнем с того, что Microsoft принципиально переработал версию Excel в 2007 году, так что, если вы используете более раннюю версию, советую вам перейти на Excel2007 или более позднюю версию.

Возможности Excel по работе с большими файлами и скорость работы с ними в значительной степени зависят от объема оперативной памяти. Например, я ощутил существенную прибавку в этом аспекте увеличив память в своем моноблоке с 2 до 4 ГВ. А в одном из комментариев к заметке «Excel тормозит. Что делать?» упоминается, что выделение 14 000 объектов заняло 12 с при 32 ГВ оперативки (я на своем моноблоке с 2 ГВ не смог выделить и за полчаса).

Число листов

Свое исследование я начал с создания книги Excel, не содержащей данных, и включающей всего 1 лист. Ее размер оказался 7,6 КВ. Книга Excel может содержать не более 255 листов (рис. 1).

Объем данных

Вернемся к книге, содержащей 1 лист, и в ячейку А1 введем значение 1. Размер книги не изменится – 7,6 КВ. Если же единицами заполнить 10 000 ячеек в столбце А, то размер увеличится до 613 КВ (рис. 2, синяя прямая).

Кстати, для заполнения ячеек можно воспользоваться следующим методом. Набираем Ctrl+G, и в окне Переход в поле Ссылка вводим адрес А10000, и жмем Ok. Мы перешли к последней ячейке заполняемого диапазона. Вводим в ячейку А10000 значение 1. Возвращаемся к ячейке А1. Удерживая Shift, нажимаем на цифровой клавиатуре кнопку End и затем стрелку вниз. Выбран диапазон А1:А10000. Не снимая выделение, вводим единицу (она появится в ячейке А1) и, по-прежнему не убирая выделение, жмем Ctrl+Enter. 10 000 ячеек заполнились единицами.

Рис. 2. Зависимость размера книги от числа заполненных ячеек

Любопытно, что заполнение единицами квадратной области (100х100, …, 1000х1000 ячеек) сокращает объем файла (рыжая кривая на рис. 2). Для меня оказалось неожиданным, что замена единицы на текстовый массив (я использовал около 1500 символов в каждой ячейке), увеличило размер файла лишь 5–10%. К еще меньшему эффекту приводит замена единицы на большое число с десятичной запятой.

Формулы и функции

Добавление формул почти удваивает размер файла. При этом даже простейшая ссылка на предыдущую ячейку (рис. 3), протянутая на миллион ячеек, увеличивает размер файла с 5,15 МВ до 9 МВ. Дальнейшее усложнение формул увеличивает размер файла лишь на несколько процентов.

Рис. 3. Ссылка вместо константы

Однако, в Excel есть особые функции, пересчитываемые при любом изменении данных, например, =СЛЧИС(). Если протянуть эту функцию на миллион ячеек, размер файла увеличится до 22 МВ!

Сводные таблицы

Исходные данные занимали прямоугольную область: 10 столбцов * 10 000 строк (рис. 4). Файл весил 569 КВ. Добавление сводной таблицы (рис. 5) увеличило размер файла до 750 КВ (на 30%). Форматирование сводной таблицы с использованием стандартных стилей (доступны по меню Работа со сводными таблицами –> Конструктор –> Стили сводной таблицы) практически не изменяет размер файла. Добавление второй сводной таблицы на основе того же кеша увеличило размер файла до 903 КВ (подробнее об использование кеша см. Создание нескольких сводных таблиц на основе одного источника данных: один кеш или несколько?). Добавление второй сводной таблицы на основе нового кеша увеличило файл с 750 до 1 060 КВ. Поэтому, создавая набор сводных таблиц, старайтесь использовать один кеш. И только если вы собираетесь применять к сводным таблицам различные настройки (например, в одной таблице группировать даты по месяцам, а в другой – по неделям), предусмотрите для каждой таблицы свой кеш.

Рис. 4. Исходные данные

Рис. 5. Сводная таблица

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

«Мусор»

Иногда (каким-то непостижимым образом) на листе Excel вводятся ненужные данные в «далекой» ячейке. На одном листе Excel2013 может быть 1 048 576 строк и 16 384 столбцов. Однажды мне встретился файл, в котором было введена единица в предельной ячейке (рис. 6). К счастью, современные версии Excel легко справляются с этой ситуацией, и размер файлов от таких ошибок не распухает, чего не скажешь о более ранних версиях. Увидеть, что на вашем листе есть «мусор» можно с помощью бегунка, который будет иметь неестественно большое поле для пробега (см. нижнюю часть рис. 6).

Рис. 6. «Мусорное» значение в последней ячейке

Форматирование

Еще одна причина распухания файла — наложение форматирования на столбцы или строки целиком, а не только на используемый диапазон данных. Чтобы исправить эту ошибку, нужно исключить излишнее форматирование. Для начала вручную найдите последнюю ячейку с фактическими данными. Если ваш лист не содержит «мусора», то в последнюю ячейку можно попасть, прокрутив бегунок по вертикали и горизонтали в крайние положения (но не более того). Далее выделите горизонтальную область, лежащую ниже данных, и содержащую форматирование, и пройдите по меню Главная –> Редактирование –> Очистить –> Очистить всё. Повторите операцию для вертикальной области, лежащей справа от области с данными.

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

Макросы

Если вы не используете макросы, сохраните книгу с расширением xlsx. Если вы не используете отдельные макросы, можете экспортировать соответствующие модули, а затем удалить их. Иногда (я такого не встречал) какие-то проблемы содержатся в рабочих модулях. Может помочь последовательный экспорт, удаление модуля, импорт.

Очистка поврежденных книг

Возможно, ваша книга или отдельные листы повреждены. К сожалению, определение точки повреждения требует выполнить процесс исключения вручную. Сохраните резервную копию книги. Чтобы гарантировать, что вы ничего не пропустите, отобразите все скрытые листы. Для этого щелкните правой кнопкой мыши на ярлычке любого листа и выберите команду Показать. Если эта команда недоступна, значит у вас нет скрытых листов. Теперь, когда все листы видимы, начните с самого левого и по очереди обрабатывайте все листы. Для каждого листа: удалите лист, сохраните книгу и проверьте размер файла, например, пройдя по меню Файл –> Сведения, область Свойства –> Размер. Если размер файла существенно уменьшился, учитывая количество данных на удаленном листе, то вы, вероятно, нашли место повреждения.

Чтобы заменить в книге поврежденный лист, создайте новый лист, вручную выделите данные на поврежденном листе, вырежьте (не копируйте!) и вставьте их на новый лист. Удалите поврежденный лист из книги, сохраните ее и повторите процесс. Благодаря вырезанию, а не копированию данных Excel автоматически будет переносить на новый лист данные с соответствующими ссылками.

[1] В том числе использованы некоторые идеи из книги Д.Холи, Р.Холи. Excel 2007. Трюки, стр. 58–60

Почему тормозит Excel? Как уменьшить размер файла Excel? Решения проблемы.

Всем привет!

Сегодня расскажу о проблемах самого популярного табличного офисного редактора Microsoft Excel, и как с этим бороться…

Бывает так, что файл книги Microsoft Excel содержащий несколько листов по 1000 строк, с простыми формулами и без связанных таблиц начинает весить непомерно много — 100 или даже 200 мегабайт, при этом открывается файл очень долго, иногда бывает даже сохраняться отказывается. Что делать в этом случае? Давайте разбираться.

Большой размер файла Micosoft Excel причина зависаний и торможений.

В этой статье я хочу предложить несколько вариантов борьбы с «тяжелыми» файлами Microsoft Excel, которыми пользуюсь сам. Так же, в комментариях с удовольствием почитаю ваши варианты борьбы с тормозами и оптимизацией размера .xls и .xlsx файлов.

Первое. Пустые ячейки с данными.

Такая ситуация может возникнуть в следствии копирования. Это так же может произойти, если данные в документ excel вставляются листами или столбцами/строками из файлов, созданных в ИНФИН, 1С, SAP и других подобных ERP-системах. Объясняется это тем, что они генерируют в файл «пустые» ячейки (забитые пробелами) и объекты типа Надпись. Со временем файл становится очень объемным, так как забивается этими невидимыми глазу данными.
Для автоматического удаления этих пустых ячеек есть отличный макрос, который вычистит все пустые ячейки автоматически, после чего торможение при работе с файлом, а так же его размер сильно сократится:

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

Второе. Очищаем неиспользуемый диапазон листа.

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

1. Выделяем первую пустую строку под вашей таблицей;
2. Нажимаем сочетание клавиш Ctrl+Shift+стрелка вниз (после этого должны выделится все пустые строки до конца листа в книге);
Поиск пустых строк в Microsoft Excel. Нашлось более 65000 пустых строк на листе.
Удалите их, через меню ленты: Главная – Удалить – Удалить строки с листа (или горячей клавишей Ctrl+знак минус);
Те же операции по удалению проделываем и со столбцами.
Если ваша книга Excel содержит один лист, то пропускаем этот пункт и переходим к пункту 6, если у вас в книге много листов, то вышеприведенные операции нужно повторить на каждом листе, при этом, при нажатии комбинации клавиш Ctrl+End активная ячейка перемещается не на последнюю ячейку с заполненными данными, а в пустую ячейку, которая может быть ниже или правее.
Сохраняем наш очищенный xlsx-файл. Теперь самое время проверить размер файла:

Наш 111-мегабайтный файл Excel после очистки от пустых строк превратился в 1,23 Мб.

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

Третье. Пересохраняем файл.

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

Сохраняем файл в бинарный формат .xlsb.

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

«Похудевший» 111-мегабайтный файл Excel после сохранения в бинарный формат .xlsb.

Как видно из скриншота, наш файл стал занимать объем в почти в 22 раза меньший, просто при смене формата с .xlsx в xlsb. Открываться он стал быстрей, при этом мы не потеряли функционал файла, формулы, стили и оформление — все это сталось на месте.

Послесловие

Про Формат файла. Еще раз. Со всей внимательностью отнеситесь к формату файла. Если у вашего Excel-документа формат файла .xls, не поленитесь пересохранить его в новый формат .xlsx. И сейчас я кратко объясню почему это необходимо сделать:

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

Начиная с версии Excel 2007 компания-разработчик Microsoft ввела новые, современные форматы файлов.

Формат .xlsx — это структурированный, по сути заархивированный xml-файл, который при том же объеме данных способен быть в среднем 6 раз меньше устаревшего .xls.

Формат .xlsm используется для макросов.

Формат .xlsb — бинарный формат, который в 2 раза более компактен но уже по сравнению с .xlsx.

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

Заключение

На данный момент я рассказал вам о 3-х вариантах оптимизации и очистки .xls и .xlsx файлов от мусора, который увеличивает объем документа и приводит к торможению. Какой метод лучше и каким пользоваться? — В итоге решать вам.

Подписывайтесь на обновления! Всем удачи и добра!

Чистка книг Excel

При работе с большим объемом меняющейся информации, которую редактируют множество людей в Excel,

периодически возникает ряд проблем с рабочими книгами:
— чрезмерное разрастание книг до десятков мегабайт;
— замедление открытия, сохранения книг, медленная работа с ними;
— заполнение книг разного рода вспомогательной разметкой «под себя»;
— проблемы совместимости у тех, кто так и не расстался с Excel 2003;
— сложности с подготовкой чистовых файлов для внешних пользователей;
— сложности подготовки к выводу на печать больших объёмов данных.

У меня есть некоторый опыт по улучшению работы сильно захламленных файлов, подготовки их к печати и выдачи их чистового варианта внешним пользователям, могу поделиться с теме рядом соображений и результатами экспериментов, некоторыми алгоритмами. Например, при подготовке чистовиков мне удается уменьшать в размер книги в 13-17 раз без смены формата файла (обработка не универсальна, заточена под конкретный тип информации). В этой теме предлагаю поучаствовать тех, кто решает сходные проблемы, хочет обменяться опытом и узнать больше, чем ему известно на данный момент. Постить в эту тему предлагаю в формате «Вопрос — Ответ», то есть обозначать частый тип захламления книг и решение — последовательность действий в Excel или VBA-код, решающие задачу.

Объединение книг в Excel
Нужна помощь начинающему. Имеются n-ное количество книг Excel (заранее не известно сколько надо.

Excel: Копирование из других книг в текущую
Здравствуйте. Помогите, пожалуйста. Каков в Excel принцип копирования из других книг в текущую.

Вытянуть данные из закрытых книг в Excel
Следующая ситуация: В ячейке А1 активной книги прописан полный путь к *.xls файлу, в ячейке А2 -.

Excel: пакетное изменение настроек книг
Добрый день! Есть такая задача-я печатаю множество файлов Эксель на заказ,почти в каждом есть.

Создаю раздел на сайте с Web-формой для ввода-вывода информации в базу данных. В базе данных создаю жесткие правила ввода данных и политику их устаревания.

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

korvindeson, Аксима, в моём случае я не управляю ни формой ввода, ни начальным шаблоном. А так бы это был идеальный вариант. Можно было бы и базу соорудить, если бы их до меня не соорудили в разных отдельных корпоративных системах. Но системы несовершенны, ~~недопользователи~~ люди выгружают что есть в Excel и там с этим работают, творчески раскрашивая, форматируя, искажая начальную форму таблиц. Мне достаются книги на пару тыс. листов весом свыше 30 Mb, которые мне удается «сжимать» до приемлемых 2,5 Mb (без смены формата файла). В книгах много всего интересного! Мне приходится работать на i-и итерации в цепочке, поэтому вынужден обрабатывать то, что имеется (и как бы оно ни выглядело).

Проблема:
Лист книги (форматов .xlsx, .xlsb) или всю книгу невозможно конвертировать в .xls Excel сообщает о превышении максимального количества форматов ячеек. Сама таблица крошечная и не использует новые возможности (появившиеся в Excel 2007 или более новой). Анализ .SpecialCells(xlCellTypeLastCell) показывает, что истинная ширина листа свыше 16 тыс. столбцов, форматы ячеек и значения/формулы в конце пусты. Суть в том, что в этом месте ширины столбцов заданы нестандартными.

Решение:
Удаление лишних столбцов. Кто пробовал сделать такое удаление на книге в тысячи листов автоматически, мог заметить, что на i-й итерации приложение выдает ошибку удаления. Дело в том, что дельта изменений сохраняются в оперативной памяти до тех пор, пока файл не будет сохранен. Поэтому каждые несколько листов делаю принудительное сохранение книги. Результат — уменьшение размера книги, книгу можно сохранить в .xls без сообщений об ошибке или открыть из Excel 2003 с пакетом совместимости.

Добавлено через 18 минут
Проблема:
На листе полно ~~неведомой фигни~~ графических объектов, таких как: фигуры, текстбоксы, диаграммы, рисунки. Иногда они замаскированы, имеют нулевой размер и несколько сотен таких объектов на каждом из нескольких листов делает работу с таблицами почти невозможной — тормозит ввод с клавиатуры, тормозит прокрутка листов. Выявить вредителей бывает удается только через VBA.
Решение:
Самое быстрое из найденных решений:

Способ очистки файлов Excel
О.ВАЛЬПА,
г.Миасс Челябинской обл. E-mail: sandh@narod.ru

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

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

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

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

— откройте файл Excel и сохраните его с помощью меню Файл-Сохранить как. в формате таблицы xml;
— сохраните созданный файл таблицы xml с помощью того же Меню
Файл-Сохранить как. в формате книги xls, заменив исходный файл.

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

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

Апострофф, примерно тот же эффект можно получить, последовательно сохраняя в разных других комбинациях форматов: xls -> xlsx -> xls, xls -> ods -> xls. Интересное наблюдение — пересохранил свой «чистовой» файл через формат «Таблица xml 2003» и снова в исходный — немного уменьшил размер (0,48%), а попытавшись сделать то же самое с исходным файлом до «чистки» получил 16gb xml, которые потом уже я не смог повторно открыть, чтобы пересохранить в форматы Excel.

Добавлено через 11 минут
korvindeson, я всё это прекрасно понимаю, в целом соглашусь с вами, и даже постараюсь для этого что-то сделать, но не хотелось бы на этом просто так уйти от темы. Понимаете, часть систем, выгружающих отчеты с хламом, уже завершили свой цикл разработки и могут быть переписаны только по отдельному договору. Плюс толпы ~~недо~~пользователей, которым правила не писаны, даже если им написать их на лбу. Моя ситуация выглядит примерно следующим образом. Опишу метафорой. Каждое утро нахожу под дверью черный ящик, в нём флешка с xls, xlsx, xlsb файлами, отправитель и ответственный за них неизвестен. Моя задача за выделенный промежуток времени выполнить с файлами любые манипуляции, чтобы из ~~говна~~ них сделать конфетку и подбросить в чёрном ящике следующему «счастливчику», которому с ними надо тоже много чего надо сделать.

Добавлено через 22 минуты
Проблема:
Объединения ячеек. Да, может красиво, но в неумелых руках — это большое зло. Вы лишаетесь возможности выделять и копировать произвольные участки таблиц. Вертикальное объединение в большой таблице приводит к замедлению работы автофильтра. И фильтр потом отсеивает не всё, что бы хотелось. Горизонтальное выделение на всю строку мешает выделить столбец данных без заголовков.
Решение:
Горизонтальные объединения без потери внешнего вида заменяю на выравнивание по горизонтали «по центру выделения». Внешний вид не меняется, работать становится в разы удобней.
Вертикальные выделения в таблицах а-ля классические таблицы баз данных. Если в объединении нечисловые значения, включая даты, то объединение можно убрать, а значение — продублировать на все строки, ранее объединенные. Для эстетического эффекта можно текст окрасить в белый цвет кроме первой строки. Тогда всё «объединение» при фильтрации будет попадать в цель, автофильтр не будет долго думать при установке условий в нескольких столбцах.

Добавлено через 10 минут
Проблема:
Внешние ссылки. Как правило, им не на что сослаться. И непонятно, не полетит ли всё к чертям, если дать им обновиться. Внешние ссылки привязываются к имени файла и редко себя оправдывают, кроме «расчётов на полях» в черновике.
Решение:
Внешние ссылки надо рвать без колебаний. Данные — Изменить связи — Разорвать связь. Ну или то же самое программным путем. Если не хотите ссылок между листами внутри книги, сделайте так:
— переместить лист в новую книгу;
— в новой книге разорвать связи (если есть);
— вернуть лист в исходную книгу.

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

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

Предлагаем инструкцию, как можно уменьшить размер Excel-файла и ускорить работу с таблицами.

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

Уменьшить рабочую зону

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

Откройте таблицу и нажмите клавиши Ctrl+End. Программа переместит вас на крайнюю строку, которую она считает рабочей. Сравните, где реально заканчивается ваша таблица и номер крайней ячейки.

Например, у вас таблица реально заканчивалась на 667 строке, а после нажатия клавиш Ctrl+End нас переместили на 1971 строку. Выходит, что 1304 строки нам не нужны, но программа считает их рабочими – и обрабатывает при открытии.

Нам нужно удалить лишние строки и ячейки. Кликаем на первую пустую ячейку под таблицей, нажимаем комбинацию клавиш Ctrl+Shift и стрелку вниз. Так выделятся все пустые строки. Забавно, что у нас выделилось более 60 тысяч строк, а не до 1971.

Кликаем правой кнопкой мышки на выделенную область и нажимаем «Удалить». Пользуйтесь только таким способом удаления, не нажимайте клавишу Del. Потому что с клавишей Delete удаляется только содержимое таблицы, но не сама таблица.

После этого появится сообщение, где мы выбираем «Строку» и нажимаем ОК.

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

Лишнее форматирование

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

То же касается, если таблица с картинками. Удалите лишние изображения.

Сжатие изображений

Но если все-таки нельзя удалить картинку или фото из документа, ее можно сжать. Выделяем изображение и кликаем на вкладку «Формат». Здесь нам нужно выбрать пункт «Сжать рисунки».

Выставляем настройки так, как показано на скриншоте, и нажимаем «ОК».

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

Удаление ссылок

Внутренние и внешние ссылки таблицы утяжеляют файл. Избавьтесь по возможности от них. Но делать это нужно осторожно: если ссылки в таблице динамические и будут изменяться со временем, удалять такое содержимое нельзя. Если же таблица содержит статические данные, которые не будут изменяться, удалите ссылки (замените их на статические).

Выделяем область ссылок. Копируем их клавишами Ctrl+C, а затем кликаем правой кнопкой мышки по выделенной области. Выбираем в параметрах вставках пункт «Значение» (обозначено на скриншоте).

Сохраняем файл в другом формате

Формат XLS – несколько устаревший, потому что сейчас новые версии Excel используют сжатый формат XLSX (по аналогии с вордом – docx). Чтобы сохранить файл в другом формате, кликните вверху таблицы контекстное меню «Файл» и нажмите «Сохранить как».

Сохраните файл на компьютер, выбрав тип файла «Книга Excel». Таблица сохранится в новом расширении xlsx и будет весить меньше. Для сравнения: наш файл xls весил чуть больше 200 Кб, а новый xlsx более чем в два раза меньше – 78 Кб.

Сжатие файлов онлайн

Можно воспользоваться онлайн-сервисами, которые предлагают сжать эксель файл. Один из таких – Wecompress (https://www.wecompress.com/ru/). Заходим на главную страницу и выбираем файл, нажав на плюсик.