HotXLS в Delphi: implicit intersection при defined names

Defined name, който сочи към цяла колона, Excel прочита като една клетка, когато се появи в скаларна позиция: =Vertical+1 в ред 7 означава „клетката на ред 7 на Vertical“, не цялата област. HotXLS Delphi Component прилага този implicit intersection в v2.382.4 на две нива — при оценяване и при извличане на зависимости — защото кредитен шаблон с 4805 формули показа, че правилната стойност не стига. Когато dependency walker-ът разгъне името до пълната му област, формула надолу по веригата, която захранва която и да е клетка на тази област, затваря цикъл, който не съществува, а TXLSXWorkbook.Recalculate отказва цялата работна книга

Става дума за стандартна работна книга за амортизация на кредит. С всяка кеширана стойност отровена на 777 и пълно изпълнение на Recalculate, и двете архитектури на engine-а върнаха 23, тоест lxErrorRef — кода за circular reference. 3842 от 4805 формули не съвпаднаха с независимото очакване, B18 държеше #VALUE!, E18 все още беше 777, а броят плащания в J7 беше прочел placeholder-ите в недовършена колона със салдо. Три отделни дефекта се криеха зад един return код, и тази статия минава през всеки с кода, който го оправи

Защо скаларна референция към име на колона създава фалшив цикъл?

Защото dependency граф познава само ребра, а ребро от формула към област от 480 реда са 480 ребра, едно от които сочи обратно през клетка, зависеща от формулата. Вземете =IF(TRUE,Vertical+1,0) в B1 с Vertical дефинирано като Inputs!$A$1:$A$2 и =B1+1 в A2. Excel оценява B1 като A1+1 и A2 като B1+1 — права верига. Walker, който записва B1 като зависещ от A1:A2, прави A2 precedent на B1, A2 вече води B1 за precedent, а Kahn опашката, която задвижва инкременталното преизчисляване в HotXLS, никога не вижда нито един възел да стигне in-degree нула. Това е шаблонът, от който са направени кредитните шаблони: всеки периоден ред реферира именувани колони за салдото, лихвения процент и броя плащания, всяко име обхваща цялото разписание, и всеки ред пише и в тези колони. Разгънете имената и графът е една гигантска силно свързана компонента. Оценете ги с implicit intersection и графът е набор къси вериги, по една на ред — точно това описва ECMA-376 Part 1 §18.17.2 за reference операнд, консумиран там, където се изисква една стойност

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;
    // Скаларна позиция: Vertical се свива до A1, защото формулата е в ред 1
    Sheet.Cells[1, 2].Formula := '=IF(TRUE,Vertical+1,0)';
    Sheet.Cells[2, 1].Formula := '=B1+1';
    // Име, чиято дефиниция е друго име, също се сече, така че това е A2
    Sheet.Cells[2, 2].Formula := '=Alias';
    // Аргумент от reference клас: сумира се цялата област, без сечение
    Sheet.Cells[3, 2].Formula := '=SUM(Vertical)';
    // Ред 6 е извън A1:A2, сечението е празно и IFERROR го хваща
    Sheet.Cells[6, 2].Formula := '=IFERROR(Vertical,42)';

    if Book.Recalculate = lxOk then
    begin
      // B1 = 2, A2 = 3, B2 = 3, B3 = 4, B6 = 42
      // Преди v2.382.4 този клон беше недостижим: B1 -> A2 -> B1 беше цикъл
    end;
  finally
    Book.Free;
  end;
end;

Как HotXLS решава, че даден аргумент е скаларен?

HotXLS прочита отговора от таблицата с функции, а не от формата на аргумента. Всеки запис в TXLSFormula.InitFuncHash е регистриран чрез THashFunc.SetValue с опционален per-argument class string: 'IF' носи '100', 'SUMIF' носи '010', 'VLOOKUP' носи '1011', а 'SUM' не носи нищо, така че всичките му аргументи се връщат към function-level клас 0. Новият TXLSFormula.FunctionArgumentClass(APtg, AArgument) излага този байт чрез THashFuncEntry.ArgClass, а резултат 1 означава value клас. Това са същите три класа, които [MS-XLS] §2.2.2 присвоява на operand токените, и encoder-ът вече зависеше от тях: когато записва референция, той изчислява ptg като $24 + $20 * aClass, което дава PtgRef за клас 0, PtgRefV за клас 1 и PtgRefA за клас 2. BIFF файл, записан от Excel, съхранява този клас във всеки reference токен, така че engine, чиято таблица съвпада със спецификацията, може да отговори „скаларен ли е този аргумент“ без да гледа данните. Средният аргумент на SUMIF е критерият — стойност; първият и третият са области, референции. SUMPRODUCT е регистриран с function-level клас 2, array, което е причината =SUMPRODUCT(Vertical,Vertical) все още да умножава цялата област

Три функции не питат собствения си запис в таблицата за нищо след първия аргумент. IF (ptg 1), CHOOSE (ptg 100) и IFERROR (ptg 255) пропускат напред това, което изберат, така че техните branch аргументи наследяват класа на позицията, която самата функция заема. Именно това едно правило позволява =CHOOSE(1,Vertical,0) в G2 да се разреши до A2, докато =SUMIF(Vertical,">0",Vertical) до него все още сумира двата реда, и именно то се упражнява най-много от амортизационно разписание, защото периодните му клетки разчитат на IF, за да тестват дали кредитът е още отворен

Пренасяне на класа през dependency обхождането

Dependency extractor-ът в lxCalc.pas е рекурсивен Walk върху компилираното syntax дърво и съществува два пъти — веднъж като TXLSCalculator.ExtractDependencies за графа на работната книга и веднъж като ExtractWorkspaceDependencies за междукнижния граф. v2.382.4 дава и на двата walker-а по два допълнителни параметъра. AScalar стартира като True в корена на формулата, преизчислява се за всяко дете-функция от FunctionArgumentClass и се предава непроменен за branch аргументите на ptg 1, 100 и 255. ANameRoot става True само когато walker-ът слезе в компилираната дефиниция на име, и оцелява само през SA_GROUP възлите — скобите — така че име, дефинирано като =A1:A2+1, не се побърква за обикновена област. Когато и двата флага са True на SA_RANGE възел, AddResolvedRange стеснява областта със същия helper, който evaluator-ът ползва, преди да запише зависимостта. Helper-ът е достатъчно кратък, за да го цитираме изцяло

function IntersectNamedScalarRange(CurRow, CurCol: Integer;
  var Row1, Row2, Col1, Col2: Integer): Boolean;
begin
  Result := False;
  if (Row1 = Row2) and (Col1 = Col2) then Exit(True);   // вече е клетка
  if (Col1 = Col2) and (CurRow >= Row1) and (CurRow <= Row2) then
  begin
    Row1 := CurRow; Row2 := CurRow;                     // една колона: вземи този ред
    Exit(True);
  end;
  if (Row1 = Row2) and (CurCol >= Col1) and (CurCol <= Col2) then
  begin
    Col1 := CurCol; Col2 := CurCol;                     // един ред: вземи тази колона
    Result := True;
  end;
end;

Всичко, което helper-ът отхвърли — двумерна област, multi-sheet референция или формула, чийто ред лежи извън именуваната колона — дава #VALUE! на страната на оценяването и никаква зависимост на страната на графа, което е точно това, което прави Excel за празно сечение. Страната на оценяването живее в TXLSCalculator.GetValueItemName: тя съблича SA_GROUP обвивките от компилираната дефиниция, и ако коренът е SA_RANGE, вика GetRangeInfo, сече и взима единствената клетка чрез FGetValue, вместо да оценява цялата дефиниция. Външните референции остават на стария път, защото няма локален ред, срещу който да се сече. Откъде идва изобщо хранилището и обхватът на едно име е разгледано в статията за defined names и междулистови формули; тук точката е само какво прави engine-ът, след като името се разреши

Защо MATCH върху полукалкулирана колона прочете 777?

Защото lookup-array аргументът на MATCH е scan reference, а scan референциите бяха нарочно изключени от реда на оценяване. Статията за lookup scan въведе TXLSDepRange.LookupScan и завърши с раздел, наречен „Какво губите, като изключите scan ребрата от подреждането“: lookup формула може да се изпълни, преди всяка клетка в нейния диапазон да е преизчислена, и да прочете стари стойности. В интерактивна сесия това се изравнява на следващото минаване. При batch преизчисляване на отровен шаблон — не, и PaymentCount, дефинирано като =MATCH(0.01,Balances,-1)+1, прочете 777 placeholder-ите, все още седящи в колоната със салдо, и върна брой периоди, който не можеше да бъде верен

TXLSDepGraph.TopoOrder вече третира scan ребрата като меки подреждащи ребра. До твърдия in-degree той пази масив ScanInDeg, броящ мръсни scan precedents на възел и намалящ ги, докато тези precedents се излъчват, ползвайки списъците ScanPrecedents, ScanDependents и ScanPrecedentCount, които по-ранната промяна вече съхраняваше. На всяка итерация Kahn опашката сканира своя ready прозорец за първия възел, чийто ScanInDeg е нула, и го разменя към главата; ако всеки ready възел още чака scan precedent, главата се изважда в нейния стабилен ред. Scan ребрата никога не влизат в твърдия in-degree, така че саморефериращ се VLOOKUP върху собствената му колона си остава легален, но lookup, който би могъл да изчака финишируем precedent, вече наистина го изчаква. Регресията, която закова това, LookupScan_WaitsForDirtyFormulaValues, отрова три клетки със салдо на 777 и очаква PaymentCount да се върне като 3, после обръща входа на нула и очаква =IFERROR(PaymentCount,99) да види #N/A и да върне 99

Откъде дойде отрязването на четири знака след десетичната запетая?

От Delphi Variant аритметиката, и само във вложени позиции. Бинарните оператори в TXLSCalculator.GetValueItem вече копираха top-level + или - в две Double локални променливи, така че =B1-A1 беше наред. Вътре в =IF(TRUE,B1-A1,0) същото изваждане вървеше като Value := Value - SubValue върху два Variant-а, и когато единият операнд беше клетъчна стойност Int64, а другият Double, резултатът, който наблюдавахме, беше Currency — тип с фиксирана точка и четири знака след десетичната запетая, така че 1066.1854641400994 минус 120 се връщаше отрязан на четири знака. В разписание, където всяко плащане се компаундира от предишния ред, тази грешка извървява стотици периоди, преди да стигне до сборовете

// TXLSCalculator.GetValueItem, клон за бинарна аритметика (lxCalc.pas)
if VarIsNull(Value) then Value := 0;
if VarIsNull(SubValue) then SubValue := 0;
// Смесена Int64/Double Variant аритметика може да се повиши до Currency.
// Аритметиката в таблиците трябва да запазва floating-point точност.
if VarIsNumeric(Value) then Value := Double(Value);
if VarIsNumeric(SubValue) then SubValue := Double(SubValue);

Guard-ът върви пред SA_ADD, SA_SUB, SA_MUL и SA_DIV еднакво, а регресията Arithmetic_MixedInt64AndDoubleKeepsPrecision записва Int64(120) в A1 и 1066.1854641400994 в B1, после проверява вложената разлика и сбор до 1E-10 и произведението и частното до 1E-8 и 1E-12. HotXLS не претендира, че знае всяко правило за повишаване на типа, което RTL прилага към смесени Variant типове из компилаторните версии; той претендира, че аритметиката в таблиците е IEEE double, и вече превръща и двата операнда в double, преди операторът да ги види, което премахва въпроса

Какво гарантира поправката и какво не

След v2.382.4 и двете архитектури на engine-а връщат lxOk за отровения шаблон, всички 4805 кеширани стойности съвпадат с независимото ред-по-ред очакване в рамките на 1E-7, а assertion-ите, че кешовете наистина са били отровени, че source hash-ът е непроменен и че всяка формула все още е на мястото си, всички пасуват. Нито една итерация не е била включвана и нито един error код не е бил потискан, за да се стигне дотам. Истински цикъл през име — =B1 в A1 с B1, все още четещ Vertical — все още връща грешка, а тестът NamedScalarRanges_IntersectWithoutFalseCycles завършва с точно това assertion

Границите си заслужават да се кажат ясно. Implicit intersection се прилага само за име, чиято компилирана дефиниция, след събличане на скобите, е едноколоночна или едноредова област на един лист; двумерно име в скаларна позиция е #VALUE!, както в Excel, а функция, която таблицата не познава, получава клас 0 от FunctionArgumentClass, така че name аргументите ѝ все още се разгъват изцяло. Мекото подреждане е предпочитание, не гаранция: цикъл само от scan ребра все още се оценява в стабилен ред и чете каквото е кеширано, което е поведението, което статията за lookup scan съзнателно прие. А резултатът за целия шаблон е проверен срещу независим очаквателен скрипт, не срещу друг табличен engine, защото reference office suite-ът не приключи преизчисляването на оригиналния шаблон в бюджет от 60 секунди. HotXLS е нативен Delphi и C++Builder spreadsheet компонент, който чете, преизчислява и записва XLS, XLSX, ODS и CSV без инсталиран Excel; сеченето на имена, таблицата с класове на аргументите и мекото scan подреждане важат за всеки формат, защото calculation engine-ът е споделен, а текущото покритие на функциите е изброено на продуктовата страница на HotXLS Delphi spreadsheet компонента