Jämförelsekedjor, tomma celler och SUMIF i HotXLS Delphi

HotXLS Delphi Component utvärderar =1<2<3 som FALSE, samma svar som Excel 16 ger, eftersom formeltolken sedan v2.384.3 viker ihop jämförelseoperatorer från vänster till höger: 1<2 blir TRUE, och TRUE<3 är FALSE för att ett booleskt värde rankas över varje tal. Samma utgåva gör en tom operand lika med både 0 och "", och låter SUMIF sträcka ett encells summaområde till formen av dess villkorsområde. Vart och ett av detta ser ut som kuriosa tills en arbetsbok beräknad i Delphi motsäger samma arbetsbok öppnad i Excel

Oenigheten börjar oftast med en formel någon skrivit på magkänsla. Någon skriver =0<B2<100 för att kontrollera att en kvantitet ligger inom intervallet, Excel svarar tyst FALSE för varje rad, och arket skeppas med den buggen inbakad. En beräkningsmotor får inte rätta användarens avsikt; dess uppgift är att producera det värde Excel skulle producera, så att det cachade resultat HotXLS skriver in i filen stämmer med vad Excel visar efter en omberäkning. Före v2.384.3 svarade HotXLS TRUE för den intervallkontrollen på varje rad, fel i motsatt riktning, och en rapport genererad på en server motsade samma rapport öppnad på en dator

Varför returnerar =1<2<3 FALSE i Excel?

Excel returnerar FALSE eftersom det läser en kedja av jämförelser som (1<2)<3, och det inre TRUE förlorar sedan typrankningsdagen mot talet 3. Den gamla HotXLS-tolken läste samma text som 1<(2<3): TXLSSyntax.Parse_expr i lxFormula.pas tolkade en operand, såg en jämförelsetoken och rekurrerade in i Parse_expr för högerledet, vilket gör operatorn högerassociativ. Det ger 1<TRUE, och ett tal ligger under ett booleskt värde, så resultatet blev TRUE. Felet är symmetriskt: =3>2>1 är TRUE i Excel och var FALSE i HotXLS, och =1=1=TRUE är TRUE i Excel och var FALSE före fixen. Regressionstestet CalculateFormula_ComparisonChainsFoldLeftToRight låser sju sådana formler mot de värden Excel 16 returnerar och kör var och en genom båda motorarkitekturerna, klassiska TXLSWorkbook och XLSX-nativa TXLSXWorkbook, med Calculate-metoden som beskrivs i översikten av HotXLS formelmotor

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');
  // Vad Excel 16 returnerar:  FALSE, TRUE, FALSE, TRUE, TRUE, TRUE, TRUE
var
  Classic: IXLSWorkbook;
  Xlsx: TXLSXWorkbook;
  i: Integer;
begin
  Classic := TXLSWorkbook.Create;
  Xlsx := TXLSXWorkbook.Create;
  try
    // TXLSXWorkbook.Calculate utvärderar mot det aktiva arket och
    // returnerar Null när arbetsboken inte har något ark alls
    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;

Fixen gör Parse_expr till en loop av samma form som Parse_expr1 redan använder för +, - och &. Den tolkar första operanden med Parse_expr1, och medan nästa token är en av =, <>, <, >, <= eller >= skapar den en jämförelsenod, fäster det ackumulerade vänsterresultatet som första barn, tolkar nästa operand med Parse_expr1 i stället för Parse_expr och gör den nya noden till vänsterresultat för nästa runda. Två detaljer var lätta att få fel när rekursionen omvandlades till iteration, och båda finns i underhållarnas anteckningar: den ackumulerade noden måste överlämnas (lChild := Item; Item := nil) i den ordningen, och felfileden måste Exit efter att den halvfärdiga noden frigjorts i stället för att ramla ur loopen och returnera en hängande trädstruktur

Hur rankar HotXLS tal, text och booleska värden i en jämförelse?

HotXLS rankar blandade typer som Excel gör: varje tal är mindre än varje textvärde, och varje textvärde är mindre än varje booleskt värde. TXLSCalculator.CompareVariants i lxCalc.pas klassificerar båda operanderna med GetRetValueType i uppräkningen TXLSRetValueType = (xlVariantValue, xlNumberValue, xlStringValue, xlBooleanValue), och när de två klasserna skiljer sig jämför den helt enkelt deras ordinal, så att deklarationsordningen i den enumen är regeln mellan typerna. Inom en klass är jämförelsen den naturliga, med en Excel-specifik vändning för text: båda strängarna går genom lxUpperCase först, så att ="abc"="ABC" är TRUE. Den rankningen är anledningen till att kedjeresultatet inte kan resoneras fram utan den. TRUE<3 är inte en tvångsomvandling av TRUE till 1, det är ett booleskt värde jämfört med ett tal, och det booleska vinner. Datum är serialtal för motorn (varDate klassas som xlNumberValue), så ett datum ligger alltid under all text, inklusive text som råkar se ut som ett datum

Vad är en tom cell lika med i en jämförelse?

En tom cell som jämförelseoperand är lika med 0 när andra sidan är ett tal, lika med "" när andra sidan är text, och sedan v2.384.53 lika med FALSE när andra sidan är ett logiskt värde, så att med A1 tom är =A1=0, =A1="" och =A1=FALSE alla TRUE. TXLSCalculator.CompareVarValues, som betjänar alla sex jämförelseoperatorer, ersätter den tomma innan CompareVariants anropas: om exakt en operand är Null blir den WideString('') när partnern är en sträng, False när partnern är boolesk, och 0 i övriga fall. Två tomma jämförs fortfarande lika med varandra utan ersättning. Aritmetikvägen hade alltid gjort en tom till 0, vilket är därför =A1+1 gav 1, men CompareVariants höll Null som sin egen lägsta rang, under varje tal, och jämförelseoperatorerna använde den rangen direkt

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

  Writeln(VarToStr(Wb.Calculate('=A1=0')));    // True
  Writeln(VarToStr(Wb.Calculate('=A1=""')));   // True
  Writeln(VarToStr(Wb.Calculate('=A1<B1')));   // True: den tomma jämförs som 0
  Writeln(VarToStr(Wb.Calculate('=A1<0')));    // False; True före v2.384.3
end;

Den sista raden är den som gjorde ont i praktiken. Under den gamla rangen var en tom mindre än varje tal, de negativa inkluderade, så att =IF(A1<0,"overdrawn","ok") märkte varje tomt saldo som överdraget, och =A1=0 var FALSE för en cell varje användare skulle beskriva som noll. En gräns återstod efter v2.384.3: ersättningen valde bara mellan 0 och den tomma strängen, så en tom jämförd med ett booleskt värde blev 0, som rankas under både TRUE och FALSE, och =A1=FALSE på en tom A1 utvärderades till FALSE. Sedan HotXLS 2.384.53 behandlas en tom jämförd med ett logiskt värde som FALSE i både XLS- och XLSX-motorerna, som Excel gör: med A1 tom returnerar =A1=FALSE och =A1<TRUE TRUE och =A1=TRUE returnerar FALSE. Det betyder också att jämförelsen inte kan skilja tom från FALSE, i Excel eller i HotXLS; när ett ark behöver den distinktionen, testa med ISBLANK eller =A1=""

Varför returnerade SUMIF med encells summaområde 0?

SUMIF returnerade 0 eftersom HotXLS klämde av iterationen till det mindre av de två områdena, medan Excel behåller villkorsområdets form och bara använder summaområdet för dess övre vänsterhörn. =SUMIF(A1:A10,">5",B1) betyder alltså B1:B10 i Excel, en bekvämlighet många handbyggda mallar litar på. Den gemensamma hjälparbetaren TXLSCalculator.GetValueItemRange2 krympte förr sina rad- och kolumnantal till de i värdeområdet, vilket reducerade exemplet till ett enda test av A1 mot B1. v2.384.3 tar bort avklämningen: loopen går nu igenom villkorsområdet och läser varje värde vid samma offset från summaområdets övre vänsterhörn. Eftersom CalcSumIF och CalcAverageIF båda anropar den hjälparbetaren får AVERAGEIF samma omstorlekning, och ett summaområde större än villkorsområdet trimmas till villkorsformen av samma skäl. Villkorsargumentet i mitten är ett värdeklassargument och de två yttre är referensklassade, distinktionen som tas upp i artikeln om implicit skärning och 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;          // villkorskolumn: 1..10
      Sheet.Cells[Row, 2].Value := Row * 100;    // belopp: 100..1000
    end;
    Sheet.Cells[1, 4].Formula := '=SUMIF(A1:A10,">5",B1)';     // encells summaområde
    Sheet.Cells[2, 4].Formula := '=SUMIF(A1:A10,">5",B1:B10)'; // explicit summaområde
    if Book.Recalculate = lxOk then
      // Både D1 och D2 är 4000 (600+700+800+900+1000); D1 var 0 före v2.384.3
      Writeln(VarToStr(Sheet.Cells[1, 4].Value), ' ', VarToStr(Sheet.Cells[2, 4].Value));
  finally
    Book.Free;
  end;
end;

INDIRECT och YEARFRAC: två tystare rättelser

INDIRECT hedrar nu sitt andra argument, och text efter en giltig referens är ett fel i stället för att ignoreras. Med a1 FALSE tolkas texten som absolut R1C1, så att =INDIRECT("R2C3",FALSE) läser C2; den gamla koden ignorerade flaggan, läste "R2" som kolumn R, rad 2, och returnerade tyst fel cell. Flaggan dispatchas på sin varianttyp (boolesk, tal eller text), eftersom en strängvariant konverterad rakt till Double kastar undantag. Relativ R1C1-text som R[1]C[1] returnerar #REF!, eftersom INDIRECT inte har något formelcellsursprung att lösa den mot, och A1-text med eftersläpande tecken, "B2 junk", returnerar också #REF!. YEARFRAC med bas 0 tillämpar nu NASD-reglerna för sista februari som DAYS360 redan implementerat: när båda datumen är sista dagen i februari blir slutdagen 30, och därefter blir en början på sista februari 30. Från 2024-02-29 till 2025-02-28 är räkningen nu 360 dagar, en andel på exakt 1, där tidigare Days360US räknade 359

Vad garanterar dessa fixar, och vad var lärdomen?

Jämförelsekedjebeteendet garanteras av ett test som jämför båda motorerna med värden uppmätta i Excel 16, och det testet finns eftersom den första beskrivningen av fixen var fel. Noten till v2.384.3 sade ursprungligen att vänster-till-höger-vikning gjorde =1<2<3 till TRUE, vilket är precis vad den gamla högerassociativa tolken producerade och motsatsen till vad både Excel och den nya koden returnerar. Ingen hade utvärderat exemplet; det var skrivet från magkänslan att "1 är mindre än 2 är mindre än 3". Noten korrigerades och sjuformeltestet lades till i en uppföljande commit, och den regel som kom ut av det gäller alla som dokumenterar kalkylbladssemantik: kör exemplet i Excel innan du skriver ner det förväntade värdet. Ersättningen av tomma operander och SUMIF-omstorlekningen följer samma Excel-beteende, inklusive fallet tom-mot-booleskt sedan v2.384.53, och villkorliga aggregeringar som också måste hoppa över filtrerade eller dolda rader följer de separata reglerna i artikeln om SUBTOTAL och AGGREGATE och dolda rader

HotXLS är en nativ Delphi- och C++Builder-kalkylbladskomponent som läser, räknar om och skriver XLS, XLSX, ODS och CSV utan Excel installerat, och reglerna för jämförelser, tomma celler och SUMIF som beskrivs här bor i beräkningsmotorn som båda arbetsboksarkitekturerna delar. Funktionslistan i sin helhet och licensalternativen finns på produktsidan för HotXLS Delphi-kalkylbladskomponenten