Слишком много различных форматов ячеек excel

Слишком много различных форматов ячеек excel

Это может случиться и с вами.

Работая с большой книгой в Excel в один совсем не прекрасный момент вы делаете что-то совершенно безобидное (добавление строки или вставку большого фрагмента ячеек, например) и вдруг получаете окно с ошибкой "Слишком много различных форматов ячеек":

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

Почему это происходит

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

  • для Excel 2003 и старше — это 4000 форматов
  • для Excel 2007 и новее — это 64000 форматов

Причем под форматом в данном случае понимается любая уникальная комбинация параметров форматирования:

  • шрифт
  • заливки
  • обрамление ячеек
  • числовой формат
  • условное форматирование

Так, например, если вы оформили небольшой фрагмент листа подобным образом:

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

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

Как с этим бороться

Направлений тут несколько:

  1. Если у вас файл старого формата (xls), то пересохраните его в новом (xlsx или xlsm). Это сразу поднимет планку с 4000 до 64000 различных форматов.
  2. Удалите избыточное форматирование ячеек и лишние "красивости" с помощью команды Главная — Очистить — Очистить форматы (Home — Clear — Clear Formatting) . Проверьте, нет ли на листах строк или столбцов отформатированных целиком (т.е. до конца листа). Не забудьте про возможные скрытые строки и столбцы.
  3. Проверьте книгу на наличие скрытых и суперскрытых листов — иногда на них и кроются "шедевры".
  4. Удалите ненужное условное форматирование на вкладке Главная — Условное форматирование — Управление правилами — Показать правила форматирования для всего листа (Home — Conditional Formatting — Show rules for this worksheet) .
  5. Проверьте, не накопилось ли у вас избыточное количество ненужных стилей после копирования данных из других книг. Если на вкладке Главная (Home) в списке Стили (Styles) огромное количество "мусора":

. то избавиться от него можно с помощью небольшого макроса. Нажмите Alt+F11 или кнопку Visual Basic на вкладке Разработчик (Developer) , вставьте новый модуль через меню Insert — Module и скопируйте туда код макроса:

Запустить его можно с помощью сочетания клавиш Alt+F8 или кнопкой Макросы (Macros) на вкладке Разработчик (Developer) . Макрос удалит все неиспользуемые стили, оставив только стандартный набор:

Данная функция является частью надстройки MulTEx

  • Описание, установка, удаление и обновление
  • Полный список команд и функций MulTEx
  • Часто задаваемые вопросы по MulTEx
  • Скачать MulTEx

Вызов команды:
MulTEx -группа Ячейки/ДиапазоныДиапазоныПодготовить таблицу — строки

Иногда в результате многочисленных манипуляций с книгой Excel она "раздувается" — в ней может быть всего один лист, данных не более 1000 строк и пары столбцов, но размер файла при этом мегабайта полтора, два, три, а то и все десять. Но главная проблема даже не столько в размере файла как в том, что при этом файл как правило "тормозит" и долго открывается. А это уже действительно проблема. Почему файл "раздувается"? В большинстве случаев это происходит по "неопытности"(чрезмерное форматирование, много объектов, имен и т.п.). Чтобы найти причину внезапного увеличения файла в размере можно потратить не один час, а то и день. И для этого надо не только знать где искать, но и уметь правильно устранить причину. Команда Оптимизировать книгу сама удалит все лишнее правильно и без ущерба функционалу.
Так же эта команда поможет устранить ошибку избыточных форматов, шрифтов и стилей в книге, когда при этом появляется сообщение "Слишком много различных форматов ячеек":

Читайте также:  Работа с двумя сим картами на андроиде

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

Если лист скрыт:
выбирается действие для скрытых листов

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

Если лист защищен:
выбирается действие, применяемое к листам, на которые установлена защита (РецензированиеЗащитить лист)

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

Во всех выбранных листах:
выбираются действия по очистке и оптимизации, которые необходимо применить ко всем выбранным листам

  • Удалять объекты с нулевыми размерами — опция удалит с листа все объекты с нулевой длиной и шириной. Что это как такое может быть: довольно часто на лист вставляют различные объекты(картинки, фигуры, стрелки и т.п.), забывая выставить правильные свойства. В результате, после удаления строк и столбцов из листа зачастую объекты не удаляются — они просто ужимаются так, что ширина и высота таких объектов становится равна нулю, а сами объекты как следствие становятся невидимыми. Найти вручную такие объекты на листе сложно и тем более — удалить, не трогая остальные объекты. А на размер файла и на работу в листах большое количество таких объектов влияет очень сильно. Как правило такие объекты совершенно не нужны в дальнейшей работе и этот пункт можно почти всегда оставлять включенным.
  • Удалять скрытые объекты — если отметить, то с листов будут удалены все невидимые(скрытые) объекты. Это такие объекты как рисунки, фигуры, диаграммы и пр., которые скрываются кем-то преднамеренно(как правило программно). Скрытые объекты и объекты с нулевыми размерами — это разные вещи. Если объекты с нулевыми размерами в подавляющем большинстве случаев не нужны, то скрытые объекты часто используются. Поэтому прежде чем применить данную опцию следует убедиться, что такие объекты действительно не нужны.
  • Отключить сохранение шрифтов в диаграммах — при создании диаграмм в них часто применяются различные шрифты. Для обеспечения корректной визуализации текста у шрифтов диаграмм есть параметр Автомасштабирование и по умолчанию для новых диаграмм он включен, вследствие чего для одной диаграммы может быть сохранено два и более шрифтов для корректного отображения при различном масштабе. В какой-то момент книга будет переполнена шрифтами и при добавлении новой диаграммы может появится сообщение "В данной книге невозможно использование других шрифтов". При этом необязательно это должно быть изменение шрифта на диаграмме — ошибка может возникнуть и при копировании диаграмм, добавлении и при изменении колонтитулов и даже ячеек. Стандартными средствами для устранения подобной ошибки необходимо либо править реестр, либо вручную выключать автомасштабирование для каждой диаграммы. Но проще просто применить пункт Отключить сохранение шрифтов в диаграммах из команды Оптимизировать книгу.
  • Удалять строки и столбцы за пределами рабочего диапазона — очень важный пункт. Он удаляет все форматирование из ячеек, расположенных за пределами реальных данных листа. Часто при назначении форматирования данным таблицы(заливка ячеек, шрифт, границы и т.п.) выделяются целые строки и столбцы и им назначается форматирование. В этом случае форматы начинают копиться, для каждой ячейки начинает храниться свое форматирование из всевозможных комбинаций свойств и значений. Так же это может повлечь неудобную навигацию по листу посредством полос прокрутки.
    Опция Удалять строки и столбцы за пределами рабочего диапазона как раз удаляет все столбцы и строки, в которых есть какое-либо форматирование, но в которых уже нет никаких данных(формул или текстовых/числовых значений). Это может значительно "облегчить" файл и работу с листами, а так же поможет избавиться от ошибки "Слишком много различных форматов ячеек".
  • Удалять имена с ошибками #ССЫЛКА! — в файле могут скопиться имена(Ctrl + F3), которые ссылаются на несуществующие данные. Такие имена уже никогда не будут работать без серьезных правок и как правило не нужны. Однако наличие большого количества таких имен может так же "тормозить" работу с данными и придавать дополнительный объем размеру файла. Данная опция удалить все такие имена, если область действия для этих имен ограничивается исключительно очищаемым листом.
  • Заменять формулы значениями — данная опция заменяет все формулы на листе на непосредственно результат вычислений этих формул. Для чего это нужно? Некоторые формулы могут значительно замедлять работу внутри файла. Так же, если на листе много ссылок на другие книги( =[Пример.xlsx]Лист1!$J$10 , или еще хуже — если эта книга закрыта: =’C:UsersДмитрийDesktop[Пример.xlsx]Лист1′!$J$10 ) файл помимо того, что значительно прибавляет в весе еще и постоянно запрашивает обновление связей. Данную опцию следует применять осторожно, т.к. она не отбирает какие-то конкретные типы формул — она заменяет формулы значениями полностью на всем листе. А далеко не всегда необходимо избавляться от всех формул на листах.
  • Удалять исходные данные сводных таблиц — по умолчанию при создании сводной таблицы она хранит внутри себя все исходные данные, на основании которых построена. Это позволяет производить изменения внутри сводной таблицы быстрее, но вместе с тем опять же прибавляет файлу дополнительный размер. Поэтому, если исходные данные для сводной таблицы расположены в том же файле, но на другом листе или доступны иным способом — можно смело применять эту опцию.
  • Удалить примечания — команда удаляет примечания к ячейкам(Рецензирование -Создать примечание). Может пригодится, если в файле создано много примечаний, которые более не используются. Особенно актуально, если примечания содержат еще и картинки в качестве фона — такие примечания способны значительно увеличить размер файла.
  • со всего листа — будут удалены все примечания на листе
  • из диапазона — будут удалены только примечания, расположенные исключительно в указанном диапазоне ячеек
Читайте также:  Первый раз за границу куда лучше поехать

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

  • Лишние стили (оставив стандартный набор) — опция удаляет из книги избыточные стили, оставляя лишь встроенные по умолчанию. Лишние стили могут появиться либо при копировании ячеек полностью из одной книги в другую(актуально при использовании инструментов вроде Сбор данных с листов/книг) или при создании стилей вручную. Как правило стили используются редко, а проблем прибавить могут. Если в книге накопится достаточное количество разнообразных стилей, то можно получить ошибку файла "Слишком много стилей". Посмотреть стили книги можно с вкладки ГлавнаяСтили ячеек:
  • Удалять имена с ошибками #ССЫЛКА! — аналогичная такой же функции для листов, но данная опция удалить все такие имена, если область действия для этих имен не ограничивается каким-то конкретным листом, а распространяет свое действие на всю книгу, т.е. действует на любом листе.

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

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

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

Признаки

В Microsoft Office Excel 2003 при форматировании ячейки или диапазона ячеек отображается следующее сообщение об ошибке:

Читайте также:  Как удалить контакты гугл с андроида

Слишком много различных форматов ячеек.

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

В Microsoft Excel 2013, Microsoft Excel 2010 или Microsoft Excel 2007 файлы могут возникать следующее сообщение об ошибке:

Excel обнаружил нечитаемый контент в файле.

Следующие сценарии также связаны со стилями:

При открытии файла отсутствует все форматирование.

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

При попытке вставить текст отображается следующее сообщение об ошибке:

Microsoft Excel не удается вставить данные

В этом сценарии все параметры меню для вставки и сочетания клавиш CTRL + V игнорируются и не выдают результатов, несмотря на то что буфер обмена не пуст.

Причина

Эта проблема возникает, если книга содержит более 4 000 различных комбинаций форматов ячеек в Excel 2003 или 64 000 различных комбинаций в Excel 2007 и более поздних версиях. Сочетание определяется как уникальный набор элементов форматирования, применяемых к ячейке. Комбинация включает все форматирование шрифтов (например, шрифт, размер шрифта, курсив, полужирное начертание и подчеркивание), границы (например, расположение, толщину и цвет), шаблоны ячеек, форматирование чисел, выравнивание и защиту ячеек.

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

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

Книга, имеющая более 4 000 стилей, может быть открыта в Excel 2007 и более поздних версий из-за увеличенного ограничения на форматирование. Однако это может привести к ошибке в Excel 2003.

Разрешение

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

Способ 1

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

Способ 2

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

В этих статьях необходимо установить и добавить раздел реестра.

Способ 3

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

Средство кслстилес форматов Excel (XLSX, xlsm)

Форматы двоичных файлов Excel (xls, XLSB), книги, защищенные паролем, и зашифрованные книги

Вы также можете скачать копию средства Кслстилес для Windows 10, Windows 8,1 и Windows 8 из Microsoft Store.

Способ 4

Упростите форматирование книг. Например, для упрощения форматирования следуйте приведенным ниже рекомендациям.

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

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

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

Если вы применяете шаблоны к ячейкам, удалите эти шаблоны. Для этого откройте диалоговое окно " Формат ячеек ", перейдите на вкладку " узоры " и выберите пункт " Нет цвета".

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

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

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

В большинстве случаев достаточно текущего числа различных комбинаций форматирования для одной книги (формат 4 000 для xls и 64 000 для формата xlsx). Эта проблема, скорее всего, возникает, только если книга содержит большое количество листов, использующих другое форматирование, или если большинство ячеек форматируется по-разному.

Заявление об отказе от сторонних поставщиков

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

Ссылка на основную публикацию
Adblock detector