HotXLS automatski prilagođava reference formula kada umećete ili brišete retke ili stupce u XLSX radnom listu. Metode mehanizma InsertRows, DeleteRows, InsertCols i DeleteCols prepisuju svaku preostalu formulu tako da njezine A1 reference — relativne, apsolutne i rasponske — nastavljaju pokazivati na iste podatke nakon strukturalne izmjene, a reference na obrisani blok postaju #REF!, što odgovara ponašanju Excela
Pogreška koju ovo sprječava jedna je od najtiših u generiranju izvješća. Generator zapisuje dnevne brojke u C2:C9 sa =SUM(C2:C9) ispod, a zatim u kasnijem koraku umeće redak zaglavlja na vrh. Ako mehanizam premješta samo vrijednosti ćelija, a tekst formule ostavlja netaknutim, ta SUM formula i dalje čita C2:C9 dok se podaci sada nalaze u C3:C10 — tako da ukupni iznos tiho ispušta zadnji dan i dvaput broji zaglavlje. Ništa ne izaziva iznimku (exception), datoteka se normalno otvara, a broj je jednostavno pogrešan. Prije verzije 2.160 HotXLS XLSX mehanizam je ostavljao formule netaknutima tijekom strukturalnih izmjena; od verzije 2.160 prepisivanje je automatski i nema nikakve zastavice (flag) koju trebate postaviti
Što se događa s formulama kada umetnete redak u Excelu?
Excelovo pravilo je da reference prate podatke, a ne adrese. Kada se umetne redak, svaka referenca čiji se indeks retka nalazi na ili ispod točke umetanja pomiče se prema dolje za broj umetnutih redaka; reference koje su u potpunosti iznad točke umetanja ostaju netaknute. Brisanje redaka pokreće isto pravilo unatrag: reference ispod obrisanog bloka klize prema gore, a reference na sam obrisani blok postaju #REF! jer ćelije koje su imenovale više ne postoje. Stupci se ponašaju identično duž druge osi. Knjižnica proračunskih tablica koja želi da njezin izlaz preživi kontakt s korisnicima Excela mora točno reproducirati ove mehanizme, jer korisnici razmišljaju o svojim formulama u tim okvirima, a da o tome uopće ne razmišljaju
Dio koji iznenađuje programere jest da se i apsolutne reference također pomiču. Sidra $ u $B$2 kontroliraju što se događa kada se formula kopira ili popunjava u drugu ćeliju — ona ne čine ništa tijekom strukturalnih izmjena. Umetnite redak iznad retka 2 i Excel prepisuje $B$2 u $B$3, s netaknutim znakovima dolara, jer se vrijednost o kojoj formula ovisi fizički pomaknula u redak 3. Mehanizam koji bi pomicao samo relativne reference pokvario bi upravo one formule koje ljudi najnamjernije sidre. HotXLS pomiče oba oblika i čuva oznake $ u prepisanom tekstu
Kako HotXLS automatski pomiče reference formula?
Sve četiri metode strukturalnog uređivanja na TXLSXWorksheet delegiraju posao jednom mehanizmu geometrije: ShiftSheetGeometry(RowFrom, RowDelta, ColFrom, ColDelta). InsertRows(BeforeRow, Count) poziva ga s pozitivnim pomakom (delta) retka, DeleteRows(StartRow, Count) s negativnim, a metode stupaca čine isto na osi stupaca. Rutina najprije premješta same ćelije — odbacujući svaku ćeliju koja padne unutar obrisanog bloka — a zatim prolazi kroz svaku preostalu ćeliju formule i propušta njezin tekst kroz XlsxAdjustFormulaRowColRefs, skener koji pronalazi reference u stilu A1 i prepisuje njihove komponente retka i stupca u skladu s pomakom. Isti prolaz premješta spojene raspone (merged ranges), hiperveze, komentare, slike, grafikone, uvjetne formate, validacije podataka i raspone tablica, tako da se cijeli list pomiče kao jedna cjelina
// 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 dataVrijedi znati nekoliko pojedinosti o skeneru. XlsxAdjustFormulaRowColRefs prepoznaje reference pojedinačnih ćelija u sva četiri oblika sidrenja (A1, $A1, A$1, $A$1) i raspone s dva kuta kao što je A1:B3, prilagođavajući svaku krajnju točku neovisno. Formule čiji tekst već počinje sa znakom # — oznaka pogreške iz prethodne izmjene — preskaču se umjesto da se ponovno skeniraju. A InsertCols dodaje i jednu svoju finu pojedinost za paritet s Excelom: novoumetnuti stupci nasljeđuju širinu svog lijevog susjeda, što je točno ono što čini Excelova naredba Insert Sheet Columns command does
Kada obrisana referenca postaje #REF!?
Pravilo pomaka za pojedinačni indeks retka ili stupca ima tri ishoda. Indeks prije točke uređivanja ostaje nepromijenjen. Indeks na ili iza točke uređivanja pomiče se za iznos pomaka. A kod brisanja, indeks koji padne unutar obrisanog bloka nema smislenu novu vrijednost — ćelije više nema — pa skener prepisuje cijelu referencu kao #REF!. Za referencu raspona, obje se krajnje točke propuštaju kroz isto pravilo, pa ako bilo koja krajnja točka sleti unutar obrisanog bloka, referenca se prepisuje kao #REF! umjesto da ostane polovično valjana
// 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 -> A7Proizvodnja glasnog #REF! umjesto tihog preusmjeravanja je ispravan kompromis, i to je onaj koji Excel radi. Formula koja bi pokazivala na susjednu ćeliju nakon što je njezin stvarni ulaz obrisan vratila bi broj koji izgleda uvjerljivo; #REF! se širi kroz ovisne formule i izlazi na vidjelo u prvom smoke testu. Isti prijelaz vrijedi i za os stupaca
// 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 goneKoji oblici referenci se ne prepisuju?
Skener cilja reference A1 na istom listu s eksplicitnim oblikom slova stupca i broja retka, pa je vrijedno biti precizan o tome što ne ulazi u to. Referencama cijelog stupca kao što je A:A i cijelog retka kao što je 1:1 nedostaje jedna od dviju komponenti, pa ih skener ostavlja onako kako su napisane. Strukturirane reference tablica (Table1[Amount]) se također propuštaju netaknute. Prepisivanje također radi isključivo na A1 notaciji — ako vaš kôd gradi formule u stilu R1C1, pretvorite ih u A1 prije strukturalnog uređivanja, kao što je opisano u pratećem članku o R1C1 notaciji formula u Delphiju
Konstrukcije između više listova i na razini cijele radne knjige obrađuju se u zasebnim prolazima, a ne skenerom teksta ćelije. Nakon prilagodbe uređenog lista, ShiftSheetGeometry prenosi istu promjenu geometrije na formule na drugim listovima koje upućuju na uređeni list, na raspone serija grafikona, na interne ciljeve hiperveza i na definirane nazive. Definirani nazivi dobivaju dodatnu zaštitu na razini životnog ciklusa lista: od verzije 2.150, brisanje radnog lista prepisuje svaki kvalifikator SheetN! unutar formule definiranog naziva u #REF!, a preimenovanje lista prepisuje kvalifikator u novi naziv, tako da nazivi nikada ne pokazuju na list koji više ne postoji. Kako se nazivi i formule na više listova spajaju zajedno pokriveno je u članku o definiranim nazivima i formulama na više listova
Ponovni izračun nakon pomaka
Prilagodba referenci prepisuje tekst formule; ona ne preračunava rezultate. Nakon strukturalnog uređivanja, predmemorirane (cached) vrijednosti pohranjene uz formule opisuju staru geometriju, pa je pouzdan redoslijed: najprije izvršite sva umetanja i brisanja, zatim pokrenite ponovni izračun jednom, a potom spremite. Pokretanje pomaka prije ponovnog izračuna također održava informacije o ovisnosti točnima — svaka prepisana referenca imenuje svog stvarnog prethodnika, što je točno ono što mehanizam inkrementalnog ponovnog izračuna i njegov grafikon ovisnosti trebaju za preračunavanje minimalnog skupa pogođenih ćelija. Ako prepisana formula sada sadrži #REF!, ponovni izračun odmah izvodi vrijednost pogreške na površinu umjesto da ostavi zastarjeli broj u datoteci
Prilagodba referenci formula isporučuje se kao standardno ponašanje XLSX mehanizma u HotXLS Delphi Excel komponenti za Delphi i C++Builder; stranica proizvoda sadrži cjelovitu referencu API-ja za uređivanje radnih listova, uključujući metode umetanja i brisanja koje su ovdje prikazane