اسکن‌های lookup در HotXLS و ارجاع‌های حلقوی کاذب

=VLOOKUP(A1,B:B,1) را در سلولی در ستون B بگذارید و اکسل بدون شکایت محاسبه‌اش می‌کند. همان workbook را به یک موتور بازمحاسبه گراف-وابستگی بدهید و به احتمال زیاد یک خطای ارجاع حلقوی می‌گیرید، چون فرمول به بازه‌ای وابسته است که خود فرمول را دربرمی‌گیرد. HotXLS دقیقاً همان را گزارش می‌کرد تا v2.361.98. fix مورد خاصی برای بازه‌های تمام‌ستونی نیست؛ تمایزی است میان دو جنس یال وابستگی که یک موتور صفحه‌گسترده لازم دارد و یک گراف جهت‌دار ساده ندارد

آرگومان آرایه‌جست‌وجوی خانواده lookup، یعنی LOOKUP، MATCH، HLOOKUP، VLOOKUP، XLOOKUP و XMATCH، اکنون به‌عنوان یک ارجاع scan علامت می‌خورد. یک ارجاع scan همچنان کثیفی می‌کارد، پس ویرایش سلولی درون بازه فرمول را بازمحاسبه می‌کند، اما هرگز به آشکارسازی چرخه یا ترتیب ارزیابی سهم نمی‌دهد. چرخه‌های واقعی همچنان پیدا می‌شوند؛ کاذب‌ها رفته‌اند

چرا اکسل اجازه می‌دهد بازه lookup حاوی خود فرمول باشد؟

چون آن آرگومان آن‌طور که یک عملوند حسابی مصرف می‌شود مصرف نمی‌شود. خانواده lookup بازه را برای مقادیر کش‌شده اسکن و یک تطابق برمی‌گرداند؛ لازم ندارد بازه اول تا انتها ارزیابی شده باشد. اکسل یک بازه lookup خودهم‌پوشان را به‌عنوان خواندن هر چه آن سلول‌ها فعلاً دارند می‌بیند، که همان معناشناسی‌ای است که روی هر workbook غیرتکراری اعمال می‌کند: سلول‌هایی که در این گذر بازمحاسبه نشده‌اند آخرین مقدار محاسبه‌شده‌شان را می‌دهند

ارجاع‌های تمام‌ستونی این را حالت رایج می‌کنند نه حالت عجیب را. B:B راه اصیل نوشتن «کل جدول lookup» در sheetی است که سطرها به آن افزوده می‌شوند، و هر فرمولی که در ستون B زندگی کند آنگاه درون بازه lookup خودش است. مدل‌های مالی، sheetهای تطبیق و workbookهای حسابرسی مدام این کار را می‌کنند، معمولاً بدون آنکه کسی بفهمد بازه هم‌پوشان است

سلول B7 حاوی VLOOKUP(A1,B:B,1) درون بازه lookup تمام‌ستونی خودش یعنی B:B است، هم‌پوشانی-با-خود که اکسل از مقادیر کش‌شده بدون شکایت محاسبه می‌کند — بازه‌های lookup تمام‌ستونی هم‌پوشانی-با-خود را در مدل‌های مالی و workbookهای حسابرسی حالت عادی می‌کنند، نه گوشه‌ای عجیب

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

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

وابستگی‌ها را از =VLOOKUP(A1,B:B,1) در سلول B7 استخراج کنید و آرگومان دوم بازه‌ای می‌دهد که خود B7 را دربرمی‌گیرد. گراف اکنون یک خودحلقه دارد. درجه-ورودی آن گره هرگز به صفر نمی‌رسد، پس گذر توپولوژیک هرگز نمی‌تواند زمان‌بندیش کند، و گذر مؤلفه آن را چرخه طبقه‌بندی می‌کند. موتور درباره گرافی که به آن داده شده درست استدلال می‌کند. گراف مدل غلط است، چون یک نوع یال را رمزگذاری می‌کند جایی که صفحه‌گسترده دو نوع دارد

بازه lookup مربوط به B:B به گره گراف B7 یک خودحلقه می‌دهد، پس درجه-ورودی هرگز به صفر نمی‌رسد و HotXLS پیش از v2.361.98 یک ارجاع حلقوی کاذب گزارش می‌کرد — موتور بازمحاسبه درباره گرافی که به آن داده شده بود درست استدلال می‌کرد؛ گراف مدل غلطی برای یک صفحه‌گسترده بود

دو رده یال، یک گراف

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

جداسازی همان چیزی است که معناشناسی را درست می‌کند. یال‌های scan توسط انتشار کثیفی پیموده می‌شوند، پس ویرایشی هر جا در B:B همچنان B7 را کثیف علامت می‌زند و B7 بازمحاسبه می‌شود. یال‌های scan هرگز در درجه-ورودی شمرده نمی‌شوند و هرگز وارد سازنده مؤلفه نمی‌شوند، پس نمی‌توانند بن‌بست توپولوژیک بسازند و نمی‌توانند چرخه طبقه‌بندی شوند. هر دو پیاده‌سازی گراف در کتابخانه، گراف کلاسیک به‌ازای هر workbook و گراف workspace میان-workbookی که تحلیل مؤلفه را حمل می‌کند، با هم تغییر کردند؛ رهاکردنشان به واگرایی workbookی تولید می‌کرد که بسته به اینکه تنها باز شده یا به‌عنوان بخشی از یک workspace جور دیگری بازمحاسبه می‌کند

یال‌های scan از TXLSDepRange.LookupScan انتشار کثیفی را به ScanPrecedents و ScanDependents می‌رانند اما هرگز در درجه-ورودی یا چرخه‌ها شمرده نمی‌شوند — ویرایش‌ها درون B:B همچنان فرمول را کثیف علامت می‌زنند، با این حال یال‌های scan نمی‌توانند گذر توپولوژیک را قفل کنند یا چرخه‌ای بسازند

var
  Book: TXLSXWorkbook;
  Sheet: TXLSXWorksheet;
begin
  Book := TXLSXWorkbook.Create;
  try
    Sheet := Book.Sheets.Add('Ledger');
    Sheet.Cells[1, 1].Value := 'ACC-4471';
    Sheet.Cells[1, 2].Value := 1200.00;
    // بازه lookup ستون B را پوشش می‌دهد، و این فرمول درونش زندگی می‌کند
    Sheet.Cells[7, 2].Formula := 'VLOOKUP(A1,B:B,1)';

    case Book.Recalculate of
      lxOk:
        // پیش از v2.361.98 این شاخه برای این sheet دست‌نیافتنی بود
        SaveReport(Book);
      lxErrorRef:
        LogWarning('Genuine circular reference - review model inputs');
    end;
  finally
    Book.Free;
  end;
end;

با کنار گذاشتن یال‌های scan از ترتیب، چه چیزی را وا می‌گذارید

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

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

خطر رگرسیون که درون fix قایم است

افزودن LookupScan به TXLSDepRange خطر بیاورد که ربطی به lookupها ندارد و همه‌اش ربط به پاسکال دارد. TXLSDepRange یک رکورد مدیریت‌نشده است، پس یک متغیر محلی از آن نوع صفر-مقداردهی اولیه نمی‌شود. پس هر جا در codebase که یکی را دستی می‌سازد، شامل بلوک‌های وابستگی data-table و چند helper تست، باید به‌روزرسانی می‌شد تا فیلد جدید را صریح ست کند. یکی را از دست بدهید و هر بایتی که اتفاقاً روی stack بوده تصمیم می‌گیرد آن ارجاع یال scan حساب شود، که باگ بازمحاسبه‌ای تولید می‌کند که با تغییرات بی‌ربط کد ظاهر و ناپدید می‌شود

// یک فیلد Boolean جدید در یک رکورد مدیریت‌نشده هر محل
// ساخت دستی را یک باگ پنهان می‌کند. دو اصطلاح امن:
var
  R: TXLSDepRange;
begin
  FillChar(R, SizeOf(R), 0);      // همه را صفر کن، بعد پر کن
  R.Sheet1 := SheetIndex;
  R.Sheet2 := SheetIndex;
  R.Row1 := Row; R.Col1 := Col;
  R.Row2 := Row; R.Col2 := Col;

  // یا هر فیلد را، شامل فیلد جدید، در هر محل ست کن
  R.LookupScan := False;
end;

قاعده کلی که این نصیب شد: افزودن یک فیلد به رکوردی که در بیش از مشتی جا روی stack ساخته می‌شود تغییری پرخطرتر از ظاهرش است، و کامپایلر به شما کمک نمی‌کند محل‌ها را پیدا کنید. اگر رکورد از یک مسیر داغ قابل‌رسیدن است، یک helper که کامل مقداردهی‌اش می‌کند را به اعتماد به اینکه هر call site به‌روز می‌شود ترجیح دهید

تمایز یک چرخه واقعی از یک هم‌پوشانی scan

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

اگر در حال حسابرسی یک workbook هستید و می‌خواهید بدانید موتور واقعاً کدام ارجاع‌ها را حل کرده و به چه ترتیبی، tracer ارزیابی ابزار آن است؛ مقاله tracer ارزیابی فرمول پوشش می‌دهد چطور خروجی‌اش را بخوانید. HotXLS یک مؤلفه صفحه‌گسترده بومی Delphi و C++Builder است که XLS، XLSX، ODS و CSV را بدون نصب Excel می‌خواند و می‌نویسد، و موتور بازمحاسبه روی همه فرمت‌ها یکی است؛ پوشش فعلی تابع و موتور در صفحه محصول HotXLS Delphi spreadsheet component فهرست شده است