وقتی با یک نرم افزار در طول روز ساعت ها کار می کنید، اگر کُند شود غیر قابل تحمل و آزار دهنده خواهد بود. بخصوص اینکه داده های مهمی در آن داشته باشید. بهینه سازی فایل های اکسلبه شما کمک شایانی خواهد کرد.
طی سال ها تجربه کار با اکسل و تدریس آن، هم من و هم کارکنان حرفه ای سازمان های مختلف در ایران نکاتی برای بهینه سازی فایل های اکسل جهت افزایش سرعت و کارآیی آن ها بدست آورده ایم. همچنین برخی مدرسان حرفه ای اکسل نیز تجربیاتی دارند که دانستن آن ها بسیار مفید خواهد بود. البته زمانی که داده های زیادی در یک فایل اکسل موجود نیست مشکل سرعت لمس نخواهد شد، بنابراین اگر داده های زیادی دارید و یا محاسبات و لینک های زیادی در فایل های شما وجود دارد بهتر است موارد این متن را به دقت مطالعه کرده و بکار ببندید.
رعایت نکات مهم بهینه سازی فایل های اکسل
- اجتناب از توابع Volatile
- استفاده از ستون های کمکی
- اجتناب از فرمول های آرایه ای (اگر ضروری نیستند.)
- استفاده محتاطانه از Conditional Formatting
- استفاده از Table و نامگذاری محدوده ها
- تبدیل فرمول های بلا استفادهت به Value
- تمام آدرس دهی های یک شیت در داخل خودش باشند.
- از آدرس کل یک ستون یا کل یک سطر استفاده نکنید.
- از Manual Calculation استفاده کنید.
- از تکنیک فرمول های سریع استفاده کنید.
- از آخرین بروز رسانی های اکسل استفاده کنید.
- افزونه های (Add-Ins) های غیر ضروری را غیر فعال کنید.
- برنامه Office را Repair کنید.
- اجتناب از Data Table ها
- حذف Style های بدون استفاده
- استفاده از توابع به جای عملگرها
۱- اجتناب از توابع Volatile
در میان ۴۷۱ تابع در اکسل ۲۰۱۶ برخی از آن ها از نوع Volatile هستند. فرمول های Volatile فقط و فقط در زمان اجرا نتیجه خود را بدست می آورند و با هربار تغییرات در یک فایل مجددا بروز رسانی می شوند. برخی از معروف ترین فرمول های Volatile به شرح ذیل می باشند:
- تایع ()Rand که برای تولید یک عدد تصادفی بین ۰ و ۱ استفاده می شود.
- تابع ()RandBetween که برای تولید عدد تصادفی بین دو عدد دلخواهد استفاده می شود.
- تابع ()Indirect که برای تبدیل متن به آدرس استفاده می شود.
- تابع ()Offset که برای موقعیت یابی و استخراج سلول های مورد نظر استفاده می شود.
- تابع ()Today که برای نمایش تاریخ جاری سیستم استفاده می شود.
- تابع ()Now که برای نمایش تاریخ و ساعت جاری سیستم استفاده می شود.
توابع فوق در شرایط زیر مجددا محاسبه اجرا می شوند:
- Auto Filter کردن
- بارگذاری یک فایل CSV
- دابل کلیک روی فاصله بین سطرها یا ستون ها
- استفاده از Goal Seek
- Hide و UnHide کردن سطرها و ستون ها
- درج یا حذف سطرها و ستون ها
- تغییر و یا اضافه کردن شیت ها
- تغییر و یا اضافه کردن نامگذاری ها
۲- استفاده از ستون های کمکی
داشتن ستون های کمکی برای کارهای اکسلی کار خیلی خوبی است اما اکثر کاربران اکسل از انجام این کار خجالت می کشند یا تصور می کنند کار غیر حرفه ای کرده اند. استفاده از ستون های کمکی مانع از نوشتن فرمول های آرایه ای می شود و این به معنای سرعت و کارآیی بیشتر و حتی سادگی بیشتر برای کاربران است.
مثال: فرض کنید می خواهیم بیشترین مرجوعی یک کالای خاص را بدست آوریم، از آنجایی که اکسل فرمول MaxIf ندارد باید Max و IF را بصورت تو در تو و به روش آرایه ای بنویسیم، اما اگر از یک ستون کمکی برای IF استفاده کنیم و مرجوعی های آن کالای خاص را بیرون بکشیم و سپس روی آن فرمول Max را پیاده کنیم بهینه تر خواهد بود.
۳- اجتناب از فرمول های آرایه ای (اگر ضروری نیستند.)
فرمول نویسی آرایه ای مزایای خود را دارد اما اگر مجبور نیستید از آن ها استفاده نکنید. چاره چیست؟ استفاده از ستون های کمکی.
۴- استفاده محتاطانه از Conditional Formatting
فرمت دهی شرطی خیلی دوست داشتنی است و کمک می کند داده ها و اطلاعات با نمایش بهتری دیده شوند. اما دو نکته در این ابزار همیشه مخفی و عجیب است. اول اینکه فرمت دهی شرطی Volatile است یعنی در شرایطی مجددا اجرا می شود و این یعنی احتمال کند شدن فایل های دارای فرمت دهی شرطی بالاست. دوم اینکه با کپی کردن یک سلول فرمت دهی شرطی آن نیز منتقل می شود و بعد از مدتی کار با یک فایل اگر سری به مدیریت Conditional Formatting بزنید با انبوهی از آن ها مواجه خواهید شد.
۵- استفاده از Table و نامگذاری محدوده ها
با این کار نه تنها آدرس دهی محدوده ها راحت تر می شود بلکه ویرایش و استفاده از آن ها نیز بهینه می شود.
۶- تبدیل فرمول های بلااستفاده به Value
اگر از فرمول های موجود استفاده نمی کنید و یا نیاز به پایداری آن ها نیست آن ها را با یک کپی ساده به Value تبدیل کنید.
۷- تمام آدرس دهی های یک شیت در داخل خودش باشند.
تا جایی که امکان دارد به فایل ها و شیت های دیگر آدرس دهی نکنید.
۸- از آدرس کل یک ستون یا یک سطر استفاده نکنید.
دادن آدرس هایی نظیر A:A و ۸:۸ یعنی درگیر کردن تعداد بسیار زیادی سلول که نیازی به استفاده از آن ها نیست. همین اشتباه ساده فایل ها را کُند می کند.
۹- از Manual Calculation استفاده کنید.
اگر فایل شرایط خاصی دارد و ناچار به استفاده از قابلیت های قبلی هستید و کُند شده یا فایل فرد دیگری را روی سیستم ضعیف خود باز کرده اید می توانید از اکسل بخواهید تا بطور خودکار محاسبات را انجام ندهد. این کار طبق شکل زیر و از طریق Options اکسل انجام می شود.
۱۰- از تکنیک فرمول های سریع استفاده کنید.
شاید باور این موضوع برای شما سخت باشد اما با اینکه برای ما نوشتن ترکیبی فرمول های Index و Match از پیاده سازی VLookup سخت تر است اما این فرمول های بهینه تر هستند و از VLookup سریعتر کار می کنند.
همچنین نوشتن فرمول IFError از نوشتن ترکیبی فرمول های IF و ISError سریعتر عمل می کند. (که البته باور این موضوع راحت تر است.)
حتی گاهی نوشتن فرمول MAX از IF سریعتر و بهتر است!!!
۱۱- از آخرین بروزرسانی های اکسل استفاده کنید.
هم برای خود اکسل و هم برای سازگاری بیشتر با سیستم عامل (ویندوز یا مک) سعی کنید اکسل را بروز رسانی کنید.
۱۲- افزونه های (Add-Ins) غیر ضروری را غیر فعال کنید.
این کار به ویژه برای افزونه های COM Add-ins که در هنگام باز شدن اکسل نیاز به بارگذاری کامل دارند کمک زیادی به سرعت اکسل می کند. حتی افرونه های اکسلی نیز بعضا اکسل را تحت تاثیر منفی خود قرار می دهند.
۱۳- برنامه Office را Repair کنید.
نصب کننده آفیس را مجددا راه اندازی کنید و آن را Repair کنید.
۱۴- اجتناب از Data Table ها
این موضوع را با Table اشتباه نگیرید. Data Table برای بررسی پارامتریک یک یا چند ورودی فرمول ها استفاده می شود که در زبانه Data و در بخش What-if-Analysis قرار دارد.
۱۵- حذف Style های بدون استفاده
ممکن است بدون اینکه شما بخواهید Style هایی به اکسل اضافه شوند. اگر آن ها را نمی خواهید حذفشان کنید.
۱۶- استفاده از توابع به جای عملگرها
به عنوان مثال قطعا استفاده از تابع ()SUM بهتر از استفاده از عملگر + خواهد بود.
قطعا نکات دیگری هم در بهینه سازی فایل های اکسل موثر هستند که می توانید با درج نظرات خود دیگران را از آن بهره مند کنید.
