Слишком много различных форматов ячеек
Это может случиться и с вами.
Работая с большой книгой в Excel в один совсем не прекрасный момент вы делаете что-то совершенно безобидное (добавление строки или вставку большого фрагмента ячеек, например) и вдруг получаете окно с ошибкой "Слишком много различных форматов ячеек":
Иногда эта проблема возникает в еще более неприятном виде. Накануне вечером вы, как обычно, сохранили и закрыли свой отчет в Excel, а сегодня утром не можете его открыть - выдается подобное же сообщение и предложение удалить все форматирование из файла. Радости мало, согласитесь? Давайте разберем причины и способы исправления этой ситуации.
Почему это происходит
Такая ошибка возникает, если в рабочей книге превышается предельно допустимое количество форматов, которое Excel может сохранять:
- для Excel 2003 и старше - это 4000 форматов
- для Excel 2007 и новее - это 64000 форматов
Причем под форматом в данном случае понимается любая уникальная комбинация параметров форматирования:
- шрифт
- заливки
- обрамление ячеек
- числовой формат
- условное форматирование
Так, например, если вы оформили небольшой фрагмент листа подобным образом:
... то Excel запомнит в книге 9 разных форматов ячеек, а не 2, как кажется на первый взгляд, т.к. толстая линия по периметру создаст, фактически 8 различных вариантов форматирования. Добавьте к этому дизайнерские танцы со шрифтами и заливками и тяга к красоте в большом отчете приведет к появлению сотен и тысяч подобных комбинаций, которые Excel будет вынужден запоминать. Размер файла от этого, само собой, тоже не уменьшается.
Подобная проблема также часто возникает при многократном копировании фрагментов из других файлов в вашу рабочую книгу (например при сборке листов макросом или вручную). Если не используется специальная вставка только значений, то в книгу вставляются и форматы копируемых диапазонов, что очень быстро приводит к превышению лимита.
Как с этим бороться
Направлений тут несколько:
- Если у вас файл старого формата (xls), то пересохраните его в новом (xlsx или xlsm). Это сразу поднимет планку с 4000 до 64000 различных форматов.
- Удалите избыточное форматирование ячеек и лишние "красивости" с помощью команды Главная - Очистить - Очистить форматы (Home - Clear - Clear Formatting). Проверьте, нет ли на листах строк или столбцов отформатированных целиком (т.е. до конца листа). Не забудьте про возможные скрытые строки и столбцы.
- Проверьте книгу на наличие скрытых и суперскрытых листов - иногда на них и кроются "шедевры".
- Удалите ненужное условное форматирование на вкладке Главная - Условное форматирование - Управление правилами - Показать правила форматирования для всего листа (Home - Conditional Formatting - Show rules for this worksheet).
- Проверьте, не накопилось ли у вас избыточное количество ненужных стилей после копирования данных из других книг. Если на вкладке Главная (Home) в списке Стили (Styles) огромное количество "мусора":
...то избавиться от него можно с помощью небольшого макроса. Нажмите 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). Макрос удалит все неиспользуемые стили, оставив только стандартный набор:
Ссылки по теме
- Как автоматически подсвечивать ячейки с помощью условного форматирования в Excel
- Что такое макросы, куда и как копировать код макроса на Visual Basic, как их запускать
- Книга Excel стала очень тяжелой и медленной - как исправить?
У меня на компьютере OptionExplicit включен,но я определил переменные, так что перестал сбоить. Но почему-то Excel запрашивает разрешение, когда стили из новой книги импортирует - не знаете, почему так?
Но самое интересное не в этом - хотел поделиться - встречаются такие коварные стили, которые не убиваются вашим кодом, да и вообще никаким VBA кодом из мною найденных в интернете не убиваются (могу пример прислать, если надо). Помог AddIn - XLStylesTool (Excel File Clenaup Tool) c сайта sergeig888.wordpress.com - он бесплатный. Насколько я понимаю, эта программа редактирует код файла, что руками достаточно сложно сделать (надо переименовывать расширение, лезть в исходный код и не всегда результат гарантирован); и сносит сбитые и скрытые стили. Пользуюсь постоянно, может чуть более, чем вашим сайтом. Буду рад если кому пригодится.
Спасибо за дополнение про - хорошая штука, встречал раньше. Допишу-ка про нее в статью, пожалуй
Этот код делает нужное, без лишних телодвижений, потом только не забыть сохранить книгу. И с новым ее открытием будет стандартный набор.
Подскажите, почему не работает ни 1-й ни 2-й код по удалению стилей?
Excel просто виснет при запуске макроса. (Excel 2010).
Заранее благодарю.
А ещё, мне приходилось даже на ночь макрос запускать)) часов 6 чистил))
может быть есть способ удалить абсолютно все пользовательские стили, но с сохранением форматов ячеек?
Подскажите пожалуйста.
Для себя проблему решил переносом значений, формул и форматов в новую книгу. Стили не перенеслись и были беспощадно удалены со старой книгой)
Вообще, чем дальше, тем больше понимаю, что со старенького, но рабочего 2003 года слезу только под дулом пистолета
Отсюда и дикое количество форматов
Помог другой вариант, который я дополнил снятием защит с листов:
Sub StyleKiller()
Dim n As Long, i As Long
For i = 1 To Sheets.Count
Sheets(i).Unprotect
Next
With ActiveWorkbook
n = .Styles.Count
For i = n To 1 Step -1
If Not .Styles(i).BuiltIn Then .Styles(i).Delete
Next
End With
End Sub