Sammenligningskjeder, tomme celler og SUMIF i HotXLS Delphi

HotXLS Delphi Component evaluerer =1<2<3 til FALSE, samme svar som Excel 16 gir, for siden v2.384.3 folder formelparseren sammenligningsoperatorer fra venstre mot høyre: 1<2 blir TRUE, og TRUE<3 er FALSE fordi en boolean rangerer over alle tall. Samme utgivelse gjør en tom operand lik både 0 og "", og lar SUMIF strekke et sumområde på én celle til formen til kriterieområdet. Hvert av disse ser ut som kuriositeter helt til en arbeidsbok beregnet i Delphi er uenig med samme arbeidsbok åpnet i Excel

Uenigheten starter vanligvis med en formel noen skrev på intuisjon. Noen taster =0<B2<100 for å sjekke at et antall er innenfor området, Excel svarer stille FALSE for hver rad, og arket sendes ut med den feilen bakt inn. En beregningsmotor får ikke lov til å reparere brukerens intensjon; jobben dens er å produsere verdien Excel ville produsert, slik at det bufrede resultatet HotXLS skriver inn i filen stemmer med det Excel viser etter en rekalkulering. Før v2.384.3 svarte HotXLS TRUE for den områdesjekken på hver rad, feil i motsatt retning, og en rapport generert på en server ville motsa samme rapport åpnet på en stasjonær

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

Excel gir FALSE fordi den leser en kjede av sammenligninger som (1<2)<3, og den indre TRUE taper så type-rangeringskonkurransen mot tallet 3. Den gamle HotXLS-parseren leste samme tekst som 1<(2<3): TXLSSyntax.Parse_expr i lxFormula.pas parset én operand, så en sammenligningstoken og rekurserte inn i Parse_expr for høyresiden, noe som gjør operatøren høyreassosiativ. Det gir 1<TRUE, og et tall er under en boolean, så resultatet ble TRUE. Feilen 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 fiksen. Regresjonstesten CalculateFormula_ComparisonChainsFoldLeftToRight fester syv slike formler mot verdiene Excel 16 gir, og kjører hver enkelt gjennom begge motorarkitekturene, classic TXLSWorkbook og den XLSX-native TXLSXWorkbook, med Calculate-metoden beskrevet i oversikten over 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');
  // Det Excel 16 gir:  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 mot det aktive arket og
    // gir Null når arbeidsboken ikke har noe ark i det hele tatt
    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;

Fiksen gjør Parse_expr om til en løkke av samme form som Parse_expr1 allerede bruker for +, - og &. Den parser den første operanden med Parse_expr1, og så lenge neste token er én av =, <>, <, >, <= eller >=, lager den en sammenligningsnode, fester det akkumulerte venstre resultatet som første barn, parser neste operand med Parse_expr1 i stedet for Parse_expr, og gjør den nye noden til venstre resultat for neste runde. To detaljer var lette å få feil da rekursjonen ble omgjort til iterasjon, og begge står i vedlikeholdernotatene: den akkumulerte noden må overlates (lChild := Item; Item := nil) i den rekkefølgen, og feilveien må Exit etter å ha frigjort den halvbygde noden i stedet for å falle ut av løkken og returnere et hengende tre

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

HotXLS rangerer blandede typer slik Excel gjør: alle tall er mindre enn alle tekstverdier, og alle tekstverdier er mindre enn alle booleans. TXLSCalculator.CompareVariants i lxCalc.pas klassifiserer begge operandene med GetRetValueType inn i enumerasjonen TXLSRetValueType = (xlVariantValue, xlNumberValue, xlStringValue, xlBooleanValue), og når de to klassene er forskjellige, sammenligner den bare ordinalene deres, så deklarasjonsrekkefølgen til den enum-en er kryss-type-regelen. Inne i én klasse er sammenligningen den naturlige, med ett Excel-spesifikt tvist for tekst: begge strengene går gjennom lxUpperCase først, så ="abc"="ABC" er TRUE. Denne rangeringen er grunnen til at kjederesultatet ikke kan ressoneres om uten den. TRUE<3 er ikke en tvangskonvertering av TRUE til 1, det er en boolean sammenlignet med et tall, og boolean-en vinner. Datoer er serialtall for motoren (varDate klassifiseres som xlNumberValue), så en dato er alltid under enhver tekst, inkludert tekst som tilfeldigvis ser ut som en dato

Hva er en tom celle lik i en sammenligning?

En tom celle brukt som sammenligningsoperand er lik 0 når den andre siden er et tall, lik "" når den andre siden er tekst, og siden v2.384.53 lik FALSE når den andre siden er en logisk verdi, så med A1 tom er =A1=0, =A1="" og =A1=FALSE alle TRUE. TXLSCalculator.CompareVarValues, som betjener alle seks sammenligningsoperatørene, erstatter den tomme før den kaller CompareVariants: er nøyaktig én operand Null, blir den WideString('') når partneren er en streng, False når partneren er en boolean, og 0 ellers. To tomme celler sammenlignes fortsatt like med hverandre uten substitusjon. Aritmetikk-veien har alltid gjort en tom celle om til 0, det er derfor =A1+1 ga 1, men CompareVariants beholder Null som sin egen laveste rang, under alle tall, og sammenligningsoperatørene brukte den rangen direkte

var
  Wb: IXLSWorkbook;
  Sh: TXLSWorksheet;
begin
  Wb := TXLSWorkbook.Create;
  Sh := Wb.Sheets.Add;
  Sh.Range['B1', 'B1'].Value := 5;      // A1 blir med vilje latt 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;

Siste linje er den som gjorde vondt i praksis. Under den gamle rangen var en tom celle mindre enn alle tall, negative inkludert, så =IF(A1<0,"overdrawn","ok") merket hver tom saldo-celle som overtrukket, og =A1=0 var FALSE for en celle enhver bruker ville beskrive som null. Én grense sto igjen etter v2.384.3: substitusjonen valgte bare mellom 0 og den tomme strengen, så en tom celle sammenlignet med en boolean ble 0, som rangerer under både TRUE og FALSE, og =A1=FALSE på en tom A1 evaluerte til FALSE. Siden HotXLS 2.384.53 behandles en tom celle sammenlignet med en logisk verdi som FALSE i både XLS- og XLSX-motoren, slik Excel gjør: med A1 tom gir =A1=FALSE og =A1<TRUE TRUE og =A1=TRUE gir FALSE. Det betyr også at sammenligningen ikke kan skille tom fra FALSE, i Excel eller i HotXLS; trenger et ark det skillet, test med ISBLANK eller =A1=""

Hvorfor ga SUMIF med et sumområde på én celle 0?

SUMIF ga 0 fordi HotXLS klemte iterasjonen inn på det minste av de to områdene, mens Excel beholder formen til kriterieområdet og bare bruker sumområdet til øvre venstre celle. =SUMIF(A1:A10,">5",B1) betyr derfor B1:B10 i Excel, en bekvemmelighet mange håndbygde maler støtter seg på. Den delte arbeideren TXLSCalculator.GetValueItemRange2 krympet rad- og kolonneantallet sitt til verdiområdets, noe som reduserte eksempelet til én enkelt test av A1 mot B1. v2.384.3 fjerner klemmen: løkken går nå gjennom kriterieområdet og leser hver verdi med samme offset fra sumområdets øvre venstre hjørne. Etter som CalcSumIF og CalcAverageIF begge kaller den arbeideren, får AVERAGEIF samme størrelsesendring, og et sumområde større enn kriterieområdet trimmes til kriterieformen av samme grunn. Kriterieargumentet i midten er et argument av verdiklasse og de to ytterste er av referanseklasse, skillet dekket i artikkelen om implisitt skjæringspunkt 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øp: 100..1000
    end;
    Sheet.Cells[1, 4].Formula := '=SUMIF(A1:A10,">5",B1)';     // sumområde på én celle
    Sheet.Cells[2, 4].Formula := '=SUMIF(A1:A10,">5",B1:B10)'; // eksplisitt 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 stillere korreksjoner

INDIRECT ærer nå sitt andre argument, og tekst etter en gyldig referanse er en feil i stedet for å bli ignorert. Med a1 FALSE parses teksten som absolutt R1C1, så =INDIRECT("R2C3",FALSE) leser C2; den gamle koden ignorerte flagget, leste «R2» som kolonne R, rad 2, og returnerte stille feil celle. Flagget dispatches på varianttypen sin (boolean, tall eller tekst), for å konvertere en strengvariant rett til Double reiser et unntak. Relativ R1C1-tekst som R[1]C[1] gir #REF!, siden INDIRECT ikke har noe formelcelle-origo å løse den mot, og A1-tekst med etterfølgende tegn, "B2 junk", gir #REF! også. YEARFRAC med basis 0 anvender nå NASD-reglene for siste-februar som DAYS360 allerede implementerte: er begge datoer den siste dagen i februar, blir sluttdagen 30, og en start på den siste dagen i februar blir så 30. Fra 2024-02-29 til 2025-02-28 er antallet nå 360 dager, en brøkdel på nøyaktig 1, der den tidligere Days360US telte 359

Hva garanterer disse fiksene, og hva var lærdommen?

Sammenligningskjede-oppførselen garanteres av en test som sammenligner begge motorer med verdier målt i Excel 16, og den testen finnes fordi den første beskrivelsen av fiksen var gal. Utgivelsesnotatet for v2.384.3 sa opprinnelig at venstre-til-høyre-folding gjorde =1<2<3 til TRUE, noe som er nøyaktig hva den gamle høyreassosiative parseren produserte og det motsatte av hva både Excel og den nye koden gir. Ingen hadde evaluert eksempelet; det var skrevet ut fra intuisjonen om at «1 er mindre enn 2 er mindre enn 3». Notatet ble korrigert og syv-formel-testen lagt til i en oppfølgings-commit, og regelen som kom ut av det, gjelder enhver som dokumenterer regnearksemantikk: kjør eksempelet i Excel før du skriver ned forventet verdi. Substitusjonen av tomme operander og SUMIF-størrelsesendringen følger samme Excel-oppførsel, inkludert tom-mot-boolean-tilfellet siden v2.384.53, og betingede aggregater som også må hoppe over filtrerte eller skjulte rader følger de separate reglene i artikkelen om skjulte rader for SUBTOTAL og AGGREGATE

HotXLS er en naturlig Delphi- og C++Builder-regnearkkomponent som leser, rekalkulerer og skriver XLS, XLSX, ODS og CSV uten Excel installert, og sammenlignings-, tom- og SUMIF-reglene beskrevet her bor i beregningsmotoren begge arbeidsbokarkitekturene deler. Full funksjonsliste og lisensalternativer finner du på produktsiden for HotXLS Delphi regnearkkomponent