8 способів зменшити розмір файлу Excel (це дійсно працює)

Відомо, що книги Excel стають болісно повільними та важкими, оскільки до них додається більше даних/формул/зображень.

Іноді легко позбутися зайвої фляби і контролювати розмір файлу Excel, а іноді це не так.

А оскільки Excel-це не веб-інструмент, більшість із нас працює та співпрацює, надсилаючи файли Excel електронною поштою. І великий файл Excel вагою 50 МБ може бути важко поділитися з колегами/клієнтами.

В моїй останній роботі ми використовували клієнт електронної пошти Lotus Notes, який повільно равликів, і надсилали важкі файли Excel через це катування.

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

Примітка: Вплив цих методів на зменшення розміру файлу Excel залежатиме від ваших даних. Для деяких це може допомогти зменшити розмір на 50%, а для когось це може призвести лише до зменшення розміру на 5%.

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

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

Для кожної поради я провів певне тестування і додав знімки, щоб показати вам вплив на розмір файлу. Отримані результати будуть відрізнятися залежно від ваших даних та файлів.

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

Видаліть непотрібні робочі аркуші/дані

Найкращі рішення часто є найпростішими.

І цей прийом є радше здоровим глуздом, а не будь -яким майстром Excel.

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

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

Результати тесту:

Я створив файл Excel з 10 аркушами, де кожен аркуш містить 2,6 мільйона точок даних (26 стовпців із 100 000 точок даних у кожному стовпці).

Отриманий розмір цієї книги становить 143 МБ (і для збереження та відкриття знадобиться досить багато часу).

Тепер, коли я видалив п’ять аркушів, розмір зменшився вдвічі (ну).

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

Перетворити у двійковий формат (XLSB)

Чи знаєте ви, що лише збереження файлу Excel у форматі XLSB може зменшити розмір файлу?

Так, це правда.

Все, що вам потрібно зробити, це зберегти книгу у форматі .xlsb.

Нижче наведено кроки для цього.

1. Перейдіть на вкладку Файл.
2. Натисніть Зберегти як.
3. Натисніть Огляд.
4. У діалоговому вікні Зберегти як змініть тип файлу на двійкову книгу Excel (.xlsb)

Результати тесту

У мене була книга Excel, яка займала 14,6 МБ місця.

Після перетворення того самого файлу у формат XLSB мені вдалося зменшити розмір до 10,4 МБ (зменшення розміру ~ 28%).

Що таке XLSB?

Коли ви зберігаєте файл Excel (.xlsx або .xlsm), він зберігається у форматі XML.

Наприклад, якщо я зміню розширення файлу XLSX на ZIP, а потім відкрию його, я отримаю це.

І якщо я далі перейду до папки "xl", а потім перейду до папки робочого аркуша, я покажу мені аркуші у цій книзі (і ці аркуші зберігаються як документ XML).

Тепер давайте зробимо те ж саме з файлом XLSB.

Тож спочатку я змінив розширення файлу на ZIP, потім відкрив його та знайшов аркуш sheet1. Ось що ви отримаєте:

Ви помітите, що і XLSX, і XLSB зберігають аркуш у іншому форматі.

XLSX/XLSM зберігає його як файл XML, а XLSB - як двійковий (.bin) файл.

І не надто технічно, XML -файли мають великий розмір у порівнянні з двійковим файлом.

Тому, коли ви зберігаєте книгу Excel у форматі XLSB, розмір файлу зменшується. У нашому прикладі вище розмір зменшився на ~ 30%.

XLSB проти XLSX/XLSM

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

Переваги формату XLSB:

1. Це зменшує розмір файлу Excel. Це може бути корисним, якщо у вас є великі файли і ви хочете поділитися ними по електронній пошті. Ви можете швидко зменшити розмір, зберігаючи його в XLSB.
2. Файли XLSB відкриваються та зберігаються швидше, ніж файли XML. Виходячи з обмежених тестів, які я зробив, файли XLSB відкривалися і заощаджували на 25% -50% швидше. Ця різниця помітна лише у великих файлах Excel. З меншими файлами XLSB і XLSX є швидкими.

Незважаючи на те, що XLSB виглядає чудово, є кілька причин, чому ви повинні дотримуватися використання файлів XLSX/XLSM:

1. У форматі XLSB у вас немає можливості дізнатися, чи є у нього макрос чи ні. Це робить його більш ризикованим, оскільки хтось може використовувати його для виконання шкідливого коду.
2. Файли XLSB кодуються у власному форматі кошика, тоді як XML-це формат файлу з відкритим вихідним кодом. Це означає, що якщо ви використовуєте сторонні інструменти з Excel, краще використовувати формат XML.
3. Power Query не може читати дані з файлу XLSB. Тому, якщо ви використовуєте Power Query, краще зберігати свої дані у форматах XLSX/XLSM.

Як правило, якщо розмір вашого файлу невеликий (менше 5 МБ), краще дотримуватися форматів XLSX/XLSM.

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

Ось хороша стаття про розуміння формату XLSB.

Видаліть непотрібні формули

Занадто багато формул збільшать розмір файлу вашої книги.

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

Ось кроки для швидкого перетворення формул у значення в Excel:

1. Виберіть весь аркуш/набір даних.
2. Натисніть кнопку F5 ключ.
3. У діалоговому вікні «Перейти» натисніть «Спеціальне».
4. У діалоговому вікні Перейти до спеціального виберіть Формули.
5. Натисніть OK.

Наведеними вище кроками буде вибрано всі клітинки, у яких є формула.

Тепер скористайтеся наведеними нижче кроками, щоб перетворити ці формули у значення:

1. Скопіювати виділені клітинки (Control + C)
2. Натисніть вкладку Домашня сторінка.
3. У групі Буфер обміну натисніть кнопку Вставити.
4. Натисніть на піктограму Вставити значення.

Наведені вище кроки перетворять усі комірки з формулами у значення.

Результати тесту

Я створив файл Excel з 2,7 мільйонами точок даних (1048576*26), а потім додав три стовпці з формулами (SUM, COUNT та AVERAGE).

Потім я перетворив ці формули на значення та зберег файл з іншою назвою.

Нижче наведено результат:

Файл без формул має розмір ~ 8% менше (зауважте, що у мене було лише 3 стовпці формул).

Якщо у вас є багато формул, це може збільшити розмір.

ПРИМІТКА: Летючі формули дійсно можуть збільшити розмір вашого файлу. Як частина тесту, коли я замінив формулу SUM на летючу формулу (OFFSET), це призвело до стрибка розміру файлу (з 186 МБ до 198 МБ). Отже, якщо у вас є летючі формули, які можна замінити на енергонезалежні, або перетворити їх у значення, це допоможе вам зменшити розмір файлу.

Стиснення зображень (до і після завантаження)

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

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

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

Хорошим онлайн -інструментом для цього є TinyPNG (або ви можете скористатися Photoshop або будь -яким іншим подібним інструментом).

Це безкоштовний інструмент, який може зменшити розмір зображення до 90% і при цьому підтримувати гідну якість зображення.

Нижче наведено скріншот, де мені вдалося стиснути зображення розміром від 3,9 МБ до 331 КБ з мінімальними втратами якості.

Після того як ви завантажили ці стислі зображення в Excel, ви можете додатково стиснути їх за допомогою вбудованої опції в Excel.

Ось кроки стиснення зображення в Excel:

1. Виберіть зображення, яке потрібно стиснути.
2. Натисніть на вкладку Формат інструментів для зображень. Це контекстна вкладка, яка з'являється лише при натисканні на зображення.
3. У групі Налаштування натисніть на опцію «Стиснути зображення».
4. У діалоговому вікні Стиснути зображення:
   • Зніміть прапорець "Застосовувати лише до цього зображення", якщо ви хочете стиснути всі зображення у книзі.
   • Виберіть Електронна пошта (96 ppi) для максимального стиснення.
5. Натисніть OK.
6. Збережіть файл.

Це стисне всі зображення та допоможе вам зменшити розмір файлу книги Excel.

Результати тесту:

Я використовував опцію стиснення зображення в Excel, використовуючи одне зображення.

Нижче наведені результати:

За допомогою опції стиснення зображення розмір файлу зменшено з 445 КБ до 43 КБ (~ 90% зменшення розміру файлу).

Це також буде залежати від зображення. Зображення, яке вже стиснене, може не призвести до значного зменшення розміру.

Видаліть зведений кеш, щоб заощадити місце

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

Коли ви створюєте зведену таблицю за допомогою набору даних, Excel автоматично створює зведену кеш -пам'ять.

Зведений кеш - це об’єкт, що містить репліку джерела даних.

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

Хоча вам здається, що ви безпосередньо пов’язані з вихідними даними, насправді ви отримуєте доступ до зведеного кешу (а не вихідних даних) під час внесення змін до зведеної таблиці.

Тепер, коли ви створюєте копію набору даних, це займає трохи місця.

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

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

Тепер є два способи зменшити розмір файлу під час роботи зі зведеними таблицями.

1. Збережіть вихідні дані та видаліть зведений кеш
2. Збережіть зведений кеш і видаліть вихідні дані

Давайте розглянемо обидві ці техніки.

Збережіть вихідні дані та видаліть зведений кеш

Коли ви завершите роботу зі зведеною таблицею та збережете її, ви можете змусити Excel зберігати лише вихідні дані та отриману зведену таблицю, а не зведену кеш.

Це зробить ваш файл Excel легшим, і у вас буде спосіб відтворити кеш -пам’ять, коли вам це буде потрібно наступного разу.

Нижче наведено різницю в розмірі, коли ви зберігаєте файл Excel з і без зведеного кешу.

Ось кроки до НІ зберегти зведений кеш:

1. Виберіть будь -яку клітинку у зведеній таблиці.
2. Перейдіть на вкладку Аналіз.
3. Натисніть на значок «Параметри» зведеної таблиці.
4. У діалоговому вікні Параметри зведеної таблиці натисніть вкладку Дані.
5. У параметрах даних зведеної таблиці, зніміть прапорець "Зберегти вихідні дані у файлі". Цей параметр гарантує, що зведений кеш не зберігається під час збереження книги (і допомагає зменшити розмір файлу Excel).
6. Поставте прапорець біля опції «Оновити дані під час відкриття файлу». Цей параметр гарантує, що як тільки ви відкриєте книгу, зведена таблиця буде оновлено, а зведений кеш буде автоматично створено. Якщо ви не встановите цей прапорець, вам доведеться оновити зведену таблицю вручну, щоб створити зведений кеш.
7. Натисніть OK.

Тепер, коли ви зберігаєте файл із зведеною таблицею, ви помітите зменшений розмір файлу.

Збережіть зведений кеш і видаліть вихідні дані

Інший спосіб зменшити розмір файлу Excel - видалити вихідні дані зведеної таблиці та зберегти лише зведений кеш.

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

Нижче наведено результати, коли я зберігав файл Excel без вихідних даних.

Для цього просто видаліть дані (видаливши дані або аркуш, у якому є дані).

Якщо ви хочете повернути вихідні дані, двічі клацніть клітинку "Загальна сума" зведеної таблиці, і вона миттєво генерує весь набір даних на новому робочому аркуші.

Стиснути файл (ZIP IT)

Це те, що ви можете робити поза програмою Excel.

Коли ви заархівуєте файл Excel, ви відразу побачите зменшення розміру приблизно на 10-15%.

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

Ось кроки, щоб заархівувати файл Excel (для Windows):

1. Клацніть правою кнопкою миші файл Excel
2. Наведіть курсор на опцію Надіслати.
3. Натисніть на опцію «Стиснута (заархівована) папка».

Це створить заархівований файл для вибраної книги Excel.

Ви також помітите зменшення розміру файлу (як показано нижче).

Результати тесту

За допомогою цієї техніки мені вдалося зменшити розмір файлу книги Excel на 8%.

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

Видалити форматування даних

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

Хоча, виходячи з моїх тестів, я помітив, що це не має великого впливу.

Нижче наведено розміри двох робочих книг Excel, одна з яких містить 2,6 мільйона точок даних (на одному аркуші), а інша з тими ж даними, але до них застосовується форматування (колір комірки, рамка та курсив).

Результати тесту

Ви бачите, що різниця між цими двома файлами не така велика (хоча це робить вплив, коли у вас багато даних).

Файл із даними становить 14,6 МБ, а файл із даними та форматуванням - 14,9 МБ.

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

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

1. Виберіть весь набір даних (з якого потрібно видалити форматування).
2. Натисніть вкладку Домашня сторінка.
3. У групі Редагування натисніть «Очистити».
4. Натисніть «Очистити формати».
5. Збережіть робочу книгу.

Видалити умовне форматування

Як і звичайне форматування даних, умовне форматування також збільшує розмір файлу.

Однак, виходячи з моїх тестів, я помітив, що різниця менша (якщо у вас немає кількох правил, що застосовуються до кількох аркушів з величезними наборами даних).

Тим не менш, непогано видалити умовне форматування з будь -якого набору даних, де він вам не потрібен.

Крім розміру, існує ще одна вагома причина для видалення умовного форматування - SPEED.

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

Результати тесту

Я створив файл з 27,2 мільйонами точок даних і застосував до нього 5 правил умовного форматування.

Здається, що різниця в розмірі становить лише кілька КБ.

Читайте також: Поради, як пришвидшити роботу повільних електронних таблиць Excel

Поради щодо підвищення ефективності Excel

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

1. Уникайте використання летких формул. Окрім збільшення розміру файлу, мінливі формули також уповільнюють роботу книг. Приклади мінливих функцій включають RAND, TODAY, OFFSET, INDIRECT тощо.
2. Якщо вам потрібно створити резюме великого набору даних, замість використання формул, подивіться, чи можна замість цього використовувати зведену таблицю. Зведена таблиця швидка та дуже інтуїтивна у порівнянні з формулами.
3. Якщо ви отримуєте дані з кількох джерел або об’єднуєте дані з кількох файлів, використовуйте Power Query для отримання та перетворення даних. Ви можете зберегти дані як з'єднання Power Query, а не зберігати їх на робочому аркуші. Ви можете легко створити зведену таблицю, використовуючи з'єднання, створені за допомогою Power Query.
4. Перетворіть табличні дані в таблицю Excel і використовуйте таблицю Excel у формулах. Це прискорює роботу вашої книги, а формули, створені за допомогою структурованих посилань таблиці Excel, легше керувати (оскільки вони більш інтуїтивно зрозумілі).
5. Уникайте посилань на весь рядок або стовпець у формулі. Це допоможе вашим формулам бути більш ефективними.
6. Якщо у вашій книзі багато формул, вимкніть автоматичний розрахунок і перейдіть до обчислення вручну. Роблячи це, щоразу, коли в робочих аркушах відбуваються зміни, формули не перераховуються. Ви можете внести цю зміну, перейшовши на вкладку Формула та змінивши «Параметри розрахунку».

Сподіваюся, вам знадобляться метод та поради в цьому підручнику.

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