Lanțuri de comparație, celule goale și SUMIF în HotXLS

HotXLS Delphi Component evaluează =1<2<3 ca FALSE, același răspuns pe care îl dă Excel 16, pentru că din v2.384.3 parserul de formule pliază operatorii de comparație de la stânga la dreapta: 1<2 devine TRUE, iar TRUE<3 este FALSE pentru că un boolean stă deasupra oricărui număr în ierarhie. Aceeași versiune face un operand gol egal și cu 0, și cu "", și lasă SUMIF să întindă un interval de sumă cu o celulă la forma intervalului său de criterii. Fiecare dintre acestea pare o curiozitate până când un registru de lucru calculat în Delphi contrazice același registru deschis în Excel

Neînțelegerea pornește de obicei de la o formulă scrisă din intuiție. Cineva tastează =0<B2<100 ca să verifice că o cantitate e în interval, Excel răspunde în tăcere FALSE pentru fiecare rând, iar foaia pleacă cu bug-ul acesta copt în ea. Un motor de calcul nu are voie să repare intenția utilizatorului; treaba lui este să producă valoarea pe care ar produce-o Excel, astfel încât rezultatul din cache pe care HotXLS îl scrie în fișier să se potrivească cu ce arată Excel după un recalcul. Înainte de v2.384.3, HotXLS răspundea TRUE pentru acea verificare de interval pe fiecare rând, greșit în direcția opusă, și un raport generat pe un server contrazicea același raport deschis pe un desktop

De ce =1<2<3 întoarce FALSE în Excel?

Excel întoarce FALSE pentru că citește un lanț de comparații ca (1<2)<3, iar TRUE-ul interior pierde apoi competiția de ierarhizare de tip contra numărul 3. Vechiul parser HotXLS citea același text ca 1<(2<3): TXLSSyntax.Parse_expr din lxFormula.pas parsea un operand, vedea un token de comparație și recursa în Parse_expr pentru partea dreaptă, ceea ce face operatorul drept-asociativ. Asta dădea 1<TRUE, iar un număr e sub un boolean, deci rezultatul era TRUE. Greșeala e simetrică: =3>2>1 este TRUE în Excel și era FALSE în HotXLS, iar =1=1=TRUE este TRUE în Excel și era FALSE înainte de reparare. Regresia CalculateFormula_ComparisonChainsFoldLeftToRight fixează șapte astfel de formule față de valorile pe care le întoarce Excel 16 și le trece pe fiecare prin ambele arhitecturi de motor, TXLSWorkbook-ul clasic și TXLSXWorkbook-ul nativ XLSX, folosind metoda Calculate descrisă în prezentarea motorului de formule 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');
  // Ce întoarce Excel 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 evaluează față de foaia activă și
    // întoarce Null când registrul de lucru nu are deloc o foaie
    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;

Repararea transformă Parse_expr într-o buclă de aceeași formă pe care o folosea deja Parse_expr1 pentru +, - și &. Parsează primul operand cu Parse_expr1, iar cât timp următorul token este unul dintre =, <>, <, >, <= sau >=, creează un nod de comparație, atașează rezultatul stâng acumulat ca prim copil, parsează următorul operand cu Parse_expr1 în loc de Parse_expr și face nodul nou rezultatul stâng pentru runda următoare. Două detalii erau ușor de greșit la conversia recursivității în iterație, și ambele sunt în notele maintainer-ilor: nodul acumulat trebuie predat în ordinea (lChild := Item; Item := nil), iar calea de eroare trebuie să facă Exit după eliberarea nodului pe jumătate construit, nu să cadă din buclă și să întoarcă un arbore atârnat

Cum ierarhizează HotXLS numerele, textul și booleenii într-o comparație?

HotXLS ierarhizează tipurile mixte așa cum face Excel: orice număr e mai mic decât orice valoare text, iar orice valoare text e mai mică decât orice boolean. TXLSCalculator.CompareVariants din lxCalc.pas clasifică ambii operanzi cu GetRetValueType în enumerarea TXLSRetValueType = (xlVariantValue, xlNumberValue, xlStringValue, xlBooleanValue), iar când cele două clase diferă pur și simplu îi compară după ordinal, deci ordinea de declarație a enum-ului aceluia este regula între tipuri. Într-o singură clasă comparația e cea naturală, cu o răsucire specifică Excel pentru text: ambele șiruri trec prin lxUpperCase mai întâi, deci ="abc"="ABC" este TRUE. Această ierarhie e motivul pentru care rezultatul lanțului nu poate fi raționat fără ea. TRUE<3 nu este o coerciție a lui TRUE la 1, ci un boolean comparat cu un număr, iar booleanul câștigă. Datele calendaristice sunt numere seriale pentru motor (varDate se clasifică xlNumberValue), deci o dată e mereu sub orice text, inclusiv sub textul care întâmplător arată ca o dată

La ce este egală o celulă goală într-o comparație?

O celulă goală folosită ca operand de comparație este egală cu 0 când partea cealaltă e număr, egală cu "" când partea cealaltă e text, iar din v2.384.53 egală cu FALSE când partea cealaltă e valoare logică, astfel încât cu A1 gol, =A1=0, =A1="" și =A1=FALSE sunt toate TRUE. TXLSCalculator.CompareVarValues, care deservește toți cei șase operatori de comparație, substituie golul înainte să apeleze CompareVariants: dacă exact un operand este Null, acesta devine WideString('') când partenerul e șir, False când partenerul e boolean și 0 în rest. Două goluri se compară tot egal între ele fără substituție. Calea aritmetică transforma întotdeauna golul în 0, motiv pentru care =A1+1 dădea 1, dar CompareVariants ținea Null ca propriul rang cel mai de jos, sub orice număr, iar operatorii de comparație foloseau direct rangul acela

var
  Wb: IXLSWorkbook;
  Sh: TXLSWorksheet;
begin
  Wb := TXLSWorkbook.Create;
  Sh := Wb.Sheets.Add;
  Sh.Range['B1', 'B1'].Value := 5;      // A1 e lăsat gol intenționat

  Writeln(VarToStr(Wb.Calculate('=A1=0')));    // True
  Writeln(VarToStr(Wb.Calculate('=A1=""')));   // True
  Writeln(VarToStr(Wb.Calculate('=A1<B1')));   // True: golul se compară ca 0
  Writeln(VarToStr(Wb.Calculate('=A1<0')));    // False; True înainte de v2.384.3
end;

Ultima linie e cea care a durut în practică. Sub vechiul rang, un gol era mai mic decât orice număr, inclusiv cele negative, deci =IF(A1<0,"overdrawn","ok") eticheta fiecare celulă de sold goală ca descoperită, iar =A1=0 era FALSE pentru o celulă pe care orice utilizator ar fi descris-o ca zero. O graniță a rămas și după v2.384.3: substituția alegea doar între 0 și șirul vid, deci un gol comparat cu un boolean devenea 0, care stă sub TRUE și sub FALSE, iar =A1=FALSE pe un A1 vid evalua FALSE. Din HotXLS 2.384.53, un gol comparat cu o valoare logică este tratat ca FALSE în ambele motoare, XLS și XLSX, ca în Excel: cu A1 vid, =A1=FALSE și =A1<TRUE întorc TRUE, iar =A1=TRUE întoarce FALSE. Asta înseamnă și că comparația nu poate deosebi golul de FALSE, nici în Excel, nici în HotXLS; când foaia are nevoie de distincția asta, testați cu ISBLANK sau =A1=""

De ce SUMIF cu un interval de sumă de o celulă întorcea 0?

SUMIF întorcea 0 pentru că HotXLS restrângea iterația la cel mai mic dintre cele două intervale, în timp ce Excel păstrează forma intervalului de criterii și folosește intervalul de sumă doar pentru celula din colțul stânga-sus. =SUMIF(A1:A10,">5",B1) înseamnă deci B1:B10 în Excel, o comoditate de care se agață multe șabloane construite de mână. Lucrătorul comun TXLSCalculator.GetValueItemRange2 își micșora numărul de rânduri și de coloane la cele ale intervalului de valori, ceea ce reducea exemplul la un singur test al lui A1 contra B1. v2.384.3 elimină restricția: bucla parcurge acum intervalul de criterii și citește fiecare valoare la același offset din colțul stânga-sus al intervalului de sumă. Pentru că CalcSumIF și CalcAverageIF apelează ambele acel lucrător, AVERAGEIF primește aceeași redimensionare, iar un interval de sumă mai mare decât intervalul de criterii este trunchiat la forma criteriilor din același motiv. Argumentul de criterii din mijloc este de clasă valoare, iar cele două exterioare sunt de clasă referință, distincția descrisă în articolul despre intersecția implicită și clasele de argumente

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;          // coloana de criterii: 1..10
      Sheet.Cells[Row, 2].Value := Row * 100;    // sume: 100..1000
    end;
    Sheet.Cells[1, 4].Formula := '=SUMIF(A1:A10,">5",B1)';     // interval de sumă de o celulă
    Sheet.Cells[2, 4].Formula := '=SUMIF(A1:A10,">5",B1:B10)'; // interval de sumă explicit
    if Book.Recalculate = lxOk then
      // Atât D1, cât și D2 sunt 4000 (600+700+800+900+1000); D1 era 0 înainte de v2.384.3
      Writeln(VarToStr(Sheet.Cells[1, 4].Value), ' ', VarToStr(Sheet.Cells[2, 4].Value));
  finally
    Book.Free;
  end;
end;

INDIRECT și YEARFRAC: două corecții mai liniștite

INDIRECT onorează acum al doilea argument, iar textul după o referință validă este o eroare în loc să fie ignorat. Cu a1 FALSE, textul e parsat ca R1C1 absolut, deci =INDIRECT("R2C3",FALSE) citește C2; vechiul cod ignora fanionul, citea "R2" ca coloana R, rândul 2, și întorcea în tăcere celula greșită. Fanionul este dispecerizat după tipul Variant al lui (boolean, număr sau text) pentru că conversia unui Variant șir direct la Double ridică o excepție. Textul R1C1 relativ precum R[1]C[1] întoarce #REF!, întrucât INDIRECT nu are originea unei celule de formulă față de care să-l rezolve, iar textul A1 cu caractere în plus, "B2 junk", întoarce și el #REF!. YEARFRAC cu baza 0 aplică acum regulile NASD pentru ultima zi din februarie pe care DAYS360 le implementase deja: când ambele date sunt ultima zi din februarie, ziua finală devine 30, apoi un început pe ultima zi din februarie devine 30. De la 2024-02-29 la 2025-02-28 numărarea este acum de 360 de zile, o fracție de exact 1, acolo unde vechiul Days360US număra 359

Ce garantează aceste reparări și care a fost lecția?

Comportamentul lanțurilor de comparație este garantat de un test care compară ambele motoare cu valori măsurate în Excel 16, iar testul există pentru că prima descriere a reparării era greșită. Nota de versiune v2.384.3 spunea inițial că plierea de la stânga la dreapta făcea =1<2<3 TRUE, ceea ce este exact ce producea vechiul parser drept-asociativ și opusul a ce întorc atât Excel, cât și noul cod. Nimeni nu evaluase exemplul; fusese scris din intuiția că „1 e mai mic decât 2, care e mai mic decât 3". Nota a fost corectată, iar testul cu șapte formule a fost adăugat într-un commit ulterior, iar regula care a ieșit de aici se aplică oricui documentează semantică de spreadsheet: rulați exemplul în Excel înainte să notați valoarea așteptată. Substituția operandului gol și redimensionarea SUMIF urmează același comportament Excel, inclusiv cazul gol-față-de-boolean din v2.384.53, iar agregatele condiționale care trebuie totodată să sară rândurile filtrate sau ascunse urmează regulile separate din articolul despre SUBTOTAL și AGGREGATE și rândurile ascunse

HotXLS este o componentă de spreadsheet nativă pentru Delphi și C++Builder care citește, recalculează și scrie XLS, XLSX, ODS și CSV fără Excel instalat, iar regulile de comparație, de gol și de SUMIF descrise aici trăiesc în motorul de calcul pe care îl partajează ambele arhitecturi de registru de lucru. Lista completă de funcții și opțiunile de licențiere sunt pe pagina de produs a componentei de spreadsheet HotXLS pentru Delphi