Porovnávacie reťazce, prázdne bunky a SUMIF v HotXLS

HotXLS Delphi Component vyhodnocuje =1<2<3 ako FALSE, tú istú odpoveď, akú dáva Excel 16, pretože od v2.384.3 jeho formula parser skladá porovnávacie operátory zľava doprava: 1<2 sa stane TRUE a TRUE<3 je FALSE, lebo boolean sa radí nad každé číslo. To isté vydanie robí prázdny operand rovným 0 aj "" a necháva SUMIF roztiahnuť jednobunkový sum range na tvar kritériového range. Každá z týchto vecí vyzerá ako trifka, kým workbook počítaný v Delphi nesúhlasí s tým istým workbookom otvoreným v Exceli

Nesúhlas zvyčajne začína formulou, ktorú niekto napísal z intuície. Niekto vypíše =0<B2<100, aby skontroloval, že množstvo je v rozsahu, Excel poticho odpovedá FALSE pre každý riadok a hárok ide von s týmto bugom zapečeným vnútri. Výpočtový engine nemá opravovať úmysel užívateľa; jeho prácou je vydať hodnotu, ktorú by vydal Excel, aby cacheovaný výsledok, ktorý HotXLS zapisuje do súboru, sedel s tým, čo Excel ukáže po prepočte. Pred v2.384.3 odpovedal HotXLS na túto kontrolu rozsahu TRUE pre každý riadok, zle na opačnú stranu, a report generovaný na serveri by si protirečil s tým istým reportom otvoreným na desktope

Prečo vracia =1<2<3 v Exceli FALSE?

Excel vracia FALSE, pretože reťazec porovnaní číta ako (1<2)<3 a vnútorné TRUE potom prehrá súboj o poradie typov s číslom 3. Starý parser HotXLS čítal ten istý text ako 1<(2<3): TXLSSyntax.Parse_expr v lxFormula.pas vyparsoval jeden operand, uvidel porovnávací token a rekurzoval do Parse_expr pre pravú stranu, čo z operátora spraví right-associative. Výsledkom bolo 1<TRUE a číslo je pod booleanom, takže výsledkom bol TRUE. Chyba je symetrická: =3>2>1 je v Exceli TRUE a v HotXLS bolo FALSE, a =1=1=TRUE je v Exceli TRUE a pred opravou bolo FALSE. Regresný test CalculateFormula_ComparisonChainsFoldLeftToRight pripichne sedem takýchto formul k hodnotám, ktoré vracia Excel 16, a pustí každú cez obe architektúry engine, klasický TXLSWorkbook a XLSX-natívny TXLSXWorkbook, pomocou metódy Calculate popísanej v prehľade formula engine HotXLS

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');
  // Čo vracia Excel 16:  FALSE, TRUE, FALSE, TRUE, TRUE, TRUE, TRUE
var
  Classic: IXLSWorkbook;
  Xlsx: TXLSXWorkbook;
  i: Integer;
begin
  Classic := TXLSWorkbook.Create;
  Xlsx := TXLSXWorkbook.Create;
  try
    // TXLSXWorkbook.Calculate vyhodnocuje proti aktívnemu hárku a
    // vráti Null, keď workbook nemá vôbec žiadny hárok
    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;

Oprava mení Parse_expr na cyklus toho istého tvaru, aký už Parse_expr1 používa pre +, - a &. Vyparsuje prvý operand cez Parse_expr1 a kým ďalší token je jedno z =, <>, <, >, <= alebo >=, vytvorí porovnávací uzol, pripojí akumulovaný ľavý výsledok ako prvé dieťa, vyparsuje ďalší operand cez Parse_expr1 namiesto Parse_expr a urobí z nového uzla ľavý výsledok pre ďalšie kolo. Pri konverzii rekurzie na iteráciu sa dva detaily dali pokoriť ľahko a oba sú v poznámkach správcov: akumulovaný uzol sa musí odovzdať v poradí (lChild := Item; Item := nil) a chybová cesta musí po uvoľnení polovicu postaveného uzla urobiť Exit, nie vypadnúť z cyklu a vrátiť visiaci strom

Ako HotXLS radí čísla, text a booleany v porovnaní?

HotXLS radí zmiešané typy ako Excel: každé číslo je menšie než každá textová hodnota a každá textová hodnota je menšia než každý boolean. TXLSCalculator.CompareVariants v lxCalc.pas klasifikuje oba operandy cez GetRetValueType do enumerácie TXLSRetValueType = (xlVariantValue, xlNumberValue, xlStringValue, xlBooleanValue) a keď sa dve triedy líšia, jednoducho porovná ich ordinály, takže poradie deklarácie toho enumu je pravidlo medzi typmi. Vnútri jednej triedy je porovnanie prirodzené, s jedným excelovým twistom pri texte: oba reťazce prejdú najprv cez lxUpperCase, takže ="abc"="ABC" je TRUE. Toto poradie je dôvod, prečo sa výsledok reťazca nedá rozumieť bez neho. TRUE<3 nie je konverzia TRUE na 1, je to boolean porovnávaný s číslom a boolean vyhráva. Dátumy sú pre engine serial čísla (varDate sa klasifikuje ako xlNumberValue), takže dátum je vždy pod akýmkoľvek textom, vrátane textu, ktorý náhodou vyzerá ako dátum

Čomu sa rovná prázdna bunka v porovnaní?

Prázdna bunka použitá ako porovnávací operand sa rovná 0, keď druhá strana je číslo, rovná sa "", keď druhá strana je text, a od v2.384.53 sa rovná FALSE, keď druhá strana je logická hodnota, takže s prázdnou A1 sú =A1=0, =A1="" aj =A1=FALSE všetky TRUE. TXLSCalculator.CompareVarValues, ktorý obsluhuje všetkých šesť porovnávacích operátorov, dosadí prázdnu pred volaním CompareVariants: ak je Null presne jeden operand, stane sa WideString(''), keď jeho partner je reťazec, False, keď partner je boolean, a inak 0. Dve prázdne sa stále porovnajú ako rovné bez dosadenia. Aritmetická cesta menila prázdnu na 0 vždy, preto =A1+1 dávalo 1, ale CompareVariants drží Null ako vlastnú najnižšiu radu pod každým číslom a porovávacie operátory túto radu používali priamo

var
  Wb: IXLSWorkbook;
  Sh: TXLSWorksheet;
begin
  Wb := TXLSWorkbook.Create;
  Sh := Wb.Sheets.Add;
  Sh.Range['B1', 'B1'].Value := 5;      // A1 je zámerne ponechaná prázdna

  Writeln(VarToStr(Wb.Calculate('=A1=0')));    // True
  Writeln(VarToStr(Wb.Calculate('=A1=""')));   // True
  Writeln(VarToStr(Wb.Calculate('=A1<B1')));   // True: prázdna sa porovná ako 0
  Writeln(VarToStr(Wb.Calculate('=A1<0')));    // False; True pred v2.384.3
end;

Posledný riadok je ten, ktorý v praxi bolil. Pod starou radou bola prázdna menšia než každé číslo, negatívne nevynímajúc, takže =IF(A1<0,"overdrawn","ok") označil každú prázdnu bunku so zostatkom ako prečerpanú a =A1=0 bolo FALSE pre bunku, ktorú by každý užívateľ popísal ako nulu. Po v2.384.3 ostala jedna hranica: dosadenie si vyberalo len medzi 0 a prázdnym reťazcom, takže prázdna porovnaná s booleanom sa stala 0, ktorá sa radí pod TRUE aj FALSE, a =A1=FALSE na prázdnej A1 vyšlo FALSE. Od HotXLS 2.384.53 sa prázdna porovnaná s logickou hodnotou berie ako FALSE v oboch engineoch, XLS aj XLSX, ako to robí Excel: s prázdnou A1 vracajú =A1=FALSE a =A1<TRUE TRUE a =A1=TRUE vracia FALSE. To tiež znamená, že porovnanie nedokáže odlíšiť prázdnu od FALSE, v Exceli aj v HotXLS; keď hárok toto odlíšenie potrebuje, testujte cez ISBLANK alebo =A1=""

Prečo vrátil SUMIF s jednobunkovým sum range 0?

SUMIF vracal 0, pretože HotXLS zovrel iteráciu na menší z oboch range, zatiaľ čo Excel drží tvar kritériového range a sum range používa len pre jeho ľavý horný roh. =SUMIF(A1:A10,">5",B1) preto v Exceli znamená B1:B10, pohodlie, na ktoré sa spoľahne veľa ručne poskladaných šablón. Zdieľaný worker TXLSCalculator.GetValueItemRange2 zmršťoval počty riadkov a stĺpcov na hodnoty value range, čím z príkladu urobil jediný test A1 proti B1. v2.384.3 zovretie berie preč: cyklus teraz prechádza kritériový range a číta každú hodnotu na tom istom offsete od ľavého horného rohu sum range. Keďže CalcSumIF aj CalcAverageIF volajú ten istý worker, AVERAGEIF dostane tú istú zmenu rozmeru a sum range väčší než kritériový range sa z rovnakého dôvodu zreže na tvar kritéria. Kritériový argument v prostriedku je argument triedy value a vonkajšie dva sú triedy reference, rozdiel popísaný v článku o implicit intersection a triedach argumentov

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;          // kritériový stĺpec: 1..10
      Sheet.Cells[Row, 2].Value := Row * 100;    // čiastky: 100..1000
    end;
    Sheet.Cells[1, 4].Formula := '=SUMIF(A1:A10,">5",B1)';     // jednobunkový sum range
    Sheet.Cells[2, 4].Formula := '=SUMIF(A1:A10,">5",B1:B10)'; // explicitný sum range
    if Book.Recalculate = lxOk then
      // D1 aj D2 sú 4000 (600+700+800+900+1000); D1 bolo 0 pred v2.384.3
      Writeln(VarToStr(Sheet.Cells[1, 4].Value), ' ', VarToStr(Sheet.Cells[2, 4].Value));
  finally
    Book.Free;
  end;
end;

INDIRECT a YEARFRAC: dve tichšie opravy

INDIRECT teraz rešpektuje svoj druhý argument a text po platnej referencii je chyba namiesto ignorovania. S a1 FALSE sa text parsuje ako absolutné R1C1, takže =INDIRECT("R2C3",FALSE) číta C2; starý kód flag ignoroval, čítal "R2" ako stĺpec R, riadok 2, a poticho vrátil zlú bunku. Flag sa dispatchuje podľa variantového typu (boolean, číslo alebo text), lebo priama konverzia string variantu na Double vyhodí výnimku. Relatívny R1C1 text ako R[1]C[1] vracia #REF!, pretože INDIRECT nemá pôvod formuly bunky, voči ktorému by ho rozlíšil, a A1 text s trailing znakmi, "B2 junk", vracia tiež #REF!. YEARFRAC so základom 0 teraz aplikuje NASD pravidlá posledného februárového dňa, ktoré DAYS360 už malo: keď oba dátumy sú posledný deň februára, koncový deň sa stane 30 a potom štart na posledný deň februára sa stane 30. Od 2024-02-29 do 2025-02-28 je počet teraz 360 dní, zlomok presne 1, kde predchádzajúce Days360US počítalo 359

Čo tieto opravy garantujú a aká bola lekcia?

Správanie reťazcov porovnaní garantuje test, ktorý porovnáva oba enginey s hodnotami odmeranými v Exceli 16, a ten test existuje preto, že prvé znenie opravy bolo zlé. Release note v2.384.3 pôvodne hovorila, že skladanie zľava doprava spraví z =1<2<3 TRUE, čo je presne to, čo produkoval starý right-associative parser a opak toho, čo vracia Excel aj nový kód. Nikto si príklad nevyhodnotil; napísaný bol z intuície "1 je menšie než 2 je menšie než 3". Poznámka bola opravená a sedemformulový test pridaný v follow-up commite a pravidlo, ktoré z toho vzišlo, platí pre každého, kto dokumentuje správanie tabuľiek: pustite si príklad v Exceli skôr, než si zapíšete očakávanú hodnotu. Dosadenie prázdneho operandu a zmena rozmeru SUMIF nasledujú to isté správanie Excelu, vrátane prípadu prázdna-versus-boolean od v2.384.53, a podmienené agregáty, ktoré musia navyše preskočiť filtrované alebo skryté riadky, nasledujú samostatné pravidlá v článku o skrytých riadkoch v SUBTOTAL a AGGREGATE

HotXLS je natívny spreadsheet component pre Delphi a C++Builder, ktorý číta, prepočítava a zapisuje XLS, XLSX, ODS a CSV bez nainštalovaného Excelu a pravidlá porovnaní, prázdnych buniek a SUMIF popísané tu žijú vo výpočtovom engine, ktorý zdieľajú obe architektúry workbookov. Celý zoznam funkcií a licenčné možnosti sú na produktovej stránke HotXLS Delphi spreadsheet component