Formelreferencer ved indsættelse af rækker i HotXLS

HotXLS justerer formelreferencer automatisk, når du indsætter eller sletter rækker eller kolonner i et XLSX-regneark. Motorens metoder InsertRows, DeleteRows, InsertCols og DeleteCols omskriver enhver overlevende formel, så dens A1-referencer — relative, absolutte og intervaller — fortsat peger på de samme data efter den strukturelle ændring, og referencer til en slettet blok bliver til #REF!, hvilket matcher Excels adfærd

Fejlen, som dette forhindrer, er en af de mest lydløse i rapportgenerering. En generator skriver daglige tal i C2:C9 med =SUM(C2:C9) nedenunder, og et senere trin indsætter en overskriftsrække øverst. Hvis motoren kun flytter celleværdier og efterlader formelteksten uændret, læser denne SUM stadig C2:C9, mert dataene nu lever i C3:C10 — så totalen lydløst taber den sidste dag og dobbelttæller en overskrift. Intet udløser en undtagelse, filen åbnes fint, og tallet er simpelthen forkert. Før version 2.160 efterlod HotXLS XLSX-motoren formler urørt under strukturelle ændringer; siden 2.160 sker omskrivningen automatisk, og der er intet flag at indstille

Hvorfor sker der med formler, når du indsætter en række i Excel?

Excels regel er, at referencer følger data, ikke adresser. Når en række indsættes, flyttes enhver reference, hvis rækkeindeks befinder sig ved eller under indsættelsespunktet, ned med antallet af indsatte rækker; referencer helt over indsættelsespunktet berøres ikke. Sletning af rækker kører den samme regel i omvendt rækkefølge: Referencer under den slettede blok glider op, og referencer til selve den slettede blok bliver til #REF!, fordi de celler, de navngav, ikke længere eksisterer. Kolonner opfører sig identisk langs den anden akse. Et regnearksbibliotek, der ønsker, at dets output skal overleve kontakt med Excel-brugere, skal genskabe denne mekanik nøjagtigt, fordi brugere ræsonnerer om deres formler i disse termer uden nogensinde at tænke over det

Den del, der overrækker udviklere, er, at absolutte referencer også flytter sig. Dollar-ankrene ($) i $B$2 styrer, hvad der sker, når en formel kopieres eller udfyldes til en anden celle — de gør intet under strukturelle ændringer. Indsæt en række over række 2, og Excel omskriver $B$2 to $B$3 med dollartegnene intakte, fordi den værdi, formlen afhænger af, fysisk flyttede til række 3. En motor, der kun flyttede relative referencer, ville korrumpere netop de formler, som folk forankrer mest bevidst. HotXLS forskyder begge former og bevarer dollar-markørerne ($) i den omskrevne tekst

Hvordan forskyder HotXLS formelreferencer automatisk?

Alle fire metoder til strukturel redigering på TXLSXWorksheet delegerer til en enkelt geometrimotor: ShiftSheetGeometry(RowFrom, RowDelta, ColFrom, ColDelta). Metoden InsertRows(BeforeRow, Count) kalder den med et positivt række-delta, DeleteRows(StartRow, Count) med et negativt delta, og kolonnemetoderne gør det samme på kolonneaksen. Rutinen flytter først selve cellerne — idet enhver celle, der falder inden for en slettet blok, kasseres — og gennemgår derefter enhver overlevende formelcelle og sender dens tekst gennem XlsxAdjustFormulaRowColRefs, en scanner der finder A1-stil-referencer og omskriver deres række- og kolonnekomponenter i forhold til forskydningen. Den samme gennemgang flytter sammenføjede områder, hyperlinks, kommentarer, billeder, diagrammer, betingede formateringer, datavalideringer og tabelområder, så hele arket flyttes som én enhed

// Layout før redigeringen:
//   C2..C9  daily figures
//   C10     =SUM(C2:C9)
Sheet.InsertRows(2, 1);      // én tom række før række 2
// Layout efter kaldet:
//   C3..C10 daily figures
//   C11     =SUM(C3:C10)    -- området flyttede med dataene

Få detaljer om scanneren er værd at kende. XlsxAdjustFormulaRowColRefs genkender enkeltcelle-referencer i alle fire ankerformer (A1, $A1, A$1, $A$1) og intervaller med to hjørner som f.eks. A1:B3, og justerer hvert slutpunkt uafhængigt. Formler, hvis tekst allerede begynder med # — en fejlmarkør fra en tidligere redigering — springes over frem for at blive scannet igen. Og InsertCols tilføjer en finesser for at bevare paritet med Excel: Nyindsatte kolonner arver bredden af deres venstre nabo, hvilket er det, Excels kommando Indsæt arkkolonner gør

Hvornår bliver en slettet reference til #REF!?

Forskydningsreglen for et enkelt række- eller kolonneindeks har tre resultater. Et indeks før redigeringspunktet forbliver uændret. Et indeks ved eller efter redigeringspunktet flyttes med deltaet. Og ved sletning har et indeks, der falder inden for den slettede blok, ingen meningsfuld ny værdi — cellen er væk — så scanneren omskriver hele referencen som #REF!. For en intervalreference køres begge slutpunkter gennem den samme regel, og hvis et af slutpunkterne lander inden for den slettede blok, omskrives referencen som #REF! frem for at blive efterladt halvgyldig

// A12 holds  =A4+A6+A10
Sheet.DeleteRows(5, 3);      // delete rows 5..7
// The formula, now in A9, reads  =A4+#REF!+A7
//   A4  : over den slettede blok, uændret
//   A6  : inside rows 5..7, gone      -> #REF!
//   A10 : under blokken, glider op  -> A7

At producere en tydelig #REF! i stedet for lydløst at omdirigere er det rigtige valg, og det er det, Excel gør. En formel, der peger på en nabocelle, efter at dens egentlige input blev slettet, ville returnere et tal, der ser sandsynligt ud; #REF! forplanter sig gennem afhængige formler og kommer op til overfladen i den første smoketest. Den samme konvertering gælder på kolonneaksen

// E1 holds  =B1*$C$1
Sheet.DeleteCols(3, 1);      // remove column C
// The formula, now in D1, reads  =B1*#REF!
// Det absolutte anker beskyttede ikke $C$1 -- selve cellen er væk

Hvilke referenceformer omskrives ikke?

Scanneren er rettet mod A1-referencer på samme ark med en eksplicit form af kolonnebogstav plus rækkenummer, og det er værd at være præcis med, hvad der falder uden for dette. Kolonnereferencer som f.eks. A:A og rækkereferencer som 1:1 mangler en af de to komponenter, så scanneren efterlader dem, som de er skrevet. Strukturerede tabelreferencer (Table1[Amount]) sendes ligeledes urørt igennem. Omskrivningen fungerer også strengt på A1-notation — hvis din kode bygger formler i R1C1-stil, skal du konvertere dem til A1 før en strukturel redigering, som beskrevet i den tilhørende artikel om R1C1-formelnotation i Delphi

Konstruktioner på tværs af ark og på arbejdsbogsniveau håndteres af separate gennemløb frem for af celletekst-scanneren. Efter justeringen af det redigerede ark propagerer ShiftSheetGeometry den samme geometriændring til formler på andre ark, der refererer til det redigerede ark, til dataserieområder i diagrammer, til interne hyperlink-mål og til definerede navne. Definerede navne får yderligere beskyttelse på arkets livscyklusniveau: Siden version 2.150 omskriver sletning af et regneark hver SheetN!-kvalifikator inde i et defineret navns formel til #REF!, og omdøbning af et ark omskriver kvalifikatoren til det nye navn, så navne aldrig peger på et ark, der ikke længere findes. Hvordan navne og formler på tværs af ark spiller sammen, er dækket i artiklen om definerede navne og formler på tværs af ark

Genberegn efter forskydningen

Referencejustering omskriver formeltekst; den genberegner ikke resultater. Efter en strukturel redigering beskriver de cachede værdier, der er gemt sammen med formlerne, den gamle geometri, så den pålidelige rækkefølge er: Udfør alle indsættelser og sletninger først, udløs derefter genberegning én gang, og gem derefter. At køre forskydningen før genberegningen holder også afhængighedsinformationen troværdig — enhver omskrevet reference navngiver sin sande forgænger, hvilket er præcis, hvad den inkrementelle genberegningsmotor og dens afhængighedsgraf har brug for til at genberegne det mindste sæt af berørte celler. Hvis en omskreven formel nu indeholder #REF!, bringer genberegningen fejlstrømmen frem til overfladen med det samme i stedet for at efterlade et forældet tal i filen

Formelreferencejustering leveres som standardadfærd for XLSX-motoren i HotXLS Delphi Excel Component til Delphi og C++Builder; produktsiden indeholder den fulde reference for API til redigering af regneark, inklusive de indsættelses- og sletningsmetoder, der vises her