ماتریس optionهای AGGREGATE و نشت gate در HotXLS برای Delphi

HotXLS، یعنی کامپوننت بومی صفحه‌گستردهٔ اکسل برای Delphi و C++Builder، در سپتامبر 2026 دو fix مربوط به AGGREGATE منتشر کرد. نسخهٔ 2.382.0 آرگومان optionها را اصلاح کرد تا کدهای 1/3/5/7 سطرهای مخفی را نادیده بگیرند، 2/3/6/7 خطاها را نادیده بگیرند و 0 تا 3 سلول‌های SUBTOTAL و AGGREGATE تودرتو را نادیده بگیرند، دقیقاً همان‌طور که مایکروسافت مستند کرده. نسخهٔ 2.382.3 بعد جلوی نشت آن پرچم‌های انتخاب را به ارزیابی همان سلول‌هایی گرفت که تابع به آن‌ها ارجاع می‌دهد. نقص اول به همان شکلی خجالت‌آور است که باگ‌های رونویسی جدول همیشه هستند: جای بیت‌ها عوض شده بود، پس هر فرمولی که یک کد option ناصفر به کار می‌برد سیاستی می‌گرفت که نویسنده‌اش نخواسته بود. دومی جالب‌تر است، چون شکلی است که در هر evaluatorی می‌بینی که از یک فیلد گذرا برای رساندن context به یک پیمایش بازگشتی استفاده می‌کند. یک aggregation بیرونی یک پرچم را مسلح می‌کند، یک بازه را می‌پیماید و به سلولی می‌رسد که فرمولش هنوز محاسبه نشده. آن فرمول روی همان calculator اجرا می‌شود، همان پرچم مسلح را می‌بیند و بی‌صدا سطرهای اشتباه را جمع می‌زند و عددی تولید می‌کند که هیچ‌کس نمی‌تواند فقط از متن فرمول توضیحش بدهد

optionهای 0 تا 7 در AGGREGATE واقعاً چه چیزی را انتخاب می‌کنند؟

آرگومان optionهای AGGREGATE یک ماتریس سه-بیتی است و آن سه بیت مستقل‌اند. بیت 0 (مقدار 1) یعنی سطرهای مخفی نادیده گرفته شوند، بیت 1 (مقدار 2) یعنی مقدارهای خطا نادیده گرفته شوند، و بیت 2 (مقدار 4) یعنی دست از نادیده گرفتن سلول‌های SUBTOTAL و AGGREGATE تودرتو بردار، چون برای کدهای پایین نادیده گرفتنشان پیش‌فرض است. دو چیز اینجا به‌سادگی برعکس فهمیده می‌شود. بیت سطر مخفی همان بیت پایین است، نه بیت وسط، پس AGGREGATE(9,1,...) شکل جمع فیلترشده است و AGGREGATE(9,2,...) شکل تحمل‌کنندهٔ خطا. و سیاست aggregation تودرتو نسبت به آن دو بیت معکوس است: فقط کدهای 4 تا 7 با سلولی که فرمول خودش یک SUBTOTAL یا AGGREGATE است مثل یک مقدار معمولی رفتار می‌کنند. ECMA-376 Part 1 §18.17.7 تابع SUBTOTAL را با همان تفکیک شامل-یا-نادیده سطر مخفی در قالب کدهای 1-11 و 101-111 تعریف می‌کند و AGGREGATE که در فایل‌های OOXML زیر پیشوند _xlfn. ذخیره می‌شود همان تفکیک را به آرگومان optionها تعمیم می‌دهد، پس جدولی که مایکروسافت برای تابع AGGREGATE منتشر می‌کند قراردادی است که یک موتور باید برآورده کند، نه یک راحتی

گزینه	سطرهای مخفی	مقدارهای خطا	SUBTOTAL / AGGREGATE تودرتو
0	شامل	منتشر	نادیده
1	نادیده	منتشر	نادیده
2	شامل	نادیده	نادیده
3	نادیده	نادیده	نادیده
4	شامل	منتشر	شامل
5	نادیده	منتشر	شامل
6	شامل	نادیده	شامل
7	نادیده	نادیده	شامل

چرا HotXLS ماتریس optionهای AGGREGATE را برعکس داشت؟

چون TXLSCalculator.CalcAggregateFunc اصلی از یک بازنویسی جدول نوشته شده بود، نه از خود جدول. این کد ignoreErrors := (optCode >= 4) and (optCode <= 7) را حساب می‌کرد و gate سطر مخفی را برای کدهای 2 و 3 و 6 و 7 مسلح می‌کرد، در حالی که سیاست aggregation تودرتو اصلاً پیاده نشده بود. مقالهٔ پیشین دربارهٔ سطرهای مخفی در SUBTOTAL و AGGREGATE همان شکاف را به‌عنوان یک محدودیت باز فهرست کرده و نگاشت قدیمی را همان‌طور که آن زمان منتشر می‌شد توصیف کرده بود؛ آن توصیف دربارهٔ کد درست و دربارهٔ اکسل غلط بود، و مدت زیادی کسی متوجه نشد چون آن دو سیاستی که بیشتر آدم‌ها با هم ترکیب می‌کنند، یعنی مخفی به‌علاوهٔ خطاها، زیر هر دو جدول روی کدهای 3 و 7 می‌افتند. فقط کدهای تک-بیتی این جابه‌جایی را رو کردند: AGGREGATE(9,1,A1:A4) جمع فیلترنشده را برمی‌گرداند و AGGREGATE(9,2,...) سطرهای مخفی را رد می‌کرد و در همان حال #DIV/0! را منتشر می‌کرد. این نقص از یک بازبینی استاتیک روی lxCalc.pas رو شد و به‌عنوان HXLS-008 در رجیستری known-issues پروژه ثبت شد، نه از یک فایل مشتری؛ و همین چیزی دربارهٔ نادر بودن کدهای تک-بیتی در workbookهای محیط عملیاتی می‌گوید. نسخهٔ 2.382.0 decode را به‌صورت سه آزمون عضویت در مجموعه از نو نوشت و یک gate دوم برای سیاست تودرتو اضافه کرد که از طریق یک callback جدید به نام TXLSIsSubtotalCell سیم‌کشی شده که workbook آن را کنار TXLSIsRowHidden فراهم می‌کند

// TXLSCalculator.CalcAggregateFunc، شکل v2.382.3
if (optCode < 0) or (optCode > 7) then
begin
  Result := lxErrorValue;            // اکسل کدهای بیرون از 0..7 را رد می‌کند
  Exit;
end;
ignoreErrors := optCode in [2, 3, 6, 7];
prevIgnoreHidden := FIgnoreHiddenRows;
prevIgnoreSubtotal := FIgnoreSubtotalCells;
FIgnoreHiddenRows := (optCode in [1, 3, 5, 7]) and Assigned(FIsRowHidden);
FIgnoreSubtotalCells := (optCode in [0, 1, 2, 3]) and Assigned(FIsSubtotalCell);
try
  // ... نگاشت function_num به iftab داخلی، پیمایش ref1..refN ...
finally
  FIgnoreHiddenRows := prevIgnoreHidden;
  FIgnoreSubtotalCells := prevIgnoreSubtotal;
end;

دقت کن که این دو پرچم بی‌قیدوشرط نسبت داده می‌شوند، نه اینکه فقط وقتی option بخواهدشان ست شوند. نسخهٔ 2.382.0 باز هم از if ... then FIgnoreHiddenRows := True استفاده می‌کرد، که یعنی یک AGGREGATE با کد 4 که داخل یک SUBTOTAL(109, ...) تودرتو نشسته بود gate سطر مخفی بیرونی را به ارث می‌برد به‌جای اینکه پاکش کند. نسبت دادن مقدار decode‌شده در ورود و برگرداندن مقدار قبلی در بلوک finally باعث می‌شود هر فراخوانی AGGREGATE برای طول پیمایشش مالک سیاست خودش باشد و نه بیشتر. نسخهٔ 2.382.0 فرم آرایه‌ای را هم صادق کرد: وقتی آرگومانی به یک آرایهٔ Variant یک- یا دو-بعدی ارزیابی می‌شود، CalcAggregateFunc حالا هر عنصر را می‌پیماید و سیاست خطا را به ازای هر عنصر اعمال می‌کند، در حالی که کد قدیمی فقط برای یک double از نوع NaN آزمون می‌کرد و در غیر آن کل آرایه را به ExcelSum می‌سپرد

چرا یک AGGREGATE بیرونی به فرمول‌هایی که به آن‌ها ارجاع می‌دهد نشت می‌کند؟

چون FIgnoreHiddenRows و FIgnoreSubtotalCells فیلدهایی روی calculator هستند و calculator میان هر فرمولی که در طول یک محاسبهٔ دوباره ارزیابی می‌شود شریک است. این gateها دقیقاً به‌عنوان فیلدهای اسکرچ طراحی شده بودند تا شش حلقهٔ پیمایش سلول بتوانند بدون رد کردن یک پارامتر از هر امضا consult‌شان کنند، و آن طراحی تا وقتی همه‌چیزِ در حال اجرا زیر یک gate مسلح به همان aggregationی تعلق داشته باشد درست است. این فرض در یک نقطهٔ مشخص می‌شکند: FGetValue. وقتی یک پیماینده از workbook مقدار یک سلول را می‌خواهد و آن سلول فرمولی با نتیجهٔ cache‌نشده دارد، workbook فرمول را کامپایل می‌کند و همان‌جا روی همان TXLSCalculator ارزیابی‌اش می‌کند، در حالی که gateهای بیرونی هنوز ست هستند. fixture رگرسیون در HotXLS.WorkbookApiTests.pas این شکست را با چهار سلول نشان می‌دهد. A1 مقدار 10 دارد، A2 مقدار 20 روی یک سطر مخفی، A3 فرمول =1/0 و A4 فرمول =SUBTOTAL(9,A1:A2) که مقدار درستش 30 است. حالا =AGGREGATE(9,7,A1:A4) را ارزیابی کن: سطرهای مخفی نادیده، خطاها نادیده، و SUBTOTAL تودرتو را به‌عنوان مقدار بشمار. اکسل 10 + 30 = 40 برمی‌گرداند. با A4 بدون cache، موتور پیش از 2.382.3 gate سطر مخفی را مسلح می‌کرد، تا A4 می‌پیمود، ارزیابی‌اش را به راه می‌انداخت و CalcSubtotalFunc برای کد 9 همان gate مسلح را به ارث می‌برد، چون این تابع پرچم را فقط برای کدهای 101 تا 111 ست می‌کند و هرگز پاکش نمی‌کند. A4 به‌جای 30 مقدار 10 ارزیابی می‌شد و جمع بیرونی 20 برمی‌گشت. هیچ‌کدام از دو فرمول در مسیری که عدد غلط را تولید کرد به سطرهای مخفی اشاره نمی‌کند

gate aggregation تودرتو به همان شکل در جهت مخالف نشت می‌کرد. با کدهای 0 تا 3، پرچم FIgnoreSubtotalCells مسلح است و پیمایندهٔ عمومی بازه در GetValueItemRange محترمش می‌شمارد، پس پیشینی که فرمولش =SUM(B1:B3) است بی‌صدا B2 را می‌انداخت اگر B2 تصادفاً یک SUBTOTAL داشت. بدتر، CalcSubtotalFunc در خروج FIgnoreSubtotalCells را به False ریست می‌کند نه اینکه مقدار قبلی را برگرداند، پس یک پیشین SUBTOTAL بدون cache که وسط پیمایش دیده شود gate بیرونی را برای هر سلول بعد از خودش از کار می‌انداخت. رجیستری known-issues پروژه این را زیر HXLS-008 به‌عنوان نشت حالت انتخاب تودرتو بایگانی می‌کند و همین نام درست برای این کلاس باگ است: یک پرچم گذرای سراسری که برای frameی که ستش کرده درست است و برای هر frameی که ارثش می‌برد غلط

AggregateGetCellValue و AggregateGetItemValue چگونه پیمایش را عایق می‌کنند

fix در v2.382.3 دور هر نقطه‌ای که AGGREGATE مقداری را می‌خواند که خودش حسابش نکرده مرزی می‌گذارد. TXLSCalculator.AggregateGetCellValue فراخوانی خام FGetValue را می‌پیچد: هر دو پرچم را ذخیره می‌کند، پاکشان می‌کند، fetch را انجام می‌دهد و در یک بلوک finally برشان می‌گرداند. aggregation بیرونی باز هم سیاست خودش را روی همان سلولی که تازه fetch کرده اعمال می‌کند، چون آزمون‌های سطر مخفی و سلول تودرتو در پیماینده و دور خود fetch رخ می‌دهند، اما خود فرمول پیشین با هیچ سیاستی اجرا می‌شود، که همان کاری است که اکسل می‌کند

function TXLSCalculator.AggregateGetCellValue(SheetIndex, Row, Col: Integer;
  var Value: Variant; var OutOfRange: Boolean): Integer;
var
  Hidden, Nested: Boolean;
begin
  Hidden := FIgnoreHiddenRows;
  Nested := FIgnoreSubtotalCells;
  FIgnoreHiddenRows := False;        // یک فرمول پیشین سیاست خودش را دارد
  FIgnoreSubtotalCells := False;
  try
    Result := FGetValue(SheetIndex, Row, Col, Value, OutOfRange);
  finally
    FIgnoreHiddenRows := Hidden;
    FIgnoreSubtotalCells := Nested;
  end;
end;

AggregateGetItemValue همین کار را برای آرگومان‌های غیر-بازه‌ای انجام می‌دهد و باید بیش از پاک کردن پرچم‌ها بکند، چون آرگومانی مثل A1:A4/(B1:B4-20) یک آرایهٔ محاسبه‌شده است که شکل عنصری‌اش باید دوام بیاورد. این wrapper یک بازهٔ ساده را از طریق AggregateGetCellValue به یک آرایهٔ Variant دو-بعدی مادی می‌کند و سلولی که کد خطا برگردانده را به VarAsError نگاشت می‌کند تا سیاست خطا باز هم به ازای هر عنصر قابل‌اعمال باشد، و از گره‌های عملگر دودویی و یکانی (SA_ADD و SA_DIV و SA_UNARMINUS و بقیه) با ApplyArrayBinaryOp و ApplyArrayUnaryOp بازگشتی می‌گذرد؛ هر چیز دیگری به GetValueItem معمول می‌افتد. دو نگهبان جلوی این مادی‌سازی نشسته‌اند: بازه‌ای بزرگ‌تر از EffectiveFormulaArrayMemoryLimit کد lxErrorResourceLimit را برمی‌گرداند و بازهٔ چند-شیتی یا وارونه #VALUE! می‌دهد. یک کد مربوط به محدودیت منابع عمداً حتی زیر optionهای 2/3/6/7 به‌عنوان خطای سلولی قابل‌نادیده‌پنداشتن رفتار نمی‌شود، چون موتوری که سیگنال out-of-memory خودش را ببلعد چون کاربر خواسته #N/A رد شود، دارد دروغ می‌گوید. هر سه پیمایندهٔ AGGREGATE یعنی AggregateCollectRange برای خانوادهٔ SUM، AggregateReduceVariance برای STDEV و VAR و PRODUCT، و AggregateReduceWithK برای MEDIAN و شکل‌های چندک، از FGetValue و GetValueItem به این دو wrapper سوئیچ شدند و هرکدام آزمون سلول تودرتو را از طریق FIsSubtotalCell به دست آوردند

AGGREGATE وقتی خطاها را نادیده نمی‌گیرد کدام خطا را برمی‌گرداند؟

همان خطای اصلی، از v2.382.3 به بعد. نسخهٔ 2.382.0 سلول‌های خطا را درست تشخیص می‌داد اما همه‌شان را به lxErrorValue فرو می‌ریخت، پس AGGREGATE(9,4,A1:A3) روی یک سلول #DIV/0! مقدار #VALUE! برمی‌گرداند، جایی که اکسل اولین خطایی را که می‌بیند بی‌تغییر منتشر می‌کند. هلپر جایگزین AggregateErrorCode یک Variant را به کد lxError* متناظرش نگاشت می‌کند، چه Variant یک varError واقعی باشد و چه یکی از آن هفت رشتهٔ خطا، و AggregateValueIsError حالا فقط یک آزمون برای نتیجهٔ ناصفر است. هر پیماینده اولین کد خطایی را که می‌بیند ثبت می‌کند و همان کد را برمی‌گرداند، که این هم یعنی سلولی که فرمولش هرگز محاسبه نشده و خطایش در نتیجه به‌صورت یک کد بازگشتی از FGetValue می‌رسد نه به‌صورت یک Variant cache‌شده، به همان شیوهٔ نسخهٔ cache‌شده منتشر می‌شود. دو تابع شمارشی داخل AggregateCollectRange برخورد ویژه‌ای می‌گیرند و آن برخورد با SUBTOTAL می‌خواند نه با SUM. برای تابع داخلی 0 یعنی COUNT، یک سلول خطا هرگز شمرده و هرگز منتشر نمی‌شود، بی‌توجه به کد optionها، چون COUNT فقط عددها را می‌شمارد. برای تابع داخلی 169 یعنی COUNTA، یک سلول خطا یک مقدار غیرخالی است و 1 حساب می‌شود مگر اینکه کد optionها خطاها را نادیده بگیرد، که در آن صورت رد می‌شود. همین عدم‌تقارن است که اکسل بیرون از AGGREGATE هم با COUNT و COUNTA دارد و همان نوع جزئیاتی است که یک قاعدهٔ عمومی «اگر خطا بود منتشر کن» بی‌صدا غلط از آب درمی‌آورد

ماتریس رگرسیون هشت-گزینه‌ای چه چیزی را تأیید می‌کند

همان fixture توصیف‌شده بالا در AggregateFunc_OptionMatrixCoversHiddenErrorsAndNestedAggregates به‌صورت یک ماتریس کامل اجرا می‌شود: برای هر کد option از 0 تا 7 هم شکل SUM و هم شکل MEDIAN را روی A1:A4 ارزیابی می‌کند و نتیجه را با یک انتظار دستی‌ساخته مقایسه می‌کند. کدهای 0 و 1 و 4 و 5 باید #DIV/0! را از A3 منتشر کنند، چون هیچ‌کدام خطاها را نادیده نمی‌گیرد. کد 2 به SUM مقدار 30 و به MEDIAN مقدار 15 می‌دهد، از 10 و 20 با رد شدن A4 تودرتو. کد 3 مقدار 10 و 10 می‌دهد. کد 6 مقدار 60 و 20 می‌دهد، چون آن 30 که در A4 است حالا حساب می‌شود. کد 7 مقدار 40 و 20 می‌دهد، که همان حالتی است که پیش از fix نشت، 20 برمی‌گرداند. اجرای پذیرش گسترده‌تر که در رجیستری known-issues ثبت شده همهٔ نوزده شمارهٔ تابع را در برابر همهٔ هشت کد پوشش می‌دهد، با هر پیشین هم cache‌شده و هم بدون cache، برای 304 سناریو روی Win32 و Win64

var
  Book: TXLSXWorkbook;
  Sheet: TXLSXWorksheet;
begin
  Book := TXLSXWorkbook.Create;
  try
    Sheet := Book.Sheets.Add('Data');
    Sheet.Cells[1, 1].Value := 10;
    Sheet.Cells[2, 1].Value := 20;
    Sheet.Cells[3, 1].Formula := '=1/0';
    Sheet.Cells[4, 1].Formula := '=SUBTOTAL(9,A1:A2)';   // جمع زیرگروه = 30
    Sheet.RowHidden[2] := True;

    Sheet.Cells[6, 1].Formula := '=AGGREGATE(9,1,A1:A4)'; // #DIV/0!  مخفی رد شده، خطا منتشر می‌شود
    Sheet.Cells[7, 1].Formula := '=AGGREGATE(9,3,A1:A4)'; // 10       مخفی و خطا و تودرتو رد شده
    Sheet.Cells[8, 1].Formula := '=AGGREGATE(9,6,A1:A4)'; // 60       فقط خطاها رد شده
    Sheet.Cells[9, 1].Formula := '=AGGREGATE(9,7,A1:A4)'; // 40       پیش از v2.382.3 برابر 20 بود
    Book.Recalculate;
    Book.SaveAs('aggregate-options.xlsx');
  finally
    Book.Free;
  end;
end;

مرز هنوز کجاست

پیش از آنکه روی این پایه چیزی بسازی سه محدودیت ارزش دانستن دارند. اول، محمول aggregation تودرتو متنی است. TXLSXWorkbook.GetCalcIsSubtotalCell و همزادش در موتور کلاسیک وقتی True جواب می‌دهند که فرمول یک سلول با SUBTOTAL( یا AGGREGATE( یا _xlfn.AGGREGATE( شروع شود، با علامت مساوی ابتدایی یا بدون آن، پس فرمولی مثل =IF(C1,SUBTOTAL(9,B1:B9),0) یا =SUBTOTAL(9,B1:B9)*2 به‌عنوان تودرتو شناخته نمی‌شود و با کدهای 0 تا 3 دوبار شمرده می‌شود، جایی که اکسل ردش می‌کرد؛ تولیدکننده‌ای که جمع‌های زیرگروه محاسبه‌شده بیرون می‌دهد باید فراخوانی aggregation را در ابتدای فرمول نگه دارد. دوم، این عایق‌بندی در همان سه پیمایندهٔ AGGREGATE زندگی می‌کند. CalcSubtotalFunc باز هم از GetValueItemRange و CollectRangeValues و SubtotalReduceVariance می‌گذرد که مستقیم FGetValue را صدا می‌زنند، پس یک SUBTOTAL(109, ...) که بازه‌اش یک فرمول پیشین بدون cache را در بر می‌گیرد باز هم می‌تواند gate سطر مخفی‌اش را به آن پیشین پاس بدهد. یک Recalculate کامل پیشین‌ها را پیش از وابسته‌ها ارزیابی می‌کند، پس مسیر cache‌شده گرفته می‌شود و gate هرگز ارث برده نمی‌شود؛ این در معرض بودن به ارزیابی ad hoc از طریق Calculate و به workbookهایی محدود است که بدون مقدارهای cache‌شده بارگذاری می‌شوند، و اگر به محاسبهٔ دوبارهٔ افزایشی روی گراف وابستگی تکیه می‌کنی تا مدل‌های بزرگ پاسخ‌گو بمانند، همان تضمین ترتیب است که این نشت را خفته نگه می‌دارد. سوم، هر دو gate مشروط به Assigned(FIsRowHidden) و Assigned(FIsSubtotalCell) هستند. هر دو facade مربوط به workbook این callbackها را در سازنده‌هایشان سیم‌کشی می‌کنند، اما کدی که یک TXLSCalculator را دستی و فقط با همان دو آرگومان اصلی بسازد، بی‌صدا برای هر کد optionها رفتار قدیمی شامل-همه‌چیز را می‌گیرد. وقتی یک جمع غلط به نظر می‌رسد و متن فرمول درست، ردگیری گام‌به‌گام ارزیابی سریع‌ترین راه است تا ببینی یک پیشین زیر یک gate ارث‌برده ارزیابی شده یا یک callback به‌سادگی هرگز متصل نشده بود

موتور محاسبه‌ای که اینجا توصیف شد و decoder مربوط به optionها و wrapperهای عایق‌بندی‌شدهٔ fetch و ماتریس رگرسیونی که همه‌شان را پین می‌کند، همه به‌صورت سورس همراه کامپوننت صفحه‌گستردهٔ HotXLS برای Delphi عرضه می‌شوند، که workbookهای XLS و XLSX و ODS را در Delphi و C++Builder بدون نصب اکسل می‌خواند و می‌نویسد و دوباره محاسبه می‌کند