8 spôsobov, ako zmenšiť veľkosť súboru programu Excel (ktoré skutočne fungujú)

Je známe, že zošity programu Excel sa stávajú bolestivo pomalými a ťažkými, pretože sa doň pridáva viac údajov/vzorcov/obrázkov.

Niekedy je ľahké zbaviť sa nadbytočnej chyby a udržať kontrolu nad veľkosťou súboru Excel, a niekedy nie.

A pretože Excel nie je webový nástroj, väčšina z nás pracuje a spolupracuje odosielaním súborov programu Excel e-mailom. A veľký súbor Excel, ktorý váži 50 MB, je ťažké zdieľať s kolegami/klientmi.

V mojom poslednom zamestnaní sme použili e-mailového klienta Lotus Notes, ktorý je pomalý a odosielanie náročných súborov programu Excel bolo mučením.

Napriek tomu, že neexistuje žiadny magický spôsob, ako zmenšiť veľkosť zošita programu Excel, existuje niekoľko techník, ktoré môžete použiť na zníženie počtu obrobkov.

Poznámka: Vplyv týchto techník na zníženie veľkosti súboru programu Excel bude závisieť od vašich údajov. Niekomu to môže pomôcť zmenšiť veľkosť o 50% a niekomu to môže skončiť iba zmenšením veľkosti o 5%.

Tipy na zníženie veľkosti súboru programu Excel

Nasledujú techniky, ktoré môžete použiť na zníženie veľkosti súboru zošita programu Excel.

Pre každý tip som vykonal niekoľko testovaní a pridal som snímky, aby som vám ukázal vplyv na veľkosť súboru. Výsledky, ktoré získate, sa budú líšiť v závislosti od vašich údajov a súborov.

Na základe zošita môžete použiť jeden alebo viac z týchto tipov.

Odstráňte nepotrebné pracovné hárky/údaje

Najlepšie riešenia sú často najľahšie.

A táto technika je viac založená na zdravom rozumu a menej na akomkoľvek čarodejníctve v Exceli.

Ak máte zošit programu Excel, ktorý obsahuje niektoré pracovné hárky, ktoré nie sú potrebné (a nepoužívajú sa v žiadnych vzorcoch), zistite, či sa ich môžete zbaviť.

To priamo súvisí s veľkosťou súboru programu Excel. Akonáhle teda odstránite niektoré pracovné hárky a zošit uložíte, okamžite uvidíte zmenšenú veľkosť súboru.

Výsledky testu:

Vytvoril som súbor programu Excel s 10 pracovnými listami, kde každý pracovný hárok má 2,6 milióna údajových bodov (26 stĺpcov so 100 000 údajovými bodmi v každom stĺpci).

Výsledná veľkosť tohto zošita je 143 MB (uloženie a otvorenie trvá nejaký čas).

Keď som teraz odstránil päť listov, veľkosť sa zmenšila na polovicu (duh).

Ako vidíte, údaje, ktoré máte v pracovnom hárku, majú priamu koreláciu s veľkosťou súboru.

Previesť na binárny formát (XLSB)

Viete, že uložením súboru programu Excel vo formáte XLSB môžete zmenšiť veľkosť súboru?

Áno, je to pravda.

Stačí, keď pri ukladaní zošita použijete formát .xlsb.

Tu sú kroky, ako to urobiť:

1. Kliknite na kartu Súbor.
2. Kliknite na položku Uložiť ako.
3. Kliknite na Prehľadávať.
4. V dialógovom okne Uložiť ako zmeňte typ súboru na Binárny zošit programu Excel (.xlsb)

Výsledky testu

Mal som excelový zošit, ktorý zaberal 14,6 MB miesta.

Po konverzii rovnakého súboru na formát XLSB sa mi podarilo zmenšiť veľkosť na 10,4 MB (zmenšenie veľkosti o ~ 28%).

Čo je to XLSB?

Keď uložíte súbor programu Excel (.xlsx alebo .xlsm), uloží sa vo formáte XML.

Ak napríklad zmením príponu súboru XLSX na ZIP a potom ho otvorím, dostanem toto.

A ak ďalej prejdem do priečinka „xl“ a potom do priečinka s pracovným listom, zobrazí sa mi pracovné hárky v tomto zošite (a tieto pracovné hárky sa uložia ako dokument XML).

Teraz urobíme to isté so súborom XLSB.

Najprv teda zmením príponu súboru na ZIP, potom ju otvorím a vyhľadám sheet1. Tu je to, čo získate:

Všimnite si, že XLSX aj XLSB ukladá pracovný hárok v inom formáte.

XLSX/XLSM ho uloží ako súbor XML a XLSB ho uloží ako binárny (.bin) súbor.

A bez toho, aby to bolo príliš technické, súbory XML sú v porovnaní s binárnym súborom veľké.

Keď teda uložíte zošit programu Excel vo formáte XLSB, veľkosť súboru sa zmenší. V našom vyššie uvedenom príklade bolo zmenšenie veľkosti ~ 30%.

XLSB vs XLSX/XLSM

Pokiaľ ide o používanie formátu XLSB, je zmenšenie veľkosti obrovským prínosom. Pri práci s formátom XLSB však musíte byť trochu opatrní.

Výhody formátu XLSB:

1. Zmenšuje veľkosť súboru programu Excel. To môže byť užitočné, ak máte veľké súbory a chcete ich zdieľať prostredníctvom e -mailu. Veľkosť môžete rýchlo zmenšiť uložením do XLSB.
2. Súbory XLSB sa otvárajú a ukladajú rýchlejšie ako súbory XML. Na základe obmedzených testov, ktoré som vykonal, sa súbory XLSB otvárali a ukladali o 25%-50% rýchlejšie. Tento rozdiel je viditeľný iba vo veľkých súboroch programu Excel. S menšími súbormi sú XLSB aj XLSX rýchle.

Aj keď XLSB vyzerá skvele, existuje niekoľko dôvodov, prečo by ste sa mali riadiť používaním súborov XLSX/XLSM:

1. Pri formáte XLSB nemáte spôsob, ako zistiť, či má makro alebo nie. Vďaka tomu je niekto riskantnejší, pretože ho môže niekto použiť na spustenie škodlivého kódu.
2. Súbory XLSB sú kódované v proprietárnom formáte bin, zatiaľ čo XML je formát súboru s otvoreným zdrojovým kódom na čítanie. To znamená, že ak v programe Excel používate nástroje tretích strán, je lepšie použiť formát XML.
3. Power Query nemôže čítať údaje zo súboru XLSB. Ak teda používate Power Query, je lepšie mať dáta vo formáte XLSX/XLSM.

Všeobecne platí, že ak je veľkosť súboru malá (menej ako 5 MB), je lepšie držať sa formátov XLSX/XLSM.

Na základe toho, čo som počul od ľudí a čítal som na mnohých fórach, veľa ľudí dáva prednosť použitiu formátu XLSB, pokiaľ ide o používanie Excelu.

Tu je dobrý článok o porozumení formátu XLSB.

Odstráňte nepotrebné vzorce

Príliš veľa vzorcov bude nadúvať veľkosť súboru zošita.

Ak teda máte súbor údajov, ktorý potrebujete, ale nepotrebujete v ňom vzorce, je lepšie tieto vzorce previesť na hodnoty.

Tu sú kroky na rýchlu konverziu vzorcov na hodnoty v programe Excel:

1. Vyberte celý pracovný hárok/množinu údajov.
2. Stlačte tlačidlo F5 kľúč.
3. V dialógovom okne Prejsť na kliknite na položku „Špeciálne“.
4. V dialógovom okne Prejsť na špeciálne vyberte položku Vzorce.
5. Kliknite na tlačidlo OK.

Vyššie uvedené kroky by vybrali všetky bunky, v ktorých je vzorec.

Teraz pomocou nižšie uvedených krokov preveďte tieto vzorce na hodnoty:

1. Skopírujte vybraté bunky (kontrola + C)
2. Kliknite na kartu Domov.
3. V skupine Schránka kliknite na Prilepiť.
4. Kliknite na ikonu Prilepiť hodnotu.

Vyššie uvedené kroky by previedli všetky bunky so vzorcami na hodnoty.

Výsledky testu

Vytvoril som súbor programu Excel s 2,7 miliónmi údajových bodov (1048576*26) a potom som pridal tri stĺpce so vzorcami (SUM, COUNT a PRIEMER).

Potom som tieto vzorce previedol na hodnoty a súbor uložil pod iným názvom.

Nasleduje výsledok:

Súbor bez vzorcov má o ~ 8% menšiu veľkosť (a všimnite si, že som mal iba 3 stĺpce vzorcov).

V prípade, že máte veľa vzorcov, môže to na veľkosti prispieť.

POZNÁMKA: Prchavé vzorce môžu skutočne nahustiť vašu veľkosť súboru. V rámci testu, keď som nahradil vzorec SUMA prchavým vzorcom (OFFSET), viedol k skoku vo veľkosti súboru (z 186 MB na 198 MB). Ak teda máte prchavé vzorce, ktoré môžete nahradiť neprchavými vzorcami, alebo ich môžete previesť na hodnoty, pomôže vám to zmenšiť veľkosť súboru.

Komprimujte obrázky (pred a po nahraní)

Ak pracujete so súbormi programu Excel, ktoré obsahujú obrázky, musíte tieto obrázky optimalizovať a uistiť sa, že veľkosť je menšia.

Problém s obrázkami je ten, že keď ich skomprimujete, dôjde aj k zníženiu kvality, čo možno nebudete chcieť.

Pri práci s obrázkami je najlepším spôsobom, ako obmedziť veľkosť súboru programu Excel, kompresia obrázkov pred jeho vložením do programu Excel.

Dobrým online nástrojom na to je TinyPNG (alebo môžete použiť Photoshop alebo iný podobný nástroj).

Je to bezplatný nástroj, ktorý dokáže zmenšiť veľkosť obrázka až o 90% a stále si zachováva slušnú kvalitu obrazu.

Nasleduje snímka obrazovky, kde sa mi podarilo skomprimovať obrázok s veľkosťou 3,9 MB na 331 kB s minimálnou stratou kvality.

Po nahraní týchto komprimovaných obrázkov do programu Excel ich môžete ďalej komprimovať pomocou vstavanej možnosti v programe Excel.

Tu sú kroky na komprimáciu obrázku v programe Excel:

1. Vyberte obrázok, ktorý chcete komprimovať.
2. Kliknite na kartu Formát obrázkových nástrojov. Toto je kontextová karta, ktorá sa zobrazí iba vtedy, keď kliknete na obrázok.
3. V skupine Upraviť kliknite na možnosť „Komprimovať obrázok“.
4. V dialógovom okne Komprimovať obrázky:
   • Ak chcete komprimovať všetky obrázky v zošite, zrušte začiarknutie políčka „Použiť iba na tento obrázok“.
   • Pre maximálnu kompresiu vyberte E -mail (96 ppi).
5. Kliknite na tlačidlo OK.
6. Uložte súbor.

Tým sa skomprimujú všetky obrázky a pomôže vám to zmenšiť veľkosť súboru zošita programu Excel.

Výsledky testu:

V programe Excel som použil možnosť komprimovať obrázok v jednom obrázku.

Nasledujú výsledky:

Použitím možnosti komprimovať obrázok sa veľkosť súboru zmenšila zo 445 KB na 43 KB (~ 90% zníženie veľkosti súboru).

To bude závisieť aj od obrazu. Obrázok, ktorý je už komprimovaný, nemusí viesť k výraznému zmenšeniu veľkosti.

Odstráňte medzipamäť Pivot, aby ste ušetrili miesto

Ak pracujete s kontingenčnými tabuľkami a bojujete s veľkými súbormi programu Excel, môže byť užitočné vedieť o kontingenčnej vyrovnávacej pamäti.

Keď vytvoríte kontingenčnú tabuľku pomocou množiny údajov, Excel automaticky vytvorí kontingenčnú vyrovnávaciu pamäť.

Kontingenčná pamäť je objekt, ktorý uchováva repliku zdroja údajov.

Aj keď ho nevidíte, je súčasťou zošita a je pripojený k kontingenčnej tabuľke. Keď vykonáte akékoľvek zmeny v kontingenčnej tabuľke, nepoužije zdroj údajov, ale použije kontingenčnú vyrovnávaciu pamäť.

Aj keď si myslíte, že ste priamo prepojení so zdrojovými údajmi, v skutočnosti po vykonaní zmien v kontingenčnej tabuľke získate prístup k kontingenčnej vyrovnávacej pamäti (a nie k zdrojovým údajom).

Teraz, keď vytvárate repliku množiny údajov, to zaberá určitý priestor.

Hneď ako vytvoríte kontingenčnú tabuľku, všimnete si, že veľkosť súboru programu Excel sa zvyšuje.

V uvedenom príklade sa veľkosť súboru dvojnásobne zvýši, akonáhle pridám kontingenčnú tabuľku.

Teraz existujú dva spôsoby, ako môžete pri práci s kontingenčnými tabuľkami zmenšiť veľkosť súboru.

1. Ponechajte zdrojové údaje a vymažte medzipamäť Pivot
2. Ponechajte Pivot Cache a vymažte zdrojové údaje

Pozrime sa na obe tieto techniky.

Ponechajte zdrojové údaje a vymažte medzipamäť

Keď ste s kontingenčnou tabuľkou hotoví a ukladáte ju, môžete vynútiť, aby Excel ukladal iba zdrojové údaje a výslednú kontingenčnú tabuľku, a nie kontingenčnú vyrovnávaciu pamäť.

Vďaka tomu bude váš súbor Excel ľahší a budete mať spôsob, ako znova vytvoriť medzipamäť Pivot, keď ju budete nabudúce potrebovať.

Nasleduje rozdiel vo veľkosti pri ukladaní súboru programu Excel s medzipamäťou Pivot a bez nej.

Tu sú kroky k NIE uložte vyrovnávaciu pamäť:

1. Vyberte ľubovoľnú bunku v kontingenčnej tabuľke.
2. Kliknite na kartu Analyzovať.
3. Kliknite na ikonu „Možnosti“ kontingenčnej tabuľky.
4. V dialógovom okne Možnosti kontingenčnej tabuľky kliknite na kartu Údaje.
5. V možnostiach údajov kontingenčnej tabuľky odškrtnúť „Uložiť zdrojové údaje pomocou súboru“. Táto možnosť zaisťuje, že sa kontingenčná vyrovnávacia pamäť neuloží pri uložení zošita (a pomáha pri znižovaní veľkosti súboru programu Excel).
6. Začiarknite možnosť - „Obnoviť údaje pri otvorení súboru“. Táto možnosť zaisťuje, že akonáhle otvoríte zošit, kontingenčná tabuľka sa obnoví a automaticky sa vygeneruje kontingenčná vyrovnávacia pamäť. Ak nezaškrtnete túto možnosť, budete musieť kontingenčnú tabuľku aktualizovať ručne, aby sa vygenerovala vyrovnávacia pamäť.
7. Kliknite na tlačidlo OK.

Keď teraz súbor uložíte pomocou kontingenčnej tabuľky, všimnete si zmenšenú veľkosť súboru.

Ponechajte vyrovnávaciu pamäť a odstráňte zdrojové údaje

Ďalším spôsobom, ako zmenšiť veľkosť súboru programu Excel, je odstrániť zdrojové údaje z kontingenčnej tabuľky a ponechať iba kontingenčnú vyrovnávaciu pamäť.

Funguje to tak, že kontingenčná vyrovnávacia pamäť je zrkadlovým obrazom vašich údajov a pri vykonávaní zmien v kontingenčnej tabuľke nemáte prístup k zdrojovým údajom, namiesto toho používate kontingenčnú vyrovnávaciu pamäť.

Nasledujú výsledky, keď som uložil súbor programu Excel bez zdrojových údajov.

Ak to chcete urobiť, odstráňte údaje (buď vymazaním údajov, alebo pracovným listom, ktorý údaje obsahuje).

Ak chcete získať zdrojové údaje späť, dvakrát kliknite na bunku „Celkom“ v kontingenčnej tabuľke a okamžite sa vygeneruje celá sada údajov v novom pracovnom hárku.

Komprimujte súbor (ZIP IT)

To je niečo, čo môžete urobiť mimo aplikácie Excel.

Keď skomprimujete súbor programu Excel, okamžite uvidíte zmenšenie veľkosti o približne 10-15%.

Tento súbor vo formáte ZIP potom môžete zdieľať s e -mailom a príjemca tohto súboru môže súbor rozbaliť a použiť zošit programu Excel.

Tu sú kroky na komprimáciu súboru Excel (pre Windows):

1. Kliknite pravým tlačidlom myši na súbor programu Excel
2. Ukážte kurzorom na možnosť Odoslať do.
3. Kliknite na možnosť „Komprimovaný (zazipovaný) priečinok“.

Vytvorí sa súbor ZIP pre vybratý zošit programu Excel.

Tiež si všimnete zmenšenie veľkosti súboru (ako je uvedené nižšie).

Výsledky testu

Touto technikou sa mi podarilo zmenšiť veľkosť súboru zošita programu Excel o 8%.

Táto metóda funguje lepšie pre súbory, ktoré sú v rozmedzí 20-50 MB. Ako sa veľkosť súboru zvyšuje, percentuálne zníženie nie je príliš vysoké.

Odstráňte formátovanie údajov

K veľkosti súboru môže prispieť aj formátovanie údajov, ako je napríklad použitie farby pozadia alebo pridanie okrajov alebo zmena štýlu písma.

Aj keď som si na základe svojich testov všimol, že to nemá veľký vplyv.

Nasledujú veľkosti dvoch zošitov programu Excel, jedného s 2,6 milióna údajových bodov (v jednom pracovnom hárku) a druhého s rovnakými údajmi, na ktoré sa však použilo formátovanie (farba bunky, orámovanie a kurzíva).

Výsledky testu

Môžete vidieť, že rozdiel medzi týmito dvoma súbormi nie je taký veľký (aj keď to má vplyv, keď máte veľa údajov).

Súbor s údajmi má 14,6 MB a súbor s údajmi a formátovaním je 14,9 MB.

Ak teda chcete zmenšiť veľkosť súboru zošita programu Excel, môžete sa pozrieť na formátovanie, ktoré nepotrebujete. Jeho odstránením môžete ušetriť miesto na disku.

Tu sú kroky na okamžité odstránenie celého formátovania pri zachovaní neporušených údajov:

1. Vyberte celú množinu údajov (z ktorej chcete odstrániť formátovanie).
2. Kliknite na kartu Domov.
3. V skupine Úpravy kliknite na „Vymazať“.
4. Kliknite na položku „Vymazať formáty“.
5. Uložte zošit.

Odstráňte podmienené formátovanie

Rovnako ako bežné formátovanie údajov, podmienené formátovanie tiež zvyšuje veľkosť súboru.

Na základe mojich testov som si však všimol, že rozdiel je menší (pokiaľ nemáte pre viacero hárkov s obrovskými množinami údajov aplikované viacero pravidiel).

Napriek tomu je dobré odstrániť podmienené formátovanie z akejkoľvek množiny údajov, kde to nepotrebujete.

Okrem veľkosti existuje ešte jeden dobrý dôvod na odstránenie podmieneného formátovania - RÝCHLOSŤ.

Podmienené formátovanie je nestále a vždy, keď dôjde k akejkoľvek zmene v pracovnom hárku, prepočíta sa. To môže viesť k pomalšiemu zošitu.

Výsledky testu

Vytvoril som súbor s 27,2 miliónmi dátových bodov a použil som naň 5 pravidiel podmieneného formátovania.

Rozdiel vo veľkosti sa zdá byť iba niekoľko kB.

Prečítajte si tiež: Tipy na zrýchlenie pomalých tabuliek programu Excel

Tipy na zvýšenie výkonu Excelu

Aj keď vám vyššie uvedené metódy pomôžu zmenšiť veľkosť súboru programu Excel, tu je niekoľko ďalších tipov na zlepšenie výkonu zošitov programu Excel:

1. Vyhnite sa používaniu prchavých vzorcov. Okrem zvýšenia veľkosti súboru vám nestále vzorce spomaľujú aj zošity. Medzi príklady volatilných funkcií patrí RAND, TODAY, OFFSET, INDIRECT atď.
2. Ak potrebujete namiesto použitia vzorcov vytvoriť súhrny veľkého súboru údajov, zistite, či namiesto toho môžete použiť kontingenčnú tabuľku. Kontingenčná tabuľka je v porovnaní so vzorcami rýchla a veľmi intuitívna.
3. Ak extrahujete údaje z viacerých zdrojov alebo kombinujete údaje z viacerých súborov, na získanie a transformáciu údajov použite Power Query. Údaje môžete uložiť ako pripojenie k Power Query, namiesto toho, aby ste ich ponechali v pracovnom hárku. Kontingenčnú tabuľku môžete ľahko vytvoriť pomocou pripojení vytvorených pomocou Power Query.
4. Premeňte svoje tabuľkové údaje na tabuľku programu Excel a použite tabuľku programu Excel vo vzorcoch. Vďaka tomu bude váš zošit pracovať rýchlejšie a vzorce vytvorené pomocou tabuľky štruktúrovanej tabuľky programu Excel sa budú ľahšie spravovať (pretože sú intuitívnejšie).
5. Vyhnite sa odkazovaniu na celý riadok alebo stĺpec vo vzorci. Vďaka tomu budú vaše vzorce efektívnejšie.
6. Ak máte v zošite veľa vzorcov, vypnite automatický výpočet a prepnite na manuálny výpočet. Ak to urobíte, zakaždým, keď dôjde k zmene v pracovných hárkoch, vzorce sa neprepočítajú. Túto zmenu môžete vykonať tak, že prejdete na kartu Vzorec a zmeníte „Možnosti výpočtu“.

Dúfam, že vám metóda a tipy v tomto návode pomôžu.

Čo by som ešte mohol pridať k tomuto tutoriálu, aby bol užitočnejší? Dajte mi prosím vedieť v sekcii komentárov.