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 керують тим, що відбувається, коли формула копіюється або протягується в іншу комірку — вони нічого не роблять під час структурного редагування. Вставте рядок над другим рядком, і 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: щойно вставлені стовпці успадковують ширину свого лівого сусіда, що аналогічно команді 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
Міжаркушні структури та структури на рівні робочої книги обробляються окремими проходами, а не сканером тексту комірок. Після коригування відредагованого аркуша метод ShiftSheetGeometry передає ті самі зміни геометрії у формули на інших аркушах, які посилаються на цей аркуш, у діапазони рядів діаграм, у цілі внутрішніх гіперпосилань та у визначені імена. Визначені імена отримують додатковий захист на рівні життєвого циклу аркуша: починаючи з версії 2.150, видалення робочого аркуша перезаписує кожен кваліфікатор SheetN! у формулі визначеного імені на #REF!, а перейменування аркуша перезаписує кваліфікатор на нове ім'я, тому імена ніколи не вказують на неіснуючий аркуш. Про те, як поєднуються імена та міжаркушні формули, читайте в статті про визначені імена та міжаркушні формули
Перерахунок після зміщення
Коригування посилань перезаписує текст формули; воно не перераховує результати. Після структурного редагування кешовані значення, що зберігаються поруч із формулами, описують стару геометрію, тому надійний порядок дій такий: спочатку виконати всі вставки та видалення, потім один раз запустити перерахунок, а вже потім зберегти файл. Виконання зміщення перед перерахунком також зберігає коректність інформації про залежності — кожне перезаписане посилання вказує на свого справжнього попередника, що є саме тим, чого потребують движок інкрементного перерахунку та його граф залежностей для перерахунку мінімального набору зачеплених комірок. Якщо перезаписана формула тепер містить #REF!, перерахунок негайно виведе значення помилки замість застарілого числа у файлі
Коригування посилань у формулах поставляється як стандартна поведінка движка XLSX у складі компонента HotXLS Delphi Excel Component для Delphi та C++Builder; сторінка продукту містить повну довідку щодо API редагування робочих аркушів, включаючи описані тут методи вставки та видалення