محاسبه مجدد فرمول به صورت افزایشی در HotXLS برای دلفی

کتابخانه بومی اکسل برای دلفی و C++Builder یعنی HotXLS، محاسبه مجدد فرمول به صورت افزایشی را از طریق TXLSXWorkbook.Recalculate. اولین فراخوانی یک نمودار وابستگی فرمول ایجاد می‌کند و تک‌تک سلول‌های فرمول را ارزیابی می‌نماید؛ هر فراخوانی بعدی فقط سلول‌هایی را که تحت تاثیر نوشتن مقادیر از آخرین مرحله قرار گرفته‌اند، به ترتیب توپولوژیکی و در یک مرحله که هزینه آن متناسب با تعداد سلول‌های تغییر یافته (کثیف) است به جای اندازه کل کتاب کار، دوباره ارزیابی می‌کند

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

چرا تغییر یک سلول باعث محاسبه مجدد صدهزار فرمول می‌شود؟

یک موتور فرمول ساده هیچ حافظه‌ای از اینکه چه کسی به چه کسی وابسته است ندارد، بنابراین تنها حرکت ایمن آن پس از هر ویرایش، ارزیابی مجدد همه چیز است. بدتر از آن، استراتژی بازگشتی کلاسیک — هنگامی که فرمول A به فرمول B ارجاع می‌دهد، B را در همان لحظه ارزیابی می‌کند — سلول‌های ارجاع‌داده‌شده را بدون قید و شرط و بدون توجه به مقدار کش شده دوباره ارزیابی می‌نماید. زنجیره‌ای از n فرمول که هر کدام به فرمول قبلی ارجاع می‌دهند، هزینه O(n²) ارزیابی در هر مرحله کامل دارند و یک مرجع حلقوی، فرآیند بازگشتی را به بن‌بست می‌کشاند. هر توسعه‌دهنده صفحه‌گسترده‌ای که یک مدل آبشاری را به یک ارزیاب بازگشتی متصل کرده است، شاهد وقوع هر دو حالت شکست بوده است

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

چگونه نمودار وابستگی یک ویرایش را به یک مرحله واحد تبدیل می‌کند

نمودار وابستگی HotXLS به هر سلول فرمول یک گره اختصاص می‌دهد که یال‌ها از گره قبلی به وابسته متصل می‌شوند. وقتی کد شما مقدار سلولی را می‌نویسد، کتاب کار آن سلول را به عنوان کثیف ثبت می‌کند؛ وقتی متد Recalculate اجرا می‌شود، تغییر در طول یال‌ها به هر فرمول پایین‌دستی منتقل می‌شود و زیرنمودار کثیف دقیقاً یک بار به ترتیب توپولوژیکی با استفاده از الگوریتم کان (Kahn) ارزیابی می‌شود. از آنجا که یک فرمول هرگز قبل از گره‌های قبلی خود بررسی نمی‌شود، هر گره به یک ارزیابی واحد نیاز دارد — این همان چیزی است که این مرحله را با پیچیدگی O(dirty) اجرا می‌کند

ترتیب توپولوژیکی همچنین مشکل بازگشت (recursion) را در ریشه آن حل می‌کند. در طول یک مرحله محاسبه مجدد، موتور به حالتی اختصاصی تغییر می‌کند که در آن هر ارجاع به سلول فرمول دیگر، مقدار کش‌شده آن سلول را مستقیماً به جای ارزیابی مجدد می‌خواند — این ترتیب تضمین می‌کند که کش از قبل تازه است. همین سازوکار به این معنی است که یک چرخه ارجاع نمی‌تواند بازگشت بی‌پایان ایجاد کند: هیچ چیز در داخل این مرحله هرگز دوباره برای یک سلول همسایه وارد ارزیاب نمی‌شود

var
  Book: TXLSXWorkbook;
  Inputs, Model: TXLSXWorksheet;
begin
  Book := TXLSXWorkbook.Create;
  try
    Inputs := Book.Sheets.Add('Inputs');
    Model  := Book.Sheets.Add('Model');

    Inputs.Cells[2, 2].Value := 0.05;                 // فرض رشد
    Model.Cells[2, 2].Formula := 'Inputs!B2*1000';    // فرمول‌های XLSX فاقد علامت '=' اولیه هستند
    Model.Cells[3, 2].Formula := 'B2*(1+Inputs!B2)';
    // ... هزاران سطر دیگر که از همان فرض به صورت آبشاری منشعب می‌شوند ...

    Book.Recalculate;                 // اولین فراخوانی: نمودار را می‌سازد، ارزیابی کامل

    Inputs.Cells[2, 2].Value := 0.07; // یک ویرایش، یک سلول را کثیف علامت‌گذاری می‌کند
    Book.Recalculate;                 // دومین فراخوانی: فقط زنجیره پایین‌دستی اجرا می‌شود
  finally
    Book.Free;
  end;
end;

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

کدام توابع اکسل محاسبه مجدد را در هر مرحله اجباری می‌کنند؟

کامپوننت HotXLS توابع NOW، TODAY، RAND، OFFSET، و INDIRECT را به عنوان فرار (volatile) در نظر می‌گیرد: هر فرمولی که حاوی یکی از آن‌ها باشد، در هر مرحله اجرای Recalculate مجدداً ارزیابی می‌شود، خواه تغییراتی در گره‌های قبلی رخ داده باشد یا خیر. سه تابع اول به همان دلیل فرار بودن در اکسل، در اینجا نیز فرار هستند — نتیجه آن‌ها به لحظه ارزیابی بستگی دارد، نه به سلول‌های دیگر. توابع OFFSET و INDIRECT به دلیل ظریف‌تری فرار هستند: سلول‌هایی که می‌خوانند در زمان اجرا محاسبه می‌شوند، بنابراین نمودار نمی‌تواند به طور استاتیک بداند کدام یال‌ها را برای آن‌ها رسم کند

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

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

چگونه HotXLS مراجع حلقوی را گزارش می‌دهد؟

متد TXLSXWorkbook.Recalculate در صورت اجرای تمیز مقدار lxOk و در صورت تشخیص چرخه ارجاع، مقدار lxErrorRef را برمی‌گرداند. اعضای چرخه در طول مرتب‌سازی توپولوژیکی شناسایی می‌شوند — آن‌ها گره‌هایی هستند که الگوریتم کان هرگز نمی‌تواند آن‌ها را آزاد کند — و به جای تکرار حلقه، نادیده گرفته می‌شوند: مقادیر کش‌شده آن‌ها همان‌طور که بودند باقی می‌مانند، در حالی که هر فرمول خارج از چرخه همچنان به طور عادی و به ترتیب ارزیابی می‌شود. محل فراخوانی شما به جای تعلیق (hang)، یک کد خطای مشخص دریافت می‌کند

case Book.Recalculate of
  lxOk:
    SaveReport(Book);
  lxErrorRef:
    // یک چرخه ارجاع وجود دارد؛ اعضای چرخه مقادیر کش‌شده قبلی خود
    // را حفظ کردند و همه چیز در خارج از چرخه به‌روز است
    LogWarning('Circular reference detected - review model inputs');
end;

یافتن اینکه کدام سلول‌ها چرخه را تشکیل می‌دهند یک کار عیب‌یابی است و ردیاب ارزیابی فرمول ابزار مناسبی برای آن است: فرمول مشکوک را ردیابی کنید تا زنجیره ارجاعی که به خودش بازمی‌گردد مرحله به مرحله نمایان شود. چرخه‌ها در مدل‌های واقعی تقریباً همیشه یک اشتباه نویسندگی هستند — برای مثال یک سطر خلاصه که به طور تصادفی در محدوده SUM خود گنجانده شده است — بنابراین دریافت یک کد خطای بلند در زمان محاسبه مجدد دقیقاً همان چیزی است که می‌خواهید

فرمول‌های آرایه‌ای، ردیابی کثیفی، و زمان بازسازی نمودار

فرمول‌های آرایه‌ای CSE برای کل مستطیل متصل‌شده یک گره دریافت می‌کنند، نه یک گره برای هر سلول. فرمول ریشه یک بار در هر مرحله ارزیابی می‌شود؛ ماتریس حاصل مستقیماً در هر سلول عضو نوشته می‌شود و فرمولی که به هر سلول در محدوده متصل‌شده ارجاع دهد — نه فقط لنگر بالا سمت چپ — یک یال وابستگی از آن گره ریشه دریافت می‌کند. نتایج اسکالر (scalar) به روشی که معناشناسی آرایه‌ای قدیمی اکسل تجویز می‌کند در طول مستطیل پخش می‌شوند

قلاب‌های ردیابی کثیفی (dirty tracking hooks) به ویژگی‌های معمولی تنظیم‌کننده متصل می‌شوند، بنابراین هیچ تغییری در کد شما ایجاد نمی‌شود. نوشتن Value روی یک سلول به کتاب کار اطلاع می‌دهد و وابسته‌ها را کثیف علامت‌گذاری می‌کند；اختصاص یک Formula جدید یک تغییر ساختاری است، بنابراین کل نمودار را کهنه علامت‌گذاری کرده و اجرای بعدی Recalculate آن را قبل از ارزیابی بازسازی می‌کند. افزودن، حذف یا جابجایی برگه‌ها نیز نمودار را باطل می‌کند، زیرا هویت گره اندیس برگه را کدگذاری می‌نماید. هنگامی که هیچ نموداری فعال نیست — کتاب کاری که هرگز Recalculate را روی آن فراخوانی نمی‌کنید — قلاب‌ها تنها هزینه یک بررسی nil برای هر تخصیص را دارند، بنابراین بارهای کاری ساده خواندن و نوشتن تحت تاثیر قرار نمی‌گیرند

یک مرز شایسته بیان صادقانه است: نمودار وابستگی‌های بین سلول‌ها را ردیابی می‌کند، بنابراین یک تابع تعریف‌شده توسط کاربر که از طریق OnUserFunction ثبت شده است، با تغییر سلول‌هایی که آرگومان‌های آن را تغذیه می‌کنند، مانند هر فرمول دیگری دوباره ارزیابی می‌شود. اگر موتور را به این روش توسعه می‌دهید، مقاله مربوط به توابع سفارشی در موتور فرمول HotXLS پیمان فراخوانی (callback) و نحوه رسیدن مقادیر آرگومان را بررسی می‌کند

محاسبه مجدد افزایشی بخشی از موتور استاندارد XLSX در کامپوننت اکسل دلفی HotXLS در کنار محاسبه‌گر فرمول، نام‌های تعریف‌شده و خط لوله واردات/صادراتی است که به آن سرعت می‌بخشد. اگر برنامه دلفی یا C++Builder شما مدل‌های فعال را نگه می‌دارد — برگه‌های قیمت‌گذاری، کتاب‌های کاری تلفیقی، جریان‌های گزارش — متد Recalculate تفاوت بین محاسبه مجدد یک کتاب کار و محاسبه مجدد یک ویرایش را رقم می‌زند