8 võimalust Exceli failisuuruse vähendamiseks (mis tegelikult töötavad)

On teada, et Exceli töövihikud muutuvad valusalt aeglaseks ja raskeks, kuna sellele lisatakse rohkem andmeid/valemeid/pilte.

Mõnikord on lisaflabist lihtne vabaneda ja Exceli failisuurust kontrolli all hoida ning mõnikord ei ole.

Ja kuna Excel ei ole veebipõhine tööriist, siis enamik meist töötab ja teeb koostööd, saates Exceli faile meilitsi. Ja suurt Exceli faili, mis kaalub 50 MB, võib olla raske oma kolleegide/klientidega jagada.

Minu viimasel töökohal kasutasime Lotus Notes e-posti klienti, mis on tigu aeglane ja raskete Exceli failide saatmine oli piinamine.

Ehkki Exceli töövihiku suuruse vähendamiseks pole maagilist viisi, on mõned tehnikad, mille abil saate mõne flab'i kärpida.

Märkus. Nende meetodite mõju Exceli faili suuruse vähendamisele sõltub teie andmetest. Mõne jaoks võib see aidata suurust vähendada 50% võrra ja mõne jaoks võib see lõppeda ainult 5% võrra.

Näpunäiteid Exceli failisuuruse vähendamiseks

Allpool on toodud meetodid, mida saate kasutada Exceli töövihiku failisuuruse vähendamiseks.

Iga näpunäite puhul olen teinud mõned testid ja lisanud hetktõmmised, et näidata teile mõju faili suurusele. Saadud tulemused varieeruvad sõltuvalt teie andmetest ja failidest.

Töövihiku põhjal saate kasutada ühte või mitut neist näpunäidetest.

Eemaldage mittevajalikud töölehed/andmed

Parimad lahendused on sageli kõige lihtsamad.

Ja see tehnika on rohkem kaine mõistus ja vähem igasugune Exceli võlur.

Kui teil on Exceli töövihik, kus on mõned töölehed, mida pole vaja (ja mida ei kasutata üheski valemis), vaadake, kas saate neist lahti saada.

Sellel on otsene seos Exceli faili suurusega. Nii et niipea, kui olete mõne töölehe kustutanud ja töövihiku salvestanud, näete koheselt vähendatud failisuurust.

Testi tulemused:

Lõin 10 töölehega Exceli faili, kus igal töölehel on 2,6 miljonit andmepunkti (26 veergu 100 000 andmepunktiga igas veerus).

Selle töövihiku suurus on 143 MB (ja selle salvestamine ja avamine võtab üsna kaua aega).

Nüüd, kui ma viis lehte kustutasin, vähenes suurus poole võrra (duh).

Nagu näete, on teie töölehe andmetel failisuurusega otsene seos.

Teisenda kahendvorminguks (XLSB)

Kas teate, et Exceli faili salvestamine XLSB -vormingus võib faili suurust vähendada?

Jah, see on tõsi.

Kõik, mida pead tegema, on töövihiku salvestamisel kasutada .xlsb -vormingut.

Selleks toimige järgmiselt.

1. Klõpsake vahekaarti Fail.
2. Klõpsake nuppu Salvesta nimega.
3. Klõpsake nuppu Sirvi.
4. Muutke dialoogiboksis Salvesta nimega failitüüp Exceli binaartöövihikuks (.xlsb)

Testi tulemused

Mul oli Exceli töövihik, mis võttis 14,6 MB ruumi.

Pärast sama faili teisendamist XLSB -vormingusse suutsin selle suurust vähendada 10,4 MB -ni (suuruse vähendamine ~ 28%).

Mis on XLSB?

Exceli faili (.xlsx või .xlsm) salvestamisel salvestatakse see XML -vormingus.

Näiteks kui muudan XLSX -faili faililaiendiks ZIP -faili ja avan selle, siis saan selle.

Ja kui ma lähen veel kausta „xl” ja seejärel töölehe kausta, näitab see mulle selle töövihiku töölehti (ja need töölehed salvestatakse XML -dokumendina).

Nüüd teeme sama XLSB -failiga.

Seega muudan esmalt faililaiendi ZIP -iks, seejärel avan selle ja leian lehe 1. Siin on see, mida saate:

Märkate, et nii XLSX kui ka XLSB salvestab töölehe erinevas vormingus.

XLSX/XLSM salvestab selle XML -failina ja XLSB kahendfailina (.bin).

Ja ilma liiga tehniliseks muutmata on XML -failid binaarfailiga võrreldes suured.

Nii et kui salvestate oma Exceli töövihiku XLSB -vormingus, vähendatakse faili suurust. Meie ülaltoodud näites oli suuruse vähenemine ~ 30%.

XLSB vs XLSX/XLSM

XLSB -vormingu kasutamisel on suuruse vähendamine tohutu eelis. Kuid XLSB -vorminguga töötamisel peate olema pisut ettevaatlik.

XLSB -vormingu eelised:

1. See vähendab Exceli faili suurust. See võib olla kasulik, kui teil on suured failid ja soovite seda e -posti teel jagada. Saate suurust kiiresti vähendada, salvestades selle XLSB -sse.
2. XLSB -failid avanevad ja salvestavad tavaliselt kiiremini kui XML -failid. Piiratud testide põhjal, mida tegin, avasid XLSB-failid 25% -50% kiiremini. See erinevus on märgatav ainult suurtes Exceli failides. Väiksemate failide puhul on nii XLSB kui ka XLSX kiire.

Kuigi XLSB näeb hea välja, on XLSX/XLSM -failide kasutamisel mõned põhjused.

1. XLSB -vormingus ei saa te teada, kas sellel on makro või mitte. See muudab selle riskantsemaks, kuna keegi saab seda kasutada pahatahtliku koodi käivitamiseks.
2. XLSB-failid on kodeeritud prügikasti failivormingus, samas kui XML on avatud lähtekoodiga loetav failivorming. See tähendab, et kui kasutate Exceliga kolmanda osapoole tööriistu, on parem kasutada XML-vormingut.
3. Power Query ei saa lugeda andmeid XLSB -failist. Seega, kui kasutate Power Queryt, on parem, kui teie andmed on XLSX/XLSM -vormingus.

Üldjuhul, kui teie failisuurus on väike (alla 5 MB), on parem jääda XLSX/XLSM -vormingute juurde.

Selle põhjal, mida kuulsin inimestelt ja lugesin paljudest foorumitest, eelistavad paljud inimesed Exceli kasutamisel failivorminguna kasutada XLSB -d.

Siin on hea artikkel XLSB -vormingu mõistmise kohta.

Eemaldage mittevajalikud valemid

Liiga palju valemeid paisutab teie töövihiku failisuurus.

Seega, kui teil on vajalik andmekogum, kuid te ei vaja selles olevaid valemeid, on parem need valemid väärtusteks teisendada.

Valemite kiireks Excelis väärtusteks teisendamiseks toimige järgmiselt.

1. Valige kogu tööleht/andmestik.
2. Vajutage F5 võti.
3. Klõpsake dialoogiboksi Mine, klõpsake nuppu "Eriline".
4. Valige dialoogiboksis Mine eriliiki Valemid.
5. Klõpsake nuppu OK.

Ülaltoodud sammud valiksid kõik lahtrid, millel on valem.

Nüüd kasutage nende valemite väärtusteks teisendamiseks järgmisi samme:

1. Kopeeri valitud lahtrid (kontroll + C)
2. Klõpsake vahekaarti Avaleht.
3. Klõpsake lõikelaua rühmas nuppu Kleebi.
4. Klõpsake ikooni Kleebi väärtus.

Ülaltoodud sammud muudaksid kõik valemitega lahtrid väärtusteks.

Testi tulemused

Lõin 2,7 miljoni andmepunktiga Exceli faili (1048576*26) ja seejärel lisasin kolm veergu valemitega (SUM, COUNT ja KESKMINE).

Seejärel teisendasin need valemid väärtusteks ja salvestasin faili teise nimega.

Allpool on tulemus:

Fail ilma valemiteta on ~ 8% väiksem (ja pange tähele, et mul oli ainult 3 veergu valemeid).

Kui teil on palju valemeid, võib see suurust suurendada.

MÄRGE: Lenduvad valemid võivad teie failisuuruse tõesti paisutada. Testi osana, kui asendasin SUM valemi lenduva valemiga (OFFSET), tõi see kaasa faili suuruse hüppe (186 MB -lt 198 MB -le). Nii et kui teil on lenduvaid valemeid, mida saate asendada püsivate valemitega või saate need väärtusteks teisendada, aitab see teil faili suurust vähendada.

Piltide tihendamine (enne ja pärast üleslaadimist)

Kui töötate Exceli failidega, milles on pilte, peate need pildid optimeerima ja veenduma, et nende suurus on väiksem.

Piltide probleem on see, et nende kokkusurumisel väheneb ka kvaliteet, mida te ei pruugi soovida.

Piltidega töötamisel on parim viis Exceli faili suuruse hoidmiseks madalal tihendada need enne Exceli sisestamist.

Hea veebitööriist selleks on TinyPNG (või võite kasutada Photoshopi või mõnda muud sarnast tööriista).

See on tasuta tööriist, mis võib vähendada pildi suurust kuni 90% ja säilitada siiski korraliku pildikvaliteedi.

Allpool on ekraanipilt, kus suutsin 3,9 MB suuruse pildi kuni 331 KB tihendada minimaalse kvaliteedi kadumisega.

Kui olete need tihendatud pildid Excelisse üles laadinud, saate seda veelgi tihendada, kasutades Exceli sisseehitatud valikut.

Kujutise kokkusurumiseks Excelis toimige järgmiselt.

1. Valige pilt, mida soovite tihendada.
2. Klõpsake vahekaarti Pilditööriistad Vorming. See on kontekstivahekaart, mis kuvatakse ainult pildil klõpsates.
3. Klõpsake rühmas Reguleeri suvandit „Tihenda pilt”.
4. Dialoogiboksis Piltide tihendamine:
   • Kui soovite kõik töövihiku pildid kokku suruda, tühjendage valik „Rakenda ainult sellele pildile”.
   • Maksimaalse pakkimise jaoks valige e -post (96 ppi).
5. Klõpsake nuppu OK.
6. Salvestage fail.

See tihendab kõik pildid ja aitab teil vähendada Exceli töövihiku failisuurust.

Testi tulemused:

Kasutasin Excelis ühe pildi abil pakkimise võimalust.

Allpool on toodud tulemused:

Pildi kokkusurumise võimalust kasutades vähendati faili suurust 445 KB -lt 43 KB -ni (faili suurus vähenes ~ 90%).

See sõltub ka pildist. Juba kokkusurutud pilt ei pruugi põhjustada tohutut suuruse vähenemist.

Ruumi säästmiseks eemaldage Pivot -vahemälu

Kui töötate Pivoti tabelitega ja näete vaeva suurte Exceli failidega, võib Pivoti vahemälu tundmine olla kasulik.

Kui loote liigendtabeli andmekomplekti abil, loob Excel automaatselt Pivot -vahemälu.

Pivot -vahemälu on objekt, mis sisaldab andmeallika koopiat.

Kuigi te seda ei näe, on see osa töövihikust ja on ühendatud pöördtabeliga. Pivot -tabelis muudatuste tegemisel ei kasuta see andmeallikat, vaid pigem Pivot -vahemälu.

Kuigi arvate, et olete otseselt lingitud lähteandmetega, pääsete tegelikult pöördetabelisse muudatuste tegemisel pöördeväljale (mitte lähteandmetele).

Nüüd, kui loote andmekogumi koopiat, võtab see natuke ruumi.

Nii et niipea, kui loote Pivot -tabeli, märkate, et Exceli faili suurus suureneb.

Ülaltoodud näites suureneb faili suurus kohe pärast Pivot-tabeli lisamist kahekordseks.

Nüüd on Pivot -tabelitega töötamisel failisuuruse vähendamiseks kaks võimalust.

1. Hoidke lähteandmed alles ja kustutage Pivot -vahemälu
2. Hoidke Pivot -vahemälu ja kustutage lähteandmed

Vaatame mõlemat tehnikat.

Hoidke lähteandmed ja kustutage Pivot -vahemälu

Kui olete Pivot -tabeliga lõpetanud ja salvestate selle, saate sundida Exceli salvestama ainult lähteandmed ja sellest tuleneva Pivot -tabeli, mitte Pivot -vahemälu.

See muudab teie Exceli faili kergemaks ja teil on võimalus Pivoti vahemälu uuesti luua, kui seda järgmisel korral vajate.

Allpool on toodud suuruse erinevus, kui salvestate Exceli faili Pivot -vahemäluga ja ilma.

Siin on sammud MITTE salvestage Pivot -vahemälu:

1. Valige liigendtabelis suvaline lahter.
2. Klõpsake vahekaarti Analüüs.
3. Klõpsake pöördtabeli ikooni „Valikud”.
4. Klõpsake dialoogiboksis Pivot Tabeli suvandid vahekaarti Andmed.
5. Pivot tabeli andmete suvandites tühjendage märkeruut „Salvesta lähteandmed failiga”. See suvand tagab, et Pivot -vahemälu ei salvestata töövihiku salvestamisel (ja aitab vähendada Exceli faili suurust).
6. Kontrollige valikut - 'Värskenda andmeid faili avamisel'. See suvand tagab, et kohe, kui avate töövihiku, värskendatakse liigendtabelit ja genereeritakse automaatselt Pivot -vahemälu. Kui te seda suvandit ei märgi, peate Pivot -vahemälu genereerimiseks Pivot -tabelit käsitsi värskendama.
7. Klõpsake nuppu OK.

Nüüd, kui salvestate faili Pivot -tabeliga, märkate vähendatud faili suurust.

Hoidke Pivot -vahemälu ja kustutage lähteandmed

Teine võimalus Exceli faili suuruse vähendamiseks on Pivot -tabeli lähteandmete kustutamine ja ainult Pivot -vahemälu säilitamine.

See toimib, kuna Pivot -vahemälu on teie andmete peegelpilt ja te ei pääse pöördtabelisse muudatuste tegemisel lähteandmetele juurde, selle asemel kasutate Pivot -vahemälu.

Allpool on toodud tulemused, kui salvestasin Exceli faili ilma lähteandmeteta.

Selleks lihtsalt kustutage andmed (kas kustutades andmed või andmed sisaldava töölehe).

Kui soovite lähteandmeid tagasi saada, topeltklõpsake Pivot-tabeli lahtrit "Grand Total" ja see genereerib koheselt kogu andmekogumi uuel töölehel.

Faili tihendamine (ZIP IT)

Seda saate teha väljaspool Exceli rakendust.

Exceli faili pakkimisel näete kohe suuruse vähenemist umbes 10-15%.

Seejärel saate seda pakitud faili e -posti teel jagada ja seda saav isik saab faili lahti pakkida ja Exceli töövihikut kasutada.

Exceli faili pakkimiseks (Windowsi jaoks) toimige järgmiselt.

1. Paremklõpsake Exceli faili
2. Hõljutage kursorit valikul Saada.
3. Klõpsake suvandit „Tihendatud (zipped) kaust”.

See loob valitud Exceli töövihiku jaoks pakitud faili.

Samuti märkate faili suuruse vähenemist (nagu allpool näidatud).

Testi tulemused

Selle tehnikaga suutsin vähendada Exceli töövihiku failisuurust 8%.

See meetod töötab paremini failide puhul, mis jäävad vahemikku 20–50 MB. Faili suuruse kasvades ei ole protsentuaalne vähenemine kuigi suur.

Eemaldage andmete vormindamine

Andmete vormindamine, näiteks taustavärvi rakendamine, ääriste lisamine või fondi stiili muutmine, võivad kõik faili suurust suurendada.

Kuigi oma testide põhjal märkasin, et sellel pole suurt mõju.

Allpool on toodud kahe Exceli töövihiku suurused, millest ühes on 2,6 miljonit andmepunkti (ühes töölehel) ja teises on samad andmed, kuid sellele on rakendatud vorming (lahtri värv, ääris ja kaldkiri).

Testi tulemused

Näete, et erinevus kahe faili vahel ei ole nii suur (kuigi see avaldab mõju, kui teil on palju andmeid).

Andmetega fail on 14,6 MB ning andmete ja vorminguga fail 14,9 MB.

Nii et kui soovite oma Exceli töövihiku failisuurust vähendada, võite otsida vormingut, mida te ei vaja. Selle eemaldamine võib säästa kettaruumi.

Siin on sammud kogu vorminduse koheselt eemaldamiseks, säilitades samal ajal andmed puutumata:

1. Valige kogu andmekogum (millest soovite vorminduse eemaldada).
2. Klõpsake vahekaarti Avaleht.
3. Klõpsake rühmas Redigeerimine nuppu Kustuta.
4. Klõpsake nuppu "Kustuta vormingud".
5. Salvestage töövihik.

Eemaldage tingimusvorming

Nii nagu tavaline andmete vormindamine, suurendab tingimusvorming ka faili suurust.

Kuid oma testide põhjal märkasin, et erinevus on väiksem (kui teil pole mitmete reeglite rakendamist mitmele lehele, millel on tohutud andmekogumid).

Sellegipoolest on hea mõte eemaldada tingimusvorming igast andmekogumist, kus te seda ei vaja.

Lisaks suurusele on tingimusvormingu eemaldamiseks veel üks hea põhjus - SPEED.

Tingimuslik vormindamine on kõikuv ja kui töölehel on mingeid muudatusi, arvutab see uuesti. See võib viia aeglasema töövihikuni.

Testi tulemused

Lõin 27,2 miljoni andmepunktiga faili ja rakendasin sellele 5 tingimusliku vormindamise reeglit.

Suuruse erinevus näib olevat vaid mõni KB.

Loe ka: Näpunäiteid aeglase Exceli arvutustabelite kiireks muutmiseks

Exceli jõudluse suurendamise näpunäited

Kuigi ülaltoodud meetodid aitavad teil Exceli faili suurust vähendada, on siin mõned täiendavad näpunäited Exceli töövihikute jõudluse parandamiseks.

1. Vältige lenduvate valemite kasutamist. Lisaks failisuuruse suurendamisele muudavad lenduvad valemid ka töövihikud aeglaseks. Lenduvate funktsioonide näideteks on RAND, TODAY, OFFSET, INDIRECT jne.
2. Kui teil on vaja valemite asemel luua suure andmestiku kokkuvõtteid, vaadake, kas saate selle asemel kasutada liigendtabelit. Pivot -tabel on valemitega võrreldes kiire ja väga intuitiivne.
3. Kui eraldate andmeid mitmest allikast või kombineerite andmeid mitmest failist, kasutage andmete hankimiseks ja teisendamiseks Power Query'i. Saate salvestada andmed Power Query ühendusena, mitte neid töölehel hoida. Pivot -tabeli saate hõlpsalt luua Power Query abil loodud ühenduste abil.
4. Teisendage tabeliandmed Exceli tabeliks ja kasutage Exceli tabelit valemites. See muudab teie töövihiku kiiremaks ja Exceli tabeli struktureeritud viidete abil loodud valemeid on lihtsam hallata (kuna need on intuitiivsemad).
5. Vältige valemis terve rea või veeru viitamist. See aitab teie valemitel olla tõhusam.
6. Kui teie töövihikus on palju valemeid, lülitage automaatne arvutamine välja ja lülitage käsitsi arvutamisele. Seda tehes ei arvutata valemeid iga kord töölehtede muutmisel ümber. Seda muudatust saate teha, minnes vahekaardile Valem ja muutes „Arvutusvalikuid”.

Loodan, et leiate sellest õpetusest meetodi ja näpunäited kasulikud.

Mida saaksin sellele õpetusele veel lisada, et see oleks kasulikum? Palun andke mulle sellest kommentaaride jaotises teada.