8 moduri de a reduce dimensiunea fișierului Excel (care funcționează efectiv)

Se știe că registrele de lucru Excel devin dureroase, lente și grele, pe măsură ce se adaugă mai multe date / formule / imagini.

Uneori, este ușor să scăpați de clema suplimentară și să țineți sub control dimensiunea fișierului Excel și, uneori, nu este.

Și întrucât Excel nu este un instrument bazat pe web, majoritatea dintre noi lucrăm și colaborăm prin trimiterea fișierelor Excel prin e-mail. Și un fișier Excel mare, care cântărește 50 MB, poate fi greu de partajat cu colegii / clienții.

În ultima mea slujbă, am folosit clientul de e-mail Lotus Notes, care este lent, și trimitem fișiere grele Excel peste tortură.

Deși nu există nicio modalitate magică de a reduce dimensiunea unui registru de lucru Excel, există câteva tehnici pe care le puteți utiliza pentru a reduce unele flab.

Notă: Impactul acestor tehnici pentru a reduce dimensiunea fișierului Excel ar depinde de datele dvs. Pentru unii, poate ajuta la reducerea dimensiunii cu 50%, iar pentru unii, poate ajunge doar la reducerea dimensiunii cu 5%.

Sfaturi pentru a reduce dimensiunea fișierului Excel

Mai jos sunt tehnicile pe care le puteți utiliza pentru a reduce dimensiunea fișierului din registrul dvs. de lucru Excel.

Pentru fiecare sfat, am făcut câteva testări și am adăugat instantanee pentru a vă arăta impactul asupra dimensiunii fișierului. Rezultatele obținute vor varia în funcție de date și fișiere.

Pe baza registrului dvs. de lucru, puteți utiliza unul sau mai multe dintre aceste sfaturi.

Eliminați foile de lucru / datele inutile

Cele mai bune soluții sunt adesea cele mai ușoare.

Și această tehnică este mai mult de bun simț și mai puțin de orice vrăjitorie Excel.

Dacă aveți un registru de lucru Excel care are câteva foi de lucru care nu sunt necesare (și care nu sunt utilizate în formule), vedeți dacă puteți scăpa de acestea.

Aceasta are o corelație directă cu dimensiunea fișierului Excel. Deci, de îndată ce ștergeți unele dintre foile de lucru și salvați registrul de lucru, veți vedea instantaneu o dimensiune redusă a fișierului.

Rezultatele testului:

Am creat un fișier Excel cu 10 foi de lucru în care fiecare foaie de lucru are 2,6 milioane de puncte de date (26 de coloane cu 100.000 de puncte de date în fiecare coloană).

Dimensiunea rezultată a acestui registru de lucru este de 143 MB (și este nevoie de ceva timp pentru a salva și a deschide).

Acum, când am șters cinci coli, dimensiunea s-a redus la jumătate (duh).

După cum puteți vedea, datele pe care le aveți foaia de lucru are o corelație directă cu dimensiunea fișierului.

Convertiți în format binar (XLSB)

Știți că doar prin salvarea unui fișier Excel în format XLSB puteți reduce dimensiunea fișierului?

Da este adevarat.

Tot ce trebuie să faceți este să salvați un registru de lucru, utilizați formatul .xlsb.

Iată pașii pentru a face acest lucru:

1. Faceți clic pe fila Fișier.
2. Faceți clic pe Salvare ca.
3. Faceți clic pe Răsfoire.
4. În caseta de dialog Salvare ca, schimbați tipul de fișier în Excel Binary Workbook (.xlsb)

Rezultatele testului

Aveam un registru de lucru Excel care ocupa 14,6 MB de spațiu.

După convertirea aceluiași fișier în formatul XLSB, am reușit să reduc dimensiunea la 10,4 MB (o reducere a dimensiunii de ~ 28%).

Ce este XLSB?

Când salvați un fișier Excel (.xlsx sau .xlsm), acesta este salvat în format XML.

De exemplu, dacă schimb extensia de fișier a fișierului XLSX în ZIP și apoi îl deschid, acesta este ceea ce obțin.

Și dacă merg mai departe în folderul ‘xl’ și apoi intru în dosarul foii de lucru, îmi arată foile de lucru din acel registru de lucru (iar aceste foi de lucru sunt salvate ca document XML).

Acum să facem același lucru cu un fișier XLSB.

Deci mai întâi schimb extensia de fișier în ZIP, apoi o deschid și localizez sheet1. Iată ce obțineți:

Veți observa că atât XLSX, cât și XLSB salvează foaia de lucru într-un format diferit.

XLSX / XLSM îl salvează ca fișier XML și XLSB îl salvează ca fișier binar (.bin).

Și fără a deveni prea tehnice, fișierele XML au dimensiuni mari în comparație cu un fișier binar.

Deci, atunci când salvați registrul de lucru Excel în format XLSB, dimensiunea fișierului este redusă. În exemplul nostru de mai sus, reducerea dimensiunii a fost de ~ 30%.

XLSB Vs XLSX / XLSM

Când vine vorba de utilizarea formatului XLSB, reducerea dimensiunii este un beneficiu imens. Dar trebuie să fii puțin precaut atunci când lucrezi cu formatul XLSB.

Avantajele formatului XLSB:

1. Reduce dimensiunea fișierului Excel. Acest lucru vă poate fi util atunci când aveți fișiere mari și doriți să le distribuiți prin e-mail. Puteți reduce rapid dimensiunea salvând-o în XLSB.
2. Fișierele XLSB tind să se deschidă și să economisească mai repede decât fișierul XML. Pe baza testelor limitate pe care le-am făcut, fișierele XLSB se deschideau și economiseau cu 25% -50% mai repede. Această diferență este vizibilă numai în fișierele Excel mari. Cu fișiere mai mici, atât XLSB, cât și XLSX sunt rapide.

În timp ce XLSB arată minunat, există câteva motive pentru care ar trebui să rămâneți la utilizarea fișierelor XLSX / XLSM:

1. Cu formatul XLSB, nu aveți de unde să știți dacă are sau nu un macro. Acest lucru îl face mai riscant, deoarece cineva îl poate folosi pentru a executa coduri rău intenționate.
2. Fișierele XLSB sunt codificate în format de fișier bin proprietar, în timp ce XML este un format de fișier lizibil open-source. Aceasta înseamnă că, dacă utilizați instrumente terță parte cu Excel, este mai bine să utilizați formatul XML.
3. Power Query nu poate citi date dintr-un fișier XLSB. Deci, dacă utilizați Power Query, este mai bine să aveți datele în format XLSX / XLSM.

Ca regulă generală, dacă dimensiunea fișierului dvs. este mică (mai puțin de 5 MB), este mai bine să rămâneți la formatele XLSX / XLSM.

Pe baza a ceea ce am auzit de la oameni și am citit pe multe forumuri, mulți oameni preferă să folosească XLSB ca format de fișier atunci când vine vorba de utilizarea Excel.

Iată un articol bun despre înțelegerea formatului XLSB.

Eliminați formulele inutile

Prea multe formule vor umfla dimensiunea fișierului din registrul de lucru.

Deci, dacă aveți un set de date de care aveți nevoie, dar nu aveți nevoie de formulele din acesta, este mai bine să convertiți aceste formule în valori.

Iată pașii pentru a converti rapid formule în valori în Excel:

1. Selectați întreaga foaie de lucru / set de date.
2. apasă pe F5 cheie.
3. În caseta de dialog Accesați, faceți clic pe „Special”.
4. În caseta de dialog Accesați special, selectați Formule.
5. Faceți clic pe OK.

Pașii de mai sus ar selecta toate celulele care au o formulă.

Acum utilizați pașii de mai jos pentru a converti aceste formule în valori:

1. Copiați celulele selectate (control + C)
2. Faceți clic pe fila Acasă.
3. În grupul Clipboard, faceți clic pe Lipire.
4. Faceți clic pe pictograma Lipire valoare.

Pașii de mai sus ar converti toate celulele cu formule în valori.

Rezultatele testului

Am creat un fișier Excel cu 2,7 milioane de puncte de date (1048576 * 26) și apoi am adăugat trei coloane cu formule (SUMĂ, COUNT și MEDIE).

Apoi am convertit aceste formule în valori și am salvat fișierul cu un nume diferit.

Mai jos este rezultatul:

Fișierul fără formule are o dimensiune de ~ 8% mai mic (și rețineți că am avut doar 3 coloane de formule).

În cazul în care aveți o mulțime de formule, se poate adăuga la dimensiune.

NOTĂ: Formulele volatile vă pot umfla dimensiunea fișierului. Ca parte a testului, când am înlocuit formula SUM cu o formulă volatilă (OFFSET), aceasta a dus la un salt al dimensiunii fișierului (de la 186 MB la 198 MB). Deci, dacă aveți formule volatile pe care le puteți înlocui cu formule nevolatile sau le puteți converti în valori, vă va ajuta să reduceți dimensiunea fișierului.

Comprimarea imaginilor (înainte și după încărcare)

Dacă lucrați cu fișiere Excel care conțin imagini, trebuie să optimizați aceste imagini și să vă asigurați că dimensiunea este mai mică.

Problema cu imaginile este că, pe măsură ce le comprimați, există, de asemenea, o reducere a calității, pe care este posibil să nu o doriți.

Când lucrați cu imagini, cel mai bun mod de a menține dimensiunea fișierului Excel scăzut este prin comprimarea imaginilor înainte de ao insera în Excel.

Un instrument online bun pentru a face acest lucru este TinyPNG (sau puteți utiliza Photoshop sau orice alt instrument similar).

Este un instrument gratuit care poate reduce dimensiunea unei imagini cu până la 90% și menține în continuare o calitate decentă a imaginii.

Mai jos este captura de ecran în care am putut comprima o imagine cu dimensiunea de 3,9 MB la 331 KB cu o pierdere minimă de calitate.

După ce ați încărcat aceste imagini comprimate în Excel, puteți să le comprimați în continuare folosind opțiunea încorporată în Excel.

Iată pașii pentru comprimarea unei imagini în Excel:

1. Selectați imaginea pe care doriți să o comprimați.
2. Faceți clic pe fila Format instrumente imagine. Aceasta este o filă contextuală care apare numai atunci când faceți clic pe o imagine.
3. În grupul Ajustare, faceți clic pe opțiunea „Comprimare imagine”.
4. În caseta de dialog Comprimare imagini:
   • Deselectați „Aplicați numai la această imagine” dacă doriți să comprimați toate imaginile din registrul de lucru.
   • Selectați E-mail (96 ppi) pentru compresie maximă.
5. Faceți clic pe OK.
6. Salvați fișierul.

Aceasta va comprima toate imaginile și vă va ajuta să reduceți dimensiunea fișierului din registrul dvs. de lucru Excel.

Rezultatele testului:

Am folosit opțiunea compress imge în Excel folosind o singură imagine.

Mai jos sunt rezultatele:

Prin utilizarea opțiunii de comprimare a imaginii, dimensiunea fișierului s-a redus de la 445 KB la 43 KB (reducere de ~ 90% a dimensiunii fișierului).

Acest lucru ar depinde și de imagine. Este posibil ca o imagine care este deja comprimată să nu conducă la o reducere imensă a dimensiunii.

Eliminați memoria cache pivot pentru a economisi spațiu

Dacă lucrați cu tabele pivot și vă confruntați cu fișiere Excel mari, poate fi util să cunoașteți memoria cache pivot.

Când creați un tabel pivot utilizând un set de date, Excel creează automat cache-ul pivot.

Pivot cache este un obiect care deține o replică a sursei de date.

Deși nu îl puteți vedea, acesta face parte din registrul de lucru și este conectat la tabelul pivot. Când faceți modificări în tabelul pivot, acesta nu folosește sursa de date, ci mai degrabă folosește cache-ul pivot.

În timp ce credeți că sunteți direct legat de datele sursă, în realitate, accesați memoria cache pivot (și nu datele sursă) atunci când faceți modificări în tabelul pivot.

Acum, deoarece creați o replică a setului de date, este nevoie de puțin spațiu.

Deci, de îndată ce creați un tabel pivot, veți observa că dimensiunea fișierului Excel crește.

În exemplul de mai sus, de îndată ce adaug tabelul Pivot, dimensiunea fișierului crește de două ori.

Acum, există două moduri în care puteți reduce dimensiunea fișierului atunci când lucrați cu tabele pivot.

1. Păstrați datele sursă și ștergeți memoria cache pivot
2. Păstrați memoria cache pivot și ștergeți datele sursă

Să analizăm ambele tehnici.

Păstrați datele sursă și ștergeți memoria cache pivot

Când ați terminat cu tabelul pivot și îl salvați, puteți forța Excel să salveze doar datele sursă și tabelul pivot rezultat, și nu cache-ul pivot.

Dacă faceți acest lucru, fișierul Excel va fi mai ușor și veți avea o modalitate de a recrea cache-ul Pivot când veți avea nevoie de el data viitoare.

Mai jos este diferența de dimensiune atunci când salvați un fișier Excel cu și fără cache pivot.

Iată pașii necesari NU salvați memoria cache pivot:

1. Selectați orice celulă din tabelul pivot.
2. Faceți clic pe fila Analize.
3. Faceți clic pe pictograma „Opțiuni” din tabelul pivot.
4. În caseta de dialog Opțiuni tabel pivot, faceți clic pe fila Date.
5. În opțiunile Date din tabelul pivot, debifează „Salvați datele sursă cu fișierul”. Această opțiune asigură faptul că memoria cache pivot nu este salvată atunci când salvați registrul de lucru (și ajută la reducerea dimensiunii fișierului Excel).
6. Bifați opțiunea - „Actualizați datele la deschiderea fișierului”. Această opțiune vă asigură că de îndată ce deschideți registrul de lucru, tabelul pivot este reîmprospătat și cache-ul pivot este generat automat. Dacă nu bifați această opțiune, va trebui să reîmprospătați manual tabelul pivot manual pentru a genera cache-ul pivot.
7. Faceți clic pe OK.

Acum, când salvați fișierul cu tabelul pivot, veți observa o dimensiune redusă a fișierului.

Păstrați memoria cache pivot și ștergeți datele sursă

O altă modalitate de a reduce dimensiunea fișierului Excel este de a șterge datele sursă ale unui tabel pivot și de a păstra numai memoria cache pivot.

Acest lucru funcționează întrucât o memorie cache pivot este o imagine în oglindă a datelor dvs. și nu accesați datele sursă atunci când faceți modificări în tabelul pivot, în schimb, utilizați memoria cache pivot.

Mai jos sunt rezultatele când am salvat fișierul Excel fără datele sursă.

Pentru a face acest lucru, trebuie doar să ștergeți datele (fie ștergând datele, fie foaia de lucru care conține datele).

Când doriți să recuperați datele sursă, faceți dublu clic pe celula „Grand Total” din tabelul pivot și va genera instantaneu întregul set de date într-o nouă foaie de lucru.

Comprimă fișierul (ZIP IT)

Acest lucru se poate face în afara aplicației Excel.

Când arhivați un fișier Excel, veți vedea imediat o reducere a dimensiunii de aproximativ 10-15%.

Apoi, puteți partaja acest fișier zip prin e-mail, iar persoana care îl primește poate dezarhiva fișierul și utiliza registrul de lucru Excel.

Iată pașii pentru Zip un fișier Excel (pentru Windows):

1. Faceți clic dreapta pe fișierul Excel
2. Plasați cursorul pe opțiunea Trimiteți către.
3. Faceți clic pe opțiunea „Dosar comprimat (zip)”.

Aceasta va crea un fișier comprimat pentru registrul de lucru Excel selectat.

Veți observa, de asemenea, o reducere a dimensiunii fișierului (așa cum se arată mai jos).

Rezultatele testului

Cu această tehnică, am reușit să reduc dimensiunea fișierului registrului de lucru Excel cu 8%.

Această tehnică funcționează mai bine pentru fișierele care se află în intervalul 20-50 MB. Pe măsură ce mărimea fișierului crește, reducerea procentuală nu este foarte mare.

Eliminați formatarea datelor

Formatarea datelor, cum ar fi aplicarea unei culori de fundal sau adăugarea de chenare sau modificarea stilului fontului, se pot adăuga la dimensiunea fișierului.

Deși, pe baza testelor mele, am observat că nu are un impact uriaș.

Mai jos sunt dimensiunile a două registre de lucru Excel, unul cu 2,6 milioane de puncte de date (într-o foaie de lucru), iar celălalt cu aceleași date, dar formatarea aplicată acestuia (culoarea celulei, chenar și cursiv).

Rezultatele testului

Puteți vedea că diferența dintre cele două fișiere nu este atât de mare (deși are un impact atunci când aveți o mulțime de date).

Fișierul cu date este de 14,6 MB, iar fișierul cu date și formatare este de 14,9 MB.

Deci, dacă doriți să reduceți dimensiunea fișierului din registrul de lucru Excel, puteți căuta formatarea de care nu aveți nevoie. Dacă îl eliminați, puteți economisi spațiu pe disc.

Iată pașii pentru a elimina instantaneu toate formatările, păstrând în același timp datele intacte:

1. Selectați întregul set de date (din care doriți să eliminați formatarea).
2. Faceți clic pe fila Acasă.
3. În grupul Editare, faceți clic pe „Ștergeți”.
4. Faceți clic pe „Ștergeți formatele”.
5. Salvați registrul de lucru.

Eliminați formatarea condiționată

La fel ca formatarea obișnuită a datelor, și formatarea condițională se adaugă la dimensiunea fișierului.

Cu toate acestea, pe baza testelor mele, am observat că diferența este mai mică (cu excepția cazului în care aveți mai multe reguli aplicate mai multor coli cu seturi de date imense).

Cu toate acestea, este o idee bună să eliminați formatarea condițională din orice set de date în care nu aveți nevoie de ea.

În afară de dimensiune, există un alt motiv bun pentru a elimina formatarea condiționată - SPEED.

Formatarea condiționată este volatilă și ori de câte ori există vreo modificare în foaia de lucru, aceasta se recalculează. Acest lucru poate duce la un registru de lucru mai lent.

Rezultatele testului

Am creat un fișier cu 27,2 milioane de puncte de date și i-am aplicat 5 reguli de formatare condiționată.

Diferența de dimensiune pare să fie de doar câțiva KB.

Citește și: Sfaturi pentru a face rapide foile de calcul Excel lent

Sfaturi pentru îmbunătățirea performanței Excel

În timp ce metodele de mai sus vă vor ajuta să reduceți dimensiunea fișierului Excel, iată câteva sfaturi suplimentare pentru a îmbunătăți performanța registrelor de lucru Excel:

1. Evitați utilizarea formulelor volatile. În afară de creșterea dimensiunii fișierului, formulele volatile vă fac, de asemenea, să aveți cărți de lucru lente. Exemple de funcții volatile includ RAND, TODAY, OFFSET, INDIRECT etc.
2. Dacă aveți nevoie să creați rezumate ale unui set de date mare, în loc să folosiți formule, vedeți dacă puteți utiliza în schimb un tabel pivot. Un tabel pivot este rapid și foarte intuitiv în comparație cu formulele.
3. Dacă extrageți date din mai multe surse sau combinați date din mai multe fișiere, utilizați Power Query pentru a obține și transforma datele. Puteți salva datele ca o conexiune Power Query în loc să le păstrați în foaia de lucru. Puteți crea cu ușurință tabelul pivot utilizând conexiunile create utilizând Power Query.
4. Convertiți datele tabulare într-un tabel Excel și utilizați tabelul Excel în formule. Face ca registrul dvs. de lucru să funcționeze mai rapid, iar formulele create folosind referințele structurate ale tabelului Excel sunt mai ușor de gestionat (deoarece acestea sunt mai intuitive).
5. Evitați să faceți referire la întregul rând sau coloană într-o formulă. Acest lucru vă va ajuta formulele să fie mai eficiente.
6. Dacă aveți o mulțime de formule în registrul de lucru, dezactivați Calculul automat și treceți la calculul manual. Procedând astfel, de fiecare dată când există o modificare a foilor de lucru, formulele nu s-ar recalcula. Puteți face această modificare accesând fila Formula și schimbând „Opțiuni de calcul”.

Sper că veți găsi utile metoda și sfaturile din acest tutorial.

Ce altceva aș mai putea adăuga la acest tutorial pentru a-l face mai util? Vă rog să mă anunțați în secțiunea de comentarii.