زنجیره‌های مقایسه، سلول‌های خالی و SUMIF در HotXLS دلفی

HotXLS Delphi Component عبارت =1<2<3 را FALSE ارزیابی می‌کند، همان جوابی که اکسل 16 می‌دهد، چون پارسر فرمول آن از v2.384.3 به بعد عملگرهای مقایسه را چپ به راست تا می‌زند: 1<2 می‌شود TRUE و TRUE<3 می‌شود FALSE چون یک boolean از هر عددی رتبه بالاتری دارد. همان انتشار یک عملوند خالی را هم با هر دوی 0 و "" برابر می‌کند، و به SUMIF اجازه می‌دهد یک محدودهٔ جمع تک‌سلولی را به شکل محدودهٔ معیارش کش بدهد. هر کدام از این‌ها تا وقتی کتاب کاری محاسبه‌شده در دلفی با همان کتاب کاری باز شده در اکسل هم‌عقیده نشود، بی‌اهمیتی به نظر می‌رسد

این ناهماهنگی معمولاً از فرمولی شروع می‌شود که یکی از سر شهود نوشته. یکی =0<B2<100 را تایپ می‌کند تا بررسی کند مقدار در بازه است، اکسل بی‌سروصدا برای هر ردیف FALSE جواب می‌دهد، و شیت با همان باگِ پخته در دلش راهی می‌شود. یک موتور محاسباتی حق ندارد نیت کاربر را اصلاح کند؛ کارش تولید همان مقداری است که اکسل تولید می‌کرد، تا نتیجهٔ کش‌شده‌ای که HotXLS در فایل می‌نویسد با چیزی که اکسل بعد از recalculation نشان می‌دهد یکی باشد. پیش از v2.384.3، HotXLS برای همان بررسی بازه در هر ردیف TRUE جواب می‌داد، غلط در جهت مخالف، و گزارشی که روی سرور تولید می‌شد با همان گزارشی که روی دسکتاپ باز می‌شد تناقض داشت

چرا =1<2<3 در اکسل FALSE برمی‌گرداند؟

اکسل FALSE برمی‌گرداند چون زنجیرهٔ مقایسه‌ها را مثل (1<2)<3 می‌خواند و آن TRUE درونی بعد در مسابقهٔ رتبه‌بندی نوع‌ها در برابر عدد 3 می‌بازد. پارسر قدیمی HotXLS همان متن را مثل 1<(2<3) می‌خواند: TXLSSyntax.Parse_expr در lxFormula.pas یک عملوند را parse می‌کرد، توکن مقایسه‌ای می‌دید و برای سمت راست به‌صورت بازگشتی وارد Parse_expr می‌شد، که عملگر را راست‌شرطی می‌کند. حاصل 1<TRUE می‌شد، عدد پایین‌تر از boolean است، پس نتیجه TRUE بود. اشتباه متقارن بود: =3>2>1 در اکسل TRUE است و در HotXLS FALSE می‌شد، و =1=1=TRUE در اکسل TRUE است و پیش از fix می‌شد FALSE. آزمون بازگشتی CalculateFormula_ComparisonChainsFoldLeftToRight هفت فرمول از این جنس را به مقادیر برگشتی اکسل 16 میخکوب می‌کند و هر کدام را از هر دو معماری موتور عبور می‌دهد، یعنی TXLSWorkbook کلاسیک و TXLSXWorkbook بومی XLSX، از طریق متد Calculate که در مرور موتور فرمول HotXLS توضیح داده شده

const
  Formulas: array [0..6] of string = ('=1<2<3', '=3>2>1', '=(1<2)<3',
    '=1<(2<3)', '=1=1=TRUE', '=3>2>1=TRUE', '=1<2<3=FALSE');
  // آنچه اکسل 16 برمی‌گرداند:  FALSE, TRUE, FALSE, TRUE, TRUE, TRUE, TRUE
var
  Classic: IXLSWorkbook;
  Xlsx: TXLSXWorkbook;
  i: Integer;
begin
  Classic := TXLSWorkbook.Create;
  Xlsx := TXLSXWorkbook.Create;
  try
    // TXLSXWorkbook.Calculate نسبت به شیت فعال ارزیابی می‌کند
    // و وقتی کتاب کاری اصلاً شیت ندارد Null برمی‌گرداند
    Xlsx.Sheets.Add('Data');
    for i := 0 to High(Formulas) do
      Writeln(Formulas[i], '  classic=', VarToStr(Classic.Calculate(Formulas[i])),
        '  xlsx=', VarToStr(Xlsx.Calculate(Formulas[i])));
  finally
    Xlsx.Free;
  end;
end;

درخت‌های parse در HotXLS برای =1<2<3 که در آن Parse_expr قدیمی راست‌شرطی عبارت 1<(2<3) را TRUE ارزیابی می‌کرد در حالی که فولدر چپ‌به‌راست از v2.384.3 عبارت (1<2)<3 را FALSE ارزیابی می‌کند؛ حکم را رتبه‌بندی CompareVariants می‌دهد که هر عددی را زیر متن و متن را زیر boolean می‌نشاند، همان قاعده در lxCalc.pas — هر دو موتور حالا زنجیره‌های مقایسه را چپ به راست تا می‌زنند و هفت فرمول را به اکسل 16 میخکوب می‌کنند — یک boolean از هر عددی رتبه بالاتر دارد، پس بازنده بودن TRUE در برابر 3 دقیقاً همان چیزی است که بررسی بازهٔ زنجیره‌شده را FALSE می‌کند

fix آن است که Parse_expr به یک حلقه با همان شکلی تبدیل شود که Parse_expr1 از قبل برای +، - و & به کار می‌برد. اولین عملوند را با Parse_expr1 parse می‌کند، و تا وقتی توکن بعدی یکی از =، <>، <، >، <= یا >= است، یک گره مقایسه می‌سازد، نتیجهٔ انباشتهٔ سمت چپ را به‌عنوان فرزند اول به آن می‌بندد، عملوند بعدی را با Parse_expr1 بازمی‌خواند نه با Parse_expr، و گره جدید را نتیجهٔ چپ دور بعد می‌کند. دو جزئیات موقع تبدیل بازگشت به حلقه راحت گول می‌زنند و هر دو در یادداشت نگه‌دارنده‌ها آمده‌اند: گرهٔ انباشته باید به همان ترتیب (lChild := Item; Item := nil) تحویل داده شود، و مسیر خطا بعد از آزاد کردن گرهٔ نیمه‌ساخته باید Exit بزند نه اینکه از حلقه بیفتد بیرون و یک درخت آویزان برگرداند

HotXLS در یک مقایسه اعداد، متن و booleanها را چطور رتبه‌بندی می‌کند؟

HotXLS نوع‌های مخلوط را مثل اکسل رتبه‌بندی می‌کند: هر عددی از هر مقدار متنی کوچک‌تر است و هر مقدار متنی از هر boolean کوچک‌تر. TXLSCalculator.CompareVariants در lxCalc.pas هر دو عملوند را با GetRetValueType به شمارش TXLSRetValueType = (xlVariantValue, xlNumberValue, xlStringValue, xlBooleanValue) طبقه‌بندی می‌کند و وقتی دو طبقه فرق دارند صرفاً ordinalهایشان را مقایسه می‌کند، پس ترتیب اعلام همان enum قاعدهٔ بین‌نوعی است. درون یک طبقه مقایسه طبیعی است، با یک پیچ خواص اکسل برای متن: هر دو رشته اول از lxUpperCase می‌گذرند، پس ="abc"="ABC" برابر TRUE است. همین رتبه‌بندی است که نتیجهٔ زنجیره را بدون آن نمی‌شود درآورد. TRUE<3 تبدیل TRUE به 1 نیست، بلکه مقایسهٔ یک boolean با یک عدد است و boolean می‌برد. تاریخ‌ها برای موتور شماره سریال‌اند (varDate به‌عنوان xlNumberValue طبقه‌بندی می‌شود)، پس یک تاریخ همیشه زیر هر متنی است، حتی متنی که اتفاقاً شبیه تاریخ به نظر می‌رسد

یک سلول خالی در مقایسه با چه چیزی برابر است؟

سلول خالی که به‌عنوان عملوند مقایسه به کار رود وقتی آن‌طرف عدد است با 0 برابر است، وقتی آن‌طرف متن است با "" برابر است، و از v2.384.53 وقتی آن‌طرف مقدار منطقی است با FALSE برابر است، پس با A1 خالی هر سهٔ =A1=0، =A1="" و =A1=FALSE برابر TRUE هستند. TXLSCalculator.CompareVarValues که به هر شش عملگر مقایسه سرویس می‌دهد، پیش از فراخوانی CompareVariants خالی را جایگزین می‌کند: اگر دقیقاً یک عملوند Null باشد، وقتی جفتش رشته است به WideString('') تبدیل می‌شود، وقتی جفتش boolean است به False، و در غیر این صورت 0. دو خالی همچنان بدون جایگزینی با هم برابر مقایسه می‌شوند. مسیر محاسباتی از همیشه خالی را 0 می‌کرد، برای همین =A1+1 جواب 1 می‌داد، اما CompareVariants Null را به‌عنوان پایین‌ترین رتبهٔ مستقل خودش نگه می‌دارد، زیر همهٔ اعداد، و عملگرهای مقایسه همان رتبه را مستقیم استفاده می‌کردند

var
  Wb: IXLSWorkbook;
  Sh: TXLSWorksheet;
begin
  Wb := TXLSWorkbook.Create;
  Sh := Wb.Sheets.Add;
  Sh.Range['B1', 'B1'].Value := 5;      // A1 عمداً خالی گذاشته شده

  Writeln(VarToStr(Wb.Calculate('=A1=0')));    // True
  Writeln(VarToStr(Wb.Calculate('=A1=""')));   // True
  Writeln(VarToStr(Wb.Calculate('=A1<B1')));   // True: خالی مثل 0 مقایسه می‌شود
  Writeln(VarToStr(Wb.Calculate('=A1<0')));    // False؛ پیش از v2.384.3 مقدار True بود
end;

جایگزینی نوع عملوند مقابل را می‌گیرد: 0، رشتهٔ خالی و از v2.384.53 به بعد FALSE — آن IF که هر موجودی خالی را بدهکار برچسب می‌زد رتبه‌بندی قدیمی Null بود، نه دادهٔ شما

خط آخر همان است که در عمل دردآور شد. زیر رتبهٔ قدیمی، خالی از هر عددی کوچک‌تر بود، منفی‌ها هم شامل می‌شد، پس =IF(A1<0,"overdrawn","ok") هر سلول موجودی خالی را بدهکار برچسب می‌زد و =A1=0 برای سلولی که هر کاربری صفرش می‌دانست FALSE می‌شد. بعد از v2.384.3 هم یک مرز باقی ماند: جایگزینی فقط بین 0 و رشتهٔ خالی انتخاب می‌کرد، پس خالیِ مقایسه‌شده با boolean به 0 تبدیل می‌شد که زیر هر دوی TRUE و FALSE رتبه دارد، و =A1=FALSE روی A1 خالی جواب FALSE می‌داد. از HotXLS 2.384.53 خالیِ مقایسه‌شده با مقدار منطقی در هر دو موتور XLS و XLSX مثل اکسل FALSE گرفته می‌شود: با A1 خالی، =A1=FALSE و =A1<TRUE جواب TRUE و =A1=TRUE جواب FALSE می‌دهند. این یعنی مقایسه نمی‌تواند خالی را از FALSE تشخیص دهد، چه در اکسل چه در HotXLS؛ وقتی شیت به این تمایز نیاز دارد، با ISBLANK یا =A1="" آزمایش کنید

چرا SUMIF با محدودهٔ جمع تک‌سلولی صفر برمی‌گرداند؟

SUMIF صفر برمی‌گرداند چون HotXLS حلقه را به کوچک‌تر از دو محدوده میخکوب می‌کرد، در حالی که اکسل شکل محدودهٔ معیار را نگه می‌دارد و از محدودهٔ جمع فقط سلول بالا-چپش را به عاریه می‌گیرد. پس =SUMIF(A1:A10,">5",B1) در اکسل یعنی B1:B10، راحتی‌ای که خیلی از قالب‌های دست‌ساز به آن تکیه دارند. کارگر مشترک TXLSCalculator.GetValueItemRange2 تعداد سطر و ستونش را به اندازهٔ محدودهٔ مقادیر کوچک می‌کرد، که مثال را به یک آزمون واحد از A1 در برابر B1 تنزل می‌داد. v2.384.3 این محدود را برمی‌دارد: حلقه حالا محدودهٔ معیار را می‌گردد و هر مقدار را با همان آفست از گوشهٔ بالا-چپ محدودهٔ جمع می‌خواند. چون CalcSumIF و CalcAverageIF هر دو از همان کارگر صدا می‌زنند، AVERAGEIF هم همان تغییر اندازه را می‌گیرد، و محدودهٔ جمع بزرگ‌تر از محدودهٔ معیار به همان دلیل به شکل معیار بریده می‌شود. آرگومان معیار در میان از کلاس مقدار است و دو آرگومان بیرونی از کلاس ارجاع؛ همان تمایزی که در مقالهٔ تلاقی ضمنی و کلاس‌های آرگومان پوشش داده شده

var
  Book: TXLSXWorkbook;
  Sheet: TXLSXWorksheet;
  Row: Integer;
begin
  Book := TXLSXWorkbook.Create;
  try
    Sheet := Book.Sheets.Add('Sales');
    for Row := 1 to 10 do
    begin
      Sheet.Cells[Row, 1].Value := Row;          // ستون معیار: 1..10
      Sheet.Cells[Row, 2].Value := Row * 100;    // مبالغ: 100..1000
    end;
    Sheet.Cells[1, 4].Formula := '=SUMIF(A1:A10,">5",B1)';     // محدودهٔ جمع تک‌سلولی
    Sheet.Cells[2, 4].Formula := '=SUMIF(A1:A10,">5",B1:B10)'; // محدودهٔ جمع صریح
    if Book.Recalculate = lxOk then
      // هر دو D1 و D2 برابر 4000 هستند (600+700+800+900+1000)؛ D1 پیش از v2.384.3 صفر بود
      Writeln(VarToStr(Sheet.Cells[1, 4].Value), ' ', VarToStr(Sheet.Cells[2, 4].Value));
  finally
    Book.Free;
  end;
end;

تغییر اندازهٔ SUMIF و AVERAGEIF در HotXLS که در آن =SUMIF(A1:A10,">5",B1) محدودهٔ معیار ده‌ردیفی را می‌گردد و B1 تا B10 را با آفست‌های متناظر از طریق کارگر CalcSumIF می‌خواند تا نتیجهٔ 4000 بدهد، به‌جای محدود شدن به محدودهٔ جمع تک‌سلولی که پیش از v2.384.3 صفر برمی‌گرداند — اکسل فقط گوشهٔ بالا-چپ محدودهٔ جمع را قرض می‌گیرد و شکل معیار را نگه می‌دارد، پس قالب دست‌سازی که B1 پاس می‌دهد یعنی B1:B10 — کارگر مشترک حالا هر ده آفست را می‌گردد و محدودهٔ بیش از اندازه بزرگ را به همان شکل کوتاه می‌کند

INDIRECT و YEARFRAC: دو اصلاح کم‌سروصدا

INDIRECT حالا آرگومان دومش را محترم می‌شمارد و متن بعد از یک ارجاع معتبر خطا است نه اینکه نادیده گرفته شود. با a1 برابر FALSE، متن به‌صورت R1C1 مطلق parse می‌شود، پس =INDIRECT("R2C3",FALSE) همان C2 را می‌خواند؛ کد قدیمی فلگ را نادیده می‌گرفت، «R2» را ستون R و سطر 2 می‌خواند و بی‌سروصدا سلول غلط را برمی‌گرداند. فلگ بر اساس نوع variant آن dispatch می‌شود (boolean، عدد یا متن)، چون تبدیل مستقیم یک variant رشته‌ای به Double exception می‌دهد. متن R1C1 نسبی مثل R[1]C[1] جواب #REF! می‌دهد، چون INDIRECT مبدأ سلول‌فرمولی برای تفسیرش ندارد، و متن A1 با کاراکترهای دنباله‌دار مثل "B2 junk" هم جواب #REF! می‌دهد. YEARFRAC با basis برابر 0 حالا قواعد آخر-فوریهٔ NASD را اعمال می‌کند که DAYS360 از قبل پیاده کرده بود: وقتی هر دو تاریخ آخرین روز فوریه‌اند روزِ پایان 30 می‌شود، بعد اگر شروع آخرین روز فوریه باشد 30 می‌شود. از 2024-02-29 تا 2025-02-28 شمارش حالا 360 روز است، یعنی کسری دقیقاً برابر 1، جایی که Days360US قبلی 359 می‌شمرد

این fixها چه چیزی را تضمین می‌کنند و درس ماجرا چه بود؟

رفتار زنجیرهٔ مقایسه با آزمونی تضمین می‌شود که هر دو موتور را با مقادیر اندازه‌گیری‌شده در اکسل 16 مقایسه می‌کند، و آن آزمون به این دلیل وجود دارد که اولین توصیف fix غلط بود. یادداشت انتشار v2.384.3 اول می‌گفت فولد چپ‌به‌راست =1<2<3 را TRUE می‌کند، که دقیقاً همان خروجی پارسر قدیمی راست‌شرطی است و برعکسِ چیزی که هم اکسل هم کد جدید برمی‌گردانند. هیچ‌کس مثال را ارزیابی نکرده بود؛ از روی شهودِ «1 از 2 کوچک‌تر است، 2 از 3 کوچک‌تر است» نوشته شده بود. یادداشت اصلاح شد و آزمون هفت‌فرمولی در یک commit بعدی اضافه شد، و قاعده‌ای که از دلش بیرون آمد برای هر کسی که معناشناسی صفحه‌گسترده را مستند می‌کند صادق است: قبل از نوشتن مقدار مورد انتظار، مثال را در اکسل اجرا کنید. جایگزینی عملوند خالی و تغییر اندازهٔ SUMIF هم همان رفتار اکسل را دنبال می‌کنند، از جمله حالت خالی-در-برابر-boolean از v2.384.53، و تجمیع‌های شرطی که باید ردیف‌های فیلترشده یا مخفی را هم رد کنند از قواعد جداگانهٔ مقالهٔ ردیف‌های مخفی SUBTOTAL و AGGREGATE پیروی می‌کنند

HotXLS یک کامپوننت صفحه‌گستردهٔ بومی دلفی و C++Builder است که بدون نصب اکسل، XLS، XLSX، ODS و CSV را می‌خواند، دوباره محاسبه می‌کند و می‌نویسد، و قواعد مقایسه، خالی و SUMIF که اینجا توضیح داده شد در موتور محاسباتی مشترک هر دو معماری کتاب کاری زندگی می‌کنند. فهرست کامل توابع و گزینه‌های لایسنس در صفحهٔ محصول کامپوننت صفحه‌گستردهٔ دلفی HotXLS است