Хибні цикли імен у HotXLS: клас аргументу в Delphi

Визначене ім'я, що вказує на цілу колонку, Excel читає як одну клітинку, коли воно стоїть у скалярній позиції: =Vertical+1 у рядку 7 означає «клітинку Vertical у рядку 7», а не всю область. HotXLS Delphi Component застосовує цей неявний перетин у v2.382.4 на двох рівнях — під час обчислення й під час вилучення залежностей, — бо шаблон позики з 4805 формулами показав, що правильно отримати значення ще недостатньо. Коли обхідник залежностей розгортає ім'я на всю його область, формула нижче по потоку, яка живить будь-яку клітинку цієї області, замикає цикл, якого не існує, і TXLSXWorkbook.Recalculate відмовляється від усієї книги

Ідеться про типовий шаблон графіка погашення позики. З кожним кешованим значенням, отруєним до 777, і повним прогоном Recalculate обидві архітектури рушія повертали 23 — це lxErrorRef, код циклічного посилання. 3842 з 4805 формул не збіглися з незалежним очікуванням, у B18 сиділо #VALUE!, E18 усе ще тримало 777, а лічильник платежів у J7 вичитав плейсхолдери з незавершеної колонки балансу. За одним кодом повернення ховалися три окремі дефекти, і ця стаття розбирає кожен разом із кодом, який його виправив

Чому скалярне посилання на ім'я колонки створює хибний цикл?

Бо граф залежностей знає лише ребра, а ребро від формули до області на 480 рядків — це 480 ребер, одне з яких веде назад через клітинку, що залежить від формули. Візьміть =IF(TRUE,Vertical+1,0) у B1 з Vertical, визначеним як Inputs!$A$1:$A$2, і =B1+1 в A2. Excel обчислює B1 як A1+1, а A2 як B1+1 — прямий ланцюжок. Обхідник, який записує B1 як залежний від A1:A2, робить A2 попередником B1, A2 уже тримає B1 у списку попередників, і черга Кана, що рухає інкрементальний перерахунок у HotXLS, ніколи не побачить, щоб хоч один вузол дійшов до нульового вхідного степеня. Саме з цього патерну й зроблені шаблони позик: кожен рядок періоду посилається на іменовані колонки балансу, ставки й кількості платежів, кожне ім'я охоплює весь графік, і кожен рядок ще й пише в ці колонки. Розгорніть імена — і граф стає однією гігантською сильно зв'язною компонентою. Обчисліть їх із неявним перетином — і граф стає набором коротких ланцюжків, по одному на рядок, що й описує ECMA-376 Part 1 §18.17.2 для операнда-посилання, який споживається там, де потрібне одне значення

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';
    // Аргумент класу посилання: підсумовується вся область, без перетину
    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 з необов'язковим рядком класів на аргумент: 'IF' несе '100', 'SUMIF' несе '010', 'VLOOKUP' несе '1011', а 'SUM' не несе нічого, тож усі його аргументи відкочуються до класу 0 на рівні функції. Новий TXLSFormula.FunctionArgumentClass(APtg, AArgument) виставляє цей байт назовні через THashFuncEntry.ArgClass, і результат 1 означає клас значення. Це ті самі три класи, які [MS-XLS] §2.2.2 призначає токенам операндів, і енкодер уже на них спирався: записуючи посилання, він рахує ptg як $24 + $20 * aClass, що дає PtgRef для класу 0, PtgRefV для класу 1 і PtgRefA для класу 2. Файл BIFF, записаний Excel, зберігає цей клас у кожному токені посилання, тож рушій, чия таблиця збігається зі специфікацією, може відповісти «чи цей аргумент скалярний», не заглядаючи в дані. Середній аргумент SUMIF — це критерій, значення; перший і третій — області, посилання. SUMPRODUCT зареєстровано з класом 2 на рівні функції, масивом, саме тому =SUMPRODUCT(Vertical,Vertical) і далі перемножує всю область

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

Як клас протягується крізь обхід залежностей

Екстрактор залежностей у lxCalc.pas — це рекурсивний Walk по скомпільованому синтаксичному дереву, і він існує двічі: раз у TXLSCalculator.ExtractDependencies для графа в межах книги, і раз у ExtractWorkspaceDependencies для графа між книгами. v2.382.4 дає обом обхідникам два додаткові параметри. AScalar стартує як True в корені формули, перераховується для кожного дочірнього вузла функції з FunctionArgumentClass і передається без змін для аргументів-гілок ptg 1, 100 і 255. ANameRoot стає True лише тоді, коли обхідник спускається в скомпільоване визначення імені, і виживає тільки крізь вузли SA_GROUP — дужки, — тож ім'я, визначене як =A1:A2+1, не сплутати зі звичайною областю. Коли обидва прапорці True на вузлі SA_RANGE, AddResolvedRange звужує область тим самим хелпером, який використовує обчислювач, перш ніж записати залежність. Хелпер досить короткий, щоб навести його повністю

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;

Усе, що хелпер відкидає — двовимірна область, посилання на кілька аркушів або формула, чий рядок лежить поза названою колонкою, — дає #VALUE! на боці обчислення і жодної залежності на боці графа, що й робить Excel для порожнього перетину. Бік обчислення живе в TXLSCalculator.GetValueItemName: він знімає обгортки SA_GROUP зі скомпільованого визначення, і якщо корінь — це SA_RANGE, викликає GetRangeInfo, перетинає й дістає одну клітинку через FGetValue замість обчислення всього визначення. Зовнішні посилання лишаються на старому шляху, бо локального рядка, з яким перетинати, немає. Звідки взагалі беруться сховище й область видимості імені, розказано в статті про визначені імена й формули між аркушами; тут ідеться лише про те, що рушій робить, коли ім'я вже розв'язалося

Чому MATCH по наполовину порахованій колонці читав 777?

Бо аргумент масиву пошуку в MATCH — це scan-посилання, а scan-посилання були свідомо виключені з порядку обчислення. Стаття про scan при пошуку запровадила TXLSDepRange.LookupScan і завершувалася розділом «Чим ви платите за виключення scan-ребер з упорядкування»: формула пошуку може виконатися раніше, ніж перераховано всі клітинки її діапазону, і прочитати застарілі значення. В інтерактивній сесії це сходиться на наступному проході. У пакетному перерахунку отруєного шаблону — ні, і PaymentCount, визначений як =MATCH(0.01,Balances,-1)+1, прочитав плейсхолдери 777, які ще сиділи в колонці балансу, і повернув кількість періодів, яка ніяк не могла бути правильною

TXLSDepGraph.TopoOrder тепер трактує scan-ребра як м'які ребра впорядкування. Поруч із жорстким вхідним степенем він тримає масив ScanInDeg, рахуючи брудні scan-попередники на вузол і зменшуючи його в міру видачі цих попередників, використовуючи списки ScanPrecedents, ScanDependents і ScanPrecedentCount, які попередня зміна вже зберігала. На кожній ітерації черга Кана сканує своє вікно готових вузлів у пошуку першого, чий ScanInDeg дорівнює нулю, і переставляє його в голову; якщо кожен готовий вузол усе ще чекає на scan-попередник, голова знімається у своєму стабільному порядку. Scan-ребра ніколи не входять у жорсткий вхідний степінь, тож самопосилальний VLOOKUP по власній колонці й далі легальний, але пошук, який міг би зачекати на завершуваний попередник, тепер чекає. Регресія, що це фіксує, LookupScan_WaitsForDirtyFormulaValues, отруює три клітинки балансу до 777 і очікує, що PaymentCount повернеться як 3, а потім перемикає вхідні дані в нуль і очікує, що =IFERROR(PaymentCount,99) побачить #N/A і поверне 99

Звідки взялося усікання до чотирьох знаків?

З арифметики Delphi Variant, і лише у вкладених позиціях. Бінарні оператори в TXLSCalculator.GetValueItem уже копіювали + чи - верхнього рівня у дві локальні змінні 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;
// Змішана арифметика Variant Int64/Double може підвищитися до Currency.
// Арифметика таблиці мусить зберігати точність з плаваючою комою.
if VarIsNumeric(Value) then Value := Double(Value);
if VarIsNumeric(SubValue) then SubValue := Double(SubValue);

Перевірка виконується перед 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 обидві архітектури рушія повертають lxOk для отруєного шаблону, всі 4805 кешованих значень збігаються з незалежним порядковим очікуванням у межах 1E-7, а твердження про те, що кеші справді були отруєні, що хеш джерела не змінився і що кожна формула все ще на місці, — усі тримаються. Жодної ітерації не вмикали й жодного коду помилки не глушили, щоб цього досягти. Справжній цикл через ім'я — =B1 в A1, коли B1 усе ще читає Vertical, — і далі повертає помилку, і тест NamedScalarRanges_IntersectWithoutFalseCycles завершується саме цим твердженням

Межі варто назвати прямо. Неявний перетин застосовується лише до імені, чиє скомпільоване визначення після зняття дужок є одноколонковою або однорядковою областю на одному аркуші; двовимірне ім'я в скалярній позиції дає #VALUE!, як і в Excel, а функція, якої таблиця не знає, отримує клас 0 з FunctionArgumentClass, тож її аргументи-імена все ще розгортаються повністю. М'яке впорядкування — це перевага, а не гарантія: цикл лише зі scan-ребрами все одно обчислюється у стабільному порядку й читає те, що закешовано, і це та поведінка, яку стаття про scan при пошуку прийняла свідомо. А результат для всього шаблону звірено з незалежним скриптом очікувань, а не з іншим рушієм таблиць, бо еталонний офісний пакет не встиг перерахувати оригінальний шаблон у 60-секундний бюджет. HotXLS — це нативний компонент таблиць для Delphi і C++Builder, який читає, перераховує та пише XLS, XLSX, ODS і CSV без встановленого Excel; перетин імен, таблиця класів аргументів і м'яке впорядкування scan-ребер працюють для всіх форматів, бо рушій обчислень спільний, а поточне покриття функцій перелічено на сторінці продукту HotXLS Delphi spreadsheet component