Reference formula pri umetanju redova u HotXLS-u

HotXLS automatski prilagođava reference formula kada umećete ili brišete redove ili kolone u XLSX radnom listu. Metode mehanizma InsertRows, DeleteRows, InsertCols i DeleteCols prepisuju svaku preostalu formulu tako da njene A1 reference — relativne, apsolutne i opsezi — nastave da ukazuju na iste podatke nakon strukturne izmene, a reference na obrisani blok postaju #REF!, što odgovara ponašanju Excel-a

Greška koju ovo sprečava je jedna od najtiših u generisanju izveštaja. Generator upisuje dnevne podatke u opseg C2:C9 sa =SUM(C2:C9) ispod njih, a zatim u kasnijem koraku umeće red zaglavlja na vrhu. Ako mehanizam samo pomera vrednosti ćelija a tekst formule ostavlja netaknutim, ta SUM formula i dalje čita opseg C2:C9 dok se podaci sada nalaze u C3:C10 — tako da ukupan zbir tiho izostavlja poslednji dan i dvostruko računa zaglavlje. Ništa ne izaziva izuzetak, fajl se otvara bez problema, a broj je jednostavno pogrešan. Pre verzije 2.160, HotXLS XLSX mehanizam je ostavljao formule netaknute tokom strukturnih izmena; od verzije 2.160 prepisivanje je automatsko i nema nikakvih opcija koje treba podešavati

Šta se dešava sa formulama kada umetnete red u Excel-u?

Pravilo u Excel-u je da reference prate podatke, a ne adrese. Kada se umetne red, svaka referenca čiji se indeks reda nalazi na mestu umetanja ili ispod njega pomera se nadole za broj umetnutih redova; reference koje su potpuno iznad mesta umetanja ostaju netaknute. Brisanje redova pokreće isto pravilo u obrnutom smeru: reference ispod obrisanog bloka klize nagore, a reference na sam obrisani blok postaju #REF! jer ćelije koje su imenovale više ne postoje. Kolone se ponašaju identično duž druge ose. Biblioteka za rad sa tabelama koja želi da njen izlaz preživi kontakt sa korisnicima Excel-a mora tačno da reprodukuje ove mehanizme, jer korisnici razmišljaju o svojim formulama na taj način a da o tome uopšte ne razmišljaju

Deo koji iznenađuje programere je to što se i apsolutne reference pomeraju. Sidra $ u oznaci $B$2 kontrolišu šta se dešava kada se formula kopira ili popunjava u drugu ćeliju — ona ne rade ništa tokom strukturnih izmena. Umetnite red iznad reda 2 i Excel prepisuje $B$2 u $B$3, sa netaknutim znacima dolara, jer se vrednost od koje formula zavisi fizički pomerila u red 3. Mehanizam koji bi pomerao samo relativne reference pokvario bi upravo one formule koje ljudi najnamernije fiksiraju. HotXLS pomera oba oblika i čuva oznake $ u prepisanom tekstu

Kako HotXLS automatski pomera reference formula?

Sve četiri metode strukturnog uređivanja klase TXLSXWorksheet delegiraju zadatak jednom mehanizmu geometrije: ShiftSheetGeometry(RowFrom, RowDelta, ColFrom, ColDelta). Metoda InsertRows(BeforeRow, Count) ga poziva sa pozitivnom razlikom (delta) redova, DeleteRows(StartRow, Count) sa negativnom, a metode za kolone rade isto na osi kolona. Procedura prvo premešta same ćelije — odbacujući svaku ćeliju koja upadne u obrisani blok — a zatim prolazi kroz svaku preostalu ćeliju sa formulom i prosleđuje njen tekst kroz XlsxAdjustFormulaRowColRefs, skener koji pronalazi reference u stilu A1 i prepisuje njihove komponente redova i kolona u odnosu na pomeraj. Isti prolaz premešta spojene opsege, hiperveze, komentare, slike, grafikone, uslovna formatiranja, validacije podataka i opsege tabela, tako da se ceo list pomera kao jedna celina

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

Vredi znati nekoliko detalja o skeneru. Funkcija XlsxAdjustFormulaRowColRefs prepoznaje reference pojedinačnih ćelija u sva četiri oblika sidrenja (A1, $A1, A$1, $A$1) i opsege sa dva ugla kao što je A1:B3, prilagođavajući svaku krajnju tačku nezavisno. Formule čiji tekst već počinje znakom # — oznakom greške iz ranije izmene — se preskaču umesto da se ponovo skeniraju. I metoda InsertCols dodaje jednu finu pojedinost specifičnu za Excel: novoumetnute kolone nasleđuju širinu svog levog suseda, što je upravo ono što radi Excel-ova komanda Insert Sheet Columns command doest

Kada obrisana referenca postaje #REF!?

Pravilo pomeranja za pojedinačni indeks reda ili kolone ima tri ishoda. Indeks pre mesta izmene ostaje nepromenjen. Indeks na mestu izmene ili nakon njega pomera se za deltu. A prilikom brisanja, indeks koji upadne u obrisani blok nema nikakvu novu smislenu vrednost — ćelija je nestala — pa skener prepisuje čitavu referencu kao #REF!. Za referencu opsega, obe krajnje tačke prolaze kroz isto pravilo, i ako bilo koja krajnja tačka završi unutar obrisanog bloka, referenca se prepisuje kao #REF! umesto da ostane polovično ispravna

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

Prikazivanje jasne greške #REF! umesto tihoh preusmeravanja je ispravan kompromis, i to je onaj koji Excel pravi. Formula koja ukazuje na susednu ćeliju nakon što je njen stvarni ulaz obrisan vratila bi broj koji izgleda uverljivo; greška #REF! se propagira kroz zavisne formule i izlazi na videlo na prvom smoke testu. Isti prenos važi i na osi kolona

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

Koji oblici referenci se ne prepisuju?

Skener cilja reference A1 na istom listu sa eksplicitnim oblikom slova-kolone-plus-broja-reda, i vredi biti precizan o tome šta ostaje izvan toga. Referencama celih kolona kao što je A:A i celih redova kao što je 1:1 nedostaje jedna od ove dve komponente, pa ih skener ostavlja onako kako su napisane. Strukturirane reference tabela (Table1[Amount]) se takođe propuštaju netaknute. Prepisivanje takođe radi isključivo na A1 notaciji — ako vaš kod gradi formule u stilu R1C1, konvertujte ih u A1 pre strukturnog uređivanja, kao što je opisano u pratećem članku o R1C1 notaciji formula u Delphi-ju

Ponovo izračunajte nakon pomeranja

Prilagođavanje referenci prepisuje tekst formule; ono ne vrši ponovno izračunavanje rezultata. Nakon strukturne izmene, keširane vrednosti sačuvane uz formule opisuju staru geometriju, tako da je pouzdan redosled: prvo izvršite sva umetanja i brisanja, zatim pokrenite ponovni proračun jednom, a onda sačuvajte fajl. Pokretanje pomeranja pre ponovnog proračuna takođe održava informacije o zavisnostima ispravnim — svaka prepisana referenca imenuje svog stvarnog prethodnika, što je upravo ono što je mehanizmu inkrementalnog ponovnog proračuna i njegovom grafu zavisnosti potrebno da ponovo izračuna minimalni skup pogođenih ćelija. Ako prepisana formula sada sadrži #REF!, ponovni proračun odmah izbacuje vrednost greške na površinu umesto da ostavi zastareli broj u fajlu

Formula reference adjustment ships as standard behavior of the XLSX engine in the HotXLS Delphi Excel Component for Delphi and C++Builder; the product page carries the full worksheet-editing API reference, including the insert and delete methods shown here