נוסחאות גלישה (Spill) של מערכים דינמיים בדלפי עם HotXLS

HotXLS מעריכה נוסחאות גלישה (spill) של מערכים דינמיים של Excel 365 באופן טבעי בדלפי וב-C++Builder: אתה מגיש לה חוברת עבודה שאתאיה מכילים XLOOKUP או FILTER, ומנוע הנוסחאות שלה מחשב את קבוצת התוצאות ומזרים (spills) אותה על פני מלבן הפלט המעוגן, אותם ערכים ש-Excel הייתה מפיקה. רכיב ה-Delphi Excel של HotXLS ממדל נוסחת גלישה כתא שורש אחד שבבעלותו החישוב בתוספת בלוק של תאים תלויים הקוראים ממנו בחזרה, וזו התמונה המלאה שאתה צריך לפני כתיבת שורת קוד

המודל הזה חשוב מכיוון שהמשימה היומיומית אינה מופשטת. לקוח שולח קובץ .xlsx שנבנה ב-Excel 365, הגיליונות שלו מלאים ב-=FILTER(...) וב-=XLOOKUP(...), והשירות שלך צריך לשחזר את אותם מספרים בדיוק ללא ממשק משתמש (headless), במחשב ללא Excel מותקן, ואז לקרוא את הערכים שגלשו או לכתוב אזור גלישה חדש משלך. מערכים דינמיים שינו את חוזה החישוב מ-\"נוסחה אחת, תא אחד\" ל-\"נוסחה אחת, מלבן של תאים\", ומנוע HotXLS עוקב אחר חוזה זה במקום לזייף אותו באמצעות גריד שהורחב מראש

כיצד מעריכים את XLOOKUP ו-FILTER בדלפי?

HotXLS מנתבת כל פונקציית מערך דינמי דרך נקודת כניסה אחת של מעריך, CalcDynArrayFunc ב-lxCalc.pas, כך שכל המשפחה חולקת נתיב קוד גלישה אחד. הסט הנתמך הוא XLOOKUP ו-FILTER (שני המקוריים), שאליהם מצטרפים XMATCH,‏ SORT,‏ UNIQUE ו-SEQUENCE. כל אחת מחזירה מערך וריאנט דו-ממדי במקום סקלר: SEQUENCE(3;2;1;1) מניבה גריד של 3 על 2,‏ SORT מסדרת מחדש שורות לפי עמודת מפתח כלשהי בסדר עולה או יורד, UNIQUE מכווצת שורות כפולות תוך שמירה על המופע הראשון, ו-XMATCH מדווחת על המיקום מבוסס 1 של ערך תחת אותם מצבי התאמה מדויקת, תו כללי (wildcard), הבא-הקטן-יותר והבא-הגדול-יותר כמו XLOOKUP. בניגוד לפונקציות התפלגות סטטיסטית הסקלריות, המחזירות מספר אחד לכל קריאה, פונקציות אלו מחזירות צורה שלמה

כדי לחשב חוברת עבודה שכבר נושאת את הנוסחאות הללו, פתח אותה, קרא ל-Recalculate פעם אחת, וקרא את התאים שאליהם הגיעה הגלישה. TXLSXWorkbook.Recalculate עוברת על גרף התלויות ומעריכה כל נוסחה מלוכלכת בסדר טופולוגי, כך ששורש גלישה מחושב פעם אחת בלבד והאלמנטים שלו נכתבים ישירות לתוך תאי האיברים

אם חוברת העבודה זקוקה לפונקציה שהמנוע אינו מספק, אותו מעריך מאפשר לך לחבר לוגיקה משלך דרך הוק (hook) של פונקציות גיליון מותאמות אישית, ופונקציה מותאמת אישית חופשית להחזיר מערך וריאנט כך שתגלוש בדיוק כמו אלו המובנות

עיגון טווח הגלישה באמצעות SetArrayFormula

HotXLS לעולם אינה מנחשת מה צריך להיות גודל הגלישה: אתה מציין את מלבן הפלט, ו-TXLSRange.SetArrayFormula מעגנת את הנוסחה אליו. השיטה מקמפלת את הנוסחה פעם אחת, מאחסנת את עץ התחביר על תא השורש השמאלי-עליון יחד עם הטווח המעוגן, ומעניקה לכל תא אחר במלבן נוסחה קלת משקל המפנה בהתייחסות חלשה לשורש. בעת חישוב מחדש, השורש מוערך פעם אחת ואלמנטי המטריצה שלו מופצים ישירות לכל תא חבר, וזו גם הדרך שבה שורש גלישה מופיע כצומת אחד בגרף התלויות של חישוב מחדש אינקרמנטלי. זהו הנתיב שבו יש להשתמש כאשר המשימה היא לכתוב אזור גלישה בחזרה לקובץ ולא לקרוא אחד החוצה

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 מכסה את +,‏ -,‏ *,‏ / ו-^ על פני מערכי וריאנט חד-ממדיים ודו-ממדיים: אופרנד סקלרי מופץ לכל אלמנט, שני מערכים חייבים להתאים בצורתם או שהפעולה נדחית, וחלוקה באפס מופצת כשגיאה במקום לקרוס. נתיב האריתמטיקה הסקלרית אינו מושפע, ולכן זה מופעל רק כאשר לפחות אופרנד אחד הוא באמת מערך, כגון טווח שגלש או תוצאת פונקציית מטריצה. פירוש הדבר הוא שנוסחה כמו =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 קוראת @ מפורש כאופרטור הצטלבות-התייחסות, ולא כהצטלבות המשתמעת של בחירת שורות של Excel חיה. במנוע, A1:A3 @ B1:B3 מחזיר את התא הבודד שבו שני הטווחים מצטלבים, והאופרטור נקשר חזק יותר מ-^ וחלש יותר מ-%. הערת כנות אחת נלווית לכך: צורת ההצטלבות המשתמעת המופרדת ברווחים אינה עוברת טוקניזציה עדיין, מכיוון שהלקסר (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) עבור השמות הקשורים שלו. הערת ניידות קטנה יותר: מחרוזת הנוסחאות בדוגמאות אלה משתמשות במפריד הרשימות של המנוע, נקודה-פסיק, ולכן התאם אותו לכל מפריד שהבנייה שלך מצפה לו כאשר אתה מרכיב טקסט של נוסחה. פונקציות המערכים הדינמיים המכוסות כאן, מ-XLOOKUP ו-FILTER ועד SORT,‏ UNIQUE,‏ SEQUENCE ו-XMATCH, מגיעות ב-HotXLS Delphi Excel Component, שהרפרנס שלו לנוסחאות מפרט את קטלוג הפונקציות המלא ואת מצבי הארגומנטים הנתמכים של כל פונקציה