A HotXLS automatikusan korrigálja a képlethivatkozásokat, amikor sorokat vagy oszlopokat szúr be vagy töröl egy XLSX munkalapon. A motor InsertRows, DeleteRows, InsertCols és DeleteCols metódusai minden megmaradt képletet újraírnak, így azok A1 hivatkozásai – legyen az relatív, abszolút vagy tartományhivatkozás – továbbra is ugyanarra az adatra mutatnak a szerkezeti módosítás után, a törölt blokkra mutató hivatkozások pedig #REF! értékűvé válnak, megegyezve az Excel viselkedésével.
A hiba, amit ez megelőz, az egyik legcsendesebb a jelentéskészítés során. A generátor napi adatokat ír a C2:C9 tartományba, alatta a =SUM(C2:C9) képlettel, majd egy későbbi lépésben beszúr egy fejlécsort a lap tetejére. Ha a motor csak a cellaértékeket mozgatja el, és a képlet szövegét érintetlenül hagyja, a SUM továbbra is a C2:C9-et fogja olvasni, miközben az adatok már a C3:C10-ben élnek – így a végösszeg csendben elhagyja az utolsó napot, és duplán számolja a fejlécet. Semmi nem vált ki kivételt, a fájl gond nélkül megnyílik, és a szám egyszerűen hibás lesz. A 2.160-as verzió előtt a HotXLS XLSX motorja érintetlenül hagyta a képleteket a szerkezeti módosítások során; a 2.160 óta az újraírás automatikus, és nincs külön beállítandó flag.
Mi történik a képletekkel, ha sort szúr be az Excelben?
Az Excel szabálya az, hogy a hivatkozások az adatokat követik, nem pedig a címeket. Sor beszúrásakor minden olyan hivatkozás, amelynek sorindexe a beszúrási pontnál vagy az alatt található, lejjebb tolódik a beszúrt sorok számával; a beszúrási pont feletti hivatkozások érintetlenül maradnak. A sorok törlése ugyanezt a szabályt futtatja fordítva: a törölt blokk alatti hivatkozások felfelé csúsznak, a törölt blokkba mutató hivatkozások pedig #REF! értékűvé válnak, mivel a megnevezett cellák már nem léteznek. Az oszlopok teljesen ugyanígy viselkednek a másik tengely mentén. Egy táblázatkezelő könyvtárnak, amely azt szeretné, hogy a kimenete túlélje az Excel-felhasználókkal való találkozást, pontosan reprodukálnia kell ezeket a mechanizmusokat, mert a felhasználók ezekkel a fogalmakkal gondolkodnak a képleteikről, anélkül, hogy valaha is belegondolnának.
A fejlesztőket meglepő részlet az, hogy az abszolút hivatkozások is elmozdulnak. A $B$2-ben lévő $ horgonyok azt szabályozzák, hogy mi történik, amikor a képletet egy másik cellába másolják vagy kitöltik – a szerkezeti módosítások során semmit sem tesznek. Ha beszúr egy sort a 2. sor fölé, az Excel a $B$2-t $B$3-ra írja át, a dollárjeleket megtartva, mert az érték, amelytől a képlet függ, fizikailag a 3. sorba került. Egy olyan motor, amely csak a relatív hivatkozásokat tolná el, pontosan azokat a képleteket rontaná el, amelyeket az emberek a leginkább rögzíteni szeretnének. A HotXLS mindkét formát eltolja, és megőrzi a $ jelölőket az újraírt szövegben.
Hogyan tolja el a HotXLS a képlethivatkozásokat automatikusan?
A TXLSXWorksheet mind a négy szerkezeti módosítási metódusa egyetlen geometriai motorhoz delegálja a feladatot: ShiftSheetGeometry(RowFrom, RowDelta, ColFrom, ColDelta). Az InsertRows(BeforeRow, Count) pozitív sor-deltával hívja meg, a DeleteRows(StartRow, Count) negatívval, és az oszlopmetódusok is ugyanezt teszik az oszloptengelyen. A rutin először magukat a cellákat helyezi át – eldobva minden olyan cellát, amely a törölt blokkba esik –, majd végigmegy az összes megmaradt képletcellán, és átadja azok szövegét a XlsxAdjustFormulaRowColRefs eljárásnak. Ez a szkenner megtalálja az A1-stílusú hivatkozásokat, és újraírja azok sor- és oszlopkomponenseit az eltolásnak megfelelően. Ugyanez a lépés áthelyezi az összevont tartományokat, hyperlinkeket, megjegyzéseket, képeket, diagramokat, feltételes formázásokat, adatérvényesítéseket és táblázattartományokat is, így a teljes lap egyetlen egységként mozog.
// Elrendezés a módosítás előtt:
// C2..C9 napi adatok
// C10 =SUM(C2:C9)
Sheet.InsertRows(2, 1); // egy üres sor a 2. sor előtt
// Elrendezés a hívás után:
// C3..C10 napi adatok
// C11 =SUM(C3:C10) -- a tartomány elmozdult az adatokkalA szkenner néhány részletét érdemes megismerni. A XlsxAdjustFormulaRowColRefs felismeri az egycellás hivatkozásokat mind a négy horgonyformában (A1, $A1, A$1, $A$1) és a két sarkos tartományokat, mint például az A1:B3, mindegyik végpontot függetlenül korrigálva. Azokat a képleteket, amelyek szövege már #-vel kezdődik – egy korábbi szerkesztésből származó hibajelzővel –, kihagyja ahelyett, hogy újra beolvasná. Az InsertCols pedig egy saját, Excellel egyenértékű finomságot ad hozzá: az újonnan beszúrt oszlopok öröklik a bal oldali szomszédjuk szélességét, amit az Excel „Insert Sheet Columns” parancsa is tesz.
Mikor válik a törölt hivatkozás #REF! értékűvé?
Az egyetlen sor- vagy oszlopindex eltolási szabályának három kimenetele van. A módosítási pont előtti index változatlan marad. A módosítási ponton lévő vagy azon túli index elmozdul a deltával. Törlés esetén pedig a törölt blokkba eső indexnek nincs értelmezhető új értéke – a cella eltűnt –, így a szkenner a teljes hivatkozást #REF!-re írja át. Egy tartományhivatkozásnál mindkét végpont ugyanazon a szabályon megy keresztül, és hu bármelyik végpont a törölt blokkba esik, a hivatkozás #REF!-re lesz átírva ahelyett, hogy félig érvényesen maradna.
// Az A12 tartalma: =A4+A6+A10
Sheet.DeleteRows(5, 3); // törli az 5..7. sorokat
// A képlet most az A9-ben van, tartalma: =A4+#REF!+A7
// A4 : a törölt blokk felett van, változatlan
// A6 : az 5..7. sorok között volt, eltűnt -> #REF!
// A10 : a blokk alatt van, felcsúszik -> A7A csendes újracélzás helyett egy feltűnő #REF! hiba előállítása a helyes kompromisszum, és az Excel is ezt teszi. Egy olyan képlet, amely a valós bemenet törlése után egy szomszédos cellára mutatna, elfogadhatónak tűnő számot adna vissza; a #REF! azonban végiggyűrűzik a függő képleteken, és felszínre kerül a legelső teszt során. Ugyanez a konverzió érvényes az oszloptengelyen is.
// Az E1 tartalma: =B1*$C$1
Sheet.DeleteCols(3, 1); // eltávolítja a C oszlopot
// A képlet most a D1-ben van, tartalma: =B1*#REF!
// Az abszolút rögzítés nem védte meg a $C$1-et — maga a cella tűnt elMely hivatkozási formák nem íródnak át?
A szkenner az azonos lapon lévő, kifejezett oszlopbetű-plusz-sorszám formájú A1 hivatkozásokat célozza meg, és érdemes pontosan tisztázni, mi esik ezen kívül. A teljes oszlopra (például A:A) és a teljes sorra (például 1:1) vonatkozó hivatkozásokból hiányzik a két komponens egyike, így a szkenner ezeket úgy hagyja, ahogy le vannak írva. A strukturált táblázathivatkozások (Table1[Amount]) szintén érintetlenül mennek át. Az újraírás szigorúan az A1 jelölésen működik – ha kódja R1C1 stílusban építi fel a képleteket, konvertálja azokat A1 stílusra a szerkezeti módosítás előtt, ahogyan azt a Delphi R1C1 képletjelölésről szóló társcikkében leírtunk.
A lapok közötti és munkafüzet-szintű konstrukciókat külön futamok kezelik, nem pedig a cellaszöveg-szkenner. A szerkesztett lap igazítása után a ShiftSheetGeometry továbbítja ugyanezt a geometriai változást a más lapokon lévő, a szerkesztett lapra hivatkozó képleteknek, a diagramsorozat-tartományoknak, a belső hyperlink-célpontoknak és a definiált neveknek (defined names). A definiált nevek további védelmet kapnak a lap életciklus szintjén: a 2.150-es verzió óta a munkalap törlése a definiált név képletén belüli összes SheetN! minősítőt #REF!-re írja át, a lap átnevezése pedig az új névre írja át a minősítőt, így a nevek soha nem mutatnak olyan lapra, amely már nem létezik. A nevek és a lapok közötti képletek összekapcsolódását a definiált nevekről és a lapok közötti képletekről szóló cikk tárgyalja.
Újraszámítás az eltolás után
A hivatkozáskorrekció a képlet szövegét írja át; az eredményeket nem számítja újra. Szerkezeti módosítás után a képletek mellett tárolt gyorsítótárazott értékek a régi geometriát írják le, így a megbízható sorrend: először végezzen el minden beszúrást és törlést, majd váltson ki egyszeri újraszámítást, és ezután mentsen. Az eltolás futtatása az újraszámítás előtt a függőségi információkat is tisztán tartja – minden újraírt hivatkozás a valós előzményét nevezi meg, ami pontosan az, amire az inkrementális újraszámítási motornak és annak függőségi gráfjának szüksége van a módosult cellák minimális készletének újraszámításához. Ha egy újraírt képlet most már #REF!-et tartalmaz, az újraszámítás azonnal felszínre hozza a hibaértéket ahelyett, hogy elavult számot hagyna a fájlban.
A képlethivatkozás-korrekció az XLSX motor standard viselkedéseként érkezik a Delphi és C++Builder rendszerekhez készült HotXLS Delphi Excel Component szoftverben; a termékoldal tartalmazza a teljes munkalap-szerkesztési API referenciát, beleértve az itt bemutatott beszúrási és törlési metódusokat is.