רכיב HotXLS מתאים הפניות לנוסחאות באופן אוטומטי בעת הוספה או מחיקה של שורות או עמודות בגיליון עבודה של XLSX. המתודות InsertRows, DeleteRows, InsertCols ו-DeleteCols של המנוע משכתבות כל נוסחה שנותרה כך שהפניות ה-A1 שלה — יחסיות, מוחלטות וטווחים — ימשיכו להצביע על אותם הנתונים לאחר העריכה המבנית, והפניות לתוך בלוק שנמחק הופכות ל-#REF!, בהתאם להתנהגות של Excel
הבאג שזה מונע הוא אחד השקטים ביותר בהפקת דוחות. מחולל כותב נתונים יומיים לתוך C2:C9 עם =SUM(C2:C9) מתחתיהם, ולאחר מכן שלב מאוחר יותר מכניס שורת כותרת בחלק העליון. אם המנוע רק מזיז ערכי תאים ומשאיר את טקסט הנוסחה כפי שהוא, פונקציית ה-SUM הזו עדיין תקרא את C2:C9 בעוד שהנתונים חיים כעת ב-C3:C10 — כך שהסכום משמיט בשקט את היום האחרון וסופר פעמיים כותרת. שום דבר אינו מעלה חריגה, הקובץ נפתח כשורה, והמספר פשוט שגוי. לפני גרסה 2.160, מנוע ה-HotXLS XLSX השאיר נוסחאות ללא שינוי במהלך עריכות מבניות; מאז 2.160 השכתוב הוא אוטומטי ואין צורך להגדיר דגל מיוחד
מה קורה לנוסחאות כאשר מוסיפים שורה ב-Excel?
הכלל של Excel הוא שהפניות עוקבות אחר נתונים, לא אחר כתובות. כאשר שורה מוכנסת, כל הפניה שאינדקס השורה שלה נמצא בנקודת ההכנסה או מתחתיה זזה למטה במספר השורות שהוכנסו; הפניות הנמצאות כולן מעל נקודת ההכנסה אינן משתנות. מחיקת שורות מריצה את אותו כלל בסדר הפוך: הפניות מתחת לבלוק שנמחק מחליקות למעלה, והפניות לתוך הבלוק שנמחק עצמו הופכות ל-#REF! מכיוון שהתאים שהן הגדירו אינם קיימים עוד. עמודות מתנהגות באופן זהה לאורך הציר השני. ספריית גיליונות אלקטרוניים שרוצה שהפלט שלה ישרוד אינטראקציה עם משתמשי Excel חייבת לשחזר מכניקה זו בדיוק, מכיוון שמשתמשים חושבים על הנוסחאות שלהם במונחים אלה מבלי לחשוב על כך כלל
החלק שמפתיע מפתחים הוא שגם הפניות מוחלטות זזות. עוגני ה-$ ב-$B$2 שולטים במה שקורה כאשר נוסחה מועתקת או ממולאת לתא אחר — הם אינם עושים דבר במהלך עריכות מבניות. הכניסו שורה מעל שורה 2 ו-Excel תשכתב את $B$2 ל-$B$3, כאשר סימני הדולר נשארים שלמים, מכיוון שהערך בו הנוסחה תלויה זז פיזית לשורה 3. מנוע שהיה מסיט רק הפניות יחסיות היה משחית בדיוק את הנוסחאות שאנשים מעגנים בצורה המכוונת ביותר. HotXLS מסיטה את שתי הצורות ושומרת על סימוני ה-$ בטקסט המשוכתב
כיצד HotXLS מסיטה הפניות לנוסחאות באופן אוטומטי?
כל ארבע שיטות העריכה המבנית ב-TXLSXWorksheet מאצילות סמכות למנוע גיאומטריה יחיד: ShiftSheetGeometry(RowFrom, RowDelta, ColFrom, ColDelta). הפונקציה InsertRows(BeforeRow, Count) קוראת לו עם דלתא חיובית של שורות, DeleteRows(StartRow, Count) עם דלתא שלילית, ושיטות העמודות עושות את אותו הדבר בציר העמודות. השגרה מעבירה תחילה את התאים עצמם — תוך השמטת כל תא הנופל בתוך בלוק שנמחק — ואז עוברת על כל תא נוסחה שנותר ומעבירה את הטקסט שלו דרך XlsxAdjustFormulaRowColRefs, סורק המוצא הפניות בסגנון A1 ומשכתב את רכיבי השורה והעמודה שלהן מול ההסטה. אותו מעבר מעביר גם טווחים ממוזגים, היפר-קישורים, הערות, תמונות, תרשימים, עיצובים מותנים, אימותי נתונים וטווחי טבלאות, כך שהגיליון כולו זז כיחידה אחת
// Layout before the edit:
// C2..C9 daily figures
// C10 =SUM(C2:C9)
Sheet.InsertRows(2, 1); // one blank row before row 2
// Layout after the call:
// C3..C10 daily figures
// C11 =SUM(C3:C10) -- the range moved with the dataכדאי להכיר כמה פרטים על הסורק. XlsxAdjustFormulaRowColRefs מזהה הפניות לתא בודד בכל ארבע צורות העיגון (A1, $A1, A$1, $A$1) וטווחים בעלי שתי פינות כמו A1:B3, ומתאים כל נקודת קצה באופן עצמאי. נוסחאות שהטקסט שלהן כבר מתחיל ב-# — סימון שגיאה מעריכה קודמת — מדולגות במקום להיסרק מחדש. ו-InsertCols מוסיף מנגנון התאמה של Excel משלו: עמודות שהוכנסו לאחרונה יורשות את הרוחב של השכן השמאלי שלהן, שזה מה שפקודת Insert Sheet Columns של Excel עושה
מתי הפניה שנמחקה הופכת ל- #REF!?
לכלל ההסטה עבור אינדקס שורה או עמודה בודד יש שלוש תוצאות. אינדקס שלפני נקודת העריכה אינו משתנה. אינדקס בנקודת העריכה או אחריה זז לפי הדלתא. ותחת מחיקה, לאינדקס הנופל בתוך הבלוק שנמחק אין ערך חדש משמעותי — התא אינו קיים עוד — ולכן הסורק משכתב את כל ההפניה כ-#REF!. עבור הפניה לטווח, שתי נקודות הקצה מועברות דרך אותו כלל, ואם אחת מנקודות הקצה נוחתת בתוך הבלוק שנמחק, ההפניה כולה משוכתבת כ-#REF! במקום להישאר חצי תקינה
// A12 holds =A4+A6+A10
Sheet.DeleteRows(5, 3); // delete rows 5..7
// The formula, now in A9, reads =A4+#REF!+A7
// A4 : above the deleted block, unchanged
// A6 : inside rows 5..7, gone -> #REF!
// A10 : below the block, slides up -> A7הפקת שגיאת #REF! ברורה במקום הפניה שקטה מחדש היא הפשרה הנכונה, וזו הפשרה ש-Excel עושה. נוסחה שמצביעה על תא שכן לאחר שקלט האמיתי שלה נמחק תחזיר מספר שנראה הגיוני; שגיאת #REF! מופצת דרך נוסחאות תלויות וצפה בבדיקה הראשונה. אותה המרה חלה גם על ציר העמודות
// E1 holds =B1*$C$1
Sheet.DeleteCols(3, 1); // remove column C
// The formula, now in D1, reads =B1*#REF!
// The absolute anchor did not protect $C$1 -- the cell itself is goneאילו צורות הפניה אינן משוכתבות?
הסורק מכוון להפניות A1 באותו גיליון בעלות מבנה מפורש של אות עמודה ועוד מספר שורה, וכדאי לדייק במה שנמצא מחוץ לזה. הפניות לעמודה שלמה כמו A:A והפניות לשורה שלמה כמו 1:1 חסרות את אחד משני הרכיבים, ולכן הסורק משאיר אותן כפי שנכתבו. הפניות לטבלאות מובנות (Table1[Amount]) מועברות גם הן ללא שינוי. השכתוב פועל אך ורק על כתיבת A1 — אם הקוד שלכם בונה נוסחאות בסגנון R1C1, המירו אותן ל-A1 לפני עריכה מבנית, כפי שמתואר במאמר הנלווה על כתיבת נוסחאות R1C1 ב-Delphi
עדכנו מחדש לאחר ההסטה
התאמת הפניות לנוסחאות משכתבת את טקסט הנוסחה; היא אינה מחשבת מחדש את התוצאות. לאחר עריכה מבנית, הערכים השמורים במטמון לצד הנוסחאות מתארים את הגיאומטריה הישנה, ולכן הרצף האמין הוא: בצעו תחילה את כל ההכנסות והמחיקות, לאחר מכן הפעילו חישוב מחדש פעם אחת, ואז שמרו. הרצת ההסטה לפני החישוב מחדש שומרת גם על אמינות מידע התלות — כל הפניה משוכתבת מציינת את התקדים האמיתי שלה, שזה בדיוק מה שמנוע החישוב מחדש המצטבר וגרף התלות שלו צריכים כדי לחשב מחדש את קבוצת התאים המינימלית המושפעת. אם נוסחה משוכתבת מכילה כעת #REF!, החישוב מחדש יעלה את ערך השגיאה באופן מיידי במקום להשאיר מספר מיושן בקובץ
התאמת הפניות לנוסחאות מגיעה כהתנהגות סטנדרטית של מנוע ה-XLSX ב-רכיב HotXLS Delphi Excel עבור Delphi ו-C++Builder; דף המוצר נושא את מפרט ה-API המלא לעריכת גיליונות עבודה, כולל שיטות ההכנסה והמחיקה המוצגות כאן