8 načina za smanjenje veličine datoteke Excel (koji stvarno funkcioniraju)

Poznato je da radne knjige programa Excel postaju bolno spore i teške s dodavanjem više podataka/formula/slika.

Ponekad se lako riješite dodatne mrlje i kontrolirate veličinu datoteke programa Excel, a ponekad nije.

A budući da Excel nije alat temeljen na webu, većina nas radi i surađuje slanjem Excel datoteka putem e-pošte. I veliku Excel datoteku tešku 50 MB može biti teško podijeliti s vašim kolegama/klijentima.

U mom prošlom poslu koristili smo Lotus Notes klijent e-pošte, koji sporo puži, i slanje teških Excel datoteka preko toga je bilo mučenje.

Iako ne postoji čaroban način za smanjenje veličine radne knjige programa Excel, postoji nekoliko tehnika koje možete koristiti za rezanje neke mrvice.

Napomena: Učinak ovih tehnika na smanjenje veličine Excel datoteke ovisit će o vašim podacima. Nekima može pomoći smanjiti veličinu za 50%, a nekima može samo smanjiti veličinu za 5%.

Savjeti za smanjenje veličine datoteke Excel

Dolje su navedene tehnike koje možete koristiti za smanjenje veličine datoteke radne knjige programa Excel.

Za svaki savjet sam proveo neka testiranja i dodao sam snimke kako bih vam pokazao utjecaj na veličinu datoteke. Rezultati koje ćete dobiti razlikovat će se ovisno o vašim podacima i datotekama.

Na temelju vaše radne knjige možete koristiti jedan ili više ovih savjeta.

Uklonite nepotrebne radne listove/podatke

Najbolja rješenja često su i najjednostavnija.

Ova je tehnika više zdrav razum, a manje čarobnjaštvo u Excelu.

Ako imate radnu knjigu programa Excel koja ima neke radne listove koji nisu potrebni (i ne koriste se u bilo kojoj formuli), provjerite možete li ih se riješiti.

To ima izravnu vezu s veličinom datoteke programa Excel. Dakle, čim izbrišete neke radne listove i spremite radnu knjigu, odmah ćete vidjeti smanjenu veličinu datoteke.

Rezultati ispitivanja:

Napravio sam Excel datoteku s 10 radnih listova gdje svaki radni list ima 2,6 milijuna podatkovnih točaka (26 stupaca sa 100 000 podatkovnih točaka u svakom stupcu).

Rezultirajuća veličina ove radne knjige je 143 MB (i potrebno je dosta vremena za spremanje i otvaranje).

Kad sam izbrisao pet listova, veličina se smanjila za pola (duh).

Kao što vidite, podaci koje imate na radnom listu izravno su povezani s veličinom datoteke.

Pretvori u binarni format (XLSB)

Znate li da samo spremanjem Excel datoteke u XLSB formatu možete smanjiti veličinu datoteke?

Da, istina je.

Sve što trebate učiniti je prilikom spremanja radne knjige upotrijebiti .xlsb format.

Evo koraka za to:

1. Kliknite karticu Datoteka.
2. Pritisnite Spremi kao.
3. Pritisnite Pregledaj.
4. U dijaloškom okviru Spremi kao promijenite vrstu datoteke u Excel Binarnu radnu knjigu (.xlsb)

Rezultati ispitivanja

Imao sam radnu knjigu programa Excel koja je zauzela 14,6 MB prostora.

Nakon pretvaranja iste datoteke u XLSB format, uspio sam smanjiti veličinu na 10,4 MB (smanjenje veličine za ~ 28%).

Što je XLSB?

Kada spremite Excel datoteku (.xlsx ili .xlsm), ona se sprema u XML formatu.

Na primjer, ako promijenim ekstenziju datoteke XLSX u ZIP, a zatim je otvorim, to dobivam.

A ako dalje odem u mapu ‘xl’, a zatim u mapu radnog lista, prikazat će mi se radni listovi u toj radnoj knjizi (a ti se radni listovi spremaju kao XML dokument).

Učinimo sada isto s XLSB datotekom.

Stoga prvo mijenjam ekstenziju datoteke u ZIP, zatim je otvaram i lociram sheet1. Evo što dobivate:

Primijetit ćete da XLSX i XLSB spremaju radni list u drugom formatu.

XLSX/XLSM sprema ga kao XML datoteku, a XLSB kao binarnu (.bin) datoteku.

I bez previše tehničke, XML datoteke velike su veličine u usporedbi s binarnom datotekom.

Dakle, kada svoju Excel radnu knjigu spremite u XLSB formatu, veličina datoteke se smanjuje. U našem gornjem primjeru smanjenje veličine je bilo ~ 30%.

XLSB vs XLSX/XLSM

Što se tiče korištenja XLSB formata, smanjenje veličine je velika prednost. No, morate biti malo oprezni pri radu s XLSB formatom.

Prednosti XLSB formata:

1. Smanjuje veličinu datoteke programa Excel. To može biti korisno ako imate velike datoteke i želite ih podijeliti putem e -pošte. Veličinu možete brzo smanjiti spremanjem u XLSB.
2. XLSB datoteke otvaraju se i spremaju brže od XML datoteke. Na temelju ograničenih testova koje sam napravio, XLSB datoteke su se brže otvarale i štedjele 25% -50% brže. Ova je razlika uočljiva samo u velikim Excel datotekama. S manjim datotekama, XLSB i XLSX su brzi.

Iako XLSB izgleda sjajno, postoji nekoliko razloga zašto biste se trebali držati korištenja XLSX/XLSM datoteka:

1. S XLSB formatom ne možete znati ima li makronaredbu ili ne. To ga čini rizičnijim jer ga netko može koristiti za izvršavanje zlonamjernog koda.
2. XLSB datoteke kodirane su u vlasničkom formatu datoteke za smeće, dok je XML format datoteke za čitanje otvorenog koda. To znači da ako koristite alate trećih strana s Excelom, bolje je koristiti XML format.
3. Power Query ne može čitati podatke iz XLSB datoteke. Stoga, ako koristite Power Query, bolje je da vaši podaci budu u XLSX/XLSM formatima.

Općenito je pravilo, ako je veličina datoteke mala (manja od 5 MB), bolje je držati se XLSX/XLSM formata.

Na temelju onoga što sam čuo od ljudi i pročitao na mnogim forumima, mnogi ljudi radije koriste XLSB kao format datoteke kada je u pitanju korištenje Excela.

Evo dobrog članka o razumijevanju XLSB formata.

Uklonite nepotrebne formule

Previše formula uvećat će veličinu datoteke vaše radne knjige.

Dakle, ako imate skup podataka koji vam je potreban, ali vam ne trebaju formule u njemu, bolje je pretvoriti te formule u vrijednosti.

Evo koraka za brzo pretvaranje formula u vrijednosti u Excelu:

1. Odaberite cijeli radni list/skup podataka.
2. pritisni F5 ključ.
3. U dijaloškom okviru Idi na kliknite "Posebno".
4. U dijaloškom okviru Idi na posebno odaberite Formule.
5. Pritisnite U redu.

Gore navedeni koraci odabrali bi sve ćelije koje u sebi imaju formulu.

Sada koristite donje korake za pretvaranje ovih formula u vrijednosti:

1. Kopirajte odabrane ćelije (control + C)
2. Kliknite karticu Početna.
3. U grupi Međuspremnik kliknite Zalijepi.
4. Kliknite na ikonu Zalijepi vrijednost.

Gore navedeni koraci pretvorili bi sve ćelije s formulama u vrijednosti.

Rezultati ispitivanja

Napravio sam Excel datoteku s 2,7 milijuna podatkovnih točaka (1048576*26), a zatim dodao tri stupca s formulama (ZBIR, COUNT i PROSJEK).

Zatim sam pretvorio ove formule u vrijednosti i spremio datoteku s drugim imenom.

Ispod je rezultat:

Datoteka bez formula je veličine ~ 8% manje (imajte na umu da sam imao samo 3 stupca formula).

U slučaju da imate mnogo formula, to se može povećati veličini.

BILJEŠKA: Hlapljive formule zaista mogu povećati veličinu vaše datoteke. Kao dio testa, kada sam formulu SUM zamijenio promjenjivom formulom (OFFSET), to je dovelo do skoka veličine datoteke (sa 186 MB na 198 MB). Dakle, ako imate hlapljive formule koje možete zamijeniti nehlapljivim formulama ili ih možete pretvoriti u vrijednosti, to će vam pomoći da smanjite veličinu datoteke.

Komprimirajte slike (prije i poslije prijenosa)

Ako radite s Excel datotekama koje sadrže slike, morate optimizirati te slike i pobrinuti se da njihova veličina bude manja.

Problem sa slikama je u tome što dok ih komprimirate dolazi i do smanjenja kvalitete, što možda ne želite.

Kada radite sa slikama, najbolji način da zadržite nisku veličinu Excel datoteke je komprimiranje slika prije nego što ih umetnete u Excel.

Dobar internetski alat za to je TinyPNG (ili možete koristiti Photoshop ili bilo koji drugi sličan alat).

To je besplatan alat koji može smanjiti veličinu slike do 90%, a i dalje zadržati pristojnu kvalitetu slike.

Ispod je snimka zaslona na kojoj sam uspio komprimirati sliku veličine od 3,9 MB do 331 KB uz minimalni gubitak kvalitete.

Nakon što prenesete ove komprimirane slike u Excel, možete ih dodatno komprimirati pomoću ugrađene opcije u Excelu.

Evo koraka za komprimiranje slike u Excelu:

1. Odaberite sliku koju želite komprimirati.
2. Kliknite karticu Format alata za slike. Ovo je kontekstualna kartica koja se pojavljuje samo kada kliknete na sliku.
3. U grupi Prilagodba kliknite opciju "Komprimiraj sliku".
4. U dijaloškom okviru Sažimanje slika:
   • Poništite odabir "Primijeni samo na ovu sliku" ako želite komprimirati sve slike u radnoj knjizi.
   • Odaberite E -pošta (96 ppi) za maksimalnu kompresiju.
5. Pritisnite U redu.
6. Spremite datoteku.

Ovo će komprimirati sve slike i pomoći vam u smanjenju veličine datoteke radne knjige programa Excel.

Rezultati ispitivanja:

Koristio sam opciju compression imge u Excelu koristeći jednu sliku.

Ispod su rezultati:

Korištenjem opcije komprimirane slike veličina datoteke je smanjena sa 445 KB na 43 KB (~ 90% smanjenje veličine datoteke).

To bi ovisilo i o slici. Komprimirana slika možda neće dovesti do velikog smanjenja veličine.

Uklonite zaokretnu predmemoriju radi uštede prostora

Ako radite sa zaokretnim tablicama i borite se s velikim Excel datotekama, znanje o zaokretnoj predmemoriji može biti korisno.

Kad kreirate zaokretnu tablicu pomoću skupa podataka, Excel automatski stvara zaokretnu predmemoriju.

Zaokretna predmemorija je objekt koji sadrži repliku izvora podataka.

Iako ga ne možete vidjeti, dio je radne knjige i povezan je sa zaokretnom tablicom. Kad unesete bilo kakve promjene u zaokretnu tablicu, ona ne koristi izvor podataka, već koristi zaokretnu predmemoriju.

Iako mislite da ste izravno povezani s izvornim podacima, u stvarnosti pristupate zaokretnoj predmemoriji (a ne izvornim podacima) kada unosite promjene u zaokretnu tablicu.

Budući da stvarate repliku skupa podataka, potrebno je malo prostora.

Dakle, čim stvorite zaokretnu tablicu, primijetit ćete da se veličina Excel datoteke povećava.

U gornjem primjeru, čim dodam zaokretnu tablicu, veličina datoteke se dvostruko povećava.

Postoje dva načina na koja možete smanjiti veličinu datoteke pri radu sa zaokretnim tablicama.

1. Sačuvajte izvorne podatke i izbrišite zaokretnu predmemoriju
2. Zadržite zaokretnu predmemoriju i izbrišite izvorne podatke

Pogledajmo obje ove tehnike.

Zadržite izvorne podatke i izbrišite zaokretnu predmemoriju

Kad završite s zaokretnom tablicom i spremate je, možete natjerati Excel da spremi samo izvorne podatke i rezultirajuću zaokretnu tablicu, a ne zaokretnu predmemoriju.

Na taj ćete način Excel datoteka postati lakša, a imate i način da ponovno stvorite zaokretnu predmemoriju kada vam sljedeći put zatreba.

Ispod je razlika u veličini kada spremate Excel datoteku sa i bez zaokretne predmemorije.

Evo koraka do NE spremite zaokretnu predmemoriju:

1. Odaberite bilo koju ćeliju u zaokretnoj tablici.
2. Kliknite karticu Analiza.
3. Kliknite ikonu "Opcije" zaokretne tablice.
4. U dijaloškom okviru Opcije zaokretne tablice kliknite karticu Podaci.
5. U opcijama Podaci zaokretne tablice, poništite odabir ‘Spremi izvorne podatke s datotekom’. Ova opcija osigurava da zaokretna predmemorija nije spremljena kada spremite radnu knjigu (i pomaže u smanjenju veličine datoteke programa Excel).
6. Označite opciju - ‘Osvježi podatke pri otvaranju datoteke’. Ova opcija osigurava da čim se otvori radna knjiga, zaokretna tablica se osvježi i zaokretna predmemorija automatski generira. Ako ne označite ovu opciju, morat ćete ručno osvježiti zaokretnu tablicu da biste generirali zaokretnu predmemoriju.
7. Pritisnite U redu.

Kad spremite datoteku sa zaokretnom tablicom, primijetit ćete smanjenu veličinu datoteke.

Zadržite zaokretnu predmemoriju i izbrišite izvorne podatke

Drugi način smanjenja veličine Excel datoteke je brisanje izvornih podataka zaokretne tablice i zadržavanje samo zaokretne predmemorije.

Ovo funkcionira kao zaokretna predmemorija zrcalna je slika vaših podataka i ne pristupate izvornim podacima kada unosite promjene u zaokretnu tablicu, umjesto toga koristite zaokretnu predmemoriju.

Ispod su rezultati kada sam spremio Excel datoteku bez izvornih podataka.

Da biste to učinili, samo izbrišite podatke (brisanjem podataka ili radnog lista koji sadrži te podatke).

Kad želite vratiti izvorne podatke, dvaput kliknite na ćeliju "Ukupni zbroj" zaokretne tablice i odmah će generirati cijeli skup podataka na novom radnom listu.

Komprimiraj datoteku (ZIP IT)

To možete učiniti izvan Excel aplikacije.

Kada zipate Excel datoteku, odmah ćete vidjeti smanjenje veličine za oko 10-15%.

Zatim možete podijeliti ovu arhiviranu datoteku putem e -pošte, a osoba koja je primi može raspakirati datoteku i koristiti radnu knjigu programa Excel.

Evo koraka za zipanje Excel datoteke (za Windows):

1. Desnom tipkom miša kliknite Excel datoteku
2. Zadržite pokazivač miša na opciji Pošalji na.
3. Kliknite opciju "Komprimirana (zipirana) mapa".

To će stvoriti arhiviranu datoteku za odabranu radnu knjigu programa Excel.

Također ćete primijetiti smanjenje veličine datoteke (kao što je prikazano u nastavku).

Rezultati ispitivanja

Ovom tehnikom uspio sam smanjiti veličinu datoteke radne knjige programa Excel za 8%.

Ova tehnika bolje radi za datoteke koje su u rasponu od 20-50 MB. S povećanjem veličine datoteke, postotak smanjenja nije jako velik.

Uklonite oblikovanje podataka

Formatiranje podataka, poput primjene boje pozadine ili dodavanja obruba ili mijenjanja stila fonta, može dodati veličinu datoteke.

Iako sam na temelju svojih testova primijetio da to nema veliki utjecaj.

Dolje su navedene veličine dvije radne knjige programa Excel, jedna s 2,6 milijuna podatkovnih točaka (na jednom radnom listu), a druga s istim podacima, ali oblikovanjem primijenjenim na nju (boja ćelije, obrub i kurziv).

Rezultati ispitivanja

Vidite da razlika između dvije datoteke nije tako velika (iako ima utjecaja kada imate puno podataka).

Datoteka s podacima iznosi 14,6 MB, a datoteka s podacima i oblikovanjem 14,9 MB.

Dakle, ako želite smanjiti veličinu datoteke svoje Excel radne knjige, možete potražiti oblikovanje koje vam ne treba. Uklanjanjem možete uštedjeti malo prostora na disku.

Evo koraka za trenutno uklanjanje svih oblikovanja, a da podaci ostanu netaknuti:

1. Odaberite cijeli skup podataka (iz kojeg želite ukloniti oblikovanje).
2. Kliknite karticu Početna.
3. U grupi Uređivanje kliknite ‘Obriši’.
4. Kliknite na "Obriši formate".
5. Spremite radnu knjigu.

Uklonite uvjetno oblikovanje

Baš kao i obično oblikovanje podataka, uvjetno oblikovanje također povećava veličinu datoteke.

Međutim, na temelju mojih testova primijetio sam da je razlika manja (osim ako imate više pravila primijenjenih na više listova s ​​ogromnim skupovima podataka).

Ipak, dobra je ideja ukloniti uvjetno oblikovanje iz bilo kojeg skupa podataka gdje vam nije potreban.

Osim veličine, postoji još jedan dobar razlog za uklanjanje uvjetnog oblikovanja - SPEED.

Uvjetno oblikovanje je nestabilno i kad god dođe do bilo kakve promjene na radnom listu, ponovno se izračunava. To može dovesti do sporije radne knjige.

Rezultati ispitivanja

Napravio sam datoteku s 27,2 milijuna podatkovnih točaka i na nju primijenio 5 pravila uvjetnog oblikovanja.

Čini se da je razlika u veličini samo nekoliko KB -a.

Također pročitajte: Savjeti za ubrzavanje usporenih Excel proračunskih tablica

Savjeti za poboljšanje učinka programa Excel

Iako će vam gore navedene metode pomoći u smanjenju veličine datoteke programa Excel, evo nekoliko dodatnih savjeta za poboljšanje izvedbe Excel radnih knjiga:

1. Izbjegavajte korištenje hlapljivih formula. Osim što povećavaju veličinu datoteke, nestabilne formule također usporavaju radne knjige. Primjeri promjenjivih funkcija uključuju RAND, TODAY, OFFSET, INDIRECT itd.
2. Ako trebate stvoriti sažetke velikog skupa podataka, umjesto da koristite formule, pogledajte možete li umjesto toga koristiti zaokretnu tablicu. Zaokretna tablica je brza i vrlo intuitivna u usporedbi s formulama.
3. Ako izvlačite podatke iz više izvora ili kombinirate podatke iz više datoteka, upotrijebite Power Query za dobivanje i pretvaranje podataka. Podatke možete spremiti kao vezu Power Query umjesto da ih držite na radnom listu. Zaokretnu tablicu možete jednostavno stvoriti pomoću veza stvorenih pomoću programa Power Query.
4. Pretvorite svoje tablične podatke u Excel tablicu i koristite Excel tablicu u formulama. Time se vaša radna knjiga brže izvodi, a formulama izrađenim pomoću Excel tablice strukturiranim referencama lakše se upravlja (budući da su one intuitivnije).
5. Izbjegavajte pozivanje na cijeli redak ili stupac u formuli. To će pomoći da vaše formule budu učinkovitije.
6. Ako u radnoj knjizi imate puno formula, isključite Automatski izračun i prijeđite na ručni izračun. Radeći to, svaki put kad dođe do promjene na radnim listovima, formule se ne bi ponovno izračunale. Ovu promjenu možete izvršiti ako odete na karticu Formula i promijenite "Opcije izračuna".

Nadam se da su vam metoda i savjeti u ovom vodiču bili korisni.

Što bih još mogao dodati ovom vodiču kako bi bio korisniji? Obavijestite me u odjeljku za komentare.