R1C1-Formelnotation in Delphi mit HotXLS

HotXLS kompiliert Formeln in R1C1-Referenznotation über TXLSCalculator.GetCompiledFormulaR1C1, das Formen wie R2C3 (absolut), R[-1]C[2] (relativer Versatz) und RC (die aktuelle Zelle) akzeptiert, sie anhand von Zeile und Spalte der Zelle, in der die Formel steht, in A1-Notation umwandelt und das Ergebnis demselben Compiler übergibt, der gewöhnliche A1-Formeln verarbeitet. Für Delphi- und C++Builder-Code, der dieselbe Formel über Hunderte von Zeilen erzeugt, beseitigt diese eine Methode eine ganze Klasse von String-Verkettungsfehlern

Diese Fehlerklasse kennt jeder, der schon einmal programmatisch eine Spalte befüllt hat. Man durchläuft die Zeilen und baut für jede Zeile mit Format('D%d*E%d', [Row, Row]) einen A1-Formelstring. Jede Iteration fügt Zeilennummern in Text ein, und die Zeilennummern sind der einzige Teil, der sich ändert. Ein einziger falscher Versatz, ein 0-basierter Schleifenzähler gemischt mit 1-basierten A1-Zeilennummern oder ein um eine Kopfzeile verschobener Datenblock, und jede Formel der Spalte zeigt eine Zeile daneben. Nichts löst eine Exception aus; die Zahlen sind einfach falsch. Die eigentlich gemeinte Formel, „multipliziere die beiden Zellen links von mir“, erwähnt überhaupt keine Zeilennummer, und die R1C1-Notation erlaubt es, sie genau so zu schreiben

Was ist die R1C1-Notation und wann sollte man sie verwenden?

Die R1C1-Notation adressiert Zellen über Zeilen- und Spaltennummer statt über Spaltenbuchstabe plus Zeilennummer und kennzeichnet relative Referenzen als explizite Versätze von der Formelzelle aus. R2C3 ist die absolute Zelle in Zeile 2, Spalte 3, die A1 als $C$2 schreibt. R[-1]C[2] liegt eine Zeile über und zwei Spalten rechts von der Stelle, an der die Formel steht. RC ist die Formelzelle selbst. Die Versätze in eckigen Klammern sind der Kern: Eine relative Referenz in R1C1 liest sich gleich, egal welche Zelle sie beherbergt, während die A1-Darstellung derselben Referenz sich mit jeder Zeile ändert

Die Notation verdient ihren Platz in genau einem Szenario, und das ist ein häufiges: Vorlagenerzeugung, bei der dieselbe relative Formel in jede Zeile eines Datenbereichs gesetzt werden muss. In A1 muss der Formeltext je Zeile neu erzeugt werden. In R1C1 ist der Text eine Konstante. Das liegt auch näher an der internen Denkweise der Tabellenkalkulationsformate: Shared-Formula-Datensätze speichern relative Referenzen als Zeilen- und Spaltenversätze von der Wirtszelle aus, sodass ein A1-String je Zeile etwas ist, das Ihr Code nur synthetisiert, damit der Parser ihn gleich wieder in Versätze zerlegt. R1C1 überspringt diesen Umweg. Für interaktive, von Menschen geschriebene Formeln bleibt A1 die natürliche Wahl, weshalb es überall in HotXLS der Standard bleibt

Wie kompiliert HotXLS eine R1C1-Formel?

HotXLS stellt die Funktion, eingeführt in v2.175.0, auf zwei Ebenen bereit. TXLSCalculator.GetCompiledFormulaR1C1(UncompiledFormula: String; SheetID, CurRow, CurCol: Integer): TXLSCompiledFormula ist diejenige, die der meiste Code aufruft: Sie liefert eine kompilierte, auswertungsbereite Formel, genau wie ihr A1-Gegenstück GetCompiledFormula, aber mit zwei zusätzlichen Parametern, die die 0-basierte Zeile und Spalte der Zelle benennen, zu der die Formel gehört. Darunter erzeugt TXLSFormula.GetCompiledR1C1 den rohen Syntaxbaum, und eine eigenständige Funktion R1C1ToA1(const AFormula: String; CurRow, CurCol: Integer): String führt die eigentliche Notationsumwandlung durch. Die Pipeline ist bewusst einfach: R1C1-Text anhand der Koordinaten der Wirtszelle in äquivalenten A1-Text übersetzen, dann den A1-Text durch die vorhandene Engine kompilieren — dieselbe Engine, die definierte Namen und blattübergreifende Referenzen auflöst und benutzerdefinierte Arbeitsblattfunktionen dispatcht

Weil die Umwandlung vor der Kompilierung stattfindet, verhält sich alles Nachgelagerte so, als hätten Sie die A1-Formel selbst geschrieben. R1C1ToA1('R2C3', 4, 3) liefert unabhängig von der Wirtszelle '$C$2', weil beide Koordinaten absolut sind. R1C1ToA1('SUM(R[-3]C[0]:R[-1]C[0])', 4, 3) — eine Formel in D5, da CurRow = 4 und CurCol = 3 0-basiert sind — liefert 'SUM(D2:D4)': Die Versätze wurden gegen Zeile 5, Spalte D aufgelöst und als einfache relative A1-Referenzen ausgegeben. Bereiche brauchen keine Sonderbehandlung; der Doppelpunkt wird durchgereicht, und jeder Endpunkt wird unabhängig umgewandelt

// Die Formel steht in D5: CurRow = 4, CurCol = 3 (beide 0-basiert)
S := R1C1ToA1('R2C3', 4, 3);
// S = '$C$2'  (absolute Zeile und Spalte)

S := R1C1ToA1('SUM(R[-3]C[0]:R[-1]C[0])', 4, 3);
// S = 'SUM(D2:D4)'  (Versätze gegen D5 aufgelöst)

S := R1C1ToA1('ROUND(R[-1]C[0], 2)', 4, 3);
// S = 'ROUND(D4, 2)'  (das R in ROUND bleibt unangetastet)

Eine Spalte mit einer einzigen relativen Formel befüllen

Der Gewinn zeigt sich in der Schleife. Man vergleiche die A1-Version, die den Formeltext bei jeder Iteration neu erzeugt, mit der R1C1-Version, in der die Formel eine Konstante ist und sich nur die Wirtskoordinaten bewegen. Beide kompilieren über TXLSCalculator und werten mit GetValue aus; der Calculator nimmt beim Erzeugen einen Cell-Provider-Callback entgegen, damit der Evaluator Zellwerte aus Ihrer Datenquelle ziehen kann

// A1-Stil: für jede Zeile ein anderer Formelstring
for Row := 1 to 500 do
begin
  FormulaText := Format('D%d*E%d', [Row + 1, Row + 1]);  // 1-basierte A1-Zeilen
  Compiled := Calc.GetCompiledFormula(FormulaText, 0);
  // ... auswerten, speichern, freigeben ...
end;

const
  AmountFormula = 'RC[-2]*RC[-1]';  // zwei Zellen links, gleiche Zeile
var
  Calc: TXLSCalculator;
  Compiled: TXLSCompiledFormula;
  Value: Variant;
  Row: Integer;
begin
  Calc := TXLSCalculator.Create(nil, Provider.GetValue);
  try
    for Row := 1 to 500 do
    begin
      Compiled := Calc.GetCompiledFormulaR1C1(AmountFormula, 0, Row, 5);
      try
        if Calc.GetValue(0, Compiled, Row, 5, Value, 1) = lxOk then
          StoreResult(Row, 5, Value);
      finally
        Compiled.Free;
      end;
    end;
  finally
    Calc.Free;
  end;
end;

Die R1C1-Schleife enthält im Formeltext überhaupt keine Zeilenarithmetik. 'RC[-2]*RC[-1]' bedeutet in Zeile 2 wie in Zeile 500 gleichermaßen „gleiche Zeile, zwei Spalten links, mal gleiche Zeile, eine Spalte links“, und wenn der Datenblock später um eine Kopfzeile nach unten rückt, ändert sich die Formelkonstante nicht — nur die Schleifengrenzen. Die A1-Version hat zwei Stellen, an denen die + 1-Anpassung schiefgehen kann; die R1C1-Version hat keine

Welche R1C1-Formen akzeptiert der Konverter?

Der Konverter R1C1ToA1 erkennt die für v2.175.0 dokumentierten Formen: R[n]C[m] für relative Versätze in beide Richtungen, RnCm für absolute Zeile und Spalte, R[-n]C[m] mit negativen Versätzen, nacktes RC für die Formelzelle selbst sowie Bereiche wie R1C1:R3C3 oder R[-1]C:R[1]C. Zeilen- und Spaltenteil werden unabhängig geparst, sodass gemischte Formen wie R[1]C3 — relative Zeile, absolute Spalte — ebenfalls funktionieren, und die Buchstaben sind schreibungsunabhängig, sodass r[-1]c[2] genauso kompiliert wie sein Zwilling in Großbuchstaben. Teile in eckigen Klammern werden zu unverankerten (relativen) A1-Koordinaten; nackte Zahlen werden zu mit $ verankerten absoluten Koordinaten

Die interessante Frage ist, wie der Konverter vermeidet, alles andere in der Formel zu verstümmeln, denn R und C sind häufige Buchstaben. Seine Unterscheidungsregel ist tokenbasiert: Ein R gilt nur dann als Beginn einer Referenz, wenn ihm kein anderer Buchstabe vorangeht, und der Kandidat muss dann vollständig parsen — optionaler Zeilenteil, obligatorisches C, optionaler Spaltenteil — oder der Text wird unangetastet wiederhergestellt. Deshalb wandelt ROUND(R[-1]C[0], 2) nur die innere Referenz um: Auf das R in ROUND folgt ein O statt einer Ziffer, einer Klammer oder eines C, sodass das Parsen fehlschlägt und der Funktionsname wörtlich durchgereicht wird. Dieselbe Logik schützt ROW(), und Funktionsnamen, die mit C beginnen, sind nie Kandidaten, da nur R eine Referenz einleitet. Die Release Notes zu v2.175.0 geben an, dass auch String-Literale und Bezeichner übersprungen werden; dennoch lohnt es sich, wenn ein Literal in Anführungszeichen in Ihrer Formel zufällig Text enthält, der genau wie eine R1C1-Referenz geformt ist, die kompilierte Ausgabe einmal zu prüfen, bevor man ihr in der Produktion vertraut

Koordinatenbasen und Verankerungsdetails, die man kennen sollte

In dieser API treffen zwei Konventionen aufeinander, und wer sie auseinanderhält, vermeidet die einzige echte Falle. Die Parameter CurRow und CurCol von GetCompiledFormulaR1C1 sind 0-basiert, entsprechend der Calculator-API, während die Zahlen innerhalb der Notation selbst 1-basiert sind, passend zu dem, was Excel anzeigt: R2C3 ist Zeile 2, Spalte 3, also $C$2, nicht $D$3. Ist Ihr Schleifenzähler bereits 0-basiert, reichen Sie ihn direkt als CurRow durch; das + 1 steckt im Konverter, nicht in Ihrem Code

Ein weiteres Detail zählt, wenn die kompilierte Formel die Zelle überdauert, für die sie kompiliert wurde. Lässt man einen Zeilen- oder Spaltenteil weg — die Formen RC[-1] oder R[2]C —, wird die ausgelassene Koordinate auf die Wirtszelle aufgelöst und im umgewandelten A1-Text als absolute, mit $ verankerte Koordinate ausgegeben. Bei der Auswertung ist das unsichtbar, da der Wert so oder so derselbe ist. Aber die Verankerung relativ gegenüber absolut entscheidet, wie sich Referenzen verschieben, wenn später Zeilen oder Spalten eingefügt oder gelöscht werden, wie im Begleitartikel zur Anpassung von Formelreferenzen beschrieben. Soll eine Koordinate über strukturelle Änderungen hinweg relativ bleiben, schreibt man den Versatz explizit — R[0]C[-1] statt RC[-1] —, damit der Konverter eine unverankerte A1-Referenz ausgibt

Die R1C1-Kompilierung ist Teil der Formel-Engine in der HotXLS Delphi Excel Component, neben dem A1-Compiler, dem Neuberechnungsgraphen und der oben gezeigten Auswertungs-API. Wenn Ihr Code Tabellen erstellt, indem er Formeln in Schleifen Spalten hinunter setzt, ist die Umstellung dieser Schleifen von zusammengesetztem A1 auf eine einzige R1C1-Konstante eines der günstigsten verfügbaren Zuverlässigkeits-Upgrades