نویسنده
درباره نویسنده
بهنام خیرالهی
تعداد پست ها 53

درباره:

دانش آموخته مقطع کارشناسی دانشگاه Staffordshire University در رشته علوم کامپیوتر گرایش امنیت و مقطع کارشناسی دانشگاه Asia Pacific University در رشته فناوری اطلاعات هستم. از سال ۱۳۹۲ به ایران برگشتم و به صورت تخصصی در زمینه طراحی وب، طراحی گرافیک و برنامه نویسی NET. فعالیت دارم.

  • بهینه سازی فایل های اکسل

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

استفاده بیش از حد از توابع Volatile

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

  • ۱ ∙NOW
  • ۲ ∙TODAY
  • ۳ ∙RAND
  • ۴ ∙RANDBETWEEN
  • ۵ ∙OFFSET
  • ۶ ∙INDIRECT
  • ۷ ∙INFO
  • ۸ ∙CELL
  • ۹ ∙SUMIF

بهینه سازی توابع

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

  • COUNTIF(B:B, C2)
  • COUNTIF(B2:B11, C2)

از تکرار محاسبات باید اجتناب کنیم. فرض کنیم از یک فرمول برای ۱۰۰ سلول استفاده میکنیم، بجای استفاده از این فرمول در تمامی سلول ها میتوانیم آن را مثلا در سلول B1 تعریف کرده و آن ۱۰۰ سلول را به سلول B1 ارجاع دهیم. با انجام اینکار در واقع ان فرمول بجای ۱۰۰ بار فقط یکبار محاسبه می شود.

در زمان استفاده از توابع جستجوگر بهتر است داده ها را مرتب کنیم. فرض کنیم با استفاده از تابع VLOOKUP می خواهیم داده ای را در یک بانک اطلاعاتی جستجو کنیم. زمانیکه داده ها در بانک اطلاعات مرتب (Sort) باشند روند جستجو سریعتر انجام می پذیرد.

استفاده زیاد از Conditional Formatting

قالب بندی شرطی یا همان Conditional Formatting اگر برای کل شیت، ستون و یا ردیف بکار برده شود باعث افزایش حجم فایل می شود. پس باید دقت کنیم که از این ابزار به اندازه نیاز استفاده کنیم. ابتدا به مسیر زیر رفته و باید تمامی شرط ها را چک کرده و مطمئن شویم که محدوده ها فقط تا جایی که به آن نیاز است اعمال شده اند.

  • Home
  • Styles
  • Conditional Formatting
  • Manage Rules

کاهش حجم فایل

فضاهای خالی در یک شیت یکی از مهمترین علت های افزایش حجم فایل های اکسل می باشد. سلول هایی که به عنوان سلول های فعال یک شیت لحاظ می شوند با سلول های که به طور واقعی در آن شیت فعال هستند متفاوت می باشند. برای دانستن اینکه اکسل کدام سلول را به عنوان آخرین سلول فعال شیت در نظر گرفته می توانیم با استفاده از کلیدهای ترکیبی Ctrl+End به آخرین سلول فعال دسترسی پیدا کنیم. اگر سلولی که اکسل به عنوان آخرین سلول فعال در نظر گرفته اشتباه باشد باعث بالا رفتن بی دلیل حجم فایل می شود. برای حل این مشکل می توان سطرها و ستون های اضافه را انتخاب کرده و آنها را حذف کنیم. این مورد در افزایش حجم فایل ها تاثیر بسزایی را خواهد داشت.

بهینه سازی فایل های اکسل − w3blog.irتصویر ۱ − محدوده استفاده شده در اکسل

بهتر است تا جایی که امکان دارد در یک فایل از شیت های کمتری استفاده کنیم. اگر یک فایل دارای شیت های Hide شده باشد، اول باید تمامی شیت ها را Unhide کرده و شیت های غیر ضروری را حذف کنیم. این امر در افزایش سرعت فایل های اکسل بی تاثیر نخواهد بود.

بهینه سازی فایل های اکسل − w3blog.irتصویر ۲ − حذف شیت های اضافی

یکی از بهترین روش ها برای کاهش حجم فایل های اکسل ذخیره کردن آنها با فرمت Excel Binary Workbook می باشد. حجم اکثر فایل ها با انجام اینکار به حدود ۵۰% کاهش پیدا می کند، اما این میزان بستگی به محتوای فایل و نوع داده ها دارد.

بهینه سازی فایل های اکسل − w3blog.irتصویر ۳ − ذخیره فایل های اکسل با فرمت xlsb

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

لینک کردن به دیگر فایل ها

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

کاهش زمان محاسبات

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

  • Formulas
  • Calculation
  • Calculation Options

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

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

استفاده از Table و PivotTable

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

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

بیشتر بخوانید

  • مطلبی برای این بخش وجود ندارد.