מטריצת האפשרויות של AGGREGATE ודליפת שער ב-HotXLS לדלפי

‏HotXLS, רכיב גיליון האקסל הילידי ל-Delphi ו-C++Builder, שלח בספטמבר 2026 שני תיקונים קשורים ל-AGGREGATE. גרסה 2.382.0 תיקנה את ארגומנט האפשרויות כך שקודים 1/3/5/7 מתעלמים משורות מוסתרות, 2/3/6/7 מתעלמים משגיאות, ו-0 עד 3 מתעלמים מתאי SUBTOTAL ו-AGGREGATE מקוננים, בדיוק כפי שמיקרוסופט מתעדת. גרסה 2.382.3 עצרה אז את דגלי הבחירה האלה מלדלוף להערכה של התאים שהפונקציה מפנה אליהם. הפגם הראשון מביך במובן שכל באג של תעתוק טבלה מביך: מיקומי הסיביות הוחלפו, כך שכל נוסחה שהשתמשה בקוד אפשרויות שאינו אפס קיבלה מדיניות שהמחבר שלה לא ביקש. השני מעניין יותר, כי זו צורה שתפגשו בכל מנוע הערכה שמשתמש בשדה חולף כדי להעביר הקשר לתוך מעבר רקורסיבי. אגרגציה חיצונית מזרעת דגל, עוברת על טווח, ומשיכה תא שהנוסחה שלו עוד לא חושבה. הנוסחה הזו רצה על אותו מחשבון, רואה את אותו דגל מזורע, ומאגדת בשקט את השורות הלא נכונות, ומייצרת מספר שהסטייה שלו היא במשהו שאף אחד לא יכול להסביר מהטקסט של הנוסחה לבד

מה באמת בוחרות האפשרויות 0 עד 7 של AGGREGATE?

ארגומנט האפשרויות של AGGREGATE הוא מטריצה בת שלוש סיביות, ושלוש הסיביות בלתי תלויות. סיבית 0 (ערך 1) משמעה להתעלם משורות מוסתרות, סיבית 1 (ערך 2) משמעה להתעלם מערכי שגיאה, וסיבית 2 (ערך 4) משמעה להפסיק להתעלם מתאי SUBTOTAL ו-AGGREGATE מקוננים, כי דילוג עליהם הוא ברירת המחדל לקודים הנמוכים. שני דברים בזה קל להפוך. סיבית השורות המוסתרות היא הסיבית הנמוכה, ולא האמצעית, כך ש-AGGREGATE(9,1,...) היא צורת הסכום המסונן ו-AGGREGATE(9,2,...) היא הסובלנית לשגיאות. ומדיניות האגרגציה המקוננת הפוכה ביחס לשתי האחרות: רק קודים 4 עד 7 מתייחסים לתא שהנוסחה שלו היא בעצמה SUBTOTAL או AGGREGATE כאל ערך רגיל. ECMA-376 חלק 1 §18.17.7 מגדיר את SUBTOTAL עם אותה חלוקה של הכללה או החרגה של שורות מוסתרות בין קודים 1-11 ו-101-111, ו-AGGREGATE, שנשמר בקבצי OOXML תחת הקידומת _xlfn., מכליל את החלוקה הזו לתוך ארגומנט האפשרויות, כך שהטבלה שמיקרוסופט מפרסמת עבור הפונקציה AGGREGATE היא חוזה שמנוע חייב למלא ולא נוחות

אפשרות	שורות מוסתרות	ערכי שגיאה	SUBTOTAL / AGGREGATE מקונן
0	נכללות	מועברת	נדלגים
1	נדלגות	מועברת	נדלגים
2	נכללות	נדלגת	נדלגים
3	נדלגות	נדלגת	נדלגים
4	נכללות	מועברת	נכללים
5	נדלגות	מועברת	נכללים
6	נכללות	נדלגת	נכללים
7	נדלגות	נדלגת	נכללים

למה ל-HotXLS היו אפשרויות AGGREGATE הפוכות?

כי TXLSCalculator.CalcAggregateFunc המקורית נכתבה מתוך פרפרזה של הטבלה ולא מהטבלה. היא חישבה ignoreErrors := (optCode >= 4) and (optCode <= 7) וזרעה את שער השורות המוסתרות עבור קודים 2, 3, 6 ו-7, בעוד שמדיניות האגרגציה המקוננת לא יושמה בכלל. המאמר הקודם על שורות מוסתרות ב-SUBTOTAL וב-AGGREGATE רשם את הפער הזה כמגבלה פתוחה ותיאר את המיפוי הישן כפי שהוא נשלח אז; התיאור היה מדויק לגבי הקוד ושגוי לגבי Excel, ואף אחד לא שם לב לכך זמן רב כי שתי המדיניות שרוב האנשים משלבים, מוסתרות ועוד שגיאות, נופלות על קודים 3 ו-7 בשתי הטבלאות. רק קוד עם סיבית אחת חשף את ההחלפה: AGGREGATE(9,1,A1:A4) החזיר את הסכום הלא מסונן, ו-AGGREGATE(9,2,...) דילג על שורות מוסתרות בעוד הוא עדיין מעביר #DIV/0!. הפגם צף בסקירה סטטית של lxCalc.pas, נרשם כ-HXLS-008 במרשם הבעיות הידועות של הפרויקט, ולא מקובץ של לקוח, וזה אומר משהו על כמה נדירים קודי הסיבית הבודדת בחוברות עבודה בפרודקשן. גרסה 2.382.0 כתבה מחדש את הפענוח כשלוש בדיקות שייכות לקבוצה והוסיפה שער שני למדיניות המקוננת, שמחווט דרך callback חדש TXLSIsSubtotalCell שחוברת העבודה מספקת לצד TXLSIsRowHidden

פענוח האפשרויות של AGGREGATE ב-HotXLS לפני ואחרי v2.382.0: ה-CalcAggregateFunc המקורית זרעה את שער השורות המוסתרות עבור קודים 2, 3, 6, 7 והתעלמה משגיאות מ-4 ומעלה בלי מדיניות מקוננת, בעוד הפענוח המתוקן בודק שורות מוסתרות ב-1, 3, 5, 7, שגיאות ב-2, 3, 6, 7 ודילוגים מקוננים ב-0 עד 3 — רק קודים עם סיבית אחת חשפו את ההחלפה כי השילוב הפופולרי של מוסתרות ועוד שגיאות נופל על קודים 3 ו-7 בשתי הטבלאות, וקודים מחוץ ל-0 עד 7 מחזירים כעת lxErrorValue בדיוק כפי ש-Excel דוחה אותם

// TXLSCalculator.CalcAggregateFunc, הצורה מ-v2.382.3
if (optCode < 0) or (optCode > 7) then
begin
  Result := lxErrorValue;            // Excel דוחה קודים מחוץ ל-0..7
  Exit;
end;
ignoreErrors := optCode in [2, 3, 6, 7];
prevIgnoreHidden := FIgnoreHiddenRows;
prevIgnoreSubtotal := FIgnoreSubtotalCells;
FIgnoreHiddenRows := (optCode in [1, 3, 5, 7]) and Assigned(FIsRowHidden);
FIgnoreSubtotalCells := (optCode in [0, 1, 2, 3]) and Assigned(FIsSubtotalCell);
try
  // ... למפות את function_num ל-iftab הפנימי, לעבור על ref1..refN ...
finally
  FIgnoreHiddenRows := prevIgnoreHidden;
  FIgnoreSubtotalCells := prevIgnoreSubtotal;
end;

שימו לב ששני הדגלים מושמים ללא תנאי ולא רק נדלקים כשהאפשרות מבקשת אותם. הגרסה של v2.382.0 עדיין השתמשה ב-if ... then FIgnoreHiddenRows := True, מה שאומר ש-AGGREGATE עם קוד 4 שמקוננת בתוך SUBTOTAL(109, ...) ירשה את שער השורות המוסתרות החיצוני במקום לנקות אותו. השמת הערך המפוענח בכניסה ושחזור הערך הקודם בבלוק ה-finally גורמים לכל קריאה ל-AGGREGATE להחזיק במדיניות שלה למשך המעבר שלה ולא יותר. גרסה 2.382.0 גם הפכה את צורת המערך להוגנת: כשהארגומנט מוערך למערך Variant חד- או דו-ממדי, CalcAggregateFunc עוברת כעת על כל איבר ומחילה את מדיניות השגיאות לכל איבר, בעוד הקוד הישן בדק רק NaN מסוג double ואחרת מסר את כל המערך ל-ExcelSum

למה AGGREGATE חיצונית דולפת לנוסחאות שהיא מפנה אליהן?

כי FIgnoreHiddenRows ו-FIgnoreSubtotalCells הם שדות על המחשבון, והמחשבון משותף לכל נוסחה שמוערכת בזמן חישוב מחדש אחד. השערים תוכננו כשדות טיוטה בדיוק כדי ששישה לולאות מעבר על תאים יוכלו להיעץ להם בלי להשחיל פרמטר בכל חתימה, והתכנון הזה תקין כל עוד כל מה שרץ בזמן ששער מזורע שייך לאגרגציה שזרעה אותו. ההנחה נשברת בנקודה אחת ספציפית: FGetValue. כשמעבר מבקש מחוברת העבודה ערך של תא וזה התא מחזיק בנוסחה בלי תוצאה שמורה, חוברת העבודה מהדרת את הנוסחה ומעריכה אותה על המקום, על אותו TXLSCalculator, כשהשערים החיצוניים עדיין דלוקים. קובץ הרגרסיה ב-HotXLS.WorkbookApiTests.pas מציג את הכשל עם ארבעה תאים. A1 מחזיק 10, A2 מחזיק 20 על שורה מוסתרת, A3 מחזיק =1/0, ו-A4 מחזיק =SUBTOTAL(9,A1:A2), שערכו הנכון הוא 30. עכשיו העריכו =AGGREGATE(9,7,A1:A4): להתעלם משורות מוסתרות, להתעלם משגיאות, לספור את ה-SUBTOTAL המקונן כערך. Excel מחזיר 10 + 30 = 40. כש-A4 לא שמור ב-cache, המנוע שלפני 2.382.3 זרע את שער השורות המוסתרות, עבר ל-A4, הפעיל את הערכתו, ו-CalcSubtotalFunc עבור קוד 9 ירשה את השער המזורע, כי היא רק מדליקה את הדגל עבור קודים 101 עד 111 ואף פעם לא מנקה אותו. A4 הוערך ל-10 במקום ל-30, והסכום החיצוני חזר כ-20. שום דבר באף אחת מהנוסחאות לא מזכיר שורות מוסתרות בנתיב שהפיק את המספר השגוי

איך AGGREGATE חיצונית ב-HotXLS דלפה לנוסחאות שהיא מפנה אליהן: כש-FIgnoreHiddenRows מזורע עבור קוד 7, המעבר מגיע ל-A4 שלא שמור ב-cache ומחזיק SUBTOTAL 9 על A1:A2, FGetValue מעריכה אותו על אותו מחשבון, CalcSubtotalFunc יורשת את השער ומחזירה 10 במקום 30, כך שהסכום מדווח 20 במקום 40 ש-Excel מחזיר — גם השער המקונן דלף בכיוון ההפוך, ו-CalcSubtotalFunc איפסה את FIgnoreSubtotalCells ביציאה במקום לשחזר אותו, ובכך פרקה את המדיניות החיצונית לכל תא שאחרי SUBTOTAL לא שמור ב-cache שהמעבר הגיע אליו באמצע

שער האגרגציה המקוננת דלף באותו אופן בכיוון ההפוך. עם קודים 0 עד 3, FIgnoreSubtotalCells מזורע, ומעבר הטווח הגנרי ב-GetValueItemRange מכבד אותו, כך שנוסחה קודמת שהיא =SUM(B1:B3) תפיל בשקט את B2 אם במקרה B2 מכיל SUBTOTAL. גרוע מכך, CalcSubtotalFunc מאפסת את FIgnoreSubtotalCells ל-False ביציאה במקום לשחזר את הערך הקודם, כך ש-SUBTOTAL קודם שלא שמור ב-cache שהמעבר הגיע אליו באמצע פרק את השער החיצוני לכל תא שאחריו. מרשם הבעיות הידועות של הפרויקט רושם את זה תחת HXLS-008 כדליפת מצב בחירה מקוננת, וזה השם הנכון לסוג הבאג הזה: דגל חולף גלובלי שנכון למסגרת שהציבה אותו ושגוי לכל מסגרת שיורשת אותו

איך AggregateGetCellValue ו-AggregateGetItemValue מבודדות את המעבר

התיקון ב-v2.382.3 מציב גבול סביב כל נקודה שבה AGGREGATE קוראת ערך שהיא לא חישבה בעצמה. TXLSCalculator.AggregateGetCellValue עוטפת את הקריאה הגולמית ל-FGetValue: היא שומרת את שני הדגלים, מנקה אותם, מבצעת את השליפה, ומשחזרת אותם בבלוק finally. האגרגציה החיצונית עדיין מחילה את המדיניות שלה על התא שהיא זה עתה שלפה, כי בדיקות השורה המוסתרת והתא המקונן קורות במעבר סביב השליפה, אבל הנוסחה הקודמת עצמה רצה בלי שום מדיניות, וזה מה ש-Excel עושה

הבידוד של HotXLS ב-v2.382.3: AggregateGetCellValue שומרת את שני דגלי השער, מנקה אותם, שולפת דרך FGetValue ומשחזרת אותם בבלוק finally, כך שנוסחה קודמת מוערכת בלי מדיניות בעוד המעבר החיצוני עדיין מחיל בדיקות שורה מוסתרת ותא מקונן סביב השליפה — AggregateGetItemValue עושה אותו דבר עבור ארגומנטים של מערך מחושב וממפה שגיאות שליפה ל-VarAsError, בעוד קוד של מגבלת משאבים במכוון אף פעם לא מטופל כשגיאה ניתנת לדילוג תחת אפשרויות ההתעלמות משגיאות

function TXLSCalculator.AggregateGetCellValue(SheetIndex, Row, Col: Integer;
  var Value: Variant; var OutOfRange: Boolean): Integer;
var
  Hidden, Nested: Boolean;
begin
  Hidden := FIgnoreHiddenRows;
  Nested := FIgnoreSubtotalCells;
  FIgnoreHiddenRows := False;        // נוסחה קודמת מחזיקה במדיניות של עצמה
  FIgnoreSubtotalCells := False;
  try
    Result := FGetValue(SheetIndex, Row, Col, Value, OutOfRange);
  finally
    FIgnoreHiddenRows := Hidden;
    FIgnoreSubtotalCells := Nested;
  end;
end;

AggregateGetItemValue עושה אותו דבר עבור ארגומנטים שאינם טווח, והיא חייבת לעשות יותר מאשר לנקות דגלים, כי ארגומנט כמו A1:A4/(B1:B4-20) הוא מערך מחושב שצורת האיברים שלו חייבת לשרוד. העטיפה מממשת טווח פשוט למערך Variant דו-ממדי דרך AggregateGetCellValue, וממפה תא שהחזיר קוד שגיאה ל-VarAsError כדי שאפשר יהיה עדיין להחיל את מדיניות השגיאות לכל איבר, והיא רקורסיבית דרך צמתי האופרטורים הבינאריים והאונריים (SA_ADD, SA_DIV, SA_UNARMINUS והשאר) עם ApplyArrayBinaryOp ו-ApplyArrayUnaryOp; כל דבר אחר נופל ל-GetValueItem הרגיל. שני שומרים יושבים לפני המימוש: טווח גדול מ-EffectiveFormulaArrayMemoryLimit מחזיר lxErrorResourceLimit, וטווח על כמה גיליונות או טווח הפוך מחזיר #VALUE!. קוד של מגבלת משאבים במכוון לא מטופל כשגיאת תא ניתנת לדילוג גם תחת אפשרויות 2/3/6/7, כי מנוע שבולע את אות ה-out-of-memory של עצמו בגלל שהמשתמש ביקש לדלג על #N/A היה משקר. כל שלושת המעברים של AGGREGATE, AggregateCollectRange למשפחת SUM, AggregateReduceVariance ל-STDEV, VAR ו-PRODUCT, ו-AggregateReduceWithK ל-MEDIAN ולצורות ה-quantile, הועברו מ-FGetValue ומ-GetValueItem לשתי העטיפות, וכל אחד מהם קיבל את בדיקת התא המקונן דרך FIsSubtotalCell

איזו שגיאה מחזירה AGGREGATE כשהיא לא מתעלמת משגיאות?

את המקורית, מאז v2.382.3. גרסה 2.382.0 זיהתה תאי שגיאה נכון אבל מוטטה כל אחד מהם ל-lxErrorValue, כך ש-AGGREGATE(9,4,A1:A3) מעל תא #DIV/0! החזיר #VALUE!, בעוד Excel מעבירה את השגיאה הראשונה שהיא פוגשת ללא שינוי. ה-helper המחליף AggregateErrorCode ממפה Variant לקוד lxError* המתאים, בין אם ה-Variant הוא varError אמיתי ובין אם הוא אחת משבע מחרוזות השגיאה, ו-AggregateValueIsError היא כעת רק בדיקה לתוצאה שאינה אפס. כל מעבר רושם את קוד השגיאה הראשון שהוא רואה ומחזיר את הקוד הזה, מה שאומר גם שתא שהנוסחה שלו מעולם לא חושבה, ושהשגיאה שלו מגיעה לכן כקוד החזרה מ-FGetValue ולא כ-Variant שמור, מועבר באותו אופן כמו אחד שמור. שתי פונקציות ספירה מקבלות טיפול מיוחד בתוך AggregateCollectRange, והטיפול תואם SUBTOTAL ולא SUM. עבור פונקציה פנימית 0, COUNT, תא שגיאה אף פעם לא נספר ואף פעם לא מועבר, בלי קשר לקוד האפשרויות, כי COUNT סופרת רק מספרים. עבור פונקציה פנימית 169, COUNTA, תא שגיאה הוא ערך שאינו ריק ונספר כ-1 אלא אם קוד האפשרויות מתעלם משגיאות, ואז הוא מדולג. הא-סימטריה הזו היא איך ש-Excel מתייחסת ל-COUNT ול-COUNTA גם מחוץ ל-AGGREGATE, וזה מהסוג של פרט שכלל גנרי של "אם שגיאה אז להעביר" מפספס בשקט

מה מאמת מערך הרגרסיה של שמונה האפשרויות

קובץ הבדיקה שתואר למעלה מופעל כמטריצה מלאה ב-AggregateFunc_OptionMatrixCoversHiddenErrorsAndNestedAggregates: לכל קוד אפשרויות מ-0 עד 7 הוא מעריך גם את צורת ה-SUM וגם את צורת ה-MEDIAN על A1:A4 ובודק את התוצאה מול ציפייה שנגזרה ביד. קודים 0, 1, 4 ו-5 חייבים להעביר את ה-#DIV/0! מ-A3, כי אף אחד מהם לא מתעלם משגיאות. קוד 2 נותן SUM 30 ו-MEDIAN 15, מ-10 ומ-20 כשהמקונן A4 מדולג. קוד 3 נותן 10 ו-10. קוד 6 נותן 60 ו-20, כי ה-30 ב-A4 נספר כעת. קוד 7 נותן 40 ו-20, וזה המקרה שהחזיר 20 לפני תיקון הדליפה. הרצת הקבלה הרחבה יותר שנרשמה במרשם הבעיות הידועות מכסה את כל תשעת עשר מספרי הפונקציה מול כל שמונת הקודים, כשכל נוסחה קודמת גם שמורה ב-cache וגם לא, עבור 304 תרחישים ב-Win32 וב-Win64

var
  Book: TXLSXWorkbook;
  Sheet: TXLSXWorksheet;
begin
  Book := TXLSXWorkbook.Create;
  try
    Sheet := Book.Sheets.Add('Data');
    Sheet.Cells[1, 1].Value := 10;
    Sheet.Cells[2, 1].Value := 20;
    Sheet.Cells[3, 1].Formula := '=1/0';
    Sheet.Cells[4, 1].Formula := '=SUBTOTAL(9,A1:A2)';   // סכום ביניים לקבוצה = 30
    Sheet.RowHidden[2] := True;

    Sheet.Cells[6, 1].Formula := '=AGGREGATE(9,1,A1:A4)'; // #DIV/0!  מוסתרות מדולגות, שגיאה מועברת
    Sheet.Cells[7, 1].Formula := '=AGGREGATE(9,3,A1:A4)'; // 10       מוסתרות + שגיאה + מקונן מדולגים
    Sheet.Cells[8, 1].Formula := '=AGGREGATE(9,6,A1:A4)'; // 60       רק שגיאות מדולגות
    Sheet.Cells[9, 1].Formula := '=AGGREGATE(9,7,A1:A4)'; // 40       היה 20 לפני v2.382.3
    Book.Recalculate;
    Book.SaveAs('aggregate-options.xlsx');
  finally
    Book.Free;
  end;
end;

איפה הגבול עוד נמצא

שלוש מגבלות שווה להכיר לפני שאתם בונים על זה. ראשית, הפרדיקט של האגרגציה המקוננת הוא טקסטואלי. TXLSXWorkbook.GetCalcIsSubtotalCell והתאום שלה במנוע הקלאסי מחזירים True כשהנוסחה של תא מתחילה ב-SUBTOTAL(, AGGREGATE( או _xlfn.AGGREGATE(, עם סימן שוויון מוביל או בלעדיו, כך שנוסחה כמו =IF(C1,SUBTOTAL(9,B1:B9),0) או =SUBTOTAL(9,B1:B9)*2 לא מזוהה כמקוננת ותיספר פעמיים על ידי קודים 0 עד 3 במקום ש-Excel תדלג עליה; מחולל שפולט סכומי ביניים מחושבים צריך להשאיר את קריאת האגרגציה בראש הנוסחה. שנית, הבידוד יושב בשלושת המעברים של AGGREGATE. CalcSubtotalFunc עדיין עוברת דרך GetValueItemRange, CollectRangeValues ו-SubtotalReduceVariance, שקוראים ל-FGetValue ישירות, כך ש-SUBTOTAL(109, ...) שהטווח שלו מכיל נוסחה קודמת שלא שמורה ב-cache עדיין יכול להעביר את שער השורות המוסתרות שלו לאותה נוסחה קודמת. Recalculate מלא מעריך נוסחאות קודמות לפני תלויות, כך שנתיב ה-cache ננקט והשער אף פעם לא עובר בירושה; החשיפה מוגבלת להערכה אד-הוק דרך Calculate ולחוברות עבודה שנטענו בלי ערכים שמורים, ואם אתם נשענים על חישוב מחדש מצטבר על גרף התלויות כדי לשמור על מודלים גדולים מגיבים, אותה הבטחת סדר היא מה שמונע מהדליפה הזו להתעורר. שלישית, שני השערים מותנים ב-Assigned(FIsRowHidden) וב-Assigned(FIsSubtotalCell). שתי חזיתות חוברת העבודה מחווטות את ה-callbacks בבנאים שלהן, אבל קוד שבונה TXLSCalculator ביד עם שני הארגומנטים המקוריים בלבד מקבל את התנהגות ההכללה המסורתית עבור כל קוד אפשרויות, בשקט. כשסכום נראה שגוי וטקסט הנוסחה נראה נכון, מעקב אחרי ההערכה צעד אחרי צעד הוא הדרך המהירה ביותר לראות אם נוסחה קודמת הוערכה תחת שער שעבר בירושה או ש-callback פשוט מעולם לא חובר

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