Lanci poređenja, prazne ćelije i SUMIF u HotXLS Delphi-ju

HotXLS Delphi Component vrednuje =1<2<3 kao FALSE, isti odgovor koji daje Excel 16, jer njegov parser formula od v2.384.3 uvija operatore poređenja s leva na desno: 1<2 postaje TRUE, a TRUE<3 je FALSE jer je boolean iznad svakog broja. Isto izdanje čini prazan operand jednakim i 0 i "", i dozvoljava SUMIF-u da razvuče sumni opseg od jedne ćelije do oblika svog kriterijumskog opsega. Svako od ovoga deluje kao sitnica dok radna sveska izračunata u Delphi-ju ne počne da se raspravlja sa istom sveskom otvorenom u Excelu

Spor obično počinje formulom koju je neko napisao intuicijom. Neko ukuca =0<B2<100 da proveri da li je količina u opsegu, Excel tiho odgovara FALSE za svaki red, i list ode u svet sa tim bugom ispečenim unutra. Računski engine ne sme da ispravlja namenu korisnika; njegov posao je da proizvede vrednost koju bi proizveo Excel, tako da keširani rezultat koji HotXLS upisuje u fajl odgovara onome što Excel prikaže posle preračunavanja. Pre v2.384.3 HotXLS je na tu proveru opsega odgovarao TRUE za svaki red, pogrešno u suprotnom smeru, i izveštaj generisan na serveru bio bi u suprotnosti sa istim izveštajem otvorenim na desktopu

Zašto =1<2<3 vraća FALSE u Excelu?

Excel vraća FALSE jer lanac poređenja čita kao (1<2)<3, a unutrašnji TRUE potom gubi borbu u rangiranju tipova protiv broja 3. Stari HotXLS parser čitao je isti tekst kao 1<(2<3): TXLSSyntax.Parse_expr u lxFormula.pas isparsira jedan operand, ugleda token poređenja i udari u rekurziju Parse_expr-a za desnu stranu, što operator čini desno-asocijativnim. Iz toga ispadne 1<TRUE, a broj je ispod boolean-a, pa je rezultat bio TRUE. Greška je simetrična: =3>2>1 je u Excelu TRUE a u HotXLS-u je bilo FALSE, i =1=1=TRUE je u Excelu TRUE a pre popravke je bilo FALSE. Regresija CalculateFormula_ComparisonChainsFoldLeftToRight prikova sedam takvih formula za vrednosti koje vraća Excel 16 i svaku propušta kroz obe engine arhitekture, klasični TXLSWorkbook i XLSX-nativni TXLSXWorkbook, koristeći Calculate metod opisan u pregledu HotXLS formula engine-a

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');
  // Šta Excel 16 vraća:  FALSE, TRUE, FALSE, TRUE, TRUE, TRUE, TRUE
var
  Classic: IXLSWorkbook;
  Xlsx: TXLSXWorkbook;
  i: Integer;
begin
  Classic := TXLSWorkbook.Create;
  Xlsx := TXLSXWorkbook.Create;
  try
    // TXLSXWorkbook.Calculate vrednuje nad aktivnim listom i
    // vraća Null kada radna sveska uopšte nema list
    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;

Popravka pretvara Parse_expr u petlju istog oblika kakvu Parse_expr1 već koristi za +, - i &. Parsira prvi operand sa Parse_expr1-om, i dok je sledeći token jedan od =, <>, <, >, <= ili >=, pravi čvor poređenja, kači nakupljeni levi rezultat kao prvo dete, parsira sledeći operand sa Parse_expr1-om umesto sa Parse_expr-om i proglašava novi čvor levim rezultatom za sledeću rundu. Dva detalja lako su se pokvarila pri pretvaranju rekurzije u iteraciju, i obojica su u beleškama održavaca: nakupljeni čvor mora se predati (lChild := Item; Item := nil) baš tim redom, a putanja greške mora Exit posle oslobađanja napola sagrađenog čvora, umesto da ispada iz petlje i vraća objeseno stablo

Kako HotXLS rangira brojeve, tekst i booleane u poređenju?

HotXLS rangira mešane tipove kako Excel: svaki broj je manji od svake tekstualne vrednosti, a svaka tekstualna vrednost manja je od svakog boolean-a. TXLSCalculator.CompareVariants u lxCalc.pas klasifikuje oba operanda sa GetRetValueType u enumeraciju TXLSRetValueType = (xlVariantValue, xlNumberValue, xlStringValue, xlBooleanValue), i kada se dve klase razlikuju jednostavno poredi njihove ordinale, pa je redosled deklaracije tog enum-a pravilo za različite tipove. Unutar jedne klase poređenje je prirodno, sa jednim Excelovim specifičnim zavrtanjem za tekst: oba stringa prvo prođu kroz lxUpperCase, pa je ="abc"="ABC" TRUE. To rangiranje je razlog što se o rezultatu lanca ne može rasuđivati bez njega. TRUE<3 nije pretvaranje TRUE u 1, to je boolean poređen sa brojem, i boolean pobeđuje. Datumi su za engine serijski brojevi (varDate klasifikuje se kao xlNumberValue), pa je datum uvek ispod bilo kog teksta, uključujući tekst koji slučajno liči na datum

Čemu se prazna ćelija izjednačava u poređenju?

Prazna ćelija upotrebljena kao operand poređenja jednaka je 0 kada je druga strana broj, jednaka "" kada je druga strana tekst, a od v2.384.53 jednaka je FALSE kada je druga strana logička vrednost, pa uz prazno A1 =A1=0, =A1="" i =A1=FALSE svi su TRUE. TXLSCalculator.CompareVarValues, koji opslužuje svih šest operatora poređenja, supstituiše prazno pre poziva CompareVariants-a: ako je tačno jedan operand Null, on postaje WideString('') kada je partner string, False kada je partner boolean, a inače 0. Dva prazna i dalje se porede kao jednaka međusobno, bez supstitucije. Aritmetička putanja je prazno od uvek pretvarala u 0, pa je =A1+1 davalo 1, ali je CompareVariants držao Null kao sopstveni najniži rang, ispod svakog broja, i operatori poređenja koristili su taj rang direktno

var
  Wb: IXLSWorkbook;
  Sh: TXLSWorksheet;
begin
  Wb := TXLSWorkbook.Create;
  Sh := Wb.Sheets.Add;
  Sh.Range['B1', 'B1'].Value := 5;      // A1 je namerno ostavljeno prazno

  Writeln(VarToStr(Wb.Calculate('=A1=0')));    // True
  Writeln(VarToStr(Wb.Calculate('=A1=""')));   // True
  Writeln(VarToStr(Wb.Calculate('=A1<B1')));   // True: prazno se poredi kao 0
  Writeln(VarToStr(Wb.Calculate('=A1<0')));    // False; True pre v2.384.3
end;

Poslednja linija je ona koja je u praksi bolela. Pod starim rangom, prazno je bilo manje od svakog broja, pa i od negativnih, pa je =IF(A1<0,"overdrawn","ok") svaku praznu ćeliju salda označavao kao prekoračenu, a =A1=0 bilo je FALSE za ćeliju koju bi svaki korisnik opisao kao nulu. Jedna granica ostala je i posle v2.384.3: supstitucija je birala samo između 0 i praznog stringa, pa se prazno, poređeno sa boolean-om, pretvaralo u 0, što je rang ispod i TRUE i FALSE, i =A1=FALSE na praznom A1 vrednovao se kao FALSE. Od HotXLS 2.384.53 prazno poređeno sa logičkom vrednošću tretira se kao FALSE u oba engine-a, XLS i XLSX, kao i u Excelu: uz prazno A1, =A1=FALSE i =A1<TRUE vraćaju TRUE a =A1=TRUE vraća FALSE. To znači i da poređenje ne ume da razlikuje prazno od FALSE, ni u Excelu ni u HotXLS-u; kad list treba tu razliku, testirajte sa ISBLANK ili =A1=""

Zašto je SUMIF sa sumnim opsegom od jedne ćelije vraćao 0?

SUMIF je vraćao 0 jer je HotXLS stegao iteraciju na manji od dva opsega, dok Excel zadržava oblik kriterijumskog opsega a sumni opseg koristi samo za svoj gornji levi ćelijski ugao. =SUMIF(A1:A10,">5",B1) znači dakle B1:B10 u Excelu, pogodnost na koju se oslanjaju mnogi ručno građeni template-i. Zajednički radnik TXLSCalculator.GetValueItemRange2 skraćivao je brojeve redova i kolona na one iz vrednosnog opsega, čime se primer svodio na jedan jedini test A1 protiv B1. v2.384.3 skida stegu: petlja sada prolazi kroz kriterijumski opseg i čita svaku vrednost na istom offsetu od gornjeg levog ugla sumnog opsega. Pošto i CalcSumIF i CalcAverageIF zovu tog radnika, AVERAGEIF dobija isto razvlačenje, a sumni opseg veći od kriterijumskog skraćuje se na oblik kriterijuma iz istog razloga. Kriterijumski argument u sredini je argument vrednosne klase a spoljašnja dva su referentne klase, razlika koju pokriva članak o implicitnom preseku i klasama argumenata

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;          // kriterijumska kolona: 1..10
      Sheet.Cells[Row, 2].Value := Row * 100;    // iznosi: 100..1000
    end;
    Sheet.Cells[1, 4].Formula := '=SUMIF(A1:A10,">5",B1)';     // sumni opseg od jedne ćelije
    Sheet.Cells[2, 4].Formula := '=SUMIF(A1:A10,">5",B1:B10)'; // eksplicitni sumni opseg
    if Book.Recalculate = lxOk then
      // I D1 i D2 su 4000 (600+700+800+900+1000); D1 je bilo 0 pre v2.384.3
      Writeln(VarToStr(Sheet.Cells[1, 4].Value), ' ', VarToStr(Sheet.Cells[2, 4].Value));
  finally
    Book.Free;
  end;
end;

INDIRECT i YEARFRAC: dve tihe popravke

INDIRECT sada poštuje svoj drugi argument, i tekst posle validne reference je greška umesto da se ignoriše. Sa a1 FALSE tekst se parsira kao apsolutni R1C1, pa =INDIRECT("R2C3",FALSE) čita C2; stari kod ignorišao je zastavicu, čitao "R2" kao kolonu R, red 2, i tiho vraćao pogrešnu ćeliju. Zastavica se razrešava prema tipu svog variant-a (boolean, broj ili tekst) jer pretvaranje string variant-a pravo u Double diže izuzetak. Relativni R1C1 tekst poput R[1]C[1] vraća #REF!, jer INDIRECT nema poreklo formula-ćelije prema kome bi ga razrešio, i A1 tekst sa znakovima na kraju, "B2 junk", takođe vraća #REF!. YEARFRAC sa osnovom 0 sada primenjuje NASD pravila o poslednjem danu februara koja je DAYS360 već imao: kada su oba datuma poslednji dan februara krajnji dan postaje 30, a potom početni dan na poslednjem danu februara takođe postaje 30. Od 2024-02-29 do 2025-02-28 broj je sada 360 dana, razlomak tačno 1, dok je prethodni Days360US brojao 359

Šta ove popravke garantuju, a koja je pouka?

Ponašanje lanca poređenja garantuje test koji oba engine-a poredi sa vrednostima izmerenim u Excelu 16, i taj test postoji jer je prvi opis popravke bio pogrešan. Izdanje v2.384.3 prvobitno je u belešci tvrdilo da levo-desno uvijanje čini =1<2<3 TRUE, što je upravo ono što je proizvodio stari desno-asocijativni parser i suprotno od onoga što vraćaju i Excel i novi kod. Niko nije vrednovao primer; napisan je iz intuicije da je „1 manje od 2, što je manje od 3". Beleška je ispravljena, a test sa sedam formula dodat u nastavnom commit-u, i pravilo koje je iz toga izašlo važi za svakog ko dokumentuje semantiku spreadsheet-a: pokrenite primer u Excelu pre nego što zapišete očekivanu vrednost. Supstitucija praznog operanda i SUMIF razvlačenje slede isto Excelovo ponašanje, uključujući slučaj prazno-protiv-boolean-a od v2.384.53, a uslovni agregati koji moraju i da preskoče filtrirane ili skrivene redove slede posebna pravila iz članka o SUBTOTAL i AGGREGATE i skrivenim redovima

HotXLS je nativna Delphi i C++Builder spreadsheet komponenta koja čita, preračunava i upisuje XLS, XLSX, ODS i CSV bez instaliranog Excela, a pravila poređenja, praznog i SUMIF-a opisana ovde žive u računskom engine-u koji dele obe arhitekture radnih sveski. Kompletna lista funkcija i opcije licenciranja su na stranici proizvoda HotXLS Delphi spreadsheet komponenta