Lanci usporedbi, prazne ćelije i SUMIF u HotXLS Delphiju

HotXLS Delphi Component vrednuje =1<2<3 kao FALSE, isti odgovor koji daje Excel 16, jer njegov parser formula od v2.384.3 slaže operatore usporedbe slijeva nadesno: 1<2 postaje TRUE, a TRUE<3 je FALSE jer se logička vrijednost rangira iznad svakog broja. Isto izdanje čini prazan operand jednakim i 0 i "", te dopušta SUMIF-u da raspon zbroja od jedne ćelije rastegne na oblik raspona kriterija. Svako od ovoga izgleda kao sitnica dok radna knjiga izračunata u Delphiju ne poriče istu knjigu otvorenu u Excelu

Neslaganje obično počne formulom koju je netko napisao intuicijom. Netko utipka =0<B2<100 da provjeri je li količina u rasponu, Excel tiho odgovara FALSE za svaki redak, i tablica ide u svijet s tom greškom zapečenom u sebi. Engine za računanje ne smije ispravljati namjeru korisnika; njegov je posao proizvesti vrijednost koju bi proizveo Excel, da se rezultat u cacheu koji HotXLS upisuje u datoteku poklapa s onim što Excel prikaže nakon ponovnog računanja. Prije v2.384.3 HotXLS je za tu provjeru raspona odgovarao TRUE u svakom retku, pogrešno u suprotnom smjeru, i izvještaj generiran na serveru proturječio bi istom izvještaju otvorenom na desktopu

Zašto =1<2<3 u Excelu vraća FALSE?

Excel vraća FALSE jer lanac usporedbi čita kao (1<2)<3, a unutarnji TRUE zatim gubi duel rangiranja tipova protiv broja 3. Stari HotXLS parser čitao je isti tekst kao 1<(2<3): TXLSSyntax.Parse_expr u lxFormula.pas parsirao je jedan operand, ugledao token usporedbe i rekurzirao se u Parse_expr za desnu stranu, što operator čini desno-asocijativnim. To daje 1<TRUE, a broj je ispod logičke vrijednosti, pa je rezultat bio TRUE. Greška je simetrična: =3>2>1 u Excelu je TRUE a u HotXLS-u bila je FALSE, i =1=1=TRUE u Excelu je TRUE a prije popravka FALSE. Regresija CalculateFormula_ComparisonChainsFoldLeftToRight veže sedam takvih formula uz vrijednosti koje vraća Excel 16 i svaku provlači kroz obje arhitekture enginea, klasični TXLSWorkbook i XLSX-nativni TXLSXWorkbook, koristeći metodu Calculate opisanu u pregledu HotXLS enginea formula

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');
  // Što vraća 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 vrednuje nad aktivnim listom i
    // vraća Null kad radna knjiga uopće nema list
    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;

Popravak pretvara Parse_expr u petlju istog oblika kakvu Parse_expr1 već koristi za +, - i &. Prvi operand parsira s Parse_expr1, i dok je sljedeći token jedan od =, <>, <, >, <= ili >=, stvara čvor usporedbe, kači nakupljeni lijevi rezultat kao prvo dijete, sljedeći operand parsira s Parse_expr1 umjesto s Parse_expr i novi čvor proglašava lijevim rezultatom za sljedeći krug. Dva detalja lako je pokvariti pri pretvaranju rekurzije u iteraciju, i oboje su u bilješkama održavatelja: nakupljeni čvor treba predati u ruke (lChild := Item; Item := nil) baš tim redom, a staza greške mora Exit-ati nakon oslobođanja napola sagrađenog čvora umjesto da ispadne iz petlje i vrati tree koji ničim drži

Kako HotXLS rangira brojeve, tekst i logičke vrijednosti u usporedbi?

HotXLS miješane tipove rangira kako Excel: svaki je broj manji od svake tekstne vrijednosti, a svaka tekstna vrijednost manja od svake logičke. TXLSCalculator.CompareVariants u lxCalc.pas oba operanda klasificira s GetRetValueType u enumeraciju TXLSRetValueType = (xlVariantValue, xlNumberValue, xlStringValue, xlBooleanValue), i kad se dvije klase razlikuju, uspoređuje njihove ordinate, pa je deklaracijski redoslijed tog enuma pravilo među tipovima. Unutar jedne klase usporedba je prirodna, uz jedno Excelovo posebno pravilo za tekst: oba stringa prije prolaze kroz lxUpperCase, pa je ="abc"="ABC" TRUE. To rangiranje je razlog što se o rezultatu lanca ne može razmišljati bez njega. TRUE<3 nije koerzija TRUE u 1, nego logička vrijednost uspoređena s brojem, i logička pobjeđuje. Datumi su za engine serijski brojevi (varDate se klasificira kao xlNumberValue), pa je datum uvijek ispod bilo kojeg teksta, uključujući tekst koji slučajno liči na datum

Čemu je jednaka prazna ćelija u usporedbi?

Prazna ćelija korištena kao operand usporedbe jednaka je 0 kad je druga strana broj, jednaka "" kad je druga strana tekst, a od v2.384.53 jednaka FALSE kad je druga strana logička vrijednost, pa uz prazan A1 =A1=0, =A1="" i =A1=FALSE svi su TRUE. TXLSCalculator.CompareVarValues, koji opslužuje svih šest operatora usporedbe, prazninu zamjenjuje prije poziva CompareVariants: ako je točno jedan operand Null, postaje WideString('') kad mu je partner string, False kad mu je partner logička vrijednost, a inače 0. Dvije praznine i dalje su međusobno jednake bez zamjene. Aritmetička putanja prazninu je oduvijek pretvarala u 0, pa je =A1+1 dalo 1, ali je CompareVariants Null držao kao vlastiti najniži rang, ispod svakog broja, a operatori usporedbe taj rang koristili su izravno

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

  Writeln(VarToStr(Wb.Calculate('=A1=0')));    // True
  Writeln(VarToStr(Wb.Calculate('=A1=""')));   // True
  Writeln(VarToStr(Wb.Calculate('=A1<B1')));   // True: prazan se uspoređuje kao 0
  Writeln(VarToStr(Wb.Calculate('=A1<0')));    // False; TRUE prije v2.384.3
end;

Posljednji redak bio je onaj koji je u praksi bolo. Pod starim rangom praznina je bila manja od svakog broja, negativnih uključivo, pa je =IF(A1<0,"overdrawn","ok") svaku praznu ćeliju salda označavao kao prekoračenu, a =A1=0 bilo je FALSE za ćeliju koju bi svaki korisnik opisao kao nulu. Jedna granica ostala je i nakon v2.384.3: zamjena je birala samo između 0 i praznog stringa, pa je praznina uspoređena s logičkom vrijednošću postajala 0, što je rang ispod i TRUE i FALSE, i =A1=FALSE na praznom A1 vrednuo se u FALSE. Od HotXLS 2.384.53 praznina uspoređena s logičkom vrijednošću tretira se kao FALSE u oba enginea, XLS i XLSX, kao i u Excelu: uz prazan A1 =A1=FALSE i =A1<TRUE vraćaju TRUE, a =A1=TRUE vraća FALSE. To znači i da usporedba ne zna razlikovati prazno od FALSE, u Excelu ili u HotXLS-u; kad tablica treba tu razliku, testirajte s ISBLANK ili =A1=""

Zašto je SUMIF s rasponom zbroja od jedne ćelije vraćao 0?

SUMIF je vraćao 0 jer je HotXLS iteraciju stezao na manji od dva raspona, dok Excel drži oblik raspona kriterija, a raspon zbroja koristi samo za svoj gornji lijevi kut. =SUMIF(A1:A10,">5",B1) u Excelu stoga znači B1:B10, udobnost na koju se oslanjaju mnogi ručno građeni predlošci. Zajednički radnik TXLSCalculator.GetValueItemRange2 svoje je brojeve redaka i stupaca smanjivao na one raspona vrijednosti, što je primjer svelo na jedan test A1 protiv B1. v2.384.3 uklanja stezanje: petlja sada hoda rasponom kriterija i čita svaku vrijednost na istom pomaku od gornjeg lijevog kuta raspona zbroja. Budući da CalcSumIF i CalcAverageIF oboje zovu tog radnika, AVERAGEIF dobiva isto rastezanje, a raspon zbroja veći od raspona kriterija skraćuje se na oblik kriterija iz istog razloga. Kriterij u sredini je argument klase vrijednosti, a vanjska dva su reference klase, razlika obrađena u članku o implicitnom presjeku i klasama argumenata

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;          // stupac kriterija: 1..10
      Sheet.Cells[Row, 2].Value := Row * 100;    // iznosi: 100..1000
    end;
    Sheet.Cells[1, 4].Formula := '=SUMIF(A1:A10,">5",B1)';     // raspon zbroja od jedne ćelije
    Sheet.Cells[2, 4].Formula := '=SUMIF(A1:A10,">5",B1:B10)'; // eksplicitni raspon zbroja
    if Book.Recalculate = lxOk then
      // D1 i D2 su oboje 4000 (600+700+800+900+1000); D1 je bilo 0 prije v2.384.3
      Writeln(VarToStr(Sheet.Cells[1, 4].Value), ' ', VarToStr(Sheet.Cells[2, 4].Value));
  finally
    Book.Free;
  end;
end;

INDIRECT i YEARFRAC: dvije tihije ispravke

INDIRECT sada poštuje svoj drugi argument, a tekst nakon valjane reference greška je umjesto da se ignorira. S a1 FALSE tekst se parsira kao apsolutni R1C1, pa =INDIRECT("R2C3",FALSE) čita C2; stari je kod flag ignorirao, čitao "R2" kao stupac R, redak 2, i tiho vraćao krivu ćeliju. Flag se dispečira po varijantnom tipu (logička vrijednost, broj ili tekst) jer izravna konverzija string variante u Double baca iznimku. Relativni R1C1 tekst poput R[1]C[1] vraća #REF!, jer INDIRECT nema ishodište formula-ćelije uz koje bi ga razriješio, a A1 tekst sa znakovima u repu, "B2 junk", vraća #REF! jednako. YEARFRAC s bazom 0 sada primjenjuje NASD pravila za zadnji dan veljače koja DAYS360 već implementira: kad su oba datuma zadnji dan veljače, završni dan postaje 30, a zatim početak na zadnji dan veljače postaje 30. Od 2024-02-29 do 2025-02-28 brojanje je sada 360 dana, razlomak točno 1, dok je stari Days360US brojao 359

Što ovi popravci jamče, a što je bila lekcija?

Ponašanje lanca usporedbi jamči test koji oba enginea uspoređuje s vrijednostima izmjerenim u Excelu 16, a taj test postoji jer je prvi opis popravka bio kriv. Izdanje v2.384.3 u bilješci je izvorno reklo da slijeva-na-desno slaženje čini =1<2<3 TRUE, što je upravo ono što je proizvodio stari desno-asocijativni parser i suprotno od onoga što Excel i novi kod vraćaju. Nitko nije vrednuo primjer; napisan je iz intuicije "1 je manje od 2 što je manje od 3". Bilješka je ispravljena, a test sa sedam formula dodan u sljedećem commitu, i pravilo koje je iz toga izašlo vrijedi za svakoga tko dokumentira semantiku proračunskih tablica: primjer pokrenite u Excelu prije nego zapišete očekivanu vrijednost. Zamjena praznog operanda i rastezanje SUMIF-a slijede isto Excelovo ponašanje, uključujući slučaj prazno-naspram-logičke od v2.384.53, a uvjetne agregate koji moraju preskakati i filtrirane ili skrivene retke prati odvojena pravila iz članka o SUBTOTAL i AGGREGATE skrivenim recima

HotXLS je nativna Delphi i C++Builder spreadsheet komponenta koja čita, ponovno računa i zapisuje XLS, XLSX, ODS i CSV bez instaliranog Excela, a pravila usporedbe, praznine i SUMIF-a opisana ovdje žive u engineu za računanje koji dijele obje arhitekture radnih knjiga. Cjelokupni popis funkcija i licencne opcije su na stranici proizvoda HotXLS Delphi spreadsheet komponenta