Komponent HotXLS kompiluje formuły zapisane w notacji odwołań R1C1 za pomocą metody TXLSCalculator.GetCompiledFormulaR1C1, która obsługuje formy takie jak R2C3 (bezwzględne), R[-1]C[2] (względne przesunięcie) oraz RC (bieżąca komórka). Przekształca je na notację A1 w odniesieniu do wiersza i kolumny komórki, w której znajduje się formuła, a wynik przekazuje do tego samego kompilatora, który obsługuje standardowe formuły A1. Wspaniałe jest to, że w przypadku kodu w Delphi i C++Builder, który generuje tę samą formułę w setkach wierszy, ta pojedyncza metoda eliminuje całą klasę błędów łączenia ciągów znaków
Ta klasa błędów jest znana każdemu, kto programowo wypełniał kolumnę. Wykonujesz iterację po wierszach i dla każdego wiersza budujesz ciąg formuły A1 za pomocą Format('D%d*E%d', [Row, Row]). Każda iteracja wstawia numery wierszy do tekstu i te numery są jedynym elementem, który się zmienia. Wystarczy raz popełnić błąd w przesunięciu, pomieszać licznik pętli (liczony od 0) z numerami wierszy A1 (liczonymi od 1) lub przesunąć blok danych o wiersz nagłówka, a każda formuła w kolumnie wskaże wiersz obok. Żaden wyjątek nie zostanie zgłoszony, a wyniki będą po prostu błędne. Formuła, o którą w rzeczywistości chodziło — „pomnóż dwie komórki po mojej lewej stronie” — w ogóle nie wymaga podawania numeru wiersza, a notacja R1C1 pozwala zapisać ją właśnie w taki sposób
Czym jest notacja R1C1 i kiedy należy jej używać?
Notacja R1C1 adresuje komórki za pomocą numeru wiersza i kolumny zamiast litery kolumny i numeru wiersza, a odwołania względne oznacza jako jawne przesunięcia względem komórki z formułą. Zapis R2C3 wskazuje na komórkę bezwzględną w wierszu 2, kolumnie 3, co w notacji A1 zapisuje się jako $C$2. Z kolei R[-1]C[2] to komórka o jeden wiersz wyżej i dwie kolumny w prawo od miejsca, w którym znajduje się formuła, a RC to sama komórka z formułą. Kluczem są przesunięcia w nawiasach kwadratowych: odwołanie względne w notacji R1C1 ma ten sam zapis bez względu na to, w której komórce się znajduje, podczas gdy zapis tego samego odwołania w notacji A1 zmienia się w każdym wierszu
Notacja ta sprawdza się doskonale w jednym, bardzo częstym scenariuszu: przy generowaniu szablonów, gdzie ta sama formuła względna musi zostać wprowadzona do każdego wiersza obszaru danych
W notacji A1 musisz na nowo generować tekst formuły dla każdego wiersza. W notacji R1C1 tekst ten pozostaje stałą. Odpowiada to również bardziej temu, jak wewnętrznie zorganizowane są formaty plików arkuszy: rekordy formuł współdzielonych przechowują odwołania względne jako przesunięcia wierszy i kolumn względem komórki macierzystej, więc tworzenie ciągu A1 dla każdego wiersza to operacja, którą Twój kod wykonuje tylko po to, by parser rozłożył ją z powrotem na przesunięcia. R1C1 eliminuje tę niepotrzebną pracę. Dla formuł tworzonych interaktywnie przez człowieka naturalnym wyborem pozostaje notacja A1, dlatego jest ona domyślna w całym komponencie HotXLS
Jak HotXLS kompiluje formułę R1C1?
Komponent HotXLS udostępnia tę funkcję na dwóch poziomach, wprowadzonych w wersji v2.175.0. Metoda TXLSCalculator.GetCompiledFormulaR1C1(UncompiledFormula: String; SheetID, CurRow, CurCol: Integer): TXLSCompiledFormula to ta, którą wywołuje większość kodu: zwraca skompilowaną formułę gotową do obliczeń, dokładnie tak samo jak jej odpowiednik A1 GetCompiledFormula, lecz z dwoma dodatkowymi parametrami określającymi wiersz i kolumnę (indeksowane od 0) komórki, do której formuła należy. Pod nią metoda TXLSFormula.GetCompiledR1C1 generuje surowe drzewo składniowe, a samodzielna funkcja R1C1ToA1(const AFormula: String; CurRow, CurCol: Integer): String wykonuje właściwą konwersję notacji. Potok ten jest celowo prosty: tłumaczy tekst R1C1 na równoważny tekst A1 przy użyciu współrzędnych komórki macierzystej, a następnie kompiluje tekst A1 przez istniejący silnik — ten sam, który obsługuje nazwy zdefiniowane i odwołania międzyarkuszowe oraz wywołuje niestandardowe funkcje arkusza
Ponieważ konwersja następuje przed kompilacją, wszystko downstream zachowuje się tak, jakbyś napisał formułę A1 samodzielnie. R1C1ToA1('R2C3', 4, 3) zwraca '$C$2' bez względu na komórkę macierzystą, ponieważ obie współrzędne są bezwzględne. Warto zauważyć, że R1C1ToA1('SUM(R[-3]C[0]:R[-1]C[0])', 4, 3) — formuła znajdująca się w D5, jako że CurRow = 4 i CurCol = 3 są indeksowane od 0 — zwraca 'SUM(D2:D4)': przesunięcia zostały wyznaczone względem wiersza 5, kolumny D i wygenerowane jako proste odwołania względne A1. Zakresy nie wymagają specjalnej obsługi; dwukropek jest pomijany bez zmian, a każdy punkt końcowy konwertuje się niezależnie
// Formuła znajduje się w D5: CurRow = 4, CurCol = 3 (oba indeksowane od 0)
S := R1C1ToA1('R2C3', 4, 3);
// S = '$C$2' (bezwzględny wiersz i kolumna)
S := R1C1ToA1('SUM(R[-3]C[0]:R[-1]C[0])', 4, 3);
// S = 'SUM(D2:D4)' (przesunięcia wyznaczone względem D5)
S := R1C1ToA1('ROUND(R[-1]C[0], 2)', 4, 3);
// S = 'ROUND(D4, 2)' (litera R w słowie ROUND pozostaje nienaruszona)Wypełnianie kolumny jedną formułą względną
Porównaj wersję A1, która na nowo generuje tekst formuły przy każdej iteracji, z wersją R1C1, w której formuła jest stałą, a zmieniają się jedynie współrzędne komórki macierzystej. Obie kompilują się przez TXLSCalculator i są wyliczane za pomocą GetValue; kalkulator przyjmuje przy konstruowaniu wywołanie zwrotne (callback) dostawcy komórek, dzięki czemu ewaluator może pobierać wartości z Twojego źródła danych
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 w ogóle nie zawiera arytmetyki wierszy w tekście formuły. Zapis 'RC[-2]*RC[-1]' oznacza „ten sam wiersz, dwie kolumny w lewo, pomnożone przez ten sam wiersz, jedna kolumna w lewo” zarówno w wierszu 2, jak i w wierszu 500. Jeśli blok danych zostanie później przesunięty w dół o wiersz nagłówka, stała formuły nie ulegnie zmianie — zmienią się jedynie granice pętli. Wersja A1 ma dwa miejsca, w których można się pomylić przy korekcie o + 1; wersja R1C1 ma ich zero
Które formy R1C1 akceptuje konwerter?
Konwerter R1C1ToA1 rozpoznaje formy udokumentowane dla wersji v2.175.0: R[n]C[m] dla przesunięć względnych w obu kierunkach, RnCm dla bezwzględnego wiersza i kolumny, R[-n]C[m] z przesunięciami ujemnymi, czyste RC dla samej komórki z formułą oraz zakresy takie jak R1C1:R3C3 lub R[-1]C:R[1]C. Składowe wiersza i kolumny są analizowane niezależnie, więc formy mieszane, takie jak R[1]C3 — względny wiersz, bezwzględna kolumna — również działają, a wielkość liter nie ma znaczenia, stąd r[-1]c[2] kompiluje się identycznie jak wersja z wielkich liter. Elementy w nawiasach kwadratowych stają się niezakotwiczonymi (względnymi) współrzędnymi A1; same liczby stają się współrzędnymi bezwzględnymi zakotwiczonymi znakiem $
Ciekawym zagadnieniem jest to, w jaki sposób konwerter unika uszkadzania innych elementów w formule, jako że litery R i C są powszechne. Reguła rozróżniania opiera się na tokenach: litera R jest uznawana za początek odwołania tylko wtedy, gdy nie poprzedza jej inna litera, a kandydat musi zostać w pełni zinterpretowany — opcjonalna część wiersza, obowiązkowe C, opcjonalna część kolumny — w przeciwnym razie tekst pozostaje nienaruszony. Dlatego ROUND(R[-1]C[0], 2) konwertuje tylko wewnętrzne odwołanie: po literze R w słowie ROUND występuje O, a nie cyfra, nawias czy C, stąd próba analizy kończy się niepowodzeniem i nazwa funkcji przechodzi bez zmian. Ta sama logika chroni funkcję ROW(), a nazwy funkcji zaczynające się od C w ogóle nie są brane pod uwagę, jako że tylko litera R rozpoczyna odwołanie. Dokumentacja wydania v2.175.0 informuje, że literały ciągów znaków oraz identyfikatory są również pomijane. Mimo to, jeśli ujęty w cudzysłów literał w formule zawiera tekst o strukturze identycznej jak odwołanie R1C1, warto sprawdzić skompilowany wynik przed użyciem go produkcyjnie
Baza współrzędnych i szczegóły zakotwiczenia, o których warto wiedzieć
W tym API spotykają się dwie konwencje i ich rozróżnienie pozwala uniknąć jedynej realnej pułapki. Parametry CurRow oraz CurCol metody GetCompiledFormulaR1C1 są indeksowane od 0 (zgodnie z API kalkulatora), podczas gdy liczby w samej notacji są indeksowane od 1 (zgodnie z tym, co wyświetla Excel): R2C3 to wiersz 2, kolumna 3, czyli $C$2, a nie $D$3. Jeśli licznik pętli jest już indeksowany od 0, przekazujesz go bezpośrednio jako CurRow; korekta o + 1 odbywa się wewnątrz konwertera, a nie w Twoim kodzie
Jeszcze jeden szczegół ma znaczenie, jeśli skompilowana formuła ma istnieć dłużej niż komórka, dla której została skompilowana. Gdy pomijasz część wiersza lub kolumny — formy RC[-1] lub R[2]C — pominięta współrzędna odnosi się do komórki macierzystej i jest generowana jako bezwzględna, zakotwiczona znakiem $ współrzędna w przekonwertowanym tekście A1. W czasie wyliczania wartości jest to niewidoczne, jako że wynik jest taki sam w obu przypadkach. Jednak zakotwiczenie względne kontra bezwzględne decyduje o tym, jak odwołania przesuwają się przy późniejszym wstawianiu lub usuwaniu wierszy i kolumn, co opisano w towarzyszącym artykule o dostosowywaniu odwołań w formułach. Jeśli potrzebujesz, aby współrzędna pozostała względna przy zmianach struktury, zapisz przesunięcie jawnie — R[0]C[-1] zamiast RC[-1] — dzięki czemu konwerter wygeneruje niezakotwiczone odwołanie A1
Kompilacja R1C1 jest częścią silnika formuł w HotXLS Delphi Excel Component, obok kompilatora A1, grafu przeliczania oraz API ewaluacji przedstawionego powyżej. Jeśli Twój kod tworzy arkusze poprzez wstawianie formuł w kolumnach w pętli, przeniesienie tych pętli z łączonego tekstu A1 na pojedynczą stałą R1C1 to jedno z najprostszych ulepszeń poprawiających niezawodność kodu