הפניות טבלה מובנות של Excel ב-Delphi עם HotXLS

HotXLS מעריך כעת הפניות טבלה מובנות, כך ש-=SUM(Table1[Amount]) מפיק מספר במקום להידלג. הפותר מטפל ב-Table[Column], ב-Table[[Column]], בטווחי עמודות כגון Table[[Q1]:[Q4]], ובמפרטי הפריט [#Data], [#All], [#Headers] ו-[#Totals], כשהוא פותר כל אחד מהם מול מודל הטבלה של חוברת העבודה בזמן הניתוח, בעוד טקסט הנוסחה המקורי עובר הלוך-חזור מילה במילה

צורה אחת נעדרת בכוונה, וזו זו שאנשים נתקלים בה ראשונה. הקיצור של השורה הנוכחית [@Column] אינו נתמך, מסיבה מבנית ששווה להבין ולא לעקוף בעיוורון

מדוע הפניה מובנית אינה סתם טווח עם שם ידידותי?

מפני ששם מוגדר מקפיא כתובת והפניית טבלה לא. כתבו DataBlock כשם שמצביע על Sheet1!$A$2:$D$100 והוא נשאר המלבן הזה עד שמשהו כותב אותו מחדש. כתבו Sales[Amount] וזה אומר "עמודת Amount של טבלת Sales", יהיה מה שיהיה היקף הטבלה הזה כשהנוסחה מוערכת. הוסיפו עשרים שורות לטבלה והסכום מכסה אותן; אין הפניה להתאים מפני שמעולם לא הייתה כתובת בנוסחה מלכתחילה

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

הדקדוק ש-HotXLS פותר

דקדוק המפרט הנתמך מכסה תוצאה מלבנית יחידה ושווה לנסח במדויק, מפני שהתיעוד של Excel מציג משטח הרבה יותר גדול ממה שרוב המנועים מממשים. HotXLS מקבל [Col] ואת המשתנה בסוגריים [[Col]], את מפרטי הפריט הפשוטים [#Data], [#All], [#Headers] ו-[#Totals], את הצורה המשולבת [[#Data],[Col]], טווח בתוך מפרט פריט כמו [[#Data],[Col1]:[Col2]], וטווח פשוט [Col1]:[Col2]

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

var
  Book: TXLSXWorkbook;
  Sheet: TXLSXWorksheet;
  Cols: TStringList;
begin
  Book := TXLSXWorkbook.Create;
  Cols := TStringList.Create;
  try
    Sheet := Book.Sheets.Add('Sales');
    Cols.Add('Region');
    Cols.Add('Q1');
    Cols.Add('Q2');
    Cols.Add('Amount');
    Sheet.Tables.Add('SalesTable', 'A1:D25', Cols);
    // ... כתבו את שורת הכותרת ואת 24 שורות הנתונים ...

    Sheet.Cells[27, 4].Formula := 'SUM(SalesTable[Amount])';
    Sheet.Cells[28, 4].Formula := 'SUM(SalesTable[[Q1]:[Q2]])';
    Sheet.Cells[29, 4].Formula := 'COUNTA(SalesTable[[#Data],[Region]])';
    Sheet.Cells[30, 4].Formula := 'ROWS(SalesTable[#All])';

    Book.Recalculate;
    Book.SaveAs('sales.xlsx');
  finally
    Cols.Free;
    Book.Free;
  end;
end;

מדוע צורת השורה הנוכחית מוחרגת בכוונה?

[@Column] ו-[#This Row] פירושם "התא של העמודה הזו בשורה שבה הנוסחה הזו יושבת". הערך תלוי אם כן במיקום התא המעריך, לא רק בטבלה. זהו סוג הפניה שונה: לא מלבן שהמקמפל יכול לפתור פעם אחת, אלא פתרון לכל תא שצריך להתבצע מחדש לכל שורה שהנוסחה תופסת

HotXLS מחזיר False מפותר טווח הטבלה עבור הצורות האלה, מה שמנתב אותן לנתיב הדילוג-ללא-ערך. טקסט הנוסחה נשמר ונכתב בחזרה ללא שינוי, כך שחוברת עבודה שמשתמשת ב-[@Amount] נפתחת נכון ב-Excel אחרי הלוך-חזור דרך היישום שלכם; רק הערך המחושב על ידי HotXLS נעדר. בין ערך נעדר לערך שמחושב מול השורה הלא נכונה, ההיעדר הוא זה שאפשר לזהות

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

var
  Book: TXLSXWorkbook;
  Sheet: TXLSXWorksheet;
  Table: TXLSXTable;
  Row: Integer;
begin
  Book := TXLSXWorkbook.Create;
  try
    if Book.Open('sales.xlsx') <> 1 then Exit;
    Sheet := Book.Sheets[1];

    Table := Sheet.Tables.FindByName('SalesTable');
    if Table <> nil then
    begin
      // חיפוש בסגנון רשומות מעל גוף הטבלה, תוצאת שורה מבוססת-1
      Row := Table.FindFirst(Sheet, 'Region', 'EMEA');
      while Row > 0 do
      begin
        Log(VarToStr(Sheet.Cells[Row, 4].Value));
        Row := Table.FindNext(Sheet, 'Region', 'EMEA', Row);
      end;
    end;
  finally
    Book.Free;
  end;
end;

מה קורה כשהטבלה משנה צורה

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

גידול שורות הוא המקרה ההפוך ואינו זקוק לשום התאמה. מפני שההפניה נוקבת בשם הטבלה ולא במלבן, הוספת שורות בתוך טווח הטבלה מרחיבה את מה ש-[#Data] מכסה מבלי לגעת בנוסחה אחת. זו התכונה שהופכת טבלאות לשוות שימוש בתבנית דוח: שורת הסיכומים ממשיכה לסכם כל מה שהייבוא הפיק, יהיו אשר יהיו מספר השורות

משמעת הלוך-חזור

HotXLS שומר את טקסט הנוסחה המקורי. חוברת עבודה שנטענה עם SUM(SalesTable[Amount]) נשמרת עם SUM(SalesTable[Amount]), לא עם הכתובת הפתורה SUM(D2:D25). זה חשוב יותר ממה שנראה: משתמש שפותח את הפלט שלכם ב-Excel מצפה לראות את הנוסחה שכתב, וכתובת פתורה הייתה בשקט הופכת מודל שמתחזק את עצמו למודל שביר שמפסיק לכסות שורות חדשות

שתי יכולות קשורות משלימות את התמונה. הגדרות הטבלה עצמן, כולל טבלאות ללא כותרת והערות לפי-טבלה, עוברות הלוך-חזור דרך מודל הטבלה המתואר באימות נתונים, AutoFilter וטבלאות Excel. וכשתאים רבים חולקים תבנית אחת, XLSX מאחסן אותם פעם אחת כנוסחה משותפת, שמורחבת ונפלטת מחדש כמכוסה בהרחבת si של נוסחה משותפת. הפניות מובנות בתוך נוסחאות משותפות עוברות דרך שני הנתיבים, כך ששניהם צריכים להתנהג כראוי, וכך הם מתנהגים

HotXLS קורא וכותב XLS, XLSX ו-ODS מ-Delphi ומ-C++Builder ללא התקנת Excel וללא אוטומציית Office, ומעריך נוסחאות במנוע משלו. מודל הטבלה, מנוע הנוסחאות וה-API של חישוב מחדש מתועדים בדף רכיב HotXLS ל-Delphi