Verige primerjav, prazne celice in SUMIF v HotXLS za Delphi

HotXLS Delphi Component ovrednoti =1<2<3 kot FALSE, enak odgovor, kot ga da Excel 16, ker njegov razčlenjevalnik formul od v2.384.3 zloži primerjalne operatorje od leve proti desni: 1<2 postane TRUE, TRUE<3 pa je FALSE, ker je logična vrednost uvrščena nad vsako številko. Ista izdaja naredi prazen operand enakega tako 0 kot "" in pusti SUMIF, da raztegne obseg vsote ene celice na obliko obsega pogojev. Vsako od teh zgleda kot malenkost, dokler se delovni zvezek, izračunan v Delphiju, ne razhaja z istim zvezkom, odprtim v Excelu

Razhajanje se običajno začne s formulo, ki jo je nekdo napisal po intuiciji. Nekdo natipka =0<B2<100, da preveri, ali je količina v obsegu, Excel tiho odgovori FALSE za vsako vrstico, list pa odpluje s to napako, pečeno v sebi. Računski pogon ne sme popraviti uporabnikove namere; njegova naloga je izdelati vrednost, ki bi jo izdelal Excel, tako da se predpomnjeni rezultat, ki ga HotXLS zapiše v datoteko, ujema s tem, kar Excel pokaže po ponovnem izračunu. Pred v2.384.3 je HotXLS odgovoril TRUE za to preverjanje obsega v vsaki vrstici, narobe v nasprotni smeri, poročilo, izdelano na strežniku, pa bi nasprotovalo istemu poročilu, odprtemu na namizju

Zakaj =1<2<3 vrne FALSE v Excelu?

Excel vrne FALSE, ker verigo primerjav bere kot (1<2)<3, notranji TRUE pa nato izgubi tekmovanje v razvrščanju tipov proti številki 3. Stari razčlenjevalnik HotXLS je isti tekst bral kot 1<(2<3): TXLSSyntax.Parse_expr v lxFormula.pas je razčlenil en operand, zagledal žeton primerjave in se rekurziral v Parse_expr za desno stran, kar naredi operator desno-asociativnega. To da 1<TRUE, številka je pod logično vrednostjo, zato je bil rezultat TRUE. Napaka je simetrična: =3>2>1 je TRUE v Excelu in je bilo FALSE v HotXLS, =1=1=TRUE je TRUE v Excelu in je bilo FALSE pred popravkom. Regresija CalculateFormula_ComparisonChainsFoldLeftToRight pripne sedem takih formul na vrednosti, ki jih vrne Excel 16, in vsako požene skozi obe arhitekturi pogonov, klasični TXLSWorkbook in izvorni XLSX TXLSXWorkbook, z metodo Calculate, opisano v pregledu formulskega pogona 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');
  // Kaj vrne 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 ovrednoti proti aktivnemu listu in
    // vrne Null, ko delovni zvezek sploh nima lista
    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;

Popravek spremeni Parse_expr v zanko iste oblike, kakršno Parse_expr1 že uporablja za +, - in &. Razčleni prvi operand s Parse_expr1 in, dokler je naslednji žeton eden od =, <>, <, >, <= ali >=, ustvari vozlišče primerjave, pripne nakopičen levi rezultat kot prvega otroka, razčleni naslednji operand s Parse_expr1 namesto s Parse_expr in naredi novo vozlišče levi rezultat za naslednji krog. Dve podrobnosti se dasta zlahka zmotiti pri pretvorbi rekurzije v iteracijo, obe sta v zapiskih vzdrževalcev: nakopičeno vozlišče je treba predati (lChild := Item; Item := nil) v tem vrstem redu, pot napake pa mora po sprostitvi polizgrajenega vozlišča Exit, namesto da bi padla iz zanke in vrnila obeseno drevo

Kako HotXLS razvršča številke, besedilo in logične vrednosti v primerjavi?

HotXLS razvršča mešane tipe tako kot Excel: vsaka številka je manjša od vsake tekstovne vrednosti, vsaka tekstovna vrednost pa od vsake logične vrednosti. TXLSCalculator.CompareVariants v lxCalc.pas razvrsti oba operanda s GetRetValueType v enumeracijo TXLSRetValueType = (xlVariantValue, xlNumberValue, xlStringValue, xlBooleanValue) in kadar se razreda razlikujeta, preprosto primerja njuni ordinalni vrednosti, torej je vrstni red deklaracije tega enuma pravilo čez tipe. Znotraj istega razreda je primerjava naravna, z eno Excelovo posebnostjo za besedilo: oba niza gresta najprej skozi lxUpperCase, zato je ="abc"="ABC" TRUE. To razvrščanje je razlog, zakaj rezultata verige ni mogoče sklepati brez njega. TRUE<3 ni prisiljanje TRUE v 1, temveč logična vrednost, primerjana s številko, in logična vrednost zmaga. Datumi so za pogon serijske številke (varDate se razvrsti kot xlNumberValue), zato je datum vedno pod katerim koli besedilom, vključno z besedilom, ki po naključju zgleda kot datum

Čemu je enaka prazna celica v primerjavi?

Prazna celica, uporabljena kot operand primerjave, je enaka 0, kadar je druga stran številka, enaka "", kadar je druga stran besedilo, od v2.384.53 pa enaka FALSE, kadar je druga stran logična vrednost, tako da je z prazno A1 =A1=0, =A1="" in =A1=FALSE vse TRUE. TXLSCalculator.CompareVarValues, ki oskrbuje vseh šest primerjalnih operatorjev, zamenja prazno, preden pokliče CompareVariants: če je natanko en operand Null, postane WideString(''), kadar je njegov partner niz, False, kadar je njegov partner logična vrednost, sicer pa 0. Dve prazni se še vedno primerjata enaki med seboj brez zamenjave. Aritmetična pot je prazno vedno spremenila v 0, zato je =A1+1 dalo 1, CompareVariants pa ohranja Null kot lastno najnižjo uvrstitev, pod vsako številko, primerjalni operatorji pa so to uvrstitev uporabljali neposredno

var
  Wb: IXLSWorkbook;
  Sh: TXLSWorksheet;
begin
  Wb := TXLSWorkbook.Create;
  Sh := Wb.Sheets.Add;
  Sh.Range['B1', 'B1'].Value := 5;      // A1 je namenoma puščena prazna

  Writeln(VarToStr(Wb.Calculate('=A1=0')));    // True
  Writeln(VarToStr(Wb.Calculate('=A1=""')));   // True
  Writeln(VarToStr(Wb.Calculate('=A1<B1')));   // True: prazna se primerja kot 0
  Writeln(VarToStr(Wb.Calculate('=A1<0')));    // False; True pred v2.384.3
end;

Zadnja vrstica je tista, ki je bolela v praksi. Pod staro uvrstitvijo je bila prazna manjša od vsake številke, tudi negativne, zato je =IF(A1<0,"overdrawn","ok") vsako prazno celico stanja označila kot prekoračeno, =A1=0 pa je bilo FALSE za celico, ki bi jo vsak uporabnik opisal kot nič. Ena meja je ostala po v2.384.3: zamenjava je izbirala le med 0 in prazenim nizom, tako da je prazna, primerjana z logično vrednostjo, postala 0, ki je uvrščena pod TRUE in FALSE, =A1=FALSE na prazni A1 pa se je ovrednotilo v FALSE. Od HotXLS 2.384.53 se prazna, primerjana z logično vrednostjo, obravnava kot FALSE v obeh pogonih, XLS in XLSX, tako kot v Excelu: s prazno A1 =A1=FALSE in =A1<TRUE vrneta TRUE, =A1=TRUE pa vrne FALSE. To pomeni tudi, da primerjava ne more ločiti prazne od FALSE, niti v Excelu niti v HotXLS; kadar list potrebuje to ločevanje, preizkusite z ISBLANK ali =A1=""

Zakaj je SUMIF z obsegom vsote ene celice vrnil 0?

SUMIF je vrnil 0, ker je HotXLS stisnil iteracijo na manjšega od obeh obsegov, Excel pa obdrži obliko obsega pogojev in uporabi obseg vsote le za njegovo celico zgoraj levo. =SUMIF(A1:A10,">5",B1) torej pomeni B1:B10 v Excelu, priročnost, na katero se zanaša marsikatera ročno zgrajena predloga. Skupna delovna funkcija TXLSCalculator.GetValueItemRange2 je svoji števili vrstic in stolpcev krčila na tiste obsega vrednosti, kar je primer zreduciralo na en sam preizkus A1 proti B1. v2.384.3 odstrani stisk: zanka zdaj obide obseg pogojev in prebere vsako vrednost na istem odmiku od kota zgoraj levo obsega vsote. Ker CalcSumIF in CalcAverageIF oba pokličeta to funkcijo, dobi AVERAGEIF isto razširitev, obseg vsote, večji od obsega pogojev, pa se iz istega razloga obreže na obliko pogojev. Argument pogojev na sredini je argument razreda vrednosti, zunanja dva pa sta razreda sklica, ločevanje, ki ga pokriva članek o implicitnem preseku in razredih 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;          // stolpec pogojev: 1..10
      Sheet.Cells[Row, 2].Value := Row * 100;    // zneski: 100..1000
    end;
    Sheet.Cells[1, 4].Formula := '=SUMIF(A1:A10,">5",B1)';     // obseg vsote ene celice
    Sheet.Cells[2, 4].Formula := '=SUMIF(A1:A10,">5",B1:B10)'; // izrecen obseg vsote
    if Book.Recalculate = lxOk then
      // Tako D1 kot D2 sta 4000 (600+700+800+900+1000); D1 je bilo 0 pred v2.384.3
      Writeln(VarToStr(Sheet.Cells[1, 4].Value), ' ', VarToStr(Sheet.Cells[2, 4].Value));
  finally
    Book.Free;
  end;
end;

INDIRECT in YEARFRAC: dva tišja popravka

INDIRECT zdaj spoštuje svoj drugi argument, besedilo za veljavnim sklicem pa je napaka, namesto da bi bilo ignorirano. Z a1 FALSE se besedilo razčleni kot absolutni R1C1, zato =INDIRECT("R2C3",FALSE) prebere C2; stara koda je ignorirala zastavico, prebrala "R2" kot stolpec R, vrstica 2, in tiho vrnila napačno celico. Zastavica se razdeli po svoji variantski vrsti (logična vrednost, številka ali besedilo), ker neposredna pretvorba niznega variant v Double sproži izjemo. Relativno besedilo R1C1, kot je R[1]C[1], vrne #REF!, saj INDIRECT nima izhodišča formulne celice, proti kateremu bi ga razrešil, besedilo A1 z znaki na repu, "B2 junk", pa prav tako vrne #REF!. YEARFRAC z osnovo 0 zdaj uporabi pravila NASD o zadnjem februarskem dnevu, ki jih DAYS360 že izvaja: kadar sta oba datuma zadnji dan februarja, postane zaključni dan 30, nato pa začetek na zadnji dan februarja postane 30. Od 2024-02-29 do 2025-02-28 je štetje zdaj 360 dni, ulomek točno 1, kjer je prejšnji Days360US štel 359

Kaj jamčijo ti popravki in katera je bila lekcija?

Obnašanje primerjalnih verig jamči preizkus, ki primerja oba pogona z vrednostmi, izmerjenimi v Excel 16, ta preizkus pa obstaja, ker je bil prvi opis popravka napačen. Opomnica k izdaji v2.384.3 je prvotno rekla, da je zlaganje od leve proti desni naredilo =1<2<3 TRUE, kar je natanko to, kar je izdelal stari desno-asociativni razčlenjevalnik, in nasprotje tistega, kar vračata Excel in nova koda. Nihče ni ovrednotil primera; napisan je bil iz intuicije, da je »1 manjše od 2, 2 pa manjše od 3«. Opomnica je bila popravljena, preizkus s sedmimi formulami pa dodan v nadaljnjem commitu, pravilo, ki je iz tega zraslo, pa velja za vsakogar, ki dokumentira semantiko preglednic: primer poženite v Excelu, preden zapišete pričakovano vrednost. Zamenjava praznega operanda in razširitev SUMIF sledita istemu obnašanju Excel, vključno s primerom prazna proti logični vrednosti od v2.384.53, pogojne agregacije, ki morajo preskočiti tudi filtrirane ali skrite vrstice, pa sledijo ločenim pravilom v članku o skritih vrsticah pri SUBTOTAL in AGGREGATE

HotXLS je izvorna komponenta preglednic za Delphi in C++Builder, ki bere, ponovno izračuna in zapiše XLS, XLSX, ODS in CSV brez nameščenega Excela, pravila primerjave, prazne celice in SUMIF, opisana tukaj, pa živijo v računskem pogonu, ki ga delita obe arhitekturi delovnih zvezkov. Celoten seznam funkcij in licenčne možnosti so na strani izdelka komponenta preglednic HotXLS za Delphi