8 načinov za zmanjšanje velikosti datoteke Excel (ki dejansko deluje)

Znano je, da so Excelovi delovni zvezki boleče počasni in težki, saj jim je dodanih več podatkov/formul/slik.

Včasih se je enostavno znebiti odvečnega bloka in obdržati velikost datoteke Excel, včasih pa ne.

Ker Excel ni spletno orodje, večina od nas dela in sodeluje s pošiljanjem Excelovih datotek po e-pošti. Veliko datoteko Excel, ki tehta 50 MB, je težko deliti s sodelavci/strankami.

Pri mojem zadnjem delu smo uporabili e-poštni odjemalec Lotus Notes, ki je počasen in pošiljali težke Excelove datoteke, kar je bilo mučenje.

Čeprav ni čarobnega načina za zmanjšanje velikosti Excelovega delovnega zvezka, obstaja nekaj tehnik, ki jih lahko uporabite za rezanje nekaterih lisic.

Opomba: Učinek teh tehnik na zmanjšanje velikosti datoteke Excel bi bil odvisen od vaših podatkov. Nekaterim lahko pomaga zmanjšati velikost za 50%, nekaterim pa lahko le zmanjša velikost za 5%.

Nasveti za zmanjšanje velikosti datoteke Excel

Spodaj so navedene tehnike, s katerimi lahko zmanjšate velikost datoteke delovnega zvezka Excel.

Za vsak namig sem opravil nekaj preizkusov in dodal posnetke, ki vam pokažejo vpliv na velikost datoteke. Rezultati, ki jih dobite, se razlikujejo glede na vaše podatke in datoteke.

Na podlagi vašega delovnega zvezka lahko uporabite enega ali več teh nasvetov.

Odstranite nepotrebne delovne liste/podatke

Najboljše rešitve so pogosto tudi najlažje.

In ta tehnika je bolj zdrava pamet in manj katera koli čarovnija Excel.

Če imate delovni zvezek Excel, ki vsebuje nekaj delovnih listov, ki niso potrebni (in se ne uporabljajo v nobeni formuli), preverite, ali se jih lahko znebite.

To je neposredno povezano z velikostjo datoteke Excel. Torej, takoj ko izbrišete nekatere delovne liste in shranite delovni zvezek, boste takoj videli zmanjšano velikost datoteke.

Rezultati testov:

Ustvaril sem datoteko Excel z 10 delovnimi listi, kjer ima vsak delovni list 2,6 milijona podatkovnih točk (26 stolpcev s 100.000 podatkovnimi točkami v vsakem stolpcu).

Nastala velikost tega delovnega zvezka je 143 MB (shranjevanje in odpiranje traja kar nekaj časa).

Ko sem izbrisal pet listov, se je velikost zmanjšala za polovico (duh).

Kot lahko vidite, imajo podatki, ki jih imate na svojem delovnem listu, neposredno povezavo z velikostjo datoteke.

Pretvori v binarno obliko (XLSB)

Ali veste, da lahko samo shranjevanje datoteke Excel v formatu XLSB zmanjša velikost datoteke?

Ja, res je.

Med shranjevanjem delovnega zvezka morate uporabiti format .xlsb.

Tu so naslednji koraki:

1. Kliknite zavihek Datoteka.
2. Kliknite Shrani kot.
3. Kliknite Prebrskaj.
4. V pogovornem oknu Shrani kot spremenite vrsto datoteke v Excelov binarni delovni zvezek (.xlsb)

Rezultati testov

Imel sem Excelov delovni zvezek, ki je zasedel 14,6 MB prostora.

Po pretvorbi iste datoteke v format XLSB sem lahko velikost zmanjšal na 10,4 MB (zmanjšanje velikosti za ~ 28%).

Kaj je XLSB?

Ko shranite datoteko Excel (.xlsx ali .xlsm), se ta shrani v obliki XML.

Na primer, če spremenim razširitev datoteke XLSX v ZIP in jo nato odprem, dobim to.

Če grem še naprej v mapo ‘xl’ in nato v mapo delovnega lista, mi prikaže delovne liste v tem delovnem zvezku (ti delovni listi pa so shranjeni kot dokument XML).

Zdaj naredimo enako z datoteko XLSB.

Zato najprej spremenim razširitev datoteke v ZIP, nato jo odprem in poiščem sheet1. Tukaj dobite:

Opazili boste, da XLSX in XLSB shranita delovni list v drugačni obliki.

XLSX/XLSM ga shrani kot datoteko XML, XLSB pa kot binarno (.bin) datoteko.

In ne da bi bile preveč tehnične, so datoteke XML velike v primerjavi z binarno datoteko.

Ko shranite Excelov delovni zvezek v obliki XLSB, se velikost datoteke zmanjša. V našem zgornjem primeru je bilo zmanjšanje velikosti ~ 30%.

XLSB proti XLSX/XLSM

Pri uporabi formata XLSB je zmanjšanje velikosti velika prednost. Pri delu s formatom XLSB pa morate biti nekoliko previdni.

Prednosti formata XLSB:

1. Zmanjša velikost datoteke Excel. To je lahko v pomoč, če imate velike datoteke in jih želite deliti po e -pošti. Velikost lahko hitro zmanjšate tako, da jo shranite v XLSB.
2. Datoteke XLSB se običajno odprejo in shranijo hitreje kot datoteka XML. Glede na omejene teste, ki sem jih opravil, so se datoteke XLSB odpirale in prihranile 25% -50% hitreje. Ta razlika je opazna samo pri velikih datotekah Excel. Pri manjših datotekah sta XLSB in XLSX hitra.

Čeprav XLSB izgleda odlično, obstaja nekaj razlogov, zakaj bi se morali držati datotek XLSX/XLSM:

1. S formatom XLSB ne morete vedeti, ali ima makro ali ne. Zaradi tega je bolj tvegano, saj jo lahko nekdo uporabi za izvajanje zlonamerne kode.
2. Datoteke XLSB so kodirane v lastniški obliki koša, medtem ko je XML odprtokodna berljiva oblika datoteke. To pomeni, da če uporabljate orodja drugih proizvajalcev z Excelom, je bolje uporabiti obliko XML.
3. Power Query ne more prebrati podatkov iz datoteke XLSB. Če torej uporabljate Power Query, je bolje, da so vaši podatki v oblikah XLSX/XLSM.

Na splošno velja, da če je velikost datoteke majhna (manj kot 5 MB), se je bolje držati formatov XLSX/XLSM.

Glede na to, kar sem slišal od ljudi in prebral na številnih forumih, veliko ljudi raje uporablja XLSB kot obliko datoteke, ko gre za uporabo Excela.

Tukaj je dober članek o razumevanju formata XLSB.

Odstranite nepotrebne formule

Preveč formul bo povečalo velikost datoteke vašega delovnega zvezka.

Torej, če imate nabor podatkov, ki ga potrebujete, vendar v njem ne potrebujete formul, je bolje, da te formule pretvorite v vrednosti.

Tu so koraki za hitro pretvorbo formul v vrednosti v Excelu:

1. Izberite celoten delovni list/nabor podatkov.
2. Pritisnite tipko F5 ključ.
3. V pogovornem oknu Pojdi na kliknite »Posebno«.
4. V pogovornem oknu Pojdi na posebno izberite Formule.
5. Kliknite V redu.

Zgornji koraki bi izbrali vse celice, v katerih je formula.

Zdaj uporabite te korake za pretvorbo teh formul v vrednosti:

1. Kopirajte izbrane celice (control + C)
2. Kliknite zavihek Domov.
3. V skupini odložišče kliknite Prilepi.
4. Kliknite ikono Prilepi vrednost.

Zgornji koraki bi pretvorili vse celice s formulami v vrednosti.

Rezultati testov

Ustvaril sem datoteko Excel z 2,7 milijona podatkovnih točk (1048576*26) in nato dodal tri stolpce s formulami (SUM, COUNT in AVERAGE).

Nato sem te formule pretvoril v vrednosti in shranil datoteko z drugim imenom.

Spodaj je rezultat:

Datoteka brez formul je velikosti ~ 8% manjša (upoštevajte, da sem imel samo 3 stolpce formul).

Če imate veliko formul, se lahko velikost poveča.

OPOMBA: Hlapne formule lahko resnično povečajo velikost vaše datoteke. Kot del testa, ko sem formulo SUM zamenjal s hlapno formulo (OFFSET), je prišlo do povečanja velikosti datoteke (s 186 MB na 198 MB). Če imate torej hlapne formule, ki jih lahko zamenjate z nehlapnimi formulami ali jih pretvorite v vrednosti, vam bo to pomagalo zmanjšati velikost datoteke.

Stisnite slike (pred in po nalaganju)

Če delate z datotekami Excel, ki vsebujejo slike, morate te slike optimizirati in se prepričati, da je velikost manjša.

Težava s slikami je v tem, da se med stiskanjem zmanjša tudi kakovost, česar morda ne želite.

Pri delu s slikami je najboljši način, da velikost datoteke Excel ohranite nizko, tako da slike stisnete, preden jih vstavite v Excel.

Dobro spletno orodje za to je TinyPNG (lahko pa uporabite Photoshop ali katero koli drugo podobno orodje).

To je brezplačno orodje, ki lahko zmanjša velikost slike do 90% in še vedno ohranja dostojno kakovost slike.

Spodaj je posnetek zaslona, ​​na katerem sem lahko stisnil sliko velikosti od 3,9 MB do 331 KB z minimalno izgubo kakovosti.

Ko naložite te stisnjene slike v Excel, jih lahko dodatno stisnete z vgrajeno možnostjo v Excelu.

Tu so koraki za stiskanje slike v Excelu:

1. Izberite sliko, ki jo želite stisniti.
2. Kliknite zavihek Oblika orodij za slike. To je kontekstualni zavihek, ki se prikaže le, ko kliknete sliko.
3. V skupini Prilagodi kliknite možnost »Stisni sliko«.
4. V pogovornem oknu Stisni slike:
   • Počistite polje »Uporabi samo za to sliko«, če želite stisniti vse slike v delovnem zvezku.
   • Izberite E -pošta (96 ppi) za največje stiskanje.
5. Kliknite V redu.
6. Shranite datoteko.

S tem boste stisnili vse slike in vam pomagali zmanjšati velikost datoteke v Excelovem delovnem zvezku.

Rezultati testov:

V Excelu sem uporabil možnost stiskanja slike z eno sliko.

Spodaj so rezultati:

Z uporabo možnosti stiskanja slike se je velikost datoteke zmanjšala s 445 KB na 43 KB (~ 90% zmanjšanje velikosti datoteke).

To bo odvisno tudi od slike. Že stisnjena slika morda ne bo povzročila velikega zmanjšanja velikosti.

Odstranite vrtilni predpomnilnik, da prihranite prostor

Če delate s vrtilnimi tabelami in se borite z velikimi datotekami Excel, je lahko poznavanje vrtilnega predpomnilnika koristno.

Ko ustvarite vrtilno tabelo z naborom podatkov, Excel samodejno ustvari vrtilni predpomnilnik.

Vrtilni predpomnilnik je objekt, ki vsebuje repliko vira podatkov.

Čeprav ga ne vidite, je del delovnega zvezka in je povezan s vrtilno tabelo. Ko naredite kakršne koli spremembe v vrtilni tabeli, ta ne uporablja vira podatkov, ampak uporablja vrtilni predpomnilnik.

Čeprav menite, da ste neposredno povezani z izvornimi podatki, v resnici dostopate do vrtilnega predpomnilnika (in ne do izvornih podatkov), ko spreminjate vrtilno tabelo.

Zdaj, ko ustvarjate repliko nabora podatkov, traja nekaj prostora.

Tako boste takoj, ko ustvarite vrtilno tabelo, opazili, da se velikost datoteke Excel poveča.

V zgornjem primeru, takoj ko dodam vrtilno tabelo, se velikost datoteke dvakrat poveča.

Pri delu s pivot tabelami lahko na dva načina zmanjšate velikost datoteke.

1. Ohranite izvorne podatke in izbrišite vrtilni predpomnilnik
2. Ohranite vrtilni predpomnilnik in izbrišite izvorne podatke

Oglejmo si obe tehniki.

Ohranite izvorne podatke in izbrišite vrtilni predpomnilnik

Ko končate z vrtilno tabelo in jo shranite, lahko Excel prisilite, da shrani samo izvorne podatke in nastalo vrtilno tabelo, ne pa tudi vrtilnega predpomnilnika.

S tem boste datoteko Excel olajšali in imate možnost, da naslednjič znova ustvarite vrtilni predpomnilnik.

Spodaj je razlika v velikosti, ko shranite datoteko Excel z in brez vrtilnega predpomnilnika.

Tu so koraki do NE shranite vrtilni predpomnilnik:

1. Izberite katero koli celico v vrtilni tabeli.
2. Kliknite zavihek Analiza.
3. Kliknite ikono »Možnosti« vrtilne tabele.
4. V pogovornem oknu Možnosti vrtilne tabele kliknite zavihek Podatki.
5. V možnostih podatkov vrtilne tabele, počistite polje 'Shrani izvorne podatke z datoteko'. Ta možnost zagotavlja, da vrtilni predpomnilnik ni shranjen, ko shranite delovni zvezek (in pomaga zmanjšati velikost datoteke Excel).
6. Potrdite možnost - »Osveži podatke pri odpiranju datoteke«. Ta možnost zagotavlja, da se vrtilna tabela, ko odprete delovni zvezek, osveži in se samodejno ustvari vrtilni predpomnilnik. Če ne potrdite te možnosti, boste morali ročno osvežiti vrtilno tabelo, da ustvarite vrtilni predpomnilnik.
7. Kliknite V redu.

Ko datoteko shranite s vrtilno tabelo, boste opazili zmanjšano velikost datoteke.

Ohranite vrtilni predpomnilnik in izbrišite izvorne podatke

Drug način za zmanjšanje velikosti datoteke Excel je izbrisati izvorne podatke vrtilne tabele in ohraniti samo vrtilni predpomnilnik.

To deluje, ker je vrtilni predpomnilnik zrcalna slika vaših podatkov in pri spreminjanju vrtilne tabele ne dostopate do izvornih podatkov, temveč uporabite vrtilni predpomnilnik.

Spodaj so rezultati, ko sem datoteko Excel shranil brez izvornih podatkov.

Če želite to narediti, preprosto izbrišite podatke (bodisi z brisanjem podatkov ali delovnega lista, ki vsebuje podatke).

Ko želite vrniti izvorne podatke, dvokliknite celico »Skupaj« v vrtilni tabeli in takoj bo ustvarila celoten nabor podatkov na novem delovnem listu.

Stisnite datoteko (ZIP IT)

To lahko počnete zunaj aplikacije Excel.

Ko stisnete datoteko Excel, boste takoj videli velikost za približno 10-15%.

To stisnjeno datoteko lahko nato delite prek e -pošte, oseba, ki jo prejme, pa lahko datoteko razpakira in uporabi delovni zvezek Excel.

Spodaj so navedeni koraki za stiskanje datoteke Excel (za Windows):

1. Z desno miškino tipko kliknite datoteko Excel
2. Premaknite kazalec miške na možnost Pošlji na.
3. Kliknite možnost »Stisnjena (zadrgana) mapa«.

Tako boste ustvarili stisnjeno datoteko za izbrani Excelov delovni zvezek.

Opazili boste tudi zmanjšanje velikosti datoteke (kot je prikazano spodaj).

Rezultati testov

S to tehniko sem lahko zmanjšal velikost datoteke Excelovega delovnega zvezka za 8%.

Ta tehnika deluje bolje za datoteke, ki so v razponu 20-50 MB. Ker se velikost datoteke povečuje, odstotek zmanjšanja ni zelo velik.

Odstrani oblikovanje podatkov

Oblikovanje podatkov, na primer uporaba barve ozadja ali dodajanje obrobe ali spreminjanje sloga pisave, lahko poveča velikost datoteke.

Čeprav sem na podlagi svojih testov opazil, da nima velikega vpliva.

Spodaj sta velikosti dveh Excelovih delovnih zvezkov, eden z 2,6 milijona podatkovnih točk (na enem delovnem listu), drugi pa z istimi podatki, vendar z oblikovanjem (barva celice, obroba in poševno).

Rezultati testov

Vidite, da razlika med obema datotekama ni tako velika (čeprav vpliva, ko imate veliko podatkov).

Datoteka s podatki je 14,6 MB, datoteka s podatki in oblikovanjem pa 14,9 MB.

Če torej želite zmanjšati velikost datoteke v Excelovem delovnem zvezku, lahko poiščete oblikovanje, ki ga ne potrebujete. Če ga odstranite, lahko prihranite nekaj prostora na disku.

Spodaj so navedeni koraki za takojšnjo odstranitev vsega oblikovanja, pri tem pa ohranite podatke nedotaknjene:

1. Izberite celoten nabor podatkov (iz katerega želite odstraniti oblikovanje).
2. Kliknite zavihek Domov.
3. V skupini Urejanje kliknite »Počisti«.
4. Kliknite »Počisti oblike«.
5. Shranite delovni zvezek.

Odstranite pogojno oblikovanje

Tako kot običajno oblikovanje podatkov tudi pogojno oblikovanje poveča velikost datoteke.

Vendar sem na podlagi svojih testov opazil, da je razlika manjša (razen če za več listov z ogromnimi nabori podatkov uporabljate več pravil).

Kljub temu je dobro odstraniti pogojno oblikovanje iz katerega koli nabora podatkov, kjer ga ne potrebujete.

Poleg velikosti obstaja še en dober razlog za odstranitev pogojnega oblikovanja - SPEED.

Pogojno oblikovanje je nestanovitno in se vsakič, ko pride do kakršne koli spremembe na delovnem listu, ponovno izračuna. To lahko vodi do počasnejšega delovnega zvezka.

Rezultati testov

Ustvaril sem datoteko s 27,2 milijona podatkovnih točk in zanjo uporabil 5 pravil pogojnega oblikovanja.

Zdi se, da je razlika v velikosti le nekaj KB.

Preberite tudi: Nasveti za hitro pospeševanje preglednic v Excelu

Nasveti za povečanje zmogljivosti programa Excel

Čeprav vam bodo zgornje metode pomagale zmanjšati velikost datoteke Excel, je tukaj nekaj dodatnih nasvetov za izboljšanje delovanja Excelovih delovnih zvezkov:

1. Izogibajte se uporabi hlapnih formul. Hlapne formule poleg povečanja velikosti datoteke upočasnijo tudi delovne zvezke. Primeri nestanovitnih funkcij vključujejo RAND, TODAY, OFFSET, INDIRECT itd.
2. Če morate namesto formul ustvariti povzetke velikega nabora podatkov, preverite, ali lahko namesto tega uporabite vrtilno tabelo. Vrtilna tabela je hitra in zelo intuitivna v primerjavi s formulami.
3. Če izvlečete podatke iz več virov ali združite podatke iz več datotek, uporabite Power Query za pridobivanje in preoblikovanje podatkov. Podatke lahko shranite kot povezavo Power Query, namesto da jih hranite na delovnem listu. Vrtilno tabelo lahko preprosto ustvarite s povezavami, ustvarjenimi s programom Power Query.
4. Pretvorite svoje tabelarne podatke v Excelovo tabelo in uporabite Excelovo tabelo v formulah. Tako bo vaš delovni zvezek deloval hitreje, formule, ustvarjene s pomočjo strukturiranih referenc Excel Table, pa je lažje upravljati (saj so te bolj intuitivne).
5. Izogibajte se sklicevanju na celotno vrstico ali stolpec v formuli. Tako bodo vaše formule učinkovitejše.
6. Če imate v delovnem zvezku veliko formul, izklopite samodejni izračun in preklopite na ročni izračun. S tem se formule ne bi preračunale vsakič, ko pride do spremembe delovnih listov. To spremembo lahko izvedete tako, da odprete zavihek Formula in spremenite »Možnosti izračuna«.

Upam, da vam bodo metoda in nasveti v tej vadnici koristni.

Kaj bi še lahko dodal tej vadnici, da bi bila bolj uporabna? Sporočite mi v oddelku za komentarje.