Покладіть =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 внесло ризик, що не має нічого спільного з пошуком і все — з Pascal. 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