כתיבת נוסחאות בשיטת R1C1 ב-Delphi עם HotXLS

רכיב HotXLS מקמפל נוסחאות שנכתבו בשיטת הפניה R1C1 באמצעות הפונקציה TXLSCalculator.GetCompiledFormulaR1C1, המקבלת צורות כתיבה כמו R2C3 (מוחלטת), R[-1]C[2] (היסט יחסי) ו-RC (התא הנוכחי), ממירה אותן לכתיבת A1 בהתאם לשורה ולעמודה של התא בו הנוסחה ממוקמת, ומזינה את התוצאה לאותו קומפיילר המטפל בנוסחאות A1 רגילות. עבור קוד ב-Delphi וב-C++Builder המייצר את אותה נוסחה על פני מאות שורות, מתודה יחידה זו מונעת סוג שלם של באגים של שרשור מחרוזות

סוג זה של באג מוכר לכל מי שמילא עמודה באופן תוכניתי. אתם מבצעים לולאה על פני שורות, ועבור כל שורה אתם בונים מחרוזת נוסחת A1 באמצעות Format('D%d*E%d', [Row, Row]). כל איטרציה מחברת מספרי שורות לתוך טקסט, ומספרי השורות הם החלק היחיד שמשתנה. טעו בהיסט פעם אחת, ערבבו מונה לולאה מבוסס 0 עם מספרי שורות A1 מבוססי 1, או הסיטו את בלוק הנתונים בשורת כותרת, וכל נוסחה בעמודה תצביע שורה אחת ליד. שום דבר אינו מעלה שגיאה; המספרים פשוט שגויים. הנוסחה שאליה באמת התכוונתם, "הכפל את שני התאים משמאלי", אינה מזכירה מספר שורה כלל, וכתיבת R1C1 מאפשרת לכם לכתוב זאת כך

מהי כתיבת R1C1 ומתי כדאי להשתמש בה?

שיטת R1C1 פונה לתאים לפי מספר שורה ועמודה במקום אות עמודה ועוד מספר שורה, והיא מסמנת הפניות יחסיות כהיסטים מפורשים מתא הנוסחה. R2C3 הוא התא המוחלט בשורה 2, עמודה 3, אשר שיטת A1 כותבת כ-$C$2. R[-1]C[2] הוא שורה אחת למעלה ושתי עמודות ימינה מכל מקום שבו הנוסחה ממוקמת. RC הוא תא הנוסחה עצמו. ההיסטים בסוגריים הם העניין: הפניה יחסית ב-R1C1 נקראת אותו הדבר ללא קשר לאיזה תא מארח אותה, בעוד שייצוג ה-A1 של אותה הפניה משתנה בכל שורה

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

כיצד HotXLS מקמפלת נוסחת R1C1?

התוכנה HotXLS חושפת את התכונה בשתי רמות, שנוספו בגרסה v2.175.0. הפונקציה TXLSCalculator.GetCompiledFormulaR1C1(UncompiledFormula: String; SheetID, CurRow, CurCol: Integer): TXLSCompiledFormula היא זו שרוב הקוד קורא לה: היא מחזירה נוסחה מקומפלת מוכנה להערכה, בדיוק כמו אחותה בשיטת A1‏ (GetCompiledFormula), אך עם שני פרמטרים נוספים המציינים את השורה והעמודה (מבוססות 0) של התא אליו הנוסחה שייכת. מתחתיה, TXLSFormula.GetCompiledR1C1 מייצרת את עץ התחביר הגולמי, ופונקציה עצמאית R1C1ToA1(const AFormula: String; CurRow, CurCol: Integer): String מבצעת את המרת הכתיבה בפועל. צינור הרינדור פשוט במכוון: תרגם טקסט R1C1 לטקסט A1 שקול באמצעות קואורדינטות התא המארח, ואז קמפל את טקסט ה-A1 דרך המנוע הקיים — אותו מנוע שמפענח שמות מוגדרים והפניות בין גיליונות ושולח פונקציות מותאמות אישית של גיליון עבודה

מכיוון שההמרה מתרחשת לפני הקומפילציה, כל מה שקורה בהמשך מתנהג כאילו כתבתם את נוסחת ה-A1 בעצמכם. הפקודה R1C1ToA1('R2C3', 4, 3) מחזירה '$C$2' ללא קשר לתא המארח, מכיוון ששתי הקואורדינטות הן מוחלטות. הפקודה R1C1ToA1('SUM(R[-3]C[0]:R[-1]C[0])', 4, 3) — נוסחה הממוקמת ב-D5, שכן CurRow = 4 ו-CurCol = 3 מבוססים על 0 — מחזירה 'SUM(D2:D4)': ההיסטים פוענחו מול שורה 5, עמודה D, והופקו כהפניות A1 יחסיות פשוטות. טווחים אינם דורשים טיפול מיוחד; הנקודתיים עוברות ללא שינוי וכל נקודת קצה מומרת באופן עצמאי

// The formula lives in D5: CurRow = 4, CurCol = 3 (both 0-based)
S := R1C1ToA1('R2C3', 4, 3);
// S = '$C$2'  (absolute row and column)

S := R1C1ToA1('SUM(R[-3]C[0]:R[-1]C[0])', 4, 3);
// S = 'SUM(D2:D4)'  (offsets resolved against D5)

S := R1C1ToA1('ROUND(R[-1]C[0], 2)', 4, 3);
// S = 'ROUND(D4, 2)'  (the R in ROUND is left alone)

מילוי עמודה עם נוסחה יחסית אחת

התועלת מתבטאת בלולאה. השוו את גרסת A1, המרנדרת מחדש את טקסט הנוסחה בכל איטרציה, עם גרסת R1C1, שבה הנוסחה היא קבועה ורק קואורדינטות המארח זזות. שניהם מקומפלים דרך TXLSCalculator ומוערכים עם GetValue; המחשבון מקבל קריאה חוזרת (callback) של ספק תאים בעת הבנייה כדי שהמעריך יוכל למשוך ערכי תאים ממקור הנתונים שלכם

// A1 style: a different formula string for every row
for Row := 1 to 500 do
begin
  FormulaText := Format('D%d*E%d', [Row + 1, Row + 1]);  // 1-based A1 rows
  Compiled := Calc.GetCompiledFormula(FormulaText, 0);
  // ... evaluate, store, free ...
end;

const
  AmountFormula = 'RC[-2]*RC[-1]';  // two cells to the left, same row
var
  Calc: TXLSCalculator;
  Compiled: TXLSCompiledFormula;
  Value: Variant;
  Row: Integer;
begin
  Calc := TXLSCalculator.Create(nil, Provider.GetValue);
  try
    for Row := 1 to 500 do
    begin
      Compiled := Calc.GetCompiledFormulaR1C1(AmountFormula, 0, Row, 5);
      try
        if Calc.GetValue(0, Compiled, Row, 5, Value, 1) = lxOk then
          StoreResult(Row, 5, Value);
      finally
        Compiled.Free;
      end;
    end;
  finally
    Calc.Free;
  end;
end;

ללולאת ה-R1C1 אין חישוב שורות בטקסט הנוסחה כלל. המשמעות של 'RC[-2]*RC[-1]' היא "אותה שורה, שתי עמודות שמאלה, כפול אותה שורה, עמודה אחת שמאלה" בשורה 2 ובשורה 500 כאחד, ואם בלוק הנתונים יזוז מאוחר יותר למטה בשל שורת כותרת, קבוע הנוסחה אינו משתנה — רק גבולות הלולאה משתנים. לגרסת ה-A1 יש שני מקומות לטעות בהם בהתאמת ה-+ 1; לגרסת ה-R1C1 יש אפס

באילו צורות R1C1 הממיר תומך?

הממיר R1C1ToA1 מזהה את הצורות המתועדות עבור גרסה v2.175.0: R[n]C[m] עבור היסטים יחסיים בכל כיוון, RnCm עבור שורה ועמודה מוחלטות, R[-n]C[m] עם היסטים שליליים, RC פשוט עבור תא הנוסחה עצמו, וטווחים כמו R1C1:R3C3 או R[-1]C:R[1]C. חלקי השורה והעמודה מנותחים באופן עצמאי, כך שגם צורות מעורבות כמו R[1]C3 — שורה יחסית, עמודה מוחלטת — עובדות, והאותיות אינן תלויות ברישיות, כך ש-r[-1]c[2] מקומפל בדיוק כמו אחותו באותיות גדולות. חלקים בסוגריים הופכים לקואורדינטות A1 לא מוגנות (יחסיות); מספרים חשופים הופכים לקואורדינטות מוחלטות מוגנות ב-$

השאלה המעניינת היא כיצד הממיר נמנע מלהשחית כל דבר אחר בנוסחה, מכיוון ש-R ו-C הן אותיות נפוצות. כלל האבחנה שלו מבוסס על אסימונים (tokens): האות R נחשבת לתחילתה של פניה רק כאשר לא קודמת לה אות אחרת, והמועמד חייב לעבור ניתוח מלא — חלק שורה אופציונלי, C חובה, חלק עמודה אופציונלי — אחרת הטקסט מוחזר ללא שינוי. זו הסיבה שהפונקציה ROUND(R[-1]C[0], 2) ממירה רק את הפניה הפנימית: אחרי ה-R ב-ROUND מגיעה O ולא ספרה, סוגריים או C, ולכן הניתוח נכשל ושם הפונקציה עובר ללא שינוי. אותו הגיון מגן על ROW(), ושמות פונקציות המתחילים ב-C אינם מועמדים כלל, שכן רק R מתחיל פניה. הערות השחרור של v2.175.0 מציינות שגם ליטרלים של מחרוזות ומזהים נמחקים; למרות זאת, אם ליטרל מצוטט בנוסחה שלכם מכיל טקסט המעוצב בדיוק כמו פניית R1C1, כדאי לבדוק את הפלט המקומפל פעם אחת לפני שסומכים עליו בייצור

בסיסי קואורדינטות ופרטי עיגון שקדאי להכיר

שתי מוסכמות נפגשות בתוך ממשק API זה, ושמירה עליהן מונעת את המלכודת האמיתית היחידה. הפרמטרים CurRow ו-CurCol של GetCompiledFormulaR1C1 מבוססים על 0, בהתאם ל-API של המחשבון, בעוד שהמספרים בתוך הכתיבה עצמה מבוססים על 1, בהתאם למה ש-Excel מציגה: R2C3 היא שורה 2, עמודה 3, שהיא $C$2, ולא $D$3. אם מונה הלולאה שלכם כבר מבוסס על 0 אתם מעבירים אותו ישירות כ-CurRow; ה-+ 1 חי בתוך הממיר, לא בקוד שלכם

פרט אחד נוסף חשוב אם הנוסחה המקומפלת תשרוד מעבר לתא שעבורו היא קומפלה. כאשר אתם משמיטים חלק שורה או עמודה — הצורות RC[-1] או R[2]C — הקואורדינטה המושמטת מפוענחת לתא המארח ומופקת כקואורדינטה מוחלטת המוגנת ב-$ בטקסט ה-A1 המומר. בזמן ההערכה זה בלתי נראה, שכן הערך זהה בשני המקרים. אך עיגון יחסי לעומת מוחלט קובע כיצד הפניות זזות כאשר שורות או עמודות מוכנסות או נמחקות מאוחר יותר, כפי שמכוסה במאמר הנלווה על התאמת הפניות לנוסחאות. אם אתם צריכים שקואורדינטה תישאר יחסית במהלך עריכות מבניות, כתבו את ההיסט במפורש — R[0]C[-1] במקום RC[-1] — כדי שהממיר יפיק הפניית A1 לא מוגנת

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