Formelreferenzen beim Einfügen von Zeilen in HotXLS

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

Der Fehler, den das verhindert, gehört zu den leisesten in der Berichtserzeugung. Ein Generator schreibt Tageswerte in C2:C9 mit =SUM(C2:C9) darunter, dann fügt ein späterer Schritt oben eine Kopfzeile ein. Wenn die Engine nur Zellwerte verschiebt und den Formeltext unangetastet lässt, liest dieses SUM weiterhin C2:C9, während die Daten jetzt in C3:C10 liegen — die Summe lässt also stillschweigend den letzten Tag weg und zählt eine Kopfzeile doppelt. Nichts löst eine Exception aus, die Datei öffnet sich einwandfrei, und die Zahl ist einfach falsch. Vor Version 2.160 ließ die HotXLS-XLSX-Engine Formeln bei strukturellen Bearbeitungen unberührt; seit 2.160 ist das Umschreiben automatisch, und es gibt kein Flag zu setzen

Was passiert mit Formeln, wenn man in Excel eine Zeile einfügt?

Excels Regel lautet, dass Referenzen den Daten folgen, nicht den Adressen. Wird eine Zeile eingefügt, rückt jede Referenz, deren Zeilenindex auf oder unter dem Einfügepunkt liegt, um die Anzahl der eingefügten Zeilen nach unten; Referenzen vollständig oberhalb des Einfügepunkts bleiben unberührt. Das Löschen von Zeilen wendet dieselbe Regel umgekehrt an: Referenzen unterhalb des gelöschten Blocks rutschen nach oben, und Referenzen in den gelöschten Block selbst werden zu #REF!, weil die Zellen, die sie benannten, nicht mehr existieren. Spalten verhalten sich entlang der anderen Achse identisch. Eine Tabellenkalkulationsbibliothek, deren Ausgabe den Kontakt mit Excel-Nutzern überstehen soll, muss diese Mechanik exakt nachbilden, weil Nutzer in genau diesen Begriffen über ihre Formeln nachdenken, ohne je darüber nachzudenken

Was Entwickler überrascht, ist, dass absolute Referenzen ebenfalls wandern. Die $-Anker in $B$2 steuern, was passiert, wenn eine Formel in eine andere Zelle kopiert oder ausgefüllt wird — bei strukturellen Bearbeitungen tun sie nichts. Fügt man eine Zeile oberhalb von Zeile 2 ein, schreibt Excel $B$2 zu $B$3 um, Dollarzeichen intakt, weil der Wert, von dem die Formel abhängt, physisch in Zeile 3 gewandert ist. Eine Engine, die nur relative Referenzen verschöbe, würde genau die Formeln beschädigen, die Menschen am bewusstesten verankern. HotXLS verschiebt beide Formen und bewahrt die $-Markierungen im umgeschriebenen Text

Wie verschiebt HotXLS Formelreferenzen automatisch?

Alle vier Methoden für strukturelle Bearbeitungen auf TXLSXWorksheet delegieren an eine einzige Geometrie-Engine: ShiftSheetGeometry(RowFrom, RowDelta, ColFrom, ColDelta). InsertRows(BeforeRow, Count) ruft sie mit positivem Zeilen-Delta auf, DeleteRows(StartRow, Count) mit negativem, und die Spaltenmethoden tun dasselbe auf der Spaltenachse. Die Routine verlagert zuerst die Zellen selbst — und verwirft jede Zelle, die in einen gelöschten Block fällt — und durchläuft dann jede verbleibende Formelzelle und schickt ihren Text durch XlsxAdjustFormulaRowColRefs, einen Scanner, der Referenzen im A1-Stil findet und ihre Zeilen- und Spaltenkomponenten gegen die Verschiebung umschreibt. Derselbe Durchlauf verlagert verbundene Bereiche, Hyperlinks, Kommentare, Bilder, Diagramme, bedingte Formate, Datenüberprüfungen und Tabellenbereiche, sodass das ganze Blatt als eine Einheit wandert

// Layout vor der Bearbeitung:
//   C2..C9  Tageswerte
//   C10     =SUM(C2:C9)
Sheet.InsertRows(2, 1);      // eine Leerzeile vor Zeile 2
// Layout nach dem Aufruf:
//   C3..C10 Tageswerte
//   C11     =SUM(C3:C10)    -- der Bereich ist mit den Daten gewandert

Ein paar Details des Scanners sind wissenswert. XlsxAdjustFormulaRowColRefs erkennt Einzelzellreferenzen in allen vier Ankerformen (A1, $A1, A$1, $A$1) sowie Zwei-Ecken-Bereiche wie A1:B3 und passt jeden Endpunkt unabhängig an. Formeln, deren Text bereits mit # beginnt — eine Fehlermarkierung aus einer früheren Bearbeitung — werden übersprungen statt erneut gescannt. Und InsertCols bringt eine eigene Excel-Parität mit: Neu eingefügte Spalten erben die Breite ihres linken Nachbarn, was Excels Befehl „Blattspalten einfügen" ebenfalls tut

Wann wird eine gelöschte Referenz zu #REF!?

Die Verschieberegel für einen einzelnen Zeilen- oder Spaltenindex hat drei Ausgänge. Ein Index vor dem Bearbeitungspunkt bleibt unverändert. Ein Index auf oder hinter dem Bearbeitungspunkt bewegt 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 die gesamte Referenz zu #REF! umschreibt. Bei einer Bereichsreferenz werden beide Endpunkte durch dieselbe Regel geschickt, und landet einer der beiden im gelöschten Block, wird die Referenz zu #REF! umgeschrieben, statt halb gültig zu bleiben

// A12 enthält  =A4+A6+A10
Sheet.DeleteRows(5, 3);      // Zeilen 5..7 löschen
// Die Formel, jetzt in A9, lautet  =A4+#REF!+A7
//   A4  : über dem gelöschten Block, unverändert
//   A6  : innerhalb der Zeilen 5..7, weg   -> #REF!
//   A10 : unter dem Block, rutscht hoch    -> A7

Ein lautes #REF! zu erzeugen statt stillschweigend umzuzielen ist der richtige Kompromiss, und es ist der, den Excel eingeht. Eine Formel, die nach dem Löschen ihrer echten Eingabe auf eine Nachbarzelle zeigt, würde eine plausibel aussehende Zahl liefern; #REF! pflanzt sich durch abhängige Formeln fort und taucht im ersten Smoke-Test auf. Dieselbe Umwandlung gilt auf der Spaltenachse

// E1 enthält  =B1*$C$1
Sheet.DeleteCols(3, 1);      // Spalte C entfernen
// Die Formel, jetzt in D1, lautet  =B1*#REF!
// Der absolute Anker hat $C$1 nicht geschützt -- die Zelle selbst ist weg

Welche Referenzformen werden nicht umgeschrieben?

Der Scanner zielt auf A1-Referenzen innerhalb desselben Blatts mit expliziter Form aus Spaltenbuchstabe plus Zeilennummer, und es lohnt sich, genau zu benennen, was außerhalb davon liegt. Ganze-Spalten-Referenzen wie A:A und Ganze-Zeilen-Referenzen wie 1:1 fehlt eine der beiden Komponenten, sodass der Scanner sie so lässt, wie sie geschrieben sind. Strukturierte Tabellenreferenzen (Table1[Amount]) werden ebenfalls unangetastet durchgereicht. Das Umschreiben arbeitet außerdem strikt auf A1-Notation — wenn Ihr Code Formeln im R1C1-Stil aufbaut, sollten Sie sie vor einer strukturellen Bearbeitung nach A1 konvertieren, wie in dem Begleitartikel zur R1C1-Formelnotation in Delphi beschrieben

Blattübergreifende Konstrukte und solche auf Arbeitsmappenebene werden von eigenen Durchläufen behandelt, nicht vom Zelltext-Scanner. Nach dem Anpassen des bearbeiteten Blatts überträgt ShiftSheetGeometry dieselbe Geometrieänderung auf Formeln anderer Blätter, die das bearbeitete Blatt referenzieren, auf Diagrammreihenbereiche, auf interne Hyperlink-Ziele und auf definierte Namen. Definierte Namen erhalten auf Ebene des Blatt-Lebenszyklus weiteren Schutz: Seit Version 2.150 schreibt das Löschen eines Arbeitsblatts jeden SheetN!-Qualifizierer in der Formel eines definierten Namens zu #REF! um, und das Umbenennen eines Blatts schreibt den Qualifizierer auf den neuen Namen um, sodass Namen nie auf ein Blatt zeigen, das nicht mehr existiert. Wie Namen und blattübergreifende Formeln zusammenspielen, wird in dem Artikel zu definierten Namen und blattübergreifenden Formeln behandelt

Nach der Verschiebung neu berechnen

Die Referenzanpassung schreibt Formeltext um; sie berechnet keine Ergebnisse neu. Nach einer strukturellen Bearbeitung beschreiben die neben den Formeln gespeicherten zwischengespeicherten Werte die alte Geometrie, daher lautet die zuverlässige Reihenfolge: zuerst alle Einfüge- und Löschvorgänge ausführen, dann einmal die Neuberechnung anstoßen, dann speichern. Die Verschiebung vor der Neuberechnung auszuführen hält auch die Abhängigkeitsinformationen ehrlich — jede umgeschriebene Referenz benennt ihren wahren Vorgänger, was genau das ist, was die inkrementelle Neuberechnungs-Engine und ihr Abhängigkeitsgraph brauchen, um die minimale Menge betroffener Zellen neu zu berechnen. Enthält eine umgeschriebene Formel jetzt #REF!, bringt die Neuberechnung den Fehlerwert sofort ans Licht, statt eine veraltete Zahl in der Datei zu belassen

Die Anpassung von Formelreferenzen ist Standardverhalten der XLSX-Engine in der HotXLS Delphi Excel Component für Delphi und C++Builder; die Produktseite enthält die vollständige API-Referenz zur Arbeitsblattbearbeitung, einschließlich der hier gezeigten Einfüge- und Löschmethoden