ردیف‌های پنهان SUBTOTAL و AGGREGATE در HotXLS Delphi

اگر SUBTOTAL(109, ...) و SUBTOTAL(9, ...) روی یک workbook دارای ردیف‌های پنهان همان عدد را برگردانند، یکی از این دو اشتباه است. HotXLS، مؤلفه بومی صفحه‌گسترده Excel برای Delphi و C++Builder، دقیقاً همین‌گونه رفتار می‌کرد تا نسخه 2.197.0، چون موتور محاسبه‌اش هیچ راهی برای پرسیدن از یک صفحه‌کار درباره پنهان‌بودن یک ردیف مشخص نداشت

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

چرا SUBTOTAL 109 ردیف‌های پنهان را شامل می‌شود؟

چون در بیشتر طراحی‌های موتور، لایه‌ای که یک فرمول را ارزیابی می‌کند هرگز درباره دیده‌پذیری ردیف چیزی یاد نمی‌گیرد. HotXLS یک مورد کتاب‌درسی بود: موتور محاسبه در lxCalc.pas به مقادیر سلول فقط از راه یک callback واحد TXLSGetValue دسترسی داشت که برای یک سه‌گانه (sheet, row, column) یک مقدار برمی‌گرداند و بس. دیده‌پذیری یک ویژگی نمایشی است که روی رکورد ردیف ذخیره می‌شود، و هیچ بخشی از آن رکورد در طول زنجیره فراخوانی سفر نمی‌کرد. بنابراین موتور یک مسیر تجمیع داشت، و هر دو نیمه جدول شماره‌تابع SUBTOTAL به آن ریزالو می‌شدند. این کلاس نقص خطای گرد‌کردنی نیست: این کل دلیل وجود نیمه دوم جدول است. ECMA-376 بخش ۱، منتشرشده به‌عنوان ISO/IEC 29500-1، SUBTOTAL را در تعاریف تابع فرمولش (§18.17.7) با یک آرگومان اول تعریف می‌کند که هم تجمیع درونی و هم خط‌مشی ردیف پنهان را انتخاب می‌کند. کدهای ۱ تا ۱۱ به AVERAGE، COUNT، COUNTA، MAX، MIN، PRODUCT، STDEV، STDEVP، SUM، VAR و VARP نگاشت می‌شوند و مقادیر ردیف‌های دستی‌پنهان‌شده را شامل می‌شوند. کدهای ۱۰۱ تا ۱۱۱ همان یازده تجمیع را انتخاب می‌کنند و آن‌ها را استثنا می‌کنند. کاربری که 109 به‌جای 9 تایپ می‌کند دارد یک بیانیه عمدی درباره داده پنهان می‌سازد، و موتوری که این تمایز را بی‌صدا فرومی‌ریزد آن بیانیه را نادیده می‌گیرد

شماره‌های تابع درون موتور به چه چیزی نگاشت می‌شوند

HotXLS آرگومان اول SUBTOTAL را در CalcSubtotalFunc حل می‌کند، که کدهای ۱۰۱ تا ۱۱۱ را روی همان شناسه‌های تابع درونی کدهای ۱ تا ۱۱ نرمال می‌کند و سپس روی خود تجمیع دیسپچ می‌کند. بیشتر این خانواده از راه انباره‌گر افزایشی ExcelSum جریان می‌یابد، همانی که SUM، COUNT، COUNTA، MIN، MAX، و AVERAGE را مدیریت می‌کند. پنج‌تا نمی‌توانند: STDEV، VAR، STDEVP، VARP، و PRODUCT به یک گذر فرم‌بسته روی داده نیاز دارند، پس CalcSubtotalFunc کدهای درونی ۱۲، ۴۶، ۱۹۳، ۱۹۴، و ۱۸۳ را به یک کاهنده جداگانه هدایت می‌کند، SubtotalReduceVariance. این تفکیک اولین چیزی است که ارزش نقشه‌برداری پیش از دست‌زدن به هرچیز را دارد، چون دو مسیر تجمیع مستقل یعنی دو حلقه پیمایش سلول مستقل، و یک رفع مشکل که فقط روی یکی از آن‌ها اعمال شود بدترین نتیجه ممکن را تولید می‌کند: SUBTOTAL(109, ...) فیلتر را رعایت می‌کند در حالی که SUBTOTAL(107, ...) روی همان بازه نمی‌کند. شمارش حلقه‌ها در HotXLS وقتی AGGREGATE هم شامل شد شش‌تا را نشان داد، پخش‌شده در سراسر ارزیابی بازه، جمع‌آوری بازه ساده، و سه کاهنده جداگانه

چرا یک فیلد اسکرچ به‌جای شش امضای جدید؟

چون رد‌کردن یک پارامتر جدید از میان شش تابع پیمایش سلول، به‌علاوه هرچیزی که آن‌ها را فراخوانی می‌کند، یک تغییر گسترده در یک مسیر کد داغ برای خاطر یک بولین است. HotXLS از قبل یک پیشینه برای جایگزین داشت: یک فیلد گذرا روی محاسبه‌گر، در همان روحیه فیلد اسکرچی که GetRangeInfo برای ثبت اینکه چه زمانی یک ارجاع سه‌بعدی به یک workbook خارجی ریزالو شد استفاده می‌کند. نسخه 2.197.0 دومی را افزود. موتور یک نوع callback به دست آورد، TXLSIsRowHidden، که به‌صورت یک تابع از (SheetIndex, row) که Boolean برمی‌گرداند اعلان شده، در FIsRowHidden ذخیره‌شده، به‌علاوه یک پرچم گذرای FIgnoreHiddenRows. پرچم در ورودی CalcSubtotalFunc وقتی کد تابع در ۱۰۱ تا ۱۱۱ بیفتد مسلح می‌شود، و در ورودی CalcAggregateFunc برای کدهای گزینه AGGREGATE که استثناکردن ردیف پنهان را انتخاب می‌کنند. سپس هر حلقه پیمایش سلول آن را بازرسی می‌کند و وقتی مسلح باشد یک ردیف را رد می‌کند، که به هرکدام یک خط اضافه می‌کند

// The shape repeated in all six cell-walk loops
for sh := s1 to s2 do
  for rr := r1 to r2 do
  begin
    if FIgnoreHiddenRows and Assigned(FIsRowHidden) and FIsRowHidden(sh, rr) then
      Continue;
    for cc := c1 to c2 do
    begin
      // ... fold Cells[rr, cc] into the accumulator ...
    end;
  end;

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

prevIgnoreHidden := FIgnoreHiddenRows;
if (fnCode >= 101) and (fnCode <= 111) and Assigned(FIsRowHidden) then
  FIgnoreHiddenRows := True;
try
  // aggregate over Item.Child[2] .. Item.Child[ChildCount]
  // every Exit path below is covered by the finally
finally
  FIgnoreHiddenRows := prevIgnoreHidden;
end;

آزمون Assigned چیزی است که تغییر را سازگار نگه می‌دارد. HotXLS سازنده TXLSCalculator را با یک پارامتر سوم که پیش‌فرضش nil است گسترش داد، پس هر کدی که یک TXLSCalculator با فراخوانی قدیمی دوآرگومانی بسازد همچنان کامپایل می‌شود و همچنان رفتار قدیمی شامل-پنهان را می‌گیرد. هیچ‌چیز درباره API موجود شکل عوض نکرد

بیت ردیف پنهان واقعاً از کجا می‌آید؟

از صفحه‌کار، از راه دو منبع متفاوت، چون HotXLS دو موتور workbook حمل می‌کند. سمت قدیمی BIFF از TXLSRowInfoList.GetHidden پاسخ می‌دهد، که از راه TXLSWorkbook.GetRowHidden رسیده. سمت OOXML از TXLSXWorksheet.GetRowHidden پاسخ می‌دهد، که از راه TXLSXWorkbook.GetCalcRowHidden رسیده. هر دو در زمان ساخت به محاسبه‌گر سیم‌کشی شده‌اند، در کنار callback مقدار سلول که آینه می‌کنند. قراردادهای ردیف جایی است که این نوع پل معمولاً به مسیر اشتباه می‌رود، پس ارزش گفتن صریح دارند. محاسبه‌گر یک ردیف صفر-پایه به callback می‌سپارد، که با مختصاتی که TXLSGetValue از قبل استفاده می‌کند مطابقت دارد. صفحه‌کار XLSX نقشه ردیف-پنهانش را با شماره ردیف یک-پایه کلیدگذاری می‌کند، دقیقاً همان‌طور که Excel ردیف‌ها را شماره‌گذاری می‌کند، که همان چیزی است که ویژگی عمومی RowHidden[ARow] نمایش می‌دهد. بنابراین پل XLSX یکی پیش از جست‌وجو اضافه می‌کند، و پل BIFF این کار را نمی‌کند، چون TXLSRowInfoList از قبل صفر-پایه است. هر دو پل یک شاخص صفحه یا ردیف خارج از بازه معتبر را دیده‌پذیر رفتار می‌کنند، پس یک پرسش خارج-از-محدوده به پاسخ قدیمی شامل-پنهان تنزل می‌کند به‌جای دورریختن داده

برای workbookهای فیلترشده چه چیزی تغییر می‌کند

این مورد چیزی است که تیکت‌های پشتیبانی تولید می‌کند. اعمال یک AutoFilter در HotXLS از راه ApplyAutoFilter معیارهای ستون را ارزیابی می‌کند و هر ردیف داده‌ای که تطبیق نکند را پنهان می‌کند، که دقیقاً همان کاری است که Excel وقتی کاربر یک منوی کشویی فیلتر را کلیک می‌کند انجام می‌دهد. پیش از v2.197.0 آن ردیف‌های پنهان برای کاربر نامرئی و برای موتور محاسبه کاملاً مرئی بودند، پس یک SUBTOTAL(109, ...) سمت سرور مجموع فیلترنشده را گزارش می‌کرد. اکنون همان فراخوانی مجموع فیلترشده را گزارش می‌کند

var
  Book: TXLSXWorkbook;
  Sheet: TXLSXWorksheet;
  VisibleRows: Integer;
begin
  Book := TXLSXWorkbook.Create;
  try
    Book.Open('orders.xlsx');
    Sheet := Book.Sheets[0];

    Sheet.SetAutoFilter('A1:E500');
    Sheet.AddAutoFilterColumn(3, xlsxAfOpGreaterOrEqual, '1000');
    VisibleRows := Sheet.ApplyAutoFilter;   // hides the non-matching rows

    Sheet.Cells[501, 4].Formula := '=SUBTOTAL(109,D2:D500)';
    Book.Recalculate;
    // The cell value now agrees with what Excel shows for the same filter,
    // and VisibleRows tells you how many rows fed into it

    Book.SaveAs('orders-filtered.xlsx');
  finally
    Book.Free;
  end;
end;

پنهان‌سازی دستی به همان شکل کار می‌کند، چون RowHidden[ARow] := True همان وضعیتی است که فیلتر می‌نویسد. آن هم‌ارزی در Excel عمدی است و اکنون در HotXLS نیز برقرار است. یک پیامد ارزش یک یادداشت در هر مستنداتی که با workbookهای تولیدی‌تان ارسال می‌شود را دارد: مجموعی که با کد ۱۰۹ محاسبه شده یک عدد وابسته-به-نما است، پس گیرنده‌ای که فیلتر را پاک می‌کند آن را عوض می‌کند. وقتی یک گزارش باید یک رقم ثابت صرف‌نظر از آنچه خواننده به نما انجام می‌دهد بیان کند، کد ۹ انتخاب درست است و همیشه بوده. فیلترها، اعتبارسنجی و جدول‌ها با هم در مقاله درباره اعتبارسنجی داده، AutoFilter و جدول‌ها پوشش داده شده‌اند. چون پنهان‌کردن ردیف‌ها هیچ فرمولی را لمس نمی‌کند، خودش گراف وابستگی را کثیف نمی‌کند، که ارزش دانستن دارد اگر برای پاسخگویی workbookهای بزرگ به محاسبه‌مجدد افزایشی روی زیرگراف کثیف متکی هستید

کدهای گزینه AGGREGATE و یک محدودیت که هنوز باز است

AGGREGATE همان SUBTOTAL با یک آرگومان خط‌مشی دوم است، و HotXLS آن را در CalcAggregateFunc مدیریت می‌کند. آرگومان گزینه سوییچ‌های مستقل را رمزگذاری می‌کند: آیا فراخوانی‌های تودرتوی SUBTOTAL و AGGREGATE درون بازه رد می‌شوند، آیا مقادیر روی ردیف‌های پنهان رد می‌شوند، و آیا مقادیر خطا سرکوب می‌شوند به‌جای انتشار. HotXLS دروازه مشترک ردیف پنهان را برای کدهای گزینه ۲، ۳، ۶، و ۷ مسلح می‌کند، و مقادیر خطا را برای کدهای گزینه ۴ تا ۷ سرکوب می‌کند. آرگومان شماره‌تابع سپس دقیقاً همان‌طور که SUBTOTAL انجام می‌دهد تجمیع را انتخاب می‌کند، شامل مسیریابی واریانس، انحراف معیار، و ضرب از راه کاهنده‌های خودشان. یک شکاف مستندشده باقی می‌ماند، و بهتر است اینجا گفته شود تا در تولید کشف شود: معنای نادیده‌گرفتن-SUBTOTAL-تودرتو مرتبط با کدهای گزینه پایین در HotXLS پیاده‌سازی نشده‌اند. تشخیص یک SUBTOTAL تودرتو درون یک بازه ارجاع‌شده نیازمند علامت‌گذاری وضعیت بازگشت ارزیاب است تا یک تجمیع درونی بتواند خودش را به بیرونی اعلام کند، که تغییری بزرگ‌تر از دروازه ردیف پنهان است. در عمل، مواجهه کوچک است، چون workbookهای واقعی تقریباً همیشه فرمول‌های SUBTOTAL را بیرون از بازه‌هایی می‌گذارند که فرمول‌های SUBTOTAL دیگر روی آن‌ها تجمیع می‌کنند. اگر تولیدکننده شما واقعاً بازه‌های تجمیع همپوشان می‌سازد، روی کدهای گزینه پایین برای رفع تکرار حساب نکنید

نگهبان تعداد آرگومان که همراهش عرضه شد

نسخه 2.197.0 همچنین یک شکاف اعتبارسنجی را در همان دیسپچر بست، و دلیل طراحی همانی است که فیلد اسکرچ را انگیزه داد: بررسی را جایی بگذار که یک‌بار نوشته شود. حدود ۲۸۰ بدنه تابع درونی هرکدام تعداد آرگومان خودشان را علیه Item.ChildCount بررسی می‌کردند، که هیچ مرز پیوسته‌ای برای مورد آرگومان‌های بیش‌ازحد باقی نمی‌گذاشت. یک فراخوانی مانند =SIN(1,2) به یک بدنه تابع می‌رسید که آرگومان اولش را بررسی می‌کرد، مازاد را نادیده می‌گرفت، و یک عدد باورپذیر برمی‌گرداند جایی که Excel #VALUE! برمی‌گرداند. HotXLS از قبل تعداد آرگومان اعلان‌شده هر تابع درونی را در رجیستری تابعش ذخیره می‌کرد، که به‌صورت THashFunc.ArgsCnt نمایش داده می‌شود با -1 که یک تابع متغیر مانند SUM، IF، یا CONCAT را علامت می‌زند. نسخه 2.197.0 آن را از راه یک ویژگی جدید TXLSFormula.FuncArgsCntByPtg رد کرد و یک دروازه در بالای GetValueItemFunc، دیسپچر اصلی، افزود

lDeclaredArgs := FFormula.FuncArgsCntByPtg[Item.IntValue];
if lDeclaredArgs >= 0 then
begin
  lProvidedArgs := Item.ChildCount - 1;   // Child[0] is the function node
  if lProvidedArgs > lDeclaredArgs then
  begin
    Result := lxErrorValue;               // =SIN(1,2) now yields #VALUE!
    Exit;
  end;
end;

نگهبان آرگومان‌های بیش‌ازحد را رد می‌کند و عمداً درباره کم‌ترازحد چیزی نمی‌گوید. حذف یک آرگومان اختیاری پسین برای VLOOKUP، SUBSTITUTE، و فهرست بلندی از تابع‌های دیگر در Excel قانونی است، پس یک بررسی متقارن فرمول‌های درست را برای گرفتن نادرست می‌شکست. شناسه‌های ناشناخته به‌عنوان متغیر گزارش می‌شوند و از دروازه می‌گذرند، که چیزی است که توابع تعریف‌شده کاربر را از سر راهش نگه می‌دارد؛ اگر توابع خودتان را ثبت می‌کنید، رفتار توصیف‌شده در راهنمای موتور فرمول و توابع سفارشی تحت‌تأثیر قرار نمی‌گیرد. متمرکزکردن مورد کم‌ترازحد یک کار جداگانه است، چون هرکدام از آن ۲۸۰ بدنه معنای کد خطای خودشان را دارند و باید یکی‌یکی بازبینی شوند نه فرض شوند

موتور محاسبه‌ای که در اینجا شرح داده شد، هر دو نمای workbook، و APIهای AutoFilter و دیده‌پذیری ردیف که آن را تغذیه می‌کنند بخشی از مؤلفه صفحه‌گسترده HotXLS Delphi هستند، که با سورس کامل برای Delphi و C++Builder ارائه می‌شود و هیچ نصب Excel روی ماشینی که آن را اجرا می‌کند نیاز ندارد