8 způsobů, jak zmenšit velikost souboru aplikace Excel (které ve skutečnosti fungují)

O sešitech Excelu je známo, že se stávají bolestně pomalými a těžkými, protože je do něj přidáno více dat/vzorců/obrázků.

Někdy je snadné zbavit se přebytečného flabu a udržet si excelní velikost souboru pod kontrolou, a někdy tomu tak není.

A protože Excel není webový nástroj, většina z nás pracuje a spolupracuje zasíláním souborů aplikace Excel e-mailem. A velký soubor aplikace Excel, který váží 50 MB, lze těžko sdílet se svými kolegy/klienty.

V mém posledním zaměstnání jsme použili e-mailového klienta Lotus Notes, který je pomalý, a odesílání těžkých souborů aplikace Excel přes to bylo mučení.

I když neexistuje žádný kouzelný způsob, jak zmenšit velikost sešitu aplikace Excel, existuje několik technik, které můžete použít k omezení některých obrub.

Poznámka: Dopad těchto technik na zmenšení velikosti souboru aplikace Excel bude záviset na vašich datech. U někoho to může pomoci zmenšit velikost o 50% a u někoho to může skončit jen se zmenšením velikosti o 5%.

Tipy ke zmenšení velikosti souboru aplikace Excel

Níže jsou uvedeny techniky, které můžete použít ke zmenšení velikosti souboru sešitu aplikace Excel.

U každého tipu jsem provedl nějaké testování a přidal snímky, abych vám ukázal dopad na velikost souboru. Výsledky, které získáte, se budou lišit v závislosti na vašich datech a souborech.

Na základě sešitu můžete použít jeden nebo více z těchto tipů.

Odstraňte nepotřebné listy/data

Nejlepší řešení jsou často ta nejjednodušší.

A tato technika je spíše o zdravém rozumu a méně o jakékoli magii v Excelu.

Pokud máte sešit aplikace Excel, který obsahuje některé listy, které nejsou potřeba (a nepoužívají se v žádných vzorcích), podívejte se, jestli se jich můžete zbavit.

To má přímou souvislost s velikostí souboru aplikace Excel. Jakmile tedy odstraníte některé listy a sešit uložíte, okamžitě uvidíte zmenšenou velikost souboru.

Výsledky testů:

Vytvořil jsem soubor aplikace Excel s 10 listy, kde každý list má 2,6 milionu datových bodů (26 sloupců se 100 000 datovými body v každém sloupci).

Výsledná velikost tohoto sešitu je 143 MB (uložení a otevření trvá docela dlouho).

Když jsem nyní odstranil pět listů, velikost se zmenšila na polovinu (duh).

Jak vidíte, data, která máte v listu, mají přímou korelaci s velikostí souboru.

Převést na binární formát (XLSB)

Víte, že pouhým uložením souboru aplikace Excel ve formátu XLSB lze zmenšit velikost souboru?

Ano, je to pravda.

Jediné, co musíte udělat, je při ukládání sešitu použít formát .xlsb.

Zde jsou kroky, jak toho dosáhnout:

1. Klikněte na kartu Soubor.
2. Klikněte na Uložit jako.
3. Klikněte na Procházet.
4. V dialogovém okně Uložit jako změňte typ souboru na binární sešit aplikace Excel (.xlsb)

Výsledky testů

Měl jsem sešit aplikace Excel, který zabíral 14,6 MB místa.

Po převodu stejného souboru do formátu XLSB se mi podařilo zmenšit velikost na 10,4 MB (zmenšení velikosti o ~ 28%).

Co je XLSB?

Když uložíte soubor aplikace Excel (.xlsx nebo .xlsm), uloží se ve formátu XML.

Pokud například změním příponu souboru XLSX na ZIP a poté ji otevřu, zobrazí se toto.

A pokud dále přejdu do složky „xl“ a poté přejdu do složky listu, zobrazí se mi listy v tomto sešitu (a tyto listy jsou uloženy jako dokument XML).

Nyní udělejme totéž se souborem XLSB.

Nejprve tedy změním příponu souboru na ZIP, poté ji otevřu a najdu sheet1. Zde získáte:

Všimněte si, že XLSX i XLSB ukládají list v jiném formátu.

XLSX/XLSM jej uloží jako soubor XML a XLSB jej uloží jako binární (.bin) soubor.

A aniž by to bylo příliš technické, soubory XML jsou ve srovnání s binárním souborem velké.

Když tedy sešit aplikace Excel uložíte ve formátu XLSB, velikost souboru se zmenší. V našem výše uvedeném příkladu bylo zmenšení velikosti ~ 30%.

XLSB Vs XLSX/XLSM

Pokud jde o použití formátu XLSB, je zmenšení velikosti obrovským přínosem. Při práci s formátem XLSB ale musíte být trochu opatrní.

Výhody formátu XLSB:

1. Snižuje velikost souboru aplikace Excel. To může být užitečné, pokud máte velké soubory a chcete je sdílet prostřednictvím e -mailu. Velikost můžete rychle zmenšit uložením do XLSB.
2. Soubory XLSB se obvykle otevírají a ukládají rychleji než soubory XML. Na základě omezených testů, které jsem provedl, se soubory XLSB otevíraly a ukládaly o 25%-50% rychleji. Tento rozdíl je patrný pouze u velkých souborů aplikace Excel. S menšími soubory jsou XLSB i XLSX rychlé.

Přestože XLSB vypadá skvěle, existuje několik důvodů, proč byste měli používat soubory XLSX/XLSM:

1. U formátu XLSB nemáte žádný způsob, jak zjistit, zda má makro nebo ne. Díky tomu je riskantnější, protože ho někdo může použít ke spuštění škodlivého kódu.
2. Soubory XLSB jsou kódovány v proprietárním formátu bin, zatímco XML je formát souboru čitelného z otevřeného zdroje. To znamená, že pokud v Excelu používáte nástroje třetích stran, je lepší použít formát XML.
3. Power Query nemůže číst data ze souboru XLSB. Pokud tedy používáte Power Query, je lepší mít data ve formátech XLSX/XLSM.

Obecně platí, že pokud je velikost vašeho souboru malá (méně než 5 MB), je lepší držet se formátů XLSX/XLSM.

Na základě toho, co jsem slyšel od lidí a četl na mnoha fórech, mnoho lidí dává přednost použití formátu XLSB jako formátu souboru, pokud jde o používání Excelu.

Zde je dobrý článek o porozumění formátu XLSB.

Odstraňte nepotřebné vzorce

Příliš mnoho vzorců bude blokovat velikost souboru sešitu.

Pokud tedy máte datovou sadu, kterou potřebujete, ale nepotřebujete v ní vzorce, je lepší tyto vzorce převést na hodnoty.

Zde jsou kroky k rychlému převodu vzorců na hodnoty v aplikaci Excel:

1. Vyberte celý list/datovou sadu.
2. zmáčkni F5 klíč.
3. V dialogovém okně Přejít na klikněte na „Speciální“.
4. V dialogovém okně Přejít na speciální vyberte Vzorce.
5. Klikněte na OK.

Výše uvedené kroky by vybrali všechny buňky, které obsahují vzorec.

Nyní pomocí následujících kroků převeďte tyto vzorce na hodnoty:

1. Zkopírujte vybrané buňky (ovládání + C)
2. Klikněte na kartu Domů.
3. Ve skupině Schránka klikněte na Vložit.
4. Klikněte na ikonu Vložit hodnotu.

Výše uvedené kroky by převedly všechny buňky se vzorci na hodnoty.

Výsledky testů

Vytvořil jsem soubor aplikace Excel s 2,7 miliony datových bodů (1048576*26) a poté přidal tři sloupce se vzorci (SUM, COUNT a PRŮMĚR).

Poté jsem tyto vzorce převedl na hodnoty a soubor uložil pod jiným názvem.

Níže je uveden výsledek:

Soubor bez vzorců má o ~ 8% menší velikost (a všimněte si, že jsem měl pouze 3 sloupce vzorců).

V případě, že máte mnoho vzorců, může to přidat na velikosti.

POZNÁMKA: Těkavé vzorce mohou vaši velikost souboru opravdu nafouknout. V rámci testu, když jsem nahradil vzorec SUM těkavým vzorcem (OFFSET), to vedlo ke skoku ve velikosti souboru (z 186 MB na 198 MB). Pokud tedy máte těkavé vzorce, které můžete nahradit těkavými vzorci, nebo je můžete převést na hodnoty, pomůže vám to zmenšit velikost souboru.

Komprimovat obrázky (před a po odeslání)

Pokud pracujete se soubory aplikace Excel, které obsahují obrázky, musíte tyto obrázky optimalizovat a ujistit se, že velikost je menší.

Problém s obrázky je ten, že jak je komprimujete, dochází také ke snížení kvality, což možná nebudete chtít.

Při práci s obrázky je nejlepší způsob, jak snížit velikost souboru aplikace Excel, komprimovat obrázky před vložením do aplikace Excel.

Dobrým online nástrojem k tomu je TinyPNG (nebo můžete použít Photoshop nebo jiný podobný nástroj).

Je to bezplatný nástroj, který dokáže zmenšit velikost obrázku až o 90% a přitom si zachovává slušnou kvalitu obrazu.

Níže je snímek obrazovky, kde se mi podařilo komprimovat obrázek o velikosti 3,9 MB na 331 KB s minimální ztrátou kvality.

Jakmile tyto komprimované obrázky nahrajete do Excelu, můžete je dále komprimovat pomocí vestavěné možnosti v Excelu.

Zde jsou kroky pro komprimaci obrázku v aplikaci Excel:

1. Vyberte obrázek, který chcete komprimovat.
2. Klikněte na kartu Formát nástrojů obrázku. Toto je kontextová karta, která se zobrazí pouze po kliknutí na obrázek.
3. Ve skupině Upravit klikněte na možnost „Komprimovat obrázek“.
4. V dialogovém okně Komprimovat obrázky:
   • Pokud chcete zkomprimovat všechny obrázky v sešitu, zrušte zaškrtnutí políčka „Použít pouze na tento obrázek“.
   • Pro maximální kompresi vyberte E -mail (96 ppi).
5. Klikněte na OK.
6. Uložte soubor.

To zkomprimuje všechny obrázky a pomůže vám zmenšit velikost souboru v sešitu aplikace Excel.

Výsledky testů:

Použil jsem možnost komprimovat obrázek v aplikaci Excel pomocí jednoho obrázku.

Níže jsou uvedeny výsledky:

Pomocí možnosti komprimovat obrázek se velikost souboru zmenšila ze 445 KB na 43 KB (~ 90% zmenšení velikosti souboru).

To by také záleželo na obrázku. Obrázek, který je již komprimovaný, nemusí vést k obrovské redukci velikosti.

Ušetřete místo odstraněním mezipaměti Pivot

Pokud pracujete s kontingenčními tabulkami a bojujete s velkými soubory aplikace Excel, může být užitečné vědět o kontingenční mezipaměti.

Když vytvoříte kontingenční tabulku pomocí datové sady, Excel automaticky vytvoří kontingenční mezipaměť.

Kontingenční mezipaměť je objekt, který obsahuje repliku zdroje dat.

I když to nevidíte, je součástí sešitu a je připojeno k kontingenční tabulce. Když provedete jakékoli změny v kontingenční tabulce, nepoužívá zdroj dat, ale používá kontingenční mezipaměť.

I když si myslíte, že jste přímo propojeni se zdrojovými daty, ve skutečnosti přistupujete ke kontingenční mezipaměti (a nikoli ke zdrojovým datům) při provádění změn v kontingenční tabulce.

Nyní, když vytváříte repliku datové sady, to zabere nějaký prostor.

Jakmile tedy vytvoříte kontingenční tabulku, všimnete si, že velikost souboru aplikace Excel se zvyšuje.

Ve výše uvedeném příkladu, jakmile přidám kontingenční tabulku, velikost souboru se zdvojnásobí.

Při práci s kontingenčními tabulkami nyní existují dva způsoby, jak můžete zmenšit velikost souboru.

1. Uchovejte zdrojová data a vymažte mezipaměť Pivot
2. Ponechejte mezipaměť Pivot a odstraňte zdrojová data

Podívejme se na obě tyto techniky.

Ponechejte zdrojová data a odstraňte mezipaměť Pivot

Když jste s kontingenční tabulkou hotovi a ukládáte ji, můžete vynutit aplikaci Excel, aby ukládala pouze zdrojová data a výslednou kontingenční tabulku, a nikoli kontingenční mezipaměť.

Díky tomu bude váš soubor aplikace Excel lehčí a budete mít způsob, jak znovu vytvořit mezipaměť Pivot, když ji budete příště potřebovat.

Níže je rozdíl ve velikosti při ukládání souboru aplikace Excel s mezipamětí Pivot a bez ní.

Zde jsou kroky k NE uložte mezipaměť Pivot:

1. Vyberte libovolnou buňku v kontingenční tabulce.
2. Klikněte na kartu Analyzovat.
3. Klikněte na ikonu „Možnosti“ kontingenční tabulky.
4. V dialogovém okně Možnosti kontingenční tabulky klikněte na kartu Data.
5. V možnostech dat kontingenční tabulky zrušte zaškrtnutí „Uložit zdrojová data pomocí souboru“. Tato možnost zajišťuje, že se při uložení sešitu neuloží mezipaměť Pivot (a pomáhá při zmenšování velikosti souboru aplikace Excel).
6. Zaškrtněte možnost - „Obnovit data při otevření souboru“. Tato možnost zajišťuje, že jakmile otevřete sešit, kontingenční tabulka se aktualizuje a kontingenční mezipaměť se automaticky vygeneruje. Pokud tuto možnost nezaškrtnete, budete muset kontingenční tabulku aktualizovat ručně, aby se generovala kontingenční mezipaměť.
7. Klikněte na OK.

Když nyní soubor uložíte pomocí kontingenční tabulky, všimnete si zmenšené velikosti souboru.

Ponechejte mezipaměť Pivot a odstraňte zdrojová data

Dalším způsobem, jak zmenšit velikost souboru aplikace Excel, je odstranit zdrojová data kontingenční tabulky a ponechat pouze kontingenční mezipaměť.

Funguje to tak, že kontingenční mezipaměť je zrcadlovým obrazem vašich dat a při provádění změn v kontingenční tabulce nepřistupujete ke zdrojovým datům, místo toho používáte kontingenční mezipaměť.

Níže jsou uvedeny výsledky, když jsem uložil soubor aplikace Excel bez zdrojových dat.

Chcete -li to provést, stačí odstranit data (buď odstraněním dat nebo listu, který data obsahuje).

Pokud chcete získat zdrojová data zpět, dvakrát klikněte na buňku „Celkový součet“ kontingenční tabulky a okamžitě vygeneruje celou sadu dat v novém listu.

Komprimujte soubor (ZIP IT)

To je něco, co můžete udělat mimo aplikaci Excel.

Když komprimujete soubor aplikace Excel, okamžitě uvidíte zmenšení velikosti přibližně o 10–15%.

Tento zipovaný soubor pak můžete sdílet prostřednictvím e -mailu a příjemce může tento soubor rozbalit a použít sešit aplikace Excel.

Zde jsou kroky ke komprimaci souboru Excel (pro Windows):

1. Klepněte pravým tlačítkem na soubor aplikace Excel
2. Najeďte kurzorem na možnost Odeslat.
3. Klikněte na možnost „Komprimovaná (komprimovaná) složka“.

Tím se vytvoří soubor zip pro vybraný sešit aplikace Excel.

Také si všimnete zmenšení velikosti souboru (jak je uvedeno níže).

Výsledky testů

Touto technikou se mi podařilo zmenšit velikost souboru sešitu aplikace Excel o 8%.

Tato technika funguje lépe u souborů, které jsou v rozsahu 20–50 MB. S rostoucí velikostí souboru není procentuální redukce příliš vysoká.

Odebrat formátování dat

Formátování dat, jako je použití barvy pozadí nebo přidání okrajů nebo změna stylu písma, to vše může přidat na velikosti souboru.

Ačkoli jsem si na základě svých testů všiml, že to nemá velký dopad.

Níže jsou uvedeny velikosti dvou sešitů aplikace Excel, jednoho s 2,6 miliony datových bodů (v jednom listu) a druhého se stejnými daty, ale s použitým formátováním (barva buňky, ohraničení a kurzíva).

Výsledky testů

Můžete vidět, že rozdíl mezi těmito dvěma soubory není tak velký (i když to má vliv, když máte hodně dat).

Soubor s daty je 14,6 MB a soubor s daty a formátováním je 14,9 MB.

Pokud tedy chcete zmenšit velikost souboru v sešitu aplikace Excel, můžete vyhledat formátování, které nepotřebujete. Jeho odebráním můžete ušetřit místo na disku.

Zde jsou kroky k okamžitému odebrání veškerého formátování při zachování neporušených dat:

1. Vyberte celou datovou sadu (ze které chcete odebrat formátování).
2. Klikněte na kartu Domů.
3. Ve skupině Úpravy klikněte na „Vymazat“.
4. Klikněte na „Vymazat formáty“.
5. Uložte sešit.

Odebrat podmíněné formátování

Stejně jako běžné formátování dat také podmíněné formátování zvyšuje velikost souboru.

Na základě mých testů jsem si však všiml, že rozdíl je menší (pokud nemáte více pravidel aplikovaných na více listů s obrovskými datovými sadami).

Přesto je dobré odstranit podmíněné formátování z jakékoli datové sady, kde ji nepotřebujete.

Kromě velikosti existuje další dobrý důvod pro odstranění podmíněného formátování - RYCHLOST.

Podmíněné formátování je nestálé a kdykoli dojde k nějaké změně v listu, přepočítá se. To může vést k pomalejšímu sešitu.

Výsledky testů

Vytvořil jsem soubor s 27,2 miliony datových bodů a použil na něj 5 pravidel podmíněného formátování.

Rozdíl ve velikosti se zdá být jen několik kB.

Čtěte také: Tipy na rychlé zpomalení tabulek aplikace Excel

Tipy pro zvýšení výkonu aplikace Excel

Výše uvedené metody vám sice pomohou zmenšit velikost souboru aplikace Excel, ale zde je několik dalších tipů, jak zlepšit výkon sešitů aplikace Excel:

1. Nepoužívejte těkavé vzorce. Kromě zvýšení velikosti souboru vám těkavé vzorce také zpomalí sešity. Mezi příklady nestálých funkcí patří RAND, TODAY, OFFSET, INDIRECT atd.
2. Pokud potřebujete vytvořit souhrny velké datové sady, místo použití vzorců se podívejte, jestli místo toho můžete použít kontingenční tabulku. Kontingenční tabulka je ve srovnání se vzorci rychlá a velmi intuitivní.
3. Pokud extrahujete data z více zdrojů nebo kombinujete data z více souborů, použijte k získání a transformaci dat Power Query. Data můžete uložit jako připojení Power Query, místo abyste je ponechali v listu. Kontingenční tabulku můžete snadno vytvořit pomocí připojení vytvořených pomocí Power Query.
4. Převeďte svá tabulková data na excelovou tabulku a použijte excelovou tabulku ve vzorcích. Díky tomu bude váš sešit fungovat rychleji a vzorce vytvořené pomocí strukturovaných odkazů v tabulce Excel se snadněji spravují (protože jsou intuitivnější).
5. Vyhněte se odkazování na celý řádek nebo sloupec ve vzorci. Díky tomu budou vaše vzorce účinnější.
6. Pokud máte v sešitu hodně vzorců, vypněte Automatický výpočet a přepněte na ruční výpočet. Tímto způsobem se vzorce pokaždé, když dojde ke změně v listech, nepočítají. Tuto změnu můžete provést tak, že přejdete na kartu Vzorec a změníte „Možnosti výpočtu“.

Doufám, že vám metoda a tipy v tomto tutoriálu budou užitečné.

Co jiného bych mohl přidat k tomuto kurzu, aby byl užitečnější? Dejte mi prosím vědět v sekci komentáře.