Устранение указания МС Эксел "Слишком много форматов ячеек".

[ViktorHo]

Господа,

после поиска путей решения в форуме и апробации некоторых предложений, и понимая, что вопрос имеет "длинную бороду" с 2005 года прошу помощи у гуру Майкрософт. В Экселе 2003: книга имеет размер более 50Мб и более сотни листов. На некоторых листах книги (не более 2000 строк) стало появляться сообщение - "Слишком много форматов ячеек", после чего блокирует любое форматирования ячеек. Помогите устранить это ограничение. Спасибо.

[Шпец Докапыч]

Помогите устранить это ограничение.

Можно поподробнее о файле?:

• - диаграммы есть?
• - листы идентичны по структуре?;
• - какой стандартный шрифт и шрифт в ячейках?
• - и т.п.

Можете заменить все слова в файле на что-то левое, заархивировать, выложить в сети и указать в сообщении ссылочку?

[ViktorHo]

Спасибо за ответ,-
- диаграмм нет,
- листы идентичны по структуре (но тоже много самостоятельных форматов),
- стандартный - "Тахома", шрифт в ячейках в 10% случаях Тахома жирный или выделение подчёркивание,
- много единичных форматирований "цветом и сеткой",
- стандартное форматирование дат, чисел, процентов по длине значения.

Это предупреждение повторяется при увеличении количества форматов и оно знакомо мне. Но, бороться ведь можно!! Через открытый офис (Калк) тестировали - все равно остается извещение. Уничтожить, сократить, уменьшить фоматы не могу - не дает система. О 4000 значений знаю и не знаю как бороться. Через Эксел2007 тоже пробовали - нет результативности. Официальный представитель МС в Москве отказался от помощи. Уже ищу путь - КАК ОЧИСТИТЬ ФОРМАТЫ В ЭКСЕЛЕ?

Спасибо

[Шпец Докапыч]

Спасибо за ответ...
Это предупреждение повторяется при увеличении количества форматов и оно знакомо мне. Но, бороться ведь можно!!

Пожалуйста. Вы правы, даже нужно!! Есть проблема – реши её. © м-ф "Роботы".
Моё предложение также отчасти будет носить экспериментальный характер.

- диаграмм нет;

Это уже плюс, значит их глюки с клонированием шрифтов отметаются.

- листы идентичны по структуре (но тоже много самостоятельных форматов)

Если у нас всё же есть какая-то структура, то есть почва для замены статических форматов на форматы, переназначаемые макросом.

- стандартный - "Тахома", шрифт в ячейках в 10% случаях Тахома жирный или выделение подчёркивание.

Сдаётся мне, лучше сделать стандартным тот шрифт, которого больше всего. Подчёркивание отнесём к следующему пункту.

- много единичных форматирований "цветом и сеткой"

Не знаю, входит ли условное_форматирование в общий счёт форматов, но проверить (создать 4000 различных форматов!!!) мне проблематично. Но этот ход (заменить статичный формат на УФ) имеем ввиду.

- стандартное форматирование дат, чисел, процентов по длине значения.

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

- Уничтожить, сократить, уменьшить фоматы не могу - не дает система

Здесь надо последовательно. Счёт шрифтом у нас идёт в общем зачёте для книги, поэтому можно её раскусить, переместив часть листов (ПКМ по ярлычку -> переместить/скопировать...) в новую книгу (в окне "Переместить или скопировать", выбираем в книгу: (новая книга); галочку на "Создавать копию" не ставим). Далее, на нейтральной территории, так сказать, почистив от лишних форматов, тем же способом возвратим облегченные листы обратно.

Через Эксел2007 тоже пробовали - нет результативности.

Здесь тоже надо поступать правильно. Если Вы открываете в Excel 2007 файл 2003-го в режиме совместимости, то получаете все ограничения предыдущей версии: кол-во столбцов, три УФ на ячейку и т.д., в т.ч. предел различного форматирования. Попробуйте так:

• открыть заполненную 2003-ю книгу;
• открыть новую 2007-ю книгу;
• выделить все ячейки на листе 2003-й (клик по уголку), скопировать и вставить в 2007-ю.

Уже ищу путь - КАК ОЧИСТИТЬ ФОРМАТЫ В ЭКСЕЛЕ?

Об этом я рассказал в первую очередь. Если что неясно - рассмотрим в деталях, конкретизируйте.

[ViktorHo]

Огромное спасибо  за выделенное время и тем более - новые для меня методики - принимаю к исполнению.

Прошу меня понять, что все начинаю тестировать на копии оригинала книги и это займет не один вечер в процедуре очистки и устранения 3990 форматов - это всего 1 год работы с книгой. Я немного выпаду на время тестирования и буду просить вас потом ответить по моим результатам. Ещё раз огромное спасибо и приступаю к экспериментам.

[Шпец Докапыч]

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

Пожалуйста.
Предлагаю выложить на время вашего эксперимента хоть один листочек (лучше самый заковыристый) на форуме в качестве примера. Вдруг всё окажется намного проще?

[ViktorHo]

Извините,- нет возможности, а как требуется ...
Будем работать и обязуюсь обязательно сообщить
результат - мне надо время, дабы сделать все  без
ошибок и чётко.

Спасибо вам за консультацию.

[Шпец Докапыч]

Извините,- нет возможности, а как требуется ...

Да ничего. Непонятно, "как требуется" – это вопрос?

[ViktorHo]

Добрый день,

кас:
.... открытие в Excel 2007 файл 2003-го в режиме совместимости, то получаете все ограничения предыдущей версии: кол-во столбцов, три УФ на ячейку и т.д., в т.ч. предел различного форматирования. Попробуйте так: * открыть заполненную 2003-ю книгу; * открыть новую 2007-ю книгу;  * выделить все ячейки на листе 2003-й (клик по уголку), скопировать и вставить в 2007-ю.


возникла проблема: все листы книги имеют взаимосвязи, есть листы отчётности, которые собирают данные с других листов и т.д. - при копировании в Экс2007 нет проблем как указано в вашей методике, сохранение тоже как указано и это нормально ... а вот при открытии новой или вновь созданной книги в Экс2007 пропали некоторые связи - одни есть, а иные пропали. Подскажите с чем это м.б. связано?

[Шпец Докапыч]

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

Попробуйте воспользоваться советом Василия Алибабаевича: совет.

[ViktorHo]

Утро доброе!

   Доброе!!!!

после борьбы посредством жесточайших чисток форматов методом "Эксэл-2003/2007/2003" руками блондинки, как вы были правы,- и восстановления утраченных макросовских связей и т.п. и т.д.,- был применён метод по вашему указанию на исходной копии файла:
                 Василий Алибабаевич   
                 Re: Глюки Excel 2007
                 Ответ #3 :
                 28 Сентябрь 2008, 11:04:33
                 "Так это не только 2007 теряет связи, у меня и в 2003-м такое было, но очень
редко, как правило решалось один раз "указыванием" экселю той же связи, но
ещё раз. Формулы же переписывать не надо: правка - связи - изменить; и
указываешь тот же файл."

Без применения Экс-2007. После перезаписи книги по изменению связи в тот же самый файл и её открытия (боюсь сказать - да бы не сглазить), всё открывается стабильно, многократно и продолжает Эксел разрешать изменять и вводить форматирование. После тестирования этого нового файла в течение недели обязательно отпишу Вам о результатах серийной работы над файлом.

Вы просто помогли устранить это отрицательное событие Эксел. Спасибо ещё раз Вам и Али Бабаевичу!   

[Димычч]

Я не раз сталкивался с данной проблемой в экселе2003. Связывался с поддержкой микрософта. По-умолчанию ответ таков" "Это является особенностью данного продукта" и "создайте новую книгу, скопируйте данные из старой книги и вставьте их в новую как значения. Отформатируйте, применяя единые стили". Ответ смешной, что тут говорить... После нескольких повторных звонков мне выслали макрос, который удаляет неиспользуемые стили книги. Толку от этой процедуры - ноль. В конце концов, я данную проблему стал решать для себя немного иначе. Пока эффект есть.
1. Открываем книгу в офисе 2007 (если 2003-й уже совсем ничего не позволяет делать с файлом)
2. Проверяем, сколько разных присвоенных, но неиспользуемых имён есть в книге. В некоторых файлах их оказалось до 2,5 тысяч. Просто когда то давно в мою книгу был вставлен лист из чужой книги, где была такая беда. Удаление 2,5 тыс имён в офисе2003 стандартным способом заняло бы очень много времени. Здесь же достаточно выделить все и удалить одним нажатием. Но избавиться от всех лишних имён мне так и не удаётся. В какой то момент при копировании листа всё равно появляется глупый запрос "данный лист уже имеет имя "svlknkln" Переименовать?". И это при том, что список имён в книге пуст. Может кто знает как это побороть?
3. Самый муторный этап. Нужно зайти на каждый лист и почистить от форматов всё неиспользуемое таблицей пространство. Уверен, что гуру с данного форума легко напишут макрос для этого, но я не умею. Думаю, что одной из причин засорения форматами является форматирование целых строк и столбцов. Например, в таблице надо покрасить или изменить шрифт в одной строке. Можно для этого выделить диапазон, а можно выбрать более быстрый способ - одним щелчком выделить сразу всю строку. При этом изменяется формат абсолютно всех ячеек этой строки. А экселю это и надо чтобы приблизиться к заветной цифре 4000.... Для чистки я нажимаю Контрл+Вправо или Контрл+Вниз чтобя добраться до края листа, затем выделяю весь диапазон до правого и соответственно нижнего края таблицы. Выбираю "главная-очистить форматы".
4. Выделяю все листы и переношу их в новую книгу, пересохраняю в старом формате. Связи при этом сохраняются. При всей простоте данной операции для экселя это нечно большее. Например, размер сложного файла при этом может сократиться в десятки раз (я не преувеличиваю!).

[ViktorHo]

Огромное спасибо!!!

Уважаемый Димыч, огромное спасибо за информацию. Очень и очень со многими Вашими словами и действиями полное согласие - ну и глюконат!! Беру на использование такой метод тоже!! У меня пока работает книга, я молюсь на ее работоспособность и не более, но ... жду снова предупреждения. Одновременно собираю методики воздействия на 4000, как магическое число. Вопрос: у Вас были связи-макросы между листами и книгами?? Как при переносе?,-  были потеряны или нет таковые связи?!?

[Димычч]

Как я и написал в последнем абзаце, связи сохраняются, а их у меня много. Макросы храню в личной книге. Фактически получаем клон старой книги, но без некоторого старого мусора, который запрятон в недрах файла. Кстати, когда я общался со "специалистом" микрософт, я выслал ему два одинаковых абсолютно пустых файла с чистыми листами без форматов. Но вот размер их отличался в 20 раз (10 и 200 Кб). Один создан как новый файл, а второй путём копирования чистого листа из старого файла. Я просил пояснить, отчего это происходит, он естественно не смог, сославшись на "особенности продукта". Похоже что и форматы эксель как-то там у себя в уме откладывает, прибавляя к общему их количеству, при этом на листе лни могут быть не видны. Доказательством тому может быть такая ситуация: возникло первое сообщение о превышении количества форматов. Я вижу, что таблицы действительно очень разнородны по цвету и шрифтам. Я выделяю их все, выставляю им один шрифт, цвет и тд. То есть делаю логичные действия к приведению форматов к одному виду. И что же? На следующий день хочу увеличить шрифт заголовка и снова вижу любимое сообщение...

[ViktorHo]

Уважаемый. Димыч!

под каждым Вашим словом подписываюсь - куда не целуй этот замечательный продукт - кругом одна ж..а - утром включаешь книгу и тоже предупреждение. Чистишь ручками - чистишь, а в итоге рост количества форматов обеспечен. И это уже не 3й год такое. Уже изворотливость смены одной книги на другую - обрывание и перезаписи. Глюконат софта. И при этом не дано право перейти на 2007й или иной открытый софт. Очень жалко себя, что попались на такую разработку.

Еще раз спасибо за ваш метод - законспектирован в картинках и буду испытывать в скором будущем - уже жду картинки - просто жду.