8 módszer az Excel fájlméretének csökkentésére (amelyek valóban működnek)

Az Excel munkafüzetekről ismert, hogy fájdalmasan lassúak és nehézkessé válnak, mivel több adat/képlet/kép kerül hozzáadásra.

Néha könnyű megszabadulni az extra flabtól, és kordában tartani az Excel fájlméretét, néha pedig nem.

És mivel az Excel nem webalapú eszköz, a legtöbben úgy dolgozunk és együttműködünk, hogy Excel fájlokat küldünk e-mailben. Egy nagyméretű, 50 MB -os Excel fájlt pedig nehéz megosztani kollégáival/ügyfeleivel.

Utolsó munkám során Lotus Notes e-mail klienst használtunk, ami csigalassú, és nehéz Excel fájlokat küldött kínzásnak.

Bár nincs varázslatos módszer az Excel -munkafüzet méretének csökkentésére, van néhány módszer, amellyel le lehet vágni néhány flab -ot.

Megjegyzés: Ezeknek a módszereknek a hatása az Excel fájlméret csökkentésére az Ön adataitól függ. Egyeseknél ez segíthet a méret 50% -os csökkentésében, egyeseknél pedig csak 5% -kal.

Tippek az Excel fájlméretének csökkentéséhez

Az alábbiakban bemutatjuk azokat a technikákat, amelyekkel csökkentheti az Excel munkafüzet fájlméretét.

Minden tipp esetében elvégeztem néhány tesztet, és hozzáadtam a pillanatfelvételeket, amelyek megmutatják a fájlméretre gyakorolt ​​hatást. A kapott eredmények adatoktól és fájloktól függően változhatnak.

A munkafüzet alapján használhat egy vagy több tippet.

Távolítsa el a felesleges munkalapokat/adatokat

A legjobb megoldások gyakran a legegyszerűbbek.

És ez a technika inkább józan ész, és kevésbé az Excel varázslása.

Ha olyan Excel -munkafüzete van, amely néhány munkalapot tartalmaz, amelyekre nincs szükség (és amelyeket nem használnak képletekben), nézze meg, hogy megszabadulhat -e azoktól.

Ennek közvetlen összefüggése van az Excel fájlméretével. Tehát amint töröl néhány munkalapot, és elmenti a munkafüzetet, azonnal megjelenik egy csökkentett fájlméret.

Vizsgálati eredmények:

Létrehoztam egy Excel fájlt 10 munkalappal, ahol minden munkalap 2,6 millió adatponttal rendelkezik (26 oszlop 100 000 adatponttal minden oszlopban).

Ennek a munkafüzetnek a mérete 143 MB (és elég sok időt vesz igénybe a mentés és a megnyitás).

Most, amikor öt lapot töröltem, a méret felére csökkent (duh).

Amint láthatja, a munkalapon lévő adatok közvetlen összefüggésben vannak a fájlmérettel.

Konvertálás bináris formátumba (XLSB)

Tudja, hogy az Excel fájl XLSB formátumban történő mentésével csökkentheti a fájlméretet?

Igen ez igaz.

Csak annyit kell tennie, hogy a munkafüzet mentése közben használja az .xlsb formátumot.

Ehhez tegye a következő lépéseket:

1. Kattintson a Fájl fülre.
2. Kattintson a Mentés másként gombra.
3. Kattintson a Tallózás gombra.
4. A Mentés másként párbeszédpanelen módosítsa a fájltípust Excel bináris munkafüzetre (.xlsb)

Vizsgálati eredmények

Volt egy Excel munkafüzetem, amely 14,6 MB helyet foglalt el.

Miután ugyanazt a fájlt XLSB formátumba konvertáltam, le tudtam csökkenteni a méretet 10,4 MB -ra (~ 28%-os méretcsökkenés).

Mi az XLSB?

Excel fájl (.xlsx vagy .xlsm) mentésekor az XML formátumban kerül mentésre.

Például, ha az XLSX fájl fájlkiterjesztését ZIP -re változtatom, majd megnyitom, akkor ezt kapom.

És ha továbbmegyek az „xl” mappába, majd a munkalap mappába, akkor megmutatja az adott munkafüzet munkalapjait (és ezeket a munkalapokat XML dokumentumként menti el).

Most tegyük ugyanezt az XLSB fájllal.

Tehát először a fájlkiterjesztést ZIP -re cserélem, majd megnyitom és megkeresem a sheet1 -et. Íme, amit kapsz:

Észre fogja venni, hogy az XLSX és az XLSB is más formátumban menti a munkalapot.

Az XLSX/XLSM XML fájlként menti, az XLSB pedig bináris (.bin) fájlként.

És anélkül, hogy túlságosan technikussá válna, az XML fájlok nagy méretűek egy bináris fájlhoz képest.

Tehát amikor az Excel munkafüzetet XLSB formátumban menti, a fájlméret csökken. A fenti példánkban a méretcsökkenés ~ 30%volt.

XLSB vs XLSX/XLSM

Ami az XLSB formátum használatát illeti, a méretcsökkentés óriási előny. Az XLSB formátummal való munkavégzés során azonban kissé óvatosnak kell lennie.

Az XLSB formátum előnyei:

1. Csökkenti az Excel fájl méretét. Ez akkor lehet hasznos, ha nagy fájlokkal rendelkezik, és e -mailben szeretné megosztani. Gyorsan csökkentheti a méretet, ha XLSB formátumban menti.
2. Az XLSB fájlok általában gyorsabban nyílnak meg és menthetők, mint az XML fájlok. Az elvégzett korlátozott tesztek alapján az XLSB fájlok 25-50% -kal gyorsabban nyíltak meg és takarítottak meg. Ez a különbség csak nagy Excel fájlokban észlelhető. Kisebb fájlok esetén mind az XLSB, mind az XLSX gyors.

Bár az XLSB jól néz ki, néhány okból érdemes ragaszkodnia az XLSX/XLSM fájlokhoz:

1. Az XLSB formátumban nem tudhatja, hogy van -e makrója vagy sem. Ez kockázatosabbá teszi, mivel valaki rosszindulatú kód végrehajtására használhatja.
2. Az XLSB fájlok saját bin fájlformátumban vannak kódolva, míg az XML nyílt forráskódú olvasható fájlformátum. Ez azt jelenti, hogy ha harmadik féltől származó eszközöket használ az Excel segítségével, akkor jobb, ha XML formátumot használ.
3. A Power Query nem tud adatokat olvasni egy XLSB fájlból. Tehát ha a Power Query -t használja, akkor jobb, ha az adatokat XLSX/XLSM formátumban tárolja.

Általános szabály, hogy ha a fájl mérete kicsi (kevesebb, mint 5 MB), akkor jobb, ha ragaszkodik az XLSX/XLSM formátumokhoz.

Az emberektől hallottam és sok fórumon olvasottak alapján sokan az XLSB -t használják fájlformátumként az Excel használatakor.

Itt van egy jó cikk az XLSB formátum megértéséről.

Távolítsa el a felesleges képleteket

Túl sok képlet felduzzasztja a munkafüzet fájlméretét.

Tehát ha van egy adatkészlete, amire szüksége van, de nincs szüksége a benne lévő képletekre, akkor jobb, ha ezeket a képleteket értékekké alakítja át.

Az alábbi lépések segítségével gyorsan átalakíthatja a képleteket Excel értékekké:

1. Válassza ki a teljes munkalapot/adatkészletet.
2. megnyomni a F5 kulcs.
3. Az Ugrás párbeszédpanelen kattintson a „Speciális” gombra.
4. Az Ugrás a speciális párbeszédpanelen válassza a Képletek lehetőséget.
5. Kattintson az OK gombra.

A fenti lépések kiválasztják az összes cellát, amelyben képlet található.

Most kövesse az alábbi lépéseket, hogy ezeket a képleteket értékekké alakítsa át:

1. Másolja a kijelölt cellákat (Control + C)
2. Kattintson a Kezdőlap fülre.
3. A Vágólap csoportban kattintson a Beillesztés gombra.
4. Kattintson az Érték beillesztése ikonra.

A fenti lépések minden képlettel rendelkező cellát értékké alakítanának.

Vizsgálati eredmények

Létrehoztam egy Excel fájlt 2,7 millió adatponttal (1048576*26), majd hozzáadtam három oszlopot képletekkel (SUM, COUNT és AVERAGE).

Ezután ezeket a képleteket értékekre konvertáltam, és más néven mentettem a fájlt.

Alább az eredmény:

A képletek nélküli fájl mérete ~ 8% -kal kisebb (és vegye figyelembe, hogy csak 3 oszlopos képlettel rendelkeztem).

Ha sok képlete van, növelheti a méretet.

JEGYZET: Az illékony képletek valóban felduzzaszthatják a fájlméretet. A teszt részeként, amikor a SUM képletet egy illékony képletre (OFFSET) cseréltem, a fájlméret ugrásához vezetett (186 MB -ról 198 MB -ra). Tehát ha olyan illékony képletekkel rendelkezik, amelyeket nem illékony képletekkel helyettesíthet, vagy át tudja alakítani ezeket értékekké, akkor segít csökkenteni a fájlméretet.

Képek tömörítése (feltöltés előtt és után)

Ha olyan Excel fájlokkal dolgozik, amelyek képeket tartalmaznak, akkor optimalizálnia kell ezeket a képeket, és győződjön meg arról, hogy a méret kisebb.

A képekkel az a probléma, hogy amikor ezeket tömöríti, a minőség is csökken, amit nem kívánhat.

Amikor képekkel dolgozik, a legjobb módja annak, hogy az Excel fájl mérete alacsony legyen, ha tömöríti a képeket, mielőtt beilleszti őket az Excelbe.

Egy jó online eszköz erre a TinyPNG (vagy használhatja a Photoshopot vagy bármely más hasonló eszközt).

Ez egy ingyenes eszköz, amely akár 90% -kal is csökkentheti a kép méretét, és továbbra is megfelelő képminőséget biztosít.

Az alábbiakban látható a képernyőkép, ahol képes voltam tömöríteni egy 3,9 MB -ról 331 KB méretű képre minimális minőségromlással.

Miután feltöltötte ezeket a tömörített képeket az Excelbe, tovább tömörítheti az Excel beépített opciójával.

Íme a képek tömörítésének lépései Excelben:

1. Válassza ki a tömöríteni kívánt képet.
2. Kattintson a Képeszközök formátum fülre. Ez egy kontextuális lap, amely csak akkor jelenik meg, ha rákattint a képre.
3. A Beállítás csoportban kattintson a „Kép tömörítése” lehetőségre.
4. A Képek tömörítése párbeszédpanelen:
   • Törölje az „Alkalmazás csak erre a képre” jelölést, ha tömöríteni szeretné a munkafüzet összes képét.
   • A maximális tömörítéshez válassza az E -mail (96 ppi) lehetőséget.
5. Kattintson az OK gombra.
6. Mentse a fájlt.

Ez tömöríti az összes képet, és segít csökkenteni az Excel -munkafüzet fájlméretét.

Vizsgálati eredmények:

Az Excel tömörítési képét használtam egy kép használatával.

Az alábbiakban az eredmények láthatók:

A képtömörítés opció használatával a fájlméret 445 KB -ról 43 KB -ra csökkent (a fájlméret ~ 90% -os csökkenése).

Ez a képtől is függne. A már tömörített kép nem vezethet hatalmas méretcsökkenéshez.

Távolítsa el a Pivot gyorsítótárat a helytakarékosság érdekében

Ha Pivot táblákkal dolgozik, és nagy Excel fájlokkal küzd, a Pivot gyorsítótár ismerete hasznos lehet.

Amikor pivot táblát hoz létre adathalmaz használatával, az Excel automatikusan létrehozza a kimutatási gyorsítótárat.

A pivot gyorsítótár egy olyan objektum, amely az adatforrás másolatát tartalmazza.

Bár nem látja, a munkafüzet része, és kapcsolódik a Pivot Table -hez. Amikor bármilyen módosítást hajt végre a kimutatástáblában, az nem az adatforrást használja, hanem a kimutatási gyorsítótárat.

Bár úgy gondolja, hogy közvetlenül kapcsolódik a forrásadatokhoz, valójában a pivot -gyorsítótárhoz (és nem a forrásadatokhoz) fér hozzá, amikor módosítja a kimutatástáblát.

Most, hogy az adatkészlet másolatát hozza létre, ez helyet foglal.

Tehát amint létrehoz egy Pivot táblát, észre fogja venni, hogy az Excel fájl mérete nő.

A fenti példában, amint hozzáadom a Pivot táblát, a fájl mérete kétszeresére nő.

Most kétféleképpen csökkentheti a fájlméretet, amikor Pivot táblákkal dolgozik.

1. Tartsa meg a forrásadatokat, és törölje a Pivot -gyorsítótárat
2. Tartsa meg a Pivot gyorsítótárat, és törölje a forrásadatokat

Nézzük mindkét technikát.

Tartsa meg a forrásadatokat, és törölje a Pivot -gyorsítótárat

Ha befejezte a kimutatástáblát, és elmenti, kényszerítheti az Excel -t, hogy csak a forrásadatokat és az ebből adódó kimutatást mentse, a Pivot -gyorsítótárat nem.

Ezáltal az Excel fájl könnyebb lesz, és lehetősége van a Pivot gyorsítótár újbóli létrehozására, amikor legközelebb szüksége lesz rá.

Az alábbiakban látható a méretbeli különbség, ha Excel fájlt ment Pivot gyorsítótárral és anélkül.

Itt vannak a lépések NEM mentse a Pivot gyorsítótárat:

1. Válassza ki a cellákat a kimutatástáblában.
2. Kattintson az Elemzés fülre.
3. Kattintson a pivot tábla „Beállítások” ikonjára.
4. A Pivot Table Options párbeszédpanelen kattintson az Adatok fülre.
5. A Pivot Table Data beállításaiban törölje a jelölést „Forrásadatok mentése fájllal”. Ez az opció biztosítja, hogy a Pivot -gyorsítótár ne kerüljön mentésre a munkafüzet mentésekor (és segít az Excel fájlméretének csökkentésében).
6. Jelölje be az „Adatok frissítése a fájl megnyitásakor” lehetőséget. Ez az opció biztosítja, hogy amint megnyitja a munkafüzetet, a Pivot táblázat frissül, és a Pivot Cache automatikusan létrejön. Ha nem jelöli be ezt az opciót, akkor a Pivot -táblát manuálisan kell frissítenie a Pivot -gyorsítótár létrehozásához.
7. Kattintson az OK gombra.

Most, amikor a Pivot táblával menti a fájlt, észreveszi a csökkentett fájlméretet.

Tartsa meg a Pivot gyorsítótárat, és törölje a forrásadatokat

Egy másik módja az Excel fájlméret csökkentésének, ha törli a Pivot tábla forrásadatait, és csak a Pivot gyorsítótárat tartja meg.

Ez úgy működik, hogy a Pivot Cache az adatok tükörképe, és nem fér hozzá a forrásadatokhoz, amikor módosítja a kimutatástáblát, hanem a Pivot Cache -t használja.

Az alábbiakban azok az eredmények láthatók, amikor az Excel fájlt a forrásadatok nélkül mentettem.

Ehhez csak törölje az adatokat (vagy az adatok törlésével, vagy az adatokat tartalmazó munkalappal).

Ha vissza szeretné kapni a forrásadatokat, kattintson duplán a Pivot tábla „Összesített” cellájára, és azonnal létrehozza a teljes adathalmazt egy új munkalapon.

A fájl tömörítése (ZIP IT)

Ezt az Excel alkalmazáson kívül is megteheti.

Amikor Excel fájlt tömörít, azonnal körülbelül 10-15% -os méretcsökkenést lát.

Ezt követően megoszthatja ezt a tömörített fájlt e -mailben, és a kapott személy kibonthatja a fájlt, és használhatja az Excel munkafüzetet.

Az alábbi lépésekkel tömörítheti az Excel fájlt (Windows esetén):

1. Kattintson a jobb gombbal az Excel fájlra
2. Vigye a kurzort a Küldés lehetőségre.
3. Kattintson a „Tömörített (zip) mappa” lehetőségre.

Ezzel létrehoz egy tömörített fájlt a kiválasztott Excel munkafüzethez.

Észre fogja venni a fájlméret csökkenését is (lásd alább).

Vizsgálati eredmények

Ezzel a technikával 8%-kal tudtam csökkenteni az Excel munkafüzet fájlméretét.

Ez a technika jobban működik a 20-50 MB tartományú fájloknál. A fájl méretének növekedésével a százalékos csökkenés nem túl nagy.

Távolítsa el az adatformázást

Az adatformázás, például a háttérszín alkalmazása, a szegélyek hozzáadása vagy a betűtípus megváltoztatása mind növelheti a fájlméretet.

Bár a tesztek alapján észrevettem, hogy nincs nagy hatása.

Az alábbiakban két Excel munkafüzet mérete látható, az egyik 2,6 millió adatponttal (egy munkalapon), a másik pedig ugyanazokkal az adatokkal, de formázással (cellaszín, szegély és dőlt betű).

Vizsgálati eredmények

Láthatja, hogy a különbség a két fájl között nem olyan nagy (bár ez hatással van, ha sok adat van).

Az adatokkal rendelkező fájl mérete 14,6 MB, az adatokkal és formázással rendelkező fájl pedig 14,9 MB.

Tehát ha csökkenteni szeretné az Excel munkafüzet fájlméretét, keresse meg a szükséges formázást. Eltávolítása lemezterületet takaríthat meg.

Íme a lépések az összes formázás azonnali eltávolításához, miközben az adatok sértetlenek maradnak:

1. Válassza ki a teljes adatkészletet (amelyből el szeretné távolítani a formázást).
2. Kattintson a Kezdőlap fülre.
3. A Szerkesztés csoportban kattintson a „Törlés” gombra.
4. Kattintson a „Formátumok törlése” gombra.
5. Mentse el a munkafüzetet.

Feltételes formázás eltávolítása

A szokásos adatformázáshoz hasonlóan a feltételes formázás is növeli a fájl méretét.

A tesztek alapján azonban észrevettem, hogy a különbség kisebb (kivéve, ha több szabályt alkalmaz több, hatalmas adatkészlettel rendelkező lapra).

Mindazonáltal jó ötlet eltávolítani a feltételes formázást minden olyan adatkészletből, ahol nincs rá szüksége.

A méreten kívül van még egy jó ok a feltételes formázás eltávolítására - SPEED.

A feltételes formázás ingadozó, és amikor bármilyen változás történik a munkalapon, újraszámítja. Ez lassabb munkafüzethez vezethet.

Vizsgálati eredmények

Létrehoztam egy fájlt 27,2 millió adatponttal, és 5 feltételes formázási szabályt alkalmaztam rá.

Úgy tűnik, hogy a méretbeli különbség csak néhány KB.

Is Olvass: Tippek a lassú Excel -táblázatok gyorsabbá tételéhez

Excel Performance Booster tippek

Bár a fenti módszerek segítenek csökkenteni az Excel fájlméretét, itt van néhány további tipp az Excel munkafüzetek teljesítményének javítására:

1. Kerülje az illékony képletek használatát. A fájlméret növelése mellett az illékony képletek lassítják a munkafüzeteket is. Az illékony funkciók például a RAND, TODAY, OFFSET, INDIRECT stb.
2. Ha összefoglalókat kell készítenie egy nagy adathalmazról, a képletek használata helyett nézze meg, hogy használhat -e helyette egy kimutatástáblát. A Pivot táblázat gyors és nagyon intuitív a képletekhez képest.
3. Ha több forrásból nyer ki adatokat, vagy több fájlból egyesít adatokat, használja a Power Query -t az adatok beszerzéséhez és átalakításához. Az adatokat Power Query -kapcsolatként mentheti, ahelyett, hogy a munkalapon tartaná. A Power Query használatával létrehozott kapcsolatok segítségével könnyedén létrehozhatja a kimutatástáblát.
4. Alakítsa át táblázatos adatait Excel táblává, és használja az Excel táblázatot képletekben. Ez gyorsítja a munkafüzet teljesítményét, és az Excel táblázat strukturált referenciái segítségével létrehozott képleteket könnyebb kezelni (mivel ezek intuitívabbak).
5. Kerülje a teljes sor vagy oszlop hivatkozását a képletben. Ez segít a formulák hatékonyabbá tételében.
6. Ha sok képlettel rendelkezik a munkafüzetben, kapcsolja ki az automatikus számítást, és váltson kézi számításra. Ezzel minden alkalommal, amikor változás történik a munkalapokon, a képletek nem számolnának újra. Ezt a módosítást a Képlet fülre kattintva végezheti el, és módosíthatja a „Számítási beállítások” lehetőséget.

Reméljük, hogy hasznosnak találja az útmutatóban leírt módszert és tippeket.

Mit tehetnék még hozzá ehhez az oktatóanyaghoz, hogy hasznosabb legyen? Kérjük, tudassa velem a megjegyzések részben.