دوازده نکته که حرفه ای ها در اکسل می دانند
نکته ۱۱: سرعت بخشیدن در اکسل – تشخیص فرمول‌های ایستا و پویا
روند تکاملی افزایش ظرفیت ورکشیت‌ها در اکسل (نسخه‌های پی‌درپی عرضه در بازار)؛ به کاربران این امکان را می‌دهد؛ که صفحات گسترده بیشتر و پیچیده‌تری را نسبت به نسخه‌های قبلی اکسل ایجاد و مدیریت کنند. همچنین در زمینه سرعت این برنامه، پیشرفت‌هایی انجام شد. با این حال، بسیاری از کاربران اکسل هنوز با صفحات گسترده کند؛ و آهسته دست و پنجه نرم می‌کنند.
با استفاده از برخی تکنیک‌های ساده و داشتن درک بهتر در مورد نحوه کار اکسل با توابع، می‌توان به طور قابل‌توجهی هر یک از دلایل آهسته و کند شدن فایل اکسل را یافته و آن را بهبود بخشید.
طراحی فایل صفحه‌گسترده در اکسل، کلیدی در تعیین میزان کارایی و سرعت محاسبات workbook شما است. اغلب صفحات گسترده که سرعت پایینی دارند؛ به خاطر تعداد فرمول‌های استفاده شده یا اندازه workbook نیست؛ بلکه به دلیل تعداد منابع سلولی بکاررفته (رفرنس‌دهی بیش از حد) در فرمول‌ها و کارایی توابع مورد استفاده است. همچنین مهم است؛ بدانیم که چگونه محاسبات اکسل و کدام سلول‌ها محاسبه می‌شوند. دانستن تفاوت بین توابع پویا و ایستا می‌تواند؛ به شما کمک کند؛ بدانید از کدام فرمول‌ها در صورتی که فایل‌های شما کند هستند؛ استفاده کنید.
دقت کنید که کارتان بهینه‌سازی شده است؟
- تمامی لینک‌ها، نام‌ها و یا فرمول‌های نامعتبر، را حذف کنید
- اطلاعات رو در حد امکان در workbookجاری نگه دارید (به فایل‌های مختلف رفرنس دهی نکنید).
- هر چیزی که “غیر متداول” باشد، حذف نمایید. کلید ترکیبی Ctrl + END را بزنید؛ تا محدوده مورد استفاده واقعی روی هرکدام از صفحات شما در فایلتان مشخص شود. ردیف‌ها و ستون‌هایی که مورد نیاز نیستند؛ را حذف کرده و همه سلول‌های خالی در محدوده مورد استفاده را حذف کنید. workbookرا ذخیره نمایید.
اطمینان حاصل کنید که فرمول‌های شما بهینه سازی شده است:
- برای به دست‌آوردن نتایج خود از کارآمدترین توابع ممکن استفاده کنید. درک کامل فرمول‌های پیشرفته‌تر اکسل بسیار اهمیت دارند؛ و از این درک شما می‌توانید قضاوت کنید؛ که کدام فرمول‌ها برای یک موقعیت مشخص بهتر عمل می‌کنند. دانشی مانند این نکات راهکارهای موقت و محاسبات پیچیده را کاهش می‌دهد. برای مثال استفاده از فرمول IFERROR()به جای این فرمول IF(ISERROR(),,) می‌تواند در جهت بهبود، تغییر ایجاد کند.
- تعداد سلول‌ها در آدرس‌های موجود در یک تابع را به حداقل برسانید. برای مثال، اگر از VLOOKUPیا SUMIF استفاده می‌کنید؛ به کل ستون (D: D) ارجاع ندهید؛ بلکه محدوده (D1:D200) را به عنوان رفرنس مورد نظر انتخاب کنید.
احتیاط در استفاده از توابع پویا
توابع پویا، توابعی هستند؛ که هر زمان، داده‌های زمانی وارد یک workbook یا فایل باز می‌شوند؛ مجددا محاسبه می‌شوند. آن‌ها مستقل از این که فرمول شما از این سلول‌ها استفاده می‌کند یا خیر، محاسبه می‌شوند(مثلا کاری با ورودهای شما ندارند)؛ محاسبات در هر سلول بدون توجه به فرمول رخ می‌دهد. همچنین هنگامی که یک سطر یا ستون جدید وارد می‌شود، مجدداً محاسبه می‌شوند؛ یک worksheet تغییر نام داده می‌شود؛ و یا ترتیب worksheet مورد نظر تغییر می‌کند. هر کار انجام‌شده؛ در هر فایل باز باعث می‌شود؛ که این فرمول پویا مجدد محاسبه شود. نمونه‌هایی از توابع پویا به شرح ذیل هستند:
- =OFFSET()
- =TODAY()
- =NOW()
- =INDIRECT()
- =RAND() & RANDBETWEEN()
- =CELL ()
- = INFO()
به احتمال زیاد، اگر شما یک کاربر متوسط اکسل هستید، از برخی از این توابع استفاده کرده‌اید.
فرمول‌های قالب‌بندی شرطی نیز پویا هستند. فرمت‌های شرطی در زمانی که داده تغییر یابد؛ معین می‌شوند. از آنجا که فرمت شرطی متشکل از قالب‌بندی و محاسبات است؛ هر دو می‌تواند زمان محاسبه را دو برابر کند. توجه داشته باشید؛ که فرمت‌های شرطی فقط براساس آنچه بر روی صفحه قابل‌مشاهده است؛ به روز می‌شوند، نه چیز دیگر. استفاده گسترده از فرمول‌های قالب‌بندی شرطی بسیار پیچیده، می‌تواند؛ باعث شود؛ تا مرور و مشاهده داده‌ها کندتر شود.
استفاده از توابع پویا لزوماً دلیل کندی یک workbook نیست. فایل “ممکن است” فقط کند باشد؛ چون شما یک workbook بزرگ و نسبتاً پیچیده دارید. اگر فایل شما کاملاً کند؛ و دارای توابع پویا است؛ باید به دنبال روش‌های جایگزین برای محاسبات باشید. گاهی اوقات شما می‌توانید؛ توابع پویا را با جایگزین‌های آن عوض کنید – برای مثال جایگزینی OFFSET با تابع INDEX و MATCH. VBA (ویژوال بیسیک برای برنامه‌ها) نیز گزینه‌ای برای workbookهای پیچیده و محاسباتی است.
فرمول‌های عادی چگونه عمل می‌کنند؟
هر زمانی که سلول اکسل موثر در فرمول شما تغییر کند؛ فرمول مورد نظر دوباره محاسبه می‌شود. بیایید فرض کنیم که یک فایل اکسل بزرگ دارید؛ که هزاران فرمول دارد و بسیاری از این فرمول‌ها به سلول‌های دیگر وابسته هستند؛ این زنجیره وابستگی در اکسل، به خاطر سپرده شده و توسط اکسل در یک درخت وابستگی ذخیره می‌شود. برای محاسبه فرمول نرمال، اکسل همیشه به این زنجیره وابستگی مراجعه می‌کند. اگر یک تغییر در سلول ایجاد کنید؛ درخت وابستگی به روز می‌شود. قدم‌های زیر را برای سرعت بخشیدن به هر کدام از workbook های با سرعت پایین، دنبال کنید.
