8 راه برای کاهش حجم فایل های اکسل

 فایل‎های اکسل بسیار کند و سنگین می‎شوند زیرا داده / فرمول و تصاویر زیادی به آنها اضافه می شود. گاهی اوقات می‎توانیم از داده های غیر ضروری اجتناب کنیم و حجم فایل اکسل را نرمال نگه داریم ولی گاهی اوقات هم این کار ممکن نیست.
از آنجایی که اکسل یک ابزار مبتنی بر وب نیست، بیشتر از طریق ارسال فایل های اکسل به وسیله ایمیل یا به اشتراک گذاری آن‎ها در شبکه شرکت با هم کار می کنیم و یک فایل بزرگ اکسل که حجم آن به عنوان مثال 100 مگابایت است، به سختی با سیستم‎های دیگر به اشتراک گذاشته می شود. در حالی که هیچ راه جادویی برای کاهش اندازه یک فایل اکسل وجود ندارد، چندین تکنیک وجود دارد که می توانید با استفاده از آنها داده های غیر ضروری را حذف کنید.
توجه: تاثیر این تکنیک ها برای کاهش حجم فایل اکسل، به داده های شما بستگی دارد. برای بعضی ها داده ها این تکنیک ها می توانند منجر به کاهش حجم فایل تا 50 درصد شود و برای بعضی ها داده ها، تنها می تواند حجم فایل را تا 5 درصد کاهش دهد.

[برای اطلاع از روش های افزایش سرعت و بهینه سازی فایل های اکسل این مطلب را مطالعه کنید.]

این آموزش موارد زیر را پوشش می دهد:
1. نکاتی برای کاهش حجم فایل اکسل
حذف workbook ها و داده های غیر ضروری
تبدیل به فرمت دودویی (XLSB)
XLSB چیست؟
XLSB در مقابل XLSX / XLSM
حذف فرمول های غیر ضروری
فشرده سازی تصاویر (قبل و بعد از آپلود)
حذف Pivot cacheبرای صرفه جویی در فضا
نگهداری Source Data وحذفPivot کش
نگهداری Pivot cacheو حذف Source Data
فشرده سازی فایل (ZIP IT)
حذف فرمت بندی داده
حذف فرمت بندی شرطی
2. نکاتی برای بالابردن عملکرد اکسل

نکاتی برای کاهش حجم فایل اکسل

در ادامه تکنیک هایی بیان می شود که می توانید با استفاده از آنها حجم فایل اکسل خود را کاهش دهید. برای هرکدام از این نکات، من تست هایی انجام داده ام و عکسهایی گرفتم تا تأثیر آنها را روی حجم فایل نشان دهم. نتایجی که شما دریافت می کنید به داده ها و فایلهای شما بسیار وابسته است. بر اساس فایلی که دارید، می توانید از یک یا چند مورد از این نکات استفاده کنید.

حذف شیت ها / داده های غیر ضروری

بهترین راه حل ها اغلب ساده ترین ها هستند. این تکنیک فراتر از یک مفهوم معمولی در اکسل است. اگر فایلی دارید که دارای تعدادی worksheet است که مورد نیاز نیستند (و در هیچ فرمولی استفاده نشده اند)، سعی کنید آن Worksheet ها را حذف کنید. این تکنیک یک رابطه مستقیم با اندازه فایل اکسل دارد. بنابراین، به محض این که برخی از worksheet ها را حذف کنید و Workbook را ذخیره کنید، حجم فایل فورا کاهش خواهد یافت.

نتایج تست

من یک فایل اکسل با 10 شیت ایجاد کردم که در آن هر برگه دارای 2.6 میلیون سلول است (26 ستون با 100000 داده در هر ستون).

حجم خروجی این فایل 143 مگابایت است (و زمان زیادی برای ذخیره کردن و باز کردن می گیرد).

وقتی من پنج شیت را حذف کردم، سایز فایل به نصف کاهش یافت. همانطور که می بینید، داده هایی که شما روی شیت خود دارید، با سایز فایل ارتباط مستقیم دارد.

تبدیل به فرمت دودویی (XLSB)

آیا می دانید که با ذخیره ی یک فایل اکسل در فرمت XLSB می توان سایز فایل را کاهش داد؟
کاری که شما باید انجام دهید این است که هنگام ذخیره یک Workbook ، از فرمت xlsb. استفاده کنید.
مراحل انجام این کار به شرح زیر است:
1. روی تب File کلیک کنید.

2. روی Save As کلیک کنید.

3. روی Browse کلیک کنید.

4. در کادر محاوره ای Save As، نوع فایل را به Workbook دودویی اکسل تغییر دهید (.xlsb)

نتایج تست
یک فایل اکسل داشتم که 14.6 مگابایت فضا گرفته بود.
پس از تبدیل همان فایل به فرمت XLSB، توانستم حجم آن را تا 10.4 مگابایت کاهش دهم (یک کاهش اندازه حدود 28٪).

XLSB چیست؟

هنگامی که یک فایل اکسل (.xlsx یا .xlsm ذخیره می کنید)، این فایل در فرمت XML ذخیره می شود.
به عنوان مثال، اگر پسوند فایل XLSX را به ZIP تغییر دهم و سپس آن را باز کنم، چیزی که دریافت می کنم به صورت زیر است:

و اگر من بعدا به فولدر ‘xl’ بروم و سپس به فولدر worksheet بروم ، شیت های آن فایل را می بینم (و این شیت ها به صورت یک داکیومنت XML ذخیره شده اند).

اکنون با یک فایل XLSB همین کار را انجام می دهیم.

ابتدا پسوند فایل را به ZIP تغییر می دهم، سپس آن را باز می کنم و در sheet1 قرار می دهم. چیزی که شما دریافت می کنید به صورت زیر است:

شما متوجه خواهید شد که XLSX و XLSB یک شیت را در فرمت متفاوتی ذخیره می کنند.

پسوند XLSX / XLSM، آن را به صورت یک فایل XML ذخیره می کند و پسوند XLSB آن را به صورت یک فایل باینری (.bin) ذخیره می کند. بدون استفاده از تکنیک های زیاد، فایلهای XML در مقایسه با یک فایل باینری از نظر حجم بزرگ تر هستند.

بنابراین هنگامی که شما workbook  اکسل خود را در فرمت XLSB ذخیره می کنید، اندازه فایل کاهش می یابد. در مثال بالا ما حجم فایل حدود 30٪ کاهش یافته بود.

XLSB در مقابل XLSX / XLSM

هنگامی که از فرمت XLSB استفاده می کنید، کاهش حجم یک مزیت بزرگ است. اما هنگام کار با فرمت XLSB، باید کمی محتاط باشید.

مزایای فرمت XLSB:

1. این فرمت حجم فایل اکسل را کاهش می دهد. زمانی که شما فایل های بزرگ دارید و می خواهید آن را از طریق ایمیل به اشتراک بگذارید، این فرمت می تواند مفید باشد. شما می توانید با ذخیره کردن فایل در فرمت XLSB، به سرعت حجم را کاهش دهید.

2. فایل های XLSB سریعتر از فایل XML باز و ذخیره می شوند. بر اساس تست های محدود شده ای که من انجام دادم ، فایل های XLSB 25٪ تا 50٪ سریعتر باز و ذخیره می شدند. این تفاوت تنها در فایل های بزرگ اکسل مشهود است. در فایل های کوچکتر، هر دو XLSB و XLSX سریع هستند.

در حالی که XLSB عالی به نظر می رسد، چند دلیل وجود دارد که نشان می دهد شما  باید در استفاده از فایل های XLSX / XLSM محتاط باشید:

1. با فرمت XLSB، شما نمی‌توانید بفهمید که این فرمت شامل کد ماکرو است یا خیر . این امر آن را ریسک پذیر می‌کند زیرا می‌توان از آن برای اجرای کد مخرب استفاده نمود.

2. فایلهای XLSB در فرمت فایل دودویی اختصاصی رمزگذاری شده اند در حالی که XML یک فرمت فایل open-source خواندنی است. به این معنی که اگر از ابزارهای شخص ثالث با اکسل استفاده می کنید بهتر است از فرمت XML استفاده کنید.

3. Power Query نمی تواند داده ها را از یک فایل XLSB بخواند. بنابراین اگر از Query Power استفاده می کنید بهتر است داده های خود را در فرمت های XLSX / XLSM ذخیره کنید.

به عنوان یک قاعده کلی، اگر حجم فایل شما کوچک است (کمتر از 5 مگابایت)، بهتر است که از فرمت های XLSX / XLSM. استفاده کنید.

بر اساس آنچه من از مردم شنیدم و در بسیاری از انجمن ها خواندم، بسیاری از افراد ترجیح می دهند در اکسل از XLSB به عنوان فرمت فایل استفاده کنند.  در اینجا یک مقاله خوب برای درک فرمت XLSB وجود دارد.

حذف فرمول های غیر ضروری

فرمول های خیلی زیاد حجم فایل شما را اشغال می کنند.

بنابراین اگر یک مجموعه داده دارید که آن را نیاز دارید، اما به فرمول های  آن نیاز ندارید بهتر است که این فرمولها را به مقادیر (Value) تبدیل کنید.

در اینجا مراحلی که به سرعت فرمول ها را به مقادیر تبدیل می کند بیان می کنیم:

1. کل worksheet / مجموعه داده ها را انتخاب کنید.

2. کلید F5 را فشار دهید.

در کادر محاوره ای Go To  روی « Special » کلیک کنید.

3. در کادر محاوره ای Go To ، Formulas انتخاب کنید.

4. OK را کلیک کنید.

مراحل فوق، تمام سلول هایی که در آن فرمولی وجود دارد ، انتخاب می کند.

برای تبدیل این فرمول ها به مقادیر از مراحل زیر استفاده کنید:

1. سلولهای انتخاب شده را کپی کنید (control + C)

2. روی تب Home کلیک کنید.

3. در گروه Clipboard روی Paste کلیک کنید.

مراحل فوق تمامی سلول های دارای فرمول را به مقادیر تبدیل می کند.

نتایج تست

من یک فایل اکسل با 2.7 میلیون نقاط داده (2648 × 1048 × 26) ایجاد کردم و سپس سه ستون دارای فرمول اضافه کردم (SUM، COUNT و VERAGE).

سپس این فرمولها را به مقادیر تبدیل کردم و فایل را با نام دیگری ذخیره کردم.

نتیجه به صورت زیر است:

فایل بدون فرمول از نظر حجم تقریبا 8٪ کمتر است ( توجه داشته باشید که من فقط 3 ستون دارای فرمول داشتم).

در صورتی که فرمول های زیادی دارید، این فرمول ها می تواند حجم را تا مقدار زیادی افزایش دهد.

نکته: فرمول های فرار واقعا می توانند حجم فایل شما را افزایش دهند. به عنوان بخشی از تست، وقتی که فرمول SUM را با یک فرمول فرار (OFFSET) جایگزین کردم، منجر به یک پرش در حجم فایل (از 186 مگابایت تا 198 مگابایت) شد.بنابراین اگر فرمول های فرار دارید که می توانید با فرمول های غیر فرار جایگزین کنید، یا می توانید آنها را به مقادیر تبدیل کنید، این کار به شما کمک می کند تا حجم فایل راکاهش دهید.

فشرده سازی تصاویر (قبل و بعد از آپلود)

اگر با فایلهای اکسلی که دارای تصاویر هستند ، کار می کنید باید این تصاویر را بهینه سازی کنید و مطمئن شوید که حجم آن ها کم شده است. مشکل تصاویر این است که وقتی فشرده سازی می کنیم، کیفیت آنها نیز کم می شود.

هنگام کار با تصاویر، بهترین راه برای کم کردن حجم فایل اکسل، فشرده سازی تصاویر قبل از قرار دادن آن در اکسل است.

یک ابزار آنلاین خوب برای انجام این کار TinyPNG است (یا می توانید از فتوشاپ یا سایر ابزار مشابه استفاده کنید). TinyPNG ابزاری رایگان است که می تواند حجم  یک تصویر را تا 90٪ کاهش دهد و همچنان کیفیت تصویر را در حالت یک نرمال نگه دارد.

در تصویری که در زیر مشاهده می کنید ، توانستم تصویر را با حجم 3.9 مگابایت تا 331 کیلوبایت  فشرده سازی کنم بدون اینکه کیفیت آن کاهش یابد.

یکبار که این تصاویر فشرده شده را به اکسل آپلود کردید، بعد از آن می توانید با استفاده از گزینه داخلی در Excel، آن را فشرده کنید.

در اینجا مراحل لازم برای فشرده سازی تصویر در اکسل را بیان می کنیم:

1. تصویری را که می خواهید فشرده سازی کنید را انتخاب کنید.

2. روی تب Picture Tools Format کلیک کنید. این یک تب متنی است که فقط وقتی روی تصویر کلیک میکنید ظاهر میشود.

3. در گروه Adjust ، بر روی گزینه “Compress Picture” کلیک کنید.

4. در کادر محاوره ای Compress Picture:

1. اگر می خواهید تمام تصاویر موجود در workbook را فشرده کنید، « Apply only to this picture» را انتخاب نکنید.

2. برای حداکثر فشرده سازی (96 پیکسل) ایمیل را انتخاب کنید.

3. روی OK کلیک کنید.

4. فایل را ذخیره کنید.

این مراحل همه تصاویر را فشرده می کند و به شما کمک می کند تا سایز فایل workbook اکسل خود را کاهش دهید.

نتایج تست

من گزینه ی فشرده سازی تصویر در Excel را برای یک تصویر استفاده کردم.

نتایج به صورت زیر است:

با استفاده از گزینه ی فشرده سازی تصویر، حجم فایل از 445 کیلو بایت به 43 کیلوبایت کاهش یافت (کاهش 90 درصدی در اندازه فایل).البته به تصویر نیز بستگی دارد. یک تصویری که قبلا فشرده شده موجب کاهش زیادی درحجم فایل نخواهد شود.

حذف Pivot cache برای ذخیره فضا

اگر با جداول Pivot کار می کنید و با فایلهای بزرگ اکسل مواجه هستید، آگاهی از Pivot cache می تواند برای شما مفید باشد.

هنگامی که یک جدول Pivot را با استفاده از یک مجموعه داده(data set) ایجاد می کنید، اکسل Pivot cache را به طور خودکار ایجاد می کند.

Pivot cache یک شیء است که یک کپی از منبع داده (data source) را نگه می دارد. با وجود اینکه شما نمیتوانید آن را ببینید، بخشی از workbook شماست که به جدول Pivot متصل است. هنگامی که شما هر گونه تغییر در جدول Pivot ایجاد می کنید، این از منبع داده استفاده نمی کند، بلکه از Pivot cache استفاده می کند.

هنگام تغییر در جدول Pivot ، درحالیکه شما فکر می کنید به طور مستقیم به داده های منبع وصل شده اید ، در واقع، شما به Pivot cache(و نه داده های منبع) دسترسی پیدا می کنید.

به دلیل اینکه شما یک کپی از مجموعه داده ها ایجاد می کنید، مقداری فضا اشغال می شود. به محض این که یک جدول Pivot ایجاد کردید، متوجه خواهید شد که اندازه فایل اکسل افزایش می یابد.

در مثال بالا، هنگامی که  جدول Pivot را اضافه می کنم، اندازه فایل دو برابر می شود.

در حال حاضر، دو روش برای کاهش حجم فایل در هنگام کار با جداول Pivot وجود دارد.

1. نگهداری داده های منبع و حذف Pivot cache

2. نگهداری Pivot cache و حذف داده های منبع

بیایید هر دو تکنیک را بررسی کنیم.

نگهداری داده های منبع و حذف Pivot cache

هنگامی که شما با جدول Pivot  کار می کنید و آن را ذخیره می کنید، می توانید اکسل را وادار کنید که تنها داده های منبع را ذخیره کند.

انجام این کار فایل اکسل شما را سبک تر می کند و شما می توانید در صورت نیاز Pivot cache را ایجاد کنید.

در زیر تفاوت در حجم فایل را مشاهده میکنید زمانی که شما یک فایل اکسل را با و بدون Pivot cache ذخیره می کنید.

در اینجا مراحلی وجود دارد که  Pivot cache را ذخیره  نکنیم:

1. هر سلول در جدول Pivot را انتخاب کنید.

2. روی تب Analyze کلیک کنید.

3. روی آیکون Options در جدول Pivot  کلیک کنید.

4. در کادر محاوره ای Pivot Table Options ، روی Data tab کلیک کنید.

5. در گزینه های Pivot Table Data ، تیک ‘Save data source with file’ را بردارید. این گزینه تضمین می کند که Pivot cache هنگام ذخیره workbook (و به کاهش اندازه فایل اکسل کمک می کند) ذخیره نمی شود.

6. ‘ Refresh data when opening the fileرا انتخاب کنید. این گزینه تضمین می کند که به محض باز کردن workbook شما، جدول Pivot مجددا بارگذاری می شود و Pivot cache به طور خودکار ایجاد می شود. اگر شما این گزینه را انتخاب نکنید، شما باید به صورت دستی جدول Pivot  را بارگذاری کنید و Pivot cache را ایجاد کنید.

7. OK کلیک کنید.

زمانی که فایل را با جدول Pivot  ذخیره می کنید، متوجه خواهید شد که حجم فایل کاهش یافته است.

نگهداری  Pivot cache و حذف داده های منبع

یکی دیگر از روش های کاهش حجم فایل اکسل این است که داده های منبع یک جدول Pivot را حذف کرده و فقط Pivot cache را نگه دارید.

Pivot cache به صورت یک تصویر آینه ای از داده های شما عمل می کند و شما هنگام تغییر در جدول Pivot به داده های منبع دسترسی ندارید، به جای آن شما از Pivot cache استفاده می کنید.

من فایل اکسل را بدون منبع داده ذخیره کردم که نتایج آن در زیر بیان شده است.

برای انجام این کار، فقط داده ها را حذف کنید ( با حذف داده ها یا worksheet  که دارای داده است).

هنگامی که شما می خواهید داده منبع را به دست آورید، روی سلول “Grand Total”جدول Pivot دابل کلیک کنید این کار بلافاصله تمام مجموعه داده ها را در یک worksheet جدید تولید می کند.

فشرده سازی فایل (ZIP کردن فایل)

این کاری است که شما می توانید خارج از برنامه اکسل انجام دهید. هنگامی که یک فایل اکسل را فشرده می کنید، بلافاصله کاهش حجم در حدود 10 تا 15 درصد مشاهده خواهید کرد.

پس از آن شما می توانید فایل زیپ شده را از طریق ایمیل به اشتراک بگذارید، و شخص دریافت کننده می تواند فایل را از حالت فشرده خارج کند و از workbook اکسل استفاده کند.

در اینجا مراحلی برای فشرده کردن یک فایل اکسل (برای ویندوز) بیان می شود:

1. روی فایل اکسل راست کلیک کنید.

2. مکان نما خود راروی Send نگه دارید تا گزینه های آن را مشاهده کنید.

3. روی گزینه ‘Compressed (zipped) folder’ کلیک کنید.

این مراحل یک فایل zip شده برای workbook اکسل انتخاب شده ، ایجاد خواهد کرد.

بعد از این مراحل شما می توانید کاهش حجم فایل را مشاهده کنید (همانطور که در زیر نشان داده شده است).

نتایج تست

با استفاده از این تکنیک، من توانستم حجم فایل اکسل را تا 8٪ کاهش دهم.

این تکنیک برای فایل هایی که در محدوده 20-50 مگابایت هستند بهتر عمل می کند. با افزایش حجم فایل، کاهش درصد خیلی زیاد نیست.

حذف فرمت بندی داده

فرمت بندی داده ها مانند اعمال رنگ پس زمینه یا اضافه کردن border یا تغییر سبک فونت ، می تواند به حجم فایل اضافه کند. اگر چه براساس تست هایی که انجام دادم متوجه شدم که تاثیر زیادی ندارد.

در زیر اندازه دو workbook اکسل را می بینید، یکی با 2.6 میلیون نقطه داده (در یک worksheet ) و دیگری با همان داده ها اما با فرمت بندی اعمال شده بر روی آن (رنگ سلول، borderو حالت نوشتن).

نتایج تست

همانطور که می بینید تفاوت بین دو فایل زیاد  نیست (اگر چه زمانی که مقدار زیادی داده دارید فرمت بندی می تواند تاثیر گذار باشد).

حجم فایل با داده ها 14.6 مگابایت است و حجم فایل با داده ها و قالب بندی 14.9 مگابایت است.

بنابراین اگر می خواهید حجم فایل workbook اکسل خود را کاهش دهید، جستجو کنید و فرمت بندی که مورد نیاز نیست ،حذف کنید. حذف آن می تواند مقداری از فضای دیسک شما را آزاد کند.

در اینجا مراحلی را بیان میکنیم که فورا تمام فرمت بندی را حذف می کند، در حالی که داده ها را سالم نگه می دارد:

1. کل مجموعه داده ها را انتخاب کنید (که می خواهید فرمت بندی آنها را حذف کنید).

2. روی تب Home کلیک کنید.

3. در گروه Editing  روی Clear کلیک کنید.

4. روی Clear Formats کلیک کنید.

5. workbook را ذخیره کنید.

حذف قالب بندی شرطی

همانند قالب بندی داده ها به طور منظم، قالب بندی شرطی نیز به حجم فایل اضافه می کند.
با این حال، بر اساس تست هایی که من انجام دادم ، متوجه شدم که تفاوت زیاد نیست (مگر اینکه چندین قانون به چندین صفحه با مجموعه داده های بزرگ اعمال شود). با این وجود، بهتر است قالب بندی شرطی را از هر مجموعه داده ای که نیاز ندارید حذف کنید. به غیر از حجم، دلیل دیگر برای حذف قالب بندی شرطی،سرعت است. قالب بندی شرطی بی ثبات است و هر زمان که تغییری در هر شیت داده شود، مجددا محاسبه می شود. که می تواند منجر به فایل های کندتر شود.

نتایج تست
من یک فایل با 27.2 میلیون نقطه داده ایجاد کردم و 5 قانون شرطی به آن اعمال کردم
تفاوت در حجم تنها چند کیلو بایت است.

نکات تقویت کننده عملکرد اکسل

در حالی که روش های فوق به شما کمک می کند تا حجم فایل اکسل را کاهش دهید، در اینجا چند نکته اضافی برای بهبود عملکرد Workbook های اکسل وجود دارد: (البته در مطلب دیگری بطور مفصل به موضوع بهینه سازی فایل های اکسل پرداخته ام.)

1. اجتناب از بکارگیری فرمول های فرار . به غیر از افزایش حجم فایل، فرمول های فرار Workbook های شما را کند می کنند. نمونه هایی از توابع فرار شامل RAND، TODAY ، OFFSET، INDIRECT ، و غیره

2. اگر نیاز دارید خلاصه ای از یک مجموعه داده بزرگ ایجاد کنید، به جای اینکه از فرمول ها استفاده کنید، ببینید آیا می توانید از یک جدول Pivot استفاده کنید یا نه. یک جدول Pivot در مقایسه با فرمول ها سریع و بسیار بصری است.

3. اگر داده ها را از منابع مختلف استخراج کنید یا داده ها را از چندین فایل ترکیب کنید، از Power Query برای دریافت و تبدیل داده ها استفاده کنید. شما می توانید داده را به صورت یک اتصال Power Query به جای نگه داشتن آن در worksheet ذخیره کنید. شما می توانید به راحتی جدول Pivot را با استفاده از اتصالات ایجاد شده با استفاده از Power Query ایجاد کنید.

4. داده های جدولی خود را به یک جدول اکسل تبدیل کنید و از جدول اکسل در فرمول ها استفاده کنید. این باعث می شود که عملکرد workbook شما سریع تر شود و فرمول های ایجاد شده با استفاده از جدول Excel برای مدیریت ساده تر هستند (زیرا اینها بیشتر بصری هستند).

5. از ارجاع کل ردیف یا ستون در یک فرمول اجتناب کنید. این امر به شما کمک خواهد کرد فرمول های شما کارآمدتر باشند.

6. اگر فرمول های زیادی در workbook خود دارید، محاسبه خودکار را خاموش کنید و به محاسبات دستی سوئیچ کنید. با انجام این کار، هر بار که تغییری در worksheet ها ایجاد می کنید، فرمول ها مجددا محاسبه نمی شوند. شما می توانید این تغییر را با رفتن به تب Formula و تغییر Calculation Options انجام دهید.

امیدوارم روش و نکات این آموزش برای شما مفید باشد.