Dynamiske matriseformler med spill i Delphi med HotXLS

HotXLS evaluerer Excel 365 dynamiske matriseformler med spill (dynamic array spill formulas) innfødt i Delphi og C++Builder: du gir den en arbeidsbok der cellene inneholder XLOOKUP eller FILTER, og formelmotoren beregner resultatsettet og "spiller" det ut (spills) over det forankrede utdatarektangelet, nøyaktig de samme verdiene som Excel ville produsert. HotXLS Delphi Excel-komponenten modellerer en spillformel som én rotcelle som eier beregningen, pluss en blokk med avhengige celler som leser tilbake fra den. Dette er hele bildet du trenger før du skriver en eneste linje med kode

Denne modellen er viktig fordi den daglige oppgaven ikke er abstrakt. En kunde sender en .xlsx-fil bygget i Excel 365, og arkene hennes er fulle av =FILTER(...) og =XLOOKUP(...). Tjenesten din må gjenskape nøyaktig de samme tallene hodeløst (headless), på en maskin uten Excel installert, for deretter enten å lese de utspilte verdiene eller skrive et helt nytt spillområde selv. Dynamiske matriser endret beregningskontrakten fra "én formel, én celle" to "én formel, et rektangel av celler", og HotXLS-motoren følger den kontrakten i stedet for å simulere den med et forhåndsutvidet rutenett

Hvordan evaluerer du XLOOKUP og FILTER i Delphi?

HotXLS sender alle dynamiske matrisefunksjoner gjennom et enkelt inngangspunkt for evaluatoren, CalcDynArrayFunc i lxCalc.pas, slik at hele familien deler samme kodebane for spill. Det støttede settet er XLOOKUP og FILTER (de to opprinnelige), etterfulgt av XMATCH, SORT, UNIQUE og SEQUENCE. Hver av dem returnerer en todimensjonal (2-D) variantmatrise fremfor en skalar verdi: SEQUENCE(3;2;1;1) gir et 3-ganger-2 rutenett, SORT sorterer rader på nytt etter en hvilken som helst nøkkelkolonne i stigende eller synkende rekkefølge, UNIQUE slår sammen dupliserte rader og beholder den første forekomsten, og XMATCH rapporterer den 1-baserte posisjonen til en verdi under de samme treffmodusene (nøyaktig, jokertegn, neste mindre og neste større) som XLOOKUP. I motsetning til de skalare statistiske distribusjonsfunksjonene, som returnerer ett tall per kall, gir disse funksjonene tilbake en hel form (matrise)

For å beregne en arbeidsbok som allerede har disse formlene, åpner du den, kaller Recalculate én gang, og leser cellene der spillet havnet. TXLSXWorkbook.Recalculate går gjennom avhengighetsgrafen og evaluerer hver endret (dirty) formel i topologisk rekkefølge, slik at en spillrot beregnes én enkelt gang og elementene skrives direkte inn i medlemscellene

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;

Hvis arbeidsboken trenger en funksjon som motoren ikke leveres med, lar den samme evaluatoren deg koble til din egen logikk via tilkoblingen for egendefinerte regnearkfunksjoner-hooken, og en egendefinert funksjon står fritt til å returnere en variantmatrise slik at den spilles ut nøyaktig som de innebygde

Forankre spillområdet med SetArrayFormula

HotXLS gjetter aldri hvor stort et spill skal være: du navngir utdatarektangelet, og TXLSRange.SetArrayFormula forankrer formelen til det. Metoden kompilerer formelen én gang, lagrer syntakstreet på den øverste venstre rotcellen sammen med det forankrede området, og gir alle andre celler i rektangelet en lettvektsformel som refererer svakt til roten. Ved ny beregning evalueres roten én gang, og dens matriseelementer kringkastes (broadcasts) direkte til hver medlemscelle. Dette er også hvordan en spillrot vises som én enkelt node i avhengighetsgrafen for inkrementell nyberegning. Dette er veien du bør bruke når oppgaven er å skrive et spillområde tilbake til filen fremfor å lese et ut

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;

Konsekvensen av eksplisitt forankring er en begrensning verdt å si tydelig: HotXLS endrer ikke automatisk størrelsen på et spillområde slik interaktiv Excel gjør. Excel utvider og krymper spillområdet ettersom inndataene endres, og viser #SPILL! når målocellene er opptatt. I HotXLS er rektangelet du sender til SetArrayFormula det rektangelet du får, så du må tilpasse størrelsen til det resultatet du forventer, og hvis en funksjon produserer flere rader enn du har forankret, har overskytende data ingen steder å gjøre av seg

Elementvis matrise-kringkasting på tvers av operatorene

HotXLS bruker de aritmetiske operatorene element for element når en av sidene i operasjonen er en matrise. Hjelpefunksjonen ApplyArrayBinaryOp dekker +, -, *, / og ^ over endimensjonale (1-D) og todimensjonale (2-D) variantmatriser: en skalar operand kringkastes til hvert element, to matriser må ha samsvarende form ellers blir operasjonen avvist, og divisjon med null forplanter seg (propagates) som en feil i stedet for å krasje programmet. Den skalare aritmetiske banen er uberørt, så dette aktiveres kun når minst én operand faktisk er en matrise, for eksempel et spillområde eller resultatet av en matrisefunksjon. Det betyr at en formel som =D2:D13*1.1 forankret over en kolonne multipliserer hvert element etter tur, og =D2:D13*E2:E13 multipliserer to kolonner med lik form posisjon for posisjon

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

Hva gjør @-operatoren sammenlignet med en eldre CSE-matrise?

HotXLS leser en eksplisitt @ som en referansekrysning-operator (reference-intersection operator), ikke som den radvelgende implisitte krysningen i vanlig Excel. I motoren returnerer A1:A3 @ B1:B3 den enkelte cellen der de to områdene krysser hverandre, og operatoren binder sterkere enn ^ og svakere enn %. En ærlig kommentar følger med dette: den implisitte krysningen atskilt med mellomrom er ikke tokenisert ennå fordi leksikalsk analysator (lexer) fortsatt hopper over mellomrom, så du får kun krysning via det eksplisitte @-symbolet

Det dypere skillet går mellom en eldre CSE-matriseformel og en moderne dynamisk matrise, og i HotXLS flyter begge gjennom det samme forankringsmaskineriet. En klassisk matriseformel var {=...}-blokken lagt inn med Ctrl+Shift+Enter over et forhåndsdefinert utvalg, der hver celle delte én enkelt kompilert formel. En dynamisk matrise er en enkelt formel der resultatet bestemmer dets egen form. HotXLS forener dem under SetArrayFormula: du navngir alltid rektangelet, roten eier det kompilerte treet, og medlemmene refererer til det, enten formelen er et matriseuttrykk av den gamle typen eller en ny SORT. Det du aldri får, er Excels automatiske ringvirkning der området utvides i det stille, og å ha dette skillet i bakhodet forhindrer at de to mentale modellene flyter sammen

Hvor støtten for dynamiske matriser stopper

Å kjenne grensene sparer deg for en ettermiddag med feilsøking. SORT respekterer en sorteringsretning per nøkkel, stigende som standard og synkende når rekkefølge-argumentet er negativt; sammenligningsretningen er et sted det er verdt å teste dine egne nøkkelkolonner mot Excel, siden en omvendt sammenligning i det stille returnerer rader i motsatt rekkefølge i stedet for å feile. UNIQUE beholder den første forekomsten av hver distinkte rad, noe motoren bekrefter ved å skanne tidligere rader i stedet for å stole på en rå duplikat-teller. TABLE-funksjonen (hva-skjer-hvis-analysedatatabeller / What-If Analysis data tables) gjenkjennes slik at den overføres gjennom filen, men den evalueres til en plassholder fordi de virkelige substitusjonsresultatene er de hurtigbufrede verdiene Excel allerede har lagret, og HotXLS kjører ikke hva-skjer-hvis-rutenettet på nytt

LET er den klareste begrensningen å merke seg. HotXLS registrerer LET og har en evalueringsbane for den, men parseren avviser et nakent navn (bare name) som ikke allerede er definert, slik at =LET(x;10;x) feiler under parsing før evaluatoren i det hele tatt nås. Behandle LET som ikke-støttet inntil parseren får skop-bevissthet (scope awareness) for sine bundne navn. En mindre portabilitetsmerknad: formelstrengene i disse eksemplene bruker motorens listeseparator, som er semikolon, så sørg for å tilpasse dette til den separatoren din byggversjon forventer når du setter sammen formeltekst. De dynamiske matrisefunksjonene som dekkes her, fra XLOOKUP og FILTER til SORT, UNIQUE, SEQUENCE og XMATCH, leveres i HotXLS Delphi Excel Component, der formelreferansen lister opp hele funksjonskatalogen og hver funksjons støttede argumentmoduser