عملکرد اکسل: بهبود عملکرد محاسباتی

"شبکه بزرگ" از 1 میلیون ردیف و 16000 ستون در آفیس اکسل 2016، همراه با بسیاری از افزایش محدودیت های دیگر، اندازه کاربرگ هایی را که می توانید در مقایسه با نسخه های قبلی اکسل بسازید، بسیار افزایش می دهد. یک کاربرگ واحد در اکسل اکنون می تواند بیش از 1000 برابر سلول های نسخه های قبلی داشته باشد.

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

نسخه های اخیر اکسل چندین ویژگی را برای کمک به شما در مدیریت این افزایش ظرفیت معرفی کرده اند، مانند توانایی استفاده از بیش از یک پردازنده در یک زمان برای محاسبات و عملیات متداول مجموعه داده ها مانند تازه سازی، مرتب سازی و باز کردن کتاب های کار. محاسبه چند رشته ای می تواند زمان محاسبه کاربرگ را به میزان قابل توجهی کاهش دهد. با این حال، مهم ترین عاملی که بر سرعت محاسبه اکسل تأثیر می گذارد، نحوه طراحی و ساخت کاربرگ شما است.

شما می توانید اکثر کاربرگ های با سرعت کم محاسبه را برای محاسبه ده ها، صدها یا حتی هزاران بار سریع تر تغییر دهید. با شناسایی، اندازه گیری و سپس بهبود موانع محاسباتی در کاربرگ های خود، می توانید سرعت محاسبه را افزایش دهید.

اهمیت سرعت محاسبه

سرعت محاسبات ضعیف بر بهره وری تأثیر می گذارد و خطای کاربر را افزایش می دهد. بهره وری کاربر و توانایی تمرکز روی یک کار با طولانی شدن زمان پاسخ بدتر می شود.

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

محاسبه خودکار - هنگامی که تغییری ایجاد می کنید، فرمول ها به طور خودکار دوباره محاسبه می شوند.

محاسبه دستی - فرمول ها فقط زمانی که شما آن را درخواست می کنید مجدداً محاسبه می شوند (مثلاً با فشار دادن F9).

برای زمان های محاسبه کمتر از حدود یک دهم ثانیه، کاربران احساس می کنند که سیستم فورا پاسخ می دهد. آنها می توانند حتی زمانی که داده ها را وارد می کنند از محاسبه خودکار استفاده کنند.

بین یک دهم ثانیه و یک ثانیه، کاربران می توانند با موفقیت یک رشته فکر را ادامه دهند، اگرچه متوجه تاخیر زمان پاسخ خواهند شد.

با افزایش زمان محاسبه (معمولاً بین 1 تا 10 ثانیه) ، کاربران باید هنگام ورود به داده ها به محاسبه دستی تغییر دهند. خطاهای کاربر و سطح دلخوری ها به ویژه برای کارهای تکراری افزایش می یابد و حفظ قطار فکری دشوار می شود.

برای زمان محاسبه بیشتر از 10 ثانیه ، کاربران بی تاب می شوند و معمولاً در حالی که منتظر هستند به کارهای دیگر تغییر می کنند. این می تواند باعث ایجاد مشکلاتی شود که محاسبه یکی از توالی کارها باشد و کاربر مسیر را از دست بدهد.

درک روشهای محاسبه در اکسل

برای بهبود عملکرد محاسبه در اکسل ، باید روشهای محاسبه موجود و نحوه کنترل آنها را درک کنید.

محاسبه کامل و وابستگی های محاسبه مجدد

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

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

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

توابع فرار و قالبهای مشروط قابل مشاهده.

اکسل سلولهای محاسبه ای را که به سلولهای قبلاً محاسبه شده وابسته هستند ، ادامه می دهد ، حتی اگر مقدار سلول قبلاً محاسبه شده هنگام محاسبه تغییر نکند.

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

در حالت محاسبه دستی ، می توانید با فشار دادن F9 این محاسبه هوشمند را تحریک کنید. شما می توانید با فشار دادن Ctrl+Alt+F9 ، یک محاسبه کامل از همه فرمول ها را مجبور کنید ، یا می توانید با فشار دادن Shift+Ctrl+Alt+F9 ، یک بازسازی کامل از وابستگی ها و محاسبه کامل را مجبور کنید.

روند محاسبه

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

اکسل مراحل محاسبه متمایز دارد:

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

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

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

قسمت های قابل مشاهده پنجره های اکسل را به روز کنید.

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

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

اکسل معمولاً فقط سلول هایی را که تغییر کرده اند و وابسته هایشان دوباره محاسبه می کند.

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

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

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

محاسبه کتاب های کار، کاربرگ ها و محدوده ها

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

محاسبه تمام کتاب های باز

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

کاربرگ های انتخاب شده را محاسبه کنید

همچنین می توانید با استفاده از Shift+F9 فقط کاربرگ های انتخاب شده را دوباره محاسبه کنید. این هیچ وابستگی بین کاربرگ ها را برطرف نمی کند و سلول های کثیف را طبق محاسبه بازنشانی نمی کند.

محدوده ای از سلول ها را محاسبه کنید

اکسل همچنین با استفاده از روش های Visual Basic for Applications (VBA) Range. CalculateRowMajorOrder و Range. Calculate امکان محاسبه محدوده ای از سلول ها را می دهد:

Range. CalculateRowMajorOrder محدوده چپ به راست و از بالا به پایین را محاسبه می کند و همه وابستگی ها را نادیده می گیرد.

Range. Calculate محدوده ای را محاسبه می کند که همه وابستگی ها را در محدوده حل می کند.

از آنجا که CalculateRowMajorOrder هیچ وابستگی را در محدوده ای که محاسبه می شود حل نمی کند، معمولاً به طور قابل توجهی سریعتر از Range. Calculate است. با این حال، باید با احتیاط استفاده شود زیرا ممکن است نتایج مشابه Range. Calculate را نداشته باشد.

Range. Calculate یکی از کاربردی ترین ابزارهای اکسل برای بهینه سازی عملکرد است زیرا می توانید از آن برای زمان بندی و مقایسه سرعت محاسبه فرمول های مختلف استفاده کنید.

توابع فرار

یک تابع فرار همیشه در هر محاسبه مجدد دوباره محاسبه می شود، حتی اگر به نظر نمی رسد که سابقه تغییری داشته باشد. استفاده از بسیاری از توابع فرار، هر محاسبه مجدد را کند می کند، اما برای محاسبه کامل تفاوتی ندارد. شما می توانید یک تابع تعریف شده توسط کاربر را با گنجاندن Application. Volatile در کد تابع فرار تبدیل کنید.

برخی از توابع داخلی در اکسل آشکارا فرار هستند: RAND() , NOW() , TODAY() . سایرین به وضوح فرار کمتری دارند: OFFSET() , CELL() INDIRECT() INFO() .

برخی از توابع که قبلاً به عنوان فرار مستند شده اند در واقع فرار نیستند: INDEX() , ROWS() , COLUMNS() , AREAS() .

اقدامات فرار

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

• در حالت خودکار روی یک سطر یا تقسیم کننده ستون کلیک کنید.
• درج یا حذف سطرها، ستون ها یا سلول ها در یک صفحه.
• افزودن، تغییر یا حذف نام های تعریف شده.
• تغییر نام کاربرگ ها یا تغییر موقعیت کاربرگ در حالت خودکار.
• فیلتر کردن، پنهان کردن، یا عدم پنهان کردن ردیف ها.
• باز کردن کتاب کار در حالت خودکار. اگر آخرین بار کتاب کار توسط نسخه دیگری از اکسل محاسبه شده است، باز کردن کتاب کار معمولاً به یک محاسبه کامل منجر می شود.
• ذخیره یک کتاب کار در حالت دستی در صورتی که گزینه Calculate before Save انتخاب شده باشد.

شرایط ارزیابی فرمول و نام

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

• فرمول را وارد یا ویرایش کنید.
• با استفاده از Function Wizard فرمول را وارد یا ویرایش کنید.
• فرمول را به عنوان آرگومان در Function Wizard وارد کنید.
• فرمول را در نوار فرمول انتخاب کنید و F9 را فشار دهید (Esc را برای لغو و بازگشت به فرمول فشار دهید)، یا روی Evaluate Formula کلیک کنید.

زمانی که یک فرمول به سلول یا فرمولی که یکی از شرایط زیر را دارد (بستگی به آن دارد) به عنوان غیرمحاسبه علامت گذاری می شود:

• وارد شد.
• عوض شد.
• این در یک لیست AutoFilter است و لیست کشویی معیارها فعال شده است.
• این به عنوان محاسبه نشده پرچم گذاری شده است.

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

شرایطی که باعث می شود نام مشخصی ارزیابی شود با فرمول در یک سلول متفاوت است:

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

میزهای داده

Excel data tables ( Data tab> Data Tools group> What-If Analysis> Data Table ) should not be confused with the table feature ( Home tab> Styles group> Format as Table , or, Insert tab> Tables group>جدول ). جداول داده اکسل محاسبات متعددی از کتاب کار را انجام می دهد که هر یک توسط مقادیر مختلف در جدول هدایت می شوند. اکسل ابتدا کتاب کار را به طور عادی محاسبه می کند. برای هر جفت مقادیر ردیف و ستون ، سپس مقادیر را جایگزین می کند ، یک محاسبه مجدد تک رشته ای را انجام می دهد و نتایج را در جدول داده ها ذخیره می کند.

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

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

کنترل گزینه های محاسبه

اکسل طیف وسیعی از گزینه ها را دارد که به شما امکان می دهد نحوه محاسبه آن را کنترل کنید. با استفاده از گروه محاسبه در برگه فرمول روی روبان می توانید گزینه های متداول را در اکسل تغییر دهید.

شکل 1. گروه محاسبه در برگه فرمول

برای دیدن گزینه های محاسبه اکسل بیشتر ، در برگه پرونده ، گزینه ها را کلیک کنید. در کادر گفتگوی Excel Options ، روی برگه Formulas کلیک کنید.

شکل 2. گزینه های محاسبه در برگه فرمول در گزینه های اکسل

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

برای یافتن گزینه های محاسبه پیشرفته ، در برگه پرونده ، گزینه ها را کلیک کنید. در کادر گفتگوی Excel Options ، روی Advanced کلیک کنید. در بخش فرمول ، گزینه های محاسبه را تنظیم کنید.

شکل 3. گزینه های محاسبه پیشرفته

هنگامی که Excel را شروع می کنید ، یا هنگامی که بدون هیچ گونه کتاب کار در حال اجرا است ، حالت محاسبه اولیه و تنظیمات تکرار از اولین کتاب کار غیر Admentate ، غیر ADD در حال باز است که باز می کنید. این بدان معناست که تنظیمات محاسبه در کتابهای کاری که بعداً افتتاح شد ، نادیده گرفته می شود ، اگرچه ، البته ، شما می توانید تنظیمات را در هر زمان به صورت دستی تغییر دهید. هنگامی که یک کتاب کار را ذخیره می کنید ، تنظیمات محاسبه فعلی در کتاب کار ذخیره می شود.

محاسبه خودکار

حالت محاسبه خودکار به این معنی است که اکسل به طور خودکار تمام کتابهای کار باز را در هر تغییر و هنگام باز کردن کتاب کار محاسبه می کند. معمولاً وقتی یک کتاب کار را در حالت اتوماتیک باز می کنید و محاسبه مجدد اکسل می شود ، محاسبه مجدد را نمی بینید زیرا از زمان ذخیره کتاب کار هیچ چیز تغییر نکرده است.

ممکن است هنگام باز کردن یک کتاب کار در نسخه بعدی اکسل ، از آخرین باری که آخرین باری که کتاب کار محاسبه شده است استفاده کنید (به عنوان مثال اکسل 2016 در مقابل اکسل 2013) این محاسبه را متوجه شوید. از آنجا که موتورهای محاسبه اکسل متفاوت هستند ، اکسل هنگام باز کردن یک کتاب کار که با استفاده از نسخه قبلی اکسل ذخیره می شود ، محاسبه کامل را انجام می دهد.

محاسبه دستی

حالت محاسبه دستی به این معنی است که اکسل تمام کتابهای کار باز را فقط در صورت درخواست آن با فشار دادن F9 یا Ctrl+Alt+F9 ، یا هنگام ذخیره کتاب کار ، محاسبه می کند. برای کتابهای کاری که بیش از کسری از ثانیه برای محاسبه مجدد طول می کشد ، باید محاسبه را روی حالت دستی تنظیم کنید تا در هنگام ایجاد تغییرات از تأخیر جلوگیری کنید.

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

تنظیمات تکرار

اگر در کتاب کار خود منابع دایره ای عمدی دارید ، تنظیمات تکرار به شما امکان می دهد حداکثر تعداد دفعاتی را که کتاب کار محاسبه می شود (تکرار) و معیارهای همگرایی (حداکثر تغییر: چه موقع متوقف کنید) کنترل کنید. جعبه تکرار را به گونه ای پاک کنید که اگر منابع دایره ای تصادفی داشته باشید ، اکسل به شما هشدار می دهد و سعی نمی کنید آنها را حل کنید.

کتاب کار ملک محاسبه نیرو

هنگامی که این ویژگی کتاب کار را بر روی True تنظیم کردید ، محاسبه مجدد هوشمند اکسل خاموش می شود و هر محاسبه مجدد تمام فرمول ها را در تمام کتاب های کار باز محاسبه می کند. برای برخی از کتابهای کار پیچیده ، زمان لازم برای ساخت و نگهداری درختان وابستگی مورد نیاز برای محاسبه مجدد هوشمند از زمان صرفه جویی در محاسبه مجدد هوشمند بزرگتر است.

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

در صورت تنظیم صحیح کتاب کار ، در نوار وضعیت ظاهر می شود.

شما می توانید این تنظیمات را با استفاده از VBE (Alt+F11) کنترل کنید ، این کار را در Project Explorer (Ctrl+R) انتخاب کرده و پنجره Properties (F4) را نشان دهید.

شکل 4. تنظیم کتاب کار.

ساخت کتاب های کار سریعتر محاسبه می شود

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

سرعت پردازنده و چندین هسته

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

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

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

همانطور که گفته شد ، نسخه های اخیر Excel می تواند از مقادیر زیادی حافظه استفاده کند و نسخه 32 بیتی Excel 2007 و Excel 2010 می توانند یک کتاب کار واحد یا ترکیبی از کتابهای کار را با استفاده از حداکثر 2 گیگابایت حافظه اداره کنند.

نسخه های 32 بیتی Excel 2013 و Excel 2016 که از ویژگی Address Address (LAA) استفاده می کنند ، بسته به نسخه ویندوز نصب شده ، می توانند از حافظه حداکثر 3 یا 4 گیگابایت استفاده کنند. نسخه 64 بیتی اکسل می تواند کتابهای بزرگتر را اداره کند. برای اطلاعات بیشتر ، به بخش "مجموعه داده های بزرگ ، LAA و 64 بیتی اکسل" در عملکرد اکسل مراجعه کنید: عملکرد و بهبود.

یک راهنمای خشن برای محاسبه کارآمد ، داشتن RAM کافی برای نگه داشتن بزرگترین مجموعه کتابهای کاری است که شما باید در همان زمان باز کنید ، به علاوه 1 تا 2 گیگابایت برای Excel و سیستم عامل ، به علاوه RAM اضافی برای سایر برنامه های در حال اجرا.

اندازه گیری زمان محاسبه

برای اینکه کتاب های کار سریع تر محاسبه شوند، باید بتوانید زمان محاسبه را به دقت اندازه گیری کنید. شما نیاز به تایمر دارید که سریعتر و دقیق تر از عملکرد VBA Time باشد. تابع MICROTIMER() نشان داده شده در مثال کد زیر از فراخوانی های API ویندوز به تایمر با وضوح بالا سیستم استفاده می کند. می تواند فواصل زمانی را تا تعداد کمی میکروثانیه اندازه گیری کند. توجه داشته باشید که از آنجایی که ویندوز یک سیستم عامل چندوظیفه ای است و به دلیل اینکه بار دوم که چیزی را محاسبه می کنید، ممکن است سریعتر از بار اول باشد، زمان هایی که دریافت می کنید معمولاً دقیقا تکرار نمی شوند. برای دستیابی به بهترین دقت، وظایف محاسبه زمان را چندین بار اندازه گیری کنید و نتایج را میانگین بگیرید.

برای کسب اطلاعات بیشتر در مورد اینکه چگونه ویرایشگر ویژوال بیسیک می تواند به طور قابل توجهی بر عملکرد عملکرد تعریف شده توسط کاربر VBA تأثیر بگذارد، به بخش «عملکردهای سریعتر تعریف شده توسط کاربر VBA» در عملکرد Excel: نکاتی برای بهینه سازی موانع عملکرد مراجعه کنید.

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

همه این زیر روال ها و توابع را در یک ماژول استاندارد VBA کپی کنید. برای باز کردن ویرایشگر VBA، Alt+F11 را فشار دهید. در منوی Insert، Module را انتخاب کنید و سپس کد را در ماژول کپی کنید.

برای اجرای زیر روال ها در اکسل، Alt+F8 را فشار دهید. زیربرنامه مورد نظر خود را انتخاب کنید و سپس روی Run کلیک کنید.

شکل 5. پنجره Excel Macro که تایمرهای محاسبه را نشان می دهد

یافتن و اولویت بندی موانع محاسباتی

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

رویکرد تمرینی برای یافتن موانع

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

برای یافتن موانع با استفاده از رویکرد تمرینی

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

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

یک نسخه پشتیبان از کتاب کار تهیه کنید.

کتاب کار حاوی ماکروهای Calculation Timers را باز کنید یا آنها را به کتاب کار اضافه کنید.

با فشار دادن Ctrl+End در هر صفحه کار به نوبه خود ، محدوده استفاده شده را بررسی کنید.

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

کلان FullCalctimer را اجرا کنید.

زمان محاسبه تمام فرمول های موجود در کتاب کار معمولاً بدترین زمان است.

ماکرو Recalctimer را اجرا کنید.

محاسبه مجدد بلافاصله پس از محاسبه کامل معمولاً بهترین زمان را به شما می دهد.

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

این میزان میزان فرمول های فرار و ارزیابی زنجیره محاسبه انسداد است.

هر ورق را فعال کرده و ماکرو Sheettimer را به نوبه خود اجرا کنید.

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

ماکرو Rangetimer را روی بلوک های انتخاب شده فرمولها اجرا کنید.

برای هر برگه مشکل ، ستون ها یا ردیف ها را به تعداد کمی بلوک تقسیم کنید.

هر بلوک را به نوبه خود انتخاب کرده و سپس ماکرو Rangetimer را روی بلوک اجرا کنید.

در صورت لزوم ، با تقسیم هر بلوک در تعداد کمتری از بلوک ها ، بیشتر متراکم شوید.

انسداد را در اولویت قرار دهید.

سرعت بخشیدن به محاسبات و کاهش انسداد

این تعداد فرمول ها یا اندازه یک کتاب کار نیست که زمان محاسبه را مصرف می کند. این تعداد منابع سلول و عملیات محاسبه و کارآیی توابع مورد استفاده است.

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

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

قانون اول: محاسبات تکراری ، مکرر و غیر ضروری را حذف کنید

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

معمولاً این شامل یک یا چند مرحله زیر است:

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

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

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

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

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

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

توابع تعریف شده توسط کاربر VBA معمولاً کندتر از توابع داخلی در اکسل هستند (اگرچه توابع VBA با دقت نوشته شده می توانند سریع باشند).

تعداد سلولهای استفاده شده را در توابع مانند SUM و SUMIF به حداقل برسانید. زمان محاسبه متناسب با تعداد سلولهای مورد استفاده است (سلولهای بلااستفاده نادیده گرفته می شوند).

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

قانون سوم: از محاسبه مجدد هوشمند و محاسبه چند رشته ای استفاده خوبی کنید

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

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

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

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

از عملکردهای تک رشته ای خودداری کنید:

• مربوط به صدا
• سلول هنگام استفاده از آرگومان "فرمت" یا "آدرس"
• غیر مستقیم
• گله
• نوشابه
• مکعب
• CubememberProperty
• مکعب
• ممتاز
• کابین
• مکعب
• آدرس که در آن پارامتر پنجم (sheet_name) داده شده است
• هر عملکرد پایگاه داده (DSUM ، Davent و غیره) که به یک pivottable اشاره دارد
• خطا. نوع
• لینک
• توابع تعریف شده توسط کاربر VBA و COM

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

قانون چهارم: زمان و آزمایش هر تغییر

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

با استفاده از ماکرو Rangetimer ، فرمولی را که می خواهید تغییر دهید.

تغییر را انجام دهید.

زمان فرمول تغییر یافته با استفاده از کلان Rangetimer.

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

نمونه های قانون

در بخش های زیر نمونه هایی از نحوه استفاده از قوانین برای سرعت بخشیدن به محاسبه ارائه شده است.

مبالغ دوره ای

به عنوان مثال ، شما باید مبلغ دوره به تاریخ یک ستون را که شامل 2،000 شماره است محاسبه کنید. فرض کنید که ستون A شامل اعداد است و ستون B و ستون C باید حاوی کل دوره به تاریخ باشد.

می توانید فرمول را با استفاده از SOM بنویسید ، که یک عملکرد کارآمد است.

شکل 6. مثال فرمولهای جمع دوره ای به تاریخ

فرمول را تا B2000 کپی کنید.

در کل چند مرجع سلولی با مبلغ اضافه می شود؟B1 به یک سلول اشاره دارد و B2000 به 2000 سلول اشاره دارد. میانگین 1000 مرجع در هر سلول است ، بنابراین تعداد کل منابع 2 میلیون است. انتخاب 2000 فرمول و استفاده از کلان Rangetimer به شما نشان می دهد که 2000 فرمول موجود در ستون B در 80 میلی ثانیه محاسبه می شود. بیشتر این محاسبات بارها تکثیر می شوند: جمع در هر فرمول از B2: B2000 A1 به A2 اضافه می کند.

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

این فرمول را تا C2000 کپی کنید.

اکنون چند مرجع سلولی در کل اضافه می شود؟هر فرمول ، به جز فرمول اول ، از دو مرجع سلولی استفاده می کند. بنابراین ، کل 1999*2+1 = 3999 است. این یک عامل 500 مرجع سلول کمتر است.

Rangetimer نشان می دهد که 2،000 فرمول در ستون C در 3. 7 میلی ثانیه در مقایسه با 80 میلی ثانیه برای ستون B محاسبه می شود. این تغییر دارای یک عامل بهبود عملکرد تنها 80/3. 7 = 22 به جای 500 است زیرا در هر فرمول یک سربار کوچک وجود دارد.

رسیدگی به خطا

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

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

B1 = if (iserror (فرمول گران قیمت) ، 0 ، فرمول گران قیمت)

می توانید آن را به عنوان دو فرمول بنویسید ، که سریع است:

A1 = زمان فرمول گران قیمت

یا می توانید از عملکرد IFError استفاده کنید ، که به صورت سریع و ساده طراحی شده است و یک فرمول واحد است:

B1 = ifError (فرمول گران قیمت ، 0)

شمارش پویا منحصر به فرد

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

اگر لیستی از 11000 ردیف داده در ستون A دارید ، که اغلب تغییر می کند ، و به فرمولی نیاز دارید که به صورت پویا تعداد موارد منحصر به فرد موجود در لیست را محاسبه کند ، نادیده گرفتن خالی ها ، زیر چندین راه حل ممکن است.

فرمول های آرایه (از Ctrl+Shift+Enter استفاده کنید) ؛Rangetimer نشان می دهد که این 13. 8 ثانیه طول می کشد.

محصول معمولاً سریعتر از فرمول آرایه معادل محاسبه می شود. این فرمول 10. 0 ثانیه طول می کشد و ضریب بهبود 13. 8/10. 0 = 1. 38 را ارائه می دهد ، که بهتر است ، اما به اندازه کافی خوب نیست.

توابع تعریف شده توسط کاربر. مثال کد زیر یک تابع تعریف شده توسط کاربر VBA را نشان می دهد که از این واقعیت استفاده می کند که این فهرست به یک مجموعه باید بی نظیر باشد. برای توضیح برخی از تکنیک های مورد استفاده ، به بخش مربوط به توابع تعریف شده توسط کاربر در بخش "استفاده از توابع کارآمد" در عملکرد اکسل مراجعه کنید: نکاتی برای بهینه سازی انسداد عملکرد. این فرمول ، = Countu (A2: A11000) ، فقط 0. 061 ثانیه طول می کشد. این یک عامل بهبود 13. 8/0. 061 = 226 را نشان می دهد.

اضافه کردن یک ستون از فرمول ها. اگر به نمونه قبلی داده ها نگاه کنید ، می بینید که طبقه بندی شده است (اکسل برای مرتب کردن 11000 ردیف 0. 5 ثانیه طول می کشد). شما می توانید با اضافه کردن ستونی از فرمول ها که بررسی می کند آیا داده های موجود در این ردیف همان داده های موجود در ردیف قبلی است ، از این مورد سوء استفاده کنید. اگر متفاوت باشد ، فرمول 1. باز می گردد. در غیر این صورت ، 0 باز می گردد.

این فرمول را به سلول B2 اضافه کنید.

فرمول را کپی کرده و سپس یک فرمول اضافه کنید تا ستون B را اضافه کنید.

محاسبه کامل از همه این فرمول ها 0. 027 ثانیه طول می کشد. این یک عامل بهبود 13. 8/0. 027 = 511 را نشان می دهد.

نتیجه

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

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

همچنین ببینید

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

در مورد Office VBA یا این مستندات سؤال یا بازخورد دارید؟لطفاً برای راهنمایی در مورد راه های دریافت پشتیبانی و ارائه بازخورد ، از پشتیبانی و بازخورد Office VBA دیدن کنید.