Notacja formuł R1C1 w Delphi z HotXLS

HotXLS kompiluje formuły zapisane w notacji referencji R1C1 przez TXLSCalculator.GetCompiledFormulaR1C1, które przyjmuje formy takie jak R2C3 (bezwzględna), R[-1]C[2] (przesunięcie względne) i RC (bieżąca komórka), przelicza je na notację A1 wobec wiersza i kolumny komórki, w której formuła mieszka, i podaje wynik temu samemu kompilatorowi, który obsługuje zwykłe formuły A1. Dla kodu Delphi i C++Buildera generującego tę samą formułę w setkach wierszy ta jedna metoda usuwa całą klasę błędów sklejania ciągów

Ta klasa błędów jest znajoma każdemu, kto programowo wypełniał kolumnę. Iterujesz po wierszach i dla każdego budujesz ciąg formuły A1 przez Format('D%d*E%d', [Row, Row]). Każda iteracja wplata numery wierszy w tekst, a numery wierszy są jedyną częścią, która się zmienia. Pomyl przesunięcie raz, zmieszaj licznik pętli liczony od zera z numerami wierszy A1 liczonymi od jedynki albo przesuń blok danych o wiersz nagłówka, a każda formuła w kolumnie wskazuje o wiersz obok. Nic się nie podnosi; liczby są po prostu błędne. Formuła, którą naprawdę miałeś na myśli, czyli "pomnóż dwie komórki po mojej lewej", w ogóle nie wspomina numeru wiersza, a notacja R1C1 pozwala napisać ją właśnie tak

Czym jest notacja R1C1 i kiedy jej używać?

Notacja R1C1 adresuje komórki numerem wiersza i kolumny zamiast literą kolumny plus numerem wiersza i oznacza referencje względne jako jawne przesunięcia od komórki formuły. R2C3 to bezwzględna komórka w wierszu 2, kolumnie 3, którą A1 zapisuje jako $C$2. R[-1]C[2] to jeden wiersz w górę i dwie kolumny w prawo od miejsca, w którym siedzi formuła. RC to sama komórka formuły. Przesunięcia w nawiasach są tu sednem: referencja względna w R1C1 czyta się tak samo bez względu na to, która komórka ją gości, podczas gdy zapis A1 tej samej referencji zmienia się z każdym wierszem

Notacja zarabia na siebie dokładnie w jednym scenariuszu i to częstym: przy generowaniu z szablonu, gdzie ta sama względna formuła musi zostać zasadzona w każdym wierszu obszaru danych. W A1 musisz renderować tekst formuły ponownie dla każdego wiersza. W R1C1 tekst jest stałą. To też jest bliższe temu, jak formaty plików arkuszowych myślą wewnętrznie: rekordy formuł współdzielonych przechowują referencje względne jako przesunięcia wiersza i kolumny od komórki goszczącej, więc ciąg A1 na wiersz jest czymś, co twój kod syntetyzuje tylko po to, by parser rozłożył go z powrotem na przesunięcia. R1C1 pomija tę podróż w obie strony. Dla interaktywnych formuł pisanych przez ludzi A1 pozostaje naturalnym wyborem i dlatego w HotXLS wszędzie zostaje domyślną

Jak HotXLS kompiluje formułę R1C1?

HotXLS wystawia tę funkcję na dwóch poziomach, dodanych w v2.175.0. TXLSCalculator.GetCompiledFormulaR1C1(UncompiledFormula: String; SheetID, CurRow, CurCol: Integer): TXLSCompiledFormula jest tym, które woła większość kodu: zwraca skompilowaną formułę gotową do wyliczenia, dokładnie jak jego siostrzane GetCompiledFormula dla A1, ale z dwoma dodatkowymi parametrami nazywającymi liczone od zera wiersz i kolumnę komórki, do której należy formuła. Pod spodem TXLSFormula.GetCompiledR1C1 produkuje surowe drzewo składniowe, a samodzielna funkcja R1C1ToA1(const AFormula: String; CurRow, CurCol: Integer): String wykonuje właściwą konwersję notacji. Potok jest celowo prosty: przetłumacz tekst R1C1 na równoważny tekst A1 przy użyciu współrzędnych komórki goszczącej, potem skompiluj tekst A1 istniejącym silnikiem — tym samym silnikiem, który rozwiązuje nazwy zdefiniowane i referencje międzyarkuszowe oraz rozsyła własne funkcje arkuszowe

Ponieważ konwersja dzieje się przed kompilacją, wszystko niżej zachowuje się tak, jakbyś sam napisał formułę A1. R1C1ToA1('R2C3', 4, 3) zwraca '$C$2' bez względu na komórkę goszczącą, bo obie współrzędne są bezwzględne. R1C1ToA1('SUM(R[-3]C[0]:R[-1]C[0])', 4, 3) — formuła mieszkająca w D5, skoro CurRow = 4 i CurCol = 3 liczą się od zera — zwraca 'SUM(D2:D4)': przesunięcia rozwiązano wobec wiersza 5, kolumny D, i wyemitowano jako zwykłe względne referencje A1. Zakresy nie potrzebują specjalnego traktowania; dwukropek przechodzi dalej, a każdy koniec konwertuje się niezależnie

// Formuła mieszka w D5: CurRow = 4, CurCol = 3 (oba liczone od zera)
S := R1C1ToA1('R2C3', 4, 3);
// S = '$C$2'  (wiersz i kolumna bezwzględne)

S := R1C1ToA1('SUM(R[-3]C[0]:R[-1]C[0])', 4, 3);
// S = 'SUM(D2:D4)'  (przesunięcia rozwiązane wobec D5)

S := R1C1ToA1('ROUND(R[-1]C[0], 2)', 4, 3);
// S = 'ROUND(D4, 2)'  (R w ROUND zostaje nietknięte)

Wypełnianie kolumny jedną formułą względną

Zysk pokazuje się w pętli. Porównaj wersję A1, która renderuje tekst formuły od nowa przy każdej iteracji, z wersją R1C1, gdzie formuła jest stałą i ruszają się tylko współrzędne goszczące. Obie kompilują się przez TXLSCalculator i wyliczają przez GetValue; kalkulator bierze przy tworzeniu wywołanie zwrotne dostawcy komórek, żeby ewaluator mógł pobierać wartości komórek z twojego źródła danych

// styl A1: inny ciąg formuły dla każdego wiersza
for Row := 1 to 500 do
begin
  FormulaText := Format('D%d*E%d', [Row + 1, Row + 1]);  // wiersze A1 liczone od jedynki
  Compiled := Calc.GetCompiledFormula(FormulaText, 0);
  // ... wylicz, zapisz, zwolnij ...
end;

const
  AmountFormula = 'RC[-2]*RC[-1]';  // dwie komórki w lewo, ten sam wiersz
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;

Pętla R1C1 nie ma w tekście formuły żadnej arytmetyki wierszy. 'RC[-2]*RC[-1]' znaczy "ten sam wiersz, dwie kolumny w lewo, razy ten sam wiersz, jedna kolumna w lewo" w wierszu 2 i w wierszu 500 jednakowo, a jeśli blok danych przesunie się później w dół o wiersz nagłówka, stała formuły się nie zmienia — zmieniają się tylko granice pętli. Wersja A1 ma dwa miejsca, w których można pomylić poprawkę + 1; wersja R1C1 nie ma ani jednego

Które formy R1C1 przyjmuje konwerter?

Konwerter R1C1ToA1 rozpoznaje formy udokumentowane dla v2.175.0: R[n]C[m] dla przesunięć względnych w dowolnym kierunku, RnCm dla bezwzględnych wiersza i kolumny, R[-n]C[m] z przesunięciami ujemnymi, gołe RC dla samej komórki formuły oraz zakresy takie jak R1C1:R3C3 czy R[-1]C:R[1]C. Części wiersza i kolumny parsują się niezależnie, więc formy mieszane jak R[1]C3 — względny wiersz, bezwzględna kolumna — też działają, a litery nie rozróżniają wielkości, więc r[-1]c[2] kompiluje się tak samo jak jego wersja wielkimi literami. Części w nawiasach stają się niezakotwiczonymi (względnymi) współrzędnymi A1; gołe liczby stają się bezwzględnymi współrzędnymi zakotwiczonymi znakiem $

Ciekawym pytaniem jest to, jak konwerter unika pokiereszowania wszystkiego innego w formule, bo R i C to częste litery. Jego reguła rozróżniania jest oparta na tokenach: R jest brane za początek referencji tylko wtedy, gdy nie poprzedza go inna litera, a kandydat musi potem sparsować się w całości — opcjonalna część wiersza, obowiązkowe C, opcjonalna część kolumny — albo tekst zostaje przywrócony nietknięty. Dlatego ROUND(R[-1]C[0], 2) konwertuje tylko wewnętrzną referencję: po R w ROUND idzie O, a nie cyfra, nawias czy C, więc parsowanie zawodzi, a nazwa funkcji przechodzi dalej dosłownie. Ta sama logika chroni ROW(), a nazwy funkcji zaczynające się od C nigdy w ogóle nie są kandydatami, skoro referencję zaczyna tylko R. Uwagi do wydania v2.175.0 mówią, że pomijane są też literały tekstowe i identyfikatory; mimo to, jeśli literał w cudzysłowie w twojej formule zawiera akurat tekst o kształcie dokładnie takim jak referencja R1C1, warto raz sprawdzić skompilowane wyjście, zanim zaufasz mu na produkcji

Bazy współrzędnych i szczegóły kotwiczenia warte poznania

Wewnątrz tego API spotykają się dwie konwencje, a trzymanie ich w porządku pozwala uniknąć jedynej prawdziwej pułapki. Parametry CurRow i CurCol w GetCompiledFormulaR1C1 liczą się od zera, zgodnie z API kalkulatora, podczas gdy liczby wewnątrz samej notacji liczą się od jedynki, zgodnie z tym, co pokazuje Excel: R2C3 to wiersz 2, kolumna 3, czyli $C$2, a nie $D$3. Jeśli twój licznik pętli już liczy od zera, podajesz go wprost jako CurRow; poprawka + 1 mieszka wewnątrz konwertera, a nie w twoim kodzie

Jeszcze jeden szczegół ma znaczenie, jeśli skompilowana formuła przeżyje komórkę, dla której ją skompilowano. Kiedy pomijasz część wiersza albo kolumny — formy RC[-1] albo R[2]C — pominięta współrzędna rozwiązuje się do komórki goszczącej i jest emitowana w skonwertowanym tekście A1 jako bezwzględna, zakotwiczona znakiem $. W czasie wyliczania jest to niewidoczne, bo wartość jest ta sama tak czy inaczej. Ale kotwiczenie względne kontra bezwzględne decyduje o tym, jak referencje przesuwają się, gdy wiersze albo kolumny są później wstawiane lub usuwane, co omawia towarzyszący artykuł o dostosowywaniu referencji w formułach. Jeśli potrzebujesz, by współrzędna została względna przez edycje strukturalne, zapisz przesunięcie jawnie — R[0]C[-1] zamiast RC[-1] — żeby konwerter wyemitował niezakotwiczoną referencję A1

Kompilacja R1C1 jest częścią silnika formuł w HotXLS Delphi Excel Component, obok kompilatora A1, grafu przeliczeń i pokazanego wyżej API wyliczania. Jeśli twój kod buduje arkusze, przelatując formułami w dół kolumn, przeniesienie tych pętli ze sklejanego A1 na jedną stałą R1C1 jest jednym z najtańszych dostępnych ulepszeń niezawodności