Vzorce dynamických polí s přelitím (spill) v Delphi s HotXLS

HotXLS vyhodnocuje vzorce dynamických polí Excel 365 s přelitím (spill) nativně v Delphi a C++Builderu: předáte mu sešit, jehož buňky obsahují XLOOKUP nebo FILTER, a jeho vzorcový engine vypočítá sadu výsledků a přelije ji přes ukotvený výstupní obdélník, tedy přesně ty hodnoty, které by vygeneroval Excel. Komponenta HotXLS Delphi Excel modeluje vzorec s přelitím jako jednu kořenovou buňku, která vlastní výpočet, plus blok závislých buněk, které z ní čtou, což je kompletní obraz, který potřebujete před napsáním prvního řádku kódu

Tento model je důležitý, protože každodenní úkol není abstraktní. Zákazník pošle soubor .xlsx vytvořený v Excelu 365, jeho listy jsou plné =FILTER(...) a =XLOOKUP(...), a vaše služba musí reprodukovat přesně stejná čísla bezhlavě (headless), na stroji bez nainstalovaného Excelu, a poté buď přečíst přelité hodnoty, nebo zapsat vlastní novou oblast přelití. Dynamická pole změnila kontrakt výpočtů z „jeden vzorec, jedna buňka“ na „jeden vzorec, obdélník buněk“ a engine HotXLS tento kontrakt dodržuje, místo aby jej předstíral s předem roztaženou mřížkou

Jak vyhodnotit XLOOKUP a FILTER v Delphi?

HotXLS odesílá každou funkci dynamického pole přes jediný vstupní bod vyhodnocovače, CalcDynArrayFunc v lxCalc.pas, takže celá rodina sdílí jednu kódovou cestu přelití. Podporovaná sada zahrnuje XLOOKUP a FILTER (původní dvě), ke kterým se připojují XMATCH, SORT, UNIQUE a SEQUENCE. Každá z nich vrací 2D variantní pole namísto skaláru: SEQUENCE(3;2;1;1) dává mřížku 3x2, SORT mění pořadí řádků podle libovolného klíčového sloupce vzestupně nebo sestupně, UNIQUE redukuje duplicitní řádky tak, že ponechává první výskyt, a XMATCH hlásí pozici hodnoty od 1 v režimech přesné shody, shody s divokými znaky, shody s nejbližším menším a shody s nejbližším větším prvkem stejně jako XLOOKUP. Na rozdíl od skalárních statistických distribučních funkcí, které při každém volání vrací jedno číslo, tyto funkce vracejí celý tvar

Chcete-li spočítat sešit, který již tyto vzorce obsahuje, otevřete jej, zavolejte jednou Recalculate a přečtěte buňky, do kterých se přelití dostalo. TXLSXWorkbook.Recalculate prochází graf závislostí a vyhodnocuje každý změněný vzorec v topologickém pořadí, takže kořen přelití se vypočítá pouze jednou a jeho prvky se zapíší přímo do členských buněk

var
  Book: TXLSXWorkbook;
  Sheet: TXLSXWorksheet;
  r, c: Integer;
begin
  Book := TXLSXWorkbook.Create;
  try
    Book.Open('from-customer.xlsx');
    Book.Recalculate;                 // vyhodnotit každý vzorec, včetně přelití
    Sheet := Book.Sheets[1];          // Sheets is 1-based
    // Přečíst zpět blok, do kterého se XLOOKUP / FILTER přelil, například 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;

Pokud sešit potřebuje funkci, kterou engine nedodává, stejný vyhodnocovač vám umožňuje připojit vlastní logiku prostřednictvím háčku (hook) vlastních funkcí listu, přičemž vlastní funkce může vrátit variantní pole, takže se přelévá přesně jako ty vestavěné

Ukotvení rozsahu přelití pomocí SetArrayFormula

HotXLS nikdy nehádá, jak velké by mělo přelití být: pojmenujete výstupní obdélník a TXLSRange.SetArrayFormula k němu vzorec ukotví. Metoda vzorec jednou zkompiluje, uloží syntaktický strom na levé horní kořenové buňce spolu s ukotveným rozsahem a každé další buňce v obdélníku předá odlehčený vzorec, který na kořen odkazuje slabým odkazem. Při přepočtu se kořen vyhodnotí jednou a prvky jeho matice se rozešlou přímo do každé členské buňky, což je také způsob, jakým se kořen přelití zobrazuje jako jeden uzel v grafu závislostí inkrementálního přepočtu. Toto je cesta, kterou použijete, když je úkolem zapsat oblast přelití zpět do souboru, nikoli ji pouze přečíst

var
  Book: TXLSXWorkbook;
  Sheet: TXLSXWorksheet;
begin
  Book := TXLSXWorkbook.Create;
  try
    Book.Open('report.xlsx');
    Sheet := Book.Sheets[1];
    // Změnit velikost obdélníku podle výsledku: mřížka o 3 řádcích a 2 sloupcích.
    if Sheet.Range['A1:B3'].SetArrayFormula('=SEQUENCE(3;2;1;1)') > 0 then
      Book.Recalculate;               // vyplnit A1:B3 hodnotami 1..6 směrem dolů po řádcích
    Book.SaveAs('report-out.xlsx');
  finally
    Book.Free;
  end;
end;

Důsledkem explicitního ukotvení je hranice, kterou stojí za to uvést na rovinu: HotXLS automaticky nemění velikost přelití způsobem, jakým to dělá interaktivní Excel. Excel zvětšuje a zmenšuje přelité oblasti podle toho, jak se mění jejich vstupy, a vyvolává chybu #SPILL!, pokud jsou cílové buňky obsazené. V HotXLS je obdélník, který předáte metodě SetArrayFormula, přesně tím obdélníkem, který dostanete, takže jeho velikost musíte přizpůsobit očekávanému výsledku, a pokud funkce vyprodukuje více řádků, než kolik jste ukotvili, přebytek nemá kam přistát

Vysílání (broadcasting) polí prvek po prvku napříč operátory

HotXLS aplikuje aritmetické operátory prvek po prvku, pokud je kterákoli strana operace polem. Pomocník ApplyArrayBinaryOp pokrývá +, -, *, / a ^ nad 1D a 2D variantními poli: skalární operand se rozšíří na každý prvek, dvě pole se musí shodovat ve tvaru, jinak je operace odmítnuta, a dělení nulou se šíří jako chyba, místo aby způsobilo pád. Skalární aritmetická cesta je nedotčena, takže se to aktivuje pouze tehdy, když je alespoň jeden operand skutečně polem, jako je přelitý rozsah nebo výsledek maticové funkce. To znamená, že vzorec jako =D2:D13*1.1 ukotvený nad sloupcem vynásobí každý prvek po řadě a =D2:D13*E2:E13 vynásobí dva stejně tvarované sloupce pozici po pozici

// Skalární vysílání: každá buňka ukotvení získá D(n) * 1.1.
Sheet.Range['F2:F13'].SetArrayFormula('=D2:D13*1.1');
// Dvě pole identického tvaru se vynásobí prvek po prvku.
Sheet.Range['G2:G13'].SetArrayFormula('=D2:D13*E2:E13');
Book.Recalculate;

Co dělá operátor @ ve srovnání s dědictvím pole CSE?

HotXLS čte explicitní @ jako operátor průniku odkazů, nikoli jako implicitní průnik živého Excelu vybírající řádek. V enginu vrací A1:A3 @ B1:B3 jedinou buňku, kde se tyto dva rozsahy kříží, a operátor se váže těsněji než ^ a volněji než %. K tomu se váže jedna upřímná poznámka: implicitní forma průniku oddělená mezerou ještě není tokenizována, protože lexer stále přeskakuje bílé znaky, takže průnik získáte pouze prostřednictvím explicitního symbolu @

Hlubší rozdíl je mezi starším maticovým vzorcem CSE a moderním dynamickým polem a v HotXLS oba procházejí stejnou ukotvovací mašinérií. Klasickým maticovým vzorcem byl blok {=...} zadaný pomocí Ctrl+Shift+Enter nad předem vybraným výběrem, kde každá buňka sdílela jeden zkompilovaný vzorec. Dynamické pole je jediný vzorec, jehož výsledek určuje jeho vlastní tvar. HotXLS je sjednocuje pod SetArrayFormula: vždy pojmenujete obdélník, kořen vlastní kompilovaný strom a členové na něj odkazují, ať už je vzorec starým maticovým výrazem nebo novým SORT. Co však nikdy nezískáte, je automatické zvlnění z Excelu, kde se oblast tiše znovu rozrůstá, a udržení této hranice v paměti brání rozmazání těchto dvou mentálních modelů

Kde podpora dynamických polí končí

Znalost hranic ušetří odpoledne ladění. SORT respektuje směr u každého klíče, ve výchozím nastavení vzestupně a sestupně, pokud je argument pořadí záporný; směr porovnání je místem, které stojí za to otestovat proti Excelu s vlastními klíčovými sloupci, protože obrácené porovnání tiše vrátí řádky v opačném pořadí, namísto selhání. UNIQUE uchovává první výskyt každého odlišného řádku, což engine potvrzuje skenováním předchozích řádků, namísto spoléhání se na hrubý počet duplicit. Funkce TABLE (datové tabulky What-If analýzy) je rozpoznána, takže prochází souborem oběma směry, ale vyhodnocuje se na zástupný symbol, protože skutečnými výsledky substituce jsou cached hodnoty, které již Excel uložil, a HotXLS tabulku what-if znovu nespouští

LET je nejostřejším limitem, který je třeba zmínit. HotXLS registruje LET a nese pro něj vyhodnocovací cestu, ale parser odmítá holý název, který není již definovaným názvem, takže =LET(x;10;x) selže při syntaktické analýze ještě předtím, než vyhodnocovač vůbec začne pracovat. Považujte LET za nepodporované, dokud parser nezíská povědomí o rozsahu pro své vázané názvy. Malá poznámka k přenositelnosti: řetězce vzorců v těchto příkladech používají oddělovač seznamů pro engine, kterým je středník, takže při sestavování textu vzorce použijte jakýkoli oddělovač, který váš sestavený projekt očekává. Funkce dynamických polí popsané v tomto článku, od XLOOKUP a FILTER přes SORT, UNIQUE, SEQUENCE a XMATCH, se dodávají v komponentě HotXLS Delphi Excel Component, jejíž referenční příručka vzorců uvádí kompletní katalog funkcí a podporované argumentační režimy každé z nich