Эксель слишком много форматов ячеек

Эксель слишком много форматов ячеек

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

Работая с большой книгой в 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 ) файл помимо того, что значительно прибавляет в весе еще и постоянно запрашивает обновление связей. Данную опцию следует применять осторожно, т.к. она не отбирает какие-то конкретные типы формул — она заменяет формулы значениями полностью на всем листе. А далеко не всегда необходимо избавляться от всех формул на листах.
  • Удалять исходные данные сводных таблиц — по умолчанию при создании сводной таблицы она хранит внутри себя все исходные данные, на основании которых построена. Это позволяет производить изменения внутри сводной таблицы быстрее, но вместе с тем опять же прибавляет файлу дополнительный размер. Поэтому, если исходные данные для сводной таблицы расположены в том же файле, но на другом листе или доступны иным способом — можно смело применять эту опцию.
  • Удалить примечания — команда удаляет примечания к ячейкам(Рецензирование -Создать примечание). Может пригодится, если в файле создано много примечаний, которые более не используются. Особенно актуально, если примечания содержат еще и картинки в качестве фона — такие примечания способны значительно увеличить размер файла.
  • со всего листа — будут удалены все примечания на листе
  • из диапазона — будут удалены только примечания, расположенные исключительно в указанном диапазоне ячеек
Читайте также:  Как поменять место установки игры в steam

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

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

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

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

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

15 ответов

" форматы ячеек " сложны. Ячейки на самом деле не имеют "формата". Они имеют шрифт (который сам имеет имя и размер), Числоформат, высоту, ширину, ориентацию и т. д.

Итак, вам нужно определить, что вы подразумеваете под "формат".

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

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

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

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

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

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

  • требуется .Net3.5 и MS Excel 2007.
  • будет исправить файлы xlsx или xlsm.
  • сообщение имеет файл ReadMe, чтобы пойти с приложением.

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

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

У меня была эта проблема, нашел самый простой способ очистить ее с помощью этот Excel добавить в. Похоже, это "официальный" ответ от страница Microsoft по проблеме.

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

  1. нажмите на Файл
  2. в разделе Управление нажмите кнопку Go.
  3. в окне надстройки нажмите кнопку Обзор, перейдите к месту сохранения XLAM-файла, выделите его и нажмите кнопку ОК.
  4. включите новую надстройку в окне надстройки и нажмите кнопку ОК.
  5. на панели ленты главного экрана теперь должен быть раздел "удалить стили", который состоит из кнопки, показывающей количество стилей в книге (возможно, тысячи, если у вас есть эта проблема).
  6. Нажмите кнопку на ленте, и она устранит все дубликаты форматы.
Читайте также:  Как добавить субтитры к фильму

Если вы не видите его, убедитесь, что он показывает правильно на ленте. Щелкните правой кнопкой мыши на ленте и скажите "Настроить ленту". Затем нажмите на надстройки. В разделе активные надстройки приложений вы увидите "удалить дополнительные стили".

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

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

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

другой вариант-переключиться на Excel 2007, который имеет ограничение на уникальные форматы ячеек, увеличенное с 4,000 до 64K.

отказ от ответственности: я владею SpreadsheetGear LLC

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

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

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

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

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

Я хотел бы знать, что такое tempstyle.заблокированное свойство фактически ссылается на другое, чем "логическое значение, указывающее, заблокирован ли объект".

Если вы можете открыть файл > правка > очистить > форматы.

выделите лист, прежде чем нажимать форматы, которые он работал для меня.

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

это ошибка в Excel, которая поражает все выпущенные версии.

проблема может быть исправлена с помощью этой.

работает с XLS и XLSX файлы.

Я знаю, что выше есть некоторые интересные методы для решения этой ошибки.

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

  1. копировать рабочий лист в новый файл-это эффективно, но занимает время
  2. удалить диапазоны, которые вы не знаете, что они являются
  3. переформатируйте листы, чтобы у вас был набор форматов mimimum
  4. очистить все формат

надеюсь, вам понравятся эти основные советы

Я в значительной степени использовал все ответы здесь, но сообщение об ошибке осталось.

после прочтения этой страницы поддержки microsoft (https://support.microsoft.com/en-us/kb/213904), я пришел в XLStylesTool.исполняемый.

но, опять не повезло (потому что я использовал его неправильно).

У меня windows seven, поэтому версия windows 8/10 не работает.

Я читаю комментарии со страницы загрузки (потому что это не написано в информации о загрузке) здесь https://sergeig888.wordpress.com/2011/03/21/net4-0-version-of-the-xlstylestool-is-now-available/

Я узнал, что мне нужно.NET4.Версия 0 для работы под win7 (эти манекены не могут просто назвать ее версией win7 . )

моей следующей проблемой было то, что она не работает на *.XLS, поэтому я открыл excel и сохранен как *.XLSM, который работает.

после этого просто нажмите "получить файл Excel" и "файл процесса" (я проверил "зафиксировать изменения и открыть в excel", а в excel "сохранить как""*.в XLS’. Закройте excel, откройте файл, работает (без предупреждения) .

Если это дает вам ошибку "слишком много форматов ячеек", то сохраните документ сразу. Измените имя документа на какое-либо другое имя на данный момент. Откройте документ и сохраните его с тем же именем, что и ранее. Теперь закройте документ и откройте его, он больше не будет отображать эту ошибку. Иногда он просто ушел с простым сохранением, но это странно для меня!

одно простое решение в случае, если это небольшой файл:

просто скопируйте все и вставьте его в Word. Он будет обнаруживать содержимое в виде таблиц. Затем выберите все столбцы (не полный стол через крестик в верхнем левом углу) и мимо него в новый .XLS лист и вы будете иметь только четкие значения.

Ссылка на основную публикацию
Шаблоны букв английского алфавита для вырезания
Трафареты и шаблоны букв английского алфавита для вырезания из бумаги, это разнообразные шрифты разного стиля и тематики. Трафареты помогут вам...
Что значит спящий режим компьютера
В операционной системе Windows есть несколько режимов выключения компьютера – это обыкновенный режим, (который полностью выключает PC), режим гибернации и...
Что значит сторнировать документ
Сто́рно (итал. storno — перевод на другой счёт, отвод; от stornare — поворачивать обратно) — в общем смысле возврат к...
Шаблоны для брошюры в ворде
Автор: admin Дата записи Быстрей всего набросать буклет, если под рукой окажется готовый шаблон. Проще всего создать буклет в программе...
Adblock detector