נוסחאות מערך ב-HotXLS: מדוע Excel מוסיף @ ו-#VALUE!

Excel 365 מכניס @ לתוך נוסחה כמו =SUM(A1:B1*{10,100}) ומציג #VALUE! כשהקובץ שומר אותה בתור נוסחה רגילה, כי Excel אז מיישם implicit intersection בסגנון הישן על כל אופרנד של אופרטור. מאז v2.384.68, HotXLS Delphi Component שומר את נוסחאות האופרטור-מערך האלה בדיוק כמו Excel 365: בתור נוסחאות dynamic array של תא בודד ב-XLSX ובתור נוסחאות מערך של תא אחד ב-XLS

הסימפטום שורד code review. שירות ה-Delphi שלכם כותב חוברת עבודה, HotXLS מחשב אותה מחדש ושומר במטמון 210 עבור =SUM(A1:B1*{10,100}), והלקוח פותח אותה ב-Excel 16 ומגלה =SUM(@A1:B1*@{10,100}) בשורת הנוסחה ו-#VALUE! בתא. שום דבר בקובץ אינו פגום. מה שחסר הוא ה-metadata שאומר ל-Excel שהנוסחה נכתבה תחת חוקי ה-dynamic array, ובלעדיו Excel נופל בחזרה למודל החישוב שקדם ל-dynamic arrays

מדוע Excel 365 מוסיף @ לנוסחה ש-HotXLS חישב נכון?

Excel 365 מוסיף @ כי נוסחה בלי סימון dynamic array היא, על פי ההגדרה, נוסחה מהדור הישן, ונוסחאות מהדור הישן מצמצמות טווח של כמה תאים לתא אחד בכל מקום שבו אופרטור מצפה לערך בודד. הצמצום הזה הוא implicit intersection: Excel לוקח את התא של הטווח שחולק את השורה של הנוסחה (לטווח אנכי) או את העמודה (לטווח אופקי), ואם אין תא כזה התוצאה היא #VALUE!. Excel 365 שומר על המשמעות הזאת לנוסחאות בסגנון ישן ומציג @ כדי להפוך את הצמצום לגלוי

שימו =SUM(A1:B1*{10,100}) ב-E5 והקריאה בסגנון הישן נהיית מובנת מאליה. A1:B1 הוא טווח אופקי, הנוסחה יושבת בעמודה E, לטווח אין תא בעמודה E, ולכן @A1:B1 הוא #VALUE! וכל ה-SUM יורש את זה. תחת חוקי ה-dynamic array אותו טקסט מכפיל איבר-איבר, 1 × 10 ועוד 2 × 100, ומחזיר 210. מנוע הנוסחאות של HotXLS מחשב בדרך ה-dynamic array עוד מהמהדורות v2.384.61 ו-v2.384.63; פורמט הקובץ פשוט לא אמר את זה. עם A1:B2 שמחזיק 1, 2, 3 ו-4, אלה נוסחאות הבדיקה ומה ש-Excel 16 מציג:

נוסחה	תוצאת HotXLS	Excel 16, נשמרת בתור נוסחה רגילה	נשמר מאז v2.384.68
=SUM(A1:B1*{10,100})	210	#VALUE!	Dynamic array, Excel מציג 210
=SUM((A1:B2>2)*1)	2	Implicit intersection, שגוי או שגיאה	Dynamic array, Excel מציג 2
=SUMPRODUCT((A1:B2>2)*1)	2	Implicit intersection, שגוי או שגיאה	Dynamic array, Excel מציג 2
=MAX(A1:B2-1)	3	Implicit intersection, שגוי או שגיאה	Dynamic array, Excel מציג 3
=SUM(A1:B2)	10	10	נוסחה רגילה, ללא שינוי

השורה האחרונה חשובה לא פחות מארבע הראשונות. SUM(A1:B2) מעביר טווח ישירות לפרמטר של פונקציה שמקבל הפניות, ולכן אף אופרטור לא רואה טווח של כמה תאים ואף חיתוך לא יכול לקרות. Excel 365 עצמו שומר את הנוסחה הזאת בתור נוסחה רגילה, ו-HotXLS עושה את אותו הדבר

איך HotXLS שומר נוסחאות אופרטור-מערך ב-XLSX וב-XLS

HotXLS כותב נוסחת אופרטור-מערך ב-XLSX בתור dynamic array של תא בודד: האלמנט <c> נושא cm="1", הנוסחה היא <f t="array" ref="E5">, והחבילה מקבלת xl/metadata.xml עם טיפוס metadata של XLDAPR שההרחבה שלו מחזיקה dynamicArrayProperties fDynamic="1". התכונה cm היא אינדקס שמתחיל מ-1 אל בלוק ה-cellMetadata של החלק הזה, והרשומה של XLDAPR שמאחוריה היא מה שאומר ל-Excel "חשבו את זה תחת חוקי dynamic array". זה אותו מבנה ש-Excel 16 כותב כשמקלידים את אותה נוסחה ושומרים, וכך בכלל זוהתה הפריסה של היעד

ב-XLS אין חלק metadata, ולכן HotXLS משתמש במבנה היחיד של-BIFF8 לחישוב מערך: נוסחת מערך של תא אחד. התא מקבל רשומת FORMULA שה-token stream שלה הוא PtgExp בודד שמצביע על עצמו, ואחריו רשומת ARRAY ($0221) שנושאת את הנוסחה המפורסרת האמיתית מעל הטווח של התא הבודד. Excel 365 כותב נוסחאות dynamic array ל-XLS באותה דרך, וגרסת Excel ישנה יותר שקוראת את הקובץ רואה נוסחת מערך קלאסית של Ctrl+Shift+Enter

לא מעורבת שום API חדשה. הסימון קורה כשמשייכים את הנוסחה דרך API התא הרגילה, בשני המנועים. בצד ה-XLSX זה TXLSXCell.Formula:

var
  Book: TXLSXWorkbook;
  Sheet: TXLSXWorksheet;
begin
  Book := TXLSXWorkbook.Create;
  try
    Sheet := Book.Sheets.Add('Data');
    Sheet.Cells[1, 1].Value := 1;
    Sheet.Cells[1, 2].Value := 2;
    Sheet.Cells[2, 1].Value := 3;
    Sheet.Cells[2, 2].Value := 4;

    // אופרטור על טווח או מערך inline: נשמר בתור dynamic array
    Sheet.Cells[5, 5].Formula := '=SUM(A1:B1*{10,100})';
    Sheet.Cells[6, 5].Formula := '=SUM((A1:B2>2)*1)';
    // טווח שמועבר ישירות לפונקציה: נשאר <f> רגיל
    Sheet.Cells[7, 5].Formula := '=SUM(A1:B2)';

    if Book.Recalculate = lxOk then
      Writeln(VarToStr(Sheet.Cells[5, 5].Value));   // 210

    // שורש המערך שומר על הטקסט שלו בלי ה-'=' הפותח
    Writeln(Sheet.Cells[5, 5].Formula);              // SUM(A1:B1*{10,100})

    Book.SaveAs('probe.xlsx');   // E5 ו-E6 מקבלות cm="1" + t="array"
  finally
    Book.Free;
  end;
end;

אחרי ההמרה, TXLSXCell.Formula מחזיר את הטקסט בלי =, אותה צורה ש-TXLSXRange.SetDynamicArrayFormula שומר, ולכן קוד שמשווה מחרוזות נוסחה אחרי השיוך צריך לנרמל את ה-= הפותח

המנוע הקלאסי הולך באותו כלל דרך IXLSRange.Formula על תא בודד. שיוך הנוסחה מפנה אותה פנימית אל נתיב המערך של תא אחד, ולכן ה-XLS הנשמר מכיל את הזוג FORMULA ו-ARRAY:

var
  Wb: IXLSWorkbook;
  Sh: TXLSWorksheet;
begin
  Wb := TXLSWorkbook.Create;
  Sh := Wb.Sheets.Add;
  Sh.Range['A1', 'A1'].Value := 1;
  Sh.Range['B1', 'B1'].Value := 2;
  Sh.Range['A2', 'A2'].Value := 3;
  Sh.Range['B2', 'B2'].Value := 4;

  Sh.Range['E5', 'E5'].Formula := '=SUM(A1:B1*{10,100})';  // רשומת ARRAY
  Sh.Range['E6', 'E6'].Formula := '=MAX(A1:B2-1)';         // רשומת ARRAY
  Sh.Range['E7', 'E7'].Formula := '=SUM(A1:B2)';           // FORMULA רגילה

  Writeln(VarToStr(Sh.Range['E5', 'E5'].Value));   // 210
  Writeln(VarToStr(Sh.Range['E6', 'E6'].Value));   // 3
  Wb.SaveAs('probe.xls');
end;

אם עוגנים תוצאה של כמה תאים ולא צבירה סקלרית, ה-APIs המפורשות עדיין הכלי הנכון: SetArrayFormula למלבן בגודל מוגדר מראש, כמתואר ב-נוסחאות spill של dynamic array עם HotXLS, או TXLSXRange.SetDynamicArrayFormula כשרוצים את סימון ה-dynamic array של ה-XLSX על טווח שמגדירים בעצמכם. הנתיב האוטומטי במאמר הזה מכסה רק נוסחאות שמוקלדות לתא אחד

אילו נוסחאות HotXLS מסמן בתור dynamic arrays?

HotXLS מסמן נוסחה רק כשלאופרטור יש תת-עץ אופרנדים שמייצר מערך. הבדיקה רצה על עץ התחביר המקומפל, ואופרנד מייצר מערך אם הוא טווח של כמה תאים, קבוע מערך inline, או ביטוי אופרטור אחר שיש לו בעצמו אופרנד כזה. סוגריים שקופים. האופרטורים שנספרים הם האריתמטיים (+ - * / ^), שרשור (&), שש ההשוואות, פלוס ומינוס אונריים, ואחוז:

• A1:B1*{10,100}, (A1:B2>2)*1, --(B1:B2>0) ו-A1:B2-1 מסומנות, בכל מקום שבו הן מופיעות בנוסחה, כולל בתוך SUMPRODUCT
• SUM(A1:B2) ו-SUMPRODUCT(A1:A2,{1;10}) לא מסומנות, כי הטווח והמערך נכנסים ישירות לארגומנט של פונקציה ואף אופרטור לא נוגע בהם
• A1*2 או SUM(A1,B1)*2 לא מסומנות: הפניות לתא בודד ותוצאות של פונקציות הן סקלרים עבור הבדיקה הזאת

שלושה גבולות מכוונים. ראשון, הסימון קורה רק כשנוסחה נכנסת דרך ה-API, כלומר TXLSXCell.Formula במנוע ה-XLSX ושיוך של Formula או Value לתא בודד במנוע הקלאסי. נוסחאות שנטענות מקובץ נכתבות בחזרה בדיוק כפי שנמצאו, כי נוסחה מהדור הישן מיצרן אחר עשויה להסתמך על implicit intersection בכוונה. שני, טקסט שאינו מכיל לא : ולא { מדולג בלי קימפול שני. שלישי, נוסחה שתגלוש, כמו =A1:B1*2 לבדה, מסומנת בתור dynamic array של תא בודד שעוגן היכן שמניחים אותה. HotXLS לא מגלוש אותה, ו-Excel ירחיב את התוצאה אל התאים השכנים בחישוב המחדש הבא

כלל האופרנדים הזה הוא אח של כלל מחלקת הארגומנטים שמכוסה ב-implicit intersection ל-defined names ב-HotXLS. המאמר ההוא עוסק בפרמטרים של פונקציות שהוכרזו בתור מחלקת value; הזה עוסק באופרטורים, שבמודל הישן תמיד דורשים ערכים

מה השתנה במנוע החישוב כדי שהתוצאות יתאימו

תיקון האחסון ב-v2.384.68 נשען על כך שמנוע הנוסחאות של HotXLS כבר מחזיר ערכי Excel 365, וזה לקח כמה תיקונים מוקדמים יותר בשני המנועים. הבולט ביותר היה SUMPRODUCT: עד v2.384.61 הוא קיבל רק שני טווחים רגילים ומעלה, ולכן SUMPRODUCT((B1:B2>0)*1), SUMPRODUCT(--(B1:B2>0)) ואפילו SUMPRODUCT(B1:B2) עם הארגומנט הבודד החזירו #N/A. HotXLS מחשב עכשיו ארגומנטים של ביטויים איבר-איבר לפי הכללים של Excel:

• לכל ארגומנט חייבת להיות בדיוק אותה צורה, סקלר נחשב 1 × 1, אחרת התוצאה היא #VALUE!
• ערך שגיאה בתוך כל ארגומנט שהוא מוחזר בתור התוצאה
• איברי טקסט ולוגיים נחשבים 0, ולכן עדיין צריך (B1:B2>0)*1 או -- כדי להפוך TRUE ל-1
• ארגומנטים שכולם טווחים רגילים שומרים על לולאת ה-streaming המקורית, כך שטווחים גדולים לא מתממשים בתור מערכים

משפחת ה-SUM (SUM, COUNT, AVERAGE, MIN, MAX, COUNTA) משתמשת באותו מחשב איבר-איבר כשארגומנט הוא ביטוי אופרטור על טווח, ולכן =SUM((B1:B2>0)*1) סופר את שתי השורות במקום להביט בתא הראשון בלבד. v2.384.62 גרם לאופרטור החיתוך ברווח להחזיר את המלבן המשותף של שתי הפניות, עם #NULL! כשאין חפיפה, ולכן =SUM(A1:B2 B1:B2) הוא 6 ולא 2, והתוצאה יכולה להזין פרמטרי הפניה כמו ROWS ו-INDEX. v2.384.63 הוסיף לפרסר קבועי מערך inline כמו {1,2;3,4} (פסיקים מפרידים עמודות, נקודות-פסיקים מפרידים שורות) ואיחודי הפניות כמו (A1:B2,D4). השוואות איבר-איבר גם נותנות לאיבר ריק את הטיפוס של הצד השני, FALSE מול לוגי, בהתאמה לכלל הסקלרי מ-v2.384.53 שמתואר ב-שרשראות השוואה ותאים ריקים ב-HotXLS

var
  V: Variant;
begin
  // Book הוא ה-TXLSXWorkbook מהדוגמה הראשונה;
  // הגיליון הפעיל שלו מחזיק A1:B2 = 1, 2, 3, 4
  V := Book.Calculate('=SUMPRODUCT((A1:B2>2)*1)');   // 2
  V := Book.Calculate('=SUMPRODUCT(A1:B2)');          // 10, ארגומנט בודד
  V := Book.Calculate('=SUMPRODUCT(A1:A2,{1;10})');   // 31 = 1*1 + 3*10
  V := Book.Calculate('=SUM(A1:B2 B1:B2)');           // 6, הטווח המשותף B1:B2
  V := Book.Calculate('=SUM((A1:B2,B1:B2))');         // 16, החפיפה נספרת פעמיים
  V := Book.Calculate('=ROWS({1,2,3;4,5,6})');        // 2
  V := Book.Calculate('=TRUE*1');                     // 1, היה -1 לפני v2.384.61
end;

TXLSXWorkbook.Calculate מחשב מחרוזת נוסחה מול הגיליון הפעיל בלי לשמור אותה, דרך מהירה לבדוק את התנהגות המנוע. אזהרה אחת לגבי ה-@ עצמו: HotXLS קיבל מבחינה היסטורית @ בין שתי הפניות בתור חיתוך בינארי, ועכשיו הוא מחשב את הצורה הזאת עם סמנטיקת חיתוך אמיתית. ב-Excel 365, @ הוא קידומת אונרית של implicit intersection. אל תכתבו @ לתוך טקסט הנוסחה ותצפו למשמעות של Excel; השתמשו ברווח לחיתוך ותנו לכללי האחסון שלמעלה לטפל בסמנטיקה של ה-dynamic array

מדוע Excel סירב לפתוח את הקובץ או חישב ערך שגוי?

לגרום ל-Excel לקבל את סימון ה-dynamic array לקח שלושה תיקונים שאף בדיקת round-trip מול עצמנו לא הייתה תופסת, כי HotXLS קרא את הפלט של עצמו נכון בכל מקרה. כל אחד התגלה בפתיחת פלט HotXLS ב-Excel 16 והחלפת משתנה אחד בכל פעם:

1. ה-GUID של ההרחבה חייב להיות כולו באותיות קטנות. ה-ext uri ב-xl/metadata.xml חייב להיות בדיוק {bdbb8cdc-fa1e-496e-a857-3c3f30c029c3}. תבנית HotXLS ישנה יותר אייתה אותו באותיות מעורבות, ו-Excel 16 סירב לפתוח את כל החבילה, לא רק את התא. חוברות עבודה שנוצרו עם TXLSXRange.SetDynamicArrayFormula לפני v2.384.68 סבלו מאותה בעיה
2. טקסט שורש המערך לא נושא = פותח. הכותב של ה-XLSX פולט את הטקסט השמור של שורש מערך מילה במילה אל <f>. אם התא המומר היה שומר על ה-= שלו, האלמנט היה נראה <f t="array" ref="E5">=SUM(...)</f>, ו-Excel דוחה גם את זה בזמן פתיחה. HotXLS מסיר אותו במהלך ההמרה, ולכן TXLSXCell.Formula קורא בחזרה בלי זה
3. Double(True) הוא -1 ב-Delphi. המרת Variant הולכת אחרי מוסכמת ה-COM שבה TRUE הוא כל הביטים דלוקים, וגם VarIsNumeric(True) מחזיר True. לפני v2.384.61 זה גרם ל-=TRUE*1 להחזיר -1 ואיפשר שאיברים לוגיים של מערך יסווגו בתור מספרים, ולכן השוואה כמו (B1:B2>0)=TRUE השתבשה. HotXLS בודק עכשיו varBoolean לפני שהוא מתייחס ל-Variant בתור מספר באריתמטיקה סקלרית, באריתמטיקת מערכים ובסיווג איברי מערך, ו-TRUE נחשב 1

מחלקות אופרנדים של BIFF8: הפרטים ברמת הבייטים למי שמממש פורמט

ב-BIFF8, כל token של אופרנד נושא את מחלקת האופרנד שלו בבייט ה-token עצמו, ו-Excel סומך על אותה מחלקה יותר מאשר על המבנה של הנוסחה. [MS-XLS] מגדיר את המחלקה בתור שדה PtgDataType בן שני ביטים בביטים 5 ו-6 של ה-token: 1 ל-reference, 2 ל-value, 3 ל-array. חמשת הביטים הנמוכים קוראים ל-token בשמו, ולכן לאותה הפניית טווח יש שלושה איותים:

Token	מחלקת reference	מחלקת value	מחלקת array
PtgRef	$24	$44	$64
PtgArea	$25	$45	$65
PtgArray	$20	$40	$60

HotXLS טעה בשלושה מאלה במקומות שונים, וכל אחד הפיק סימפטום מובחן ב-Excel בזמן שב-HotXLS הקריאה חזרה הייתה תקינה:

• קבועי מערך במחלקת reference. המקודד בחר את המחלקה מההקשר, והפרמטרים של SUM או ROWS הם מחלקת reference, ולכן =SUM({1,2}) נכתב עם PtgArray בתור $20. Excel מציג את כל הנוסחה בתור =#N/A. קבוע מערך לעולם לא יכול להיות reference, ולכן מאז v2.384.63 HotXLS כותב מחלקת array $60 בכל מקום שבו ההקשר מבקש reference
• אופרנדים במחלקת value של PtgIsect ו-PtgUnion. האופרטורים הבינאריים לקחו אופרנדים במחלקת value, מה שנכון ל-* אבל שגוי לאופרטורי ההפניות. עם טווחי $45 לפני PtgIsect ($0F), Excel קרא =SUM(A1:B2 B1:B2) בתור =SUM(@A1:B2 @B1:B2) והחזיר #VALUE!. מאז v2.384.62 האופרנדים של PtgIsect ו-PtgUnion ($10) נכתבים במחלקת reference, $25
• אופרנדים במחלקת value בתוך רשומת ה-ARRAY. Excel מיישם implicit intersection גם בתוך נוסחת מערך כשאופרנד הוא מחלקת value. HotXLS כתב $45 שם, ולכן נוסחת המערך של התא הבודד עבור =SUM(A1:B1*{10,100}) החזירה 10 ב-Excel. מאז v2.384.68, ה-token stream של רשומת ARRAY מקדם כל הפניה במחלקת value וכל קבוע מערך למחלקת array, $65 ו-$60, שזה בדיוק מה ש-Excel כותב

קורא שמתעלם מביטי המחלקה עושה round-trip לכל שלושת המקרים בשמחה, ולכן אם אתם מתחזקים כותב BIFF8 משלכם, השוו את ביטי המחלקה של כל token אופרנד מול קובץ ש-Excel שמר עבור אותה נוסחה, ולא רק את המספרים של ה-tokens

עזר זריז

• Excel 365 מציג @ כשאופרטור בנוסחה רגילה ולא מסומנת מקבל טווח של כמה תאים או מערך inline
• HotXLS מ-v2.384.68 ואילך שומר נוסחאות כאלה בתור dynamic arrays של תא בודד ב-XLSX (cm="1", t="array", metadata של XLDAPR) ובתור נוסחאות מערך של תא אחד ב-XLS (FORMULA עם PtgExp בתוספת ARRAY $0221)
• רק אופרנדים של אופרטורים נספרים; טווח שמועבר ישירות לארגומנט של פונקציה נשאר נוסחה רגילה
• רק נוסחאות שנכנסו דרך TXLSXCell.Formula או Formula / Value הקלאסי של תא בודד מסומנות; נוסחאות טעונות אינן נגועות
• תא השורש המומר נקרא בחזרה בלי ה-= הפותח
• ה-GUID של ה-ext uri של ה-dynamic array חייב להיות באותיות קטנות או ש-Excel דוחה את החבילה
• ב-Delphi, Double(True) הוא -1; בודקים varBoolean לפני המרה מספרית
• BIFF8: קבועי מערך לעולם לא במחלקת reference, אופרנדים של PtgIsect / PtgUnion במחלקת reference, אופרנדים של רשומת ARRAY במחלקת array

HotXLS קורא, כותב ומחשב חוברות עבודה של XLS ו-XLSX באופן טבעי מ-Delphi ו-C++Builder, ושומר נוסחאות אופרטור-מערך כך ש-Excel 365 פותח אותן עם אותם ערכים ש-HotXLS חישב. ראו את רכיב הגיליונות HotXLS ל-Delphi למהדורות, לתיעוד ולהורדת ניסיון