SUBTOTAL ו-AGGREGATE ושורות מוסתרות ב-Delphi עם HotXLS

אם SUBTOTAL(109, ...) ו-SUBTOTAL(9, ...) מחזירים את אותו המספר על חוברת עבודה המכילה שורות מוסתרות, אחד מהשניים שגוי. HotXLS, רכיב גיליון האלקטרוני של Excel הילידי עבור Delphi ו-C++Builder, התנהג בדיוק כך עד גרסה 2.197.0, כי מנוע החישוב שלו לא היה לו דרך לשאול גיליון עבודה אם שורה נתונה מוסתרת

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

מדוע SUBTOTAL 109 כולל שורות מוסתרות?

כי ברוב עיצובי המנוע השכבה שמעריכה נוסחה אף פעם לא לומדת על נראות שורה. HotXLS היה מקרה מספר לימוד: מנוע החישוב ב-lxCalc.pas הגיע לערכי תא דרך callback יחיד TXLSGetValue שעונה עם ערך עבור שלישיית (גיליון, שורה, עמודה) ולא כלום מעבר. נראות היא תכונת הצגה מאוחסנת ברשומת השורה, ואף חלק מהרשומה הזו לא נסע במורד שרשרת הקריאה. למנוע לכן היה נתיב אגרגציה אחד, ושתי מחציות טבלת מספרי הפונקציה של SUBTOTAL נפתרו אליו. זה לא מחלקת פגם של שגיאת עיגול: זו כל הסיבה שהמחצית השנייה של הטבלה קיימת. ECMA-376 חלק 1, שפורסם כ-ISO/IEC 29500-1, מגדיר את SUBTOTAL בהגדרות פונקציית הנוסחה שלו (§18.17.7) עם ארגומנט ראשון שבוחר גם את האגרגציה הפנימית וגם את מדיניות השורה-המוסתרת. קודים 1 עד 11 ממופים ל-AVERAGE, COUNT, COUNTA, MAX, MIN, PRODUCT, STDEV, STDEVP, SUM, VAR, ו-VARP תוך כלל ערכים בשורות מוסתרות ידנית. קודים 101 עד 111 בוחרים את אותן אחת עשרה אגרגציות ומחריגים אותן. משתמש שמקליד 109 במקום 9 עושה הצהרה מכוונת על נתונים מוסתרים, ומנוע שקורס את ההבחנה מבטל בשקט את ההצהרה הזו

למה מספרי הפונקציה ממופים בתוך המנוע

HotXLS פותר את הארגומנט הראשון של SUBTOTAL ב-CalcSubtotalFunc, שמנרמל קודים 101 עד 111 למטה על אותם מזהי פונקציה פנימיים כמו קודים 1 עד 11 ואז שולח בהתאם לאגרגציה עצמה. רוב המשפחה זורמת דרך צובר ExcelSum ההדרגתי, זה שמטפל ב-SUM, COUNT, COUNTA, MIN, MAX, ו-AVERAGE. חמישה מהם לא יכולים: STDEV, VAR, STDEVP, VARP, ו-PRODUCT זקוקים למעבר בצורה סגורה על הנתונים, כך ש-CalcSubtotalFunc מנתב קודים פנימיים 12, 46, 193, 194, ו-183 לצמצם (reducer) נפרד, SubtotalReduceVariance. הפיצול הזה הוא הדבר הראשון ששווה למפות לפני שנוגעים בכל דבר, כי שני נתיבי אגרגציה עצמאיים משמעם שתי לולאות מעבר-תא עצמאיות, ותיקון שמוחל רק על אחת מהן מפיק את התוצאה הגרועה ביותר האפשרית: SUBTOTAL(109, ...) מכבד את המסנן בעוד SUBTOTAL(107, ...) על אותו טווח לא. ספירת הלולאות ב-HotXLS העלתה שש מהן ברגע ש-AGGREGATE נכלל, פרוסות על פני הערכת טווח, איסוף טווח פשוט, ושלושה צמצמים נפרדים

מדוע שדה scratch במקום שש חתימות חדשות?

כי חוט פרמטר חדש דרך שש פונקציות מעבר-תא, בתוספת כל מה שקורא להן, הוא שינוי רחב לנתיב קוד חם למען בוליאני אחד. ל-HotXLS כבר היה תקדים לחלופה: שדה זמני על המחשבון, באותה רוח כשדה ה-scratch ש-GetRangeInfo משתמש בו כדי לרשום מתי הפניה תלת-ממדית נפתרה לתוך חוברת עבודה חיצונית. גרסה 2.197.0 הוסיפה שני. המנוע קיבל סוג callback, TXLSIsRowHidden, מוצהר כפונקציה של (SheetIndex, row) המחזירה Boolean, מאוחסן ב-FIsRowHidden, בתוספת דגל זמני FIgnoreHiddenRows. הדגל מזוין בכניסה ל-CalcSubtotalFunc כשקוד הפונקציה נופל ב-101 עד 111, ובכניסה ל-CalcAggregateFunc עבור קודי אפשרות AGGREGATE שבוחרים החרגת שורה מוסתרת. כל לולאת מעבר-תא אז בודקת אותו ומדלגת שורה אחת כשהוא מוגדר, מוסיפה שורה אחת לכל אחת

// The shape repeated in all six cell-walk loops
for sh := s1 to s2 do
  for rr := r1 to r2 do
  begin
    if FIgnoreHiddenRows and Assigned(FIsRowHidden) and FIsRowHidden(sh, rr) then
      Continue;
    for cc := c1 to c2 do
    begin
      // ... fold Cells[rr, cc] into the accumulator ...
    end;
  end;

שני פרטים בקוד הזיון (arming) נושאים את נכונות התכנית כולה. הדגל נשמר ומשוחזר במקום פשוט מוגדר ומנוקה, כי ארגומנט SUBTOTAL יכול להכיל ביטוי שמריץ הערכה משלו בעוד האגרגציה החיצונית עדיין על המחסנית, והעבודה המקוננת הזו אסור לה לרשת או להשמיד את השער החיצוני. והשחזור חי בבלוק finally, כי ל-CalcSubtotalFunc יש כמה יציאות מוקדמות עבור קודי שגיאה; דגל שנשאר מזוין אחרי החזרת שגיאה היה משחית בשקט את הנוסחה הלא-קשורה הבאה בסדר החישוב מחדש

prevIgnoreHidden := FIgnoreHiddenRows;
if (fnCode >= 101) and (fnCode <= 111) and Assigned(FIsRowHidden) then
  FIgnoreHiddenRows := True;
try
  // aggregate over Item.Child[2] .. Item.Child[ChildCount]
  // every Exit path below is covered by the finally
finally
  FIgnoreHiddenRows := prevIgnoreHidden;
end;

בדיקת ה-Assigned היא מה ששומר על השינוי תואם-לאחור. HotXLS הרחיב את בנאי המחשבון עם פרמטר שלישי שברירת המחדל שלו nil, כך שכל קוד שבונה TXLSCalculator עם הקריאה הישנה דו-הארגומנטים עדיין מתקמפל ועדיין מקבל את התנהגות הכלל-מוסתר הישנה. שום דבר בצורת ה-API הקיים לא השתנה

מאיפה סיבית השורה-המוסתרת באמת מגיעה?

מגיליון העבודה, דרך שני מקורות שונים, כי HotXLS נושא שני מנועי חוברת עבודה. צד ה-BIFF הישן עונה מ-TXLSRowInfoList.GetHidden, מושג דרך TXLSWorkbook.GetRowHidden. צד ה-OOXML עונה מ-TXLSXWorksheet.GetRowHidden, מושג דרך TXLSXWorkbook.GetCalcRowHidden. שניהם מחווטים לתוך המחשבון בזמן הבנייה, לצד ה-callback של ערך התא שהם משקפים. מוסכמות השורה הן היכן שסוג הגישור הזה בדרך כלל שוגה, כך ששווה לציין אותן במפורש. המחשבון מוסר ל-callback שורה מבוססת-0, תואם לקואורדינטות ש-TXLSGetValue כבר משתמש בהן. גיליון העבודה של XLSX ממפתח את מפת השורה-המוסתרת שלו לפי מספר שורה מבוסס-1, בדיוק כפי ש-Excel ממספר שורות, שזה גם מה שה-property הציבורי RowHidden[ARow] חושף. גשר ה-XLSX לכן מוסיף אחד לפני החיפוש, וגשר ה-BIFF לא, כי TXLSRowInfoList כבר מבוסס-0. שני הגשרים מתייחסים לאינדקס גיליון או שורה מחוץ לטווח התקף כנראה, כך ששאילתה מחוץ-לגבולות מתדרדרת לתשובת הכלל-מוסתר הישנה במקום להשמיט נתונים

מה משתנה עבור חוברות עבודה מסוננות

זה המקרה שמייצר את כרטיסי התמיכה. החלת AutoFilter ב-HotXLS דרך ApplyAutoFilter מעריכה את קריטריון העמודה ומסתירה כל שורת נתונים שלא תואמת, שזה בדיוק מה ש-Excel עושה כשמשתמש לוחץ תפריט נגלל של מסנן. לפני v2.197.0 השורות המוסתרות הללו היו בלתי-נראות למשתמש ולגמרי נראות למנוע החישוב, כך ש-SUBTOTAL(109, ...) בצד השרת דיווח את הסכום הלא-מסונן. עכשיו אותה קריאה מדווחת את המסונן

var
  Book: TXLSXWorkbook;
  Sheet: TXLSXWorksheet;
  VisibleRows: Integer;
begin
  Book := TXLSXWorkbook.Create;
  try
    Book.Open('orders.xlsx');
    Sheet := Book.Sheets[0];

    Sheet.SetAutoFilter('A1:E500');
    Sheet.AddAutoFilterColumn(3, xlsxAfOpGreaterOrEqual, '1000');
    VisibleRows := Sheet.ApplyAutoFilter;   // hides the non-matching rows

    Sheet.Cells[501, 4].Formula := '=SUBTOTAL(109,D2:D500)';
    Book.Recalculate;
    // The cell value now agrees with what Excel shows for the same filter,
    // and VisibleRows tells you how many rows fed into it

    Book.SaveAs('orders-filtered.xlsx');
  finally
    Book.Free;
  end;
end;

הסתרה ידנית עובדת באותה דרך, מכיוון ש-RowHidden[ARow] := True הוא אותו מצב שהמסנן כותב. השקילות הזו מכוונת ב-Excel ומחזיקה עכשיו גם ב-HotXLS. השלכה אחת ראויה להערה בכל תיעוד שמגיע עם חוברות העבודה שיצרתם: סכום שחושב עם קוד 109 הוא מספר תלוי-תצוגה, כך שנמען שמנקה את המסנן משנה אותו. כשדוח חייב לציין מספר קבוע ללא קשר למה שהקורא עושה לתצוגה, קוד 9 הוא הבחירה הנכונה ותמיד הייתה. מסננים, אימות, וטבלאות מכוסים יחד בהמאמר על אימות נתונים, AutoFilter, וטבלאות. מכיוון שהסתרת שורות לא נוגעת בשום נוסחה, היא גם לא מלכלכת את גרף התלויות מעצמה, ששווה לדעת אם אתם מסתמכים על חישוב מחדש הדרגתי על פני תת-הגרף המלוכלך כדי לשמור חוברות עבודה גדולות מגיבות

קודי אפשרות AGGREGATE ומגבלה אחת שעדיין פתוחה

AGGREGATE הוא SUBTOTAL עם ארגומנט מדיניות שני, ו-HotXLS מטפל בו ב-CalcAggregateFunc. ארגומנט האפשרות מקודד מתגים עצמאיים: האם קריאות SUBTOTAL ו-AGGREGATE מקוננות בתוך הטווח מדולגות, האם ערכים בשורות מוסתרות מדולגים, והאם ערכי שגיאה מודחקים במקום מופצים. HotXLS מזיין את שער השורה-המוסתרת המשותף עבור קודי אפשרות 2, 3, 6, ו-7, ומדחיק ערכי שגיאה עבור קודי אפשרות 4 עד 7. ארגומנט מספר-הפונקציה אז בוחר את האגרגציה בדיוק כפי ש-SUBTOTAL עושה, כולל ניתוב שונות, סטיית תקן, ומכפלה דרך הצמצמים שלהם. פער מתועד אחד נשאר, ועדיף לציין אותו כאן מאשר לגלות אותו בייצור: הסמנטיקה של התעלמות-מ-SUBTOTAL-מקונן המשויכת לקודי האפשרות הנמוכים אינה ממומשת ב-HotXLS. זיהוי SUBTOTAL מקונן בתוך טווח מוזכר דורש סימון מצב הרקורסיה של המעריך כך שאגרגציה פנימית יכולה להכריז על עצמה לחיצונית, שזה שינוי גדול יותר משער השורה-המוסתרת. בפועל החשיפה קטנה, כי חוברות עבודה אמיתיות כמעט תמיד ממקמות נוסחאות SUBTOTAL מחוץ לטווחים שנוסחאות SUBTOTAL אחרות מצטברות עליהם. אם המחולל שלכם כן בונה טווחי אגרגציה חופפים, אל תסתמכו על קודי האפשרות הנמוכים כדי לבטל כפילויות שלהם

שומר ה-arity שנשלח לצידו

גרסה 2.197.0 גם סגרה פער אימות באותו dispatcher, וסיבת העיצוב היא אותה סיבה שהניעה את שדה ה-scratch: לשים את הבדיקה במקום שאפשר לכתוב אותה פעם אחת. בערך 280 גופי פונקציה מובנים כל אחד אימת את ספירת הארגומנטים שלו מול Item.ChildCount, מה שהשאיר ללא גבול עקבי עבור המקרה של יותר מדי ארגומנטים. קריאה כמו =SIN(1,2) הגיעה לגוף פונקציה שבחן את הארגומנט הראשון שלו, התעלם מהעודף, והחזיר מספר סביר במקום ש-Excel מחזיר #VALUE!. ל-HotXLS כבר היה מאוחסן ה-arity המוצהר של כל built-in ברשם הפונקציות שלו, חשוף כ-THashFunc.ArgsCnt עם -1 המסמן פונקציה variadic כמו SUM, IF, או CONCAT. גרסה 2.197.0 העבירה את זה דרך property חדש TXLSFormula.FuncArgsCntByPtg והוסיפה שער אחד בראש GetValueItemFunc, ה-dispatcher הראשי

lDeclaredArgs := FFormula.FuncArgsCntByPtg[Item.IntValue];
if lDeclaredArgs >= 0 then
begin
  lProvidedArgs := Item.ChildCount - 1;   // Child[0] is the function node
  if lProvidedArgs > lDeclaredArgs then
  begin
    Result := lxErrorValue;               // =SIN(1,2) now yields #VALUE!
    Exit;
  end;
end;

השומר דוחה יותר מדי ארגומנטים ובכוונה לא אומר דבר על מעט מדי. השמטת ארגומנט אופציונלי נגרר היא חוקית ב-Excel עבור VLOOKUP, SUBSTITUTE, ורשימה ארוכה של אחרים, כך שבדיקה סימטרית הייתה שוברת נוסחאות נכונות כדי לתפוס לא-נכונות. מזהים לא-ידועים מדווחים כ-variadic ומדלגים על השער לגמרי, מה ששומר על פונקציות מוגדרות-משתמש מחוץ לדרכו; אם אתם רושמים פונקציות משלכם, ההתנהגות המתוארת בהמדריך למנוע הנוסחאות ופונקציות מותאמות אישית לא מושפעת. ריכוז המקרה של מעט-מדי הוא עבודה נפרדת, כי לכל אחד מ-280 הגופים הללו יש סמנטיקת קוד שגיאה משלו והם חייבים להיבדק אחד בכל פעם ולא להיות מונחים כמובן מאליו

מנוע החישוב המתואר כאן, שני פני חוברת העבודה, וה-API-ים של AutoFilter ונראות-שורה שמזינים אותו הם חלק מ-רכיב גיליון האלקטרוני של HotXLS ל-Delphi, שמגיע עם קוד מקור מלא עבור Delphi ו-C++Builder ולא דורש התקנת Excel במחשב שמריץ אותו