Apibrėžtieji pavadinimai HotXLS: numanoma sankirta Delphi

Apibrėžtąjį pavadinimą, nurodantį į visą stulpelį, Excel skaito kaip vieną ląstelę, kai jis atsiduria skaliarinėje pozicijoje: =Vertical+1 7 eilutėje reiškia „Vertical 7-os eilutės ląstelę", o ne visą sritį. HotXLS Delphi Component tą numanomą sankirtą taiko nuo v2.382.4 dviem lygmenimis — vertinimo metu ir ištraukiant priklausomybes — nes paskolos šablonas su 4805 formulėmis parodė, kad teisingos reikšmės nepakanka. Kai priklausomybių ėjiklis išplečia pavadinimą į visą jo sritį, tolesnė formulė, maitinanti bet kurią tos srities ląstelę, uždaro ciklą, kurio nėra, ir TXLSXWorkbook.Recalculate atsisako visos darbaknygės

Minėtas šablonas yra įprasta paskolos amortizacijos darbaknygė. Kai kiekviena podėlyje laikoma reikšmė užnuodijama 777, o paleidžiamas pilnas Recalculate, abi variklio architektūros grąžino 23, tai yra lxErrorRef — ciklinės nuorodos kodas. 3842 iš 4805 formulių nesutapo su nepriklausomu lūkesčiu, B18 laikė #VALUE!, E18 tebebuvo 777, o mokėjimų skaičius J7 buvo perskaitęs rezervuotas vietas nebaigtame balanso stulpelyje. Už vieno grąžinimo kodo slėpėsi trys atskiri defektai, ir šis straipsnis kiekvieną iš jų pereina kartu su jį sutvarkiusiu kodu

Kodėl skaliarinė nuoroda į stulpelio pavadinimą sukuria klaidingą ciklą?

Nes priklausomybių grafas pažįsta tik briaunas, o briauna nuo formulės į 480 eilučių sritį yra 480 briaunų, ir viena iš jų veda atgal per ląstelę, kuri priklauso nuo tos formulės. Įsivaizduokite =IF(TRUE,Vertical+1,0) B1 ląstelėje, kai Vertical apibrėžtas kaip Inputs!$A$1:$A$2, ir =B1+1 A2 ląstelėje. Excel B1 įvertina kaip A1+1, o A2 kaip B1+1 — tiesi grandinė. Ėjiklis, kuris B1 užregistruoja kaip priklausomą nuo A1:A2, padaro A2 B1 pirmtaku, o A2 jau ir taip išvardija B1 kaip pirmtaką, ir Kahn eilė, varanti inkrementinį perskaičiavimą HotXLS, niekada nepamato nė vieno mazgo, kurio įeinantis laipsnis būtų nulinis. Būtent iš tokio rašto ir padaryti paskolų šablonai: kiekviena periodo eilutė nurodo į pavadintus balanso, palūkanų normos ir mokėjimų skaičiaus stulpelius, kiekvienas pavadinimas apima visą grafiką, ir kiekviena eilutė dar ir rašo į tuos stulpelius. Išplėskite pavadinimus — ir grafas yra vienas milžiniškas stipriai susietas komponentas. Įvertinkite juos su numanoma sankirta — ir grafas yra trumpų grandinių rinkinys, po vieną eilutei, ką ir aprašo ECMA-376 1 dalies §18.17.2 nuorodos operandui, vartojamam ten, kur reikia vienos reikšmės

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;
    // Skaliarinė pozicija: Vertical susitraukia į A1, nes formulė yra 1 eilutėje
    Sheet.Cells[1, 2].Formula := '=IF(TRUE,Vertical+1,0)';
    Sheet.Cells[2, 1].Formula := '=B1+1';
    // Pavadinimas, kurio apibrėžimas yra kitas pavadinimas, vis tiek susikerta, tad čia A2
    Sheet.Cells[2, 2].Formula := '=Alias';
    // Nuorodos klasės argumentas: sumuojama visa sritis, sankirtos nėra
    Sheet.Cells[3, 2].Formula := '=SUM(Vertical)';
    // 6 eilutė yra už A1:A2 ribų, sankirta tuščia ir IFERROR ją pagauna
    Sheet.Cells[6, 2].Formula := '=IFERROR(Vertical,42)';

    if Book.Recalculate = lxOk then
    begin
      // B1 = 2, A2 = 3, B2 = 3, B3 = 4, B6 = 42
      // Iki v2.382.4 ši šaka buvo nepasiekiama: B1 -> A2 -> B1 buvo ciklas
    end;
  finally
    Book.Free;
  end;
end;

Kaip HotXLS nusprendžia, kad argumentas yra skaliarinis?

HotXLS atsakymą skaito iš funkcijų lentelės, o ne iš argumento formos. Kiekvienas TXLSFormula.InitFuncHash įrašas registruojamas per THashFunc.SetValue su neprivaloma kiekvieno argumento klasių eilute: 'IF' neša '100', 'SUMIF' neša '010', 'VLOOKUP' neša '1011', o 'SUM' neneša jokios, tad visi jo argumentai nusileidžia į funkcijos lygmens klasę 0. Naujasis TXLSFormula.FunctionArgumentClass(APtg, AArgument) atidengia tą baitą per THashFuncEntry.ArgClass, o rezultatas 1 reiškia reikšmės klasę. Tai tos pačios trys klasės, kurias [MS-XLS] §2.2.2 priskiria operandų tokenams, ir kodavimo įrenginys jau nuo jų priklausė: rašydamas nuorodą jis apskaičiuoja ptg kaip $24 + $20 * aClass, kas duoda PtgRef klasei 0, PtgRefV klasei 1 ir PtgRefA klasei 2. Excel rašomas BIFF failas tą klasę saugo kiekviename nuorodos tokene, tad variklis, kurio lentelė atitinka specifikaciją, gali atsakyti, ar šis argumentas skaliarinis, net nepažvelgęs į duomenis. Vidurinis SUMIF argumentas yra kriterijus, reikšmė; pirmas ir trečias yra sritys, nuorodos. SUMPRODUCT užregistruotas su funkcijos lygmens klase 2, masyvo, ir todėl =SUMPRODUCT(Vertical,Vertical) vis tiek sudaugina visą sritį

Trys funkcijos dėl nieko, kas eina po pirmo argumento, nesikreipia į savo lentelės įrašą. IF (ptg 1), CHOOSE (ptg 100) ir IFERROR (ptg 255) praleidžia tai, ką pasirenka, tad jų šakų argumentai paveldi klasę pozicijos, kurią užima pati funkcija. Būtent ši viena taisyklė leidžia =CHOOSE(1,Vertical,0) G2 ląstelėje išsispręsti į A2, o šalia esančiam =SUMIF(Vertical,">0",Vertical) vis tiek sumuoti abi eilutes, ir tai yra taisyklė, kurią amortizacijos grafikas naudoja dažniausiai, nes jo periodų ląstelės remiasi IF, tikrindamos, ar paskola dar atvira

Klasės pernešimas per priklausomybių ėjimą

Priklausomybių ištraukėjas lxCalc.pas faile yra rekursyvus Walk per sukompiliuotą sintaksės medį, ir jis egzistuoja du kartus: vienas TXLSCalculator.ExtractDependencies darbaknygės grafui, kitas ExtractWorkspaceDependencies tarpdarbaknyginiam grafui. v2.382.4 abiem ėjikliams duoda po du papildomus parametrus. AScalar prasideda kaip True formulės šaknyje, iš naujo apskaičiuojamas kiekvienam funkcijos vaikui iš FunctionArgumentClass, o ptg 1, 100 ir 255 šakų argumentams perduodamas nepakitęs. ANameRoot tampa True tik tada, kai ėjiklis nusileidžia į sukompiliuotą pavadinimo apibrėžimą, ir išlieka tik per SA_GROUP mazgus, t. y. skliaustus, tad pavadinimas, apibrėžtas kaip =A1:A2+1, nėra palaikomas paprasta sritimi. Kai abu požymiai ties SA_RANGE mazgu yra True, AddResolvedRange susiaurina sritį tuo pačiu pagalbiniu metodu, kurį naudoja vertintuvas, prieš užregistruodamas priklausomybę. Metodas pakankamai trumpas, kad jį būtų galima pacituoti visą

function IntersectNamedScalarRange(CurRow, CurCol: Integer;
  var Row1, Row2, Col1, Col2: Integer): Boolean;
begin
  Result := False;
  if (Row1 = Row2) and (Col1 = Col2) then Exit(True);   // jau ląstelė
  if (Col1 = Col2) and (CurRow >= Row1) and (CurRow <= Row2) then
  begin
    Row1 := CurRow; Row2 := CurRow;                     // vienas stulpelis: imti šią eilutę
    Exit(True);
  end;
  if (Row1 = Row2) and (CurCol >= Col1) and (CurCol <= Col2) then
  begin
    Col1 := CurCol; Col2 := CurCol;                     // viena eilutė: imti šį stulpelį
    Result := True;
  end;
end;

Viskas, ką metodas atmeta — dvimatė sritis, kelių lapų nuoroda ar formulė, kurios eilutė yra už pavadinto stulpelio ribų — vertinimo pusėje duoda #VALUE!, o grafo pusėje jokios priklausomybės, ką Excel ir daro su tuščia sankirta. Vertinimo pusė gyvena TXLSCalculator.GetValueItemName: jis nuo sukompiliuoto apibrėžimo nuima SA_GROUP apvalkalus, ir jei šaknis yra SA_RANGE, kviečia GetRangeInfo, susikerta ir paima tą vieną ląstelę per FGetValue, o ne vertina visą apibrėžimą. Išorinės nuorodos lieka senajame kelyje, nes nėra su kuo susikirsti vietinėje eilutėje. Iš kur iš viso atsiranda pavadinimo saugykla ir apimtis, aptarta straipsnyje apie apibrėžtuosius pavadinimus ir tarpdarbalapines formules; čia svarbu tik tai, ką variklis daro, kai pavadinimas jau išsisprendžia

Kodėl MATCH per pusiau perskaičiuotą stulpelį perskaitė 777?

Nes MATCH paieškos masyvo argumentas yra scan nuoroda, o scan nuorodos buvo sąmoningai pašalintos iš vertinimo tvarkos. Straipsnis apie paieškos scan pristatė TXLSDepRange.LookupScan ir baigėsi skyriumi „Ko atsisakote pašalindami scan briaunas iš rikiavimo": paieškos formulė gali įvykti anksčiau, nei perskaičiuota kiekviena jos srities ląstelė, ir perskaityti pasenusias reikšmes. Interaktyvioje sesijoje tai susitvarko kitame ėjime. Paketiniame užnuodyto šablono perskaičiavime — ne, ir PaymentCount, apibrėžtas kaip =MATCH(0.01,Balances,-1)+1, perskaitė 777 rezervuotas vietas, tebesisėdėjusias balanso stulpelyje, ir grąžino periodo skaičių, kuris niekaip negalėjo būti teisingas

TXLSDepGraph.TopoOrder dabar scan briaunas traktuoja kaip minkštas rikiavimo briaunas. Šalia kietojo įeinančio laipsnio jis laiko ScanInDeg masyvą, skaičiuojantį nešvarius scan pirmtakus kiekvienam mazgui ir mažinantį jį tiems pirmtakams išsirikiuojant, naudodamas ScanPrecedents, ScanDependents ir ScanPrecedentCount sąrašus, kuriuos ankstesnis pakeitimas jau saugojo. Kiekvienoje iteracijoje Kahn eilė peržvelgia savo paruoštų mazgų langą ir suranda pirmą mazgą, kurio ScanInDeg lygus nuliui, bei perstato jį į priekį; jei kiekvienas paruoštas mazgas vis dar laukia scan pirmtako, priekinis mazgas išimamas savo stabilia tvarka. Scan briaunos niekada nepatenka į kietąjį įeinantį laipsnį, tad save nurodantis VLOOKUP per savo paties stulpelį vis tiek yra teisėtas, o paieška, kuri galėtų palaukti užbaigiamo pirmtako, dabar palaukia. Regresija, kuri tai įtvirtina, LookupScan_WaitsForDirtyFormulaValues, užnuodija tris balanso ląsteles 777 ir tikisi, kad PaymentCount grįš kaip 3, paskui įvestį paverčia nuliu ir tikisi, kad =IFERROR(PaymentCount,99) pamatys #N/A ir grąžins 99

Iš kur atsirado keturių skaičių po kablelio apkarpymas?

Iš Delphi Variant aritmetikos, ir tik įdėtose pozicijose. Dvejetainiai operatoriai TXLSCalculator.GetValueItem jau nusikopijuodavo viršutinio lygmens + ar - į du Double vietinius kintamuosius, tad =B1-A1 buvo tvarkingai. O =IF(TRUE,B1-A1,0) viduje tas pats atėmimas vyko kaip Value := Value - SubValue su dviem Variant, ir kai vienas operandas buvo Int64 ląstelės reikšmė, o kitas Double, stebėtas rezultatas buvo Currency — fiksuoto kablelio tipas su keturiais skaičiais po kablelio, tad 1066.1854641400994 minus 120 grįžo apkarpytas iki keturių skaičių po kablelio. Grafike, kuriame kiekvienas mokėjimas sudaromas iš ankstesnės eilutės, ta klaida nukeliauja per šimtus periodų, kol pasiekia sumas

// TXLSCalculator.GetValueItem, dvejetainės aritmetikos šaka (lxCalc.pas)
if VarIsNull(Value) then Value := 0;
if VarIsNull(SubValue) then SubValue := 0;
// Mišri Int64/Double Variant aritmetika gali pakilti į Currency.
// Skaičiuoklės aritmetika privalo išlaikyti slankiojo kablelio tikslumą.
if VarIsNumeric(Value) then Value := Double(Value);
if VarIsNumeric(SubValue) then SubValue := Double(SubValue);

Ši apsauga veikia prieš SA_ADD, SA_SUB, SA_MUL ir SA_DIV vienodai, o regresija Arithmetic_MixedInt64AndDoubleKeepsPrecision įrašo Int64(120) į A1 ir 1066.1854641400994 į B1, paskui tikrina įdėtą atėmimą ir sumą 1E-10 tikslumu, o sandaugą ir dalmenį — 1E-8 ir 1E-12. HotXLS nesiryžta teigti, kad žino visas pakėlimo taisykles, kurias RTL taiko mišriems Variant tipams įvairiose kompiliatoriaus versijose; jis teigia, kad skaičiuoklės aritmetika yra IEEE double, ir dabar abu operandus padaro double, kol operatorius juos pamato, o tai tą klausimą panaikina

Ką pataisa garantuoja, o ko — ne

Po v2.382.4 abi variklio architektūros užnuodytam šablonui grąžina lxOk, visos 4805 podėlyje laikomos reikšmės sutampa su nepriklausomu eilutė-po-eilutės lūkesčiu 1E-7 tikslumu, o teiginiai, kad podėliai tikrai buvo užnuodyti, kad šaltinio maišas nepakitęs ir kad kiekviena formulė tebėra vietoje, visi galioja. Tam pasiekti nebuvo įjungta jokia iteracija ir nebuvo nuslopintas joks klaidos kodas. Tikras ciklas per pavadinimą — =B1 A1 ląstelėje, kai B1 vis dar skaito Vertical — vis tiek grąžina klaidą, ir testas NamedScalarRanges_IntersectWithoutFalseCycles baigiasi būtent tuo teiginiu

Ribas verta išdėstyti tiesiai. Numanoma sankirta taikoma tik pavadinimui, kurio sukompiliuotas apibrėžimas, nuėmus skliaustus, yra vieno stulpelio arba vienos eilutės sritis viename lape; dvimatis pavadinimas skaliarinėje pozicijoje duoda #VALUE!, kaip ir Excel, o funkcija, kurios lentelė nepažįsta, iš FunctionArgumentClass gauna klasę 0, tad jos pavadinimų argumentai vis tiek išplečiami ištisai. Minkštasis rikiavimas yra pageidavimas, o ne garantija: ciklas, sudarytas vien iš scan briaunų, vis tiek vertinamas stabilia tvarka ir skaito tai, kas yra podėlyje, ir tai yra elgesys, kurį straipsnis apie paieškos scan sąmoningai priėmė. O viso šablono rezultatas tikrinamas pagal nepriklausomą lūkesčių scenarijų, o ne pagal kitą skaičiuoklės variklį, nes orientacinis biuro paketas nebaigė perskaičiuoti originalaus šablono per 60 sekundžių biudžetą. HotXLS yra savo Delphi ir C++Builder skaičiuoklės komponentas, skaitantis, perskaičiuojantis ir rašantis XLS, XLSX, ODS bei CSV be įdiegto Excel; pavadinimų sankirta, argumentų klasių lentelė ir minkštasis scan rikiavimas galioja visiems formatams, nes skaičiavimo variklis yra bendras, o dabartinė funkcijų aprėptis išvardyta HotXLS Delphi spreadsheet komponento produkto puslapyje