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

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

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

too-many-formats1.png

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

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

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

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

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

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

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

too-many-formats4.png

... то 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) огромное количество "мусора":

    too-many-formats2.png

    ...то избавиться от него можно с помощью небольшого макроса. Нажмите Alt+F11 или кнопку Visual Basic на вкладке Разработчик (Developer), вставьте новый модуль через меню Insert - Module и скопируйте туда код макроса:
Sub Reset_Styles()
    'удаляем все лишние стили
    For Each objStyle In ActiveWorkbook.Styles
        On Error Resume Next
        If Not objStyle.BuiltIn Then objStyle.Delete
        On Error GoTo 0
    Next objStyle
    'копируем стандартный набор стилей из новой книги
    Set wbMy = ActiveWorkbook
    Set wbNew = Workbooks.Add
    wbMy.Styles.Merge wbNew
    wbNew.Close savechanges:=False
End Sub
  

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

too-many-formats3.png

Ссылки по теме




18.11.2014 02:37:20
Николай, отличная статья - давно столкнулся с этой проблемой. Макрос работает чудно, за исключением пары моментов.

У меня на компьютере OptionExplicit включен,но я определил переменные, так что перестал сбоить. Но почему-то Excel запрашивает разрешение, когда стили из новой книги импортирует - не знаете, почему так?

Но самое интересное не в этом - хотел поделиться - встречаются такие коварные стили, которые не убиваются вашим кодом, да и вообще никаким VBA кодом из мною найденных в интернете не убиваются (могу пример прислать, если надо). Помог AddIn -  XLStylesTool (Excel File Clenaup Tool) c сайта sergeig888.wordpress.com - он бесплатный. Насколько я понимаю, эта программа редактирует код файла, что руками достаточно сложно сделать (надо переименовывать расширение, лезть в исходный код и не всегда результат гарантирован); и сносит сбитые и скрытые стили. Пользуюсь постоянно, может чуть более, чем вашим сайтом. Буду рад если кому пригодится.  
23.11.2014 09:45:30
Юрий, у меня разрешений не спрашивает - все делает молча (Excel 2013 из Office 365 Prof Plus).
Спасибо за дополнение про XLStylesTool - хорошая штука, встречал раньше. Допишу-ка про нее в статью, пожалуй :)
21.12.2015 23:29:53
Николай, огромное Вам спасибо, что помогли с решением данной пакостной проблемы больших файлов. Если бы не ваша статья и комментарии Юрия мне бы пришлось туго. Особая благодарность за то, что ваш сайт уже на протяжении 3-ех лет мне помогает в решении различных вопросов с экселем и помогает осваивать азы программирования в эксель.
20.01.2016 11:54:47
Спасибо!!!
04.02.2016 21:59:13
А зачем открывать новую книгу и из нее что-то копировать? Удаляем ведь только пользовательские.

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


Sub Del_Styles()
Dim stl As Style
On Error Resume Next
For Each stl In ActiveWorkbook.Styles
If Not stl.BuiltIn Then stl.Delete
Next
End Sub
 
06.05.2016 13:18:33
Здравствуйте!

Подскажите, почему не работает ни 1-й ни 2-й код по удалению стилей?
Excel просто виснет при запуске макроса. (Excel 2010).
Заранее благодарю.
09.05.2016 09:37:02
Сергей, он не виснет - он так работает. Просто надо подождать. В тяжелых случаях он может несколько минут "думать".
08.06.2016 13:51:28
Спасибо! Очень помогли! Видимо я столкнулась с тяжелейшим случаем  - макрос работал около часа.
А ещё, мне приходилось даже на ночь макрос запускать)) часов 6 чистил))
01.11.2016 18:32:14
Здравствуйте,
может быть есть способ удалить  абсолютно все пользовательские стили, но с сохранением форматов ячеек?
Подскажите пожалуйста.
Наверх