Файл слишком большой, удалите строки или столбцы и повторите попытку

Обновлено: 21.11.2024

Вы когда-нибудь задумывались, почему ваш, казалось бы, простой файл Excel такой большой (с точки зрения КБ или МБ) или почему он кажется «медленным»? Недавно мы получили файл от клиента, который был разочарован тем, что Excel слишком долго открывал файл или обрабатывал какие-либо изменения (т. е. несколько секунд каждый раз — как это раздражает!). Размер файла составил более 20 МБ, несмотря на то, что он не содержал большого количества данных или каких-либо сложных вычислений. Оказывается, причиной было то, что мы называем «раздуванием Excel». Раздувание означает, что в файле могут быть «скрытые» проблемы, вызывающие проблемы с производительностью. Эти проблемы можно легко исправить, если знать, где искать и что делать. С помощью нескольких простых приемов мы уменьшили размер файла до менее чем 2 МБ.

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

Часть 1. Поиск раздутых вкладок

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

Шаг 1. Выберите «Сохранить как» и в области «Тип файла» выберите «Веб-страница». Это создаст папку в месте сохранения. Вы можете получить предупреждение о том, что некоторые функции несовместимы с веб-страницей. Просто проигнорируйте это и продолжите сохранение.

Шаг 2. Перейдите в папку (там, где вы ее сохранили) и откройте ее. Вы увидите файлы для каждого листа (они будут пронумерованы последовательно, а не с их правильными именами листов), а также множество других файлов.

Шаг 3. Просто отсортируйте файлы по размеру, чтобы увидеть, какие из них самые большие. Обратите внимание, что поскольку файл был преобразован в веб-страницу, совокупный размер этих файлов будет намного больше, чем исходный файл Excel. Не беспокойтесь об этом. Что вас интересует, так это относительный размер всех листов, и этот трюк даст вам быстрое представление об этом, чтобы вы знали, с чего начать поиск.

Часть 2. Уменьшение размера вкладки

Как видите, 5-я и 7-я вкладки в нашем примере файла самые большие. Но как уменьшить их размер?

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

Например, у вас может быть 10 столбцов данных, разбросанных по 5000 строк таблицы. В этом случае размер вашего листа будет основан на используемом диапазоне 50 000 ячеек. Это может показаться большим, но в Excel на листе доступно 17,2 миллиарда ячеек.

Довольно часто сам используемый диапазон раздувается, потому что Excel не только отслеживает содержимое ячейки, но и присваивает ячейкам десятки других атрибутов (цвет, границы, числовые форматы, проверку данных и т. д.). Многие из них не видны пользователю. Назначение некоторых из этих атрибутов может привести к тому, что Excel сочтет ячейку важной и, следовательно, включит ячейку в используемый диапазон листа. Например, если выбрать весь столбец и применить форматирование, используемый диапазон теперь включает все 1 048 576 строк в файле, что увеличивает размер файла и замедляет работу Excel.

Поскольку обычно вас интересуют только те ячейки, которые содержат фактические данные, вы можете быстро уменьшить используемый диапазон (и, следовательно, размер) листа, выполнив следующие действия:

Шаг 1. Выберите лист, который, по вашему мнению, больше ожидаемого.

Шаг 2. Нажмите одновременно клавиши CTRL + END. Вы перейдете в правый нижний угол используемого диапазона листа.

Примечание. В листе 007 в приведенном выше примере это привело нас к ячейке IV503 (см. рисунок ниже). IV — это 256-й столбец, поэтому используемый диапазон составляет 256 столбцов на 503 строки или 128 768 ячеек. Однако у нас были данные только в ячейках от A1 до Q53 или 901 ячейке. Используемый диапазон был в 143 раза больше предполагаемого размера.

Шаг 3. Теперь, когда мы знаем размер используемого диапазона, нам нужно уменьшить его до размера данных. Для этого удаляем неиспользуемые столбцы и строки. С чего начать удаление зависит от размера:

Размер моего файла составляет 700 КБ для электронной таблицы с небольшим количеством текста и несколькими
формулами со ссылками, но без макросов.Я удалил ячейки справа
и внизу используемых ячеек, и размер остался прежним; даже
удаление всех ячеек книги не меняет ее размер. Я
вырезал таблицу и вставил в новый файл, и новый файл имеет
тот же размер, что и оригинал. Есть идеи? Спасибо!

Пит_Великобритания

В книге могут быть определены именованные диапазоны, которые
больше не нужны. Другой распространенной причиной увеличения размера рабочей книги
является наличие некоторых графических объектов, которые больше не нужны
. Убедитесь также, что файл сохранен как последняя версия, а не
как гибридная (например, книга Excel 5.0/95).

Надеюсь, это поможет.

Горд Диббен

Некоторые версии требуют сохранения/закрытия и повторного открытия, прежде чем изменения вступят в силу.

Горд Диббен MVP MS Excel

Я провел следующие тесты и добавил результаты:

Любые предложения по устранению моей проблемы с использованием Excel 2003.

Горд Диббен

Колючий

Есть ли объекты? Нажмите F5, нажмите «Специальные», выберите «Объекты». Если они есть
клавиша Delete быстро их удалит. Если вы
действительно хотите от них избавиться.

Сколько именно данных?

каннансита

Это помогло решить ту же проблему, что и проблема с размером файла.

Удаление объектов резко уменьшило размер..

данфлеминг

На случай, если другое решение может помочь кому-то другому, у меня был файл Excel со сводными таблицами, которые ссылались на внешние исходные данные. Я попытался сохранить извлеченную версию книги, которая не содержала сводных таблиц и не ссылалась на внешние данные, но файл книги оказался больше, чем ожидалось, и не сильно уменьшился в размере, даже если все листы были удалены. Проблема заключалась в том, что перекрестные ссылки на внешние данные все еще сохранялись где-то внутри рабочей книги. Одно из решений состояло в том, чтобы вернуться к версии электронной таблицы перед удалением листов, содержащих сводные таблицы и другие внешние ссылки, а затем перенести внешние исходные данные в книгу, убедившись, что нет дополнительных внешних ссылок (пункт меню: Правка / Ссылки ), а ЗАТЕМ удаление различных листов, которые мне не нужны, включая данные, которые были перенесены обратно и не нужны для извлеченной версии книги. ВНИМАНИЕ! Используйте отдельную копию рабочей книги и исходные данные, скопированные в отдельный каталог, чтобы исходная рабочая книга и ее справочные ссылки на исходные данные не перепутались. [примечание. Другие возможные решения для слишком большой электронной таблицы включали просмотр пункта меню: «формат / лист / показать». и поиск скрытых объектов в пункте меню: «инструменты / макрос / визуальный базовый редактор», но это для более опытных или рискованных пользователей.]

Зарегистрированный пользователь Регистрация Дата 10-09-2010 Местоположение Seku MS-Off Ver Excel 2003 Сообщений 27

Файл Excel все еще большой после удаления всего

Я работал с файлом Excel, и моим основным ограничением был размер. Пытаясь определить, что увеличивает размер файла,
я удалил все рабочие листы, все пользовательские формы, все модули и модули классов, пока ничего не осталось. Хотя пустой файл
не кажется таким уж пустым. В поисках решения я наткнулся на некоторые коды VBA для -DeleteMissingItems-, DeleteOldItems,
для удаления фигур, объектов и т. д. и т. д. Пока ничего не работает, и размер файла по-прежнему составляет 1,2 МБ, когда совершенно пусто.

Кто-нибудь знает, как избавиться от этих бесполезных 1,2 МБ?

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

Заранее спасибо.-

Forum Expert Дата регистрации 23 марта 2004 г. Местоположение Лондон, Англия MS-Off Ver Excel 2019 Сообщений 7038

Создайте новую книгу.
Копируйте существующие листы в книгу лист за листом
Сохраните новую книгу.
Посмотрите, меньше ли это, обычно так и есть.

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

все удалить невозможно, должен остаться хотя бы один лист.

попробуйте найти объекты, которые скрыты vba или
недавно мне дали файл, который был предположительно пуст, но в нем было 15000 мелких объектов (изображений), где они прозрачны и не имеют границ, поэтому не были видны
это часто происходит, когда данные копируются с веб-сайтов путем копирования и вставки.

самый быстрый способ узнать, есть ли объекты, — нажать F5 > нажать специальную кнопку > выбрать объекты

Forum Expert Дата регистрации 23.12.2012 Местоположение Северная Каролина MS-Off Ver Excel 2019 Professional Plus — 2007 Enterprise Сообщений 6 463

Если вы сохраните книгу в формате .xlsb (двоичном), ее размер значительно уменьшится.

Во-вторых, если вы запустите этот макрос в рабочей книге (вставьте его в стандартный модуль и запустите), он проверит невидимые остаточные "данные", находящиеся в ячейках ниже последней строки/столбца того, что видно .
а затем он удалит эти "данные".

Другой способ выполнения той же задачи, что и макрос, — переход по листам. найдите последнюю строку данных / выделите эту строку и все строки под ней, пока не дойдете до нижней части листа, затем
щелкните правой кнопкой мыши выделенную область и выберите УДАЛИТЬ или ОЧИСТИТЬ СОДЕРЖИМОЕ. Этот метод требует много времени и труда.

Зарегистрированный пользователь Регистрация Дата 10-09-2010 Местоположение Seku MS-Off Ver Excel 2003 Сообщений 27

Special-K -
Я пробовал это, но столкнулся с несколькими проблемами со ссылками. не говоря уже о том, что это отнимает много времени,
это не постоянное решение, поскольку то, что вызвало появление этих 1,2 МБ, сделает это снова.

Роэл Джонгман
Я создал новый абсолютно пустой лист и удалил все остальное.
Что касается объектов, я искал их так же, как вы предложили. Объектов нет.

Logit
Я не уверен, что смогу сохранить файл как .xlsb (двоичный), так как я использую Excel 2003 или такое расширение файла может привести к потере данных.
Что касается кода для удаления всего, я пробовал, но ничего не нашел. В Excel 2003 все равно нет скрытых листов.

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

Forum Expert Дата регистрации 05.02.2015 Местоположение Любая Страна MS-Off Ver farerwell Сообщений 8,749

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

Зарегистрированный пользователь Регистрация Дата 10-09-2010 Местоположение Seku MS-Off Ver Excel 2003 Сообщений 27

Forum Expert Дата регистрации 05.02.2015 Местоположение Любая Страна MS-Off Ver farerwell Сообщений 8,749

Зарегистрированный пользователь Регистрация Дата 10-09-2010 Местоположение Seku MS-Off Ver Excel 2003 Сообщений 27

Я загружу не такой уж и пустой файл. Надеюсь, в нем нет скрытой конфиденциальной информации.

ПС. Я попробовал то, что предложил Logit, и сохранил «пустой» файл как двоичный файл с помощью Excel 2010.
Это действительно превратило файл в 14 КБ, как и должно было быть в первую очередь, но это, вероятно, вызовет проблемы
когда я пытаюсь вернуться к Excel 2003.

Это раздутый файл. Я следовал протоколу из ячейки A10, и размер файла уменьшился до 25 КБ.

Forum Expert Дата регистрации 05.02.2015 Местоположение Любая Страна MS-Off Ver farerwell Сообщений 8,749

Зарегистрированный пользователь Регистрация Дата 10-09-2010 Местоположение Seku MS-Off Ver Excel 2003 Сообщений 27

Гленн Кеннеди
Какую версию Excel вы использовали и что вы имеете в виду под ячейкой A10?
. Потому что когда я нажимаю ctrl + End в Excel 2003, я перехожу к A1.

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

Разделите, например, A10. Ctrl сдвинуть стрелку вниз, удалить строки. Сохранять. Клизе. Проверьте размер файла. Перечитайте инструкции по устранению раздувания файлов.

Гуру форума Дата регистрации 27 июня 2016 г. Местоположение Лондон MS-Off Ver Windows: 2010; Mac: 16,13 (O365) сообщений 7085

Простое удаление двух пользовательских стилей ячеек и последующее сохранение рабочей книги уменьшило размер файла для меня в версии 2010.

У меня есть файл Excel неоправданно большого размера (около 27 МБ). Чтобы отследить проблему, я удалил все содержимое, формат и т. д. из этого файла. Теперь у меня осталось 3 листа (а именно Sheet1, 2 и 3).

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

Убедительная просьба к коллегам помочь мне решить проблему.

Файл1 — Copy.xlsx

Факты об Excel

манеканкит

Новый участник

Также пользуюсь помощью другого сообщества.

Вы можете получить доступ к статусу по ссылке ниже.

Размер файла Excel очень велик даже после удаления всего содержимого

MARK858

MrExcel MVP

365
2010

Окна
Мобильный

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

На первый взгляд ссылки кажутся правилами условного форматирования

Алекс Блэкенбург

Известный участник

Ctrl+End на Листе2 переместит вас в самый конец листа 1+ миллион строк.
Ctrl+End на листе 3. Перемещает вас к отметке 850 тыс. строк.

Сколько строк данных у вас есть на самом деле?

Майкл М

Известный участник

2016
2013
2007

Окна

манеканкит

Новый участник

На первый взгляд ссылки кажутся правилами условного форматирования

Привет, MARK858, спасибо за отзыв.

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

манеканкит

Новый участник

Сколько строк данных у вас есть на самом деле?

Здравствуйте, Алекс Блэкенбург! Спасибо за уделенное время и отзыв.

В моем исходном файле я использую около 12 800 строк на Листе 1, около 900 строк на Листе 2 и около 160 строк на Листе 3.
Я попытался удалить данные, заливку и границы из всех этих строк. Но все равно размер файла очень большой, даже если в нем нет содержимого.

Я попытался проверить размер каждого листа по коду VBA.
Я заметил, что размер листа 1 (с 12 800 строк до удаления содержимого) составляет около 1 МБ.
Размер листа 2 (имеющего около 900 строк, используемых только до удаления компонента) составлял около 12+ МБ.
Кроме того, размер листа 3 (имеющего около 160 строк, используемых только до удаления компонента) составлял около 12+ МБ.

Читайте также: