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

Excel: слишком много различных форматов ячеек (Эксель)

При работе с программой Microsoft Excel, а именно с таблицами, можно столкнуться с этой ошибкой. Есть несколько способов для её устранения.

Более новое расширение Xlsx поддерживает 64 тыс. форматов, а Xls не более 4000. Если лимит форматов был превышен — появляется ошибка. Она является следствием использования многих элементов форматирования в ячейках (например заливка или шрифт).

Первый вариант решения проблемы. Сохранение документа с использованием расширения Xslx

Использование этого варианта подойдет если файл был сохранён с расширением Xls. Для начала откроем раздел «Файл» и в списке выберем пункт «Сохранить как».

Далее нужно выбрать папку куда будет сохранён файл. В специальном поле «Тип файла» после нажатия на него, выбираем среди элементов списка
«Книга Excel» и сохраняем файл.

После этой процедуры можно будет работать с 64 тыс. форматами. Соответственно — проблема решена.

Второй вариант решения проблемы. Очистить форматы в пустых ячейках таблицы

Если первый способ не помог или же в более новом расширении также возникает ошибка, то это может значить что превышено 64 тыс. форматов. Вполне вероятно что пользователю нужен только формат .xls для работы.

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

Область которая была форматирована про запас, выделяется с помощью нажатия мышкой на границе координаты строки и выделяем ее. После нажатия комбинации клавиш на клавиатуре Ctrl-Shift-клавиша вниз будет отмечена часть ниже таблицы

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

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

Работая с большой книгой в 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 и скопируйте туда код макроса:

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

Оптимизировать книгу

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

Группа: WinCity Team
Сообщений: 2141
Регистрация: 26.06.2001
Из: Россия, Александров-Кольчугино
Пользователь №: 131
Конфигурация

Группа: Гость форума
Сообщений: 2
Регистрация: 28.11.2001
Из: Ukraine
Пользователь №: 747
Конфигурация

цитата: Из сообщения пользователя Gluzer:
А при этом сообщение кнопка справки не появляется? Если пишет что слишком много — значит и правда много[/QUOTE]

Увы, справка не появляется. Как бы узнать сколько форматов можно ставить (я использовал только числовой 0 и 2 знака после запятой, текстовай, дата и общий — разве это много?) и где это определяется (может в настройках). Как она считает форматы, для каждой ячейки отдельно или один формат для всех однотипных. Число форматов считается для каждой страницы отдельно или для всего файла целиком. Применяется расцветка и рамки (может они тоже фходят в понятие формат?).
Сталкиваюсь с таким ограничением впервые, правда много сказать о файле не могу, писал его не я, но при быстром просмотре никаких криминалов (типа рекурсии) нет. Разве что ссылки на несуществующие внешние файлы, но до сих пор это работало. Ошибка выскакивает после очередной попытки изменить разрядность ячейки типа число. Спасибо за ответ.

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

Я прочитал некоторые из результатов поиска Google, и они говорят, что я должен упростить форматирование, но я даже не знаю, как я до 4000 различных форматов ячеек, не говоря уже о том, как много я использую, какие из них, так что я могу удалить некоторые.

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

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

Проблема вы описываете заставила меня (и Коллега) потерять много часов производительности при использовании Excel 2010. Следующий кода VBA / макро помог мне бросить файл .xlsm с помощью 3540 стилей вплоть до 34 лет.

«Клеточные форматы» являются сложными. Клетки на самом деле не имеют «формат». У них есть шрифт (который сам по себе имеет имя и размер), NumberFormat, высота, ширина, ориентация и т.д.

Таким образом, вы должны определить, что вы имеете в виду под «формат».

Ниже код, чтобы получить имя и размер шрифта. Вы можете заменить любые атрибуты, которые вы хотите.

Приведенный ниже код предполагает, что вы создали рабочий лист с именем «Форматы» в книге. После запуска макроса, шрифт Название и размеры будут перечислены в этой таблице.

Многие люди, кажется, столкнулись с этой проблемой.

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

Я написал утилиту для исправления файлов XL2007 OOXML , которые могут быть сохранены до XL2003. Вот ссылка на запись в блоге:

Требуется .Net3.5 и MS Excel 2007.
Будет ли исправить XLSX или XLSM файлы.
Пост имеет ReadMe файл, чтобы перейти с приложением.

Нет необходимости идти на риск дальнейшего развращает файл с помощью Open Office, как это не предлагается на некоторых других форумах

Я имел эту проблему, нашел самый простой способ , чтобы очистить это было с помощью этого Excel добавить . Это , как представляется , «официальный» ответ от страницы Microsoft по этой проблеме .

Для людей, которые так запутался, я был в использовании .xlam файлов, после его загрузки вы делаете это в Excel:

Эти ошибки , которые приводят к «Слишком много форматов ячеек» сообщение об ошибке в Excel 2007+ были рассмотрены: http://sergeig888.wordpress.com/2011/05/06/msft-released-hot-fix-for-excel-2007 -Custom-стили дублирование / Обратите внимание , что исправление ошибок не будет удалять уже существующие стили , связанные с коррупцией файла. Инструменты , основанные на Open XML (доступны бесплатно) единственный вариант , который может удалять элементы , недоступные для Excel объектной модели на основе инструментов: например, плохие стили, переодетые себя как встроенные, скрытые стили и т.д . Open XML средствами очистки на основе 100% стилей , связанных с коррупцией свободных файлов.

Это удалит все стили КРОМЕ стилей по умолчанию (нормальный, толковый, 20% Accent1 и т.д.). В том числе стилей, созданных пользователем, но это быстрый и грязный способ очистить книгу:

Я хотел бы знать, что tempstyle.locked собственность на самом деле относится к другим, чем «логическое значение, которое указывает, если объект заблокирован».

Одним из решений может быть использование ASAP утилиты . В разделе листа есть remove all unused styles вариант. Затем вы должны закрыть книгу и открыть заново, я думаю.

Я видел эту проблему раньше. Можно воссоздать, чтобы доказать, что я Abaout сказать. Это немного поймать 22, но если у вас есть лист, который имеет проблему, «Слишком много форматов», открыть новый лист и скопировать одну ячейку из слишком большого количества форматов листа и просто вставить его в новую книгу. Эта книга будет теперь «Infected», как это было и будет также иметь слишком много форматов ошибок. Кажется, что много данных формата приходит с этой пастой, если вы используете пасту специальной, чтобы ограничить его.

Как сделать вещи лучше? Ну обходной путь заключается в использовании функции Сохранить как HTML. Медведь со мной , хотя это работает. Я говорю о 2003 здесь, я не знаю , что происходит в 2007 году, может быть , эта ошибка была исправлена. Так что . Сохранить как Html затем закройте первенствовать. Загрузите новую сессию и загрузить это в убедившись , что вы не запускать макросы , а затем после загрузки сохранения в виде таблицы.

Когда вы закрыть и снова открыть эту новую таблицу вашей Слишком много форматов Беды должна исчезнуть.

SpreadsheetGear для .NET будет сливаться неуникальными форматы , которые могут помочь в вашей ситуации.

Вы можете скачать бесплатную пробную версию здесь , если вы хотите увидеть , помогает ли это. Просто загрузите книгу в «SpreadsheetGear 2009 для Windows» приложение , которое устанавливается вместе с программой оценки , а затем сохранить книгу.

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

Другой вариант заключается в переходе на Excel 2007, который имеет ограничение на уникальных форматов ячеек увеличилось с 4000 до 64К.

Отказ от ответственности: У меня есть SpreadsheetGear LLC

Читать еще: Эксель среднее арифметическое