Vzorce dynamických polí s presahom (spill) v Delphi pomocou HotXLS

HotXLS vyhodnocuje vzorce dynamických polí s presahom (spill) z Excelu 365 natívne v Delphi a C++Builder: odovzdáte mu zošit, ktorého bunky obsahujú XLOOKUP alebo FILTER, a jeho vzorcový engine vypočíta výslednú sada a preleje (spill) ju cez ukotvený výstupný obdĺžnik, čo sú rovnaké hodnoty, aké by vyprodukoval Excel. Excel komponent HotXLS pre Delphi modeluje vzorec s presahom ako jednu koreňovú bunku, ktorá vlastní výpočet, a blok závislých buniek, ktoré z nej čítajú, čo je presný obraz, ktorý potrebujete predtým, než napíšete riadok kódu

Tento model je dôležitý, pretože každodenné úlohy nie sú abstraktné. Zákazník odošle súbor .xlsx vytvorený v Exceli 365, ktorého hárky sú plné vzorcov =FILTER(...) a =XLOOKUP(...), a vaša služba musí reprodukovať presne tie isté čísla na pozadí (headless), na počítači bez nainštalovaného Excelu, a potom buď prečítať preliate hodnoty, alebo zapísať vlastnú novú oblasť presahu. Dynamické polia zmenili zmluvu o výpočte z „jeden vzorec, jedna bunka“ na „jeden vzorec, obdĺžnik buniek“ a engine HotXLS túto zmluvu dodržiava namiesto toho, aby ju predstieral s vopred rozšírenou mriežkou

Ako vyhodnocovať XLOOKUP a FILTER v Delphi?

HotXLS smeruje každú funkciu dynamického poľa cez jediný vstupný bod vyhodnocovača, CalcDynArrayFunc v lxCalc.pas, so celá rodina funkcií zdieľa jednu cestu kódu pre presah. Podporovaná sada obsahuje XLOOKUP a FILTER (pôvodné dve), ku ktorým sa pripájajú XMATCH, SORT, UNIQUE a SEQUENCE. Každá z nich vracia 2D variantné pole a nie skalár: SEQUENCE(3;2;1;1) vytvorí mriežku 3x2, SORT preusporiada riadky podľa ľubovoľného stĺpca kľúča vzostupne alebo zostupne, UNIQUE zredukuje duplicitné riadky a ponechá prvý výskyt, a XMATCH hlási pozíciu hodnoty (od 1) pri rovnakých režimoch presnej zhody, zástupných znakov (wildcards), nasledujúcej menšej a nasledujúcej väčšej zhody ako XLOOKUP. Na rozdiel od skalárnych štatistických distribučných funkcií, ktoré pri každom volaní vracajú jedno číslo, tieto funkcie vracajú celý tvar

Ak chcete prepočítať zošit, ktorý už obsahuje tieto vzorce, otvorte ho, raz zavolajte Recalculate a prečítajte bunky, do ktorých dopadol presah. TXLSXWorkbook.Recalculate prechádza graf závislostí a vyhodnocuje každý zmenený vzorec v topologickom poradí, takže koreň presahu sa vypočíta iba raz a jeho prvky sa zapíšu priamo do príslušných buniek

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;

Ak zošit potrebuje funkciu, ktorú engine neobsahuje, ten istý vyhodnocovač vám umožňuje pripojiť vlastnú logiku prostredníctvom hooku pre vlastné funkcie pracovného hárka, pričom vlastná funkcia môže vrátiť variantné pole, takže sa preleje presne tak isto ako vstavané funkcie

Ukotvenie rozsahu presahu pomocou SetArrayFormula

HotXLS nikdy neháda, aký veľký by sam mal byť presah: vy definujete výstupný obdĺžnik a TXLSRange.SetArrayFormula k nemu ukotví vzorec. Metóda skompiluje vzorec iba raz, uloží syntaktický strom do ľavej hornej koreňovej bunky spolu s ukotveným rozsahom a každej ďalšej bunke v obdĺžniku pridelí odľahčený vzorec, ktorý slabo odkazuje na koreň. Pri prepočítaní sa koreň vyhodnotí raz a jeho maticové prvky sa prenesú priamo do každej bunky, čo je tiež spôsob, akým sa koreň presahu zobrazuje ako jeden uzol v grafe závislostí prírastkového prepočítania. Toto je cesta, ktorú treba použiť, keď je úlohou zapísať oblasť presahu späť do súboru a nie ju len čítať

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;

Dôsledkom explicitného ukotvenia je hranica, ktorú stojí za to uviesť na rovinu: HotXLS automaticky nemení veľkosť presahu tak, ako to robí interaktívny Excel. Excel zväčšuje a zmenšuje preliatu oblasť pri zmene jej vstupov a vyvolá chybu #SPILL! (PRESAH!), keď sú cieľové bunky obsadené. V HotXLS je obdĺžnik, ktorý odovzdáte metóde SetArrayFormula, presne tým obdĺžnikom, ktorý získate, takže jeho veľkosť prispôsobte výsledku, ktorý očakávate, a ak funkcia vyprodukuje viac riadkov, než ste ukotvili, prebytok nemá kam dopadnúť

Prenos (broadcasting) operátorov po jednotlivých prvkoch poľa

HotXLS aplikuje aritmetické operátory prvok po prvku, keď je ktorákoľvek strana operácie poľom. Pomocná funkcia ApplyArrayBinaryOp pokrýva operácie +, -, *, / a ^ nad 1D a 2D variantnými poľami: skalárny operand sa prenesie na každý prvok, dve polia sa musia zhodovať v tvare, inak je operácia odmietnutá, a delenie nulou sa šíri ako chyba a nespôsobí pád programu. Cesta skalárnej aritmetiky zostáva nedotknutá, takže toto sa aktivuje len vtedy, keď je aspoň jeden operand skutočne poľom, napríklad preliatym rozsahom alebo výsledkom maticovej funkcie. To znamená, že vzorec ako =D2:D13*1.1 ukotvený nad stĺpcom vynásobí postupne každý prvok a vzorec =D2:D13*E2:E13 vynásobí dva rovnako tvarované stĺpce pozíciu po pozícii

// 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;

Čo robí operátor @ v porovnaní s historickým poľom CSE?

HotXLS číta explicitný znak @ ako operátor prieniku odkazov a nie ako implicitný prienik výberu riadkov zo živého Excelu. V engine A1:A3 @ B1:B3 vracia jedinú bunku, kde sa tieto dva rozsahy pretínajú, a operátor sa viaže tesnejšie ako ^ a voľnejšie ako %. S tým súvisí aj jedno úprimné upozornenie: implicitná forma prieniku oddelená medzerou zatiaľ nie je tokenizovaná, pretože lexikálny analyzátor stále preskakuje biele znaky, takže prienik získate iba prostredníctvom explicitného symbolu @

Hlbší rozdiel je medzi historickým maticovým vzorcom CSE a moderným dynamickým poľom, pričom v HotXLS oba prechádzajú rovnakým mechanizmom ukotvenia. Klasický maticový vzorec bol blokom {=...} zadaným pomocou Ctrl+Shift+Enter nad vopred dimenzovaným výberom, kde každá bunka zdieľala jeden skompilovaný vzorec. Dynamické pole je jediný vzorec, ktorého výsledok určuje jeho vlastný tvar. HotXLS ich zjednocuje pod SetArrayFormula: vždy pomenujete obdĺžnik, koreň vlastní skompilovaný strom a členovia naň odkazujú, či už ide o starý štýl maticového výrazu alebo nový SORT. Čo však nikdy nezískate, je automatické zvlnenie z Excelu, kde sa oblasť potichu sama zväčšuje, a udržanie tohto pravidla v pamäti zabraňuje prelínaniu týchto dvoch mentálnych modelov

Kde podpora dynamických polí končí

Poznanie hraníc vám ušetrí pol dňa ladenia. SORT rešpektuje smer pre každý kľúč, predvolene vzostupne a zostupne, keď je argument poradia záporný. Smer porovnávania je miesto, ktoré stojí za to otestovať na vašich vlastných stĺpcoch kľúčov voči Excelu, pretože obrátené porovnanie ticho vráti riadky v opačnom poradí namiesto zlyhania. UNIQUE zachováva prvý výskyt každého odlišného riadku, čo engine potvrdzuje skenovaním predchádzajúcich riadkov namiesto spoliehania sa na hrubý počet duplicít. Funkcia TABLE (dátové tabuľky analýzy What-If) je rozpoznaná, takže prechádza súborom obojsmerne, ale vyhodnocuje sa na zástupný symbol, pretože skutočnými výsledkami substitúcie sú načítané hodnoty, ktoré už Excel uložil, a HotXLS znova nespúšťa mriežku What-If

LET je najvýraznejším obmedzením, ktoré treba spomenúť. HotXLS registruje LET a nesie preň cestu vyhodnocovania, avšak parser odmieta čistý názov, ktorý nie je už definovaným názvom, takže =LET(x;10;x) zlyhá už pri parsovaní predtým, než sa vôbec dostane k vyhodnocovaču. Považujte LET za nepodporovaný, kým parser nezíska povedomie o rozsahu platnosti pre svoje viazané názvy. Malá poznámka k prenosnosti: reťazce vzorcov v týchto príkladoch používajú ako oddeľovač zoznamu bodkočiarku, takže pri zostavovaní textu vzorca sa prispôsobte oddeľovaču, ktorý očakáva váš build. Tu opísané funkcie dynamického poľa, od XLOOKUP a FILTER cez SORT, UNIQUE, SEQUENCE až po XMATCH, sa dodávajú v komponente HotXLS Delphi Excel Component, ktorého referenčná príručka vzorcov uvádza kompletný katalóg funkcií a podporované režimy argumentov každej funkcie