ارجاعات فرمول هنگام درج ردیف‌ها در HotXLS

کامپوننت HotXLS هنگام درج یا حذف ردیف‌ها یا ستون‌ها در یک کاربرگ XLSX، ارجاعات فرمول را به طور خودکار تنظیم می‌کند. متدهای InsertRows، DeleteRows، InsertCols و DeleteCols موتور، هر فرمول باقی‌مانده را بازنویسی می‌کنند تا ارجاعات A1 آن — اعم از نسبی، مطلق و محدوده — پس از ویرایش ساختاری همچنان به همان داده‌ها اشاره کنند و ارجاعات به بلوک‌های حذف شده به #REF! تبدیل شوند، که این کار با رفتار اکسل مطابقت دارد

باگی که این ویژگی از آن جلوگیری می‌کند یکی از خاموش‌ترین باگ‌ها در تولید گزارش است. یک تولیدکننده ارقام روزانه را در خانه‌های C2:C9 با فرمول =SUM(C2:C9) در زیر آن می‌نویسد، سپس در مرحله بعدی یک ردیف هدر را در بالا درج می‌کند. اگر موتور فقط مقادیر سلول‌ها را جابجا کند و متن فرمول را به حال خود رها کند، آن SUM همچنان C2:C9 را می‌خواند در حالی که داده‌ها اکنون در C3:C10 زندگی می‌کنند — بنابراین مجموع فاکتور بی سروصدا آخرین روز را حذف کرده و یک هدر را دو بار حساب می‌کند. هیچ استثنایی ایجاد نمی‌شود، فایل به خوبی باز می‌شود و عدد به سادگی اشتباه است. قبل از نسخه 2.160، موتور HotXLS XLSX فرمول‌ها را در حین ویرایش‌های ساختاری دست‌نخورده رها می‌کرد؛ از نسخه 2.160 این بازنویسی خودکار است و نیازی به تنظیم هیچ پرچمی نیست

وقتی یک ردیف در اکسل درج می‌کنید چه اتفاقی برای فرمول‌ها می‌افتد؟

قانون اکسل این است که ارجاعات از داده‌ها پیروی می‌کنند و نه از آدرس‌ها. هنگامی که یک ردیف درج می‌شود، هر ارجاعی که شاخص ردیف آن در نقطه درج یا زیر آن قرار دارد، به تعداد ردیف‌های درج‌شده به پایین حرکت می‌کند؛ ارجاعاتی که کاملاً بالاتر از نقطه درج قرار دارند دست‌نخورده باقی می‌مانند. حذف ردیف‌ها همین قانون را به صورت معکوس اجرا می‌کند: ارجاعات زیر بلوک حذف شده به بالا می‌لغزند و ارجاعات به خود بلوک حذف شده به #REF! تبدیل می‌شوند زیرا سلول‌هایی که نام برده بودند دیگر وجود ندارند. ستون‌ها در امتداد محور دیگر نیز رفتار مشابهی دارند. یک کتابخانه صفحات گسترده که می‌خواهد خروجی‌اش در تماس با کاربران اکسل دوام بیاورد، باید این مکانیسم‌ها را دقیقاً بازسازی کند، زیرا کاربران بدون اینکه هرگز به آن فکر کنند، در مورد فرمول‌های خود بر اساس این شرایط استدلال می‌کنند

بخشی که توسعه‌دهندگان را شگفت‌زده می‌کند این است که ارجاعات مطلق نیز جابجا می‌شوند. علامت‌های $ در $B$2 کنترل می‌کنند که وقتی یک فرمول در سلول دیگری کپی یا پر می‌شود چه اتفاقی می‌افتد — آنها در حین ویرایش‌های ساختاری کاری انجام نمی‌دهند. یک ردیف را بالای ردیف ۲ درج کنید و اکسل $B$2 را به $B$3 بازنویسی می‌کند، در حالی که علامت‌های دلار دست‌نخورده باقی می‌مانند، زیرا مقداری که فرمول به آن وابسته است به طور فیزیکی به ردیف ۳ منتقل شده است. موتوری که فقط ارجاعات نسبی را منتقل می‌کرد، دقیقاً فرمول‌هایی را که مردم با دقت بیشتری لنگر انداخته‌اند خراب می‌کرد. HotXLS هر دو شکل را جابجا می‌کند و نشانگرهای $ را در متن بازنویسی‌شده حفظ می‌کند

HotXLS چگونه ارجاعات فرمول را به طور خودکار جابجا می‌کند؟

هر چهار متد ویرایش ساختاری در TXLSXWorksheet به یک موتور هندسی واحد واگذار می‌شوند: ShiftSheetGeometry(RowFrom, RowDelta, ColFrom, ColDelta). متد InsertRows(BeforeRow, Count) آن را با یک دلتای ردیف مثبت و DeleteRows(StartRow, Count) با یک دلتای منفی فراخوانی می‌کند، و متدهای ستون نیز همین کار را روی محور ستون انجام می‌دهند. این روال ابتدا خود سلول‌ها را جابجا می‌کند — و هر سلولی را که در داخل یک بلوک حذف شده قرار می‌گیرد حذف می‌کند — و سپس هر سلول فرمول باقی‌مانده را پیمایش کرده و متن آن را از XlsxAdjustFormulaRowColRefs عبور می‌دهد، اسکنری که ارجاعات سبک A1 را پیدا کرده و مؤلفه‌های ردیف و ستون آنها را در برابر جابجایی بازنویسی می‌کند. همین گذر، محدوده‌های ادغام‌شده، هایپرلینک‌ها، کامنت‌ها، تصاویر، نمودارها، فرمت‌های شرطی، اعتبارسنجی داده‌ها و محدوده‌های جدول را جابجا می‌کند، بنابراین کل برگ به عنوان یک واحد حرکت می‌کند

دانستن چند جزئیات از اسکنر ارزش دارد. متد XlsxAdjustFormulaRowColRefs ارجاعات تک سلولی را در هر چهار شکل لنگر (A1، $A1، A$1، $A$1) و محدوده‌های دو گوشه مانند A1:B3 را تشخیص می‌دهد و هر نقطه انتهایی را به طور مستقل تنظیم می‌کند. فرمول‌هایی که متن آنها از قبل با # شروع می‌شود — یک نشانگر خطا از ویرایش قبلی — به جای اسکن مجدد، نادیده گرفته می‌شوند. و متد InsertCols یکی از ویژگی‌های ظریف خودسازگاری با اکسل را اضافه می‌کند: ستون‌های تازه درج شده عرض همسایه چپ خود را به ارث می‌برند، که این همان کاری است که دستور Insert Sheet Columns اکسل انجام می‌دهد

// 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

چه زمانی یک ارجاع حذف شده به #REF! تبدیل می‌شود؟

قانون جابجایی برای یک شاخص ردیف یا ستون واحد، سه نتیجه دارد. یک شاخص قبل از نقطه ویرایش بدون تغییر می‌ماند. یک شاخص در نقطه ویرایش یا بعد از آن، به اندازه دلتا جابجا می‌شود. و در زمان حذف، شاخصی که در داخل بلوک حذف شده قرار می‌گیرد، مقدار جدید معناداری ندارد — سلول از بین رفته است — بنابراین اسکنر کل ارجاع را به عنوان #REF! بازنویسی می‌کند. برای یک ارجاع محدوده، هر دو نقطه انتهایی از همان قانون عبور می‌کنند و اگر هر یک از نقاط انتهایی در داخل بلوک حذف شده قرار گیرد، ارجاع به جای اینکه نیمه معتبر رها شود، به عنوان #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

کدام اشکال ارجاع بازنویسی نمی‌شوند؟

اسکنر ارجاعات A1 همان برگ را با شکل صریح حرف‌ستون‌به‌علاوه-عدد‌ردیف هدف قرار می‌دهد و شایسته است در مورد آنچه خارج از آن قرار می‌گیرد دقیق باشیم. ارجاعات کل ستون مانند A:A و ارجاعات کل ردیف مانند 1:1 فاقد یکی از دو مؤلفه هستند، بنابراین اسکنر آنها را همانطور که نوشته شده‌اند رها می‌کند. ارجاعات جدول ساختاریافته (Table1[Amount]) نیز بدون تغییر عبور می‌کنند. بازنویسی همچنین به طور انحصاری روی نشانه‌گذاری A1 کار می‌کند — اگر کد شما فرمول‌ها را به سبک R1C1 می‌سازد، قبل از ویرایش ساختاری آنها را به A1 تبدیل کنید، همانطور که در مقاله همراه در مورد نشانه‌گذاری فرمول R1C1 در دلفی توضیح داده شده است

ساختارهای بین برگی و در سطح کارپوشه (workbook) به جای اسکنر متن سلول، توسط گذرهای مجزا مدیریت می‌شوند. پس از تنظیم برگ ویرایش‌شده، متد ShiftSheetGeometry همان تغییر هندسی را به فرمول‌های سایر برگ‌ها که به برگ ویرایش‌شده ارجاع دارند، به محدوده‌های سری نمودار، به اهداف هایپرلینک داخلی و به نام‌های تعریف‌شده منتقل می‌کند. نام‌های تعریف‌شده محافظت بیشتری را در سطح چرخه عمر برگ دریافت می‌کنند: از نسخه 2.150، حذف یک کاربرگ، هر توصیف‌گر SheetN! را در داخل فرمول نام تعریف‌شده به #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

محاسبه مجدد پس از جابجایی

تنظیم ارجاع، متن فرمول را بازنویسی می‌کند؛ این کار نتایج را دوباره محاسبه نمی‌کند. پس از یک ویرایش ساختاری، مقادیر کش شده ذخیره شده در کنار فرمول‌ها هندسه قدیمی را توصیف می‌کنند، بنابراین توالی قابل اعتماد این است: ابتدا تمام درج‌ها و حذف‌ها را انجام دهید، سپس محاسبات مجدد را یک بار اجرا کنید و سپس ذخیره کنید. اجرای جابجایی قبل از محاسبه مجدد همچنین اطلاعات وابستگی را دقیق نگه می‌دارد — هر ارجاع بازنویسی شده نام سلف واقعی خود را می‌آورد، که این دقیقاً همان چیزی است که موتور محاسبه مجدد تدریجی و گراف وابستگی آن برای محاسبه مجدد حداقل مجموعه سلول‌های آسیب‌دیده نیاز دارند. اگر یک فرمول بازنویسی شده اکنون حاوی #REF! باشد، محاسبه مجدد بلافاصله مقدار خطا را نشان می‌دهد به جای اینکه یک عدد کهنه را در فایل باقی بگذارد

تنظیم ارجاع فرمول به عنوان رفتار استاندارد موتور XLSX در HotXLS Delphi Excel Component برای دلفی و C++Builder ارائه می‌شود؛ صفحه محصول شامل مرجع کامل API ویرایش کاربرگ، از جمله متدهای درج و حذف نشان داده شده در اینجا است