Dynamiska matrisformler (spill) i Delphi med HotXLS

HotXLS utvärderar Excel 365 dynamiska matrisformler (spill) inbyggt i Delphi och C++Builder: du ger det en arbetsbok vars celler innehåller XLOOKUP eller FILTER, och dess formelmotor beräknar resultatuppsättningen och spiller ut den över den förankrade utdatarektangeln, samma värden som Excel skulle producera. HotXLS Delphi Excel-komponenten modellerar en spillformel som en rotcell som äger beräkningen plus ett block av beroende celler som läser från den, vilket är hela bilden du behöver innan du skriver en rad kod

Den modellen är viktig eftersom den vardagliga uppgiften inte är abstrakt. En kund skickar en .xlsx-fil skapad i Excel 365, dess blad är fulla av =FILTER(...) och =XLOOKUP(...), och din tjänst måste reproducera exakt samma siffror headless, på en maskin utan Excel installerat, och sedan antingen läsa de spillda värdena eller skriva ett nytt spillområde själv. Dynamiska matriser ändrade formelkontraktet från "en formel, en cell" till "en formel, en rektangel av celler", och HotXLS-motorn följer det kontraktet snarare än att fejka det med ett i förväg expanderat rutnät

Hur utvärderar du XLOOKUP och FILTER i Delphi?

HotXLS skickar varje dynamisk matris-funktion via en enda ingångspunkt för utvärdering, CalcDynArrayFunc i lxCalc.pas, så att hela familjen delar samma spillkodväg. Den uppsättning som stöds är XLOOKUP och FILTER (de ursprungliga två), tillsammans med XMATCH, SORT, UNIQUE och SEQUENCE. Var och en returnerar en tvådimensionell variantmatris snarare än en skalär: SEQUENCE(3;2;1;1) ger ett 3x2-rutnät, SORT sorterar om rader efter valfri nyckelkolumn stigande eller fallande, UNIQUE komprimerar dubblettrader och behåller den första förekomsten, och XMATCH rapporterar den 1-baserade positionen för ett värde under samma exakta, jokertecken-, näst minsta- och näst största-matchningslägen som XLOOKUP. Till skillnad från de skalära statistiska fördelningsfunktionerna, som returnerar ett tal per anrop, returnerar dessa funktioner en hel form

För att beräkna en arbetsbok som redan innehåller dessa formler öppnar du den, anropar Recalculate en gång och läser cellerna som spillet landade i. TXLSXWorkbook.Recalculate går igenom beroendegrafen och utvärderar varje smutsig formel i topologisk ordning, så att en spillrot beräknas en enda gång och dess element skrivs direkt in i medlemslistans celler

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;

Om arbetsboken behöver en funktion som motorn inte levereras med, låter samma utvärderare dig ansluta din egen logik via kroken för anpassade kalkylbladsfunktioner, och en anpassad funktion är fri att returnera en variantmatris så att den spiller exakt som de inbyggda

Förankra spillområdet med SetArrayFormula

HotXLS gissar aldrig hur stort ett spill ska vara: du anger utdatarektangeln, och TXLSRange.SetArrayFormula förankrar formeln till den. Metoden kompilerar formeln en gång, lagrar syntax-trädet i rotcellen uppe till vänster tillsammans med det förankrade området och ger varje annan cell i rektangeln en lättviktsformel som refererar svagt till roten. Vid återberäkning utvärderas roten en gång och dess matriselement sprids direkt till varje medlems-cell, vilket också är hur en spillrot visas som en nod i den inkrementella återberäkningens beroendegraf. Detta är den väg man ska använda när jobbet är att skriva ett spillområde tillbaka till filen snarare än att läsa ut ett

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 explicit förankring är en gräns som är värd att nämna tydligt. HotXLS ändrar inte automatiskt storlek på ett spill på det sätt som det interaktiva Excel gör. Excel krymper och växer det spillda området när dess indata ändras och genererar #SPILL! när målblocket är upptaget. I HotXLS är rektangeln du skickar till SetArrayFormula den rektangel du får, så du anpassar storleken efter det resultat du förväntar dig, och om en funktion producerar fler rader än du förankrade har överskottet ingenstans att landa

Elementvis matris-broadcasting över operatörer

HotXLS tillämpar de aritmetiska operatörerna element för element när någon av sidorna i operationen är en matris. Hjälparen ApplyArrayBinaryOp täcker +, -, *, / och ^ över 1D- och 2D-variantmatriser: en skalär operand sprids till varje element, två matriser måste matcha i form annars nekas operationen, och division med noll sprids som ett fel snarare än att krascha. Den skalära aritmetiska banan är orörd, så detta aktiveras endast när minst en operand verkligen är en matris, till exempel ett spillt område eller ett matris-funktionsresultat. Det innebär att en formel som =D2:D13*1.1 förankrad över en kolumn multiplicerar varje element i tur och ordning, och =D2:D13*E2:E13 multiplicerar två kolumner med samma form position för position

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

Vad gör @-operatören jämfört med en äldre CSE-matris?

HotXLS läser ett explicit @ som referenssnittsoperatör (reference-intersection), inte som den radväljande implicita skärningen i levande Excel. I motorn returnerar A1:A3 @ B1:B3 den enskilda cellen där de två områdena korsar varandra, och operatören binder hårdare än ^ och lösare än %. En ärlig kommentar hör till detta: den implicita blankstegsseparerade skärningsformen är inte tokeniserad ännu eftersom lexern fortfarande hoppar över blanksteg, så du får skärningspunkter endast via den explicita @-symbolen

Den djupare skillnaden är mellan en äldre CSE-matrisformel och en modern dynamisk matris, och i HotXLS flyter båda genom samma förankringsmekanism. En klassisk matrisformel var blocket {=...} som matades in med Ctrl+Shift+Enter över ett förhandsvalt block, där varje cell delade en kompilerad formel. En dynamisk matris är en enda formel vars resultat avgör dess egen form. HotXLS förenar dem under SetArrayFormula: du namnger alltid rektangeln, roten äger det kompilerade trädet och medlemmarna refererar till det, oavsett om formeln är ett matrisuttryck av gammal typ eller en ny SORT. Vad du aldrig får är Excels automatiska spridning där området tyst växer om sig självt, och att ha den gränsen i åtanke förhindrar att de två mentala modellerna suddas ut

Var stödet för dynamiska matriser upphör

Att känna till gränserna sparar en eftermiddag av felsökning. SORT respekterar en riktning per nyckel, stigande som standard och fallande när ordningsargumentet är negativt; jämförelseriktningen är en plats som är värd att testa dina egna nyckelkolumner mot Excel, eftersom en omvänd jämförelse tyst returnerar rader i motsatt ordning istället för att misslyckas. UNIQUE behåller den första förekomsten av varje distinkt rad, vilket motorn bekräftar genom att skanna de tidigare raderna snarare än att lita på en rå dubbletträkning. Funktionen TABLE (känslighetsanalysens datatabeller) identifieras så att den skickas fram och tillbaka i filen, men den utvärderas till en platshållare eftersom de verkliga substitutionsresultaten är de cachade värden som Excel redan har lagrat, och HotXLS kör inte om what-if-rutnätet

LET är den skarpaste begränsningen att påpeka. HotXLS registrerar LET och bär på en utvärderingsbana för det, men parsern avvisar ett tomt namn som inte är ett redan definierat namn, så =LET(x;10;x) misslyckas under parsing innan utvärderaren ens nås. Behandla LET som ej stött tills parsern får räckviddsmedvetenhet (scope awareness) för sina bundna namn. En mindre portabilitetsnotis: formelsträngarna i dessa exempel använder motorns listseparator, ett semikolon, så matcha den separator som din version förväntar sig när du sätter ihop formeltext. De dynamiska matris-funktionerna som täcks här, från XLOOKUP och FILTER till SORT, UNIQUE, SEQUENCE och XMATCH, levereras i HotXLS Delphi Excel Component, vars formelreferens listar hela funktionskatalogen och varje funktions stödda argumentlägen