Összetett és gyakori problémát kísérlek meg orvosolni a mai bejegyzésben, mégpedig az óriási méretűre nőtt, lomha és instabil Excel fájlokét.
Köztudott, hogy az Excel nem óriási adathalmazra lett kitalálva. Hiába az 1.048.576 ( 2^20) sor valamint a 16.384 (2^14 ) oszlop (a 2007-es verziótól): ha az első oszlop minden celláját feltöltjük mondjuk 1-gyel, akkor a fájl mérete (xlsm kiterjesztésnél) máris 5,4 MB-ra ugrik.
Az, hogy mi a túl nagy méret, persze relatív. Nálam már a 10-20 MB-is Excel fájlok is nagyobbnak számítanak, de ismerőseim vígan eldolgozgatnak akár több száz MB-os fájlokkal is (mondjuk mire meg tudják nyitni, addig megisszák a kávét, elmennek mosdóba és átnézik az aznapi teendőiket).
Lássuk a méretnövekedés leggyakoribb okait:
1. Üres munkalapok: lásd az erről szóló posztomat:
2. a munkalap vagy a munkafüzet korrupttá vált
Azt, hogy egy munkafüzet korrupttá tud válni, nem volt újdonság számomra. Viszont hogy egyetlen munkalappal is megeshet ugyanez, az már igen! Ilyenkor legvégső esetben újra kell építeni a munkalapot nulláról, ha szerencsénk van ugyanabban a munkafüzetben
3. (Sérült) külső hivatkozások eltávolítása:
A külső hivatkozás legárulkodóbb jele, hogy a fájl megnyitásakor az alábbi üzenet jelenik meg:
Szintén árulkodó jel, hogy az Adatok menün belüli “Csatolások szerkesztése” opció aktív:
A hivatkozásokat is itt ellenőrizhetjük (Adatok menü – Csatolások szerkesztése):
Ha egy hivatkozást meg akarunk szüntetni, akkor kijelöljük a megfelelő sorát (lásd a fenti képen), majd a “Csatolás megszüntetése” gombra kattintunk. Azonban ez sem mindig kivitelezhető, olykor egyszerűen nem tudjuk törölni. Javaslatok erre az esetre:
A. Képletek menü alatti Névkezelő: töröljük a felesleges névtartományokat
B. Kezdőlap menüben található Feltételes formázás: töröljük a nem használatos feltételeket
C. Adatok menü, Érvényesítés: szükségtelen feltételek törlése
D. Képletek, melyek küldő hivatkozásokat tartalmaznak: a “[” jelre (idézőjelek nélkül) keressünk rá a munkalapokon a CTRL+F paranccsal majd módosítsuk ezeket a képleteket vagy illesszük be a kapcsolódó cellák tartalmát értékként.
A képletek vizuális ellenőrzéséhez alkalmazzuk a Képletek menüben lévő Képletek opciót (megnövelheti a képleteket tartalmazó oszlopok szélességét):
E. Kompatibilitási vizsgálat: Ha korábbi verziójú Excel fájllal van dolgunk. Csekkolása: Fájl menü – Információ – Munkafüzet vizsgálatán belüli Problémák ellenőrzése – Kompatibilitás ellenőrzése:
Ha ezen opciók után az Adatok menüben lévő 
még mindig aktív: munkafüzet mentése, bezárása, majd újbóli megnyitása.
4. Felesleges sorok/oszlopok – az egyik leggyakoribb ok. Előfordulhat, hogy a cellák egy adott tartományban üresek, viszont pl. korábbi formázás miatt az Excel beleveszi ezen cellákat a használt tartományba (angolul used range-be). Erről a témáról az alábbi posztomat ajánlom:
Velem is megesett, hogy egy munkalapon pár ezer sor volt kitöltve, de a “used range” kétszázezer-valahány soros volt.
5. Excel munkalapokon lévő objektumok, mint pl. grafikonok, képek, szövegdobozok ActiveX-vezérlők stb. Egyik korábbi cégemnél a heti riportom mérete minden konszolidáció után nőtt kb. 1 MB-tal. Nem értettem az okát, hiszen az adatmennyiség nem indokolta. A fájl tizenöt munkalapból állt és akkoriban még manuálisan végeztem az adatmásolást. A munkalapok bal felső sarkában ott volt a céges logó, képként beillesztve. Valahányszor az adatokat kimásoltam az input fájlokból (amik szintén tartalmazták a logót) és beillesztettem a riportba, a riportban lévő logóra rámásoltam az input fájlban lévő logót. Mivel a képek teljesen fedték egymást, szabad szemmel nem lehetett észrevenni.
6. Fájl kiterjesztése
A jó öreg xls kiterjesztést a 2007-es verziótól felváltotta az xlsx (makrómentes) és xlsm (makrót is tartalmazható) változat. Ez utóbbiak XML alapú formátumok. Sokan nem ismerik a – szintén 2007-es verzióban debütált – xlsb kiterjesztést. Ez bináris formátumban tárolja az információkat, jobb tömörítést használ, makrókat is tartalmazhat és a nagyobb méretű munkafüzeteknél javallott. Ha xlsb-ben mentjük el a munkafüzetet, gyorsabb lesz a megnyitás/mentés és legtöbbször a fájl mérete is csökken – olykor drasztikusan. A bevezetőben említett 5,4 MB-os xlsm fájlt elmentettem xlsb formátumban és a mérete kevesebb mint a felére, 2,58 MB-ra zsugorodott!
A fentebbi pontokból kiindulva létrehoztam egy olyan bővítményt, ami – ha nem is mindent lefedve – pára kattintást követően számos műveletet elvégez helyettünk, ezáltal csökkentve munkafüzetünk méretét. Használata:
- lépés: másoljuk be az xlam kiterjesztésű fájlt az Excel alapértelmezett bővítmény mappájába. Ez nálam a következő (Windows 10 alatt):
A “Roland” helyett természetesen a saját felhasználónevet kell beírni.
2. lépés: adjuk hozzá a bővítményt. Erre többféle módszer létezik, én kettőt ismertetek:
Első módszer:
Fájl menü – Beállítások – Bővítmények – a Kezelés szekcióban lévő legördülő listából válasszuk ki az “Excel-bővítmények” opciót, majd OK:
Tegyük be a pipát a szóban forgó név elé, majd OK:
Második módszer:
Fejlesztőeszközök menü – Excel bővítmények:
Itt rögtön az imént említett bővítmények listáját láthatjuk. Tegyük be a pipát a szóban forgó név elé, majd OK:
Az OK gomb megnyomása után megjelenik az űrlap, amin különböző lehetőségek közül választhatunk:
A bővítmény használatára egy példa:
Tegyük fel, hogy az alábbi munkalap megtisztításával szeretnénk a fájl méretét csökkenteni:
A számozások jelölik azon lépéseket, melyek végrehajtásával csökken a méret:
1: felesleges sorok/oszlopok törlése
2: külső hivatkozás eltávolítása
3: alakzatok törlése és a grafikon megtartása
+ a fájl mentése a jobb tömörítésű .xlsb formátumba
A lépések animált gif-ként szemléltetve:
Mint látható, a bővítmény – miután betöltjük – automatikusan megnyit egy űrlapot. Ha az űrlapot bezárjuk, a bővítményt el kell távolítani (kivenni a neve elől a pipát a Fejlesztőeszközök menü – Excel bővítmények megnyitása után), majd újból hozzá kell adni. Ha másolatot hozatunk létre,akkor az az eredeti munkafüzet mappájában kerül mentésre.
A bővítmény letölthető innen.
Ismert hátulütői:
– nem működik rejtett munkalapok esetében
– jelszóval védett lapok esetén csak akkor működik, ha a jelszó közös
– az automatikus újraszámolást nem veszi figyelembe. Vagyis ha a munkafüzetben sok a képlet (főleg az ún. volatile képlet): a makró futási ideje megnőhet.
Figyelmeztetés: a bővítmény használatakor csak az a munkafüzet legyen megnyitva, ahol a méretet csökkenteni akarjuk. Mindig legyen az adott fájlról biztonsági mentésünk. A makrók miatt Excelben a visszavonás (undo) gomb nem fog működni!
Remélem hasznos volt a poszt.
Kérdésed, észrevételed van? Dobj egy emailt: xlmotyo@gmail.com
