8 начина да намалите размера на файла на Excel (това действително работи)

Известно е, че работните книги на Excel стават болезнено бавни и тежки, тъй като към тях се добавят още данни/формули/изображения.

Понякога е лесно да се отървете от допълнителния флаб и да държите под контрол размера на Excel файла, а понякога не е така.

И тъй като Excel не е уеб-базиран инструмент, повечето от нас работят и си сътрудничат, като изпращат Excel файлове по имейл. И голям Excel файл, който тежи 50 MB, може да бъде трудно да се сподели с вашите колеги/клиенти.

В последната ми работа използвахме имейл клиент на Lotus Notes, който е бавен за охлюви и изпращахме тежки Excel файлове, които бяха изтезания.

Въпреки че няма магически начин да се намали размерът на работна книга на Excel, има няколко техники, които можете да използвате, за да отрежете някаква част.

Забележка: Въздействието на тези техники за намаляване на размера на файла на Excel ще зависи от вашите данни. За някои това може да помогне за намаляване на размера с 50%, а за някои може да намали размера само с 5%.

Съвети за намаляване на размера на Excel файла

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

За всеки съвет направих някои тестове и добавих моментните снимки, за да ви покажа въздействието върху размера на файла. Резултатите, които получавате, ще варират в зависимост от вашите данни и файлове.

Въз основа на вашата работна книга можете да използвате един или повече от тези съвети.

Премахнете ненужните работни листове/данни

Най -добрите решения често са най -лесните.

И тази техника е по -скоро здрав разум и по -малко от всякакви магьосници на Excel.

Ако имате работна книга на Excel, която има някои работни листове, които не са необходими (и не се използват в никакви формули), вижте дали можете да се отървете от тях.

Това има пряка връзка с размера на файла на Excel. Така че веднага след като изтриете някои от работните листове и запазите работната книга, веднага ще видите намален размер на файла.

Резултати от тестовете:

Създадох файл на Excel с 10 работни листа, където всеки работен лист има 2,6 милиона точки данни (26 колони със 100 000 точки данни във всяка колона).

Полученият размер на тази работна книга е 143 MB (и отнема доста време за запазване и отваряне).

Сега, когато изтрих пет листа, размерът се намали наполовина (да).

Както можете да видите, данните, които имате на работен лист, имат пряка връзка с размера на файла.

Конвертиране в двоичен формат (XLSB)

Знаете ли, че само чрез запазване на Excel файл във формат XLSB може да намалите размера на файла?

Да, това е вярно.

Всичко, което трябва да направите, е докато запазвате работна книга, използвайте формата .xlsb.

Ето стъпките за това:

1. Щракнете върху раздела Файл.
2. Кликнете върху Запазване като.
3. Кликнете върху Преглед.
4. В диалоговия прозорец Запиши като променете типа на файла в двоична работна книга на Excel (.xlsb)

Резултати от тестовете

Имах работна книга на Excel, която заемаше 14,6 MB пространство.

След конвертирането на същия файл във формат XLSB успях да намаля размера до 10,4 MB (намаление на размера с ~ 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 MB), по -добре е да се придържате към формати XLSX/XLSM.

Въз основа на това, което чух от хората и прочетох в много форуми, много хора предпочитат да използват XLSB като файлов формат, когато става въпрос за използване на Excel.

Ето една добра статия за разбирането на XLSB формата.

Премахнете ненужните формули

Твърде много формули ще увеличат размера на файла на работната ви книга.

Така че, ако имате набор от данни, от който се нуждаете, но нямате нужда от формулите в него, по -добре е да ги преобразувате в стойности.

Ето стъпките за бързо преобразуване на формули в стойности в Excel:

1. Изберете целия работен лист/набор от данни.
2. Натисни F5 ключ.
3. В диалоговия прозорец Go To кликнете върху „Special“.
4. В диалоговия прозорец Go To Special изберете Формули.
5. Щракнете върху OK.

Горните стъпки биха избрали всички клетки, които имат формула в нея.

Сега използвайте стъпките по -долу, за да преобразувате тези формули в стойности:

1. Копирайте избраните клетки (control + C)
2. Щракнете върху раздела Начало.
3. В групата "Буфер" кликнете върху Поставяне.
4. Кликнете върху иконата Paste Value.

Горните стъпки биха преобразували всички клетки с формули в стойности.

Резултати от тестовете

Създадох Excel файл с 2,7 милиона точки данни (1048576*26) и след това добавих три колони с формули (SUM, COUNT и AVERAGE).

След това преобразувах тези формули в стойности и запазих файла с различно име.

По -долу е резултатът:

Файлът без формули е с ~ 8% по -малък по размер (и имайте предвид, че имах само 3 колони с формули).

В случай, че имате много формули, тя може да добави към размера.

ЗАБЕЛЕЖКА: Летливите формули наистина могат да увеличат размера на файла ви. Като част от теста, когато замених формулата SUM с променлива формула (OFFSET), това доведе до скок в размера на файла (от 186 MB на 198 MB). Така че, ако имате летливи формули, които можете да замените с нелетливи формули, или можете да ги преобразувате в стойности, това ще ви помогне да намалите размера на файла.

Компресиране на изображения (преди и след качване)

Ако работите с файлове на Excel, в които има изображения, трябва да оптимизирате тези изображения и да се уверите, че размерът е по -малък.

Проблемът с изображенията е, че докато ги компресирате, има и намаляване на качеството, което може да не искате.

Когато работите с изображения, най -добрият начин да запазите ниския размер на вашия Excel файл е чрез компресиране на изображенията, преди да го вмъкнете в Excel.

Добър онлайн инструмент за това е TinyPNG (или можете да използвате Photoshop или друг подобен инструмент).

Това е безплатен инструмент, който може да намали размера на изображението с до 90% и все пак поддържа прилично качество на изображението.

По -долу е екранната снимка, на която успях да компресирам изображение с размер от 3.9 MB до 331 KB с минимална загуба на качество.

След като качите тези компресирани изображения в Excel, можете допълнително да ги компресирате, като използвате вградената опция в Excel.

Ето стъпките за компресиране на изображение в Excel:

1. Изберете изображението, което искате да компресирате.
2. Щракнете върху раздела Формати на инструментите за картина. Това е контекстуален раздел, който се появява само когато кликнете върху снимка.
3. В групата Adjust кликнете върху опцията „Compress Picture“.
4. В диалоговия прозорец Компресиране на снимки:
   • Премахнете отметката от „Прилагане само към тази картина“, ако искате да компресирате всички изображения в работната книга.
   • Изберете Email (96 ppi) за максимална компресия.
5. Щракнете върху OK.
6. Запазете файла.

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

Резултати от тестовете:

Използвах опцията компресиране на имидж в Excel, използвайки едно изображение.

По -долу са резултатите:

С помощта на опцията за компресиране на изображението размерът на файла се намалява от 445 KB на 43 KB (~ 90% намаление на размера на файла).

Това ще зависи и от изображението. Компресирано изображение може да не доведе до огромно намаляване на размера.

Премахнете Pivot Cache, за да спестите място

Ако работите с обобщени таблици и се борите с големи Excel файлове, познаването на Pivot кеша може да бъде полезно.

Когато създавате обобщена таблица с помощта на набор от данни, Excel автоматично създава Pivot Cache.

Обобщен кеш е обект, който съдържа реплика на източника на данни.

Въпреки че не можете да го видите, той е част от работната книга и е свързан с обобщената таблица. Когато правите промени в обобщената таблица, тя не използва източника на данни, а използва Pivot Cache.

Докато смятате, че сте директно свързани с изходните данни, в действителност имате достъп до осевия кеш (а не към изходните данни), когато правите промени в обобщената таблица.

Сега, тъй като създавате реплика на набора от данни, това отнема малко място.

Така че веднага щом създадете обобщена таблица, ще забележите, че размерът на файла на Excel се увеличава.

В горния пример, веднага щом добавя обобщена таблица, размерът на файла се увеличава двойно.

Сега има два начина, по които можете да намалите размера на файла, когато работите с обобщени таблици.

1. Запазете изходните данни и изтрийте Pivot Cache
2. Запазете Pivot Cache и изтрийте изходните данни

Нека разгледаме и двете тези техники.

Запазете изходните данни и изтрийте Pivot Cache

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

Това ще направи вашия Excel файл по -лек и имате начин да пресъздадете Pivot кеша, когато имате нужда от него следващия път.

По -долу е разликата в размера, когато запазвате Excel файл със и без Pivot Cache.

Ето стъпките към НЕ запазете Pivot Cache:

1. Изберете всяка клетка в обобщената таблица.
2. Щракнете върху раздела Анализ.
3. Кликнете върху иконата „Опции“ на обобщена таблица.
4. В диалоговия прозорец Опции на обобщената таблица щракнете върху раздела Данни.
5. В опциите за данни за обобщена таблица, премахнете отметката „Запазване на изходни данни с файл“. Тази опция гарантира, че Pivot Cache не се запазва, когато записвате работната книга (и помага за намаляване на размера на файла на Excel).
6. Проверете опцията - „Опресняване на данните при отваряне на файла“. Тази опция гарантира, че веднага след като отворите работната книга, обобщената таблица се опреснява и Pivot Cache се генерира автоматично. Ако не поставите отметка в тази опция, ще трябва да опресните Pivot таблицата ръчно, за да генерирате Pivot Cache.
7. Щракнете върху OK.

Сега, когато запишете файла с обобщена таблица, ще забележите намален размер на файла.

Запазете Pivot Cache и изтрийте изходните данни

Друг начин да намалите размера на файла на Excel е да изтриете изходните данни на обобщена таблица и да запазите само обобщения кеш.

Това работи като Pivot Cache е огледален образ на вашите данни и нямате достъп до изходните данни, когато правите промени в Pivot Table, вместо това използвате Pivot Cache.

По -долу са дадени резултатите, когато запазих файла Excel без изходните данни.

За да направите това, просто изтрийте данните (или като изтриете данните или работния лист, който съдържа данните).

Когато искате да върнете изходните данни, щракнете двукратно върху клетката „Обща сума“ на обобщената таблица и тя незабавно ще генерира целия набор от данни в нов работен лист.

Компресирайте файла (ZIP IT)

Това е нещо, което можете да направите извън приложението Excel.

Когато архивирате Excel файл, веднага ще видите намаление на размера с около 10-15%.

След това можете да споделите този компресиран файл по имейл и лицето, което го получава, може да разархивира файла и да използва работната книга на Excel.

Ето стъпките за ципиране на Excel файл (за Windows):

1. Щракнете с десния бутон върху файла Excel
2. Задръжте курсора на мишката върху опцията Изпращане до.
3. Кликнете върху опцията „Компресирана (ципирана) папка“.

Това ще създаде компресиран файл за избраната работна книга на Excel.

Ще забележите и намаляване на размера на файла (както е показано по -долу).

Резултати от тестовете

С тази техника успях да намаля размера на файла на работната книга на Excel с 8%.

Тази техника работи по-добре за файлове, които са в диапазона 20-50 MB. С увеличаването на размера на файла процентното намаление не е много високо.

Премахване на форматирането на данни

Форматирането на данни, като например прилагане на цвят на фона или добавяне на рамки или промяна на стила на шрифта, може да добави към размера на файла.

Въпреки че въз основа на моите тестове забелязах, че няма голямо влияние.

По -долу са размерите на две работни книги на Excel, едната с 2,6 милиона точки данни (в един работен лист), а другата със същите данни, но форматиране, приложено към нея (цвят на клетката, рамка и курсив).

Резултати от тестовете

Можете да видите, че разликата между двата файла не е толкова голяма (въпреки че оказва влияние, когато имате много данни).

Файлът с данни е 14,6 MB, а файлът с данни и форматиране е 14,9 MB.

Така че, ако искате да намалите размера на файла на работната си книга в Excel, можете да потърсите форматиране, което не ви е необходимо. Премахването му може да ви спести малко дисково пространство.

Ето стъпките за незабавно премахване на цялото форматиране, като същевременно запазите данните непокътнати:

1. Изберете целия набор от данни (от който искате да премахнете форматирането).
2. Щракнете върху раздела Начало.
3. В групата Редактиране кликнете върху „Изчистване“.
4. Кликнете върху „Изчистване на формати“.
5. Запазете работната книга.

Премахнете условното форматиране

Точно като обикновеното форматиране на данни, условното форматиране също увеличава размера на файла.

Въз основа на моите тестове обаче забелязах, че разликата е по -малка (освен ако нямате няколко правила, приложени към множество листове с огромни набори от данни).

Въпреки това е добра идея да премахнете условното форматиране от всеки набор от данни, където нямате нужда от него.

Освен размера, има и друга добра причина да премахнете условното форматиране - SPEED.

Условното форматиране е нестабилно и винаги, когато има някаква промяна в работния лист, той се преизчислява. Това може да доведе до по -бавна работна книга.

Резултати от тестовете

Създадох файл с 27,2 милиона точки данни и приложих 5 правила за условно форматиране към него.

Разликата в размера изглежда е само няколко KB.

Прочетете също: Съвети за бързи електронни таблици в Excel

Съвети за подобряване на ефективността на Excel

Докато горните методи ще ви помогнат да намалите размера на файла на Excel, ето някои допълнителни съвети за подобряване на производителността на работните книги на Excel:

1. Избягвайте използването на летливи формули. Освен че увеличават размера на файла, летливите формули също ви карат работните книги да се забавят. Примерите за променливи функции включват RAND, TODAY, OFFSET, INDIRECT и др.
2. Ако имате нужда да създадете обобщения на голям набор от данни, вместо да използвате формули, вижте дали вместо това можете да използвате обобщена таблица. Обобщена таблица е бърза и много интуитивна в сравнение с формулите.
3. Ако извличате данни от множество източници или комбинирате данни от множество файлове, използвайте Power Query, за да получите и преобразувате данните. Можете да запишете данните като връзка с Power Query, вместо да ги съхранявате в работния лист. Можете лесно да създадете обобщена таблица, като използвате връзките, създадени с помощта на Power Query.
4. Преобразувайте табличните си данни в таблица на Excel и използвайте таблицата на Excel във формули. Това прави вашата работна книга по -бърза и формулите, създадени с помощта на структурирани препратки на таблица на Excel, са по -лесни за управление (тъй като те са по -интуитивни).
5. Избягвайте да препращате цял ред или колона във формула. Това ще помогне вашите формули да бъдат по -ефективни.
6. Ако имате много формули в работната си книга, изключете Автоматичното изчисление и преминете към ръчно изчисление. По този начин всеки път, когато има промяна в работните листове, формулите няма да се преизчислят. Можете да направите тази промяна, като отидете в раздела Формула и промените „Опции за изчисление“.

Надявам се да намерите метода и съветите в този урок за полезни.

Какво друго мога да добавя към този урок, за да го направя по -полезен? Моля, уведомете ме в раздела за коментари.