Dynaamisten taulukoiden spill-kaavat Delphissä HotXLS:n avulla

HotXLS arvioi Excel 365:n dynaamisten taulukoiden spill-kaavoja natiivisti Delphissä ja C++Builderissa: annat sille työkirjan, jonka solut sisältävät XLOOKUP- tai FILTER-kaavoja, ja sen kaavamoottori laskee tulosjoukon ja levittää (spill) sen kiinnitetyn tulossuorakulmion yli — aivan kuten Excel tuottasi. HotXLS Delphi Excel -komponentti mallintaa spill-kaavan yhtenä juurisoluna, joka omistaa laskennan, ja riippuvaisina soluina, jotka lukevat arvon sieltä. Tämä on koko kuva, jonka tarvitset ennen koodin kirjoittamista

Mallilla on merkitystä, koska jokapäiväinen tehtävä ei ole abstrakti. Asiakas lähettää Excel 365:llä luodun .xlsx-tiedoston, jonka taulukot ovat täynnä =FILTER(...)- ja =XLOOKUP(...)-kaavoja, ja palvelusi on toistettava täsmälleen samat luvut ilman käyttöliittymää (headless) koneella, johon ei ole asennettu Exceliä. Sen jälkeen sinun on joko luettava spill-arvot tai kirjoitettava oma uusi spill-alueesi. Dynaamiset taulukot muuttivat laskentasopimuksen periaatteesta 'yksi kaava, yksi solu' periaatteeseen 'yksi kaava, solujen suorakulmio', ja HotXLS-moottori noudattaa tätä sopimusta sen sijaan, että se teeskentelisi sitä esilaajennetulla ruudukolla

Miten XLOOKUP ja FILTER arvioidaan Delphissä?

HotXLS ohjaa jokaisen dynaamisen taulukkofunktion yhden arviointipisteen kautta (CalcDynArrayFunc tiedostossa lxCalc.pas), joten koko perhe jakaa saman spill-koodipolun. Tuettu joukko koostuu aluksi funktioista XLOOKUP ja FILTER, joiden seuraksi liittyvät XMATCH, SORT, UNIQUE ja SEQUENCE. Kukin palauttaa kaksiulotteisen varianttitaulukon skalaarin sijasta: SEQUENCE(3;2;1;1) tuottaa 3x2-ruudukon, SORT järjestää rivit minkä tahansa avainsarakkeen mukaan nousevasti tai laskevasti, UNIQUE poistaa kaksoiskappaleet säilyttäen ensimmäisen esiintymän, ja XMATCH ilmoittaa arvon 1-perusteisen sijainnin käyttäen samoja tarkka-, villi kortti-, seuraavaksi pienempi- ja seuraavaksi suurempi -hakutiloja kuin XLOOKUP. Toisin kuin skalaariset tilastolliset jakaumafunktiot, jotka palauttavat yhden luvun kutsua kohden, nämä funktiot palauttavat kokonaisen muodon

Laskeaksesi työkirjan, joka jo sisältää nämä kaavat, avaa se, kutsu Recalculate-metodia kerran ja lue solut, joihin spill laskeutui. TXLSXWorkbook.Recalculate käy läpi riippuvuuskaavion ja laskee kunkin likaisen kaavan topologisessa järjestyksessä, joten spill-juuri lasketaan vain kerran ja sen elementit kirjoitetaan suoraan jäsensoluihin

var
  Book: TXLSXWorkbook;
  Sheet: TXLSXWorksheet;
  r, c: Integer;
begin
  Book := TXLSXWorkbook.Create;
  try
    Book.Open('from-customer.xlsx');
    Book.Recalculate;                 // arvioi jokainen kaava, mukaan lukien spill-kaavat
    Sheet := Book.Sheets[1];          // Sheets on 1-perusteinen
    // Lue takaisin lohko, johon XLOOKUP / FILTER valui (spilled), esimerkiksi 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;

Jos työkirja tarvitsee funktion, jota moottori ei toimita, sama arvioija mahdollistaa oman logiikkasi kytkemisen mukautettujen taulukkofunktioiden kytkimen kautta. Mukautettu funktio voi vapaasti palauttaa varianttitaulukon, joten se valuu (spill) täsmälleen samalla tavalla kuin sisäänrakennetutkin

Spill-alueen kiinnittäminen SetArrayFormula-kutsulla

HotXLS ei koskaan arvaa, kuinka suuri spill-alueen pitäisi olla: nimeät kohdesuorakulmion, ja TXLSRange.SetArrayFormula kiinnittää kaavan siihen. Menetelmä kääntää kaavan kerran, tallentaa syntaksipuun vasemman yläkulman juurisoluun yhdessä kiinnitetyn alueen kanssa ja antaa kaikille muille suorakulmion soluille kevyen kaavan, joka viittaa heikosti juureen. Uudelleenlaskennassa juuri arvioidaan kerran ja sen matriisielementit lähetetään suoraan kuhunkin jäsensoluun, mikä on myös tapa, jolla spill-juuri näkyy yhtenä solmuna inkrementaalisen uudelleenlaskennan riippuvuuskaaviossa. Tätä polkua käytetään silloin, kun tehtävänä on kirjoittaa spill-alue takaisin tiedostoon sen sijaan, että se luettaisiin sieltä

var
  Book: TXLSXWorkbook;
  Sheet: TXLSXWorksheet;
begin
  Book := TXLSXWorkbook.Create;
  try
    Book.Open('report.xlsx');
    Sheet := Book.Sheets[1];
    // Sovita suorakulmio tuloksen mukaan: 3 riviä x 2 saraketta ruudukko.
    if Sheet.Range['A1:B3'].SetArrayFormula('=SEQUENCE(3;2;1;1)') > 0 then
      Book.Recalculate;               // täytä A1:B3 luvuilla 1..6 riveittäin
    Book.SaveAs('report-out.xlsx');
  finally
    Book.Free;
  end;
end;

Eksplisiittisen kiinnittämisen seurauksena on raja, joka on syytä sanoa suoraan: HotXLS ei muuta spill-alueen kokoa automaattisesti siten kuin interaktiivinen Excel tekee. Excel kasvattaa ja kutistaa valunutta aluetta syötteiden muuttuessa ja nostaa virheen #SPILL!, jos kohdesolut ovat varattuja. HotXLS:ssä suorakulmio, jonka välität SetArrayFormula-metodille, on se suorakulmio jonka saat, joten mitoitat sen odottamasi tuloksen mukaan. Jos funktio tuottaa enemmän rivejä kuin kiinnitit, ylijäämällä ei ole paikkaa mihin laskeutua

Elementtikohtainen taulukkolähetys operaattoreiden yli

HotXLS soveltaa aritmeettisia operaattoreita elementti kerrallaan, kun jompikumpi operaation puoli on taulukko. Apuri ApplyArrayBinaryOp kattaa operaatiot +, -, *, / ja ^ 1-D ja 2-D varianttitaulukoille: skalaarioperandi lähetetään kuhunkin elementtiin, kahden taulukon on oltava samanmuotoisia tai muuten operaatio hylätään, ja nollalla jakaminen etenee virheenä kaatumisen sijaan. Skalaariaritmeettiseen polkuun ei kosketa, joten tämä aktivoituu vain silloin, kun vähintään yksi operandi on aidosti taulukko, kuten spill-alue tai matriisifunktion tulos. Tämä tarkoittaa, että kaava kuten =D2:D13*1.1 kiinnitettynä sarakkeen yli kertoo kunkin elementin vuorotellen, ja =D2:D13*E2:E13 kertoo kaksi samanmuotoista saraketta paikka kohdalta

// Skalaarinen lähetys: jokainen ankkurin solu saa arvon D(n) * 1.1.
Sheet.Range['F2:F13'].SetArrayFormula('=D2:D13*1.1');
// Kaksi samanmuotoista taulukkoa kerrotaan elementti kerrallaan.
Sheet.Range['G2:G13'].SetArrayFormula('=D2:D13*E2:E13');
Book.Recalculate;

Mitä @-operaattori tekee verrattuna vanhaan CSE-taulukkoon?

HotXLS lukee eksplisiittisen @-merkin viittauksen leikkauspisteoperaattorina (reference-intersection operator), ei Excelin rivinvalitsevana implisiittisenä leikkauksena. Moottorissa A1:A3 @ B1:B3 palauttaa sen yksittäisen solun, jossa kaksi aluetta risteävät, ja operaattori sitoo tiukemmin kuin ^ ja löyhemmin kuin %. Yksi rehellinen huomautus tähän liittyen: implisiittistä välilyönnillä erotettua leikkausmuotoa ei ole vielä tokenisoitu, koska jäsennin ohittaa edelleen tyhjät välit, joten leikkauspiste saadaan vain eksplisiittisen @-symbolin kautta

Syvempi ero on vanhan CSE-taulukkokaavan ja modernin dynaamisen taulukon välillä, ja HotXLS:ssä molemmat virtaavat saman kiinnityskoneiston läpi. Klassinen taulukkokaava oli {=...}-lohko, joka syötettiin näppäinyhdistelmällä Ctrl+Shift+Enter esivalitun alueen yli, jolloin jokainen solu jakoi saman käännetyn kaavan. Dynaaminen taulukko on yksi kaava, jonka tulos määrittää oman muotonsa. HotXLS yhdistää ne SetArrayFormula-kutsun alle: nimeät aina suorakulmion, juuri omistaa käännetyn puun ja jäsenet viittaavat siihen, olipa kaava vanhanaikainen matriisilauseke tai uusi SORT. Mitä et koskaan saa, on Excelin automaattinen aaltoilu, jossa alue muuttaa kokoaan hiljaisesti. Tämän rajan pitäminen mielessä estää näiden kahden ajatusmallin hämmentymisen

Mihin dynaamisten taulukoiden tuki pysähtyy

Rajojen tunteminen säästää iltapäivän virheenkorjaukselta. SORT noudattaa avainkohtaista suuntaa, nouseva oletuksena ja laskeva silloin, kun järjestysargumentti on negatiivinen. Vertailusuunta on paikka, jota vastaan omat avainsarakkeesi kannattaa testata suhteessa Exceliin, sillä käänteinen vertailu palauttaa rivit hiljaisesti päinvastaisessa järjestyksessä virheen sijaan. UNIQUE säilyttää kunkin erillisen rivin ensimmäisen esiintymän, minkä moottori varmistaa skannaamalla aiemmat rivit raa'an kaksoiskappalemäärän luottamisen sijaan. TABLE-funktio (mitä-jos-analyysin datataulukot) tunnistetaan siten, että se kulkee tiedoston läpi, mutta se arvioidaan paikkamerkiksi, koska todelliset sijoitustulokset ovat Excelin jo tallentamia välimuistiarvoja eikä HotXLS aja mitä-jos-ruudukkoa uudelleen

LET on suurin kutsuttava rajoitus. HotXLS rekisteröi LET-funktion ja sisältää arviointipolun sille, mutta jäsennin hylkää paljaan nimen, joka ei ole valmiiksi määritelty nimi, joten =LET(x;10;x) epäonnistuu jäsennyksessä ennen kuin arvioija koskaan saavutetaan. Käsittele LET-funktiota tukemattomana, kunnes jäsennin saa näkyvyysalueen tunnistuksen sen sidotuille nimille. Pieni yhteensopivuushuomautus: näiden esimerkkien kaavamerkkijonot käyttävät moottorin luettelonerotinta eli puolipistettä, joten vastaa sitä erotinta, jota buildisi odottaa, kun kokoat kaavatekstiä. Dynaamiset taulukkofunktiot, joita tässä käsitellään, alkaen XLOOKUPista ja FILTERistä aina SORT-, UNIQUE-, SEQUENCE- ja XMATCH-funktioihin asti, toimitetaan HotXLS Delphi Excel -komponentissa, jonka kaavaviiteopas luettelee täydellisen funktioluettelon ja kunkin funktion tuetut argumenttitilat