Розмір. Вага. Гучність. Неважливо, як ми це називаємо. Коли файл занадто важкий, він стає занадто великим, це проблема. Ми не можемо надіслати його електронною поштою, завантаження та збереження займає багато часу. Це особливо дратує при створенні резервних копій, оскільки це займає кілька секунд, коли наш мозок думав про продуктивність.

Історія

Днями мій друг Квіке сказав мені, що отримав файл із надмірним розміром. Коли його творець створив його свого часу, він зайняв менше 1 Мб, але зараз він зайняв майже 50 Мб. Що сталося? Коментуючи по телефону, ми націлюємося на деяких загальних підозрюваних:

  • Чи є дані в моделі даних?
  • Чи багато аркушів (наприклад, порожні текстові поля) на аркушах?

І як це робиться? Починаємо з кирки та лопати.

Інформація про модель даних

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

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

Хоча модель даних є дуже ефективною, вона може зберігати багато інформації, тому вона може зайняти багато місця, створюючи дуже великі файли. Щоб з’ясувати, чи є в моделі даних інформація, мені здається, що ми можемо зробити дві речі:

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

зменште
Вставити зведену таблицю

2. Безпосередній доступ до адміністратора моделі даних. Це можна зробити у версії 2016 року на вкладці Дані в групі «Інструменти даних». У попередніх версіях через вкладку Power Pivot, яка в деяких версіях є надбудовою, яку потрібно встановити.

Вкладка Power Pivot Вкладка Дані

У нашому випадку файл не мав моделі даних, тому ми перейшли до наступного підозрюваного у списку.

Приховані (або видимі) форми

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

Ну, є кілька способів. Один - "на око". Звичайний значок миші в електронній таблиці - це хрестик. Якщо у нас є текстове поле, і ми кладемо на нього курсор, ми отримаємо піктограму, яка виглядає як велике Я (як у Word). Нам краще це побачити на малюнку.

Це те, що ви зазвичай бачите в Excel:

Звичайна піктограма в Excel

Ось що слід подивитися, чи є "зловмисники":

Піктограма, коли є текстове поле

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

Видиме текстове поле

Якщо у нас є версія Excel пізніше 2007 року (у нас вона є, так?), Тоді ми можемо зробити наші об’єкти видимими іншим способом: через панель виділення. Це можна знайти у версії 2016 року в розділі «Розмітка сторінки» на стрічці на панелі «упорядкувати». У попередніх версіях ми повинні вибрати об'єкт, а кнопка для активації панелі виділення знаходиться на вкладці "Інструменти малювання" або "Графічні інструменти". Панель з’являється праворуч, і там ми маємо список усіх об’єктів, що знаходяться на аркуші.

Якщо щось є, воно буде там.

Панель виділення - панель виділення Excel 2016 у версіях до 2016 року

Як бачите, за допомогою декількох copy-paste я створив понад 33 000 копій того самого порожнього текстового поля. Генерація кадрів є експоненціальною, тому вона швидко досягає непомірних величин, якщо ми цього не усвідомлюємо. Зараз проблема полягає в тому, щоб видалити їх усі. Ви б дали ключ видалення 33 000 разів? Я не. Тому я знаю VBA. Я б поставив код більш-менш таким:

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

Однак це також не було проблемою у файлі нашого друга. Панель вибору була порожньою, тому я продовжував шукати.

Умовні формати, формати комірок, кольори ...

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

І мені було цікаво, як можна дізнатися, що саме займає місце на диску ... І звичайно, рішення було.

Перегляньте нутрощі файлу .xlsx

З тих пір, як Microsoft прийняла стандарт .xlsx, вона перетворила файли Excel на файли зі структурою xml. Це означає, що його можна читати по-різному, і що багато типів програм розумітимуть структуру. Цікаво, що власний xml-імпортер Excel видає нам помилку. І чому це?

Просто тому, що Excel безпосередньо не зберігає файл у форматі xml, а також стискає його у форматі zip, щоб він займав менше місця. Отже ... Що, якщо ми спробуємо змінити розширення файлу і відкрити його за допомогою компресора?

Попередні кроки для зміни розширення файлу

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

Перше, що потрібно - це відкрити вікно Провідника Windows. Це можна зробити швидко, натиснувши клавіші Windows + E.

Комбінація для відкриття Провідника Windows

У вікні "Мій комп'ютер" відкриється вікно провідника Windows. Там ми вибираємо панель «Перегляд» і праворуч від цілого маємо кнопку Параметри, де натискаємо на «Змінити папку та параметри пошуку».

Змінити параметри папки

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

Показати розширення файлу.

Тепер так, ми змінюємо розширення файлу

Після цього ми переходимо до нашого файлу .xlsx і перейменовуємо його. Ми замінюємо .xlsx на .zip і приймаємо всі повідомлення Windows.

Змінити розширення файлу.

Як бачите, я спочатку зробив копію, щоб зберегти оригінал .xlsx (ну, у нашому випадку це був .xlsm, оскільки він макроси). І тепер ми можемо відкрити новий файл .zip у будь-якому компресорі. У моєму випадку у мене встановлений WinRar, але кожен, хто відкриває .zip, є дійсним, навіть сам Провідник Windows.

Відкривши його, ви побачите структуру папок та файлів. Виявляється, наш .xlsx файл - це насправді купа стиснених XML-файлів та папок. Ми можемо переміщатися по папках, щоб побачити, що займає місце ... У нашому конкретному випадку я ввів папку 'xl', а всередині папки 'робочі аркуші'. Сортуючи за розміром, ми вже знаємо, який аркуш винен:

Перегляд файлів Excel через WinRar.

І ось вони у нас є! Нашими винуватцями є аркуші 5 і 4 нашого файлу.

Розуміння, до якого аркуша це стосується ...

Що трапляється, так це те, що в нашому файлі аркуші не називаються 'sheet5' або 'sheet4'. Вони мають такі назви, як "Обкладинка", "Індекс", "Основні дані" ...

Перший підхід - звернутися до нашого редактора VBA, оскільки ми знаємо, що на панелі проекту вони вводять наші імена в дужки, але зовні вони все ще називаються `` Аркуш 1 '', `` Аркуш 2 '' тощо.

Структура файлу .xlsx видно на панелі проекту VBE.

Існує трохи цензури, оскільки файл не є моїм, і я не маю явного дозволу на його використання. Я зберіг лише загальні назви, тому що це досить добре зрозуміло. Назви в дужках - це те, що ми бачимо на вкладках наших аркушів. І 'Аркуш 1', 'Аркуш 2' тощо. - внутрішня назва листя. Власне, це властивість під назвою «кодова назва», що дозволяє нам певною легкістю працювати з листами. Але це не назва, якій відповідає наша номенклатура в архіві zip. Я спробую це трохи прояснити.

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

У цьому випадку індекс дорівнює 1, а кодова назва - Аркуш1. Але вони не повинні збігатися. Насправді в моєму файлі аркуш під назвою "Обкладинка" має індекс 2 і кодову назву "Аркуш5".

Ну, число, що стоїть за "аркушем" в іменах файлів нашого zip, є індексом кожного аркуша.

Вирішення справи: зменшення розміру файлу

Нарешті, знаючи, на якому аркуші нам доведеться діяти, і, відкинувши решту підозрюваних, я просто перейшов до останнього рядка з даними, виділив весь наступний рядок і натиснув Ctrl + Shift + ↓, щоб виділити всі клітинки до кінця з файлу. Потім клацніть правою кнопкою миші та видаліть. Таким же чином я перейшов до останнього стовпця з даними і зробив те саме, вибравши наступний стовпець, а потім Ctrl + Shift + →, щоб виділити всі стовпці до кінця. Знову клацніть правою кнопкою миші та видаліть.

Видалити комірки.

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

Він важить менше ...

Що сталося?

Як я вже обговорював раніше, аркуші застосовували багато форматів. Затінені, смугасті, кольори ...

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

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

Отже, якщо ви зайшли так далеко, я можу сказати вам не перефарбовувати аркуші Excel, якщо ви не хочете знайти собі 40 мегабайт файлу, не знаючи, чому.