Vergelijkingsketens, lege cellen en SUMIF in HotXLS Delphi

De HotXLS Delphi-component evalueert =1<2<3 als FALSE, hetzelfde antwoord dat Excel 16 geeft, want sinds v2.384.3 vouwt zijn formuleparser vergelijkingsoperatoren van links naar rechts: 1<2 wordt TRUE, en TRUE<3 is FALSE omdat een boolean boven elk getal rangschikt. Dezelfde release maakt een lege operand gelijk aan zowel 0 als "", en laat SUMIF een sombereik van één cel uitrekken tot de vorm van zijn criteriumbereik. Elk van deze drie oogt als trivia tot een in Delphi berekende werkmap het oneens is met dezelfde werkmap geopend in Excel

Het meningsverschil begint meestal met een formule die iemand op intuïtie schreef. Iemand tikt =0<B2<100 om te controleren dat een hoeveelheid binnen bereik ligt, Excel antwoordt geruisloos FALSE voor elke rij, en het werkblad verlaat het huis met die bug ingebakken. Een rekenmotor mag de intentie van de gebruiker niet repareren; zijn taak is de waarde produceren die Excel zou produceren, zodat het gecachte resultaat dat HotXLS in het bestand schrijft overeenkomt met wat Excel na een herberekening toont. Vóór v2.384.3 antwoordde HotXLS TRUE voor die bereikcontrole op elke rij, verkeerd in de tegenovergestelde richting, en een op een server gegenereerd rapport zou hetzelfde rapport op een desktop tegenspreken

Waarom geeft =1<2<3 FALSE terug in Excel?

Excel geeft FALSE terug omdat hij een keten vergelijkingen leest als (1<2)<3, en de binnenste TRUE dan het type-rangschikkingsgevecht tegen het getal 3 verliest. De oude HotXLS-parser las dezelfde tekst als 1<(2<3): TXLSSyntax.Parse_expr in lxFormula.pas parseerde één operand, zag een vergelijkingstoken en recurseerde naar Parse_expr voor de rechterkant, wat de operator rechts-associatief maakt. Dat geeft 1<TRUE, en een getal staat onder een boolean, dus het resultaat was TRUE. De fout is symmetrisch: =3>2>1 is TRUE in Excel en was FALSE in HotXLS, en =1=1=TRUE is TRUE in Excel en was vóór de fix FALSE. De regressietest CalculateFormula_ComparisonChainsFoldLeftToRight pint zeven zulke formules vast aan de waarden die Excel 16 teruggeeft, en draait elke één langs beide engine-architecturen, de klassieke TXLSWorkbook en de XLSX-native TXLSXWorkbook, met de Calculate-methode uit het overzicht van de HotXLS-formule-engine

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');
  // Wat Excel 16 teruggeeft: FALSE, TRUE, FALSE, TRUE, TRUE, TRUE, TRUE
var
  Classic: IXLSWorkbook;
  Xlsx: TXLSXWorkbook;
  i: Integer;
begin
  Classic := TXLSWorkbook.Create;
  Xlsx := TXLSXWorkbook.Create;
  try
    // TXLSXWorkbook.Calculate evalueert op het actieve werkblad en
    // geeft Null terug wanneer de werkmap helemaal geen werkblad heeft
    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;

De fix maakt van Parse_expr een lus van dezelfde vorm die Parse_expr1 al gebruikte voor +, - en &. Hij parseert de eerste operand met Parse_expr1, en zolang het volgende token een van =, <>, <, >, <= of >= is, maakt hij een vergelijkingsknoop, hangt het opgehoopte linkerresultaat als eerste kind eraan, parseert de volgende operand met Parse_expr1 in plaats van Parse_expr, en maakt de nieuwe knoop tot het linkerresultaat voor de volgende ronde. Twee details waren makkelijk fout te doen bij het omzetten van recursie in iteratie, en beide staan in de maintainer-notities: de opgehoopte knoop moet in die volgorde worden overgedragen (lChild := Item; Item := nil), en het foutpad moet na het vrijgeven van de halfgebouwde knoop Exit doen in plaats van uit de lus te vallen en een zwervende boom terug te geven

Hoe rangschikt HotXLS getallen, tekst en booleans in een vergelijking?

HotXLS rangschikt gemengde types zoals Excel: elk getal is kleiner dan elke tekstwaarde, en elke tekstwaarde is kleiner dan elke boolean. TXLSCalculator.CompareVariants in lxCalc.pas classificeert beide operanden met GetRetValueType in de opsomming TXLSRetValueType = (xlVariantValue, xlNumberValue, xlStringValue, xlBooleanValue), en wanneer de twee klassen verschillen vergelijkt hij simpelweg hun ordinalen, dus de declaratievolgorde van die enum is de cross-type-regel. Binnen één klasse is de vergelijking de natuurlijke, met één Excel-specifieke kneep voor tekst: beide strings gaan eerst door lxUpperCase, dus ="abc"="ABC" is TRUE. Deze rangschikking is de reden dat het ketenresultaat niet zonder haar te doorgronden is. TRUE<3 is geen coercie van TRUE naar 1, het is een boolean vergeleken met een getal, en de boolean wint. Datums zijn serienummers voor de motor (varDate classificeert als xlNumberValue), dus een datum staat altijd onder elke tekst, ook onder tekst die toevallig op een datum lijkt

Waaraan is een lege cel gelijk in een vergelijking?

Een lege cel als vergelijkingsoperand is gelijk aan 0 wanneer de andere kant een getal is, gelijk aan "" wanneer de andere kant tekst is, en sinds v2.384.53 gelijk aan FALSE wanneer de andere kant een logische waarde is, dus met een lege A1 zijn =A1=0, =A1="" en =A1=FALSE allemaal TRUE. TXLSCalculator.CompareVarValues, dat alle zes vergelijkingsoperatoren bedient, substitueert de lege cel vóór het aanroepen van CompareVariants: is precies één operand Null, dan wordt hij WideString('') wanneer zijn partner een string is, False wanneer zijn partner een boolean is, en anders 0. Twee lege cellen zijn nog steeds zonder substitutie aan elkaar gelijk. Het rekenpad maakte van een lege cel altijd 0, wat verklaart waarom =A1+1 1 gaf, maar CompareVariants hield Null als eigen laagste rang, onder elk getal, en de vergelijkingsoperatoren gebruikten die rang rechtstreeks

var
  Wb: IXLSWorkbook;
  Sh: TXLSWorksheet;
begin
  Wb := TXLSWorkbook.Create;
  Sh := Wb.Sheets.Add;
  Sh.Range['B1', 'B1'].Value := 5;      // A1 wordt met opzet leeg gelaten

  Writeln(VarToStr(Wb.Calculate('=A1=0')));    // True
  Writeln(VarToStr(Wb.Calculate('=A1=""')));   // True
  Writeln(VarToStr(Wb.Calculate('=A1<B1')));   // True: de lege cel vergelijkt als 0
  Writeln(VarToStr(Wb.Calculate('=A1<0')));    // False; vóór v2.384.3 True
end;

De laatste regel is degene die in de praktijk pijn deed. Onder de oude rang was een lege cel kleiner dan elk getal, negatieve incluis, dus =IF(A1<0,"overdrawn","ok") bestempelde elke lege saldo-cel als in het rood, en =A1=0 was FALSE voor een cel die elke gebruiker nul zou noemen. Eén grens bleef na v2.384.3 over: de substitutie koos alleen tussen 0 en de lege string, dus een lege cel vergeleken met een boolean werd 0, wat onder zowel TRUE als FALSE rangschikt, en =A1=FALSE op een lege A1 evalueerde naar FALSE. Sinds HotXLS 2.384.53 wordt een lege cel vergeleken met een logische waarde in zowel de XLS- als de XLSX-engine als FALSE behandeld, zoals Excel doet: met een lege A1 geven =A1=FALSE en =A1<TRUE TRUE terug en =A1=TRUE FALSE. Dat betekent ook dat de vergelijking leeg niet van FALSE kan onderscheiden, in Excel noch in HotXLS; heeft een werkblad dat onderscheid nodig, test dan met ISBLANK of =A1=""

Waarom gaf SUMIF met een sombereik van één cel 0 terug?

SUMIF gaf 0 terug omdat HotXLS de iteratie klemzette op het kleinste van de twee bereiken, terwijl Excel de vorm van het criteriumbereik houdt en het sombereik alleen voor zijn linkerbovencel gebruikt. =SUMIF(A1:A10,">5",B1) betekent in Excel dus B1:B10, een gemak waar veel handgebouwde sjablonen op vertrouwen. De gedeelde werker TXLSCalculator.GetValueItemRange2 verkleinde voorheen zijn rij- en kolomaantallen naar die van het waardebereik, wat het voorbeeld terugbracht tot één toets van A1 tegen B1. v2.384.3 haalt de klem weg: de lus loopt nu het criteriumbereik af en leest elke waarde op dezelfde offset vanuit de linkerbovenhoek van het sombereik. Omdat CalcSumIF en CalcAverageIF beide die werker aanroepen, krijgt AVERAGEIF dezelfde herschaling, en een sombereik groter dan het criteriumbereik wordt om dezelfde reden tot de criteriumvorm bijgesneden. Het criteriumargument in het midden is een value-class-argument en de buitenste twee zijn reference-class, het onderscheid uit het artikel over impliciete intersectie en argumentklassen

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;          // criteriumkolom: 1..10
      Sheet.Cells[Row, 2].Value := Row * 100;    // bedragen: 100..1000
    end;
    Sheet.Cells[1, 4].Formula := '=SUMIF(A1:A10,">5",B1)';     // sombereik van één cel
    Sheet.Cells[2, 4].Formula := '=SUMIF(A1:A10,">5",B1:B10)'; // expliciet sombereik
    if Book.Recalculate = lxOk then
      // Zowel D1 als D2 is 4000 (600+700+800+900+1000); D1 was vóór v2.384.3 0
      Writeln(VarToStr(Sheet.Cells[1, 4].Value), ' ', VarToStr(Sheet.Cells[2, 4].Value));
  finally
    Book.Free;
  end;
end;

INDIRECT en YEARFRAC: twee stillere correcties

INDIRECT eert nu zijn tweede argument, en tekst na een geldige verwijzing is een fout in plaats van dat ze wordt genegeerd. Met a1 FALSE wordt de tekst als absolute R1C1 geparseerd, dus =INDIRECT("R2C3",FALSE) leest C2; de oude code negeerde de vlag, las "R2" als kolom R, rij 2, en gaf geruisloos de verkeerde cel terug. De vlag wordt op zijn variant-type verzonden (boolean, getal of tekst), want een stringvariant rechtstreeks naar Double converteren gooit een exception. Relatieve R1C1-tekst zoals R[1]C[1] geeft #REF! terug, omdat INDIRECT geen formulecel-oorsprong heeft om haar tegen te herleiden, en A1-tekst met nakomende tekens, "B2 junk", geeft eveneens #REF! terug. YEARFRAC met basis 0 past nu de NASD-regels voor de laatste februari-dag toe die DAYS360 al had: zijn beide datums de laatste dag van februari, dan wordt de einddag 30, en wordt een begin op de laatste dag van februari vervolgens 30. Van 2024-02-29 tot 2025-02-28 is de telling nu 360 dagen, een fractie van precies 1, waar de vorige Days360US op 359 uitkwam

Wat garanderen deze fixes, en wat was de les?

Het vergelijkingsketen-gedrag wordt door een test gegarandeerd die beide engines vergelijkt met in Excel 16 gemeten waarden, en die test bestaat omdat de eerste beschrijving van de fix verkeerd was. De v2.384.3-releasenote zei aanvankelijk dat links-naar-rechts-vouwen =1<2<3 TRUE maakte, wat precies is wat de oude rechts-associatieve parser produceerde en het tegendeel van wat zowel Excel als de nieuwe code teruggeven. Niemand had het voorbeeld geëvalueerd; het was opgeschreven vanuit de intuïtie dat "1 kleiner is dan 2 kleiner is dan 3". De note is gecorrigeerd en de zeven-formules-test kwam in een vervolg-commit erbij, en de regel die eruit voortkwam geldt voor iedereen die spreadsheet-semantiek documenteert: draai het voorbeeld in Excel voordat u de verwachte waarde opschrijft. De substitutie van lege operanden en de SUMIF-herschaling volgen hetzelfde Excel-gedrag, inclusief het leeg-tegen-boolean-geval sinds v2.384.53, en voorwaardelijke aggregaten die ook gefilterde of verborgen rijen moeten overslaan volgen de aparte regels uit het SUBTOTAL- en AGGREGATE-artikel over verborgen rijen

HotXLS is een native Delphi- en C++Builder-spreadsheetcomponent die XLS, XLSX, ODS en CSV leest, herberekent en schrijft zonder geïnstalleerde Excel, en de vergelijkings-, leegcel- en SUMIF-regels die hier zijn beschreven wonen in de rekenmotor die beide werkmap-architecturen delen. De volledige functielijst en licentieopties staan op de productpagina van de HotXLS Delphi-spreadsheetcomponent