HotXLS implicitna intersekcija definisanih imena u Delphiju

Definisano ime koje se odnosi na celu kolonu Excel čita kao jednu ćeliju kada se pojavi u skalarnoj poziciji: =Vertical+1 u redu 7 znači „ćelija imena Vertical u redu 7", a ne cela oblast. HotXLS Delphi Component primenjuje tu implicitnu intersekciju u v2.382.4 na dva nivoa, pri evaluaciji i pri izvlačenju zavisnosti, jer je predložak kredita sa 4805 formula pokazao da tačna vrednost nije dovoljna. Kada prolaz kroz zavisnosti razvije ime u punu oblast, formula nizvodno koja napaja bilo koju ćeliju te oblasti zatvara ciklus koji ne postoji, i TXLSXWorkbook.Recalculate odbija celu radnu svesku

Predložak o kojem je reč je radna sveska za amortizaciju kredita. Sa svakom keširanom vrednošću zatrovanom na 777 i punim Recalculate pokretanjem, obe arhitekture engine-a vratile su 23, što je lxErrorRef, kod za kružnu referencu. 3842 od 4805 formula nisu se poklopile sa nezavisnim očekivanjem, B18 je držao #VALUE!, E18 je i dalje bio 777, a broj rata u J7 pročitao je placeholdere u nedovršenoj koloni salda. Tri odvojena defekta krila su se iza jednog povratnog koda, i ovaj članak prolazi kroz svaki uz izvorni kod koji ga je ispravio

Zašto skalarna referenca na ime kolone stvara lažni ciklus?

Zato što graf zavisnosti poznaje samo grane, a grana od formule do oblasti sa 480 redova je 480 grana, od kojih jedna vodi nazad kroz ćeliju koja zavisi od te formule. Uzmite =IF(TRUE,Vertical+1,0) u B1 sa Vertical definisanim kao Inputs!$A$1:$A$2, i =B1+1 u A2. Excel evaluira B1 kao A1+1 a A2 kao B1+1, pravi lanac. Prolaz koji zabeleži da B1 zavisi od A1:A2 čini A2 prethodnikom B1, A2 već navodi B1 kao prethodnika, i Kahn red koji pokreće inkrementalno preračunavanje u HotXLS-u nikad ne vidi nijedan čvor sa ulaznim stepenom nula. Od tog obrasca su napravljeni predlošci kredita: svaki red perioda referencira imenovane kolone za saldo, kamatu i broj rata, svako ime pokriva ceo plan otplate, a svaki red i upisuje u te kolone. Razvijte imena i graf je jedna ogromna jako povezana komponenta. Evaluirajte ih sa implicitnom intersekcijom i graf je skup kratkih lanaca, po jedan za svaki red, što je i ono što ECMA-376 Part 1 §18.17.2 opisuje za referentni operand koji se koristi tamo gde se traži jedna vrednost

var
  Book: TXLSXWorkbook;
  Sheet: TXLSXWorksheet;
begin
  Book := TXLSXWorkbook.Create;
  try
    Sheet := Book.Sheets.Add('Inputs');
    Book.DefinedNames.Add('Vertical', 'Inputs!$A$1:$A$2');
    Book.DefinedNames.Add('Alias', '=Vertical');
    Sheet.Cells[1, 1].Value := 1;
    // Skalarna pozicija: Vertical se svodi na A1 jer je formula u redu 1
    Sheet.Cells[1, 2].Formula := '=IF(TRUE,Vertical+1,0)';
    Sheet.Cells[2, 1].Formula := '=B1+1';
    // Ime čija je definicija drugo ime i dalje trpi intersekciju, pa je ovo A2
    Sheet.Cells[2, 2].Formula := '=Alias';
    // Argument referentne klase: cela oblast se sabira, bez intersekcije
    Sheet.Cells[3, 2].Formula := '=SUM(Vertical)';
    // Red 6 leži izvan A1:A2, intersekcija je prazna i IFERROR je hvata
    Sheet.Cells[6, 2].Formula := '=IFERROR(Vertical,42)';

    if Book.Recalculate = lxOk then
    begin
      // B1 = 2, A2 = 3, B2 = 3, B3 = 4, B6 = 42
      // Pre v2.382.4 ova grana je bila nedostupna: B1 -> A2 -> B1 bio je ciklus
    end;
  finally
    Book.Free;
  end;
end;

Kako HotXLS odlučuje da je argument skalaran?

HotXLS čita odgovor iz tabele funkcija, a ne iz oblika argumenta. Svaki unos u TXLSFormula.InitFuncHash registruje se preko THashFunc.SetValue sa opcionim stringom klase po argumentu: 'IF' nosi '100', 'SUMIF' nosi '010', 'VLOOKUP' nosi '1011', a 'SUM' ne nosi nijedan, pa svi njegovi argumenti padaju na klasu 0 na nivou funkcije. Nova TXLSFormula.FunctionArgumentClass(APtg, AArgument) izlaže taj bajt preko THashFuncEntry.ArgClass, a rezultat 1 znači klasu vrednosti. To su iste tri klase koje [MS-XLS] §2.2.2 dodeljuje tokenima operanada, i enkoder je već zavisio od njih: kada piše referencu, računa ptg kao $24 + $20 * aClass, što daje PtgRef za klasu 0, PtgRefV za klasu 1 i PtgRefA za klasu 2. BIFF fajl koji je napisao Excel čuva tu klasu u svakom referentnom tokenu, pa engine čija se tabela poklapa sa specifikacijom može da odgovori „da li je ovaj argument skalaran" bez gledanja u podatke. Srednji argument funkcije SUMIF je kriterijum, vrednost; prvi i treći su oblasti, reference. SUMPRODUCT je registrovan sa klasom 2 na nivou funkcije, array, i zato =SUMPRODUCT(Vertical,Vertical) i dalje množi celu oblast

Tri funkcije ne konsultuju sopstveni unos u tabeli za bilo šta posle prvog argumenta. IF (ptg 1), CHOOSE (ptg 100) i IFERROR (ptg 255) propuštaju kroz sebe ono što izaberu, pa njihovi argumenti grana nasleđuju klasu pozicije koju sama funkcija zauzima. To jedno pravilo omogućava da =CHOOSE(1,Vertical,0) u G2 razreši na A2 dok =SUMIF(Vertical,">0",Vertical) pored njega i dalje sabira oba reda, i to pravilo plan amortizacije najviše koristi, jer se njegove ćelije perioda oslanjaju na IF da provere da li je kredit još otvoren

Nošenje klase kroz prolaz kroz zavisnosti

Ekstraktor zavisnosti u lxCalc.pas je rekurzivni Walk nad kompajliranim sintaksnim stablom, i postoji dvaput, jednom u TXLSCalculator.ExtractDependencies za graf na nivou radne sveske i jednom u ExtractWorkspaceDependencies za graf između radnih sveska. v2.382.4 daje obama prolazima dva dodatna parametra. AScalar počinje kao True u korenu formule, ponovo se računa za svako dete funkcije iz FunctionArgumentClass, i prosleđuje se nepromenjen za argumente grane ptg 1, 100 i 255. ANameRoot postaje True samo kada prolaz zaroni u kompajliranu definiciju imena, i preživljava samo kroz SA_GROUP čvorove, zagrade, pa ime definisano kao =A1:A2+1 nije pomešano sa običnom oblašću. Kada su obe zastavice True u čvoru SA_RANGE, AddResolvedRange svodi oblast istim helper-om koji koristi i evaluator pre nego što zabeleži zavisnost. Helper je dovoljno kratak da se citira ceo

function IntersectNamedScalarRange(CurRow, CurCol: Integer;
  var Row1, Row2, Col1, Col2: Integer): Boolean;
begin
  Result := False;
  if (Row1 = Row2) and (Col1 = Col2) then Exit(True);   // već je ćelija
  if (Col1 = Col2) and (CurRow >= Row1) and (CurRow <= Row2) then
  begin
    Row1 := CurRow; Row2 := CurRow;                     // jedna kolona: uzmi ovaj red
    Exit(True);
  end;
  if (Row1 = Row2) and (CurCol >= Col1) and (CurCol <= Col2) then
  begin
    Col1 := CurCol; Col2 := CurCol;                     // jedan red: uzmi ovu kolonu
    Result := True;
  end;
end;

Sve što helper odbije, dvodimenzionalna oblast, referenca na više listova ili formula čiji red leži izvan imenovane kolone, daje #VALUE! na strani evaluacije i nikakvu zavisnost na strani grafa, što je i ono što Excel radi za praznu intersekciju. Strana evaluacije živi u TXLSCalculator.GetValueItemName: skida SA_GROUP omotače sa kompajlirane definicije, i ako je koren SA_RANGE, poziva GetRangeInfo, primenjuje intersekciju i dohvata tu jednu ćeliju preko FGetValue umesto da evaluira celu definiciju. Eksterne reference ostaju na staroj putanji, jer nema lokalnog reda sa kojim bi se presekle. Odakle inače dolaze smeštanje i opseg imena pokriveno je u članku o definisanim imenima i formulama između listova; ovde je poenta samo šta engine radi kada se ime razreši

Zašto je MATCH nad poluizračunatom kolonom pročitao 777?

Zato što je argument lookup-array funkcije MATCH scan referenca, a scan reference su namerno izuzete iz redosleda evaluacije. Članak o lookup scan-u uveo je TXLSDepRange.LookupScan i završio odeljkom o tome šta gubite izuzimanjem scan grana iz ordenacije: lookup formula može da se izvrši pre nego što se svaka ćelija u njenoj oblasti preračuna i da pročita zastarele vrednosti. U interaktivnoj sesiji to se slegne u sledećem prolazu. U batch preračunavanju zatrovanog predloška ne, i PaymentCount, definisan kao =MATCH(0.01,Balances,-1)+1, pročitao je 777 placeholdera koji su još stajali u koloni salda i vratio broj perioda koji nije mogao biti tačan

TXLSDepGraph.TopoOrder sada tretira scan grane kao meke grane ordenacije. Uz tvrdi ulazni stepen drži i niz ScanInDeg, brojeći prljave scan prethodnike po čvoru i umanjujući ga kako se ti prethodnici emituju, koristeći liste ScanPrecedents, ScanDependents i ScanPrecedentCount koje je prethodna izmena već čuvala. U svakoj iteraciji Kahn red pregleda svoj spremni prozor za prvim čvorom čiji je ScanInDeg nula i prebacuje ga na čelo; ako svaki spremni čvor još čeka scan prethodnika, čelo se skida u svom stabilnom redosledu. Scan grane nikad ne ulaze u tvrdi ulazni stepen, pa je samo-referencirajući VLOOKUP nad sopstvenom kolonom i dalje legalan, ali lookup koji je mogao da čeka prethodnika koji se može završiti sada i čeka. Regresija koja ovo prikiva, LookupScan_WaitsForDirtyFormulaValues, zatruje tri ćelije salda na 777 i očekuje da PaymentCount vrati 3, zatim obrće ulaz na nulu i očekuje da =IFERROR(PaymentCount,99) vidi #N/A i vrati 99

Odakle je došlo skraćivanje na četiri decimale?

Iz Delphi Variant aritmetike, i to samo u ugnježdenim pozicijama. Binarni operatori u TXLSCalculator.GetValueItem već su kopirali + ili - na vrhu u dve Double lokalne promenljive, pa je =B1-A1 bilo u redu. Unutar =IF(TRUE,B1-A1,0) isto oduzimanje izvršavalo se kao Value := Value - SubValue nad dva Variant-a, a kada je jedan operand bila Int64 vrednost ćelije a drugi Double, rezultat koji smo videli bio je Currency, fiksno-tačkasti tip sa četiri decimale, pa je 1066.1854641400994 minus 120 vraćeno skraćeno na četiri decimale. Kroz plan u kojem se svaka rata složi od prethodnog reda, ta greška prođe kroz stotine perioda pre nego što stigne do zbirova

// TXLSCalculator.GetValueItem, grana za binarnu aritmetiku (lxCalc.pas)
if VarIsNull(Value) then Value := 0;
if VarIsNull(SubValue) then SubValue := 0;
// Mešovita Int64/Double Variant aritmetika može da se promoviše u Currency.
// Spreadsheet aritmetika mora da zadrži preciznost pokretnog zareza.
if VarIsNumeric(Value) then Value := Double(Value);
if VarIsNumeric(SubValue) then SubValue := Double(SubValue);

Zaštita se izvršava pre SA_ADD, SA_SUB, SA_MUL i SA_DIV podjednako, a regresija Arithmetic_MixedInt64AndDoubleKeepsPrecision smešta Int64(120) u A1 i 1066.1854641400994 u B1, zatim proverava ugnježdenu razliku i zbir na 1E-10 a proizvod i količnik na 1E-8 i 1E-12. HotXLS ne tvrdi da zna svako pravilo promocije koje RTL primenjuje na mešovite Variant tipove kroz verzije kompajlera; tvrdi da je spreadsheet aritmetika IEEE double, i sada oba operanda čini double pre nego što ih operator vidi, čime pitanje uklanja

Šta ispravka garantuje, a šta ne

Posle v2.382.4 obe arhitekture engine-a vraćaju lxOk za zatrovani predložak, svih 4805 keširanih vrednosti poklapa se sa nezavisnim očekivanjem red po red unutar 1E-7, a tvrdnje da su keševi zaista bili zatrovani, da je hash izvora nepromenjen i da je svaka formula još prisutna sve stoje. Nijedna iteracija nije uključena i nijedan kod greške nije potisnut da bi se to postiglo. Pravi ciklus kroz ime, =B1 u A1 dok B1 još čita Vertical, i dalje vraća grešku, i test NamedScalarRanges_IntersectWithoutFalseCycles završava se upravo takvom tvrdnjom

Granice vredi reći jasno. Implicitna intersekcija primenjuje se samo na ime čija je kompajlirana definicija, po skidanju zagrada, oblast jedne kolone ili jednog reda na jednom listu; dvodimenzionalno ime u skalarnoj poziciji daje #VALUE!, kao u Excel-u, a funkcija koju tabela ne poznaje dobija klasu 0 iz FunctionArgumentClass, pa se njeni argumenti imena i dalje razvijaju u punoj meri. Meko ordeniranje je preferencija, ne garancija: ciklus samo od scan grana i dalje se evaluira u stabilnom redosledu i čita ono što je keširano, što je ponašanje koje je članak o lookup scan-u namerno prihvatio. A rezultat celog predloška verifikovan je prema nezavisnoj skripti očekivanja, ne prema drugom spreadsheet engine-u, jer referentni office paket nije završio preračunavanje originalnog predloška u budžetu od 60 sekundi. HotXLS je nativna Delphi i C++Builder spreadsheet komponenta koja čita, preračunava i piše XLS, XLSX, ODS i CSV bez instaliranog Excel-a; intersekcija imena, tabela klasa argumenata i meko scan ordeniranje primenjuju se na svaki format jer je engine za izračunavanje deljen, a trenutna pokrivenost funkcija navedena je na stranici HotXLS Delphi spreadsheet komponente