Formulereferenties bij het invoegen van rijen in HotXLS

HotXLS past formulereferenties automatisch aan wanneer u rijen of kolommen invoegt of verwijdert in een XLSX-werkblad. De methoden InsertRows, DeleteRows, InsertCols en DeleteCols van de engine herschrijven elke overblijvende formule, zodat de A1-referenties — relatief, absoluut en bereik — na de structurele bewerking naar dezelfde gegevens blijven verwijzen, en verwijzingen naar een verwijderd blok veranderen in #REF!, wat overeenkomt met het gedrag van Excel

De bug die hiermee wordt voorkomen, is een van de meest onopvallende bij het genereren van rapporten. Een generator schrijft dagelijkse cijfers in C2:C9 met =SUM(C2:C9) daaronder, en een latere stap voegt bovenaan een koprij in. Als de engine alleen celwaarden verplaatst en de formuletekst ongemoeid laat, leest die SOM nog steeds C2:C9 terwijl de gegevens zich nu in C3:C10 bevinden — waardoor het totaal in stilte de laatste dag weglaat en een koptekst dubbel telt. Niets veroorzaakt een uitzondering, het bestand opent prima, en het getal is simpelweg onjuist. Vóór versie 2.160 liet de XLSX-engine van HotXLS formules ongemoeid tijdens structurele bewerkingen; sinds 2.160 is het herschrijven automatisch en hoeft er geen vlag te worden ingesteld

Wat gebeurt er met formules wanneer u een rij invoegt in Excel?

De regel van Excel is dat verwijzingen de gegevens volgen, niet de adressen. Wanneer een rij wordt ingevoegd, verschuift elke referentie waarvan de rij-index zich op of onder het invoegpunt bevindt omlaag met het aantal ingevoegde rijen; verwijzingen die zich volledig boven het invoegpunt bevinden, blijven ongewijzigd. Het verwijderen van rijen voert dezelfde regel in omgekeerde volgorde uit: verwijzingen onder het verwijderde blok schuiven omhoog, en verwijzingen naar het verwijderde blok zelf worden #REF! omdat de cellen waarnaar ze verwezen niet meer bestaan. Kolommen gedragen zich identiek langs de andere as. Een spreadsheetbibliotheek die wil dat haar uitvoer de interactie met Excel-gebruikers overleeft, moet deze mechanica exact reproduceren, omdat gebruikers in deze termen over hun formules redeneren zonder er ooit bij stil te staan

Het deel dat ontwikkelaars verrast, is dat absolute verwijzingen ook verschuiven. De $-ankers in $B$2 bepalen wat er gebeurt wanneer een formule wordt gekopieerd of doorgevoerd naar een andere cel — ze doen niets tijdens structurele bewerkingen. Voeg een rij in boven rij 2 en Excel herschrijft $B$2 naar $B$3, met behoud van de dollartekens, omdat de waarde waarvan de formule afhankelijk is fysiek naar rij 3 is verplaatst. Een engine die alleen relatieve verwijzingen zou verschuiven, zou juist de formules beschadigen die mensen het meest bewust verankeren. HotXLS verschuift beide vormen en behoudt de $-markeringen in de herschreven tekst

Hoe verschuift HotXLS formulereferenties automatisch?

Alle vier de methoden voor structurele bewerkingen op TXLSXWorksheet delegeren naar een enkele geometrie-engine: ShiftSheetGeometry(RowFrom, RowDelta, ColFrom, ColDelta). InsertRows(BeforeRow, Count) roept deze aan met een positieve rij-delta, DeleteRows(StartRow, Count) met een negatieve rij-delta, en de kolommethoden doen hetzelfde op de kolomas. De routine verplaatst eerst de cellen zelf — waarbij elke cel die binnen een verwijderd blok valt wordt weggegooid — en doorloopt vervolgens elke overblijvende formulecel en stuurt de tekst ervan door naar XlsxAdjustFormulaRowColRefs, een scanner die A1-verwijzingen zoekt en hun rij- en kolomcomponenten herschrijft op basis van de verschuiving. Dezelfde bewerking verplaatst samengevoegde bereiken, hyperlinks, opmerkingen, afbeeldingen, grafieken, voorwaardelijke opmaken, gegevensvalidaties en tabelbereiken, zodat het hele werkblad als één geheel verschuift

// Lay-out voor de bewerking:
//   C2..C9  dagelijkse cijfers
//   C10     =SUM(C2:C9)
Sheet.InsertRows(2, 1);      // één lege rij voor rij 2
// Lay-out na de aanroep:
//   C3..C10 dagelijkse cijfers
//   C11     =SUM(C3:C10)    -- het bereik is meeverhuisd met de gegevens

Een paar details van de scanner zijn de moeite waard om te weten. XlsxAdjustFormulaRowColRefs herkent afzonderlijke celverwijzingen in alle vier de ankervormen (A1, $A1, A$1, $A$1) en bereiken met twee hoeken zoals A1:B3, waarbij elk eindpunt onafhankelijk wordt aangepast. Formules waarvan de tekst al begint met # — een foutmarkering van een eerdere bewerking — worden overgeslagen in plaats van opnieuw gescand. En InsertCols voegt een eigen Excel-gelijke verfijning toe: nieuw ingevoegde kolommen erven de breedte van hun linkerbuurman, wat overeenkomt met de actie van het commando 'Bladkolommen invoegen' in Excel

Wanneer wordt een verwijderde referentie #REF!?

De verschuivingsregel voor een enkele rij- of kolomspecificatie heeft drie uitkomsten. Een index vóór het bewerkingspunt blijft ongewijzigd. Een index op of na het bewerkingspunt verschuift met de delta. En bij verwijdering heeft een index die binnen het verwijderde blok valt geen zinvolle nieuwe waarde meer — de cel is verdwenen — dus de scanner herschrijft de gehele verwijzing als #REF!. Voor een bereikverwijzing worden beide eindpunten via dezelfde regel verwerkt, en als een van de eindpunten in het verwijderde blok terechtkomt, wordt de verwijzing herschreven als #REF! in plaats van deze half geldig achter te laten

// A12 bevat  =A4+A6+A10
Sheet.DeleteRows(5, 3);      // verwijder rijen 5..7
// De formule, nu in A9, luidt  =A4+#REF!+A7
//   A4  : boven het verwijderde blok, ongewijzigd
//   A6  : binnen rijen 5..7, verdwenen      -> #REF!
//   A10 : onder het blok, schuift omhoog  -> A7

Het genereren van een duidelijke #REF! in plaats van geruisloos opnieuw te richten is de juiste keuze, en het is de keuze die Excel maakt. Een formule die naar een naburige cel verwijst nadat de werkelijke invoer is verwijderd, zou een aannemelijk ogend getal retourneren; #REF! plant zich voort door afhankelijke formules en komt bij de eerste rooktest aan de oppervlakte. Dezelfde conversie is van toepassing op de kolomas

// E1 bevat  =B1*$C$1
Sheet.DeleteCols(3, 1);      // verwijder kolom C
// De formule, nu in D1, luidt  =B1*#REF!
// Het absolute anker beschermde $C$1 niet — de cel zelf is verdwenen

Welke referentievormen worden niet herschreven?

De scanner richt zich op A1-verwijzingen op hetzelfde werkblad met een expliciete kolomletter-plus-rijnummer-vorm, en het is goed om precies te zijn over wat daarbuiten valt. Verwijzingen naar hele kolommen zoals A:A en verwijzingen naar hele rijen zoals 1:1 missen een van de twee componenten, dus de scanner laat ze ongewijzigd. Gestructureerde tabelverwijzingen (Table1[Amount]) worden eveneens ongewijzigd doorgegeven. Bovendien werkt de herschrijving strikt op A1-notatie — als uw code formules opbouwt in R1C1-stijl, converteer ze dan naar A1 vóór een structurele bewerking, zoals beschreven in het bijbehorende artikel over R1C1-formulenotatie in Delphi

Constructies tussen verschillende bladen en op werkboekniveau worden afgehandeld door afzonderlijke stappen in plaats van door de celtekstscanner. Na het aanpassen van het bewerkte blad geeft ShiftSheetGeometry dezelfde geometrieverandering door aan formules op andere bladen die naar het bewerkte blad verwijzen, aan grafiekgegevensreeksen, aan interne hyperlinkdoelen en aan gedefinieerde namen. Gedefinieerde namen krijgen verdere bescherming op het niveau van de levenscyclus van het blad: sinds versie 2.150 herschrijft het verwijderen van een werkblad elke SheetN!-kwalificatie binnen de formule van een gedefinieerde naam naar #REF!, en het hernoemen van een blad herschrijft de kwalificatie naar de nieuwe naam, zodat namen nooit naar een blad verwijzen dat niet meer bestaat. Hoe namen en formules over bladen heen samenwerken, wordt behandeld in het artikel over gedefinieerde namen en formules over bladen heen

Herberekenen na de verschuiving

Het aanpassen van verwijzingen herschrijft de formule-tekst; het berekent de resultaten niet opnieuw. Na een structurele bewerking beschrijven de gecachte waarden die samen met formules zijn opgeslagen de oude geometrie, dus de betrouwbare volgorde is: voer eerst alle invoegingen en verwijderingen uit, activeer vervolgens eenmalig de herberekening en sla het bestand daarna op. Het uitvoeren van de verschuiving vóór de herberekening houdt ook de afhankelijkheidsinformatie correct — elke herschreven verwijzing noemt haar werkelijke precedent, wat precies is wat de incrementele herberekenings-engine en haar afhankelijkheidsgraaf nodig hebben om het minimale aantal beïnvloede cellen opnieuw te berekenen. Als een herschreven formule nu #REF! bevat, brengt de herberekening de foutwaarde onmiddellijk aan het licht in plaats van een verouderd getal in het bestand achter te laten

Het aanpassen van formulereferenties wordt geleverd als standaardgedrag van de XLSX-engine in het HotXLS Delphi Excel Component voor Delphi en C++Builder; de productpagina bevat de volledige API-referentie voor werkbladbeveliging, inclusief de hier getoonde methoden voor invoegen en verwijderen