Formelreferenser vid infogning av rader i HotXLS

HotXLS justerar formelreferenser automatiskt när du infogar eller tar bort rader eller kolumner i ett XLSX-kalkylblad. Motorns metoder InsertRows, DeleteRows, InsertCols och DeleteCols skriver om varje överlevande formel så att dess A1-referenser — relativa, absoluta och områden — fortsätter peka på samma data efter den strukturella redigeringen, och referenser in i ett borttaget block blir #REF!, i linje med Excels beteende

Buggen det här förhindrar är en av de tystaste i rapportgenerering. En generator skriver dagliga siffror i C2:C9 med =SUM(C2:C9) under, sedan infogar ett senare steg en rubrikrad högst upp. Om motorn bara flyttar cellvärden och lämnar formeltexten i fred läser den SUM-formeln fortfarande C2:C9 medan datan nu bor i C3:C10 — så summan tappar tyst den sista dagen och räknar en rubrik dubbelt. Inget undantag utlöses, filen öppnas felfritt, och talet är helt enkelt fel. Före version 2.160 lämnade HotXLS XLSX-motor formler orörda under strukturella redigeringar; sedan 2.160 sker omskrivningen automatiskt och det finns ingen flagga att sätta

Vad händer med formler när du infogar en rad i Excel?

Excels regel är att referenser följer data, inte adresser. När en rad infogas flyttas varje referens vars radindex ligger på eller under infogningspunkten ned med antalet infogade rader; referenser helt ovanför infogningspunkten är orörda. Att ta bort rader kör samma regel baklänges: referenser under det borttagna blocket glider upp, och referenser in i själva det borttagna blocket blir #REF! eftersom cellerna de namngav inte längre finns. Kolumner beter sig identiskt längs den andra axeln. Ett kalkylbladsbibliotek som vill att dess utdata ska överleva kontakt med Excel-användare måste återge den här mekaniken exakt, eftersom användarna resonerar om sina formler i de här termerna utan att någonsin tänka på det

Det som överraskar utvecklare är att absoluta referenser också flyttar sig. $-ankarna i $B$2 styr vad som händer när en formel kopieras eller fylls till en annan cell — de gör ingenting vid strukturella redigeringar. Infoga en rad ovanför rad 2 och Excel skriver om $B$2 till $B$3, med dollartecknen intakta, eftersom värdet formeln beror på fysiskt flyttade till rad 3. En motor som bara förskjöt relativa referenser skulle korrumpera precis de formler människor ankrar mest medvetet. HotXLS förskjuter båda formerna och bevarar $-markörerna i den omskrivna texten

Hur förskjuter HotXLS formelreferenser automatiskt?

Alla fyra metoderna för strukturell redigering på TXLSXWorksheet delegerar till en enda geometrimotor: ShiftSheetGeometry(RowFrom, RowDelta, ColFrom, ColDelta). InsertRows(BeforeRow, Count) anropar den med ett positivt raddelta, DeleteRows(StartRow, Count) med ett negativt, och kolumnmetoderna gör detsamma på kolumnaxeln. Rutinen flyttar först cellerna själva — och släpper varje cell som hamnar inuti ett borttaget block — och går sedan igenom varje överlevande formelcell och skickar dess text genom XlsxAdjustFormulaRowColRefs, en skanner som hittar referenser i A1-stil och skriver om deras rad- och kolumnkomponenter mot förskjutningen. Samma genomgång flyttar sammanfogade områden, hyperlänkar, kommentarer, bilder, diagram, villkorsstyrda format, datavalideringar och tabellområden, så att hela bladet rör sig som en enhet

// Layout före redigeringen:
//   C2..C9  dagliga siffror
//   C10     =SUM(C2:C9)
Sheet.InsertRows(2, 1);      // en tom rad före rad 2
// Layout efter anropet:
//   C3..C10 dagliga siffror
//   C11     =SUM(C3:C10)    -- området flyttade med datan

Några detaljer om skannern är värda att känna till. XlsxAdjustFormulaRowColRefs känner igen encellsreferenser i alla fyra ankarformerna (A1, $A1, A$1, $A$1) och tvåhörnsområden såsom A1:B3, och justerar varje ändpunkt oberoende. Formler vars text redan börjar med # — en felmarkör från en tidigare redigering — hoppas över istället för att skannas om. Och InsertCols lägger till en egen finess i paritet med Excel: nyinfogade kolumner ärver bredden från sin vänstra granne, vilket är vad Excels kommando Infoga kolumner i blad gör

När blir en borttagen referens #REF!?

Förskjutningsregeln för ett enskilt rad- eller kolumnindex har tre utfall. Ett index före redigeringspunkten är oförändrat. Ett index på eller efter redigeringspunkten flyttas med deltat. Och vid borttagning har ett index som hamnar inuti det borttagna blocket inget meningsfullt nytt värde — cellen är borta — så skannern skriver om hela referensen som #REF!. För en områdesreferens körs båda ändpunkterna genom samma regel, och om endera ändpunkten landar inuti det borttagna blocket skrivs referensen om som #REF! snarare än att lämnas halvt giltig

// A12 innehåller  =A4+A6+A10
Sheet.DeleteRows(5, 3);      // ta bort raderna 5..7
// Formeln, nu i A9, lyder  =A4+#REF!+A7
//   A4  : ovanför det borttagna blocket, oförändrad
//   A6  : inuti raderna 5..7, borta   -> #REF!
//   A10 : under blocket, glider upp   -> A7

Att framställa ett högljutt #REF! istället för att tyst rikta om är rätt avvägning, och det är den Excel gör. En formel som pekar på en granncell efter att dess verkliga indata tagits bort skulle returnera ett rimligt utseende tal; #REF! propagerar genom beroende formler och dyker upp i det första röktestet. Samma omvandling gäller på kolumnaxeln

// E1 innehåller  =B1*$C$1
Sheet.DeleteCols(3, 1);      // ta bort kolumn C
// Formeln, nu i D1, lyder  =B1*#REF!
// Det absoluta ankaret skyddade inte $C$1 -- själva cellen är borta

Vilka referensformer skrivs inte om?

Skannern siktar på A1-referenser inom samma blad med en uttrycklig form av kolumnbokstav plus radnummer, och det är värt att vara precis om vad som faller utanför det. Helkolumnsreferenser såsom A:A och helradsreferenser såsom 1:1 saknar en av de två komponenterna, så skannern lämnar dem som de står. Strukturerade tabellreferenser (Table1[Amount]) släpps likaså igenom orörda. Omskrivningen arbetar också strikt på A1-notation — om din kod bygger formler i R1C1-stil, konvertera dem till A1 före en strukturell redigering, som beskrivs i följeslagsartikeln om R1C1-formelnotation i Delphi

Konstruktioner över blad och på arbetsboksnivå hanteras av separata genomgångar snarare än av skannern för celltext. Efter att ha justerat det redigerade bladet propagerar ShiftSheetGeometry samma geometriförändring till formler på andra blad som refererar till det redigerade bladet, till diagramseriers områden, till interna hyperlänkmål och till definierade namn. Definierade namn får ytterligare skydd på bladets livscykelnivå: sedan version 2.150 skriver borttagning av ett kalkylblad om varje SheetN!-kvalificerare inuti ett definierat namns formel till #REF!, och namnbyte på ett blad skriver om kvalificeraren till det nya namnet, så att namn aldrig pekar på ett blad som inte längre finns. Hur namn och formler över blad hänger ihop täcks i artikeln om definierade namn och formler över blad

Räkna om efter förskjutningen

Referensjustering skriver om formeltext; den beräknar inte om resultat. Efter en strukturell redigering beskriver de cachelagrade värden som lagras vid sidan av formlerna den gamla geometrin, så den pålitliga sekvensen är: gör alla infogningar och borttagningar först, utlös sedan omräkning en gång, och spara sedan. Att köra förskjutningen före omräkningen håller också beroendeinformationen ärlig — varje omskriven referens namnger sin sanna föregångare, vilket är precis vad den inkrementella omräkningsmotorn och dess beroendegraf behöver för att beräkna om den minsta mängden berörda celler. Om en omskriven formel nu innehåller #REF! lyfter omräkningen fram felvärdet omedelbart istället för att lämna ett inaktuellt tal i filen

Justering av formelreferenser levereras som standardbeteende hos XLSX-motorn i HotXLS Delphi Excel Component för Delphi och C++Builder; produktsidan bär den fullständiga API-referensen för kalkylbladsredigering, inklusive de metoder för infogning och borttagning som visas här