HotXLS, собствената библиотека за Excel за Delphi и C++Builder, извършва инкрементално преизчисляване на формули чрез TXLSXWorkbook.Recalculate; Първото извикване изгражда граф на зависимости на формулите и изчислява всяка формула; всяко по-късно извикване преизчислява само клетките, засегнати от промени в стойностите след последното преминаване, в топологичен ред, при едно преминаване, чиято цена е пропорционална на броя на променените клетки, а не на размера на работната книга
Това единствено решение при дизайна е разликата между финансов модел, който реагира на променено предположение за милисекунди, и такъв, който блокира за секунди; Ако генерирате отчети, в които шепа входящи клетки захранват хиляди формули по веригата, останалата част от тази статия обяснява какво прави графът, кои функции се изключват от инкременталността и как се докладват циклическите препратки, вместо да се върти безкраен цикъл
Защо промяната на една клетка преизчислява сто хиляди формули？
Обикновеният двигател за формули не помни кой от кого зависи, така че единственото му безопасно действие след всяка редакция е да изчисли всичко отново; По-лошото е, че класическата рекурсивна стратегия — когато формула A препраща към формула B, изчисли B на място — преизчислява препратените клетки безусловно, игнорирайки всяка кеширана стойност; Верига от n формули, всяка от които препраща към предходната, струва O(n²) изчисления на пълно преминаване, а цикличната препратка изпраща рекурсията в бездната; Всеки разработчик на електронни таблици, който е свързал каскаден модел в рекурсивен оценител, е виждал и двата режима на отказ да се случват
Excel самият реши това преди десетилетия с веригата си за изчисления: поддържане на подредба на формулните клетки така, че редакция да маркира малък набор от клетки като променени и двигателят да обхожда само засегнатия край на веригата; HotXLS прилага същата идея като ясен граф на зависимости, изграден веднъж от компилираните формулни дървета и повторно използван при преминаванията за преизчисляване; Целта не е оригиналност; целта е цената на преизчисляването да следва размера на вашата редакция, а не размера на вашата работна книга
Как графът на зависимости превръща една редакция в едно преминаване
Графът на зависимости на HotXLS дава на всяка формула един възел (node) с ребра, водещи от прецедент към зависим; Когато вашият код записва стойност в клетка, работната книга я маркира като променена; когато се стартира Recalculate, състоянието на промяна се разпространява по ребрата към всяка формула по веригата и промененият подграф се изчислява точно веднъж в топологичен ред с помощта на алгоритъма на Кан; Тъй като дадена формула никога не се посещава преди нейните прецеденти, всеки възел се нуждае от едно изчисление — това е, което прави преминаването O(dirty)
Топологичният ред също така коригира проблема с рекурсията в неговия корен; По време на преминаване за преизчисляване двигателят превключва в специален режим, при който всяка препратка към друга формула чете директно кешираната стойност на тази клетка, вместо да я преизчислява — подредбата гарантира, че кешът вече е обновен; Същият механизъм означава, че цикъл от препратки не може да предизвика безкрайна рекурсия: нищо по време на преминаването не влиза повторно в оценителната функция за съседна клетка
var
Book: TXLSXWorkbook;
Inputs, Model: TXLSXWorksheet;
begin
Book := TXLSXWorkbook.Create;
try
Inputs := Book.Sheets.Add('Inputs');
Model := Book.Sheets.Add('Model');
Inputs.Cells[2, 2].Value := 0.05; // предположение за растеж
Model.Cells[2, 2].Formula := 'Inputs!B2*1000'; // XLSX формулите не съдържат водещ знак '='
Model.Cells[3, 2].Formula := 'B2*(1+Inputs!B2)';
// ... още хиляди редове, каскадно зависещи от същото предположение ...
Book.Recalculate; // първо извикване: изгражда графа, пълно изчисление
Inputs.Cells[2, 2].Value := 0.07; // една редакция маркира една клетка като променена
Book.Recalculate; // второ извикване: изпълнява се само веригата надолу
finally
Book.Free;
end;
end;Всеки резултат попада в кешираната стойност Value на клетката, така че след връщането на Recalculate четете изходните данни по същия начин, по който четете всяка друга клетка; В цикъл за генериране на отчети моделът е точно като кода по-горе: зареждате или изграждате модела веднъж, а след това редувате записването на няколко входящи клетки и извикването на Recalculate, като плащате само за формулите, които действително зависят от това, което се е променило
Кои функции на Excel налагат преизчисляване при всяко преминаване？
HotXLS третира NOW, TODAY, RAND, OFFSET и INDIRECT като летливи (volatile): всяка формула, съдържаща някоя от тях, се преизчислява при всяко преминаване на Recalculate, независимо дали нещо нагоре по веригата се е променило; Първите три са летливи по същата причина, поради която са и в Excel — техният резултат зависи от момента на изчисление, а не от други клетки; OFFSET и INDIRECT са летливи по по-фина причина: клетките, които четат, се изчисляват по време на изпълнение, така че графът не може да знае статично кои ребра да начертае за тях
Същото консервативно правило се разпростира и върху препратки, които модулът за изграждане на граф не може да закрепи към един правоъгълник; Формула, която преминава през именован диапазон с множество области, или такава, която препраща към външна работна книга, също се понижава до летлива и се преизчислява при всяко преминаване; Тази политика е съзнателна: допълнително изчисление струва малко време, но липсващо ребро на зависимост означава тихомълком остаряла стойност в изпратения отчет, което е много по-лош отказ; Ако вашият модел разчита на имена с обхват на работната книга, съпътстващата статия за дефинирани имена и формули между листове описва как се разрешават имена с единична област — те участват в графа нормално
Практическото ръководство следва директно от това; Дръжте критичните пътища на голям модел върху обикновени препратки към клетки и диапазони, където графът може да си върши работата, и изолирайте OFFSET и INDIRECT само до малкото места, които наистина се нуждаят от динамично адресиране; Модел с хиляда летливи формули изпълнява тези хиляда при всяко преминаване, без значение колко малка е била редакцията — точно поведението, което потребителите на Excel познават от работни книги, които „се преизчисляват при всяко натискане на клавиш“
Как HotXLS докладва циклични препратки？
Методът TXLSXWorkbook.Recalculate връща lxOk при успешно преминаване и lxErrorRef при откриване на цикъл от препратки; Членовете на цикъла се идентифицират по време на топологичното сортиране — те са възлите, които алгоритъмът на Кан никога не може да освободи — и те се пропускат, вместо да се върти безкраен цикъл: техните кеширани стойности остават каквито са били, докато всяка формула извън цикъла продължава да се изчислява нормално по ред; Вашето извикване получава ясен код за грешка вместо замръзване на програмата
case Book.Recalculate of
lxOk:
SaveReport(Book);
lxErrorRef:
// съществува цикъл от препратки; членовете на цикъла запазиха предишните си
// кеширани стойности и всичко извън цикъла е обновено
LogWarning('Circular reference detected - review model inputs');
end;Намирането на това кои клетки формират цикъла е задача за дебъгване и трасиращият модул за изчисление на формули е правилният инструмент за това: трасирайте подозрителната формула и веригата от препратки, която се затваря в себе си, ще стане видима стъпка по стъпка; Циклите в реалните модели почти винаги са авторска грешка — сумарен ред, случайно включен в собствения му диапазон на SUM — така че ясен код за грешка по време на преизчисляване е точно това, което искате
Формули за масиви, проследяване на промените и кога графът се изгражда отново
Формулите за масиви CSE получават един възел за целия закотвен правоъгълник, а не по един възел за клетка; Основната формула се изчислява веднъж на преминаване; полученият масив се записва директно във всяка клетка-член, а формула, която препраща към която и да е клетка в закотвения диапазон — не само към горната лява котва — получава ребро на зависимост от този основен възел; Скаларните резултати се разпространяват в правоъгълника по начина, по който изисква традиционната семантика на масивите в Excel
Проследяването на промените (dirty tracking) се закача за обикновените методи за задаване на свойства, така че нищо в кода ви не се променя; Записването на Value в клетка уведомява работната книга и маркира зависимите като променени; присвояването на нова Formula е структурна промяна, така че маркира целия граф като остарял и следващото Recalculate го изгражда отново преди изчислението; Добавянето, изтриването или преместването на листове също инвалидизира графа, тъй като идентичността на възлите кодира индекса на листа; Когато няма активен граф — работна книга, на която никога не извиквате Recalculate — куките струват една проверка за nil на присвояване, така че обикновените натоварвания за четене и запис не са засегнати
Една граница, която трябва да се заяви честно: графът проследява зависимостите между клетките, така че дефинирана от потребителя функция, регистрирана чрез OnUserFunction, се преизчислява, когато клетките, захранващи нейните аргументи, се променят, подобно на всяка друга формула; Ако разширявате двигателя по този начин, статията за персонализирани функции в двигателя за формули на HotXLS разглежда договора за обратно извикване и начина, по който пристигат стойностите на аргументите
Инкременталното преизчисляване е част от стандартния XLSX двигател в HotXLS Delphi Excel Component, заедно с калкулатора за формули, дефинираните имена и конвейера за импортиране/експортиране, който той ускорява; Ако вашето Delphi или C++Builder приложение поддържа живи модели — ценови листи, консолидационни работни книги, каскади от отчети — Recalculate е разликата между преизчисляване на работна книга и преизчисляване на редакция