Неявное пересечение имён в HotXLS и классы аргументов

Определённое имя, ссылающееся на целый столбец, 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, не принимается за простую область. Когда оба флага истинны в узле 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

Откуда взялось усечение до четырёх знаков?

Из арифметики Variant в Delphi, и только во вложенных позициях. Бинарные операторы в 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, а функция, которой таблица не знает, получает от FunctionArgumentClass класс 0, поэтому её аргументы-имена всё равно раскрываются целиком. Мягкое упорядочивание — это предпочтение, а не гарантия: цикл только из scan-рёбер всё равно вычисляется в устойчивом порядке и читает то, что закешировано, — поведение, которое статья про scan при поиске приняла сознательно. И результат по всему шаблону сверяется с независимым скриптом ожиданий, а не с другим табличным движком, потому что эталонный офисный пакет не успел пересчитать исходный шаблон за 60 секунд. HotXLS — нативный компонент для работы с электронными таблицами на Delphi и C++Builder, который читает, пересчитывает и пишет XLS, XLSX, ODS и CSV без установленного Excel; пересечение имён, таблица классов аргументов и мягкое упорядочивание scan действуют для всех форматов, потому что движок вычислений общий, а текущее покрытие функций перечислено на странице продукта HotXLS Delphi spreadsheet component