کار با فایل های سنگین اکسل و افزایش سرعت

کاهش حجم و افزایش سرعت فایل اکسل

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

1. مستقیما حجم فایل رو کاهش بدیم
   • فضاهای خالی در شیت حذف کنید.
   • شیتهای غیر لازم و Hide شدن رو حذف کنید.
   • فایل رو با پسوند xlsb ذخیره کنید.
   • فرمت هایی که روی سلول های خالی اعمال شدند رو حذف کنید.
   • فرمت های شرطی Conditional Formatting رو چک کنید.
2. زمان محاسبات رو کاهش بدیم
   • حالت اتوماتیک محاسبات اکسل رو غیر فعال کنید.
   • از Watch Window استفاده کنید تا همیشه سلول های مشخصی رو ببینید.
3. فرمول های نوشته شده رو بهینه سازی کنیم
   • از توابع Volatile استفاده نکنید.
   • از Pivot Table و Table استفاده کنید.
   • از ارجاع کل ردیف ها و ستون ها در فرمول ها اجتناب کنید.
   • از محاسبات تکراری اجتناب کنید.
   • داده ها رو موقع استفاده از توابع جستجو مرتب کنید.

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

اگر میخواید به داده های داخل فایل دست نزنید و به لحاظ محتوایی فایل تغییر نکنه، اول از این روش ها استفاده کنید. راه هایی که برای افزایش سرعت فایل از طریق کاهش حجم فایل وجود داره عبارتند از: فضاهای خالی در شیت حذف کنید. این موضوع یکی از مهم ترین علل افزایش حجم هست و حلش هم خیلی راحته. اکسل مفهومی داره به عنوان “رنج استفاده شده”. هر چقدر این رنج بزرگتر باشه حجم فایل هم بیشتر. حالا ببینیم مفهوم این عبارت چی هست؟ برای فایل های جدید که باز میکنیم، این محدوده استفاده شده، معادل سلول A1 هست. اما به محض اینکه شروع میکنیم به کار کردن، این محدوده گسترش پیدا میکنه و دورترین سلول (هم دورترین ستون و هم دورترین ردیف) از A1 رو که چیزی داخلش نوشته شده یا فرمتی داده شده رو در نظر میگیره و از A1 تا اونجا محدوده استفاده شده در نظر گرفته میشه.

  برای اینکه ببینیم محدوده استفاده شده تا کجاست، کافیه کلید ترکیبی Ctrl+End رو بزنیم. به شکل ۱ نگاه کنید. طبق شکل انتظار داریم محدوده استفاده شده تا E9 (دورترین ستون E و دورترین ردیف ۹ هست) در نظر گرفته بشه. اما چون قبلا در سلول J18 مطلبی نوشته شده و پاک شده، با زدن کلید Ctrl+End سلول J18 انتخاب میشه و محدوده استفاده شده A1:J18 خواهد بود.

شکل ۱- فایل های سنگین و افزایش سرعت فایل اکسل -محدوده استفاده شده

چطور این مشکل رو کنیم؟ باید فضای استفاده شده رو حذف کنیم. این کار رو از طریق انتخاب سطرها و ستون های مورد نظر و Delete کردن اونها انجام میدیم. شمل ۱ رو در نظر بگیرید. برای اینکه محدوده استفاده شده رو محدود کنیم، روی ستون F کلیک کرده و کلید ترکیبی Ctrl+Shift+Arrow Right رو میزنیم. بعد از انتخاب همه ستون ها، کلیک راست کرده و Delete رو میزنیم. برای ردیف هم به همین شکل عمل میکنیم. یعنی ردیف شماره ۱۰ رو انتخاب کرده و کلید ترکیبی Ctrl+Shift+Arrow Down رو شده، کلیک راست میکنیم و Delete. شیتهای غیر لازم و Hide شدن رو حذف کنید. ممکنه فایلی که به دستتون رسیده تعدادی شیت Hide شده داشته باشه. اول همه شیت ها رو Unhide کنید. بعد بررسی کنید که آیا همه این شیت ها لازم هستن یا نه. هر کدوم لازم نبود رو حذف کنید.

شکل ۲- فایل های سنگین و افزایش سرعت فایل اکسل -حذف شیت

فایل رو با پسوند .xlsb ذخیره کنید. یکی از بهترین روش ها برای کاهش حجم فایل و افزایش سرعت فایل اکسل ، ذخیره کردن با پسوند Xlsb هست. اگر فایلی دارید که حجم زیادی داده و فرمول نویسی داره، با این فرمت ذخیره کنید. با این کار همه فرمول ها و کدهای VBA همچنان کار میکنن و نگرانی بابت کارکرد فایل نخواهید داشت.البته دقت داشته باشید که امکان بازیابی فایل در این حالت از بین میره.

شکل ۳- فایل های سنگین و افزایش سرعت فایل اکسل -ذخیره با پسوند Binary

حجم اکثر فایل ها حدودا ۵۰% کاهش پیدا میکنه. اما این میزان بیشتر بستگی به محتوای فایل و نوع داده های موجود در اون داره. بعضی وقت ها حجم فایل به ۲۰% حجم فایل اصلی هم کاهش پیدا میکنه! فرمت هایی که روی سلول های خالی اعمال شدند رو حذف کنید. اگر داده های خام دارید و فرمت دهی کردید، حجم فایل شما بالا خواهد رفت. اگر فایل شما بصورتی هست که این داده ها صرفا برای انجام محاسبات استفاده میشن و قرار نیست مستقیما نمایش داده بشن، سعی کنید هیچ فرمتی نداشته باشه. فرمت رو فقط در شیت گزارشگیری و نتیجه اعمال کنید. به هیچ وجه روی بانک اطلاعاتی این کار رو انجام ندید. این موضوع ممکنه با مخالفت برخی مواجه بشه، چرا که برخی عادت دارند که داده های دیتابیس رو با رنگ و فرمت های مختلف از هم تفکیک کنن. در حالیکه باید سعی کنیم داده های دیتابیس در خام ترین حالت ممکن باشند. پس فرمت های از قبیل رنگ، کادر (Border) و هر چیزی که صرفا بخاطر زیبایی اعمال شده رو حذف کنید. دقت کنید که فرمت های تاریخ و واحد پول و … حذف نکنید. چون باعث ناخوانی داده ها میشه. فرمت های شرطی Conditional Formatting رو چک کنید. فرمت دهی شرطی یا Conditional Formatting یکی از راه های جذاب بصری سازی داده هاست. اما این ابزار باعث افزایش حجم فایل میشه. پس باید دقت داشته باشید که از این ابزار فقط به اندازه نیاز استفاده کنید. برخی کاربرها عادت دارند که فرمت رو برای کل شیت، یک ستون یا ردیف کامل اعمال کنند. خب این مسئله باعث افزایش حجم فایل خواهد شد. از قسمت Manage Rule Conditional Formatting> همه شرط ها رو چک کنید و مطمئن بشید که رنج ها فقط تا جایی که نیاز هست اعمال شده اند. اگر رنجی بیش از محدوده مورد نیاز تعیین شده بود، ویرایش کنید و محدود کنید به محدوده مورد نیاز.

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

حالت اتوماتیک محاسبات اکسل رو غیر فعال کنید. اگر محاسبه فرمول ها زمانبر هست، پس باید محاسبات رو از حالت خودکار خارج کنیم. بعبارتی از حالت Automatic به حالت Manual تغییر بدیم. با این کار به اکسل میگیم که “لازم نیست با هر بار تغییر، کل فایل رو محاسبه کنی، فقط زمانهایی که میخوایم این محاسبات رو انجام بده”. این کار از مسیر نشان داده شده در شکل ۴ انجام میشه:

شکل ۴- فایل های سنگین و افزایش سرعت فایل اکسل – غیراتومات ساختن محاسبات

زمانی که محاسبات روی حالت دستی هست، با زدن کلید F9 محاسبات کل فایل انجام میشه و کلید Shift+F9 فقط شیت فعلی. به این نکته توجه داشته باشید که وقتی محاسبات روی حالت دستی هست، با اعمال تغییرات در صفحه، نتیجه فرمول ها آپدیت نمیشه. چون ما بهش گفتیم تا زمانی که F9 نزدیم، هیچ محاسبه ای انجام نده. پس اگر فایلی دیدید که با تغییر داده ها، نتایج تغییر نمیکنه، باید بدونید که محاسبات روی حالت دستی تنظیم شده.

  از Watch Window استفاده کنید تا همیشه سلول های مشخصی رو ببینید. استفاده از Watch Window به کاهش حجم فایل کمکی نمیکنه. اما به افزایش سرعت در استفاده از فایل کمک میکنه. توضیحات کامل این ابزار در لینک زیر ارائه شده.

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

از توابع Volatile استفاده نکنید. یک لیست هشت تایی از توابع وجود داره که اصطلاحا Volatile نامیده میشن. در هر بار محاسبه، هر سلولی که حاوی این توابع باشه و همه سلولهای وابسته به این سلولها دوباره محاسبه میشن، که این مسئله باعث افزایش مدت محاسبه خواهد شد. این هفت تابع عبارتند از:

• Now
• Today
• Rand/Randbetween
• Offset
• Indirect
• Info (بسته به آرگومانهای استفاده شده)
• Cell (بسته به آرگومانهای استفاده شده)
• Sumif (بسته به آرگومانهای استفاده شده)

هر چقدر کمتر از این توابع استفاده کنید، به افزایش سرعت محاسبات فایل کمک کردید. از Pivottable و Table استفاده کنید. سعی کنید بجای استفاده از تعداد زیادی فرمول های پیچیده و طولانی، تا جای ممکن از Pivot Table برای تهیه گزارش استفاده کنید. همچنین استفاده از Table خیلی اثرگذار هست و کمک میکنه به کاهش حجم فایل. فرمول های نوشته شده در یک Table نسبت به فرمول های نوشته شده در رنج معمولی، به مراتب کمتر باعث حجم فایل میشن. از ارجاع کل ردیف ها و ستون ها در فرمول ها اجتناب کنید. دقت کنید زمان فرمول نویسی از انتخاب کل ستون ها و ردیف ها بجای تخصیص چند صد ردیف، اجتناب کنید. چرا که اکسل فقط محدوده تخصیص داده شده محاسبه میکنه نه کل ۱,۰۴۸,۵۷۶ ردیف رو!

شکل ۵-فایل های سنگین و افزایش سرعت فایل اکسل -تخصیص درست محدوده ها در فرمول

از محاسبات تکراری اجتناب کنید. از تکرار محاسبات اجتناب کنید. اجازه بدید این موضوع رو با مثال توضیح بدم. فرض کنید فرمولی دارید بصورت “=$D$۵+$E$۵″، و این فرمول رو در صد سلول استفاده کردید. یعنی یعنی تعداد کل ارجاعات به سلول میشه دویست تا. اما اگه در یک سلول مثل A5 این فرمول رو بنویسیم و اون صد سلول رو به سلول A5 وصل کنیم، تعداد ارجاعات به سلول ها نصف میشه. در واقع =$D$۵+$E$۵ بجای صد بار، یکبار محاسبه میشه. پس اگه حجم زیادی فرمول با محاسبات یکسان دارید، مطابق مثال ارائه شده اونها رو به چند قسمت بشکنید. داده ها رو موقع استفاده از توابع جستجو مرتب کنید. وقتی داده ها مرتب باشن، زودتر و راحت تر پیدا میشن! این موضوع در خصوص توابعی مثل VLOOKUP, INDEX, MATCH, SUMIF و یا هر تابعی در اکسل که به دنبال داده بخصوصی میگرده صدق میکنه. پس بهتره زمانی که از این توابع استفاده میکنیم، داده هامون مرتب (Sort) باشن تا روند جستجو سریع تر باشه.   شما با راه حل های زیادی آشنا شدید که میتونید در مورد فایل های سنگین، بسته به شرایط، از هر کدوم از این روش ها استفاده کنید. با این روش ها دیگه فایل هاتون دیر باز نمیشن و نیازی نیست زمان زیادی رو برای انجام محاسبات اتلاف کنید. آیا راه حل های ارائه شده برای افزایش سرعت فایل اکسل شما مفید بودن؟ یا هنوز با فایل های سنگین مشکل دارید؟ مسائل در رابطه با این موضوع رو در قالب کامنت در ادامه همین پست با ما در میون بذارید. راه حل های مایکروسافت برای بهبود سرعت محاسبات در اکسل رو هم نگاه کنید.