Dinaminių masyvų išsiliejimo formulės Delphi aplinkoje naudojant HotXLS

HotXLS atlieka „Excel 365“ dinaminių masyvų išsiliejimo (spill) formulių įvertinimą tiesiogiai Delphi bei C++Builder aplinkose: pateikiate jai darbo knygą, kurios langeliuose yra XLOOKUP arba FILTER, ir jos formulių variklis apskaičiuoja rezultatų rinkinį bei išlieja jį per prikabintą išvesties stačiakampį — tas pačias reikšmes, kurias sukurtų „Excel“. HotXLS Delphi Excel komponentas išsiliejimo formulę modeliuoja kaip vieną šakninį langelį, kuriam priklauso skaičiavimas, bei priklausomų langelių bloką, kuris nuskaito duomenis iš jo; tai yra visas vaizdas, kurio jums reikia prieš parašant kodo eilutę

Šis modelis yra svarbus, nes kasdienė užduotis nėra abstrakcija. Klientas atsiunčia .xlsx failą, sukurtą su „Excel 365“, jo lapai pilni formulių =FILTER(...) ir =XLOOKUP(...), o jūsų paslauga turi atkurti tuos pačius skaičius beekraniniu režimu, kompiuteryje, kuriame nėra įdiegtos „Excel“ programos, o tada arba nuskaityti išsiliejusias reikšmes, arba patys įrašyti naują išsiliejimo sritį. Dinaminiai masyvai pakeitė skaičiavimo principą iš „viena formulė — vienas langelis“ į „viena formulė — langelių stačiakampis“, ir HotXLS variklis seka šiuo principu, užuot imitavęs jį iš anksto išplėstu tinkleliu

Kaip Delphi aplinkoje įvertinti XLOOKUP ir FILTER?

HotXLS nukreipia kiekvieną dinaminio masyvo funkciją per vieną įvertintojo įėjimo tašką CalcDynArrayFunc modulyje lxCalc.pas, todėl visa šeima dalijasi vienu išsiliejimo kodo keliu. Palaikomą rinkinį sudaro XLOOKUP ir FILTER (pradiniai du), prie kurių prisijungia XMATCH, SORT, UNIQUE ir SEQUENCE. Kiekviena iš jų grąžina dvimatį variantų masyvą, o ne skaliarinį dydį: SEQUENCE(3;2;1;1) sukuria 3x2 tinklelį, SORT perrikiuoja eilutes pagal bet kurį raktinį stulpelį didėjimo arba mažėjimo tvarka, UNIQUE sutraukia dubliuotas eilutes išlaikydama pirmąjį pasitaikymą, o XMATCH praneša apie 1 pagrindu veikiančią reikšmės padėtį pagal tokius pačius tikslaus, pakaitos simbolių, artimiausio mažesnio ir artimiausio didesnio atitikimo režimus kaip XLOOKUP. Skirtingai nuo skaliarinių statistinio pasiskirstymo funkcijų, kurios grąžina po vieną skaičių vienam iškvietimui, šios funkcijos grąžina visą formą

Norėdami apskaičiuoti darbo knygą, kurioje jau yra šios formulės, atidarykite ją, vieną kartą iškvieskite Recalculate ir nuskaitykite langelius, į kuriuos pateko išsiliejusios reikšmės. TXLSXWorkbook.Recalculate pereina priklausomybių grafiką ir įvertina kiekvieną pakeistą formulę topologine tvarka, todėl išsiliejimo šaknis apskaičiuojama vieną kartą, o jos elementai įrašomi tiesiai į atitinkamus langelius

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;

Jei darbo knygai reikalinga funkcija, kurios variklis neturi, tas pats įvertintojas leidžia prijungti jūsų pačių logiką per pasirinktinių skaičiuoklės funkcijų jungtį, o pasirinktinė funkcija gali laisvai grąžinti variantų masyvą, todėl ji išsilieja tiksliai taip pat, kaip ir gamyklinės funkcijos

Išsiliejimo diapazono prikabinimas naudojant SetArrayFormula

HotXLS niekada nespėlioja, kokio dydžio turėtų būti išsiliejimas: jūs patys nurodote išvesties stačiakampį, o TXLSRange.SetArrayFormula prikabina formulę prie jo. Metodas vieną kartą sukompiliuoja formulę, išsaugo sintaksės medį viršutiniame kairiajame šakniniame langelyje kartu su prikabintu diapazonu ir suteikia kiekvienam kitam stačiakampio langeliui lengvą formulę, kuri turi silpnąją nuorodą į šaknį. Perskaičiavimo metu šaknis įvertinama vieną kartą, o jos matricos elementai transliuojami tiesiai į kiekvieną langelį; būtent tai leidžia išsiliejimo šakniai rodytis kaip vienam mazgui prieaugio perskaičiavimo priklausomybių grafike. Šį kelią verta naudoti tada, kai užduotis yra įrašyti išsiliejimo sritį atgal į failą, o ne nuskaityti ją

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;

Aiškaus prikabinimo pasekmė yra riba, kurią verta pasakyti atvirai: HotXLS automatiškai nekeičia išsiliejimo dydžio taip, kaip tai daro interaktyvi „Excel“ programa. „Excel“ padidina ir sumažina išsiliejusią sritį keičiantis jos įvestims ir iškelia #SPILL! klaidą, kai tiksliniai langeliai yra užimti. HotXLS aplinkoje stačiakampis, kurį perduodate metodui SetArrayFormula, yra būtent toks stačiakampis, kokį gausite, todėl nustatykite jo dydį pagal laukiamą rezultatą; jei funkcija sugeneruos daugiau eilučių, nei prikabinote, perteklius neturės kur nusileisti

Elementinis masyvų transliavimas per operatorius

HotXLS taiko aritmetinius operatorius elementas po elemento, kai bet kuri operacijos pusė yra masyvas. Pagalbinė funkcija ApplyArrayBinaryOp apima +, -, *, / ir ^ operacijas vienmačiams bei dvimačiams variantų masyvams: skaliarinis operandas transliuojamas į kiekvieną elementą, du masyvai turi sutapti savo forma, kitaip operacija atmetama, o dalyba iš nulio plinta kaip klaida, o ne užlūžta. Skaliarinis aritmetikos kelias lieka nepaliestas, zodžiu, tai suveikia tik dalinai kai bent vienas operandas tikrai yra masyvas, pavyzdžiui, išsiliejęs diapazonas arba matricos funkcijos rezultatas. Tai reiškia, kad tokia formulė kaip =D2:D13*1.1, prikabinta virš stulpelio, paeiliui padaugina kiekvieną elementą, o =D2:D13*E2:E13 padaugina du vienodos formos stulpelius pozicija po pozicijos

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

Ką daro @ operatorius, palyginti su pasenusiu CSE masyvu?

HotXLS nuskaito aiškų @ kaip nuorodos sankirtos operatorių, o ne kaip eilučių parinkimo numanomą sankirtą gyvoje „Excel“ programoje. Variklyje A1:A3 @ B1:B3 grąžina vieną langelį, kuriame susikerta du diapazonai, o operatorius susieja stipriau nei ^ ir silpniau nei %. Verta pastebėti vieną dalyką: numanoma sankirtos forma, atskirta tarpais, dar nėra leksiškai apdorojama, nes leksinis analizatorius vis dar praleidžia tarpus, todėl sankirtą gausite tik per aiškų @ simbolį

Gilesnis skirtumas yra tarp pasenusios CSE masyvo formulės ir šiuolaikinio dinaminio masyvo, ir HotXLS sistemoje abu teka per tą patį prikabinimo mechanizmą. Klasikinė masyvo formulė buvo {=...} blokas, įvedamas naudojant Ctrl+Shift+Enter virš iš anksto parinkto dydžio srities, kur kiekvienas langelis dalijosi viena sukompiliuota formule. Dinaminis masyvas yra viena formulė, kurios rezultatas nustato savo formą. HotXLS sujungia juos naudodama SetArrayFormula: visada nurodote stačiakampį, šaknis turi sukompiliuotą medį, o nariai nurodo į ją, nesvarbu, ar formulė yra senojo stiliaus matricos išraiška, ar naujasis SORT. Ko niekada negausite, tai „Excel“ automatinio išsiplėtimo, kai sritis tyliai perskaičiuojama iš naujo; šios ribos atsiminimas neleidžia susilieti dviem mąstymo modeliams

Kur sustoja dinaminių masyvų palaikymas

Riboženklių žinojimas sutaupo daug laiko derinant kodą. SORT atsižvelgia į kiekvieno rakto kryptį (didėjimo tvarka pagal numatymą ir mažėjimo tvarka, kai rikiuojamas argumentas yra neigiamas); palyginimo kryptis yra vieta, kurią verta išbandyti su savo raktiniais stulpeliais lyginant su „Excel“, nes apverstas palyginimas tyliai grąžina eilutes priešinga tvarka, o ne sukelia klaidą. UNIQUE išlaiko pirmąjį kiekvienos skirtingos eilutės pasitaikymą, ką variklis patvirtina nuskaitydamas ankstesnes eilutes, o ne pasitikėdamas neapdorotu dublikatų skaičiumi. TABLE funkcija (What-If Analysis duomenų lentelės) yra atpažįstama, zodžiu, ji išlieka faile, tačiau ji yra įvertinama kaip vietos rezervuaras, nes tikrieji pakeitimo rezultatai yra talpykloje esančios reikšmės, kurias „Excel“ jau išsaugojo, o HotXLS iš naujo nevykdo what-if tinklelio

LET yra didžiausias apribojimas, kurį verta paminėti. HotXLS užregistruoja LET ir turi jos įvertinimo kelią, tačiau analizatorius atmeta paprastą pavadinimą, kuris nėra iš anksto apibrėžtas, todėl =LET(x;10;x) nepavyksta dar analizavimo metu, prieš pasiekiant įvertintoją. Laikykite LET nepalaikoma, kol analizatorius neįgis jos susietų pavadinimų srities suvokimo. Nedidelė suderinamumo pastaba: šiuose pavyzdžiuose formulių eilutėse naudojamas variklio sąrašo skirtukas yra kabliataškis, todėl surinkdami formulės tekstą suderinkite jį su bet kokiu skirtuku, kurio tikisi jūsų programos versija. Čia aprašytos dinaminių masyvų funkcijos, nuo XLOOKUP ir FILTER iki SORT, UNIQUE, SEQUENCE bei XMATCH, yra platinamos HotXLS Delphi Excel komponente, kurio formulių žinyne pateikiamas visas funkcijų katalogas bei kiekvienos funkcijos palaikomi argumentų režimai