HotXLS lookup сканиране и фалшиви циклични препратки

Сложете =VLOOKUP(A1,B:B,1) в клетка в колона B и Excel го изчислява без оплакване; Подайте същата работна книга на recalculation engine със граф на зависимости и е вероятно да получите грешка за циклична препратка, защото формулата зависи от диапазон, който съдържа формулата; HotXLS докладваше точно това до v2.361.98; Поправката не е специален случай за целиколонни диапазони; тя е разграничение между два вида ребро на зависимост, от които един engine за електронни таблици се нуждае и които обикновен насочен граф няма

Аргументът lookup-array на lookup семейството, LOOKUP, MATCH, HLOOKUP, VLOOKUP, XLOOKUP и XMATCH, вече е маркиран като scan препратка; Scan препратка все още разпространява dirtiness, така че редактиране на клетка вътре в диапазона преизчислява формулата, но никога не допринася за откриване на цикли или за подреждане на оценка; Истински цикли все още се откриват; фалшивите са изчезнали

Защо Excel позволява lookup диапазон да съдържа формулата?

Защото този аргумент не се консумира по начина, по който се консумира аритметичен операнд; Lookup семейството сканира диапазона за кеширани стойности и връща съвпадение; то не изисква диапазонът да е бил оценен до завършване първо; Excel третира самонадсичащ се lookup диапазон като четене на каквото тези клетки в момента държат, което е същата семантика, която прилага към всяка неитеративна работна книга: клетки, които не са били преизчислени в този проход, допринасят последната си изчислена стойност

Целиколонните препратки правят това честния случай, а не екзотичен; B:B е идиоматичният начин да се напише „цялата lookup таблица“ в лист, където редове се добавят и всяка формула, която живее в колона B, тогава е вътре в собствения си lookup диапазон; Финансови модели, sheets за съгласуване и одитни работни книги правят това постоянно, обикновено без някой да забележи, че диапазонът се застъпва

Какво прави графът на зависимостите със същата формула

HotXLS преизчислява инкрементално, което изисква истински граф на зависимости: възли за клетки, ребра за препратки, топологичен ред за оценка и проход на силно свързани компоненти, за да класифицира цикли; Тази машинерия е описана в статията за инкрементално преизчисляване и е точно причината фалшивият положителен резултат да се появи

Извлечете зависимостите от =VLOOKUP(A1,B:B,1) в клетка B7 и вторият аргумент дава диапазон, съдържащ самата B7; Графът сега има self-loop; In-degree на този възел никога не достига нула, така че топологичният проход никога не може да го планира, а компонентният проход го класифицира като цикъл; Engine-ът разсъждава правилно върху графа, който е получил; Графът е грешният модел, защото кодира един тип ребро там, където електронната таблица има два

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

Промяната добавя флаг към записа на разрешената препратка, TXLSDepRange.LookupScan, който екстракторът на зависимости задава, когато обхожда аргумента lookup-array на една от шестте функции; Надолу по веригата ребрата, идващи от тези препратки, се съхраняват отделно от обикновените ребра: графовият възел пази списъци ScanDependents и ScanPrecedents до нормалните си dependent и precedent списъци

Разделянето е това, което прави семантиката права; Scan ребра се обхождат от dirty разпространение, така че редакция навсякъде в B:B все още маркира B7 dirty и B7 преизчислява; Scan ребра никога не се броят в in-degree и никога не влизат в компонентния строител, така че те не могат да създадат топологична задръстка и не могат да бъдат класифицирани като цикъл; И двете имплементации на графа в библиотеката, класическият per-workbook граф и кръстосано-работен-книжният workspace граф, носещ анализа на компоненти, бяха променени заедно; оставянето им да се разминат би произведе работна книга, която преизчислява различно в зависимост от това дали е отворена сама или като част от workspace

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;
    // Lookup диапазонът покрива колона 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;

Какво жертвате, като изключите scan ребрата от подреждането

Точно едно нещо и си заслужава да се каже открито, а не да се крие; Понеже scan ребрата не участват в топологичния ред, lookup формула може да бъде оценена в същия проход, преди някои клетки в нейния lookup диапазон да са били преизчислени и тя тогава ще прочете предишните им стойности; Резултатът конвергира при следващото преизчисляване

Това е приемливо, защото е това, което Excel прави; За работна книга без включено итеративно изчисляване собственият отговор на Excel за стойност, още не преизчислена в текущия проход, е последната изчислена стойност, така че engine, който възпроизвежда това поведение, съвпада с reference имплементацията, а не я приближава; Ако се нуждаете от наистина конвергиран отговор върху саморефериращ модел, механизмът за това е итеративно изчисляване с изричен лимит на итерации, разгледано в статията за итеративно изчисляване и то се отнася за истински цикли, а не за scan застъпвания

Рискът от регресия, криещ се вътре в поправката

Добавянето на LookupScan към TXLSDepRange въведе риск, който няма нищо общо с lookups и всичко общо с Pascal; TXLSDepRange е неуправляван запис, така че локална променлива от този тип не е нулево инициализирана; Всяко място в codebase-а, което изгражда такъв на ръка, включително блоковете за зависимости на data-table и няколко тестови помощника, следователно трябваше да бъде обновено да задава новото поле изрично; Пропуснете едно и какъвто и да е байт случайно е на стека решава дали тази препратка се третира като scan ребро, което произвежда преизчислителен дефект, който се появява и изчезва с несвързани промени в кода

// Ново Boolean поле в неуправляван запис прави всяко ръчно място
// на конструиране латентен дефект. Два безопасни идиома:
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;

Общото правило, което това извоюва: добавяне на поле към запис, който е конструиран на стека в повече от шепа места, е промяна с по-висок риск, отколкото изглежда и компилаторът няма да ви помогне да намерите местата; Ако записът е достижим от горещ път, предпочитайте помощник, който го инициализира напълно, пред да вярвате на всяко място на извикване да бъде обновено

Различаване на истински цикъл от scan застъпване

Нищо около тази промяна не отслабва откриването на цикли; =B7+1 в B7 все още е цикъл, верига от три формули, която се затваря върху себе си, все още е цикъл и двете все още се докладват чрез резултата от преизчисляването с членовете на цикъла, запазващи предишните си кеширани стойности, докато всичко извън цикъла остава текущо; Това, което се промени, е само, че аргументът lookup-array вече не фабрикува цикли, които Excel не вижда

Ако одитирате работна книга и искате да знаете кои препратки engine-ът действително е разрешил и в какъв ред, evaluation tracer-ът е инструментът за това; статията за formula evaluation tracer разглежда как да четете нейния изход; HotXLS е нативен Delphi и C++Builder компонент за електронни таблици, който чете и записва XLS, XLSX, ODS и CSV без инсталиран Excel и recalculation engine-ът е същият на всеки формат; текущото покритие на функции и engine е изброено на продуктовата страница HotXLS Delphi spreadsheet component