Рішення помилки Microsoft Excel «Занадто багато різних форматів осередків»

Однією з проблем, з якою зустрічаються користувачі при роботі з таблицями в Microsoft Excel, є помилка «Занадто багато різних форматів осередків». Особливо вона поширена при роботі з таблицями з розширенням XLS. Давайте розберемося в суті цієї проблеми та з'ясуємо, якими способами її можна усунути.

Читайте також: Як зменшити розмір файлу в Ексель

усунення помилки

Щоб зрозуміти, як вирішити проблему, потрібно знати її суть. Справа в тому, що файли Ексель з розширенням XLSX підтримують одночасну роботу з 64000 форматами в документі, а з розширенням XLS - тільки 4000. При перевищенні зазначених лімітів і виникає дана помилка. Під форматом розуміється комбінація різних елементів форматування:

• Межі;
• заливка;
• шрифт;
• Гістограми і т.д.

Тому в одній комірці може одночасно бути кілька форматів. Якщо в документі застосовується зайве форматування, то це якраз і може викликати помилку. Давайте тепер з'ясуємо, як усунути цю проблему.

Спосіб 1: збереження файлу з розширенням XLSX

Як вже було сказано вище, документи з розширенням XLS підтримують одночасну роботу тільки з 4000 одиниць форматів. Цим і пояснюється той факт, що найчастіше дана помилка зустрічається саме в них. Конвертація книги в більш сучасний документ XLSX, який підтримує роботу одночасно з 64000 елементами форматування, дозволить в 16 разів більше використовувати ці елементи перш, ніж виникне зазначена вище помилка.

1. Переходимо у вкладку «Файл».
2. Далі в лівому вертикальному меню натискаємо по пункту «Зберегти як».
3. Запускається вікно збереження файлу. При бажанні його можна зберегти в іншому місці, а не в тому, де розташовується документ-початківець, перейшовши в іншу директорію жорсткого диска. Також в поле «Ім'я файлу» можна при бажанні змінити його найменування. Але це не обов'язкові умови. Ці настройки можна залишити за замовчуванням. Головним завданням є в полі «Тип файлу» змінити значення «Книга Excel 97-2003» на «Книга Excel». З цією метою натискаємо по даному полю і вибираємо відповідне найменування з списку. Після виконання зазначеної процедури тиснемо на кнопку «Зберегти».

Тепер документ буде збережений з розширенням XLSX, що дозволить працювати з великим в 16 разів кількістю форматів одночасно, ніж це було при роботі з файлом з розширенням XLS. У переважній більшості випадків даний спосіб дозволяє усунути досліджувану нами помилку.

Спосіб 2: очищення форматів в порожніх рядках

Але все-таки бувають випадки, коли користувач працює саме з розширенням XLSX, але у нього все одно виникає дана помилка. Це пов'язано з тим, що при роботі з документом був перевищений рубіж в 64000 форматів. Крім того, з певних причин не виключено, коли потрібно зберегти файл саме з розширенням XLS, а не XLSX, так як з першим, наприклад, вміють працювати більшу кількість сторонніх програм. У цих випадках потрібно шукати інший вихід з ситуації, що склалася.

Часто багато користувачів форматують місце під таблицю з запасом, щоб в майбутньому не витрачати час на цю процедуру в разі розширення таблиці. Але це абсолютно неправильний підхід. Через це значно збільшується розмір файлу, сповільнюється робота з ним, до того ж, подібні дії можуть привести до помилки, яку ми обговорюємо в цій темі. Тому, від таких надмірностей слід позбутися.

1. Перш за все, нам потрібно виділити всю область під таблицею, починаючи з першого рядка, в якій немає даних. Для цього клацаємо лівою кнопкою миші по числовому найменуванню цього рядка на вертикальній панелі координат. Виробляється виділення всього рядка. Застосовуємо натискання комбінації клавіш Ctrl + Shift + стрілка вниз. Виділяється весь діапазон документа нижче таблиці.
2. Потім переміщаємося у вкладку «Головна» і тиснемо на іконку на стрічці «Очистити», яка розташовується в блоці інструментів «Редагування». Відкривається список, в якому вибираємо позицію «Очистити формати».
3. Після цього дії виділений діапазон буде очищений.

Аналогічним чином можна проводити очищення в осередках праворуч від таблиці.

1. Клацають по найменуванню першого незаповнення даними стовпчика в панелі координат. Відбувається виділення його до самого низу. Потім виробляємо набір комбінації клавіш Ctrl + Shift + стрілка вправо. При цьому виділяється весь діапазон документа, розташований праворуч від таблиці.
2. Потім, як і в попередньому випадку, тиснемо на іконку «Очистити», а в випадаючому меню вибираємо варіант «Очистити формати».
3. Після цього буде проведена очистка у всіх осередках праворуч від таблиці.

Подібну процедуру при виникненні помилки, про яку ми говоримо в даному уроці, не зайвим буде провести навіть в тому випадку, якщо на перший погляд здається, що діапазони нижче і правіше таблиці взагалі не відформатовані. Справа в тому, що в них можуть знаходитися «приховані» формати. Наприклад, тексту або чисел в осередку може і не бути, але в ній встановлений формат напівжирного шрифту і т.д. Тому, не полінуйтеся, в разі виникнення помилки, провести цю процедуру навіть над зовні порожніми діапазонами. Також не потрібно забувати про можливі приховані стовпці і рядки.

Спосіб 3: видалення форматів всередині таблиці

Якщо попередній варіант не допоміг вирішити проблему, то варто звернути увагу на зайве форматування всередині самої таблиці. Деякі користувачі роблять форматування в таблиці навіть там, де воно не несе ніякої додаткової інформації. Вони думають, що роблять таблицю красивіше, але на ділі досить часто з боку подібне оформлення виглядає досить без смаку. Ще гірше, якщо зазначені речі призводять до гальмування програми або до помилки, яку ми описуємо. В цьому випадку слід залишити в таблиці тільки дійсно значуще форматування.

1. У тих діапазонах, в яких форматування можна прибрати повністю, і це не вплине на інформативність таблиці, виконуємо процедуру за тим же алгоритмом, що був описаний в попередньому способі. Спочатку виділяємо діапазон в таблиці, в якому слід провести очищення. Якщо таблиця дуже велика, то дану процедуру буде зручніше робити, скориставшись комбінаціями кнопок Ctrl + Shift + стрілка вправо (вліво, вгору, вниз). Якщо ви при цьому виділіть осередок всередині таблиці, то за допомогою даних клавіш виділення буде проводитися тільки всередині неї, а не до кінця листа, як в попередньому способі.

   Тиснемо на вже знайому нам кнопку «Очистити» у вкладці «Головна». У випадаючому списку вибираємо варіант «Очистити формати».

2. Виділений діапазон таблиці буде повністю очищений.
3. Єдине, що потрібно потім буде зробити - це встановити межі в очищеному фрагменті, якщо вони присутні в іншому табличному масиві.

Але для деяких областей таблиці подібний варіант не підійде. Наприклад, в певному діапазоні можна видалити заливку, але при цьому слід залишити формат дати, інакше дані будуть відображатися некоректно, кордони і деякі інші елементи. Той же варіант дій, про який ми говорили вище, повністю прибирає форматування.

Але існує вихід і в цьому випадку, правда, він більш трудомісткий. За таких обставин користувачеві доведеться виділяти кожен блок однорідно відформатованих осередків і вручну прибирати той формат, без, якого можна обійтися.

Звичайно, це довгий і копітка заняття, якщо таблиця занадто велика. Тому краще відразу при складанні документа не зловживати «красивостями», щоб потім не виникло проблем, на вирішення яких доведеться витрачати багато часу.

Спосіб 4: видалення умовного форматування

Умовне форматування - дуже зручний інструмент візуалізації даних, але надмірне його застосування теж може викликати досліджувану нами помилку. Тому потрібно переглянути список правил умовного форматування, що застосовується на даному аркуші, і видалити з нього позиції, без яких можна обійтися.

1. Розташовуючись у вкладці «Головна», клацають по кнопці «Умовне форматування», яка знаходиться в блоці «Стилі». В меню, яке відкриється після цього дії, вибираємо пункт «Управління правилами».
2. Слідом за цим запускається віконце управління правилами, в якому розташовується список елементів умовного форматування.
3. За замовчуванням в списку розташовані тільки елементи виділеного фрагмента. Для того, щоб відобразити всі правила на аркуші, переставляємо перемикач в поле «Показати правила форматування для" в позицію «Цей лист». Після цього будуть відображені всі правила поточного листа.
4. Потім виділяємо правило, без якого можна обійтися, і тиснемо на кнопку «Видалити правило».
5. Таким способом видаляємо ті правила, які не грають важливої ​​ролі в візуальному сприйнятті даних. Після того, як процедура завершена, тиснемо на кнопку «OK» внизу віконця Диспетчера правил.

Якщо потрібно повністю видалити умовне форматування з конкретного діапазону, то зробити це ще простіше.

1. Виділяємо діапазон комірок, в якому плануємо провести видалення.
2. Клацаємо по кнопці «Умовне форматування» в блоці «Стилі» у вкладці «Головна». У списку вибираємо варіант «Видалити правила». Далі відкривається ще один список. У ньому вибираємо пункт «Видалити правила з виділених осередків».
3. Після цього всі правила в виділеному діапазоні будуть видалені.

Якщо ви хочете повністю видалити умовне форматування, то в останньому списку меню потрібно вибрати варіант «Видалити правила з усього листа».

Спосіб 5: видалення призначених для користувача стилів

Крім того, дана проблема може виникати через використання великої кількості призначених для користувача стилів. Причому вони можуть з'явитися, як результат імпорту або копіювання з інших книг.

1. Усувається дана проблема в такий спосіб. Переходимо у вкладку «Головна». На стрічці в блоці інструментів «Стилі» клацаємо по групі «Стилі комірок».
2. Відкривається меню стилів. Тут представлені різні стилі оформлення осередків, тобто, по суті, зафіксовані поєднання декількох форматів. У самому верху списку розташований блок «Призначені для користувача». Якраз дані стилі не є споконвічно вбудованими в Excel, а є продуктом дій користувача. При виникненні помилки, усунення якої ми вивчаємо, рекомендується їх видалити.
3. Проблема полягає в тому, що не існує вбудованого інструменту масового видалення стилів, тому доведеться кожного з них видаляти окремо. Наводимо курсор на конкретний стиль з групи «Призначені для користувача». Кількома по ньому правою кнопкою миші і в контекстному меню вибираємо варіант «Видалити ...».
4. Видаляємо таким способом кожен стиль з блоку «Призначені для користувача», поки не залишаться тільки вбудовані стилі Excel.

Спосіб 6: видалення призначених для користувача форматів

Дуже схожою процедурою на видалення стилів є видалення призначених для користувача форматів. Тобто, ми будемо видаляти ті елементи, які не є вбудованими за замовчуванням в Excel, а впроваджені користувачем, або були вбудовані в документ іншим способом.

1. Перш за все, нам потрібно буде відкрити вікно форматування. Найпоширеніший спосіб це зробити - натиснути правою кнопкою миші по будь-якого місця в документі і з контекстного меню вибрати варіант «Формат ячеек ...».

   Також можна, перебуваючи у вкладці «Головна», клікнути на кнопку «Формат» в блоці «Осередки» на стрічці. У запустити меню вибираємо пункт «Формат ячеек ...».

   Ще один варіант виклику потрібного нам вікна - це набір клавіш Ctrl + 1 на клавіатурі.

2. Після виконання будь-якого з дій, які були описані вище, запуститься вікно форматування. Переходимо у вкладку «Число». У блоці параметрів «Числові формати» виробляємо установку перемикача в позицію «(всі формати)». У правій частині даного вікна розташовується поле, в якому знаходиться перелік всіх типів елементів, які використовуються в даному документі.

   Виділяємо курсором кожен з них. Переходити до наступного найменуванню найзручніше за допомогою клавіші «Вниз» на клавіатурі в блоці навігації. Якщо елемент є вбудованим, то кнопка «Видалити» під переліком буде неактивною.

3. Як тільки буде виділено доданий призначений для користувача елемент, кнопка «Видалити» стане активною. Тиснемо на неї. Таким же чином видаляємо всі найменування призначеного для користувача форматування в переліку.
4. Після завершення процедури обов'язково тиснемо на кнопку «OK» внизу вікна.

Спосіб 7: видалення непотрібних листів

Ми описували дії щодо вирішення проблеми тільки в межах одного аркуша. Але не варто забувати, що точно такі ж маніпуляції потрібно проробити і з усіма іншими заповненими даними листами книги.

Крім того, непотрібні листи або листи, де інформація дублюється, краще взагалі видалити. Робиться це досить просто.

1. Натискаємо правою кнопкою миші по ярлику листа, який слід прибрати, розташованому над рядком стану. Далі в меню вибираємо пункт «Видалити ...».
2. Після цього відкривається діалогове вікно, яке вимагає підтвердження видалення ярлика. Кількома в ньому по кнопці «Видалити».
3. Слідом за цим обраний ярлик буде видалений з документа, а, отже, і всі елементи форматування на ньому.

Якщо потрібно видалити кілька послідовно розташованих ярликів, то натискаємо на першу з них лівою кнопкою миші, а потім натискаємо за останнім, але тільки при цьому затиснувши клавішу Shift. Будуть виділені всі ярлики, розташовані між даними елементами. Далі процедуру видалення проводимо за тим же алгоритмом, який був описаний вище.

Але існують також приховані листи, і як раз на них може перебувати досить велика кількість різних відформатованих елементів. Щоб прибрати надмірне форматування на цих аркушах або взагалі видалити їх, потрібно відразу відобразити ярлики.

1. Кількома по будь-якому ярлику і в контекстному меню вибираємо пункт «Показати».
2. Відкривається список прихованих аркушів. Вибираємо найменування прихованого листа і натискаємо на кнопку «OK». Після цього він буде відображений на панелі.

Таку операцію проробляємо з усіма прихованими листами. Потім дивимося, що з ними робити: повністю видаляти або очищати від надлишкового форматування, якщо інформація на них важлива.

Але крім цього існують ще й так звані суперскритие листи, яких в списку звичайних прихованих аркушів ви не знайдете. Їх можна побачити і відобразити на панелі тільки через редактор VBA.

1. Щоб запустити редактор VBA (редактор макросів), тиснемо поєднання гарячих клавіш Alt + F11. У блоці «Project» виділяємо найменування листа. Тут відображаються, як звичайні видимі листи, так приховані і суперскритие. У нижній області «Properties» дивимося значенням параметра «Visible». Якщо там встановлено значення «2-xlSheetVeryHidden», то це і є суперскритий лист.
2. Кількома по даному параметру і в списку, вибираємо найменування «-1-xlSheetVisible». Потім клацаємо по стандартній кнопці закриття вікна.

Після даної дії обраний лист перестане бути суперскритим і його ярлик відобразиться на панелі. Далі з ним можна буде проводити або процедуру очищення, або видалення.

урок: Что делать, если пропали листы в Экселе

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

• ← Заголовок таблиці на кожній сторінці в Microsoft Excel
• Прибираємо нумерацію сторінок в Microsoft Excel →