Notația de formule R1C1 în Delphi cu HotXLS

HotXLS compilează formulele scrise în notația de referință R1C1 prin intermediul TXLSCalculator.GetCompiledFormulaR1C1, care acceptă forme precum R2C3 (absolut), R[-1]C[2] (deplasare relativă) și RC (celula curentă), le convertește în notația A1 în raport cu rândul și coloana celulei în care se află formula și trimite rezultatul către același compilator care gestionează formulele obișnuite A1. Pentru codul Delphi și C++Builder care generează aceeași formulă pe sute de rânduri, această metodă elimină o întreagă clasă de erori de concatenare a șirurilor

Clasa de erori este familiară oricui a completat programatic o coloană. Iterați peste rânduri și, pentru fiecare rând, construiți un șir de formulă A1 cu Format('D%d*E%d', [Row, Row]). Fiecare iterație inserează numerele de rând în text, iar numerele de rând sunt singura parte care se modifică. Greșiți deplasarea (offset-ul) o singură dată, amestecați un contor de buclă cu baza 0 cu numere de rând A1 cu baza 1, sau mutați blocul de date cu un rând de antet, iar fiecare formulă din coloană va indica spre un rând greșit. Nimic nu generează o excepție; numerele sunt pur și simplu greșite. Formula pe care o doreați de fapt, „înmulțește cele două celule din stânga mea”, nu menționează deloc un număr de rând, iar notația R1C1 vă permite să o scrieți în acest mod

Ce este notația R1C1 și când ar trebui să o folosiți?

Notația R1C1 adresează celulele prin numărul rândului și al coloanei, în loc de litera coloanei plus numărul rândului, și marchează referințele relative ca deplasări explicite față de celula formulei. R2C3 este celula absolută de la rândul 2, coloana 3, pe care A1 o scrie $C$2. R[-1]C[2] este cu un rând mai sus și cu două coloane la dreapta față de poziția formulei. RC reprezintă celula formulei în sine. Deplasările între paranteze drepte reprezintă esența: o referință relativă în R1C1 se citește la fel, indiferent de celula care o găzduiește, în timp ce redarea A1 a aceleiași referințe se modifică cu fiecare rând

Notația își dovedește utilitatea în special într-un scenariu comun: generarea de șabloane, unde aceeași formulă relativă trebuie plasată în fiecare rând al unei regiuni de date. În A1 trebuie să randați din nou textul formulei pentru fiecare rând. În R1C1 textul este o constantă. Acest mod este, de asemenea, mai apropiat de modul în care formatele de fișiere de calcul funcționează intern: înregistrările de formule partajate stochează referințele relative ca deplasări de rând și coloană față de celula gazdă, deci un șir A1 per rând este ceva ce codul dvs. sintetizează doar pentru ca parserul să-l descompună înapoi în deplasări. R1C1 evită această conversie suplimentară. Pentru formulele interactive, scrise manual de oameni, A1 rămâne alegerea firească, motiv pentru care rămâne valoarea implicită peste tot în HotXLS

Cum compilează HotXLS o formulă R1C1?

HotXLS expune această caracteristică la două niveluri, adăugate în versiunea v2.175.0. TXLSCalculator.GetCompiledFormulaR1C1(UncompiledFormula: String; SheetID, CurRow, CurCol: Integer): TXLSCompiledFormula este apelul utilizat de cele mai multe programe: returnează o formulă compilată gata de evaluare, exact ca ruda sa A1 GetCompiledFormula, pero cu doi parametri suplimentari care specifică rândul și coloana (baza 0) ale celulei căreia îi aparține formula. Subiacent, TXLSFormula.GetCompiledR1C1 produce arborele de sintaxă brut, iar o funcție autonomă R1C1ToA1(const AFormula: String; CurRow, CurCol: Integer): String efectuează conversia efectivă a notației. Conducta este simplă: traduce textul R1C1 în textul A1 echivalent utilizând coordonatele celulei gazdă, apoi compilează textul A1 prin motorul existent — același motor care rezolvă numele definite și referințele cross-sheet și trimite funcții personalizate în foaia de calcul

// Formula se află în D5: CurRow = 4, CurCol = 3 (ambele cu baza 0)
S := R1C1ToA1('R2C3', 4, 3);
// S = '$C$2'  (rând și coloană absolută)

S := R1C1ToA1('SUM(R[-3]C[0]:R[-1]C[0])', 4, 3);
// S = 'SUM(D2:D4)'  (deplasări rezolvate în raport cu D5)

S := R1C1ToA1('ROUND(R[-1]C[0], 2)', 4, 3);
// S = 'ROUND(D4, 2)'  (litera R din ROUND este lăsată neschimbată)

Completarea unei coloane cu o singură formulă relativă

Avantajul apare în buclă. Comparați versiunea A1, care re-randează textul formulei la fiecare iterație, cu versiunea R1C1, unde formula este o constantă și se mută doar coordonatele gazdei. Ambele se compilează prin TXLSCalculator și se evaluează cu GetValue; calculatorul primește o funcție callback de furnizare a celulelor la construcție, astfel încât evaluatorul să poată prelua valorile celulelor din sursa dvs. de date

// Stilul A1: un șir de formulă diferit pentru fiecare rând
for Row := 1 to 500 do
begin
  FormulaText := Format('D%d*E%d', [Row + 1, Row + 1]);  // rânduri A1 cu baza 1
  Compiled := Calc.GetCompiledFormula(FormulaText, 0);
  // ... evaluează, stochează, eliberează ...
end;

const
  AmountFormula = 'RC[-2]*RC[-1]';  // două celule la stânga, același rând
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;

Bucla R1C1 nu conține deloc aritmetică de rânduri în textul formulei. 'RC[-2]*RC[-1]' înseamnă „același rând, două coloane la stânga, înmulțit cu același rând, o coloană la stânga” atât în rândul 2, cât și în rândul 500, iar dacă blocul de date se mută ulterior în jos din cauza unui rând de antet, formula constantă nu se modifică — se schimbă doar limitele buclei. Versiunea A1 are două locuri în care ajustarea + 1 poate fi greșită; versiunea R1C1 are zero

Care forme R1C1 sunt acceptate de convertor?

Convertorul R1C1ToA1 recunoaște formele documentate pentru v2.175.0: R[n]C[m] pentru deplasări relative în ambele direcții, RnCm pentru rând și coloană absolută, R[-n]C[m] cu deplasări negative, simplul RC pentru celula formulei în sine și intervale cum ar fi R1C1:R3C3 sau R[-1]C:R[1]C. Părțile de rând și coloană se analizează independent, astfel încât funcționează și forme mixte precum R[1]C3 — rând relativ, coloană absolută — iar literele nu sunt sensibile la majuscule (case-insensitive), deci r[-1]c[2] se compilează la fel ca versiunea sa cu majuscule. Părțile între paranteze devin coordonate A1 neancorate (relative); numerele simple devin coordonate absolute ancorate cu $

Întrebarea interesantă este cum evită convertorul să modifice restul formulei, deoarece R și C sunt litere comune. Regula sa de discriminare se bazează pe tokeni: un R este considerat începutul unei referințe numai atunci când nu este precedat de o altă lită, iar candidatul trebuie să poată fi analizat complet — parte de rând opțională, C obligatoriu, parte de coloană opțională — altfel textul este lăsat intact. De aceea, ROUND(R[-1]C[0], 2) convertește doar referința internă: litera R din ROUND este urmată de O și nu de o cifră, paranteză sau C, deci analiza eșuează și numele funcției trece neschimbat. Aceeași logică protejează funcția ROW(), iar numele de funcții care încep cu C nu sunt niciodată candidate, deoarece numai R începe o referință. Notele de versiune pentru v2.175.0 precizează că literalele de tip șir și identificatorii sunt de asemenea omise; chiar și așa, dacă un literal între ghilimele din formula dvs. conține text formatat exact ca o referință R1C1, merită să verificați rezultatul compilat înainte de a-l folosi în producție

Coordonatele de bază și detalii de ancorare care merită cunoscute

Parametrii `CurRow` și `CurCol` din `GetCompiledFormulaR1C1` au baza 0, urmând API-ul calculatorului, în timp ce numerele din notația în sine au baza 1, potrivindu-se cu ceea ce afișează Excel: `R2C3` este rândul 2, coloana 3, adică `$C$2`, nu `$D$3`. Dacă contorul dvs. de buclă are deja baza 0, îl trimiteți direct ca `CurRow`; modificarea `+ 1` se face în interiorul convertorului, nu în codul dvs.

Încă un detaliu contează dacă formula compilată va supraviețui celulei pentru care a fost compilată. Când omiteți o parte de rând sau coloană — sub formele `RC[-1]` sau `R[2]C` — coordonata omisă se rezolvă la celula gazdă și este emisă ca o coordonată absolută, ancorată cu `$`, în textul A1 convertit. La momentul evaluării acest lucru este invizibil, deoarece valoarea este aceeași în ambele cazuri. Însă ancorarea relativă versus absolută decide modul în care referințele se decalează atunci când rândurile sau coloanele sunt inserate sau șterse ulterior, așa cum este explicat în articolul însoțitor despre ajustarea referințelor de formule. Dacă aveți nevoie ca o coordonată să rămână relativă la editările structurale, scrieți deplasarea în mod explicit — `R[0]C[-1]` în loc de `RC[-1]` — astfel încât convertorul să emită o referință A1 neancorată

Compilarea R1C1 face parte din motorul de formule din HotXLS Delphi Excel Component, alături de compilatorul A1, graful de recalculare și API-ul de evaluare prezentat mai sus. Dacă codul dvs. construiește foi de calcul prin bucle de formule pe coloane, trecerea acelor bucle de la îmbinarea de șiruri A1 la o singură constantă R1C1 reprezintă una dintre cele mai simple îmbunătățiri de fiabilitate disponibile