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

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

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

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

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

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

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

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

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

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

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

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

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

Здесь есть несколько направлений:

  1. Если у вас есть файл старого формата (xls), то пересохраните его в новый (xlsx или xlsm). Это сразу поднимет планку с 4000 до 64000 различных форматов.
  2. Удалите лишнее форматирование ячеек и лишние «красивости» командой Главная — Очистить — Очистить форматы (Главная страница — Очистить — Очистить форматирование). Проверьте, есть ли на листах строки или столбцы, отформатированные целиком (т. е. до конца листа). Не забывайте о возможных скрытых строках и столбцах.
  3. Проверьте книгу на наличие скрытых и сверхскрытых листов – иногда на них спрятаны «шедевры».
  4. Удаление нежелательного условного форматирования на вкладке Главная страница — Условное форматирование — Управление правилами — Показать правила форматирования для всего листа (Главная страница — Условное форматирование — Показать правила для этого листа).
  5. Проверьте, не накопилось ли у вас избыточное количество ненужных стилей после копирования данных из других книг. Если на вкладке Главная (Главная) В списке Стили (Стили) огромное количество «мусора»:

    …тогда от этого можно избавиться с помощью небольшого макроса. Нажмите Alt + F11 или кнопка Визуальный Бейсик таб разработчик (Разработчик), вставляем новый модуль через меню Вставка – Модуль и скопируйте туда код макроса:

Sub Reset_Styles() 'удаляем все ненужные стили для каждого objStyle в ActiveWorkbook.Styles При ошибке Возобновить далее, если нет objStyle.BuiltIn Тогда objStyle.Delete При ошибке GoTo 0 Далее objStyle 'копируем стандартный набор стилей из новой книги Set wbMy = ActiveWorkbook Set wbNew = Workbooks.Add wbMy.Styles.Merge wbNew wbNew.Close savechanges:=False End Sub

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

  • Как автоматически выделять ячейки с условным форматированием в Excel
  • Что такое макросы, куда и как копировать код макроса в Visual Basic, как их запускать
  • Книга Excel стала очень тяжелой и медленной – как это исправить?

Оставьте комментарий