Sammenligningskæder, tomme celler og SUMIF i HotXLS Delphi

HotXLS Delphi Component evaluerer =1<2<3 som FALSE, samme svar som Excel 16 giver, for siden v2.384.3 folder dens formelparser sammenligningsoperatorer fra venstre mod højre: 1<2 bliver TRUE, og TRUE<3 er FALSE, fordi en boolean rangerer over ethvert tal. Samme release gør en tom operand lig både 0 og "" og lader SUMIF strække et encelle-sumområde til kriterieområdets form. Hvert af punkterne ligner trivia, til en arbejdsbog beregnet i Delphi er uenig med samme arbejdsbog åbnet i Excel

Uenigheden starter normalt med en formel, et menneske har skrevet ud fra intuition. Nogen taster =0<B2<100 for at tjekke, at en mængde er inden for intervallet, Excel svarer stille FALSE på hver række, og arket skibes med den bug indbagt. En beregningsmotor må ikke rette brugerens hensigt; dens job er at levere den værdi, Excel ville levere, så det cachete resultat, HotXLS skriver i filen, matcher, hvad Excel viser efter en rekalkulation. Før v2.384.3 svarede HotXLS TRUE på det intervaltjek på hver række, forkert i den modsatte retning, og en rapport genereret på en server ville modsiges af samme rapport åbnet på en desktop

Hvorfor returnerer =1<2<3 FALSE i Excel?

Excel returnerer FALSE, fordi den læser en kæde af sammenligninger som (1<2)<3, og den indre TRUE så taber type-rangeringskonkurrencen mod tallet 3. Den gamle HotXLS-parser læste samme tekst som 1<(2<3): TXLSSyntax.Parse_expr i lxFormula.pas parsede én operand, så en sammenligningstoken og rekursede ind i Parse_expr for højresiden, hvilket gør operatoren right-associative. Det giver 1<TRUE, og et tal er under en boolean, så resultatet var TRUE. Fejlen er symmetrisk: =3>2>1 er TRUE i Excel og var FALSE i HotXLS, og =1=1=TRUE er TRUE i Excel og var FALSE før fixen. Regressionstesten CalculateFormula_ComparisonChainsFoldLeftToRight fastlåser syv sådanne formler mod de værdier, Excel 16 returnerer, og kører hver eneste gennem begge motorarkitekturer, classic TXLSWorkbook og XLSX-native TXLSXWorkbook, via Calculate-metoden beskrevet i the HotXLS formula engine overview

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');
  // Hvad Excel 16 returnerer:  FALSE, TRUE, FALSE, TRUE, TRUE, TRUE, TRUE
var
  Classic: IXLSWorkbook;
  Xlsx: TXLSXWorkbook;
  i: Integer;
begin
  Classic := TXLSWorkbook.Create;
  Xlsx := TXLSXWorkbook.Create;
  try
    // TXLSXWorkbook.Calculate evaluerer mod det aktive ark og
    // returnerer Null, når arbejdsbogen slet ikke har et ark
    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;

Fixet gør Parse_expr til en løkke af samme form, som Parse_expr1 allerede bruger til +, - og &. Den parser den første operand med Parse_expr1, og mens næste token er en af =, <>, <, >, <= eller >=, opretter den en sammenligningsnode, hæfter det akkumulerede venstre resultat som første barn, parser næste operand med Parse_expr1 frem for Parse_expr og gør den nye node til venstre resultat i næste runde. To detaljer var lette at fikse forkert, da rekursionen blev lavet om til iteration, og begge står i vedligeholdernes noter: den akkumulerede node skal overdrages i rækkefølgen (lChild := Item; Item := nil), og fejlvejen skal Exit efter at have frigjort den halvbyggede node frem for at falde ud af løkken og returnere en hængende graf

Hvordan rangerer HotXLS tal, tekst og booleans i en sammenligning?

HotXLS rangerer blandede typer, som Excel gør: ethvert tal er mindre end enhver tekstværdi, og enhver tekstværdi er mindre end enhver boolean. TXLSCalculator.CompareVariants i lxCalc.pas klassificerer begge operander med GetRetValueType i enumereringen TXLSRetValueType = (xlVariantValue, xlNumberValue, xlStringValue, xlBooleanValue), og når de to klasser adskiller sig, sammenligner den blot deres ordinaler, så deklarationsrækkefølgen af den enum er tværtype-reglen. Inden for én klasse er sammenligningen den naturlige, med ét Excel-specifikt twist for tekst: begge strenge passerer lxUpperCase først, så ="abc"="ABC" er TRUE. Denne rangering er grunden til, at kæderesultatet ikke kan ræsonneres ud uden den. TRUE<3 er ikke en coercion af TRUE til 1, det er en boolean sammenlignet med et tal, og boolean vinder. Datoer er serienumre for motoren (varDate klassificeres som xlNumberValue), så en dato er altid under enhver tekst, inklusive tekst, der tilfældigvis ligner en dato

Hvad er en tom celce lig i en sammenligning?

En tom celle brugt som sammenligningsoperand er lig 0, når modparten er et tal, lig "", når modparten er tekst, og siden v2.384.53 lig FALSE, når modparten er en logisk værdi, så med A1 tom er =A1=0, =A1="" og =A1=FALSE alle TRUE. TXLSCalculator.CompareVarValues, som betjener alle seks sammenligningsoperatorer, substituerer den tomme, før den kalder CompareVariants: er præcis én operand Null, bliver den WideString(''), når partneren er en streng, False, når partneren er en boolean, og 0 ellers. To tomme celler sammenlignes stadig lig hinanden uden substitution. Aritmetikvejen havde altid gjort en tom celle til 0, hvilket er derfor, =A1+1 gav 1, men CompareVariants holdt Null som sin egen laveste rang under ethvert tal, og sammenligningsoperatorerne brugte den rang direkte

var
  Wb: IXLSWorkbook;
  Sh: TXLSWorksheet;
begin
  Wb := TXLSWorkbook.Create;
  Sh := Wb.Sheets.Add;
  Sh.Range['B1', 'B1'].Value := 5;      // A1 efterlades bevidst tom

  Writeln(VarToStr(Wb.Calculate('=A1=0')));    // True
  Writeln(VarToStr(Wb.Calculate('=A1=""')));   // True
  Writeln(VarToStr(Wb.Calculate('=A1<B1')));   // True: den tomme sammenlignes som 0
  Writeln(VarToStr(Wb.Calculate('=A1<0')));    // False; True før v2.384.3
end;

Den sidste linje er den, der gjorde ondt i praksis. Under den gamle rang var en tom celle mindre end ethvert tal, negative inkluderet, så =IF(A1<0,"overdrawn","ok") mærkede hver tom balancecelle som overtrukket, og =A1=0 var FALSE for en celle, enhver bruger ville beskrive som nul. Én grænse stod tilbage efter v2.384.3: substitutionen valgte kun mellem 0 og den tomme streng, så en tom celle sammenlignet med en boolean blev 0, som rangerer under både TRUE og FALSE, og =A1=FALSE på en tom A1 evaluerede til FALSE. Siden HotXLS 2.384.53 behandles en tom celle sammenlignet med en logisk værdi som FALSE i både XLS- og XLSX-motoren, som Excel gør: med A1 tom returnerer =A1=FALSE og =A1<TRUE TRUE, og =A1=TRUE returnerer FALSE. Det betyder også, at sammenligningen ikke kan skelne tom fra FALSE, hverken i Excel eller HotXLS; skal et ark have den skelnen, så test med ISBLANK eller =A1=""

Hvorfor returnerede SUMIF med encelle-sumområde 0?

SUMIF returnerede 0, fordi HotXLS klemte iterationen til det mindste af de to områder, mens Excel beholder kriterieområdets form og kun bruger sumområdet til dets øverste venstre celle. =SUMIF(A1:A10,">5",B1) betyder derfor B1:B10 i Excel, en bekvemmelighed, mange håndbyggede templates er afhængige af. Den delte worker TXLSCalculator.GetValueItemRange2 krympede tidligere sine række- og kolonneantaller til værdiområdets, hvilket reducerede eksemplet til en enkelt test af A1 mod B1. v2.384.3 fjerner klemmen: løkken går nu igennem kriterieområdet og læser hver værdi med samme offset fra sumområdets øverste venstre hjørne. Fordi CalcSumIF og CalcAverageIF begge kalder den worker, får AVERAGEIF samme resize, og et sumområde større end kriterieområdet trimmes til kriterieformen af samme grund. Kriterieargumentet i midten er et value-class-argument, og de to yderste er reference-class, den skelnen, der er dækket i artiklen om implicit intersection og argumentklasser

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;          // kriteriekolonne: 1..10
      Sheet.Cells[Row, 2].Value := Row * 100;    // beløb: 100..1000
    end;
    Sheet.Cells[1, 4].Formula := '=SUMIF(A1:A10,">5",B1)';     // encelle-sumområde
    Sheet.Cells[2, 4].Formula := '=SUMIF(A1:A10,">5",B1:B10)'; // eksplicit sumområde
    if Book.Recalculate = lxOk then
      // Både D1 og D2 er 4000 (600+700+800+900+1000); D1 var 0 før v2.384.3
      Writeln(VarToStr(Sheet.Cells[1, 4].Value), ' ', VarToStr(Sheet.Cells[2, 4].Value));
  finally
    Book.Free;
  end;
end;

INDIRECT og YEARFRAC: to mere stille korrektioner

INDIRECT respekterer nu sit andet argument, og tekst efter en gyldig reference er en fejl i stedet for at blive ignoreret. Med a1 FALSE parses teksten som absolut R1C1, så =INDIRECT("R2C3",FALSE) læser C2; den gamle kode ignorerede flaget, læste "R2" som kolonne R, række 2, og returnerede stille den forkerte celle. Flaget dispatches på sin varianttype (boolean, tal eller tekst), for at konvertere en strengvariant direkte til Double rejser en exception. Relativ R1C1-tekst som R[1]C[1] returnerer #REF!, da INDIRECT ikke har nogen formelcelle-origin at opløse den mod, og A1-tekst med efterfølgende tegn, "B2 junk", returnerer også #REF!. YEARFRAC med basis 0 anvender nu de NASD-regler for sidste-februar, som DAYS360 allerede implementerede: er begge datoer den sidste dag i februar, bliver slutdagen 30, og derefter bliver en start på den sidste dag i februar 30. Fra 2024-02-29 til 2025-02-28 er tællingen nu 360 dage, en brøk på præcis 1, hvor den tidligere Days360US talte 359

Hvad garanterer disse fixes, og hvad var lærdommen?

Sammenligningskæde-adfærden er garanteret af en test, der sammenligner begge motorer med værdier målt i Excel 16, og den test findes, fordi den første beskrivelse af fixen var forkert. v2.384.3-release-noten sagde oprindeligt, at venstre-til-højre-folding gjorde =1<2<3 TRUE, hvilket er præcis, hvad den gamle right-associative parser producerede, og det modsatte af, hvad både Excel og den nye kode returnerer. Ingen havde evalueret eksemplet; det var skrevet ud fra intuitionen "1 er mindre end 2 er mindre end 3". Noten blev rettet, og syv-formel-testen tilføjet i en opfølgende commit, og den regel, der kom ud af det, gælder alle, der dokumenterer spreadsheet-semantik: kør eksemplet i Excel, før du skriver den forventede værdi ned. Substitutionen af tomme operander og SUMIF-resize følger samme Excel-adfærd, inklusive tom-versus-boolean-tilfældet siden v2.384.53, og betingede aggregater, der også skal springe filtrerede eller skjulte rækker over, følger de separate regler i artiklen om SUBTOTAL og AGGREGATE og skjulte rækker

HotXLS er en native Delphi- og C++Builder-spreadsheet-komponent, der læser, rekalkulerer og skriver XLS, XLSX, ODS og CSV uden Excel installeret, og sammenlignings-, tomme- og SUMIF-reglerne beskrevet her bor i beregningsmotoren, som begge arbejdsbogsarkitekturer deler. Den fulde funktionsliste og licensmulighederne står på produktsiden for HotXLS Delphi spreadsheet component