Формули за динамични масиви (Spill) в Delphi с HotXLS

HotXLS изчислява Excel 365 формули за динамични масиви нативно в Delphi и C++Builder: предавате му работна книга (workbook), чиито клетки съдържат XLOOKUP или FILTER, и неговият модул за формули изчислява резултатите и ги разпростира (spill) по целия свързан изходен правоъгълник – същите стойности, които Excel би произвел. Delphi Excel компонентът HotXLS моделира разпростиращата се формула (spill formula) като една коренна клетка (root cell), която притежава изчислението, плюс блок от зависими клетки, които четат обратно от нея – това е цялостната картина, която трябва да знаете преди написването на какъвто и да е код

Този модел е важен, тъй като ежедневната задача не е абстрактна. Клиент изпраща .xlsx файл, създаден в Excel 365, неговите листове са пълни с =FILTER(...) и =XLOOKUP(...), и вашата програма трябва да възпроизведе абсолютно същите числа във фонов режим, на компютър без инсталиран Excel, а след това или да прочете разпрострените стойности, или да запише нова разпростираща се област. Динамичните масиви промениха концепцията за изчисляване от „една формула, една клетка“ на „една формула, правоъгълник от клетки“ и HotXLS следва този модел, вместо да симулира поведението с предварително разширена мрежа

Как се изчисляват XLOOKUP и FILTER в Delphi?

HotXLS насочва всяка функция за динамичен масив през една входна точка за изчисляване – CalcDynArrayFunc в lxCalc.pas, така че цялото семейство функции споделя един и същ код. Поддържаният набор е XLOOKUP и FILTER (оригиналните две), придружени от XMATCH, SORT, UNIQUE и SEQUENCE. Всяка от тях връща двумерен Variant масив вместо скаларна стойност: SEQUENCE(3;2;1;1) генерира мрежа 3 на 2, SORT пренарежда редовете по произволна ключова колона възходящо или низходящо, UNIQUE премахва дублиращите се редове, като запазва първото срещане, а XMATCH връща 1-базираната позиция на дадена стойност при абсолютно съвпадение, съвпадение с шаблони (wildcard), следваща по-малка или следваща по-голяма стойност, по подобие на XLOOKUP. За разлика от скаларните функции за статистическо разпределение, които връщат едно число при всяко извикване, тези функции връщат цяла структура

За да изчислите работна книга, която вече съдържа тези формули, отворете я, извикайте Recalculate веднъж и прочетете клетките, в които са разпрострени резултатите. Методът TXLSXWorkbook.Recalculate обхожда графа на зависимостите и изчислява всяка променена формула в топологичен ред, така че коренът на разпростирането се изчислява веднъж, а неговите елементи се записват директно в съответните клетки

var
  Book: TXLSXWorkbook;
  Sheet: TXLSXWorksheet;
  r, c: Integer;
begin
  Book := TXLSXWorkbook.Create;
  try
    Book.Open('from-customer.xlsx');
    Book.Recalculate;                 // evaluate every formula, spills included
    Sheet := Book.Sheets[1];          // Sheets is 1-based
    // Read back the block the XLOOKUP / FILTER spilled into, say D2:F9.
    for r := 2 to 9 do
      for c := 4 to 6 do
        Writeln(Sheet.Cells[r, c].Text);
  finally
    Book.Free;
  end;
end;

Свързване (Anchoring) на разпростиращия се диапазон чрез SetArrayFormula

HotXLS никога не отгатва колко голяма трябва да бъде разпростиращата се област: вие посочвате изходния правоъгълник и методът TXLSRange.SetArrayFormula свързва формулата с него. Този метод компилира формулата веднъж, съхранява синтактичното дърво в горната лява коренна клетка заедно със свързания диапазон и дава на всяка друга клетка в правоъгълника олекотена формула, съдържаща слаба връзка (weak reference) към корена. При преизчисляване коренът се изчислява веднъж, а неговите матрични елементи се разпространяват директно към всяка клетка член, което е и начинът, по който коренът на разпростиране се появява като един възел в графа на зависимостите при инкрементално преизчисляване. Това е пътят, който трябва да се използва, когато целта е да запишете разпростираща се област във файла, вместо само да я прочетете

var
  Book: TXLSXWorkbook;
  Sheet: TXLSXWorksheet;
begin
  Book := TXLSXWorkbook.Create;
  try
    Book.Open('report.xlsx');
    Sheet := Book.Sheets[1];
    // Size the rectangle to the result: a 3-row by 2-column grid.
    if Sheet.Range['A1:B3'].SetArrayFormula('=SEQUENCE(3;2;1;1)') > 0 then
      Book.Recalculate;               // fill A1:B3 with 1..6 down the rows
    Book.SaveAs('report-out.xlsx');
  finally
    Book.Free;
  end;
end;

Следствието от изричното свързване е граница, която трябва да се отбележи ясно: HotXLS не преоразмерява автоматично разпростиращата се област по начина, по който го прави интерактивният Excel. Excel разширява и свива областта с промяната на входните данни и генерира грешка #SPILL!, когато целевите клетки са заети. В HotXLS правоъгълникът, който предавате на SetArrayFormula, е точно правоъгълникът, който получавате, така че вие го оразмерявате спрямо очаквания резултат, и ако дадена функция произведе повече редове от свързаните, излишъкът няма къде да бъде записан

Поелементно разпространение на масиви при операторите

HotXLS прилага аритметичните оператори поелементно, когато някоя от страните в операцията е масив. Помощната функция ApplyArrayBinaryOp обхваща +, -, *, / и ^ над едноизмерни и двуизмерни Variant масиви: скаларен операнд се прилага върху всеки елемент, два масива трябва да съвпадат по форма (в противен случай операцията се отхвърля), а деленето на нула се предава като грешка, без да предизвиква срив на програмата. Пътят на скаларната аритметика е непроменен, така че това се активира само когато поне един операнд е реален масив – например разпрострян диапазон или резултат от матрична функция. Това означава, че формула като =D2:D13*1.1, свързана по колона, умножава всеки елемент поред, а =D2:D13*E2:E13 умножава две колони с еднаква форма позиция по позиция

// Scalar broadcast: every cell of the anchor gets D(n) * 1.1.
Sheet.Range['F2:F13'].SetArrayFormula('=D2:D13*1.1');
// Two arrays of identical shape multiply element by element.
Sheet.Range['G2:G13'].SetArrayFormula('=D2:D13*E2:E13');
Book.Recalculate;

Какво прави операторът @ в сравнение с класическите CSE масиви?

HotXLS чете изричния оператор @ като оператор за сечение (reference-intersection), а не като неявното сечение за избор на ред в стандартния Excel. В ядрото на HotXLS изразът A1:A3 @ B1:B3 връща единичната клетка, в която двата диапазона се пресичат, като операторът е с по-висок приоритет от ^ и по-нисък от %. Една подробност: неявната форма на сечение, разделена с интервал, все още не е токенизирана, тъй като лексикалният анализатор все още пропуска интервалите, така че получавате сечение само чрез изричния символ @

По-дълбоката разлика е между класическата CSE формула за масив и съвременния динамичен масив, като в HotXLS и двата типа преминават през един и същ механизъм за свързване. Класическата формула за масив беше блокът {=...}, въвеждан с Ctrl+Shift+Enter над предварително оразмерена селекция, при която всяка клетка споделя една и съща компилирана формула. Динамичният масив е единична формула, чийто резултат сам определя формата си. HotXLS ги обединява под SetArrayFormula: вие винаги указвате правоъгълника, коренът притежава компилираното дърво, а членовете се свързват с него, независимо дали формулата е стара матрична структура или нов SORT. Това, което никога няма да получите, е автоматичното преливане на Excel, при което областта се разширява без намеса, и разбирането на тази разлика предпазва двата модела от объркване

Къде спира поддръжката на динамични масиви

Познаването на ограниченията спестява време за дебъгване. SORT поддържа посока за всеки ключ – възходяща по подразбиране и низходяща, когато аргументът за ред е отрицателен; посоката на сравнение е нещо, което си струва да тествате спрямо Excel за вашите ключови колони, тъй като обърнатото сравнение тихомълком връща редовете в обратен ред, вместо да даде грешка. UNIQUE запазва първото срещане на всеки различен ред, което модулът потвърждава чрез сканиране на предходните редове, вместо да разчита на обикновен брой дубликати. Функцията TABLE (таблици с данни за What-If анализ) се разпознава, за да може да премине през файла при двупосочно четене/запис, но тя се изчислява до фиктивна стойност (placeholder), тъй като реалните резултати от замяната са кешираните стойности, които Excel вече е съхранил, и HotXLS не преизчислява What-If мрежата

Функцията LET е най-ясното ограничение, което трябва да се спомене. HotXLS регистрира LET и има път за изчисляване за нея, но парсерът отхвърля име на променлива, което не е предварително дефинирано име, така че изразът =LET(x;10;x) се проваля още при анализа (parsing), преди да се достигне до изчисляването. Разглеждайте LET като неподдържана функция, докато парсерът не получи възможност да разпознава обхвата (scope) на дефинираните в нея имена. Една малка бележка за преносимост: низовете с формули в тези примери използват разделителя на списъци на ядрото – точка и запетая, така че използвайте разделителя, който очаква вашата конкретна среда, когато сглобявате текста на формули. Функциите за динамични масиви, разгледани тук – от XLOOKUP и FILTER до SORT, UNIQUE, SEQUENCE и XMATCH, се доставят с HotXLS Delphi Excel Component, чиято документация за формули описва пълния списък с функции и техните аргументи