8 tapaa pienentää Excel -tiedoston kokoa (jotka todella toimivat)

Excel -työkirjojen tiedetään muuttuvan tuskallisen hitaiksi ja raskaiksi, kun siihen lisätään lisää dataa/kaavoja/kuvia.

Joskus on helppo päästä eroon ylimääräisestä flabista ja pitää Excel -tiedoston koko kurissa, ja joskus se ei ole.

Koska Excel ei ole verkkopohjainen työkalu, useimmat meistä työskentelevät ja tekevät yhteistyötä lähettämällä Excel-tiedostoja sähköpostitse. Ja suurta 50 Mt: n Excel -tiedostoa voi olla vaikea jakaa kollegoidesi/asiakkaiden kanssa.

Viimeisessä työssäni käytimme Lotus Notes -sähköpostiohjelmaa, joka on etana-hidas, ja lähetimme raskaita Excel-tiedostoja sen yli.

Vaikka ei ole mitään maagista tapaa pienentää Excel -työkirjan kokoa, on olemassa muutamia tekniikoita, joiden avulla voit leikata jonkin verran flabia.

Huomautus: Näiden tekniikoiden vaikutus Excel -tiedoston koon pienentämiseen riippuu tiedoistasi. Joillekin se voi auttaa pienentämään kokoa 50% ja joillekin vain pienentämään kokoa 5%.

Vinkkejä Excel -tiedoston koon pienentämiseen

Alla on tekniikoita, joilla voit pienentää Excel -työkirjasi tiedoston kokoa.

Olen tehnyt jokaista vihjettä varten joitain testejä ja lisän tilannekuvia näyttääksesi vaikutuksen tiedoston kokoon. Saamasi tulokset vaihtelevat tiedoista ja tiedostoista riippuen.

Työkirjasi perusteella voit käyttää yhtä tai useampaa näistä vinkeistä.

Poista tarpeettomat laskentataulukot/tiedot

Parhaat ratkaisut ovat usein helpoimpia.

Ja tämä tekniikka on enemmän tervettä järkeä ja vähemmän Excelin velhoa.

Jos sinulla on Excel -työkirja, jossa on joitain laskentataulukoita, joita ei tarvita (ja joita ei käytetä missään kaavassa), katso, voitko päästä eroon niistä.

Tällä on suora korrelaatio Excel -tiedoston koon kanssa. Joten heti kun poistat joitain laskentataulukoita ja tallennat työkirjan, näet heti pienennetyn tiedoston koon.

Testitulokset:

Tein Excel -tiedoston, jossa oli 10 laskentataulukkoa, joissa jokaisella laskentataulukolla on 2,6 miljoonaa datapistettä (26 saraketta ja 100 000 datapistettä kussakin sarakkeessa).

Tämän työkirjan koko on 143 Mt (ja sen tallentaminen ja avaaminen vie jonkin aikaa).

Nyt kun poistin viisi arkkia, koko pieneni puoleen (duh).

Kuten näette, laskentataulukon tiedoilla on suora korrelaatio tiedoston koon kanssa.

Muunna binäärimuotoon (XLSB)

Tiedätkö, että vain tallentamalla Excel -tiedosto XLSB -muotoon voi pienentää tiedoston kokoa?

Kyllä se on totta.

Sinun tarvitsee vain tallentaa työkirja, kun käytät .xlsb -muotoa.

Tässä on seuraavat vaiheet:

1. Napsauta Tiedosto -välilehteä.
2. Napsauta Tallenna nimellä.
3. Napsauta Selaa.
4. Muuta Tallenna nimellä -valintaikkunassa tiedostotyypiksi Excel Binary Workbook (.xlsb)

Testitulokset

Minulla oli Excel -työkirja, joka vei 14,6 Mt tilaa.

Kun olen muuttanut saman tiedoston XLSB -muotoon, pystyin pienentämään koon 10,4 Mt: iin (koon pienennys ~ 28%).

Mikä on XLSB?

Kun tallennat Excel -tiedoston (.xlsx tai .xlsm), se tallennetaan XML -muotoon.

Jos esimerkiksi muutan XLSX -tiedoston tiedostopäätteen ZIP -muotoon ja avaan sen, saan tämän.

Ja jos siirryn edelleen xl -kansioon ja sitten laskentataulukkoon, se näyttää minulle kyseisen työkirjan laskentataulukot (ja nämä laskentataulukot tallennetaan XML -asiakirjana).

Tehdään nyt sama XLSB -tiedoston kanssa.

Joten muutan ensin tiedostopääte ZIP: ksi, avaan sen ja etsin sheet1. Tässä on mitä saat:

Huomaat, että sekä XLSX että XLSB tallentaa laskentataulukon eri muodossa.

XLSX/XLSM tallentaa sen XML -tiedostona ja XLSB binaaritiedostona (.bin).

Ja olematta liian teknisiä, XML -tiedostot ovat kooltaan suuria verrattuna binääritiedostoihin.

Joten kun tallennat Excel -työkirjasi XLSB -muodossa, tiedostokoko pienenee. Yllä olevassa esimerkissämme koon pienennys oli ~ 30%.

XLSB vs XLSX/XLSM

XLSB -muodon käytössä koon pienentäminen on valtava etu. Mutta sinun on oltava hieman varovainen, kun työskentelet XLSB -muodon kanssa.

XLSB -muodon edut:

1. Se pienentää Excel -tiedoston kokoa. Tästä voi olla apua, kun sinulla on suuria tiedostoja ja haluat jakaa sen sähköpostitse. Voit pienentää kokoa nopeasti tallentamalla sen XLSB -muotoon.
2. XLSB -tiedostoilla on taipumus avata ja tallentaa nopeammin kuin XML -tiedostoja. Tehtyjen rajoitettujen testien perusteella XLSB-tiedostot avautuivat ja sääsivät 25% -50% nopeammin. Tämä ero on havaittavissa vain suurissa Excel -tiedostoissa. Pienemmillä tiedostoilla sekä XLSB että XLSX ovat nopeita.

Vaikka XLSB näyttää hyvältä, XLSX/XLSM -tiedostojen käyttämiseen on muutamia syitä:

1. XLSB -muodossa et voi tietää, onko siinä makro vai ei. Tämä tekee siitä riskialttiimman, koska joku voi käyttää sitä haitallisen koodin suorittamiseen.
2. XLSB-tiedostot on koodattu omaan bin-tiedostomuotoon, kun taas XML on avoimen lähdekoodin luettavissa oleva tiedostomuoto. Tämä tarkoittaa, että jos käytät kolmannen osapuolen työkaluja Excelin kanssa, on parempi käyttää XML-muotoa.
3. Power Query ei voi lukea tietoja XLSB -tiedostosta. Joten jos käytät Power Queryä, on parempi, että tietosi ovat XLSX/XLSM -muodossa.

Yleisesti ottaen, jos tiedostokoko on pieni (alle 5 Mt), on parempi pysyä XLSX/XLSM -muodoissa.

Sen perusteella, mitä olen kuullut ihmisiltä ja lukenut monilla foorumeilla, monet ihmiset haluavat käyttää XLSB -tiedostomuotoa Excelin käytössä.

Tässä on hyvä artikkeli XLSB -muodon ymmärtämisestä.

Poista tarpeettomat kaavat

Liian monet kaavat paisuttavat työkirjasi tiedoston kokoa.

Joten jos sinulla on tarvitsemasi tietojoukko, mutta et tarvitse sen kaavoja, on parempi muuntaa nämä kaavat arvoiksi.

Voit muuntaa kaavat nopeasti arvoiksi Excelissä seuraavasti:

1. Valitse koko laskentataulukko/tietojoukko.
2. paina F5 näppäintä.
3. Napsauta Siirry -valintaikkunassa "Erikoista".
4. Valitse Siirry erikoisvalintaikkunaan Kaavat.
5. Napsauta OK.

Yllä olevat vaiheet valitsisivat kaikki solut, joissa on kaava.

Muunna nämä kaavat arvoiksi seuraavien ohjeiden avulla:

1. Kopioi valitut solut (Ctrl + C)
2. Napsauta Etusivu -välilehteä.
3. Napsauta Leikepöytä -ryhmässä Liitä.
4. Napsauta Liitä arvo -kuvaketta.

Yllä olevat vaiheet muuntaisivat kaikki solut kaavoilla arvoiksi.

Testitulokset

Tein Excel -tiedoston, jossa oli 2,7 miljoonaa datapistettä (1048576*26), ja lisäsin sitten kolme saraketta kaavoilla (SUM, COUNT ja AVERAGE).

Sitten muutin nämä kaavat arvoiksi ja tallensin tiedoston eri nimellä.

Alla tulos:

Tiedosto, jolla ei ole kaavoja, on kooltaan ~ 8% pienempi (ja huomaa, että minulla oli vain 3 saraketta kaavoja).

Jos sinulla on paljon kaavoja, se voi lisätä kokoa.

MERKINTÄ: Haihtuvat kaavat voivat todella paisuttaa tiedostokokosi. Osana testiä, kun korvasin SUM -kaavan haihtuvalla kaavalla (OFFSET), se johti tiedoston koon nousuun (186 Mt: sta 198 Mt: aan). Joten jos sinulla on haihtuvia kaavoja, jotka voit korvata haihtumattomilla kaavoilla tai voit muuntaa ne arvoiksi, se auttaa sinua pienentämään tiedoston kokoa.

Pakkaa kuvat (ennen ja jälkeen latauksen)

Jos käytät Excel -tiedostoja, joissa on kuvia, sinun on optimoitava nämä kuvat ja varmistettava, että niiden koko on pienempi.

Kuvien ongelma on se, että kun pakkaat näitä kuvia, laatu heikkenee, mitä et ehkä halua.

Kun käsittelet kuvia, paras tapa pitää Excel -tiedoston koko pienenä on pakata kuvat ennen kuin lisäät ne Exceliin.

Hyvä online -työkalu tähän on TinyPNG (tai voit käyttää Photoshopia tai muuta vastaavaa työkalua).

Se on ilmainen työkalu, joka voi pienentää kuvan kokoa jopa 90% ja ylläpitää silti kunnollista kuvanlaatua.

Alla on kuvakaappaus, jossa pystyin pakkaamaan kuvan, jonka koko on 3,9 Mt - 331 kt pienellä laadun heikkenemisellä.

Kun olet ladannut nämä pakatut kuvat Exceliin, voit pakata niitä edelleen käyttämällä Excelin sisäänrakennettua vaihtoehtoa.

Seuraavassa kerrotaan kuvan pakkaamisesta Excelissä:

1. Valitse pakattava kuva.
2. Napsauta Kuvatyökalut -muoto -välilehteä. Tämä on asiayhteydellinen välilehti, joka näkyy vain, kun napsautat kuvaa.
3. Valitse Säädä -ryhmässä Pakkaa kuva -vaihtoehto.
4. Pakkaa kuvat -valintaikkunassa:
   • Poista Käytä vain tähän kuvaan -valinta, jos haluat pakata kaikki työkirjan kuvat.
   • Valitse Sähköposti (96 ppi) maksimaaliseen pakkaamiseen.
5. Napsauta OK.
6. Tallenna tiedosto.

Tämä pakkaa kaikki kuvat ja auttaa sinua pienentämään Excel -työkirjasi tiedoston kokoa.

Testitulokset:

Käytin Excel -kuvan pakkausvaihtoehtoa käyttämällä yhtä kuvaa.

Alla tulokset:

Pakkauskuvavaihtoehdon avulla tiedostokoko pieneni 445 kt: sta 43 kt: iin (~ 90% pienempi tiedoston koko).

Tämä riippuu myös kuvasta. Jo pakattu kuva ei välttämättä vähennä valtavasti kokoa.

Poista pivot -välimuisti säästääksesi tilaa

Jos työskentelet Pivot -taulukoiden kanssa ja kamppailet suurien Excel -tiedostojen kanssa, Pivot -välimuistin tunteminen voi olla hyödyllistä.

Kun luot pivot -taulukon tietojoukon avulla, Excel luo automaattisesti pivot -välimuistin.

Pivot -välimuisti on objekti, joka sisältää kopion tietolähteestä.

Vaikka et näe sitä, se on osa työkirjaa ja yhdistetty pivot -taulukkoon. Kun teet muutoksia pivot -taulukkoon, se ei käytä tietolähdettä, vaan käyttää pivot -välimuistia.

Vaikka luulet olevasi suoraan yhteydessä lähdetietoihin, todellisuudessa käytät pivot -välimuistia (etkä lähdetietoja), kun teet muutoksia pivot -taulukkoon.

Nyt kun luot tietojoukon kopion, se vie jonkin verran tilaa.

Joten heti kun luot pivot -taulukon, huomaat, että Excel -tiedoston koko kasvaa.

Yllä olevassa esimerkissä heti kun lisään Pivot-taulukon, tiedoston koko kasvaa kaksinkertaiseksi.

Nyt voit pienentää tiedoston kokoa kahdella tavalla, kun käytät Pivot -taulukoita.

1. Säilytä lähdetiedot ja poista Pivot -välimuisti
2. Säilytä Pivot -välimuisti ja poista lähdetiedot

Katsotaanpa molempia tekniikoita.

Säilytä lähdetiedot ja poista pivot -välimuisti

Kun olet suorittanut Pivot -taulukon ja tallennat sen, voit pakottaa Excelin tallentamaan vain lähdetiedot ja tuloksena olevan pivot -taulukon, ei Pivot -välimuistia.

Tämä tekee Excel -tiedostosta kevyemmän ja voit luoda Pivot -välimuistin uudelleen, kun tarvitset sitä seuraavan kerran.

Alla on ero koossa, kun tallennat Excel -tiedoston Pivot -välimuistilla ja ilman sitä.

Tässä on vaiheet EI Tallenna Pivot -välimuisti:

1. Valitse mikä tahansa pivot -taulukon solu.
2. Napsauta Analysoi -välilehteä.
3. Napsauta pivot -taulukon Asetukset -kuvaketta.
4. Napsauta Pivot Table Options -valintaikkunassa Data -välilehteä.
5. Pivot -taulukon tiedot -vaihtoehdoissa poista valinta "Tallenna lähdetiedot tiedostolla". Tämä vaihtoehto varmistaa, että Pivot -välimuistia ei tallenneta, kun tallennat työkirjan (ja auttaa pienentämään Excel -tiedoston kokoa).
6. Valitse vaihtoehto - "Päivitä tiedot avattaessa tiedostoa". Tämä vaihtoehto varmistaa, että kun avaat työkirjan, pivot -taulukko päivitetään ja pivot -välimuisti luodaan automaattisesti. Jos et valitse tätä vaihtoehtoa, sinun on päivitettävä Pivot -taulukko manuaalisesti Pivot -välimuistin luomiseksi.
7. Napsauta OK.

Nyt kun tallennat tiedoston Pivot -taulukon kanssa, huomaat pienentyneen tiedoston koon.

Säilytä Pivot -välimuisti ja poista lähdetiedot

Toinen tapa pienentää Excel -tiedoston kokoa on poistaa pivot -taulukon lähdetiedot ja säilyttää vain pivot -välimuisti.

Tämä toimii pivot -välimuistina, joka on peilikuva tiedoistasi, etkä pääse lähdetietoihin, kun teet muutoksia pivot -taulukkoon, vaan käytät pivot -välimuistia.

Alla on tuloksia, kun tallensin Excel -tiedoston ilman lähdetietoja.

Voit tehdä tämän poistamalla tiedot (joko poistamalla tiedot tai laskentataulukon, jossa tiedot ovat).

Kun haluat saada lähdetiedot takaisin, kaksoisnapsauta Pivot-taulukon "Grand Total" -solua ja se luo välittömästi koko tietojoukon uudelle laskentataulukolle.

Pakkaa tiedosto (ZIP IT)

Tämän voit tehdä Excel -sovelluksen ulkopuolella.

Kun pakat Excel-tiedoston, näet heti koon pienenemisen noin 10-15%.

Voit sitten jakaa tämän zip -tiedoston sähköpostitse, ja tämän vastaanottava henkilö voi purkaa tiedoston ja käyttää Excel -työkirjaa.

Tässä on Excel -tiedoston zip -pakkaus (Windows):

1. Napsauta Excel-tiedostoa hiiren kakkospainikkeella
2. Vie hiiri Lähetä kohteeseen -vaihtoehdon kohdalle.
3. Napsauta "Pakattu (zip) -kansio" -vaihtoehtoa.

Tämä luo zip -tiedoston valitulle Excel -työkirjalle.

Huomaat myös tiedoston koon pienenemisen (kuten alla).

Testitulokset

Tällä tekniikalla pystyin pienentämään Excel -työkirjan tiedoston kokoa 8%.

Tämä tekniikka toimii paremmin tiedostoille, jotka ovat 20-50 Mt: n alueella. Tiedoston koon kasvaessa prosenttiosuus ei ole kovin suuri.

Poista tietojen muotoilu

Tietojen muotoilu, kuten taustavärin käyttäminen, reunusten lisääminen tai kirjasintyylin muuttaminen, voivat kaikki lisätä tiedoston kokoa.

Vaikka testien perusteella huomasin, ettei sillä ole suurta vaikutusta.

Alla on kahden Excel -työkirjan koot, joista toisessa on 2,6 miljoonaa datapistettä (yhdessä laskentataulukossa) ja toisessa on samat tiedot, mutta siihen on käytetty muotoilua (solun väri, reunus ja kursivoitu).

Testitulokset

Näet, että ero näiden kahden tiedoston välillä ei ole niin suuri (vaikka sillä on vaikutusta, kun sinulla on paljon tietoja).

Tiedosto, jossa on tietoja, on 14,6 Mt ja tiedosto, jossa on dataa ja muotoilua, on 14,9 Mt.

Joten jos haluat pienentää Excel -työkirjasi tiedoston kokoa, voit etsiä muotoilua, jota et tarvitse. Sen poistaminen voi säästää levytilaa.

Tässä on vaiheet, joilla voit poistaa kaikki muotoilut välittömästi pitäen tiedot ehjinä:

1. Valitse koko tietojoukko (josta haluat poistaa muotoilun).
2. Napsauta Etusivu -välilehteä.
3. Napsauta muokkausryhmässä "Tyhjennä".
4. Napsauta "Tyhjennä muodot".
5. Tallenna työkirja.

Poista ehdollinen muotoilu

Aivan kuten tavallinen tietojen muotoilu, ehdollinen muotoilu lisää myös tiedoston kokoa.

Testien perusteella huomasin kuitenkin, että ero on pienempi (ellei sinulla ole useita sääntöjä, joita sovelletaan useisiin arkkeihin, joissa on valtavat tietojoukot).

Siitä huolimatta on hyvä idea poistaa ehdollinen muotoilu mistä tahansa tietojoukosta, jos et sitä tarvitse.

Koon lisäksi ehdollisen muotoilun poistamiseen on toinen hyvä syy - SPEED.

Ehdollinen muotoilu on epävakaa ja aina kun laskentataulukossa tapahtuu muutoksia, se laskee uudelleen. Tämä voi johtaa hitaampaan työkirjaan.

Testitulokset

Luin tiedoston, jossa oli 27,2 miljoonaa datapistettä, ja käytin siihen 5 ehdollista muotoilusääntöä.

Kokoero näyttää olevan vain muutama kt.

Lue myös: Vinkkejä hitaiden Excel -laskentataulukoiden nopeuttamiseen

Excel Performance Booster -vinkkejä

Vaikka yllä olevat menetelmät auttavat sinua pienentämään Excel -tiedoston kokoa, tässä on muutamia lisävinkkejä Excel -työkirjojen suorituskyvyn parantamiseksi:

1. Vältä haihtuvien kaavojen käyttöä. Tiedoston koon kasvattamisen lisäksi haihtuvat kaavat myös hidastavat työkirjoja. Esimerkkejä haihtuvista toiminnoista ovat RAND, TODAY, OFFSET, INDIRECT jne.
2. Jos sinun on luotava yhteenvedot suuresta tietojoukosta kaavojen käyttämisen sijaan, katso, voitko käyttää pivot -taulukkoa. Pivot -taulukko on nopea ja erittäin intuitiivinen kaavoihin verrattuna.
3. Jos otat tietoja useista lähteistä tai yhdistät tietoja useista tiedostoista, hae ja muunna tiedot Power Queryn avulla. Voit tallentaa tiedot Power Query -yhteytenä sen sijaan, että pidät niitä laskentataulukossa. Voit luoda pivot -taulukon helposti käyttämällä Power Queryn avulla luotuja yhteyksiä.
4. Muunna taulukkotietosi Excel -taulukkoksi ja käytä Excel -taulukkoa kaavoissa. Sen avulla työkirjasi toimii nopeammin ja Excel -taulukon jäsenneltyjen viitteiden avulla luotuja kaavoja on helpompi hallita (koska ne ovat intuitiivisempia).
5. Vältä koko rivin tai sarakkeen viittaamista kaavassa. Tämä auttaa kaavoja olemaan tehokkaampia.
6. Jos työkirjassa on paljon kaavoja, poista Automaattinen laskenta käytöstä ja vaihda manuaaliseen laskentaan. Tällä tavalla kaavat eivät laskisi uudelleen aina, kun laskentataulukoissa tapahtuu muutoksia. Voit tehdä tämän muutoksen siirtymällä Kaava -välilehteen ja muuttamalla Laskutusasetukset.

Toivottavasti tämän opetusohjelman menetelmä ja vinkit ovat hyödyllisiä.

Mitä muuta voisin lisätä tähän opetusohjelmaan tehdäkseni siitä hyödyllisemmän? Kerro minulle kommenttikentässä.