Τύποι Διάχυσης Δυναμικών Πινάκων (Dynamic Array Spill Formulas) στο Delphi με το HotXLS

Το HotXLS αξιολογεί εγγενώς τους τύπους διάχυσης δυναμικών πινάκων (dynamic array spill formulas) του Excel 365 στο Delphi και το C++Builder: του παραδίδετε ένα βιβλίο εργασίας του οποίου τα κελιά περιέχουν XLOOKUP ή FILTER, και η μηχανή υπολογισμού τύπων υπολογίζει το σύνολο αποτελεσμάτων και το διαχέει (spill) στο αγκυρωμένο ορθογώνιο εξόδου, τις ίδιες τιμές που θα παρήγαγε το Excel. Το στοιχείο HotXLS Delphi Excel μοντελοποιεί έναν τύπο διάχυσης ως ένα κελί-ρίζα που κατέχει τον υπολογισμό συν ένα μπλοκ εξαρτώμενων κελιών που διαβάζουν από αυτό, κάτι που αποτελεί τη συνολική εικόνα που χρειάζεστε πριν γράψετε μια γραμμή κώδικα

Αυτό το μοντέλο έχει σημασία επειδή η καθημερινή εργασία δεν είναι αφηρημένη. Ένας πελάτης στέλνει ένα αρχείο .xlsx που έχει δημιουργηθεί στο Excel 365, τα φύλλα του οποίου είναι γεμάτα με =FILTER(...) και =XLOOKUP(...), και η υπηρεσία σας πρέπει να αναπαράγει ακριβώς τους ίδιους αριθμούς χωρίς γραφικό περιβάλλον (headless), σε ένα μηχάνημα χωρίς εγκατεστημένο το Excel, και στη συνέχεια είτε να διαβάσει τις διαχυμένες τιμές είτε να γράψει μια νέα δική σας περιοχή διάχυσης. Οι δυναμικοί πίνακες άλλαξαν το συμβόλαιο υπολογισμού από "ένας τύπος, ένα κελί" σε "ένας τύπος, ένα ορθογώνιο κελιών", και η μηχανή HotXLS ακολουθεί αυτό το συμβόλαιο αντί να το προσομοιώνει με ένα εκ των προτέρων αναπτυγμένο πλέγμα

Πώς αξιολογείτε τις XLOOKUP και FILTER στο Delphi;

Το HotXLS δρομολογεί κάθε συνάρτηση δυναμικού πίνακα μέσω ενός ενιαίου σημείου εισόδου αξιολογητή, της CalcDynArrayFunc στο lxCalc.pas, οπότε ολόκληρη η οικογένεια μοιράζεται μια κοινή διαδρομή κώδικα διάχυσης. Το υποστηριζόμενο σύνολο αποτελείται από τις XLOOKUP και FILTER (οι αρχικές δύο), μαζί με τις XMATCH, SORT, UNIQUE και SEQUENCE. Κάθε μία επιστρέφει έναν δισδιάστατο πίνακα variant αντί για μια βαθμωτή τιμή (scalar): η SEQUENCE(3;2;1;1) παράγει ένα πλέγμα 3 επί 2, η SORT αναδιατάσσει τις γραμμές κατά οποιαδήποτε στήλη-κλειδί σε αύξουσα ή φθίνουσα σειρά, η UNIQUE συμπτύσσει τις διπλότυπες γραμμές διατηρώντας την πρώτη εμφάνιση, και η XMATCH αναφέρει τη θέση ενός στοιχείου (με βάση το 1) υπό τις ίδιες λειτουργίες ακριβούς αντιστοίχισης, χαρακτήρων μπαλαντέρ, επόμενης μικρότερης και επόμενης μεγαλύτερης αντιστοίχισης όπως η XLOOKUP. Σε αντίθεση με τις βαθμωτές συναρτήσεις στατιστικής κατανομής, οι οποίες επιστρέφουν έναν αριθμό ανά κλήση, αυτές οι συναρτήσεις επιστρέφουν ένα ολόκληρο σχήμα

Για να υπολογίσετε ένα βιβλίο εργασίας που περιέχει ήδη αυτούς τους τύπους, ανοίξτε το, καλέστε το Recalculate μία φορά και διαβάστε τα κελιά στα οποία προσγειώθηκε η διάχυση. Το TXLSXWorkbook.Recalculate διατρέχει το γράφημα εξαρτήσεων και αξιολογεί κάθε τροποποιημένο τύπο σε τοπολογική σειρά, οπότε η ρίζα της διάχυσης υπολογίζεται μία φορά και τα στοιχεία της γράφονται απευθείας στα μέλη των κελιών

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;

Εάν το βιβλίο εργασίας χρειάζεται μια συνάρτηση που δεν παρέχει η μηχανή, ο ίδιος αξιολογητής σάς επιτρέπει να συνδέσετε τη δική σας λογική μέσω του hook προσαρμοσμένων συναρτήσεων φύλλου εργασίας, και μια προσαρμοσμένη συνάρτηση είναι ελεύθερη να επιστρέψει έναν πίνακα variant ώστε να διαχέεται ακριβώς όπως οι ενσωματωμένες

Αγκύρωση του εύρους διάχυσης με το SetArrayFormula

Το HotXLS δεν μαντεύει ποτέ το μέγεθος που πρέπει να έχει μια διάχυση: εσείς ορίζετε το ορθογώνιο εξόδου, και το TXLSRange.SetArrayFormula αγκυρώνει τον τύπο σε αυτό. Η μέθοδος μεταγλωττίζει τον τύπο μία φορά, αποθηκεύει το συντακτικό δέντρο στο πάνω αριστερά κελί-ρίζα μαζί με το αγκυρωμένο εύρος, και δίνει σε κάθε άλλο κελί στο ορθογώνιο έναν ελαφρύ τύπο που κάνει ασθενή αναφορά (weak reference) στη ρίζα. Κατά τον επανυπολογισμό, η ρίζα αξιολογείται μία φορά και τα στοιχεία του πίνακα μεταδίδονται απευθείας σε κάθε κελί-μέλος, που είναι επίσης ο τρόπος με τον οποίο μια ρίζα διάχυσης εμφανίζεται ως ένας κόμβος στο γράφημα εξαρτήσεων σταδιακού επανυπολογισμού. Αυτή είναι η διαδρομή που πρέπει να χρησιμοποιήσετε όταν η εργασία είναι να γράψετε μια περιοχή διάχυσης πίσω στο αρχείο αντί να διαβάσετε μία

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;

Η συνέπεια της ρητής αγκύρωσης είναι ένα όριο που αξίζει να αναφερθεί ξεκάθαρα: το HotXLS δεν αλλάζει αυτόματα το μέγεθος μιας διάχυσης με τον τρόπο που το κάνει το διαδραστικό Excel. Το Excel μεγαλώνει και μικραίνει τη διαχυμένη περιοχή καθώς αλλάζουν οι είσοδοί του και εμφανίζει το σφάλμα #SPILL! όταν τα κελιά προορισμού είναι κατειλημμένα. Στο HotXLS, το ορθογώνιο που περνάτε στο SetArrayFormula είναι το ορθογώνιο που λαμβάνετε, επομένως το προσαρμόζετε στο μέγεθος του αποτελέσματος που αναμένετε, και αν μια συνάρτηση παράγει περισσότερες γραμμές από αυτές που αγκυρώσατε, το πλεόνασμα δεν έχει πού να τοποθετηθεί

Μετάδοση (broadcasting) πινάκων ανά στοιχείο στους τελεστές

Το HotXLS εφαρμόζει τους αριθμητικούς τελεστές στοιχείο προς στοιχείο όταν οποιαδήποτε πλευρά της πράξης είναι πίνακας. Η βοηθητική συνάρτηση ApplyArrayBinaryOp καλύπτει τις πράξεις +, -, *, / και ^ σε μονοδιάστατους και δισδιάστατους πίνακες variant: ένας βαθμωτός τελεστέος μεταδίδεται σε κάθε στοιχείο, δύο πίνακες πρέπει να ταιριάζουν στο σχήμα διαφορετικά η πράξη απορρίπτεται, και η διαίρεση με το μηδέν διαδίδεται ως σφάλμα αντί να προκαλέσει κατάρρευση. Η διαδρομή βαθμωτής αριθμητικής παραμένει ανέπαφη, επομένως αυτό ενεργοποιείται μόνο όταν τουλάχιστον ένας τελεστέος είναι πραγματικά πίνακας, όπως ένα εύρος διάχυσης ή το αποτέλεσμα μιας συνάρτησης πίνακα. Αυτό σημαίνει ότι ένας τύπος όπως ο =D2:D13*1.1 αγκυρωμένος σε μια στήλη πολλαπλασιάζει κάθε στοιχείο με τη σειρά, και ο =D2:D13*E2:E13 πολλαπλασιάζει δύο στήλες ίδιου σχήματος θέση προς θέση

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

Τι κάνει ο τελεστής @ σε σύγκριση με έναν παραδοσιακό πίνακα CSE;

Το HotXLS αναγιγνώσκει ένα ρητό @ ως τον τελεστή τομής αναφοράς (reference-intersection operator), και όχι ως την έμμεση τομή επιλογής γραμμής του ζωντανού Excel. Στη μηχανή, η έκφραση A1:A3 @ B1:B3 επιστρέφει το μοναδικό κελί όπου τέμνονται τα δύο εύρη, και ο τελεστής συνδέεται πιο στενά από το ^ και λιγότερο στενά από το %. Μια ειλικρινής σημείωση σχετικά με αυτό: η έμμεση μορφή τομής που διαχωρίζεται με κενό δεν έχει μετατραπεί ακόμη σε token, επειδή ο λεκτικός αναλυτής (lexer) εξακολουθεί να παραλείπει τα κενά, οπότε λαμβάνετε τομή μόνο μέσω του ρητού συμβόλου @

Η βαθύτερη διάκριση είναι μεταξύ ενός παραδοσιακού τύπου πίνακα CSE και ενός σύγχρονου δυναμικού πίνακα, και στο HotXLS και οι δύο ρέουν μέσω του ίδιου μηχανισμού αγκύρωσης. Ένας κλασικός τύπος πίνακα ήταν το μπλοκ {=...} που εισαγόταν με Ctrl+Shift+Enter σε μια προκαθορισμένου μεγέθους επιλογή, με κάθε κελί να μοιράζεται έναν μεταγλωττισμένο τύπο. Ένας δυναμικός πίνακας είναι ένας ενιαίος τύπος του οποίου το αποτέλεσμα καθορίζει το δικό του σχήμα. Το HotXLS τα ενοποιεί υπό το SetArrayFormula: ονομάζετε πάντα το ορθογώνιο, η ρίζα κατέχει το μεταγλωττισμένο δέντρο και τα μέλη αναφέρονται σε αυτό, είτε ο τύπος είναι μια παλαιού τύπου έκφραση πίνακα είτε μια νέα SORT. Αυτό που δεν λαμβάνετε ποτέ είναι η αυτόματη διάχυση του Excel όπου η περιοχή μεγαλώνει σιωπηλά, και η διατήρηση αυτού του ορίου στο μυαλό εμποδίζει το θόλωμα των δύο μοντέλων

Πού σταματά η υποστήριξη δυναμικών πινάκων

Η γνώση των ορίων εξοικονομεί χρόνο από τον εντοπισμό σφαλμάτων. Η SORT τηρεί την κατεύθυνση ανά κλειδί, η οποία είναι αύξουσα από προεπιλογή και φθίνουσα όταν το όρισμα σειράς είναι αρνητικό. Η κατεύθυνση σύγκρισης είναι ένα σημείο που αξίζει να δοκιμάσετε τις δικές σας στήλες-κλειδιά έναντι του Excel, καθώς μια αντίστροφη σύγκριση επιστρέφει σιωπηλά γραμμές με την αντίθετη σειρά αντί να αποτύχει. Η UNIQUE διατηρεί την πρώτη εμφάνιση κάθε ξεχωριστής γραμμής, κάτι που η μηχανή επιβεβαιώνει σαρώνοντας τις προηγούμενες γραμμές αντί να εμπιστεύεται έναν απλό αριθμό διπλότυπων. Η συνάρτηση TABLE (πίνακες δεδομένων ανάλυσης What-If) αναγνωρίζεται ώστε να ολοκληρώνει την κυκλική διαδρομή μέσω του αρχείου, αλλά αξιολογείται ως placeholder επειδή τα πραγματικά αποτελέσματα αντικατάστασης είναι οι προσωρινά αποθηκευμένες τιμές που έχει ήδη αποθηκεύσει το Excel, και το HotXLS δεν εκτελεί ξανά το πλέγμα what-if

Η LET είναι το πιο αυστηρό όριο που πρέπει να επισημανθεί. Το HotXLS καταχωρίζει τη LET και φέρει μια διαδρομή αξιολόγησης γι' αυτήν, αλλά ο αναλυτής (parser) απορρίπτει ένα απλό όνομα που δεν είναι ήδη καθορισμένο όνομα, οπότε η έκφραση =LET(x;10;x) αποτυγχάνει κατά την ανάλυση προτού φτάσει στον αξιολογητή. Αντιμετωπίστε τη LET ως μη υποστηριζόμενη έως ότου ο αναλυτής αποκτήσει επίγνωση εμβέλειας (scope awareness) για τα δεσμευμένα ονόματά του. Μια μικρότερη σημείωση φορητότητας: οι συμβολοσειρές τύπων σε αυτά τα παραδείγματα χρησιμοποιούν το διαχωριστικό λίστας της μηχανής, ένα ερωτηματικό (;), επομένως αντιστοιχίστε το με όποιο διαχωριστικό αναμένει η δική σας έκδοση όταν συναρμολογείτε κείμενο τύπων. Οι συναρτήσεις δυναμικών πινάκων που καλύπτονται εδώ, από τις XLOOKUP και FILTER έως τις SORT, UNIQUE, SEQUENCE και XMATCH, παρέχονται στο HotXLS Delphi Excel Component, του οποίου η αναφορά τύπων παραθέτει τον πλήρη κατάλογο συναρτήσεων και τις υποστηριζόμενες λειτουργίες ορισμάτων κάθε συνάρτησης