Сканирование поиска и ложные циклические ссылки в HotXLS

Поместите =VLOOKUP(A1,B:B,1) в ячейку в столбце B, и Excel вычислит её без возражений. Ту же книгу, отданную механизму пересчёта на графе зависимостей, ждёт, скорее всего, ошибка циклической ссылки, потому что формула зависит от диапазона, который содержит саму формулу. HotXLS сообщал именно это до v2.361.98. Исправление — не специальный случай для целостолбцовых диапазонов; это различие между двумя видами рёбер зависимости, которое нужно табличному механизму и которого нет у простого ориентированного графа

Аргумент массива поиска у семейства поиска, LOOKUP, MATCH, HLOOKUP, VLOOKUP, XLOOKUP и XMATCH, теперь помечается как сканирующая ссылка. Сканирующая ссылка по-прежнему распространяет признак изменённости, поэтому правка ячейки внутри диапазона пересчитывает формулу, но она никогда не участвует в обнаружении циклов и в упорядочивании вычисления. Настоящие циклы по-прежнему находятся; ложные исчезли

Почему Excel разрешает диапазону поиска содержать формулу?

Потому что этот аргумент не потребляется так, как арифметический операнд. Семейство поиска сканирует диапазон на кэшированные значения и возвращает совпадение; оно не требует, чтобы диапазон был сначала вычислен до конца. Excel трактует самопересекающийся диапазон поиска как чтение того, что ячейки держат сейчас, — та же семантика, которую он применяет к любой книге без итеративных вычислений: ячейки, не пересчитанные в этом проходе, отдают последнее вычисленное значение

Целостолбцовые ссылки делают это обычным случаем, а не экзотикой. B:B — идиоматический способ записать «вся таблица поиска» в листе, куда добавляют строки, и любая формула, живущая в столбце B, оказывается внутри собственного диапазона поиска. Финансовые модели, листы сверки и книги аудита делают это постоянно, обычно без кого-либо, заметившего пересечение диапазона

Что граф зависимостей делает с той же формулой

HotXLS пересчитывает инкрементально, что требует настоящего графа зависимостей: узлы для ячеек, рёбра для ссылок, топологический порядок для вычисления и проход по сильно связным компонентам, чтобы классифицировать циклы. Эта механика описана в статье об инкрементальном пересчёте, и именно из-за неё появилось ложное срабатывание

Извлеките зависимости из =VLOOKUP(A1,B:B,1) в ячейке B7, и второй аргумент даст диапазон, содержащий саму B7. У графа теперь есть петля. Входящая степень этого узла никогда не достигает нуля, поэтому топологический проход никогда не сможет его запланировать, а проход компонентов классифицирует его как цикл. Механизм рассуждает корректно о графе, который ему дали. Граф — неверная модель, потому что он кодирует один тип ребра там, где у таблицы их два

Два класса рёбер, один граф

Изменение добавляет флаг в запись разрешённой ссылки, TXLSDepRange.LookupScan, который извлекатель зависимостей ставит, когда проходит аргумент массива поиска одной из шести функций. Ниже по потоку рёбра, происходящие из этих ссылок, хранятся отдельно от обычных: узел графа держит списки ScanDependents и ScanPrecedents рядом с обычными списками зависимых и предшественников

Разделение — то, что делает семантику правильной. Рёбра сканирования проходятся распространением изменённости, поэтому правка где угодно в B:B по-прежнему помечает B7 изменённой, и B7 пересчитывается. Рёбра сканирования никогда не считаются во входящую степень и никогда не входят в построитель компонентов, поэтому не могут создать топологический тупик и не могут быть классифицированы как цикл. Обе реализации графа в библиотеке, классический граф на книгу и межкнижный граф рабочей области, несущий анализ компонентов, менялись вместе; дать им разъехаться значило бы получить книгу, пересчитывающуюся по-разному в зависимости от того, открыта ли она одна или как часть рабочей области

var
  Book: TXLSXWorkbook;
  Sheet: TXLSXWorksheet;
begin
  Book := TXLSXWorkbook.Create;
  try
    Sheet := Book.Sheets.Add('Ledger');
    Sheet.Cells[1, 1].Value := 'ACC-4471';
    Sheet.Cells[1, 2].Value := 1200.00;
    // Диапазон поиска покрывает столбец B, и эта формула живёт в нём
    Sheet.Cells[7, 2].Formula := 'VLOOKUP(A1,B:B,1)';

    case Book.Recalculate of
      lxOk:
        // До v2.361.98 эта ветвь была недостижима для этого листа
        SaveReport(Book);
      lxErrorRef:
        LogWarning('Genuine circular reference - review model inputs');
    end;
  finally
    Book.Free;
  end;
end;

Чем вы жертвуете, исключая рёбра сканирования из упорядочивания

Ровно об одном, и об этом стоит сказать прямо, а не прятать. Поскольку рёбра сканирования не участвуют в топологическом порядке, формула поиска может быть вычислена в том же проходе раньше, чем некоторые ячейки её диапазона поиска пересчитаны, и тогда она прочтёт их предыдущие значения. Результат сходится при следующем пересчёте

Это приемлемо, потому что именно так поступает Excel. Для книги без включённых итеративных вычислений собственный ответ Excel на значение, ещё не пересчитанное в текущем проходе, — последнее вычисленное значение, поэтому механизм, воспроизводящий это поведение, совпадает с эталонной реализацией, а не аппроксимирует её. Если вам нужен действительно сходящийся ответ по самоссылочной модели, механизм для этого — итеративные вычисления с явным пределом итераций, описанные в статье об итеративных вычислениях, и они применимы к настоящим циклам, а не к сканирующим пересечениям

Риск регрессии, прячущийся внутри исправления

Добавление LookupScan в TXLSDepRange внесло риск, не имеющий ничего общего с поиском и всё — с Паскалем. TXLSDepRange — неуправляемая запись, поэтому локальная переменная этого типа не инициализируется нулём. Каждое место в кодовой базе, строящее её вручную, включая блоки зависимостей таблиц данных и несколько тестовых помощников, пришлось обновить, чтобы выставлять новое поле явно. Пропустите одно — и случайный байт на стеке решает, трактуется ли ссылка как ребро сканирования, что даёт ошибку пересчёта, появляющуюся и исчезающую вместе с несвязанными правками кода

// Новое булево поле в неуправляемой записи превращает каждое
// место ручного построения в скрытую ошибку. Два безопасных идиома:
var
  R: TXLSDepRange;
begin
  FillChar(R, SizeOf(R), 0);      // обнулите всё, затем заполните
  R.Sheet1 := SheetIndex;
  R.Sheet2 := SheetIndex;
  R.Row1 := Row; R.Col1 := Col;
  R.Row2 := Row; R.Col2 := Col;

  // или выставляйте каждое поле, включая новое, в каждом месте
  R.LookupScan := False;
end;

Общее правило, которое это заслужило: добавление поля в запись, конструируемую на стеке больше чем в горстке мест, — более рискованное изменение, чем кажется, и компилятор не поможет найти эти места. Если запись достижима из горячего пути, предпочитайте помощник, инициализирующий её полностью, а не надежду, что каждое место вызова будет обновлено

Как отличить настоящий цикл от сканирующего пересечения

Ничто в этом изменении не ослабляет обнаружение циклов. =B7+1 в B7 — по-прежнему цикл, цепочка из трёх формул, замкнувшаяся на себя, — по-прежнему цикл, и оба по-прежнему сообщаются через результат пересчёта, причём участники цикла сохраняют прежние кэшированные значения, пока всё вне цикла остаётся актуальным. Изменилось лишь то, что аргумент массива поиска больше не изготовляет циклы, которых Excel не видит

Если вы аудитируете книгу и хотите знать, какие ссылки механизм действительно разрешил и в каком порядке, инструмент для этого — трассировщик вычислений; статья о трассировщике вычисления формул рассказывает, как читать его вывод. HotXLS — нативный табличный компонент Delphi и C++Builder, читающий и пишущий XLS, XLSX, ODS и CSV без установленного Excel, и механизм пересчёта одинаков на всех форматах; текущее покрытие функций и механизма перечислено на странице продукта HotXLS Delphi spreadsheet component