Kaavaviittaukset lisättäessä rivejä HotXLS-komponentissa

HotXLS säää kaavaviittaukset automaattisesti, kun lisäät tai poistat rivejä tai sarakkeita XLSX-taulukossa. Komponentin metodit InsertRows, DeleteRows, InsertCols ja DeleteCols kirjoittavat jokaisen jäljelle jääneen kaavan uudelleen siten, että sen A1-viittaukset — suhteelliset, absoluuttiset ja alueviittaukset — osoittavat edelleen samaan tietoon rakenne-editoinnin jälkeen, ja poistettuun lohkoon kohdistuvat viittaukset muuttuvat muotoon #REF! Excelin toimintaa vastaavasti

Tämän ehkäisemä virhe on yksi hiljaisimmista raporttien luonnissa. Sovellus kirjoittaa päivittäiset luvut soluun C2:C9 kaavan =SUM(C2:C9) ollessa sen alla, minkä jälkeen myöhempi vaihe lisää otsikkorivin yläreunaan. Jos moottori siirtää vain solujen arvot ja jättää kaavatekstit rauhaan, SUM-kaava lukee edelleen aluetta C2:C9, vaikka tiedot sijaitsevat nyt soluissa C3:C10 — jolloin loppusumma pudottaa hiljaa viimeisen päivän pois ja laskee otsikon kahdesti. Mikään ei aiheuta poikkeusta, tiedosto aukeaa ongelmitta ja luku on yksinkertaisesti väärä. Ennen versiota 2.160 HotXLS XLSX -moottori jätti kaavat ennalleen rakenteellisten muokkausten aikana; versiosta 2.160 lähtien uudelleenkirjoitus on automaattinen eikä mitään asetuksia tarvitse säätää

Mitä kaavoille tapahtuu, kun Exceliin lisätään rivi?

Excelin sääntö on, että viittaukset seuraavat tietoja, eivät osoitteita. Kun rivi lisätään, jokainen viittaus, jonka rivi-indeksi on lisäyskohdassa tai sen alapuolella, siirtyy alaspäin lisättyjen rivien lukumäärän verran; viittauksiin, jotka ovat kokonaan lisäyskohdan yläpuolella, ei kosketa. Rivien poistaminen noudattaa samaa sääntöä käänteisesti: poistetun lohkon alapuolella olevat viittaukset liukuvat ylöspäin ja itse poistettuun lohkoon kohdistuvat viittaukset muuttuvat muotoon #REF!, koska niiden nimeämiä soluja ei enää ole. Sarakkeet toimivat täsmälleen samalla tavalla toisella akselilla. Taulukkolaskentakirjaston, joka haluaa tulosteensa toimivan Excel-käyttäjien kanssa, on toistettava tämä logiikka tarkasti, sillä käyttäjät ajattelevat kaavojaan näillä ehdoilla edes huomaamatta sitä

Kehittäjät yllättää usein se, että myös absoluuttiset viittaukset siirtyvät. Dollarimerkit $ kaavassa $B$2 hallitsevat sitä, mitä tapahtuu, kun kaava kopioidaan tai täytetään toiseen soluun — ne eivät vaikuta rakenteellisten muokkausten aikana. Jos lisäät rivin rivin 2 yläpuolelle, Excel muuttaa viittauksen $B$2 muotoon $B$3 dollarimerkit säilyttäen, koska arvo, josta kaava riippuu, siirtyi fyysisesti riville 3. Moottori, joka siirtäisi vain suhteellisia viittauksia, korruptoisi juuri ne kaavat, jotka käyttäjät ankkuroivat tarkoituksella. HotXLS siirtää molempia tyyppejä ja säilyttää $-merkit uudelleenkirjoitetussa tekstissä

Miten HotXLS siirtää kaavaviittauksia automaattisesti?

Kaikki neljä TXLSXWorksheet-luokan rakenne-editointimenetelmää delegoivat tehtävän yhdelle geometriamoottorille: ShiftSheetGeometry(RowFrom, RowDelta, ColFrom, ColDelta). InsertRows(BeforeRow, Count) kutsuu sitä positiivisella rivierolla (delta), DeleteRows(StartRow, Count) negatiivisella ja sarakemetodit toimivat samalla tavoin sarakeakselilla. Rutiini siirtää ensin itse solut — pudottaen pois kaikki poistetun lohkon sisälle jäävät solut — ja käy sitten läpi jokaisen jäljelle jääneen kaavasolun ja välittää sen tekstin funktiolle XlsxAdjustFormulaRowColRefs. Tämä skanneri etsii A1-tyyliset viittaukset ja kirjoittaa niiden rivi- ja sarakekomponentit uudelleen siirron mukaisesti. Samalla kertaa siirretään myös yhdistetyt alueet, hyperlinkit, kommentit, kuvat, kaaviot, ehdolliset muotoilut, tietojen validoinnit ja taulukkoalueet, joten koko taulukko siirtyy yhtenä yksikkönä

// Asettelu ennen muokkausta:
//   C2..C9  päivittäiset luvut
//   C10     =SUM(C2:C9)
Sheet.InsertRows(2, 1);      // yksi tyhjä rivi ennen riviä 2
// Asettelu kutsun jälkeen:
//   C3..C10 päivittäiset luvut
//   C11     =SUM(C3:C10)    -- alue siirtyi tietojen mukana

Muutamat skannerin yksityiskohdat on hyvä tietää. XlsxAdjustFormulaRowColRefs tunnistaa yksittäisten solujen viittaukset kaikissa neljässä ankkurimuodossa (A1, $A1, A$1, $A$1) ja kaksikulmaiset alueet kuten A1:B3, säätäen kumpaakin päätepistettä itsenäisesti. Kaavat, joiden teksti alkaa jo merkillä # — virhemerkintä aiemmasta muokkauksesta — ohitetaan uudelleenskannauksen sijaan. Ja sarakkeiden lisäys InsertCols sisältää oman pienen Exceliä vastaavan mukavuuden: uudet sarakkeet perivät vasemman naapurinsa leveyden, mikä vastaa Excelin Insert Sheet Columns -toimintoa

Milloin poistetusta viittauksesta tulee #REF!?

Yksittäisen rivi- tai sarakeindeksin siirtosäännöllä on kolme lopputulosta. Ennen muokkauskohtaa oleva indeksi säilyy muuttumattomana. Muokkauskohdassa tai sen jälkeen oleva indeksi siirtyy eron (delta) verran. Ja poistotapauksessa indeksi, joka jää poistetun lohkon sisälle, menettää merkityksellisen uuden arvonsa — solu on poissa — joten skanneri kirjoittaa koko viittauksen uudelleen muotoon #REF!. Alueviittauksessa molemmat päätepisteet ajetaan saman säännön läpi, ja jos jompikumpi päätepiste päätyy poistetun lohkon sisälle, viittaus kirjoitetaan kokonaan muotoon #REF! sen sijaan, että se jätettäisiin puoliksi toimivaksi

// A12 sisältää  =A4+A6+A10
Sheet.DeleteRows(5, 3);      // poista rivit 5..7
// Kaava, nyt solussa A9, on  =A4+#REF!+A7
//   A4  : poistetun lohkon yläpuolella, muuttumaton
//   A6  : rivien 5..7 sisällä, poissa      -> #REF!
//   A10 : lohkon alapuolella, liukuu ylös  -> A7

Selkeän #REF!-virheen tuottaminen hiljaisen uudelleenkohdistamisen sijaan on oikea valinta, ja se on sama, jonka Excel tekee. Kaava, joka osoittaisi naapurisoluun sen todellisen syötteen poistamisen jälkeen, palauttaisi uskottavalta vaikuttavan luvun; #REF! etenee riippuvaisten kaavojen läpi ja nousee esiin heti ensimmäisessä testissä. Sama muunnos pätee sarakeakselilla

// E1 sisältää  =B1*$C$1
Sheet.DeleteCols(3, 1);      // poista sarake C
// Kaava, nyt solussa D1, on  =B1*#REF!
// Absoluuttinen ankkuri ei suojannut solua $C$1 -- itse solu on poissa

Mitä viittausmuotoja ei kirjoiteta uudelleen?

Skanneri kohdistuu saman taulukon A1-viittauksiin, joilla on selkeä sarakekirjain-plus-rivinumero-rakenne, ja on syytä olla tarkka siitä, mikä jää tämän ulkopuolelle. Kokonaisen sarakkeen viittaukset kuten A:A ja kokonaisen rivin viittaukset kuten 1:1 puuttuvat toisen näistä kahdesta komponentista, joten skanneri jättää ne ennalleen. Strukturoidut taulukkoviittaukset (Table1[Amount]) ohitetaan myös sellaisenaan. Uudelleenkirjoitus toimii myös tiukasti vain A1-esitystavalla — jos koodisi rakentaa kaavoja R1C1-tyylillä, muunna ne A1-muotoon ennen rakenteellista muokkausta, kuten kuvataan aiheeseen liittyvässä artikkelissa R1C1-kaavaesityksestä Delphissä

Taulukoiden väliset ja työkirjatason rakenteet käsitellään erillisillä ajoilla solutekstiskannerin sijaan. Muokatun taulukon säätämisen jälkeen ShiftSheetGeometry välittää saman geometrian muutoksen muiden taulukoiden kaavoihin, jotka viittaavat muokattuun taulukkoon, sekä kaavioiden aluesarjoihin, sisäisiin hyperlinkkikohteisiin ja nimettyihin alueisiin (defined names). Nimetyt alueet saavat lisäsuojaa taulukon elinkaaritasolla: versiosta 2.150 lähtien laskentataulukon poistaminen muuttaa jokaisen nimetyn alueen kaavan sisällä olevan SheetN!-määritteen muotoon #REF!, ja taulukon nimeäminen uudelleen muuttaa määritteen uudeksi nimeksi, jotta nimet eivät koskaan osoita taulukkoon, jota ei enää ole. Nimien ja taulukoiden välisten kaavojen yhteistoimintaa käsitellään artikkelissa nimettyistä alueista ja taulukoiden välisistä kaavoista

Laske arvot uudelleen siirron jälkeen

Viittausten säätäminen kirjoittaa uudelleen kaavan tekstin; se ei laske tuloksia uudelleen. Rakenteellisen muokkauksen jälkeen kaavojen ohella tallennetut välimuistiin tallennetut arvot kuvaavat vanhaa geometriaa, joten varma järjestys on: suorita ensin kaikki lisäykset ja poistot, käynnistä sitten uudelleenlaskenta kerran ja tallenna lopuksi. Siirron ajaminen ennen uudelleenlaskentaa pitää myös riippuvuustiedot oikeina — jokainen uudelleenkirjoitettu viittaus nimeää todellisen edeltäjänsä, mikä on juuri se, mitä inkrementaalinen uudelleenlaskentamoottori ja sen riippuvuusgraafi tarvitsevat laskeakseen uudelleen mahdollisimman pienen joukon vaikuttavia soluja. Jos uudelleenkirjoitettu kaava sisältää nyt virheen #REF!, uudelleenlaskenta tuo virhearvon heti esiin sen sijaan, että tiedostoon jätettäisiin vanhentunut luku

Kaavaviittauksen säätäminen toimitetaan standardina XLSX-moottorin toimintana HotXLS Delphi Excel Component -tuotteessa Delphille ja C++Builderille; tuotesivulla on täydellinen taulukkoeditoinnin rajapintaohje, mukaan lukien tässä esitetyt lisäys- ja poistometodit