Formelbezüge beim Einfügen von Zeilen in HotXLS

HotXLS passt Formelbezüge automatisch an, wenn Sie Zeilen oder Spalten in einem XLSX-Arbeitsblatt einfügen oder löschen. Die Methoden InsertRows, DeleteRows, InsertCols und DeleteCols der Engine schreiben jede verbleibende Formel so um, dass ihre A1-Bezüge — relative, absolute und Bereichsbezüge — nach der strukturellen Bearbeitung weiterhin auf dieselben Daten zeigen, und Bezüge auf einen gelöschten Block zu #REF! werden, was dem Verhalten von Excel entspricht

Der Fehler, der dadurch verhindert wird, ist einer der unauffälligsten bei der Berichtserstellung. Ein Generator schreibt tägliche Zahlen in C2:C9 mit =SUM(C2:C9) darunter, und ein späterer Schritt fügt oben eine Kopfzeile ein. Wenn die Engine nur die Zellwerte verschiebt und den Formeltext unberührt lässt, liest diese SUMME immer noch C2:C9, während die Daten nun in C3:C10 liegen — so lässt die Gesamtsumme den letzten Tag stillschweigend unter den Tisch fallen und zählt eine Kopfzeile doppelt. Nichts löst eine Ausnahme aus, die Datei lässt sich problemlos öffnen und die Zahl ist einfach falsch. Vor Version 2.160 ließ die HotXLS-XLSX-Engine Formeln bei strukturellen Bearbeitungen unberührt; seit 2.160 erfolgt das Umschreiben automatisch und es muss kein Flag gesetzt werden

Was passiert mit Formeln, wenn Sie eine Zeile in Excel einfügen?

Die Regel von Excel lautet, dass Bezüge den Daten folgen, nicht den Adressen. Wenn eine Zeile eingefügt wird, verschiebt sich jeder Bezug, dessen Zeilenindex auf oder unter dem Einfügepunkt liegt, um die Anzahl der eingefügten Zeilen nach unten; Bezüge, die sich vollständig über dem Einfügepunkt befinden, bleiben unberührt. Das Löschen von Zeilen wendet dieselbe Regel in umgekehrter Reihenfolge an: Bezüge unterhalb des gelöschten Blocks rutschen nach oben, und Bezüge in den gelöschten Block selbst werden zu #REF!, da die von ihnen benannten Zellen nicht mehr existieren. Spalten verhalten sich entlang der anderen Achse identisch. Eine Tabellenkalkulationsbibliothek, deren Ausgabe den Kontakt mit Excel-Benutzern überstehen soll, muss diese Mechanismen exakt nachbilden, da Benutzer über ihre Formeln in diesen Kategorien nachdenken, ohne jemals bewusst darüber nachdenken

Der Teil, der Entwickler überrascht, ist, dass sich auch absolute Bezüge verschieben. Die $-Verankerungen in $B$2 steuern, was passiert, wenn eine Formel in eine andere Zelle kopiert oder ausgefüllt wird — bei strukturellen Bearbeitungen bewirken sie nichts. Fügen Sie eine Zeile über Zeile 2 ein, und Excel schreibt $B$2 in $B$3 um, wobei die Dollarzeichen erhalten bleiben, da der Wert, von dem die Formel abhängt, physisch in Zeile 3 verschoben wurde. Eine Engine, die nur relative Bezüge verschieben würde, würde genau die Formeln beschädigen, die Benutzer am bewusstesten verankern. HotXLS verschiebt beide Formen und behält die $-Markierungen im umgeschriebenen Text bei

Wie verschiebt HotXLS Formelbezüge automatisch?

Alle vier strukturellen Bearbeitungsmethoden auf TXLSXWorksheet delegieren an eine einzige Geometrie-Engine: ShiftSheetGeometry(RowFrom, RowDelta, ColFrom, ColDelta). InsertRows(BeforeRow, Count) ruft sie mit einem privaten Zeilen-Delta auf, DeleteRows(StartRow, Count) mit einem negativen, und die Spaltenmethoden tun dasselbe auf der Spaltenachse. Die Routine verschiebt zuerst die Zellen selbst — wobei alle Zellen verworfen werden, die in einen gelöschten Block fallen — und geht dann jede verbleibende Formelzelle durch und leitet ihren Text durch XlsxAdjustFormulaRowColRefs, einen Scanner, der A1-Bezüge findet und deren Zeilen- und Spaltenkomponenten entsprechend der Verschiebung umschreibt. Derselbe Durchgang verschiebt verbundene Bereiche, Hyperlinks, Kommentare, Bilder, Diagramme, bedingte Formatierungen, Datenüberprüfungen und Tabellenbereiche, sodass sich das gesamte Blatt als eine Einheit bewegt

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

Einige Details des Scanners sind wissenswert. XlsxAdjustFormulaRowColRefs erkennt Einzelzellbezüge in allen vier Verankerungsformen (A1, $A1, A$1, $A$1) und zweiecke Bereiche wie A1:B3 und passt jeden Endpunkt unabhängig an. Formeln, deren Text bereits mit # beginnt — ein Fehlermarker aus einer früheren Bearbeitung —, werden übersprungen und nicht erneut gescannt. Und InsertCols fügt eine eigene Excel-Paritäts-Nettigkeit hinzu: Neu eingefügte Spalten erben die Breite seines linken Nachbarn, wat dem Excel-Befehl "Blattspalten einfügen" entspricht

Wann wird ein gelöschter Bezug zu #REF!?

Die Verschiebungsregel für einen einzelnen Zeilen- oder Spaltenindex hat drei Ergebnisse. Ein Index vor dem Bearbeitungspunkt bleibt unverändert. Ein Index auf oder nach dem Bearbeitungspunkt verschiebt sich um das Delta. Und beim Löschen hat ein Index, der in den gelöschten Block fällt, keinen sinnvollen neuen Wert — die Zelle ist weg —, sodass der Scanner den gesamten Bezug in #REF! umschreibt. Bei einem Bereichsbezug werden beide Endpunkte nach derselben Regel verarbeitet, und wenn einer der Endpunkte im gelöschten Block landet, wird der Bezug als #REF! umgeschrieben, anstatt ihn halbgültig zu lassen

Ein unübersehbares #REF! zu erzeugen, anstatt stillschweigend auf ein neues Ziel zu verweisen, ist die richtige Entscheidung, und es ist dieselbe, die Excel trifft. Eine Formel, die nach dem Löschen ihrer tatsächlichen Eingabe auf eine Nachbarzelle verweisen würde, würde eine plausibel aussehende Zahl zurückgeben; #REF! pflanzt sich durch abhängige Formeln fort und taucht im ersten Smoke-Test auf. Dieselbe Konvertierung gilt für die Spaltenachse

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

Welche Bezugsformen werden nicht umgeschrieben?

Der Scanner zielt auf A1-Bezüge auf demselben Arbeitsblatt mit einer expliziten Spaltenbuchstaben-plus-Zeilennummer-Form ab, und es lohnt sich, genau zu bestimmen, was nicht darunter fällt. Spaltenbezüge wie A:A und Zeilenbezüge wie 1:1 vermissen eine der beiden Komponenten, sodass der Scanner sie im Originalzustand belässt. Strukturierte Tabellenbezüge (Table1[Amount]) werden ebenfalls unberührt durchgelassen. Das Umschreiben funktioniert auch ausschließlich in der A1-Notation — wenn Ihr Code Formeln im R1C1-Stil erstellt, konvertieren Sie diese vor einer strukturellen Bearbeitung in A1, wie im Begleitartikel zur R1C1-Formelnotation in Delphi beschrieben

Blattübergreifende Konstrukte und solche auf Arbeitsmappenebene werden in separaten Durchgängen und nicht durch den Zelltextscanner verarbeitet. Nach dem Anpassen des bearbeiteten Blatts überträgt ShiftSheetGeometry dieselbe Geometrieänderung auf Formeln auf anderen Blättern, die auf das bearbeitete Blatt verweisen, auf Diagrammserienbereiche, auf interne Hyperlinkziele und auf definierte Namen. Definierte Namen erhalten zusätzlichen Schutz auf der Lebenszyklusebene des Blatts: Seit Version 2.150 überschreibt das Löschen eines Arbeitsblatts jeden SheetN!-Qualifizierer innerhalb der Formel eines definierten Namens mit #REF!, und das Umbenennen eines Blatts schreibt den Qualifizierer in den neuen Namen um, sodass Namen niemals auf ein Blatt zeigen, das nicht mehr existiert. Wie Namen und blattübergreifende Formeln zusammenpassen, wird im Artikel über definierte Namen und blattübergreifende Formeln behandelt

Nach der Verschiebung neu berechnen

Die Anpassung der Bezüge schreibt den Formeltext um; sie berechnet die Ergebnisse nicht neu. Nach einer strukturellen Bearbeitung beschreiben die neben den Formeln gespeicherten Cache-Werte die alte Geometrie, sodass die zuverlässige Reihenfolge lautet: Zuerst alle Einfügungen und Löschungen durchführen, dann die Neuberechnung einmal auslösen und anschließend speichern. Das Ausführen der Verschiebung vor der Neuberechnung hält auch die Abhängigkeitsinformationen korrekt — jeder umgeschriebene Bezug benennt seinen wahren Vorgänger, was genau das ist, was die inkrementelle Neuberechnungs-Engine und ihr Abhängigkeitsgraph benötigen, um die minimale Anzahl der betroffenen Zellen neu zu berechnen. Wenn eine umgeschriebene Formel nun #REF! enthält, bringt die Neuberechnung den Fehlerwert sofort zum Vorschein, anstatt eine veraltete Zahl in der Datei zu belassen

Die Anpassung von Formelbezügen gehört zum Standardverhalten der XLSX-Engine in der HotXLS Delphi Excel-Komponente für Delphi und C++Builder; die Produktseite enthält die vollständige API-Referenz zur Bearbeitung von Arbeitsblättern, einschließlich der hier gezeigten Einfüge- und Löschmethoden