محاسبات تکراری برای ارجاعات چرخشی در دلفی

برای محاسبه ارجاعات چرخشی (circular references) آگاهانه در دلفی، ابزار HotXLS محاسبات تکراری را روی موتور XLSX خود ارائه می‌دهد: کافی است TXLSXWorkbook.Iterate را روی True تنظیم کنید تا متد TXLSXWorkbook.Recalculate هر چرخه ارجاع شناسایی‌شده را به سمت یک نقطه ثابت طی کند — حداکثر تا تعداد مراحل IterateCount، یا تا زمانی که تغییر هر سلول کمتر از IterateDelta شود — به جای اینکه مقدار خطای #REF! را برگرداند و متوقف شود

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

چرا ارجاع چرخشی به طور پیش‌فرض منجر به بروز خطا می‌شود؟

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

حالت پیش‌فرض یک انتخاب ایمن است زیرا بیشتر چرخه‌ها باگ هستند: مانند یک ردیف خلاصه که به طور تصادفی در محدوده SUM خود قرار گرفته، یا یک کپی-پیست که یک مرجع را روی خودش منتقل کرده است. یک کد خطای واضح در زمان محاسبه مجدد دقیقاً همان چیزی است که برای این موارد می‌خواهید. اما کلاس خاص و مهمی از مدل‌ها به طور آگاهانه چرخشی هستند. محاسبات بهره روی بهره (interest-on-interest)، تخصیص هزینه‌ها یا سربارهای چرخشی بین دپارتمان‌ها، و محاسبات کارمزد مبتنی بر تراز، همگی مقداری را توصیف می‌کنند که به طور قانونی به ورودی‌های خودش بازخورد می‌دهد — و اکسل آنها را تنها زمانی محاسبه می‌کند که کاربر گزینه File → Options → Formulas → Enable iterative calculation را فعال نماید

How do you enable iterative calculation in HotXLS?

ابزار HotXLS آن چک‌باکس اکسل را با سه ویژگی در TXLSXWorkbook: ویژگی Iterate این حالت را فعال می‌نماید و زمانی که مقدار آن True باشد، یک چرخه شناسایی‌شده به جای تولید lxErrorRef به یک حل‌کننده تکراری تحویل داده می‌شود. جفت سلول بهره روی بهره مرسوم را در نظر بگیرید که در آن تراز نهایی به بهره و بهره به تراز بستگی دارد

var
  Book: TXLSXWorkbook;
  Sheet: TXLSXWorksheet;
begin
  Book := TXLSXWorkbook.Create;
  try
    Sheet := Book.Sheets.Add('Model');
    Sheet.Cells[1, 2].Value   := 1000;     // B1: opening principal
    Sheet.Cells[2, 2].Value   := 0.05;     // B2: period rate
    Sheet.Cells[3, 2].Formula := 'B1+B4';  // B3: balance  = principal + interest
    Sheet.Cells[4, 2].Formula := 'B3*B2';  // B4: interest = balance * rate

    Book.Iterate := True;                   // opt in to iterative calculation
    if Book.Recalculate = lxOk then
      // B3 converges to 1052.63..., B4 to 52.63...
      Report(Sheet.Cells[3, 2].Value);
  finally
    Book.Free;
  end;
end;

چه چیزهایی باعث متوقف شدن تکرار می‌شود؟

دو شرط توقف مستقل، حل‌کننده را محدود می‌کنند، و درک هر دو مانع از بروز خطا یا چرخش بی‌نهایت یک مدل می‌شود. ویژگی IterateCount سقف سختی برای تعداد دفعات ارزیابی مجدد اعضای چرخه است؛ مقدار پیش‌فرض آن ۱۰۰ است که با اکسل همخوانی دارد. IterateDelta آستانه همگرایی است: پس از هر مرحله، حل‌کننده بزرگ‌ترین تغییر عددی را در تمام سلول‌های چرخه اندازه‌گیری می‌کند، و به محض اینکه آن حداکثر تغییر به کمتر از IterateDelta — با مقدار پیش‌فرض 0.001 — برسد، حلقه تکرار زودتر متوقف می‌شود. هر کدام از این شرایط که زودتر برقرار شود، تکرار را پایان می‌دهد

یک نتیجه ارزش بیان صریح دارد، زیرا مرز واقعی این ویژگی است. وقتی سقف تکرار بدون رسیدن تغییرات به زیر IterateDelta تکمیل شود، متد SolveCycleIteratively خطایی ایجاد نمی‌کند — مقدار lxOk را برمی‌گرداند و سلول‌ها را با مقادیر آخرین تکرار رها می‌سازد، دقیقاً همان‌طور که اکسل در زمان رسیدن به محدودیت تکرار بدون همگرایی، آخرین اعداد محاسبه‌شده را می‌نویسد. بنابراین، یک کد برگشتی موفقیت‌آمیز از Recalculate در حالت تکرار به معنای «اجرای حل‌کننده» است، نه «همگرایی حل‌کننده». مدلی که حلقه بازخورد آن واگرا یا نوسانی باشد، به طور بی‌صدا تمام مراحل IterateCount را مصرف کرده و اعدادی را پس می‌دهد که اصلاً یک نقطه ثابت نیستند، و هیچ استثنایی این تفاوت را نشان نمی‌دهد

تنظیمات چگونه در فایل‌های XLSX و XLS ذخیره می‌شوند؟

تنظیمات محاسبات تکراری در هر دو قالب صفحه‌گسترده ذخیره می‌شوند، بنابراین کارپوشه بازشده در اکسل همان‌گونه رفتار می‌کند که کد دلفی شما پیکربندی کرده است. در سمت XLSX، نویسنده عنصر OOXML <calcPr> را تنها در زمانی که Iterate روی True باشد صادر می‌کند، و هر ویژگی را که همچنان روی مقدار پیش‌فرض خود باشد نادیده می‌گیرد تا خروجی حداقلی بماند: یک کارپوشه که از مقادیر پیش‌فرض استفاده می‌کند فقط عبارت <calcPr iterate="1"/> را می‌نویسد، در حالی که ویژگی iterateCount تنها زمانی ظاهر می‌شود که با ۱۰۰ متفاوت باشد و iterateDelta تنها زمانی که با 0.001 تفاوت داشته باشد. در زمان باز شدن، کلاس TXLSXWorkbook همان سه ویژگی را می‌خواند، بنابراین این فرآیند رفت و برگشت متقارن است

موتور قدیمی BIFF8 (.xls)، یعنی TXLSWorkbook، وضعیت معادل را از طریق سه رکورد مجزا در زیر سه ویژگی متفاوت حمل می‌کند. EnableIteration به رکورد CalcIter ($0011, [MS-XLS] §2.4.33)، ویژگی MaxIterations به رکورد CalcCount ($000C, [MS-XLS] §2.4.31)， و MaxIterationChange به رکورد CalcDelta ($0010, [MS-XLS] §2.4.32) نگاشت می‌شوند. متدها محدوده‌های مشخص‌شده را اعمال می‌کنند — مقدار CalcCount باید در محدوده ۱ تا ۳۲۷۶۷ باشد، بنابراین MaxIterations محدود می‌شود، و مقدار منفی MaxIterationChange به مقدار پیش‌فرض 0.001 بازمی‌گرداند. آنها را روی یک کارپوشه بارگذاری‌شده .xls تنظیم کنید تا هر سه رکورد محاسباتی در زمان ذخیره به درستی نوشته شوند

var
  Book: TXLSWorkbook;   // BIFF8 (.xls) engine
begin
  Book := TXLSWorkbook.Create;
  try
    Book.Open('model.xls');
    Book.EnableIteration    := True;    // CalcIter  record $0011
    Book.MaxIterations      := 500;     // CalcCount record $000C (clamped to 1..32767)
    Book.MaxIterationChange := 0.0001;  // CalcDelta record $0010
    Book.SaveAs('model.xls');           // the three calc records round-trip
  finally
    Book.Free;
  end;
end;

به تفاوت نام‌گذاری آگاهانه توجه کنید: موتور XLSX از اصطلاحات Iterate / IterateCount / IterateDelta (واژگان OOXML) استفاده می‌کند، در حالی که موتور BIFF8 از اصطلاحات EnableIteration / MaxIterations / MaxIterationChange (همسو با نام رکوردهای [MS-XLS]) استفاده می‌نماید. هر دو گروه همان سه دکمه تنظیم را توصیف می‌کنند — یک سوئیچ فعال/غیرفعال، یک سقف تکرار، و یک اختلاف همگرایی — با مقادیر پیش‌فرض یکسان شامل غیرفعال، ۱۰۰، و 0.001

چه زمانی ارجاع چرخشی یک باگ است تا یک مدل؟

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

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

چک‌لیست عملی کوتاه است. قبل از اینکه به تکرار تکیه کنید، تأیید نمایید که حلقه یک نقطه ثابت واقعی دارد؛ مقدار IterateDelta را به اندازه‌ای دقیق نگه دارید که عبارت «همگرا شده» همان معنایی را داشته باشد که مدل شما نیاز دارد؛ و پس از اجرای Recalculate که انتظار دارید همگرا شود، یک خروجی مشخص را به عنوان بررسی اولیه بررسی کنید به جای اینکه تنها به کد lxOk اعتماد نمایید، زیرا این کد نمی‌تواند همگرایی را از رسیدن به سقف تکرار متمایز کند

محاسبه ارجاع چرخشی تکراری بخشی از موتور XLSX در HotXLS Delphi Excel Component است، در کنار محاسبه مجدد افزایشی گراف وابستگی که بر پایه آن ساخته شده است و ذخیره‌سازی OOXML و BIFF8 که تنظیمات را به هر فایلی که می‌نویسید منتقل می‌کند. برای مدل‌های مالی و مهندسی که به صورت آگاهانه چرخشی طراحی شده‌اند، این ویژگی تفاوت بین یک کد خطا و یک پاسخ واقعی است